MySQL实训答案知识地图：从建表到存储过程的核心考点与易错点

简介这份PDF面向正在学习或备考MySQL数据库实训的学生与初学者针对头歌平台实训题目提供可直接对照的参考答案帮助解决建库建表、约束设置与表结构修改等练习中的卡点。资源为单个PDF文件约433KB正文按实训关卡组织并配有目录便于按知识点快速定位。内容覆盖数据库定义与操作、主键与外键及唯一约束的应用、查看表结构与修改表名、修改字段名与数据类型、添加删除字段、调整字段排列位置、删除外键约束以及插入与更新数据等典型关卡每关均给出完整SQL语句与执行环境提示。已有18400人学习下载适合课堂实训跟练、作业自查与考前复习时逐条比对也可作为教师核对参考答案的辅助材料。1. 从一份实训答案反推 MySQL 知识地图很多人拿到一份 MySQL 实训答案第一反应是照着抄、跑通就完事。但真正复习过一轮的人会发现这类实训答案其实是一张被拆成关卡的知识地图从建库建表、约束设计到单表查询、连接查询、子查询再到视图、索引、存储过程几乎把 MySQL 入门到进阶的主干全串了一遍。这份资料按「数据定义与操作 → 表的基本操作 → 单表查询 → 连接与子查询 → 复杂查询 → 聚合与函数 → 视图索引 → 存储过程」组织带目录、按关卡给代码适合正在系统学 MySQL、或者准备数据库实训考核的人拿来对照复习。下面按模块把重点、难点和易错点梳理清楚让你从「抄答案」变成「看得懂为什么这么写」。2. 数据定义与约束建库建表的地基怎么打数据定义是整个 MySQL 实训的第一段也是后面所有查询的前提。这一块看起来只是CREATE DATABASE和CREATE TABLE但真正拉开差距的是约束设计——主键、外键、唯一、默认值、自增每一个都对应着「数据能不能乱写」的规则。实训答案里第 1 关到第 5 关正好把这条线走完先建库再建表然后逐层加约束。2.1 建库建表与常用数据类型建库的标准动作是先连上服务再CREATE DATABASE然后USE切进去。实训里反复出现的连接命令是mysql -uroot -p123123 -h127.0.0.1这条命令拆开看就是用户名、密码、主机地址三要素本地练习时主机可以省略但写全了更接近真实环境。-- 连接后先建库再切换避免建表建到错误的库里 CREATE DATABASE MyDb; USE MyDb; -- 建表时把类型、长度一次写清楚 CREATE TABLE t_emp ( id INT, name VARCHAR(32), deptId INT, salary FLOAT );这里最容易忽略的是「先 USE 再建表」。很多人建完库直接CREATE TABLE结果表落到了默认库里后面DESCRIBE找不到表就开始怀疑人生。类型选择上INT用于整数、VARCHAR(n)用于变长字符串、FLOAT用于小数实训里的表结构基本就围绕这几类展开先把这套组合用熟后面加约束才不会乱。2.2 主键、外键、唯一与默认约束约束是数据定义部分的核心考点实训答案里用四张表把常见约束过了一遍。主键分单列主键和复合主键单列主键直接写在字段后复合主键要单独写一行PRIMARY KEY(name, phone)。外键则要先用CONSTRAINT起名再FOREIGN KEY ... REFERENCES指向父表。约束类型写法要点作用主键 PRIMARY KEY单列写在字段后复合单独一行唯一标识一行非空且唯一外键 FOREIGN KEYCONSTRAINT 名 FOREIGN KEY(列) REFERENCES 父表(列)保证引用完整性唯一 UNIQUE字段后加UNIQUE列值不重复可为空默认 DEFAULT字段后加DEFAULT 值未赋值时填默认值自增 AUTO_INCREMENT配合主键使用自动生成递增整数-- 复合主键两个字段一起唯一 CREATE TABLE t_user2 ( name VARCHAR(32), phone VARCHAR(11), email VARCHAR(32), PRIMARY KEY(name, phone) ); -- 外键先给约束起名方便以后删除 CREATE TABLE t_student ( id INT PRIMARY KEY, name VARCHAR(22), classId INT, CONSTRAINT fk_stu_class1 FOREIGN KEY(classId) REFERENCES t_class(id) ); -- 自增 非空 唯一 默认值一起上 CREATE TABLE t_user ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(32) NOT NULL UNIQUE, sex VARCHAR(4) DEFAULT 男 ) DEFAULT CHARSETutf8;梳理下来会发现约束的顺序有讲究主键通常最先定因为外键要引用父表主键自增只能配主键或唯一键默认值和字符集放在表级更稳妥。实训里DEFAULT CHARSETutf8写在建表语句末尾就是为了避免中文乱码这个老问题。2.3 表结构的查看与修改建完表不等于结束实训第二段专门练ALTER TABLE。改表名用RENAME改字段名用CHANGE要同时写旧名新名和类型只改类型用MODIFY加字段用ADD ... AFTER删字段用DROP调位置用FIRST或AFTER。-- 改表名 ALTER TABLE tb_emp RENAME jd_emp; -- 改字段名CHANGE 必须带新类型 ALTER TABLE tb_emp CHANGE Id prod_id INT(11); -- 只改类型 ALTER TABLE tb_emp MODIFY Name VARCHAR(30); -- 加字段并指定位置 ALTER TABLE tb_emp ADD Country VARCHAR(20) AFTER Name; -- 删字段 ALTER TABLE tb_emp DROP Salary; -- 调字段到最前 ALTER TABLE tb_emp MODIFY Name VARCHAR(25) FIRST;CHANGE和MODIFY是这一节最容易混的一对CHANGE能改名也能改类型MODIFY只能改类型不能改名。另外删除外键要用DROP FOREIGN KEY 约束名不是删字段删完可以用SHOW CREATE TABLE ... \G验证。提示ALTER TABLE改结构前先DESCRIBE看一眼现状改完再DESCRIBE确认两步都别省。3. 单表查询WHERE、排序、分组与分页的主线单表查询是实训里关卡最多的一段从基本SELECT一路练到LIKE、IS NULL、DISTINCT、ORDER BY、GROUP BY、LIMIT。这条主线其实就是 SQL 的执行顺序先FROM定表再WHERE过滤然后GROUP BY分组、HAVING过滤组接着ORDER BY排序最后LIMIT截取。把顺序记牢写查询时就不会把条件放错地方。3.1 条件过滤IN、BETWEEN、LIKE 与空值条件过滤的关键是选对运算符。IN用于枚举匹配BETWEEN AND用于闭区间LIKE配合%做模糊匹配空值必须用IS NULL而不能用 NULL。-- IN排除某个 id SELECT Name, Salary FROM tb_emp WHERE Id NOT IN (1); -- BETWEEN AND闭区间含两端 SELECT Name, Salary FROM tb_emp WHERE Salary BETWEEN 3000 AND 5000; -- LIKEC 开头的名字 SELECT Name, Salary FROM tb_emp WHERE Name LIKE C%; -- 空值判断不能用 NULL SELECT * FROM tb_emp WHERE DeptId IS NULL; -- 去重 SELECT DISTINCT Name FROM tb_emp;这里有个血泪经验 NULL永远返回空结果因为 NULL 不等于任何值包括它自己。实训里专门设了一关练IS NULL就是为了把这个坑提前踩掉。LIKE的%代表任意长度_代表单个字符写C%是 C 开头写%C%是包含 C别混。3.2 排序、分组与 LIMIT 分页排序用ORDER BY 列 DESC/ASC分组用GROUP BY分页用LIMIT 偏移, 条数。实训里「查询第 2 名到第 5 名」就是LIMIT 1,4——偏移从 0 开始所以第 2 名偏移是 1。-- 按成绩降序 SELECT * FROM tb_score WHERE class_id 1 ORDER BY score DESC; -- 按班级分组 SELECT * FROM tb_class GROUP BY class_id; -- 第 2 名到第 5 名偏移 1取 4 条 SELECT * FROM tb_score ORDER BY score DESC LIMIT 1, 4;LIMIT的偏移量是最容易算错的地方LIMIT 0,5是前 5 条LIMIT 5,5是第 6 到第 10 条。分页查询在数据量大时还会遇到性能问题实训后面用「子查询 主键比较」来优化深分页思路是先查出边界主键再取范围避免大偏移扫描。3.3 聚合函数与 HAVING 的组合聚合函数COUNT、SUM、AVG、MAX、MIN是分组统计的标配配合GROUP BY出分组结果再用HAVING过滤分组后的条件。WHERE过滤行HAVING过滤组这个分工必须分清。-- 按年级和性别统计人数 SELECT gradeId, sex, COUNT(*) FROM student WHERE gradeId IN (2,3,4) GROUP BY gradeId, sex; -- 至少两门 90 分以上的学生 SELECT sno, COUNT(*) FROM tb_grade WHERE score 90 GROUP BY sno HAVING COUNT(pno) 2;HAVING里可以用聚合函数WHERE里不行这是两者最本质的区别。实训里「平均分大于 90 且语文 95 以上」那题就是先用子查询筛出语文 95 以上的学号再在外层GROUP BY后用HAVING卡平均分两层条件各归其位。4. 连接与子查询多表取数的两条路单表查询练熟后实训进入多表阶段。连接查询解决「表与表横向拼」的问题子查询解决「用一张表的结果去筛另一张表」的问题。这两条路经常混着用复杂查询关卡里几乎每道题都是连接加子查询的组合。4.1 内连接、外连接与复合条件连接内连接只保留两表都匹配的行外连接保留一侧全部行。左外连接保留左表全部右外连接保留右表全部。实训里用JOIN ... ON和LEFT/RIGHT JOIN两种写法都练过。-- 内连接学生和班级 SELECT s.name AS studentName, c.name AS className FROM tb_student s JOIN tb_class c ON c.id s.class_id; -- 左外连接保留所有学生 SELECT s.name AS studentName, c.name AS className FROM tb_student s LEFT JOIN tb_class c ON c.id s.class_id; -- 复合条件连接分数 90 以上 SELECT s1.name AS studentName, score, s2.name AS className FROM tb_student s1, tb_class s2 WHERE s1.class_id s2.id AND s1.score 90 ORDER BY score DESC;外连接的方向是最容易翻车的地方A LEFT JOIN B保留 A 的全部行A RIGHT JOIN B保留 B 的全部行。实训答案里有一处用tb_class RIGHT JOIN tb_student来实现「所有学生」本质等价于tb_student LEFT JOIN tb_class方向反了结果就少人。写外连接前先问自己「要保留哪张表的全部行」答案就清楚了。4.2 子查询比较、ANY/ALL 与 IN子查询是嵌在另一条语句里的SELECT按返回结果分标量子查询、列子查询、行子查询。实训里重点练了比较运算符子查询、ANY/ALL和IN。-- 比较运算符大于平均年龄 SELECT name, age FROM tb_emp WHERE age (SELECT AVG(age) FROM tb_emp); -- ANY大于 java 岗位最低工资的任意一个 SELECT position, salary FROM tb_salary WHERE salary ANY (SELECT MIN(salary) FROM tb_salary WHERE position java); -- ALL大于 java 岗位所有工资 SELECT position, salary FROM tb_salary WHERE salary ALL (SELECT salary FROM tb_salary WHERE position java); -- IN岗位是 java SELECT position, salary FROM tb_salary WHERE position IN (java);ANY和ALL的区别记一句话 ANY等价于大于最小值 ALL等价于大于最大值。实训答案里有一处注释写「使用 ALL 关键字」但实际写的是ANY这种答案和注释对不上的情况在整理资料里并不少见复习时以语义为准别被注释带偏。4.3 复杂查询里的排名、换位与统计复杂查询关卡把前面所有技能揉在一起交换工资用CASE WHEN换座位用IF加奇偶判断分数排名用相关子查询数「比自己高的有几个」体育馆人流量用三表自连接找连续三天。-- 交换性别CASE 表达式 UPDATE tb_Salary SET sex CASE sex WHEN m THEN f ELSE m END; -- 换座位偶数减一奇数加一末位不动 SELECT IF(Id % 2 0, Id - 1, IF(Id 5, Id, Id 1)) AS id, name FROM tb_Seat ORDER BY Id; -- 分数排名比自己高的不同分数个数 1 SELECT Score, (SELECT COUNT(DISTINCT score) FROM score WHERE score s.score) AS Rank FROM score s ORDER BY Score DESC;排名题有两种写法COUNT(DISTINCT score)处理并列名次COUNT(*) 1处理连续名次题目要哪种看它是否允许并列。体育馆那题用三表自连接加OR条件判断连续 id本质是在没有窗口函数的 MySQL 版本里模拟「连续区间」检测思路值得记下来。5. 视图、索引与存储过程进阶模块的取舍实训后半段进入开发技巧视图、索引、分页优化、存储过程。这几块不再是单纯写查询而是涉及「怎么组织数据访问」的设计问题。视图简化复杂查询索引加速检索存储过程封装逻辑三者各有适用场景也各有代价。5.1 视图把复杂查询存成一张虚拟表视图是基于查询结果的虚拟表创建用CREATE VIEW 名 AS 查询。单表视图和多表视图的区别只在查询本身视图不存数据每次查询都重新执行底层语句。-- 单表视图数学加语文总分 CREATE VIEW stu_view AS SELECT math, chinese, math chinese FROM student; -- 多表视图学生和班级信息 CREATE VIEW stu_classes AS SELECT student.stu_id, student.name, stu_info.classes FROM student, stu_info WHERE student.stu_id stu_info.stu_id;视图的价值在于把多表连接和计算逻辑封装起来调用方只写SELECT * FROM stu_view。但视图也有代价底层查询复杂时每次访问都要重算且部分视图不可更新。复习时记住「视图是逻辑封装不是性能优化手段」就够了。5.2 索引单列、唯一与组合索引索引是加速查询的核心手段。主键自带索引唯一索引保证列值唯一普通索引只加速不约束组合索引按最左前缀匹配。-- 建表时主键索引 CREATE TABLE student ( id INT(11) NOT NULL AUTO_INCREMENT, name VARCHAR(20) NOT NULL, score INT(10), PRIMARY KEY (id) ); -- 唯一索引 CREATE UNIQUE INDEX name_index ON student(name); -- 普通索引 CREATE INDEX score_index ON student(score); -- 组合索引 ALTER TABLE person ADD INDEX name_city_score (name, age, address); -- 查看索引 SHOW INDEX FROM student;组合索引的关键是最左前缀(name, age, address)能命中name、nameage、nameageaddress但单独查age用不上。实训里专门练了组合索引的创建复习时要把「最左前缀」和「索引失效场景」一起记比如对索引列做函数运算、用LIKE %x前置通配都会让索引白建。5.3 存储过程IN/OUT 参数与流程控制存储过程把一段 SQL 逻辑存到数据库里用CREATE PROCEDURE定义CALL调用。参数分IN传入、OUT传出、INOUT双向。流程控制有IF...ELSEIF...ELSE、CASE、循环。DROP PROCEDURE IF EXISTS GetCustomerLevel; DELIMITER $$ CREATE PROCEDURE GetCustomerLevel( IN p_customNumber INT(11), OUT p_customerLevel VARCHAR(10) ) BEGIN DECLARE levels INT; SELECT creditlimit INTO levels FROM customers WHERE customerNumber p_customNumber; IF levels 5000 THEN SET p_customerLevel SILVER; ELSEIF levels 10000 THEN SET p_customerLevel GOLD; ELSE SET p_customerLevel PLATINUM; END IF; END $$ DELIMITER ;存储过程最容易错的是分隔符过程体里有分号不先用DELIMITER $$改掉默认分隔符MySQL 会在第一个分号处就截断。另外DECLARE必须写在BEGIN之后、其他语句之前顺序错了直接报语法错误。6. 易错点排查这几处最容易翻车把整份实训答案过一遍会发现错误高度集中在几个地方。下面按「易漏点 → 为什么漏 → 怎么补」列出来复习时对着自查。易漏点一 NULL当空值判断。为什么漏直觉上「等于空」很自然但 SQL 里 NULL 表示未知任何与 NULL 的比较都返回未知。怎么补空值一律用IS NULL/IS NOT NULL写完条件扫一眼有没有 NULL。易漏点二CHANGE和MODIFY混用。为什么漏两者都能改字段名字又像。怎么补记「CHANGE 要写两遍字段名旧新MODIFY 只写一遍」改名前先DESCRIBE确认旧名。易漏点三外连接方向搞反。为什么漏LEFT/RIGHT保留哪边全靠记一急就反。怎么补写之前先明确「要保留哪张表全部行」那张表放LEFT JOIN左边或者用RIGHT JOIN时把它放右边。易漏点四WHERE和HAVING放错聚合条件。为什么漏两个都是过滤位置又近。怎么补聚合函数条件进HAVING普通列条件进WHEREHAVING永远跟在GROUP BY后面。易漏点五存储过程忘了改分隔符。为什么漏过程体里全是分号默认分隔符会提前截断。怎么补CREATE PROCEDURE前先DELIMITER $$结束后DELIMITER ;改回来养成固定动作。注意实训答案里个别关卡的注释和实际代码不一致比如注释写 ALL、代码写 ANY复习时以语句语义为准别照抄注释。7. 复习清单从抄答案到能默写把这份资料当复习材料最有效的用法不是从头抄到尾而是按「能默写」的标准过一遍。下面这张清单按模块列了必须能独立写出的语句每条都对应实训里的关卡写完对照答案查漏。模块必须能默写自查标准建库建表CREATE DATABASE、CREATE TABLE带类型不看答案写出含 4 字段的表约束主键、复合主键、外键、唯一、默认、自增说清每种约束写在哪一行改表RENAME、CHANGE、MODIFY、ADD、DROP区分 CHANGE 与 MODIFY单表查询WHERE、IN、BETWEEN、LIKE、IS NULL空值不用 NULL排序分组ORDER BY、GROUP BY、HAVING、LIMITLIMIT 偏移算对连接JOIN ON、LEFT/RIGHT JOIN外连接方向不反子查询比较、ANY/ALL、IN说清 ANY 与 ALL 差别聚合COUNT、SUM、AVG、MAX、MIN配合 GROUP BY 使用视图索引CREATE VIEW、CREATE INDEX、组合索引记住最左前缀存储过程DELIMITER、IN/OUT、IF分隔符先改后还原具体操作上我自己的习惯是分三轮第一轮按关卡顺序把答案跑一遍确认环境能通第二轮遮住答案只看题目描述默写写不出来的标记第三轮专攻标记题把易错点那五条对着改一遍。默写时特别注意LIMIT偏移、外连接方向、HAVING位置这三处它们是最容易在考场上手滑的地方。从那以后我每次复习数据库都强制自己先默写再对答案而不是先看答案找感觉——先看答案会产生「我会了」的错觉默写才会暴露真正的漏洞。这份带目录的实训答案正好适合当默写题库按模块过一遍比零散刷题扎实得多。希望帮到你。本文还有配套的精品资源点击获取