MySQL 基本查询全实战：从 CRUD 到聚合分组，搞定 90% 的业务查询需求

        MySQL 的查询操作（CRUD）是日常开发中最频繁的工作，也是业务逻辑落地的核心。很多开发者初期只会用SELECT *查询全表，却忽略了 “字段筛选”“条件过滤”“分页排序” 等优化手段，导致查询效率低、返回数据冗余。本文将以 “学生成绩管理”“员工信息统计” 等实战场景为切入点，系统讲解 MySQL 的增删改查（CRUD）、聚合函数、分组查询等核心操作，帮你写出高效、精准的 SQL 语句。

一、基础认知：CRUD 与 SQL 执行顺序

在学习具体操作前，先明确两个核心概念，避免后续踩坑：

1. CRUD 定义：Create（插入）、Retrieve（查询）、Update（更新）、Delete（删除），覆盖数据从创建到销毁的全生命周期；
2. SQL 执行顺序：查询语句中关键字的执行顺序与书写顺序不同，正确顺序为：FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT例如SELECT name FROM exam_result WHERE math > 80 ORDER BY math，实际先从exam_result表筛选，再过滤数学成绩，最后排序。

二、Create：插入数据的 4 种场景

插入数据不仅是 “简单插入”，还需处理 “主键冲突”“批量插入” 等场景，合理使用插入语法能大幅提升效率。

2.1 单行全列插入（不推荐）

插入一条数据并指定所有列，需严格匹配表的列顺序和数量，灵活性低，不建议用于生产环境（表结构变更会导致插入失败）。

-- 前提：students表结构为(id, sn, name, qq)
INSERT INTO students VALUES (100, 10000, '唐三藏', NULL);

2.2 多行指定列插入（推荐）

指定插入的列名，可批量插入多条数据，无需匹配全列，表结构变更时更稳定。

-- 插入2条数据，仅指定id、sn、name列（qq列用默认值NULL）
INSERT INTO students (id, sn, name) VALUES 
(102, 20001, '曹孟德'), 
(103, 20002, '孙仲谋');

2.3 插入否则更新（解决主键 / 唯一键冲突）

当插入数据的主键或唯一键已存在时，不报错而是更新指定字段，避免重复插入导致业务中断。

-- 若id=100已存在，更新sn和name；若不存在，直接插入
INSERT INTO students (id, sn, name) VALUES (100, 10010, '唐大师')
ON DUPLICATE KEY UPDATE sn = 10010, name = '唐大师';

-- 查看受影响行数（2表示“冲突并更新”，1表示“新插入”，0表示“冲突但值未变”）
SELECT ROW_COUNT();

2.4 替换插入（删除后重新插入）

若主键或唯一键冲突，先删除原数据再插入新数据（会丢失原数据的其他字段值，慎用）。

-- 若sn=20001已存在，删除原记录后插入新记录
REPLACE INTO students (sn, name) VALUES (20001, '曹阿瞒');

三、Retrieve：查询数据的核心技巧

查询是 MySQL 最复杂的操作，需掌握 “字段筛选”“条件过滤”“排序分页” 等技巧，避免返回冗余数据。

3.1 字段筛选：拒绝SELECT *

SELECT *会返回所有列，增加数据传输量，还可能影响索引使用，推荐 “按需查询字段”。

3.1.1 指定列查询

仅查询需要的列，可调整列的顺序（无需与表结构一致）。

sql

-- 查询学生的id、姓名和英语成绩
SELECT id, name, english FROM exam_result;

3.1.2 表达式查询

查询时可对字段进行计算（如成绩加分、求总分），无需程序端二次处理。

-- 1. 固定值表达式（所有行返回10）
SELECT id, name, 10 FROM exam_result;

-- 2. 单字段表达式（英语成绩+10）
SELECT id, name, english + 10 AS english_plus FROM exam_result;

-- 3. 多字段表达式（计算总分）
SELECT id, name, chinese + math + english AS total_score FROM exam_result;

3.1.3 结果去重（DISTINCT）

当查询结果有重复值时（如多个学生数学成绩为 98），用DISTINCT去重。

-- 去重查询数学成绩（仅显示不同的分数）
SELECT DISTINCT math FROM exam_result;

3.2 条件过滤（WHERE）：精准筛选数据

