MySQL cheat sheet: the commands you use every day

This is a quick reference for the MySQL you actually type: connecting, reading, summarising, changing and tuning data. It is a cheat sheet, so it is short on explanation and long on working examples. Every query that shows output was run on MySQL 8.4 against a small customers and orders dataset (defined in the SQL joins cheat sheet), so the output is real. For the reasoning behind each command, the free MySQL course has a lesson on each.

Connect and look around

-- from the shell
mysql -u root -p                  -- prompt for the password
mysql -h 127.0.0.1 -P 3306 -u app -p shopdb

SHOW DATABASES;
USE shopdb;
SHOW TABLES;
DESCRIBE customers;               -- columns and types
SHOW CREATE TABLE customers\G     -- the full CREATE statement
SELECT VERSION(), DATABASE(), CURRENT_USER();

Create and change structure

CREATE DATABASE shopdb CHARACTER SET utf8mb4;

CREATE TABLE tags (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  label VARCHAR(20) UNIQUE,
  uses  INT DEFAULT 1
);

ALTER TABLE tags ADD COLUMN note TEXT;
ALTER TABLE tags MODIFY label VARCHAR(40) NOT NULL;
ALTER TABLE tags DROP COLUMN note;
CREATE INDEX idx_orders_customer ON orders (customer_id);
DROP TABLE IF EXISTS tags;       -- gone for good

Common data types: INT, BIGINT, DECIMAL(10,2) (money), VARCHAR(n), TEXT, DATE, DATETIME, TIMESTAMP, BOOLEAN (alias of TINYINT(1)), JSON.

SELECT, filter, sort, page

SELECT name, city FROM customers
WHERE city IN ('Pune','Goa')
ORDER BY name DESC
LIMIT 2 OFFSET 1;

name	city
Chen	Pune
Asha	Pune

Goal	Syntax
Range	WHERE total BETWEEN 100 AND 300
Several values	WHERE city IN ('Pune','Goa')
Pattern	WHERE name LIKE 'A%' (% any text, _ one character)
Regex	WHERE name REGEXP '^[A-C]'
Missing value	WHERE customer_id IS NULL
Unique values	SELECT DISTINCT city FROM customers
Combine	AND, OR, NOT (use brackets)

LIKE 'A%' OR LIKE '_e%' returned Asha, Ben and Dev on our data. REGEXP '^[A-C]' returned Asha, Ben and Chen.

NULL: the rules

SELECT NULL = NULL AS eq, NULL IS NULL AS isnull, 1 + NULL AS plus,
       COUNT(customer_id) AS cnt_col, COUNT(*) AS cnt_all
FROM orders;

eq	isnull	plus	cnt_col	cnt_all
NULL	1	NULL	4	5

• Comparing with NULL gives NULL, so use IS NULL / IS NOT NULL.
• Arithmetic with NULL gives NULL.
• COUNT(column) skips NULLs; COUNT(*) counts rows.

Group and aggregate

SELECT status, COUNT(*) AS n, SUM(total) AS sum_total,
       ROUND(AVG(total),2) AS avg_total, MIN(total) AS lo, MAX(total) AS hi
FROM orders
GROUP BY status
HAVING COUNT(*) >= 1
ORDER BY n DESC, status;

status	n	sum_total	avg_total	lo	hi
paid	3	430.50	143.50	60.00	250.00
cancelled	1	500.00	500.00	500.00	500.00
pending	1	80.00	80.00	80.00	80.00

WHERE filters rows before grouping; HAVING filters groups after. WITH ROLLUP adds a grand-total row:

SELECT status, COUNT(*) n FROM orders GROUP BY status WITH ROLLUP;

status	n
cancelled	1
paid	3
pending	1
NULL	5

Functions you reach for

Text

SELECT name, UPPER(name) u, LENGTH(name) len, CONCAT(name,' - ',city) label,
       SUBSTRING(name,1,2) s2, REPLACE(city,'Pune','Pune City') r
FROM customers LIMIT 1;

name	u	len	label	s2	r
Asha	ASHA	4	Asha - Pune	As	Pune City

Also: TRIM(), LEFT(), RIGHT(), LOWER(), CHAR_LENGTH() (characters; LENGTH counts bytes), LOCATE().

Numbers

SELECT ROUND(2.567,1) r, CEIL(2.1) c, FLOOR(2.9) f, MOD(17,5) m, ABS(-4) a, TRUNCATE(2.789,2) t;

r	c	f	m	a	t
2.6	3	2	2	4	2.78

Dates

SELECT DATE_FORMAT('2026-10-24','%d %b %Y') f, DATE_ADD('2026-10-24', INTERVAL 30 DAY) plus30,
       DATEDIFF('2026-12-25','2026-10-24') days_left, YEAR('2026-10-24') y,
       DAYNAME('2026-10-24') dn, LAST_DAY('2026-02-10') ld;

