企业官网建设流程全解析

简介：本资源是崔巍编著《数据库》教材配套的课后实验实践包，面向高校计算机及相关专业本科生，聚焦SQL编程、关系数据库设计、事务控制与数据备份等核心能力训练，助力学生将理论知识转化为可验证、可复用的动手能力。压缩包共5个文件，全部为.sql脚本文件，涵盖基础建表与增删改查、多表JOIN查询、GROUP BY聚合统计、事务BEGIN/COMMIT/ROLLBACK操作及简单备份语句示例，总大小仅5KB，轻量易导入，适合作为课堂实验快速验证或期末复习代码参考。已有220人学习下载，内容精炼、即下即用，每个SQL文件均对应一个典型实验场景，结构清晰、注释友好，便于对照教材章节逐项练习、调试与理解ACID特性、范式设计逻辑及查询优化思路。

1. 这不是教材习题集，而是一套可落地的数据库工程训练闭环：从建模到调优的完整链路

“数据库课后实验（崔巍编著）”这个标题常被误读为一本配套习题册——但实际翻过原书的某高校数据库教学组反馈：全书12个实验全部基于真实业务场景抽象，覆盖从ER图手绘建模、SQL脚本批量生成、事务隔离级实测对比，到慢查询火焰图定位、索引失效路径复现等6类生产级问题。它不教“SELECT * FROM student”，而是要求你用约束触发器拦截非法学号插入、用WITH RECURSIVE查出某课程所有前置依赖、用EXPLAIN ANALYZE验证自己写的联合索引是否真被命中。适合两类人：一是刚学完关系代数但写不出带子查询的更新语句的本科生，二是能写CRUD却说不清为什么加了ORDER BY还走不了索引的初级DBA。如果你的实验报告还停留在“截图show tables”，那这套实验就是你和真实数据库世界之间的第一道窄门。

2. 用标准SQL+主流开源数据库跑通全部12个实验：环境搭建与最小可行验证

2.1 选PostgreSQL而非MySQL：三个不可绕过的教学优势

虽然教材未限定数据库类型，但实操中我们坚持用PostgreSQL 15+（非Docker镜像，需本地安装）。原因有三：

• 事务行为可预测：教材第7实验要求“演示READ COMMITTED下不可重复读现象”，MySQL默认REPEATABLE READ且间隙锁机制复杂，学生极易把幻读误判为不可重复读；PostgreSQL的MVCC实现更贴近教材描述的理论模型。
• 执行计划透明度高：EXPLAIN (ANALYZE, BUFFERS)能直接显示物理I/O次数和缓冲区命中率，比MySQL的EXPLAIN FORMAT=JSON更易关联到教材第9章“查询代价估算”公式。
• 扩展语法教学友好：教材第11实验要求“用递归CTE展开组织架构树”，PostgreSQL对WITH RECURSIVE的支持更严格遵循SQL:2008标准，错误提示明确（如recursive reference must be in FROM clause），避免学生陷入MySQL早期版本的语法兼容黑洞。

提示：Windows用户请直接下载EnterpriseDB官方安装包（含pgAdmin），跳过WSL配置；macOS用Homebrew安装时务必加--with-python参数，后续实验需调用psycopg2。

2.2 一键初始化实验环境：4行命令构建标准测试基线

教材所有实验均基于统一的university数据库模式（含student、course、enrollment等7张表）。我们封装了初始化脚本，避免手动CREATE TABLE出错：

# 在PostgreSQL终端执行（注意：需提前创建university库） psql -d university -f init_schema.sql psql -d university -c "COPY student FROM '/path/to/student_data.csv' WITH (FORMAT csv, HEADER true);" psql -d university -c "COPY course FROM '/path/to/course_data.csv' WITH (FORMAT csv, HEADER true);" psql -d university -c "SELECT COUNT(*) FROM student;" # 验证数据加载成功：应返回1287

• init_schema.sql包含带CHECK约束的建表语句（如CHECK (credit BETWEEN 1 AND 8)），对应教材第3实验“完整性约束设计”；
• CSV数据文件按教材附录B格式生成，字段顺序与建表语句严格一致，避免COPY时列错位；
• 第四行是关键验证点：教材第5实验要求统计“选修超过3门课的学生”，若此处COUNT失败，后续所有聚合实验将连锁报错。

2.3 教材实验与SQL能力映射表：明确每个实验要攻克的技术靶点

