简介本资源为国家开放大学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 snocno 既不在 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 默认latin18.0 默认utf8mb4但客户端连接时可能还是按latin1解析。解决办法是建库时显式指定utf8mb4连接时执行SET NAMES utf8mb4;或者在建表语句末尾加DEFAULT CHARSETutf8mb4。已经建错的表可以用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_CHECKS0;关闭检查不推荐会掩盖数据问题。更稳妥的做法是插入前先用 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 是 ALLkey 是 NULL加索引后 type 可能变成 rangekey 变成 idx_grade。这个对比能让你直观感受到索引的作用比背概念有用得多。注意索引不是越多越好每个索引都会增加插入和更新的开销实验里加一个感受一下就行别把所有列都加上。另一个验证技巧是用SHOW WARNINGS;配合 EXPLAIN能看到优化器改写后的 SQL。有时候你写的子查询会被优化器改写成连接SHOW WARNINGS能让你看到这个转换过程对理解 MySQL 的执行逻辑很有帮助。我自己的习惯是实验里的每条多表查询写完结果验证后都跑一遍 EXPLAIN把 type 和 rows 记在实验报告里。这个习惯坚持几次后你写查询时会下意识考虑这条语句会走索引吗而不是等数据量大了才发现慢。热词里慢sql优化 听着像高级话题但它的起点就是看懂 EXPLAIN 输出而实验训练2正好提供了练手的场景。最后说个我踩过的坑有次实验里我写了条带子查询的语句结果正确但 EXPLAIN 显示子查询被反复执行。后来改成 JOIN 写法rows 从几十降到个位数。查询结果一样执行方式完全不同。这件事让我养成了一个习惯结果对只是及格线执行计划合理才算真正写对了查询。希望帮到你。本文还有配套的精品资源点击获取