技术 · 思考 · 实践 · 成长

首页/MySQL8专栏/正文

MySQL 8.0 SELECT 查询详解:条件、排序、分页与分组统计

MySQL8专栏734 次阅读

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 的逻辑执行顺序

理解执行顺序有助于判断别名何时可用:

  1. FROM:确定数据来源。
  2. WHERE:过滤原始记录。
  3. GROUP BY:对记录分组。
  4. HAVING:过滤分组结果。
  5. SELECT:计算返回字段。
  6. DISTINCT:去除重复结果。
  7. ORDER BY:排序。
  8. 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 官方文档。

无才无以立足,不苦不能成才。

评论交流

0 条评论 · 支持多级回复
验证码
暂无评论,来说说你的想法吧

相关文章

继续阅读