跳到主要内容
约 12 分钟7 张卡

SQL 查询

SQL 查询是声明式的:你描述想要哪些数据、按什么顺序返回,至于怎样读表、先做哪一步,由数据库决定。

正因为只描述结果,写查询时最要紧的是把结果的规则想清楚,否则 SQL 能执行,返回的数据却不是你以为的那样。最常出问题的是三个地方:NULL 参与比较时既不是真也不是假,没有唯一决胜列的排序在分页时会乱,以及去重、合并和排名各自按什么判断重复。下文以 MySQL 8.0 为准。

1. 面试核心​

先记住这几条,再读正文
  • 普通比较遇到 NULL 得到 UNKNOWN,而 WHERE 只保留结果为 TRUE 的行,所以查空值要用 IS NULL。卡 · 面试核心
  • 查询以“张”开头的姓名,使用 LIKE '张%';% 匹配零个或多个字符,_ 只匹配一个字符。卡 · 姓名的前缀匹配
  • NOT IN 的集合里有 NULL 时,没命中其他值的比较结果是 UNKNOWN,整个条件得不到 TRUE。卡 · NOT IN 与 NULL
  • DISTINCT 按所选列的组合去重;分页排序要加入唯一的决胜列,同值行的先后才确定。卡 · 面试核心
  • UNION 对合并后的结果去重,UNION ALL 保留所有重复行。卡 · UNION 与 UNION ALL
  • ROW_NUMBER 逐行编号,RANK 并列后跳号,DENSE_RANK 并列后连续编号。卡 · 窗口排名与分组前几名
  • 连续日期先按用户和日期去重,再按日期编号;连续日期减去连续行号,得到相同的分组标记,可以据此统计最长连续登录。卡 · 连续日期与区间分组

2. 最小表与过滤​

先用这张候选人表说明数值过滤,其中 id=2 的成绩是 NULL,表示还没有录入:

CREATE TABLE candidates (
id INTEGER PRIMARY KEY,
city VARCHAR(20),
score INTEGER
);
INSERT INTO candidates VALUES
(1, '上海', 80), (2, '杭州', NULL),
(3, '上海', 95), (4, '杭州', 80);

SELECT id, score
FROM candidates
WHERE score >= 80
ORDER BY score DESC, id ASC;

结果依次为 (3,95)、(1,80)、(4,80)。id=2 不在结果里,原因要从 NULL 说起。

NULL 表示「值未知」。拿一个未知的值和 80 比较,没法说它大于等于 80,也没法说不是,结果是第三种值 UNKNOWN。SQL 的条件因此有 TRUE、FALSE、UNKNOWN 三种结果,这叫三值逻辑。WHERE 只保留结果为 TRUE 的行,UNKNOWN 和 FALSE 一样被去掉。

同样的道理,想查没有成绩的人,写 score = NULL 是查不到的:和 NULL 做等值比较,结果仍然是 UNKNOWN。要写成 WHERE score IS NULL。

2.1 姓名的前缀匹配​

LIKE 按字符串模式筛选行。如果用户表把完整姓名存放在 name 列,要找以“张”开头的姓名,可以写 WHERE name LIKE '张%'。这里约定姓名直接以姓氏开头,不带前置空格或称呼;若已有独立的姓氏列,直接按姓氏等值查询更明确。

下面另建一个最小用户表,用单字、不同长度和空值说明匹配范围:

CREATE TABLE users (
id INTEGER PRIMARY KEY,
name VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs
);
INSERT INTO users VALUES
(1, '张'), (2, '张三'), (3, '张三丰'),
(4, '小张'), (5, '李张'), (6, NULL), (7, '');

SELECT id, name FROM users
WHERE name LIKE '张%'
ORDER BY id;

结果是 (1,张)、(2,张三)、(3,张三丰)。% 可以匹配零个字符,所以单字“张”也符合模式;“小张”和“李张”虽然包含“张”,却不是以它开头。这里判断的是字符串前缀,不验证姓名是否完整或符合真实姓氏规则。

条件在这张表里返回的 id原因
name LIKE '张%'1、2、3“张”之后允许有零个或多个字符
name LIKE '张_'2“张”之后必须恰好有一个字符
name LIKE '%张%'1、2、3、4、5“张”前后都允许有其他字符,表示包含而非前缀
name = '张'1按等值条件比较,不把 % 或 _ 当作模式

