简介面向数据库课程实验与概念数据建模练习这份压缩包对应西北工业大学软件学院数据库实验五围绕电子商务项目描述完成完整的ER模式设计适合正在学习数据库原理、需要参考ER图绘制与概念模型表达的学生对照使用。包内共20个文件以14个GIF操作截图、2个TXT说明、2个DOC文档以及CDM模型和HTM页面等为主压缩包约282KB体积轻巧但覆盖了从需求理解到模型呈现的关键环节。已有911人学习说明其在同类实验参考中具有一定关注度。读者可从中获得电子商务场景下的实体、联系与属性梳理思路查看ER图及配套文字说明并借助截图了解建模工具中的操作过程与结果呈现CDM文件也有助于理解概念数据模型的落盘结构便于对照修改和复现实验。1. 一份被触发器卡住三小时的实验五答辩前一晚某高校软件学院的一位同学在群里贴出报错选课表人数统计和课程表对不上enrolled字段比实际记录多了一倍删掉重跑还是错。问题不在 SQL 写错而在于他把加人数这件事同时写进了存储过程和触发器两条路径各加一次。数据库实验五这类作业真正难的地方正是存储过程、触发器、事务这三块语法只是表层核心在于事务边界、执行时机以及谁负责维护冗余字段。这份资源包面向的就是这类场景从建库脚本一路写到存储过程、触发器、视图索引再配一个可执行的调用层和验证脚本。适合正在做数据库课程实验、需要交一份能跑通且讲得清的报告的同学也适合已经写过 SQL 但没系统练过事务与并发的人。下面按目录、核心代码、跑通链路、踩坑、二次开发五段展开。2. 资源包拆解目录结构与技术选型先摸清2.1 解压后先看这五个目录拿到压缩包别急着打开报告文档先看sql/和src/两个目录。脚本的命名带数字前缀这是刻意设计的——建表、灌数据、建视图、建存储过程、建触发器顺序错了外键直接报 1452。整个包的骨架大致如下db-lab5/ ├── sql/ │ ├── 01_schema.sql # 建库建表 约束 │ ├── 02_data.sql # 基础测试数据 │ ├── 03_view_index.sql # 视图与索引 │ ├── 04_procedure.sql # 存储过程 │ ├── 05_trigger.sql # 触发器 │ └── 99_teardown.sql # 清库方便反复重跑 ├── src/ │ ├── db.py # 连接池与参数读取 │ ├── enroll.py # 选课业务调用入口 │ └── report.py # 统计报表查询 ├── tests/ │ └── test_enroll.py # 断言式验证 ├── report/ │ └── lab5_report.md # 实验报告模板 └── README.md各文件的职责要和实验报告里的实验步骤对齐交作业时按这个顺序截图逻辑上是一条直线文件作用报告中对应章节01_schema.sql三张表、主外键、CHECK 约束需求分析与表设计02_data.sql学生、课程、历史选课数据数据准备03_view_index.sql统计视图、复合索引视图与索引优化04_procedure.sql选课、退课、成绩录入存储过程设计05_trigger.sql容量校验、人数同步触发器设计tests/test_enroll.py并发与边界断言测试与结果分析值得说明的是99_teardown.sql。很多人做实验时反复改脚本、反复手动删表改到第五版自己都记不清哪张表是老的。留一个一键清库脚本每次从零跑可复现性立刻上一个档次这也是我后来做任何数据库实验都保留的习惯。2.2 选型与版本约束为什么是 MySQL 8.0包的默认目标是 MySQL 8.0 InnoDB。选它主要有三个现实理由一是SIGNAL SQLSTATE能在触发器和存储过程里抛自定义错误实验报告里业务校验这一栏才好写二是 8.0 起CHECK约束真正生效7.x 及以前是解析后直接忽略写了等于没写三是窗口函数和EXPLAIN ANALYZE拿来分析索引效果很方便。如果课程要求 SQL Server改动量集中在三处DELIMITER $$换成GO每条CREATE PROCEDURE/CREATE TRIGGER单独一批SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT ...换成RAISERROR(..., 16, 1)或THROW存储过程里的异常处理DECLARE EXIT HANDLER换成BEGIN TRY ... END TRY BEGIN CATCH ... END CATCH。其余 SQL 逻辑基本可以平移。常见做法是在db.py里把连接参数抽成环境变量换库只改配置不改代码import os import pymysql def get_conn(): return pymysql.connect( hostos.getenv(DB_HOST, 127.0.0.1), portint(os.getenv(DB_PORT, 3306)), useros.getenv(DB_USER, lab), passwordos.getenv(DB_PASS, lab), databaseos.getenv(DB_NAME, lab5), charsetutf8mb4, # 中文课程名必须显式指定否则默认 latin1 会乱码 autocommitFalse, # 事务交给业务代码控制 )charset和autocommit这两个参数是最容易被忽略的。前者不写插入数据库原理这种课程名会变成问号后者设成True存储过程里的事务还没ROLLBACK就被提交了测试永远测不出回滚效果。2.3 三张表撑起的选课模型整个实验围绕学生、课程、选课三张表转。设计上有个关键取舍课程表里要不要留enrolled这个冗余字段。留着统计查询不用每次COUNT但必须有人维护它这正是触发器存在的意义不留一致性天然成立但每次查余量都要聚合。实验五选的是前者因为教学目的就是练冗余字段的一致性维护。CREATE TABLE student ( student_id CHAR(10) PRIMARY KEY, name VARCHAR(32) NOT NULL, major VARCHAR(64) NOT NULL, total_credit DECIMAL(6,1) NOT NULL DEFAULT 0 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE course ( course_id CHAR(8) PRIMARY KEY, title VARCHAR(64) NOT NULL, credit DECIMAL(3,1) NOT NULL, capacity INT NOT NULL DEFAULT 60, enrolled INT NOT NULL DEFAULT 0, -- 冗余计数字段由触发器/过程维护 teacher VARCHAR(32), CONSTRAINT ck_course_seat CHECK (capacity 0 AND enrolled 0) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE enrollment ( student_id CHAR(10) NOT NULL, course_id CHAR(8) NOT NULL, term CHAR(6) NOT NULL, -- 形如 2024-1 score DECIMAL(5,1) DEFAULT NULL, -- 未出分时为 NULL created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (student_id, course_id, term), -- 天然防重复选课 KEY idx_course_term (course_id, term), CONSTRAINT fk_en_student FOREIGN KEY (student_id) REFERENCES student(student_id) ON DELETE CASCADE, CONSTRAINT fk_en_course FOREIGN KEY (course_id) REFERENCES course(course_id), CONSTRAINT ck_en_score CHECK (score IS NULL OR score BETWEEN 0 AND 100) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;参数说明几处主键用(student_id, course_id, term)三列组合而不是自增 id好处是数据库层面直接堵死重复选课坏处是后续要加退课再选的流水记录会受限二次开发那一章会展开。score允许NULL表示已选未考ON DELETE CASCADE只加在学生一侧课程被删时应该报错而不是悄悄清空选课记录。idx_course_term是为某学期某课程的选课名单这类查询准备的后面验证索引效果就靠它。3. 核心实现存储过程与触发器这条链路3.1 选课存储过程一个事务里做完四件事选课这个动作在业务上不是一条INSERT能收尾的要判重、要判余量、要写记录、要同步计数。四步之间任何一步失败都得整体回退所以必须包在显式事务里并且对课程行加锁。包里04_procedure.sql的主过程如下DELIMITER $$ CREATE PROCEDURE sp_enroll( IN p_student_id CHAR(10), IN p_course_id CHAR(8), IN p_term CHAR(6), OUT p_code INT, -- 0 成功 / 1 重复选课 / 2 名额满或课程不存在 / -1 异常 OUT p_msg VARCHAR(128) ) BEGIN DECLARE v_capacity INT DEFAULT 0; DECLARE v_enrolled INT DEFAULT 0; DECLARE v_exists INT DEFAULT 0; -- 任何未捕获的 SQL 异常统一回滚避免半截事务留在连接上 DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SET p_code -1, p_msg 数据库异常已回滚; END; START TRANSACTION; -- 1. 对课程行加排他锁并发选最后一个名额时只有一个能进 SELECT capacity, enrolled INTO v_capacity, v_enrolled FROM course WHERE course_id p_course_id FOR UPDATE; IF v_capacity IS NULL OR v_capacity 0 THEN ROLLBACK; SET p_code 2, p_msg 课程不存在; ELSE -- 2. 重复选课检查主键也拦但这里要给用户可读的提示 SELECT COUNT(*) INTO v_exists FROM enrollment WHERE student_id p_student_id AND course_id p_course_id AND term p_term; IF v_exists 0 THEN ROLLBACK; SET p_code 1, p_msg 已经选过这门课; ELSEIF v_enrolled v_capacity THEN ROLLBACK; SET p_code 2, p_msg 名额已满; ELSE -- 3. 只插选课记录计数字段交给触发器统一维护 INSERT INTO enrollment(student_id, course_id, term) VALUES (p_student_id, p_course_id, p_term); COMMIT; SET p_code 0, p_msg 选课成功; END IF; END IF; END$$ DELIMITER ;几个参数和写法要逐条解释。OUT p_code/OUT p_msg是给应用层留的结构化返回值比让 PHP 或 Python 去解析错误字符串靠谱得多0/1/2/-1这套编码直接对应前端提示文案。SELECT ... FOR UPDATE是这段代码的核心没有它两个连接同时读到enrolled 59、capacity 60各自判断还有位置最后一起插入人数就变成 61这就是典型的超卖。加了行锁以后第二个事务会阻塞在SELECT上等第一个提交后再读到 60判断结果自然变成已满。注意最后一步只有INSERT没有UPDATE course SET enrolled enrolled 1。这个少写一句是刻意留的计数统一由AFTER INSERT触发器负责保证无论从存储过程进来还是直接INSERT计数行为都一致。早期版本两处都写了结果就是开头说的翻倍排查了大半夜。DECLARE EXIT HANDLER也不能省。它保证存储过程内部出任何 SQL 异常都会先ROLLBACK再把p_code置为 -1否则异常向上抛连接会带着未提交事务回到调用方下一次调用可能读到脏数据。3.2 触发器兜底容量校验与人数同步触发器负责两件事插入前拦一次容量插入后同步计数。为什么容量校验和存储过程里重复了一遍因为实验报告要证明数据库层面的完整性不依赖应用层。绕过存储过程直接INSERT时触发器是最后一道闸。DELIMITER $$ -- 插入前容量校验满员直接抛业务错误 CREATE TRIGGER trg_enroll_before_insert BEFORE INSERT ON enrollment FOR EACH ROW BEGIN DECLARE v_capacity INT DEFAULT 0; DECLARE v_enrolled INT DEFAULT 0; SELECT capacity, enrolled INTO v_capacity, v_enrolled FROM course WHERE course_id NEW.course_id; IF v_enrolled v_capacity THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 选课失败课程名额已满; END IF; END$$ -- 插入后同步课程冗余计数 CREATE TRIGGER trg_enroll_after_insert AFTER INSERT ON enrollment FOR EACH ROW BEGIN UPDATE course SET enrolled enrolled 1 WHERE course_id NEW.course_id; END$$ -- 删除后回退计数注意用 GREATEST 兜底防止历史脏数据把计数减成负数 CREATE TRIGGER trg_enroll_after_delete AFTER DELETE ON enrollment FOR EACH ROW BEGIN UPDATE course SET enrolled GREATEST(enrolled - 1, 0) WHERE course_id OLD.course_id; END$$ DELIMITER ;NEW指即将写入的新行OLD指被删除的旧行这是行级触发器里唯一可靠的数据来源别去SELECT刚插入的那行可能还没可见。SIGNAL SQLSTATE 45000是自定义业务错误的固定写法45000表示未处理的用户定义异常应用层捕获后能拿到MESSAGE_TEXT。三个触发器的执行顺序要理清BEFORE INSERT抛错时AFTER INSERT根本不会触发所以容量校验放在前面是必须的。AFTER DELETE里用GREATEST(..., 0)是个防御性写法万一之前有人手工改过enrolled减法不会把字段搞成负数然后被CHECK约束拦住导致退课失败。这里有个硬约束要提前记住MySQL 不允许在enrollment的触发器里再修改enrollment表会报ERROR 1442 Cant update table enrollment in stored function/trigger。所以任何退课后自动补位候补的需求都没法在触发器里直接实现这一条卡住了不少人的二次开发想法第五章会展开讲替代方案。3.3 视图与索引报告里最容易拿分的两块视图负责把统计口径固化下来索引负责让查询真的变快两者都是实验报告优化章节的硬指标。CREATE OR REPLACE VIEW v_course_stat AS SELECT c.course_id, c.title, c.teacher, c.capacity, c.enrolled, ROUND(c.enrolled / c.capacity * 100, 2) AS fill_rate, -- 满座率百分比 COUNT(e.student_id) AS roster_cnt, -- 实际名单人数用来校验 enrolled ROUND(AVG(e.score), 2) AS avg_score FROM course c LEFT JOIN enrollment e ON e.course_id c.course_id GROUP BY c.course_id, c.title, c.teacher, c.capacity, c.enrolled;这个视图的实用之处在于enrolled和roster_cnt并排放。两者不一致就说明计数字段被写坏了一条SELECT * FROM v_course_stat WHERE enrolled roster_cnt就能当巡检语句用。用LEFT JOIN而不是INNER JOIN是为了让没人选的课也能出现在报表里COUNT会返回 0 而不是整行消失。索引验证部分不要只写建了索引所以快了要用EXPLAIN前后对比-- 建索引前后的执行计划对比 EXPLAIN SELECT student_id, score FROM enrollment WHERE course_id C0001 AND term 2024-1 ORDER BY score DESC; -- 若无 idx_course_termtype 会是 ALL 全表扫建了以后应为 ref CREATE INDEX idx_en_score ON enrollment(course_id, term, score);(course_id, term, score)这个顺序遵循最左前缀前两列等值过滤第三列直接参与排序ORDER BY score DESC不再需要额外filesort。如果写成(score, course_id, term)这个查询就用不上索引了。报告里把type从ALL变ref、Extra里的Using filesort消失这两个变化截图放上去比写十行文字有说服力。4. 跑通链路从建库到断言验证4.1 命令行导入顺序与字符集图形化客户端点一圈也能建完但脚本化导入才是可复现的做法。下面这段按文件名排序执行任一脚本失败立即中断避免带着半截库继续往下跑#!/usr/bin/env bash set -euo pipefail # 任一命令失败即退出变量未定义也退出 DBlab5 MYSQLmysql -u root -p --default-character-setutf8mb4 # 1. 重建库排序规则显式指定避免和服务端默认值打架 $MYSQL -e DROP DATABASE IF EXISTS ${DB}; CREATE DATABASE ${DB} DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci; # 2. 按数字前缀顺序执行顺序即依赖顺序 for f in sql/01_schema.sql sql/02_data.sql sql/03_view_index.sql \ sql/04_procedure.sql sql/05_trigger.sql; do echo running $f $MYSQL ${DB} $f done--default-character-setutf8mb4必须显式带上尤其在 Windows 下用命令行导入时客户端默认字符集常常和服务端不一致导入的中文直接变乱码后面查半天以为是字段长度问题。set -euo pipefail是血泪经验循环里某个脚本报错但 bash 继续往下跑最后看到的错误信息往往是下游的比如存储过程建失败、触发器引用了不存在的字段真正的根因被埋在几十行日志前面。导入完成后先做一次冒烟检查确认对象都在SELECT TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA lab5; SHOW PROCEDURE STATUS WHERE Db lab5; SHOW TRIGGERS FROM lab5;4.2 应用层怎么调Python pymysql 调用存储过程src/enroll.py演示的是最常见的调用姿势——存储过程带OUT参数Python 侧的cursor.execute拿不到出参值必须用会话变量接一下再查import pymysql from db import get_conn CALL_SQL CALL sp_enroll(%s, %s, %s, code, msg) def enroll(conn, student_id: str, course_id: str, term: str): 返回 (code, msg)code 含义见 sp_enroll 的注释。 try: with conn.cursor() as cur: cur.execute(CALL_SQL, (student_id, course_id, term)) cur.execute(SELECT code, msg) # 出参通过会话变量回读 code, msg cur.fetchone() conn.commit() # 过程内已 COMMIT这里再提交是幂等的 return code, msg except pymysql.MySQLError as e: conn.rollback() # 触发器 SIGNAL 抛出的错误在这里被接住 return -1, str(e) if __name__ __main__: conn get_conn() for i in range(3): # 连续选三次观察第 2、3 次的返回码 print(enroll(conn, 2021000001, C0001, 2024-1)) conn.close()%s占位符是 pymysql 的参数化写法不是字符串格式化能防注入。code、msg是 MySQL 的会话级用户变量作用域跟着当前连接走所以必须在同一个cursor上紧接着读换连接就读不到了。except pymysql.MySQLError这段要特别注意触发器里SIGNAL抛出的 45000 错误在 Python 侧表现为一个普通异常如果不捕获程序直接崩实验报告里就演示不出满员时业务被正确拒绝这个结论。捕获之后返回-1和原始消息日志里能看清是哪条规则拦的。4.3 验证脚本把看起来对变成断言对手工点几下看结果对不对是实验报告最弱的一环。包里tests/test_enroll.py用 pytest 把边界条件固化成断言改代码后跑一遍就知道有没有破坏原有行为import pytest import pymysql from db import get_conn from enroll import enroll pytest.fixture() def conn(): c get_conn() yield c c.rollback() # 每个用例结束回滚避免污染其他用例 c.close() def test_capacity_full(conn): 容量为 1 的课程第一个人成功后第二个人必须拿到 code2 with conn.cursor() as cur: cur.execute(UPDATE course SET capacity 1, enrolled 0 WHERE course_id C0002) cur.execute(DELETE FROM enrollment WHERE course_id C0002) conn.commit() code1, _ enroll(conn, 2021000001, C0002, 2024-1) code2, _ enroll(conn, 2021000002, C0002, 2024-1) assert code1 0 assert code2 2 # 名额已满 def test_duplicate_enroll(conn): 同一学生同一学期重复选课返回 1 enroll(conn, 2021000003, C0003, 2024-1) code, _ enroll(conn, 2021000003, C0003, 2024-1) assert code 1 def test_counter_consistency(conn): 冗余计数必须和实际名单人数一致这是触发器有没有生效的直接证据 with conn.cursor() as cur: cur.execute(SELECT COUNT(*) FROM v_course_stat WHERE enrolled roster_cnt) assert cur.fetchone()[0] 0三个用例分别覆盖容量边界、唯一性、冗余一致性正好对应实验报告里测试用例设计那张表。fixture末尾的rollback不能省pytest 用例之间共享同一个数据库前一个用例插的数据会影响后一个的计数断言跑出来时对时错非常难查。第三个用例复用v_course_stat视图做巡检比手写JOIN更省事也让视图有了实际用途报告里可以顺势说明视图用于一致性校验。5. 排查清单实验五最容易翻车的五个点5.1 计数翻倍enrolled比实际人数多现象跑完选课流程course.enrolled是 2但enrollment里只有 1 条记录视图巡检一查就露馅。原因存储过程里写了UPDATE course SET enrolled enrolled 1AFTER INSERT触发器里又写了一遍两条路径各加一次。解决计数只留一个维护入口。包里统一交给触发器存储过程只负责插入如果反过来选择过程维护就把触发器里的UPDATE删掉并在注释里写清楚计数由 sp_enroll 负责禁止直接 INSERT。哪个方案都行关键是全局唯一别两边都写。5.2ERROR 1442触发器里改同一张表现象想在enrollment的AFTER INSERT触发器里做候补自动补位——有人退课时从候补表挑一个插进enrollment结果报Cant update table enrollment in stored function/trigger。原因MySQL 明确禁止触发器修改自己监听的那张表否则会引发递归触发。解决把补位逻辑挪出触发器。可选做法有三种——在应用层用事务包住删除 补位两步用EVENT定时任务异步扫候补表或者干脆改成先写候补表由存储过程统一调度。三者各有取舍实验报告里挑一种说清理由即可。5.3DELIMITER没生效存储过程只建了一半现象在图形化客户端里粘贴04_procedure.sql提示You have an error in your SQL syntax near 或者过程建出来了但里面只有第一行。原因DELIMITER是 mysql 命令行客户端的指令不是服务端语法部分 GUI 工具不认或者粘贴时把$$当成了语句分隔。解决优先用命令行mysql db file.sql执行必须用 GUI 时找它自己的定义结束符设置改成//或$$再执行。建完立刻SHOW CREATE PROCEDURE sp_enroll\G检查函数体是否完整这一步能省掉后面大量莫名其妙的调试。5.4 参数名和列名同名WHERE恒真现象sp_enroll执行后提示已经选过这门课但表里明明是空的。原因参数写成了IN student_id CHAR(10)而WHERE student_id student_id里两边同名MySQL 优先解析成列与列比较条件恒真COUNT(*)永远大于 0。解决参数统一加前缀p_student_id、p_course_id命名上和列名区分开。这个坑很隐蔽因为不报错只是逻辑静默错误血泪经验是给所有存储过程参数加p_前缀并且往后再也不改这个约定。5.5 事务没回滚异常后数据留了一半现象Python 里调用选课失败报错也捕获了但重跑发现enrollment里多了一条记录、course.enrolled却没变。原因连接autocommitTrueINSERT一执行就自动提交触发器还没跑完就落库了或者存储过程内部的EXIT HANDLER漏写ROLLBACK异常抛出后事务挂在连接上下一次复用了这条脏连接。解决连接层固定autocommitFalse业务代码显式commit/rollback存储过程里的EXIT HANDLER必须包含ROLLBACK。另外注意 DDL 语句会隐式提交调试时别在事务中间夹CREATE TABLE否则前面的事务边界全被打乱。6. 二次开发把实验五改成能写进简历的课程设计实验报告交完就丢实在浪费。这套结构往上加东西的成本很低分享三个我实际改过的方向。第一个是候补队列。新建waitlist(course_id, student_id, term, seq)seq用AUTO_INCREMENT保证先来先得。sp_enroll在名额满时不再直接返回 2而是插一条候补记录并返回 3 表示已进候补。真正难的是补位触发时机受 1442 限制不能在触发器里做我的做法是在sp_drop退课过程里删完记录后用SELECT ... FOR UPDATE SKIP LOCKED取候补表里seq最小的一条插进enrollment整个过程在一个事务里。SKIP LOCKED是 8.0 才有的多线程下不会互相阻塞这段写进报告比原来的选课逻辑更有分量。第二个是把校验逻辑上移到应用层做对比实验。触发器好用但生产环境里很多团队忌惮它的隐式执行——出问题时调用栈里看不到它属于典型的黑匣子。可以保留触发器版本再写一份纯应用层事务版本用pytest在 50 并发下压一遍对比两版的成功率和响应时间。表结构不用动只加一个enroll_service.py结论写进报告高并发场景下应用层锁 乐观重试比触发器更可控。这种对比分析是课程设计里最容易出彩的部分。第三个是回归测试常态化。把tests/扩到覆盖退课、成绩录入、跨学期统计每次改 SQL 先跑pytest -q。改索引、改触发器、调事务隔离级别全都有断言兜底。我一般还会加一条巡检查询定时跑-- 一致性巡检任何一行返回都说明冗余计数被写坏了 SELECT course_id, enrolled, roster_cnt FROM v_course_stat WHERE enrolled roster_cnt;从那以后我每次改触发器或者动事务边界都强制先把这条巡检和三个断言用例跑一遍再去看业务效果——触发器这种隐式执行的东西不靠断言兜着出问题真的只能靠猜。希望帮到你。本文还有配套的精品资源点击获取
