人人都会AI编程

9.1 基础查询：SELECT、WHERE 条件、排序、分页

更新时间：2026-07-11

基础查询是 SQL 最核心的能力，也是日常开发中书写频率最高的语句。无论多复杂的报表、多精巧的业务逻辑，本质上都是由这些基础子句组合而成。本节以实用为导向，覆盖 SELECT、WHERE、排序、分页的常用写法和避坑要点。

9.1.1 SELECT：从表中检索数据

SELECT 语句的作用是从一张或多张表中读取数据，最常见的四种形态如下。

查询所有列

SELECT * FROM user;

代表返回表中所有列。在开发测试阶段，这个写法非常方便，可以快速预览数据。但在生产代码中，建议显式列出需要的列名，而不是使用 。原因是：

  • 避免读取不需要的列，减少网络传输和结果集内存占用。
  • 表结构变更（如增加列）时，不会导致未知的性能波动或程序异常。
  • 配合索引更易分析覆盖索引，执行计划更可控。

查询指定列

SELECT id, username, email FROM user;

只返回 id、username、email 三列。这是生产代码的标准写法，清晰且安全。

返回计算结果

SELECT 后面可以跟表达式或函数：

SELECT id, 
       CONCAT(first_name, ' ', last_name) AS full_name,
       DATEDIFF(NOW(), created_at) AS days_since_register
FROM user;

通过 AS 可以给表达式起一个别名，让结果集更易读。在排序和后续处理中也可以通过别名引用。

去除重复值

SELECT DISTINCT status FROM order;

DISTINCT 关键字会对结果集中的所有列进行组合去重。注意它只能放在所有列名的最前面，不能指定某几列去重。如果需要分组去重并保留其他字段，应使用 GROUP BY 或窗口函数。

9.1.2 WHERE：按条件过滤数据

WHERE 子句用于指定查询条件，从表中筛选出符合条件的行。MySQL 按以下顺序执行：FROM → WHERE → SELECT → ORDER BY → LIMIT，所以 WHERE 是在选择列之前就进行的过滤。

比较运算符

SELECT * FROM product WHERE price >= 100;
SELECT * FROM user WHERE email = 'admin@example.com';
SELECT * FROM order WHERE status != 'cancelled';

支持的运算符包括 =、!= 或 <>、>、>=、<、<=。注意 != 和 <> 等价。

逻辑运算组合条件

SELECT * FROM product 
WHERE category = '电子' 
  AND price BETWEEN 500 AND 2000 
  AND stock > 0;
  • AND：所有条件都满足。
  • OR：任一条件满足。当 AND 和 OR 混用时，AND 优先级高于 OR，建议用括号明确意图：
SELECT * FROM user 
WHERE (status = 'active' OR vip_level > 0) 
  AND last_login > '2024-01-01';
  • NOT：否定一个条件。

范围与集合判断

  • BETWEEN … AND …：等价于 >= 下界 AND <= 上界，对数值和日期类型非常方便。
  • IN：判断值是否在指定集合中。
SELECT * FROM order WHERE status IN ('paid', 'shipped', 'done');

IN 列表较长时比多个 OR 更清晰，且优化器可能会对其做更好的处理。如果要判断不在集合内，用 NOT IN。

模糊匹配

SELECT * FROM product WHERE name LIKE '%手机%';

LIKE 配合通配符进行字符串模糊匹配：% 代表零个或多个字符，_ 代表一个任意字符。

  • LIKE '%abc'：以 abc 结尾。
  • LIKE 'abc%'：以 abc 开头。
  • LIKE '%abc%'：包含 abc。

需要特别注意的是：以 % 开头的模糊查询无法使用普通 B+ 树索引，会导致全表扫描。如果必须做前缀模糊搜索，可考虑使用全文索引或通过搜索引擎（如 Elasticsearch）弥补。

空值判断

SELECT * FROM user WHERE referral_code IS NULL;
SELECT * FROM user WHERE referral_code IS NOT NULL;

NULL 不能用 = 或 != 来比较，必须使用 IS NULL 或 IS NOT NULL。因为 NULL 在 SQL 中代表“未知”，任何与 NULL 的比较结果都是未知（NULL），即 NULL = NULL 的结果是 NULL 而非 TRUE。

9.1.3 排序：ORDER BY

ORDER BY 用于对查询结果进行排序，可以指定一个或多个列，每个列后可选 ASC（升序，默认）或 DESC（降序）。

SELECT id, username, created_at 
FROM user 
ORDER BY created_at DESC, id ASC;

