MySQL Cheatsheet

1. Connection and Basic Commands

Connecting to MySQL

Bash

1
2
3
4
5
6
7
8
9
10
11
12
# Connect to MySQL server
mysql -u username -p

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

# Connect to a remote host on a specific port
mysql -h host.example.com -P 3306 -u username -p database_name

# Connect using SSL (recommended for remote connections)
mysql -u username -p --ssl-mode=REQUIRED

Basic Client Commands

SQL

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

-- Exit MySQL client
EXIT;
QUIT;

-- Current user and host
SELECT CURRENT_USER();

-- Show server version
SELECT VERSION();

2. Database Operations

Create Database

SQL

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

-- Create with character set and collation
-- utf8mb4 is the recommended default; utf8mb4_0900_ai_ci is the default collation in MySQL 8.0+
CREATE DATABASE my_database
CHARACTER SET utf8mb4
COLLATE utf8mb4_0900_ai_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
10
11
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    profile JSON,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_active BOOLEAN NOT NULL DEFAULT TRUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Alter Table

SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 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);

-- Rename column (MySQL 8.0+)
ALTER TABLE users
RENAME COLUMN old_name TO new_name;

-- Rename table (MySQL 8.0+)
ALTER TABLE users RENAME TO app_users;

-- 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

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT: Whole numbers (with optional UNSIGNED)
DECIMAL(M,D) / NUMERIC(M,D): Precise decimal numbers — use for money
FLOAT, DOUBLE: Approximate floating-point
Note: Display widths on integer types (e.g. INT(11)) are deprecated in MySQL 8.0

String Types

VARCHAR(n): Variable-length string
CHAR(n): Fixed-length string
TEXT / MEDIUMTEXT / LONGTEXT: Long text
BINARY / VARBINARY / BLOB: Binary data

Date and Time Types

DATE: Date (YYYY-MM-DD)
DATETIME: Date and time (YYYY-MM-DD HH:MM:SS), supports fractional seconds DATETIME(6)
TIMESTAMP: UTC-stored timestamp, range 1970-01-01 to 2038-01-19
TIME: Time of day
YEAR: 4-digit year (2-digit YEAR(2) was removed)

Other Types

BOOLEAN / BOOL: Synonym for TINYINT(1)
ENUM / SET: Predefined list of values
JSON: Native JSON type with validation and indexing via generated columns
Spatial types: GEOMETRY, POINT, POLYGON, etc.

5. CRUD Operations

Insert Data

SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Single row insert
INSERT INTO users (username, email, password_hash)
VALUES ('johndoe', 'john@example.com', 'hashed_password');

-- Multiple row insert
INSERT INTO users (username, email, password_hash)
VALUES
    ('jane', 'jane@example.com', 'hash1'),
    ('bob', 'bob@example.com', 'hash2');

-- Upsert (insert or update on duplicate key)
-- Note: VALUES() in the UPDATE clause is deprecated in 8.0.20+;
-- use a row alias instead.
INSERT INTO users (id, username, email)
VALUES (1, 'johndoe', 'john@example.com') AS new
ON DUPLICATE KEY UPDATE
    username = new.username,
    email    = new.email;

Select Data

SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- 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;

-- Common Table Expression (MySQL 8.0+)
WITH active AS (
    SELECT id, username FROM users WHERE is_active = TRUE
)
SELECT * FROM active ORDER BY username;

-- Window function (MySQL 8.0+)
SELECT
    id,
    username,
    ROW_NUMBER() OVER (PARTITION BY is_active ORDER BY created_at DESC) AS rn
FROM users;

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_hash = 'new_hash'
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, resets AUTO_INCREMENT)
TRUNCATE TABLE users;

6. Joins and Relationships

Inner Join

SQL

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

Left Join

SQL

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

Right Join

SQL

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

Lateral Join (MySQL 8.0.14+)

SQL

1
2
3
4
5
6
7
8
9
10
11
-- Per-user latest order via LATERAL derived table
SELECT u.id, u.username, latest.order_id, latest.created_at
FROM users AS u,
LATERAL (
    SELECT order_id, created_at
    FROM orders
    WHERE user_id = u.id
    ORDER BY created_at DESC
    LIMIT 1
) AS latest;

7. Indexes and Constraints

Create Index

SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 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);

-- Descending index (physically descending, MySQL 8.0+)
CREATE INDEX idx_created_desc ON users(created_at DESC);

-- Invisible index (8.0+) — exists but ignored by the optimizer; useful for testing
CREATE INDEX idx_phone ON users(phone) INVISIBLE;
ALTER TABLE users ALTER INDEX idx_phone VISIBLE;

-- Functional index (8.0.13+)
CREATE INDEX idx_email_lower ON users((LOWER(email)));

-- Multi-valued index over a JSON array (8.0.17+)
CREATE INDEX idx_tags
ON users ((CAST(profile->'$.tags' AS CHAR(32) ARRAY)));

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 (enforced as of MySQL 8.0.16)
CHECK (age >= 18)

8. Functions and Operators

String Functions

SQL

