SELECT 是 MySQL 中最常用的查询语句,可以完成列选择、条件筛选、排序、分组聚合、表连接、子查询和分页。本文使用 sys_user、orders 等示例表,系统讲解 MySQL 8 的查询方法和常见错误。
一、SELECT 基本结构
SELECT [DISTINCT] select_list FROM table_name [JOIN ...] [WHERE condition] [GROUP BY group_expression] [HAVING group_condition] [ORDER BY sort_expression] [LIMIT row_count OFFSET offset];
并不是每条查询都必须包含所有子句。最简单的 SELECT 甚至不需要数据表。
二、无表查询
查询当前时间、执行计算或调用函数:
SELECT NOW() AS current_time; SELECT 1 + 1 AS result; SELECT VERSION() AS mysql_version;
MySQL 允许省略 FROM DUAL。下面两种写法结果相同:
SELECT NOW(); SELECT NOW() FROM DUAL;
三、查询全部列与指定列
-- 查询全部列 SELECT * FROM sys_user; -- 只查询需要的列 SELECT id, username, email FROM sys_user;
生产代码建议明确写出需要的列,而不是长期使用 SELECT *:
- 减少不必要的数据传输。
- 避免表结构新增字段后影响程序。
- 更容易利用覆盖索引。
- 查询结果的字段含义更清晰。
四、字段别名和计算列
使用 AS 为字段或表达式设置别名:
SELECT id,
username AS user_name,
age,
age + 1 AS age_next_year
FROM sys_user;
AS 可以省略,但保留它通常更容易阅读。别名只改变查询结果的列名,不会修改表结构。
五、DISTINCT 去除重复结果
SELECT DISTINCT city FROM sys_user;
多列 DISTINCT 判断的是列组合:
SELECT DISTINCT city, status FROM sys_user;
只有 city 和 status 都相同的结果行才会被合并。不要为了掩盖错误连接产生的重复数据而盲目添加 DISTINCT。
六、WHERE 条件筛选
1. 比较运算符
SELECT id, username, age FROM sys_user WHERE username = 'Helen'; SELECT id, username, age FROM sys_user WHERE age >= 18;
常见比较运算符:
| 运算符 | 含义 |
|---|---|
| = | 等于 |
| <> 或 != | 不等于 |
| >、>= | 大于、大于等于 |
| <、<= | 小于、小于等于 |
2. BETWEEN 范围查询
SELECT id, username, age FROM sys_user WHERE age BETWEEN 18 AND 30;
BETWEEN 18 AND 30 包含 18 和 30。等价写法为:
SELECT id, username, age FROM sys_user WHERE age >= 18 AND age <= 30;
3. IN 多值查询
SELECT id, username, age
FROM sys_user
WHERE age IN (3, 7, 18);
SELECT id, username
FROM sys_user
WHERE username IN ('Yvonne', 'Chloe', 'Maite');
原写法如果缺少 IN,例如 WHERE username (...),属于语法错误。
4. NOT BETWEEN 与 NOT IN
SELECT id, username, age FROM sys_user WHERE age NOT BETWEEN 0 AND 7; SELECT id, username, age FROM sys_user WHERE age NOT IN (3, 7, 9);
NOT IN 列表中如果包含 NULL,可能因为 SQL 三值逻辑导致结果与直觉不同。列表来自子查询时,应明确排除 NULL,或改用适合的 NOT EXISTS。
七、AND、OR 与 NOT
SELECT id, username, age, status FROM sys_user WHERE status = 1 AND age >= 18;
AND 的优先级高于 OR。混合使用时应添加括号表达真实意图:
SELECT id, username, age, status FROM sys_user WHERE status = 1 AND (age < 18 OR age >= 60);
如果省略括号,查询含义可能发生变化,即使语法仍然正确。
八、LIKE 模糊查询
%:匹配任意数量的字符,包括零个字符。_:匹配一个字符。
-- 以 A 开头 SELECT id, username FROM sys_user WHERE username LIKE 'A%'; -- 以“裙”结尾 SELECT id, username FROM sys_user WHERE username LIKE '%裙'; -- 三个字符,最后一个是“女” SELECT id, username FROM sys_user WHERE username LIKE '__女';
查询值本身包含 % 或 _ 时,需要转义:
SELECT id, username FROM sys_user WHERE username LIKE '%10\%%' ESCAPE '\\';
以 % 开头的条件通常难以使用普通 B-Tree 索引完成前缀定位。数据量较大时,应根据业务评估全文索引、搜索引擎或其他检索方式。
九、NULL 与空字符串
NULL 表示未知或缺失,空字符串 '' 是长度为 0 的已知字符串,两者不是同一个值。
-- 查询 NULL SELECT id, username, email FROM sys_user WHERE email IS NULL; -- 查询非 NULL SELECT id, username, email FROM sys_user WHERE email IS NOT NULL; -- 查询空字符串 SELECT id, username, email FROM sys_user WHERE email = '';
不要写成:
-- 错误示例:不会按预期匹配 NULL SELECT * FROM sys_user WHERE email = NULL;
任何普通比较只要涉及 NULL,结果通常也是 NULL,而不是 true。MySQL 还提供 NULL 安全等号 <=>:
SELECT NULL <=> NULL AS both_null,
email <=> NULL AS email_is_null
FROM sys_user;
COUNT(*) 与 COUNT(column)
SELECT COUNT(*) AS total_rows,
COUNT(email) AS non_null_emails
FROM sys_user;
COUNT(*) 统计行数,COUNT(email) 忽略 email IS NULL 的记录,但会统计空字符串。
十、ORDER BY 排序
-- 年龄升序,ASC 可以省略 SELECT id, username, age FROM sys_user ORDER BY age ASC; -- 年龄降序 SELECT id, username, age FROM sys_user ORDER BY age DESC;
多列排序按从左到右依次处理:
SELECT id, username, age FROM sys_user ORDER BY age ASC, id ASC;
当多个用户年龄相同时,再按 id 升序排列。加入唯一列作为最后排序条件,可获得稳定、可重复的顺序。
十一、LIMIT 与分页
查询前 10 条:
SELECT id, username, age FROM sys_user ORDER BY id ASC LIMIT 10;
跳过前 5 条,再返回 10 条:
SELECT id, username, age FROM sys_user ORDER BY id ASC LIMIT 10 OFFSET 5;
MySQL 也支持 LIMIT 5, 10,其中第一个参数是偏移量,第二个参数是返回数量。但 LIMIT row_count OFFSET offset 更容易阅读。
分页必须有稳定排序
如果只按非唯一字段排序,相同值之间的顺序不确定,翻页时可能出现重复或遗漏。应增加唯一列:
SELECT id, username, created_at FROM sys_user ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 40;
深分页优化
偏移量很大时,MySQL 仍需扫描并丢弃前面的记录。可以使用上一次结果的排序键进行游标分页:
SELECT id, username, created_at
FROM sys_user
WHERE (created_at, id) < ('2026-11-14 10:00:00', 5000)
ORDER BY created_at DESC, id DESC
LIMIT 20;
该方式要求排序字段和比较条件保持一致,并建立合适的联合索引。
十二、聚合函数
常用聚合函数包括 COUNT()、SUM()、AVG()、MIN() 和 MAX():
SELECT COUNT(*) AS user_count,
MIN(age) AS min_age,
MAX(age) AS max_age,
AVG(age) AS average_age
FROM sys_user
WHERE status = 1;
除 COUNT(*) 外,多数聚合函数会忽略表达式中的 NULL。
十三、GROUP BY 分组
统计每个状态的用户数:
SELECT status,
COUNT(*) AS user_count
FROM sys_user
GROUP BY status
ORDER BY status;
多字段分组:
SELECT city,
status,
COUNT(*) AS user_count
FROM sys_user
GROUP BY city, status
ORDER BY city, status;
ONLY_FULL_GROUP_BY
MySQL 8 默认 SQL 模式通常包含 ONLY_FULL_GROUP_BY。查询列必须是聚合表达式、分组列,或能由分组列函数依赖确定。
-- 不规范:username 既没有聚合,也不在 GROUP BY 中 SELECT status, username, COUNT(*) FROM sys_user GROUP BY status;
不要通过关闭 ONLY_FULL_GROUP_BY 掩盖含义不明确的查询,应明确需要哪一行数据或使用适当的聚合方式。
十四、HAVING 分组后筛选
WHERE 在分组前过滤原始记录,HAVING 在聚合后过滤结果:
SELECT city,
COUNT(*) AS user_count,
ROUND(AVG(age), 2) AS average_age
FROM sys_user
WHERE status = 1
GROUP BY city
HAVING COUNT(*) >= 10
ORDER BY user_count DESC;
HAVING 常与 GROUP BY 配合,但不能简单说没有 GROUP BY 就绝对不能使用。对于普通行级条件,应优先放在 WHERE,让数据库尽早减少参与分组的数据。
十五、表连接 JOIN
INNER JOIN
只返回两张表能够匹配的记录:
SELECT o.id,
o.order_no,
u.username,
o.amount
FROM orders AS o
INNER JOIN sys_user AS u
ON u.id = o.user_id
WHERE o.status = 1
ORDER BY o.id DESC;
LEFT JOIN
保留左表全部记录,右表无法匹配时返回 NULL:
SELECT u.id,
u.username,
COUNT(o.id) AS order_count
FROM sys_user AS u
LEFT JOIN orders AS o
ON o.user_id = u.id
GROUP BY u.id, u.username
ORDER BY u.id;
ON 与 WHERE 的区别
对 LEFT JOIN 右表的过滤条件放在 WHERE 中,可能把没有匹配行的结果过滤掉,使查询效果接近内连接:
SELECT u.id, u.username, o.id AS order_id FROM sys_user AS u LEFT JOIN orders AS o ON o.user_id = u.id AND o.status = 1;
上面把订单状态放在 ON 中,仍会保留没有有效订单的用户。
十六、子查询与 EXISTS
查询存在有效订单的用户:
SELECT u.id, u.username
FROM sys_user AS u
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.user_id = u.id
AND o.status = 1
);
EXISTS 只关心子查询是否返回记录,SELECT 1 不表示只查询 ID 为 1 的记录。
查询没有订单的用户:
SELECT u.id, u.username FROM sys_user AS u WHERE NOT EXISTS ( SELECT 1 FROM orders AS o WHERE o.user_id = u.id );
当子查询字段可能包含 NULL 时,NOT EXISTS 往往比 NOT IN 更不容易产生三值逻辑问题。
十七、SELECT 的逻辑处理顺序
便于理解的逻辑顺序通常是:
FROM / JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT
这解释了为什么 WHERE 通常不能直接引用同级 SELECT 中刚定义的别名,而 ORDER BY 可以使用结果列别名。
SELECT age + 1 AS next_age FROM sys_user WHERE age + 1 >= 18 ORDER BY next_age;
实际执行计划由优化器决定,不会机械地按文本顺序逐步执行。逻辑顺序用于理解查询语义,性能分析应查看执行计划。
十八、使用 EXPLAIN 查看执行计划
EXPLAIN SELECT id, username, age FROM sys_user WHERE status = 1 AND age >= 18 ORDER BY age, id LIMIT 20;
MySQL 8 还可使用 EXPLAIN ANALYZE 实际执行查询并显示运行统计:
EXPLAIN ANALYZE SELECT id, username, age FROM sys_user WHERE status = 1 AND age >= 18 ORDER BY age, id LIMIT 20;
EXPLAIN ANALYZE 会真实执行语句,生产环境使用前要评估查询开销。
十九、应用程序中的安全查询
不要把用户输入直接拼接进 SQL:
// 错误示例 $sql = "SELECT * FROM sys_user WHERE username = '" . $_GET['name'] . "'";
应使用预处理参数:
$stmt = $pdo->prepare(
'SELECT id, username, email
FROM sys_user
WHERE username = ?'
);
$stmt->execute([$username]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
参数绑定只能绑定值,不能直接绑定表名、列名或排序方向。动态排序字段应在服务器端白名单中选择。
二十、常见错误
- 长期使用
SELECT *,读取大量不需要的列。 - 使用
= NULL或<> NULL判断空值。 AND、OR混用时不加括号。- 使用
LIMIT分页却没有稳定的ORDER BY。 - 深分页使用非常大的
OFFSET,没有考虑游标分页。 - 在
GROUP BY查询中选择含义不明确的非聚合列。 - 把
LEFT JOIN右表条件错误地放进WHERE。 - 用
DISTINCT掩盖错误连接产生的重复行。 - 把用户输入直接拼接进 SQL。
- 只凭感觉添加索引,不查看执行计划和真实数据分布。
二十一、完整查询示例
查询有效用户的订单统计,只显示订单数不少于 3 的用户,按消费总额降序分页:
SELECT u.id,
u.username,
COUNT(o.id) AS order_count,
ROUND(SUM(o.amount), 2) AS total_amount,
MAX(o.created_at) AS latest_order_time
FROM sys_user AS u
INNER JOIN orders AS o
ON o.user_id = u.id
AND o.status = 1
WHERE u.status = 1
GROUP BY u.id, u.username
HAVING COUNT(o.id) >= 3
ORDER BY total_amount DESC, u.id DESC
LIMIT 20 OFFSET 0;
这个示例包含:
JOIN:关联用户和订单。ON:限定有效订单。WHERE:分组前筛选有效用户。GROUP BY:按用户聚合。HAVING:过滤订单数不足的分组。ORDER BY:按总额和唯一 ID 稳定排序。LIMIT:限制当前页数量。
二十二、官方文档
- MySQL 8.0 SELECT Statement
- MySQL GROUP BY Handling
- Problems with NULL Values
- LIMIT Query Optimization
二十三、总结
SELECT 的核心流程是确定数据来源、筛选记录、分组聚合、筛选分组、排序并限制结果。写查询时应明确列、正确处理 NULL、保持分页排序稳定,并通过参数化查询和执行计划兼顾安全与性能。