简介《国家开放大学MySQL数据库应用实验训练2数据查询操作》是一份面向数据库初学者的实验指导文档旨在通过具体练习题帮助学习者掌握SQL查询的核心语法与使用场景。全包仅含1个PDF文件大小约1.75MB内容按16个小实验层层递进涵盖字段查询、多条件查询、DISTINCT去重、ORDER BY排序、GROUP BY分组、COUNT/SUM/AVG/MAX/MIN等聚合函数、内连接、外连接及嵌套查询等主题基本覆盖了数据库查询的完整知识链。已有3336人浏览学习。每个实验均先给出查询题目再给出SQL分析与操作思路既适合课堂同步练习也适合考前快速复习。通过完成这些实验学习者可以系统掌握SELECT语句的常用子句与聚合函数用法理解单表查询与多表关联的实现思路为后续数据库开发与管理打下扎实基础。1. 数据查询操作不是照着敲而是十几条实验要求的组合体MySQL数据库应用这门课的实验训练2叫数据查询操作表面上是让你敲几条SELECT交付物却是一堆“查出每个专业人数”“统计各课程平均分”“输出成绩大于80的学生名单”这样的具体结果。我帮几位同学调试这份实验作业时发现绝大多数问题不是SELECT语法不会写而是建表的数据没备齐、多表之间关系没理清、聚合函数用错了位置同一段代码在别人机器上跑出两种结果。这篇文章就顺着“建库建表 → 准备数据 → 单表查询 → 聚合分组 → 多表连接 → 避坑 → 加分验证”这条路线把实验2完整拆开讲。适合正在赶实验2提交的初学者也适合想快速过一遍查询操作边界的老手。2. 建库建表并准备数据查询实验的地基2.1 从建库开始字符集和排序规则怎么选查询操作能不能跑对一半在查询语句另一半在表结构和数据上。常见做法是先建库再建表库的字符集直接决定后面中文数据会不会乱码、LIKE匹配会不会出怪问题。我用下面这段DDL创建库CREATE DATABASE IF NOT EXISTS school_db DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;逻辑说明utf8mb4是MySQL里对中文、生僻字、表情符号支持最完整的字符集utf8mb3也就是以前的utf8存4字节字符时会报错说字符超出范围。COLLATE是排序规则utf8mb4_general_ci不区分大小写做字符串比较和排序时更省心学生在做“按姓名排序”“查某个专业”这类操作时不容易因为大小写问题翻车。参数说明DEFAULT CHARACTER SET和DEFAULT COLLATE会被后续建表的语句继承只要建库时指定一次后面CREATE TABLE里不写CHARSET也默认是这个比每张表单独声明少写很多重复配置也避免出现“库是好端端的中文表却是乱码”的割裂状态。这里有个我常看到的情况有人直接CREATE TABLE不指定字符集表就继承了MySQL服务端的全局配置。如果全局是latin1中文存进去显示成问号后面所有WHERE name张同学的查询都白搭。所以训导库里第一步就把字符集钉死是最便宜的后悔药。如果你担心大小写敏感影响实验判分不要用utf8mb4_bin用general_ci就好判分一般也按不区分大小写的标准来。2.2 三张业务表学生表、课程表、成绩表实验2的核心是查询表结构要能支持三类查询场景单表查、分组统计、多表连接。我一般用一个典型的教务结构学生表存学生基本信息课程表存课程信息成绩表存学号与课程号的对应关系。建表语句如下USE school_db; CREATE TABLE student ( stu_id CHAR(10) PRIMARY KEY COMMENT 学号, stu_name VARCHAR(20) NOT NULL COMMENT 姓名, major VARCHAR(30) COMMENT 专业, enroll_year YEAR COMMENT 入学年份 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE course ( course_id CHAR(6) PRIMARY KEY COMMENT 课程号, course_name VARCHAR(50) NOT NULL COMMENT 课程名, credit DECIMAL(3,1) COMMENT 学分 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE score ( stu_id CHAR(10) NOT NULL COMMENT 学号, course_id CHAR(6) NOT NULL COMMENT 课程号, score DECIMAL(5,2) COMMENT 成绩, PRIMARY KEY (stu_id, course_id), CONSTRAINT fk_score_student FOREIGN KEY (stu_id) REFERENCES student(stu_id), CONSTRAINT fk_score_course FOREIGN KEY (course_id) REFERENCES course(course_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;逻辑说明学号用CHAR(10)而不是INT是因为学号可能有前导零整数类型会把它吃掉查询时对不上。成绩用DECIMAL(5,2)表示最大999.99保留两位小数符合大多数成绩表习惯。score表把主键设为(stu_id, course_id)联合主键意义是一个学生同一门课只能有一条成绩记录重复插入会直接报错。参数说明ENGINEInnoDB是为了外键约束能生效如果用了MyISAM外键会被忽略后面做连接查询时成绩表里出现不存在的学号也不会被拦住实验数据就脏了。外键约束是这次实验中容易被忽视的一点。它的作用是保证score表里的stu_id必须存在于student表course_id必须存在于course表防止实验过程中插入“无主成绩”。代价是插入顺序有讲究先插学生、课程再插成绩否则报外键失败。很多学生一上来先插成绩报错后一脸懵其实是顺序问题不是语法问题。2.3 插入测试数据数据量要能撑起查询结果建完表就该往里放数据。数据量太少会导致“查什么都是三五行”分组统计结果没有说服力太多又会让实验操作变慢。我给学生准备的是一组最能覆盖课程要求的数据让每个查询场景都能得出有区分度的结果。执行插入INSERT INTO student VALUES (20230001,张同学,计算机科学与技术,2023), (20230002,李同学,软件工程,2023), (20230003,王同学,数据科学,2022), (20230004,赵同学,计算机科学与技术,2022); INSERT INTO course VALUES (C001,数据库原理,3.0), (C002,操作系统,3.5), (C003,数据结构,4.0), (C004,计算机网络,2.5); INSERT INTO score VALUES (20230001,C001,88.5), (20230001,C002,79.0), (20230002,C001,92.0), (20230002,C003,85.5), (20230003,C001,61.0), (20230003,C004,72.5), (20230004,C002,95.0), (20230004,C003,76.0);逻辑说明这样安排有一个用意——score表里不是每个学生都选了每门课个别学生的课程有缺口后续做LEFT JOIN时才能看出内连接和外连接的区别。数据中混有不及格成绩61分、高分95分、重复课程C001被三个学生选聚合统计时AVG、MAX、MIN、COUNT的结果才有层次。参数说明INSERT可以一次插多行VALUES后面用逗号分隔元组如果学生表要加人直接用单条INSERT语句也行实验判分不依赖插入条数只依赖最终查询结果。插完数据建议先跑一条SELECT * FROM student;确认记录都在。我遇到过一个典型问题插入时字段顺序和VALUES顺序没对齐把2023填进了name列查出来张同学变成数字后面所有与“姓名”相关的条件查询全废。插入语句没有显式列名时VALUES顺序必须严格对应建表语句的字段顺序这是新手最容易踩但不看报错的地方。3. 单表查询与聚合统计SELECT的完整拼图3.1 基础SELECT与列别名先学会控制输出单表查询是这个实验最基础的部分第一件事是掌握SELECT的投影能力也就是只要某些列、不要某些列。常见写法是SELECT stu_id, stu_name, major FROM student; SELECT stu_id AS 学号, stu_name AS 姓名 FROM student;逻辑说明第一条查询只返回三个指定列第二条用AS把列标题换成中文别名。做实验训练2时输出结果的列名要和实验要求里的表述一致很多自动判分系统是按结果集列名对比的不写别名导致输出列叫stu_id而要求是学号结果明明对却被判错。参数说明AS可以省略不写写成SELECT stu_id 学号也合法但省略了可读性会差多人协作改脚本时容易分不清哪个是列哪个是别名我建议一律写AS。这里再补一个LIMIT的使用习惯SELECT stu_id, stu_name FROM student LIMIT 2;LIMIT 2表示最多返回2行LIMIT 1,2表示跳过第1行取2行。在实验后续翻数据量大的表时先用LIMIT小范围确认结果比一次性拉全表更安全也不会因为结果集太长把客户端卡住。3.2 WHERE条件过滤精确值、区间和NULL的判断查询操作的核心在WHERE。熟练使用WHERE是实验2评分占比最大的部分。几条必会的条件写法如下SELECT stu_name, major FROM student WHERE major 计算机科学与技术; SELECT stu_name, enroll_year FROM student WHERE enroll_year BETWEEN 2022 AND 2023; SELECT stu_id, score FROM score WHERE score 60 AND score 90;逻辑说明BETWEEN是闭区间BETWEEN 2022 AND 2023会把2022和2023都包含进去这点和很多学生以为的“不包含右边界”不同。score判断用和拼接是为了和BETWEEN的写法做对比两者结果有时差一点点交作业时建议先在客户端里各跑一遍看行数。参数说明字符串比较要带引号数字比较可以直接写和NULL比较不能用要用IS NULL或IS NOT NULL比如WHERE enroll_year IS NULL才能查出没填年份的学生。讲个真实的坑有学生想查“2022年之后入学的人”写了WHERE enroll_year 2022结果漏掉了2023年的记录。原因是enroll_year是YEAR类型比较没问题但数据里2023年的人恰好没插全漏查后他怀疑是类型问题折腾半天。这类问题排查时先查数据本身再查语句顺序别反。3.3 LIKE模糊查询与DISTINCT去重两个容易混淆的操作实验里经常要求“查专业带‘计算机’的学生”“查名字里含‘同学’的人”这就要用到LIKE模糊匹配SELECT stu_id, stu_name, major FROM student WHERE major LIKE %计算机%; SELECT DISTINCT major FROM student;逻辑说明%是任意长度的通配符包在关键词两边表示包含匹配。专业的字符串是“计算机科学与技术”LIKE %计算机%能匹配到LIKE 计算机%只能匹配以计算机开头的专业。DISTINCT的作用是去掉重复行这里把专业列去重可以看到这个库里有几个不同的专业。参数说明LIKE对中文匹配依存于字符集表创建时是utf8mb4就不会有问题如果表是latin1LIKE %计算机%查不出任何结果这是排查方向之一不是SQL语法错误。不要在一列上既用DISTINCT又用LIKE比如SELECT DISTINCT major LIKE %计算机%它会返回0和1两个值表达的意思完全不同。实验里如果要求“列出所有专业重复的只算一次”就只要DISTINCT要求“按条件筛专业”就用WHERE LIKE两者各管一段。3.4 聚合函数与GROUP BY行变组的关键一步聚合是实验2里最容易拉开差距的部分。COUNT、SUM、AVG、MAX、MIN五个函数必须会用配合GROUP BY做分组统计。示例SELECT major, COUNT(*) AS 人数 FROM student GROUP BY major; SELECT course_id, AVG(score) AS 平均分, MAX(score) AS 最高分 FROM score GROUP BY course_id HAVING AVG(score) 70;逻辑说明第一个查询按专业分组统计每个专业的人数第二个按课程分组算出每门课的平均分和最高分再用HAVING把平均分大于70的课程筛出来。这里重难点是HAVING和WHERE的区别WHERE在分组之前过滤原始行HAVING在分组之后过滤组。WHERE里不能写COUNT()、AVG(score)这类聚合函数这是MySQL会直接报错的而HAVING专门用来做组级条件判断。参数说明GROUP BY后面的列必须和SELECT里出现的非聚合列一致比如SELECT major, COUNT()就必须GROUP BY major如果把major换成stu_nameMySQL在only_full_group_by模式下直接报错不报错的版本也是随机取一条name结果没意义。另一个常见误用是SELECT中混入未被分组的列比如按课程分组却查询学生姓名语义上是矛盾的。解决办法是严格遵循“SELECT里出现的普通列必须出现在GROUP BY里”这条铁律。聚合函数之间可以组合嵌套但嵌套层数太多会降低可读性建议一个步骤只做一件事分步查询再用临时表或子查询接起来。4. 多表连接与子查询从一张表查到三张表4.1 INNER JOIN把成绩表、学生表、课程表串起来单表查询一旦做完实验的核心自然落到多表连接上。score表里只有学号和课程号看不出姓名和课程名连接就是把分散在三张表的信息拼成一张宽表。最常见的写法是INNER JOINSELECT s.stu_id, st.stu_name, c.course_name, sc.score FROM score sc INNER JOIN student st ON sc.stu_id st.stu_id INNER JOIN course c ON sc.course_id c.course_id WHERE sc.score 60 ORDER BY sc.score DESC;逻辑说明score表作为连接起点通过学号关联student表拿到姓名通过课程号关联course表拿到课程名查询结果是一个完整的三表宽表。INNER JOIN只返回两表都匹配上的行如果有学生在score表没有成绩记录这条数据不会出现这是内连接的语义很多学生在做左右连接对比时理解反了。参数说明ON后面是连接条件通常用主键等于外键表别名sc、st、c是为了让多表查询的语句简短可读如果不加别名查询里到处是score.score这样嵌套表名的写法脚本稍长就眼花。最容易翻车的点是漏写连接条件。有人写成FROM score, student, course WHERE sc.score 60相当于做了三张表的笛卡尔积结果行数爆炸成几十行每个学生都和所有课程交叉一遍。排查这类错误第一眼看WHERE里有没有连接条件再看ON写的是不是两表都有关联的外键和主键。实验2里如果发现结果行数异常多九成是这里出了问题不是数据错了。4.2 子查询与EXISTS什么时候用嵌套什么时候用连接多表关联除了JOIN还有子查询这种写法。它适合把一个问题拆成“先查一个集合再在这个集合里查结果”的场景。实验里典型的需求是“查成绩大于全体平均分的学生成绩”SELECT stu_id, course_id, score FROM score WHERE score ( SELECT AVG(score) FROM score ); SELECT stu_id, stu_name FROM student st WHERE EXISTS ( SELECT 1 FROM score sc WHERE sc.stu_id st.stu_id AND sc.score 60 );逻辑说明第一个子查询在括号里先算出全表平均分外层再用这个值做比较这种写法把“求平均”和“比较”拆成两层逻辑清晰。第二个查询用EXISTS判断学生是否存在不及格记录EXISTS关心的是子查询有没有返回行返回了就成立SELECT 1只是占位。参数说明子查询可以放在WHERE、FROM、SELECT后面放在FROM后面时作为一个派生表比如SELECT * FROM (SELECT ...) AS tEXISTS和IN的区别是EXISTS是逐行判断适合子查询结果很大的情况IN是先算完子查询结果集合再匹配子查询结果小的时候两者都能用。实际做实验时我发现学生容易把连接查询和子查询混用。一个需求能用JOIN写出来也能用子查询写出来两种都正确但执行效率不一样。数据量小的实验表感觉不到区别如果后面自己扩展数据到几十万行用EXISTS通常比NOT IN更稳因为NOT IN遇到NULL值会屏蔽掉所有行这是个隐形的黑匣子。写作业时我的建议是看实验要求里描述的是“关联两张表”优先JOIN描述是“在某个结果范围内再筛选”优先子查询语义和出题思路更吻合。5. 数据查询操作避坑指南5个常见翻车现场5.1 WHERE里写聚合函数直接报错现象执行WHERE AVG(score) 60MySQL报错说聚合函数不能用在WHERE子句学生以为是语法记错反复改逗号、括号都没用。原因WHERE是在数据分组之前逐行过滤的聚合函数需要先对所有行做计算两者执行顺序矛盾。SQL的执行顺序是FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY聚合发生在WHERE之后。解决把条件移到HAVING里或者先用子查询算出聚合结果再在外层比较。比如要查“平均分大于70的课程”用GROUP BY course_id加上HAVING AVG(score) 70不要在WHERE里做这件事。养成写完WHERE先想一句“这个条件能不能在分组前判断”的习惯。5.2 GROUP BY后SELECT了无关列现象执行SELECT stu_name, COUNT(*) FROM score GROUP BY course_id在某些版本MySQL下不报错但stu_name返回的值是乱的一会是张同学一会是李同学判定结果不稳定。原因SELECT里的普通列stu_name没有被GROUP BY包含也没有包在聚合函数里。MySQL 5.7以上默认开启only_full_group_by模式会直接报错没开启的版本会随机选一行的值结果不可控。解决要么把stu_name加进GROUP BY要么把它改成都用聚合函数表达。分组查询遵循一个原则SELECT里出现的每个普通列都能由GROUP BY的列唯一确定做不到就用聚合函数包裹。写完分组查询先把SELECT列表逐个对一遍GROUP BY列表能省一大半调试时间。5.3 JOIN忘记连接条件结果行数暴涨现象三张表连接查询后返回了几百行正常应该是二十几行翻到最后数据错乱看起来“查出了不存在的数据”。原因这是一种笛卡尔积灾变FROM score, student, course没有把连接条件写进WHERE或ON三张表互相交叉相乘行数等于三表行数的乘积。解决用显式JOIN语法时把连接条件放ON里过滤条件放WHERE里两者分开写逻辑不会乱。如果是逗号连接老写法必须把连接条件和过滤条件一起写进WHERE比如WHERE sc.stu_id st.stu_id AND sc.course_id c.course_id。顺便提一句笛卡尔积不一定都错实验要求“两两组合所有课程和学生”时反而要用但那是有意为之不是疏忽。5.4 中文LIKE查不出结果先怀疑字符集现象明明表里有“计算机科学与技术”专业WHERE major LIKE %计算机%查出来是空换相等判断也是空数据一条都摸不到。原因大概率是建表时字符集不是utf8mb4或数据库连接层字符集设置不对中文搜索条件在传输时被转成错误编码匹配不上。少数情况是数据里混入了全角空格、换行符导致字符串在视觉上一样但字节不同。解决先查表结构SHOW CREATE TABLE student确认带不带utf8mb4。表没问题就检查连接串在连接URL里加上characterEncodingutf8。最后用SELECT QUANTITY(stu_name), stu_name FROM student GROUP BY stu_name看看有没有隐藏字符。字符集问题排查顺序固定为库→表→连接→数据不要在SQL语法上反复耗时间。5.5 联合主键重复插入和外键约束失败同时出现现象往score表里插一条记录报错提示Duplicate entry或外键约束失败数据没有写进去。原因score表主键是(stu_id, course_id)同一个学生同一门课只能有一条记录重复插入被拒或者插入的学号/课程号在student/course表里不存在外键约束拦截。解决插入前用一条查询确认学生和课程都存在比如SELECT * FROM student WHERE stu_id20230005; 再用SELECT * FROM score WHERE stu_id20230001 AND course_idC001; 检查是否已存在。如果实验要求更新成绩而不是新增用INSERT ... ON DUPLICATE KEY UPDATE或先DELETE再INSERT不要硬插。这个报错看着吓人其实是数据库在保护数据一致性不是环境坏了。6. 进阶验证用EXPLAIN和视图给实验加分实验2交完后如果想在原有基础上多花半小时做点加分项我推荐两个动作用EXPLAIN看执行计划用视图固化常用查询。先看EXPLAIN。在每个关键查询前面加上EXPLAIN关键字MySQL会返回一张执行计划表EXPLAIN SELECT sc.stu_id, st.stu_name, c.course_name, sc.score FROM score sc INNER JOIN student st ON sc.stu_id st.stu_id INNER JOIN course c ON sc.course_id c.course_id WHERE sc.score 60;重点看type字段。如果是ALL说明是全表扫描实验数据小看不出问题换大表就会慢。如果看到eq_ref或者ref说明走了索引连接效率高。随后可以给score表加一个复合索引CREATE INDEX idx_score_stu ON score(stu_id, course_id);这个索引既服务了成绩表本身的联合主键查询也加快了通过学号连接student表的操作。加完再跑EXPLAIN对比type字段变化就是一份很好的实验结论。再看视图。如果实验报告里有重复出现的查询需求比如“学生成绩明细”这种每次都要三表连接的操作可以建一个视图CREATE VIEW v_student_score AS SELECT st.stu_id, st.stu_name, c.course_name, sc.score FROM score sc INNER JOIN student st ON sc.stu_id st.stu_id INNER JOIN course c ON sc.course_id c.course_id;之后直接SELECT * FROM v_student_score WHERE score 60;底层逻辑封装在视图里写起来像查一张单表。视图不真正复制数据查询时临时展开不影响原有表结构实验报告中把视图和原查询的结果各放一份作对比能明显看出你理解了一层。我现在的习惯是每次写完SELECT先跑EXPLAIN确认没有全表扫描再交结果。这个习惯让我在数据量变大的项目里少踩了很多性能坑也是从“完成实验”走向“会写查询”的分水岭。实验训练2的价值就在于把查询操作从语法层面推进到逻辑层面希望帮到你。本文还有配套的精品资源点击获取