查询、过滤、排序与分页

学习目标

• 能把业务条件写成明确的投影、过滤与排序。
• 能解释 NULL、闭区间和模糊匹配的结果。
• 能选择偏移分页或游标分页，并正确处理输入参数。

核心知识点

本节使用 MySQL 8.0 / InnoDB 的 learning_lab；先执行 第二篇建表和种子数据，保留 users.id=1、2 和 orders.id=101、102、103。第四篇额外用户不影响本节带 id 范围的查询。SQL 的逻辑理解顺序是 FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT；优化器可采用不同物理执行顺序。

谓词有 TRUE、FALSE、UNKNOWN 三种逻辑结果，WHERE 只保留 TRUE。查询不指定 ORDER BY 就没有顺序承诺；排序列值重复时加入唯一主键才能给分页稳定的全序。函数可能影响类型、字符集、NULL 传播和索引使用，不能仅凭结果正确就认为性能合适。

常用用法

SELECT：选择明确的列

用于只返回调用方需要的字段并给表达式提供稳定的列名。

USE learning_lab;
-- 初始：id=1 余额 100.00，id=2 余额 50.00。
SELECT id, username, balance * 100 AS balance_cents
FROM users WHERE id IN (1,2) ORDER BY id;
-- 关键变化：返回10000.00与5000.00的金额换算结果，原表余额不变。
-- 输出：1/林同学/10000.00；2/陈同学/5000.00。

SELECT * 会随结构演进扩大结果，增加网络传输和映射风险。别名可用于 ORDER BY，通常不能用于同层 WHERE。SELECT 语法。

SELECT WHERE：组合业务条件

用于在读取之前排除不满足余额与状态要求的用户。

-- 初始：id=1、2 均 ACTIVE，余额分别 100.00、50.00。
SELECT id FROM learning_lab.users
WHERE id IN (1,2) AND (balance >= 80 OR status='DISABLED') ORDER BY id;
-- 关键变化：过滤后只返回 id=1。
-- 输出：1。

AND 优先于 OR；复杂条件加括号表达业务分组。字符串与数值混比可能触发隐式转换，金额阈值按数值类型绑定。

SELECT IS NULL：识别未知值

用于区分缺失数据、空字符串与数值零，并观察三值逻辑。

-- 输入：NULL、空字符串与 0 是三种不同值。
SELECT NULL = NULL AS eq, NULL IS NULL AS missing,
       NULL <=> NULL AS null_safe, '' IS NULL AS empty_missing,
       NOT (NULL = 1) AS unknown_not;
-- 关键变化：普通比较返回 NULL，IS NULL 与空安全相等返回 1。
-- 输出：NULL | 1 | 1 | 0 | NULL。

<=> 是 MySQL 空安全相等；普通 = NULL 不会找出缺失行。UNKNOWN 在 SQL 中通常以 NULL 表示，NOT UNKNOWN 仍为 UNKNOWN。NULL 语义。

SELECT LIKE：匹配字符串模式

用于按前缀查找姓名，并用显式转义字符匹配字面通配符。

-- 初始：id=1 用户名是林同学，id=2 是陈同学。
SELECT id FROM learning_lab.users WHERE id IN (1,2) AND username LIKE '林%';
SELECT 'a_b' LIKE 'a!_b' ESCAPE '!' AS literal_underscore;
-- 关键变化：姓名过滤返回 id=1；转义后下划线按字面匹配。
-- 输出：1；literal_underscore=1。

% 匹配任意长度，_ 匹配一个字符；大小写与重音行为受排序规则影响。前导 % 通常无法利用普通 B+Tree 做前缀范围定位。

SELECT IN：匹配离散候选值

用于把同一字段的一组候选值表示为集合成员条件。

-- 初始：订单金额为 101/25、102/15、103/40。
SELECT id FROM learning_lab.orders WHERE id IN (101,103) ORDER BY id;
SELECT 9 IN (1,NULL) AS unknown_member;
-- 关键变化：返回两个目标订单，未匹配且集合有 NULL 的比较返回 NULL。
-- 输出：101、103；unknown_member=NULL。

空候选集合应由应用明确处理，不要生成非法 IN ()。NOT IN 的 NULL 陷阱见 子查询篇。

SELECT BETWEEN：使用包含端点的范围

用于查询闭区间数值，并对日期使用更易组合的半开区间。

-- 初始：种子订单都位于 2026-10-05，金额 15、25、40。
SELECT id FROM learning_lab.orders WHERE amount BETWEEN 15 AND 25 ORDER BY id;
SELECT id FROM learning_lab.orders
WHERE created_at >= '2026-10-05 00:00:00'
  AND created_at < '2026-10-06 00:00:00' AND id BETWEEN 101 AND 103 ORDER BY id;
-- 关键变化：金额过滤返回 101、102；半开日期区间返回三单。
-- 输出：101、102；101、102、103。

BETWEEN 包含上下界；BETWEEN '2026-10-05' AND '2026-10-05 23:59:59' 会遗漏带微秒的末秒数据。

SELECT CASE：映射展示标签

用于按条件计算标签而不修改订单状态。

-- 初始：101 金额 25，102 金额 15，103 金额 40。
SELECT id, CASE WHEN amount >= 30 THEN '大额' ELSE '普通' END AS label
FROM learning_lab.orders WHERE id BETWEEN 101 AND 103 ORDER BY id;
-- 关键变化：金额40的103返回大额，25与15的订单返回普通。
-- 输出：101/普通、102/普通、103/大额。

