schoolDB四张表建表造数查询完整实战：学生教师课程成绩一网打尽

刷到不少人在问 schoolDB 数据库的四张表怎么建、怎么塞数据正好我最近刚帮人整理完一套带数据的完整案例从建库、建表、造数据到常用查询全部在 MySQL 里实测过一遍今天就把这套“有数据的四张表”完整拆给大家。这套方案适合谁正在做数据库课程设计的学生、刚学完 SQL 想找个完整案例练手的人、想快速搭一套测试数据做功能开发的人。拿到之后可以直接在 MySQL 里执行MSSQL 和达梦数据库稍微改一下数据类型也能跑通。先把核心结论放前面schoolDB 最常见的四张表就是学生表、教师表、课程表、选课成绩表。这个组合不是随便凑的而是从学校教学管理场景里一步步拆出来的最小可用集合。下面我从表结构设计、建表语句、造数据方法、连表查询、踩坑经验五个方面完整讲一遍你跟着操作就能得到一套能跑、能查、能演示的完整数据库。1. 为什么课程设计总绕不开这四张表1.1 从业务场景倒推表结构很多人一上来就建表这个顺序其实是反的。正确的做法是先想清楚一个问题这个数据库到底要回答哪些业务问题schoolDB 这个库核心业务是一个最小闭环学校有学生和老师老师开课学生选课选完课考试出成绩。围绕这个闭环你需要记录四类核心信息谁在上学学生、谁在授课教师、上什么课课程、学得怎么样成绩。学生表存放学生基础信息如学号、姓名、性别、出生日期、班级、联系电话、入学年份。教师表存放教师基础信息如工号、姓名、性别、职称、所属院系、入职时间。课程表存放课程信息如课程编号、课程名称、学分、授课教师、开课学期。成绩表存放学生选课后的成绩记录关联学生和课程记录分数和考试时间。这四张表构成了一个完整的教学管理闭环。为什么强调“最小”因为真实学校的业务远不止这些还有院系表、班级表、教材表、考勤表等等。但作为课程设计或学习案例四张表已经能把数据库设计的核心知识点全部覆盖实体定义、关系建模、主外键、约束、连表查询、聚合统计、事务和备份恢复全都能在这四张表上练一遍。如果你后面想扩展通常的演进方向是拆班级表、拆院系表甚至加一个用户权限表。但那是进阶玩法先把这四张表吃透后面所有扩展都是水到渠成的事。1.2 四张表之间的引用关系这四张表不是孤立的它们之间的关联关系是整个设计的灵魂。成绩表是整个数据库的枢纽它通过 student_id 关联学生表通过 course_id 关联课程表课程表通过 teacher_id 关联教师表。画个关系图在脑子里过一遍教师对课程是 1 对 N一位老师可以教多门课课程对成绩是 1 对 N一门课被很多学生选每个学生产生一条成绩记录学生对成绩是 1 对 N一个学生可以选多门课产生多条成绩记录。这里有一个关系型数据库里最经典的设计点成绩表本质上是一个“多对多关系的中间表”。学生和课程之间天然是多对多关系——一个学生选多门课一门课被多个学生选——这种关系必须通过中间表来拆解拆成学生到成绩的一对多、课程到成绩的一对多。如果你把课程直接塞进学生表里或者把学生直接塞进课程表里后面查数据只会是一场灾难。要么字段冗余到没法看要么查询逻辑绕得自己都看不懂。这个设计思路是你在答辩或写实验报告时需要重点说明的地方——为什么成绩表一定要独立存在。2. 建表语句这样写后续少改八遍2.1 字段类型与长度的选择依据建表的时候字段类型选不对后面造数据、跑查询都会出问题。我直接把四张表的 DDL 给出来然后逐个讲为什么这么写这样你既能直接抄又能理解背后的取舍。CREATE DATABASE IF NOT EXISTS schoolDB DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci; USE schoolDB; CREATE TABLE teacher ( id INT AUTO_INCREMENT PRIMARY KEY COMMENT 主键, teacher_no VARCHAR(20) NOT NULL UNIQUE COMMENT 教师工号, name VARCHAR(50) NOT NULL COMMENT 姓名, gender ENUM(男, 女) DEFAULT 男 COMMENT 性别, title VARCHAR(30) COMMENT 职称, department VARCHAR(50) COMMENT 所属院系, hire_date DATE COMMENT 入职时间, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间 ) ENGINEInnoDB COMMENT教师表;这里有几个核心选择逻辑我说一下id 用 INT AUTO_INCREMENT这是主键的标准做法。教师工号虽然也有唯一性但不建议当主键因为工号可能因人事调整发生变化而自增主键永不涉及业务变更。这个原则对所有表通用。工号、姓名这类字段用 VARCHAR(20)、VARCHAR(50)长度按最大可能值再留一点余量。中文姓名很少超过 10 个字VARCHAR(50) 绰绰有余。gender 用 ENUM 直观但如果后续要增加其他选项改列定义比较麻烦。生产环境我更推荐 TINYINT 加代码映射课程设计用 ENUM 足够。hire_date 用 DATE 而不是 DATETIME因为入职时间只需要精确到天用 DATE 更省空间语义也更清晰。学生表和课程表、成绩表的 DDL 如下CREATE TABLE student ( id INT AUTO_INCREMENT PRIMARY KEY COMMENT 主键, student_no VARCHAR(20) NOT NULL UNIQUE COMMENT 学号, name VARCHAR(50) NOT NULL COMMENT 姓名, gender ENUM(男, 女) DEFAULT 男 COMMENT 性别, birth_date DATE COMMENT 出生日期, phone VARCHAR(11) COMMENT 联系电话, class_name VARCHAR(50) COMMENT 班级, enroll_year YEAR COMMENT 入学年份, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间 ) ENGINEInnoDB COMMENT学生表; CREATE TABLE course ( id INT AUTO_INCREMENT PRIMARY KEY COMMENT 主键, course_no VARCHAR(20) NOT NULL UNIQUE COMMENT 课程编号, course_name VARCHAR(100) NOT NULL COMMENT 课程名称, credit DECIMAL(3,1) NOT NULL DEFAULT 2.0 COMMENT 学分, teacher_id INT COMMENT 授课教师ID, semester VARCHAR(20) COMMENT 开课学期, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, CONSTRAINT fk_course_teacher FOREIGN KEY (teacher_id) REFERENCES teacher(id) ) ENGINEInnoDB COMMENT课程表; CREATE TABLE score ( id INT AUTO_INCREMENT PRIMARY KEY COMMENT 主键, student_id INT NOT NULL COMMENT 学生ID, course_id INT NOT NULL COMMENT 课程ID, score DECIMAL(5,2) COMMENT 成绩, exam_date DATE COMMENT 考试日期, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student(id), CONSTRAINT fk_score_course FOREIGN KEY (course_id) REFERENCES course(id), UNIQUE KEY uk_student_course (student_id, course_id) ) ENGINEInnoDB COMMENT选课成绩表;有几个细节特别容易出错单独拿出来强调credit 用 DECIMAL(3,1)是因为学分经常出现 1.5、2.5、3.5 这种小数用 INT 会丢精度用 FLOAT 又会有浮点误差。DECIMAL 是定点数适合存学分、金额、成绩这类要求精确计算的数值。score 用 DECIMAL(5,2)理论上可以存 0 到 999.99但成绩通常只在 0 到 100 之间。如果你担心脏数据可以加一个检查约束CHECK (score 0 AND score 100)。不过要注意MySQL 8.0.16 之前的版本会忽略 CHECK 约束只做语法校验真正想严格兜底还得靠应用层。成绩表上加了 UNIQUE KEY uk_student_course (student_id, course_id)这个唯一键非常关键。它从数据库层面保证同一个学生同一门课只能有一条成绩记录。如果没有这层约束程序写得不严谨时同一个人可能在一门课的成绩单里出现两次后续统计直接翻倍。课程表的 teacher_id 外键允许为空因为存在“课程还没分配老师”的情况但成绩表的 student_id 和 course_id 都是 NOT NULL因为成绩必须挂靠在具体的人和具体的课之上。2.2 主键、外键、约束设置的实操细节再单独聊聊主键和外键的实操原则这些是课堂上学不到、但实际开发里非常关键的东西。第一主键尽量用自增整数。很多教材喜欢用学号当主键但现实中确实出现过学号因转专业、复学、学籍异动而调整的情况。一旦学号改了所有引用它的表的关联数据都要跟着改这就是一场灾难。用自增 id 做主键学号只做唯一业务键两者互不干扰数据怎么变都不会影响关联关系。第二外键到底加不加我的建议是课程设计里一定要加因为你正在学习数据库完整性约束。加了外键后往 score 表里插入一条不存在的 student_idMySQL 会直接报错而不是等查数据时才发现问题。但进入生产环境后高并发写入场景下很多人会选择去掉外键把数据完整性校验放到应用层因为外键会带来额外的检查和锁开销。这个差异你心里有数就行至少现在这个阶段外键是你的朋友。第三字符集要统一用 utf8mb4。MySQL 5.5 之后才有 utf8mb4它能完整支持中文、繁体、emoji 等四字节字符。如果建库时用了老的 utf8某些生僻字会存不进去或者迁移数据时出现乱码。这是我在真实项目中踩过的坑现在建任何新库都直接 utf8mb4不带犹豫的。第四如果你拿到的实验指导书上有明确的字段清单那要以指导书为准。我这里给的是基于常见场景的标准设计导师如果指定了具体字段和类型优先按他的要求来把自己的理解和调整思路写进报告里的“设计说明”部分即可。3. 给四张表填充“像样”的数据3.1 手工造数与脚本批量生成的取舍表建好了下一步是往里塞数据。这里先要区分一下需求你是想要能演示功能的少量数据还是想建一套能测试查询性能的较大数据集如果只是课程设计演示手工造 15 到 20 条就够。关键是数据要“像样”不是随便 qwq 这种乱字符。学生姓名用现实中常见的名字班级名称用“计算机2101班”这种格式电话号码用符合 1 开头 11 位规则的号码。评分老师看到这样的数据第一印象就会好很多。如果你想练真本事我建议写一个存储过程批量生成数据。比如循环 200 次插入 200 个学生姓名可以从一个名字池里随机组合。这样后面练索引优化、分页查询、窗口函数时数据量才够看。下面这个存储过程是我常用的批量造数方式DELIMITER $$ CREATE PROCEDURE generate_students(IN total INT) BEGIN DECLARE i INT DEFAULT 1; DECLARE r1 INT; DECLARE r2 INT; DECLARE rand_name VARCHAR(50); DECLARE surname VARCHAR(10); DECLARE given VARCHAR(10); WHILE i total DO SET r1 FLOOR(1 RAND() * 10); SET surname ELT(r1, 王, 李, 张, 刘, 陈, 杨, 赵, 黄, 周, 吴); SET r2 FLOOR(1 RAND() * 10); SET given ELT(r2, 伟, 芳, 娜, 敏, 静, 磊, 军, 洋, 勇, 杰); SET rand_name CONCAT(surname, given); INSERT INTO student (student_no, name, gender, birth_date, phone, class_name, enroll_year) VALUES (CONCAT(2024, LPAD(i, 4, 0)), rand_name, IF(RAND() 0.5, 男, 女), DATE_SUB(2005-01-01, INTERVAL FLOOR(RAND() * 1000) DAY), CONCAT(13, LPAD(FLOOR(RAND() * 1000000000), 9, 0)), CONCAT(计算机, 2000 FLOOR(RAND() * 5), 班), 2024 FLOOR(RAND() * 2)); SET i i 1; END WHILE; END$$ DELIMITER ; CALL generate_students(200);这里几个要点解释一下LPAD 函数用来补齐位数比如 LPAD(3, 4, 0) 会得到“0003”保证学号格式统一。ELT 函数从列表里按索引取值配合 RAND() 生成随机名字。当然真实的名字池应该更大才不容易撞名我这里只是为了演示用法。DATE_SUB 配合 INTERVAL 生成出生日期保证年龄在合理范围内。手机号用 13 开头加 9 位随机数符合国内手机号的基本格式。教师和课程的造数逻辑类似可以直接用 INSERT 语句手工插入几条这里数据量少手工写更可控INSERT INTO teacher (teacher_no, name, gender, title, department, hire_date) VALUES (T001, 陈立群, 男, 教授, 计算机学院, 2008-09-01), (T002, 林晓梅, 女, 副教授, 计算机学院, 2012-06-15), (T003, 王建国, 男, 讲师, 数学学院, 2016-03-20), (T004, 赵文静, 女, 讲师, 外国语学院, 2019-09-10); INSERT INTO course (course_no, course_name, credit, teacher_id, semester) VALUES (C001, 数据库原理, 3.0, 1, 2024-2025-1), (C002, 数据结构, 4.0, 2, 2024-2025-1), (C003, 高等数学, 5.0, 3, 2024-2025-1), (C004, 大学英语, 2.0, 4, 2024-2025-1), (C005, 操作系统, 3.5, 1, 2024-2025-2);课程表的 teacher_id 必须能关联到 teacher 表里已有的 id否则外键约束直接报错。所以顺序一定是先插教师再插课程。3.2 成绩表数据要避免的三种错误成绩表是最容易出问题的一张表造数据时常见三种错误你最好避开。第一种错误分数全部集中在 80 分以上。数据看起来光鲜但统计、排序、分组练习全都没有区分度。真实数据应该覆盖完整分布有高分有及格线附近的有不及格的有缺考为 NULL 的。这样你练 AVG、MAX、MIN、GROUP BY、HAVING 时才有真实场景可练。第二种错误student_id 和 course_id 组合重复。表上有唯一键 uk_student_course 兜底插入重复组合时 MySQL 会报 Duplicate entry 错误。但从设计角度说你也不应该生成重复的选课记录。插入成绩前先确认学生表和课程表各自的有效数据范围再控制组合不重复。第三种错误忽略了分数的语义。百分制的分数应该限制在 0 到 100你可以用 ROUND(40 RAND() * 60, 1) 生成 40 到 100 之间的随机成绩既保证范围又天然产生一些低分段数据。一次性生成大量成绩记录的技巧是走一条 SELECT 语句来 INSERTINSERT INTO score (student_id, course_id, score, exam_date) SELECT s.id, c.id, ROUND(40 RAND() * 60, 1), DATE_SUB(2025-01-15, INTERVAL FLOOR(RAND() * 20) DAY) FROM student s CROSS JOIN course c WHERE s.id 80 AND c.id 3;这段 SQL 的巧妙之处在于INSERT 的值不是手写的常量而是从 student 表和 course 表做笛卡尔积后动态生成。一次就能给 80 个学生、3 门课各生成一条成绩总共 240 条比一条条 INSERT 高效得多。WHERE 子句用来控制参与生成成绩的学生和课程范围。如果你要更精细地控制每个学生的选课数量比如有的学生选 2 门、有的选 5 门那就需要写循环或存储过程来逐人处理。但上面这条 SQL 已经足够应付大部分课程设计了。4. 最常用的连表查询与统计实操4.1 查询每个学生的总成绩和平均分数据到位后就到了核心价值环节——查询。四张表的经典连表查询是课程设计的重头戏也是面试和考试最喜欢考的点。先看最经典的查询查每个学生的学号、姓名、选课门数、总成绩、平均分。SELECT s.student_no, s.name AS student_name, COUNT(sc.id) AS course_count, SUM(sc.score) AS total_score, ROUND(AVG(sc.score), 2) AS avg_score FROM student s LEFT JOIN score sc ON s.id sc.student_id GROUP BY s.id, s.student_no, s.name ORDER BY avg_score DESC;这里要注意 GROUP BY 的字段。SELECT 里出现了 student_no、nameGROUP BY 里就必须带上它们。虽然 MySQL 在某些模式下允许只 GROUP BY 主键但为了兼容性和规范性把所有非聚合字段都写进 GROUP BY 是最稳妥的做法。为什么用 LEFT JOIN 而不是 INNER JOIN因为要保留没选任何课的学生。如果一个学生在学籍库里但暂时没选课INNER JOIN 会直接把他过滤掉而 LEFT JOIN 会把他保留下来选课门数和平均分显示为 NULL。这才叫“以学生为主体”的查询。avg_score 字段用 ROUND 函数保留两位小数是因为 AVG 算出来的结果可能是一长串小数直接展示会很丑。ORDER BY avg_score DESC 按平均分从高到低排列一眼就能看出谁学得好。4.2 课程不及格率统计与排名再进阶一点统计每门课程的不及格人数和不及格率。这个查询在真实的教学管理系统中非常常见教务老师天天要用。SELECT c.course_no, c.course_name, COUNT(sc.id) AS total_students, SUM(CASE WHEN sc.score 60 THEN 1 ELSE 0 END) AS failed_count, ROUND(SUM(CASE WHEN sc.score 60 THEN 1 ELSE 0 END) / COUNT(sc.id) * 100, 2) AS fail_rate FROM course c LEFT JOIN score sc ON c.id sc.course_id GROUP BY c.id, c.course_no, c.course_name HAVING fail_rate 0 ORDER BY fail_rate DESC;这个案例里有两个知识点值得细说。一个是 CASE WHEN 条件聚合。它可以在聚合函数内部做条件判断把一个字段按条件拆成多个计数。这里是判断成绩是否小于 60用同样的思路还能统计优秀率、良好率。另一个是 HAVING 与 WHERE 的区别。WHERE 是在分组之前过滤原始行HAVING 是在分组之后过滤聚合结果。“不及格率大于 0”这个条件是聚合后产生的只能在 HAVING 里写因为 WHERE 执行时 fail_rate 还不存在。这是 SQL 初学者最容易混淆的知识点笔试面试也经常考。如果你还要做排名MySQL 8.0 里可以用窗口函数SELECT s.student_no, s.name, ROUND(AVG(sc.score), 2) AS avg_score, RANK() OVER (ORDER BY AVG(sc.score) DESC) AS rank_no FROM student s JOIN score sc ON s.id sc.student_id GROUP BY s.id, s.student_no, s.name;RANK() 和 DENSE_RANK() 的差别在于并列时是否跳号RANK() 遇到并列成绩会输出 1、1、3跳过一个号DENSE_RANK() 输出 1、1、2不跳号。窗口函数是数据库查询里的实用工具课程设计里用上它明显比只会 GROUP BY 的写法高一个层次。4.3 查看每门课程的授课教师信息最后一个常用的宽表查询模式把课程、教师、选课人数合到一张结果集里模拟“课程信息总览”页面的数据来源。做管理系统的人对这个需求再熟悉不过。SELECT c.course_no, c.course_name, c.credit, t.name AS teacher_name, t.title, COUNT(sc.id) AS enrolled_count FROM course c LEFT JOIN teacher t ON c.teacher_id t.id LEFT JOIN score sc ON c.id sc.course_id GROUP BY c.id, c.course_no, c.course_name, c.credit, t.name, t.title ORDER BY enrolled_count DESC;这种“查一张主表同时把关联表的关键字段带出来”的写法在实际开发里出现频率极高。后面你做学生管理系统、教务管理系统列表页和详情页 90% 的查询都是这个模式。掌握好 JOIN 的用法等于掌握了一切列表查询的骨架。这里 LEFT JOIN 的作用也很有意思。如果某门课程还没分配老师teacher_name 会是 NULL但课程本身不会丢失。如果换成 INNER JOIN没分配老师的课程会直接消失这在业务上是不能接受的。同理一门课如果还没有任何学生选LEFT JOIN 保住了课程记录enrolled_count 显示 0。5. 在这个练习项目里踩过的坑5.1 字符集和排序规则引发的乱码第一个坑也是新手最容易碰到的建表时没指定字符集插入中文后查询出来全是问号。MySQL 8.0 默认已经是 utf8mb4但如果你用的是老版本或者从旧项目拷贝的配置文件默认字符集可能是 latin1中文写入后直接变成乱码。解决方案是在建库时就明确指定这是最省事的做法CREATE DATABASE schoolDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;对于已经建好的库用 ALTER 语句可以补救ALTER DATABASE schoolDB CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; ALTER TABLE student CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;注意ALTER DATABASE 和 ALTER TABLE 只影响默认字符集已有列的字符集要用 CONVERT TO 才能转换。另外连接字符串也要指定字符集。命令行连接加--default-character-setutf8mb4JDBC 连接在 URL 后面加?useUnicodetruecharacterEncodingutf8。只有数据库改了而连接没改照样乱码这是我踩过最冤枉的一次。5.2 外键约束与删除策略的设置外键什么时候会让你崩溃当你想重置数据、清空重来的时候。清空成绩表直接 DELETE FROM score 没问题因为它是子表。但如果你想清空课程表而成绩表里还有成绩引用了课程就会触发外键约束报错提示不能删除或更新父记录。解决办法有三个先删子表再删父表用 SET FOREIGN_KEY_CHECKS0 临时关闭外键检查操作完再开启或者在定义外键时设置 ON DELETE CASCADE。我个人的意见是课程设计里尽量少用 CASCADE。级联删除虽然省事但隐患很大——删一门课成绩表里所有选这门课的成绩记录会全部消失而且是隐式删除你根本看不到删除过程。真要设置ON DELETE SET NULL 比 CASCADE 更稳把关联字段置空至少保留了操作痕迹。重置数据的常规操作顺序应该是先删成绩表数据再删课程表数据最后删学生和教师数据。这个顺序永远不要搞反。5.3 时间字段的三种写法最后说一下时间字段。很多人在做课程设计时习惯用 VARCHAR 存时间比如存“2024-09-01 08:30:00”。能跑但后患无穷。用字符串存时间你没法用 ORDER BY 得到正确的时间顺序除非严格控制格式没法用 DATE_SUB、DATE_ADD 做日期间隔计算查询时还得自己保证格式统一。这些问题在数据量少时看不出来一旦数据量上来后悔都来不及。正确做法是使用 DATE、DATETIME、TIMESTAMP 三种类型生日、入职时间只精确到天用 DATE。考试时间要精确到时分秒用 DATETIME。创建时间要跟随系统时区自动变化的用 TIMESTAMP。还有一个细节MySQL 里 TIMESTAMP 的有效范围只到 2038 年DATETIME 的范围大得多。如果要存几十年后的日期优先 DATETIME。我在这个项目里统一使用的规则是业务日期用 DATE业务时间用 DATETIME记录创建时间用 TIMESTAMP DEFAULT CURRENT_TIMESTAMP。这套规则在绝大多数管理系统里都是通用的。最后分享一个实际操作中的小经验做完这套数据后记得用一条 SQL 快速验证数据是否完整比如统计每个表的总行数、检查成绩表里是否有 NULL 异常数据。我用的是SELECT student AS tbl, COUNT(*) AS cnt FROM student UNION ALL SELECT teacher, COUNT(*) FROM teacher UNION ALL SELECT course, COUNT(*) FROM course UNION ALL SELECT score, COUNT(*) FROM score;如果四个计数都符合预期说明建表和造数环节没有问题可以放心拿去做查询练习或课程设计演示了。后面你想扩展的话可以在这个基础上加班级表、院系表或者用视图把常用查询固化下来生成 ER 关系图导出都是很好的进阶方向。