Skip to content
ReadMe Daily

Search articles, problems, patterns and every cheat sheet topic.

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.

1

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.
  • shop is 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();
  • \G prints 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 older utf8 cannot 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
2

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;
  • ALTER on a huge table can lock it; test on a copy first.
3

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 OFFSET still reads every skipped row. For deep paging, remember the last id and use WHERE id > ?.

Handling NULL

SELECT IFNULL(note, '-') FROM orders;
SELECT COALESCE(a, b, 'none') FROM t;
SELECT NULLIF(total, 0) FROM orders;
  • Any arithmetic with NULL gives NULL, including NULL = NULL.

CASE

SELECT id,
  CASE
    WHEN total > 500 THEN 'large'
    WHEN total > 100 THEN 'medium'
    ELSE 'small'
  END AS size
FROM orders;
4

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 INSERT with many rows is far faster than many INSERTs.

UPDATE and DELETE

UPDATE orders
SET status = 'paid', total = total + 5
WHERE id = 42;
 
DELETE FROM orders WHERE id = 42;
  • Both without WHERE change every row. Run the WHERE as a SELECT first.
  • SET sql_safe_updates = 1; blocks an unkeyed UPDATE or DELETE.

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, reinserts
  • ON DUPLICATE KEY needs a UNIQUE or 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';
5

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 ordered

Join 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.
6

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;
  • WHERE filters rows before grouping, HAVING filters 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);
  • EXISTS stops at the first match, so it beats IN on 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
7

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-based

Numbers

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 JSON column cannot be indexed directly.
8

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 timing
type 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 query
9

Transactions

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. MyISAM has 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 UPDATE holds the row until commit, so two buyers cannot both take the last item.
  • Keep transactions short: long ones block others and risk deadlocks.
10

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]);

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-transaction dumps InnoDB without locking writers out.
  • A backup you have never restored is not yet a backup.
11

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
12

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