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

1. Filter your writes — never run UPDATE or DELETE without a WHERE clause unless you truly mean every row.
2. Index what you filter and join on — indexes speed up reads dramatically but slow down writes, so index deliberately.
3. Use DECIMAL for money — FLOAT/DOUBLE introduce rounding errors.
4. Prefer parameterized queries — never concatenate user input into SQL; it's the root cause of SQL injection.
5. 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.