第 4 章:过滤、排序与分页
上一章的
WHERE只会用=和<。真实的查询里,条件往往是"某几个值之一""某个区间内""包含某个词",结果还要排序、只取前几条。
学习目标
- 会用
IN、BETWEEN、LIKE写出三种最常见的过滤模式 - 理解
LIKE的大小写规则,知道为什么前导%会让索引失效 - 会用多列
ORDER BY和LIMIT/OFFSET,知道深分页为什么慢 - 能组合出一个"条件 + 排序 + 截取"的完整查询
4.1 IN:从一组值里挑
要查"文学或历史"的书,用 OR 要写两遍列名:
SELECT title, category FROM books
WHERE category = '文学' OR category = '历史';用 IN 更紧凑:
SELECT title, category FROM books
WHERE category IN ('文学', '历史');IN 后面是一个值列表,只要等于其中任意一个就通过,等价于多个 OR 的简写。值多的时候尤其省事。
取反用 NOT IN:
SELECT title, category FROM books
WHERE category NOT IN ('文学', '历史');┌──────────────────────┬──────────┐
│ title │ category │
├──────────────────────┼──────────┤
│ 深入理解计算机系统 │ 计算机 │
│ SQL 必知必会 │ 数据库 │
│ 数据库系统概念 │ 数据库 │
│ 代码整洁之道 │ 计算机 │
└──────────────────────┴──────────┘⚠️
NOT IN的列表里不能出现 NULL。x NOT IN (1, 2, NULL)中x <> NULL恒为 NULL,整个条件就永远是"未知",结果为空。列表是写死的还好,如果来自子查询,务必先过滤掉 NULL(第 7 章展开)。
4.2 BETWEEN:区间包含两端
查价格在 40 到 100 之间的书:
SELECT title, price FROM books
WHERE price BETWEEN 40 AND 100;BETWEEN a AND b 等价于 price >= a AND price <= b,两端都包含。这一点和很多人的直觉相反——它不是"大于 40 小于 100",是"大于等于 40 小于等于 100"。
价格 49、99、98、59、68 的书都会命中,128、39、25 被排除。
日期也能 BETWEEN,但边界要小心
published_at 存的是 YYYY-MM-DD 字符串,字典序和日期序一致,所以可以直接比较:
SELECT title, published_at FROM books
WHERE published_at BETWEEN '2020-01-01' AND '2025-12-31';┌──────────┬──────────────┐
│ title │ published_at │
├──────────┼──────────────┤
│ 无名诗集 │ 2023-01-01 │
└──────────┴──────────────┘只有《无名诗集》落在 2020 之后,因为其他书的出版日期都早于 2020。
但如果存的是带时间的字符串(2025-06-01 14:30:00),写 BETWEEN '2025-06-01' AND '2025-06-30' 就会漏掉 6 月 30 日当天带时间的记录——因为 '2025-06-30 08:00' 字典序大于 '2025-06-30'。
正确写法是左闭右开:
WHERE ordered_at >= '2025-06-01' AND ordered_at < '2025-07-01'这条规则在日志、订单、报表里都适用:时间区间永远用 >= 起点 AND < 下一个周期的起点。
4.3 LIKE:模糊匹配
LIKE 用两个通配符:
| 通配符 | 含义 |
|---|---|
% | 任意个字符(包括 0 个) |
_ | 恰好一个字符 |
查书名里含 "SQL" 的书:
SELECT title FROM books WHERE title LIKE '%SQL%';┌──────────────┐
│ title │
├──────────────┤
│ SQL 必知必会 │
└──────────────┘查三个字的书名(_ 占一个汉字):
SELECT title FROM books WHERE title LIKE '___';'活着'、'___' 这种长度固定的匹配,比 % 精确,但依然用不上索引(原因见下)。
大小写:SQLite 只对 ASCII 不敏感
SELECT 'SQL' LIKE 'sql'; -- 1(不区分大小写)
SELECT 'SQL' GLOB 'sql*'; -- 0(GLOB 区分大小写)SQLite 的 LIKE 对英文字母忽略大小写,对中文没有影响(本来就没有大小写)。想要区分大小写,用 GLOB,语法换成 * 和 ?。
PostgreSQL 正好相反:LIKE 区分大小写,忽略大小写要用 ILIKE。这个差异留到第 12 章,先知道两边的默认行为不同。
为什么前导 % 会拖慢查询
LIKE '%SQL%' 的问题是:它要求从每个字符串的任意位置开始匹配。索引是按"前缀"组织的(第 9 章会讲 B-tree),前缀未知就只能全表扫。
而 LIKE 'SQL%'(% 只在末尾)是可以走索引的,因为它确定了前缀是 "SQL"。
所以一条实用规则:模糊查询尽量把 % 放右边。如果业务真的需要"包含",那考虑全文索引或搜索引擎,而不是在 SQL 里硬扛。
4.4 ORDER BY:排序
SELECT title, price FROM books ORDER BY price DESC;ASC 升序(默认,可省略),DESC 降序。
多列排序
先按分类,分类内按价格从高到低:
SELECT title, category, price FROM books
ORDER BY category ASC, price DESC;┌──────────────────────┬──────────┬───────┐
│ title │ category │ price │
├──────────────────────┼──────────┼───────┤
│ 人类简史 │ 历史 │ 68.0 │
│ 数据库系统概念 │ 数据库 │ 99.0 │
│ SQL 必知必会 │ 数据库 │ 49.0 │
│ 平凡的世界 │ 文学 │ 98.0 │
│ 活着 │ 文学 │ 39.0 │
│ 无名诗集 │ 文学 │ 25.0 │
│ 深入理解计算机系统 │ 计算机 │ 128.0 │
│ 代码整洁之道 │ 计算机 │ 59.0 │
└──────────────────────┴──────────┴───────┘注意分类的顺序:历史 → 数据库 → 文学 → 计算机,既不是拼音也不是笔画,而是 Unicode 码点的字节序。中文 TEXT 的默认排序是"按字节比",不是按语义。想要按拼音或笔画排,得显式指定排序规则(COLLATE),或者把排序键单独存一列——这一点在第 12 章迁移时会再次遇到(PostgreSQL 的行为又不一样)。
规则是:前一列相等时,才看后一列。排序是稳定分层的,不是"两个条件混合打分"。
别名可以用(验证上一章那张图)
SELECT title, ROUND(price * 0.8, 2) AS 八折价
FROM books
ORDER BY 八折价 ASC
LIMIT 3;结果是最便宜的三本:《无名诗集》20.0、《活着》31.2、《SQL 必知必会》39.2。
能跑通,正是因为 ORDER BY 在 SELECT 之后执行——上一章的执行顺序图在这里得到了验证。
NULL 排在哪
ORDER BY author ASC 时,NULL 会排在最前面(SQLite 把 NULL 当最小值)。如果想让它排最后:
ORDER BY author IS NULL, author ASC;author IS NULL 对非空行是 0,对 NULL 行是 1,升序就把 NULL 推到末尾。这个技巧在报表里很常用。
PostgreSQL 更直接:
ORDER BY author ASC NULLS LAST。SQLite 3.30 之后也支持NULLS LAST,但上面的写法兼容性最好。
4.5 LIMIT / OFFSET:只取一页
SELECT title, price FROM books ORDER BY price DESC LIMIT 3;取最贵的三本:128、99、98。
翻页用 OFFSET:
-- 第一页
SELECT title, price FROM books ORDER BY price DESC LIMIT 3 OFFSET 0;
-- 第二页
SELECT title, price FROM books ORDER BY price DESC LIMIT 3 OFFSET 3;LIMIT 必须配 ORDER BY。 不排序时,"前 3 行"是哪 3 行由存储顺序决定,看起来能用,数据一更新结果就变。分页尤其要固定排序键,否则会出现"第 2 页重复出现第 1 页的内容"。
深分页为什么慢
OFFSET 10000 LIMIT 10 并不是"直接跳到第 10001 行"。数据库必须先数过前 10000 行再把它们扔掉,越往后翻越慢。
两种解法(第 9 章细讲):
- 游标分页:不用
OFFSET,用"上一页最后一条的排序值"作为起点,WHERE price < 上一页最后的价格; - 覆盖索引:让排序和取数都走索引,减少回表。
小项目 OFFSET 几千行以内感觉不出来;数据上了十万行,深分页就会变成明显的卡顿。
4.6 组合起来:一个真实的查询
需求:2025 年下半年(7–12 月)已付款或已发货的订单里,消费超过 200 元的客户,按金额从高到低取前 3 名。
这个查询要用到连接和聚合,第 5、6 章才讲全。先看它的形状,体会"条件、排序、截取"是怎么叠起来的:
SELECT c.name, ROUND(SUM(i.quantity * i.unit_price), 2) AS 消费额
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items i ON i.order_id = o.id
WHERE o.ordered_at >= '2025-07-01'
AND o.ordered_at < '2026-01-01'
AND o.status IN ('paid', 'shipped')
GROUP BY c.id, c.name
HAVING SUM(i.quantity * i.unit_price) > 200
ORDER BY 消费额 DESC
LIMIT 3;逐层读:
WHERE先筛订单:时间区间(左闭右开)+ 状态白名单;GROUP BY按客户归并,SUM算每人消费额;HAVING再筛掉不足 200 的客户;ORDER BY按算出来的别名排序;LIMIT只留前三。
到第 6 章结束时,你就能独立写出这条查询,并解释每一行为什么放在那个位置。
本章小结
| 要点 | 说明 |
|---|---|
IN = 多个 OR 的简写 | 列表里不能有 NULL(NOT IN 尤其) |
BETWEEN 含两端 | 时间区间更推荐左闭右开 |
LIKE 的 % 与 _ | % 任意长度,_ 恰好一个字符 |
前导 % 不走索引 | 模糊匹配尽量把 % 放右边 |
| 多列排序是分层的 | 前一列相等才看后一列 |
ORDER BY 能用别名 | 因为它在 SELECT 之后执行 |
LIMIT 必须配 ORDER BY | 否则分页结果不稳定 |
| 深分页慢在"数过再扔" | 用游标分页或覆盖索引 |
下一章
单表查询到这里就够用了。下一章开始"汇总":怎么用一条 SQL 算出总营收、每本书卖了多少、每个城市贡献了多少——GROUP BY 和 HAVING 会把第 3 章那张执行顺序图的后半段填满。