MySQL Cheat Sheet — MySQL

MySQL Cheatsheet

A comprehensive quick-reference for MySQL. Use this as a daily lookup card — exact syntax for common commands, data types, functions, error codes, and administration tasks organized for fast scanning.

Database Commands

Command	Description
SHOW DATABASES;	List all databases
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;	Create database with charset
USE myapp;	Switch to a database
DROP DATABASE myapp;	Delete a database and all its tables
SHOW CREATE DATABASE myapp;	Show CREATE statement for a database
SELECT DATABASE();	Show currently selected database

Data Types Quick Reference

Type	Storage	Range / Notes	Best Use Case
TINYINT	1 byte	-128 to 127 (signed), 0-255 (UNSIGNED)	Boolean flags, small counters
SMALLINT	2 bytes	-32,768 to 32,767	Age, small IDs
INT	4 bytes	-2.1B to 2.1B	General-purpose integer IDs
BIGINT	8 bytes	-9.2e18 to 9.2e18	Large IDs, timestamps as integers
DECIMAL(p,s)	Variable	Exact decimal, up to 65 digits	Money, financial calculations
FLOAT	4 bytes	~7 significant digits	Scientific data (imprecise)
DOUBLE	8 bytes	~15 significant digits	Scientific data (imprecise)
VARCHAR(n)	1-2 + n bytes	Up to 65,535 bytes total row	Variable-length strings
CHAR(n)	n bytes (padded)	Fixed length, faster for uniform data	Country codes, hashes
TEXT	2 + up to 65KB	No default, no index prefix needed	Short-medium text bodies
MEDIUMTEXT	3 + up to 16MB	Larger text content	Blog posts, HTML content
LONGTEXT	4 + up to 4GB	Very large text	Large documents
DATE	3 bytes	1000-01-01 to 9999-12-31	Date without time
DATETIME	5-8 bytes	1000-01-01 to 9999-12-31	Timestamps without timezone
TIMESTAMP	4 bytes	1970-01-01 to 2038-01-19	Auto-updating row timestamps
JSON	Variable	Up to 1GB, validated on insert	Semi-structured / API data
ENUM	1-2 bytes	Up to 65,535 distinct values	Fixed set of string values
BOOLEAN / TINYINT(1)	1 byte	0 = false, 1 = true	Boolean flags

Table DDL

Command	Description
SHOW TABLES;	List all tables in current database
DESCRIBE users;	Show column definitions for a table
SHOW CREATE TABLE users\G	Show full CREATE TABLE statement
CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100));	Create a table
ALTER TABLE users ADD COLUMN age INT;	Add a column
ALTER TABLE users MODIFY COLUMN name VARCHAR(200) NOT NULL;	Change column definition
ALTER TABLE users DROP COLUMN age;	Remove a column
ALTER TABLE users RENAME TO customers;	Rename a table
TRUNCATE TABLE orders;	Delete all rows, reset AUTO_INCREMENT (DDL)
DROP TABLE IF EXISTS temp_data;	Delete table and all its data

CRUD Commands

SQL

-- SELECT with all common clauses
SELECT id, name, email FROM users WHERE active = 1 ORDER BY name LIMIT 10;

-- INSERT — single and multi-row
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com'), ('Carol', 'carol@example.com');

-- INSERT OR UPDATE on duplicate key
INSERT INTO page_views (page, views) VALUES ('/home', 1)
ON DUPLICATE KEY UPDATE views = views + 1;

-- UPDATE
UPDATE users SET email = 'new@example.com', updated_at = NOW() WHERE id = 42;

-- UPDATE with JOIN
UPDATE orders o JOIN users u ON u.id = o.user_id
SET o.customer_name = u.name WHERE o.customer_name IS NULL;

-- DELETE
DELETE FROM sessions WHERE expires_at < NOW();
DELETE FROM logs WHERE created_at < '2023-01-01' LIMIT 1000;  -- batch delete

String Functions Reference