CASE 按顺序选择首个成立分支；没有匹配且没有 ELSE 时返回 NULL，分支结果类型会共同决定表达式类型。

SELECT DISTINCT：去除重复投影

用于从订单中获取去重后的用户编号集合。

-- 初始：101、102 属于用户 1，103 属于用户 2。
SELECT DISTINCT user_id FROM learning_lab.orders WHERE id BETWEEN 101 AND 103 ORDER BY user_id;
-- 关键变化：两条 user_id=1 合并为一个投影值。
-- 输出：1、2。

多列 DISTINCT 对整个列组合去重，不保证每一列都独立唯一；它也不能修复错误 JOIN 造成的行数膨胀。

SELECT ORDER BY：建立稳定的排序

用于按金额降序展示订单，并用主键解决金额相同时的顺序。

-- 初始：订单金额分别 25、15、40。
SELECT id,amount FROM learning_lab.orders WHERE id BETWEEN 101 AND 103
ORDER BY amount DESC,id DESC;
-- 关键变化：结果重排为 103/40、101/25、102/15。
-- 输出：id 顺序 103、101、102。

ORDER BY 决定结果顺序，不承诺数据库一定借助索引完成排序。默认升序时 NULL 位于非 NULL 前面。

SELECT LIMIT：偏移分页

用于在小结果集上读取固定页长的某一页。

-- 初始：101、102、103 按 id 升序排列。
SELECT id FROM learning_lab.orders WHERE id BETWEEN 101 AND 103
ORDER BY id LIMIT 1 OFFSET 1;
-- 关键变化：跳过 101 后返回 102。
-- 输出：102。

深偏移通常需要扫描或丢弃大量前置记录。并发新增、删除会造成跨页重复或遗漏；全序只解决同一数据状态下的排序不确定性。

SELECT keyset：按游标取下一页

用于把上一页最后一条记录的排序键作为下一页定位边界。

-- 初始：按 amount DESC,id DESC，上一页最后一条为 (40.00,103)。
SELECT id,amount FROM learning_lab.orders
WHERE id BETWEEN 101 AND 103
  AND (amount < 40.00 OR (amount=40.00 AND id<103))
ORDER BY amount DESC,id DESC LIMIT 2;
-- 关键变化：游标之后返回 101/25.00、102/15.00。
-- 输出：101、102。

游标包含全部排序键和唯一主键；使用可变金额排序时，跨请求更新仍会移动行。稳定浏览可改用不可变创建时间与主键，或采用业务快照；keyset 不支持直接跳到任意页。

SELECT COALESCE()：提供缺失值回退

用于将可空值转为明确的展示默认值。

-- 输入：NULL 和 空字符串。
SELECT COALESCE(NULL,'未填写') AS missing, COALESCE('','未填写') AS empty_text;
-- 关键变化：只对 NULL 返回默认值。
-- 输出：未填写 | 空字符串。

COALESCE 返回首个非 NULL 参数；不要在过滤列上随意包函数，否则可能失去索引范围查询机会。

SELECT CONCAT()：组合展示文本

用于把多个字段组成展示字符串，并理解 NULL 的传播。

-- 输入：用户名林同学与金额100；另一组含 NULL。
SELECT CONCAT('林同学',':',100) AS label, CONCAT('林同学',NULL) AS unknown_label;
-- 关键变化：一组拼接为林同学:100，另一组返回 NULL。
-- 输出：林同学:100 | NULL。

字符串拼接可用于展示，不能用于拼接用户输入组成 SQL。应用对值使用参数绑定；表名、列名、ASC/DESC 等结构片段不能作为普通值占位符，必须映射到预先定义的白名单。LIKE 的值仍需按业务转义通配符，绑定参数只解决 SQL 注入边界。预处理语句。

SELECT DATE_FORMAT()：格式化展示日期

用于把确定的日期时间按指定格式呈现给调用方。

-- 输入：2026-10-05 10:00:00。
SELECT DATE_FORMAT('2026-10-05 10:00:00','%Y-%m-%d') AS day;
-- 关键变化：返回日期字符串 2026-10-05。
-- 输出：2026-10-05。

展示格式不改变 DATETIME 的时区约定。按日期过滤优先使用原列的半开范围，避免 WHERE 中格式化整列；日期函数见 官方说明。

易混点

NULL 不是空字符串；LIMIT 不创造排序；绑定参数不允许任意动态列名。查询函数正确与索引友好是两项独立判断，执行计划在第十篇验证。

课后小问

1. 为什么 WHERE balance=NULL 找不到缺失值？

   答：比较产生 UNKNOWN，WHERE 不保留它；应使用 IS NULL。

2. 为什么按金额翻页要带 id？

   答：相同金额没有唯一顺序，主键补足排序键；跨请求金额变更仍需要额外业务策略。

本节小结

把投影、条件与顺序分别表达清楚，先核对 NULL 与边界，再选择分页方式。所有来自外部的值通过参数绑定传入。

快速回顾

• WHERE 只保留 TRUE；IS NULL 检查未知值。
• BETWEEN 是闭区间；时间范围常用左闭右开。
• ORDER BY 补齐唯一键；深分页优先评估 keyset。