《数据库原理及应用》实验二个人版是我最近独立完成的一个小项目内容不复杂但要把 SQL 写对、把结果跑稳里面的门道比想象中要多。尤其是个人独立完成实验时没人帮忙debug所有坑都得自己踩一遍再爬出来。这篇东西就是把我完整的实操过程、思路拆解和踩坑记录整理出来给同样要做这类实验的同学一个可以直接参照的路线也让打算复习 SQL 的同学能快速找回手感。这个实验的核心是数据库的建表、插数据和 SQL 查询涉及单表查询、多表连接、子查询、聚合统计这些最基础也最常用的操作。看起来就是写几条 SELECT 语句实际动手才会发现表结构设计得不合理后面查询怎么写都不对数据插得不够典型聚合结果就看不出规律甚至大小写、引号、编码问题都能卡你半小时。整篇内容我会按实验环境准备、表结构设计、数据填充、查询实操、问题排查这个顺序来展开每段都会配上我实际用过的 SQL 语句和踩坑教训可以直接复制去用。1. 实验任务拆解与环境准备1.1 先搞清楚实验二到底要考什么动手之前我先把实验说明书从头到尾读了两遍。实验二这一类任务在数据库课程里通常都围绕 SQL 语言展开核心目标不外乎三块内容第一掌握数据库和表的创建方法包括数据类型选择、主键设置、默认值约束和外键关联。这部分对应的是 DDL数据定义语言是后续所有操作的基础。第二掌握数据的插入、修改和删除也就是 DML数据操纵语言重点是理解数据完整性的含义——主键不能重复、外键必须存在、非空字段不能留空。第三掌握各种形式的 SELECT 查询从最基本的条件筛选、排序到多表连接、嵌套子查询和 GROUP BY 聚合统计最后一般还会有一个视图或索引的小练习。我这次的实验题设计了一个模拟的课程选课系统包含学生表、课程表和选课记录表三张表。之所以选这个场景是因为它能很好地覆盖上述所有知识点学生和课程是一对多的关系选课记录把两个主表关联起来形成典型的多对多关系。这种结构在真实系统里极为常见把这三张表玩明白以后处理大部分业务数据都不会怵。1.2 个人版 vs 团队版一个人做实验的资源规划网上很多实验指导都是按小组分工设计的但我这次明确是个人版意味着所有环节都得自己搞定。个人做实验有好处也有麻烦。好处是思路连贯不会出现队友写了一半代码、另一个人看不懂的情况麻烦是时间成本高每一步都可能卡住而且一旦卡住没人可以商量只能靠搜索引擎和自己推理。资源规划上我建议至少准备两样东西一个用于运行环境的时间预算一个用于记录问题的笔记本。我的时间分配大致是这样的环境搭建和验证 30 分钟表结构设计和建表语句 45 分钟数据插入和检查 30 分钟查询练习和调优 90 分钟最后写实验报告和复盘 60 分钟。这套分配可以灵活调整但有一个原则——查询阶段必须留足时间因为那是实验分值最重的部分。工具选型也要提前想清楚。我用的组合是 MySQL 8.0 社区版搭配 DBeaver 客户端前者作为数据库服务端后者负责日常连接、编写 SQL 和查看结果集。选 MySQL 是因为它免费、跨平台、教材覆盖度高、网上资料最多。DBeaver 则是个人用下来最顺手的通用客户端支持自动补全、结果集可视化、ER图展示对排查表结构关系很有帮助。提示如果你用的是学校机房预装的环境别急着动手先确认数据库版本和服务是否启动正常。MySQL 5.7 和 8.0 在部分语法比如窗口函数、WITH 子句上支持程度不同实验内容以兼容 5.7 为主但如果你本机是 8.0就能额外尝试一些更现代的写法。2. 表结构设计与初始化细节2.1 三张表怎么设计才合理我先画出简单的逻辑模型再做物理设计。学生表负责存学生基本信息字段包括学号、姓名、性别、出生日期、所在系。课程表负责存课程信息字段包括课程号、课程名、学分、授课教师。选课表记录学生选修课程的情况字段包括学号、课程号、成绩。设计的时候有几个关键决定。学生表的主键用学号字符型长度设为 10因为学号通常不是纯数字可能包含前缀字母而且即使是纯数字用字符串类型也能避免前端显示时丢掉前导零课程表主键用课程号字符型长度 6选课表采用联合主键学号、课程号既能保证一个学生对同一课程只有一条选课记录又能通过这两个字段分别关联到学生表和课程表实现外键约束。性别字段我设置了 CHECK 约束限定只能填男或女学分字段限定在 1 到 6 之间成绩字段限定在 0 到 100 之间。MySQL 8.0 之后的版本会真正执行 CHECK 约束5.7 及更早版本则只是解析但忽略这点要注意不要以为写了约束数据就一定合法插入数据时还得自己把关。建表语句如下顺序很关键先建学生表、课程表最后建选课表因为选课表的外键依赖前两张表。如果顺序反了MySQL 会直接报错找不到被引用的表。CREATE TABLE student ( sno VARCHAR(10) PRIMARY KEY, sname VARCHAR(20) NOT NULL, ssex CHAR(2) NOT NULL, sbirth DATE, sdept VARCHAR(30) ); CREATE TABLE course ( cno VARCHAR(6) PRIMARY KEY, cname VARCHAR(40) NOT NULL, credit DECIMAL(2,1) NOT NULL, cteacher VARCHAR(20) ); CREATE TABLE sc ( sno VARCHAR(10), cno VARCHAR(6), score DECIMAL(5,2), PRIMARY KEY (sno, cno), FOREIGN KEY (sno) REFERENCES student(sno), FOREIGN KEY (cno) REFERENCES course(cno) );有一点我特别想提醒成绩字段用 DECIMAL(5,2) 而不是 INT。虽然实验里的成绩基本都是整数但 DECIMAL 能表达小数成绩比如 89.5实际考试中也可能有 0.5 分的情况而且在后续做 AVG 聚合时DECIMAL 的精度处理比 INT 更自然不需要额外 CAST。2.2 插入测试数据数据要typical不要random表建好之后就是填充数据。很多同学这一步随便敲几条记录进去觉得够用就行。但实验结果是否漂亮很大程度上取决于测试数据有没有代表性。我在插数据前先想好了要覆盖的场景每个系至少两名学生、每门课程至少三个人选、成绩分布要包含优秀、良好、及格和不及格还要有至少一名学生选了全部课程、至少一门课程没有任何人选。这样的分布是为了让每一种查询都有意义。比如查平均分最高的课程如果所有课程都有人选这个查询才公平查选修了全部课程的学生如果选课数据不够全结果大概率是空集反而看不出写对了没有。插入语句我按学生、课程、选课的顺序执行。学生表我插了 6 条记录覆盖计算机系、数学系、外语系三个系男女各半。课程表插了 4 门课其中一门学分较大、一门无人选修。选课表插了 10 条记录其中一名学生选了全部四门课程成绩有高有低还有一名学生选了两门但其中一门缺考——也就是成绩字段为空这种情况对后续 IS NULL 查询很有用。INSERT INTO student VALUES (2023001001, 张三, 男, 2004-03-12, 计算机系), (2023001002, 李四, 女, 2004-07-21, 计算机系), (2023002001, 王五, 男, 2003-11-05, 数学系), (2023002002, 赵六, 女, 2004-01-18, 数学系), (2023003001, 钱七, 男, 2005-06-30, 外语系), (2023003002, 孙八, 女, 2004-09-16, 外语系);插入后我立刻做了一次数据完整性检查用 SELECT COUNT(*) 分别统计三张表的行数确认和预设计数一致。这一步虽然简单但能尽早发现漏插、重复插入的问题别等到后面写查询时遇到诡异结果才回头看数据。3. 查询实操每一类SQL语句的写法与效果3.1 单表查询条件、排序与去重的正确姿势实验二最基础的部分是单表查询。单个表的 SELECT 查询要掌握 WHERE 条件过滤、ORDER BY 排序、DISTINCT 去重、LIKE 模糊匹配以及 LIMIT 分页。这些语句难度不大但容易在小细节上翻车。比如查看所有学生的信息并按出生日期从早到晚排序SELECT sno, sname, ssex, sbirth, sdept FROM student ORDER BY sbirth ASC;ASC 可以省略不写默认就是升序但写出来会更明确。如果我想看计算机系有哪些学生就加 WHERESELECT sno, sname FROM student WHERE sdept 计算机系;这里有个常见问题字符串比较是否区分大小写取决于数据库的排序规则。MySQL 默认的 utf8mb4_0900_ai_ci 是不区分大小写的所以 WHERE sdept 计算机系 没问题但如果字段是英文代码比如 dept_code CS那么写成 cs 也能匹配上。对这个特性不熟悉的同学在跨数据库迁移时容易懵Oracle 和 PostgreSQL 的默认行为就不同这点可以在实验报告里提一嘴能加分。模糊查询 LIKE 中% 代表任意多个字符_ 代表单个字符。比如查所有姓张的学生SELECT sno, sname FROM student WHERE sname LIKE 张%;注意如果学生名字里包含特殊字符下划线需要转义。比如要查名字里带下划线的学生不能直接写 LIKE _%因为那样会匹配任意单个字符再加任意后缀必须写成SELECT sno, sname FROM student WHERE sname LIKE %\_% ESCAPE \\;这个转义很容易被忽略属于实战中很高频的坑。DISTINCT 去重则是配合查询某个列的唯一值场景比如查看一共有几个系SELECT DISTINCT sdept FROM student;查出来三行就对了。3.2 多表连接查询内连接、外连接的语义差异多表连接是实验二的重头戏也是很多同学第一次接触关系型数据库核心魅力的地方。连接的本质是把两张表或多张表的行按照某个关联条件拼在一起。我这次实验的三张表刚好能组成一个完整的连接链路选课表同时关联学生表和课程表三表连接就是把学生选了什么课、课是谁教的、考了多少分一次全查出来。内连接INNER JOIN只返回满足连接条件的记录。比如查询每个学生的选课情况和成绩SELECT s.sno, s.sname, c.cname, sc.score FROM student s INNER JOIN sc ON s.sno sc.sno INNER JOIN course c ON sc.cno c.cno ORDER BY s.sno, c.cno;这里的 s、c、sc 是表别名写别名能让 SQL 简短也避免同名字段比如多个表中都有 sno产生歧义。不用别名也得用全限定名去引用否则会报 ambiguous column 的错误。左外连接LEFT JOIN则保证左表的每一行都会出现在结果里即使右表没有匹配的记录右侧字段显示为 NULL。比如我想知道“每个学生选了几门课没选课的学生也要显示出来”这时候就必须用 LEFT JOINSELECT s.sname, COUNT(sc.cno) AS course_count FROM student s LEFT JOIN sc ON s.sno sc.sno GROUP BY s.sno, s.sname;如果这里用了 INNER JOIN没选课的学生会被直接过滤掉统计结果就会缺失一行。很多同学写这个查询时默认随手写 JOIN结果少了一条记录还浑然不觉这是我在批改同学代码时经常见到的问题。逻辑上「包含左表全部」的需求必须写成 LEFT JOIN 或 RIGHT JOIN 才能满足。3.3 子查询与聚合统计从嵌套到 GROUP BY 的进阶子查询是实验二里区分熟练度的分水岭。所谓子查询就是一个 SELECT 语句嵌套在另一个 SELECT 语句里面先算内层的再拿结果给外层用。比如查询所有选了“数据库原理”这门课的学生的姓名和成绩SELECT s.sname, sc.score FROM student s INNER JOIN sc ON s.sno sc.sno WHERE sc.cno ( SELECT cno FROM course WHERE cname 数据库原理 );这个写法用到了单行子查询内层只返回一个课程号。还有一种场景是返回多行这时要用 IN 而不是 比如查询选了任何一门课程的学生姓名SELECT sname FROM student WHERE sno IN (SELECT DISTINCT sno FROM sc);如果内层的 sc 表里有人选了多门课DISTINCT 保证返回的 sno 不重复IN 语义才正确。这两种子查询开头看似相似但在遇到多行结果时会直接报错错误信息是 Subquery returns more than 1 row这是新手最容易踩的坑。聚合统计则和 GROUP BY 紧紧绑定。GROUP BY 会把结果集按某个字段分组然后在每个组内做聚合计算。比如统计每个系的学生人数SELECT sdept, COUNT(*) AS stu_count FROM student GROUP BY sdept;值得注意的是SELECT 后面的字段要么出现在 GROUP BY 里要么被聚合函数包裹否则在大多数数据库里都会报错。SQL Server 和 PostgreSQL 对这一点管得很严MySQL 默认允许返回未被聚合的列但这样做出的结果没有意义甚至会误导。我实验里遇到一个典型查询统计每门课程的平均分和选课人数SELECT c.cname, COUNT(sc.sno) AS join_count, AVG(sc.score) AS avg_score FROM course c LEFT JOIN sc ON c.cno sc.cno GROUP BY c.cno, c.cname;这里 course 表是通过主键 cno 分组的cname 函数上依赖于 cno所以在 GROUP BY 里把 cno 和 cname 都放上完全合法且稳健。AVG 函数会自动忽略 NULL 成绩行如果想让 NULL 参与计算就要用 COALESCE 先转成 0这个细节在实验报告里可以作为额外知识点写上。4. 常见错误与排查技巧实录4.1 报错信息不完全等于问题方向做实验过程中我遇到过的报错基本能分成三类。第一类是语法错误错误提示通常会指向某个 SQL 关键字附近比如少了逗号、多了一个括号、字符串没加单引号。这类问题定位最简单把相关行复制出来肉眼检查即可。第二类是比较隐蔽的逻辑错误SQL 不会报错但结果集和预期不符比如上面说的 INNER JOIN 丢掉了没选课的学生、COUNT 统计出了比自己设想更大的数字。第三类是环境问题比如字符集导致乱码、端口被占用、服务没启动。我分享一个卡了我比较久的例子。当时想统计每个学生选修课程的总学分我写了这样的语句SELECT s.sno, SUM(c.credit) AS total_credit FROM student s, sc, course c WHERE s.sno sc.sno AND sc.cno c.cno GROUP BY s.sno;看起来没毛病但结果显示某个学生的总学分明显偏大。后来查了下原因——这个学生在选课表里有多条记录而课程表中刚好有一门课的学分数在 JOIN 时被多次累加其实逻辑是对的但我看错了成绩和数据。后来改用显式 JOIN 语法并加 HAVING 过滤才真正把逻辑理清楚。教训是老式逗号连接虽然能用但结构不清晰排查问题时不友好推荐统一使用 JOIN 关键字。4.2 定位查询结果异常的三板斧第一板斧是分步查询。把多表连接拆开先分别查询单表再用小范围条件逐步实验连接结果。比如发现某个 JOIN 结果多出几行可以把 ON 条件单独拿出来先查左表所有行、再查右表匹配了多少行两边一对比就能看出是哪一边出了问题。第二板斧是临时缩小数据范围加 LIMIT 5 或用 WHERE 限定几个已知学号用小数据集人工核对结果这样逻辑错误会一目了然。第三板斧是利用客户端的可视化功能DBeaver 可以直接查看表的数据和 ER 图把关联关系画出来看有时候比盯 SQL 文本更快发现问题。还有一个经验遇到 COUNT 或 SUM 结果对不上的时候先检查 NULL 值。COUNT() 会统计所有行COUNT(列名) 会忽略该列为 NULL 的行。同样的表这两个统计结果可能不同。实验里如果学校要求「统计选课人数」那你必须想清楚到底是统计选课记录的总数还是统计有多少不同的学生参与了选课前者用 COUNT()后者要 COUNT(DISTINCT sno)一字之差结论差距很大。4.3 数据库备份与错误恢复的保命操作我个人做过的最有价值的准备是在实验开始前用 mysqldump 把三张表的结构和数据完整备份了一次。操作方式很简单mysqldump -u root -p database_name backup.sql后来我在实验中途不小心误删了一条关键成绩记录正是因为有了备份才没有从头再来。恢复也简单mysql -u root -p database_name backup.sql这个习惯强烈建议保留。无论是课程实验还是以后工作只要你正在操作的数据不是一次性玩具都要有备份意识。你可能觉得一个实验而已没多大价值但这种肌肉记忆会在真正重要的时刻救你一命。注意mysqldump 备份前最好确认一下 MySQL 服务在运行而且备份命令执行过程中不要在客户端里做写操作否则备份出来的数据可能不一致。严谨的做法是先执行 FLUSH TABLES把内存中的数据写入磁盘再执行备份。5. 从实验延伸到实际场景的思考做完这个实验我最大的感受是SQL 这门语言不是背出来的是用出来的。实验二的内容看起来只是几个简单查询但它背后涉及的数据建模思想、约束设计、连接语义、聚合思维正是关系数据库之所以能统治企业级应用几十年的原因。一个能熟练处理三表连接和子查询的人面对真实业务里十几种表、上百万行数据的报表需求时至少不会手足无措。实验之外我还额外做了一些小扩展练习这里也分享一个我特别推荐的做法把实验数据导出成 CSV 或 JSON 格式然后写一小段 Python 脚本读取并做同样的统计。同样一份数据分别在 SQL 和 Python 里算出结果两边数一致说明逻辑真的对了。这个方法既练了 SQL又练了数据对比能力一举两得。当然也别把 SQL 想得太复杂。它本质上就是告诉数据库“我要什么”不需要关心“怎么取”这个思维转换是初学者最需要跨过的坎。很多人第一次接触 SQL 会不自觉地用程序员的循环思维去理解查询总想着“先遍历这个表再判断那个条件”但 SQL 是声明式的你只要把条件和关系描述清楚优化交给数据库引擎。实验二练的就是这种声明式思维越早适应后面学索引优化、事务隔离、查询计划时就越轻松。最后再分享一个小技巧。我在实验报告里给每个查询都加了一行注释写清楚这个查询的业务目的是什么比如“查每个系的学生人数用于统计系规模”。这看起来费事但写完报告复盘时注释能帮助快速回忆每条 SQL 的作用不用重新读一遍几十行代码去猜测意图。数据库课程以后的工作场景里代码注释和文档说明往往比代码本身更能体现专业度这就是从学生思维到工程思维的第一步转变。