Function	What it does	Example
LENGTH(s)	Byte length of string	LENGTH('hello') = 5
CHAR_LENGTH(s)	Character count (multi-byte safe)	CHAR_LENGTH('hello') = 5
UPPER(s) / LOWER(s)	Change case	UPPER('hello') = 'HELLO'
TRIM(s)	Remove leading+trailing spaces	TRIM(' hi ') = 'hi'
LTRIM(s) / RTRIM(s)	Remove left or right spaces only	LTRIM(' hi') = 'hi'
CONCAT(s1,s2,...)	Join strings	CONCAT('Hello',' ','World')
CONCAT_WS(sep,...)	Join with separator (skips NULLs)	CONCAT_WS('-','2024','01','15')
SUBSTRING(s,pos,len)	Extract substring	SUBSTRING('abcdef',2,3) = 'bcd'
LEFT(s,n) / RIGHT(s,n)	First or last n characters	LEFT('Hello',3) = 'Hel'
LOCATE(sub,s)	Position of substring (1-indexed)	LOCATE('ll','Hello') = 3
REPLACE(s,from,to)	Replace substring	REPLACE('foo bar','bar','baz')
REGEXP_REPLACE(s,pat,rep)	Replace with regex	REGEXP_REPLACE('abc','[a-z]','X')
FORMAT(n,d)	Number with comma thousands separator	FORMAT(1234567.89,2)
LPAD(s,len,pad)	Left-pad to length	LPAD('42',6,'0') = '000042'
RPAD(s,len,pad)	Right-pad to length	RPAD('hi',5,'.') = 'hi...'
MD5(s) / SHA2(s,bits)	Hash string	SHA2('password',256)

Date and Time Functions Reference

Function	Returns	Example
NOW()	Current datetime	NOW() = '2024-07-03 14:30:00'
CURDATE() / CURRENT_DATE	Current date only	CURDATE() = '2024-07-03'
CURTIME()	Current time only	CURTIME() = '14:30:00'
DATE(dt)	Extract date part	DATE('2024-07-03 14:30') = '2024-07-03'
TIME(dt)	Extract time part	TIME('2024-07-03 14:30') = '14:30:00'
YEAR/MONTH/DAY(dt)	Extract part as integer	YEAR('2024-07-03') = 2024
DATE_FORMAT(dt,fmt)	Format as string	DATE_FORMAT(NOW(),'%Y-%m-%d')
DATE_ADD(dt,INTERVAL n unit)	Add time interval	DATE_ADD(NOW(),INTERVAL 7 DAY)
DATE_SUB(dt,INTERVAL n unit)	Subtract time interval	DATE_SUB(NOW(),INTERVAL 1 MONTH)
DATEDIFF(d1,d2)	Days between dates	DATEDIFF('2024-12-31','2024-01-01') = 365
TIMESTAMPDIFF(unit,d1,d2)	Diff in given unit	TIMESTAMPDIFF(HOUR,start,end)
UNIX_TIMESTAMP(dt)	Convert to Unix epoch	UNIX_TIMESTAMP('2024-01-01')
FROM_UNIXTIME(n)	Convert from Unix epoch	FROM_UNIXTIME(1704067200)
LAST_DAY(dt)	Last day of the month	LAST_DAY('2024-02-01') = '2024-02-29'
WEEKDAY(dt)	0=Monday...6=Sunday	WEEKDAY('2024-07-03') = 2 (Wednesday)

Aggregate Functions Reference

Function	Description	Notes
COUNT(*)	Count all rows (including NULLs)	Fastest count
COUNT(col)	Count non-NULL values in column	Excludes NULLs
COUNT(DISTINCT col)	Count unique non-NULL values	Can be slow on large tables
SUM(col)	Sum of values	Returns NULL if no rows
AVG(col)	Average of non-NULL values	Returns NULL if no rows
MIN(col) / MAX(col)	Minimum / maximum value	Works on strings, dates too
GROUP_CONCAT(col)	Concatenate values into a string	GROUP_CONCAT(name ORDER BY name SEPARATOR ', ')
JSON_ARRAYAGG(col)	Aggregate into JSON array	MySQL 5.7.22+
JSON_OBJECTAGG(k,v)	Aggregate into JSON object	MySQL 5.7.22+
STD(col) / STDDEV_POP(col)	Population standard deviation	Statistical analysis
VARIANCE(col)	Population variance	Statistical analysis
BIT_AND / BIT_OR / BIT_XOR	Bitwise aggregates	Bitmask operations

