Programming · Cheatsheet

MySQL Reference Cheatsheet

MySQL queries, joins, indexes, user management, and database administration

64 commands 9 sections

Connection & Basics

CREATE DATABASE dbname CHARACTER SET utf8mb4; Create database with UTF-8 support
DROP DATABASE dbname; Delete a database
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255)); Create a table
DROP TABLE table_name; Delete a table
TRUNCATE TABLE table_name; Delete all rows (faster than DELETE)
RENAME TABLE old_name TO new_name; Rename a table
ALTER TABLE users ADD email VARCHAR(255) AFTER name; Add column after specific column
ALTER TABLE users DROP COLUMN email; Remove a column
ALTER TABLE users MODIFY name VARCHAR(500); Change column type

SELECT * FROM users; Select all columns and rows
SELECT name, email FROM users WHERE id = 1; Select specific columns with filter
SELECT DISTINCT status FROM orders; Select unique values only
SELECT * FROM users ORDER BY created_at DESC; Sort results descending
SELECT * FROM users LIMIT 10 OFFSET 20; Paginate results (page 3)
SELECT * FROM users WHERE name LIKE "%john%"; Pattern matching search
SELECT * FROM users WHERE id IN (1, 2, 3); Filter by list of values
SELECT * FROM users WHERE age BETWEEN 18 AND 30; Filter by range
SELECT * FROM users WHERE email IS NOT NULL; Filter for non-null values

INSERT INTO users (name, email) VALUES ('Alice', '[email protected]'); Insert a single row
INSERT INTO users (name) VALUES ('A'), ('B'), ('C'); Insert multiple rows
INSERT INTO target SELECT * FROM source; Insert from another table
UPDATE users SET name = 'Bob' WHERE id = 1; Update specific rows
UPDATE users SET status = "active" WHERE last_login > NOW() - INTERVAL 30 DAY; Update with date condition
DELETE FROM users WHERE id = 1; Delete specific rows
DELETE FROM logs WHERE created_at < "2025-01-01"; Delete old records
INSERT INTO ... ON DUPLICATE KEY UPDATE name = VALUES(name); Upsert (insert or update)

SELECT * FROM orders INNER JOIN users ON orders.user_id = users.id; Inner join (matching rows only)
SELECT * FROM users LEFT JOIN orders ON users.id = orders.user_id; Left join (all from left table)
SELECT * FROM orders RIGHT JOIN users ON orders.user_id = users.id; Right join (all from right table)
SELECT * FROM t1 CROSS JOIN t2; Cross join (cartesian product)
SELECT * FROM users u JOIN orders o ON u.id = o.user_id JOIN items i ON o.id = i.order_id; Multi-table join
SELECT * FROM employees e JOIN employees m ON e.manager_id = m.id; Self join

SELECT COUNT(*) FROM users; Count all rows
SELECT status, COUNT(*) FROM users GROUP BY status; Count by group
SELECT AVG(price), SUM(price) FROM orders; Average and sum
SELECT MAX(salary), MIN(salary) FROM employees; Max and min values
SELECT status, COUNT(*) FROM orders GROUP BY status HAVING COUNT(*) > 10; Filter groups with HAVING
SELECT YEAR(created_at), COUNT(*) FROM users GROUP BY YEAR(created_at); Group by date part

CREATE INDEX idx_email ON users(email); Create an index on column
CREATE UNIQUE INDEX idx_email ON users(email); Create unique index
CREATE INDEX idx_name_email ON users(name, email); Create composite index
DROP INDEX idx_email ON users; Delete an index
SHOW INDEX FROM users; List indexes on a table
EXPLAIN SELECT * FROM users WHERE email = "[email protected]"; Analyze query execution plan
EXPLAIN ANALYZE SELECT * FROM users; Execute and show actual timing
SHOW PROCESSLIST; Show active queries and connections

https://dev.mysql.com/doc/refman/8.4/en/ MySQL 8.4 manual: SQL syntax, indexes, accounts, and administration.