MySQL Cheat Sheet

A quick lookup for the MySQL syntax you use most — database and table commands, data types, querying, joins, aggregation, indexes, and user management. Each section keeps the syntax terse so you can find and copy what you need fast.

Note: examples use standard MySQL 8.x syntax. A few features (window functions, CTEs) require 8.0+, and some behavior differs in MariaDB.

Database Commands

Task	Command
List databases	`SHOW DATABASES;`
Create a database	`CREATE DATABASE mydb;`
Use a database	`USE mydb;`
Delete a database	`DROP DATABASE mydb;`
Show current database	`SELECT DATABASE();`

Table Commands

Task	Command
List tables	`SHOW TABLES;`
Describe a table	`DESCRIBE users;`
Show create statement	`SHOW CREATE TABLE users;`
Create a table	`CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100));`
Rename a table	`RENAME TABLE old_name TO new_name;`
Delete a table	`DROP TABLE users;`
Empty a table (keep structure)	`TRUNCATE TABLE users;`

Altering Tables

Task	Command
Add a column	`ALTER TABLE users ADD email VARCHAR(255);`
Modify a column	`ALTER TABLE users MODIFY name VARCHAR(200);`
Rename a column	`ALTER TABLE users RENAME COLUMN name TO full_name;`
Drop a column	`ALTER TABLE users DROP COLUMN email;`
Add an index	`ALTER TABLE users ADD INDEX (email);`

Common Data Types

Type	Description	Example
`INT`	Integer	`age INT`
`BIGINT`	Large integer	`views BIGINT`
`DECIMAL(p,s)`	Exact decimal (money)	`price DECIMAL(10,2)`
`FLOAT` / `DOUBLE`	Approximate float	`ratio DOUBLE`
`VARCHAR(n)`	Variable-length string	`name VARCHAR(100)`
`CHAR(n)`	Fixed-length string	`code CHAR(3)`
`TEXT`	Long text	`bio TEXT`
`BOOLEAN`	True/false (alias for TINYINT(1))	`active BOOLEAN`
`DATE`	Date only	`dob DATE`
`DATETIME`	Date and time	`created_at DATETIME`
`TIMESTAMP`	Auto-updating timestamp	`updated_at TIMESTAMP`
`JSON`	JSON data (5.7+)	`meta JSON`
`ENUM(...)`	One value from a list	`status ENUM('on','off')`

Querying Data (SELECT)

Task	Command
Select all columns	`SELECT * FROM users;`
Select specific columns	`SELECT id, name FROM users;`
Filter rows	`SELECT * FROM users WHERE age > 18;`
Sort results	`SELECT * FROM users ORDER BY name ASC;`
Limit results	`SELECT * FROM users LIMIT 10;`
Pagination	`SELECT * FROM users LIMIT 10 OFFSET 20;`
Remove duplicates	`SELECT DISTINCT country FROM users;`
Pattern match	`SELECT * FROM users WHERE name LIKE 'A%';`
Match a set	`SELECT * FROM users WHERE id IN (1,2,3);`
Range	`SELECT * FROM users WHERE age BETWEEN 18 AND 30;`
Null check	`SELECT * FROM users WHERE email IS NULL;`

Inserting, Updating & Deleting

Task	Command
Insert a row	`INSERT INTO users (name) VALUES ('Ana');`
Insert multiple rows	`INSERT INTO users (name) VALUES ('A'),('B');`
Update rows	`UPDATE users SET active = 1 WHERE id = 5;`
Delete rows	`DELETE FROM users WHERE id = 5;`
Insert or update (upsert)	`INSERT INTO users (id,name) VALUES (1,'A') ON DUPLICATE KEY UPDATE name='A';`

Always use a WHERE clause with UPDATE and DELETE, or you'll change every row in the table.

Joins

