速查 12 · SQL

按问题索引。Ctrl+F 页内搜索。以 PostgreSQL 语法为主，MySQL 差异已标注。 系统学习见 10 · 数据建模与数据访问。 本文是给前端/全栈用的"能读懂、能写对、能优化"最小 SQL 集，重点是看懂 ORM 生成的 SQL。

目录

• SELECT 基础与别名
• WHERE 过滤
• 排序与分页
• JOIN 联表
• GROUP BY 与聚合
• 子查询
• 窗口函数
• INSERT / UPDATE / DELETE
• 事务与锁
• 索引与 EXPLAIN
• 常用函数速查
• 与 ORM 对照

---

SELECT 基础与别名

-- 查所有列（联表/大表别用 *，会拖垮网络与内存）
SELECT * FROM users;

-- 只查需要的列 + 别名（AS 可省略）
SELECT id, name AS 用户名 FROM users;

-- 去重
SELECT DISTINCT status FROM orders;

-- 常量列 / 计算列
SELECT id, price * quantity AS total FROM order_items;

---

WHERE 过滤

-- 等值 / 范围
WHERE status = 'paid'
WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01'
WHERE price BETWEEN 10 AND 100

-- IN / NOT IN / IS NULL（注意：NULL 不能用 = 判断）
WHERE id IN (1, 2, 3)
WHERE deleted_at IS NULL

-- 模糊匹配：% 任意多个字符，_ 单个
WHERE name LIKE '张%'

-- 布尔
WHERE is_active = true

-- 优先级：AND 高于 OR，多用括号
WHERE (a = 1 OR b = 2) AND c = 3

---

排序与分页

-- 排序：NULLS LAST 把空值放最后（默认 PostgreSQL 是大在前）
ORDER BY created_at DESC NULLS LAST

-- 分页（两种写法）
-- 写法一：OFFSET/LIMIT —— 简单，但深分页越翻越慢
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 0;   -- 第 1 页
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 20;  -- 第 2 页

-- 写法二：游标分页（推荐，深分页性能好）
SELECT * FROM orders
WHERE id > :last_id          -- 上一页最后一条的 id
ORDER BY id LIMIT 20;

---

JOIN 联表

-- 四种 JOIN 的心智模型
-- INNER：只要两表都有的（交集）
-- LEFT：左表全要，右表没有补 NULL（最常用）
-- RIGHT：右表全要（写 LEFT 的镜像，实际少用）
-- FULL：两表都要，没有的一侧补 NULL

SELECT u.name, o.total
FROM users u                     -- 别名：u = users
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.id = 42;

-- JOIN 之后要过滤时注意：条件放 ON 还是 WHERE 语义不同
-- ON 里过滤右表 → 左表仍保留（LEFT 语义）
-- WHERE 里过滤右表 → 等于把 LEFT 变成了 INNER

---

GROUP BY 与聚合

-- 聚合函数：COUNT / SUM / AVG / MIN / MAX
SELECT status, COUNT(*) AS cnt, SUM(amount) AS total
FROM orders
GROUP BY status;

-- HAVING：聚合后再过滤（WHERE 不能用在聚合结果上）
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 1000;

-- COUNT(*) vs COUNT(col)：col 为 NULL 时不计入 COUNT(col)

---

子查询

-- 标量子查询（返回单值）
SELECT name,
  (SELECT MAX(amount) FROM orders WHERE user_id = u.id) AS max_order
FROM users u;

-- 集合子查询：IN / EXISTS
-- EXISTS 通常比 IN 快（找到就停，不建全量集合）
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- 派生表（FROM 里的子查询 = 临时表）
SELECT user_id, total FROM (
  SELECT user_id, SUM(amount) AS total
  FROM orders GROUP BY user_id
) t WHERE total > 1000;

---

窗口函数

-- 核心：OVER 不合并行（区别于 GROUP BY），每行都保留 + 旁边多一列

-- 行号：TOP-N 问题
SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM orders;
-- 外面套一层取 rn = 1 就是"每个用户最新一单"

