SQL 语句学习手册——从入门到面试通关

本文目标：从零到能在面试里手写复杂 SQL、讲清执行计划与索引优化。按"基础语法 → 多表查询 → 聚合与窗口函数 → 进阶 → 性能优化 → 面试高频题"组织，每段配可运行示例。

全文示例基于 MySQL（最常考），并在涉及 PostgreSQL/标准 SQL 差异处注明。

一、SQL 三大门类

先建立全局观，SQL 语句分三大类：

类 关键字 作用
DDL（Data Definition） CREATE / DROP / ALTER / TRUNCATE 定义结构（库、表、索引、视图）
DML（Data Manipulation） INSERT / UPDATE / DELETE / SELECT 操作数据（查增改删）
DCL（Data Control） GRANT / REVOKE / COMMIT / ROLLBACK 权限与事务控制

注：严格说 SELECT 属 DQL，但日常归到 DML。TRUNCATE 是 DDL 不是 DML——它是删表重建，不逐行删除，不能回滚，面试常考这点。

二、DDL：建库建表与约束

2.1 库与表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE DATABASE shop
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_unicode_ci;

CREATE TABLE orders (
  id          BIGINT       PRIMARY KEY AUTO_INCREMENT,
  user_id     BIGINT       NOT NULL,
  amount      DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  status      TINYINT      NOT NULL DEFAULT 0,  -- 0未支付 1已支付 2已取消
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_user (user_id),
  INDEX idx_status_time (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2.2 约束（重点）

约束 关键字 说明
主键 PRIMARY KEY 唯一且非空，自带索引
非空 NOT NULL 不允许 NULL
唯一 UNIQUE 唯一，可有多个 NULL
默认 DEFAULT 缺省值
外键 FOREIGN KEY 引用另一表主键，保证参照完整性
检查 CHECK 条件约束（MySQL 8.0.16+ 才真正校验）

外键示例：

1
2
3
4
5
6
7
8
CREATE TABLE order_item (
  id        BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_id  BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  qty       INT NOT NULL,
  CONSTRAINT fk_order FOREIGN KEY (order_id)
    REFERENCES orders(id) ON DELETE CASCADE ON UPDATE CASCADE
);

面试坑：外键在高并发/分库分表场景基本不用，靠应用层保证；它带来锁和性能开销。生产 OLTP 常禁用外键。

三、DML 基础：增删改

1
2
3
4
5
INSERT INTO orders (user_id, amount, status) VALUES (1001, 199.00, 0);
INSERT INTO orders (user_id, amount) VALUES (1002, 88.00),(1003, 9.90);  -- 批量插入

UPDATE orders SET status = 1 WHERE id = 5;   -- 一定要带 WHERE！
DELETE FROM orders WHERE status = 2;         -- 一定要带 WHERE！

重要：

  • UPDATE/DELETE 不带 WHERE = 全表操作，生产事故源头。建议执行前先 SELECT 确认范围。
  • 批量更新用 INSERT ... ON DUPLICATE KEY UPDATE（MySQL）或 UPSERT（PG ON CONFLICT）。
  • 软删除：加 deleted_at 字段做逻辑删除，而非 DELETE，便于审计和恢复。

四、SELECT：查询基础

4.1 基本结构与执行顺序

1
2
3
4
5
6
7
SELECT   字段
FROM     表
WHERE    行过滤条件
GROUP BY 分组列
HAVING   分组后过滤
ORDER BY 排序列
LIMIT    限制行数;

SQL 书写顺序 vs 引擎执行顺序（面试必考）：

1
2
书写: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
执行: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

因为先有表、再过滤、再分组、再过滤组、再选列、最后排序取前 N。理解这点就能解释"为什么 WHERE 里不能用 SELECT 的别名，而 ORDER BY 可以"。

4.2 WHERE 条件与运算符

1
2
3
4
5
6
SELECT * FROM orders
WHERE status = 1
  AND amount BETWEEN 100 AND 1000
  AND user_id IN (1001,1002,1003)
  AND created_at >= '2026-07-01'
  AND remark LIKE '%加急%';        -- % 任意多字符, _ 单字符

索引陷阱：对索引列用函数/运算会让索引失效：

1
2
3
4
WHERE DATE(created_at) = '2026-07-17'   -- 索引失效！改用范围:
WHERE created_at >= '2026-07-17' AND created_at < '2026-07-18'
WHERE LEFT(name,3)='abc'               -- 失效，改用 name LIKE 'abc%'
WHERE amount + 1 = 100                 -- 失效，改用 amount = 99

4.3 排序、分页与去重

1
2
3
4
SELECT DISTINCT user_id FROM orders WHERE status = 1;

SELECT * FROM orders ORDER BY amount DESC, created_at ASC LIMIT 10 OFFSET 20;
-- MySQL 简写: LIMIT 20, 10  (offset 20, 取 10)

深分页问题（面试高频）：LIMIT 1000000, 10 会扫描前 1000010 行再丢弃，极慢。优化方案：

1
2
3
4
5
6
7
-- 方案1: 游标/延迟关联，用上一页最后一个 id
SELECT * FROM orders WHERE id > #{last_id} ORDER BY id LIMIT 10;

-- 方案2: 延迟关联，先查主键再回表
SELECT * FROM orders o
JOIN (SELECT id FROM orders WHERE status=1 ORDER BY created_at LIMIT 1000000,10) t
  ON o.id = t.id;

五、多表查询：JOIN（核心中的核心）

5.1 JOIN 七种语义图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
  A表              B表
┌─────┐          ┌─────┐
│  ◯──┼──┼──◯    │     │
└─────┘          └─────┘
内连接 INNER JOIN：只取两表交集

┌─────┐
│◯──┼──┼──◯  +  左表全部
└─────┘
左连接 LEFT JOIN：左表全留，右表无则填 NULL

右连接 RIGHT JOIN：右表全留
全连接 FULL JOIN：左右全留(MySQL 不支持，用 UNION 模拟)
交叉连接 CROSS JOIN：笛卡尔积
自连接 SELF JOIN：表连自己

5.2 示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 查每个订单的用户名和金额
SELECT u.name, o.amount, o.created_at
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE o.status = 1;

-- 左连接：列出所有用户，没下单的也显示(amount 为 NULL)
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

-- 自连接：查员工的经理
SELECT e.name AS emp, m.name AS manager
FROM employee e
LEFT JOIN employee m ON e.manager_id = m.id;

5.3 JOIN 执行原理（面试讲清）

  • Nested Loop Join（嵌套循环）：驱动表一行，遍历被驱动表匹配。小表驱动大表。
  • Index Nested Loop（INL）：被驱动表连接列有索引，走索引，快。
  • Block Nested Loop（BNL）：无索引时，把驱动表批量放入 join buffer，减少被驱动表扫描次数，慢。
  • Hash Join：MySQL 8.0.18+ 支持，PG 老早支持，构建哈希表后探测，等值连接利器。
  • 驱动表选择：优化器按"小结果集驱动大结果集"选，可被 STRAIGHT_JOIN 强制顺序。

5.4 子查询 vs JOIN

1
2
3
4
5
6
-- 子查询(相关子查询，慢)
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM users u WHERE u.id=o.user_id AND u.vip=1);

-- 等价 JOIN(通常更快)
SELECT o.* FROM orders o JOIN users u ON o.user_id=u.id WHERE u.vip=1;
  • IN 子查询大结果集时 MySQL 可能性能差，可用 JOIN 或 EXISTS 替代。
  • MySQL 8 起 IN 子查询会自动优化为 semi-join。

六、聚合与分组

1
2
3
4
5
6
7
8
9
10
11
12
-- 每个用户的订单数和总金额，只看下单≥3次的
SELECT user_id,
       COUNT(*)              AS order_cnt,
       SUM(amount)           AS total,
       AVG(amount)           AS avg_amt,
       MAX(amount)           AS max_amt,
       MIN(amount)           AS min_amt
FROM orders
WHERE status = 1
GROUP BY user_id
HAVING COUNT(*) >= 3
ORDER BY total DESC;

要点：

  • WHERE 在分组前过滤行，HAVING 在分组后过滤组。
  • SELECT 出现的非聚合列必须在 GROUP BY 里（MySQL 默认 ONLY_FULL_GROUP_BY 严格模式），否则报错。
  • COUNT(*) 含 NULL 行，COUNT(列) 不计 NULL 行——区别常考。
  • SUM/AVG 忽略 NULL；AVG = SUM/COUNT(非NULL)。

常用聚合 + 条件聚合（pivot 式）

1
2
3
4
5
6
-- 按状态统计订单数，一行展示
SELECT
  COUNT(*)                    AS total,
  SUM(status=1)               AS paid_cnt,     -- MySQL 布尔转0/1
  SUM(status=0)               AS unpaid_cnt
FROM orders;

七、窗口函数（面试加分项，MySQL 8.0+）

窗口函数 = 聚合 + 保留明细行，不像 GROUP BY 会折叠行。语法：

1
2
3
4
5
函数() OVER (
  PARTITION BY 分组列
  ORDER BY 排序列
  ROWS/RANGE BETWEEN 帧起 AND 帧止
)

常用窗口函数

函数 作用
ROW_NUMBER() 行号 1,2,3…（不并列）
RANK() 排名，并列后跳号 1,1,3
DENSE_RANK() 排名，并列不跳号 1,1,2
LEAD(col,n) / LAG(col,n) 取后/前 n 行的值
FIRST_VALUE / LAST_VALUE 帧内首/末值
SUM/AVG/MAX/MIN OVER 滚动聚合
NTILE(n) 分成 n 桶

经典场景

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 1. 每个用户最近3笔订单(分组 Top N)
SELECT * FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders o
) t WHERE rn <= 3;

