简介：西北工业大学软件学院数据库实验五资料包，面向高校软件工程与数据库课程学习者，聚焦电商数据库ER模式设计这一典型建模任务。资源共20个文件，以14个gif业务流程图、2个txt与2个doc实验说明为主，另含1个cdm概念数据模型和1个htm项目页面，压缩包约282KB，体量轻便，便于随实验课分发和本地查阅。目前已有911人学习，说明该实验在数据库课程中具有较高参考需求。内容围绕E-Commerce项目描述展开，可对照附录要求完成电商数据库的完整ER模式设计，借助ER图示与说明梳理实体、联系、属性及基数约束；配套的注册登录、书籍详情、购物车、结账、订单确认、收货信息、信用卡与历史订单等流程截图，有助于把抽象模型与真实业务场景对应起来。适合软件学院学生完成实验报告、检查ER图完整性，也便于教师备课与建模思路复盘。

1. 数据库实验五里最容易被卡住的，是游标和触发器

“数据库实验五”这个词在课程群里出现的频率，基本和“触发器建不上”“游标多跑一行”绑在一起。这份材料是某高校软件学院数据库课程实验五的整包资源：建库建表脚本、示例数据、存储过程与触发器参考实现，以及可以逐条对照的执行结果。它要解决的核心矛盾，是“语法都认识，逻辑写出来了但跑不对、报告说不清”——比如触发器里更新同一张表直接报错、游标循环把最后一行处理了两次、并发场景下选课人数上限形同虚设。适合三类人：正在赶实验五的、想把过程化 SQL 补一遍的、以及准备做数据库课程设计需要一套可复用骨架的。下面按“它考什么—怎么跑起来—怎么写对—哪里会翻车—怎么自证”的顺序拆。

2. 实验五考什么：从 SELECT 到过程化 SQL 的三级跳

2.1 存储过程、触发器、事务各自解决什么问题

前四个实验基本停在“把数据查出来”：单表查询、连接查询、嵌套查询、视图与索引。这些题目的共同点是只读，写错了最多结果集不对，不会污染数据。实验五换了个方向：要求把业务规则下沉到数据库里，用数据库自己的过程化能力去“改数据”，并且改得可控。

三块内容的分工其实很清晰。存储过程解决的是“一组需要重复执行的取数加判断加写回”，把原来在应用层写的 if-else 搬进数据库，调用方只需要一个CALL；触发器解决的是“某个表被 INSERT/UPDATE/DELETE 时，必须顺带做的事”，比如成绩越界拦截、冗余统计字段同步；事务解决的是“多条语句要么全成、要么全败”，以及并发情况下别人能看到什么。

课程把这三样凑在一个实验里，考的是三种不同的思维：过程化思维（变量、分支、循环、异常）、事件驱动思维（触发时机 BEFORE/AFTER、行级/语句级）、并发思维（隔离级别、锁等待）。很多人翻车不是因为不会写 SQL，而是把这三套思维混着用——在触发器里写循环、在存储过程里开事务却不处理异常、以为 CHECK 约束能挡住所有脏数据。

一个常见的误判是“触发器能替代应用层校验”。实际上触发器的定位是最后一道防线，它跑在数据库进程里，出错就是整个语句回滚，用户体验很差；真正该在前端拦的还应在前面拦。实验里用触发器，更多是为了让你理解“约束”这件事有多个层次：列级 CHECK、表级外键、触发器、应用层校验，越往后越灵活也越贵。

2.2 MySQL 与 SQL Server 的语法分岔：按哪套写

教学环境常见两套：MySQL 8.x 和 SQL Server。实验五的题目描述往往只给逻辑，不给方言，直接照抄网上答案很容易在DELIMITER、GO这种地方卡住。我一般建议按你机器上已装好的那套写，别中途换，因为两边的过程化语法差异足够让整份脚本重写。

注意DELIMITER不是服务端语法，它是客户端指令。你在命令行里敲没问题，但如果用某些 GUI 把整段脚本一次性丢给服务端，它会连DELIMITER一起当 SQL 解析然后报语法错。判断方法很简单：如果报错位置正好在DELIMITER那一行，就是客户端不支持，换 MySQL Workbench 或命令行即可，脚本本身没毛病。

