MySQL Cheat Sheet
12 groups · 45 topics · Updated
MySQL is a relational database: data lives in tables of rows and columns, and you ask for it in SQL. The examples are written for MySQL 8.0 or later. For the document-shaped alternative, see the MongoDB cheat sheet.
Getting started
Connecting, and the handful of commands that show you what is there.
Connect
mysql -u root -p
mysql -u app -p -h db.example.com shop
mysql -u app -p shop < dump.sql- Never put the password after
-p: it lands in your shell history. shopis the database to open on connect.
Look around
SHOW DATABASES;
USE shop;
SHOW TABLES;
DESCRIBE orders; -- columns and types
SHOW CREATE TABLE orders\G
SHOW INDEX FROM orders;
SELECT VERSION(), DATABASE(), NOW();\Gprints one field per line instead of a wide table.
Databases and tables
CREATE DATABASE shop
CHARACTER SET utf8mb4;
DROP DATABASE shop; -- no undo
CREATE TABLE t LIKE orders; -- structure
TRUNCATE TABLE logs; -- empty it, fast
DROP TABLE logs;- Always use
utf8mb4: the olderutf8cannot store emoji.
Comments and quoting
-- a line comment (note the space)
# also a line comment
/* a block comment */
SELECT 'text', "also text", `column name`;| Quote | Means |
|---|---|
'...' |
A string value |
`...` |
A table or column name |
"..." |
A string, by default |
Types and tables
Choosing a column type, and the rules that keep the data honest.
Numbers
| Type | Holds |
|---|---|
TINYINT |
−128 to 127 |
INT |
About ±2.1 billion |
BIGINT |
About ±9.2 quintillion |
UNSIGNED |
Zero and up, double the top |
DECIMAL(10,2) |
Exact: use it for money |
DOUBLE |
Approximate: never for money |
BOOLEAN |
Alias for TINYINT(1) |
Text, dates and the rest
| Type | Holds |
|---|---|
CHAR(2) |
Fixed length, padded |
VARCHAR(255) |
Text up to a length |
TEXT |
Long text, not fully indexable |
DATE |
2026-11-19 |
DATETIME |
Date and time, no time zone |
TIMESTAMP |
UTC, converted on read |
ENUM('new','paid') |
One of a fixed list |
JSON |
A validated JSON document |
Create a table
CREATE TABLE orders (
id BIGINT UNSIGNED NOT NULL
AUTO_INCREMENT,
customer_id BIGINT UNSIGNED NOT NULL,
total DECIMAL(10,2) NOT NULL,
status ENUM('new','paid','sent')
NOT NULL DEFAULT 'new',
created_at TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY idx_customer (customer_id),
CONSTRAINT fk_customer
FOREIGN KEY (customer_id)
REFERENCES customers (id)
ON DELETE CASCADE
) ENGINE=InnoDB;Constraints
| Constraint | Effect |
|---|---|
PRIMARY KEY |
Unique, not null, one per table |
UNIQUE |
No two rows share the value |
NOT NULL |
A value is required |
DEFAULT x |
Used when none is given |
FOREIGN KEY |
Must match a row elsewhere |
CHECK (n > 0) |
Rejects rows that fail |
Change a table
ALTER TABLE orders
ADD COLUMN note VARCHAR(255) NULL,
MODIFY COLUMN total DECIMAL(12,2),
ADD UNIQUE KEY uq_ref (reference),
DROP COLUMN note,
RENAME COLUMN note TO comment;
RENAME TABLE orders TO sales_orders;ALTERon a huge table can lock it; test on a copy first.
Reading rows
SELECT and the clauses that narrow, order and cut down its result.
SELECT
SELECT * FROM orders;
SELECT id, total FROM orders;
SELECT total * 1.2 AS gross FROM orders;
SELECT DISTINCT status FROM orders;- Ask for the columns you need;
*is for the command line.
WHERE
WHERE status = 'paid'
WHERE total >= 100 AND total < 500
WHERE status IN ('new', 'paid')
WHERE total BETWEEN 10 AND 20
WHERE note IS NULL
WHERE email LIKE '%@example.com'
WHERE name REGEXP '^a'
WHERE NOT (status = 'sent')| Operator | Matches |
|---|---|
=, <> |
Equal, not equal |
LIKE '%a_' |
% any run, _ one character |
IS NULL |
Nothing: = NULL never matches |
<=> |
Equal, and NULL equals NULL |
Order, limit and page
SELECT id, total FROM orders
ORDER BY total DESC, id ASC
LIMIT 20 OFFSET 40; -- page 3 of 20- A large
OFFSETstill reads every skipped row. For deep paging, remember the last id and useWHERE id > ?.
Handling NULL
SELECT IFNULL(note, '-') FROM orders;
SELECT COALESCE(a, b, 'none') FROM t;
SELECT NULLIF(total, 0) FROM orders;- Any arithmetic with
NULLgivesNULL, includingNULL = NULL.
CASE
SELECT id,
CASE
WHEN total > 500 THEN 'large'
WHEN total > 100 THEN 'medium'
ELSE 'small'
END AS size
FROM orders;Changing rows
Writing data, and the clauses that stop one typo emptying a table.
INSERT
INSERT INTO orders (customer_id, total)
VALUES (7, 99.50);
INSERT INTO orders (customer_id, total)
VALUES (7, 10.00), (8, 20.00);
SELECT LAST_INSERT_ID(); -- the new id- One
INSERTwith many rows is far faster than manyINSERTs.
UPDATE and DELETE
UPDATE orders
SET status = 'paid', total = total + 5
WHERE id = 42;
DELETE FROM orders WHERE id = 42;- Both without
WHEREchange every row. Run theWHEREas aSELECTfirst. SET sql_safe_updates = 1;blocks an unkeyedUPDATEorDELETE.
Insert or update
INSERT INTO stock (sku, qty)
VALUES ('abc', 5) AS new
ON DUPLICATE KEY UPDATE
qty = stock.qty + new.qty;
REPLACE INTO stock (sku, qty)
VALUES ('abc', 5); -- deletes, reinsertsON DUPLICATE KEYneeds aUNIQUEor primary key to collide with.
Copy rows in
INSERT INTO orders_2025 (id, total)
SELECT id, total FROM orders
WHERE created_at < '2026-01-01';Joins
Pulling columns from more than one table into a single result.
The four you need
SELECT o.id, c.name
FROM orders o
INNER JOIN customers c
ON c.id = o.customer_id;| Join | Keeps |
|---|---|
INNER JOIN |
Only rows matching on both sides |
LEFT JOIN |
All left rows, NULL if no match |
RIGHT JOIN |
All right rows, the mirror image |
CROSS JOIN |
Every pair: rarely what you want |
Rows with no match
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id
WHERE o.id IS NULL; -- never orderedJoin a table to itself
SELECT e.name, m.name AS manager
FROM staff e
LEFT JOIN staff m ON m.id = e.manager_id;Combine results
SELECT email FROM customers
UNION -- drops duplicates
SELECT email FROM leads;
SELECT email FROM customers
UNION ALL -- keeps them, faster
SELECT email FROM leads;- Both sides need the same number of columns, in the same order.
Grouping and windows
Turning many rows into one number per group, or a running figure per row.
Aggregate functions
SELECT COUNT(*) FROM orders;
SELECT COUNT(note) FROM orders; -- skips NULL
SELECT SUM(total) FROM orders;
SELECT AVG(total) FROM orders;
SELECT MIN(total), MAX(total) FROM orders;
SELECT GROUP_CONCAT(id) FROM orders;GROUP BY and HAVING
SELECT status, COUNT(*) AS n,
SUM(total) AS revenue
FROM orders
WHERE created_at >= '2026-01-01'
GROUP BY status
HAVING revenue > 1000
ORDER BY revenue DESC;WHEREfilters rows before grouping,HAVINGfilters groups after.- Every selected column must be grouped or aggregated.
Subqueries
SELECT * FROM orders
WHERE total > (SELECT AVG(total)
FROM orders);
SELECT * FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.id);EXISTSstops at the first match, so it beatsINon big lists.
CTEs
WITH paid AS (
SELECT * FROM orders
WHERE status = 'paid'
)
SELECT customer_id, SUM(total)
FROM paid
GROUP BY customer_id;- A CTE names a result once and reads top to bottom, unlike a nested subquery.
Window functions
SELECT id, customer_id, total,
ROW_NUMBER() OVER w AS rn,
RANK() OVER w AS rnk,
SUM(total) OVER w AS running
FROM orders
WINDOW w AS (PARTITION BY customer_id
ORDER BY created_at);| Function | Gives |
|---|---|
ROW_NUMBER() |
1, 2, 3, no ties |
RANK() |
Ties share, then a gap |
DENSE_RANK() |
Ties share, no gap |
LAG(x), LEAD(x) |
The previous or next row's x |
Dates, strings and JSON
The built-in functions that come up most often.
Dates
SELECT NOW(), CURDATE(), UTC_TIMESTAMP();
SELECT DATE(created_at) FROM orders;
SELECT YEAR(d), MONTH(d), DAYOFWEEK(d);
SELECT DATE_ADD(d, INTERVAL 7 DAY);
SELECT DATE_SUB(d, INTERVAL 1 MONTH);
SELECT DATEDIFF(a, b); -- in days
SELECT DATE_FORMAT(d, '%Y-%m-%d %H:%i');- Wrapping an indexed column in a function stops the index being used.
Strings
SELECT CONCAT(first, ' ', last) FROM people;
SELECT CONCAT_WS(', ', city, country);
SELECT UPPER(s), LOWER(s), LENGTH(s);
SELECT CHAR_LENGTH(s); -- characters
SELECT TRIM(s), SUBSTRING(s, 1, 5);
SELECT REPLACE(s, 'a', 'b');
SELECT LOCATE('@', email); -- 1-basedNumbers
SELECT ROUND(1.567, 2); -- 1.57
SELECT CEIL(1.2), FLOOR(1.8);
SELECT ABS(-3), MOD(7, 2), POW(2, 10);
SELECT GREATEST(1, 9), LEAST(1, 9);
SELECT CAST('12' AS SIGNED);JSON columns
SELECT meta->>'$.colour' FROM products;
SELECT JSON_EXTRACT(meta, '$.sizes[0]');
UPDATE products
SET meta = JSON_SET(meta, '$.colour', 'red')
WHERE id = 1;
SELECT * FROM products
WHERE JSON_CONTAINS(meta->'$.tags', '"new"');->returns JSON,->>returns the unquoted text.- Index a JSON field through a generated column; a plain
JSONcolumn cannot be indexed directly.
Indexes and speed
How MySQL finds rows quickly, and how to see what it actually did.
Create an index
CREATE INDEX idx_status ON orders (status);
CREATE UNIQUE INDEX uq_email
ON customers (email);
CREATE INDEX idx_cs
ON orders (customer_id, status);
DROP INDEX idx_status ON orders;Leftmost prefix
-- index on (customer_id, status)
WHERE customer_id = 7 -- used
WHERE customer_id = 7 AND status=? -- used
WHERE status = 'paid' -- not used- A composite index works left to right, like a phone book sorted by surname then first name.
What stops an index
| Written as | Problem |
|---|---|
WHERE YEAR(d) = 2026 |
Column wrapped in a function |
WHERE total + 1 > 5 |
Column in an expression |
WHERE name LIKE '%x' |
Leading wildcard |
WHERE id = '7' |
Type mismatch, int against text |
OR across columns |
Often scans; try UNION |
Read the plan
EXPLAIN SELECT * FROM orders
WHERE status = 'paid';
EXPLAIN ANALYZE SELECT ...; -- real timingtype |
Meaning |
|---|---|
const, eq_ref |
One row by key: ideal |
ref, range |
Index used: good |
index |
Whole index scanned |
ALL |
Whole table scanned: check it |
Housekeeping
ANALYZE TABLE orders; -- refresh stats
OPTIMIZE TABLE orders; -- reclaim space
SHOW FULL PROCESSLIST; -- what is running
KILL 1234; -- stop a queryTransactions
Grouping several statements so they all happen, or none do.
Commit or roll back
START TRANSACTION;
UPDATE accounts SET bal = bal - 100
WHERE id = 1;
UPDATE accounts SET bal = bal + 100
WHERE id = 2;
COMMIT; -- or ROLLBACK;- InnoDB only.
MyISAMhas no transactions, which is one reason not to use it.
ACID
| Letter | Promise |
|---|---|
| Atomic | All of it, or none of it |
| Consistent | Constraints hold at the end |
| Isolated | Concurrent work does not mix |
| Durable | A commit survives a crash |
Isolation levels
| Level | Allows |
|---|---|
READ UNCOMMITTED |
Dirty reads |
READ COMMITTED |
Non-repeatable reads |
REPEATABLE READ |
The InnoDB default |
SERIALIZABLE |
Nothing, by locking most |
Locking a row
START TRANSACTION;
SELECT qty FROM stock
WHERE sku = 'abc' FOR UPDATE;
UPDATE stock SET qty = qty - 1
WHERE sku = 'abc';
COMMIT;FOR UPDATEholds the row until commit, so two buyers cannot both take the last item.- Keep transactions short: long ones block others and risk deadlocks.
Users and safety
Who may do what, how to avoid injection, and how to take a backup.
Users and grants
CREATE USER 'app'@'%'
IDENTIFIED BY 'a-long-passphrase';
GRANT SELECT, INSERT, UPDATE, DELETE
ON shop.* TO 'app'@'%';
REVOKE DELETE ON shop.* FROM 'app'@'%';
SHOW GRANTS FOR 'app'@'%';
DROP USER 'app'@'%';- Give the application account only the rights it uses; never let it run as
root.
Placeholders, not string building
// Node.js, mysql2
const [rows] = await pool.execute(
"SELECT * FROM users WHERE email = ?",
[email],
);<?php
$stmt = $pdo->prepare(
'SELECT * FROM users WHERE email = ?'
);
$stmt->execute([$email]);- Concatenating user input into SQL is how injection happens. See how to secure a website and OWASP's SQL injection prevention guidance.
- Store passwords with a slow hash, never in a plain column.
Backup and restore
mysqldump -u root -p shop > shop.sql
mysqldump -u root -p \
--single-transaction --routines shop \
> shop.sql
mysql -u root -p shop < shop.sql--single-transactiondumps InnoDB without locking writers out.- A backup you have never restored is not yet a backup.
Watch out
The slips that cost an afternoon.
Common mistakes
| Mistake | Fix |
|---|---|
UPDATE with no WHERE |
Select it first; sql_safe_updates |
= NULL matches nothing |
IS NULL |
FLOAT for money |
DECIMAL(10,2) |
utf8 drops emoji |
utf8mb4 |
WHERE YEAR(d) = 2026 |
A range on d |
COUNT(col) skips nulls |
COUNT(*) |
LIMIT with no ORDER BY |
Order is not guaranteed |
Deep OFFSET pages crawl |
Page by last id |
OR kills an index |
UNION of two queries |
| Query inside a loop | One query with IN or a join |
SELECT * everywhere |
Name the columns |
Reusing root in an app |
A least-privilege user |
More help
The official pages to check details against.
Official documentation
| Topic | Page |
|---|---|
| Reference | MySQL 8.0 Reference Manual |
| SQL syntax | SQL statements |
| Functions | Functions and operators |
| Indexes | Optimization and indexes |
| InnoDB | The InnoDB storage engine |
| Injection | OWASP SQL injection prevention |