简介西北工业大学软件学院数据库实验五的完整资源包面向该校软件学院选修数据库课程的学生及正在练习 ER 建模的初学者配套电商数据库项目背景要求完成完整的 ER schema 设计。资源共包含 20 个文件其中 14 个 gif 覆盖注册、登录、购物车、下单、订单查看等关键流程的操作录屏2 个 doc 提供实验任务与项目描述1 个 cdm 给出概念数据模型1 个 htm 为练习指导2 个 txt 存放 ER 文本说明整包仅 282KB轻量易下载。已有 910 人学习下载适合按步骤对照完成 E-Commerce 数据库的实体联系建模。借助包内 ER 图、概念模型和录屏学习者可以理解实体、属性、联系的设计过程明确订单、商品、会员等核心对象的建模要点还能通过 CDM 文件在 PowerDesigner 中查看和修改模型并利用说明文档验证设计思路从而系统完成西工大软件学院数据库实验五。1. 这个实验五到底卡在哪一步数据库实验五在很多高校软件工程培养方案里不是单个知识点的小作业而是一次串起「设计 - 建表 - 数据操作 - 视图/存储过程/权限 - 综合校验」的完整工程演练。拿到题目包时你会看到一堆文档和模板 SQL但核心任务通常只有一个按需求文档从零设计一套关系模式导入数据然后实现一批规定好的查询与写入逻辑。标题里的“西北工业大学软件学院数据库实验五”看着像某次课设专用包但这类实验的结构在各高校高度相似——先给一个真实场景图书借阅/教务选课/订单库存再提十几条业务需求最后要求你交付建表脚本、数据脚本和查询脚本。很多同学在这类实验上翻车不是因为不会写 SQL而是把实验当成了“多写几条语句”的练习。实际上实验五的评分重心往往是三个地方表结构设计是否符合规范化要求、查询是否能处理边界数据、存储过程/触发器能否正确应对并发或异常场景。说白了这不是背语法是在考你建模意识和耐心读需求的能力。适合读这篇文章的人是自己正卡在“不知道从哪下手建表”或“写完查询总觉得结果不对”阶段的同学。我下面会把一套能直接照做的流程完整拆给你从需求分析到最终验收每一步都有能复现的命令和参数逻辑。2. 先拆需求文档从业务描述反推关系模式的四个要点拿到实验五材料第一步永远不是打开 MySQL 写建表语句而是把需求文档里的业务规则画成结构。要确认文档默认的连接方式。多数实验室环境用 MySQL 8.x 或 5.7连接串参数有差异后面导入和验证阶段能省许多不必要的折腾。更关键的是把“实体」「联系」「约束」三类信息先摘出来。2.1 实体识别找出所有名词性主体筛掉伪实体通读需求里每个自然段把名词性主体列出来。比如“学生借阅图书”场景里会得到学生、图书、出版社、分类、借阅记录、罚款单等候选实体。其中“出版社”和“分类”很可能是从属属性而非独立实体。判断标准简单是否存在一个业务对象需要独立维护它多个属性、且这个对象被多条记录引用。如果“出版社”只有名称一个属性通常是图书表的外键属性如果有地址、电话、联系人才值得独立建表。筛选完实体后我需要做的一件事就是给每个实体命名并统一单复数。建议全用小写复数表名students、books、borrow_records。命名统一能很大程度减少后续写 join 时的手误。你还会发现需求文档里有些名词是动作结果比如“预约”“续借”它们不是实体而是关系或状态字段。可先手动删掉等画 ER 图时再放回联系上。2.2 属性归属与主键选择先看业务规则再看范式每个实体字段从需求句子里摘出来后第一件事是选主键。有自然主键的如学号、ISBN先保留但要注意实验题里经常挖坑学生可能换号图书同 ISBN 存在多册副本。我在做某跨平台系统的借用模块时用过复合主键book_id copy_id这种设计能应对“同一种书有多本可借”的真实场景。确认主键候选时看两条业务规则一是同一实体是否允许重复出现相似记录二是业务上是否需要单条记录被独立引用。范式检查挪到第三步。多数实验五只要求到 3NF具体做法是把“非主属性对码的部分依赖和传递依赖”拆干净。举例如果借阅记录表里有 student_name而 student_name 由 student_id 决定这个字段就是冗余的时间紧不报错但项目验收的数据一致性检查很难过。经验做法是所有带 _name 的字段先删掉保留 id用 join 时再带出名称。2.3 关系与基数1:N 和 M:N 是设计好坏的分水岭实体之间的关系我用表格列一个判断速查实体间自然语言基数物理实现方式每个学生可借多本书每本书可被多人借过多对多中间表 borrow_records 两个外键每个分类下多本图书一对多books 表加 category_id 外键每次处罚对应一条借阅记录一对一在 fine_records 里存 borrow_id 唯一索引每本书当前只在一个书架一对多shelves.id 挂在 books 表多对多是实验五核心考点。常见的错法是把多条借阅信息做成逗号分隔串塞在学生表的一个字段里这直接违反第一范式。另一个高频错误是忘了中间表可以带自己的属性例如借书时间、应还时间、实际归还时间。必须把时间字段放进关系表而不是塞在实体表里。2.4 一个可以直接套用的最小设计模板如果题目场景没给足够细节或者你时间只剩半天我会直接用下面这套骨架去套。以通用“用户-书籍-借阅”场景为例四个核心表的 DDL 写法如下CREATE TABLE readers ( reader_id VARCHAR(20) PRIMARY KEY, reader_name VARCHAR(50) NOT NULL, department VARCHAR(100), max_borrow TINYINT DEFAULT 5, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE books ( book_id INT AUTO_INCREMENT PRIMARY KEY, isbn VARCHAR(20) NOT NULL, title VARCHAR(200) NOT NULL, category_id INT, total_copies TINYINT DEFAULT 1, UNIQUE KEY uk_isbn_copy (isbn, total_copies) ); CREATE TABLE borrow_records ( borrow_id BIGINT AUTO_INCREMENT PRIMARY KEY, reader_id VARCHAR(20) NOT NULL, book_id INT NOT NULL, borrow_date DATE NOT NULL, due_date DATE NOT NULL, return_date DATE, status TINYINT DEFAULT 0, FOREIGN KEY (reader_id) REFERENCES readers(reader_id), FOREIGN KEY (book_id) REFERENCES books(book_id), INDEX idx_reader_borrow (reader_id, status) ); CREATE TABLE fines ( fine_id BIGINT AUTO_INCREMENT PRIMARY KEY, borrow_id BIGINT NOT NULL, fine_days INT NOT NULL, fine_amount DECIMAL(6,2) NOT NULL, paid TINYINT DEFAULT 0, FOREIGN KEY (borrow_id) REFERENCES borrow_records(borrow_id) );每个字段都按需求文档来定NOT NULL 表达业务强约束DEFAULT 表达默认规则。UNIQUE 和 INDEX 是为查询路径服务的不是故意加约束。需要注意 max_borrow 这类信息放 readers 表而不是借阅规则表假设同一读者全局限制时这样最直接如果规则会随时间改才需要单独建规则表。做完 DDL 草稿建几个虚构数据测试一下能不能完成需求里的核心查询大部分边界问题在填数据阶段都能提前暴露。这个阶段的目标不是性能是让逻辑自洽。3. 从 ER 图到物理建表把设计转成可执行脚本的三个步骤需求分析完成不代表能直接交差实验五通常要求交付可运行的 SQL 文件。你手里要有三份独立脚本建库建表、示例数据导入、查询实现。下面按我习惯的顺序逐一落地。3.1 建库与编码参数一张字符集选错引发的乱码账建库时字符集用 utf8mb4不要用 utf8。utf8 在 MySQL 里最多存 3 字节遇到生僻字或 Emoji 直接报错数据导入后查出来是问号。排序规则统一用 utf8mb4_unicode_ci不同库表混用排序规则会导致 join 时无法使用索引。如果用的是云数据库还要额外确认默认参数组没开 only_full_group_by 限制之外的坑。CREATE DATABASE IF NOT EXISTS db_exp5 DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci; USE db_exp5;IF NOT EXISTS是为了脚本可重复执行。你在实验环境里可能反复跑同一个文件不加这个前缀第二次会直接报 database exists虽然不影响结果但截图交作业时不好看。排序规则用unicode_ci而不是general_ci前者按 Unicode 标准排序中文排序相对合理后者更老部分版本对中文拼音排序结果不稳定。连接层还有一个必须在客户端处理的事如果你的 SQL 文件里有中文注释导入时终端要执行SET NAMES utf8mb4否则注释和数据里的中文会乱。这个在命令行导入时最容易被忽略后面排查时会浪费大量时间所以一开始就写进脚本。3.2 数据导入INSERT 语句的批量化写法与自增主键处理数据文件一般有两种来源自己手写少量样例或者从 Excel/CSV 转成 SQL。实验五评分不看你数据量大建议只构造能覆盖所有查询条件的数据每张表 5~15 条足够。关键是覆盖边界某读者借了 5 本没还、某书被借走全部副本、某条记录刚好逾期一天。这些边界记录用来验证查询逻辑和触发器是否健壮。INSERT INTO readers (reader_id, reader_name, department, max_borrow) VALUES (20210001, A同学, 软件工程, 5), (20210002, B同学, 数据科学, 3), (20210003, C同学, 软件工程, 5); INSERT INTO books (isbn, title, category_id, total_copies) VALUES (9787111000011, 数据库系统概论, 1, 3), (9787111000028, 计算机网络, 2, 2); INSERT INTO borrow_records (reader_id, book_id, borrow_date, due_date, return_date, status) VALUES (20210001, 1, 2025-03-01, 2025-03-15, NULL, 0), (20210002, 2, 2025-03-10, 2025-03-24, 2025-03-20, 1);从文件导入时源数据如果带了自增主键值插入后要让 AUTO_INCREMENT 从最大值后继续如果源数据没带主键自己在 INSERT 列表里省略这一列即可。数据文件开头加一行SET FOREIGN_KEY_CHECKS 0可以避免父子表插入顺序的麻烦但导入完成后要记得恢复为 1否则后续的级联删除和外键约束全部失效这个坑很隐蔽。3.3 份文件组织三个脚本分开交付别混成一个“全家桶”我见过很多提交物是一个几百行的 main.sql建表、插数据、查询全混在一起读到一半报错很难定位。建议这样组织01_schema.sql建库、建表、外键、索引02_data.sql所有 INSERT 语句03_queries.sql每条需求对应的 SELECT 查询04_routines.sql触发器、存储过程、视图拆开的好处有两个一是出问题时不用从头跑两个文件之间可独立执行二是验收老师通常只看对应编号的查询文件结构清晰直接加分。每个文件顶部写一段注释说明文件内容和执行顺序注释里写清楚是自己完成的。用命令行导入单文件时顺序执行即可注意 03 和 04 依赖前两个文件的数据。如果在图形客户端里手动执行记得先切到对应数据库不然表会建到默认库里。4. 查询、视图与存储过程把需求翻译成 SQL 的实操套路实验五的主体工作量在查询实现。需求文档里的句子要转换成 SQL 有固定套路理解这个套路比背写法重要得多。这一章我会按处理顺序讲先写基础查询确认表数据对得上再做聚合和分组最后处理带业务规则的操作型需求。4.1 基础查询和连接先跑通数据链路再优化写法拿到需求先不看复杂函数第一步是“把涉及的表先 join 起来看看原始结果”。比如需求是“查询每位读者的借阅数量”我先写SELECT r.reader_id, r.reader_name, b.borrow_id FROM readers r LEFT JOIN borrow_records b ON r.reader_id b.reader_id ORDER BY r.reader_id;这一步的目的是确认 join 方向正确。LEFT JOIN 能保留没借过书的读者这是评分里常考的边界。如果需求说“每位读者的借阅数量”没借过的人也要出现在结果里用 INNER JOIN 会把零借阅的人过滤掉。很多同学在这里直接写 COUNT 然后发现少人就是因为 join 类型选错。确认数据链路正确后再加聚合SELECT r.reader_id, r.reader_name, COUNT(b.borrow_id) AS borrow_count, SUM(CASE WHEN b.status 0 THEN 1 ELSE 0 END) AS unfinished_count FROM readers r LEFT JOIN borrow_records b ON r.reader_id b.reader_id GROUP BY r.reader_id, r.reader_name;GROUP BY 的列要包含 SELECT 里所有非聚合列这是 MySQL 5.7 之后默认开启 only_full_group_by 的硬性要求。你如果只 GROUP BY r.reader_idSELECT 里带走 reader_name 在旧版本可能不报错但 8.x 会直接拒绝还是按标准写省心。COUNT 里传具体列名而不是 COUNT(*)可以配合 LEFT JOIN 准确统计关联次数——这里统计的是借阅记录数不是读者数。4.2 日期计算与逾期判断别用 NOW() 写死用 CURDATE() 做每日动态判断逾期查询是实验五几乎必出的题。需求常用句式是“列出所有已逾期未还的借阅记录”。实现里需要比较 due_date 和当前日期两个坑要避开一是不要用 NOW() 存到字段里二是日期比较要留当天边界。SELECT br.borrow_id, r.reader_name, bk.title, br.due_date, DATEDIFF(CURDATE(), br.due_date) AS overdue_days FROM borrow_records br JOIN readers r ON br.reader_id r.reader_id JOIN books bk ON br.book_id bk.book_id WHERE br.status 0 AND br.due_date CURDATE();CURDATE()取当前日期不带时间DATEDIFF返回的是两个日期相差的天数参数顺序是“减数在前”。如果你写成 DATEDIFF(due_date, CURDATE())逾期天数会是负数后面 ORDER BY 的方向就会全反。status 0表示未还这个状态值需要在建表时统一约定不然三条查询里状态定义不一致结果就会对不上。4.3 存储过程与事务批量更新类需求的标准包裹实验五里有一类需求是“还书处理”把 borrow_records 的状态改成已还、return_date 设为今天、如果有逾期自动生成罚款记录。这种多表联动操作不能写成单条 UPDATE要用存储过程包起来。我给出一个能直接改来用的模板DELIMITER $$ CREATE PROCEDURE sp_return_book(IN p_borrow_id BIGINT) BEGIN DECLARE v_due_date DATE; DECLARE v_overdue_days INT DEFAULT 0; DECLARE v_fine DECIMAL(6,2) DEFAULT 0; SELECT due_date INTO v_due_date FROM borrow_records WHERE borrow_id p_borrow_id AND status 0 FOR UPDATE; IF v_due_date IS NULL THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT record not found or already returned; END IF; SET v_overdue_days DATEDIFF(CURDATE(), v_due_date); IF v_overdue_days 0 THEN SET v_fine v_overdue_days * 0.50; INSERT INTO fines (borrow_id, fine_days, fine_amount, paid) VALUES (p_borrow_id, v_overdue_days, v_fine, 0); END IF; UPDATE borrow_records SET return_date CURDATE(), status 1 WHERE borrow_id p_borrow_id; END$$ DELIMITER ;FOR UPDATE是行级锁在多客户端同时操作同一条借阅记录时能防止重复还书。日常做实验数据量小看不出差别但这类语句能体现你是否理解并发场景。SIGNAL是主动抛错调用时如果传错 borrow_id客户端会收到明确错误提示而不是静默失败。计算罚款时每天 0.50 是演示值正式做要把单价改成题目给定金额。还注意一个业务规则如果需求要求“还书当天不算逾期”DATEDIFF 得到 0 时不会生成罚款这个逻辑天然满足。如果题目要求宽松一天就要把判断改成v_overdue_days 1别机械照抄参数。4.4 视图的加分用法把复杂统计封装成“查表”一样简单视图在实验五里的价值不是性能是让查询结果和语句长度都更清爽。比如后面有三条需求都需要“在借图书列表”你在开头建一个视图后续查询直接引用它CREATE VIEW v_borrowed_books AS SELECT br.borrow_id, r.reader_name, bk.title, br.borrow_date, br.due_date, br.status FROM borrow_records br JOIN readers r ON br.reader_id r.reader_id JOIN books bk ON br.book_id bk.book_id WHERE br.status 0;视图本质上是一段预定义查询创建时不存储数据每次查询视图时把 SELECT 语句展开执行。你不需要担心数据同步问题它永远实时反映基表内容。要注意的是视图里不能带 ORDER BY除非有 LIMIT因为排序逻辑属于查询时机不该锁死在视图里。当你查询视图时再排序写法更合理。引用视图的查询如果带了 WHERE 条件MySQL 会优化合并视图不会拖慢速度。但如果视图里 join 了三张表而你没用到其中某些表的数据性能上确实比直接查两张表差不过实验场景数据量小到不用考虑这一点。5. 实验五避坑指南场景、原因和处理办法这一章集中写我在帮人排查实验五时见到最多次的问题。每一条都有真实触发场景不是理论推演。你在提交前逐条对照检查能拦下一大半扣分点。5.1 中文乱码或导入报错破事最多的一个坑现象SQL 文件里中文注释或字符串数据显示为问号或者导入时报Incorrect string value错误。我遇到最夸张的一次是同学整个 students 表里的“张”字全部变成 ??排查了两小时。原因文件字节流与数据库连接字符集不一致。文件是 UTF-8但客户端连接用了 latin1 或者数据库建库时没指定字符集默认落到 latin1。SOURCE 命令导入时字符集不匹配中文就爆掉。解决先执行SET NAMES utf8mb4;再 SOURCE 文件。如果乱码已经发生把表 DROP 掉重建不要尝试 UPDATE 修复。另有一个习惯直接用 IDE 的数据导入功能而不是命令行图形工具一般会自动识别文件编码省掉一次手动指定。换用工具前先建个小文件测试两条数据确认中文没问题再导全量。5.2 外键约束导致 INSERT 失败父子表顺序不对现象插入借阅记录时报Cannot add or update a child row: a foreign key constraint fails。同学往往觉得数据没问题查了 student_id 确实存在。原因外键引用字段的类型或字符集不一致。常见的是 readers.reader_id 是 VARCHAR(20)而 borrow_records.reader_id 建成了 CHAR(20) 或 INTMySQL 类型不匹配直接拒绝。还有一种情况是两张表的字符集不同一个 utf8mb4一个 latin1也会触发这个错误。解决检查两表字段定义是否完全一致不一致就 ALTER 统一。SHOW CREATE TABLE borrow_records\G一眼能看出类型和字符集。如果确实需要临时跳过外键检查导入数据可以在导入前SET FOREIGN_KEY_CHECKS0但全部导完要恢复成 1后续触发器和级联操作都依赖它。5.3 聚合查询结果缺失了零记录的行现象查询每位读者借阅数量没有任何借阅记录的读者没出现在结果里。同学觉得是自己数据漏了反复 INSERT 补数据问题照旧。原因INNER JOIN 只保留两表匹配的行。没借过书的读者在 borrow_records 里没有对应记录天然被过滤。需求原文写“每位读者”时缺失的人等于不满足题意。解决把 INNER JOIN 改成 LEFT JOIN并确保聚合方向是对的。这里要连带着检查 COUNT( b.borrow_id ) 有没有写成 COUNT()。写成 COUNT() 会把 LEFT JOIN 产生的 NULL 行也计入本来 0 的记录会变成 1结果更隐蔽。5.4 自增主键跳号导致外键错位现象books 表手动指定了 book_id 之后再插入新书记录id 不连续导致数据文件里写死的关联关系全部错位。原因手动插入时指定了主键值AUTO_INCREMENT 计数器没有跟随更新继续从原值增长与预期不一致。更糟的是数据文件里后插入的 book_id 是写死的一旦错位就全错。解决如果数据文件里手动指定了主键导入全部数据后执行ALTER TABLE books AUTO_INCREMENT 1;MySQL 会把它设为当前最大主键值 1。在发数据文件前先执行这条语句重置计数器后面的自动插入才安全。如果是靠图形工具在已有数据上追加也要注意检查当前最大 ID 和数据文件里手写的 ID 范围不重叠。5.5 触发器循环调用不报错但执行效果错乱现象建了还书触发器还书时又去更新罚款表罚款表上的触发器又回写借阅表结果数据更新了两遍或者出现死锁。原因触发器里直接操作了同一个表或形成了触发器级联MySQL 会限制递归触发深度但不报错时逻辑已经重复执行。解决先明确业务规则一个事件的操作尽量放同一段存储过程里而不是拆成多个触发器。触发器只做一件事比如只更新 overdue 标记罚款计算放存储过程。审计类需求用触发器记录到日志表是合理的但不要在触发器里回写同一张业务表。这个问题的排查方式是在触发器里加SELECT debug临时观察执行次数确认后删掉调试语句。6. 验证脚本的编写习惯与两个终检技巧接近提交阶段大部分同学会选择把每条查询手动执行一遍肉眼看看结果。实际上有更可靠的方法把你期望的结果写成一个断言脚本数据库返回后自动比对差异。这个过程不需要复杂框架MySQL 本身就能做基础校验。验证思路是按需求逐条建立独立查询并判断返回行数和关键字段。比如检查“所有未还记录都是逾期的”这条需求可以构造一个反例查询SELECT COUNT(*) AS invalid_count FROM borrow_records br WHERE br.status 0 AND br.due_date CURDATE();结果应该是 0。如果返回非 0说明逾期判断的条件里混入了未到期记录逐一检查 WHERE 条件里的日期比较方向。第二个有效技巧是核对表数据完整性检查外键对应关系和状态字段取值范围。随机抽 3~5 条记录验证 join 后能正确返回再检查 fines 表里每条记录的 borrow_id 都指向存在且已还的记录避免生成罚款但没还书的矛盾状态。习惯上我会每次改动后都跑一遍全量验证脚本把所有断言查询放进05_verify.sql直接执行看输出。这样改一处不会带崩另一处。这里一个血泪经验不要在验证脚本里只 SELECT COUNT(*)因为 COUNT 为 1 不代表内容对必须加字段级断言。比如统计逾期罚款总额要固定一个阈值区间超出即报错否则你以为查到了实际上查的是错误数据。最后收尾时还有一个检查容易被忽略清理你建的所有中间辅助表。如果实验要求里声明了只交付哪些文件多余的临时表最好删掉。避免图表清单和交付脚本对不上而被追问。这套流程走完自己心里就有底了建表脚本能重复执行不报错、数据文件覆盖边界条件、每条需求至少对应一段可运行的 SQL、而验证脚本能保证改动不破坏已有功能。做数据库实验最容易吃到教训的地方从来不是某一个语法不会写而是做一半失去对数据的掌控感。保持脚本可重跑、数据可重建就是一个值得养成的习惯。希望帮到你。本文还有配套的精品资源点击获取