2.3 三张教学表与约束底座

实验五的脚本都建立在一套经典教学模式上：学生、课程、选课。这套模式看着简单，但主外键和复合主键的设计直接决定了后面触发器和事务能不能写顺。

DROP DATABASE IF EXISTS lab5; CREATE DATABASE lab5 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; USE lab5; CREATE TABLE student ( sno CHAR(8) PRIMARY KEY, sname VARCHAR(20) NOT NULL, ssex CHAR(1) NOT NULL DEFAULT '男', sage TINYINT NULL, sdept VARCHAR(30) NOT NULL, CONSTRAINT ck_student_sex CHECK (ssex IN ('男','女')), CONSTRAINT ck_student_age CHECK (sage BETWEEN 15 AND 45) ) ENGINE=InnoDB; CREATE TABLE course ( cno CHAR(4) PRIMARY KEY, cname VARCHAR(40) NOT NULL, credit DECIMAL(3,1) NOT NULL, cpno CHAR(4) NULL, -- 先修课，自引用外键 CONSTRAINT ck_course_credit CHECK (credit > 0 AND credit <= 10), CONSTRAINT fk_course_cpno FOREIGN KEY (cpno) REFERENCES course(cno) ) ENGINE=InnoDB; CREATE TABLE sc ( sno CHAR(8) NOT NULL, cno CHAR(4) NOT NULL, grade DECIMAL(5,1) NULL, -- 允许 NULL：已选课但未出分 PRIMARY KEY (sno, cno), CONSTRAINT fk_sc_student FOREIGN KEY (sno) REFERENCES student(sno), CONSTRAINT fk_sc_course FOREIGN KEY (cno) REFERENCES course(cno), CONSTRAINT ck_sc_grade CHECK (grade BETWEEN 0 AND 100) ) ENGINE=InnoDB;

sc用(sno, cno)复合主键而不是自增 id，是为了天然阻止同一个人重复选同一门课，同时给外键提供明确的引用目标。grade允许 NULL 是刻意的：选课记录先于成绩产生，如果把grade设成NOT NULL DEFAULT 0，后面统计平均分时就会把“没出分”算成“考了 0 分”，这个坑在实验报告里经常被扣分。

CHECK约束在这里是底座，但要注意一个版本事实：MySQL 在 8.0.16 之前的版本会解析CHECK然后直接忽略，不报错也不生效。如果你写完约束插一条grade = 120竟然成功了，先查SELECT VERSION()，别急着怀疑自己脚本写错。这也解释了为什么实验里还要再写一个触发器做校验——在旧版本上，触发器是唯一能兜住的那层。

3. 把实验五跑起来：建库、导数、存储过程落地

3.1 建库建表：字符集与幂等重跑

压缩包里的脚本一般按编号排好了执行顺序，但重跑是常态，所以第一段脚本必须写成幂等的：开头DROP DATABASE IF EXISTS，建表前可以再加DROP TABLE IF EXISTS。这样你改一次表结构就能整段重来，不用手动删库。

字符集统一用utf8mb4，不要用utf8。教学库里学生姓名、课程名都是中文，utf8在 MySQL 里其实是三字节实现，遇到某些生僻字会截断报错。utf8mb4_0900_ai_ci是 8.0 的默认排序规则，ai表示重音不敏感、ci表示大小写不敏感，做姓名模糊查询时行为符合直觉。

导入示例数据时建议把INSERT写成多值形式，比一条一条插快很多，也方便你整体注释掉重来：

INSERT INTO student (sno, sname, ssex, sage, sdept) VALUES ('20230001','张一','男',19,'软件工程'), ('20230002','李二','女',20,'软件工程'), ('20230003','王三','男',21,'计算机科学'); INSERT INTO course (cno, cname, credit, cpno) VALUES ('C001','数据库系统',4.0,NULL), ('C002','数据结构',4.0,NULL), ('C003','操作系统',3.5,'C002'); INSERT INTO sc (sno, cno, grade) VALUES ('20230001','C001',86.0), ('20230001','C002',78.5), ('20230002','C001',92.0), ('20230003','C001',NULL);

