简介这份PDF文档面向国家开放大学MySQL数据库应用课程的学习者聚焦实验训练2的数据查询操作适合正在备考实验、需要系统梳理查询语法的学生与自学者。文档围绕单表查询到复杂嵌套查询逐层展开涵盖字段查询、多条件查询、DISTINCT去重、ORDER BY排序、GROUP BY分组、COUNT/SUM/AVG/MAX/MIN聚合函数以及内连接、外连接、复合条件连接和IN、EXISTS嵌套查询等核心知识点每个实验均配有题目分析与实现思路。资源包共1个PDF文件约1.75MB内容紧凑便于打印或离线查阅。目前已有3334人学习下载可作为实验报告撰写与期末复习的参考材料帮助读者对照实验编号快速定位语法要点理解多表连接与子查询的解题逻辑提升数据库查询语句的编写与调试能力。1. 数据查询操作从建表到多表联查一次实验课到底要跨过几道坎国家开放大学 MySQL 数据库应用这门课里实验训练2 的数据查询操作表面看就是写几条 SELECT真上手才发现卡点根本不在语法。我带过几届学生的上机课十个人里有八个会在「查不出结果」和「查出来不对」之间反复横跳——表建好了数据插进去了SELECT * 能跑但一加 WHERE 条件就空集一加 GROUP BY 就报错一写多表联查就笛卡尔积爆炸。这篇笔记就按实验训练2 的真实推进顺序把数据查询操作从单表条件筛选、聚合分组、子查询到多表连接完整走一遍每一步给出可复现的 SQL、参数说明和翻车现场。适合正在做这个实验的在校生也适合想系统补一遍 MySQL 查询基础的从业者。你不需要装什么高级工具mysql 命令行或者 Workbench 都行关键是每一步都知道自己在查什么、为什么这么查。2. 实验前的库表准备先把查询的地基打对2.1 建库建表与数据类型选择数据查询操作的前提是有一张结构合理的表。实验训练2 通常会给一套现成的建表语句但很多人直接复制粘贴就跑结果字符集不对、字段类型选错后面查询全是坑。我一般会先确认三件事字符集用 utf8mb4、主键用自增整数、金额和成绩这类字段用 DECIMAL 而不是 FLOAT。下面这套建表和插数据的脚本是我按实验常见场景整理的包含学生表、课程表和成绩表三张表后面所有查询都基于它-- 建库字符集用 utf8mb4避免中文乱码 CREATE DATABASE IF NOT EXISTS lab_query DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci; USE lab_query; -- 学生表学号为主键姓名非空性别用枚举 CREATE TABLE student ( sno CHAR(8) NOT NULL PRIMARY KEY, -- 学号定长8位 sname VARCHAR(20) NOT NULL, -- 姓名 gender ENUM(男,女) DEFAULT 男, -- 性别 age TINYINT UNSIGNED DEFAULT 18, -- 年龄无符号 dept VARCHAR(30) -- 院系 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 课程表课程号为主键 CREATE TABLE course ( cno CHAR(4) NOT NULL PRIMARY KEY, -- 课程号 cname VARCHAR(30) NOT NULL, -- 课程名 credit DECIMAL(3,1) DEFAULT 2.0 -- 学分一位小数 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 成绩表学号课程号联合主键成绩用 DECIMAL CREATE TABLE score ( sno CHAR(8) NOT NULL, cno CHAR(4) NOT NULL, grade DECIMAL(5,1) DEFAULT 0.0, -- 成绩保留一位小数 PRIMARY KEY (sno, cno), FOREIGN KEY (sno) REFERENCES student(sno), FOREIGN KEY (cno) REFERENCES course(cno) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 插入测试数据 INSERT INTO student VALUES (20230101,张伟,男,20,计算机), (20230102,李娜,女,19,计算机), (20230103,王强,男,21,数学), (20230104,赵敏,女,20,数学), (20230105,刘洋,男,22,物理); INSERT INTO course VALUES (C001,数据库原理,3.0), (C002,数据结构,4.0), (C003,操作系统,3.5); INSERT INTO score VALUES (20230101,C001,88.5), (20230101,C002,76.0), (20230102,C001,92.0), (20230102,C003,85.5), (20230103,C001,60.0), (20230103,C002,58.5), (20230104,C002,90.0), (20230105,C003,72.5);这段脚本里几个参数值得说清楚。CHAR(8)和VARCHAR(20)的区别在于定长和变长学号固定 8 位用 CHAR 更省空间姓名长度不固定用 VARCHAR。DECIMAL(5,1)表示总共 5 位数字、小数点后 1 位最大能存 9999.9成绩够用了千万别用 FLOAT否则 88.5 可能存成 88.499999。外键约束保证 score 表里的学号和课程号一定在父表里存在插入脏数据会直接报错这是好事。提示如果你的 MySQL 版本是 8.0 以上建表时字符集建议显式写 utf8mb4不要依赖默认值不同版本默认字符集不一样。2.2 用 DESC 和 SELECT 确认数据到位建完表别急着写复杂查询先用两条命令确认地基没问题-- 查看表结构确认字段类型和约束 DESC student; -- 查看每张表的行数确认数据插进去了 SELECT student AS tbl, COUNT(*) AS cnt FROM student UNION ALL SELECT course, COUNT(*) FROM course UNION ALL SELECT score, COUNT(*) FROM score;DESC会列出字段名、类型、是否可为空、键类型和默认值重点看主键和外键有没有建上。第二条用 UNION ALL 把三张表的行数拼在一起看正常应该是 5、3、8。如果行数不对先别往下做查询回头检查 INSERT 是不是被外键挡了或者字符集导致中文变问号。这一步花两分钟能省掉后面半小时的排查。3. 单表查询WHERE 条件怎么写才不返回空集3.1 比较、逻辑与范围条件的组合单表查询是实验训练2 的重头戏也是最多人翻车的地方。最常见的现象是明明表里有数据加了 WHERE 就返回 Empty set。原因通常有三类——字符串没加引号、NULL 值比较用了等号、条件之间逻辑关系搞反了。先看一组能直接跑的查询-- 查询计算机系年龄大于19岁的学生 SELECT sno, sname, age, dept FROM student WHERE dept 计算机 AND age 19; -- 查询年龄在19到21之间的学生BETWEEN 包含边界 SELECT sno, sname, age FROM student WHERE age BETWEEN 19 AND 21; -- 查询姓张或姓李的学生LIKE 配合通配符 SELECT sno, sname FROM student WHERE sname LIKE 张% OR sname LIKE 李%; -- 查询院系不是计算机的学生注意 NULL 的处理 SELECT sno, sname, dept FROM student WHERE dept 计算机 OR dept IS NULL;第一条里dept 计算机的引号不能省字符串比较必须带引号写成dept 计算机MySQL 会把它当列名去找直接报 Unknown column。BETWEEN 19 AND 21是闭区间19 和 21 都能查到等价于age 19 AND age 21。LIKE 里%匹配任意多个字符_匹配一个字符张%就是姓张。最后一条特别要注意不等于比较遇到 NULL 会返回 UNKNOWN所以必须补OR dept IS NULL否则院系为空的学生会被漏掉。注意任何和 NULL 的比较—— NULL、 NULL、 NULL——结果都是 UNKNOWN不会返回任何行。判断空值只能用IS NULL或IS NOT NULL。3.2 排序、去重与限制返回行数查出来数据之后排序和截取是高频操作。ORDER BY 默认升序DESC 降序LIMIT 用来限制返回条数做分页或者只看前几名时特别有用-- 按年龄降序排列年龄相同按学号升序 SELECT sno, sname, age FROM student ORDER BY age DESC, sno ASC; -- 查询所有院系去掉重复值 SELECT DISTINCT dept FROM student; -- 查询年龄最大的前3名学生 SELECT sno, sname, age FROM student ORDER BY age DESC LIMIT 3; -- 分页跳过前2条取接下来2条 SELECT sno, sname FROM student ORDER BY sno LIMIT 2, 2;ORDER BY age DESC, sno ASC是多列排序先按年龄降序年龄一样再按学号升序这个组合在成绩排名里很常用。DISTINCT作用于后面所有列的组合SELECT DISTINCT dept是去重院系如果写SELECT DISTINCT dept, gender就是按院系和性别的组合去重。LIMIT 3取前三条LIMIT 2, 2是跳过前两条取两条注意 MySQL 的 LIMIT 偏移量是从 0 开始算的LIMIT 2, 2返回的是第 3、4 条。这里有个血泪经验ORDER BY 和 LIMIT 一起用时如果排序字段有重复值每次查询返回的顺序可能不一样因为 MySQL 不保证稳定排序。做分页时最好在 ORDER BY 里加上主键这种唯一字段保证结果可复现。4. 聚合、分组与子查询统计类查询的写法与边界4.1 GROUP BY 配合聚合函数的正确姿势实验训练2 里统计类题目占比很高比如「查询每个院系的人数」「查询每门课程的平均分」。这类查询的核心是 GROUP BY 加聚合函数但很多人会踩一个经典坑SELECT 里出现了非聚合、非分组的列。-- 统计每个院系的学生人数 SELECT dept, COUNT(*) AS stu_count FROM student GROUP BY dept; -- 统计每门课程的最高分、最低分、平均分 SELECT cno, MAX(grade) AS max_grade, MIN(grade) AS min_grade, ROUND(AVG(grade), 1) AS avg_grade FROM score GROUP BY cno; -- 只统计平均分大于80的课程用 HAVING 过滤分组 SELECT cno, ROUND(AVG(grade), 1) AS avg_grade FROM score GROUP BY cno HAVING avg_grade 80; -- 统计每个院系男生和女生的人数多列分组 SELECT dept, gender, COUNT(*) AS cnt FROM student GROUP BY dept, gender ORDER BY dept, gender;第一条SELECT dept, COUNT(*)里 dept 出现在 GROUP BY 中合法。如果你写成SELECT sno, dept, COUNT(*) ... GROUP BY deptsno 既不是聚合列也不是分组列MySQL 8.0 默认的 ONLY_FULL_GROUP_BY 模式会直接报错5.7 以前会随机返回一行结果不可控。HAVING和WHERE的区别要记牢WHERE 在分组前过滤行HAVING 在分组后过滤组聚合函数的结果只能用 HAVING 过滤。ROUND(AVG(grade), 1)把平均分保留一位小数不加 ROUND 可能出来一长串。提示如果 HAVING 里用了 SELECT 中定义的别名比如 avg_gradeMySQL 是允许的但标准 SQL 不允许换数据库时可能报错稳妥写法是 HAVING AVG(grade) 80。4.2 子查询IN、EXISTS 与标量子的选择子查询是实验训练2 的难点也是慢 SQL 的高发区。常见形式有三种标量子查询返回一个值、IN 子查询返回一列值、EXISTS 子查询判断是否存在。-- 标量子查询查询成绩高于全体平均分的学生 SELECT sno, cno, grade FROM score WHERE grade (SELECT AVG(grade) FROM score); -- IN 子查询查询选修了 C001 课程的学生姓名 SELECT sno, sname FROM student WHERE sno IN (SELECT sno FROM score WHERE cno C001); -- EXISTS 子查询查询有成绩记录的学生 SELECT sno, sname FROM student s WHERE EXISTS (SELECT 1 FROM score sc WHERE sc.sno s.sno); -- 相关子查询查询每门课程中高于该课程平均分的学生 SELECT sno, cno, grade FROM score sc1 WHERE grade (SELECT AVG(grade) FROM score sc2 WHERE sc2.cno sc1.cno);标量子查询必须保证只返回一个值如果子查询返回多行会报 Subquery returns more than 1 row。IN 子查询适合子查询结果集不大的场景结果集超过几千行时性能会明显下降。EXISTS 只关心子查询有没有返回行不关心返回什么所以写SELECT 1就够了它比 IN 更适合大表关联判断。最后一条相关子查询子查询里引用了外层的sc1.cno每处理一行外层数据就执行一次子查询逻辑清晰但数据量大时慢实际项目里更推荐用 JOIN 改写。这里给一个用 JOIN 改写相关子查询的例子结果一样但通常更快-- 用 JOIN 改写上面的相关子查询 SELECT sc1.sno, sc1.cno, sc1.grade FROM score sc1 JOIN (SELECT cno, AVG(grade) AS avg_g FROM score GROUP BY cno) t ON sc1.cno t.cno WHERE sc1.grade t.avg_g;子查询先算出每门课的平均分再和原表 JOIN避免了逐行执行子查询。两种写法都要会考试可能考子查询实际工作更倾向 JOIN。5. 多表连接查询JOIN 类型选错结果全废5.1 INNER JOIN、LEFT JOIN 与 RIGHT JOIN 的差异多表连接是实验训练2 最后一块硬骨头。三种连接的差别用一句话概括INNER JOIN 只返回两表都匹配的行LEFT JOIN 返回左表全部加右表匹配的RIGHT JOIN 反过来。选错了不是报错而是结果少行或多行最难排查。-- INNER JOIN只查有成绩的学生和课程信息 SELECT s.sno, s.sname, c.cname, sc.grade FROM student s INNER JOIN score sc ON s.sno sc.sno INNER JOIN course c ON sc.cno c.cno; -- LEFT JOIN查所有学生没成绩的也显示成绩为 NULL SELECT s.sno, s.sname, sc.cno, sc.grade FROM student s LEFT JOIN score sc ON s.sno sc.sno; -- 找出没选任何课程的学生用 LEFT JOIN IS NULL SELECT s.sno, s.sname FROM student s LEFT JOIN score sc ON s.sno sc.sno WHERE sc.sno IS NULL;第一条 INNER JOIN 链式连接三张表只返回有成绩记录的学生张伟、李娜这些有成绩的会出现如果某个学生没选课就不会出现在结果里。第二条 LEFT JOIN 以 student 为左表所有学生都保留没成绩的课程号和成绩显示 NULL。第三条是 LEFT JOIN 的经典用法——找左表有而右表没有的记录WHERE sc.sno IS NULL筛出没选课的学生。这个模式在实际工作里叫反连接比 NOT IN 更安全因为 NOT IN 遇到子查询里有 NULL 会返回空集。注意LEFT JOIN 后面的 WHERE 条件如果写在 ON 里和写在 WHERE 里效果不同。ON sc.cno C001会在连接时就过滤左表所有行保留WHERE sc.cno C001会在连接后过滤把左表没匹配的行也删掉LEFT JOIN 就退化成 INNER JOIN 了。5.2 多表连接的执行顺序与笛卡尔积防范三张表以上连接时写 JOIN 的顺序和 ON 条件的位置会影响结果和性能。最常见的翻车是漏写 ON 条件导致笛卡尔积——两张表行数相乘5 个学生乘 8 条成绩出来 40 行数据量一大直接卡死。-- 错误示范漏写 ON 条件产生笛卡尔积 SELECT s.sname, c.cname FROM student s, course c; -- 5 * 3 15 行全是无意义组合 -- 正确写法显式 JOIN ON SELECT s.sname, c.cname, sc.grade FROM student s JOIN score sc ON s.sno sc.sno JOIN course c ON sc.cno c.cno WHERE s.dept 计算机 ORDER BY s.sno, c.cno;第一条用逗号连接但没写 WHERE 关联条件MySQL 会返回所有组合这就是笛卡尔积。第二条显式写 JOIN 和 ON连接条件清晰再加 WHERE 过滤院系。写多表查询时我一般遵循三个习惯一是永远用显式 JOIN 语法不用逗号加 WHERE二是每写一个 JOIN 立刻补上 ON三是连接字段尽量都有索引外键字段 MySQL 会自动建索引但非外键的连接字段要手动加。连接字段的数据类型也要一致。如果 student.sno 是 CHAR(8)score.sno 是 VARCHAR(8)虽然值一样但 MySQL 可能无法用索引导致全表扫描。建表时关联字段类型统一这是建表阶段就该定好的事。6. 查询排查避坑五条上机课反复出现的翻车记录6.1 现象WHERE 条件查不出数据表里明明有原因字符串值没加引号或者和 NULL 做了等值比较。WHERE sname 张伟会被当成列名WHERE dept NULL永远返回空集。解决字符串一律加单引号判断空值用IS NULL。排查时先把 WHERE 去掉看全表有没有数据再逐条加条件定位是哪一条出的问题。6.2 现象GROUP BY 查询报错 ONLY_FULL_GROUP_BY原因SELECT 列表里出现了既不在 GROUP BY 中、也没被聚合函数包裹的列。MySQL 8.0 默认开启 ONLY_FULL_GROUP_BY5.7 之前不报错但结果随机。解决要么把该列加进 GROUP BY要么用聚合函数包起来。比如SELECT dept, sname, COUNT(*) ... GROUP BY dept就是错的sname 要么去掉要么改成GROUP_CONCAT(sname)。6.3 现象多表连接结果行数比预期多很多原因漏写 ON 条件产生笛卡尔积或者连接条件不唯一导致一对多放大。比如一个学生有多条成绩和课程表连接时每个成绩都会匹配一次课程。解决先单独查每张表的行数再查连接后的行数对比是否合理。检查每个 JOIN 是否都有 ONON 的字段是否能唯一确定匹配关系。6.4 现象LEFT JOIN 查出来和 INNER JOIN 一样原因WHERE 子句里对右表字段加了非空过滤条件把左表没匹配的行过滤掉了LEFT JOIN 退化成 INNER JOIN。解决把右表的过滤条件从 WHERE 移到 ON 里。比如LEFT JOIN score sc ON s.sno sc.sno WHERE sc.grade 80会丢掉没成绩的学生改成LEFT JOIN score sc ON s.sno sc.sno AND sc.grade 80才能保留。6.5 现象子查询报 Subquery returns more than 1 row原因标量子查询的位置比如WHERE grade (SELECT ...)要求子查询只返回一个值但子查询实际返回了多行。解决确认子查询是否应该加聚合函数AVG、MAX 等保证单值或者改用 IN、EXISTS、JOIN 来处理多行结果。用SELECT COUNT(*)先看子查询返回几行定位问题最快。7. 用 EXPLAIN 看懂查询计划一条慢 SQL 的排查习惯实验训练2 的查询数据量小怎么写都快但养成看执行计划的习惯到了真实项目能救命。EXPLAIN 放在 SELECT 前面MySQL 会告诉你这条查询走了哪个索引、扫描了多少行、有没有用临时表。-- 查看单表条件查询的执行计划 EXPLAIN SELECT sno, sname FROM student WHERE dept 计算机; -- 查看多表连接的执行计划 EXPLAIN SELECT s.sname, c.cname, sc.grade FROM student s JOIN score sc ON s.sno sc.sno JOIN course c ON sc.cno c.cno WHERE s.dept 计算机;输出里重点看四列。type 列从好到坏是 const、eq_ref、ref、range、index、ALL出现 ALL 就是全表扫描数据量大时要优化。key 列显示实际用了哪个索引如果是 NULL 说明没走索引。rows 列是预估扫描行数越小越好。Extra 列出现 Using filesort 或 Using temporary 说明有额外排序或临时表开销也值得关注。我一般排查慢查询的顺序是先 EXPLAIN 看 type 和 key确认有没有走索引再看 rows 估算是否合理最后看 Extra 有没有 filesort。如果 WHERE 或 JOIN 的字段没索引加索引通常是最直接的优化。但索引不是越多越好每个索引都会拖慢写入实验阶段先理解「连接字段和常用过滤字段加索引」这个原则就够了。最后说个我自己的习惯每次写完一条复杂查询先不加 ORDER BY 和 LIMIT 跑一遍看总行数确认结果集大小符合预期再加排序和分页。这个顺序能帮你快速区分是连接逻辑错了还是排序截取错了。数据查询操作这件事语法只是入场券真正拉开差距的是对结果集的预判和验证习惯。希望帮到你。本文还有配套的精品资源点击获取