SQL Cheat Sheet

SELECT id, name, created_at FROM users

Select named columns; avoid SELECT * so new columns do not break callers.

SELECT * FROM users WHERE id = 1

Select every column; fine for ad-hoc checks only.

SELECT DISTINCT city FROM users

Distinct rows; note it applies to the whole selected row.

SELECT user_name AS name FROM users

Alias a column or expression.

SELECT id FROM orders ORDER BY created_at DESC

Sort descending; ASC is the default.

SELECT id, score FROM exam ORDER BY score DESC, id ASC

Multi-column sort; always add a unique tiebreaker when paginating.

SELECT id FROM orders ORDER BY id LIMIT 10 OFFSET 20

Pagination: rows 21 through 30.

SELECT TOP 10 id FROM orders ORDER BY id DESC

SQL Server syntax for taking the first N rows.

SELECT id FROM orders ORDER BY id FETCH FIRST 10 ROWS ONLY

Standard SQL for the first N rows; Oracle and newer PostgreSQL.

SELECT email FROM users UNION SELECT email FROM leads

Union with deduplication; column counts and types must match.

SELECT id FROM a UNION ALL SELECT id FROM b

Union without deduplication; much faster than UNION.

WITH paid AS (SELECT * FROM orders WHERE status = 1) SELECT COUNT(*) FROM paid

Common table expression; breaks a complex query into readable steps.

WITH RECURSIVE tree AS (SELECT id, parent_id FROM nodes WHERE parent_id IS NULL UNION ALL SELECT n.id, n.parent_id FROM nodes n JOIN tree t ON n.parent_id = t.id) SELECT * FROM tree

Recursive CTE for tree and graph structures.

SELECT CASE WHEN score >= 60 THEN 1 ELSE 0 END AS passed FROM exam

Conditional expression inside the result set.

SELECT CAST(price AS DECIMAL(10,2)) FROM orders

Explicit cast, safer than relying on implicit conversion.

SELECT COALESCE(nickname, user_name) FROM users

First non-null value; the usual way to handle nulls.

SELECT CONCAT(first_name, surname) FROM users

String concatenation; PostgreSQL and MySQL also accept the || operator.

SELECT ROW_NUMBER() OVER (ORDER BY id) AS rn FROM users

Add a row number; see the window group.

WHERE status = 1

Equality filter.

WHERE status <> 0

Not-equal filter; != also works.

WHERE status IN (1, 2, 5)

Match any of several values; clearer than chained ORs.

WHERE id NOT IN (SELECT user_id FROM bans)

Exclusion by subquery; a NULL in the subquery empties the whole result.

WHERE created_at BETWEEN 2 AND 8

Inclusive on both ends; with dates it drops the tail of the end day.

WHERE email LIKE 'admin%'

Prefix match, which can use an index.

WHERE remark LIKE '%退货%'

Contains match; the leading wildcard prevents index use.

WHERE deleted_at IS NULL

Test for null with IS NULL; = NULL never matches.

WHERE email IS NOT NULL

Not-null test.

WHERE status = 1 AND (type = 2 OR type = 3)

Parentheses decide precedence; AND binds tighter than OR.

WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)

Existence test; usually faster than IN and immune to NULL.

WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01'

Use a half-open range; never wrap the column in a function.

SELECT u.name, o.amount FROM users u INNER JOIN orders o ON u.id = o.user_id

Inner join, keeping only matched rows on both sides.

SELECT u.name, o.amount FROM users u LEFT JOIN orders o ON u.id = o.user_id

Left join keeps all left rows, filling NULL for missing right rows.

SELECT * FROM a FULL OUTER JOIN b ON a.id = b.a_id

Full outer join keeps unmatched rows from both sides.

SELECT s.size, c.color FROM sizes s CROSS JOIN colors c

Cartesian product, useful for building combination matrices.

SELECT u.* FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE o.id IS NULL

Find rows in the left table with no match on the right (anti-join).

SELECT * FROM users JOIN profiles USING (user_id)

Join on same-named columns; the column appears once in the result.

SELECT e.name, m.name AS manager FROM emp e JOIN emp m ON e.mgr_id = m.id