执行完先做一次冒烟检查：SELECT COUNT(*) FROM sc;应该是 4 行。如果只有 0 行，多半是外键顺序错了——sc必须在student和course之后插，否则外键找不到父行直接拒绝。这类错误报错信息里会带a foreign key constraint fails，看到这句就去查插入顺序，不用往下看别的。

3.2 IN/OUT 参数与结果集：第一个存储过程

实验五的第一题通常是“给定学号，返回该生平均分”。这题的考点不在AVG()，而在参数模式：IN是调用方传进来、过程内只读；OUT是过程内赋值、调用方用用户变量接；INOUT两边都能改。

DELIMITER $$ DROP PROCEDURE IF EXISTS sp_student_avg $$ CREATE PROCEDURE sp_student_avg( IN p_sno CHAR(8), -- 入参，调用时传学号 OUT p_avg DECIMAL(5,2), -- 出参，平均分 OUT p_msg VARCHAR(64) -- 出参，提示信息 ) BEGIN DECLARE v_cnt INT DEFAULT 0; SELECT COUNT(*), AVG(grade) INTO v_cnt, p_avg FROM sc WHERE sno = p_sno AND grade IS NOT NULL; -- 排除未出分记录 IF v_cnt = 0 THEN SET p_avg = NULL; SET p_msg = CONCAT('学号 ', IFNULL(p_sno,'NULL'), ' 无有效成绩'); ELSE SET p_msg = CONCAT('共 ', v_cnt, ' 门有效成绩'); END IF; END $$ DELIMITER ;

调用时OUT位置必须传用户变量，不能传字面量：

CALL sp_student_avg('20230001', @avg, @msg); SELECT @avg AS avg_grade, @msg AS tip;

参数名前缀p_不是风格问题，是保命习惯。如果参数名直接叫sno，过程体里写WHERE sno = sno，MySQL 会优先把它解析成列与列比较，条件恒真，返回全表平均分——不报错，结果悄悄错，这种错最难查。统一加前缀后，WHERE sno = p_sno一眼就能看出哪边是列、哪边是变量。

逻辑上再做两点说明。COUNT(*)和AVG(grade)放在同一条SELECT ... INTO里，保证拿到的是同一快照下的结果，分两条语句写在高并发下可能对不上。grade IS NOT NULL这个条件必须有，否则未出分的选课记录会把分母撑大、均值拉低，报告里那组对不上的数字大多来自这里。

3.3 游标逐行统计：HANDLER 与循环退出条件

第二题往往是“逐门课程统计选课人数、平均分、最高最低分，写入统计表”。这题的本意是练游标，虽然实际工程里能用一条GROUP BY解决，但课程要求逐行处理，就老老实实写游标。

DELIMITER $$ DROP PROCEDURE IF EXISTS sp_stat_course $$ CREATE PROCEDURE sp_stat_course() BEGIN DECLARE v_cno CHAR(4); DECLARE v_cname VARCHAR(40); DECLARE v_cnt INT; DECLARE v_avg DECIMAL(5,2); DECLARE v_max DECIMAL(5,1); DECLARE v_min DECIMAL(5,1); DECLARE done INT DEFAULT 0; -- 游标：结果集必须是有序且稳定的 DECLARE cur_course CURSOR FOR SELECT cno, cname FROM course ORDER BY cno; -- 注意处理器：NOT FOUND 会同时被 SELECT INTO 触发，谨慎使用 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; DROP TEMPORARY TABLE IF EXISTS tmp_course_stat; CREATE TEMPORARY TABLE tmp_course_stat ( cno CHAR(4) PRIMARY KEY, cname VARCHAR(40), stu_cnt INT, avg_grade DECIMAL(5,2), max_grade DECIMAL(5,1), min_grade DECIMAL(5,1) ); OPEN cur_course; read_loop: LOOP FETCH cur_course INTO v_cno, v_cname; IF done = 1 THEN LEAVE read_loop; -- 必须紧跟在 FETCH 之后判断 END IF; SELECT COUNT(grade), AVG(grade), MAX(grade), MIN(grade) INTO v_cnt, v_avg, v_max, v_min FROM sc WHERE cno = v_cno; INSERT INTO tmp_course_stat VALUES (v_cno, v_cname, v_cnt, v_avg, v_max, v_min); END LOOP; CLOSE cur_course; SELECT * FROM tmp_course_stat ORDER BY cno; END $$ DELIMITER ;