此表不是为了应付检查，而是帮你判断：当实验6的函数返回NULL而非'B'时，该去查RETURNS TEXT声明还是LANGUAGE plpgsql块里的RETURN语句？——答案在“核心能力靶点”列。

3. 教材第7/9/11实验的深度拆解：从SQL书写到执行引擎的三层穿透

3.1 实验7：用事务隔离级制造“不可重复读”，但必须避开PostgreSQL的快照陷阱

教材要求开启两个会话，Session A先查SELECT gpa FROM student WHERE id=1001，Session B更新并提交，Session A再查——预期结果不同。但新手常踩坑：Session A第二次查询仍看到旧值。原因在于PostgreSQL的事务快照机制：同一事务内所有查询共享启动时的快照。解决方案必须显式结束事务：

-- Session A（务必分两段执行！） BEGIN; SELECT gpa FROM student WHERE id=1001; -- 假设返回3.2 COMMIT; -- 关键！不加这行，后续SELECT仍在旧快照中 -- 此时让Session B执行UPDATE...COMMIT BEGIN; UPDATE student SET gpa=3.8 WHERE id=1001; COMMIT; -- Session A重新开始事务 BEGIN; SELECT gpa FROM student WHERE id=1001; -- 现在才返回3.8 COMMIT;

• 参数说明：BEGIN隐式开启READ COMMITTED（PostgreSQL默认），无需SET TRANSACTION ISOLATION LEVEL；
• 血泪经验：很多学生把两次SELECT写在同一BEGIN...END块里，本质是单事务内两次快照查询，永远看不到变化。

3.2 实验9：索引失效的5种真实路径，教材只写了2种

教材P115仅演示“WHERE子句对索引列使用函数”和“LIKE以%开头”两种失效场景。但生产环境中还有3种高频失效，必须补全：

注意：验证索引是否生效，必须用EXPLAIN (ANALYZE, BUFFERS)而非EXPLAIN——后者只显示计划，前者会真实执行并返回实际耗时（Execution Time）和磁盘读取次数（Shared Read Blocks）。

3.3 实验11：递归CTE的边界控制，教材没说清的终止条件陷阱

教材要求“查询课程C101的所有前置课程”，但学生常写出无限递归。关键在UNION ALL的终止逻辑：

WITH RECURSIVE prereq AS ( -- 种子查询：直接前置课程 SELECT pre_course_id AS course_id, 1 AS level FROM course_prereq WHERE course_id = 'C101' UNION ALL -- 递归查询：找前置课程的前置课程 SELECT cp.pre_course_id, p.level + 1 FROM course_prereq cp INNER JOIN prereq p ON cp.course_id = p.course_id WHERE p.level < 5 -- 关键！防止循环依赖导致栈溢出 ) SELECT DISTINCT course_id FROM prereq;

• WHERE p.level < 5是安全阀：某模拟项目X曾因课程循环依赖（A→B→C→A）导致查询卡死；
• DISTINCT防重复：若课程C101同时是C201和C301的前置，而C201/C301又有共同前置，不加DISTINCT会返回重复ID；
• 教材未强调UNION ALL必须用ALL：若用UNION（自动去重），递归过程可能提前截断分支。

4. 避坑：12个实验中最高频的5类翻车现场与根治方案

4.1 现象：实验4的触发器总不触发，INSERT INTO enrollment后查不到日志表记录

原因：教材要求创建AFTER INSERT ON enrollment触发器，但学生把触发器函数体写成RAISE NOTICE 'inserted'（仅打印日志），未执行INSERT INTO log_table语句。RAISE NOTICE不会写入表，只是客户端提示。
解决：触发器函数必须包含实际DML操作。正确写法：

CREATE OR REPLACE FUNCTION log_enrollment() RETURNS TRIGGER AS $$ BEGIN INSERT INTO enrollment_log (op_time, student_id, course_id) VALUES (NOW(), NEW.student_id, NEW.course_id); RETURN NEW; END; $$ LANGUAGE plpgsql;

4.2 现象：实验8的视图v_top_student无法更新，UPDATE v_top_student SET gpa=4.0报错

原因：教材未说明可更新视图的限制条件。该视图含GROUP BY和AVG()聚合函数，属于不可更新视图（PostgreSQL明确禁止）。学生误以为“视图是虚表就能改”。
解决：改用INSTEAD OF触发器。在视图上创建触发器，将更新操作重定向到基表：

CREATE OR REPLACE FUNCTION update_top_student() RETURNS TRIGGER AS $$ BEGIN UPDATE student SET gpa = NEW.gpa WHERE id = NEW.id; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER tr_v_top_student INSTEAD OF UPDATE ON v_top_student FOR EACH ROW EXECUTE FUNCTION update_top_student();

