MySQL Cheat Sheet
Practical MySQL reference covering connections, SELECT, JOINs, GROUP BY, DDL, indexes, and backup with mysqldump.
MySQL is the world's most widely deployed open-source relational database. Quote identifiers with backticks when they collide with reserved words, prefer utf8mb4 over the legacy utf8, and use prepared statements to keep user input out of query strings.
Connecting
The mysql client reads connection options from flags, environment variables (MYSQL_HOST, MYSQL_USER, MYSQL_PWD), or an option file under ~/.my.cnf. Never pass a password as -p on the command line — it leaks into shell history.
# Prompt-based password
mysql -u app -p -h db.example.com -P 3306 mydb
# Connection via URI (MySQL 8+ shell)
mysql "mysql://app:secret@db.example.com:3306/mydb?ssl-mode=REQUIRED"
# Non-interactive query
mysql -u app -p'mypass' -e "SELECT COUNT(*) FROM users" mydb
# Source a script file
mysql -u app -p mydb < schema.sql
| Flag | Effect |
|---|---|
-h / -P | Host / port |
-u | User |
-p | Prompt for password (omit value) |
-D | Default schema after connect |
--ssl-mode=REQUIRED | Force TLS to the server |
-e | Execute a single statement and exit |
SELECT, WHERE, ORDER, LIMIT
The basic read pattern is filter, sort, and cap the rows. Always add an ORDER BY when using LIMIT in paginated code, otherwise the engine is free to return any rows it likes.
SELECT id, name, email, created_at
FROM users
WHERE active = 1
AND created_at >= '2026-01-01'
ORDER BY created_at DESC
LIMIT 25 OFFSET 50;
-- Find rows where name starts with a prefix (uses index)
SELECT * FROM users WHERE name LIKE 'ada%';
-- Pagination keyset (faster than OFFSET for large tables)
SELECT * FROM users
WHERE id > 1000
ORDER BY id ASC
LIMIT 25;
| Clause | Purpose |
|---|---|
WHERE | Filter rows before grouping |
GROUP BY | Collapse rows for aggregation |
HAVING | Filter groups after aggregation |
ORDER BY ... ASC|DESC | Sort the result set |
LIMIT N OFFSET M | Return at most N rows starting at M |
DISTINCT | Drop duplicate rows |
JOINs
JOIN combines rows from two tables on a related column. Pick the join that preserves the rows you actually need — INNER JOIN drops unmatched rows, LEFT JOIN keeps them with NULLs from the right side.
-- Each user with their latest order (if any)
SELECT u.id, u.name, o.id AS order_id, o.total
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE u.active = 1;
-- Three-way: users, orders, line items
SELECT u.name, o.id AS order_id, li.product_id, li.quantity
FROM users AS u
INNER JOIN orders AS o ON o.user_id = u.id
INNER JOIN order_items AS li ON li.order_id = o.id
WHERE o.status = 'paid';
| Join | Behavior |
|---|---|
INNER JOIN | Only rows that match on both sides |
LEFT JOIN | All left rows, right columns NULL if no match |
RIGHT JOIN | All right rows, left columns NULL if no match |
CROSS JOIN | Cartesian product — use sparingly |
LEFT JOIN ... WHERE right.id IS NULL | Anti-join: rows on the left with no match |
GROUP BY, HAVING, and aggregates
Aggregates collapse many rows into one. HAVING filters groups, WHERE filters rows — putting a group-level condition in WHERE is the most common beginner mistake.
SELECT
category,
COUNT(*) AS product_count,
SUM(price) AS total_value,
AVG(price) AS avg_price,
MIN(price) AS cheapest,
MAX(price) AS priciest
FROM products
WHERE discontinued = 0
GROUP BY category
HAVING product_count >= 5
ORDER BY avg_price DESC;
| Aggregate | Returns |
|---|---|
COUNT(*) | Row count including NULLs |
COUNT(col) | Non-NULL values in col |
SUM, AVG | Numeric aggregates (NULLs ignored) |
MIN, MAX | Smallest / largest value |
GROUP_CONCAT(col) | Concatenated string per group |
INSERT, UPDATE, DELETE
Always wrap write statements in an explicit transaction when more than one row is changing. Use INSERT ... ON DUPLICATE KEY UPDATE for upsert semantics and REPLACE INTO only when you genuinely want delete-then-insert.
-- Single-row insert
INSERT INTO users (name, email) VALUES ('Ada', 'ada@example.com');
-- Batch insert
INSERT INTO users (name, email) VALUES
('Grace', 'grace@example.com'),
('Linus', 'linus@example.com');
-- Upsert (MySQL-specific)
INSERT INTO users (id, name, email)
VALUES (1, 'Ada', 'ada@example.com')
ON DUPLICATE KEY UPDATE name = VALUES(name), email = VALUES(email);
-- Update with a filter
UPDATE users SET last_login_at = NOW() WHERE id = 1;
-- Delete with a limit (safety rail)
DELETE FROM sessions WHERE created_at < NOW() - INTERVAL 30 DAY LIMIT 10000;
DDL: CREATE TABLE and data types
Pick the smallest type that fits the data. VARCHAR(255) is rarely correct for names; DATETIME does not store time zone, TIMESTAMP does and is converted to UTC on storage.
CREATE TABLE products (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
sku VARCHAR(64) NOT NULL,
name VARCHAR(255) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL DEFAULT 0,
stock INT NOT NULL DEFAULT 0,
released_on DATE,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uq_sku (sku)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
| Type | Use for |
|---|---|
TINYINT, SMALLINT, INT, BIGINT | Integer columns; UNSIGNED doubles the upper bound |
DECIMAL(p, s) | Money — never use FLOAT for currency |
VARCHAR(n) | Variable-length strings up to n chars |
TEXT | Long strings (articles, JSON blobs) |
DATE, DATETIME, TIMESTAMP, TIME | Calendar types; pick by precision and TZ needs |
JSON | Native JSON with indexed paths |
ENUM | Fixed set of labels |
Quote identifiers with backticks if they collide with reserved words: SELECT `key`, `order` FROM `my table`;.
Indexes and EXPLAIN
Indexes trade write speed for read speed. Use EXPLAIN to confirm the planner picks your index; a sequential scan on a million-row table means a missing index or a bad query shape.
-- B-tree index on a single column
CREATE INDEX idx_users_created_at ON users (created_at);
-- Composite index respects leftmost-prefix rule
CREATE INDEX idx_orders_user_status ON orders (user_id, status);
-- Inspect the query plan
EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';
EXPLAIN column | Meaning |
|---|---|
type | Join type: system, const, eq_ref, ref, range, index, ALL |
possible_keys | Candidate indexes |
key | Index actually chosen |
rows | Estimated rows examined |
Extra | Using filesort, Using temporary, Using index |
Backup and restore with mysqldump
mysqldump produces a logical SQL dump. It is fine for small databases and migrations; for large ones use mysqlpump, mydumper, or physical backups like Percona XtraBackup.
# Dump a single database
mysqldump -u root -p --single-transaction --routines --triggers mydb > mydb.sql
# Dump all databases
mysqldump -u root -p --all-databases --single-transaction > alldb.sql
# Dump schema only, or data only
mysqldump -u root -p --no-data mydb > schema.sql
mysqldump -u root -p --no-create-info mydb > data.sql
# Restore
mysql -u root -p mydb < mydb.sql
| Flag | Why it matters |
|---|---|
--single-transaction | Consistent snapshot for InnoDB without locking |
--routines | Include stored procedures and functions |
--triggers | Include triggers (on by default) |
--add-drop-table | Prepend DROP TABLE (handy for restores) |
--column-statistics=0 | Workaround for old MySQL ↔ new mysqldump |
Useful built-in functions
Date math and string handling come up in nearly every application. Reach for DATE_FORMAT rather than formatting in application code; reach for JSON_EXTRACT instead of pulling JSON to the client.
-- Date / time
SELECT NOW(), CURDATE(), CURTIME();
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);
SELECT DATEDIFF('2026-12-31', '2026-01-01');
-- Strings
SELECT CONCAT(first_name, ' ', last_name) AS full_name;
SELECT UPPER(name), LOWER(email), LENGTH(description);
SELECT TRIM(name), REPLACE(name, 'Inc.', 'Incorporated');
-- JSON
SELECT JSON_EXTRACT(meta, '$.plan') AS plan FROM users;
SELECT meta->>'$.plan' AS plan FROM users;
-- Control flow
SELECT IFNULL(nickname, name) AS display_name FROM users;
SELECT CASE status WHEN 'paid' THEN 1 WHEN 'refunded' THEN -1 ELSE 0 END AS s
FROM orders;