SQL 语法速查表

SELECT id, name, created_at FROM users

按列查询，生产环境别用 SELECT *，否则加字段会牵动所有调用方

SELECT * FROM users WHERE id = 1

查全部列，只适合临时排查

SELECT DISTINCT city FROM users

去重，注意它会对整行去重而不是单列

SELECT user_name AS name FROM users

给列或表达式起别名

SELECT id FROM orders ORDER BY created_at DESC

排序，DESC 降序、ASC 升序（默认）

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

多列排序，先按 a 再按 b；分页时务必加唯一列兜底

SELECT id FROM orders ORDER BY id LIMIT 10 OFFSET 20

分页，取第 21 到 30 行

SELECT TOP 10 id FROM orders ORDER BY id DESC

SQL Server 的取前 N 行写法

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

标准 SQL 的取前 N 行，Oracle 与新版 PG 都支持

SELECT email FROM users UNION SELECT email FROM leads

合并结果并去重，列数与类型必须一致

SELECT id FROM a UNION ALL SELECT id FROM b

合并但不去重，比 UNION 快很多

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

公用表表达式，把复杂查询拆成可读的步骤

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

递归 CTE，查树形或图结构

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

条件表达式，在结果里做分支

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

显式类型转换，比隐式转换安全

SELECT COALESCE(nickname, user_name) FROM users

取第一个非 NULL 值，处理空值最常用

SELECT CONCAT(first_name, surname) FROM users

字符串拼接，MySQL 与 PG 也支持 || 运算符

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

加行号（详见窗口函数组）

WHERE status = 1

等值筛选

WHERE status <> 0

不等筛选，也可写 !=

WHERE status IN (1, 2, 5)

多值匹配，比多个 OR 好读

WHERE id NOT IN (SELECT user_id FROM bans)

排除子查询结果，子查询返回 NULL 时整个条件会变空集

WHERE created_at BETWEEN 2 AND 8

闭区间包含两端，日期用 BETWEEN 会漏掉当天末尾时刻

WHERE email LIKE 'admin%'

前缀匹配，能用到索引

WHERE remark LIKE '%退货%'

包含匹配，前导通配符用不上索引

WHERE deleted_at IS NULL

判空必须用 IS NULL，= NULL 永远不成立

WHERE email IS NOT NULL

非空判断，也可用于过滤未赋值字段

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

括号决定优先级：AND 先于 OR，不写括号极易出逻辑错

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

存在性判断，通常比 IN 更快且不受 NULL 影响

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

时间范围推荐用左闭右开，不要对列套函数

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

内连接，只保留两边都匹配的行

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

左连接，保留左表全部行，右表缺失补 NULL

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

全外连接，两边不匹配的行都保留

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

笛卡尔积，生成组合矩阵时故意这么写

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

找左表有而右表没有的行（差集）

SELECT * FROM users JOIN profiles USING (user_id)

同名列连接，结果里该列只出现一次

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

自连接，查层级关系

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

左连接的多余条件必须写在 ON 里；写进 WHERE 会退化成内连接

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

按列分组统计

SELECT COUNT(*) FROM orders

统计行数，包含 NULL 行

SELECT COUNT(email) FROM users

统计该列非 NULL 的行数，和 COUNT(*) 结果可能不同

SELECT COUNT(DISTINCT user_id) FROM orders

统计去重后的数量

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

求和、平均、最小、最大；AVG 忽略 NULL 会让分母变小

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

分组后筛选，找重复数据最常用

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

MySQL 把分组内多个值拼成一个字符串，PG 用 STRING_AGG 并指定分隔符

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

条件聚合，一次扫描算多个指标；MySQL 用 SUM(cond) 替代

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

自动补小计与总计行

SELECT city, status FROM orders GROUP BY city, status

多列分组；SELECT 里的非聚合列必须都出现在 GROUP BY 中

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

组内排名，不并列，是「每组取最新一条」的核心

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

并列排名，跳过后续名次（1,2,2,4）

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

并列排名，不跳号（1,2,2,3）

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

取上一行的值，算环比与差值

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

取下一行的值

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

累计求和，默认窗口是从开头到当前行

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

组内平均，不折叠行数，可与明细并列显示

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

