MySQL实训答案的实战指南：从建表到执行计划

简介这是头歌MySQL数据库实训答案的PDF整理版面向正在完成头歌平台数据库实训、需要逐关核对SQL语句的初学者和自学者。压缩包内为1个已生成目录的PDF文档大小433KB按实训关卡的顺序汇总了多章答案可从建库建表一直查到外键删除、数据更新等操作。内容覆盖创建数据库与数据表、主键/外键/唯一/默认值约束、查看表结构、修改表名、变更字段名与字段类型、添加删除字段、调整字段排列位置等典型关卡均给出完整可直接参照的SQL语句对于约束应用部分还保留了一题多写法或复合主键等细节能帮助读者对照理解不同命令的作用。命令原文保留核心语法去掉冗余说明便于逐条阅读和复制核验。当前已有18369人在CSDN学习下载资料体积小、定位明确既适合实训卡点快速排错也适合作为MySQL常用命令的便携速查笔记。1. 头歌MySQL数据库实训答案有目录.pdf这不是题库而是一本带索引的错题集你对着头歌MySQL数据库实训题一个表一个表试错的时候突然拿到一份“头歌MySQL数据库实训答案有目录.pdf”第一反应是捡到宝终于有标准答案可以抄第二反应才是抄也抄不顺本地环境和评测平台的差异、字符集、sql_mode、DELIMITER哪一个都能让同一段SQL在两个环境里表现不一样。这份PDF真正值钱的地方不在最后那几行SQL而在“有目录”三个字它给了你一条按知识点检索答案的路径也逼着你把散落的SQL语法按主题归档。适合读这篇文章的人不是想复制粘贴的人而是想把实训题当成体检报告、顺着目录把MySQL漏洞一个个补齐的人。2. 答案文件覆盖什么MySQL实训从建表到存储过程的五大出题点带目录的答案本质上是一张出题地图。拿这类PDF和实训题逐一比照会发现训练编排非常固定先建库建表再灌数据再写查询再做优化和自动化最后落在存储过程与触发器上。下面按这个顺序把五大出题点拆开同时把每类题里最容易忽略的语法语义讲清楚。2.1 建表与约束主键、外键、唯一键在答案里怎么变形第一类题基本围着CREATE TABLE和ALTER TABLE打转。题目名字各不同但答案里绕不开一个点主键和唯一键的区别。主键在整张表里只允许一个唯一键可以有多个唯一键允许存NULL而NULL在唯一索引里可以出现多次。这道题之所以反复被拿出来考是因为很多人以为“唯一键就是不能重复”却没注意NULL的行不参与重复判断。CREATE TABLE student ( id INT NOT NULL, no VARCHAR(20) NOT NULL, name VARCHAR(50), PRIMARY KEY (id), UNIQUE KEY uk_student_no (no) ); ALTER TABLE student ADD CONSTRAINT fk_student_class FOREIGN KEY (class_id) REFERENCES class(id);逻辑说明第一段建表语句把主键和唯一键都定义在列上告诉MySQL这两类约束各自的名字第二段用ALTER TABLE追加外键是为了把“先建表、后补关系”的流程体现出来。实训题经常同时考这两种写法实际工程里表结构往往是迭代出来的不是一次CREATE就定稿。参数说明PRIMARY KEY (id)会让id列自动成为聚簇索引入口UNIQUE KEY uk_student_no (no)除了阻止重复学号还附带生成一个二级索引外键约束要求class表的id必须是唯一键或主键否则创建直接报错。MySQL 8.0里外键的级联动作写在ON DELETE和ON UPDATE上答案里如果只建约束不写动作默认是NO ACTION删除父表行会被拦下来。拿到这种答案时建议先在本地的两张表上试一下“删父表行”和“插入重复no”亲眼确认约束行为比死记定义有用得多。2.2 增删改与自增列INSERT、UPDATE、DELETE的高频坑第二类题是增删改看着最简单却最容易在“影响行数”上翻车。INSERT的坑集中在没写列清单、往自增列塞NULL、以及主键冲突后的处理UPDATE和DELETE的坑集中在WHERE条件写窄或写宽尤其是DELETE不带WHERE直接清空全表。INSERT INTO student (name, class_id) VALUES (A同学, 1); UPDATE student SET class_id 2 WHERE id 5; DELETE FROM student WHERE id 10;逻辑说明三句话分别对应新增一行、修改一行、删除一行答案简洁到看不出门道真正考的是“你有没有先确认WHERE命中的行”。习惯先写SELECT看一眼再执行UPDATE/DELETE是最稳妥的落地习惯。参数说明INSERT如果省略列清单就必须按表结构顺序给全所有列遇到自增列还得算好位置UPDATE的SET可以同时改多列用逗号分隔DELETE执行后可以用ROW_COUNT()看影响行数也可以先开事务再决定COMMIT或ROLLBACK。实训环境多半是自动提交所以DELETE全表的代价相当大。自增列是这类的隐藏考点。INSERT不指定自增列时MySQL自动分配下一个值显式插入比当前最大值更大的id后下一个自动值会跳到这个值之上。很多人以为删除最大id行再插入就会复用实际上InnoDB的自增计数器不会因为DELETE回退这个现象会在避坑章节再展开。2.3 查询与聚合GROUP BY和HAVING为什么总在同一道题里查询题是实训的重头目录条目最多。最典型的组合是“多表JOIN 条件过滤 分组聚合 结果排序”。统计每个班的平均分只保留及格线以上的班级这类题几乎每个实训包都会出现。SELECT c.class_name, AVG(s.score) AS avg_score FROM class c JOIN student s ON c.id s.class_id WHERE s.score 0 GROUP BY c.class_name HAVING AVG(s.score) 60 ORDER BY avg_score DESC;逻辑说明WHERE在分组前过滤行HAVING在分组后过滤组这是答案能跑通的关键。如果把s.score 0写进HAVING结果可能一样但执行顺序完全不同优化器处理起来也笨重。GROUP BY的列尽量和SELECT里的非聚合列保持一致否则会碰到only_full_group_by报错这点在MySQL 5.7和8.0里都是默认开启的。参数说明AVG忽略NULL但不忽略0统计前要先想清楚业务上0分算不算有效成绩JOIN默认是INNER JOIN只有左右两边都匹配上的行才会出现ORDER BY里可以用SELECT中定义的别名avg_score但WHERE里不能用别名而HAVING可以使用别名这是SQL解析顺序决定的。这类题拿到答案后建议把JOIN换成逗号连接加WHERE条件的老写法跑一遍理解两种写法的等价关系和优化器差异后面排查慢查询会更有底气。2.4 索引与视图答案里看似配角其实决定性能与可复用性索引题和视图题在实训答案里占的比例不高但对照着做的人来说最该认真读。索引题一般给一个查询条件让你决定在哪个列上建索引视图题一般给一个复杂SELECT要求封装成视图供后续查询复用。CREATE INDEX idx_student_class_id ON student(class_id); CREATE VIEW v_class_avg AS SELECT c.class_name, AVG(s.score) AS avg_score FROM class c JOIN student s ON c.id s.class_id GROUP BY c.class_name;逻辑说明CREATE INDEX只告诉MySQL在class_id上建二级索引查询优化器会在合适的时候选用它来加速等值查询CREATE VIEW不存数据只是把SELECT语句存成命名对象每次查询视图都会执行底层SQL所以视图性能取决于底层SQL写得怎么样。参数说明索引名要按项目命名习惯来起否则索引多了之后分不清复合索引要考虑列顺序通常把等值条件的列放前面范围条件的列放后面视图在8.0里可能用MERGE或物化临时表两种方式处理如果视图里用了GROUP BY、DISTINCT、LIMIT等句法通常只能物化成临时表。不要为了建索引而建索引。如果实训答案只要求建索引不要求验证可以执行EXPLAIN对比前后两趟看type列从ALL变成ref还是range再判断这个索引建得有没有意义。2.5 存储过程与触发器DELIMITER与事务提交的边界最后一类题难度陡然上升存储过程、函数、触发器、事务。它们的共同点是答案里几乎都有BEGIN...END块而BEGIN...END块内部允许出现分号这就会和MySQL客户端默认的“分号即语句结束”冲突。DELIMITER // CREATE PROCEDURE sp_class_students(IN cid INT) BEGIN SELECT id, name, score FROM student WHERE class_id cid; END // DELIMITER ;逻辑说明DELIMITER的作用是把客户端的语句分隔符暂时改成//让创建过程时内部的分号不再打断语句等过程建完再改回分号。很多实训答案是从文本编辑器直接复制的在命令行粘贴时如果不带DELIMITER两行创建过程十有八九报语法错。参数说明IN参数表示输入参数存储过程不能用RETURN回传值SET结果集要由调用方接收CALL sp_class_students(1)即可调用。事务题里存储过程默认沿用调用者的autocommit设定如果希望过程内多处写入保持原子性需要显式开启事务正常结束COMMIT、异常ROLLBACK。触发器题有相同的痛点BEFORE INSERT的FOR EACH ROW写过第一条语句后只要遇到多条语句就离不开DELIMITER。这类环境适配问题避坑章节会给出最具体的应对。3. 把答案用出效率定位、先答、对照、改写四步走3.1 用目录定位题号先建自己的知识地图拿到任何带目录的PDF第一动作不该是翻正文而是把目录当索引做一次映射。常见目录格式是按实训分组实训一建表、实训二增删改、实训三查询。题目顺序和实训平台不一定一一对应所以要先花十分钟手工确认章节序号和知识点建一张自己的映射表。目录条目对应知识点我的掌握程度平台题号实训一 建表与约束主键、唯一键、外键不熟练待补充实训三 分组查询JOIN、GROUP BY、HAVING一般待补充建这张表的过程就是把“答案有目录”变成“脑子里有目录”。不需要写太多字只写下题目用了哪些表和列然后继续下一步。3.2 先写自己的版本再翻答案看答案的最优策略是“先答题、后对照”。见过A同学一上来就逐条复制答案等实训平台的题目顺序一变整个人就懵了。更有效的做法是拿到一道题先屏蔽答案自己写下SQL在本地或平台上执行确认输出符合预期然后把答案翻出来跟自己的版本逐行对比。-- 我的版本 SELECT c.class_name, AVG(s.score) avg_score FROM class c, student s WHERE c.id s.class_id GROUP BY c.class_name; -- 答案版本 SELECT c.class_name, AVG(s.score) AS avg_score FROM class c JOIN student s ON c.id s.class_id GROUP BY c.class_name HAVING AVG(s.score) 60;逻辑说明这道题如果只要求每个班平均分我的版本能过但如果题目要求“只显示及格以上的班”两段SQL的非等价部分就是HAVING那一行。逐行对比时会发现自己漏了过滤条件这才是答案给你带来的真实增量。参数说明我的版本里逗号连接和WHERE是SQL-89老写法答案版本是SQL-92标准的JOIN写法。两者语义等价但JOIN写法更直白后面要加LEFT JOIN也不会产生歧义。对比时重点看三件事有没有过滤条件、分组粒度对不对、排序要不要。3.3 把答案改写成自己的命名再跑一遍只盯着答案看记忆留存率很低。我常用的做法是把答案中的表名、列名换成自己本地的命名再执行一遍。比如实训平台叫student表我本地叫stu_info答案里的student不替换就直接报表不存在。这个替换动作本身就是在检验你有没有真正读懂答案。sed -i s/student/stu_info/g; s/class_id/cls_id/g answer.sql mysql -u root -p mydb answer.sql逻辑说明sed做全局替换后将SQL导入本地库表不存在、列名不匹配的问题马上暴露。如果你连答案里出现的列名都认不全这一步会卡住是在逼你回头查表结构。参数说明sed的全局替换要注意误伤student可能误匹配student_detail这类长表名稳妥做法是先人工过一遍替换清单再执行把每条g替换后可能波及的对象整理出来mysql导入时如果答案里有USE语句会切到答案指定的库建议先把那行删掉再导入避免覆盖自己不想动的库。提示替换完之后运行一条SELECT * FROM stu_info LIMIT 5确认表结构仍然完整再继续做后面的验证。3.4 评测系统反馈差异别急着怪答案很多时候你把答案原封不动贴进评测系统仍然判错原因大概率在环境。比如平台用严格模式本地默认宽松又比如答案假设表结构是utf8平台是utf8mb4还有答案依赖初始数据生成顺序一旦平台重置数据结果就对不上。把“本地跑通”和“平台通过”当成两件事分别记录报错信息和结果差异才有排查依据。具体的版本、字符集、时区问题下一章给出清单。4. 头歌MySQL实训答案跑不通的避坑清单版本、字符集与严格模式目录解决的是找得到的问题跑不跑得通是环境的问题。以下五条踩坑记录来自实际训练里反复出现的现象每条按“现象→原因→解决”展开。4.1 分组查询报错only_full_group_by5.7与8.0的默认差异现象SELECT name, COUNT(*) FROM student GROUP BY class_id; 在实训平台或MySQL 8.0上直接报错但换到一台老配置机器上却能跑通。原因only_full_group_by在MySQL 5.7和8.0里默认开启要求SELECT列表里出现的非聚合列必须出现在GROUP BY列表里。答案里如果有这种写法说明它默认了一个宽松环境一旦交给严格模式的评测环境立刻翻车。解决把非聚合列改成聚合写法或者用ANY_VALUE()明确表达“我只要任意一个样例值”SELECT class_id, ANY_VALUE(name) AS sample_name, COUNT(*) AS cnt FROM student GROUP BY class_id;逻辑说明ANY_VALUE(name)表面上给了name一个“聚合”的身份本质上就是告诉优化器这一列我不关心具体哪一行随便取一个。它能通过only_full_group_by的检查但如果题目要求的是“每个班成绩最高的学生”就不能指望这种写法这种业务规则要用窗口函数或自连接实现不能靠ANY_VALUE的随机性碰运气。参数说明本地调试时临时移除only_full_group_by是可行的SET SESSION sql_mode (SELECT REPLACE(sql_mode, ONLY_FULL_GROUP_BY, ));但实训平台没法容忍这种修改所以不要把它写进最终提交的答案改写SQL本身才是最稳的路。4.2 中文乱码或插入失败字符集没对齐现象向表里插入中文执行成功但查出来是问号或者直接报“Incorrect string value”错误。原因MySQL的字符集有服务端、数据库、表、连接四个层面。实训答案建表时如果没指定CHARSET会继承库级设置客户端写入时如果连接字符集不是utf8mb4中文就会在传输层被翻译成错误字节。解决执行答案的会话里先统一连接字符集命令行连库时直接指定mysql -uroot -p --default-character-setutf8mb4 training_db逻辑说明第一步先让连接层是utf8mb4如果插入仍然报错再检查建表语句里是否用CHARSETutf8mb4显式声明。答案自带的建表语句很多出自旧课程只有CHARSETutf8在MySQL 8.0里虽然可以运行但强烈建议统一为utf8mb4。参数说明SET NAMES utf8mb4等价于同时设置character_set_client、character_set_connection和character_set_results三个变量如果历史遗留的表已经建成latin1还要先执行ALTER TABLE student CONVERT TO CHARACTER SET utf8mb4这个操作会重写整表执行前先确认数据有备份。4.3 存储过程或触发器创建失败DELIMITER引起的边界错乱现象把含BEGIN...END的存储过程答案粘贴到命令行总是提示“ERROR 1064 near BEGIN”但答案看起来完整。原因客户端默认把分号当语句结束符CREATE PROCEDURE内部的多个分号会把语句提前切碎服务端收到的是一段残缺SQL。解决执行前用DELIMITER改分隔符创建完再改回来DELIMITER // CREATE PROCEDURE sp_class_students(IN cid INT) BEGIN SELECT id, name FROM student WHERE class_id cid; END // DELIMITER ;逻辑说明DELIMITER本身不是SQL语法它是mysql客户端内置的识别技巧。因此通过JDBC、Python的MySQL驱动或者部分IDE执行时不一定要写但实训平台的命令行窗口里DELIMITER是绕不过去的一步。参数说明DELIMITER后面的分隔符可以自定义常见有//和$$。DELIMITER与分隔符之间要有空格写成DELIMITER //而不是DELIMITER//创建完立刻改回分号不然后续普通语句都以//结束会制造更多困惑。4.4 执行成功但结果差8小时或日期格式化不对现象答案里用到NOW()或DATE_FORMAT的题目执行不报错但得到的时间比预期晚8小时或者日期格式输出和预期不一样。原因MySQL的时区设置不一致。NOW()返回会话时区里的当前时间如果会话时区是UTC本地是UTC8结果就差8小时DATE_FORMAT的格式符拼错也会让结果看起来诡异。解决执行前统一设定会话时区SET time_zone 08:00; SELECT NOW(); SELECT DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s);逻辑说明把time_zone显式钉在08:00是让本地结果和评测环境可以对比的最简单方式。如果答案里日期写的是某个固定值那还要判断是不是MySQL 8.0对日期零值和默认值的处理变化。参数说明MySQL 8.0默认time_zone是SYSTEM跟随操作系统SET SESSION只影响当前连接UNIX_TIMESTAMP()也受会话时区影响。遇到日期结果不一致先查time_zone和sql_mode两个变量而不是怀疑SQL写错。4.5 自增主键不连续或插入指定id后顺序被打乱现象插入失败一次下一次成功行的id变成3DELETE掉最大id后再插入自增值并没有回退最终SELECT ... ORDER BY id的结果顺序和答案对不上。原因InnoDB的自增计数器一旦分配就不会回收事务回滚或DELETE行都不会让它回退MySQL 8.0里这个计数器持久化保存重启也不能重置。解决先确认题目是否校验自增值。如果只是希望接下来从某个数字开始用ALTER TABLE设置ALTER TABLE student AUTO_INCREMENT 100;如果答案要求整张表回到初始状态用TRUNCATE清空并重建自增起点随之一同重置TRUNCATE TABLE student;逻辑说明ALTER TABLE设置自增起点只能往后调不能往前调TRUNCATE会重建表存储结构所以能彻底重置。但外键引用这张表时TRUNCATE会被拒绝要先处理引用表再做全表清空。把自增列当作黑匣子不要用INSERT失败来猜测下一个id才是站在工程视角对待答案的态度。参数说明TRUNCATE在MySQL里会隐式提交事务执行后不能回滚务必确认操作对象再执行最好先用SELECT确认当前最大id与总行数再决定要不要清。5. 超越答案用EXPLAIN判断每条SQL值不值得借鉴目录是文章的索引索引是数据库的目录。带着这个视角去看答案会发现“能跑通”和“值得抄”是两回事。下面用执行计划这条路径把参考答案的价值再榨一层。5.1 执行计划里的type、key、rows三列怎么看先给一段常见实训查询EXPLAIN SELECT s.name, c.class_name FROM student s LEFT JOIN class c ON s.class_id c.id WHERE s.score 60;执行计划会返回一张表其中最重要的三列是type、key、rows。type表示访问表的方式按从好到差大致是system const eq_ref ref range index ALLtype是ALL说明全表扫描ref或range则说明用到了索引。key列显示实际使用的索引名没显示就表示索引没被选中。rows是优化器估算要扫描的行数不看绝对值看它和表总行数的比例。逻辑说明当rows占全表的大部分优化器可能放弃索引改走全表扫描因为随机访问的代价反而更大当key为NULL而type为ALL第一件要做的事就是从WHERE条件和查询字段里寻找能建立合适索引的列。答案没给索引不代表不需要表结构设计才是决定查询上限的根本。参数说明MySQL 8.0的EXPLAIN支持FORMATJSON能看到更细的成本估算如果对执行计划有疑问先看filtered列它表示存储引擎返回的行里过滤条件的符合比例比例越低查询中“无用行”越多。5.2 回表与覆盖索引为什么答案里的SELECT *不要照抄实训答案为了简化输出常在SELECT里写星号。学习环境没问题还原到业务场景问题就来了二级索引只存索引列和主键值回表是指根据主键再去聚簇索引取其他列的过程读星号通常就会多一段回表IO。CREATE INDEX idx_class_id ON student(class_id); EXPLAIN SELECT * FROM student WHERE class_id 1; EXPLAIN SELECT class_id FROM student WHERE class_id 1;逻辑说明第一条因为要取name、score等列执行计划里Extra列不会有Using index想做的是回表第二条只需要class_id索引本身已经覆盖Extra会显示Using index。覆盖索引是避免回表的最直接手段。参数说明覆盖索引不是越多越好每一个索引都会拖慢写入。对于实训答案里考察索引设计的题判断标准不是有没有extra索引而是它减少了多少实际查询的回表次数。单表的数据量、写入压力都得看规模再定不要盲目复合。5.3 同一道题的三种写法子查询、JOIN、窗口函数怎么选带目录的答案多数只给一种写法但同一道“找出每个班成绩最高的人”可以写成子查询、JOIN和窗口函数三种风格执行计划与语义差别都不小SELECT s.name, s.score FROM student s WHERE s.score (SELECT MAX(score) FROM student s2 WHERE s2.class_id s.class_id); SELECT s.name, s.score FROM student s JOIN ( SELECT class_id, MAX(score) AS max_score FROM student GROUP BY class_id ) t ON s.class_id t.class_id AND s.score t.max_score; SELECT name, score, RANK() OVER (PARTITION BY class_id ORDER BY score DESC) AS rk FROM student;逻辑说明第一种子查询在MySQL 8.0里优化器常常改写成JOIN但写错的话可能造成逐行执行变慢第二种先聚合再JOIN数据量大时通常是最稳的第三种窗口函数在8.0才有语义最清晰。答案里如果没出现窗口函数不代表不能写可能只是出题时间早于8.0流行。参数说明RANK()遇到同分会把名次变成1、1、3DENSE_RANK()是1、1、2ROW_NUMBER()则对同分行随机排序三者不可混用。实训答案如果需要“取每个班的第一名”先确认题目是否允许并列再决定用哪个窗口函数。6. 一页纸自测表从答案有目录到脑子有目录最后一个技巧把这份带目录PDF的“目录”和你的执行计划结合起来做一张一页纸自测表。纸不用多每次只放自己本周要攻克的5道题。题号知识点我能一次性写对吗答案差异点验证EXPLAIN结论具体做法是先在“我能一次性写对吗”那格写一个大胆的判断不看答案写写完回想锁上答案逐个对照“差异点”如果差异为零或很小这种掌握才真正体现水平最后把表里各条SQL的EXPLAIN结果写进“验证EXPLAIN结论”让每一道题至少有一个基于索引、回表或扫描的判断。每个差异点都要自问一句答案为什么用这种方式表达它是为了兼容某版本还是在语法层面更清晰或者只是作者的用语习惯只要能用自己话把这句回答清楚答案就已经转化成自己的知识回答不了就别骗自己。背诵是起点复盘后的表达才是终点。我在这类实训的复盘上有过一段弯路原因是拿刷题数衡量成果停留在“复制答案、运行通过、标记完成”这三步没有自测、没有差异记录结果换了数据集或表名之后照样不会用。后来改成这种一页纸的方法每次只记5道题反而比整本笔记更有效。如果你手头正拿着这份“头歌MySQL数据库实训答案有目录.pdf”先翻目录挑三道不那么简单的题按上面的四步走重写一遍再用EXPLAIN验证你会发现答案本体只是入口真正的收益藏在从目录到执行计划的那条路径上。希望帮到你。本文还有配套的精品资源点击获取