简介面向学习数据库管理系统课程的高校学生与自学者这份资料包围绕悉尼大学 Database Management System 课程的知识框架整理覆盖数据模型、关系代数、SQL查询、事务与并发控制、规范化、索引及查询处理等主题适合课程同步学习、实验练习与考前复习。压缩包共65个文件约17.04MB包含39份PDF讲义与习题解析、7个SQL脚本、4个压缩子包和2份PPT课件兼顾理论阅读、上机操作与课堂演示。已有193人学习下载。PDF可用于对照tutorial与solution梳理概念SQL脚本便于建库建表和数据查询练习压缩子包提供Java/Python数据库应用开发示例资料按周次组织涵盖概念建模、复杂SQL、完整性约束、事务、规范化、存储索引与课程复习并配有课堂练习及解答便于读者按教学进度查漏补缺把关系模型、事务处理和数据库设计等抽象知识落到具体操作中。1. 这套 DBMS 课程资料救的不只是期末考前段时间有个做后端的同学找我说他接手的一套管理系统数据库里同一个学生姓名在五张表里各存一份改一处漏四处删一条记录直接外键报错。我问他建表时怎么想的他说“字段缺了就加没人教过我怎么拆”。后来我把手头这份 Database Management System 课程资料发给他让他先把第三周的 ER 建模和第七周的范式那两讲看完再回头看自己的表他自己就发现问题在哪了。这份资料是海外某高校 DBMS 课程的全套材料每周讲义、tutorial 习题与参考解、两次 assignment 的说明和评分点、配套的建表脚本与样例数据再加一个期末 mini project 的骨架。它解决的不是“考试怎么过”而是“给你一个业务场景你能不能设计出一套不返工的表结构写出不拖垮库的查询”。适合三类人数据库基础薄弱想系统补一遍的、正在做课程设计或毕业设计的、以及写了两三年 CRUD 但从没看过执行计划的。如果你要找现成的 ORM 框架源码这份资料不是那个方向。2. 资料结构与技术栈先把目录摸清再动手拿到一个压缩包最忌讳的就是打开最新那个 PDF 从头读。这门课的资料是分层组织的讲义负责讲概念tutorial 负责把概念变成手写 SQLassignment 负责把 SQL 变成一套完整的设计。三层混着看效率会低很多。2.1 目录骨架讲义、tutorial、assignment 三层怎么分常见做法是按周次 类型两个维度切分实际的目录大致长这样dbms-course/ ├── lectures/ # 每周讲义概念与理论都在这里 │ ├── w01-intro.pdf # 课程导论、DBMS 三层结构 │ ├── w03-er-model.pdf # ER 建模、基数约束 │ ├── w05-relational-algebra.pdf │ ├── w07-normalization.pdf # 函数依赖、1NF~BCNF │ └── w10-transactions.pdf # ACID、隔离级别 ├── tutorials/ # 每周小课题目 参考解 │ ├── t02-relational-algebra.md │ ├── t05-sql-basics.sql │ └── t08-index-and-explain.sql ├── assignments/ # 两次大作业的说明与评分细则 │ ├── a1-er-design/ # 给定业务描述产出 ER 图 关系模式 │ └── a2-sql-tuning/ # 给定 schema写查询并优化 ├── schema/ # 可直接执行的 DDL 与样例数据 │ ├── ddl.sql │ └── seed.sql └── project/ # 期末 mini project 骨架这个结构里最值钱的是schema/和assignments/两个目录。schema/里的 DDL 是老师反复打磨过的字段类型、约束、级联规则都写得很规范可以当模板抄assignments/里的评分细则等于把“什么叫设计得好”量化了比如“实体完整性 10 分、参照完整性 10 分、是否消除了部分依赖 15 分”照着这份细则自查比凭感觉改有效得多。lectures/里如果时间紧优先级是 w03ER 建模→ w07范式→ w10事务→ w05关系代数。关系代数看着抽象但它是后面写查询和理解查询优化的地基别跳。2.2 技术栈与工具链PostgreSQL 还是 MySQL课程示例 SQL 通常是标准 SQL 加少量方言两个主流开源库都能跑但细节上有差别。我一般建议用 PostgreSQL 起步原因是它的EXPLAIN (ANALYZE, BUFFERS)输出信息量大得多讲索引和执行计划的那几讲能看得更透。组件推荐选型说明数据库PostgreSQL 15执行计划详细窗口函数、CTE 支持完整客户端psql / DBeaver / DataGrippsql 适合跑脚本图形客户端适合看表关系建模draw.io / dbdiagram.io画 ER 图导出 PNG 交作业版本管理gitschema 变更一定要留痕后面会讲到用 MySQL 也完全能跑通只是把EXPLAIN (ANALYZE, BUFFERS)换成EXPLAIN ANALYZE把SERIAL换成AUTO_INCREMENT把TEXT上的索引换成前缀索引。方言差异集中在自增主键、字符串类型和窗口函数这三处改的时候对着报错逐个换就行。提示不要一上来就在本地装一堆数据库版本。一门课的资料只需要一个版本跑通多版本并存反而会让你搞不清“这个语法到底是谁不支持”。2.3 十分钟把环境跑起来建库、导数、跑第一个脚本环境准备这一步血泪经验是别用图形界面一路点“新建数据库”用命令行脚本走一遍出问题能看到完整报错。下面这段是我常用的初始化流程# 1. 创建专用数据库不要用默认的 postgres 库方便后面整套删掉重来 createdb -U postgres dbms_course # 2. 灌入建表脚本遇到错误立即中断 psql -U postgres -d dbms_course -v ON_ERROR_STOP1 -f schema/ddl.sql # 3. 导入样例数据 psql -U postgres -d dbms_course -v ON_ERROR_STOP1 -f schema/seed.sql # 4. 进交互终端确认表和数据都在 psql -U postgres -d dbms_course -c \dt psql -U postgres -d dbms_course -c SELECT count(*) FROM student;几个参数值得说清楚-v ON_ERROR_STOP1是关键默认情况下 psql 遇到错误会打印一行然后继续往下执行一个建表失败会导致后面所有外键全部失败但你只看到满屏红字根本不知道源头在哪加上它之后第一条错误就停下定位成本直接降到最低。-f指定文件路径-c执行单条命令后退出适合做快速验证。跑完\dt应该能看到 schema 里定义的全部表。如果表数量对但某张表是空的八成是seed.sql里那条INSERT被外键挡住了这时候先查ddl.sql里表的定义顺序父表要排在子表前面。还有一个容易忽略的点样例数据里的日期、金额字段往往用的是课程所在地的格式。导入前先head -20 schema/seed.sql看一眼如果日期是DD/MM/YYYY而你的库默认是MDY那导入后日期会整体错位而且不报错。这种问题在后面做按月份统计的查询时会突然冒出来非常隐蔽。3. 课程作业的关系建模把 ER 图拆成数据库表这门课最硬核的部分是 assignment 1给你一段三四百字的业务描述让你产出 ER 图、关系模式、建表 SQL 三样东西。很多人卡在“ER 图会画但不知道怎么变成表”。这一章就按这个顺序拆一遍。3.1 从 ER 图到关系模式主键、外键、联系表怎么落地转换规则其实就四条但每条都有坑每个强实体转一张表实体的标识属性做主键。1:N 联系不建表把“1”端的主键塞进“N”端做外键。M:N 联系必须单独建一张联系表两个端的主键组合成联合主键。多值属性单独建表别用逗号分隔塞进一个字段。第三条是最容易漏的。业务描述里写“一个学生可以选多门课一门课可以被多个学生选”这就是典型 M:N必须建enrollment表。我见过不少人图省事在student表里加个courses字段存C01,C02,C03当时能跑等到要查“选了 C01 的所有学生”时只能上LIKE %C01%全表扫描加字符串匹配数据量一上来直接崩这就是典型的翻车现场。第四条也一样。业务描述里出现“一个学生可以有多个联系电话”正确做法是建student_phone(student_id, phone)而不是在student表加phone1、phone2、phone3。后者的结局是某天业务方说“最多可能有五个”你就得改表结构。3.2 函数依赖与范式检查把冗余字段揪出来关系模式出来了不等于结束还得过一遍范式。判断方法很朴素把每个表的函数依赖写出来看有没有“非主属性依赖部分主键”或者“依赖传递”。举个例子enrollment(student_id, course_id, course_name, teacher_id, teacher_name, grade)联合主键是(student_id, course_id)。这里的course_name只依赖course_id主键的一部分这叫部分函数依赖不满足 2NFteacher_name依赖teacher_id而teacher_id又依赖course_id这叫传递依赖不满足 3NF。拆法就是把它拆干净student(student_id, name, major) course(course_id, course_name, teacher_id) teacher(teacher_id, teacher_name, dept) enrollment(student_id, course_id, grade) -- 联合主键无冗余拆完之后course_name只出现在course表里改名改一处这就是范式带来的实际收益。注意范式不是越高越好。BCNF 之后如果还继续拆可能拆出一堆只有两三个字段的小表每次查询要 JOIN 五六张表读性能反而下降。工程上的常见做法是拆到 3NF然后针对高频查询做有限的反范式比如把订单总金额冗余到订单主表里用触发器或应用层保证一致。这点课程讲义里可能会提但作业评分通常只要求到 3NF。3.3 建表脚本逐段拆约束、默认值、级联怎么定关系模式定了落到 DDL 上有几处细节决定这套表好不好维护。下面是从课程资料里提炼出的写法我按段拆开讲-- 学生表主键用业务编号不用自增 ID方便和作业里的样例数据对上 CREATE TABLE student ( student_id VARCHAR(10) PRIMARY KEY, name VARCHAR(50) NOT NULL, major VARCHAR(50), enroll_date DATE NOT NULL DEFAULT CURRENT_DATE ); -- 课程表teacher_id 是外键课程没了老师不能没 CREATE TABLE course ( course_id VARCHAR(10) PRIMARY KEY, course_name VARCHAR(100) NOT NULL, credits SMALLINT NOT NULL CHECK (credits BETWEEN 1 AND 10), teacher_id VARCHAR(10) REFERENCES teacher(teacher_id) ON DELETE SET NULL ); -- 选课表M:N 联系联合主键防重复选课 CREATE TABLE enrollment ( student_id VARCHAR(10) REFERENCES student(student_id) ON DELETE CASCADE, course_id VARCHAR(10) REFERENCES course(course_id) ON DELETE CASCADE, term VARCHAR(10) NOT NULL, grade NUMERIC(5,2) CHECK (grade 0 AND grade 100), PRIMARY KEY (student_id, course_id, term) ); -- 高频查询走这个索引按学期统计成绩 CREATE INDEX idx_enrollment_term ON enrollment (term);逻辑说明student_id用VARCHAR(10)而不是自增整型是因为作业样例数据里给的就是S001这种学号用自增 ID 会导致导数时还要额外做一次映射纯属给自己找事。credits上的CHECK约束是廉价的数据质量保障比在应用层写校验可靠得多因为任何绕过应用的批量导入也会被拦住。enrollment表的主键包含term这是个容易起争议的设计。如果业务上允许重修同一门课那(student_id, course_id)就不能做唯一键必须加上学期如果不允许重修主键去掉term更合适重复选课会直接被数据库拒绝。这个判断要回到业务描述里找别凭习惯拍板。ON DELETE的选择是另一个高频失分点。enrollment用CASCADE因为学生退学后他的选课记录确实应该一起清掉course里的teacher_id用SET NULL因为老师离职了课程还得留着让历史选课记录能查。这两个如果写反了期末演示时删一条数据能把半张表带走那场面相当尴尬。索引部分只建了term一个因为样例查询里WHERE term 2024S1出现频率最高。索引不是越多越好每个索引都会拖慢写入课程作业里建五六个索引的情况很常见评语通常是“未说明建索引依据”。4. SQL 与查询优化从能跑通到跑得快assignment 2 的套路是给一套已经建好的 schema让你写七八条查询最后两条明确要求“优化”。前六条考的是 SQL 基本功后两条考的是你会不会看执行计划。这一章按这个顺序过。4.1 多表连接与聚合tutorial 里出现频率最高的三类写法课程 tutorial 的 SQL 题翻来覆去就三种骨架认出来之后写起来很快。第一种多表内连接加聚合。典型题面是“统计每个学生每学期的平均分只显示平均分及格的”SELECT s.student_id, s.name, e.term, ROUND(AVG(e.grade), 2) AS avg_grade, COUNT(*) AS course_cnt FROM student s JOIN enrollment e ON e.student_id s.student_id WHERE e.grade IS NOT NULL -- 排除未出分的记录否则 AVG 会把 NULL 当0 GROUP BY s.student_id, s.name, e.term HAVING AVG(e.grade) 60 ORDER BY avg_grade DESC;WHERE里那句grade IS NOT NULL是这类题最大的坑。AVG会忽略 NULL但COUNT(*)不会两个指标放在一起时口径必须统一否则算出来的平均分看着没问题门数却是错的。GROUP BY里必须带上s.name因为它在SELECT里且不是聚合函数标准 SQL 不允许省略MySQL 的宽松模式会放过但换到 PostgreSQL 直接报错这种“本地能跑线上报错”的差异最坑人。第二种LEFT JOIN加NULL判断找“没有”。题面是“找出没选过任何课的学生”SELECT s.student_id, s.name FROM student s LEFT JOIN enrollment e ON e.student_id s.student_id WHERE e.student_id IS NULL;这题的常见错误是用NOT IN (SELECT student_id FROM enrollment)。语法没错但只要子查询里有 NULL整个NOT IN就恒为 false结果集直接空掉。用NOT EXISTS或者LEFT JOIN ... IS NULL都能避开这个玄学问题。第三种相关子查询找“每组最大”。题面是“找出每个学生分数最高的那门课”SELECT e.student_id, e.course_id, e.grade FROM enrollment e WHERE e.grade ( SELECT MAX(e2.grade) FROM enrollment e2 WHERE e2.student_id e.student_id );这种写法可读性好但数据量大时性能一般因为子查询要对每行执行一次。优化版用窗口函数ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY grade DESC)一次扫描搞定课程后半段讲到窗口函数时通常会拿这个做对比。4.2 索引与 EXPLAIN看懂执行计划里的三个关键指标优化题的第一步永远是看执行计划不是凭感觉加索引。命令很简单EXPLAIN (ANALYZE, BUFFERS) SELECT s.student_id, s.name, AVG(e.grade) FROM student s JOIN enrollment e ON e.student_id s.student_id WHERE e.term 2024S1 GROUP BY s.student_id, s.name;输出里重点看三样东西。第一是节点类型出现Seq Scan就代表全表扫描小表无所谓几十万行的表就要警惕了。第二是rows估算值和actual rows实际值如果两者差一个数量级说明统计信息过期了跑一次ANALYZE enrollment;就能修正。第三是Buffers: shared hit和readread数值大说明数据没在内存里磁盘 IO 是主要开销。针对上面这条查询WHERE term 2024S1是过滤条件student_id是连接键建一个联合索引能同时覆盖两个用途-- 等值过滤列在前连接列在后覆盖索引把 grade 也带上避免回表 CREATE INDEX idx_enrollment_term_student ON enrollment (term, student_id) INCLUDE (grade);参数顺序不能反。索引(term, student_id)能支持WHERE term ?和WHERE term ? AND student_id ?但支持不了单独WHERE student_id ?。这就是最左前缀原则课程讲义里一般会讲但作业里写反的人不少。INCLUDE (grade)是 PostgreSQL 的特性让索引本身带上grade的值查询时不用再回表读数据页代价是索引体积变大。MySQL 没有INCLUDE只能把grade加进索引列做覆盖索引。还有一种翻车情况加了索引反而更慢。原因是优化器估算“走索引要随机读很多页不如顺序全表扫”于是不用你的索引。遇到这种情况先ANALYZE更新统计信息还不行就检查查询是不是返回了表里大部分行——如果是那全表扫描本来就是更优解别硬加。4.3 事务、隔离级别与锁assignment 里最容易拉开差距的部分课程后半段会讲到并发作业里通常是一道场景题“两个用户同时选同一门课剩余名额怎么保证不超卖”。这题的标准答案是用事务加行锁BEGIN; -- 先锁住课程行再检查余量避免两个事务同时读到还有1个名额 SELECT remaining FROM course WHERE course_id C01 FOR UPDATE; -- 应用层判断 remaining 0 后执行扣减与插入 UPDATE course SET remaining remaining - 1 WHERE course_id C01; INSERT INTO enrollment (student_id, course_id, term) VALUES (S001, C01, 2024S1); COMMIT;FOR UPDATE是关键它给这一行加排他锁第二个事务执行到同一条SELECT时会阻塞直到第一个事务提交。没有它的话两个事务都能读到remaining 1然后各自扣减最后变成-1。隔离级别这块考试爱考“脏读、不可重复读、幻读分别对应哪个级别”。工程上的实际建议是默认用READ COMMITTEDPostgreSQL 和 Oracle 的默认值需要“同一事务内多次读结果一致”时升到REPEATABLE READ。SERIALIZABLE虽然最安全但冲突时会直接报序列化失败应用层必须写重试逻辑课程作业里用不到生产环境也得慎用。提示写完事务记得ROLLBACK或者COMMIT。psql 里手动测事务最容易忘一个没提交的事务会一直持有锁后面所有对该表的操作全部挂起排查半天才发现是自己忘了收尾。5. 常见问题排查跑课程 SQL 的五个翻车现场这些坑基本都是我自己或者带人时真实遇到过的按现象、原因、解决三段写遇到时对号入座就行。5.1 数据导入阶段的两个坑现象一seed.sql导入报invalid input syntax for type date但文件里看着日期都是对的。原因课程样例数据用的是DD/MM/YYYY格式而数据库的DateStyle默认是MDY也就是按MM/DD/YYYY解析。05/03/2024会被当成 5 月 3 日13/03/2024直接报错。更阴险的是那些恰好小于等于 12 的日子不报错但日期全错等你查“三月份的成绩”时才发现数据对不上。解决导入前先SHOW DateStyle;确认然后临时改成本地格式SET DateStyle DMY;或者干脆在 DDL 里把字段写成DATE且在seed.sql里统一用2024-03-05这种 ISO 格式。我一般选后者ISO 格式在任何DateStyle下都不会歧义。现象二外键约束报错violates foreign key constraint但父表数据明明存在。原因seed.sql里的INSERT顺序和表的依赖顺序不一致。比如先插enrollment再插student虽然脚本末尾数据是全的但执行到enrollment那一句时student还是空的。解决调整seed.sql里的插入顺序按“被引用的表在前”排。如果脚本是别人给的不好改可以在导入时临时关闭约束检查SET session_replication_role replica;导完再改回origin。这个命令要超级用户权限本地开发用没问题共享库上别乱用。5.2 查询与事务阶段的三个坑现象三WHERE name 张三查不到数据但表里确实有这条记录。原因中文编码问题。客户端连接编码client_encoding和数据库编码不一致写入时是 UTF-8读取时按 GBK 解读条件自然匹配不上。还有一个可能是字段用了CHAR(n)而不是VARCHAR(n)CHAR会用空格补齐到定长张三存进去变成张三 等值比较时对不上。解决先SHOW client_encoding;和SHOW server_encoding;两者都应该是UTF8。如果不是在连接串或psql启动时显式指定。字段类型上凡是变长文本一律用VARCHAR或TEXTCHAR只用在确实是定长的场景比如性别、国家代码这种。现象四查询加了索引反而变慢EXPLAIN显示还是Seq Scan。原因两种可能。一是表太小几十行的表全表扫描本来就是最优解优化器不傻。二是统计信息过期优化器对数据分布判断错误以为走索引要扫大部分行。解决先SELECT count(*) FROM 表名;确认数据量小表就别纠结索引了。数据量大就执行ANALYZE 表名;刷新统计信息再看执行计划。如果还是不走索引检查索引列上是不是套了函数比如WHERE UPPER(name) ABC会让普通索引失效得建表达式索引CREATE INDEX ON student (UPPER(name));。现象五程序跑到一半卡住不动pg_stat_activity里看到一条idle in transaction状态挂了很久。原因前面某个事务开了没提交可能是代码里BEGIN之后抛异常没走ROLLBACK也可能是手动测的时候忘了收尾。这个事务持有行锁后面所有改同一行的请求全部阻塞。解决查SELECT pid, state, query, state_change FROM pg_stat_activity WHERE state idle in transaction;找到那个 pid用SELECT pg_terminate_backend(pid);干掉。根治办法是把事务包在try/finally里异常路径也强制ROLLBACK再给数据库配一个idle_in_transaction_session_timeout超时自动断开相当于给自己留了颗后悔药。6. 二次开发把课程资料攒成一个能讲的项目课程资料本身是散的讲义归讲义、SQL 归 SQL直接拿去面试讲不出东西。我的做法是拿课程 schema 当底座往上加三层东西把它变成一个完整可运行的小系统。第一层是迁移管理。把schema/ddl.sql拆成编号脚本001_init.sql、002_add_term_to_enrollment.sql配一个极简的迁移执行器Python 或 Node 都行二十行代码读目录、按序执行、记录版本表。这一步的收益是让“改表结构”变成可回滚的操作也顺手解决了前面提到的 schema 变更没留痕的问题。第二层是数据访问层。别一上来就上 ORM先用原生 SQL 参数化查询写五六个典型接口按学期查成绩、按学生查课表、选课带事务和行锁。SQL 直接复用 assignment 2 里调优过的版本参数化能防注入也让执行计划能复用。改造点大致工作量产出物迁移脚本拆分 版本表半天migrations/目录 执行器数据访问层 6 个接口一到两天带事务的查询封装样例数据生成器半天可生成万级测试数据的脚本慢查询记录两小时pg_stat_statements查询面板第三层是数据量验证。课程样例数据通常只有几十行看不出索引的价值。写个脚本批量生成十万条选课记录把 assignment 2 里的查询重跑一遍对比加索引前后的执行时间。这一步做完你在面试里讲“我给(term, student_id)建了联合索引把这条查询从 800ms 降到 30ms”才是有据可依的不是背概念。验证方法很简单每次改完 schema重新跑一遍迁移 导数 全量查询看结果是否和改之前一致索引调整后固定用EXPLAIN (ANALYZE, BUFFERS)记录前后对比把两次输出贴在一起看actual time和Buffers的变化。别凭感觉判断快了还是慢了那种判断十次有三次是错的。从那以后我每次动表结构都强制先把 DDL 落成带编号的迁移脚本再执行哪怕是本地临时库也绝不直接在客户端里手敲ALTER TABLE。这个习惯让我少解释了很多次“这字段什么时候多出来的”。希望这些拆解能帮你把这份课程资料用出课程之外的价值。本文还有配套的精品资源点击获取