数据库实验五实战：从SQL增删改查到索引与连接池

简介面向西北工业大学软件学院数据库课程的实验五配套资源是一份完整的电子商务数据库概念模型设计示例。压缩包共二十个文件、体积仅二百八十二KB文件类型以gif动图、doc与txt文档、cdm模型文件和htm网页为主gif演示了从注册登录、图书浏览、购物车管理到下单结算、订单查询的完整电商操作链路doc和txt提供实验题目与ER文字版说明cdm是可直接打开查看的概念数据模型。目前已有九百零九人学习下载适合软件学院学生及正在完成E-Commerce数据库设计作业的读者。借助这份资源可以快速理清会员、图书、购物车、订单、配送、支付与历史订单等核心实体及其联系对照ER图、Conceptual Data模型和操作演示检查自己的设计理解从需求分析到ER建模的完整思路。无论是课程实验、课程设计还是其他类似主题的数据库练习都有直接参考价值。1. 拿到西北工业大学软件学院数据库实验五.zip先别急着解压想清楚这三件事如果你在课程群或网盘里看到“西北工业大学软件学院数据库实验五.zip”它多半不是一份单纯的答题卡而是老师或助教打包好的课设材料合集。里面通常有实验指导书、表结构脚本、示例代码骨架甚至还有一份要求你按格式填写的实验报告模板。这个压缩包想解决的事情只有一件把“数据库增删改查”从课本上的例题变成一个能跑起来、能导出结果、能放进简历的完整工程。它适合谁适合正在补数据库实践课的学生也适合刚入职需要快速复习关系型数据库基础的新人。但拿到包先别急着双击解压。先做三个确认压缩包里的文件是用什么工具打包的、SQL 脚本是给哪个数据库写的、有没有额外密码保护。否则后面每跑一步都在给前面的失误交学费。这类课程设计包最值钱的不是“压缩包里的代码”而是一套能复现的环境和操作顺序。2. 解开实验五.zip文件结构、环境准备与导入 SQL 的完整流程2.1 用命令行解压避开双击解压的坑双击解压是最省事的方式但在实验五这种压缩包里反而容易埋雷。Windows 自带的右键解压会丢失文件权限遇到文件名编码不是 UTF-8 时还会解出一堆乱码目录。另一个常见问题是文件被标记成 ZIP 伪加密鼠标双击后各种报错请改用命令行。我一般先在压缩包所在目录打开终端然后按系统选择解压方式# Windows PowerShell 7-Zip假设 7z 已加入 PATH C:\Program Files\7-Zip\7z.exe x 实验五.zip -o实验五解压 -aoa# Linux / macOS优先尝试 unzip 并修复文件名编码 unzip -O gbk 实验五.zip -d 实验五解压如果你的 unzip 版本不支持-O参数用 7-Zip 代替7z x 实验五.zip -o实验五解压 -y参数说明-o实验五解压7-Zip 要求-o后面直接紧跟目标路径不能有空格否则会把后面当成另一个参数。-aoa遇到同名文件直接覆盖适合反复解压同一份材料。-O gbk告诉 unzip 压缩包内文件名使用 GBK 编码。Windows 中文环境打出来的 zip 包文件名编码常是 GBK不在命令行指定的话解压后看到的就是“锟斤拷”一类乱码。解压完后用ls或dir检查顶层结构确认文件夹层级是否符合预期。如果发现压缩包里还有一个同名嵌套 zip先别管那是老师把上一轮实验材料也塞进来了。2.2 看清单数据库实验五里通常躺着哪四类文件打开目录后先做一次文件分类。我经手过的课程设计包结构上大体逃不出这四类文件 / 目录作用你需要注意的坑实验指导书 PDF / docx说明实验目标、表结构、提交要求先读它不要凭标题猜需求schema.sql 或 init.sql建库建表脚本可能附带初始数据注意语法兼容哪个数据库是 MySQL 还是 SQL Serversrc/ 或 code/ 目录可运行的代码骨架不一定完整经常需要自己补数据源配置report 模板实验报告格式得分好坏一半看这里实验五这种序号型课设内容走到后半程基本都会落到“学生-课程-选课”或者“用户-订单-商品”这类经典模型。如果 SQL 脚本里出现了CREATE TABLE student、CREATE TABLE course、CREATE TABLE sc那题目范围基本就锁定了。不要急着分析全部代码先把表结构脚本和实验指导书对照一遍确认老师要求的是“只写 SQL”还是“写一个能跑的 Java/Python 程序”。2.3 把 schema.sql 导入数据库用命令行跑通最小路径明确脚本针对 MySQL 后先在本地把库建出来。我习惯用命令行而不是图形化工具做首次导入因为报错信息更直接。mysql -u root -p -e create database if not exists lab5 default charset utf8mb4; mysql -u root -p lab5 schema.sql如果schema.sql里已经写了CREATE DATABASE和USE则不需要手动建库。还有一种情况是脚本用source命令从外部导入数据文件那就要保证当前 mysql 会话的工作目录能正确拼出相对路径。遇到导入报错先记下错误文本和行号再用编辑器打开脚本对应位置而不是反复点重试。提示导入前先确认脚本头部有没有SET NAMES utf8mb4。没有的话在导入命令里加上--default-character-setutf8mb4可以省掉后面一大半中文乱码问题。导入成功后用这四条命令验证结构是否完整USE lab5; SHOW TABLES; DESC student; SELECT COUNT(*) FROM course;这一步的重点不是查数据而是确认外键有没有真正生效。用SHOW CREATE TABLE sc;查看建表语句如果输出里没有CONSTRAINT说明脚本里的外键定义被忽略或没执行成功后面做级联删除时会莫名出错。2.4 用 Navicat 或命令行可视化检查数据如果你更喜欢图形界面打开 Navicat 连接本地 MySQL连接名随意主机127.0.0.1端口3306用户root。连接后新建查询输入SELECT * FROM student LIMIT 10;如果实验环境换成达梦数据库或国产数据库Navicat 同样支持连接只是驱动选择不同。第一次连接时注意选择正确的数据库类型驱动弄错会一直报“无法加载驱动程序”。3. 把实验五的“数据库增删改查”跑通建表到联表查询的完整路径3.1 看实体关系先别急着进代码库实验五这类任务核心往往不是考你做了多少操作而是看你能不能把关系模型建起来。学生选课是一个经典三元关系学生、课程、选课记录。学生与课程之间是多对多关系选课表就是中间关系表。建表前先回答三个问题学生表的自然主键是学号还是自增 ID成绩字段是允许 NULL还是默认为 0删除学生时选课记录是级联删除还是保留记录但不关联学生这三个问题的答案直接影响建表语句。课程实验里最常见的翻车方式是所有表都用单列主键忽略(sno, cno)作为联合主键的情况结果同一个学生选了同一门课两次数据层面完全无法拦截。3.2 最小建表语句主键、外键、字符集一次到位就算实验包里已经给了建表脚本我也建议你手抄一份因为考试或面试时可能要求现场写。以下是一个能直接跑通的最小版本CREATE TABLE student ( sno CHAR(10) PRIMARY KEY, sname VARCHAR(20) NOT NULL, ssex ENUM(M, F) DEFAULT M, sbirthdate DATE, sdept VARCHAR(40) ) ENGINE InnoDB DEFAULT CHARSET utf8mb4; CREATE TABLE course ( cno CHAR(6) PRIMARY KEY, cname VARCHAR(40) NOT NULL, credit DECIMAL(3, 1) ) ENGINE InnoDB DEFAULT CHARSET utf8mb4; CREATE TABLE sc ( sno CHAR(10), cno CHAR(6), grade DECIMAL(5, 2), PRIMARY KEY (sno, cno), CONSTRAINT fk_sc_student FOREIGN KEY (sno) REFERENCES student(sno), CONSTRAINT fk_sc_course FOREIGN KEY (cno) REFERENCES course(cno) ) ENGINE InnoDB DEFAULT CHARSET utf8mb4;逻辑说明sno用CHAR(10)而不是VARCHAR因为学号固定长度定长字段在等值查询时效率略好也防止输入长短不一。ENUM(M, F)能挡住非法性别值但如果你后续要支持“未知”性别得用ALTER TABLE改类型牺牲一点灵活性。选课表sc的联合主键(sno, cno)是防止重复选课的第一道防线。外键约束命名fk_sc_student后期看错误日志时能一眼看出是哪张表、哪个约束在报错。建完后用一条带连接查询的语句验证外键是否顺畅SELECT student.sname, course.cname, sc.grade FROM sc JOIN student ON sc.sno student.sno JOIN course ON sc.cno course.cno LIMIT 5;如果这条语句能查出数据说明三张表的关联关系建对了。3.3 CRUD 四件套增删改查的常见写法与参数化手工在 Navicat 里点增删改查不算本事因为作业最终要求你写代码或存储过程。这里给出一段完整的 Python 增删改查代码实验五代码骨架里最常缺的就是这部分。import pymysql config { host: 127.0.0.1, port: 3306, user: root, password: your_password, database: lab5, charset: utf8mb4, } conn pymysql.connect(**config) cursor conn.cursor() # 新增一条选课记录 sql INSERT INTO sc(sno, cno, grade) VALUES (%s, %s, %s) cursor.execute(sql, (20230001, CS101, 88.5)) # 修改成绩 sql UPDATE sc SET grade %s WHERE sno %s AND cno %s cursor.execute(sql, (91.0, 20230001, CS101)) # 删除记录 sql DELETE FROM sc WHERE sno %s AND cno %s cursor.execute(sql, (20230001, CS101)) # 查询所有及格学生的成绩 sql SELECT sname, cname, grade FROM student s JOIN sc ON s.sno sc.sno JOIN course c ON c.cno sc.cno WHERE sc.grade 60 cursor.execute(sql) rows cursor.fetchall() for row in rows: print(row) conn.commit() cursor.close() conn.close()参数说明所有用户输入都用%s占位符传给cursor.execute不要用字符串拼接。这是防止 SQL 注入的最低门槛数据库实验报告里写上这一条能加分。conn.commit()必须放在插入、更新、删除之后。如果忘记提交当前连接能看到数据换一个连接就看不到这在调试时非常像“玄学”。fetchall()会把结果一次性加载到内存。如果数据量很大改用fetchmany(size)分批读取否则实验五的数据量不大不用过度设计。3.4 联表查询与分组统计拿高分的关键分水岭数据库实验做到后半段老师通常会让统计人数或平均分。最常见的需求是“查询每门课的选课人数和平均分按平均分降序”这也是网上被问烂的题目。SELECT c.cno, c.cname, COUNT(sc.sno) AS selected_count, AVG(sc.grade) AS avg_grade FROM course c LEFT JOIN sc ON c.cno sc.cno GROUP BY c.cno, c.cname HAVING COUNT(sc.sno) 2 ORDER BY avg_grade DESC;参数说明LEFT JOIN保证没有学生选的课程也出现在结果里COUNT(sc.sno)会返回 0。如果改成INNER JOIN没被选过的课程会被过滤掉这不符合题意。GROUP BY后面必须带上c.cname否则在 MySQL 8.0 默认ONLY_FULL_GROUP_BY模式下直接报错。HAVING是在分组之后过滤分组条件不能用WHERE代替。如果这个查询很慢优先看sc表上有没有(sno, cno)联合索引以及grade列的索引是否被使用。联表查询写不出来实验五大概率不及格写出来但不会解释JOIN和LEFT JOIN的区别答辩也会被问住。建议顺手执行EXPLAIN看看执行计划说明自己知道索引怎么走。4. 再往前一步存储过程、触发器、索引与数据库连接池4.1 存储过程与触发器实验报告里最常被追问的地方数据库课程设计到实验五这个阶段存储过程和触发器已经成了必选项。老师想看到的不是“我会写 SQL”而是“我能把业务逻辑放在数据层”。一个典型的存储过程输入学号输出平均成绩。DELIMITER $$ CREATE PROCEDURE get_avg_grade(IN p_sno CHAR(10), OUT p_avg DECIMAL(5, 2)) BEGIN SELECT AVG(grade) INTO p_avg FROM sc WHERE sno p_sno; END$$ DELIMITER ;调用方式CALL get_avg_grade(20230001, avg); SELECT avg;参数说明IN是入参OUT是出参。这里用OUT把结果回传给客户端而不是用SELECT直接输出是为了让程序通过游标或输出参数接住结果。DELIMITER是客户端工具的分隔符设置不是 SQL 语法。如果不改分隔符BEGIN...END里的分号会提前结束整个存储过程定义。存储过程适合封装复杂的、多条语句的事务逻辑。但如果只是单条SELECT AVG你大可以用普通 SQL不必非要包一层过程。触发器通常用在“选课后自动更新统计表”的场景CREATE TABLE student_summary ( sno CHAR(10) PRIMARY KEY, total_courses INT NOT NULL DEFAULT 0 ); INSERT INTO student_summary(sno, total_courses) SELECT sno, 0 FROM student; DELIMITER $$ CREATE TRIGGER trg_after_insert_sc AFTER INSERT ON sc FOR EACH ROW BEGIN UPDATE student_summary SET total_courses total_courses 1 WHERE sno NEW.sno; END$$ DELIMITER ;这里的NEW.sno指新插入选课记录里的学号。触发器在低并发环境下很省心但在高并发批量导入时可能成为瓶颈。实验报告里写一句“触发器能保证绕过业务系统时统计仍然准确但高频场景建议改为应用层维护”比硬吹触发器好用更可信。4.2 索引不是每个列都加先看执行计划很多同学交实验五之前会把所有 WHERE 字段都加上索引然后发现数据库写入慢了很多。索引的价值在于减少扫描行数但每建一个索引写入时都要额外维护 B 树索引。正确做法是先用执行计划判断。EXPLAIN SELECT * FROM sc WHERE grade 60;观察type列typeALL表示全表扫描数据量大时是性能灾难。typerange表示索引范围扫描说明条件被索引覆盖。typeref表示非唯一索引等值匹配是多表连接里比较理想的情况。针对上面这条查询加索引试试CREATE INDEX idx_sc_grade ON sc(grade); EXPLAIN SELECT * FROM sc WHERE grade 60;执行计划里如果type变成range、key变成idx_sc_grade说明索引生效了。但如果分数小于 60 的数据占了整张表的 30% 以上MySQL 优化器可能会放弃索引转而走全表扫描因为回表代价比扫描还高。实验报告的“优化”章节里能写出这个权衡比单纯贴执行计划截图显水平。4.3 数据库连接池解决“连接耗尽”的硬配置程序里如果每次操作都新建数据库连接请求量稍微高一点就报Too many connections。你用到的连接池工具无论 Java 的 HikariCP 还是 Python 的 dbutils核心参数都绕不开下面几项。以 Java 侧 HikariCP 为例HikariConfig config new HikariConfig(); config.setJdbcUrl(jdbc:mysql://localhost:3306/lab5?useSSLfalseserverTimezoneAsia/Shanghai); config.setUsername(root); config.setPassword(password); config.setMaximumPoolSize(20); config.setMinimumIdle(5); config.setConnectionTimeout(30000); config.setIdleTimeout(600000); HikariDataSource ds new HikariDataSource(config);参数说明maximumPoolSize连接池最大连接数。对课设项目20 足够。不要看到别人设 100 就跟风先查 MySQL 侧max_connections:SHOW VARIABLES LIKE max_connections;应用侧连接数超过数据库上限启动即报错。minimumIdle空闲时保留的连接数。保留太少突发流量来时都在建连请求会变慢。connectionTimeout从池中借连接的最大等待时间。设为 30000 毫秒可以避免并发高峰时无限等待。idleTimeout空闲连接被回收的时间。注意它必须小于 MySQL 的wait_timeout否则连接会被 MySQL 先切断应用侧拿到的是坏连接。数据库连接池不是铺在报告里充字数而是让老师敢在你的代码上点五次“刷新”。没有连接池的演示刷新第三次大概率卡死。5. 避坑记录实验五配套数据库踩过的 5 个经典坑5.1 中文乱码为什么到处都是根因在建库时没指定字符集现象执行SELECT * FROM student WHERE sname张三返回空结果但表格里明明有“张三”或者导入后看到???乱码。原因建库时没指定字符集MySQL 服务端可能用了latin1客户端连接却是utf8mb4两边编码不一致导致存进去的数据已经损坏。解决先用SHOW CREATE DATABASE lab5;查看默认字符集。如果不是utf8mb4重新建库并显式指定DROP DATABASE IF EXISTS lab5; CREATE DATABASE lab5 DEFAULT CHARSET utf8mb4; USE lab5; SOURCE schema.sql;还要检查连接字符串和 Python 配置里的charsetutf8mb4三处必须保持一致。字符集问题不解决后面所有中文数据都是黑匣子。5.2 导入 SQL 脚本报错报的行号对不上文件里的行号现象Navicat 运行schema.sql报 “Line 7: You have an error in your SQL syntax”但打开文件第 7 行看起来完全正常。原因SQL 文件带 BOMEF BB BF三个不可见字符某些数据库客户端的 SQL 解析器会把 BOM 当作第一个 token 的组成部分导致第一条语句就报错。解决用 VS Code 打开脚本点击右下角“UTF-8”选择“通过编码保存”改成“UTF-8 无 BOM”后保存再重新导入。如果手头只有命令行用sed -i 1s/^\xEF\xBB\xBF// schema.sql去掉 BOM。5.3 外键导致删除失败不是锁是子表还有引用数据现象执行DELETE FROM student WHERE sno20230001;报错Cannot delete or update a parent row: a foreign key constraint fails。原因sc表里还有指向这个学生的选课记录外键约束不允许直接删除父表数据。解决先删子表再删父表或者改用级联规则DELETE FROM sc WHERE sno 20230001; DELETE FROM student WHERE sno 20230001;如果希望删除学生时自动清理选课记录建表时加上ON DELETE CASCADE。注意ON DELETE SET NULL要求子表对应列允许 NULL否则还是报错。实验报告中至少写清楚你的业务规则而不是让老师猜。5.4 连接池打满报 HikariPool-1 timeout先查慢查询和连接泄漏现象压测或连续刷新页面后日志报HikariPool-1 - Connection is not available, request timed out after 30000ms。原因程序里拿到的连接没有归还池中最典型的是conn.close()没有执行另一个原因是某条慢查询占着连接迟迟不返回。解决在 Java 里用 try-with-resources 保证连接自动关闭try (Connection conn ds.getConnection(); PreparedStatement ps conn.prepareStatement(sql); ResultSet rs ps.executeQuery()) { // 业务处理 }同时进 MySQL 查当前连接状态SHOW PROCESSLIST;如果看到大量Sleep状态的连接说明连接没被正确归还。杀掉实验进程、重启服务通常能恢复但根子还是代码泄漏。5.5 ZIP 伪加密和 missing zip entry不是文件损坏是打包姿势问题现象双击实验五.zip提示需要密码但你并没有设置密码或者用unzip -l能看到文件名真正解压时却报missing zip entry。原因部分打包工具把加密标志位写进了 ZIP 头但文件数据并没有真正加密这就是网上常说的 zip 伪加密另一类情况是压缩包内带了特殊字符的文件名解压工具处理路径时出错。解决先用 7-Zip 测试压缩包完整性7z t 实验五.zip如果 7-Zip 能通过测试尝试用7z x强制解压通常会绕过伪加密限制。遇到单文件报错可以单独提取7z e 实验五.zip 目录/文件.sql -o输出目录如果压缩包结构已经损坏用zip -FF修复zip -FF 实验五.zip --out 修复.zip unzip 修复.zip这是最常用的“后悔药”。另外有同学为了改缩进用第三方工具把压缩包拆过了一遍重新打包时文件名编码从 GBK 变成了 UTF-8又把原文件覆盖掉结果源文件丢失。建议解压出来的源文件目录保持只读不要原地改。6. 把实验五变成你的数据库实践底子验证与提交技巧6.1 一键回归从零开始重建整个实验环境实验五不是交上去就完了老师大概率会在不同环境跑你的代码。为了避免“在我电脑上好好的”这种悲剧我把重建流程写成一个脚本每次提交前跑一遍#!/bin/bash set -e mysql -u root -ppassword -e DROP DATABASE IF EXISTS lab5; CREATE DATABASE lab5 DEFAULT CHARSET utf8mb4; mysql -u root -ppassword lab5 schema.sql python test_crud.py python test_proc.pyset -e的含义是其中任何一条命令失败脚本立即退出。这样能暴露“schema.sql 是否完整”“代码里是否硬编码了本机路径”之类问题。6.2 交付前自己验四件事建表、造数据、跑查询、关连接提交前至少做四轮检查别只用鼠标点几下界面就完事建表脚本能不能从空库跑一遍把数据库删掉重新执行schema.sql确认没有报错。实验要求里的每一条查询是不是都有对应结果把结果导出成txt或csv作为附件放进压缩包。关键查询是否用到索引每个核心查询前面加一次EXPLAIN能自己解释执行计划的type和key字段再交。程序跑完有没有主动关闭连接和释放资源先开任务管理器确认没有残留 java/python 进程占用 3306。6.3 留下一个 300 字的 README 文件最后在实验五解压根目录里放一个README.txt写清楚数据库版本、端口、账号、建库顺序、启动命令。不需要大篇幅四五行足够。这件事看起来不起眼但当你三个月后再打开这个目录准备复用时就知道了一份干净的实验材料比多写五十行代码更值钱。我自己带过的课设小组里最惨的不是代码跑不通而是项目里根本没有 README老师换一台电脑就不知道该点什么按钮。数据库实验五的意义不在于那一份压缩包能给你多少现成答案而在于你有没有把自己的思路沉淀成一套可以复用的流程。最后给你一句我经常对自己说的话数据库实验不是把 SQL 跑通就结束跑通之后记得回头看看执行计划、索引、连接池那些看不见的配置。把这些习惯留住了这一份实验五.zip 才没有白做。希望帮到你。本文还有配套的精品资源点击获取