-- 2. 每个用户按时间累加金额(滚动求和)
SELECT user_id, created_at, amount,
       SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS cum_amount
FROM orders;

-- 3. 用户内按金额排名
SELECT user_id, amount,
       DENSE_RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rk
FROM orders;

-- 4. 同环比：用 LAG 取上一行
SELECT user_id, dt, daily_amt,
       daily_amt - LAG(daily_amt) OVER (PARTITION BY user_id ORDER BY dt) AS diff
FROM daily_stats;

窗口函数是面试 SQL 的分水岭。GROUP BY 求分组聚合谁都会，窗口函数求"分组内 TopN / 滚动 / 排名"才是区分度。

八、集合操作：UNION / INTERSECT / EXCEPT

1
2
3
4
5
6
7
8
-- UNION 去重，UNION ALL 不去重(更快，常用)
SELECT name FROM vip_users
UNION ALL
SELECT name FROM normal_users;

-- MySQL 无 INTERSECT/EXCEPT，用 JOIN / NOT IN 替代
SELECT id FROM A WHERE id IN (SELECT id FROM B);     -- 交集
SELECT id FROM A WHERE id NOT IN (SELECT id FROM B); -- 差集
  • UNION 列数与类型必须一致。
  • 去重要排序去重，开销大，确认无重复时优先 UNION ALL。

