★ FEATURED ARTICLE 简介西北工业大学软件学院数据库实验五资料包面向高校软件工程与数据库课程学习者聚焦电商数据库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这种地方卡住。我一般建议按你机器上已装好的那套写别中途换因为两边的过程化语法差异足够让整份脚本重写。能力点MySQL 8.x 写法SQL Server 写法语句块分隔DELIMITER $$ ... $$ DELIMITER ;GO批处理分隔存储过程参数IN/OUT/INOUT显式声明p前缀OUTPUT关键字抛自定义错误SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT...RAISERROR(...,16,1)或THROW游标声明位置BEGIN内所有 DECLARE 必须在最前DECLARE后可跟语句位置更宽松取最后一行的自增值LAST_INSERT_ID()SCOPE_IDENTITY()触发器内改本表禁止报 1442默认允许可用nested triggers关闭注意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) ) ENGINEInnoDB; 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) ) ENGINEInnoDB; 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) ) ENGINEInnoDB;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 snoMySQL 会优先把它解析成列与列比较条件恒真返回全表平均分——不报错结果悄悄错这种错最难查。统一加前缀后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: Cant 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 INSERTBEFORE阶段新行还没落盘外键检查也没做往子表写统计可能撞外键。删除侧要配一个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 sno20230001 AND cnoC001; -- 读到 86 -- 会话 B另一个连接 UPDATE sc SET grade 88 WHERE sno20230001 AND cnoC001; COMMIT; -- 会话 A 再查 SELECT grade FROM sc WHERE sno20230001 AND cnoC001; -- 仍然是 86 COMMIT;在REPEATABLE READ下会话 A 的两次读来自同一个快照所以结果一致这叫可重复读只有COMMIT之后重开事务才能看到 88。把隔离级别改成READ COMMITTED再跑一遍第二次读就是 88这就是不可重复读。同一段脚本换个隔离级别、观察结果变化是实验报告里最能体现理解的部分比贴十张截图有用。需要“先读后改、期间不让别人插队”的时候用锁定读START TRANSACTION; SELECT grade FROM sc WHERE sno20230001 AND cnoC001 FOR UPDATE; -- 加排他锁别人改这行要等 UPDATE sc SET grade grade 5 WHERE sno20230001 AND cnoC001 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 Dblab5;里能看到。现象二建表时写了CHECK (grade BETWEEN 0 AND 100)插一条grade120却成功了。原因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或者并发选课时人数上限被突破。原因前者是在触发器里对定义它的表做了 DMLMySQL 直接禁止后者是COUNT加INSERT两步不是原子操作快照读看不到未提交的插入。解决同表同步改成写独立的统计表用ON DUPLICATE KEY UPDATE做原子自增容量限制要么对父表行加FOR UPDATE要么用唯一索引加应用层重试。这两条在单线程测试里都不会暴露必须开两个连接手动制造并发才能复现建议把这组对照实验直接写进报告。6. 验收标准用执行计划和隔离级别实验自证实现正确脚本能跑通只是及格线。真正让实验报告站得住脚的是你能证明“它为什么对、在什么条件下会不对”。我一般用两个动作收尾。第一个动作是把存储过程里的关键查询抽出来跑EXPLAIN看索引有没有被用上EXPLAIN FORMATJSON SELECT COUNT(grade), AVG(grade) FROM sc WHERE cno C001;重点看key是不是走了主键或cno相关索引rows估算的行数是不是接近实际。如果type显示ALL说明在扫全表——教学库数据少感觉不出来但这条查询被包在游标里循环执行课程数一多就是平方级开销。补一个(cno)上的辅助索引再跑一次对比rows和filtered的变化这段对比放进报告比任何文字描述都直观。第二个动作是隔离级别矩阵。同一段“改成绩再读”的脚本分别在READ COMMITTED和REPEATABLE READ下各跑一遍把两个会话的输出按时间顺序记录下来隔离级别会话 A 第二次读现象名称触发条件READ COMMITTED看到新值 88不可重复读同一事务内两次读之间别人提交了修改REPEATABLE READ仍是旧值 86可重复读快照在事务开始时建立SERIALIZABLE第二次读被阻塞锁等待读操作也加共享锁跑这张表的时候记得把两个会话的autocommit都关掉否则每条语句自动提交事务边界根本不存在实验现象全是乱的。这是新手最容易忽略的一步——START TRANSACTION写了但客户端默认自动提交没关观察到的全是单语句行为。还有一个具体技巧给每个存储过程配一条“最小验证用例”写在脚本注释里。比如sp_student_avg的用例是“传一个只选了一门课且已出分的学生期望返回该分数和‘共 1 门有效成绩’”。改完参数、改完条件先跑这条用例十秒内知道有没有改坏比重新翻数据表快得多。血泪经验是我早期做这类实验改完触发器就直接在整表上跑一遍UPDATE结果一条脏数据把几百行全带偏回滚都找不回原值。从那以后我每次动触发器或存储过程都强制先在一个只有三五行的临时副本表上验证确认行为符合预期再放到主表上跑并且动手前先CREATE TABLE sc_bak AS SELECT * FROM sc留一份后悔药。数据库实验最贵的成本不是写代码的时间是数据被改坏之后重来的时间。希望帮到你。本文还有配套的精品资源点击获取
阅读完成 · 觉得有帮助？
