简介本资源是国家开放大学《MySQL数据库应用》课程配套实验训练材料面向数据库初学者、高职高专及成人教育学员系统训练SQL数据查询核心能力。内容覆盖字段查询、多条件筛选、DISTINCT去重、ORDER BY排序、GROUP BY分组、COUNT/SUM/AVG/MAX/MIN等聚合函数应用以及INNER JOIN、LEFT JOIN、复合连接与IN/EXISTS嵌套子查询等典型场景全部基于汽车用品网上商城真实业务模型展开含13个递进式实验任务与详细分析说明。资源为单个PDF文档1.75MB结构清晰、步骤完整、语义明确适合作为课堂实训手册或自学练习指南。已有3335人学习下载可直接用于巩固SELECT语法体系、理解查询逻辑分层、掌握多表关联与聚合统计的实际写法。1. 为什么“国家开放大学 MySQL数据库应用 实验训练2数据查询操作”不是抄作业而是你真正掌握SQL的分水岭很多人拿到这个实验标题第一反应是“又是照着课本敲SELECT语句”——错了。这根本不是语法填空题而是一次对真实业务查询思维的系统性校准。国家开放大学这门课面向的是在职学习者、基层技术人员和转行新人他们常卡在“能写简单WHERE但一遇到多表关联就查不出正确结果”“ORDER BY排得不对却找不到原因”“明明写了DISTINCT还是重复”这类问题上。实验训练2表面只做“数据查询”实则覆盖了WHERE条件组合的优先级陷阱、JOIN类型选择的业务语义偏差、GROUP BY与聚合函数的隐式分组逻辑、子查询嵌套时的执行顺序错位四大高频翻车点。它不考你背命令而是逼你用MySQL验证自己对“数据关系”的理解是否准确。如果你刚学完SELECT FROM WHERE建议先别急着跑通代码——先想清楚你查出来的每一行到底是“学生信息”还是“某门课的平均分”这个语义边界一旦模糊后面所有优化、索引、性能调优全都会走偏。这节实验就是帮你把SQL从“字符串拼接”拉回“关系代数落地”的关键一课。2. 用真实实验环境还原国家开放大学标准数据集建库、建表、导入数据三步到位国家开放大学《MySQL数据库应用》课程配套实验通常基于一个教学型教务管理系统数据模型核心包含student学生、course课程、sc选课成绩三张表。该模型虽简化但完整覆盖主键、外键、非空约束、默认值等基础设计要素且数据量控制在200~500行之间既保证查询响应速度又足够暴露逻辑错误。下面按实验要求严格复现标准环境。2.1 创建数据库与三张核心表结构含字段注释与约束我们不直接复制粘贴网上流传的“实验答案SQL”而是按国家开放大学教材中《实验指导书》第2章明确列出的字段定义来构建。重点注意sc表中的grade字段为DECIMAL(5,2)而非INT这是为后续计算平均分预留精度student表中sdept所在院系允许NULL反映实际教务中部分学生尚未分配院系的场景。-- 创建数据库名称与教材一致避免路径混淆 CREATE DATABASE IF NOT EXISTS gkdx_db CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; USE gkdx_db; -- 学生表sno学号为主键sname姓名非空sage年龄有检查约束 CREATE TABLE student ( sno CHAR(10) PRIMARY KEY, sname VARCHAR(20) NOT NULL, ssex CHAR(2) CHECK (ssex IN (男, 女)), sage INT CHECK (sage BETWEEN 16 AND 50), sdept VARCHAR(30) NULL ); -- 课程表cno课程号为主键cname课程名非空cpno先行课为外键指向自身 CREATE TABLE course ( cno CHAR(10) PRIMARY KEY, cname VARCHAR(40) NOT NULL, cpno CHAR(10) NULL, ccredit DECIMAL(3,1) DEFAULT 0.0, FOREIGN KEY (cpno) REFERENCES course(cno) ); -- 选课成绩表联合主键(sno,cno)外键引用student和course CREATE TABLE sc ( sno CHAR(10) NOT NULL, cno CHAR(10) NOT NULL, grade DECIMAL(5,2) NULL, PRIMARY KEY (sno, cno), FOREIGN KEY (sno) REFERENCES student(sno) ON DELETE CASCADE, FOREIGN KEY (cno) REFERENCES course(cno) ON DELETE CASCADE );参数说明与设计依据CHAR(10)用于学号/课程号因教学系统中编号长度固定比VARCHAR更省空间且索引效率略高utf8mb4_0900_ai_ci是MySQL 8.0推荐的排序规则支持emoji且大小写不敏感ai_ci符合中文姓名检索需求ON DELETE CASCADE在sc表中启用模拟真实业务中“删除学生时自动清理其选课记录”的级联逻辑避免孤儿数据cpno外键指向course自身体现课程间的先行课依赖关系这是后续自连接查询的伏笔。2.2 插入标准实验数据严格按教材示例值含典型边界案例国家开放大学实验数据并非随机生成而是精心设计了若干“检验点”如student表中存在sage16和sage50的极端值用于验证BETWEEN范围查询sc表中包含gradeNULL的记录表示未录入成绩测试IS NULL与IS NOT NULL的正确用法course表中设置cpno6指向不存在的课程号用于暴露外键约束报错场景。以下为教材指定的最小可行数据集共12条学生、6条课程、15条选课记录-- 插入学生数据含sage16和sage50的边界值 INSERT INTO student VALUES (201215121, 李勇, 男, 20, CS), (201215122, 刘晨, 女, 19, CS), (201215123, 王敏, 女, 18, MA), (201215124, 张立, 男, 19, IS), (201215125, 吴宾, 女, 17, MA), (201215126, 张海, 男, 18, CS), (201215127, 钱小平, 女, 16, IS), (201215128, 孙庆, 男, 22, CS), (201215129, 周梅, 女, 21, MA), (201215130, 王小红, 女, 50, IS); -- 插入课程数据含cpno6的无效先行课触发外键约束 INSERT INTO course VALUES (1, 数据库, NULL, 4.0), (2, 数学, NULL, 2.0), (3, 信息系统, 1, 3.0), (4, 操作系统, 6, 3.0), -- cpno6不存在插入时会报错需先插入cno6 (5, 数据结构, 4, 4.0), (6, 数据处理, NULL, 2.0); -- 插入选课成绩含NULL值用于测试空值处理 INSERT INTO sc VALUES (201215121, 1, 92.00), (201215121, 2, 85.00), (201215121, 3, 88.00), (201215122, 2, 90.00), (201215122, 3, 80.00), (201215123, 3, 90.00), (201215123, 4, 85.00), (201215123, 5, 80.00), (201215124, 1, 90.00), (201215124, 2, 80.00), (201215124, 3, 85.00), (201215125, 2, NULL), -- 关键NULL成绩 (201215125, 3, 95.00), (201215126, 1, 85.00), (201215126, 2, 90.00);执行逻辑说明必须按student → course → sc顺序插入否则外键约束会失败如先插sc再插studentcourse表中cno4的cpno6必须在cno6插入之后才能成功这是教材故意设置的“约束认知关卡”sc表中gradeNULL不是错误而是教学重点——后续实验会要求统计“已录入成绩的学生人数”必须用COUNT(grade)而非COUNT(*)否则结果错误。3. 实验训练2核心查询任务拆解从单表筛选到多表聚合的五层递进国家开放大学实验训练2并非罗列10条独立SQL而是以能力递进为暗线组织从最基础的单表条件筛选Level 1到跨表关联Level 2再到分组统计Level 3最后挑战嵌套子查询Level 4和复杂排序Level 5。每层都对应一个典型业务场景且答案必须通过EXPLAIN验证执行计划合理性。下面逐层还原标准解法并标注教材评分要点。3.1 Level 1单表条件查询——WHERE子句的优先级与NULL处理教材题1-3这是最容易被轻视的层级但恰恰是90%初学者翻车的起点。例如教材题1“查询计算机系CS所有男生的信息”。看似简单但若写成WHERE sdeptCS AND ssex男虽结果正确却忽略了AND运算符的短路特性在大数据量下的潜在影响实际影响极小但教学强调规范。更关键的是题3“查询所有未录入成绩的学生学号”必须用WHERE grade IS NULL而非WHERE grade NULL——后者永远返回空集这是SQL三值逻辑TRUE/FALSE/UNKNOWN的铁律。-- 题1计算机系男生标准写法显式指定字段避免SELECT * SELECT sno, sname, sage, ssex, sdept FROM student WHERE sdept CS AND ssex 男; -- 题2年龄在18~20岁之间的学生BETWEEN包含边界比 AND 更直观 SELECT * FROM student WHERE sage BETWEEN 18 AND 20; -- 题3未录入成绩的学生学号IS NULL是唯一正确写法 SELECT DISTINCT sno FROM sc WHERE grade IS NULL;参数与逻辑说明DISTINCT在题3中必不可少因一个学生可能选多门课且均未录成绩sno会重复出现BETWEEN是闭区间等价于sage 18 AND sage 20但可读性更高教材明确要求使用所有查询必须指定具体字段如题1的sno,sname...禁用SELECT *这是国家开放大学评分细则硬性要求——防止未来表结构变更导致程序崩溃。3.2 Level 2两表关联查询——INNER JOIN与LEFT JOIN的业务语义选择教材题4-5题4“查询每个学生的姓名、选修的课程名及成绩”。这里必须用INNER JOIN因为只关心“既有学生信息又有成绩记录”的数据。若误用LEFT JOIN会导致sc表中无成绩记录的学生如sno201215125仍出现在结果中但cname和grade为NULL不符合“每个学生”在此语境下指“已选课学生”的业务含义。-- 题4学生姓名、课程名、成绩三表关联注意JOIN顺序 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; -- 题5查询所有课程名及其先行课名称自连接需为course表起别名 SELECT c1.cname AS 课程名, c2.cname AS 先行课名称 FROM course c1 LEFT JOIN course c2 ON c1.cpno c2.cno;关键细节LEFT JOIN在题5中必须因为cpno允许NULL如cno1的cpnoNULL用INNER JOIN会丢失无先行课的课程别名c1/c2不可省略否则cname字段歧义MySQL报错Column cname in field list is ambiguousAS关键字可省略但教材示例中强制要求写出培养命名习惯。3.3 Level 3分组聚合查询——GROUP BY的隐式分组逻辑与HAVING过滤时机教材题6-7题6“查询选修了3门以上课程的学生学号”。初学者常犯错误写WHERE COUNT(*) 3——这是语法错误因为WHERE在分组前执行无法访问聚合函数。正确做法是GROUP BY sno后用HAVING COUNT(*) 3HAVING在分组后过滤。-- 题6选修3门以上课程的学生学号HAVING是唯一解法 SELECT sno FROM sc GROUP BY sno HAVING COUNT(*) 3; -- 题7查询各院系学生的平均年龄GROUP BY AVG注意AVG忽略NULL SELECT sdept, AVG(sage) AS avg_age FROM student WHERE sdept IS NOT NULL -- 过滤sdept为NULL的记录避免avg计算偏差 GROUP BY sdept;血泪经验AVG()函数自动忽略NULL值但COUNT(*)统计所有行COUNT(sage)只统计非NULL的sage——题7中若student表有sageNULL记录AVG(sage)结果会失真故教材要求先WHERE sdept IS NOT NULLGROUP BY后SELECT的字段必须是分组字段或聚合函数否则MySQL 5.7严格模式报错sql_modeONLY_FULL_GROUP_BY这是国家开放大学环境默认启用的。4. 避坑指南实验训练2中5个高频翻车点与现场排查方案这节实验的“坑”不是为了刁难学生而是精准对应生产环境中最常被忽视的SQL认知盲区。我带过17期国开学员以下5个问题出现率超80%且90%的人靠百度搜“报错代码”解决却不知底层原理。这里不讲理论只给现象、原因、一步到位的修复命令。4.1 现象执行SELECT * FROM student WHERE sdept CS;返回空集但确认数据中存在CS院系学生原因sdept字段定义为VARCHAR(30)但插入时末尾带空格如CS 比较是精确匹配空格参与比较。解决用TRIM()函数清洗或修改表结构加COLLATE utf8mb4_0900_as_cs大小写敏感且忽略尾部空格。-- 临时修复教学环境推荐 SELECT * FROM student WHERE TRIM(sdept) CS; -- 永久修复需ALTER TABLE ALTER TABLE student MODIFY sdept VARCHAR(30) COLLATE utf8mb4_0900_as_cs;4.2 现象SELECT COUNT(*) FROM sc WHERE grade 90;结果比预期少1人原因grade字段含NULL值NULL参与任何比较运算,,结果均为UNKNOWN被WHERE过滤掉。COUNT(*)统计所有行但WHERE已筛除NULL行。解决明确NULL处理逻辑。若要包含NULL行改用COUNT(*) FILTER (WHERE grade 90 OR grade IS NULL)MySQL 8.0.17但国开环境多为5.7故应-- 教材标准解法用COALESCE将NULL转为0再比较 SELECT COUNT(*) FROM sc WHERE COALESCE(grade, 0) 90;4.3 现象SELECT sname, cname FROM student, course;返回笛卡尔积12×672行远超预期原因未写WHERE关联条件FROM后多表默认CROSS JOIN。这是SQL初学者最经典错误。解决立即补ON条件且必须用JOIN ... ON显式语法禁用逗号分隔的隐式连接教材明令禁止。-- 正确写法即使简单关联也强制JOIN SELECT s.sname, c.cname FROM student s INNER JOIN sc ON s.sno sc.sno INNER JOIN course c ON sc.cno c.cno;4.4 现象UPDATE sc SET grade 95 WHERE sno 201215121 AND cno 1;执行后SELECT查不到更新原因事务未提交。MySQL默认autocommitOFF的教学环境如国开虚拟机UPDATE后必须COMMIT;。解决养成UPDATE/DELETE后立刻SELECT验证的习惯并确认autocommit状态-- 检查当前autocommit状态 SELECT autocommit; -- 若为0则执行 COMMIT; -- 或永久开启仅限教学环境 SET autocommit 1;4.5 现象ORDER BY grade DESC结果中NULL成绩排在最前面而非最后原因MySQL中NULL默认排序位置由sql_mode决定在STRICT_TRANS_TABLES下NULL排最前。业务要求NULL排末尾如成绩未录视为最低。解决用ORDER BY grade DESC, grade IS NULL强制NULL置后。-- 标准解法兼容所有MySQL版本 SELECT sno, cno, grade FROM sc ORDER BY grade DESC, grade IS NULL;5. 进阶验证用EXPLAIN读懂查询执行计划让每条SQL都经得起生产环境拷问做完实验训练2如果只满足于“结果正确”就浪费了这个绝佳的性能启蒙机会。国家开放大学虽不考索引优化但EXPLAIN是验证你是否真正理解查询逻辑的终极标尺。下面用题4的INNER JOIN查询为例手把手教你从执行计划里揪出3个关键信号——它们直接决定这条SQL在百万级数据表上是秒出还是超时。5.1 执行EXPLAIN并解读核心字段以题4查询为例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;idselect_typetabletypepossible_keyskeykey_lenrowsExtra1SIMPLEsALLPRIMARYNULLNULL10NULL1SIMPLEscrefPRIMARY,snosno1015Using index1SIMPLEceq_refPRIMARYPRIMARY101NULL逐字段解读聚焦教学场景typeALL在s表student上表示全表扫描因sno是主键但未在WHERE中过滤只能扫全表。这是正常现象不必优化typeref在sc表表示用到了sno字段的索引possible_keyssnokey_len10证实使用了CHAR(10)索引的全部长度索引利用充分typeeq_ref在c表表示用主键PRIMARY做等值查找效率最高理想状态rows10/15/1预估扫描行数总和远小于笛卡尔积10×15×6900证明JOIN顺序合理。5.2 两个必调参数key_len与Extra中的Using indexkey_len值告诉你索引用了多少字节。sc表sno字段是CHAR(10)UTF8MB4下每个字符占4字节但key_len10说明MySQL用的是前缀索引优化实际存储为CHAR(10)但索引按字节压缩这是好事——意味着索引体积小、内存占用低。若key_len4010×4则说明索引未优化需检查字符集。ExtraUsing index出现在sc行这是黄金信号表示查询所需字段sno,cno,grade全部被sno索引覆盖无需回表查数据页。但注意题4中SELECT的sname,cname,grade来自三张表sc表的索引只覆盖gradesname和cname仍需回表。真正的“覆盖索引”需建联合索引如CREATE INDEX idx_sc_sno_cno_grade ON sc(sno,cno,grade);——但这超出实验范围属于延伸思考。5.3 用FORMATJSON深挖JOIN顺序与成本估算MySQL 5.7教学环境常忽略FORMATJSON但它能暴露EXPLAIN文本模式隐藏的关键信息EXPLAIN FORMATJSON 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;在返回JSON的query_cost字段中你会看到类似query_cost: 28.20的数值。这个数字是MySQL优化器估算的I/O成本。对比修改JOIN顺序后的成本原顺序student→sc→course28.20改为course→sc→student35.70成本上升26%证明原顺序更优——因为sc表15行比course表6行大先连小表再连大表是优化器基本原则。这个数字不是玄学而是你下次写复杂查询时调整JOIN顺序的量化依据。我带的第一届国开学员里有个做社区网格员的学员他把EXPLAIN分析法用在街道人口库查询上把一个12秒的报表查询压到0.8秒。他没改一行代码只是把JOIN顺序按EXPLAIN的rows从小到大重排并给sc表加了INDEX(sno,grade)。后来他告诉我“原来SQL不是写出来就行是算出来才对。” 这句话我一直记着。实验训练2的价值从来不在“查出结果”而在让你第一次亲手触摸到数据流动的脉搏。希望帮到你。本文还有配套的精品资源点击获取