对非二进制字符串,_ 按字符而不是 UTF-8 字节匹配,“三”虽然占多个字节,仍只占一个匹配位置。NULL LIKE '张%' 的结果仍是 NULL,放进 WHERE 后不会被保留;空字符串也不以“张”开头。

匹配仍受字符集和排序规则影响。若要搜索的文本本身含 % 或 _,需要将它们作为字面字符转义,例如 LIKE '张!%' ESCAPE '!' 匹配字面字符串“张%”,不表示所有“张”开头的值。把模式作为参数传入时,这些通配符的语义仍然存在,不能把参数化与字面匹配混为一谈。

3. NOT IN 与 NULL​

三值逻辑在 NOT IN 上最容易出错。x NOT IN (a, b) 等价于 x <> a AND x <> b,要求目标值和集合里的每个值都不相等。集合里有 NULL 时,x <> NULL 永远是 UNKNOWN:

SELECT 3 NOT IN (1, NULL) AS result;
SELECT NULL <=> NULL AS null_equal;

第一条展开是 3 <> 1 AND 3 <> NULL,即 TRUE AND UNKNOWN,结果是 UNKNOWN,MySQL 显示为 NULL。放在 WHERE 里,这一行就会被排除。第二条返回 1,<=> 是 MySQL 的 NULL 安全等值运算符,两个 NULL 比较时返回 1 而不是 NULL。

这个问题在子查询里最常见:WHERE id NOT IN (SELECT customer_id FROM ...),只要子查询返回的列里有一个 NULL,外层一行都查不出来。子查询的列可能为空时,要么在子查询里明确加上 IS NOT NULL,要么改用 NOT EXISTS 表达「没有关联记录」,具体写法见 存在性与反连接。

4. 去重与分页​

DISTINCT 对所选列的组合去重:只有每一列都相同的两行,才算重复。分页则靠 ORDER BY 定好顺序,再用 LIMIT 和 OFFSET 取其中一段:

SELECT DISTINCT city FROM candidates ORDER BY city;
SELECT id, score FROM candidates
WHERE score IS NOT NULL
ORDER BY score DESC, id ASC LIMIT 2 OFFSET 1;

第一条返回上海、杭州各一行,中文的先后取决于列的排序规则。第二条跳过成绩最高的一行,返回 (1,80)、(4,80)。

注意 ORDER BY 里的 id ASC。id=1 和 id=4 都是 80 分,只按 score DESC 排序的话,两者谁先谁后没有保证,同一个请求执行两次可能顺序不同,分页时就可能一条重复出现、另一条被漏掉。加上唯一的 id 作为决胜列,同分记录的顺序就确定了。

这些子句的执行顺序和书写顺序不一样。逻辑上,FROM 和 JOIN 先得到来源行,WHERE 筛选行,GROUP BY 和 HAVING 处理分组,然后计算窗口函数和 SELECT 里的输出列,最后去重、排序、取 LIMIT。优化器可以调整实际的执行步骤,但结果必须和这个逻辑顺序一致。后面窗口函数要套一层子查询,原因就在这里。

5. 游标分页​

OFFSET 越大,越慢:LIMIT 20 OFFSET 100000 要先按顺序读出前 100000 行再丢掉。游标分页换了一个思路,不数跳过多少行,而是记住上一页最后一行的排序键,下一页从这个位置之后接着查:

-- 上页最后一行为 id=1、score=80。
SELECT id, score FROM candidates
WHERE score < 80 OR (score = 80 AND id > 1)
ORDER BY score DESC, id ASC LIMIT 2;

结果为 (4,80)。条件要和排序方向对应:成绩是降序,「在上一行之后」意味着成绩更低,所以写 score < 80;成绩相同时 id 是升序,所以写 id > 1。示例里的 80 和 1 是为了演示写死的,实际请求要把上一页最后一行的值作为参数传进来。

游标分页也有限制。如果排序列的值经常变化,一行可能在翻页过程中从游标的一侧移到另一侧,被重复返回或漏掉。完整导出数据时,可以按不会变的键(比如主键)分页,并约定数据的截止时间。

6. UNION 与 UNION ALL​

UNION 把列数和类型兼容的几个结果集上下拼在一起,并去掉重复行。UNION ALL 只拼接,保留所有行,省掉了去重这一步,所以更快:

SELECT city FROM candidates WHERE id=1
UNION ALL
SELECT city FROM candidates WHERE id=3;