Self join for hierarchical relationships.

SELECT u.name, o.amount FROM users u LEFT JOIN orders o ON u.id = o.user_id AND o.status = 1

Extra conditions on a left join belong in ON; in WHERE they turn it into an inner join.

SELECT city, COUNT(*) FROM users GROUP BY city

Group rows and count.

SELECT COUNT(*) FROM orders

Count rows, including rows with nulls.

SELECT COUNT(email) FROM users

Count non-null values; may differ from COUNT(*).

SELECT COUNT(DISTINCT user_id) FROM orders

Count distinct values.

SELECT SUM(amount), AVG(amount) FROM orders

Sum, average, min, max; AVG skips nulls and shrinks the denominator.

SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1

Filter after grouping; the usual way to find duplicates.

SELECT GROUP_CONCAT(name SEPARATOR ', ') FROM users GROUP BY city

MySQL aggregation into a string; PostgreSQL uses STRING_AGG.

SELECT COUNT(*) FILTER (WHERE status = 1) AS paid FROM orders

Conditional aggregation in one scan; MySQL uses SUM(condition).

SELECT city, status, COUNT(*) FROM orders GROUP BY ROLLUP (city, status)

Adds subtotal and grand total rows.

SELECT city, status FROM orders GROUP BY city, status

Multi-column grouping; non-aggregated select columns must all be grouped.

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn

Row number within a partition; the core of the latest-per-group pattern.

RANK() OVER (ORDER BY score DESC) AS r

Rank with gaps for ties (1,2,2,4).

DENSE_RANK() OVER (ORDER BY score DESC) AS r

Rank without gaps (1,2,2,3).

LAG(amount, 1, 0) OVER (ORDER BY created_at) AS prev_amount

Previous row value, for deltas and period-over-period.

LEAD(amount) OVER (ORDER BY created_at) AS next_amount

Next row value.

SUM(amount) OVER (ORDER BY created_at) AS running_total

Running total; the default frame spans from the start to the current row.

AVG(amount) OVER (PARTITION BY user_id) AS user_avg

Average within a partition without collapsing rows.

NTILE(4) OVER (ORDER BY amount DESC) AS quartile

Split into N buckets, for quantiles and stratified sampling.

FIRST_VALUE(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS first_amount

Value from the first row in the partition.

LAST_VALUE(amount) OVER (PARTITION BY user_id ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_amount

Last row in the partition; without the full frame you get the current row.

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) rn FROM orders) x WHERE rn = 1

The standard latest-per-group query; it needs an outer subquery.

SELECT * FROM (SELECT id, ROW_NUMBER() OVER (PARTITION BY g ORDER BY t) rn FROM t) x WHERE rn <= 3

Window functions are not evaluated yet in WHERE; materialize them in a subquery first.

INSERT INTO users (name, email) VALUES ('Ada', 'ada@example.com')

Insert one or many rows; batching values is faster.

INSERT INTO archive SELECT * FROM orders WHERE created_at < 2025

Bulk insert from a query.

INSERT INTO s (id, v) VALUES (1, 10) ON CONFLICT (id) DO UPDATE SET v = EXCLUDED.v

PostgreSQL upsert: update on conflict, insert otherwise.

INSERT INTO s (id, v) VALUES (1, 10) ON DUPLICATE KEY UPDATE v = VALUES(v)

The MySQL upsert form.

UPDATE users SET status = 1 WHERE id = 10

Update matching rows; omitting WHERE updates everything.

UPDATE orders o SET price = p.price FROM products p WHERE o.product_id = p.id

PostgreSQL join update; MySQL uses JOIN, SQL Server uses FROM.

DELETE FROM sessions WHERE expires_at < NOW()

Delete matching rows.

TRUNCATE TABLE staging_rows

Empty a table faster than DELETE and reset identity; usually not rollback-able.

MERGE INTO target t USING source s ON t.id = s.id WHEN MATCHED THEN UPDATE SET t.v = s.v WHEN NOT MATCHED THEN INSERT VALUES (s.id, s.v)

Standard SQL merge; supported by Oracle and SQL Server.

