条件查询、排序与分页

🎯 引言

上一篇我们学会了建库建表和增删改查的基本操作，但每次查询都是「把整张表拿出来」，真实项目里很少这样干。学完这篇文章，你能用 WHERE 精确筛选数据、用 ORDER BY 排序、用 LIMIT 做分页，还能用聚合函数和 GROUP BY 做统计，从表里拿出任何你想要的那部分数据。

🛠 准备示例数据

我们沿用 user 用户表，先建表并插入一批不同城市、不同年龄的数据，后面的查询都基于这批数据演示。

CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    age INT,
    city VARCHAR(50),
    created_at DATETIME
);

INSERT INTO user (username, age, city, created_at) VALUES
    ('张三', 25, '北京', '2024-01-05 10:00:00'),
    ('李四', 30, '上海', '2024-01-12 09:30:00'),
    ('王五', 22, '北京', '2024-02-03 14:20:00'),
    ('赵六', 28, '广州', '2024-02-18 16:45:00'),
    ('钱七', 35, '上海', '2024-03-01 08:15:00'),
    ('孙八', 19, '北京', '2024-03-10 11:00:00'),
    ('周九', 30, '广州', '2024-03-22 19:40:00'),
    ('吴十', NULL, '深圳', '2024-04-02 13:25:00'),
    ('郑一', 26, '上海', '2024-04-15 15:50:00');

注意「吴十」的 age 是 NULL，这是故意的，后面讲 IS NULL 时会用到。插入后可以用 SELECT * FROM user; 确认数据都在。执行结果如下：

🧱 WHERE 条件查询

WHERE 的作用是筛选行：只返回满足条件的记录，不满足的直接丢掉。可以把它类比成前端数组的 filter 方法，users.filter(u => u.age > 25) 和 SQL 的 WHERE age > 25 是同一个思路。

常用的比较运算符有 =（等于）、>（大于）、<（小于）、!=（不等于），还有 >=、<=。

-- 查出年龄大于 25 的用户
SELECT * FROM user WHERE age > 25;

-- 查出城市是「北京」的用户
SELECT * FROM user WHERE city = '北京';

-- 查出年龄不等于 30 的用户
SELECT * FROM user WHERE age != 30;

SELECT * FROM user WHERE age > 25; 的执行结果如下：

注意吴十的 age 是 NULL，NULL 和 25 比较结果不为真，所以它不会出现在结果里。

SELECT * FROM user WHERE city = '北京'; 的执行结果如下：

SELECT * FROM user WHERE age != 30; 的执行结果如下：

吴十的 age 是 NULL，NULL != 30 同样不成立，所以它也被排除了。

✨ AND / OR 组合条件

一个条件不够用时，可以用 AND（并且）和 OR（或者）把多个条件组合起来，逻辑和 JS 里的 &&、|| 一样。

-- 北京且年龄大于 20 的用户
SELECT * FROM user WHERE city = '北京' AND age > 20;

-- 北京或上海的用户
SELECT * FROM user WHERE city = '北京' OR city = '上海';

SELECT * FROM user WHERE city = '北京' AND age > 20; 的执行结果如下：

孙八也是北京的，但年龄 19 不满足 age > 20，被筛掉了。

SELECT * FROM user WHERE city = '北京' OR city = '上海'; 的执行结果如下：

当 AND 和 OR 同时出现时，AND 的优先级更高，容易写出和预期不符的条件。建议养成加括号的习惯：

-- 年龄大于 30，或者（北京且年龄大于 20）
SELECT * FROM user WHERE age > 30 OR (city = '北京' AND age > 20);

执行结果如下：

钱七因为年龄 35 满足 age > 30 入选，张三和王五因为满足括号里「北京且年龄大于 20」入选。

💡 BETWEEN、IN、LIKE、IS NULL

除了比较运算符，还有四个常用的条件写法，各自解决一类场景。

BETWEEN ... AND ... 表示闭区间，包含两端，适合查范围：

-- 年龄在 22 到 28 之间的用户（含 22 和 28）
SELECT * FROM user WHERE age BETWEEN 22 AND 28;

执行结果如下：

王五 22 岁和赵六 28 岁正好卡在两端，因为是闭区间所以都包含在内。

