MySQL Cheat Sheet

01Connect & Sessioncommand-line client

mysql -u user -p★
Connect, prompts for password.
mysql -u user -p db
Connect directly into a database.
mysql -h host -P 3306 -u user -p
Remote host + non-default port.
SHOW DATABASES;★
List databases on the server.
SELECT DATABASE();
Which database is currently active.
STATUS;
Server version, charset, uptime, socket.
SOURCE file.sql
Run a .sql script from disk.
exit / quit
Leave the client.

02DatabasesDDL

CREATE DATABASE db;★
Create a new database (schema).
CREATE DATABASE IF NOT EXISTS db;
Idempotent create — no error if it exists.
USE db;★
Set the active database for the session.
ALTER DATABASE db CHARACTER SET utf8mb4;
Change default charset/collation.
DROP DATABASE db;destroys
Delete database + every table in it.

03Create & Inspect TablesDDL

CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100));★
Define columns, types & keys in one go.
SHOW TABLES;★
List tables in the active database.
DESCRIBE t;★
Columns, types, nullability, keys, defaults.
SHOW CREATE TABLE t;
Exact DDL MySQL would use to rebuild it.
SHOW COLUMNS FROM t LIKE '%name%';
Filter column metadata by pattern.
RENAME TABLE old TO new;
Rename a table in place.
DROP TABLE t;destroys
Delete the table + its data + structure.
DROP TABLE IF EXISTS t;
Safe drop — no error if absent.

04Alter Tablereshape existing structure

ALTER TABLE t ADD col VARCHAR(50);★
Add a new column.
ALTER TABLE t MODIFY col INT NOT NULL;
Change a column's type/constraints, same name.
ALTER TABLE t CHANGE old new INT;
Rename a column while retyping it.
ALTER TABLE t RENAME COLUMN old TO new;
Rename only — type stays the same (8.0+).
ALTER TABLE t DROP COLUMN c;destroys
Remove a column and its data.
ALTER TABLE t ADD PRIMARY KEY (id);
Add a primary key after the fact.
ALTER TABLE t ENGINE=InnoDB;
Convert storage engine.

05Data Typeswhat a column can hold

Numeric

TINYINT	1 byte · ±127 (0–255 unsigned)
SMALLINT / MEDIUMINT	2 / 3 bytes
INT	4 bytes · ±2.1 billion
BIGINT	8 bytes · ±9.2×10¹⁸
DECIMAL(M,D)	exact fixed-point — use for money
FLOAT / DOUBLE	approximate, floating-point

String

CHAR(M)	fixed length, padded, ≤255
VARCHAR(M)	variable length, ≤65,535 bytes/row share
TEXT / LONGTEXT	64KB → 4GB of text
ENUM('a','b')	one value from a fixed list
BLOB / LONGBLOB	binary data, same size tiers as TEXT

Date & Time

DATE	YYYY-MM-DD
DATETIME	YYYY-MM-DD HH:MM:SS, no TZ conversion
TIMESTAMP	like DATETIME, UTC-stored, 1970–2038
TIME / YEAR	HH:MM:SS / YYYY

Other

BOOLEAN	alias for TINYINT(1)
JSON	validated JSON document (5.7+)
GEOMETRY / POINT	spatial types (needs SRID for GIS work)

06Insert DataDML

INSERT INTO t (a,b) VALUES (1,2);★
Insert one row into named columns.
INSERT INTO t (a,b) VALUES (1,2),(3,4);★
Insert multiple rows in one statement.
INSERT INTO t2 SELECT * FROM t1;
Copy rows from one table into another.
INSERT IGNORE INTO t VALUES (...);
Skip rows that would error (dup key etc).
INSERT INTO t (a) VALUES (1) ON DUPLICATE KEY UPDATE a=a+1;
Upsert — update if the key already exists.
REPLACE INTO t VALUES (...);
Delete-then-insert on key conflict.

07Select — BasicsDQL

