简介数据库课后实验崔巍编著资源包为数据库课程配套的查询脚本合集面向高校学生适用于课后复习、实验参考与期末备考资源共五个文件全部为查询脚本压缩包仅五千字节轻巧易用。脚本贴合教材实验内容从建表、插入、更新、删除等基础操作入手逐步过渡到带过滤与排序的查询再扩展至多表连接、子查询、分组与过滤等常用查询技巧。数据库设计方面包含对第一、第二、第三范式的识别与分解练习并借助实体联系模型转换案例理解关系模式规范化事务处理部分通过开启事务、提交、回滚等语句演示原子性、一致性、隔离性和持久性同时介绍并发控制与死锁概念并涉及索引创建与查询优化相关实验。每条语句都可在数据库管理系统中直接运行便于对照教材观察结果并修正错误已有二百二十人学习下载适合正在学习数据库课程、希望提升动手能力的同学使用。1. 数据库课后实验到底在练什么先跑通环境再谈 SQL 手感第一次打开这份数据库课后实验资源时最常见的状态不是题目不会而是环境没起来。我见过一些同学卡在第一步装好了某个数据库软件却不知道实验里要求的是哪个版本也不知道哪些题需要写成脚本、哪些可以直接在命令行里跑出来。这套课后实验覆盖了从建库建表、增删改查、索引优化、事务处理到存储过程与触发器的完整链路本质是把数据库原理里的概念翻译成可运行的代码。适合第一次做数据库课程实验的初学者也适合需要复查实验完整度的自学者。看完这篇文章你能够照着顺序把环境搭起来把每类实验的标准写法落地并且提前避开那些最容易把结果带跑的坑。2. 建库建表实验环境选型、最小配置与可对照的落地脚本数据库课后实验的第一章往往是建库建表这部分的隐藏要求比表面上看起来要多。表面上是写几条CREATE TABLE实际上还涉及到存储引擎选择、字符集统一、主外键关系是否被正确约束、列类型是否和后续的插入数据匹配。如果不先把这些前提定下来后面做查询实验时会出现一批看似莫名其妙的结果差异。2.1 实验环境选型为什么多数课后实验默认 MySQL 8.x数据库课后实验资源里出现的示例大多以 MySQL 为默认环境原因不是只有 MySQL 能用而是它覆盖了课后实验需要的所有功能点同时安装和调试成本相对可控。MySQL 8.x 自带的默认字符集是utf8mb4对中文支持比较完整和实验里常见的“学生”“课程”“选课”这类中文字段名或中文数据兼容性更好。在动手之前先确认本地是否已经有一个可用的 MySQL 8.x我用一个最小检查命令来判断mysql --version如果输出版本号说明客户端可用接下来确认服务端是否在运行再决定是直接登录还是先启动服务。mysql -u root -p登录后建议先看几个关键参数字符集、排序规则、事务隔离级别。这三个参数决定了后面实验里很多“看起来没错但结果不对”的问题到底出在哪。SHOW VARIABLES LIKE character_set_server; SHOW VARIABLES LIKE transaction_isolation;character_set_server默认值在 MySQL 8.x 里通常是utf8mb4这能避免大量因字符集不一致导致的中文乱码。transaction_isolation默认是REPEATABLE-READ做事务实验时要特别注意因为这个隔离级别下两个会话同时修改同一行数据的行为和READ-COMMITTED有明显区别。如果本机安装的是 5.7 版本也不需要更换只要在建库时显式指定字符集后续实验同样能跑通。2.2 建库建表脚本从 ER 图到可执行 DDL绝大多数课后实验的第一道题是建立一个“学生-课程-选课”关系模式。这里最合理的做法是先用一段 DDL 把整个数据库结构一次建完而不是在命令行里一条一条地敲。下面这段脚本是一个可以直接对照着修改的模板CREATE DATABASE IF NOT EXISTS course_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; USE course_db; CREATE TABLE student ( sno CHAR(9) PRIMARY KEY, sname VARCHAR(20) NOT NULL, ssex CHAR(2) DEFAULT 男, sage SMALLINT, sdept VARCHAR(30) ) ENGINEInnoDB; CREATE TABLE course ( cno CHAR(4) PRIMARY KEY, cname VARCHAR(40) NOT NULL, cpno CHAR(4), ccredit SMALLINT ) ENGINEInnoDB; CREATE TABLE sc ( sno CHAR(9), cno CHAR(4), grade DECIMAL(5,2), PRIMARY KEY (sno, cno), CONSTRAINT fk_sc_sno FOREIGN KEY (sno) REFERENCES student(sno), CONSTRAINT fk_sc_cno FOREIGN KEY (cno) REFERENCES course(cno) ) ENGINEInnoDB;这段 DDL 的逻辑很直接先建数据库再建三张表。student表里sno用CHAR(9)是因为学号通常是固定长度使用VARCHAR反而会在比较时多一次类型转换。sage用SMALLINT而不是INT能减少存储开销也能避免某些实验环境里因为默认整数类型过宽导致的隐性类型问题。sc表用复合主键(sno, cno)保证同一个学生同一门课只能有一条记录。外键约束一定要显式加上因为后续的插入异常实验会用到这些约束。使用 InnoDB 引擎是这个场景下的推荐选择它支持事务、外键和行级锁。如果实验题里不要求验证 MyISAM 的特性就不要去换成 MyISAM否则在事务实验里会出现“执行了回滚但数据还在”的误导结果。建完表后可以用SHOW CREATE TABLE sc;检查外键是否有被正确记录这一步能让你在开始插数据前就确认表结构完全符合预期。2.3 数据初始化与三大注意事项课后实验通常会给一批样本数据学生表、课程表、选课表各若干行。初始化数据时要保持插入顺序先插父表再插子表。先插student和course再插sc否则外键约束会直接拒绝插入。下面这段插入脚本可以作为初始化的参考INSERT INTO student (sno, sname, ssex, sage, sdept) VALUES (20180101, 张三, 男, 20, CS), (20180102, 李四, 女, 19, CS), (20180103, 王五, 男, 21, MA); INSERT INTO course (cno, cname, cpno, ccredit) VALUES (C001, 数据库, NULL, 4), (C002, 数据结构, C001, 3), (C003, 操作系统, C001, 3); INSERT INTO sc (sno, cno, grade) VALUES (20180101, C001, 92.5), (20180101, C002, 85.0), (20180102, C001, 78.0);执行完插入后用SELECT COUNT(*) FROM student;核对各表行数是初始化阶段最稳妥的自检方式。这里有一个容易踩的细节cpno列代表先修课程它引用的还是course表自身的cno严格来说应该加一个自引用外键很多实验题的参考答案里并没有加但这不影响大多数查询实验。如果后面的实验要求做“先修课程”的自连接查询建议提前手动加上外键这样写连接条件时会更明确。3. 查询与索引实验从零基础 SQL 到 EXPLAIN 执行计划查询是数据库课后实验里题量最大的一章表面考的是 SQL 语法实际考的是对关系代数执行过程的理解。单表查询谁都能写两句但真正拉开差距的是多表连接、子查询、分组过滤这三类写法的执行效率。做实验时不要只满足于“结果对”每一步都要想清楚数据库到底是怎么把这条 SQL 跑出来的。3.1 查询实验的层级单表、连接、子查询与分组课后实验里的查询题通常按难度递进排列。最开始是单表查询比如查询年龄大于 20 的学生信息接着是连接查询比如查询选了“数据库”这门课的学生姓名再往后是子查询和分组查询。以连接查询为例最常见的标准写法如下SELECT s.sname, c.cname, sc.grade FROM sc JOIN student s ON sc.sno s.sno JOIN course c ON sc.cno c.cno WHERE c.cname 数据库;这段查询先把sc表分别与student表、course表建立连接再通过WHERE过滤出课程名为“数据库”的记录。关键点在于JOIN ON中的关联字段必须与表定义里的外键字段一致这里sc.sno对应student.snosc.cno对应course.cno。如果外键约束已经建好MySQL 会利用索引加速关联如果没建外键也没有在关联字段上建索引两个连接操作就会退化为全表扫描。分组查询是出现误解比较多的一类。看下面这个例子SELECT cno, AVG(grade) AS avg_grade FROM sc GROUP BY cno HAVING AVG(grade) 80;WHERE在分组前过滤HAVING在分组后过滤两者不能互换。这个实验里常犯的错误是在WHERE里写聚合条件结果数据库直接报错提示你不知道avg_grade这一列。AS别名在HAVING里可以引用在WHERE里不行这是 SQL 执行顺序决定的。写分组查询时建议每一条都先在脑子里过一遍“聚合发生在什么时候”。3.2 索引什么时候该建执行计划里的关键字段索引实验是“数据库课后实验”里的重头戏。建索引不是越多越好而是要在查询条件和连接字段上建。判断一个查询是否走了索引看执行计划里几个关键列type、key、rows。type从好到差大致是system、const、eq_ref、ref、range、index、ALL见到ALL就意味着全表扫描。对一个连接查询来说最理想的type是ref或eq_ref。下面这段操作先建立索引再查看执行计划的变化CREATE INDEX idx_sc_cno ON sc(cno); EXPLAIN SELECT c.cname, sc.grade FROM sc JOIN course c ON sc.cno c.cno WHERE sc.cno C001;执行EXPLAIN后查看key列是否变成idx_sc_cnotype是否从ALL变成refrows是否明显下降。rows表示 MySQL 估算需要扫描的行数数值越大通常执行越慢。如果type还是ALL可以先检查是不是sc表里数据量太少优化器认为全表扫描比走索引更快这是正常现象。想强制使用索引可以在表名后面加FORCE INDEX(idx_sc_cno)但实验报告里不建议这么写更好的做法是把数据量增多后再对比。3.3 用 EXPLAIN 验证索引是否命中课后实验里经常要求“给某字段建索引然后说明查询性能变化”。只贴一条CREATE INDEX命令是不够的要用EXPLAIN把前后变化量化出来。比较规范的操作是先跑一遍没有索引的执行计划记录type和rows建索引后再跑一遍记录同样两个字段。在 MySQL 8.0 以上版本还可以用EXPLAIN ANALYZE查看实际耗时EXPLAIN ANALYZE SELECT s.sname, c.cname FROM sc JOIN student s ON sc.sno s.sno JOIN course c ON sc.cno c.cno WHERE sc.grade 60;EXPLAIN ANALYZE会直接输出一个耗时说明里面包含每个步骤实际处理的记录数以及花费的时间。注意这套语法对版本要求较高如果实验环境是 MySQL 5.7只能使用普通EXPLAIN。在校验时还要关注一个细节索引不是建在 SELECT 后面的结果列上而是建在WHERE和JOIN ON涉及的条件列上。课后实验里常有人给sname建索引然后查询条件写的是sage最终执行计划完全没有变化这就是没分清索引的生效位置。4. 事务与存储过程实验把业务逻辑交给数据库的两个方向事务、存储过程和触发器是数据库课后实验里最容易出现“结果对但理解不对”的部分。这三个功能点都在解决同一个问题如何让数据库自己保证数据的一致性。事务负责把一组操作打包执行存储过程负责把一段可复用的逻辑固化在数据库内部触发器则负责在特定数据变化时自动执行动作。4.1 事务实验隔离级别、提交时机与回滚边界课后实验里的事务题通常会设计一个转账场景要求验证COMMIT与ROLLBACK的作用。最典型的做法是开两个终端会话一个执行UPDATE不提交另一个去查询同一行数据观察是否看到未提交的修改。这个场景能否复现取决于当前会话的隔离级别。下面这段是能在单个会话里演示事务回滚的最小脚本START TRANSACTION; UPDATE sc SET grade grade 5 WHERE sno 20180101 AND cno C001; SELECT * FROM sc WHERE sno 20180101 AND cno C001; ROLLBACK; SELECT * FROM sc WHERE sno 20180101 AND cno C001;第一次SELECT能看到成绩被修改第二次SELECT在ROLLBACK之后看到的又是原来的值。这段代码验证的是“事务未提交前修改对当前会话可见但回滚后全部撤销”。注意一点必须显式写START TRANSACTION不能依赖命令行自动提交。大部分终端默认autocommit1没有START TRANSACTION时每条UPDATE都会立即生效ROLLBACK也就没有回滚对象了。检查当前自动提交状态可以用SHOW VARIABLES LIKE autocommit;如果输出是ON建议在实验前显式执行SET autocommit0;避免漏掉事务边界导致回滚失效。做并发实验时开两个终端后要记得分别设置隔离级别并且确认两边查看的是同一个数据库和表否则很容易误以为“锁失效了”。4.2 存储过程实验参数、游标与异常处理存储过程的实验题一般要求写一个带输入参数和输出参数的过程比如根据课程号统计选课人数。这里的关键不只是过程能跑通还包括结束符的处理。MySQL 客户端默认把分号当作语句结束符而过程体内部也有分号所以要用DELIMITER先把结束符临时改掉。参考写法如下DELIMITER // CREATE PROCEDURE count_students_by_course( IN p_cno CHAR(4), OUT p_cnt INT ) BEGIN SELECT COUNT(*) INTO p_cnt FROM sc WHERE cno p_cno; END // DELIMITER ; CALL count_students_by_course(C001, result); SELECT result;IN p_cno是传入参数OUT p_cnt是输出参数。SELECT COUNT(*) INTO p_cnt是存储过程里常用的赋值方式把查询结果直接写进输出变量。调用时先用result这个用户变量接收再用SELECT查看。存储过程实验里容易翻车的地方主要在DELIMITER如果忘记改结束符执行CREATE PROCEDURE时会把整个过程体拆成多段语句导致创建失败。改完结束符后一定要恢复成默认分号否则后续 SQL 语句全部会带上奇怪的解析规则。4.3 触发器实验审计日志与约束兜底触发器是课后实验里比较难理解的一块原因在于它是被动的平时写 SQL 是“我让数据库做什么”而触发器是“数据发生变化时数据库自动做什么”。实验题里最常见的需求是给选课表加一个审计触发器把插入操作记录到日志表。核心代码如下CREATE TABLE sc_log ( log_id INT AUTO_INCREMENT PRIMARY KEY, sno CHAR(9), cno CHAR(4), op_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINEInnoDB; DELIMITER // CREATE TRIGGER trg_sc_after_insert AFTER INSERT ON sc FOR EACH ROW BEGIN INSERT INTO sc_log (sno, cno) VALUES (NEW.sno, NEW.cno); END // DELIMITER ;触发器里的NEW代表插入后的新记录NEW.sno和NEW.cno就是刚插入的数据值。如果想在删除时也记录日志再写一个AFTER DELETE触发器里面用OLD.sno和OLD.cno。做这个实验时最容易忽略权限问题如果 MySQL 开启了二进制日志创建触发器可能会报错提示log_bin_trust_function_creators权限不足。此时需要执行SET GLOBAL log_bin_trust_function_creators 1;授权这个操作在部分数据库版本里需要管理员权限。触发器验证方式很简单往sc表插入一条记录再去sc_log表看是否多出一行建议把验证步骤也写进实验报告比口头解释更直观。5. 数据库课后实验避坑区五个把结果带跑的常见问题数据库课后实验里的坑往往不是“不会写”而是“看着写对了结果一跑就偏”。下面这几条是我在实际复现这套实验时整理出的高频问题每一条都按现象、原因、解决三个层次列出可以当作排错清单来用。5.1 中文乱码现象、原因与复现排查现象向表里插入中文数据后用SELECT查询出来是一串问号或者乱码。原因客户端连接字符集与服务器端字符集不一致。建库时指定了utf8mb4但登录 MySQL 时没有指定连接字符集或者连接的客户端编码还是latin1中文字符在传输过程中就已经被截断。另一个常见原因是建表时没有指定DEFAULT CHARSETutf8mb4导致表沿用了数据库级别的旧字符集。解决先查看SHOW VARIABLES LIKE character_set_client;确认客户端字符集。在命令行或连接参数中显式加上连接字符集设置常见做法是在登录后执行SET NAMES utf8mb4;如果使用的是图形化客户端在连接编辑框里的编码一栏选择utf8mb4。建库建表时统一指定字符集和排序规则不要混合使用utf8与utf8mb4这两者虽然都能存中文但排序规则不同连接两个表时可能报字符集冲突的错误。5.2 TRUNCATE 与事务回滚改了表却救不回来现象在事务里执行TRUNCATE TABLE sc;随后ROLLBACK结果发现sc表的数据被清空回滚没有生效。原因TRUNCATE属于 DDL 操作DDL 会隐式提交事务。也就是说即使前面写了START TRANSACTION执行到TRUNCATE时事务已经终结后面的ROLLBACK没有任何可回滚的内容。这和DELETE这类 DML 操作完全不同。解决实验里要求验证回滚时清空表数据统一使用DELETE FROM sc;不要使用TRUNCATE。如果题目明确要求比较两者区别可以同时测试后再在报告里说明“TRUNCATE 不支持事务回滚”这个结论。这个坑在课后实验里出现频率很高不少人在写“删除表中的全部记录并要求可恢复”的题目时被拦下。5.3 COUNT 的 NULL 陷阱统计结果差一个现象对sc.grade做一个COUNT(grade)得到的结果比COUNT(*)少但实验预期是两者相同。原因COUNT(column)只统计该列非 NULL 的记录数而COUNT(*)统计表的全部记录数。只要有一条记录的grade是 NULL两个结果就不可能一致。课后实验给的数据有时会有意留几个 NULL专门验证考生是否理解这个语义差异。解决统计记录数时要明确实验要求的是“总行数”还是“某列非空数”。如果题目要求统计选课人数标准写法是COUNT(*)如果题目要求统计有成绩的人数才用COUNT(grade)。排查类似问题时先执行SELECT * FROM sc WHERE grade IS NULL;看看是不是有 NULL 行在捣乱再决定用哪个聚合函数。5.4 触发器只能建不能跑权限与日志模式问题现象CREATE TRIGGER执行成功但往源表插入数据时触发器不执行sc_log表里没有新增记录。原因权限配置导致触发器创建成功但运行受限或者触发器的表名和字段名写错比如在触发器内引用了一张不存在的表导致触发器执行时报错但INSERT事务被吞掉表面看不出来。还有一种偏隐蔽的原因是触发器名的前缀混淆比如误建了BEFORE INSERT触发器却在里面查询NEW.grade此时数值还不是最终值日志记录和预期不一致。解决先看触发器是否真的存在执行SHOW TRIGGERS;检查状态。确认无误后手动在触发器体内加一个SIGNAL语句做失败提示这样触发器执行异常时能直接看到报错信息。如果确认是权限问题按上一章的log_bin_trust_function_creators处理方案解决。做触发器实验时建议把触发器和日志表放在同一张逻辑结构下避免跨库引用带来的权限歧义。5.5 外键删除顺序删主表被拦住的完整流程现象删除student表里一条学生记录时数据库报错提示外键约束失败无法删除。原因sc表里有外键引用这条学生记录。删除主表记录时如果子表仍存在关联数据数据库会拒绝执行防止产生“孤儿数据”。这是关系完整性的正常保护不是异常。解决按依赖顺序先删除子表关联记录再删除主表记录。比较规范的实验操作如下DELETE FROM sc WHERE sno 20180102; DELETE FROM student WHERE sno 20180102;如果实验设计里要求“删除学生时自动清理选课记录”则需要给外键加上ON DELETE CASCADE并在建表时指定。课后实验最常见的考核点是让考生判断删除顺序所以不要省略子表清理这一步直接删主表。排查这类问题时用SHOW CREATE TABLE sc;查看外键定义能快速确认约束行为。6. 交实验前的自检技巧用元数据查询验证作业完整度实验做完不等于可以交。课后实验资源里通常有十几个题目最容易出现的情况是少建了一张索引、漏建了一个触发器、或者某张表里数据行数不对。手动一张表一张表地点开查看太慢更高效的做法是直接用元数据查询把所有对象一次性列出来。下面这段脚本可以查出一个数据库里的全部表SELECT table_name, table_rows, engine FROM information_schema.tables WHERE table_schema course_db ORDER BY table_name;information_schema.tables里的table_rows对 InnoDB 表是估算值不能当精确行数用。真正核对行数还是要用SELECT COUNT(*)。想检查外键和主键是否完整可以查table_constraintsSELECT table_name, constraint_type, constraint_name FROM information_schema.table_constraints WHERE constraint_schema course_db;输出结果里能看到PRIMARY KEY、FOREIGN KEY、UNIQUE三类约束逐一和实验要求对照比打开建表语句一条条看更省时间。检查索引是否生效时用SHOW INDEX FROM sc;查看Key_name和Cardinality。Cardinality数值很低说明该列重复度太高数据库优化器可能不会优先使用这个索引。我个人的经验是每次交实验前强制走一遍自检流程先查表清单再查约束清单最后随机抽一条连接查询和一条事务操作原地验证。这个方法帮我避免过不少“打开文件才发现少建了一张索引”的情况。希望这套数据库课后实验的完整推进过程能让你在做实验时少一点“玄学”多一点把结果握在手心里的踏实感。本文还有配套的精品资源点击获取