条件查询、排序与分页
🎯 引言
学完这篇文章,你能筛选需要的记录,并稳定地返回分页结果。
🛠 准备固定查询数据
在 blog_demo 中创建查询专用表,避免增删改练习影响结果:
CREATE TABLE query_user (
id INT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
age INT,
city VARCHAR(50) NOT NULL,
created_at DATETIME NOT NULL
);
插入固定数据,后面的统计、关联与性能文章都沿用它:
INSERT INTO query_user VALUES
(1, '小明', 25, '北京', '2026-10-01 09:00:00'),
(2, '小红', 30, '上海', '2026-10-02 09:00:00'),
(3, '小刚', 22, '北京', '2026-10-03 09:00:00'),
(4, '小林', 30, '上海', '2026-10-04 09:00:00'),
(5, '小安', NULL, '杭州', '2026-10-05 09:00:00');
插入后,query_user 的完整数据如下:
| id | username | age | city | created_at |
|---|---|---|---|---|
| 1 | 小明 | 25 | 北京 | 2026-10-01 09:00:00 |
| 2 | 小红 | 30 | 上海 | 2026-10-02 09:00:00 |
| 3 | 小刚 | 22 | 北京 | 2026-10-03 09:00:00 |
| 4 | 小林 | 30 | 上海 | 2026-10-04 09:00:00 |
| 5 | 小安 | NULL | 杭州 | 2026-10-05 09:00:00 |
只需初始化一次。如果表已存在,先检查数据是否一致,不要直接删除。每次重新连接客户端,先执行 USE blog_demo。
🔎 WHERE 筛选满足条件的行
找出北京、年龄至少 23 岁的用户:
SELECT id, username FROM query_user
WHERE city = '北京' AND age >= 23;
查询结果:
| id | username |
|---|---|
| 1 | 小明 |
AND 要求两个条件同时成立;OR 表示任一条件成立。混用时用括号说明分组:
SELECT id, username FROM query_user
WHERE (city = '北京' OR city = '上海') AND age >= 25
ORDER BY id;
查询结果:
| id | username |
|---|---|
| 1 | 小明 |
| 2 | 小红 |
| 4 | 小林 |
先选北京或上海,再排除年龄小于 25 的人。
📍 IN 查询多个候选值
SELECT id, username FROM query_user
WHERE city IN ('北京', '杭州') ORDER BY id;
查询结果:
| id | username |
|---|---|
| 1 | 小明 |
| 3 | 小刚 |
| 5 | 小安 |
IN 表示城市等于列表中的任一个值,适合多个城市筛选。
🔤 LIKE 按文字片段查询
SELECT id, username FROM query_user WHERE username LIKE '小%';
查询结果:
| id | username |
|---|---|
| 1 | 小明 |
| 2 | 小红 |
| 3 | 小刚 |
| 4 | 小林 |
| 5 | 小安 |
% 表示任意长度的文字;搜索包含某段文字可以在两侧放 %,但大量数据上的包含搜索需要单独评估性能。
🕳 缺少值时用 IS NULL
SELECT id, username FROM query_user WHERE age IS NULL;
查询结果:
| id | username |
|---|---|
| 5 | 小安 |
不能写 age = NULL;NULL 表示未知,普通等值比较不能用它判断缺失。
显示时可以给未知年龄一个替代值:
SELECT username, COALESCE(age, 0) AS display_age
FROM query_user WHERE id = 5;
查询结果:
| username | display_age |
|---|---|
| 小安 | 0 |
COALESCE 选择第一个非 NULL 的值,只改变查询结果,不会把表中年龄修改为 0。实际页面也可以直接显示“未填写”。
🗓 查询时间范围
查询 10 月 2 日和 3 日的数据:
SELECT id, username FROM query_user
WHERE created_at >= '2026-10-02' AND created_at < '2026-10-04'
ORDER BY id;
查询结果:
| id | username |
|---|---|
| 2 | 小红 |
| 3 | 小刚 |
使用“包含开始、不含结束”的范围,能覆盖结束前的时分秒,不必拼出 23:59:59。
📄 排序后再分页
按年龄从大到小,相同年龄按 id 从小到大:
SELECT id, username, age FROM query_user
ORDER BY age DESC, id ASC LIMIT 2;
查询结果:
| id | username | age |
|---|---|---|
| 2 | 小红 | 30 |
| 4 | 小林 | 30 |
DESC 是降序,ASC 是升序;增加 id 能让同龄记录也有确定顺序。
读取同一排序的下一页:
SELECT id, username, age FROM query_user
ORDER BY age DESC, id ASC LIMIT 2 OFFSET 2;
查询结果:
| id | username | age |
|---|---|---|
| 1 | 小明 | 25 |
| 3 | 小刚 | 22 |
LIMIT 2 表示取两条,OFFSET 2 表示跳过前两条。每页大小为 pageSize 时,跳过数量为 (page - 1) × pageSize。
稳定排序解决同值记录顺序不明确的问题,但翻页期间发生插入、删除,偏移分页仍可能重复或遗漏记录;连续加载列表的处理见性能篇。
❓ 知识问答
Q1:没有 ORDER BY 时会按 id 排序吗?
不能依赖这种现象。数据库没有承诺默认返回顺序,需要顺序就显式写 ORDER BY。
Q2:如何得到不重复的城市列表?
使用 SELECT DISTINCT city FROM query_user ORDER BY city;DISTINCT 按所选列的组合去重。
Q3:BETWEEN 能查日期吗?
能,但它包含两端。日期时间查询通常使用大于等于开始、小于结束的范围,边界更清楚。
🧪 小练习
查询上海用户,按 id 倒序取一条,预期得到编号 4 的小林。再查询未填写年龄的用户。
-- 请在这里编写 SQL 或记录验证结果