WHERE子句通过 “比较运算符” 和 “逻辑运算符” 筛选数据，是查询的核心，常见场景如下：

3.2.1 基础比较（>、<、= 等）

-- 英语成绩不及格（<60）的学生
SELECT name, english FROM exam_result WHERE english < 60;

3.2.2 范围匹配（BETWEEN、IN）

-- 1. 语文成绩在80~90之间（含边界）
SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90;

-- 2. 数学成绩为58、59、98、99的学生
SELECT name, math FROM exam_result WHERE math IN (58, 59, 98, 99);

3.2.3 模糊匹配（LIKE）

用%匹配任意多个字符（包括 0 个），_匹配单个字符，适合 “按前缀 / 后缀查询”。

-- 1. 姓“孙”的学生（孙后可跟任意字符）
SELECT name FROM exam_result WHERE name LIKE '孙%';

-- 2. 名字为2个字且姓“孙”的学生（孙后仅1个字符）
SELECT name FROM exam_result WHERE name LIKE '孙_';

3.2.4 NULL 值处理（IS NULL/IS NOT NULL）

NULL表示 “未知值”，不能用=判断，需用IS NULL或IS NOT NULL。

-- 查询QQ号已知的学生（排除NULL）
SELECT name, qq FROM students WHERE qq IS NOT NULL;

3.2.5 逻辑组合（AND、OR、NOT）

-- 语文>80且不姓“孙”的学生
SELECT name, chinese FROM exam_result WHERE chinese > 80 AND name NOT LIKE '孙%';

3.3 结果排序（ORDER BY）：规范返回顺序

未加ORDER BY的查询，返回顺序是未定义的，生产环境必须显式排序。

-- 1. 按数学成绩升序（默认ASC，从小到大）
SELECT name, math FROM exam_result ORDER BY math;

-- 2. 按总分降序（DESC，从大到小），支持表达式或别名
SELECT name, chinese+math+english AS total FROM exam_result ORDER BY total DESC;

-- 3. 多字段排序（先按数学降序，再按英语升序）
SELECT name, math, english FROM exam_result ORDER BY math DESC, english;

3.4 分页查询（LIMIT）：避免大数据量卡死

当表数据量大时（如 10 万条），用LIMIT分页查询，避免一次性返回全表数据。

-- 语法1：LIMIT 条数 OFFSET 起始下标（起始下标从0开始）
-- 第1页：从0开始，查3条
SELECT id, name FROM exam_result ORDER BY id LIMIT 3 OFFSET 0;

-- 第2页：从3开始，查3条
SELECT id, name FROM exam_result ORDER BY id LIMIT 3 OFFSET 3;

-- 语法2：LIMIT 起始下标, 条数（与语法1等价，推荐用语法1，更清晰）
SELECT id, name FROM exam_result ORDER BY id LIMIT 3, 3;

注意：分页查询必须搭配ORDER BY，否则每次返回的结果顺序可能不同，导致数据重复或遗漏。

四、Update：更新数据的 3 个关键场景

更新数据需谨慎，必须加WHERE条件（否则更新全表），常见场景如下：

4.1 更新单个字段

-- 将“孙悟空”的数学成绩改为80分
UPDATE exam_result SET math = 80 WHERE name = '孙悟空';

4.2 批量更新多个字段

-- 将“曹孟德”的数学改为60分，语文改为70分
UPDATE exam_result SET math = 60, chinese = 70 WHERE name = '曹孟德';

4.3 基于原字段值更新

更新值可基于字段当前值计算（如成绩加 30 分），支持排序和分页（仅更新部分数据）。

-- 总分倒数前三的学生，数学成绩加30分
UPDATE exam_result 
SET math = math + 30 
ORDER BY chinese + math + english 
LIMIT 3;

警告：无WHERE条件的UPDATE会更新全表，生产环境需先通过SELECT验证条件，再执行更新。

五、Delete：删除数据的 2 种方式

删除数据同样需加WHERE条件，避免误删全表，核心方式有 “普通删除” 和 “截断表”。

5.1 普通删除（DELETE）

删除指定条件的数据，支持排序和分页（仅删除部分数据），删除后自增 ID 不会重置。

-- 删除“孙悟空”的考试成绩
DELETE FROM exam_result WHERE name = '孙悟空';

