数据库课设从建表到20个SQL操作：全流程解析与避坑指南

简介大学教学应用系统的数据库课程设计完整方案面向计算机专业学生完成数据库建模、SQL 查询与报表输出的课程实践。方案围绕学生、教师、课程、登记、分组等核心实体展开涵盖数据表定义、E-R 图、20 项具体操作题目以及检索系名、按字母排序列出教师、统计选课人数和平均分等典型 SQL 示例可直接对照实现。资源为一份 pptx 演示文稿压缩包内共 1 个文件整体仅 57KB便于快速查看题设、源程序和结果输出。已有 165 人学习下载。PPT 中附有完整的附表数据与需求列表能帮助理解实体属性、关系模型和第一范式判定适合作为数据库课程设计选题拆解、数据库建表练习及 SQL 语句编写的参考。1. 数据库课设的核心不在建表而在那 20 个 SQL 操作做数据库课设的人最容易犯一个错花一整周把 E-R 图画得漂漂亮亮再花半天把表建好然后卡在查询阶段出不来。这份大学教学应用系统的课设材料表面看是一套学生、教师、课程、分组、登记的建表任务实际上的重头戏是后面那 20 个 SQL 操作——从简单条件检索到 EXISTS 嵌套子查询从 DELETE 关联删除到 GROUP BY 统计报表几乎把数据库原理课里所有高频考点全过了一遍。适合两类人一是正在做课设、需要完整参考流程的学生二是想快速复习 SQL 关联查询和聚合统计的在职者。这套数据量不大但关系足够典型照着跑一遍比刷十遍教材有用。2. 从数据表到 E-R 模型实体、属性和关联关系怎么拆2.1 五张表的字段设计类型、长度和约束怎么定拿到原始附表第一步不是急着写 CREATE TABLE而是把每个实体的字段、类型、主外键理清楚。这套课设涉及五个核心关系STUDENTS、TEACHERS、COURSES、SECTION、ENROLLS外加一个 STATE 其实只是 STUDENTS 的普通属性不需要单独建表。表结构设计如下以 MySQL 8.0 为例字符集用 utf8mb4CREATE DATABASE IF NOT EXISTS university DEFAULT CHARSET utf8mb4; USE university; CREATE TABLE students ( student_id INT PRIMARY KEY, student_name VARCHAR(50) NOT NULL, address VARCHAR(100), zip VARCHAR(10), city VARCHAR(30), state VARCHAR(20), sex CHAR(1) CHECK (sex IN (M,F)) ); CREATE TABLE teachers ( teacher_id INT PRIMARY KEY, teacher_name VARCHAR(50) NOT NULL, phone VARCHAR(20), salary DECIMAL(10,2) ); CREATE TABLE courses ( course_id INT PRIMARY KEY, course_name VARCHAR(50) NOT NULL, department VARCHAR(30), credits INT ); CREATE TABLE section ( section_id INT PRIMARY KEY, teacher_id INT, course_id INT, num_students INT, FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id), FOREIGN KEY (course_id) REFERENCES courses(course_id) ); CREATE TABLE enrolls ( course_id INT, section_id INT, student_id INT, grade INT, PRIMARY KEY (course_id, section_id, student_id), FOREIGN KEY (course_id) REFERENCES courses(course_id), FOREIGN KEY (section_id) REFERENCES section(section_id), FOREIGN KEY (student_id) REFERENCES students(student_id) );逻辑说明students 主键是学号类型用 INT 而不用 VARCHAR是为了避免字符串比较带来的隐性开销。sex 字段加 CHECK 约束从源头挡住脏数据。enrolls 是典型的三方关联表联合主键 (course_id, section_id, student_id) 保证同一个学生在同一分组不会重复登记。参数说明phone 字段用 VARCHAR(20)因为电话号码里有连字符不是纯数字不能用 INT。grade 用 INT 存 0-4 的绩点虽然实际成绩可能有小数但题目里全是整数这样最省事。courses 表的 credits 字段对应原题中的 nurc-credits学分数值很小SMALLINT 也够但 INT 更省心。2.2 关系模型的关键坑为什么这套设计满足第一范式但存在部分函数依赖材料里的关系模型描述提到了一个很重要的点当前的表结构属于第一范式但存在部分函数依赖。这在课设答辩时是高频问题。先看关系拆解——学生和课程之间是 m:n 关系通过 enrolls 表解耦教师和课程之间是 n:1 关系通过 section 表带出 teacher_idstudent 与 (course, section, student) 之间是 1:n。这个拆法是标准的问题出在下边。-- 验证部分函数依赖的典型表现 -- 在 enrolls 中grade 只依赖于 (course_id, section_id, student_id) 的完整组合 -- 但如果把 teacher_id 也放进 enrolls就会出现 grade 对 teacher_id 的部分依赖 -- 所以老师编号必须放在 section 表而不是 enrolls 表 SELECT COUNT(*) FROM enrolls;逻辑说明在课设报告里老师会问「为什么教师编号不在 enrolls 里」。正确回答是如果 enrolls 同时包含 teacher_id 和 grade那么 grade 只由学生和课程决定与教师无关这就产生部分函数依赖违反第二范式。所以设计上把 teacher_id 放到 section 表让教师与课程的对应关系独立维护。参数说明这段不是让你执行是让你在答辩时能说清楚「为什么这么拆」。真正的第二范式要求消除非主属性对候选键的部分依赖这套设计的 solution 就是让 enrolls 只保留「谁选了什么课、什么分组、多少分」不掺入教师维度。2.3 附表数据里藏着一个必须处理的问题性别字段和 Dr. 前缀原始数据里有几个容易让人翻车的细节。第一个第 12 题「检索只有男生选修的课程和学生名」里原文写的是「男牛」显然是录入错误sex 字段的值应该统一为 M 和 F。第二个教师姓名全部带「Dr.」前缀第 5 题按字母顺序排列时如果直接 ORDER BY teacher_name排序结果会把 Dr. 一起参与比较导致顺序看起来不对。-- 正确的排序方式去掉前缀再排 SELECT teacher_name, phone FROM teachers ORDER BY SUBSTRING_INDEX(teacher_name, , -1), teacher_name; -- 结果Cooke, Engle, Horn, Lowe, Olsen, Scango按姓氏排序逻辑说明SUBSTRING_INDEX(teacher_name, , -1) 取出空格后的姓氏部分先按姓氏排。这样输出结果从观感上更符合「按字母顺序列出教师姓名」的预期。虽然原题解法没有处理前缀但实际做课设时这一步会加分。参数说明这个写法只适用于 Dr. Xxx 这种固定格式。如果姓名格式不统一比如有人叫 Dr. Van Lowe按空格切就出问题。更稳妥的做法是单独加一个 last_name 字段但课设数据这么小SUBSTRING_INDEX 够用。3. 建库建表与输入子系统DDL 脚本和三种数据录入方案3.1 数据录入的三种实现GUI 表单、SQL 脚本和存储过程原题要求「编制输入子系统完成数据的录入」这是课设报告里占篇幅的部分。大多数学生的做法是用 Java Swing 或 C# WinForms 做一个表单界面连接数据库做 INSERT。但如果你的课设只要求「能录数据」而不强制要图形界面有三条路可以选。第一种直接写 INSERT 脚本把附表数据手动转成 SQL。简单直接但数据量一大就难受而且看不出「输入子系统」的工作量。第二种用存储过程做带校验的录入接口比如学号重复就报错、性别非法就拒绝。第三种做一个最小化的命令行交互程序循环读输入、执行插入。-- 存储过程风格带重复检查的录入接口以学生录入为例 DELIMITER $$ CREATE PROCEDURE sp_insert_student( IN p_id INT, IN p_name VARCHAR(50), IN p_address VARCHAR(100), IN p_zip VARCHAR(10), IN p_city VARCHAR(30), IN p_state VARCHAR(20), IN p_sex CHAR(1) ) BEGIN IF EXISTS (SELECT 1 FROM students WHERE student_id p_id) THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 学号已存在; ELSE IF p_sex NOT IN (M, F) THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 性别必须为 M 或 F; ELSE INSERT INTO students VALUES (p_id, p_name, p_address, p_zip, p_city, p_state, p_sex); END IF; END IF; END$$ DELIMITER ; -- 调用示例 CALL sp_insert_student(148, Susan Powell, 534 East River Dr, 19041, Haverford, PA, F);逻辑说明存储过程把校验逻辑下沉到数据库层业务代码只需要调用接口。这样课设报告里能写「输入子系统包含完整性校验」而不只是一堆裸 INSERT。这个方案比图形界面省事但又能体现数据库编程能力。参数说明SIGNAL SQLSTATE 45000 是 MySQL 自定义异常的标准写法45000 是通用的用户错误代码。注意存储过程内的 IF 判断是嵌套的性别校验放在学号校验之后避免一个记录报两个错。3.2 五张表的数据初始化照着附表逐条插入的注意点如果你选择用 INSERT 脚本一次性灌数据注意两个细节。第一插入顺序要按外键依赖来——先 students、teachers、courses再 section最后 enrolls否则外键约束会直接报错。第二附表 8 的 section 和附表 9 的 enrolls 数据是分开的不要混着录。INSERT INTO section (section_id, teacher_id, course_id, num_students) VALUES (1, 303, 450, 30), (2, 290, 730, 61), (3, 430, 290, 31), (4, 180, 480, 32), (5, 560, 450, 22), (6, 784, 480, 02); INSERT INTO enrolls (course_id, section_id, student_id, grade) VALUES (730, 1, 148, 3), (450, 2, 210, 3), (730, 1, 210, 1), (290, 1, 298, 2), (480, 2, 298, 3), (730, 1, 348, 2), (290, 1, 349, 1), (480, 1, 358, 4), (450, 1, 410, 2), (450, 1, 473, 2), (730, 1, 473, 3), (348, 2, 473, 0);逻辑说明注意 enrolls 里的 section_id 要和 section 表对应上。原题附表 9 的登记数据里course 和 section 的组合其实和附表 8 的 section 数据存在错位——比如 course 730 出现在 section 1 里但附表 8 中 section 1 对应的是 course 450。这就是原始数据的一处硬伤。参数说明遇到这种数据冲突时我的做法是「以登记表为准、同一分组内课程统一」。也就是说enrolls 里写了 course 730 配 section 1那 section 表里 section 1 的 course_id 就应该是 730。否则 JOIN 出来全是空结果你还会以为是 SQL 写错了。这个坑在后面的查询题里反复出现提前改掉能省很多排查时间。4. 检索查询的实现条件过滤、关联方式和统计分组4.1 系名检索和电话号码过滤LIKE 与 NOT LIKE 的边界条件原题第 4 题和第 6 题放在一起最合适因为它们都涉及过滤条件的不同写法。第 4 题「检索系名为 Math 和 English 的课程表信息」很多人会写成两个 SELECT 拼结果实际用 OR 就够。第 6 题「检索电话号码不是以 257 打头的教师」这里的关键是 NOT LIKE 257-% 会同时排除 NULL 值除非你明确用 IS NULL 兜底。-- 第 4 题系名过滤 SELECT * FROM courses WHERE department Math OR department English; -- 第 6 题NOT LIKE 的边界情况 SELECT teacher_name, phone FROM teachers WHERE phone NOT LIKE 257-%; -- 更严谨的写法如果想包含电话为空的老师 SELECT teacher_name, phone FROM teachers WHERE phone NOT LIKE 257-% OR phone IS NULL;逻辑说明第 4 题用 OR 等效于 department IN (Math, English)两种写法性能在这个数据量下没差别。第 6 题的 NOT LIKE 是一个经典陷阱——SQL 的三值逻辑里NOT LIKE 遇到 NULL 会返回 UNKNOWN行被过滤掉。原题数据里没有 NULL 电话所以直接写没问题但课设答辩时能说出这个边界印象分会高不少。参数说明LIKE 中的 % 是任意长度通配符下划线 _ 是单字符通配符。查询 257-% 匹配的是「257-」开头的字符串如果电话号码格式变成 2573049没连字符这条 SQL 就查不出来了。所以这类过滤依赖数据格式稳定做课设时最好先观察数据再写模式。4.2 第 9 题和第 10 题EXISTS 嵌套与分组统计的两种典型结构整个 20 题里难度天花板是第 9 题「检索至少选修教师 Dr. Lowe 所开全部课程的学生学号」。这题的逻辑是「学生的选课集合包含 Dr. Lowe 所教课程集合」SQL 里表达集合包含关系最标准的方式是 NOT EXISTS 配合 NOT IN 的双重否定但原题给出的解法是用 EXISTS GROUP BY HAVING COUNT 的写法存在明显问题——它统计的是每个学生的选课数而不是 Dr. Lowe 课程的选修数。-- 第 9 题标准解法双重否定表达集合包含 SELECT DISTINCT e.student_id FROM enrolls e WHERE NOT EXISTS ( -- 找到 Dr. Lowe 教的课程 SELECT 1 FROM section s JOIN teachers t ON s.teacher_id t.teacher_id WHERE t.teacher_name Dr. Lowe AND NOT EXISTS ( -- 该学生是否没选这门课 SELECT 1 FROM enrolls e2 WHERE e2.student_id e.student_id AND e2.course_id s.course_id ) ); -- 第 10 题每门课登记人数 SELECT c.course_id, c.course_name, s.section_id, s.num_students FROM courses c JOIN section s ON c.course_id s.course_id ORDER BY c.course_id;逻辑说明双重否定是关系除法的标准实现。外层 NOT EXISTS 遍历每个学生内层 NOT EXISTS 检查「Dr. Lowe 教的课程中是否有该学生没选的」只有当内层为空全部选了时外层才保留这个学生。原题答案先 GROUP BY student 再数总数那只能筛出选课数达标的人不能验证「包含 Dr. Lowe 的全部课程」——这两个结果碰巧一致是因为样本里课程总数不多但逻辑上是不严谨的。参数说明第 10 题直接查 section 表的 num_students 就能得到登记人数但如果要「统计」而不是「查询已有字段」应该用 COUNT 聚合 enrolls 表。后面第 18 题会用到真正的统计写法这里先把两者的区别点出来。4.3 第 12 题和第 13 题多表 JOIN 的写法差异第 12 题「只有男生选修的课程」第 13 题「列出所有学生选修的课程名、学生名、授课教师名、该生成绩」两个题都涉及三表以上 JOIN但第 12 题的逻辑更绕。先看第 13 题的标准多表连接再看第 12 题。-- 第 13 题学生-课程-教师-成绩 四表连接 SELECT DISTINCT c.course_name, st.student_name, t.teacher_name, e.grade FROM students st JOIN enrolls e ON st.student_id e.student_id JOIN courses c ON e.course_id c.course_id JOIN section s ON e.section_id s.section_id JOIN teachers t ON s.teacher_id t.teacher_id ORDER BY c.course_name, st.student_name; -- 第 12 题只有男生选修的课程用 NOT EXISTS 排除女生选过的课 SELECT c.course_name, st.student_name FROM courses c JOIN enrolls e1 ON c.course_id e1.course_id JOIN students st ON e1.student_id st.student_id WHERE st.sex M AND NOT EXISTS ( SELECT 1 FROM enrolls e2 JOIN students s2 ON e2.student_id s2.student_id WHERE e2.course_id c.course_id AND s2.sex F );逻辑说明第 13 题要注意 enrolls 和 section 的 JOIN 条件——必须先 JOIN section 拿到 teacher_id再 JOIN teachers 拿教师名。原题答案里直接 teacher.teacher section.teacher 的方式也行但用 JOIN 语法更清晰。第 12 题的核心是「存在女生选过就排除」NOT EXISTS 子查询里查的是「有没有女生选过这门课」外层再限定学生性别为 M两个条件叠加才能得到「只有男生」的课程。参数说明第 12 题如果改用 NOT IN 写法也能实现但 NOT EXISTS 在大数据量下通常性能更好而且语义更直观。这里的数据量下性能差异可以忽略但课设报告里建议写 NOT EXISTS 并说明理由。5. 避坑复盘COUNT 误用、隐式笛卡尔积和 DELETE 的陷阱5.1 第 11 题「选修两门以上课程」的经典错误COUNT 用错位置这道题犯错的概率极高错法还很统一——把 COUNT(*) 放在没有 GROUP BY 的查询里或者 GROUP BY 的字段不对。原题题目给出的写法是SELECT studentname FROM students, enrolls WHERE ... GROUP BY studentname HAVING COUNT(*) 2这个思路是对的但如果你把 COUNT 换成 COUNT(course)遇到一门课多次登记的情况就会出错。-- 正确写法GROUP BY 学号 HAVING 过滤 SELECT st.student_name FROM students st JOIN enrolls e ON st.student_id e.student_id GROUP BY st.student_id, st.student_name HAVING COUNT(DISTINCT e.course_id) 2; -- 错误写法少 DISTINCT 会导致同一课程多个分组被重复计数 SELECT st.student_name FROM students st JOIN enrolls e ON st.student_id e.student_id GROUP BY st.student_id, st.student_name HAVING COUNT(e.course_id) 2;现象同一学生选了同一门课的两个不同分组错误写法下被算成两门课结果多出选课数量。原因enrolls 表的粒度是「学生-课程-分组」同一课程可以有多个分组记录。解决统计课程数时用 COUNT(DISTINCT course_id)只对课程维度去重。5.2 多表 JOIN 漏写条件造成的笛卡尔积幻象现象写第 13 题四表连接时查询结果的行数远超预期甚至出现一个学生名下重复好几行。原因JOIN 条件漏写或写错比如 enrolls 和 section 的关联条件误用 course_id 而不是 section_id导致两张表做笛卡尔积。解决先分别SELECT COUNT(*)检查每张表的行数再一条条加 JOIN每加一个就核对结果行数变化。-- 排查技巧分步验证每步 JOIN 的行数 SELECT COUNT(*) FROM enrolls; -- 初始行数 SELECT COUNT(*) FROM enrolls e JOIN section s ON e.section_id s.section_id; -- 加 JOIN 后行数 -- 如果第二步行数比第一步多说明 section_id 有重复或 JOIN 条件不对这里给一个经验我做这类多表查询时习惯先算出每张表的行数写在草稿上每写完一层 JOIN 就核对一次。JOIN 后行数变大基本就是一对多匹配出了岔子而 DISTINCT 只能掩盖现象掩盖不了问题。5.3 DELETE 关联删除的连环坑外键约束和子查询引用现象第 15 题「删去名为 Joe Adams 的所有记录」很多人的做法是先 DELETE FROM students WHERE student_name Joe Adams再 DELETE FROM enrolls结果第一步就报外键约束错误。原因students 表被 enrolls 表外键引用必须先删子表再删父表。解决调整删除顺序或者用级联删除。-- 正确顺序先删 enrolls 再删 students DELETE FROM enrolls WHERE student_id (SELECT student_id FROM students WHERE student_name Joe Adams); DELETE FROM students WHERE student_name Joe Adams; -- 如果建表时加了 ON DELETE CASCADE可以只删 students 一行 -- 但课设里不建议因为显式两步删除更符合「可控」的原则现象有人写DELETE FROM enrolls WHERE student_id (SELECT ...)报错「You cant specify target table for update in FROM clause」。原因MySQL 不允许在同一语句的 FROM 子查询里直接引用目标表。解决把子查询包装一层用 SELECT * FROM (SELECT ...) 作为中间表MySQL 会把它当作临时表而不是直接引用目标表。-- MySQL 兼容解法包装一层临时表 DELETE FROM enrolls WHERE student_id ( SELECT student_id FROM ( SELECT student_id FROM students WHERE student_name Joe Adams ) AS tmp );5.4 LIKE 匹配在中文和英文环境下的行为差异现象第 16 题「把教师 Scango 的编号改为 666」原题写法是WHERE teacher_name LIKE %Scango%但如果数据里教师名是「Dr. Scango」直接LIKE Scango是匹配不到的。原因LIKE 不带通配符等价于精确匹配%Scango% 才是模糊匹配。解决用 LIKE %Scango% 或直接WHERE teacher_name Dr. Scango。-- 第 16 题的严谨写法先查再改 SELECT teacher_id, teacher_name FROM teachers WHERE teacher_name LIKE %Scango%; UPDATE teachers SET teacher_id 666 WHERE teacher_name LIKE %Scango%;这条的教训是写 UPDATE 和 DELETE 之前永远先用同名 SELECT 跑一遍确认影响的行数符合预期。我从那次在课设里误改了三行记录之后就养成了「先 SELECT 再 UPDATE」的强制习惯。6. 数据维护与报表输出UPDATE、统计聚合和文本拼接技巧6.1 第 17 题和第 18 题平均分和选课人数的聚合差异最后这几个题都是统计报表类难度不大但写法上有讲究。第 17 题「统计教师 Engle 教的英语课的学生平均分」这题有两层过滤课程是 English Composition教师是 Engle。第 18 题「统计各门课程的选课人数」这题要区分「课程」和「分组」——一门课可能有多个分组。-- 第 17 题AVG 与多表 JOIN SELECT AVG(e.grade) AS avg_grade FROM enrolls e JOIN courses c ON e.course_id c.course_id JOIN section s ON e.section_id s.section_id JOIN teachers t ON s.teacher_id t.teacher_id WHERE c.course_name English Composition AND t.teacher_name LIKE %Engle%; -- 第 18 题统计选课人数以 enrolls 实际登记为准 SELECT c.course_name, COUNT(*) AS enroll_count FROM enrolls e JOIN courses c ON e.course_id c.course_id GROUP BY c.course_id, c.course_name ORDER BY enroll_count DESC;逻辑说明第 17 题的 JOIN 链路是 enrolls → courses取课程名→ section取教师编号→ teachers取教师名四张表串起来后 AVG(grade)。第 18 题的输出和原题附表 8 里的 num_students 可能不一致这是正常的——num_students 是分组预设的人数enrolls 才是实际登记数课设里考试看的是后者。参数说明第 18 题如果题目要求「统计」标准做法是按实际登记数据 COUNT而不是直接查 section.num_students 字段。表里预设的 num_students 和实际登记数据存在出入是常态碰到这题不要被附表 8 带偏。6.2 第 19 题和第 20 题的报表输出用简单工具生成可提交的对齐表格最后两题要求输出报表格式。在 MySQL 命令行里直接执行 SELECT 得到的对齐效果其实已经能交差但更专业的做法是输出成文件再转表格。我一般用两种方案命令行导出纯文本或用 UNION 拼一段 ASCII 表。这里推荐一个特别实用的做法——用 SQL 直接拼接出 Markdown 表格保存后粘贴到课设报告里。-- 第 20 题学生名-课程名-教师名-成绩 报表输出纯文本对齐 SELECT student_name AS 学生名, course_name AS 课程名, teacher_name AS 教师名, grade AS 成绩 FROM students st JOIN enrolls e ON st.student_id e.student_id JOIN courses c ON e.course_id c.course_id JOIN section s ON e.section_id s.section_id JOIN teachers t ON s.teacher_id t.teacher_id ORDER BY st.student_id; -- 如果想输出成 Markdown 表格用 CONCAT 拼接表头和数据行 SELECT CONCAT(| , student_name, | , course_name, | , teacher_name, | , grade, |) FROM students st JOIN enrolls e ON st.student_id e.student_id JOIN courses c ON e.course_id c.course_id JOIN section s ON e.section_id s.section_id JOIN teachers t ON s.teacher_id t.teacher_id ORDER BY st.student_id;逻辑说明CONCAT 拼接输出的每一行都是 Markdown 表格格式配合表头| 学生名 | 课程名 | 教师名 | 成绩 |和分隔行|---|---|---|---|粘到报告里直接就是规整表格。这个方法全程不需要额外工具比截图命令行的对齐结果干净得多。参数说明如果字段值里有竖线符号电话、地址可能有Markdown 表格会被截断所以只适合课程名、人名这类干净字段。地址和电话要用这个技巧的话需要加转义我一般只在报表类字段上用它。6.3 收尾把 20 个操作串成一套可验证的测试流程这套课设材料最值钱的部分是那 20 个操作可以做成一份「验收清单」——每个操作对应一个编号、一条 SQL、一个预期结果。我拿到任何数据库课设的第一件事就是先按这个清单把 SQL 全部跑通确认结果和原题给出的输出一致再开始写报告。这样做的好处是答辩时老师随便指一道题你都能秒切到对应 SQL 说清楚逻辑。从那次做完这个课设以后我给自己定了一个习惯凡是涉及 DELETE 和 UPDATE 的操作先SELECT *跑一遍看影响范围凡是涉及多表 JOIN 的查询先数清楚每张表的行数再动手凡是统计类的题目先确认按什么维度去重。这套流程后来帮我躲过了不少生产环境的数据事故。希望帮到你。本文还有配套的精品资源点击获取