Window Functions Reference (MySQL 8.0+)

Function	Description	Example Usage
ROW_NUMBER()	Unique sequential number per partition	ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)
RANK()	Rank with gaps for ties	RANK() OVER (ORDER BY score DESC)
DENSE_RANK()	Rank without gaps for ties	DENSE_RANK() OVER (ORDER BY score DESC)
NTILE(n)	Divide into n buckets (quartiles, deciles)	NTILE(4) OVER (ORDER BY salary)
LAG(col,n)	Value n rows before current row	LAG(revenue,1) OVER (ORDER BY month)
LEAD(col,n)	Value n rows after current row	LEAD(revenue,1) OVER (ORDER BY month)
FIRST_VALUE(col)	First value in the window frame	FIRST_VALUE(salary) OVER (PARTITION BY dept ORDER BY hire_date)
LAST_VALUE(col)	Last value in the window frame	LAST_VALUE(salary) OVER (...)
SUM() OVER()	Running total	SUM(amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING)
AVG() OVER()	Moving average	AVG(price) OVER (ORDER BY date ROWS 6 PRECEDING)
PERCENT_RANK()	Relative rank 0.0 to 1.0	PERCENT_RANK() OVER (PARTITION BY dept ORDER BY salary)
CUME_DIST()	Cumulative distribution 0-1	CUME_DIST() OVER (ORDER BY score)

Indexes

Command	Description
CREATE INDEX idx_email ON users (email);	Create a regular B-tree index
CREATE UNIQUE INDEX idx_email ON users (email);	Create a unique index
CREATE INDEX idx_name_city ON users (name, city);	Composite (multi-column) index
CREATE FULLTEXT INDEX idx_body ON posts (body);	Full-text search index
DROP INDEX idx_email ON users;	Remove an index
SHOW INDEX FROM users;	List all indexes on a table
ANALYZE TABLE users;	Update index statistics for the optimizer
OPTIMIZE TABLE users;	Defragment and rebuild — reclaim space after many DELETEs
EXPLAIN SELECT ...;	Show query execution plan (estimated)
EXPLAIN ANALYZE SELECT ...;	Execute query and show real vs estimated stats (8.0+)

Transaction Commands

Command	Description
START TRANSACTION;	Begin an explicit transaction
BEGIN;	Alias for START TRANSACTION
COMMIT;	Permanently save all changes made in this transaction
ROLLBACK;	Undo all changes made in this transaction
SAVEPOINT sp1;	Create a named savepoint within a transaction
ROLLBACK TO SAVEPOINT sp1;	Roll back to a savepoint (keep transaction open)
RELEASE SAVEPOINT sp1;	Remove a savepoint
SET autocommit = 0;	Disable auto-commit for the session
SHOW VARIABLES LIKE "transaction_isolation";	Check current isolation level
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;	Change isolation level for session
SELECT ... FOR UPDATE;	Lock rows for exclusive write access
SELECT ... FOR SHARE;	Lock rows for shared (read) access

User and Privilege Commands

Command	Description
CREATE USER 'app'@'%' IDENTIFIED BY 'pass';	Create a user
GRANT SELECT, INSERT ON myapp.* TO 'app'@'%';	Grant privileges on a database
GRANT ALL PRIVILEGES ON . TO 'dba'@'localhost' WITH GRANT OPTION;	Grant all (for DBA)
REVOKE INSERT ON myapp.* FROM 'app'@'%';	Revoke a specific privilege
SHOW GRANTS FOR 'app'@'%';	View grants for a user
ALTER USER 'app'@'%' IDENTIFIED BY 'newpass';	Change password
ALTER USER 'app'@'%' REQUIRE SSL;	Require SSL for user
DROP USER 'app'@'%';	Delete a user
FLUSH PRIVILEGES;	Reload grant tables (rarely needed in 8.0)
SELECT user, host FROM mysql.user;	List all users
CREATE ROLE app_writer;	Create a role (MySQL 8.0)
GRANT 'app_writer' TO 'app'@'%';	Assign role to user
SET DEFAULT ROLE 'app_writer' TO 'app'@'%';	Auto-activate role on login

