简介西北工业大学软件学院数据库实验五资源包面向正在完成电子商务数据库ER建模实验的本科生。资源围绕E-Commerce项目描述展开提供从概念模型设计到操作演示的完整内容覆盖注册、购物车、结算、订单、支付、历史订单等核心业务环节的数据库建模需求。压缩包共二十个文件约二百八十二KB包括十四个gif操作录屏、两个txt说明、两个doc实验文档、一个cdm概念数据模型文件和一个htm文件gif可直观复现各界面操作流程doc与txt用于整理实验要求与ER建模思路cdm文件则便于对照检查概念结构。目前已有九百一十人学习适合西北工业大学软件学院学生对照完成实验五、理解ER图绘制及数据库概念建模方法。资源内附ER图与说明文档可直接对照实验报告帮助梳理电商平台的实体、属性和联系也可为复习数据库设计提供参考。1. 软件学院数据库实验五的压缩包先别急着写 SQL软件学院数据库实验五的压缩包在课程群里出现的那一刻多数人的第一反应是解压、打开实验指导书、对着题目敲 SQL。我见过太多人栽在这个顺序上本地数据库版本和实验环境对不上、数据脚本没导全、触发器把正常业务操作连带弄崩最后提交的压缩包里还混着一堆 IDE 配置文件——扣的分全在实验之外。这篇笔记按我自己的实战习惯把实验五从解压到提交的完整路径拆一遍重点落在存储过程、触发器、事务这些高频考核点上以及每次都会有人翻车的细节上。适合正在赶实验进度的同学也适合想搞明白这类实验到底在考什么的自学者。2. 数据库实验五的通行考核点存储过程、触发器与事务为什么总绑在一起2.1 为什么实验五通常落在存储过程和触发器上课程节奏与知识依赖软件学院数据库课程的前四个实验通常依次覆盖建表与约束、增删改查、多表连接查询、视图与索引。到实验五课程重心从用 SQL 查数据转向把业务逻辑写进数据库也就是存储过程、函数、触发器与事务。这个节点选得很实际存储过程和触发器能演示数据库从存储引擎变成可编程平台的那一步而且结果确定、可测试批量评分时也好验证。实验五的压缩包解压后常见结构是四类东西实验指导书负责题目要求、表结构和评分标准建表与初始数据脚本通常叫 schema.sql 和 init_data.sql若干带 TODO 标记的 SQL 模板文件一份报告模板。我拿到包之后的第一步不是读指导书正文而是先看报告模板——报告模板里列的截图和思考题就是评分表的倒影。如果报告要求贴触发器执行前后统计表对比那触发器就是必做项偷懒不得。实验包里的 SQL 模板文件一般带 TODO 注释那是出题人留的白也是给分点。看到 TODO 不用紧张把它当成这里需要补全的标注完成一个就删一个注释最后文件里没有任何 TODO你也能确认自己全部完成。这个习惯能帮你建立完成度概念比对着指导书猜全不少。如果指导书的环境要求栏写着存储过程 触发器 事务那实验五的考核主线基本锁死了。这一章的三个交付物就很明确一个能跑的存储过程并展示调用结果、一个能演示效果的触发器、一段能讲清楚隔离级别和锁的事务代码外加一份把这些东西串起来的报告。下面按这条主线展开。2.2 从实验指导书里反推评分点三个必看位置指导书不用通读三个位置必看实验目的、实验内容、考核方式。实验目的决定考点范围实验内容决定任务数量考核方式决定你要做到什么程度——是现场演示还是只看报告。现场演示的话存储过程必须能在干净环境里一次跑通。我见过不少同学报告写得无懈可击演示时因为数据被之前测试搞乱过程调用直接报错。指导书里的典型表述实际要准备的验证动作创建存储过程查询学生选课信息用有选课记录和无选课记录的学生各调用一次展示两种结果调用带输出参数的存储过程用会话变量接收 OUT 参数SELECT 出来并截图创建触发器维护统计表对主表执行一次 INSERT再查统计表展示计数变化事务提交与回滚分别走 COMMIT 和 ROLLBACK 两条路径对比前后数据这四条就是实验五里最常做的事。动手前先把指导书里的任务按这种表格拆一遍你会发现所有任务都能落成执行什么 SQL、期望什么结果的验证动作。先拆再写代码而不是边看边写返工会少很多。另外注意表结构。有的指导书直接给建表语句有的只给 E-R 图需要自己转成表结构。我一般会先看主键外键和字段类型再动笔——student_id 是 VARCHAR(20) 还是 INT直接决定存储过程参数怎么写。参数类型和表结构对不上时MySQL 8.0 会因为隐式转换报错或者结果对不上这种问题排查起来最浪费时间。还有个环境细节表名大小写是否敏感取决于服务器的 lower_case_table_names 参数。Windows 下 MySQL 默认大小写不敏感Linux 下默认敏感。照着指导书里的表名抄大小写差一个字母导入时可能报 1146 表不存在这不是实验题难是环境细节。实验五的题目还经常是存储过程 校验 事务三合一比如选课过程里要判断课程容量满了返回提示否则插入选课记录并扣减容量。这种综合题不要一口气写完先写只插入不判断的版本测通再单独测容量判断最后合并。另外指导书末尾的思考题比如为什么选课场景需要事务触发器和存储过程有什么区别不写不影响基础分但写了且言之有物通常能进报告加分。把思考题当一次小型答辩用一两段话说清楚原理比堆截图有用得多。3. 本地环境与数据导入把实验五的数据脚本跑起来的完整操作3.1 先选数据库版本跟随教材还是跟随实验室实验包在建的时候绑定了一个数据库环境指导书环境要求那一栏通常会写。最常见的是 MySQL 5.7 或 8.0也有用 SQL Server 或 MariaDB 的。我的建议是以指导书为准其次是实验室能用的版本最后才是你顺手装的版本。如果本地是 8.0、指导书基于 5.7 写的绝大多数脚本没问题但要注意默认字符集和认证插件的差异反过来指导书明确写 8.0 的语法你在 5.7 上跑个别写法可能不支持。指导书环境说明本地建议理由MySQL 5.7装 5.7 或 8.0 均可实验五涉及的存储过程、触发器语法两者基本兼容MySQL 8.0装 8.0避免 5.7 上不支持的写法未写明装 8.0当前主流默认 utf8mb4字符集踩坑少如果实验室装的是 MariaDB也不用慌本实验级别的存储过程和触发器语法与 MySQL 几乎一致差异基本碰不到。真正的坑不在选哪个版本而是选完之后没在同一个版本上跑完整个实验——报告里贴的截图如果混用了 5.7 和 8.0 的会话变量名老师一眼就能看出来不是同一个环境跑的。3.2 导入数据脚本命令行重定向与 source 命令两种方式导入前先看脚本开头。schema.sql 里如果已经带了 CREATE DATABASE 和 USE 语句直接重定向进去如果只有 CREATE TABLE就得先手动建库再导。判断方式很简单用任意编辑器打开脚本看前 20 行有没有建库和切换库的语句。# 脚本不带建库语句时先建库再分步导入 mysql -u root -p -e CREATE DATABASE IF NOT EXISTS exp5_db DEFAULT CHARSET utf8mb4; # 导入表结构 mysql -u root -p --default-character-setutf8mb4 exp5_db schema.sql # 导入初始数据 mysql -u root -p --default-character-setutf8mb4 exp5_db init_data.sql这里 -p 后面不跟密码回车后交互输入是为了避免密码出现在 shell 历史记录里。--default-character-set 显式指定 utf8mb4可以避免中文字段在导入时变成乱码。另一种方式是进到客户端里用 source-- 在 mysql 命令行客户端里执行路径写绝对路径最稳妥 USE exp5_db; SOURCE /path/to/schema.sql; SOURCE /path/to/init_data.sql;两种方式的差别在于命令行重定向适合整个流程跑通之后做一键复现source 适合第一次导入时用因为客户端会逐条显示语句和报错位置哪一行出错立刻能看到。我第一次导入数据脚本时永远用 source确认无误后再把命令保存成脚本。Windows 上还有个常见的编码坑zip 解压出来的 sql 文件是 GBK 编码直接导入时中文注释或中文数据会报错。解决办法有两个要么把文件另存为 UTF-8 再导要么导入时指定 gbk 编码# 怀疑脚本是 GBK 编码时先尝试用 gbk 编码导入 mysql -u root -p --default-character-setgbk exp5_db init_data.sql我一般推荐另存为 UTF-8。因为后续你要在 GUI 工具里反复编辑这些脚本统一成 UTF-8 可以避免编辑器里看着正常、一导入就乱码的问题。这个现象看起来像玄学实际就是字符集不一致。3.3 验证导入结果数据完整性的三查导入完成后不要急着写存储过程先花两分钟验证数据。我把这一步叫三查查表清单、查行数、查外键完整性。-- 1) 表清单对照指导书的表名少一张都说明建表脚本没导全 SHOW TABLES; -- 2) 行数对照指导书实验数据说明里的初始行数 SELECT COUNT(*) AS student_cnt FROM student; SELECT COUNT(*) AS course_cnt FROM course; SELECT COUNT(*) AS sc_cnt FROM sc; -- 3) 外键完整性sc 里有没有查不到学生的孤儿记录 SELECT COUNT(*) AS orphan_rows FROM sc s LEFT JOIN student stu ON s.student_id stu.student_id WHERE stu.student_id IS NULL;第一查防止脚本漏导第二查防止重复导入或导入中断第三查最容易被忽略。有些数据脚本为了避开导入顺序问题会在开头执行 SET FOREIGN_KEY_CHECKS0如果中途出错部分外键数据就丢了但报错被吞掉后面跑 JOIN 和触发器时结果怎么看怎么不对。这就是典型的数据看起来正常、实验全对不上的来源。我一般会把这三条验证语句存成一个 verify.sql 文件每次重新导完数据就执行一遍。实验做到后面数据库被折腾乱了重建再导然后跑一遍 verify.sql确认干净了再开始下一步。这个习惯能省下大量排查时间。4. 数据库实验五核心代码存储过程、触发器与事务的可复现写法4.1 存储过程从模板到能跑的最小改动以最常见的题目为例创建存储过程输入学生学号输出该学生已选课程的总学分和选课门数没有选课记录时返回 0 而不是 NULL。USE exp5_db; -- 把分隔符切成 //防止过程体里的分号被当成语句结束 DELIMITER // CREATE PROCEDURE sp_student_credit( IN p_stu_id VARCHAR(20), OUT p_credit DECIMAL(5,1), OUT p_count INT ) BEGIN SELECT COALESCE(SUM(c.credit), 0), COUNT(1) INTO p_credit, p_count FROM sc LEFT JOIN course c ON c.course_id sc.course_id WHERE sc.student_id p_stu_id; END // -- 恢复默认分隔符 DELIMITER ; -- 调用过程用会话变量接收出参 SET sid 2024001; CALL sp_student_credit(sid, credit, cnt); SELECT sid AS student_id, credit AS total_credit, cnt AS course_count;这里的核心细节有三个。第一DELIMITER 必须成对出现先切走再切回来否则客户端会把过程体里第一条语句的分号当成 CREATE PROCEDURE 的结束报 1064。第二LEFT JOIN 加 COALESCE 而不是普通 JOIN因为没选课的学生 SUM(c.credit) 是 NULL不兜底的话出参就是 NULL报告截图里出现 NULL 会被判定边界情况处理不到位。第三OUT 参数一定要用会话变量接收调用完再 SELECT 出来直接在 CALL 语句里是看不到出参值的。如果题目上升到综合题比如选课过程判断课程容量满了返回提示否则插入选课记录并扣减容量常见写法是这样DELIMITER // CREATE PROCEDURE sp_enroll_course( IN p_stu_id VARCHAR(20), IN p_course_id VARCHAR(20), OUT p_msg VARCHAR(100) ) BEGIN DECLARE v_capacity INT; -- 读取当前容量注意这个版本没有处理并发先保证逻辑正确 SELECT capacity INTO v_capacity FROM course WHERE course_id p_course_id; IF v_capacity IS NULL THEN SET p_msg course not found; ELSEIF v_capacity 0 THEN SET p_msg course full; ELSE INSERT INTO sc(student_id, course_id) VALUES (p_stu_id, p_course_id); UPDATE course SET capacity capacity - 1 WHERE course_id p_course_id; SET p_msg enroll ok; END IF; END // DELIMITER ; CALL sp_enroll_course(2024001, CS001, msg); SELECT msg;IS NULL 分支判断课程不存在的情况这一支写出来边界情况就齐了。这个版本能跑通但没有处理并发选课的问题——真正的并发安全要套一层事务并用行锁4.3 会展开。综合题的正确拆法是先把插入和扣减跑通再补容量判断最后套事务一步到位反而难排查。4.2 触发器两个高频业务场景的完整代码触发器题目常见的有两类一类是维护统计表一类是限制非法操作。先看维护统计表要求向 sc 表插入一条选课记录后自动更新课程统计表 course_stat 中的选课人数。-- 前提course_stat 表已存在且每个课程号都有初始行 DELIMITER // CREATE TRIGGER trg_sc_after_insert AFTER INSERT ON sc FOR EACH ROW BEGIN UPDATE course_stat SET stu_count stu_count 1 WHERE course_id NEW.course_id; END // DELIMITER ; -- 验证插入一条选课记录再查统计表 INSERT INTO sc(student_id, course_id) VALUES (2024001, CS001); SELECT * FROM course_stat WHERE course_id CS001;NEW 代表 INSERT 之后产生的新行NEW.course_id 就是刚插入的课程号。这里要注意如果 course_stat 表里没有对应课程号的初始行UPDATE 影响 0 行不会报错但计数不变看起来就像触发器没生效。所以建触发器前先 SELECT 一下统计表有没有初始数据。触发器里要养成的第一个意识是触发器的失败会连带主操作失败。也就是说 INSERT INTO sc 触发触发器触发器里 UPDATE 出错整条 INSERT 也会失败。所以写完触发器不能只看创建成功必须实测一次主操作。第二类是限制非法操作比如删除课程时有选课记录则禁止删除DELIMITER // CREATE TRIGGER trg_course_before_delete BEFORE DELETE ON course FOR EACH ROW BEGIN DECLARE v_cnt INT; SELECT COUNT(1) INTO v_cnt FROM sc WHERE course_id OLD.course_id; IF v_cnt 0 THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT cannot delete: sc records exist; END IF; END // DELIMITER ; -- 验证尝试删除有选课记录的课程应该被拒绝 DELETE FROM course WHERE course_id CS001;OLD 代表被删除前的行。SIGNAL 是主动抛错SQLSTATE 45000 是用户自定义错误的通用代号后面的 MESSAGE_TEXT 会在客户端显示成提示信息。这条 DELETE 会被中断并回滚课程记录还在。这个写法在 MySQL 5.5 之后的版本都支持实验环境一般没问题。注意MySQL 5.7 限制同一张表在同一触发时机和同一事件上只能有一个触发器。也就是说 AFTER INSERT 触发器只能建一个建第二个会直接报错。8.0 允许建多个。如果你在 5.7 上做实验多条逻辑要写进同一个触发器的体内而不是拆成两个。4.3 事务与隔离级别报告里最容易被追问的一问事务题在实验五里一般不单独出而是藏在综合题里比如选课系统。考核的核心不是 START TRANSACTION 和 COMMIT 的语法而是隔离级别和锁。报告里只贴事务语句会丢一半分。-- 查看当前隔离级别写进报告 SHOW VARIABLES LIKE transaction_isolation; -- MySQL 8.0 SHOW VARIABLES LIKE tx_isolation; -- MySQL 5.7 及更早 START TRANSACTION; -- 锁定课程行防止并发事务读到同一个 capacity SELECT capacity FROM course WHERE course_id CS001 FOR UPDATE; INSERT INTO sc(student_id, course_id) VALUES (2024999, CS001); UPDATE course SET capacity capacity - 1 WHERE course_id CS001; COMMIT;SELECT ... FOR UPDATE 拿的是行级排他锁另一个事务执行同样的查询会阻塞等待直到当前事务 COMMIT 或 ROLLBACK 释放锁。这就能保证两个并发选课请求不会同时读到 capacity1然后都去插入选课记录。MySQL 8.0 默认隔离级别是可重复读 REPEATABLE READ配合行锁这个场景是安全的。回滚的展示也很重要START TRANSACTION; -- 故意插入一条外键不存在的选课记录模拟业务中途失败 INSERT INTO sc(student_id, course_id) VALUES (999999, CS001); UPDATE course SET capacity capacity - 1 WHERE course_id CS001; -- 发现插入有误主动回滚两张表都回到事务前状态 ROLLBACK; SELECT COUNT(*) FROM sc WHERE student_id 999999; -- 应该返回 0在数据库实验五里事务代码的评判标准通常不是能跑而是能讲。老师追问时你要能说清楚为什么 FOR UPDATE 能防止超选、默认隔离级别是什么、行锁和表锁的区别。回答不上来即使截图全对也只在及格线上。回答得上来即使代码简单印象分也完全不同。5. 数据库实验五避坑记录报错、扣分与数据恢复5.1 现象一创建存储过程报 1064 语法错误现象照着教材抄的 CREATE PROCEDURE一执行就报 1064错误提示的行号指向 BEGIN 附近。原因十有八九是 DELIMITER 没写或用错。客户端默认用分号作为语句结束符过程体里的分号会被当成语句边界CREATE PROCEDURE 在 BEGIN 之前就被截断了。另一个常见原因是复制粘贴时带入了中文引号或全角空格编辑器看着正常MySQL 不认。解决先确认 DELIMITER // 和 DELIMITER ; 成对出现再把引号统一替换成英文半角。排查时有个技巧把过程体单独存成 .sql 文件用命令行导入而不是在 GUI 工具里粘贴能避开编码和引号问题。如果报错位置在某个看不出问题的字符处把那一行复制出来用十六进制查看器看多半是全角空格。5.2 现象二触发器建好后正常 INSERT 全失败了现象触发器创建成功但之后任何一条 INSERT INTO sc 都失败错误码 1442 或外键约束错误。原因1442 是 MySQL 不允许触发器修改触发它自己的那张表。比如在 AFTER INSERT ON sc 的触发器里又写了 UPDATE sc就会触发 1442。另一个常见原因是触发器体内 UPDATE 了另一张表但那张表没有对应行或外键不匹配触发器的失败连带主操作失败。解决触发器只写关联表绝不回写触发表涉及统计表时先确认统计表有初始数据。排查用排除法把触发器 DROP 掉INSERT 立刻恢复基本就能锁定是触发器的问题。恢复后逐行检查触发器体内每条语句用 SELECT 单独验证关联数据是否存在。5.3 现象三实验做到一半数据乱了只想恢复现场现象反复测试存储过程和触发器之后sc 表里堆了一堆测试记录统计数字对不上手删又怕破坏外键。原因手动执行了多次 INSERT触发器把统计表基数改乱了。逐条清理很容易漏而且有外键约束DELETE 顺序错了就报错。解决不要逐条清直接重建实验库。只要 schema.sql 和 init_data.sql 两个脚本在手重建是十秒的事DROP DATABASE IF EXISTS exp5_db; CREATE DATABASE exp5_db DEFAULT CHARSET utf8mb4;重建后重新走一遍建表和导数脚本再跑 verify.sql 确认数据干净。外键存在时 TRUNCATE 会被拒绝所以 DROP DATABASE 才是最省事的后悔药。这也是我一直强调保管好两个原始脚本的原因——数据库随便折腾脚本不丢就能一键回到起点。5.4 现象四提交的压缩包里混进来一堆无关文件现象提交的 zip 解压后多出 .idea/、.vscode/、__MACOSX/ 目录或者某个 SQL 文件里能看到本机用户名和绝对路径。原因直接在项目目录右键压缩把 IDE 配置文件也打进去了macOS 解压会生成 __MACOSX 隐藏目录。报告截图如果带着资源管理器的地址栏也会暴露本机用户名。解决单独建一个提交目录只复制指导书要求的文件进去再压缩mkdir -p submit_dir cp report.docx schema.sql init_data.sql sp_*.sql submit_dir/ zip -r exp5_submit.zip submit_dir/压缩完成后把 zip 解压到另一个目录检查一遍再交。截完图用任意画图工具把地址栏的盘符和用户名抹掉再插进报告。这类问题不影响功能分但印象分打折属于典型的血泪经验——丢分丢在最不该丢的地方。6. 把实验五做成能拿分的交付验证清单与报告书写习惯实验代码写完剩下的问题是怎么证明每个任务都达标了。我习惯在提交前按下面这张清单逐项过一遍每项都实际执行一次而不是我觉得应该没问题。实验任务必须验证的边界情况期望结果存储过程查询类无选课记录的学生、多课程的学生各调一次分别返回 0 和正确合计不报错存储过程写操作类课程容量为 0、课程不存在返回提示信息不产生脏数据触发器统计类INSERT 一行后立即查统计表计数 1触发器限制类删除有选课记录的课程被拒绝并显示自定义提示事务分别走 COMMIT 和 ROLLBACK 两条路径数据都保持一致性报告书写我一般用四段式先把题目抄成一句话再贴自己写的 SQL然后是运行结果截图最后加一句说明。说明是最能拉开差距的地方避免写执行成功这种废话要写有观察的句子。比如当学生不存在时返回 0 而非 NULL说明 COALESCE 兜底逻辑生效或者删除有选课记录的课程被 SIGNAL 中断说明 BEFORE DELETE 触发器拦截成功。这种句子不需要多每个任务一句就能让老师看出你真正跑通了实验。最后是我自己的习惯也是实验五上救过我多次的流程提交前从 DROP DATABASE 开始把建库、导数据、跑存储过程、测触发器、走事务回滚整个流程重跑一遍全部通过再打 zip。有一次我自认为代码都正确重跑时才发现触发器测试把统计表基数改了第二次插入后的计数结果对不上——正是重跑救了我。这个习惯的代价是十分钟收益是避免提交一份跑不通的实验。希望帮到你。本文还有配套的精品资源点击获取