Skip to content
Lv1SQL 与数据库进度 4 / 12

第 4 章:过滤、排序与分页 ​

上一章的 WHERE 只会用 = 和 <。真实的查询里,条件往往是"某几个值之一""某个区间内""包含某个词",结果还要排序、只取前几条。


学习目标 ​

  • 会用 IN、BETWEEN、LIKE 写出三种最常见的过滤模式
  • 理解 LIKE 的大小写规则,知道为什么前导 % 会让索引失效
  • 会用多列 ORDER BY 和 LIMIT/OFFSET,知道深分页为什么慢
  • 能组合出一个"条件 + 排序 + 截取"的完整查询

4.1 IN:从一组值里挑 ​

要查"文学或历史"的书,用 OR 要写两遍列名:

sql
SELECT title, category FROM books
WHERE category = '文学' OR category = '历史';

用 IN 更紧凑:

sql
SELECT title, category FROM books
WHERE category IN ('文学', '历史');

IN 后面是一个值列表,只要等于其中任意一个就通过,等价于多个 OR 的简写。值多的时候尤其省事。

取反用 NOT IN:

sql
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 之间的书:

sql
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 字符串,字典序和日期序一致,所以可以直接比较:

sql
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'。

正确写法是左闭右开:

sql
WHERE ordered_at >= '2025-06-01' AND ordered_at < '2025-07-01'

这条规则在日志、订单、报表里都适用:时间区间永远用 >= 起点 AND < 下一个周期的起点。


4.3 LIKE:模糊匹配 ​

LIKE 用两个通配符:

通配符含义
%任意个字符(包括 0 个)
_恰好一个字符

查书名里含 "SQL" 的书:

sql
SELECT title FROM books WHERE title LIKE '%SQL%';
┌──────────────┐
│    title     │
├──────────────┤
│ SQL 必知必会 │
└──────────────┘

查三个字的书名(_ 占一个汉字):

sql
SELECT title FROM books WHERE title LIKE '___';

'活着'、'___' 这种长度固定的匹配,比 % 精确,但依然用不上索引(原因见下)。

大小写:SQLite 只对 ASCII 不敏感 ​

sql
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:排序 ​

sql
SELECT title, price FROM books ORDER BY price DESC;

ASC 升序(默认,可省略),DESC 降序。

多列排序 ​

先按分类,分类内按价格从高到低:

sql
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 的行为又不一样)。

规则是:前一列相等时,才看后一列。排序是稳定分层的,不是"两个条件混合打分"。

别名可以用(验证上一章那张图) ​

sql
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 当最小值)。如果想让它排最后:

sql
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:只取一页 ​

sql
SELECT title, price FROM books ORDER BY price DESC LIMIT 3;

取最贵的三本:128、99、98。

翻页用 OFFSET:

sql
-- 第一页
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 章才讲全。先看它的形状,体会"条件、排序、截取"是怎么叠起来的:

sql
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;

逐层读:

  1. WHERE 先筛订单:时间区间(左闭右开)+ 状态白名单;
  2. GROUP BY 按客户归并,SUM 算每人消费额;
  3. HAVING 再筛掉不足 200 的客户;
  4. ORDER BY 按算出来的别名排序;
  5. LIMIT 只留前三。

到第 6 章结束时,你就能独立写出这条查询,并解释每一行为什么放在那个位置。


本章小结 ​

要点说明
IN = 多个 OR 的简写列表里不能有 NULL(NOT IN 尤其)
BETWEEN 含两端时间区间更推荐左闭右开
LIKE 的 % 与 _% 任意长度,_ 恰好一个字符
前导 % 不走索引模糊匹配尽量把 % 放右边
多列排序是分层的前一列相等才看后一列
ORDER BY 能用别名因为它在 SELECT 之后执行
LIMIT 必须配 ORDER BY否则分页结果不稳定
深分页慢在"数过再扔"用游标分页或覆盖索引

下一章 ​

单表查询到这里就够用了。下一章开始"汇总":怎么用一条 SQL 算出总营收、每本书卖了多少、每个城市贡献了多少——GROUP BY 和 HAVING 会把第 3 章那张执行顺序图的后半段填满。

👉 第 5 章:聚合与分组

本文阅读--次|全站访问--次|访客--人