简介北京理工大学计算机学院“数据库原理与设计”课程上机实验完整材料包面向该课程本科生复习实验内容也适合备考数据库原理或需要规范练习SQL的开发者。压缩包共12个文件、4.69MB包含4个SQL脚本、3个JavaScript文件、2个JSON配置、1份PDF实验报告、1份Word报告及1个说明文档分别对应建库建表、增删改查、嵌入式SQL调用、数据清理和实验总结等环节SQL脚本覆盖数据定义、查询、更新与删除等核心操作JavaScript文件则展示了在Node.js环境操作数据库的示例思路清晰便于对照学习。目前已有168人学习下载。通过该材料可完整复盘建表、查询、更新、删除及索引优化等典型实验任务实验报告详细记录了操作过程、遇到的问题与解决思路有助于加深对关系数据库设计与SQL实践的理解可直接作为课程复习和实验自查的参考资料。1. 北理工“数据库原理与设计”上机实验一份zip背后要掌握的真实技能把_sjkylysj.zip解压出来的那一刻你发现里面并不是现成的实验答案而是一堆实验题目、建表脚本和几段残缺不齐的SQL。这是北京理工大学计算机学院“数据库原理与设计”课程的上机实验覆盖的是一套经典到不能再经典的内容从建库建表、完整性约束到增删改查、多表查询再到视图、索引、存储过程、触发器、事务并发。这份zip能解决什么问题至少三件事让缺课的补完实验、让跨考或求职的人在面试前把数据库捡起来、让想复现课程实验的人有一份能落地的方案。适合谁正在上这门课的学生以及准备数据库岗位面试的从业者。下面按最常见的实验流程来写不依赖特定版本重点是把每一步讲到你敢照着敲。2. 搭好实验环境再解压北理工数据库课的三种主流选型与导入流程实验环境这件事看着简单实际翻车率最高。很多人解压zip后第一反应是双击.sql文件结果系统弹出一个记事本窗口然后就不知下一步怎么办了。其实这门课上机实验的第一步是选一套能跑通全部脚本的数据库再把它装到一个不干扰日常工作的环境里。2.1 实验用哪套数据库先看清题目再决定别让“SQL Server vs MySQL”浪费你半天北理工“数据库原理与设计”这门课上机实验历年用的数据库并不固定。有的老师带MySQL有的老师按教材用SQL Server个别实验还会让你接触达梦、人大金仓这类数据库产品。拿到zip里的实验说明前先别急着装软件。选型安装成本SQL兼容性存储过程/触发器实验报告截图建议MySQL 8.0低一条Docker命令即可教材SQL基本兼容少数函数名不同支持语法要按MySQL调整Navicat/DBeaver截图方便默认首选SQL Server 2019 Developer中等安装包大建议DockerT-SQL更接近教材示例支持且调试方便SSMS截图清楚老师明确指定时用达梦8/DM中等需申请试用兼容Oracle和MySQL风格支持细节略不同自带工具一般课程要求国产化时用Oracle高配置复杂实验SQL基本可跑功能强但上手难工具偏重不推荐作为课程实验首选我这里主要以MySQL 8.0往下讲。理由很实际zip里的建表脚本绝大多数能在MySQL里直接跑以后准备数据库岗位面试时增删改查、数据库优化、数据库并发锁这些高频话题也都能在同一个环境里练习。Windows、Linux、macOS都能用Docker跑能绕开“我明明装成功了却连不上”这种最浪费时间的局面。如果课程通知明确写了SQL Server就把SQL语法里MySQL的AUTO_INCREMENT换成IDENTITY(1,1)把反引号去掉剩下的思路完全一致。2.2 用一条Docker命令起实验库端口、字符集、账号一次配好常见做法是装一个本地MySQL实例但我更推荐用Docker跑单机容器尤其是你电脑上已经装过MySQL或装过其他数据库时。端口冲突和PATH问题是不确定因素容器隔离能省掉后续的麻烦。docker run -d --name db_exp \ -p 3307:3306 \ -e MYSQL_ROOT_PASSWORDroot_123 \ -e MYSQL_DATABASEsjky \ -e TZAsia/Shanghai \ mysql:8.0 \ --character-set-serverutf8mb4 \ --collation-serverutf8mb4_unicode_ci逻辑说明-d表示后台运行--name db_exp给容器命名后续日志和停止操作都靠这个名字-p 3307:3306把容器内MySQL的3306端口映射到宿主机的3307避开本机可能已经占用的3306端口。MYSQL_ROOT_PASSWORD设置root密码MYSQL_DATABASEsjky会在容器首次初始化时自动创建一个名为sjky的数据库省去手动建库。TZAsia/Shanghai让容器内时间与东八区一致实验报告里的日志时间才正常。后两个长参数是MySQL服务端的启动参数字符集设为utf8mb4而不是utf8是为了能完整保存中文和特殊符号。课程实验里“姓名”“院系”这类字段全是中文字符集选错导入后就会变成一排问号。容器启动后验证环境是否就绪docker exec -it db_exp mysql -uroot -proot_123 -e SELECT VERSION(); SHOW DATABASES;如果看到版本号和一个默认的sjky数据库环境就绪。再强调一个细节本机已有MySQL的话别把新容器也映射到3306。连错实例是排查半天也发现不了的低级问题用3307端口区分是最省心的做法。2.3 导入压缩包里的SQL外键顺序和字符集是两座山解压zip后通常会看到建表脚本、插入数据脚本、实验要求文档三类文件。导入前先看一眼建表脚本的文件头确认保存编码是UTF-8无BOM。Windows记事本保存的文件常带BOMMySQL命令行用source导入时第一行会出现Unknown command \\xEF这类诡异报错。处理办法是用VS Code或Notepad把文件另存为“UTF-8无BOM”再导入。推荐统一走命令行重定向导入mysql -h127.0.0.1 -P3307 -uroot -proot_123 \ --default-character-setutf8mb4 sjky 01_create_tables.sql mysql -h127.0.0.1 -P3307 -uroot -proot_123 \ --default-character-setutf8mb4 sjky 02_insert_data.sql参数说明-h127.0.0.1指定连接地址-P3307对应刚才映射的宿主机端口--default-character-setutf8mb4让客户端按UTF-8发送SQL文本和数据库字符集保持一致避免中文乱码。sjky指定默认数据库即使脚本里忘了写USE sjky;也能落在正确库里。导入失败时不要反复重跑。最常见的报错是外键约束Cannot add foreign key constraint。原因是建表顺序不对比如先创建带外键的选课表再创建父表student。临时关掉外键检查再导入是课程实验里最实用的后悔药mysql -h127.0.0.1 -P3307 -uroot -proot_123 \ --default-character-setutf8mb4 \ -e SET FOREIGN_KEY_CHECKS0; SOURCE /path/to/01_create_tables.sql; SET FOREIGN_KEY_CHECKS1; sjkyFOREIGN_KEY_CHECKS是会话级别开关只对当前连接生效关闭期间不校验外键。导完以后一定要执行SHOW TABLES;确认表数量再SELECT COUNT(*) FROM student;确认数据量别等写实验报告时才发现某张表是空的。3. 数据库增删改查实验把建表、约束、查询按评分点写全这章聚焦核心但老师最爱扣分的部分。上机实验评分通常不只看能不能跑通更看实验报告里对设计理由的说明。建表时为什么用这个类型查询为什么用LEFT JOIN这些才是报告的主体。3.1 一个标准的教学库长什么样学生、课程、选课三张表的建表脚本几乎每个数据库原理上机题都涉及学生选课这个经典三表模型。按下面这个结构建表就足够覆盖题目里“体会主键、外键、CHECK约束”的要求USE sjky; CREATE TABLE student ( sno CHAR(9) PRIMARY KEY, sname VARCHAR(20) NOT NULL, ssex CHAR(2) DEFAULT 男, sage INT CHECK (sage BETWEEN 15 AND 45), sdept VARCHAR(20) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE course ( cno CHAR(4) PRIMARY KEY, cname VARCHAR(40) NOT NULL, cpno CHAR(4) NULL, credit SMALLINT CHECK (credit 0), CONSTRAINT fk_course_cpno FOREIGN KEY (cpno) REFERENCES course(cno) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE sc ( sno CHAR(9) NOT NULL, cno CHAR(4) NOT NULL, grade DECIMAL(5,1) CHECK (grade BETWEEN 0 AND 100), PRIMARY KEY (sno, cno), CONSTRAINT fk_sc_sno FOREIGN KEY (sno) REFERENCES student(sno), CONSTRAINT fk_sc_cno FOREIGN KEY (cno) REFERENCES course(cno) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;逻辑说明sno用CHAR(9)而不是VARCHAR学号长度固定定长字符类型在等值查询时开销更小这也正好能写进实验报告的选型理由。sc表用(sno, cno)复合主键表达“同一个学生选同一门课只有一条成绩记录”。外键约束都起了名字之后删约束、查报错信息时容易定位。课程表的cpno是自引用外键指先修课程这里故意允许NULL因为第一门课没有先修课。参数说明MySQL 8.0.x之后CHECK会强制生效如果用的是5.7CHECK只写进表结构但不会真正拦截。所以实验报告里写“我测试了年龄大于45插入失败”之前先确认版本。DECIMAL(5,1)表示总长5位、小数后1位成绩最大999.9足够课程实验使用。ENGINEInnoDB是必须的外键和事务都依赖这个引擎换成MyISAM后面做事务并发实验时会发现锁机制完全不同。3.2 增删改查四件事先开事务再演示别把测试数据搞脏增删改查部分最容易看出你是真会还是背命令。我的做法是先START TRANSACTION;演示完后ROLLBACK;既展示了事务概念又不污染后续查询实验的数据。START TRANSACTION; INSERT INTO student (sno, sname, ssex, sage, sdept) VALUES (20230001, 李雷, 男, 20, 计算机学院); UPDATE student SET sdept 软件学院 WHERE sno 20230001; SELECT * FROM student WHERE sno 20230001; DELETE FROM student WHERE sno 20230001; ROLLBACK;逻辑说明START TRANSACTION开启一个事务UPDATE和DELETE都带主键WHERE条件精确影响一行ROLLBACK把刚才的插入、更新、删除全部回滚。于是后续实验里student表的原始数据没有任何变化你也不用在报告里编“我插入后又删除了”。参数与习惯说明INSERT可以省略列名但一旦表结构调整省略列名的写法极易错位建议永远写出列名。UPDATE最怕漏WHERE漏了就全表更新。如果把危险操作放进显式事务还能用ROLLBACK兜底直接执行就只能从备份里恢复。实验课上我会让学生把危险SQL都放进事务里这是从源头避免翻车的习惯。3.3 查询实验的三种必考写法连接、子查询、分组聚合查询题在实验报告里占比最大三个必练题型是多表连接、带子查询的条件过滤、分组统计后的筛选。-- 多表连接查询成绩不低于60分学生的学号、姓名、课程名、成绩 SELECT s.sno, s.sname, c.cname, sc.grade FROM student s JOIN sc ON s.sno sc.sno JOIN course c ON c.cno sc.cno WHERE sc.grade 60 ORDER BY sc.grade DESC; -- 子查询查选过“数据库原理”课程的学生姓名 SELECT sname FROM student WHERE sno IN ( SELECT sno FROM sc WHERE cno (SELECT cno FROM course WHERE cname 数据库原理) ); -- 分组聚合查选课人数超过3人的课程及其平均成绩 SELECT cno, COUNT(*) AS stu_cnt, AVG(grade) AS avg_grade FROM sc GROUP BY cno HAVING COUNT(*) 3 ORDER BY avg_grade DESC;逻辑说明多表连接用表别名s、c、sc省去反复写全表名的冗长也让连接条件一目了然。第一段是内连接只会留下既有学生又有选课记录且满足成绩条件的行。如果想看“没选课的学生也要列出来”就得改成LEFT JOIN sc这是数据库岗位面试里常见的追问点。第二个查询用了嵌套子查询外层WHERE sno IN接收内层查出的学号集合。注意如果内层子查询返回多行外层用就会报Subquery returns more than 1 row正确做法是改回IN。第三个查询里WHERE不能换成HAVING因为WHERE在分组前过滤行HAVING在分组后过滤组。AVG(grade)会自动忽略NULL成绩但COUNT(*)会统计所有行两者混用会导致报告里的数字对不上。把这三条查询在前面加EXPLAIN跑一遍还能看到有没有走全表扫描正好衔接下一章的索引实验。4. 视图、存储过程与事务并发数据库原理实验里的拉分项很多同学把基础部分做完就以为实验结束了实际上评分表里拉开差距的是这一章。视图体现你对逻辑数据独立性的理解索引体现你是否做过数据库优化存储过程和触发器体现你能把业务逻辑放进数据库事务则直接对应“数据库并发锁”这个面试高频话题。4.1 索引实验一条EXPLAIN从ALL到ref报告里能多写两页索引部分通常要求你给某张表的字段建索引并说明查询性能变化。教学库里数据量小时间上感觉不到快慢但EXPLAIN输出会告诉你访问类型的变化。EXPLAIN SELECT * FROM sc WHERE sno 20230001 AND cno C001;建索引前执行结果里type通常是ALL说明全表扫描。接着建复合索引CREATE INDEX idx_sc_sno_cno ON sc(sno, cno); SHOW INDEX FROM sc; EXPLAIN SELECT * FROM sc WHERE sno 20230001 AND cno C001;逻辑说明第二次EXPLAIN的type应变成refkey列值是idx_sc_sno_cnorows估算明显下降。这就是数据库优化最直观的证据实验报告的索引部分可以拿这两次输出做前后对比。参数说明复合索引遵循最左前缀原则查询条件里必须出现sno才可能走这个索引只按cno条件查时这个索引帮不上忙。实验报告里要写清楚“为什么建复合索引而不是两个单列索引”原因是InnoDB中两个单列索引很难被一起用于同一查询反而白白增加写入时的维护成本。索引不是越多越好写操作多的表上每多一个索引写入放大一分。4.2 存储过程与触发器把两个题型合成一个实验场景实验题目里存储过程和触发器往往是分开的两题但合在一起写反而更好演示触发器自动维护统计表存储过程负责查统计结果。DELIMITER $$ CREATE PROCEDURE get_student_avg ( IN p_sno CHAR(9), OUT p_avg DECIMAL(5,2) ) BEGIN SELECT AVG(grade) INTO p_avg FROM sc WHERE sno p_sno; END$$ DELIMITER ; CALL get_student_avg(20230001, avg); SELECT avg;逻辑说明DELIMITER $$把MySQL语句结束符临时改成$$。存储过程内部有多条以分号结尾的语句如果还用分号当结束符客户端会在BEGIN处提前截断并报语法错误。过程体里SELECT AVG(grade) INTO p_avg把聚合结果写入输出参数IN和OUT分别表示入参和出参。触发器部分先建课程统计表再写一个AFTER INSERT触发器CREATE TABLE course_stat ( cno CHAR(4) PRIMARY KEY, selected_cnt INT NOT NULL DEFAULT 0 ); DELIMITER $$ CREATE TRIGGER trg_sc_after_insert AFTER INSERT ON sc FOR EACH ROW BEGIN UPDATE course_stat SET selected_cnt selected_cnt 1 WHERE cno NEW.cno; END$$ DELIMITER ;逻辑说明FOR EACH ROW表示按插入的每一行触发NEW.cno是当前插入行的新值OLD则在DELETE或UPDATE触发器中指代旧行。这个触发器会在每次往选课表插入成绩时把对应课程统计表的选课人数加一。参数说明触发器不是银弹批量导入初始化数据时逐行触发会让导入慢好几倍。如果实验数据是几万条我建议先导完数据再建触发器或者在导入前临时DROP TRIGGER。第5章会专门展开这个坑。4.3 事务并发实验数据库死锁不是玄学是加锁顺序冲突数据库并发锁和数据库死锁是实验里的重头戏。做法是开两个终端连接模拟两个事务同时更新相关行。终端A先执行START TRANSACTION; SELECT * FROM student WHERE sno 20230001 FOR UPDATE; UPDATE course SET credit credit 1 WHERE cno C001; -- 先不提交等待终端B执行终端B随后执行START TRANSACTION; UPDATE course SET credit credit 1 WHERE cno C001; SELECT * FROM student WHERE sno 20230001 FOR UPDATE; -- 这里会阻塞几秒随后其中一个终端报死锁逻辑说明终端A先锁了sno20230001的student行再去更新课程C001终端B先锁了课程C001再回头锁student行两者互相等对方释放锁触发InnoDB死锁检测其中一个事务被回滚。实验报告里加上查看死锁现场的命令会更有说服力SHOW ENGINE INNODB STATUS\G关注输出里的LATEST DETECTED DEADLOCK里面写着两个事务各自持有的锁和等待的锁。也可以调参数验证锁等待超时SET SESSION innodb_lock_wait_timeout 5;再复现时5秒后报Lock wait timeout exceeded。写结论时别只写“出现了死锁”要加一句避免死锁的常规手段是让所有事务按相同顺序加锁。这句话才是加分点。5. 上机实验避坑五条能救命的数据库配置与运行记录实验翻车大多不是SQL语法问题而是环境问题。以下五条都是反复出现的同类问题每条按现象、原因、解决列清楚。5.1 导入建表脚本反复报“Cannot add foreign key constraint”现象同一个建表脚本在别人电脑上能跑在你这里报外键约束创建失败而且报错不指向具体字段。原因脚本里建表顺序不对外键引用的父表还没创建或者两张表对应字段类型不一致比如父表sno是CHAR(9)子表sno是VARCHAR(9)。MySQL对外键要求两张表列类型一致字符集和排序规则也要一致。解决导入前先加临时会话开关SET FOREIGN_KEY_CHECKS0;完成后再SET FOREIGN_KEY_CHECKS1;。如果仍然报错用SHOW CREATE TABLE对比两张表对应字段的类型和CHARSET统一成CHAR(9)和utf8mb4。教学实验里这道命令能解决八成外键报错。5.2 命令行导入SQL后中文全是问号用Navicat打开却是好的现象在终端里执行mysql xxx.sql导入查询SELECT * FROM student看到的中文全是???用可视化客户端连同一个库看数据又正常。原因客户端连接时没有声明字符集。MySQL服务端认为客户端传过来的是latin1把UTF-8中文字节按latin1解释后丢失。Navicat正常是因为它在握手阶段自动发送了SET NAMES utf8mb4。解决命令行统一加--default-character-setutf8mb4。如果已经导脏了不要只改客户端再导一次因为表里已经存了错误字符最干净的后悔药是DROP TABLE后重新导入。脚本里也可以写一句SET NAMES utf8mb4;但用连接参数更不容易忘。5.3 在可视化工具里执行UPDATE不带主键提示“safe update mode”现象在MySQL Workbench里执行UPDATE student SET sdept软件学院;工具直接报You are using safe update mode并拒绝执行。原因这是客户端的安全保护不是数据库服务端的报错。可视化工具默认禁止不带WHERE的UPDATE和DELETE防止用户在实验环境里一下改掉整张表。解决在SQL前加SET SQL_SAFE_UPDATES0;可以临时关掉保护但我建议养成给UPDATE都带WHERE条件的习惯。数据库岗位面试被问到“如何避免误更新全表”时回答“显式事务 WHERE条件 工具安全模式”比回答“我关掉了安全模式”好太多。如果实验题要求演示全表更新就先START TRANSACTION再执行最后ROLLBACK。5.4 批量插入几万行成绩数据时卡死一条INSERT等几十秒现象用循环或批量插入往sc表导入成绩前几千行很快越往后越慢最后甚至报锁等待超时。原因最常见的是表上有触发器每插入一行都去UPDATE统计表统计表行锁竞争越来越严重其次是表上有多个索引每次插入都要维护索引树。本质问题都是行级操作被放大了额外写操作。解决分两阶段。MySQL 8.0不像SQL Server那样支持DISABLE TRIGGER常见做法是先DROP TRIGGER trg_sc_after_insert;导完数据再重新创建。导入期间也可以临时SET FOREIGN_KEY_CHECKS0;减少逐行校验。如果脚本是逐条INSERT可以试试改成LOAD DATA INFILE批量导入速度差距是数量级的。实验报告里说明生产环境的触发器不会随便删除这里是教学演练。5.5 实验报告截图和别人同题不同结果被老师要求重做现象同一道查询题别人查出来3行你查出来2行或者平均成绩比别人多了0.5分。原因多数是初始化数据版本不一致。zip文件名带下划线说明可能经过转手里面的插入数据脚本可能是旧版学号、成绩和老师课堂演示用的并不完全一样。另外AVG会忽略NULL但COUNT(*)会统计NULL行混用也会产生对不上的结果。解决先核对脚本头部的注释和题目序号以老师课上发的题目PDF为准不要把zip里的数据当成标准答案。写报告时在开头写清楚“我共导入student表XX行course表XX行sc表XX行”主动说明数据规模老师一眼就能看出不是照抄。成绩统计类题目统一用AVG(grade)并写明NULL处理方式。6. 让实验报告经得起追问验收的最小用例和验证方法最后一晚不要急着整理报告先花半小时把每个实验点跑一遍最小用例。我的验收清单是一个表格加几条命令实验点验收检查通过标准建库建表SHOW TABLES;表与题目要求一一对应完整性约束插入一条违反外键或CHECK的记录明确报错且数据未写入增删改查事务内INSERT、UPDATE、DELETE结果符合预期回滚后数据不变多表查询跑三条必考题行数与题目样例一致索引建索引前后各跑一次EXPLAIN访问类型从ALL变成ref视图SHOW FULL TABLES WHERE Table_typeVIEW;视图存在且查询结果非空存储过程CALL传一个学号OUT参数返回值与手算一致触发器往sc插入一行后查统计表统计表该课程人数加一事务死锁双会话复现死锁能读到LATEST DETECTED DEADLOCK如果实验目录允许提交脚本我建议把所有SQL按编号存成独立文件lab1_build.sql、lab2_crud.sql、lab3_query.sql。第一个文件里不带DROP DATABASE只带CREATE DATABASE IF NOT EXISTS和USE保证老师验收时重复执行不会误删其他库。最后一个文件末尾留一行注释写清楚“本脚本包含存储过程和触发器推荐用SOURCE或客户端运行”。我自己的习惯是每跑通一个实验点就在report目录下存一个.sql文件和一张结果截图不要等全部做完再补截图。截图窗口标题里要能看到当前库名和连接端口这能证明是本地环境跑出来的。如果zip里的脚本比较旧出现TYPEInnoDB或DEFAULT CHARSETlatin1这类老写法导入前全局替换成ENGINEInnoDB和utf8mb4能少踩一半的乱码坑。把这份实验按这个顺序完整跑一遍“数据库原理与设计”上机的知识点基本就覆盖全了。后面的数据库课程设计、面试里的存储过程手写题都可以直接拿这套库当底子继续扩展。希望帮到你。本文还有配套的精品资源点击获取