Join	Meaning	Command
`INNER JOIN`	Only matching rows in both tables	`SELECT * FROM a INNER JOIN b ON a.id = b.a_id;`
`LEFT JOIN`	All rows from left, matches from right	`SELECT * FROM a LEFT JOIN b ON a.id = b.a_id;`
`RIGHT JOIN`	All rows from right, matches from left	`SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id;`
`CROSS JOIN`	Every combination (cartesian)	`SELECT * FROM a CROSS JOIN b;`

MySQL has no built-in FULL OUTER JOIN — emulate it by UNION-ing a LEFT JOIN and a RIGHT JOIN.

Aggregation & Grouping

Task	Command
Count rows	`SELECT COUNT(*) FROM users;`
Sum a column	`SELECT SUM(amount) FROM orders;`
Average	`SELECT AVG(price) FROM products;`
Min / Max	`SELECT MIN(age), MAX(age) FROM users;`
Group by	`SELECT country, COUNT(*) FROM users GROUP BY country;`
Filter groups	`... GROUP BY country HAVING COUNT(*) > 5;`

Use WHERE to filter rows before grouping and HAVING to filter after grouping.

Indexes & Keys

Task	Command
Create an index	`CREATE INDEX idx_email ON users (email);`
Unique index	`CREATE UNIQUE INDEX idx_email ON users (email);`
Composite index	`CREATE INDEX idx_name_age ON users (name, age);`
List indexes	`SHOW INDEX FROM users;`
Drop an index	`DROP INDEX idx_email ON users;`
Add a foreign key	`ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id);`

User & Permission Management

Task	Command
Create a user	`CREATE USER 'app'@'localhost' IDENTIFIED BY 'pass';`
Grant privileges	`GRANT ALL ON mydb.* TO 'app'@'localhost';`
Grant read-only	`GRANT SELECT ON mydb.* TO 'app'@'localhost';`
Apply changes	`FLUSH PRIVILEGES;`
Show grants	`SHOW GRANTS FOR 'app'@'localhost';`
Revoke privileges	`REVOKE ALL ON mydb.* FROM 'app'@'localhost';`
Delete a user	`DROP USER 'app'@'localhost';`

Backup & Restore (CLI)

Task	Command
Back up a database	`mysqldump -u root -p mydb > backup.sql`
Back up all databases	`mysqldump -u root -p --all-databases > all.sql`
Restore a database	`mysql -u root -p mydb < backup.sql`

Golden Rules

Filter your writes — never run UPDATE or DELETE without a WHERE clause unless you truly mean every row.
Index what you filter and join on — indexes speed up reads dramatically but slow down writes, so index deliberately.
Use DECIMAL for money — FLOAT/DOUBLE introduce rounding errors.
Prefer parameterized queries — never concatenate user input into SQL; it's the root cause of SQL injection.
TRUNCATE ≠ DELETE — TRUNCATE is faster, resets auto-increment, and can't be rolled back the same way; DELETE is row-by-row and transactional.

Frequently Asked Questions

What is the difference between DELETE and TRUNCATE in MySQL? DELETE removes rows one at a time and can use a WHERE clause and be rolled back within a transaction. TRUNCATE empties the whole table quickly, resets the auto-increment counter, and can't be filtered.

What is the difference between WHERE and HAVING? WHERE filters individual rows before grouping, while HAVING filters grouped results after GROUP BY. Use HAVING when your condition involves an aggregate like COUNT().

What's the difference between CHAR and VARCHAR? CHAR(n) is fixed-length and pads with spaces, which suits short, uniform values like country codes. VARCHAR(n) stores only the characters used, which is better for variable-length text.

How do I do an upsert (insert or update) in MySQL? Use INSERT ... ON DUPLICATE KEY UPDATE. If a row with the same primary key or unique index exists, MySQL updates it instead of inserting a duplicate.

Does MySQL support FULL OUTER JOIN? Not natively. You emulate it by combining a LEFT JOIN and a RIGHT JOIN with UNION.

When should I add an index? Add indexes to columns you frequently filter (WHERE), join on, or sort by. Avoid over-indexing, since each index adds overhead to inserts and updates.