SELECT * FROM t;★
Every column, every row.
SELECT a, b FROM t;★
Only the columns you need — cheaper.
SELECT DISTINCT col FROM t;
Unique values only.
SELECT price*1.1 AS with_tax FROM t;
Computed column with an alias.
SELECT * FROM t AS x;
Table alias — handy in joins.
SELECT * FROM t LIMIT 10;★
Cap the number of rows returned.

08Filter Rows — WHEREDQL

... WHERE age > 21;★
Comparison: = < > <= >= != (or <>).
... WHERE a=1 AND b=2;★
Combine conditions — also OR, NOT.
... WHERE age BETWEEN 18 AND 30;
Inclusive range check.
... WHERE status IN ('new','open');★
Match any value in a list.
... WHERE name LIKE 'A%';★
Pattern match — % any run, _ one char.
... WHERE deleted_at IS NULL;★
NULL check — never use = NULL.
... WHERE col <=> NULL;
NULL-safe equality (MySQL-specific).

09Sort & PaginateDQL

... ORDER BY created_at;★
Ascending by default.
... ORDER BY created_at DESC;★
Newest / largest first.
... ORDER BY a, b DESC;
Multi-key sort, mixed directions.
... LIMIT 20 OFFSET 40;★
Page 3 of 20-row pages.
... LIMIT 40, 20;
Same as above — offset, count shorthand.
... ORDER BY RAND() LIMIT 1;
One random row — fine for small tables.

10JoinsDQL · combine tables

... FROM a JOIN b ON a.id=b.a_id;★
INNER JOIN — only matching rows both sides.
... FROM a LEFT JOIN b ON a.id=b.a_id;★
All of a, matched b or NULLs.
... FROM a RIGHT JOIN b ON a.id=b.a_id;
All of b, matched a or NULLs.
... FROM a CROSS JOIN b;
Every row of a × every row of b.
... FROM emp e1 JOIN emp e2 ON e1.mgr_id=e2.id;
Self join — same table, two aliases.
SELECT... LEFT JOIN... UNION SELECT... RIGHT JOIN...;
Emulates FULL OUTER JOIN (no native support).

11Group & AggregateDQL · roll rows up

SELECT dept, COUNT(*) FROM t GROUP BY dept;★
One output row per distinct group.
... GROUP BY dept HAVING COUNT(*) > 5;★
Filter groups — runs after grouping.
COUNT(*), SUM(x), AVG(x), MIN(x), MAX(x)★
The five core aggregate functions.
GROUP_CONCAT(name SEPARATOR ', ')
Concatenate group values into one string.
... GROUP BY dept WITH ROLLUP;
Adds subtotal / grand-total rows.

12Update & DeleteDML · handle with care

UPDATE t SET status='done' WHERE id=7;★
Change matching rows — always use WHERE.
UPDATE a JOIN b ON a.id=b.a_id SET a.x=b.y;
Multi-table update via join.
DELETE FROM t WHERE id=7;★
Remove matching rows, logged & rollback-able.
UPDATE t SET status='done';no WHERE
Updates every row in the table.
DELETE FROM t;no WHERE
Deletes every row in the table.
TRUNCATE TABLE t;destroys
Wipes all rows instantly; resets AUTO_INCREMENT; not row-logged.

13Keys & ConstraintsDDL · data integrity

id INT AUTO_INCREMENT PRIMARY KEY★
Unique row identity, auto-numbered.
email VARCHAR(120) UNIQUE★
No two rows may share this value.
name VARCHAR(50) NOT NULL★
Column may never be NULL.
status VARCHAR(10) DEFAULT 'new'
Fallback value when none supplied.
FOREIGN KEY (user_id) REFERENCES users(id)★
Value must exist in the parent table.
... ON DELETE CASCADE
Auto-delete children when the parent goes.
CHECK (price >= 0)
Row-level validation rule (8.0.16+).

14Indexes & EXPLAINDDL · speed up reads