1
2
3
4
5
6
7
SELECT
    CONCAT(first_name, ' ', last_name) AS full_name,
    LOWER(username)                    AS lowercase_username,
    CHAR_LENGTH(email)                 AS email_length,    -- character count
    LENGTH(email)                      AS email_bytes      -- byte count
FROM users;

Date Functions

SQL

1
2
3
4
5
6
7
8
SELECT
    CURRENT_DATE(),
    NOW(),
    DATE_ADD(created_at, INTERVAL 1 MONTH) AS next_month,
    DATEDIFF(end_date, start_date)         AS duration_days,
    TIMESTAMPDIFF(HOUR, start_time, end_time) AS duration_hours
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;

JSON Functions (MySQL 5.7+, expanded in 8.0)

SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
    profile->>'$.name'                       AS name,           -- unquoted extract
    JSON_EXTRACT(profile, '$.address.city')  AS city,
    JSON_CONTAINS(profile, '"admin"', '$.roles') AS is_admin
FROM users;

-- Update a value in place
UPDATE users
SET profile = JSON_SET(profile, '$.last_login', NOW())
WHERE id = 1;

-- JSON_TABLE: turn a JSON array into rows (8.0+)
SELECT t.tag
FROM users,
JSON_TABLE(profile, '$.tags[*]' COLUMNS (tag VARCHAR(64) PATH '$')) AS t;

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;

Generated Columns

SQL

1
2
3
4
5
6
ALTER TABLE users
ADD COLUMN email_domain VARCHAR(255)
    AS (SUBSTRING_INDEX(email, '@', -1)) STORED;

CREATE INDEX idx_email_domain ON users(email_domain);

Transactions

SQL

1
2
3
4
5
6
7
8
9
10
11
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;            -- or ROLLBACK;

-- Row-level locking
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
SELECT * FROM accounts WHERE id = 1 FOR SHARE;          -- 8.0+
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;  -- 8.0+
SELECT * FROM accounts WHERE id = 1 FOR UPDATE SKIP LOCKED; -- 8.0+, great for queues

User and Privilege Management (MySQL 8.0+)

SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Create a user with the modern default auth plugin (caching_sha2_password)
CREATE USER 'app'@'%' IDENTIFIED BY 'strong_password';

-- Grants must now be done separately from CREATE USER
GRANT SELECT, INSERT, UPDATE, DELETE ON my_database.* TO 'app'@'%';

-- Roles (8.0+)
CREATE ROLE 'app_read';
GRANT SELECT ON my_database.* TO 'app_read';
GRANT 'app_read' TO 'app'@'%';
SET DEFAULT ROLE ALL TO 'app'@'%';

FLUSH PRIVILEGES;

10. Performance Optimization Tips

Use appropriate indexes; verify they're used via EXPLAIN / EXPLAIN ANALYZE (8.0.18+).
Avoid SELECT *; project only the columns you need.
Limit result sets with LIMIT and keyset (seek) pagination over deep OFFSET.
Use the smallest correct data type (e.g. INT UNSIGNED over BIGINT when sufficient).
Prefer covering indexes so queries can be served from the index alone.
Use utf8mb4 everywhere — utf8 is a deprecated alias for the 3-byte utf8mb3.
Use InnoDB (the default engine) for transactional workloads.
Tune innodb_buffer_pool_size — typically 50–75% of available RAM on a dedicated server.
Batch writes inside transactions to amortize fsync overhead.
Watch the slow query log and performance_schema for regressions.

11. Common Patterns and Best Practices

Always use prepared statements / parameterized queries to prevent SQL injection.
Wrap multi-statement changes in transactions.
Prefer soft delete (deleted_at TIMESTAMP NULL) over hard delete when audit history matters.
Use VARCHAR for variable-length strings; reserve CHAR for truly fixed-width values.
Declare columns NOT NULL with sensible defaults whenever possible.
Use ENUM or a lookup table for small predefined value sets.
Hash passwords with a modern KDF (bcrypt/argon2) in the application — never store plaintext.
Use TIMESTAMP or DATETIME columns with DEFAULT CURRENT_TIMESTAMP / ON UPDATE CURRENT_TIMESTAMP for automatic time tracking.
Always set sql_mode to include STRICT_TRANS_TABLES (the default in 8.0) to catch bad data early.
Pin the auth plugin (caching_sha2_password) and require TLS for remote connections.

Bonus: Quick Reference Commands

Bash

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# Backup a single database
mysqldump -u username -p --single-transaction --routines --triggers \
    database_name > backup.sql

# Backup all databases
mysqldump -u username -p --all-databases --single-transaction > all.sql

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

# Logical backup/restore with the modern MySQL Shell utilities
mysqlsh -u username -h host -- util dump-instance /backups/full
mysqlsh -u username -h host -- util load-dump /backups/full

SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
-- Show table structure
DESCRIBE users;

-- Show CREATE TABLE statement
SHOW CREATE TABLE users;

-- Inspect query plan
EXPLAIN SELECT * FROM users WHERE username = 'johndoe';
EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'johndoe'; -- 8.0.18+

-- Show currently running queries
SHOW PROCESSLIST;

MySQL

Continue Learning