f	plus30	days_left	y	dn	ld
24 Oct 2026	2026-11-23	62	2026	Saturday	2026-02-28

Also: NOW(), CURDATE(), DATE_SUB(), TIMESTAMPDIFF(unit, a, b), STR_TO_DATE().

Conditions

SELECT id, total,
       IF(total >= 200,'big','small') size,
       CASE WHEN status='paid' THEN 'ok' WHEN status='pending' THEN 'wait' ELSE 'no' END state,
       IFNULL(customer_id,0) cid, COALESCE(customer_id,-1) c2
FROM orders;

id	total	size	state	cid	c2
101	250.00	big	ok	1	1
102	120.50	small	ok	1	1
103	80.00	small	wait	2	2
104	60.00	small	ok	0	-1
105	500.00	big	no	3	3

Joins and subqueries (short form)

SELECT c.name, o.total FROM customers c JOIN orders o ON o.customer_id = c.id;   -- matches only
SELECT c.name, o.total FROM customers c LEFT JOIN orders o ON o.customer_id = c.id; -- keep all customers
SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE total > 200);
SELECT name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

The first subquery returns Asha and Chen. The details and traps are in the SQL joins cheat sheet.

CTEs and window functions

A CTE names a subquery so the main query reads top to bottom:

WITH big AS (SELECT customer_id, SUM(total) s FROM orders GROUP BY customer_id)
SELECT c.name, big.s FROM customers c JOIN big ON big.customer_id = c.id ORDER BY big.s DESC;

name	s
Chen	500.00
Asha	370.50
Ben	80.00

A window function calculates over related rows without collapsing them:

SELECT id, total,
       ROW_NUMBER() OVER (ORDER BY total DESC) AS rn,
       RANK() OVER (ORDER BY status)           AS rk,
       SUM(total) OVER ()                      AS grand
FROM orders;

id	total	rn	rk	grand
105	500.00	1	1	1010.50
101	250.00	2	2	1010.50
102	120.50	3	2	1010.50
104	60.00	5	2	1010.50
103	80.00	4	5	1010.50

Notice RANK(): three rows tie at rank 2 (status paid), and the next rank jumps to 5. DENSE_RANK() would give 3 instead. ROW_NUMBER() never ties. Add PARTITION BY to restart the numbering per group.

Insert, update, delete, upsert

INSERT INTO tags (label) VALUES ('php'),('sql');
INSERT INTO tags (label) VALUES ('php')
  ON DUPLICATE KEY UPDATE uses = uses + 1;      -- upsert
UPDATE tags SET uses = uses * 10 WHERE label = 'sql';
DELETE FROM tags WHERE label = 'php';

After the upsert, php had uses = 2; after the update and delete only sql remained, with uses = 10. A golden rule: write the WHERE first, and test it with a SELECT before running an UPDATE or DELETE. Without WHERE, every row changes.

One detail worth knowing: the upsert used up an AUTO_INCREMENT value even though it inserted nothing. SHOW CREATE TABLE tags afterwards showed AUTO_INCREMENT=4 with only ids 1 and 2 in the table. Auto-increment numbers are unique, not gap-free.

Transactions

START TRANSACTION;
UPDATE orders SET status = 'paid' WHERE id = 103;
ROLLBACK;                       -- or COMMIT;
SELECT status FROM orders WHERE id = 103;   -- still 'pending'

A transaction makes several statements succeed or fail together. It needs a transactional engine (InnoDB, the default).

Indexes and EXPLAIN

CREATE INDEX idx_orders_customer ON orders (customer_id);
EXPLAIN SELECT * FROM orders WHERE customer_id = 1;

EXPLAIN showed type = ref, key = idx_orders_customer and rows = 2: MySQL used the index and expected to read two rows. Without an index you would see type = ALL, a full table scan. Index columns used in WHERE, JOIN and ORDER BY; every index slows writes a little and uses space.

Users, backup and restore

CREATE USER 'app'@'localhost' IDENTIFIED BY 'a-long-secret';
GRANT SELECT, INSERT, UPDATE ON shopdb.* TO 'app'@'localhost';
SHOW GRANTS FOR 'app'@'localhost';

mysqldump -u root -p shopdb > shopdb.sql        # backup
mysql -u root -p shopdb < shopdb.sql             # restore

Give applications the fewest privileges they need, and never connect a website as root.

Order of a SELECT

Written order: SELECT … FROM … JOIN … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT. MySQL evaluates roughly FROM/JOIN, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. This is why a column alias works in ORDER BY but not in WHERE.

More

Practise with the lessons in the MySQL course, follow the 60-day plan, test yourself with the MySQL interview questions, and prepare for the exam with MySQL certificate: how the final exam works. From code, connect with PDO as shown in the PHP course, and never build queries by joining user input into strings.