-- 排名：RANK 允许并列（1,1,3）；DENSE_RANK 连续（1,1,2）
RANK() OVER (ORDER BY score DESC)

-- 累计 / 移动
SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at)  -- 累计
AVG(price) OVER (ORDER BY created_at ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) -- 7 日移动平均

---

INSERT / UPDATE / DELETE

-- 插入
INSERT INTO users (name, email) VALUES ('sunqy', 'a@b.com');
INSERT INTO users (name, email)
VALUES ('a', 'x'), ('b', 'y');            -- 批量

-- 冲突处理（PostgreSQL ON CONFLICT / MySQL ON DUPLICATE KEY）
INSERT INTO users (id, name)
VALUES (1, 'sunqy')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

-- 更新 / 删除（不加 WHERE = 全表！）
UPDATE users SET name = 'new' WHERE id = 42;
DELETE FROM users WHERE id = 42;

-- 更新后取回新值（PostgreSQL）
UPDATE users SET name = 'new' WHERE id = 42 RETURNING id, name;

---

事务与锁

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;       -- 一起生效
-- ROLLBACK;  -- 出错就回滚

-- 事务四大特性 ACID：原子 / 一致 / 隔离 / 持久
-- 隔离级别：读未提交 < 读已提交(默认) < 可重复读 < 串行化
-- 相关问题：脏读 / 不可重复读 / 幻读

-- 悲观锁（SELECT ... FOR UPDATE）：拿住行锁，事务结束才释放
SELECT * FROM inventory WHERE id = 1 FOR UPDATE;

-- 幂等更新的技巧：先 CAS 再更新
UPDATE inventory SET qty = qty - 1 WHERE id = 1 AND qty >= 1;

---

索引与 EXPLAIN

-- 建索引
CREATE INDEX idx_users_email ON users (email);
-- 联合索引（最左匹配：a 必须出现，其次 b、c）
CREATE INDEX idx_orders_uid_ct ON orders (user_id, created_at);

-- 分析执行计划：慢查询必看
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

-- 看关键项
-- Seq Scan  → 全表扫描，慢，该建索引
-- Index Scan / Index Only Scan → 用了索引
-- Bitmap Heap Scan → 多条件合并索引
-- 注意：对列做函数包裹（WHERE DATE(created_at) = ...）会失效索引

-- 覆盖索引：只查索引里有的列，连表都不用回 → Index Only Scan

---

常用函数速查

-- 日期
NOW()                              -- 当前时间（事务内不变）
CURRENT_DATE
created_at::date                   -- 转日期（PostgreSQL 转型写法）
DATE_TRUNC('month', created_at)    -- 按月分组用这个
AGE(created_at)                    -- 距今时长

-- 字符串
CONCAT(a, b) 或 a || b             -- 拼接
UPPER(name), LOWER(name)
SUBSTRING(name, 1, 3)
LENGTH(name)
REPLACE(name, 'a', 'b')

-- 条件
CASE WHEN status = 'paid' THEN '已付'
     WHEN status = 'pending' THEN '待付'
     ELSE '其他' END AS status_label

-- 空值处理
COALESCE(price, 0)                 -- 取第一个非 NULL
NULLIF(a, 0)                       -- a=0 时返回 NULL

---

与 ORM 对照

ORM（SQLAlchemy / Prisma）	SQL
User.query.filter(User.id == 1).first()	SELECT * FROM users WHERE id = 1 LIMIT 1
session.query(User).join(Order).all()	SELECT ... FROM users JOIN orders ...
func.count(), func.sum()	COUNT(*), SUM(...)
.order_by(User.created_at.desc())	ORDER BY created_at DESC
.limit(20).offset(0)	LIMIT 20 OFFSET 0
.group_by(User.status).having(...)	GROUP BY status HAVING ...

排查"ORM 为什么慢"的第一步：打印 ORM 生成的 SQL，EXPLAIN ANALYZE 看是否走了索引、有没有 N+1（循环里逐条查询）。