CREATE INDEX idx_email ON t(email);★
Speed up lookups/filters on a column.
CREATE UNIQUE INDEX idx ON t(sku);
Index that also enforces uniqueness.
CREATE INDEX idx ON t(last, first);
Composite index — order of columns matters.
SHOW INDEX FROM t;
List indexes and their cardinality.
DROP INDEX idx ON t;care
Removes the index — reads slow down.
EXPLAIN SELECT * FROM t WHERE ...;★
Shows whether MySQL uses an index or scans.

15TransactionsTCL · InnoDB only

START TRANSACTION;★
Begin a block of atomic statements.
COMMIT;★
Make all changes in the block permanent.
ROLLBACK;★
Undo everything since the transaction began.
SAVEPOINT sp1;
Named point to roll back to, mid-transaction.
ROLLBACK TO sp1;
Undo only back to that savepoint.
SET autocommit = 0;
Turn off per-statement auto-commit for the session.

16Users & PrivilegesDCL · access control

CREATE USER 'u'@'localhost' IDENTIFIED BY 'pw';★
Create a new login. '%' host = any host.
GRANT ALL PRIVILEGES ON db.* TO 'u'@'localhost';★
Full rights on every table in db.
GRANT SELECT, INSERT ON db.t TO 'u'@'localhost';
Grant only specific privileges on one table.
SHOW GRANTS FOR 'u'@'localhost';
List a user's current privileges.
REVOKE INSERT ON db.t FROM 'u'@'localhost';
Remove a specific privilege.
ALTER USER 'u'@'localhost' IDENTIFIED BY 'newpw';
Change a user's password.
FLUSH PRIVILEGES;★
Reload grant tables after manual edits.
DROP USER 'u'@'localhost';destroys
Delete the account entirely.

17ViewsDDL · saved queries

CREATE VIEW active_users AS SELECT * FROM u WHERE active=1;★
Name a query — query it like a table.
CREATE OR REPLACE VIEW v AS ...;
Redefine an existing view.
SHOW FULL TABLES WHERE Table_type = 'VIEW';
List views in the database.
DROP VIEW v;removes
Delete the view (underlying data is untouched).

18Backup, Restore & Importshell · not run inside mysql>

mysqldump -u u -p db > backup.sql★
Export a full database to a .sql file.
mysqldump -u u -p --all-databases > all.sql
Dump every database on the server.
mysql -u u -p db < backup.sql★
Restore a dump into an existing database.
LOAD DATA INFILE 'f.csv' INTO TABLE t FIELDS TERMINATED BY ',';
Bulk-load a CSV — much faster than INSERTs.
SELECT * FROM t INTO OUTFILE '/tmp/t.csv';
Export a query result straight to a file.

19Common Functionsstring / date / numeric

CONCAT(first,' ',last)★
Join strings together.
UPPER(s) / LOWER(s) / TRIM(s)
Case & whitespace cleanup.
SUBSTRING(s, pos, len)
Extract part of a string.
NOW() / CURDATE() / CURTIME()★
Current timestamp / date / time.
DATE_ADD(d, INTERVAL 7 DAY)
Date arithmetic — also DATE_SUB.
DATEDIFF(d1, d2)
Whole days between two dates.
ROUND(x, 2) / ABS(x) / CEIL(x)
Numeric rounding & sign helpers.
IFNULL(x, 'default')★
Substitute a value when x is NULL.
CAST(x AS UNSIGNED)
Convert between types explicitly.

★Operators & Wildcardsused throughout WHERE / LIKE

= <=> != <> < > <= >=
Standard + NULL-safe equality.
%
LIKE wildcard: zero or more characters.
_
LIKE wildcard: exactly one character.
AND · OR · NOT
Boolean logic, evaluated in that precedence.
IN (...) · NOT IN (...)
Set membership tests.
IS NULL · IS NOT NULL
The only correct way to test for NULL.

mysql cheat sheet

Join types at a glance

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL OUTER (via UNION)

CROSS JOIN

Worth memorizing