-- 删除全表数据（慎用！自增ID继续增长）
DELETE FROM for_delete;

5.2 截断表（TRUNCATE）

快速删除全表数据，比DELETE效率高（不逐行删除），但会重置自增 ID，且无法回滚（不支持事务）。

-- 截断表（仅适用于删除全表，无法加WHERE）
TRUNCATE TABLE for_truncate;

选择建议：需保留自增 ID 或删除部分数据→用DELETE；需彻底清空表且重置 ID→用TRUNCATE。

六、高级查询：聚合函数与分组查询

当需要 “统计分析”（如平均成绩、部门人数）时，需用 “聚合函数” 和 “GROUP BY” 分组查询。

6.1 常用聚合函数

聚合函数对一组数据进行计算并返回单个结果，常见函数如下：

函数	作用	示例
COUNT([DISTINCT] 列)	统计行数（DISTINCT 去重）	COUNT(qq)（统计非 NULL 的 QQ 号数量）
SUM([DISTINCT] 列)	计算总和（仅数字类型）	SUM(math)（数学成绩总和）
AVG([DISTINCT] 列)	计算平均值（仅数字类型）	AVG(total_score)（平均总分）
MAX(列)	求最大值	MAX(english)（英语最高分）
MIN(列)	求最小值	MIN(math)（数学最低分）

实战案例

-- 1. 统计学生总数（COUNT(*)不受NULL影响）
SELECT COUNT(*) AS student_count FROM students;

-- 2. 统计数学成绩的不同分数个数（去重）
SELECT COUNT(DISTINCT math) AS math_distinct FROM exam_result;

-- 3. 计算语文成绩的平均分
SELECT AVG(chinese) AS chinese_avg FROM exam_result;

-- 4. 求总分大于200的学生中，数学最低分
SELECT MIN(math) AS min_math FROM exam_result WHERE chinese+math+english > 200;

6.2 分组查询（GROUP BY）

GROUP BY按指定列分组，结合聚合函数可实现 “按组统计”（如按部门统计平均工资）。

基础分组

-- 准备：EMP表（含deptno部门号、sal工资、job岗位）
-- 1. 按部门统计平均工资和最高工资
SELECT deptno, AVG(sal) AS avg_sal, MAX(sal) AS max_sal FROM EMP GROUP BY deptno;

-- 2. 按部门+岗位分组，统计平均工资和最低工资
SELECT deptno, job, AVG(sal) AS avg_sal, MIN(sal) AS min_sal FROM EMP GROUP BY deptno, job;

分组筛选（HAVING）

WHERE筛选行数据，HAVING筛选分组结果（需配合GROUP BY使用）。

-- 统计平均工资低于2000的部门（先分组统计，再筛选分组结果）
SELECT deptno, AVG(sal) AS avg_sal 
FROM EMP 
GROUP BY deptno 
HAVING avg_sal < 2000;

七、实战技巧：插入查询结果（去重案例）

当需要 “删除表中重复记录” 时，可通过 “创建空表→插入去重数据→重命名表” 实现，步骤如下：

-- 1. 创建有重复数据的表
CREATE TABLE duplicate_table (id int, name varchar(20));
INSERT INTO duplicate_table VALUES 
(100, 'aaa'), (100, 'aaa'), (200, 'bbb'), (200, 'bbb'), (300, 'ccc');

-- 2. 创建结构相同的空表
CREATE TABLE no_duplicate_table LIKE duplicate_table;

-- 3. 插入去重后的数据
INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table;

-- 4. 重命名表（原子操作，避免数据丢失）
RENAME TABLE 
duplicate_table TO old_duplicate_table, 
no_duplicate_table TO duplicate_table;

-- 5. 查看结果（仅保留唯一数据）
SELECT * FROM duplicate_table;

八、总结

MySQL 的基本查询操作覆盖了大部分业务场景，核心要点如下：

1. 插入：优先用 “指定列批量插入”，用ON DUPLICATE KEY处理冲突；
2. 查询：拒绝SELECT *，用WHERE精准筛选，ORDER BY+LIMIT分页排序；
3. 更新 / 删除：必须加WHERE条件，避免误操作全表；
4. 统计：用聚合函数实现数据分析，GROUP BY+HAVING实现分组筛选。