数据库课的第二次作业题目定为“水浒传英雄人物”我一开始真没当回事。脑子里第一反应是建一张hero表把id、name、nickname、rank放进去再插进108条数据最后跑几条select交差。结果真正动手的时候才发现这个题目比想象中耐琢磨字段到底该怎么设计才能把“英雄档案”讲清楚排名、绰号、星号这些信息是塞在一张表里还是拆开各路派系怎么统计中文乱码怎么处理等我把整套作业从头到尾做完相当于把MySQL的建库、建表、约束、插入、事务、多表查询、聚合函数、窗口函数全复习了一遍。这篇文章就把完整过程复盘一遍给正在做类似作业的同学以及想借这个题材练SQL的朋友做个参考。1. 作业本质看似“填表”实际考的是这四件事1.1 一个业务场景的数据库建模很多人看到“水浒传英雄人物”这个名字第一反应是“这不就是建一张表吗”。实际上老师出这个题通常不是让你介绍梁山好汉而是把一个“英雄档案”场景抽象成数据模型。你需要回答的问题包括英雄有哪些属性这些属性之间是什么关系怎么保证数据不冗余、不冲突怎么让后续的统计查询更高效我习惯先把业务需求拆出来英雄的基本信息、绰号、排名、星号、所属阵营、武器、综合战斗力、当前状态。如果再深入一点一个英雄可能有多件武器也可能在不同时期归属不同阵营所以这不是一张平铺的表就能解决的问题而是一个小型业务系统的雏形。对应到MySQL就是表结构设计、数据类型选择、约束条件、外键关系再加上数据插入和查询分析。这个题目最好的一点是数据量不大108条记录刚好能撑起各种统计但又不会大到让查询卡顿。而且“排名”“阵营”“战斗力”这些概念天然适合排序、分组、窗口函数练起来非常顺手。1.2 作业评分点拆解综合我看到的很多同类作业评分点一般集中在下面几项评分维度主要看什么常见扣分点建表语句字段类型是否合理主键、唯一约束、外键是否齐全全用VARCHAR类型乱选数据完整性108将排名、绰号、星号是否准确是否有重复排名重复、绰号错字查询正确性是否覆盖条件查询、排序、分组、聚合、多表连接只写了SELECT *设计规范命名是否清晰注释是否存在脚本能否重复执行命名随意脚本重复跑报错扩展能力是否拆分了阵营表是否考虑多武器场景一个字段存好几个武器名看清楚这些评分点之后你就明白了这不是“填空题”而是要求你用工程化的思路去做一个小项目。哪怕老师没有明文要求把表结构拆分、加约束、写注释都会让作业质感高一大截。2. 表结构设计把“人物档案”拆成几张表才合理2.1 字段设计与业务规则我最初的单表方案是英雄编号、姓名、绰号、星号、排名、阵营、武器、战斗力、驻地、状态。但仔细想一下“武器”这个字段就有问题。武松既用过哨棒也用过雪花镔铁戒刀如果我把武器写成一个字符串“哨棒,戒刀”后面统计“用过戒刀的好汉有几位”就会非常痛苦。所以我把武器拆成单独的关系表至少让表结构对应的业务规则清楚。给英雄主表设计了这些字段字段名类型约束说明hero_idINT UNSIGNED主键自增英雄唯一标识nameVARCHAR(20)NOT NULLUNIQUE姓名nicknameVARCHAR(50)NOT NULLUNIQUE绰号star_nameVARCHAR(20)NOT NULL星号比如天魁星rank_noTINYINT UNSIGNEDNOT NULLUNIQUE梁山排名1-108camp_idTINYINT UNSIGNED外键所属阵营force_scoreDECIMAL(5,2)默认60.00综合武力评分locationVARCHAR(50)可空来历/原所在地statusENUM(在世,离世,隐居)默认在世人物状态说几个选型的理由。rank_no用TINYINT UNSIGNED就够了因为梁山排名最大是108TINYINT无符号最多255占用空间小。name和nickname都加UNIQUE约束因为梁山好汉的插翅虎、锦毛虎这些绰号在108将里都是唯一的加了唯一约束能防止插入重复数据。force_score用DECIMAL而不是DOUBLE因为战斗力这种数值需要精度可控用浮点数容易出现“拳打猛虎97.9999998”这种尴尬。2.2 约束与外键范式不范式的取舍“范式”这个词对新手有点劝退但理解起来其实不难不要把阵营名称在hero表里重复存108次。如果hero表里直接写camp_name梁山泊每行都存一段相同字符串浪费空间不说万一不小心把其中一个写成“梁山伯”后续统计就会被悄悄污染。更合理的做法是单独建一张camp表英雄表里只存camp_id。这个动作本质就是“消除冗余”。外键加上之后插入一个不存在的camp_id会直接报错这能保证数据的引用完整性。很多学生怕外键因为插入数据时必须先插入阵营表再插入英雄表顺序反了会报错。但正是这个“麻烦”能逼你养成正确的数据维护习惯。建库建表的SQL我放在下面字符集统一用utf8mb4避免中文乱码CREATE DATABASE IF NOT EXISTS shuihu_db DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci; USE shuihu_db; CREATE TABLE camp ( camp_id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, camp_name VARCHAR(30) NOT NULL UNIQUE, camp_desc VARCHAR(100) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE hero ( hero_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL UNIQUE, nickname VARCHAR(50) NOT NULL UNIQUE, star_name VARCHAR(20) NOT NULL, rank_no TINYINT UNSIGNED NOT NULL UNIQUE, camp_id TINYINT UNSIGNED NOT NULL, force_score DECIMAL(5,2) DEFAULT 60.00, location VARCHAR(50), status ENUM(在世,离世,隐居) DEFAULT 在世, CONSTRAINT fk_hero_camp FOREIGN KEY (camp_id) REFERENCES camp(camp_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这里有几个细节值得说。AUTO_INCREMENT从1开始每次插入英雄自动编号不用手动维护。外键约束命名为fk_hero_camp以后如果要删除约束直接通过名字操作不用靠记忆。ENGINEInnoDB是为了支持事务和外键MyISAM不支持这些作业里不要用。3. 数据准备108将的“石碣排名”是最佳数据源3.1 数据整理排名、绰号、星号怎么对应数据从哪儿来最权威的出处是《水浒传》第七十一回“忠义堂石碣受天文”梁山好汉排名那段。前36位是“天罡星”后72位是“地煞星”一共108人。不同版本可能有个别字差异所以我的建议是找一份完整的《水浒传108将排名表》然后交叉核对两遍。我挑几行用来做演示完整数据请按原著整理1 宋江 及时雨 天魁星 2 卢俊义 玉麒麟 天罡星 3 吴用 智多星 天机星 14 武松 行者 天伤星 22 李逵 黑旋风 天杀星注意“星号”和“排名号”是两个概念。排名是梁山座次的顺序星号是石碣上对应的星宿名。两者都唯一但一个偏数字一个偏文字。在表里我同时保留了rank_no和star_name就是为了既能按名次排序又能按星宿名筛选。整理数据的时候容易犯一个错直接复制网上资料结果把排名和星号对不上。我的经验是先灌输到Excel里第一列排名第二列姓名第三列绰号第四列星号然后做一次排序检查看看排名1到108是否连续、姓名是否重复。如果只是练习可以只挑天罡36人先插进去这个量级足够验证所有SQL。3.2 插入语句的三种写法与效率对比数据插入是最容易“翻车”的环节。推荐三种写法从简单到进阶第一种单条INSERT。适合调试阶段看得清楚报错也知道错在哪行INSERT INTO hero(name, nickname, star_name, rank_no, camp_id, force_score, status) VALUES(宋江, 及时雨, 天魁星, 1, 1, 78.00, 在世); INSERT INTO hero(name, nickname, star_name, rank_no, camp_id, force_score, status) VALUES(卢俊义, 玉麒麟, 天罡星, 2, 1, 95.00, 在世);第二种多VALUES批量插入。适合正式导入一次写很多条执行效率高节省网络来回INSERT INTO hero(name, nickname, star_name, rank_no, camp_id, force_score, status) VALUES (吴用, 智多星, 天机星, 3, 1, 62.00, 在世), (武松, 行者, 天伤星, 14, 2, 97.00, 在世), (李逵, 黑旋风, 天杀星, 22, 1, 88.00, 在世);第三种LOAD DATA INFILE。适合数据量大到写SQL太痛苦的场景比如已经有CSV文件LOAD DATA INFILE /tmp/heroes.csv INTO TABLE hero FIELDS TERMINATED BY , IGNORE 1 LINES (name, nickname, star_name, rank_no, camp_id, force_score, status);需要注意LOAD DATA INFILE操作的是服务端文件如果想用本机CSV需要LOCAL关键字而且MySQL 8.0对文件权限有额外限制。对作业来说第二种写法最稳妥既能看清数据也不依赖外部文件。我强烈建议把插入语句包在事务里START TRANSACTION; INSERT INTO hero(...) VALUES (...); INSERT INTO hero(...) VALUES (...); -- 继续插入更多数据 COMMIT;如果中间某条数据报错可以在COMMIT之前执行ROLLBACK整个插入操作回滚不会留下“一半有数据一半没数据”的脏状态。这个习惯在真实项目里太重要了作业里写出来也能加印象分。4. SQL查询实战从基础到面试级4.1 基础查询从“选全部”到“带条件地选”作业最基本的查询是查看全部英雄SELECT * FROM hero ORDER BY rank_no;但只写这一句老师会觉得你还没入门。至少要展示条件查询比如筛选天罡36将SELECT name, nickname, star_name, rank_no FROM hero WHERE rank_no BETWEEN 1 AND 36 ORDER BY rank_no;这里有个关键细节rank_no必须存成数字类型。如果你存成VARCHAR排序会得到1、10、100、108、2这种字典序而不是1、2、3、4的数字顺序。很多同学排序结果怪怪的就是这个原因。4.2 条件查询与排序找“武力前10”和“各阵营第一”实战中比较有用的查询是“武力前10名”SELECT name, nickname, force_score FROM hero ORDER BY force_score DESC LIMIT 10;这里LIMIT一定要放在ORDER BY后面逻辑上是先排序再取前10条。如果把LIMIT写在WHERE里MySQL会直接报语法错误。再看一个有点意思的查询某个阵营里排名最靠前的英雄。SELECT name, nickname, rank_no FROM hero WHERE camp_id 1 ORDER BY rank_no ASC LIMIT 1;这条SQL能体现你对“条件过滤排序限量”三个概念的理解。4.3 聚合统计人数、平均武力、武器数量聚合函数是第二次作业常考的重点。统计每个阵营的英雄人数SELECT c.camp_name, COUNT(h.hero_id) AS cnt FROM camp c LEFT JOIN hero h ON c.camp_id h.camp_id GROUP BY c.camp_id, c.camp_name;这里为什么用LEFT JOIN而不是INNER JOIN因为LEFT JOIN会保留左侧camp表中没有对应英雄的阵营结果至少返回所有阵营INNER JOIN则会自动忽略空阵营。虽然这个案例里所有阵营大概都有人但这个习惯在真实业务里很关键。统计所有英雄的平均武力值SELECT AVG(force_score) AS avg_force FROM hero;统计每个阵营的平均武力并且按平均武力从高到低排序SELECT camp_id, AVG(force_score) AS avg_force FROM hero GROUP BY camp_id ORDER BY avg_force DESC;这里有个细节ORDER BY可以用别名avg_force因为SELECT阶段先计算出别名ORDER BY阶段再排序。这一点在MySQL里是允许的但在某些严格模式下会有限制属于SQL执行顺序的经典考点。4.4 多表连接英雄与阵营的联查如果前面建了camp表那JOIN几乎是必写项目。最基础的联查SELECT h.name, h.nickname, c.camp_name FROM hero h INNER JOIN camp c ON h.camp_id c.camp_id ORDER BY h.rank_no;给表起别名的好处是SQL变短不易写错。我习惯把主表别名取为h关联表别名取为c一眼就能分辨。INNER JOIN和LEFT JOIN的区别建议自己分别执行两条观察结果是否一致。如果还建了hero_weapon武器关系表就可以三表联查SELECT h.name, h.nickname, w.weapon_name FROM hero h JOIN hero_weapon hw ON h.hero_id hw.hero_id JOIN weapon w ON hw.weapon_id w.weapon_id ORDER BY h.rank_no;这种三表连接是“英雄—武器关系表—武器表”的经典写法能应对“一个人多把武器”的场景比在hero表里拼字符串干净得多。4.5 窗口函数与高级玩法每个阵营中武力前三如果MySQL版本是8.0窗口函数是很好的加分项。给每个英雄在所属阵营里按照武力排名SELECT name, nickname, camp_id, force_score, ROW_NUMBER() OVER (PARTITION BY camp_id ORDER BY force_score DESC) AS rn FROM hero;想取出每个阵营武力前三名可以把上面的查询包成子查询SELECT t.name, t.nickname, t.camp_id, t.force_score FROM ( SELECT name, nickname, camp_id, force_score, ROW_NUMBER() OVER (PARTITION BY camp_id ORDER BY force_score DESC) AS rn FROM hero ) t WHERE t.rn 3 ORDER BY t.camp_id, t.rn;这个题目用窗口函数来练非常合适因为“排名”本身就是梁山主题的关键词。如果老师没要求写上去属于明显的“超纲发挥”通常能看出你对新特性的掌握。4.6 经典面试题查询“天罡地煞”在各阵营的分布还有个很经典的“行转列”思路可以用在作业里SELECT camp_id, SUM(CASE WHEN rank_no 36 THEN 1 ELSE 0 END) AS tian_gang_cnt, SUM(CASE WHEN rank_no 36 THEN 1 ELSE 0 END) AS di_sha_cnt FROM hero GROUP BY camp_id;这条SQL用CASE WHEN把“是否天罡”这个判断变成0或1再SUM聚合得到每个阵营的天罡人数和地煞人数。它展示了条件聚合的写法比单纯写COUNT(*)更有区分度。5. 实操中的问题和排查记录5.1 中文乱码罪魁祸首不只是表最经典的问题是表建好了插入中文后select出来全是问号。很多人第一时间怪表其实乱码可能出在三个地方建库时没指定utf8mb4、连接的字符集不是utf8mb4、SQL文件本身保存成了其他编码。我的排错顺序是先执行SHOW CREATE TABLE hero;看表是否utf8mb4再执行SELECT character_set_client;看连接字符集最后检查SQL文件保存编码推荐用无BOM的UTF-8。必要时在连接后执行一次SET NAMES utf8mb4;MySQL 8.0默认字符集虽然已经是utf8mb4但一些老客户端工具仍可能以latin1连接所以这一句写进脚本里能省不少事。5.2 中文排序想按拼音排序怎么办默认情况下utf8mb4_general_ci对中文排序用的是Unicode码位顺序不是拼音顺序。如果想按姓名拼音排序可以把中文字段转成GBK再排序SELECT name FROM hero ORDER BY CONVERT(name USING GBK);GBK编码对常用汉字排序时结果基本等同于拼音顺序。这个方法适合“按姓氏拼音输出好汉名单”这种需求作为作业的小加分项很讨巧。但要注意如果有生僻字转换GBK可能失败暂时会显示NULL实际作业里影响不大。5.3 SQL文件保存编码导致“Incorrect string value”我踩过的坑之一是在Windows记事本里把SQL文件保存成ANSI编码结果执行INSERT时直接报错ERROR 1366 (HY000): Incorrect string value因为MySQL期望收到utf8mb4的字节流收到GBK/ANSI字节流后无法正确解码汉字。解决办法很简单用VSCode、Notepad等工具把文件另存为UTF-8 with or without BOM都会比较省事。在VSCode右下角可以看到当前编码手动切到UTF-8再保存即可。5.4 only_full_group_by报错MySQL 5.7及以上默认开启了sql_modeonly_full_group_by如果你写SELECT name, camp_id, COUNT(*) FROM hero GROUP BY camp_id;会直接报错因为name没有包含在GROUP BY子句中也没有被聚合函数包裹。这个报错不是数据库坏了而是SQL本身不符合标准。我的建议是不要为了省事去修改sql_mode更不要关掉only_full_group_by。这样会把逻辑错误掩盖掉考试或面试时容易吃亏。正确做法是如果确实需要某个非聚合字段要么把它加入GROUP BY要么用MAX(name)这类聚合要么干脆去掉。5.5 数据重复与唯一性约束如果插入数据时不注意或者后续又执行了多次插入可能出现重复的好汉比如两个“李逵”。排查重复数据可以用SELECT rank_no, COUNT(*) AS cnt FROM hero GROUP BY rank_no HAVING cnt 1;HAVING是用来筛选分组结果的和WHERE不同后者在分组前过滤前者在分组后过滤。如果作业里要求“保证排名唯一”其实应该靠UNIQUE约束在源头挡住而不是等到查出来再清理。所以我建议在设计表时就加上UNIQUE。5.6 事务未提交导致后续操作卡住如果在命令行或客户端执行了START TRANSACTION;插入一大堆数据后没有执行COMMIT;就关掉窗口可能出现表被锁住或者后续操作一直卡住的情况。遇到这种情况用SHOW PROCESSLIST;看看有没有长时间处于Sleep状态或Waiting for table metadata lock的连接如果有可以把对应会话KILL掉或者先执行COMMIT或ROLLBACK完成事务。这个经验虽然和题目没有直接关系但在实操中经常遇到值得记一下。6. 作业提交与加分项6.1 脚本组织三个文件分开执行顺序清楚交作业时最怕的是老师拿到一个巨大无比的SQL文件分不清哪些是建表、哪些是插数据、哪些是查询。我建议把项目目录组织成这样shuihu_assignment/ ├── 01_create_db.sql ├── 02_insert_data.sql ├── 03_queries.sql └── readme.md01脚本负责建库建表02脚本负责插入数据03脚本负责跑各类查询。每个脚本开头写清楚执行顺序。readme里写明环境要求比如“MySQL 8.0字符集utf8mb4先执行01再执行02最后执行03”。这种工程感在评分时很有优势。6.2 注释让每条SQL都有“解释自己”的能力代码注释不要写废话比如这句就毫无价值-- 查询数据 SELECT * FROM hero;要写的是“业务目的”-- 查询梁山排名前10的英雄用于验证排名字段唯一性 SELECT name, nickname, rank_no FROM hero ORDER BY rank_no LIMIT 10;老师批改时看注释就能知道你是不是真理解每条SQL的作用这种注释习惯对以后写复杂SQL也有巨大帮助。6.3 进阶加分项视图、存储过程如果学有余力可以加一个视图让后续查询更简洁CREATE VIEW v_hero_camp AS SELECT h.name, h.nickname, h.star_name, h.rank_no, c.camp_name, h.force_score FROM hero h JOIN camp c ON h.camp_id c.camp_id;创建之后查询天罡36将可以简化成SELECT * FROM v_hero_camp WHERE rank_no 36 ORDER BY rank_no;存储过程也可以写一个“按排名查英雄”就是很好的例子DELIMITER // CREATE PROCEDURE sp_get_hero_by_rank(IN p_rank TINYINT UNSIGNED) BEGIN SELECT name, nickname, star_name, force_score FROM hero WHERE rank_no p_rank; END// DELIMITER ;调用时CALL sp_get_hero_by_rank(14);这会返回排名第14位的英雄。这种封装思路和实际业务中的接口设计很接近能在作业里体现你对“逻辑复用”的理解。6.4 演示文档与结果展示技巧查询结果如果只是命令行里一堆加号表格自己看都吃力。我的建议是把每个查询的“业务问题、SQL、执行结果”整理成文档结果直接转成Markdown表格。实现方式很简单在客户端里执行查询复制结果到在线工具转成Markdown再粘贴到readme。每个章节前加一小段说明比如“本部分用于统计各阵营的天罡地煞人数分布”整体效果会非常清晰。最后说点个人体会以前我总觉得数据库作业就是写几个SQL糊弄过去。可真把这个“水浒传英雄人物”当成一个从零开始的小项目来做才发现建表、插数据、写查询这三步里藏着大量细节char和varchar怎么选、utf8mb4和中文排序怎么处理、JOIN和GROUP BY怎么组合、窗口函数在分组排名里怎么用。如果你正在做类似的作业我的建议是别急着抄网上现成的108将脚本先自己把需求拆清楚再动手建表和插数据。哪怕只做天罡36人也比直接复制完整脚本有价值得多。这个题材后续还能扩展成“梁山好汉战斗力排行榜”“各派系实力统计”“随机抽取一名好汉”等功能当成自己练手的小项目收获会远比作业分数大。 
长河君 · 2026/10/14 4:36:59 · 阅读 1.2k MySQL实战：用水浒传英雄人物练手数据库设计到多表查询
数据库课的第二次作业题目定为“水浒传英雄人物”我一开始真没当回事。脑子里第一反应是建一张hero表把id、name、nickname、rank放进去再插进108条数据最后跑几条select交差。结果真正动手的时候才发现这个题目比想象中耐琢磨字段到底该怎么设计才能把“英雄档案”讲清楚排名、绰号、星号这些信息是塞在一张表里还是拆开各路派系怎么统计中文乱码怎么处理等我把整套作业从头到尾做完相当于把MySQL的建库、建表、约束、插入、事务、多表查询、聚合函数、窗口函数全复习了一遍。这篇文章就把完整过程复盘一遍给正在做类似作业的同学以及想借这个题材练SQL的朋友做个参考。1. 作业本质看似“填表”实际考的是这四件事1.1 一个业务场景的数据库建模很多人看到“水浒传英雄人物”这个名字第一反应是“这不就是建一张表吗”。实际上老师出这个题通常不是让你介绍梁山好汉而是把一个“英雄档案”场景抽象成数据模型。你需要回答的问题包括英雄有哪些属性这些属性之间是什么关系怎么保证数据不冗余、不冲突怎么让后续的统计查询更高效我习惯先把业务需求拆出来英雄的基本信息、绰号、排名、星号、所属阵营、武器、综合战斗力、当前状态。如果再深入一点一个英雄可能有多件武器也可能在不同时期归属不同阵营所以这不是一张平铺的表就能解决的问题而是一个小型业务系统的雏形。对应到MySQL就是表结构设计、数据类型选择、约束条件、外键关系再加上数据插入和查询分析。这个题目最好的一点是数据量不大108条记录刚好能撑起各种统计但又不会大到让查询卡顿。而且“排名”“阵营”“战斗力”这些概念天然适合排序、分组、窗口函数练起来非常顺手。1.2 作业评分点拆解综合我看到的很多同类作业评分点一般集中在下面几项评分维度主要看什么常见扣分点建表语句字段类型是否合理主键、唯一约束、外键是否齐全全用VARCHAR类型乱选数据完整性108将排名、绰号、星号是否准确是否有重复排名重复、绰号错字查询正确性是否覆盖条件查询、排序、分组、聚合、多表连接只写了SELECT *设计规范命名是否清晰注释是否存在脚本能否重复执行命名随意脚本重复跑报错扩展能力是否拆分了阵营表是否考虑多武器场景一个字段存好几个武器名看清楚这些评分点之后你就明白了这不是“填空题”而是要求你用工程化的思路去做一个小项目。哪怕老师没有明文要求把表结构拆分、加约束、写注释都会让作业质感高一大截。2. 表结构设计把“人物档案”拆成几张表才合理2.1 字段设计与业务规则我最初的单表方案是英雄编号、姓名、绰号、星号、排名、阵营、武器、战斗力、驻地、状态。但仔细想一下“武器”这个字段就有问题。武松既用过哨棒也用过雪花镔铁戒刀如果我把武器写成一个字符串“哨棒,戒刀”后面统计“用过戒刀的好汉有几位”就会非常痛苦。所以我把武器拆成单独的关系表至少让表结构对应的业务规则清楚。给英雄主表设计了这些字段字段名类型约束说明hero_idINT UNSIGNED主键自增英雄唯一标识nameVARCHAR(20)NOT NULLUNIQUE姓名nicknameVARCHAR(50)NOT NULLUNIQUE绰号star_nameVARCHAR(20)NOT NULL星号比如天魁星rank_noTINYINT UNSIGNEDNOT NULLUNIQUE梁山排名1-108camp_idTINYINT UNSIGNED外键所属阵营force_scoreDECIMAL(5,2)默认60.00综合武力评分locationVARCHAR(50)可空来历/原所在地statusENUM(在世,离世,隐居)默认在世人物状态说几个选型的理由。rank_no用TINYINT UNSIGNED就够了因为梁山排名最大是108TINYINT无符号最多255占用空间小。name和nickname都加UNIQUE约束因为梁山好汉的插翅虎、锦毛虎这些绰号在108将里都是唯一的加了唯一约束能防止插入重复数据。force_score用DECIMAL而不是DOUBLE因为战斗力这种数值需要精度可控用浮点数容易出现“拳打猛虎97.9999998”这种尴尬。2.2 约束与外键范式不范式的取舍“范式”这个词对新手有点劝退但理解起来其实不难不要把阵营名称在hero表里重复存108次。如果hero表里直接写camp_name梁山泊每行都存一段相同字符串浪费空间不说万一不小心把其中一个写成“梁山伯”后续统计就会被悄悄污染。更合理的做法是单独建一张camp表英雄表里只存camp_id。这个动作本质就是“消除冗余”。外键加上之后插入一个不存在的camp_id会直接报错这能保证数据的引用完整性。很多学生怕外键因为插入数据时必须先插入阵营表再插入英雄表顺序反了会报错。但正是这个“麻烦”能逼你养成正确的数据维护习惯。建库建表的SQL我放在下面字符集统一用utf8mb4避免中文乱码CREATE DATABASE IF NOT EXISTS shuihu_db DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci; USE shuihu_db; CREATE TABLE camp ( camp_id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, camp_name VARCHAR(30) NOT NULL UNIQUE, camp_desc VARCHAR(100) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE hero ( hero_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL UNIQUE, nickname VARCHAR(50) NOT NULL UNIQUE, star_name VARCHAR(20) NOT NULL, rank_no TINYINT UNSIGNED NOT NULL UNIQUE, camp_id TINYINT UNSIGNED NOT NULL, force_score DECIMAL(5,2) DEFAULT 60.00, location VARCHAR(50), status ENUM(在世,离世,隐居) DEFAULT 在世, CONSTRAINT fk_hero_camp FOREIGN KEY (camp_id) REFERENCES camp(camp_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这里有几个细节值得说。AUTO_INCREMENT从1开始每次插入英雄自动编号不用手动维护。外键约束命名为fk_hero_camp以后如果要删除约束直接通过名字操作不用靠记忆。ENGINEInnoDB是为了支持事务和外键MyISAM不支持这些作业里不要用。3. 数据准备108将的“石碣排名”是最佳数据源3.1 数据整理排名、绰号、星号怎么对应数据从哪儿来最权威的出处是《水浒传》第七十一回“忠义堂石碣受天文”梁山好汉排名那段。前36位是“天罡星”后72位是“地煞星”一共108人。不同版本可能有个别字差异所以我的建议是找一份完整的《水浒传108将排名表》然后交叉核对两遍。我挑几行用来做演示完整数据请按原著整理1 宋江 及时雨 天魁星 2 卢俊义 玉麒麟 天罡星 3 吴用 智多星 天机星 14 武松 行者 天伤星 22 李逵 黑旋风 天杀星注意“星号”和“排名号”是两个概念。排名是梁山座次的顺序星号是石碣上对应的星宿名。两者都唯一但一个偏数字一个偏文字。在表里我同时保留了rank_no和star_name就是为了既能按名次排序又能按星宿名筛选。整理数据的时候容易犯一个错直接复制网上资料结果把排名和星号对不上。我的经验是先灌输到Excel里第一列排名第二列姓名第三列绰号第四列星号然后做一次排序检查看看排名1到108是否连续、姓名是否重复。如果只是练习可以只挑天罡36人先插进去这个量级足够验证所有SQL。3.2 插入语句的三种写法与效率对比数据插入是最容易“翻车”的环节。推荐三种写法从简单到进阶第一种单条INSERT。适合调试阶段看得清楚报错也知道错在哪行INSERT INTO hero(name, nickname, star_name, rank_no, camp_id, force_score, status) VALUES(宋江, 及时雨, 天魁星, 1, 1, 78.00, 在世); INSERT INTO hero(name, nickname, star_name, rank_no, camp_id, force_score, status) VALUES(卢俊义, 玉麒麟, 天罡星, 2, 1, 95.00, 在世);第二种多VALUES批量插入。适合正式导入一次写很多条执行效率高节省网络来回INSERT INTO hero(name, nickname, star_name, rank_no, camp_id, force_score, status) VALUES (吴用, 智多星, 天机星, 3, 1, 62.00, 在世), (武松, 行者, 天伤星, 14, 2, 97.00, 在世), (李逵, 黑旋风, 天杀星, 22, 1, 88.00, 在世);第三种LOAD DATA INFILE。适合数据量大到写SQL太痛苦的场景比如已经有CSV文件LOAD DATA INFILE /tmp/heroes.csv INTO TABLE hero FIELDS TERMINATED BY , IGNORE 1 LINES (name, nickname, star_name, rank_no, camp_id, force_score, status);需要注意LOAD DATA INFILE操作的是服务端文件如果想用本机CSV需要LOCAL关键字而且MySQL 8.0对文件权限有额外限制。对作业来说第二种写法最稳妥既能看清数据也不依赖外部文件。我强烈建议把插入语句包在事务里START TRANSACTION; INSERT INTO hero(...) VALUES (...); INSERT INTO hero(...) VALUES (...); -- 继续插入更多数据 COMMIT;如果中间某条数据报错可以在COMMIT之前执行ROLLBACK整个插入操作回滚不会留下“一半有数据一半没数据”的脏状态。这个习惯在真实项目里太重要了作业里写出来也能加印象分。4. SQL查询实战从基础到面试级4.1 基础查询从“选全部”到“带条件地选”作业最基本的查询是查看全部英雄SELECT * FROM hero ORDER BY rank_no;但只写这一句老师会觉得你还没入门。至少要展示条件查询比如筛选天罡36将SELECT name, nickname, star_name, rank_no FROM hero WHERE rank_no BETWEEN 1 AND 36 ORDER BY rank_no;这里有个关键细节rank_no必须存成数字类型。如果你存成VARCHAR排序会得到1、10、100、108、2这种字典序而不是1、2、3、4的数字顺序。很多同学排序结果怪怪的就是这个原因。4.2 条件查询与排序找“武力前10”和“各阵营第一”实战中比较有用的查询是“武力前10名”SELECT name, nickname, force_score FROM hero ORDER BY force_score DESC LIMIT 10;这里LIMIT一定要放在ORDER BY后面逻辑上是先排序再取前10条。如果把LIMIT写在WHERE里MySQL会直接报语法错误。再看一个有点意思的查询某个阵营里排名最靠前的英雄。SELECT name, nickname, rank_no FROM hero WHERE camp_id 1 ORDER BY rank_no ASC LIMIT 1;这条SQL能体现你对“条件过滤排序限量”三个概念的理解。4.3 聚合统计人数、平均武力、武器数量聚合函数是第二次作业常考的重点。统计每个阵营的英雄人数SELECT c.camp_name, COUNT(h.hero_id) AS cnt FROM camp c LEFT JOIN hero h ON c.camp_id h.camp_id GROUP BY c.camp_id, c.camp_name;这里为什么用LEFT JOIN而不是INNER JOIN因为LEFT JOIN会保留左侧camp表中没有对应英雄的阵营结果至少返回所有阵营INNER JOIN则会自动忽略空阵营。虽然这个案例里所有阵营大概都有人但这个习惯在真实业务里很关键。统计所有英雄的平均武力值SELECT AVG(force_score) AS avg_force FROM hero;统计每个阵营的平均武力并且按平均武力从高到低排序SELECT camp_id, AVG(force_score) AS avg_force FROM hero GROUP BY camp_id ORDER BY avg_force DESC;这里有个细节ORDER BY可以用别名avg_force因为SELECT阶段先计算出别名ORDER BY阶段再排序。这一点在MySQL里是允许的但在某些严格模式下会有限制属于SQL执行顺序的经典考点。4.4 多表连接英雄与阵营的联查如果前面建了camp表那JOIN几乎是必写项目。最基础的联查SELECT h.name, h.nickname, c.camp_name FROM hero h INNER JOIN camp c ON h.camp_id c.camp_id ORDER BY h.rank_no;给表起别名的好处是SQL变短不易写错。我习惯把主表别名取为h关联表别名取为c一眼就能分辨。INNER JOIN和LEFT JOIN的区别建议自己分别执行两条观察结果是否一致。如果还建了hero_weapon武器关系表就可以三表联查SELECT h.name, h.nickname, w.weapon_name FROM hero h JOIN hero_weapon hw ON h.hero_id hw.hero_id JOIN weapon w ON hw.weapon_id w.weapon_id ORDER BY h.rank_no;这种三表连接是“英雄—武器关系表—武器表”的经典写法能应对“一个人多把武器”的场景比在hero表里拼字符串干净得多。4.5 窗口函数与高级玩法每个阵营中武力前三如果MySQL版本是8.0窗口函数是很好的加分项。给每个英雄在所属阵营里按照武力排名SELECT name, nickname, camp_id, force_score, ROW_NUMBER() OVER (PARTITION BY camp_id ORDER BY force_score DESC) AS rn FROM hero;想取出每个阵营武力前三名可以把上面的查询包成子查询SELECT t.name, t.nickname, t.camp_id, t.force_score FROM ( SELECT name, nickname, camp_id, force_score, ROW_NUMBER() OVER (PARTITION BY camp_id ORDER BY force_score DESC) AS rn FROM hero ) t WHERE t.rn 3 ORDER BY t.camp_id, t.rn;这个题目用窗口函数来练非常合适因为“排名”本身就是梁山主题的关键词。如果老师没要求写上去属于明显的“超纲发挥”通常能看出你对新特性的掌握。4.6 经典面试题查询“天罡地煞”在各阵营的分布还有个很经典的“行转列”思路可以用在作业里SELECT camp_id, SUM(CASE WHEN rank_no 36 THEN 1 ELSE 0 END) AS tian_gang_cnt, SUM(CASE WHEN rank_no 36 THEN 1 ELSE 0 END) AS di_sha_cnt FROM hero GROUP BY camp_id;这条SQL用CASE WHEN把“是否天罡”这个判断变成0或1再SUM聚合得到每个阵营的天罡人数和地煞人数。它展示了条件聚合的写法比单纯写COUNT(*)更有区分度。5. 实操中的问题和排查记录5.1 中文乱码罪魁祸首不只是表最经典的问题是表建好了插入中文后select出来全是问号。很多人第一时间怪表其实乱码可能出在三个地方建库时没指定utf8mb4、连接的字符集不是utf8mb4、SQL文件本身保存成了其他编码。我的排错顺序是先执行SHOW CREATE TABLE hero;看表是否utf8mb4再执行SELECT character_set_client;看连接字符集最后检查SQL文件保存编码推荐用无BOM的UTF-8。必要时在连接后执行一次SET NAMES utf8mb4;MySQL 8.0默认字符集虽然已经是utf8mb4但一些老客户端工具仍可能以latin1连接所以这一句写进脚本里能省不少事。5.2 中文排序想按拼音排序怎么办默认情况下utf8mb4_general_ci对中文排序用的是Unicode码位顺序不是拼音顺序。如果想按姓名拼音排序可以把中文字段转成GBK再排序SELECT name FROM hero ORDER BY CONVERT(name USING GBK);GBK编码对常用汉字排序时结果基本等同于拼音顺序。这个方法适合“按姓氏拼音输出好汉名单”这种需求作为作业的小加分项很讨巧。但要注意如果有生僻字转换GBK可能失败暂时会显示NULL实际作业里影响不大。5.3 SQL文件保存编码导致“Incorrect string value”我踩过的坑之一是在Windows记事本里把SQL文件保存成ANSI编码结果执行INSERT时直接报错ERROR 1366 (HY000): Incorrect string value因为MySQL期望收到utf8mb4的字节流收到GBK/ANSI字节流后无法正确解码汉字。解决办法很简单用VSCode、Notepad等工具把文件另存为UTF-8 with or without BOM都会比较省事。在VSCode右下角可以看到当前编码手动切到UTF-8再保存即可。5.4 only_full_group_by报错MySQL 5.7及以上默认开启了sql_modeonly_full_group_by如果你写SELECT name, camp_id, COUNT(*) FROM hero GROUP BY camp_id;会直接报错因为name没有包含在GROUP BY子句中也没有被聚合函数包裹。这个报错不是数据库坏了而是SQL本身不符合标准。我的建议是不要为了省事去修改sql_mode更不要关掉only_full_group_by。这样会把逻辑错误掩盖掉考试或面试时容易吃亏。正确做法是如果确实需要某个非聚合字段要么把它加入GROUP BY要么用MAX(name)这类聚合要么干脆去掉。5.5 数据重复与唯一性约束如果插入数据时不注意或者后续又执行了多次插入可能出现重复的好汉比如两个“李逵”。排查重复数据可以用SELECT rank_no, COUNT(*) AS cnt FROM hero GROUP BY rank_no HAVING cnt 1;HAVING是用来筛选分组结果的和WHERE不同后者在分组前过滤前者在分组后过滤。如果作业里要求“保证排名唯一”其实应该靠UNIQUE约束在源头挡住而不是等到查出来再清理。所以我建议在设计表时就加上UNIQUE。5.6 事务未提交导致后续操作卡住如果在命令行或客户端执行了START TRANSACTION;插入一大堆数据后没有执行COMMIT;就关掉窗口可能出现表被锁住或者后续操作一直卡住的情况。遇到这种情况用SHOW PROCESSLIST;看看有没有长时间处于Sleep状态或Waiting for table metadata lock的连接如果有可以把对应会话KILL掉或者先执行COMMIT或ROLLBACK完成事务。这个经验虽然和题目没有直接关系但在实操中经常遇到值得记一下。6. 作业提交与加分项6.1 脚本组织三个文件分开执行顺序清楚交作业时最怕的是老师拿到一个巨大无比的SQL文件分不清哪些是建表、哪些是插数据、哪些是查询。我建议把项目目录组织成这样shuihu_assignment/ ├── 01_create_db.sql ├── 02_insert_data.sql ├── 03_queries.sql └── readme.md01脚本负责建库建表02脚本负责插入数据03脚本负责跑各类查询。每个脚本开头写清楚执行顺序。readme里写明环境要求比如“MySQL 8.0字符集utf8mb4先执行01再执行02最后执行03”。这种工程感在评分时很有优势。6.2 注释让每条SQL都有“解释自己”的能力代码注释不要写废话比如这句就毫无价值-- 查询数据 SELECT * FROM hero;要写的是“业务目的”-- 查询梁山排名前10的英雄用于验证排名字段唯一性 SELECT name, nickname, rank_no FROM hero ORDER BY rank_no LIMIT 10;老师批改时看注释就能知道你是不是真理解每条SQL的作用这种注释习惯对以后写复杂SQL也有巨大帮助。6.3 进阶加分项视图、存储过程如果学有余力可以加一个视图让后续查询更简洁CREATE VIEW v_hero_camp AS SELECT h.name, h.nickname, h.star_name, h.rank_no, c.camp_name, h.force_score FROM hero h JOIN camp c ON h.camp_id c.camp_id;创建之后查询天罡36将可以简化成SELECT * FROM v_hero_camp WHERE rank_no 36 ORDER BY rank_no;存储过程也可以写一个“按排名查英雄”就是很好的例子DELIMITER // CREATE PROCEDURE sp_get_hero_by_rank(IN p_rank TINYINT UNSIGNED) BEGIN SELECT name, nickname, star_name, force_score FROM hero WHERE rank_no p_rank; END// DELIMITER ;调用时CALL sp_get_hero_by_rank(14);这会返回排名第14位的英雄。这种封装思路和实际业务中的接口设计很接近能在作业里体现你对“逻辑复用”的理解。6.4 演示文档与结果展示技巧查询结果如果只是命令行里一堆加号表格自己看都吃力。我的建议是把每个查询的“业务问题、SQL、执行结果”整理成文档结果直接转成Markdown表格。实现方式很简单在客户端里执行查询复制结果到在线工具转成Markdown再粘贴到readme。每个章节前加一小段说明比如“本部分用于统计各阵营的天罡地煞人数分布”整体效果会非常清晰。最后说点个人体会以前我总觉得数据库作业就是写几个SQL糊弄过去。可真把这个“水浒传英雄人物”当成一个从零开始的小项目来做才发现建表、插数据、写查询这三步里藏着大量细节char和varchar怎么选、utf8mb4和中文排序怎么处理、JOIN和GROUP BY怎么组合、窗口函数在分组排名里怎么用。如果你正在做类似的作业我的建议是别急着抄网上现成的108将脚本先自己把需求拆清楚再动手建表和插数据。哪怕只做天罡36人也比直接复制完整脚本有价值得多。这个题材后续还能扩展成“梁山好汉战斗力排行榜”“各派系实力统计”“随机抽取一名好汉”等功能当成自己练手的小项目收获会远比作业分数大。