简介这份Word文档面向计算机专业学生与数据库课程设计者系统讲解数据库类在线学习系统的数据库设计全过程可帮助读者完成课程设计、毕业设计或自学数据库建模。资源为单个doc文件压缩包约625KB内容完整、结构清晰便于直接参考与修改。文档从系统功能需求分析入手将系统划分为在线学习、在线交流、在线测试和后台管理四大模块并给出各模块的结构功能图随后进入概念结构设计梳理教师、学生、公告、教程、试题、成绩、帖子七个实体及其E-R关系再通过逻辑结构设计将E-R图转化为关系模型并列出tb_teacher、tb_bulletin、tb_course、tb_tiezi、tb_exam、tb_student、tb_result等数据表的字段、类型与约束说明。已有71人学习下载适合需要完整数据库设计范例、E-R图与建表依据的读者参考。1. 数据库类在线学习系统从一份课程设计文档到能跑的后端带过几届数据库课程设计每年都有学生拿着“在线学习系统”这个题目来找我最后交上来的东西两极分化严重一类是几张 Excel 截图拼出来的 E-R 图另一类是直接套一个开源 CMS 改个名字。真正把“数据库设计”这件事做扎实的少。这份《数据库类在线学习系统》的文档核心价值不在界面多花哨而在于它逼你把用户、课程、选课、学习进度、测验这几张表的关系想清楚——这恰好是数据库设计里最典型的“多对多 状态流转”场景。如果你正在做数据库课程设计、毕业设计或者想拿一个真实业务练手 MySQL 建表与查询这篇笔记就是按我实际带项目的路径拆的先定实体和关系再落 SQL再谈索引和并发最后给一套能自查的验证方法。全程围绕数据库设计本身不堆前端花架子。2. 先把实体和关系钉死在线学习系统的表结构怎么定2.1 从业务动作反推实体而不是先画 E-R 图很多人一上来就打开画图工具画矩形画完发现字段对不上。我的习惯是先把系统里“谁对谁做了什么”列成句子再从句子里抠实体。在线学习系统的核心动作无非这几条学生注册账号、教师发布课程、学生选课、学生看课时记录进度、学生做测验、教师看统计。把这些句子里的名词圈出来去重后就是候选实体用户学生和教师共用一张表加角色字段、课程、章节、课时、选课记录、学习进度、测验、题目、答题记录。这里有个关键判断学生和教师要不要分两张表常见做法是合成一张user表用role字段区分。理由是登录、权限、外键引用都统一避免两套认证逻辑。代价是教师特有的字段比如职称和学生特有的字段比如班级会有一堆 NULL如果这类专属字段超过五六个再考虑拆子表。关系上学生和课程是多对多必须有一张中间表enrollment而且这张表不能只放两个外键——选课时间、选课状态在学/退课/结业都要挂上去否则后面统计“本月新增选课”就没数据。课程和章节是一对多章节和课时是一对多这是一条典型的层级链。学习进度不要塞进enrollment因为进度是“学生对某个课时”的粒度比选课更细单独一张learning_progress表用(user_id, lesson_id)做联合唯一键。2.2 一份可直接执行的建表 SQL下面这套 DDL 是我在 MySQL 8 上跑通的精简版覆盖核心八张表。字符集统一utf8mb4引擎 InnoDB主键用自增 BIGINT时间字段用DATETIME而不是TIMESTAMP避免 2038 问题也方便跨时区处理。-- 用户表学生与教师共用role 区分 CREATE TABLE user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, username VARCHAR(50) NOT NULL COMMENT 登录名唯一, password CHAR(60) NOT NULL COMMENT bcrypt 哈希固定60字符, role TINYINT NOT NULL DEFAULT 0 COMMENT 0学生 1教师 2管理员, nickname VARCHAR(50) DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_username (username) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 课程表teacher_id 指向 user CREATE TABLE course ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, teacher_id BIGINT UNSIGNED NOT NULL, title VARCHAR(120) NOT NULL, description TEXT, status TINYINT NOT NULL DEFAULT 0 COMMENT 0草稿 1已发布 2下架, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_teacher (teacher_id), CONSTRAINT fk_course_teacher FOREIGN KEY (teacher_id) REFERENCES user(id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 章节表 CREATE TABLE chapter ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, course_id BIGINT UNSIGNED NOT NULL, title VARCHAR(120) NOT NULL, sort_no INT NOT NULL DEFAULT 0 COMMENT 章节排序, PRIMARY KEY (id), KEY idx_course_sort (course_id, sort_no), CONSTRAINT fk_chapter_course FOREIGN KEY (course_id) REFERENCES course(id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 课时表 CREATE TABLE lesson ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, chapter_id BIGINT UNSIGNED NOT NULL, title VARCHAR(120) NOT NULL, video_url VARCHAR(255) DEFAULT NULL, duration INT NOT NULL DEFAULT 0 COMMENT 秒, sort_no INT NOT NULL DEFAULT 0, PRIMARY KEY (id), KEY idx_chapter_sort (chapter_id, sort_no), CONSTRAINT fk_lesson_chapter FOREIGN KEY (chapter_id) REFERENCES chapter(id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 选课表多对多中间表带状态 CREATE TABLE enrollment ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, course_id BIGINT UNSIGNED NOT NULL, status TINYINT NOT NULL DEFAULT 0 COMMENT 0在学 1结业 2退课, enrolled_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_user_course (user_id, course_id), KEY idx_course_status (course_id, status), CONSTRAINT fk_enroll_user FOREIGN KEY (user_id) REFERENCES user(id), CONSTRAINT fk_enroll_course FOREIGN KEY (course_id) REFERENCES course(id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 学习进度表学生对课时粒度 CREATE TABLE learning_progress ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, lesson_id BIGINT UNSIGNED NOT NULL, watched_sec INT NOT NULL DEFAULT 0 COMMENT 已观看秒数, finished TINYINT NOT NULL DEFAULT 0, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_user_lesson (user_id, lesson_id), CONSTRAINT fk_prog_user FOREIGN KEY (user_id) REFERENCES user(id), CONSTRAINT fk_prog_lesson FOREIGN KEY (lesson_id) REFERENCES lesson(id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 测验表 CREATE TABLE quiz ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, lesson_id BIGINT UNSIGNED NOT NULL, title VARCHAR(120) NOT NULL, pass_score INT NOT NULL DEFAULT 60, PRIMARY KEY (id), KEY idx_lesson (lesson_id), CONSTRAINT fk_quiz_lesson FOREIGN KEY (lesson_id) REFERENCES lesson(id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 答题记录表 CREATE TABLE quiz_attempt ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, quiz_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, score INT NOT NULL DEFAULT 0, attempted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_quiz_user (quiz_id, user_id), CONSTRAINT fk_attempt_quiz FOREIGN KEY (quiz_id) REFERENCES quiz(id), CONSTRAINT fk_attempt_user FOREIGN KEY (user_id) REFERENCES user(id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;逻辑说明enrollment上的uk_user_course保证一个学生不会重复选同一门课这是业务底线靠应用层判断容易在并发下失效交给数据库唯一索引最稳。learning_progress的uk_user_lesson同理进度上报是高频写操作用INSERT ... ON DUPLICATE KEY UPDATE一条语句搞定避免先查后写的竞态。idx_course_status是为“查某课程的在学人数”这类统计准备的复合索引把过滤条件按区分度从高到低排。参数说明password用CHAR(60)是因为 bcrypt 输出固定 60 字符用VARCHAR(255)会浪费且失去约束意义。duration、watched_sec用 INT 存秒数别用浮点存分钟浮点比较和累加会有精度坑。所有外键都显式命名fk_前缀方便后续用ALTER TABLE ... DROP FOREIGN KEY精确操作不然 MySQL 自动生成的名字很难记。3. 增删改查落地几个高频查询怎么写才不拖垮库3.1 课程详情页那条“章节课时”的嵌套查询课程详情要一次性把章节和课时按顺序拉出来。新手容易写成先查章节、再循环查课时N1 查询在课时多的时候直接拖慢接口。正确做法是一条 JOIN 查出来在应用层组装成树。SELECT c.id AS chapter_id, c.title AS chapter_title, c.sort_no AS c_sort, l.id AS lesson_id, l.title AS lesson_title, l.duration, l.sort_no AS l_sort FROM chapter c LEFT JOIN lesson l ON l.chapter_id c.id WHERE c.course_id ? ORDER BY c.sort_no ASC, l.sort_no ASC;逻辑说明用LEFT JOIN而不是INNER JOIN是为了让“有章节但还没加课时”的课程也能正常显示章节标题否则该章节会整条消失。排序在 SQL 层做完应用层只负责按chapter_id分组不要在两处都排序容易乱。参数就是课程 ID走idx_course_sort索引。3.2 学习进度上报一条语句解决插入或更新进度上报是最高频的写操作学生看视频时可能每十几秒上报一次。用下面这条INSERT INTO learning_progress (user_id, lesson_id, watched_sec, finished) VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE watched_sec GREATEST(watched_sec, VALUES(watched_sec)), finished GREATEST(finished, VALUES(finished));逻辑说明ON DUPLICATE KEY UPDATE命中uk_user_lesson唯一键时转为更新。GREATEST是关键——防止网络乱序导致旧的低进度覆盖新的高进度这是实际项目里踩过的坑用户拖进度条回看时上报的秒数会变小直接覆盖就把进度弄丢了。VALUES()在 MySQL 8.0.20 后标记为废弃新写法是给 VALUES 起别名但为兼容老版本这里保留经典写法。参数说明finished用GREATEST保证一旦标记完成就不会被回退成未完成。如果业务允许重看那watched_sec的语义要改成“最大观看位置”而不是“累计观看时长”两者统计口径完全不同建表时就要想清楚。3.3 统计类查询某课程各状态选课人数教师后台常要看选课分布用GROUP BY一次出结果SELECT status, COUNT(*) AS cnt FROM enrollment WHERE course_id ? GROUP BY status;逻辑说明走idx_course_status索引course_id等值过滤后status直接可用于分组避免回表扫全表。如果还要算结业率在应用层用cnt相除即可不要塞进同一条 SQL 里做子查询可读性差且优化器不一定领情。4. 索引、并发与死锁在线学习系统最容易翻车的地方4.1 索引不是越多越好写多读少的表要克制learning_progress是典型的写密集表每次上报都触发一次写。除了uk_user_lesson这个必须的唯一索引不要再加额外索引。有人为了“按更新时间查最近学习”加一个updated_at索引结果每次更新都要维护这个索引写入放大明显。真要做“最近学习”功能用enrollment表的enrolled_at或者单独一张轻量缓存表别在进度表上堆索引。反过来course表读多写少teacher_id、status上的索引可以放心加。判断标准很简单这张表每天写多少次、读多少次写远多于读就砍索引。4.2 选课并发下的重复插入与死锁两个请求同时给同一学生选同一门课如果应用层先SELECT判断再INSERT中间有窗口期两个请求都判断为“未选”然后都去插入第二个撞唯一键报错。正确姿势是直接INSERT捕获唯一键冲突异常MySQL 错误码 1062返回“已选过”。把判断交给数据库这是唯一可靠的做法。死锁更容易出现在批量操作里。比如退课时要同时更新enrollment.status和删除learning_progress两个事务如果加锁顺序相反就会死锁。我的习惯是约定所有事务按固定顺序访问表先enrollment后learning_progress全项目统一。另外把事务粒度压到最小别在一个事务里做网络调用或循环几十次写。4.3 用 EXPLAIN 自查慢查询上线前对每条核心查询跑一遍EXPLAIN重点看三列type最好是ref或range出现ALL就是全表扫key要显示实际用到的索引是NULL说明没走索引rows估算扫描行数超过几千就要警惕。下面这条是自查命令EXPLAIN SELECT status, COUNT(*) FROM enrollment WHERE course_id 1 GROUP BY status;如果key显示idx_course_statustype是ref基本没问题。如果显示ALL先检查是不是course_id类型和索引列类型不一致——比如索引是 BIGINT UNSIGNED查询传了字符串MySQL 会做隐式转换导致索引失效这是最常见的“索引明明建了却没走”的原因。5. 避坑与排查数据库设计里那些血泪经验现象选课表数据重复同一学生同一课程出现两条记录。原因应用层用“先查后插”做去重并发下失效或者历史数据迁移时没加唯一约束。解决补上uk_user_course唯一索引清理重复数据时保留id最小的一条其余删除。清理前先备份别直接 DELETE。现象进度上报偶尔把已完成的课时改回未完成。原因更新语句直接赋值finished VALUES(finished)旧请求乱序到达覆盖了新值。解决改用GREATEST单调递增或者加版本号做乐观锁。这个坑在移动端弱网环境下特别容易复现。现象删除课程时外键报错删不掉。原因chapter、enrollment等表还引用着该课程。解决要么按依赖顺序先删子表再删主表要么在建外键时定义ON DELETE CASCADE。但级联删除要慎用误删一门课连带删掉所有学习记录没有后悔药。我的做法是课程用status软删除置为下架物理删除只留给管理员且走单独脚本。现象查询课程列表越来越慢数据才几万条。原因course表按title做了模糊查询LIKE %关键词%前置通配符让索引完全失效。解决改成前缀匹配LIKE 关键词%能走索引如果必须全文检索上全文索引或者专门的检索方案别在 MySQL 里硬扛。现象时间字段存进去和查出来差 8 小时。原因连接串没指定时区或者DATETIME与TIMESTAMP混用。解决统一用DATETIME连接参数显式设置serverTimezone应用层统一按 UTC 存储、按用户时区展示。这个玄学问题排查起来能耗掉半天。6. 进阶用视图和存储过程把统计逻辑收口课程设计做到后面教师端统计需求会越来越多某课程平均完成率、某学生所有课程的学习时长汇总、每门课的测验通过率。如果每个统计都在应用层拼 SQL代码会散得到处都是。我的习惯是把稳定的统计逻辑做成视图把带参数的复杂统计做成存储过程让数据库承担它擅长的聚合。先看一个视图统计每门课程的选课人数和结业人数CREATE OR REPLACE VIEW v_course_stats AS SELECT c.id AS course_id, c.title, COUNT(e.id) AS enroll_cnt, SUM(CASE WHEN e.status 1 THEN 1 ELSE 0 END) AS graduate_cnt, ROUND(SUM(CASE WHEN e.status 1 THEN 1 ELSE 0 END) / NULLIF(COUNT(e.id), 0) * 100, 2) AS graduate_rate FROM course c LEFT JOIN enrollment e ON e.course_id c.id GROUP BY c.id, c.title;逻辑说明用LEFT JOIN保证零选课的课程也出现在结果里NULLIF防止除零。graduate_rate直接算成百分比保留两位应用层拿来即用。视图的好处是统计口径统一改一处全项目生效代价是每次查询都实时计算数据量大时要考虑物化或定时任务落表。再看一个带参数的存储过程查某学生在某课程下的学习进度百分比DELIMITER $$ CREATE PROCEDURE sp_student_progress(IN p_user BIGINT, IN p_course BIGINT) BEGIN SELECT COUNT(l.id) AS total_lessons, SUM(CASE WHEN lp.finished 1 THEN 1 ELSE 0 END) AS finished_lessons, ROUND(SUM(CASE WHEN lp.finished 1 THEN 1 ELSE 0 END) / NULLIF(COUNT(l.id), 0) * 100, 2) AS progress_pct FROM chapter c JOIN lesson l ON l.chapter_id c.id LEFT JOIN learning_progress lp ON lp.lesson_id l.id AND lp.user_id p_user WHERE c.course_id p_course; END$$ DELIMITER ;逻辑说明LEFT JOIN的条件里带上lp.user_id p_user这样没学过的课时也会计入total_lessons只是finished为 NULL被CASE判为未完成。如果把user_id条件放到WHERE里LEFT JOIN会退化成INNER JOIN没学过的课时直接消失进度永远算不对——这是写进度统计时最隐蔽的坑。调用方式CALL sp_student_progress(1001, 5);参数分别是用户 ID 和课程 ID。存储过程的争议一直有业务逻辑放数据库不利于版本管理和测试但对于课程设计这种规模、且统计口径固定的场景它能把 SQL 收口减少应用层拼字符串出错。我的原则是只读统计可以进存储过程写操作一律留在应用层方便事务控制和日志追踪。验证这套设计是否靠谱我一般做三件事一是造一批测试数据用INSERT ... SELECT批量灌几十万条进度记录跑一遍核心查询看响应时间二是用两个终端并发调选课接口确认唯一键能挡住重复三是把EXPLAIN结果存下来作为后续加字段、改索引时的对照基线。数据库设计没有一劳永逸需求一变表结构就得跟着动留好迁移脚本比什么都重要。希望帮到你。本文还有配套的精品资源点击获取