Performance and Diagnostics Commands

Command	Description
EXPLAIN SELECT ...;	Show query execution plan (estimated)
EXPLAIN ANALYZE SELECT ...;	Execute and show real vs estimated stats (8.0+)
EXPLAIN FORMAT=JSON SELECT ...;	Machine-readable detailed plan
SHOW PROCESSLIST;	List running queries and connections
KILL QUERY 1234;	Kill a query while keeping the connection
KILL 1234;	Kill a connection entirely
SHOW GLOBAL STATUS LIKE "Slow%";	Show slow query counters
SHOW VARIABLES LIKE "slow_query_log%";	Check slow query log config
SET GLOBAL slow_query_log = ON;	Enable slow query log
SHOW VARIABLES LIKE "innodb_buffer%";	Show InnoDB buffer pool settings
SHOW ENGINE INNODB STATUS\G	Detailed InnoDB internals: locks, transactions, I/O
ANALYZE TABLE orders;	Update table statistics for the optimizer
OPTIMIZE TABLE orders;	Reclaim space and defragment after many DELETEs
SHOW TABLE STATUS LIKE "orders"\G	Table metadata: rows, size, engine, collation

Admin Commands Quick Reference

Command	Description
SHOW VARIABLES LIKE "version%";	Show MySQL server version
SHOW VARIABLES LIKE "datadir";	Show data directory path
SHOW VARIABLES LIKE "max_connections";	Show max allowed connections
SHOW GLOBAL STATUS LIKE "Threads_connected";	Current open connections
SHOW GLOBAL STATUS LIKE "Questions";	Total queries since server start
SET GLOBAL max_connections = 500;	Change max connections at runtime (not persisted)
FLUSH TABLES;	Close all open tables, flush table cache
FLUSH TABLES WITH READ LOCK;	Lock all tables for consistent backup
UNLOCK TABLES;	Release all table locks
RESET BINARY LOGS AND GTIDS;	Delete all binary logs and reset GTIDs (MySQL 8.0)
PURGE BINARY LOGS BEFORE "2024-01-01";	Delete old binary log files
SELECT @@datadir;	Show data directory
SHOW WARNINGS;	Show warnings from last statement
SHOW ERRORS;	Show errors from last statement

Backup and Restore

Bash

# Logical backup — single database
mysqldump -u root -p myapp > myapp_backup.sql

# Logical backup — all databases, with routines and events
mysqldump -u root -p --all-databases --routines --events   --single-transaction > full_backup.sql

# Restore
mysql -u root -p myapp < myapp_backup.sql

# Compress backup
mysqldump -u root -p myapp | gzip > myapp_backup.sql.gz

# Restore compressed backup
gunzip < myapp_backup.sql.gz | mysql -u root -p myapp

# Physical backup with Percona XtraBackup (hot backup, no downtime)
xtrabackup --backup --target-dir=/backup/full
xtrabackup --prepare --target-dir=/backup/full

Replication Commands

Command	Description
SHOW BINARY LOG STATUS\G	Show source binlog position
SHOW REPLICA STATUS\G	Show replica replication status
START REPLICA;	Start replication threads
STOP REPLICA;	Stop replication threads
RESET REPLICA ALL;	Clear all replication config on replica
SHOW REPLICAS;	List connected replicas (run on source)
SHOW BINARY LOGS;	List binary log files and sizes
PURGE BINARY LOGS BEFORE NOW() - INTERVAL 7 DAY;	Clean old binary logs

Common Error Codes Reference