这个查询先按 created_at 降序排列，创建时间相同的行再按 id 升序排列。

排序的性能要点

  • MySQL 使用两种方式完成排序：利用索引顺序直接读取（最理想）或使用 filesort（在内存或磁盘中排序）。
  • 如果 ORDER BY 的列正好是某个索引的最左前缀连续列，且索引全列覆盖查询时，可以直接利用索引的有序性，避免额外排序开销。例如，对于联合索引 (status, created_at)，以下查询可以直接利用索引排序：
SELECT id, created_at FROM order 
WHERE status = 'paid' 
ORDER BY created_at DESC;
  • 当 ORDER BY 与 LIMIT 结合使用时，如果能够利用索引，MySQL 可以在读取到所需的少量行后立即终止，性能极佳。这就是为什么合理的索引能让分页查询快上几个数量级。

排序与字符集

排序规则由列的排序规则（Collation）决定。例如 utf8mb4_general_ci 是不区分大小写的，utf8mb4_bin 则区分大小写。在涉及中文排序时，默认的 utf8mb4_general_ci 按 Unicode 码点排序，并非拼音顺序，如果需要按拼音排序，可以在设计表时指定排序规则，或者在 SQL 中转换：

SELECT name FROM product ORDER BY CONVERT(name USING gbk);

9.1.4 分页：LIMIT 与 OFFSET

分页查询是应用开发中最常见的需求之一，MySQL 使用 LIMIT 和 OFFSET 来控制返回的行数和起始位置。

基本语法

SELECT * FROM article ORDER BY id LIMIT 20 OFFSET 40;

LIMIT 20 表示最多返回 20 行，OFFSET 40 表示跳过前 40 行。这条语句的效果是：返回第 41 到 60 行。

也可以使用简写形式：

SELECT * FROM article ORDER BY id LIMIT 40, 20;

这个 40, 20 中的 40 就是 OFFSET，20 是 LIMIT。但为了可读性，建议使用 LIMIT ... OFFSET ... 写法。

深分页问题

当 OFFSET 非常大时，MySQL 依然需要扫描并丢弃前面 OFFSET 数量的所有行。比如 LIMIT 20 OFFSET 1000000，MySQL 会扫描 1000020 行再丢弃前 1000000 行，性能极差，甚至造成慢查询。

解决深分页的方案：

  • 基于游标的分页（推荐）：不直接用 OFFSET，而是记住上一页最后一条数据的某个唯一键值，比如主键 id：
SELECT * FROM article 
WHERE id > 5000000 
ORDER BY id LIMIT 20;

前提是分页顺序与索引顺序一致，且不存在删除造成的空洞影响业务（如果丢失几行可以接受，这种模式性能最佳）。

  • 子查询定位法：先快速定位到 offset 处的主键值，再回表查询完整数据。
SELECT * FROM article 
WHERE id >= (SELECT id FROM article ORDER BY id LIMIT 1000000, 1)
ORDER BY id LIMIT 20;

这样回表只取 20 行数据，避免了大量回表开销。

  • 业务折中：对页数做限制，不允许翻到过大的页码；或者使用搜索引擎、列式存储等专门的技术来处理深度分页。

分页与排序的稳定性

如果排序的列有重复值，分页结果可能不稳定，出现同一行数据在不同页多次出现或漏掉。为了确保分页的一致性，ORDER BY 的最后一个排序列应该是一个唯一列（通常是主键 id），例如：

SELECT * FROM order 
ORDER BY create_time DESC, id DESC 
LIMIT 20 OFFSET 0;

这样即使 create_time 相同，确定性排序也能保证分页结果的稳定。

9.1.5 基础查询的优化意识

即使只用到最简单的语法，养成几个习惯也能大幅避免性能坑：

  • 只查需要的列：明确列名，勿用 SELECT *。
  • 尽早过滤：把能最大减少扫描行数的条件放在 WHERE 里，必要时创建合适的索引。
  • 利用索引避免排序：让 ORDER BY 的列和索引前缀匹配。
  • 避免大 OFFSET：改用基于游标的分页。
  • 善用 EXPLAIN：对每个涉及分页、排序的实际查询，都养成用 EXPLAIN 查看执行计划的习惯，确保类型至少是 range 或更好，且没有出现 Using filesort。

基础查询虽然简单，但它们是复杂 SQL 的基石。把这些语法细节和优化原则掌握好，你写出的 SQL 不仅能正确运行，还能在大数据量下保持高效。