尧图网页设计 
简介面向数据库课程实验/大作业的学生成绩管理数据库系统设计文档围绕 MySQL 环境下的学生信息查询管理系统展开解决旧式人工/低效学籍管理中的检索慢、保密性差、数据冗余等问题适合正在完成课程设计或需要撰写需求分析的学生参考。资源为单个 docx 文档压缩包约 928KB正文包含需求分析、系统功能框架、运行环境、用户角色说明可直接编辑修改。资源已有 906 人学习参考能够帮助读者快速理解学生成绩管理系统的完整设计脉络。文档详细分解了管理员、教师、学生三类角色的权限与操作流程对信息管理、成绩管理、系统管理三个模块均给出功能与业务流程说明也涉及登录验证、MD5 加密等实现细节可作为数据库实验大作业的报告模板与数据库设计依据。1. 为什么我把学生成绩管理数据库从单表拆成了七张表数据库实验大作业里学生成绩管理系统是出现频率最高的题目但大多数提交上来的设计都是“一张成绩表走天下”学号、姓名、课程、分数全塞在一起。这种设计应付课程报告可以一到答辩现场老师问“学生能不能改自己的成绩”“教师删课之后选课记录怎么办”基本就答不上来。这份设计的不同之处在于它把用户角色直接做进了模式里管理员、教师、学生各自拥有独立的账号表和权限边界成绩和选课信息通过外键关联到学生表和课程表登录密码用 MD5 存储。它不是一个只能跑通 CRUD 的玩具而是把数据库的安全性和完整性考虑进去的课程设计范本。适合正在做数据库课程设计、准备答辩或者想系统梳理 MySQL 权限模型与关系模式设计的同学参考。下面按我实际拆解这个项目的顺序从权限模型、建表语句到查询优化逐一展开。2. 成绩管理系统的权限模型与关系模式设计2.1 三种角色的权限边界这个系统把用户分成三类管理员、教师、学生。管理员是 root 用户拥有所有表的增删改查权限教师只能操作自己授课相关的选课记录比如录入成绩、修改选课信息但不能看其他教师的数据学生只有查询权限不能修改任何数据。这个权限模型对应到数据库层面不是靠某一条 SQL 实现的而是靠关系模式的拆分登录账号表和业务数据表分开账号表只存用户名和密码业务表里只存学号、工号这类业务主键。管理员维护账号和业务表教师通过课程信息表里的任课教师字段来限定操作范围学生则依靠学号与选课表的关联来限定查询范围。角色账号表可操作业务表操作范围管理员adminstu_info / tea_info / course_info / stu_course全部数据增删改查维护账号教师tealoginstu_course / course_info仅任课课程的选课与成绩学生stuloginstu_info / stu_course仅本人信息与本人成绩2.2 E-R 图向关系模式的转化原设计定义了课程、学生、教师、成绩四个核心实体外加管理员、学生登录、教师登录三个账号实体。把 E-R 图转化为关系模式时最关键的是确定主键和外键stu_info(sno, sname, age, sex, dept, place)主键sno。tea_info(tno, tname, dept)主键tno。course_info(cno, cname, tname, student_num)主键cno其中tname是冗余的任课教师姓名原设计没有用tno做外键这是个值得在答辩时讨论的点。stu_course(sno, cno, usual_grade, final_grade, total_mark)主键是复合键(sno, cno)sno和cno分别外键引用stu_info和course_info。admin(username, password)、tealogin(username, password)、stulogin(username, password)三个账号表分别管理三类登录账号。这里要特别注意stu_course表的设计它既是选课表又是成绩表平时成绩、期末成绩、总成绩都放在同一个记录里。这种设计简化了查询但会带来更新异常——如果平时成绩改了总成绩必须手动同步。后面第 5 章会讲用触发器解决。2.3 为什么选复合主键而不是自增 IDstu_course的主键采用(sno, cno)复合主键而不是单独加一个自增id字段。这样做的好处是天然保证同一个学生不能重复选同一门课数据库层面就能拦截重复插入。如果改用自增主键就需要额外给(sno, cno)加唯一索引否则会插入重复选课记录。我一般建议课程设计中不要图省事只建三张表学生表、课程表、成绩表。像原设计这样把登录表独立出来虽然多建了三张表但好处很明显业务数据表里不混入登录密码即使业务表被误导出密码也不会泄露。这是符合自主访问控制原则的最小实现。第 2 章的表格和模式说明已经把逻辑结构讲清楚了下面直接进入 MySQL 的物理实现。3. MySQL 建表语句与初始化数据实操3.1 建库与表结构原设计最后落地在 MySQL跟需求文档里写的 SQL Server 有点出入但反而更贴近现在课程设计的主流环境。先建库然后按依赖顺序建表。注意外键表必须在被引用表创建之后再建所以顺序是先建业务表stu_info、tea_info、course_info再建选课表stu_course最后建三个登录表。-- 建库指定 utf8mb4 字符集避免中文乱码 CREATE DATABASE IF NOT EXISTS student DEFAULT CHARSET utf8mb4; USE student; -- 学生信息表 CREATE TABLE IF NOT EXISTS stu_info ( sno VARCHAR(20) NOT NULL COMMENT 学号, sname VARCHAR(30) COMMENT 姓名, age NUMERIC(2) COMMENT 年龄, sex VARCHAR(2) COMMENT 性别, dept VARCHAR(20) COMMENT 院系, place VARCHAR(20) COMMENT 籍贯, PRIMARY KEY (sno) ) DEFAULT CHARSETutf8mb4 COMMENT学生信息表; -- 教师信息表 CREATE TABLE IF NOT EXISTS tea_info ( tno VARCHAR(20) NOT NULL COMMENT 教师工号, tname VARCHAR(30) COMMENT 姓名, dept VARCHAR(20) COMMENT 院系, PRIMARY KEY (tno) ) DEFAULT CHARSETutf8mb4 COMMENT教师信息表; -- 课程信息表 CREATE TABLE IF NOT EXISTS course_info ( cno VARCHAR(20) NOT NULL COMMENT 课程号, cname VARCHAR(30) COMMENT 课程名, tname VARCHAR(30) COMMENT 任课教师, student_num NUMERIC(10) COMMENT 课程人数, PRIMARY KEY (cno) ) DEFAULT CHARSETutf8mb4 COMMENT课程信息表; -- 学生选课表成绩表 CREATE TABLE IF NOT EXISTS stu_course ( sno VARCHAR(20) NOT NULL COMMENT 学号, cno VARCHAR(20) NOT NULL COMMENT 课程号, usual_grade INT COMMENT 平时成绩, final_grade INT COMMENT 期末成绩, total_mark INT COMMENT 总成绩, PRIMARY KEY (sno, cno), FOREIGN KEY (sno) REFERENCES stu_info(sno), FOREIGN KEY (cno) REFERENCES course_info(cno) ) DEFAULT CHARSETutf8mb4 COMMENT选课信息表; -- 管理员账号表 CREATE TABLE IF NOT EXISTS admin ( username VARCHAR(20) NOT NULL COMMENT 用户名, password VARCHAR(30) COMMENT 登录密码, PRIMARY KEY (username) ) DEFAULT CHARSETutf8mb4 COMMENT管理员表; -- 教师登录表外键关联教师信息表 CREATE TABLE IF NOT EXISTS tealogin ( username VARCHAR(20) NOT NULL, password VARCHAR(30), PRIMARY KEY (username), FOREIGN KEY (username) REFERENCES tea_info(tno) ) DEFAULT CHARSETutf8mb4 COMMENT教师登录表; -- 学生登录表外键关联学生信息表 CREATE TABLE IF NOT EXISTS stulogin ( username VARCHAR(20) NOT NULL, password VARCHAR(30), PRIMARY KEY (username), FOREIGN KEY (username) REFERENCES stu_info(sno) ) DEFAULT CHARSETutf8mb4 COMMENT学生登录表;这段 SQL 里有几个参数值得展开说。VARCHAR(20)是学号长度不同学校学号长度不一样实验里可以按本校规则调整但注意学号要和登录表的username完全一致否则外键会失败。NUMERIC(2)表示年龄最大两位不需要更大的精度。COMMENT是字段注释写清楚字段含义这个习惯在课程设计的文档评审里很加分。登录表的外键直接引用业务表主键意思是教师登录账号的username必须是tea_info里存在的工号学生登录账号必须是stu_info里存在的学号。这保证不会出现“账号存在但业务数据不存在”的孤儿数据。3.2 初始化数据与 MD5 密码原设计插入了三条管理员、三位教师、三位学生和对应的业务数据。密码统一用MD5(123)存储不存明文。-- 管理员账号 INSERT INTO admin VALUES (2013302550010, MD5(123)); INSERT INTO admin VALUES (2013302550011, MD5(123)); INSERT INTO admin VALUES (2013302550012, MD5(123)); -- 教师账号与教师信息 INSERT INTO tealogin VALUES (2013302540010, MD5(123)); INSERT INTO tealogin VALUES (2013302540011, MD5(123)); INSERT INTO tealogin VALUES (2013302540012, MD5(123)); INSERT INTO tea_info VALUES (2013302540010,赵一,计算机学院); INSERT INTO tea_info VALUES (2013302540011,赵二,经济与管理学院); INSERT INTO tea_info VALUES (2013302540012,赵三,物理学院); -- 学生账号与学生信息 INSERT INTO stulogin VALUES (2013302530010, MD5(123)); INSERT INTO stulogin VALUES (2013302530011, MD5(123)); INSERT INTO stulogin VALUES (2013302530012, MD5(123)); INSERT INTO stu_info VALUES (2013302530010,张一,20,男,计算机学院,湖北); INSERT INTO stu_info VALUES (2013302530011,张二,21,女,经济与管理学院,湖南); INSERT INTO stu_info VALUES (2013302530012,张三,22,男,物理学院,福建); -- 课程与选课成绩 INSERT INTO course_info VALUES (201501,数据库,赵一); INSERT INTO course_info VALUES (201502,C语言程序设计,赵二); INSERT INTO course_info VALUES (201503,计算机网络,赵一); INSERT INTO stu_course VALUES (2013302530012,201501,90,90,90); INSERT INTO stu_course VALUES (2013302530012,201502,100,90,94); INSERT INTO stu_course VALUES (2013302530012,201503,90,100,96);注意原设计里course_info的student_num字段在插入语句里没有给值MySQL 会默认填NULL。如果后续要统计课程人数这个字段要么在应用层更新要么用视图实时计算。我建议改成视图直接用COUNT(*)统计选课表这样student_num就不容易“变脏”。MD5(123)是个演示级写法。实际系统里应该用MD5(CONCAT(123, salt))做加盐或者直接用SHA2。这个点在答辩时主动提出来比等老师问要有说服力得多。3.3 常见建表报错与处理建表时最容易踩的坑有两个。第一个是外键引用的列没有建索引MySQL 会直接报ERROR 1215 (HY000): Cannot add foreign key constraint。被引用的sno、cno本来就是主键有主键索引所以这里没问题。但如果你改了表的字段要注意被引用列必须是索引列。第二个是字符集不一致登录表用了utf8业务表用了utf8mb4外键比较会报Illegal mix of collations。统一用utf8mb4就能避开。4. 多角色查询与成绩统计的 SQL 实战4.1 学生查自己的成绩学生登录后只能查自己的成绩查询条件必须带上当前登录的学号而不是让用户自己填学号。实际应用里学号是从 session 里取出来的不会来自前端输入。模拟实验时用参数占位符更规范-- 学生查看自己的所有成绩需要关联课程信息拿到课程名 SELECT sc.cno, ci.cname, sc.usual_grade, sc.final_grade, sc.total_mark FROM stu_course sc JOIN course_info ci ON sc.cno ci.cno WHERE sc.sno ?; -- ? 是当前登录学生的学号这里用了JOIN而不是直接在stu_course里查是因为成绩表里只有课程号cno用户界面上要显示“数据库”“计算机网络”这类课程名必须关联course_info。如果不关联前端就得拿课程号再去数据库查一遍多一次往返。JOIN在 MySQL 里的执行逻辑是先把两表按cno做笛卡尔积再用ON条件过滤最后用WHERE限制学号。实际查询优化器会先下推WHERE条件不会真的做全量笛卡尔积但写 SQL 时仍然应该先写WHERE缩小范围、再写JOIN条件这样阅读顺序更符合人的思维。4.2 教师录入成绩与修改权限控制教师模块的核心操作是给选课学生录入成绩。原设计里的权限约束是“只能操作任课课程的选课表”这个限制在 SQL 上要靠子查询来实现先根据教师登录名去课程表里找到课程再更新对应课程的成绩。-- 教师录入成绩只允许更新自己任课的课程 UPDATE stu_course SET usual_grade ?, -- 平时成绩 final_grade ?, -- 期末成绩 total_mark ROUND(usual_grade * 0.4 final_grade * 0.6, 0) -- 这里假设权重 WHERE cno ( SELECT cno FROM course_info WHERE tname ? ) AND sno ?;这个 SQL 的关键在WHERE cno (SELECT cno FROM course_info WHERE tname ?)。子查询先把课程限定到当前教师名下如果这个教师没有任课子查询返回空更新影响行数为 0从数据层面挡住了越权操作。不过这里有个隐患course_info里tname不是主键如果同名教师存在子查询会返回多行导致报错Subquery returns more than 1 row。更稳妥的方案是课程表里存tno外键或者把tname改成唯一索引。这是原设计的一个短板答辩时可以主动提出改进方案。total_mark的计算我直接写在UPDATE语句里了利用的是 SQL 语句中字段引用顺序的特性SET子句里usual_grade和final_grade已经被赋新值total_mark ROUND(usual_grade * 0.4 final_grade * 0.6, 0)引用的是更新后的值。这样做虽然方便但不够严谨因为权重比例写在 SQL 里被硬编码了后续如果要调整平时分和期末分的占比就得改 SQL。把权重抽成系统参数表或者在应用层算好再传进来会更合理。4.3 管理员删除学生时的外键约束处理管理员可以删除学生但stu_course表里有外键引用stu_info。直接删除有选课记录的学生会报外键冲突DELETE FROM stu_info WHERE sno 2013302530010; -- 报错Cannot delete or update a parent row: a foreign key constraint fails处理方式有两种。第一种是先删子表再删父表事务包裹START TRANSACTION; DELETE FROM stulogin WHERE username 2013302530010; DELETE FROM stu_course WHERE sno 2013302530010; DELETE FROM stu_info WHERE sno 2013302530010; COMMIT;第二种是在建表时就指定ON DELETE CASCADE。比如stu_course表外键改为FOREIGN KEY (sno) REFERENCES stu_info(sno) ON DELETE CASCADE这样删除学生时选课成绩会自动删除。但要注意如果成绩记录还有审计需求级联删除会把历史数据直接抹掉不是所有场景都合适。课程设计里用第一种事务方式更安全也更能体现你对并发控制的理解。4.4 成绩统计与排名的常用查询实验报告的加分项是统计类 SQL。下面这几个可以直接用在系统“成绩分布”页面-- 每门课程的平均分、最高分、最低分 SELECT cno, AVG(total_mark) AS avg_mark, MAX(total_mark) AS max_mark, MIN(total_mark) AS min_mark FROM stu_course GROUP BY cno; -- 某门课程的成绩排名按总分倒序 SELECT sno, total_mark, RANK() OVER (ORDER BY total_mark DESC) AS rank_no FROM stu_course WHERE cno 201501; -- 查询总成绩大于等于 60 分的学生名单 SELECT s.sno, s.sname, sc.cno, sc.total_mark FROM stu_info s JOIN stu_course sc ON s.sno sc.sno WHERE sc.total_mark 60;GROUP BY cno后面出现的非聚合列只有cno因为AVG、MAX、MIN是聚合函数其他列会被忽略或直接报错MySQL 的ONLY_FULL_GROUP_BY模式下严格禁止查询非聚合非分组列。如果想显示课程名需要再JOIN course_info。RANK()是 MySQL 8.0 引入的窗口函数如果你用的是 5.7 或更低版本要改用rank : rank 1的会话变量实现这一点在答辩时经常被问版本兼容性问题。5. 实验答辩前的边界问题与优化细节5.1 密码加盐与安全存储原设计用MD5(123)这在实验报告里可以但老师追问安全时就露怯了。MD5 不加盐很容易被彩虹表反查我可以直接告诉你123的 MD5 是202cb962ac59075b964b07152d234b70网上随便查。改进方案是加盐-- 注册时存储加盐后的哈希 INSERT INTO stulogin (username, password) VALUES (?, SHA2(CONCAT(?, random_salt), 256));盐值要为每个用户单独生成不能全局用一个。严格说应该用专门的库处理密码哈希但课程设计里能讲到加盐和 SHA-256已经比大多数同学深入了。5.2 触发器自动维护总成绩前面提到total_mark靠应用层或手动计算容易不一致。MySQL 里可以用触发器在插入和更新成绩时自动计算DELIMITER $$ CREATE TRIGGER trg_stu_course_insert BEFORE INSERT ON stu_course FOR EACH ROW BEGIN SET NEW.total_mark ROUND(NEW.usual_grade * 0.4 NEW.final_grade * 0.6, 0); END$$ CREATE TRIGGER trg_stu_course_update BEFORE UPDATE ON stu_course FOR EACH ROW BEGIN SET NEW.total_mark ROUND(NEW.usual_grade * 0.4 NEW.final_grade * 0.6, 0); END$$ DELIMITER ;BEFORE INSERT和BEFORE UPDATE两个触发器分别处理新增和修改。NEW是触发器内置的伪行用来引用即将插入或更新的新值。这样总成绩字段就从“手动维护”变成了“数据库自动维护”应用层再也不用管权重计算。5.3 索引与连接池的边界这个系统的数据量在实验场景下很小但答辩时老师可能会问你“如果学生有一万人成绩表有十万条记录怎么保证查询快”。答案分两层。第一层是索引stu_course的复合主键(sno, cno)已经能支撑按学号和课程号的等值查询如果经常按cno单独查成绩应该给cno加一个普通索引否则WHERE cno ?会走全表扫描。第二层是连接池真实 Web 系统里每次查询都新建数据库连接开销很大一般用 HikariCP 或 Druid 维护一批连接复用标准连接池参数是初始连接数 10、最大连接数 50、空闲超时 10 分钟。课程设计不要求你实际配连接池但能在答辩时说出“应用层连接由连接池管理避免频繁建立 TCP 连接”就足够了。5.4 并发更新成绩时的死锁避让多个教师同时给不同学生录成绩如果更新顺序不统一可能互相持有对方需要的行锁形成死锁。MySQL 检测到死锁会回滚其中一个事务。实验里最简单有效的规避办法是让所有更新语句都按(sno, cno)的排序顺序执行-- 统一先按 sno 升序再按 cno 升序降低死锁概率 UPDATE stu_course SET final_grade ? WHERE (sno, cno) IN ( SELECT sno, cno FROM stu_course ORDER BY sno, cno LIMIT ? );这个写法只是演示排序思路实际业务里更新单行时只要保证事务内加锁顺序一致即可。答辩时提到死锁的概念和处理策略比单纯说“我们用了事务”更有层次。5.5 备份与还原的检验方法课程设计最后通常要演示系统可恢复。MySQL 的命令行备份很简单# 备份整个 student 库到文件 mysqldump -u root -p --databases student student_backup.sql # 还原 mysql -u root -p student_backup.sqlmysqldump默认导出表结构和数据但不包含存储过程和触发器要加--routines参数才能导出触发器mysqldump -u root -p --databases student --routines student_backup.sql还原前最好先建一个空库测试确认视图和触发器都能正常重建。这比只贴一句mysqldump更有说服力因为老师很可能让你现场演示还原过程。这个项目从需求分析到触发器优化已经形成了完整闭环拿着上面的 SQL 和参数去答辩基本不会被问倒。本文还有配套的精品资源点击获取 学生成绩管理系统数据库设计：从单表到七张表的MySQL实践
简介面向数据库课程实验/大作业的学生成绩管理数据库系统设计文档围绕 MySQL 环境下的学生信息查询管理系统展开解决旧式人工/低效学籍管理中的检索慢、保密性差、数据冗余等问题适合正在完成课程设计或需要撰写需求分析的学生参考。资源为单个 docx 文档压缩包约 928KB正文包含需求分析、系统功能框架、运行环境、用户角色说明可直接编辑修改。资源已有 906 人学习参考能够帮助读者快速理解学生成绩管理系统的完整设计脉络。文档详细分解了管理员、教师、学生三类角色的权限与操作流程对信息管理、成绩管理、系统管理三个模块均给出功能与业务流程说明也涉及登录验证、MD5 加密等实现细节可作为数据库实验大作业的报告模板与数据库设计依据。1. 为什么我把学生成绩管理数据库从单表拆成了七张表数据库实验大作业里学生成绩管理系统是出现频率最高的题目但大多数提交上来的设计都是“一张成绩表走天下”学号、姓名、课程、分数全塞在一起。这种设计应付课程报告可以一到答辩现场老师问“学生能不能改自己的成绩”“教师删课之后选课记录怎么办”基本就答不上来。这份设计的不同之处在于它把用户角色直接做进了模式里管理员、教师、学生各自拥有独立的账号表和权限边界成绩和选课信息通过外键关联到学生表和课程表登录密码用 MD5 存储。它不是一个只能跑通 CRUD 的玩具而是把数据库的安全性和完整性考虑进去的课程设计范本。适合正在做数据库课程设计、准备答辩或者想系统梳理 MySQL 权限模型与关系模式设计的同学参考。下面按我实际拆解这个项目的顺序从权限模型、建表语句到查询优化逐一展开。2. 成绩管理系统的权限模型与关系模式设计2.1 三种角色的权限边界这个系统把用户分成三类管理员、教师、学生。管理员是 root 用户拥有所有表的增删改查权限教师只能操作自己授课相关的选课记录比如录入成绩、修改选课信息但不能看其他教师的数据学生只有查询权限不能修改任何数据。这个权限模型对应到数据库层面不是靠某一条 SQL 实现的而是靠关系模式的拆分登录账号表和业务数据表分开账号表只存用户名和密码业务表里只存学号、工号这类业务主键。管理员维护账号和业务表教师通过课程信息表里的任课教师字段来限定操作范围学生则依靠学号与选课表的关联来限定查询范围。角色账号表可操作业务表操作范围管理员adminstu_info / tea_info / course_info / stu_course全部数据增删改查维护账号教师tealoginstu_course / course_info仅任课课程的选课与成绩学生stuloginstu_info / stu_course仅本人信息与本人成绩2.2 E-R 图向关系模式的转化原设计定义了课程、学生、教师、成绩四个核心实体外加管理员、学生登录、教师登录三个账号实体。把 E-R 图转化为关系模式时最关键的是确定主键和外键stu_info(sno, sname, age, sex, dept, place)主键sno。tea_info(tno, tname, dept)主键tno。course_info(cno, cname, tname, student_num)主键cno其中tname是冗余的任课教师姓名原设计没有用tno做外键这是个值得在答辩时讨论的点。stu_course(sno, cno, usual_grade, final_grade, total_mark)主键是复合键(sno, cno)sno和cno分别外键引用stu_info和course_info。admin(username, password)、tealogin(username, password)、stulogin(username, password)三个账号表分别管理三类登录账号。这里要特别注意stu_course表的设计它既是选课表又是成绩表平时成绩、期末成绩、总成绩都放在同一个记录里。这种设计简化了查询但会带来更新异常——如果平时成绩改了总成绩必须手动同步。后面第 5 章会讲用触发器解决。2.3 为什么选复合主键而不是自增 IDstu_course的主键采用(sno, cno)复合主键而不是单独加一个自增id字段。这样做的好处是天然保证同一个学生不能重复选同一门课数据库层面就能拦截重复插入。如果改用自增主键就需要额外给(sno, cno)加唯一索引否则会插入重复选课记录。我一般建议课程设计中不要图省事只建三张表学生表、课程表、成绩表。像原设计这样把登录表独立出来虽然多建了三张表但好处很明显业务数据表里不混入登录密码即使业务表被误导出密码也不会泄露。这是符合自主访问控制原则的最小实现。第 2 章的表格和模式说明已经把逻辑结构讲清楚了下面直接进入 MySQL 的物理实现。3. MySQL 建表语句与初始化数据实操3.1 建库与表结构原设计最后落地在 MySQL跟需求文档里写的 SQL Server 有点出入但反而更贴近现在课程设计的主流环境。先建库然后按依赖顺序建表。注意外键表必须在被引用表创建之后再建所以顺序是先建业务表stu_info、tea_info、course_info再建选课表stu_course最后建三个登录表。-- 建库指定 utf8mb4 字符集避免中文乱码 CREATE DATABASE IF NOT EXISTS student DEFAULT CHARSET utf8mb4; USE student; -- 学生信息表 CREATE TABLE IF NOT EXISTS stu_info ( sno VARCHAR(20) NOT NULL COMMENT 学号, sname VARCHAR(30) COMMENT 姓名, age NUMERIC(2) COMMENT 年龄, sex VARCHAR(2) COMMENT 性别, dept VARCHAR(20) COMMENT 院系, place VARCHAR(20) COMMENT 籍贯, PRIMARY KEY (sno) ) DEFAULT CHARSETutf8mb4 COMMENT学生信息表; -- 教师信息表 CREATE TABLE IF NOT EXISTS tea_info ( tno VARCHAR(20) NOT NULL COMMENT 教师工号, tname VARCHAR(30) COMMENT 姓名, dept VARCHAR(20) COMMENT 院系, PRIMARY KEY (tno) ) DEFAULT CHARSETutf8mb4 COMMENT教师信息表; -- 课程信息表 CREATE TABLE IF NOT EXISTS course_info ( cno VARCHAR(20) NOT NULL COMMENT 课程号, cname VARCHAR(30) COMMENT 课程名, tname VARCHAR(30) COMMENT 任课教师, student_num NUMERIC(10) COMMENT 课程人数, PRIMARY KEY (cno) ) DEFAULT CHARSETutf8mb4 COMMENT课程信息表; -- 学生选课表成绩表 CREATE TABLE IF NOT EXISTS stu_course ( sno VARCHAR(20) NOT NULL COMMENT 学号, cno VARCHAR(20) NOT NULL COMMENT 课程号, usual_grade INT COMMENT 平时成绩, final_grade INT COMMENT 期末成绩, total_mark INT COMMENT 总成绩, PRIMARY KEY (sno, cno), FOREIGN KEY (sno) REFERENCES stu_info(sno), FOREIGN KEY (cno) REFERENCES course_info(cno) ) DEFAULT CHARSETutf8mb4 COMMENT选课信息表; -- 管理员账号表 CREATE TABLE IF NOT EXISTS admin ( username VARCHAR(20) NOT NULL COMMENT 用户名, password VARCHAR(30) COMMENT 登录密码, PRIMARY KEY (username) ) DEFAULT CHARSETutf8mb4 COMMENT管理员表; -- 教师登录表外键关联教师信息表 CREATE TABLE IF NOT EXISTS tealogin ( username VARCHAR(20) NOT NULL, password VARCHAR(30), PRIMARY KEY (username), FOREIGN KEY (username) REFERENCES tea_info(tno) ) DEFAULT CHARSETutf8mb4 COMMENT教师登录表; -- 学生登录表外键关联学生信息表 CREATE TABLE IF NOT EXISTS stulogin ( username VARCHAR(20) NOT NULL, password VARCHAR(30), PRIMARY KEY (username), FOREIGN KEY (username) REFERENCES stu_info(sno) ) DEFAULT CHARSETutf8mb4 COMMENT学生登录表;这段 SQL 里有几个参数值得展开说。VARCHAR(20)是学号长度不同学校学号长度不一样实验里可以按本校规则调整但注意学号要和登录表的username完全一致否则外键会失败。NUMERIC(2)表示年龄最大两位不需要更大的精度。COMMENT是字段注释写清楚字段含义这个习惯在课程设计的文档评审里很加分。登录表的外键直接引用业务表主键意思是教师登录账号的username必须是tea_info里存在的工号学生登录账号必须是stu_info里存在的学号。这保证不会出现“账号存在但业务数据不存在”的孤儿数据。3.2 初始化数据与 MD5 密码原设计插入了三条管理员、三位教师、三位学生和对应的业务数据。密码统一用MD5(123)存储不存明文。-- 管理员账号 INSERT INTO admin VALUES (2013302550010, MD5(123)); INSERT INTO admin VALUES (2013302550011, MD5(123)); INSERT INTO admin VALUES (2013302550012, MD5(123)); -- 教师账号与教师信息 INSERT INTO tealogin VALUES (2013302540010, MD5(123)); INSERT INTO tealogin VALUES (2013302540011, MD5(123)); INSERT INTO tealogin VALUES (2013302540012, MD5(123)); INSERT INTO tea_info VALUES (2013302540010,赵一,计算机学院); INSERT INTO tea_info VALUES (2013302540011,赵二,经济与管理学院); INSERT INTO tea_info VALUES (2013302540012,赵三,物理学院); -- 学生账号与学生信息 INSERT INTO stulogin VALUES (2013302530010, MD5(123)); INSERT INTO stulogin VALUES (2013302530011, MD5(123)); INSERT INTO stulogin VALUES (2013302530012, MD5(123)); INSERT INTO stu_info VALUES (2013302530010,张一,20,男,计算机学院,湖北); INSERT INTO stu_info VALUES (2013302530011,张二,21,女,经济与管理学院,湖南); INSERT INTO stu_info VALUES (2013302530012,张三,22,男,物理学院,福建); -- 课程与选课成绩 INSERT INTO course_info VALUES (201501,数据库,赵一); INSERT INTO course_info VALUES (201502,C语言程序设计,赵二); INSERT INTO course_info VALUES (201503,计算机网络,赵一); INSERT INTO stu_course VALUES (2013302530012,201501,90,90,90); INSERT INTO stu_course VALUES (2013302530012,201502,100,90,94); INSERT INTO stu_course VALUES (2013302530012,201503,90,100,96);注意原设计里course_info的student_num字段在插入语句里没有给值MySQL 会默认填NULL。如果后续要统计课程人数这个字段要么在应用层更新要么用视图实时计算。我建议改成视图直接用COUNT(*)统计选课表这样student_num就不容易“变脏”。MD5(123)是个演示级写法。实际系统里应该用MD5(CONCAT(123, salt))做加盐或者直接用SHA2。这个点在答辩时主动提出来比等老师问要有说服力得多。3.3 常见建表报错与处理建表时最容易踩的坑有两个。第一个是外键引用的列没有建索引MySQL 会直接报ERROR 1215 (HY000): Cannot add foreign key constraint。被引用的sno、cno本来就是主键有主键索引所以这里没问题。但如果你改了表的字段要注意被引用列必须是索引列。第二个是字符集不一致登录表用了utf8业务表用了utf8mb4外键比较会报Illegal mix of collations。统一用utf8mb4就能避开。4. 多角色查询与成绩统计的 SQL 实战4.1 学生查自己的成绩学生登录后只能查自己的成绩查询条件必须带上当前登录的学号而不是让用户自己填学号。实际应用里学号是从 session 里取出来的不会来自前端输入。模拟实验时用参数占位符更规范-- 学生查看自己的所有成绩需要关联课程信息拿到课程名 SELECT sc.cno, ci.cname, sc.usual_grade, sc.final_grade, sc.total_mark FROM stu_course sc JOIN course_info ci ON sc.cno ci.cno WHERE sc.sno ?; -- ? 是当前登录学生的学号这里用了JOIN而不是直接在stu_course里查是因为成绩表里只有课程号cno用户界面上要显示“数据库”“计算机网络”这类课程名必须关联course_info。如果不关联前端就得拿课程号再去数据库查一遍多一次往返。JOIN在 MySQL 里的执行逻辑是先把两表按cno做笛卡尔积再用ON条件过滤最后用WHERE限制学号。实际查询优化器会先下推WHERE条件不会真的做全量笛卡尔积但写 SQL 时仍然应该先写WHERE缩小范围、再写JOIN条件这样阅读顺序更符合人的思维。4.2 教师录入成绩与修改权限控制教师模块的核心操作是给选课学生录入成绩。原设计里的权限约束是“只能操作任课课程的选课表”这个限制在 SQL 上要靠子查询来实现先根据教师登录名去课程表里找到课程再更新对应课程的成绩。-- 教师录入成绩只允许更新自己任课的课程 UPDATE stu_course SET usual_grade ?, -- 平时成绩 final_grade ?, -- 期末成绩 total_mark ROUND(usual_grade * 0.4 final_grade * 0.6, 0) -- 这里假设权重 WHERE cno ( SELECT cno FROM course_info WHERE tname ? ) AND sno ?;这个 SQL 的关键在WHERE cno (SELECT cno FROM course_info WHERE tname ?)。子查询先把课程限定到当前教师名下如果这个教师没有任课子查询返回空更新影响行数为 0从数据层面挡住了越权操作。不过这里有个隐患course_info里tname不是主键如果同名教师存在子查询会返回多行导致报错Subquery returns more than 1 row。更稳妥的方案是课程表里存tno外键或者把tname改成唯一索引。这是原设计的一个短板答辩时可以主动提出改进方案。total_mark的计算我直接写在UPDATE语句里了利用的是 SQL 语句中字段引用顺序的特性SET子句里usual_grade和final_grade已经被赋新值total_mark ROUND(usual_grade * 0.4 final_grade * 0.6, 0)引用的是更新后的值。这样做虽然方便但不够严谨因为权重比例写在 SQL 里被硬编码了后续如果要调整平时分和期末分的占比就得改 SQL。把权重抽成系统参数表或者在应用层算好再传进来会更合理。4.3 管理员删除学生时的外键约束处理管理员可以删除学生但stu_course表里有外键引用stu_info。直接删除有选课记录的学生会报外键冲突DELETE FROM stu_info WHERE sno 2013302530010; -- 报错Cannot delete or update a parent row: a foreign key constraint fails处理方式有两种。第一种是先删子表再删父表事务包裹START TRANSACTION; DELETE FROM stulogin WHERE username 2013302530010; DELETE FROM stu_course WHERE sno 2013302530010; DELETE FROM stu_info WHERE sno 2013302530010; COMMIT;第二种是在建表时就指定ON DELETE CASCADE。比如stu_course表外键改为FOREIGN KEY (sno) REFERENCES stu_info(sno) ON DELETE CASCADE这样删除学生时选课成绩会自动删除。但要注意如果成绩记录还有审计需求级联删除会把历史数据直接抹掉不是所有场景都合适。课程设计里用第一种事务方式更安全也更能体现你对并发控制的理解。4.4 成绩统计与排名的常用查询实验报告的加分项是统计类 SQL。下面这几个可以直接用在系统“成绩分布”页面-- 每门课程的平均分、最高分、最低分 SELECT cno, AVG(total_mark) AS avg_mark, MAX(total_mark) AS max_mark, MIN(total_mark) AS min_mark FROM stu_course GROUP BY cno; -- 某门课程的成绩排名按总分倒序 SELECT sno, total_mark, RANK() OVER (ORDER BY total_mark DESC) AS rank_no FROM stu_course WHERE cno 201501; -- 查询总成绩大于等于 60 分的学生名单 SELECT s.sno, s.sname, sc.cno, sc.total_mark FROM stu_info s JOIN stu_course sc ON s.sno sc.sno WHERE sc.total_mark 60;GROUP BY cno后面出现的非聚合列只有cno因为AVG、MAX、MIN是聚合函数其他列会被忽略或直接报错MySQL 的ONLY_FULL_GROUP_BY模式下严格禁止查询非聚合非分组列。如果想显示课程名需要再JOIN course_info。RANK()是 MySQL 8.0 引入的窗口函数如果你用的是 5.7 或更低版本要改用rank : rank 1的会话变量实现这一点在答辩时经常被问版本兼容性问题。5. 实验答辩前的边界问题与优化细节5.1 密码加盐与安全存储原设计用MD5(123)这在实验报告里可以但老师追问安全时就露怯了。MD5 不加盐很容易被彩虹表反查我可以直接告诉你123的 MD5 是202cb962ac59075b964b07152d234b70网上随便查。改进方案是加盐-- 注册时存储加盐后的哈希 INSERT INTO stulogin (username, password) VALUES (?, SHA2(CONCAT(?, random_salt), 256));盐值要为每个用户单独生成不能全局用一个。严格说应该用专门的库处理密码哈希但课程设计里能讲到加盐和 SHA-256已经比大多数同学深入了。5.2 触发器自动维护总成绩前面提到total_mark靠应用层或手动计算容易不一致。MySQL 里可以用触发器在插入和更新成绩时自动计算DELIMITER $$ CREATE TRIGGER trg_stu_course_insert BEFORE INSERT ON stu_course FOR EACH ROW BEGIN SET NEW.total_mark ROUND(NEW.usual_grade * 0.4 NEW.final_grade * 0.6, 0); END$$ CREATE TRIGGER trg_stu_course_update BEFORE UPDATE ON stu_course FOR EACH ROW BEGIN SET NEW.total_mark ROUND(NEW.usual_grade * 0.4 NEW.final_grade * 0.6, 0); END$$ DELIMITER ;BEFORE INSERT和BEFORE UPDATE两个触发器分别处理新增和修改。NEW是触发器内置的伪行用来引用即将插入或更新的新值。这样总成绩字段就从“手动维护”变成了“数据库自动维护”应用层再也不用管权重计算。5.3 索引与连接池的边界这个系统的数据量在实验场景下很小但答辩时老师可能会问你“如果学生有一万人成绩表有十万条记录怎么保证查询快”。答案分两层。第一层是索引stu_course的复合主键(sno, cno)已经能支撑按学号和课程号的等值查询如果经常按cno单独查成绩应该给cno加一个普通索引否则WHERE cno ?会走全表扫描。第二层是连接池真实 Web 系统里每次查询都新建数据库连接开销很大一般用 HikariCP 或 Druid 维护一批连接复用标准连接池参数是初始连接数 10、最大连接数 50、空闲超时 10 分钟。课程设计不要求你实际配连接池但能在答辩时说出“应用层连接由连接池管理避免频繁建立 TCP 连接”就足够了。5.4 并发更新成绩时的死锁避让多个教师同时给不同学生录成绩如果更新顺序不统一可能互相持有对方需要的行锁形成死锁。MySQL 检测到死锁会回滚其中一个事务。实验里最简单有效的规避办法是让所有更新语句都按(sno, cno)的排序顺序执行-- 统一先按 sno 升序再按 cno 升序降低死锁概率 UPDATE stu_course SET final_grade ? WHERE (sno, cno) IN ( SELECT sno, cno FROM stu_course ORDER BY sno, cno LIMIT ? );这个写法只是演示排序思路实际业务里更新单行时只要保证事务内加锁顺序一致即可。答辩时提到死锁的概念和处理策略比单纯说“我们用了事务”更有层次。5.5 备份与还原的检验方法课程设计最后通常要演示系统可恢复。MySQL 的命令行备份很简单# 备份整个 student 库到文件 mysqldump -u root -p --databases student student_backup.sql # 还原 mysql -u root -p student_backup.sqlmysqldump默认导出表结构和数据但不包含存储过程和触发器要加--routines参数才能导出触发器mysqldump -u root -p --databases student --routines student_backup.sql还原前最好先建一个空库测试确认视图和触发器都能正常重建。这比只贴一句mysqldump更有说服力因为老师很可能让你现场演示还原过程。这个项目从需求分析到触发器优化已经形成了完整闭环拿着上面的 SQL 和参数去答辩基本不会被问倒。本文还有配套的精品资源点击获取