CONTINUE HANDLER FOR NOT FOUND是这段代码里最容易出事的一行。NOT FOUND不只由FETCH触发，SELECT ... INTO查不到行时同样会触发。所以如果循环体里还有别的SELECT INTO，一旦某门课没人选，done会被提前置 1，循环在中途就退出了，统计表只写了一半。稳妥做法是把循环体里的统计改成INSERT ... SELECT，避免额外的SELECT INTO；或者用带条件的处理器FOR NOT FOUND配合在循环体开头重置标志。

另一个细节是done = 1的判断位置。它必须紧跟在FETCH之后、处理逻辑之前。如果把判断放到循环末尾，最后一次FETCH失败后v_cno还是上一行的值，循环体会多用旧值执行一次，统计表里就多出一条重复记录。这个“多一行”的现象在报告里非常显眼，看到统计表行数比课程数多 1，基本就是这里的问题。

用临时表而不是直接把结果SELECT出来，是为了让过程能“既产生结果集、又留下可回查的中间结果”。临时表只在当前会话可见，会话断开自动清空，不会污染教学库。

4. 触发器与事务：让“改数据”可控

4.1 行级触发器的两种典型写法：校验与容量

实验五的触发器题一般分两类：一类做数据校验，一类做业务规则。校验类用SIGNAL抛错，业务规则类要读写别的表。

先说校验。虽然建表时已经写了CHECK，但旧版本不生效，所以再加一个BEFORE UPDATE触发器兜底：

DELIMITER $$ DROP TRIGGER IF EXISTS trg_sc_grade_bu $$ CREATE TRIGGER trg_sc_grade_bu BEFORE UPDATE ON sc FOR EACH ROW BEGIN IF NEW.grade IS NOT NULL AND (NEW.grade < 0 OR NEW.grade > 100) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '成绩必须在 0 到 100 之间'; END IF; END $$ DELIMITER ;

BEFORE和AFTER的区别在于能不能改NEW。BEFORE里可以SET NEW.grade = 0把非法值修正后再写入，AFTER里不行，只能抛错或写日志。校验场景一般用BEFORE：拦得住、不写脏数据、报错发生在落盘之前。SIGNAL SQLSTATE '45000'里的45000是“未处理的用户自定义异常”，属于通用约定，换成别的五位数也能跑，但用这个最不容易被上层框架误判。

业务规则类的典型题是“每门课选课人数不超过 60”：

DELIMITER $$ DROP TRIGGER IF EXISTS trg_sc_capacity_bi $$ CREATE TRIGGER trg_sc_capacity_bi BEFORE INSERT ON sc FOR EACH ROW BEGIN DECLARE v_cur INT DEFAULT 0; SELECT COUNT(*) INTO v_cur FROM sc WHERE cno = NEW.cno; IF v_cur >= 60 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '该课程选课人数已满 60 人'; END IF; END $$ DELIMITER ;

这段代码单线程跑没问题，并发下会失效：两个会话同时插第 60 个人，各自COUNT都读到 59，都通过校验，最后变成 61 人。原因是REPEATABLE READ下的普通SELECT是快照读，看不到对方尚未提交的插入。要真正挡住，需要在计数时对课程行加锁（SELECT ... FOR UPDATE锁course里对应行），或者干脆去掉触发器，改用应用层加锁加唯一索引的方案。课程实验通常不考并发，但报告里能点出这个局限，是加分项。

4.2 触发器递归与同表 DML 的边界

“在触发器里更新定义它的那张表”是实验五报错率最高的一处。MySQL 直接禁止：给sc建了AFTER INSERT触发器，里面又去UPDATE sc，插入时立刻报ERROR 1442: Can't update table 'sc' in stored function/trigger because it is already used by statement。这不是你写错了，是数据库在防无限递归。

SQL Server 的行为不同，它默认允许同表操作，靠nested triggers和recursive triggers两个开关控制递归深度，递归超过 32 层才终止。所以同一份逻辑在两边跑，结果完全不一样，跨环境交作业时特别容易翻车。