IN 表示「在几个值之中」，比写一长串 OR 清爽：

-- 北京、上海、广州的用户
SELECT * FROM user WHERE city IN ('北京', '上海', '广州');

执行结果如下：

只有深圳的吴十不在 IN 列表里，被筛掉了。

LIKE 是模糊查询，配合通配符 %（匹配任意多个字符）使用：

-- 用户名以「张」开头
SELECT * FROM user WHERE username LIKE '张%';

-- 用户名以「三」结尾
SELECT * FROM user WHERE username LIKE '%三';

-- 用户名中包含「三」
SELECT * FROM user WHERE username LIKE '%三%';

这三条语句在这批数据里的执行结果相同，都只匹配到张三：

如果表里有「小张」「张三丰」之类的数据，三条语句的结果就不一样了：LIKE '张%' 能匹配「张三丰」但匹配不到「小张」，LIKE '%三' 能匹配「张三丰」但匹配不到「三月」，LIKE '%三%' 则两边都能匹配。

IS NULL 用来查空值。空值在数据库里是「没有值」，不是 0 也不是空字符串，判断时不能用 = NULL：

-- 查出年龄为空的用户（吴十）
SELECT * FROM user WHERE age IS NULL;

-- 反过来：年龄不为空的用户
SELECT * FROM user WHERE age IS NOT NULL;

SELECT * FROM user WHERE age IS NULL; 的执行结果如下：

SELECT * FROM user WHERE age IS NOT NULL; 的执行结果如下：

🧰 ORDER BY 排序

查询结果的默认顺序不保证稳定，想要有序的结果就要显式写 ORDER BY。ASC 表示升序（从小到大，默认值可省略），DESC 表示降序（从大到小）。

-- 按年龄从小到大排序
SELECT * FROM user ORDER BY age ASC;

-- 按创建时间从新到旧排序
SELECT * FROM user ORDER BY created_at DESC;

SELECT * FROM user ORDER BY age ASC; 的执行结果如下：

升序时 NULL 会排在前面，所以吴十出现在第一行。

SELECT * FROM user ORDER BY created_at DESC; 的执行结果如下：

还可以按多列排序：先按第一列排，第一列相同的再按第二列排。

-- 先按城市升序，同城内再按年龄降序
SELECT * FROM user ORDER BY city ASC, age DESC;

执行结果如下：

可以看到同一个城市的行聚在一起，城市内部再按年龄从大到小排，比如上海三人依次是钱七 35、李四 30、郑一 26。

⚡ LIMIT 分页

数据量大了以后，一次查出全部数据既慢又没必要，前端列表也是一页一页展示的。SQL 里用 LIMIT 限制返回的条数：

-- 只取前 3 条
SELECT * FROM user LIMIT 3;

执行结果如下：

分页的写法是 LIMIT offset, count：跳过 offset 条，再取 count 条。类比前端分页「第 2 页，每页 3 条」：跳过前 3 条，取接下来的 3 条。

-- 第 1 页：LIMIT 0, 3
-- 第 2 页：跳过前 3 条，取 3 条
SELECT * FROM user ORDER BY id ASC LIMIT 3, 3;

-- 第 3 页
SELECT * FROM user ORDER BY id ASC LIMIT 6, 3;

SELECT * FROM user ORDER BY id ASC LIMIT 3, 3; 的执行结果如下（第 2 页）：

SELECT * FROM user ORDER BY id ASC LIMIT 6, 3; 的执行结果如下（第 3 页）：

规律是：offset = (页码 - 1) × 每页条数。做分页时一定要配合 ORDER BY，否则每次返回的「第 2 页」可能不是同一批数据。

🧱 聚合函数与 GROUP BY

有时候我们关心的不是每一行数据，而是统计结果，比如「一共有多少用户」「平均年龄多大」。这时用 聚合函数，它对一批数据做计算，返回一个值：

-- 用户总数
SELECT COUNT(*) FROM user;

-- 年龄的总和、平均值、最大值、最小值
SELECT SUM(age), AVG(age), MAX(age), MIN(age) FROM user;

SELECT COUNT(*) FROM user; 的执行结果如下：

SELECT SUM(age), AVG(age), MAX(age), MIN(age) FROM user; 的执行结果如下：