把结果均分成 N 桶，分位数与分层抽样用

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

组内第一行的值

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

取组内最后一行；不写完整窗口帧会得到当前行而非末行

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

取每组最新一条的标准写法，必须套一层子查询

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

窗口函数在 WHERE 时还未求值，必须先物化成子查询或 CTE

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

插入一行或多行，多行写成多个括号更快

INSERT INTO archive SELECT * FROM orders WHERE created_at < 2025

从查询结果批量插入

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

PostgreSQL 的 upsert：存在则更新，不存在则插入

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

MySQL 的 upsert 写法

UPDATE users SET status = 1 WHERE id = 10

更新指定行，WHERE 漏写会更新全表

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

PostgreSQL 的连接更新；MySQL 写 JOIN，SQL Server 写 FROM

DELETE FROM sessions WHERE expires_at < NOW()

按条件删除

TRUNCATE TABLE staging_rows

清空整表，比 DELETE 快且重置自增值，但不可回滚（多数库）

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)

标准 SQL 的合并写入，Oracle 与 SQL Server 支持

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

MySQL 特有：先删后插，自增主键会被改变，触发器行为也和 upsert 不同

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

建表，主键与非空是最基本的约束

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

外键约束，保证引用完整性但会影响写入性能

CREATE UNIQUE INDEX uk_email ON users (email)

唯一约束，也能阻止并发写入重复值

ALTER TABLE users ADD COLUMN phone VARCHAR(20)

加字段，大表加非空默认值字段可能锁表

ALTER TABLE users DROP COLUMN phone

删字段

ALTER TABLE users ALTER COLUMN phone TYPE VARCHAR(30)

改字段类型（PostgreSQL 写法；MySQL 用 MODIFY COLUMN）

ALTER TABLE users RENAME TO app_users

改表名

DROP TABLE IF EXISTS tmp_import

删表，加 IF EXISTS 避免脚本重跑报错

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

视图，只存查询定义不存数据

CREATE TABLE users_backup AS SELECT * FROM users

按查询结果建表，约束与索引不会带过来

CREATE INDEX idx_created ON orders (created_at)

普通索引，加速查询但拖慢写入

CREATE INDEX idx_user_time ON orders (user_id, created_at)

复合索引遵守最左前缀：只查 b 用不上它

DROP INDEX idx_created

删索引（SQL Server 需写 DROP INDEX t.idx）

EXPLAIN SELECT * FROM orders WHERE user_id = 1

查看执行计划，重点看 type 与 key（MySQL）或 Seq Scan（PG）

EXPLAIN ANALYZE SELECT COUNT(*) FROM orders

真正执行并给出实际耗时与行数，比 EXPLAIN 准

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

覆盖索引，把要查的列带上，免去回表

BEGIN

开启事务（也可写 START TRANSACTION）

COMMIT

提交事务

ROLLBACK

回滚事务

SAVEPOINT sp1

设置保存点，可部分回滚

ROLLBACK TO SAVEPOINT sp1

回滚到保存点，之前的操作仍保留

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

设置隔离级别：读未提交/读已提交/可重复读/串行化

SELECT * FROM accounts WHERE id = 1 FOR UPDATE

加写锁，防并发超卖；必须在事务内使用

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

跳过已加锁的行，做任务队列抢占的标准写法

SELECT * FROM t WHERE deleted_at = NULL

NULL 表示未知，与任何值比较结果都是未知，必须用 IS NULL

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

子查询返回哪怕一个 NULL，NOT IN 就会返回空集，改用 NOT EXISTS

SELECT * FROM users WHERE phone = 13800000000

隐式类型转换会让索引失效，参数类型必须与列类型一致

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

对列套函数会让索引失效，改写成范围条件

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

OR 跨列时单列索引都用不上，可拆成两次查询再 UNION

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

深分页要先扫描再丢弃前面所有行，改用游标式翻页

DELETE FROM t WHERE created_at < 2024

漏写 WHERE 会清空整表；执行前先用同条件的 SELECT 验证

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

排序值有并列时顺序不确定，分页会漏行或重复，务必加唯一列兜底

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

大事务会长时间持锁、撑爆回滚段，改成小批多次提交

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

MySQL 默认排序规则不区分大小写，换库后行为可能相反