REPLACE INTO s (id, v) VALUES (1, 10)

MySQL only: delete then insert; auto-increment changes and triggers fire differently.

CREATE TABLE users (id BIGINT PRIMARY KEY, name VARCHAR(50) NOT NULL, created_at TIMESTAMP DEFAULT NOW())

Create a table; primary key and NOT NULL are the basics.

CREATE TABLE orders (id BIGINT PRIMARY KEY, user_id BIGINT, CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id))

Foreign key for referential integrity at a write-cost.

CREATE UNIQUE INDEX uk_email ON users (email)

Unique constraint; also blocks duplicate concurrent writes.

ALTER TABLE users ADD COLUMN phone VARCHAR(20)

Add a column; on large tables a NOT NULL default may lock.

ALTER TABLE users DROP COLUMN phone

Drop a column.

ALTER TABLE users ALTER COLUMN phone TYPE VARCHAR(30)

Change a column type; MySQL spells it MODIFY COLUMN.

ALTER TABLE users RENAME TO app_users

Rename a table.

DROP TABLE IF EXISTS tmp_import

Drop a table; IF EXISTS keeps scripts idempotent.

CREATE VIEW v_paid AS SELECT * FROM orders WHERE status = 1

View: stores the query, not the data.

CREATE TABLE users_backup AS SELECT * FROM users

Create a table from a query; constraints and indexes are not copied.

CREATE INDEX idx_created ON orders (created_at)

Plain index: speeds reads, slows writes.

CREATE INDEX idx_user_time ON orders (user_id, created_at)

Composite index follows the leftmost prefix rule; a query on b alone cannot use it.

DROP INDEX idx_created

Drop an index; SQL Server spells it DROP INDEX table.index.

EXPLAIN SELECT * FROM orders WHERE user_id = 1

Show the plan; watch type and key in MySQL, Seq Scan in PostgreSQL.

EXPLAIN ANALYZE SELECT COUNT(*) FROM orders

Actually runs the query with real timings and row counts.

CREATE INDEX idx_user ON orders (user_id) INCLUDE (amount)

Covering index: carry the selected column to avoid table lookups.

BEGIN

Start a transaction (START TRANSACTION also works).

COMMIT

Commit the transaction.

ROLLBACK

Roll back the transaction.

SAVEPOINT sp1

Set a savepoint for partial rollback.

ROLLBACK TO SAVEPOINT sp1

Roll back to a savepoint, keeping earlier work.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Set isolation: read uncommitted, read committed, repeatable read, serializable.

SELECT * FROM accounts WHERE id = 1 FOR UPDATE

Take a write lock against lost updates; must be inside a transaction.

SELECT * FROM jobs WHERE status = 0 ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED

Skip locked rows; the standard queue-worker pattern.

SELECT * FROM t WHERE deleted_at = NULL

NULL means unknown; comparisons with it are unknown, so use IS NULL.

SELECT * FROM a WHERE id NOT IN (SELECT a_id FROM b)

A single NULL in the subquery empties NOT IN; use NOT EXISTS.

SELECT * FROM users WHERE phone = 13800000000

Implicit conversion disables the index; match parameter and column types.

WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02'

A function on the column disables the index; rewrite as a range.

SELECT * FROM t WHERE a = 1 UNION SELECT * FROM t WHERE b = 2

OR across columns prevents single-column index use; split and UNION.

SELECT * FROM t WHERE id > 100000 ORDER BY id LIMIT 20

Deep offsets scan and discard everything before; use keyset pagination.

DELETE FROM t WHERE created_at < 2024

A missing WHERE empties the table; verify with a matching SELECT first.

SELECT id, name FROM t ORDER BY score DESC, id ASC LIMIT 20

Ties make ordering non-deterministic and pagination unstable; add a unique tiebreaker.

-- 改成每批 1000 行循环提交 DELETE FROM logs WHERE created_at < 2024 LIMIT 1000;

Huge transactions hold locks and bloat rollback segments; batch them.

SELECT * FROM t WHERE name = 'Ada' -- 大小写敏感取决于 collation

MySQL collations are often case-insensitive; another database may not be.