MySQL 基本查询实战：增删改查与聚合分组详解

2.2 WHERE 条件筛选

WHERE 子句用于过滤数据，支持多种运算符。

比较与逻辑运算

• 比较运算符：=, >, <, >=, <=, <>
• 逻辑运算符：AND, OR, NOT

示例：英语不及格的同学

SELECT name, english FROM exam_result WHERE english < 60;

示例：语文成绩在 [80, 90] 之间

-- 写法一：使用 AND
SELECT name, chinese FROM exam_result WHERE chinese >= 80 AND chinese <= 90;

-- 写法二：使用 BETWEEN...AND（推荐，更简洁）
SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90;

示例：数学成绩为特定值

-- 写法一：多个 OR
SELECT name, math FROM exam_result WHERE math = 58 OR math = 59 OR math = 98 OR math = 99;

-- 写法二：使用 IN（推荐）
SELECT name, math FROM exam_result WHERE math IN (58, 59, 98, 99);

模糊查询 LIKE

区分'姓孙'和'孙某'：

• %：匹配任意多个字符（包括 0 个）。
• _：匹配严格的一个字符。

-- 姓孙的同学（名字长度不限）
SELECT * FROM exam_result WHERE name LIKE '孙%';

-- 孙某同学（名字两个字）
SELECT * FROM exam_result WHERE name LIKE '孙_';

注意事项：别名与执行顺序

在 WHERE 中使用别名会报错，因为 SQL 执行顺序是：FROM -> WHERE -> SELECT。别名是在 SELECT 阶段生成的，WHERE 阶段还无法识别。

但在 ORDER BY 中可以使用别名，因为排序发生在 SELECT 之后。

2.3 结果排序与分页

排序 ORDER BY

默认升序（ASC），降序需加 DESC。NULL 值在升序时排在最前，降序时排在最后。

-- 按数学成绩降序
SELECT name, math FROM exam_result ORDER BY math DESC;

分页 LIMIT

避免全表扫描导致卡顿，使用 LIMIT 限制返回行数。

-- 显示前 3 行
SELECT * FROM exam_result LIMIT 3;

-- 跳过前 2 行，显示接下来的 3 行（offset, count）
SELECT * FROM exam_result LIMIT 2, 3;

-- 推荐写法：OFFSET 关键字
SELECT * FROM exam_result LIMIT 3 OFFSET 2;

三、Update 更新

UPDATE 用于修改现有数据，必须配合 WHERE 防止误改全表。

-- 修改孙悟空的数学成绩
UPDATE exam_result SET math = 80 WHERE name = '孙悟空';

-- 同时修改多列
UPDATE exam_result SET math = 60, chinese = 70 WHERE name = '曹孟德';

-- 复杂条件：总成绩倒数前三加 30 分
UPDATE exam_result 
SET math = math + 30 
WHERE id IN (
    SELECT id FROM (
        SELECT id FROM exam_result 
        ORDER BY (chinese + math + english) ASC 
        LIMIT 3
    ) AS temp
);

注意：MySQL 不支持 += 这种语法，必须写成 math = math + 30。

四、Delete 删除

删除数据需谨慎，DELETE 和 TRUNCATE 有本质区别。

4.1 DELETE

可带条件删除，也可清空整表。但 DELETE 不会重置自增长 ID（AUTO_INCREMENT）。

DELETE FROM exam_result WHERE name = '孙悟空';
DELETE FROM exam_result; -- 清空整表

4.2 TRUNCATE

截断表，速度快，且会重置自增长 ID。但不能回滚，且只能针对整表。

TRUNCATE TABLE exam_result;

五、聚合函数

聚合函数用于对一组数据进行统计。

• COUNT()：统计行数
• SUM()：求和
• AVG()：平均值
• MAX() / MIN()：最大/最小值

-- 统计班级人数
SELECT COUNT(*) FROM exam_result;

-- 统计数学成绩去重后的个数
SELECT COUNT(DISTINCT math) FROM exam_result;

-- 获取英语最高分
SELECT MAX(english) FROM exam_result;

-- 获取大于 70 分的数学最低分
SELECT MIN(math) FROM exam_result WHERE math > 70;

六、GROUP BY 分组

GROUP BY 将数据分组后应用聚合函数。配合 HAVING 进行分组后的筛选。

6.1 基础分组

-- 每个部门的平均工资和最高工资
SELECT deptno, AVG(sal), MAX(sal) 
FROM emp 
GROUP BY deptno;

6.2 多重分组

-- 每个部门每种岗位的平均工资
SELECT deptno, job, AVG(sal) 
FROM emp 
GROUP BY deptno, job;

6.3 HAVING vs WHERE

• WHERE：在分组前筛选具体列。
• HAVING：在分组后筛选聚合结果。

-- 错误：WHERE 不能用于聚合函数
-- SELECT deptno, AVG(sal) FROM emp WHERE AVG(sal) > 2000 GROUP BY deptno;

-- 正确：使用 HAVING
SELECT deptno, AVG(sal) 
FROM emp 
GROUP BY deptno 
HAVING AVG(sal) > 2000;

掌握这些基础查询技巧，能帮你更高效地处理数据库中的各类业务需求。