这些聚合函数都会忽略 NULL，所以吴十不参与年龄的计算，平均值是 8 个人的平均值。

COUNT(*) 统计所有行；COUNT(age) 只统计 age 不为 NULL 的行，所以我们这批数据的两个结果会不一样，可以动手验证一下。

GROUP BY 是分组统计：先按某个字段把数据分成若干组，再在每组里分别聚合。比如「每个城市有多少人」：

SELECT city, COUNT(*) AS total
FROM user
GROUP BY city;

执行结果如下：

可以把它类比成前端按城市 reduce 归类后再数每组的长度。AS total 是给统计列起别名，让结果更好读。

⚖️ HAVING：分组之后再过滤

WHERE 和 HAVING 都是过滤，区别一句话说清：WHERE 在分组前过滤行，HAVING 在分组后过滤组。

比如「人数超过 2 人的城市」，这个条件针对的是分组后的统计结果，只能写在 HAVING 里：

SELECT city, COUNT(*) AS total
FROM user
GROUP BY city
HAVING total > 2;

执行结果如下：

广州 2 人、深圳 1 人不满足 total > 2，分组后被 HAVING 筛掉了。

如果只想统计部分城市，比如「只看北京和上海，且人数超过 2 人」，那就是 WHERE 和 HAVING 一起上：

SELECT city, COUNT(*) AS total
FROM user
WHERE city IN ('北京', '上海')
GROUP BY city
HAVING total > 2;

执行结果如下：

执行顺序是：先用 WHERE 筛行，再用 GROUP BY 分组，最后用 HAVING 筛组。

🧾 小节总结

• WHERE 筛选行，常用运算符有 =、>、<、!=，组合条件用 AND、OR，混用时记得加括号。
• BETWEEN 查闭区间，IN 替代多个 OR，LIKE 配合 % 做模糊查询，查空值必须用 IS NULL 而不是 = NULL。
• ORDER BY 列名 ASC/DESC 排序，多列排序时前面的列优先。
• LIMIT offset, count 做分页，offset = (页码 - 1) × 每页条数，分页一定要配 ORDER BY。
• COUNT、SUM、AVG、MAX、MIN 做聚合统计，GROUP BY 按字段分组后分别统计。
• WHERE 在分组前过滤行，HAVING 在分组后过滤组。

❓ 知识问答

Q1：WHERE age = NULL 为什么查不到年龄为空的用户？

NULL 表示「未知」，它和任何值比较（包括它自己）结果都不是真，所以 = NULL 永远匹配不到行。正确写法是 WHERE age IS NULL。

Q2：LIMIT 3, 3 里的两个数字分别是什么意思？

第一个数是跳过的条数（offset），第二个数是取的条数（count）。LIMIT 3, 3 就是跳过前 3 条，再取 3 条，对应「每页 3 条时的第 2 页」。

Q3：COUNT(*) 和 COUNT(age) 有什么区别？

COUNT(*) 统计所有行，不管字段是不是 NULL；COUNT(age) 只统计 age 不为 NULL 的行。本文示例中两条语句的结果不同，原因就在这里。

Q4：聚合统计时为什么城市为空的行也出现在结果里？

GROUP BY 会把 NULL 也当成一个分组。如果不想看到它，在分组前加 WHERE city IS NOT NULL 过滤掉即可。

Q5：WHERE 和 HAVING 能互换吗？

不能。WHERE 在分组前执行，里面不能用聚合结果；HAVING 在分组后执行，专门筛分组后的统计值。能放在 WHERE 里的条件就尽量放 WHERE，先筛掉数据再分组，效率更高。

🧪 小练习

基于本文开头的 user 示例数据，完成下面两个查询：

1. 查出「年龄在 20 到 30 岁之间，且来自北京或上海」的用户，按年龄从大到小排序。

-- 请在这里编写 SQL

2. 统计每个城市的平均年龄，只保留平均年龄大于 26 的城市。

-- 请在这里编写 SQL

🎉 恭喜你已经掌握 MySQL 条件查询与分页统计技能啦！下一篇我们聊聊数据类型与约束，看看建表时每个字段该怎么选类型、怎么防止脏数据混进表里。