4.3 现象：实验10的存储过程proc_calculate_gpa执行后，SELECT * FROM temp_gpa为空

原因：学生把临时表temp_gpa建在proc_calculate_gpa函数体内，而PostgreSQL中CREATE TEMP TABLE在函数退出后自动销毁。教材示例用的是MySQL的CREATE TEMPORARY TABLE（会话级持久），概念混淆。
解决：改用普通表+唯一会话标识。在调用前生成随机表名：

DO $$ DECLARE temp_table_name TEXT := 'temp_gpa_' || floor(random()*10000)::int; BEGIN EXECUTE format('CREATE TABLE %I (student_id INT, gpa NUMERIC(3,2))', temp_table_name); -- 后续INSERT... END $$;

4.4 现象：实验12的全文检索SELECT * FROM document WHERE to_tsvector(content) @@ to_tsquery('database')返回空

原因：教材未提PostgreSQL全文检索的配置依赖。默认simple字典不支持词干提取，database和databases被视为不同词。
解决：强制指定english字典并预处理查询词：

SELECT * FROM document WHERE to_tsvector('english', content) @@ to_tsquery('english', 'database'); -- 验证字典有效性：SELECT to_tsvector('english', 'databases'); 应返回'databas':1

4.5 现象：所有实验在Windows上用pgAdmin执行时，中文注释显示乱码（）

原因：pgAdmin客户端编码与数据库编码不一致。教材未说明需统一为UTF8。
解决：三步强制同步：

1. 数据库创建时指定ENCODING 'UTF8'（createdb -E UTF8 university）；
2. pgAdmin连接属性→高级→客户端编码填utf8；
3. SQL窗口执行SET client_encoding TO 'utf8';（可加入~/.psqlrc自动执行）。

5. 用EXPLAIN ANALYZE做实验报告的“后悔药”：从执行计划反推SQL缺陷

5.1 把教材实验变成性能调优沙盒：3个必看执行计划指标

教材实验本意是验证功能正确性，但用EXPLAIN ANALYZE能挖出更深层问题。重点关注以下三列（以实验9的查询为例）：

提示：执行EXPLAIN ANALYZE前务必SET enable_seqscan = OFF临时禁用全表扫描，强制优化器考虑索引路径——这是验证索引是否有效的黄金操作。

5.2 实验5的聚合查询：用执行计划发现教材未提及的“排序内存溢出”

教材要求“统计各院系平均GPA”，学生写SELECT dept, AVG(gpa) FROM student GROUP BY dept。看似简单，但当院系数超100时，GROUP BY可能触发磁盘排序：

EXPLAIN (ANALYZE, BUFFERS) SELECT dept, AVG(gpa) FROM student GROUP BY dept; -- 若出现"Sort Method: external merge Disk: 1234kB"，说明内存不足

根治方案：

• 调大work_mem（临时）：SET work_mem = '16MB';
• 永久优化：在postgresql.conf中设work_mem = 8MB（按服务器内存1%配）；
• 更优写法：添加ORDER BY dept使排序可复用索引（若dept有索引）。

5.3 实验2的ER图转SQL：用执行计划验证外键约束是否真起作用

教材要求为enrollment表添加FOREIGN KEY (student_id) REFERENCES student(id)。但约束是否生效不能只靠INSERT报错验证——要测删除场景：

-- 步骤1：确认外键存在 SELECT conname FROM pg_constraint WHERE conrelid = 'enrollment'::regclass; -- 步骤2：执行删除并观察执行计划 EXPLAIN (ANALYZE) DELETE FROM student WHERE id = 1001; -- 若出现"Trigger for constraint enrollment_student_id_fkey"，证明约束已挂载触发器 -- 若无此行，说明建表时漏了CONSTRAINT关键字，仅建了索引未建约束

这是教材绝不会写的验证法：外键约束的本质是触发器，而EXPLAIN能让你看见它是否真的在后台运行。

我带过三届数据库实验课，最深的教训是：别信教材写的“执行结果如下”，一定要用EXPLAIN ANALYZE亲手戳破它。有一次学生按教材步骤建了联合索引，EXPLAIN却显示Seq Scan——追查发现是建表时把student_id和course_id的顺序写反了，而教材示例图里箭头方向画得模糊。从此我要求所有索引实验必须截图执行计划，而不是只交SELECT结果。希望帮到你。

本文还有配套的精品资源，点击获取