九、CASE 表达式

1
2
3
4
5
6
7
8
SELECT user_id,
  CASE
    WHEN SUM(amount) >= 10000 THEN '钻石'
    WHEN SUM(amount) >= 1000  THEN '黄金'
    ELSE '普通'
  END AS level
FROM orders
GROUP BY user_id;

CASE 可在 SELECT / WHERE / ORDER BY / GROUP BY 任意位置，灵活做分类/透传。

十、进阶：CTE 与递归

10.1 CTE（公用表表达式，with 子句）

1
2
3
4
WITH vip_orders AS (
  SELECT o.* FROM orders o JOIN users u ON o.user_id=u.id WHERE u.vip=1
)
SELECT user_id, COUNT(*) FROM vip_orders GROUP BY user_id;
  • 可读性好，可多次引用，MySQL 8.0+ 支持。
  • 物化 CTE（PG MATERIALIZED）可做性能优化。

10.2 递归 CTE（树/层级查询）

1
2
3
4
5
6
7
8
-- 查所有下属(组织树)
WITH RECURSIVE sub AS (
  SELECT id, name, parent_id FROM org WHERE id = 1        -- 锚点: 起点
  UNION ALL
  SELECT o.id, o.name, o.parent_id
  FROM org o JOIN sub s ON o.parent_id = s.id             -- 递归: 找下级
)
SELECT * FROM sub;
  • 递归必须有终止条件（递归不再产生新行时自然停）。
  • 常用于：组织树、菜单树、BOM 物料展开、路径分析。