真正需要“插入后同步冗余字段”时，正确做法是把冗余放到另一张表，让触发器去改那张表：

CREATE TABLE course_stat ( cno CHAR(4) PRIMARY KEY, stu_cnt INT NOT NULL DEFAULT 0, CONSTRAINT fk_cs_course FOREIGN KEY (cno) REFERENCES course(cno) ); DELIMITER $$ DROP TRIGGER IF EXISTS trg_sc_count_ai $$ CREATE TRIGGER trg_sc_count_ai AFTER INSERT ON sc FOR EACH ROW BEGIN INSERT INTO course_stat (cno, stu_cnt) VALUES (NEW.cno, 1) ON DUPLICATE KEY UPDATE stu_cnt = stu_cnt + 1; -- 原子自增，避免读改写 END $$ DELIMITER ;

INSERT ... ON DUPLICATE KEY UPDATE把“查有没有、没有就插、有就加一”压成一条语句，省掉SELECT再UPDATE的竞态窗口。注意这里是AFTER INSERT：BEFORE阶段新行还没落盘，外键检查也没做，往子表写统计可能撞外键。删除侧要配一个AFTER DELETE做减一，并加WHERE stu_cnt > 0防止减成负数。

还有一个隐蔽的坑：course_stat上如果有指向course的外键，而course的删除又依赖触发器清理统计，两边会互相等锁。教学库里数据量小看不出来，真跑起来就是死锁。稳妥的写法是统计表不加外键，靠定时任务或应用层保证一致性——冗余字段本来就要接受“可能短暂不一致”。

4.3 事务与隔离级别：选课并发下看到什么

事务部分的核心不是COMMIT和ROLLBACK两个词，而是“别人什么时候能看到你的修改”。实验里常给的场景是：一个会话改成绩，另一个会话在事务里反复查，看看读到的值变不变。

-- 会话 A SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; START TRANSACTION; SELECT grade FROM sc WHERE sno='20230001' AND cno='C001'; -- 读到 86 -- 会话 B（另一个连接） UPDATE sc SET grade = 88 WHERE sno='20230001' AND cno='C001'; COMMIT; -- 会话 A 再查 SELECT grade FROM sc WHERE sno='20230001' AND cno='C001'; -- 仍然是 86 COMMIT;

在REPEATABLE READ下，会话 A 的两次读来自同一个快照，所以结果一致，这叫可重复读；只有COMMIT之后重开事务才能看到 88。把隔离级别改成READ COMMITTED再跑一遍，第二次读就是 88，这就是不可重复读。同一段脚本换个隔离级别、观察结果变化，是实验报告里最能体现理解的部分，比贴十张截图有用。

需要“先读后改、期间不让别人插队”的时候，用锁定读：

START TRANSACTION; SELECT grade FROM sc WHERE sno='20230001' AND cno='C001' FOR UPDATE; -- 加排他锁，别人改这行要等 UPDATE sc SET grade = grade + 5 WHERE sno='20230001' AND cno='C001' AND grade + 5 <= 100; COMMIT;

FOR UPDATE锁的是索引命中的行。这里(sno, cno)是主键，能精确定位到一行；如果WHERE里用的是没索引的列，InnoDB 会退化成扫全表加锁，把不相干的行也锁住，并发立刻崩。所以写锁定读之前先确认条件列有索引，这条经验在课程设计阶段比实验五本身更值钱。

5. 避坑与排查：五类让实验报告返工的问题

5.1 环境与语法类：DELIMITER、CHECK、参数同名

现象一：整段脚本粘进去，只执行了第一条建库语句，后面的存储过程一个都没建，也不报错。原因：DELIMITER是客户端指令，某些 GUI 会把它当普通 SQL 发给服务端，服务端解析失败后中止了当前批处理，但界面只显示第一条成功。解决：换 MySQL 命令行或 Workbench 执行；或者干脆把每个存储过程拆成独立文件单独跑，跑完确认SHOW PROCEDURE STATUS WHERE Db='lab5';里能看到。