Error Number	Name	Common Cause	Fix
1044	ER_DBACCESS_DENIED_ERROR	User lacks privilege on database	GRANT needed privileges
1045	ER_ACCESS_DENIED_ERROR	Wrong password or user does not exist	Check username, password, host
1046	ER_NO_DB_ERROR	No database selected	Run USE dbname; first
1048	ER_BAD_NULL_ERROR	NULL inserted into NOT NULL column	Provide a value or set a default
1054	ER_BAD_FIELD_ERROR	Unknown column name in query	Check column name and table alias
1062	ER_DUP_ENTRY	Duplicate value violates UNIQUE/PRIMARY KEY	Use INSERT ... ON DUPLICATE KEY or check data
1064	ER_PARSE_ERROR	SQL syntax error	Check SQL syntax near the indicated position
1146	ER_NO_SUCH_TABLE	Table does not exist	Check table name and database
1205	ER_LOCK_WAIT_TIMEOUT	Lock wait timeout exceeded	Investigate blocking transaction; increase innodb_lock_wait_timeout
1213	ER_LOCK_DEADLOCK	Deadlock detected — transaction rolled back	Retry the transaction; check transaction order
1215	ER_CANNOT_ADD_FOREIGN	Cannot add foreign key constraint	Check data types match and referenced rows exist
1364	ER_NO_DEFAULT_FOR_FIELD	Field has no default value and none provided	Provide value or add DEFAULT to column
1366	ER_TRUNCATED_WRONG_VALUE	Incorrect integer/date value	Check data type and strict SQL mode
1406	ER_DATA_TOO_LONG	Data too long for column	Increase column size or truncate data
1451	ER_ROW_IS_REFERENCED_2	Cannot delete: child rows exist (FK)	Delete child rows first or use CASCADE
1452	ER_NO_REFERENCED_ROW_2	Cannot insert: parent row does not exist (FK)	Insert parent row first
2002	CR_CONNECTION_ERROR	Cannot connect to MySQL server	Check host, port, and that MySQL is running
2006	CR_SERVER_GONE_ERROR	MySQL server has gone away	Connection dropped; increase wait_timeout or reconnect

Joins Quick Reference

Join Type	Returns	Use When
INNER JOIN	Only rows with matching values in both tables	Strict relationship — both sides must exist
LEFT JOIN	All rows from left table; NULL for right where no match	Optional relationship — left always included
RIGHT JOIN	All rows from right table; NULL for left where no match	Rare — usually rewrite as LEFT JOIN
CROSS JOIN	Cartesian product — every combination of rows	Generating combinations, test data
SELF JOIN	Table joined to itself (using different aliases)	Hierarchies, employee-manager, adjacency lists
UNION (set op)	Stacks result sets vertically (more rows)	Combining similar data from separate tables

CTEs and Subqueries

SQL

-- CTE (Common Table Expression)
WITH monthly_revenue AS (
  SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, SUM(total) AS revenue
  FROM orders WHERE status = 'completed'
  GROUP BY month
)
SELECT month, revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS growth
FROM monthly_revenue;

-- Recursive CTE (org chart / tree structure)
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.depth + 1
  FROM employees e JOIN org o ON o.id = e.manager_id
)
SELECT * FROM org ORDER BY depth, name;

Isolation Levels Quick Reference

Level	Dirty Read	Non-repeatable Read	Phantom Read	Set Command
READ UNCOMMITTED	Yes	Yes	Yes	SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
READ COMMITTED	No	Yes	Yes	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
REPEATABLE READ	No	No	No*	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
SERIALIZABLE	No	No	No	SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE

InnoDB Key Settings Reference

Variable	Description	Recommended Value
innodb_buffer_pool_size	Main cache for data + indexes	70-80% of available RAM
innodb_flush_log_at_trx_commit	Redo log flush behavior on COMMIT	1 (full ACID), 2 (performance)
innodb_log_file_size	Size of each redo log file (pre-8.0.30)	512MB to 2GB for busy systems
innodb_redo_log_capacity	Total redo log size (MySQL 8.0.30+)	1GB to 8GB for busy systems
innodb_doublewrite	Doublewrite buffer (torn page protection)	ON (always, unless ZFS/hardware atomic writes)
innodb_file_per_table	Separate .ibd file per table	ON (default since MySQL 5.6)
max_connections	Max simultaneous connections	100-500 for typical apps
wait_timeout	Idle connection timeout (seconds)	300-600 for web apps
slow_query_log	Log slow queries	ON with long_query_time = 1
long_query_time	Threshold for slow query log (seconds)	1 (log queries over 1 second)
query_cache_type	Query cache (deprecated in 8.0)	Removed in MySQL 8.0
character_set_server	Default server character set	utf8mb4
collation_server	Default server collation	utf8mb4_unicode_ci

Note

Keep this page bookmarked — use it to look up exact syntax without digging through the full tutorial pages.