十一、索引与性能优化（面试重头）

11.1 索引类型

  • B+Tree 索引：默认，等值/范围/前缀匹配。
  • Hash 索引：Memory 引擎，只等值。
  • 全文索引 FULLTEXT：文本搜索。
  • 空间索引：GIS。

11.2 B+Tree 与最左前缀原则

联合索引 (a, b, c) 的 B+Tree 先按 a 排序、再 b、再 c。能走索引的查询：

  • a=1 ✓
  • a=1 AND b=2 ✓
  • a=1 AND b=2 AND c=3 ✓
  • a=1 AND c=3 ✓ 用到 a，c 用不上（中间 b 断了）→ 实际走索引下推
  • b=2 ✗ 不满足最左前缀
  • a>1 AND b=2 ✓ a 走范围后 b 用不上（范围之后失效）

索引下推 ICP（Index Condition Pushdown，5.6+）：在存储引擎层先用索引列条件过滤，减少回表次数。面试可加分提。

11.3 覆盖索引

查询列都在索引里，不用回表：

1
2
3
-- 索引 (user_id, status, amount)
SELECT user_id, amount FROM orders WHERE user_id=1 AND status=1;
-- 索引含所有列，Extra 显示 Using index，无需回表

11.4 常见索引失效

  • 对索引列用函数/运算（见 4.2）
  • 隐式类型转换：WHERE phone = 13800000000（phone 是 varchar，传数字导致失效）
  • LIKE '%xxx' 左模糊失效（'xxx%' 右模糊可用）
  • OR 两边不全有索引 → 全表扫描
  • !=/NOT IN 常导致放弃索引（视数据分布）

11.5 EXPLAIN 看执行计划（必会）

1
EXPLAIN SELECT * FROM orders WHERE user_id=1 AND status=1;

关键字段：

列 含义 关注点
type 访问类型 const > eq_ref > ref > range > index > ALL。ALL 全表扫描要避免
key 实际用的索引 是否用了预期索引
key_len 用到的索引长度 判断联合索引用了几列
rows 估算扫描行数 越小越好
Extra 额外信息 Using index 覆盖索引好；Using filesort/Using temporary 要优化

11.6 建索引经验

  • 区分度高的列优先（status 只有 0/1/2 区分度低，单独建索引意义不大）。
  • 频繁查询的 WHERE/JOIN/ORDER BY/GROUP BY 列。
  • 单表索引数控制在 5 个内，联合索引列数 ≤5。
  • 长字符串用前缀索引 INDEX(name(20))。
  • 写多读少的表少建索引（索引维护开销）。

十二、事务与隔离级别

12.1 ACID

  • A 原子性：全做或全不做，靠 undo log。
  • C 一致性：约束始终满足。
  • I 隔离性：并发事务互不干扰，靠锁+MVCC。
  • D 持久性：提交即落盘，靠 redo log。

12.2 四种隔离级别（MySQL InnoDB 默认 RR）

级别 脏读 不可重复读 幻读
READ UNCOMMITTED 读未提交 ✓ ✓ ✓
READ COMMITTED 读已提交 ✗ ✓ ✓
REPEATABLE READ 可重复读（默认） ✗ ✗ ✓(MySQL 用间隙锁/Next-Key Lock 部分解决)
SERIALIZABLE 串行化 ✗ ✗ ✗
  • 脏读：读到别的事务未提交的数据。
  • 不可重复读：同一事务两次读同一行结果不同（别人 update 提交了）。
  • 幻读：同一事务两次范围查询结果集不同（别人 insert 提交了）。
  • MySQL RR 通过 MVCC（快照读）+ Next-Key Lock（当前读） 解决大部分幻读。

12.3 锁

  • 行锁/间隙锁/临键锁；共享锁（S，读锁）/排他锁（X，写锁）。
  • SELECT ... FOR UPDATE（当前读 + X 锁）、SELECT ... LOCK IN SHARE MODE（S 锁）。
  • MVCC：每行多版本，事务读自己快照，读不加锁不阻塞写。