id=1 和 id=3 都在上海,结果包含两行上海;改成 UNION 只剩一行。确定没有重复,或者需要保留重复行时,用 UNION ALL。另外,合并结果里各分支的先后没有保证,需要固定顺序时,在整个合并结果上写 ORDER BY。

7. 窗口排名与分组前几名​

窗口函数为每一行计算一个基于一组相关行的结果,比如这一行在本城市内的成绩排名。它和 GROUP BY 的区别是不把多行压成一行,明细行全部保留。MySQL 8.0 起支持窗口函数。下面取每个城市成绩最高的人:

SELECT id, city, score FROM (
SELECT id, city, score,
ROW_NUMBER() OVER (
PARTITION BY city ORDER BY score DESC, id ASC
) AS rn
FROM candidates WHERE score IS NOT NULL
) ranked
WHERE rn <= 1 ORDER BY id;

结果为 (3,上海,95)、(4,杭州,80)。内层查询用 PARTITION BY city 按城市分组,在每组内按成绩降序编号,外层再选出编号为 1 的行。之所以要套一层派生表,是因为按逻辑顺序 WHERE 先于窗口函数执行,同一层的 WHERE 里还拿不到 rn。

三个排名函数的区别在于怎样处理并列。以成绩 95、95、80 为例:

  • ROW_NUMBER 给每行不同的序号,得到 1、2、3,并列的两行谁是 1 由决胜列决定。
  • RANK 给并列的行相同名次,之后跳过被占用的名次,得到 1、1、3。
  • DENSE_RANK 也给并列的行相同名次,但名次连续,得到 1、1、2。

每组恰好取 N 行,用 ROW_NUMBER 并加上决胜列;要把同分的人一起保留,用 RANK 或 DENSE_RANK。

7.1 连续日期与区间分组​

连续登录统计的是连续的业务日期,不是连续的日志行。同一天登录两次只能算一天,第一步要按用户和日期去重。假设 login_date 已经按约定的业务时区生成,数据如下:

CREATE TABLE login_events (
user_id INTEGER NOT NULL,
login_date DATE NOT NULL
);
INSERT INTO login_events VALUES
(1, '2026-01-01'), (1, '2026-01-01'),
(1, '2026-01-02'), (1, '2026-01-04'),
(2, '2026-01-02'), (2, '2026-01-03'), (2, '2026-01-04');

WITH days AS (
SELECT DISTINCT user_id, login_date FROM login_events
), numbered AS (
SELECT user_id, login_date,
ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY login_date
) AS rn
FROM days
), marked AS (
SELECT user_id, login_date,
DATE_SUB(login_date, INTERVAL rn DAY) AS group_date
FROM numbered
), runs AS (
SELECT user_id, group_date, COUNT(*) AS days_count
FROM marked
GROUP BY user_id, group_date
)
SELECT user_id, MAX(days_count) AS longest_days
FROM runs
GROUP BY user_id
ORDER BY user_id;

结果是 (1,2)、(2,3)。对用户 1,去重后的日期为 1 日、2 日、4 日,行号为 1、2、3。前两个日期减去行号天数都得到上一年的 12 月 31 日,属于同一段;4 日减 3 天得到 1 月 1 日,形成新段。连续时日期和行号一起增加,差保持不变;日期断开时,差才改变。

WITH 声明的各段叫公共表表达式(CTE),在同一条查询中给中间结果命名。这里依次分开去重、编号、标记和聚合,方便核对每一步的行粒度。

没有登录记录的用户不会进入结果。若要求其最长天数为 0,应从用户表 LEFT JOIN 最终结果并用 COALESCE 补零。原始字段若是时间戳,先明确业务时区再取日期;若题目要求“截至今天仍在持续的连续登录”,还要筛选结束日期为今天的区间,不能直接取历史最大值。

8. 使用边界​

AND 的优先级高于 OR。a OR b AND c 会按 a OR (b AND c) 计算,混合使用时用括号写清楚组合关系。字符串的比较和排序结果由字符集和排序规则决定,比如是否区分大小写。

稳定排序只保证单次查询内的先后确定。用户翻页的过程中,如果有数据新增或修改,分页位置仍然会变化。需要一份完整一致的数据集时,还要另外约定快照或截止范围。

9. 面试题卡片​

10. 参考资料​

来源:Coding 101 原文 · 免费阅读,引用请保留出处。内容使用说明