MySQL实训通关指南：核心考点与易错点解析

简介这份PDF面向正在学习或备考MySQL数据库实训的学生与初学者针对头歌平台实训任务中建库建表、约束设置、表结构修改等常见卡点提供可直接对照的参考答案帮助读者快速核对操作结果、理清语法思路。资源包共1个PDF文件约433KB文件内附有清晰目录按实训关卡组织内容涵盖创建数据库与数据表、主键与外键约束、唯一约束与默认值、查看表结构、修改表名与字段名、增删字段及调整字段位置、删除外键约束、插入与更新数据等知识点每关均给出完整SQL语句部分命令保留原始执行环境写法便于手动粘贴验证。目前已有18400人学习下载适合课堂实训跟练、课后复盘与考前查漏补缺也可作为教师核对参考答案的辅助材料。1. 从一道“建表题”看这场实训到底考什么很多人第一次打开这套 MySQL 实训看到第一关只是create database MyDb;心里会松一口气觉得不过如此。真正让人翻车的是后面外键约束里多写一个括号、ALTER TABLE改字段名时忘了写类型、分页查询把LIMIT 1,4当成“第 1 到第 4 条”。这套实训的判分逻辑很直接——语句能跑通、结果集对得上、结构改到位就给分差一个关键字整关归零没有后悔药。它考的不是背概念而是把“数据定义、表结构维护、单表查询、连接与子查询、聚合与分组、视图索引存储过程”这一整条链路写成可执行 SQL。适合正在刷这套实训、被关卡卡住或者准备数据库相关笔试面试的人。拿分的关键只有两条记住每类操作的固定句式以及知道判题系统最在意哪个细节。下面按考点模块拆开讲每块都配真题和答题思路。2. 数据定义与约束建库建表这几分最好拿也最容易丢数据定义是整个实训的地基关卡集中在“初识 MySQL”这一组。题目形式高度固定给你库名、表名、字段清单和约束要求让你写CREATE DATABASE、CREATE TABLE再补主键、外键、唯一、默认值、自增。这类题没有算法难度失分几乎都出在语法细节和约束写法上。2.1 建库建表的标准句式与字段类型选择第 1 关到第 2 关的套路是先create database 库名;再use 库名;然后create table 表名(...)。字段类型要按题目给的语义选编号用int姓名用varchar(32)工资用float或decimal性别用varchar(4)。字符集如果题目要求中文不乱码就在建表末尾加DEFAULT CHARSETutf8。-- 建库并切换避免在错误的库里建表 create database TestDb; use TestDb; -- 按题目字段清单建表类型和长度照抄要求 create table t_emp ( id int, name varchar(32), deptId int, salary float );这段代码的关键不在难度而在顺序use必须在create table之前否则表会建到默认库里判题系统查不到。字段类型写错比如把varchar(32)写成varchar在部分关卡也会判错因为系统会比对表结构。答题时先把题目里的字段名、类型、长度逐字抄下来再补约束比凭记忆写稳得多。2.2 主键、外键、唯一与默认值约束题的四种固定写法约束是这一模块的高频失分区。主键分单列和复合两种单列直接userId INT PRIMARY KEY复合要写成表级约束PRIMARY KEY(name, phone)。外键必须用CONSTRAINT 约束名 FOREIGN KEY(子表字段) REFERENCES 父表(主表字段)约束名不能省因为后面“删除外键约束”那一关要靠这个名字。唯一约束写成username VARCHAR(32) NOT NULL UNIQUE默认值写成sex VARCHAR(4) DEFAULT 男自增写成id INT PRIMARY KEY AUTO_INCREMENT。-- 复合主键两个字段一起唯一 create table t_user2( name VARCHAR(32), phone VARCHAR(11), email VARCHAR(32), PRIMARY KEY(name, phone) ); -- 外键子表 classId 引用父表 t_class 的 id CREATE TABLE t_student( id INT PRIMARY KEY, name VARCHAR(22), classId int, CONSTRAINT fk_stu_class1 FOREIGN KEY(classId) REFERENCES t_class(id) );外键这段最容易错在两处一是父表必须先建否则引用不到二是CONSTRAINT后面的名字要记住因为“删除表的外键约束”那关要求ALTER TABLE tb_emp DROP FOREIGN KEY emp_dept;名字对不上就删不掉。面试里问“主键和唯一约束的区别”标准答法是主键不允许空且一张表只能一个唯一约束允许一个空值且可以多个。这类概念题通常这么答先给定义再给一句使用场景。2.3 表结构维护ALTER TABLE 的改名、改类型、加删字段“数据库和表的基本操作”两组关卡全压在ALTER TABLE上。改表名用RENAME改字段名用CHANGE必须同时写旧名、新名和新类型只改类型用MODIFY加字段用ADD ... AFTER 某字段删字段用DROP调整位置用MODIFY ... FIRST或MODIFY ... AFTER 某字段。USE Company; -- 改表名 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); -- 在 Name 后面加字段删掉 Salary 字段 ALTER TABLE tb_emp ADD Country varchar(20) AFTER Name; ALTER TABLE tb_emp DROP Salary; -- 把 Name 挪到第一列把 DeptId 挪到 Salary 后面 ALTER TABLE tb_emp MODIFY Name varchar(25) FIRST; ALTER TABLE tb_emp MODIFY DeptId int(11) AFTER Salary;CHANGE和MODIFY的区别是必考点CHANGE能改名也能改类型MODIFY只能改类型不能改名。很多人写CHANGE Id prod_id漏掉int(11)直接报语法错。答题时记住一句口诀改名带类型改型用 MODIFY加字段想好位置删字段别手抖。3. 单表查询WHERE 条件写不对后面全白搭单表查询占了实训很大比重从基本查询、IN、BETWEEN AND、LIKE、空值判断、去重到排序、分组、LIMIT几乎每个语法点一关。这一块的判分看结果集字段顺序、别名、排序方向错一个都可能不过。高频考点集中在条件组合和空值处理上。3.1 条件查询IN、BETWEEN、LIKE 与空值的边界IN用于枚举匹配NOT IN是排除BETWEEN 3000 AND 5000是闭区间包含两端LIKE C%匹配以 C 开头%代表任意长度_代表一个字符。空值判断必须用IS NULL不能用 NULL这是 SQL 三值逻辑的经典坑。USE Company; -- 排除 Id 为 1 的记录 SELECT Name,Salary FROM tb_emp WHERE Id NOT IN (1); -- 闭区间3000 和 5000 都算 SELECT Name,Salary FROM tb_emp WHERE Salary BETWEEN 3000 AND 5000; -- 以 C 开头的姓名 SELECT Name,Salary FROM tb_emp WHERE Name LIKE C%; -- 空值只能用 IS NULL SELECT * FROM tb_emp WHERE DeptId IS NULL; -- 去重 SELECT DISTINCT Name FROM tb_emp;面试官最想听的是你对 NULL为什么查不出东西的解释NULL参与比较结果是未知WHERE只保留结果为真的行所以必须用IS NULL。这类题通常这么答先指出三值逻辑再给正确写法最后补一句“聚合函数会忽略 NULLCOUNT(*)不忽略”。3.2 排序、分组与 LIMIT第 2 名到第 5 名怎么切排序用ORDER BY 字段 DESC/ASC分组用GROUP BY限制条数用LIMIT 偏移量, 条数。最容易错的是LIMIT的偏移从 0 开始查“第 2 名到第 5 名”要先按成绩降序再写LIMIT 1,4因为第 1 名偏移是 0第 2 名偏移是 1取 4 条正好到第 5 名。USE School; -- 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;GROUP BY的失分点在于SELECT里出现未分组的非聚合字段严格模式下会报错。答题时先想清楚“按什么分组、每组要什么聚合值”再写字段。LIMIT题建议在草稿上标一下序号第 1 条偏移 0第 n 条偏移 n-1套公式就不会错。4. 连接与子查询多表题的分水岭在这里连接查询和子查询是实训里拉开差距的部分。内连接、左右外连接、复合条件连接、比较运算符子查询、ANY/ALL/IN子查询每一类都有固定骨架。判分看结果集和别名字段名对不上就不过。这一块也是数据库笔试面试的高频区。4.1 内连接与外连接LEFT JOIN 和 RIGHT JOIN 别写反内连接只保留两表都匹配的行外连接保留一侧全部行。题目要“所有学生姓名和对应班级”学生是主表就该用左外连接或把学生放右边用右外连接。实训答案里用tb_class right join tb_student和tb_class left join tb_student两种写法本质是保证学生全保留。USE School; -- 内连接只保留能匹配上的学生和班级 select tb_student.name as studentName, tb_class.name as className from tb_student join tb_class on tb_class.id tb_student.class_id; -- 右外连接以右边的学生表为准班级为空也保留 select tb_student.name as studentName, tb_class.name as className from tb_class right join tb_student on tb_class.id tb_student.class_id; -- 左外连接以左边的班级表为准 select tb_student.name as studentName, tb_class.name as className from tb_class left join tb_student on tb_class.id tb_student.class_id;外连接题最常见的错是把主表放错边导致没班级的学生被过滤掉。答题时先问自己“题目要保留哪张表的全部记录”那张表就放在外连接保留的一侧。别名也要按题目要求写studentName、className少一个大小写都可能判错。4.2 子查询比较运算符、ANY、ALL、IN 的语义差别子查询返回单值时可以直接用、比较返回多值时要用ANY、ALL、IN。 ANY(子查询)等价于大于最小值 ALL(子查询)等价于大于最大值。实训里“大于所有平均年龄”就是age (select avg(age) from tb_emp)因为AVG返回单值。USE Company; -- 大于平均年龄的员工 select name, age from tb_emp where age (select avg(age) from tb_emp); -- 大于 java 岗位最高工资用 ALL SELECT position, salary FROM tb_salary WHERE salary ALL(SELECT salary FROM tb_salary WHERE position java); -- 大于 java 岗位最低工资用 ANY SELECT position, salary FROM tb_salary WHERE salary ANY(SELECT salary FROM tb_salary WHERE position java); -- 岗位等于 java select position, salary from tb_salary where position in (java);ANY和ALL的语义是面试常问点ANY是“满足其中一个”ALL是“满足全部”。答题时先翻译成自然语言再写 SQL能避免把ANY写成ALL。另外子查询里如果返回多行却用比较会直接报错这是很典型的踩坑点。4.3 复杂查询交换工资、换座位、分数排名、人流量“复杂查询”两组是整套实训里最像面试题的部分。交换工资用CASE WHEN把 m 和 f 对调换座位用IF处理奇偶最后一位单独判断分数排名用相关子查询统计“比自己高的不同分数个数加一”人流量用三表自连接找连续三条记录都大于等于 100 的区间。-- 交换性别m 变 f其他变 m UPDATE tb_Salary SET sex CASE sex WHEN m THEN f ELSE m END; -- 换座位偶数 id 减一奇数 id 加一最后一位不动 SELECT if(Id%20, Id-1, if(Id5, Id, Id1)) 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 as s order by Score desc;排名题有两种写法COUNT(DISTINCT score)处理并列名次COUNT(*) 1处理连续名次题目要哪种看它是否允许并列。人流量题的关键是把“连续三天”翻译成自连接的 id 关系再用DISTINCT去重。这类题面试官最想听的是思路拆解先讲怎么把业务条件转成表连接条件再落到 SQL。5. 聚合、分组与函数GROUP BY 和 HAVING 的先后顺序聚合函数和分组是单表查询的进阶也是笔试必考。COUNT、SUM、AVG、MAX、MIN各一关后面还有GROUP BY配合HAVING和ORDER BY。核心规则只有一条WHERE过滤行HAVING过滤组聚合条件放HAVING。5.1 五个聚合函数与分组过滤COUNT(*)统计行数COUNT(字段)忽略 NULLSUM、AVG只对数值列有效MAX、MIN可用于数值和日期。分组后要筛“至少两门 90 分以上”必须用HAVING count(*) 2不能写在WHERE里因为WHERE执行时还没分组。USE School; -- 每个年级男女各多少人 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; -- 平均分大于 90 且语文 95 以上 select sno, avg(score) from tb_grade where sno in (select sno from tb_grade where score 95 and pno 语文) group by sno having avg(score) 90;HAVING题最常见的错是把聚合条件写进WHERE报“Invalid use of group function”。答题顺序记牢先WHERE筛行再GROUP BY分组再HAVING筛组最后ORDER BY排序。面试问“WHERE 和 HAVING 区别”就按这个执行顺序答。5.2 字符、数学、日期与自定义函数“其他函数的使用”这组覆盖CONCAT、UPPER、LOWER、SUBSTR、ROUND、TRUNCATE、SQRT、POWER、YEAR以及自定义函数。字符函数题要求首字母大写其余小写用CONCAT(UPPER(SUBSTR(Name,1,1)), LOWER(SUBSTR(Name,2,LENGTH(Name))))。自定义函数要用DELIMITER改结束符函数体里DECLARE变量、IF判断、RETURN返回。-- 首字母大写其余小写 select CONCAT(UPPER(SUBSTR(Name,1,1)), LOWER(SUBSTR(Name,2,LENGTH(Name)))) as Name from employee; -- 自定义函数返回三个数中的最大值 delimiter // create function fn_three_max(param_1 int, param_2 int, param_3 int) RETURNS int BEGIN DECLARE max_val int DEFAULT 0; if param_1 param_2 then set max_val param_1; else set max_val param_2; end if; if param_3 max_val then set max_val param_3; end if; return max_val; END//自定义函数失分多在DELIMITER不改结束符函数体里的分号会被当成语句结束。答题时先写delimiter //函数写完再恢复。日期题里“年龄”常用当前日期减出生日期注意YEAR取年份、ROUND保留小数位。6. 视图、索引、分页与存储过程开发技巧类关卡怎么答“数据管理技术实战”这一部分从查询转向数据库对象视图、索引、分页优化、存储过程。题目不再是单纯查数据而是让你建对象、看执行计划、写带输入输出参数的存储过程。这一块面试问得最多实训判分也最看结构。6.1 视图与索引创建、查看与组合索引视图是保存好的查询CREATE VIEW 视图名 AS SELECT ...单表视图直接选字段多表视图要写连接条件。索引分主键索引、唯一索引、普通索引、组合索引建表时PRIMARY KEY自带主键索引CREATE UNIQUE INDEX建唯一索引CREATE INDEX建普通索引ALTER TABLE ... ADD INDEX建组合索引最后用SHOW INDEX FROM 表名查看。USE School; -- 单表视图数学、语文和总分 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; USE Students; -- 唯一索引和普通索引 CREATE UNIQUE INDEX name_index ON student(name); CREATE INDEX score_index ON student(score); SHOW INDEX FROM student; USE Person; -- 组合索引注意字段顺序影响最左前缀匹配 ALTER TABLE person ADD INDEX name_city_score (name, age, address); SHOW INDEX FROM person;组合索引的考点是最左前缀(name, age, address)能命中name、nameage、nameageaddress单独查age用不上。面试常问“索引是不是越多越好”答法是索引加速查询但拖慢写入、占空间要按查询模式建。视图题注意视图不存数据查视图等于执行定义里的查询。6.2 分页优化与存储过程LIMIT 深翻页和 IN/OUT 参数分页题给了两种写法普通LIMIT和用子查询先定位 id 再取。深翻页时LIMIT 100000,5要扫描大量行用where prod_id (select prod_id from products limit 99999,1) limit 5能走索引。存储过程用CREATE PROCEDURE输入参数IN输出参数OUT内部用IF/ELSEIF分支最后SELECT输出。USE Products; -- 普通分页偏移 4取 5 条 select prod_id from products where prod_id (select prod_id from products limit 4,1) limit 5; -- 深翻页优化先定位上一页最后一条 id再往后取 select prod_id from products where prod_id (select prod_id from products limit 9,1) limit 5; USE mydb; -- 存储过程按信用额度输出客户等级 drop procedure if exists mydb.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; select p_customNumber as customerNumber, p_customerLevel; End$$存储过程失分点有三个DELIMITER没改、IN/OUT写反、SELECT ... INTO没查到数据导致变量为 NULL。答题时先把参数方向标清楚再写分支。分页优化题面试官想听的是“为什么深翻页慢”——偏移越大扫描越多用 id 定位能减少扫描行数。7. 易错点复盘这五类题丢分最多7.1 外键约束名对不上删不掉也建不成错在哪建外键时没写CONSTRAINT名字或者名字和后面删除时写的不一致。为什么错DROP FOREIGN KEY靠约束名定位不是靠字段名。怎么答对建表时统一写成CONSTRAINT fk_xxx FOREIGN KEY(...) REFERENCES ...把名字记在草稿上删除时原样照抄。7.2 CHANGE 漏写类型MODIFY 想改名错在哪ALTER TABLE tb_emp CHANGE Id prod_id;少了新类型。为什么错CHANGE语法要求旧名、新名、类型三样齐全。怎么答对改名一律写CHANGE 旧名 新名 类型只改类型用MODIFY 字段 类型两者别混。7.3 空值用 NULL查出来永远是空错在哪WHERE DeptId NULL。为什么错NULL比较结果是未知不满足WHERE的真条件。怎么答对统一用IS NULL/IS NOT NULL聚合时记住COUNT(字段)会跳过 NULL。7.4 LIMIT 偏移算错第 2 名写成 LIMIT 2,4错在哪把“第 2 名”当成偏移 2。为什么错偏移从 0 开始第 2 名偏移是 1。怎么答对第 m 名到第 n 名写成LIMIT m-1, n-m1先在纸上标序号再写。7.5 聚合条件写进 WHEREGROUP BY 后字段不匹配错在哪WHERE count(*) 2或SELECT里出现未分组字段。为什么错WHERE在分组前执行聚合函数还不能用非聚合字段在严格模式下必须出现在GROUP BY里。怎么答对行条件放WHERE组条件放HAVINGSELECT字段要么分组要么聚合。8. 考场上的答题顺序先跑通再抠细节这套实训的关卡设计有个规律越靠前的关卡越基础越靠后越综合。真正上场时我建议按“先结构后数据、先单表后多表、先跑通再优化”的顺序推进。拿到一关先看题目要的结果长什么样倒推需要哪些表、哪些字段、什么条件写完先在脑子里过一遍执行顺序FROM找表WHERE筛行GROUP BY分组HAVING筛组SELECT取列ORDER BY排序LIMIT截断。这个顺序能解释绝大多数“为什么我的语句报错”。下面这张表是我自己复盘时用的检查清单按题型对应最容易漏的点题型先检查什么常见补救建库建表库是否use、字段类型长度逐字对照题目字段清单约束主键单列还是复合、外键名父表先建约束名记下来ALTER TABLE改名是否带类型、位置关键字CHANGE三件套MODIFY只改型条件查询空值是否用IS NULL、区间是否闭BETWEEN含两端LIKE看通配符连接查询主表放哪边、别名是否一致要保留全部记录的表放外连接侧子查询返回单值还是多值多值用ANY/ALL/IN别用分组聚合聚合条件是否在HAVINGWHERE筛行HAVING筛组视图索引视图定义能否单独跑通组合索引注意最左前缀存储过程DELIMITER、IN/OUT方向先改结束符再写分支押题方向上连接查询的外连接主表判断、子查询的ANY/ALL语义、GROUP BY配HAVING、深翻页优化这四类几乎每次都会以不同表名出现。答题技巧上我自己的习惯是每写完一条 SQL先不急着提交用DESCRIBE或SELECT *小范围验证结构再跑完整语句遇到报错先看错误码指向语法还是字段语法错查关键字拼写字段错查表名和别名。这套答案 PDF 带目录按关卡顺序排好卡住时直接翻到对应关卡对照语句比从头翻效率高得多。从那以后我每次做这类实训都强制自己先写检查清单再动手错得明显少了。希望帮到你。本文还有配套的精品资源点击获取