SELECT 是 MySQL 中最常用的查询语句。本文通过 fruits 水果表,讲解字段查询、条件过滤、模糊匹配、排序、分页、聚合与分组等常见用法。
一、准备示例数据
下面的示例都基于这张水果表:
CREATE TABLE fruits (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(8, 2) NOT NULL,
stock INT NOT NULL DEFAULT 0,
supplier_id INT UNSIGNED,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB;
INSERT INTO fruits (name, category, price, stock, supplier_id) VALUES
('苹果', '仁果类', 8.50, 120, 101),
('香蕉', '热带水果', 6.20, 80, 102),
('橙子', '柑橘类', 10.20, 60, 101),
('榴莲', '热带水果', 38.00, 15, 103),
('葡萄', '浆果类', 16.80, 0, NULL);
二、SELECT 基本语法
SELECT 字段列表 FROM 表名 WHERE 查询条件 GROUP BY 分组字段 HAVING 分组条件 ORDER BY 排序字段 LIMIT 返回数量;
各子句必须按上述顺序书写,但并非每个查询都要包含全部子句。
1. 查询所有字段
SELECT * FROM fruits;
* 适合临时查看数据。业务代码中建议明确列出所需字段,减少无用数据传输,也避免表结构改变影响结果。
2. 查询指定字段
SELECT id, name, price FROM fruits;
3. 使用字段别名
SELECT
name AS fruit_name,
price AS unit_price
FROM fruits;
AS 可以省略,但保留它通常更容易阅读。
4. 去除重复结果
SELECT DISTINCT category FROM fruits;
三、使用 WHERE 过滤记录
1. 比较运算符
| 运算符 | 含义 |
|---|---|
= | 等于 |
<> 或 != | 不等于 |
>、>= | 大于、大于或等于 |
<、<= | 小于、小于或等于 |
-- 查询价格等于 10.20 元的水果 SELECT name, price FROM fruits WHERE price = 10.20; -- 查询价格低于 10 元的水果 SELECT name, price FROM fruits WHERE price < 10;
2. AND、OR 与 NOT
-- 价格低于 20 元,并且有库存 SELECT name, price, stock FROM fruits WHERE price < 20 AND stock > 0; -- 查询柑橘类或浆果类水果 SELECT name, category FROM fruits WHERE category = '柑橘类' OR category = '浆果类';
AND 的优先级高于 OR。条件复杂时应使用括号明确逻辑,避免结果与预期不一致。
SELECT name, category, stock FROM fruits WHERE (category = '柑橘类' OR category = '浆果类') AND stock > 0;
四、IN 与 BETWEEN 范围查询
1. IN 查询多个指定值
SELECT supplier_id, name, price FROM fruits WHERE supplier_id IN (101, 102) ORDER BY name;
排除这些值时使用 NOT IN:
SELECT supplier_id, name, price FROM fruits WHERE supplier_id NOT IN (101, 102);
如果 NOT IN 的列表或子查询结果中含有 NULL,结果可能不符合直觉。处理可空字段时应特别检查 NULL,或改用 NOT EXISTS。
2. BETWEEN 查询连续范围
BETWEEN 同时包含开始值和结束值。
SELECT name, price FROM fruits WHERE price BETWEEN 8.00 AND 20.00; SELECT name, price FROM fruits WHERE price NOT BETWEEN 8.00 AND 20.00;
五、LIKE 模糊查询
%:匹配任意长度的字符,包括空字符串。_:匹配一个字符。
-- 名称中包含“果” SELECT id, name FROM fruits WHERE name LIKE '%果%'; -- 名称以“香”开头 SELECT id, name FROM fruits WHERE name LIKE '香%';
以 % 开头的模糊查询通常难以利用普通 B-Tree 索引,大数据表中要谨慎使用。
六、正确查询 NULL
NULL 表示未知或缺失值,不能使用 = NULL 或 != NULL 判断。
-- 未设置供应商 SELECT id, name FROM fruits WHERE supplier_id IS NULL; -- 已设置供应商 SELECT id, name FROM fruits WHERE supplier_id IS NOT NULL;
七、ORDER BY 排序
ASC 表示升序,是默认值;DESC 表示降序。
-- 按价格从高到低 SELECT name, price FROM fruits ORDER BY price DESC; -- 先按分类升序,同一分类按价格降序 SELECT name, category, price FROM fruits ORDER BY category ASC, price DESC;
八、LIMIT 分页
-- 返回前 3 条 SELECT id, name, price FROM fruits ORDER BY id LIMIT 3; -- 跳过前 3 条,再返回 3 条 SELECT id, name, price FROM fruits ORDER BY id LIMIT 3 OFFSET 3;
分页必须配合稳定的 ORDER BY,否则不同请求得到的记录顺序可能不一致。数据量很大时,较大的 OFFSET 会变慢,可改用基于主键的游标分页:
SELECT id, name, price FROM fruits WHERE id > 1000 ORDER BY id LIMIT 20;
九、聚合函数
常用聚合函数包括 COUNT()、SUM()、AVG()、MAX() 和 MIN()。
SELECT
COUNT(*) AS fruit_count,
SUM(stock) AS total_stock,
AVG(price) AS average_price,
MAX(price) AS highest_price,
MIN(price) AS lowest_price
FROM fruits;
COUNT(*) 统计行数;COUNT(字段) 不统计该字段为 NULL 的行。
十、GROUP BY 与 HAVING
GROUP BY 对记录分组,HAVING 对聚合后的分组结果进行过滤。
SELECT
category,
COUNT(*) AS fruit_count,
ROUND(AVG(price), 2) AS average_price,
SUM(stock) AS total_stock
FROM fruits
GROUP BY category
HAVING COUNT(*) >= 1
ORDER BY average_price DESC;
WHERE 在分组前过滤原始行,不能直接使用聚合函数;HAVING 在分组后过滤统计结果。
SELECT category, COUNT(*) AS fruit_count FROM fruits WHERE stock > 0 GROUP BY category HAVING COUNT(*) >= 1;
MySQL 8.0 默认启用 ONLY_FULL_GROUP_BY。查询中的非聚合字段通常应出现在 GROUP BY 中,避免结果不确定或直接报错。
十一、SELECT 的逻辑执行顺序
理解执行顺序有助于判断别名何时可用:
- FROM:确定数据来源。
- WHERE:过滤原始记录。
- GROUP BY:对记录分组。
- HAVING:过滤分组结果。
- SELECT:计算返回字段。
- DISTINCT:去除重复结果。
- ORDER BY:排序。
- LIMIT:截取结果。
十二、查询优化建议
- 业务查询尽量只选择需要的字段,避免长期使用
SELECT *。 - 为高频 WHERE、JOIN 和 ORDER BY 条件设计合适索引。
- 不要对索引列随意套函数或进行不必要的隐式类型转换。
- 大分页优先使用主键或唯一索引进行游标分页。
- 慢查询使用
EXPLAIN或EXPLAIN ANALYZE检查执行计划。 - 应用程序传入的条件必须使用参数绑定,不能直接拼接 SQL,以避免 SQL 注入。
EXPLAIN SELECT id, name, price FROM fruits WHERE category = '热带水果' ORDER BY price DESC;
完整语法可参考 MySQL 8.0 SELECT 官方文档。