简介：本资源为国家开放大学MySQL数据库应用课程的实验训练2配套资料，面向正在学习数据库基础与SQL查询的在校学生及自学者，帮助系统梳理数据查询操作的核心知识点。内容围绕字段查询、多条件查询、DISTINCT去重、ORDER BY排序、GROUP BY分组、聚合函数以及内连接、外连接、嵌套查询等实验展开，每个实验均配有分析思路，便于对照练习与复盘。资源包共1个PDF文件，大小约1.75MB，以图文文档形式呈现，适合打印或电子阅读，方便随时查阅实验步骤与要点。目前已有3334人学习下载，说明该资料在同类课程中具有较高的参考价值。通过这份文档，读者可以完整掌握从单表查询到复合条件连接、子查询的递进式训练路径，理解COUNT、SUM、AVG、MAX、MIN等聚合函数的实际应用场景，并借助实验分析培养独立编写SQL语句的能力，为后续数据库开发与管理打下扎实基础。
1. 数据查询操作到底在训练什么：从一条 SELECT 说起
国家开放大学 MySQL数据库应用 实验训练2：数据查询操作，这个标题看起来像一份课程作业，但它真正训练的东西比"会写 SELECT"要具体得多。我带过几届做这个实验的学生，也帮同事排查过不少查询问题，发现一个反直觉的现象：大部分人在实验里卡住，不是因为不会写 SQL 语句，而是因为不清楚"查询结果对不对"该怎么验证。他们能敲出SELECT * FROM student，但面对"查询选修了3门以上课程的学生姓名"这种需求时，就不知道从哪下手拆解了。
这个实验训练的核心，是把一个自然语言描述的需求，翻译成 MySQL 能执行的查询语句，再用结果反推语句是否正确。它适合正在学数据库课程的学生、需要补 SQL 基础的开发者，以及工作中要写查询但总靠试错的人。热词里 mysql、sql语句、数据库增删改查、慢sql优化 这些词，其实都指向同一个能力：你得先能把查询写对，才谈得上优化。这篇笔记就按"建环境 → 单表查询 → 多表连接 → 子查询与聚合 → 避坑 → 进阶验证"的顺序，把实验训练2涉及的数据查询操作拆成能直接复现的步骤。
2. 实验环境与数据准备：把查询的靶子先立起来
2.1 为什么建议用本地 MySQL 而不是在线工具
做数据查询实验，第一件事是有一个能反复折腾的数据库。常见做法是用本地安装的 MySQL，版本选 8.0 系列即可，因为窗口函数、CTE 这些在后续进阶查询里会用到。热词里 mysql安装教程、mysql安装配置教程、linux安装mysql 出现频率很高，说明环境搭建本身就是很多人的第一道坎。
我一般会推荐两种方式：Windows 上用 MySQL Installer 装社区版，Linux 上用包管理器装。不推荐一上来就用在线 SQL 练习平台，原因是实验训练2需要你建自己的表、插自己的数据，在线平台通常只给固定数据集，你没法验证"我改一个字段查询结果会怎么变"。
安装完成后，用命令行或 MySQL Workbench 连上，执行下面这条语句确认版本：
-- 确认 MySQL 版本，8.0 以上才支持窗口函数和 CTE SELECT VERSION();这条语句返回类似8.0.36的结果就说明环境没问题。如果返回 5.7 系列，后面写窗口函数会报语法错误，需要先升级。参数上没什么可调的，重点是记住你的 root 密码和端口号，默认 3306。
2.2 建库建表：实验训练2 需要的最小数据集
数据查询操作要有表可查。实验训练2通常围绕学生、课程、选课三个实体展开，我按这个结构建一套最小数据集，字段类型和约束都写清楚，方便你直接抄：
-- 创建实验数据库，字符集用 utf8mb4 避免中文乱码 CREATE DATABASE IF NOT EXISTS lab_query DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci; USE lab_query; -- 学生表：学号为主键，姓名非空，年龄加检查约束 CREATE TABLE student ( sno VARCHAR(10) PRIMARY KEY, sname VARCHAR(20) NOT NULL, sage INT CHECK (sage BETWEEN 15 AND 60), sdept VARCHAR(20) ); -- 课程表：课程号为主键，学分用小数 CREATE TABLE course ( cno VARCHAR(10) PRIMARY KEY, cname VARCHAR(30) NOT NULL, credit DECIMAL(3,1) ); -- 选课表：联合主键，成绩允许为空（表示还没考） CREATE TABLE sc ( sno VARCHAR(10), cno VARCHAR(10), grade DECIMAL(4,1), PRIMARY KEY (sno, cno), FOREIGN KEY (sno) REFERENCES student(sno), FOREIGN KEY (cno) REFERENCES course(cno) );建表时几个参数值得说明。utf8mb4而不是utf8，是因为 MySQL 的utf8实际只支持 3 字节，存某些生僻字会出问题。CHECK约束在 8.0 才真正生效，5.7 会解析但忽略。DECIMAL(4,1)表示总共 4 位、小数点后 1 位，成绩最大 100.0 刚好够用。外键约束建议加上，它能帮你发现插入数据时的引用错误，这在实验里是很重要的反馈。
插入数据时注意顺序：先插 student 和 course，再插 sc，否则外键会拦你。
INSERT INTO student VALUES ('S001','张明',20,'计算机'), ('S002','李华',21,'计算机'), ('S003','王芳',19,'数学'), ('S004','赵强',22,'数学'), ('S005','陈静',20,'外语'); INSERT INTO course VALUES ('C01','数据库',3.0), ('C02','数据结构',4.0), ('C03','高等数学',5.0), ('C04','英语',2.0); INSERT INTO sc VALUES ('S001','C01',88.0), ('S001','C02',76.5), ('S002','C01',92.0), ('S002','C03',85.0), ('S003','C01',70.0), ('S003','C03',90.0), ('S004','C02',60.0), ('S005','C04',95.0);这套数据只有 5 个学生、4 门课、8 条选课记录，但足够覆盖单表查询、连接查询、聚合和子查询的所有典型场景。数据量小有个好处：你能手动算出预期结果，再和 SQL 返回的结果对比，这是验证查询正确性最可靠的办法。
3. 单表查询：WHERE 条件怎么写才不出错
3.1 比较、范围与模糊查询的边界
单表查询是实验训练2的基础部分，核心是 WHERE 子句。很多人觉得这太简单，但实际翻车往往就在细节上。先看几条典型语句：
-- 查询年龄大于20的学生 SELECT sno, sname, sage FROM student WHERE sage > 20; -- 查询年龄在19到21之间的学生，BETWEEN 包含两端 SELECT sno, sname, sage FROM student WHERE sage BETWEEN 19 AND 21; -- 查询姓张的学生，% 匹配任意长度，_ 匹配单个字符 SELECT sno, sname FROM student WHERE sname LIKE '张%'; -- 查询计算机系和数学系的学生，IN 比多个 OR 更清晰 SELECT sno, sname, sdept FROM student WHERE sdept IN ('计算机','数学');这里有几个参数和写法上的坑。BETWEEN 19 AND 21是闭区间，包含 19 和 21，和sage >= 19 AND sage <= 21等价。LIKE '张%'里的%可以匹配零个或多个字符，所以"张"本身也会被匹配到。如果要匹配真正的下划线字符，得用转义LIKE '\_'，因为_在 LIKE 里是通配符。
还有一个容易被忽略的点：字符串比较默认不区分大小写，取决于排序规则。utf8mb4_general_ci里的ci就是 case insensitive。如果你需要区分大小写，得用BINARY关键字或者改排序规则为_bin。实验里一般不需要，但知道这个边界能帮你排查"为什么 'abc' 和 'ABC' 被当成相等"的问题。
3.2 空值处理与结果排序
空值在 SQL 里是个特殊存在，它不等于任何值，包括它自己。实验数据里如果成绩还没录入，grade 就是 NULL。查询空值必须用IS NULL，不能用= NULL：
-- 正确：查询没有成绩的选课记录 SELECT * FROM sc WHERE grade IS NULL; -- 错误写法：这条永远返回空结果，因为 NULL = NULL 结果是 UNKNOWN SELECT * FROM sc WHERE grade = NULL;排序用 ORDER BY，可以指定升序 ASC 或降序 DESC，默认升序。多列排序时，先按第一列排，第一列相同再按第二列排：
-- 按成绩降序排列，成绩相同的按学号升序 SELECT sno, cno, grade FROM sc ORDER BY grade DESC, sno ASC;注意 NULL 在排序中的位置。MySQL 默认把 NULL 当作最小值，升序时排在最前面，降序时排在最后。如果你希望 NULL 始终排在最后，可以用ORDER BY grade IS NULL, grade DESC这种技巧，先按"是否为空"排，再按值排。这个写法在实验报告里不常见，但工作中处理不完整数据时很有用。
4. 连接查询与聚合：多表关联的三种写法和 GROUP BY 的陷阱
4.1 内连接、左连接的选择依据
数据查询操作里，单表能解决的问题有限，真正体现能力的是多表连接。实验训练2通常要求"查询每个学生的选课情况"，这就涉及 student 和 sc 两张表。连接写法有三种：隐式连接、显式 INNER JOIN、LEFT JOIN。
-- 写法一：隐式连接，在 WHERE 里写连接条件 SELECT s.sname, c.cname, sc.grade FROM student s, sc, course c WHERE s.sno = sc.sno AND sc.cno = c.cno; -- 写法二：显式内连接，连接条件和过滤条件分开 SELECT s.sname, c.cname, sc.grade FROM student s INNER JOIN sc ON s.sno = sc.sno INNER JOIN course c ON sc.cno = c.cno; -- 写法三：左连接，保留没有选课的学生 SELECT s.sname, c.cname, sc.grade FROM student s LEFT JOIN sc ON s.sno = sc.sno LEFT JOIN course c ON sc.cno = c.cno;三种写法的区别在结果集上。写法一和写法二结果相同，都是只返回有选课记录的学生。写法三会保留所有学生，没选课的学生在 cname 和 grade 列显示 NULL。选哪种取决于需求：如果题目问"查询所有学生及其选课情况"，哪怕没选课也要列出来，就必须用 LEFT JOIN。
我一般推荐显式 JOIN 写法，因为连接条件和过滤条件分开后，语句更容易读，也不容易漏写连接条件导致笛卡尔积。隐式连接如果忘了写 WHERE 里的连接条件，两张表会做全组合，5 个学生乘 8 条选课记录就是 40 行，结果明显不对，但新手往往看不出来。
4.2 GROUP BY 与聚合函数的配合规则
聚合查询是实验训练2的重头戏，常见需求是"查询每个学生的选课门数""查询每门课的平均分"。聚合函数有 COUNT、SUM、AVG、MAX、MIN，配合 GROUP BY 使用：
-- 查询每个学生的选课门数和平均分 SELECT sno, COUNT(*) AS course_count, AVG(grade) AS avg_grade FROM sc GROUP BY sno; -- 查询每门课的选课人数和最高分 SELECT cno, COUNT(*) AS student_count, MAX(grade) AS max_grade FROM sc GROUP BY cno;这里有个必须记住的规则：SELECT 列表里出现的非聚合列，必须出现在 GROUP BY 里。上面第一条语句里 sno 在 GROUP BY 中，COUNT 和 AVG 是聚合函数，所以合法。如果写成SELECT sno, cno, COUNT(*) FROM sc GROUP BY sno，cno 既不在 GROUP BY 里也不是聚合函数，MySQL 8.0 默认会报错（ONLY_FULL_GROUP_BY模式），5.7 可能返回不确定的值。这个报错是好事，它在阻止你写出语义模糊的查询。
HAVING 和 WHERE 的区别也是高频考点。WHERE 在分组前过滤行，HAVING 在分组后过滤组。比如"查询选课门数超过2门的学生"：
-- 正确：用 HAVING 过滤分组后的结果 SELECT sno, COUNT(*) AS cnt FROM sc GROUP BY sno HAVING COUNT(*) > 2; -- 错误：WHERE 里不能用聚合函数 SELECT sno, COUNT(*) AS cnt FROM sc WHERE COUNT(*) > 2 GROUP BY sno;第二条会直接报错，因为 WHERE 执行时分组还没发生，聚合函数没有上下文。这个执行顺序（FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY）是理解聚合查询的关键，建议在实验报告里画一遍。
5. 子查询与嵌套：把复杂需求拆成两层
5.1 标量子查询、IN 子查询与 EXISTS 的适用场景
子查询是实验训练2里区分度最高的部分。需求一旦变成"查询比某个学生年龄大的所有学生"或者"查询选修了数据库课程的学生"，就需要嵌套。子查询按返回结果分三类：标量（一行一列）、行/列子查询（多行一列）、表子查询（多行多列）。
-- 标量子查询：查询比张明年龄大的学生 SELECT sno, sname, sage FROM student WHERE sage > (SELECT sage FROM student WHERE sname = '张明'); -- IN 子查询：查询选修了 C01 课程的学生 SELECT sno, sname FROM student WHERE sno IN (SELECT sno FROM sc WHERE cno = 'C01'); -- EXISTS 子查询：查询选修了课程的学生（相关子查询） SELECT sno, sname FROM student s WHERE EXISTS (SELECT 1 FROM sc WHERE sc.sno = s.sno);标量子查询要求子查询只返回一个值，如果返回多行会报错Subquery returns more than 1 row。IN 子查询适合子查询结果集不大的情况。EXISTS 是相关子查询，对外表的每一行执行一次，适合子查询结果集大但只需要判断存在性的场景。
性能上，IN 和 EXISTS 在 MySQL 8.0 里优化器会做半连接转换，多数情况下差异不大。但有个经验：子查询结果集小用 IN，外表小、子查询结果集大用 EXISTS。实验数据量小看不出差别，但养成这个判断习惯对以后处理大表有帮助。
5.2 派生表与 CTE：让嵌套查询可读
子查询嵌套层数多了以后，语句会变得很难读。MySQL 8.0 支持 CTE（公用表表达式），可以把子查询提到前面命名，逻辑更清晰：
-- 派生表写法：子查询放在 FROM 里 SELECT t.sno, t.avg_grade FROM ( SELECT sno, AVG(grade) AS avg_grade FROM sc GROUP BY sno ) AS t WHERE t.avg_grade > 80; -- CTE 写法：用 WITH 提前定义，可读性更好 WITH avg_sc AS ( SELECT sno, AVG(grade) AS avg_grade FROM sc GROUP BY sno ) SELECT sno, avg_grade FROM avg_sc WHERE avg_grade > 80;派生表必须起别名（上面的AS t），否则报错Every derived table must have its own alias。CTE 的好处是可以被多次引用，比如你需要同时用这个平均分做筛选和做连接，CTE 写一次就够了，派生表得重复写。热词里 mysql存储过程 和 CTE 不是一回事，存储过程是把逻辑存在数据库端，CTE 只是单条查询内的临时命名结果集，别混淆。
实验训练2如果要求"查询平均分高于全体平均分的学生"，可以这样写：
-- 先算全体平均分，再和每个学生的平均分比较 WITH stu_avg AS ( SELECT sno, AVG(grade) AS avg_grade FROM sc GROUP BY sno ), total_avg AS ( SELECT AVG(grade) AS avg_all FROM sc ) SELECT s.sno, s.avg_grade FROM stu_avg s, total_avg t WHERE s.avg_grade > t.avg_all;这个查询拆成两步后，每一步都能单独运行验证，比写一个三层嵌套的子查询容易调试得多。这也是我推荐在实验里多用 CTE 的原因：出错时你能快速定位是哪一层的问题。
6. 数据查询实验里的避坑清单：5 个真实翻车记录
6.1 中文乱码：现象是问号，根因在字符集
现象：插入中文姓名后，查询结果显示??或者乱码。原因通常是建库时没指定字符集，或者连接字符集和库字符集不一致。MySQL 5.7 默认latin1，8.0 默认utf8mb4，但客户端连接时可能还是按latin1解析。解决办法是建库时显式指定utf8mb4，连接时执行SET NAMES utf8mb4;，或者在建表语句末尾加DEFAULT CHARSET=utf8mb4。已经建错的表可以用ALTER TABLE student CONVERT TO CHARACTER SET utf8mb4;修复，但已有数据可能已经损坏，需要重新插入。
6.2 外键约束报错：插入顺序和引用完整性
现象：插入 sc 表数据时报Cannot add or update a child row: a foreign key constraint fails。原因是 sc 里的 sno 或 cno 在 student 或 course 里不存在。解决办法是先插主表再插从表，或者临时SET FOREIGN_KEY_CHECKS=0;关闭检查（不推荐，会掩盖数据问题）。更稳妥的做法是插入前先用 SELECT 确认引用的主键存在。删除时反过来，先删从表再删主表，否则也会被外键拦住。
6.3 GROUP BY 报错：ONLY_FULL_GROUP_BY 模式
现象：SELECT sno, cno, COUNT(*) FROM sc GROUP BY sno报错Expression #2 of SELECT list is not in GROUP BY clause。原因是 MySQL 8.0 默认开启ONLY_FULL_GROUP_BY，要求 SELECT 里的非聚合列必须在 GROUP BY 中出现。解决办法是补全 GROUP BY，或者用ANY_VALUE(cno)包一下（但语义上要想清楚你要的是哪个 cno）。不建议直接关掉这个模式，它是在帮你避免不确定的查询结果。
6.4 NULL 参与运算：结果全变 NULL
现象：SELECT sno, grade + 10 FROM sc里，grade 为 NULL 的行结果也是 NULL。原因是 NULL 参与任何算术运算结果都是 NULL。解决办法是用IFNULL(grade, 0) + 10或者COALESCE(grade, 0) + 10把 NULL 替换成默认值。聚合函数是个例外，AVG、SUM会自动忽略 NULL，但COUNT(*)会计数所有行，COUNT(grade)只计数非 NULL 的 grade，这两个写法结果可能不同，实验里要看清题目问的是"选课记录数"还是"有成绩的记录数"。
6.5 连接漏写条件：笛卡尔积悄悄放大结果
现象：查询返回的行数远多于预期，比如 5 个学生查出 40 行。原因是多表查询时漏写了连接条件，MySQL 做了笛卡尔积。解决办法是检查 WHERE 或 ON 里是否每两张表都有连接条件。N 张表连接至少需要 N-1 个连接条件。用显式 JOIN 写法时，ON 子句不容易漏；用隐式连接时，WHERE 里条件一多就容易忘。养成写完查询先看行数的习惯，行数异常先怀疑连接条件。
7. 用 EXPLAIN 验证查询：从"能跑"到"跑得对"
实验训练2的查询写完后，多数人只验证结果对不对，但有个更硬核的验证手段：EXPLAIN。它能告诉你 MySQL 打算怎么执行这条查询，用没用索引、扫了多少行、连接顺序是什么。这在实验里不是必做项，但它是从"会写查询"到"懂查询"的分水岭。
-- 查看查询的执行计划 EXPLAIN SELECT s.sname, c.cname, sc.grade FROM student s INNER JOIN sc ON s.sno = sc.sno INNER JOIN course c ON sc.cno = c.cno WHERE sc.grade > 80;输出里重点看几列。type表示访问类型，ALL是全表扫描，ref或eq_ref是用到了索引，index是全索引扫描。rows是预估扫描行数，越小越好。key是实际使用的索引，如果是 NULL 说明没走索引。Extra里出现Using filesort表示需要额外排序，出现Using temporary表示用了临时表，这两个在数据量大时是性能信号。
实验数据只有几行，EXPLAIN 的 rows 可能都是 1 或 2，看不出明显差异。但你可以做一件事：给 sc 表的 grade 列加个索引，再对比 EXPLAIN 结果。
-- 加索引前先看执行计划，再执行下面这条 CREATE INDEX idx_grade ON sc(grade); -- 再次 EXPLAIN，观察 type 和 key 的变化 EXPLAIN SELECT sno, cno, grade FROM sc WHERE grade > 80;加索引前 type 是 ALL，key 是 NULL；加索引后 type 可能变成 range，key 变成 idx_grade。这个对比能让你直观感受到索引的作用，比背概念有用得多。注意索引不是越多越好，每个索引都会增加插入和更新的开销，实验里加一个感受一下就行，别把所有列都加上。
另一个验证技巧是用SHOW WARNINGS;配合 EXPLAIN，能看到优化器改写后的 SQL。有时候你写的子查询会被优化器改写成连接，SHOW WARNINGS能让你看到这个转换过程，对理解 MySQL 的执行逻辑很有帮助。
我自己的习惯是：实验里的每条多表查询，写完结果验证后，都跑一遍 EXPLAIN，把 type 和 rows 记在实验报告里。这个习惯坚持几次后，你写查询时会下意识考虑"这条语句会走索引吗"，而不是等数据量大了才发现慢。热词里慢sql优化 听着像高级话题，但它的起点就是看懂 EXPLAIN 输出，而实验训练2正好提供了练手的场景。
最后说个我踩过的坑：有次实验里我写了条带子查询的语句，结果正确但 EXPLAIN 显示子查询被反复执行。后来改成 JOIN 写法，rows 从几十降到个位数。查询结果一样，执行方式完全不同。这件事让我养成了一个习惯：结果对只是及格线，执行计划合理才算真正写对了查询。希望帮到你。
本文还有配套的精品资源，点击获取
