MySQL Cheatsheet

1. Connection and Basic Commands

Connecting to MySQL

Bash

1
2
3
4
5
6
# Connect to MySQL server
mysql -u username -p

# Connect to a specific database
mysql -u username -p database_name

Basic Client Commands

SQL

1
2
3
4
5
6
7
8
9
10
-- Show all databases
SHOW DATABASES;

-- Exit MySQL client
EXIT;
QUIT;

-- Current user and host
SELECT CURRENT_USER();

2. Database Operations

Create Database

SQL

1
2
3
4
5
6
7
8
9
10
11
-- Basic creation
CREATE DATABASE my_database;

-- Create with character set and collation
CREATE DATABASE my_database 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- Create only if not exists
CREATE DATABASE IF NOT EXISTS my_database;

Use and Drop Database

SQL

1
2
3
4
5
6
7
8
9
-- Switch to a database
USE my_database;

-- Drop a database
DROP DATABASE my_database;

-- Drop only if exists
DROP DATABASE IF EXISTS my_database;

3. Table Operations

Create Table

SQL

1
2
3
4
5
6
7
8
9
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

Alter Table

SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Add a column
ALTER TABLE users 
ADD COLUMN phone VARCHAR(20);

-- Modify column
ALTER TABLE users 
MODIFY COLUMN username VARCHAR(100);

-- Rename column
ALTER TABLE users 
CHANGE COLUMN old_name new_name VARCHAR(50);

-- Drop column
ALTER TABLE users 
DROP COLUMN phone;

Drop Table

SQL

1
2
3
4
5
6
-- Drop table
DROP TABLE users;

-- Drop table if exists
DROP TABLE IF EXISTS users;

4. Data Types

Numeric Types

INT: Whole numbers
DECIMAL(M,D): Precise decimal numbers
FLOAT: Floating-point number
DOUBLE: Double-precision floating-point

String Types

VARCHAR(n): Variable-length string
CHAR(n): Fixed-length string
TEXT: Long text string

Date and Time Types

DATE: Date (YYYY-MM-DD)
DATETIME: Date and time (YYYY-MM-DD HH:MM:SS)
TIMESTAMP: Automatic timestamp
TIME: Time of day

Other Types

BOOLEAN: True/False
ENUM: List of predefined values
JSON: JSON data type

5. CRUD Operations

Insert Data

SQL

1
2
3
4
5
6
7
8
9
10
-- Single row insert
INSERT INTO users (username, email, password) 
VALUES ('johndoe', 'john@example.com', 'password123');

-- Multiple row insert
INSERT INTO users (username, email, password) 
VALUES 
    ('jane', 'jane@example.com', 'pass1'),
    ('bob', 'bob@example.com', 'pass2');

Select Data

SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Basic select
SELECT * FROM users;

-- Select specific columns
SELECT username, email FROM users;

-- Select with conditions
SELECT * FROM users 
WHERE is_active = TRUE AND created_at > '2023-01-01';

-- Limit and offset
SELECT * FROM users 
LIMIT 10 OFFSET 20;

-- Sorting
SELECT * FROM users 
ORDER BY created_at DESC;

Update Data

SQL

1
2
3
4
5
6
7
8
9
10
11
12
-- Update specific rows
UPDATE users 
SET is_active = FALSE 
WHERE id = 5;

-- Multiple column update
UPDATE users 
SET 
    email = 'new_email@example.com',
    password = 'newpassword' 
WHERE username = 'johndoe';

Delete Data

SQL

1
2
3
4
5
6
7
-- Delete specific rows
DELETE FROM users 
WHERE id = 10;

-- Truncate entire table (faster than delete)
TRUNCATE TABLE users;

6. Joins and Relationships

Inner Join

SQL

1
2
3
4
SELECT users.username, orders.order_id
FROM users
INNER JOIN orders ON users.id = orders.user_id;

Left Join

SQL

1
2
3
4
SELECT users.username, orders.order_id
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

Right Join

SQL

1
2
3
4
SELECT users.username, orders.order_id
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

7. Indexes and Constraints

Create Index

SQL

1
2
3
4
5
6
7
8
9
-- Single column index
CREATE INDEX idx_username ON users(username);

-- Unique index
CREATE UNIQUE INDEX idx_email ON users(email);

-- Composite index
CREATE INDEX idx_name_email ON users(username, email);

Constraints

SQL

1
2
3
4
5
6
7
8
9
10
11
-- Primary Key
PRIMARY KEY (id)

-- Foreign Key
FOREIGN KEY (user_id) 
REFERENCES users(id) 
ON DELETE CASCADE

-- Check Constraint
CHECK (age >= 18)

8. Functions and Operators

String Functions

SQL

1
2
3
4
5
6
SELECT 
    CONCAT(first_name, ' ', last_name) AS full_name,
    LOWER(username) AS lowercase_username,
    LENGTH(email) AS email_length
FROM users;

Date Functions

SQL

1
2
3
4
5
6
SELECT 
    CURRENT_DATE(),
    DATE_ADD(created_at, INTERVAL 1 MONTH) AS next_month,
    DATEDIFF(end_date, start_date) AS duration
FROM events;

Numeric Functions

SQL

1
2
3
4
5
6
SELECT 
    ROUND(price, 2) AS rounded_price,
    FLOOR(price) AS floor_price,
    CEILING(price) AS ceiling_price
FROM products;

9. Advanced Features

Stored Procedure

SQL

1
2
3
4
5
6
7
8
9
10
DELIMITER //
CREATE PROCEDURE GetActiveUsers()
BEGIN
    SELECT * FROM users WHERE is_active = TRUE;
END //
DELIMITER ;

-- Call procedure
CALL GetActiveUsers();

Trigger

SQL

1
2
3
4
5
6
7
8
9
DELIMITER //
CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.created_at = CURRENT_TIMESTAMP;
END //
DELIMITER ;

View

SQL

1
2
3
4
5
6
7
8
CREATE VIEW active_users AS
SELECT id, username, email 
FROM users 
WHERE is_active = TRUE;

-- Query view
SELECT * FROM active_users;

10. Performance Optimization Tips

Use appropriate indexes
Avoid SELECT *, select only needed columns
Use EXPLAIN to analyze query performance
Limit result sets with LIMIT
Use appropriate data types
Avoid complex joins and subqueries
Use INNER JOIN instead of OUTER JOIN when possible

11. Common Patterns and Best Practices

Always use prepared statements to prevent SQL injection
Use transactions for complex operations
Implement soft delete instead of hard delete when possible
Use VARCHAR for variable-length strings
Set NOT NULL and provide default values
Use enums for predefined lists
Implement proper indexing strategy
Use TIMESTAMP for automatic time tracking

Bonus: Quick Reference Commands

Bash

1
2
3
4
5
6
7
8
9
10
11
12
# Backup database
mysqldump -u username -p database_name > backup.sql

# Restore database
mysql -u username -p database_name < backup.sql

# Show table structure
DESCRIBE users;

# Show create table statement
SHOW CREATE TABLE users;

MySQL

Continue Learning