现象二：建表时写了CHECK (grade BETWEEN 0 AND 100)，插一条grade=120却成功了。原因：MySQL 8.0.16 之前CHECK只做语法解析、不做实际校验。解决：SELECT VERSION()确认版本；低版本用BEFORE INSERT/UPDATE触发器兜底，或者把校验挪到应用层。报告里写清楚“约束在不同版本上的生效差异”，比单纯贴一条能跑通的语句更有说服力。

现象三：存储过程调用返回的平均分明显偏高，跟手算对不上。原因：参数名和列名同名，WHERE sno = sno被解析成列与列比较，条件恒真，统计了全表。解决：所有参数统一加p_前缀，过程体里凡是引用列的地方都写全表名.列名，一眼能分辨。改完用一条已知数据回归：某个只选了两门课的学生，平均分应该落在两门课分数之间，超出范围就说明条件没生效。

5.2 逻辑与并发类：游标多一行、触发器递归、超选

现象四：游标统计出来的行数比课程总数多 1，最后一行是重复的。原因：done标志判断放到了循环体末尾，最后一次FETCH失败后变量仍保留上一行的值，循环体又用旧值执行了一遍。解决：FETCH之后立刻IF done = 1 THEN LEAVE ...。顺带检查循环体里有没有额外的SELECT ... INTO——它同样会触发NOT FOUND，让循环提前退出，表现是“少几行”，跟多一行正好相反，排查时容易看反。

现象五：触发器创建成功，一插入就报 1442，或者并发选课时人数上限被突破。原因：前者是在触发器里对定义它的表做了 DML，MySQL 直接禁止；后者是COUNT加INSERT两步不是原子操作，快照读看不到未提交的插入。解决：同表同步改成写独立的统计表，用ON DUPLICATE KEY UPDATE做原子自增；容量限制要么对父表行加FOR UPDATE，要么用唯一索引加应用层重试。这两条在单线程测试里都不会暴露，必须开两个连接手动制造并发才能复现，建议把这组对照实验直接写进报告。

6. 验收标准：用执行计划和隔离级别实验自证实现正确

脚本能跑通只是及格线。真正让实验报告站得住脚的，是你能证明“它为什么对、在什么条件下会不对”。我一般用两个动作收尾。

第一个动作是把存储过程里的关键查询抽出来跑EXPLAIN，看索引有没有被用上：

EXPLAIN FORMAT=JSON SELECT COUNT(grade), AVG(grade) FROM sc WHERE cno = 'C001';

重点看key是不是走了主键或cno相关索引，rows估算的行数是不是接近实际。如果type显示ALL，说明在扫全表——教学库数据少感觉不出来，但这条查询被包在游标里循环执行，课程数一多就是平方级开销。补一个(cno)上的辅助索引再跑一次，对比rows和filtered的变化，这段对比放进报告比任何文字描述都直观。

第二个动作是隔离级别矩阵。同一段“改成绩再读”的脚本，分别在READ COMMITTED和REPEATABLE READ下各跑一遍，把两个会话的输出按时间顺序记录下来：

跑这张表的时候记得把两个会话的autocommit都关掉，否则每条语句自动提交，事务边界根本不存在，实验现象全是乱的。这是新手最容易忽略的一步——START TRANSACTION写了，但客户端默认自动提交没关，观察到的全是单语句行为。

还有一个具体技巧：给每个存储过程配一条“最小验证用例”，写在脚本注释里。比如sp_student_avg的用例是“传一个只选了一门课且已出分的学生，期望返回该分数和‘共 1 门有效成绩’”。改完参数、改完条件，先跑这条用例，十秒内知道有没有改坏，比重新翻数据表快得多。

血泪经验是：我早期做这类实验，改完触发器就直接在整表上跑一遍UPDATE，结果一条脏数据把几百行全带偏，回滚都找不回原值。从那以后我每次动触发器或存储过程，都强制先在一个只有三五行的临时副本表上验证，确认行为符合预期再放到主表上跑，并且动手前先CREATE TABLE sc_bak AS SELECT * FROM sc留一份后悔药。数据库实验最贵的成本不是写代码的时间，是数据被改坏之后重来的时间。希望帮到你。

本文还有配套的精品资源，点击获取