南航人工智能数据库课设实战：从setup.sql到并发控制的完整落地

简介这份资源是南京航空航天大学人工智能专业2024年《数据库原理》课程设计的完整项目包面向正在学习数据库课程、需要完成课程设计或上机实验的本科生与自学者。内容围绕数据库系统的基本概念、原理与方法展开涵盖需求分析、概念与逻辑设计、物理实现、测试维护等阶段并涉及SQL语言使用、数据模型、查询优化、事务管理与并发控制等核心知识点适合作为课程设计参考与动手实践模板。压缩包共6个文件约1.92MB包含2个SQL脚本、1个Python程序、1份PDF实验报告、1个TXT依赖说明及1个Markdown说明文档分别对应建库建表、应用实现、实验记录与运行环境配置。目前已有211人学习下载读者可借此了解完整课程设计的组织方式、数据库对象创建思路与项目文档结构为独立完成同类设计提供可复用的参考框架。1. 从一份课设压缩包说起数据库原理到底该怎么落地很多人看到「南京航空航天大学人工智能专业2024年《数据库原理》课程设计」这个标题第一反应是去找答案、找模板、找能直接交差的压缩包。但真正做过这门课的人都知道NUAA_DB2024_Project.zip解压之后真正决定你能不能跑通、能不能讲清楚、能不能在答辩时不翻车的不是那份 PDF 报告而是三个东西setup.sql建不建得起库、run.sql跑不跑得动查询、main.py连不连得上数据库。数据库原理这门课考试考的是范式、事务、索引课设考的是你能不能把这一套东西真的跑起来。人工智能专业的学生尤其容易在这里踩坑——平时写 Python 调库习惯了一到手写 SQL、设计 ER 图、处理并发就露怯。这篇笔记不聊虚的就按一个真实课设的落地路径把从环境搭建到查询优化、从建表到并发控制的完整链路拆开讲适合正在做课设、或者想用一个小项目把数据库原理真正吃透的人。2. 环境搭建与项目结构让 setup.sql 和 main.py 先跑起来2.1 为什么课设第一步不是写代码而是定技术栈数据库原理课设最常见的翻车方式是代码写到一半发现数据库连不上或者换台电脑就崩。根源在于一开始没把技术栈定死。南航这门课设通常不限制具体数据库MySQL、PostgreSQL、SQLite 都能用但选择不同后面的setup.sql写法和main.py连接方式完全不一样。我的建议是如果课设要求里没有强制用某个数据库优先选 MySQL 8.0 或 PostgreSQL 14 以上版本原因是这两个对事务、索引、视图的支持最完整答辩时老师问「你的事务隔离级别怎么设的」你能答得上来。SQLite 虽然零配置但它对并发和权限的控制太弱做「数据库原理」课设容易显得没深度。选型确定后项目结构要提前规划。一个能跑通的课设目录通常长这样NUAA_DB2024_Project/ ├── sql/ │ ├── setup.sql # 建库、建表、初始化数据 │ └── run.sql # 业务查询、视图、存储过程 ├── src/ │ ├── main.py # 程序入口负责连接和调用 │ ├── db.py # 数据库连接封装 │ └── models.py # 表对应的实体类 ├── config.ini # 数据库连接参数 └── requirements.txt # Python 依赖这个结构不是摆设。setup.sql和run.sql分开是为了让「建库」和「查询」解耦——答辩演示时先跑setup.sql重建干净环境再跑run.sql展示业务逻辑顺序清晰出问题也好定位。2.2 用 setup.sql 建库建表字段类型和约束怎么定setup.sql是整个课设的地基。很多同学直接从网上抄一段建表语句字段全用varchar(255)主键用自增 int外键不写结果查询慢、数据重复、答辩被问「你的范式体现在哪」直接卡住。正确的做法是先画 ER 图再落成 SQL。以一个常见的「学生选课系统」为例核心表至少三张学生表、课程表、选课表。选课表必须是联合主键这是第二范式的直接体现。-- setup.sql DROP DATABASE IF EXISTS nuaa_db2024; CREATE DATABASE nuaa_db2024 DEFAULT CHARACTER SET utf8mb4; USE nuaa_db2024; -- 学生表学号为主键姓名非空 CREATE TABLE student ( sno CHAR(10) PRIMARY KEY, sname VARCHAR(20) NOT NULL, gender ENUM(M,F) DEFAULT M, age TINYINT CHECK (age BETWEEN 16 AND 40), major VARCHAR(30) ) ENGINEInnoDB; -- 课程表课程号为主键学分用 DECIMAL 避免浮点误差 CREATE TABLE course ( cno CHAR(8) PRIMARY KEY, cname VARCHAR(40) NOT NULL, credit DECIMAL(3,1) NOT NULL CHECK (credit 0), teacher VARCHAR(20) ) ENGINEInnoDB; -- 选课表联合主键外键约束保证引用完整性 CREATE TABLE sc ( sno CHAR(10), cno CHAR(8), grade DECIMAL(4,1) CHECK (grade BETWEEN 0 AND 100), term VARCHAR(10) NOT NULL, PRIMARY KEY (sno, cno, term), FOREIGN KEY (sno) REFERENCES student(sno) ON DELETE CASCADE, FOREIGN KEY (cno) REFERENCES course(cno) ON DELETE CASCADE ) ENGINEInnoDB;这段代码有几个关键点值得说清楚。第一ENGINEInnoDB必须显式写因为只有 InnoDB 支持事务和外键MyISAM 不支持课设里如果涉及事务演示用 MyISAM 直接判死刑。第二CHAR和VARCHAR的选择有讲究学号、课程号长度固定用CHAR效率更高姓名、专业长度不定用VARCHAR。第三DECIMAL用于学分和成绩避免FLOAT带来的精度问题这是数据库原理里「数据类型选择」的考点。第四联合主键(sno, cno, term)保证了同一个学生同一门课同一学期只能有一条记录天然满足实体完整性。初始化数据也要写在setup.sql里但注意不要用INSERT一条条插数据量大的话用批量插入INSERT INTO student (sno, sname, gender, age, major) VALUES (2024001, 张三, M, 20, 人工智能), (2024002, 李四, F, 19, 人工智能), (2024003, 王五, M, 21, 计算机); INSERT INTO course (cno, cname, credit, teacher) VALUES (CS101, 数据库原理, 3.0, 赵老师), (CS102, 数据结构, 4.0, 钱老师), (AI201, 机器学习, 3.5, 孙老师); INSERT INTO sc (sno, cno, grade, term) VALUES (2024001, CS101, 88.5, 2024-2025-1), (2024001, AI201, 92.0, 2024-2025-1), (2024002, CS101, 76.0, 2024-2025-1);执行setup.sql的方式有两种命令行mysql -u root -p setup.sql或者在main.py里用 Python 读取文件执行。我一般推荐命令行先跑一遍确认没有语法错误再集成到代码里。2.3 main.py 连接数据库参数配置和异常处理main.py是课设的入口负责把 Python 和数据库连起来。很多同学在这里犯的错是连接参数硬编码在代码里换台电脑就报Access denied或者不写异常处理数据库没启动程序直接崩答辩现场很尴尬。正确的做法是把连接参数放到config.ini代码里读取配置并且用try-except包住连接过程。# db.py import configparser import pymysql from pymysql import Error def get_connection(): config configparser.ConfigParser() config.read(config.ini, encodingutf-8) try: conn pymysql.connect( hostconfig.get(db, host), portconfig.getint(db, port), userconfig.get(db, user), passwordconfig.get(db, password), databaseconfig.get(db, database), charsetutf8mb4, cursorclasspymysql.cursors.DictCursor ) return conn except Error as e: print(f数据库连接失败: {e}) return None对应的config.ini[db] host 127.0.0.1 port 3306 user root password your_password database nuaa_db2024这里有几个参数需要解释。charsetutf8mb4是为了支持中文和特殊字符如果只写utf8插入 emoji 或某些生僻字会报错。cursorclassDictCursor让查询结果以字典返回方便后续按字段名取值不用记列索引。port用getint读取因为配置文件里读出来默认是字符串直接传给connect可能类型不匹配。main.py里调用时要判断连接是否成功# main.py from db import get_connection def query_student_scores(sno): conn get_connection() if conn is None: return [] try: with conn.cursor() as cursor: sql SELECT s.sname, c.cname, sc.grade FROM sc JOIN student s ON sc.sno s.sno JOIN course c ON sc.cno c.cno WHERE sc.sno %s cursor.execute(sql, (sno,)) return cursor.fetchall() finally: conn.close() if __name__ __main__: results query_student_scores(2024001) for row in results: print(row)注意cursor.execute的第二个参数用元组传参不要用字符串拼接这是防止 SQL 注入的基本功也是课设答辩常问的点。finally里关闭连接避免连接泄漏。3. run.sql 里的查询设计从单表到多表连接再到视图3.1 单表查询和聚合GROUP BY 与 HAVING 的边界run.sql是课设里展示「数据库原理」功力的地方。老师不会只看你建了几张表更会看你查询写得对不对、优不优化。单表查询看似简单但GROUP BY和HAVING的区分是高频考点。比如「查询每门课程的平均分只显示平均分大于 80 的课程」正确写法是-- 每门课平均分筛选平均分 80 SELECT cno, AVG(grade) AS avg_grade FROM sc GROUP BY cno HAVING AVG(grade) 80;这里WHERE不能替代HAVING因为WHERE在分组前过滤行HAVING在分组后过滤组。如果写成WHERE AVG(grade) 80数据库直接报错。这个点答辩被问到概率极高建议在run.sql里注释清楚。另一个常见需求是「查询每个学生的选课门数」SELECT sno, COUNT(*) AS course_count FROM sc GROUP BY sno ORDER BY course_count DESC;COUNT(*)统计所有行COUNT(cno)统计非空 cno如果 cno 允许为空两者结果不同。课设里如果选课表 cno 是主键的一部分不可能为空用哪个都行但理解区别是必要的。3.2 多表连接INNER JOIN、LEFT JOIN 和自连接的适用场景多表连接是课设的核心。以「查询所有学生及其选课成绩没选课的学生也要显示」为例必须用LEFT JOINSELECT s.sno, s.sname, c.cname, sc.grade FROM student s LEFT JOIN sc ON s.sno sc.sno LEFT JOIN course c ON sc.cno c.cno ORDER BY s.sno;如果用INNER JOIN没选课的学生直接消失结果不完整。LEFT JOIN保证左表student所有行都出现右表没匹配的用 NULL 填充。这个区别在课设报告里要写清楚答辩时老师很可能让你现场改查询。自连接用于同一张表内的比较比如「查询比张三年龄大的学生」SELECT s2.sname, s2.age FROM student s1 JOIN student s2 ON s2.age s1.age WHERE s1.sname 张三;自连接必须给表起别名否则数据库不知道你指的是哪一张。这个查询也可以用子查询写但自连接在性能上通常更优因为优化器可以更好地利用索引。3.3 视图和存储过程把复杂查询封装成可复用的对象课设里如果只写 SELECT深度不够。视图和存储过程是加分项。视图适合把频繁使用的复杂查询固化下来-- 创建视图学生成绩单 CREATE OR REPLACE VIEW v_student_score AS SELECT s.sno, s.sname, c.cname, sc.grade, sc.term FROM sc JOIN student s ON sc.sno s.sno JOIN course c ON sc.cno c.cno; -- 使用视图 SELECT * FROM v_student_score WHERE grade 90;视图的好处是简化查询、隐藏底层表结构但注意视图不存储数据每次查询都会展开成底层 SQL性能上不一定比直接写 JOIN 快。课设里用视图展示「逻辑独立性」的概念即可不要指望它优化性能。存储过程适合封装业务逻辑比如「插入选课记录前检查是否已选」DELIMITER // CREATE PROCEDURE add_sc(IN p_sno CHAR(10), IN p_cno CHAR(8), IN p_term VARCHAR(10)) BEGIN DECLARE cnt INT; SELECT COUNT(*) INTO cnt FROM sc WHERE sno p_sno AND cno p_cno AND term p_term; IF cnt 0 THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 该学生已选此课; ELSE INSERT INTO sc (sno, cno, term) VALUES (p_sno, p_cno, p_term); END IF; END // DELIMITER ;调用方式CALL add_sc(2024003, CS101, 2024-2025-1);。存储过程里SIGNAL SQLSTATE用于抛出自定义错误比在 Python 里判断更靠近数据层。注意DELIMITER是 MySQL 客户端指令不是 SQL 标准PostgreSQL 里用$$代替。4. 并发与锁课设里最容易忽略但答辩必问的点4.1 乐观锁和悲观锁在选课场景下的区别数据库原理课设如果只做增删改查深度最多及格。想拿高分必须碰并发。选课系统是天然的并发场景同一门课名额有限多个学生同时选怎么保证不超选这就涉及乐观锁和悲观锁。悲观锁的思路是「先锁再操作」用SELECT ... FOR UPDATE把行锁住START TRANSACTION; SELECT remaining FROM course WHERE cno CS101 FOR UPDATE; -- 应用层判断 remaining 0 UPDATE course SET remaining remaining - 1 WHERE cno CS101; INSERT INTO sc (sno, cno, term) VALUES (2024003, CS101, 2024-2025-1); COMMIT;FOR UPDATE会对查询到的行加排他锁其他事务再查同一行会阻塞直到当前事务提交。悲观锁适合写冲突频繁的场景但缺点是并发度低学生多了会排队。乐观锁的思路是「先操作提交时检查版本」通常用版本号字段实现-- 表里加 version 字段 UPDATE course SET remaining remaining - 1, version version 1 WHERE cno CS101 AND remaining 0 AND version 3; -- 检查 affected_rows 是否为 1为 0 说明被其他事务改过重试乐观锁不锁行只在更新时检查版本冲突少时性能好冲突多时重试成本高。课设里两种都实现一遍对比 QPS 和失败率报告里写清楚适用场景答辩时老师基本不会再追问。4.2 事务隔离级别READ COMMITTED 和 REPEATABLE READ 怎么选MySQL InnoDB 默认隔离级别是REPEATABLE READPostgreSQL 默认是READ COMMITTED。课设里如果涉及事务演示要能说清楚区别。REPEATABLE READ保证同一事务内多次读同一行结果一致靠 MVCC 实现READ COMMITTED每次读都取最新已提交数据可能出现不可重复读。选课场景下如果只是扣减名额READ COMMITTED够用如果事务里要多次读同一行做判断REPEATABLE READ更安全。设置方式SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION; -- 业务操作 COMMIT;注意SET SESSION只影响当前连接SET GLOBAL影响所有新连接课设里用SESSION即可不要动全局配置。5. 避坑与排查课设从跑通到答辩的 5 个血泪教训5.1 现象setup.sql 执行报「Unknown character set: utf8mb4」原因MySQL 版本低于 5.5.3不支持 utf8mb4。解决升级 MySQL 到 8.0或者把utf8mb4改成utf8但后者不支持 emoji。课设环境尽量用 MySQL 8.0安装时选对版本。5.2 现象main.py 报「Access denied for user rootlocalhost」原因密码错误或者 MySQL 8.0 的caching_sha2_password认证插件和旧版 pymysql 不兼容。解决先确认密码如果密码对还报错执行ALTER USER rootlocalhost IDENTIFIED WITH mysql_native_password BY your_password;切换认证方式或者升级 pymysql 到 1.0 以上。5.3 现象run.sql 里 JOIN 查询结果比预期少原因用了INNER JOIN但本意是保留左表所有行。解决改成LEFT JOIN并检查连接条件是否写错比如ON sc.sno s.sno写成ON sc.sno s.sname类型不匹配会导致匹配失败。5.4 现象存储过程创建时报「This function has none of DETERMINISTIC...」原因MySQL 开启了binlog且log_bin_trust_function_creators为 OFF。解决临时执行SET GLOBAL log_bin_trust_function_creators 1;或者在建存储过程时加DETERMINISTIC声明。课设环境直接设全局变量即可重启后失效不影响。5.5 现象并发测试时出现超选remaining 变成负数原因没用锁或者用了乐观锁但没检查affected_rows。解决悲观锁加FOR UPDATE乐观锁在UPDATE后判断cursor.rowcount为 0 则重试。重试次数设 3 次超过则返回失败避免死循环。6. 用 Python 脚本做自动化验证把课设变成可复现的工程课设最后一步不是交报告而是让整套流程可复现。我一般会写一个verify.py自动跑setup.sql、插入测试数据、执行run.sql里的关键查询、对比预期结果。这样换台电脑也能一键验证答辩演示时不怕环境问题。# verify.py import subprocess import pymysql from db import get_connection def run_sql_file(path): with open(path, r, encodingutf-8) as f: sql f.read() conn get_connection() try: with conn.cursor() as cursor: for statement in sql.split(;): if statement.strip(): cursor.execute(statement) conn.commit() finally: conn.close() def check_avg_grade(): conn get_connection() with conn.cursor() as cursor: cursor.execute(SELECT cno, AVG(grade) FROM sc GROUP BY cno HAVING AVG(grade) 80) return cursor.fetchall() if __name__ __main__: run_sql_file(sql/setup.sql) result check_avg_grade() assert len(result) 0, 平均分查询无结果 print(验证通过:, result)这个脚本的关键是run_sql_file按分号拆分语句注意存储过程里的分号会干扰拆分所以setup.sql里不要放存储过程存储过程单独放run.sql并用DELIMITER处理。check_avg_grade用断言验证结果失败时抛异常CI 里也能用。参数方面verify.py依赖config.ini里的连接信息确保和main.py用同一份配置。如果课设要求提交压缩包把verify.py一起放进去答辩时现场跑一遍比口头说「我实现了」有说服力得多。我自己的习惯是每次改完setup.sql或run.sql先跑verify.py通过了再更新报告。这样报告里的截图和实际代码永远一致不会出现「报告写的是 A代码跑的是 B」的尴尬。数据库原理课设不难难的是把每个细节都落到实处希望帮到你。本文还有配套的精品资源点击获取