12.4 死锁

两个事务互相等对方锁，数据库自动检测并回滚代价小的一方。预防：固定加锁顺序、缩小事务、走索引避免锁升级。

十三、面试高频 SQL 题（手写）

Q1：第 N 高的薪水

1
2
3
4
5
6
SELECT DISTINCT salary FROM employee ORDER BY salary DESC LIMIT 1 OFFSET N-1;
-- 窗口函数版，处理 null 友好
SELECT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) rk
  FROM employee
) t WHERE rk = N;

Q2：连续登录 N 天的用户

1
2
3
4
5
6
7
8
SELECT user_id
FROM (
  SELECT user_id, login_date,
         DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS grp
  FROM (SELECT DISTINCT user_id, DATE(login_time) login_date FROM logins) a
) b
GROUP BY user_id, grp
HAVING COUNT(*) >= N;

思路：用 日期 - 行号 把连续日期归到同一组（差值相同 = 连续）。

Q3：各部门薪水前三名（分组 TopN）

1
2
3
4
5
SELECT * FROM (
  SELECT dept_id, name, salary,
         DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) rk
  FROM employee
) t WHERE rk <= 3;

Q4：找出工资比经理高的员工

1
2
3
4
5
-- 自连接
SELECT e.name
FROM employee e
JOIN employee m ON e.manager_id = m.id
WHERE e.salary > m.salary;

Q5：行转列（pivot）

1
2
3
4
5
SELECT user_id,
  SUM(CASE WHEN month=1 THEN amount ELSE 0 END) AS m1,
  SUM(CASE WHEN month=2 THEN amount ELSE 0 END) AS m2
FROM monthly_amount
GROUP BY user_id;

Q6：删重复行，保留 id 最小

1
2
3
4
5
6
DELETE FROM orders
WHERE id NOT IN (
  SELECT min_id FROM (
    SELECT MIN(id) min_id FROM orders GROUP BY user_id, created_at
  ) t
);

（MySQL 不能直接在 DELETE 里嵌套查同表，要再包一层子查询。）

Q7：列转行（unpivot）

1
2
3
SELECT user_id, 'm1' AS month, m1 AS amount FROM t
UNION ALL
SELECT user_id, 'm2', m2 FROM t;

十四、学习路径建议

  1. 第 1 周：DDL/DML/SELECT 基础 + WHERE/ORDER/LIMIT，在本地 MySQL 建几张大表练手。
  2. 第 2 周：JOIN 七种 + 子查询，重点掌握执行顺序和驱动表。
  3. 第 3 周：GROUP BY/HAVING + 聚合 + 窗口函数（这是分水岭，多刷 LeetCode SQL 50 题）。
  4. 第 4 周：索引 + EXPLAIN + 优化，造慢查询练调优。
  5. 第 5 周：事务隔离 + 锁 + MVCC，理解原理。
  6. 持续：CTE/递归/存储过程，进阶优化。

刷题资源：LeetCode 数据库题、牛客 SQL 题、SQLZoo。

十五、一张速查表

1
2
3
4
5
6
7
8
9
10
执行顺序: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
索引最左前缀: 联合索引(a,b,c) 必须从 a 开始，范围后失效
覆盖索引: 查询列都在索引里 → Using index 不回表
窗口函数: 函数() OVER(PARTITION BY .. ORDER BY ..)
深分页: 用游标 WHERE id>last_id 或延迟关联
聚合含NULL: COUNT(*) 计 NULL, COUNT(列) 不计; SUM/AVG 忽略 NULL
索引失效: 函数/运算列、隐式类型转换、%左模糊、OR两边缺索引、!=
UNION ALL 优于 UNION(不去重更快)
TRUNCATE 不可回滚(DDL), DELETE 可回滚(DML)
MySQL 默认隔离级别 RR，靠 MVCC+Next-Key Lock 防幻读

一句话：SQL 的本质是"用声明式语法描述要什么数据，让优化器决定怎么取"。掌握执行顺序、JOIN 原理、索引最左前缀与 EXPLAIN、窗口函数、事务隔离这五块，面试和日常开发都能游刃有余。