简介本资源是面向高校数据库课程学习者的配套实验材料聚焦SQL实践与数据库核心原理巩固适用于课后动手训练、期末复习及课程设计准备。压缩包共5个文件全部为.sql脚本文件总大小仅5KB轻量精炼涵盖基础建表与数据插入、多表JOIN查询、分组聚合统计、事务控制BEGIN/COMMIT/ROLLBACK等典型实验场景每个脚本对应一个独立实验模块结构清晰、即下即用。已有220人下载学习适合作为崔巍《数据库》教材的实操延伸帮助学生将范式设计、ACID事务、备份恢复等理论知识转化为可运行、可验证的代码能力。脚本命名体现递进关系如SQLQuery1至SQLQuery3便于按教学顺序逐步实践同时支持直接导入主流关系型数据库执行无需额外配置。1. 这不是教材习题集而是数据库工程师的「手把手筑基现场」崔巍《数据库课后实验》到底在练什么如果你刚翻开崔巍编著的《数据库课后实验》第一反应可能是“又一本配套习题册”——错了。这本被国内多所高校数据库课程长期选用的实验手册本质是一套高度结构化、强反馈闭环的数据库工程能力训练系统。它不讲“关系代数怎么推导”而要求你亲手在 PostgreSQL 或 SQL Server 上建出带约束的订单库、用事务模拟秒杀超卖、用 EXPLAIN 分析慢查询执行计划、甚至手动构造死锁并观察 WAITING 状态。它的实验设计暗合 DBA 日常真实工作流从 DDL 建模 → DML 数据校验 → 事务边界控制 → 索引调优 → 错误日志溯源。适合三类人计算机专业学生需通过课程实验考核、转行初学者缺真实环境操作经验、以及刚接手遗留数据库维护任务但对底层机制模糊的初级运维。它不教你怎么写论文只逼你敲出能跑通、能抗压、能查错的 SQL。下面我将按一个一线工程师带实习生做实验的真实节奏带你把这本手册变成可落地的技能训练沙盒——所有命令、参数、报错截图级还原连 pgAdmin 里点哪个按钮都写清楚。2. 从零搭起实验环境为什么必须用 PostgreSQL 而非 SQLite 或 MySQL崔巍实验手册虽未强制指定数据库引擎但全书 12 个核心实验含完整性约束、触发器、存储过程、并发控制在 PostgreSQL 上复现成功率最高、报错信息最友好、且与工业界主流 OLTP 场景贴合度最强。MySQL 在外键级联动作、WITH RECURSIVE 语法支持上存在版本碎片SQLite 则完全缺失事务隔离级别控制和真正的并发锁视图。我带过 7 届学生最终统一锁定PostgreSQL 15.4 pgAdmin 4 v7.6组合——这个组合能 100% 复现实验 3参照完整性级联删除、实验 7行级锁与 SELECT FOR UPDATE、实验 10基于时间戳的乐观并发控制。2.1 三步完成最小可用环境Windows / macOS / Linux 通用提示全程离线可完成无需 Docker 或云服务。安装包总大小 280MB实测校园网 2 分钟下载完。# 步骤 1下载官方二进制包以 macOS ARM64 为例 curl -O https://get.enterprisedb.com/postgresql/postgresql-15.4-1-osx-arm64.dmg # Windows 用户请访问 https://www.postgresql.org/download/windows/ 下载 exe 安装器 # Linux 用户推荐使用 aptUbuntu/Debian或 yumCentOS/RHEL避免源码编译 # 步骤 2初始化集群关键手册实验依赖默认配置 initdb -D /usr/local/var/postgres -U postgres -E UTF8 --localeC # 步骤 3启动服务并设为开机自启macOS 示例 pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start # 验证ps aux | grep postgres 应看到 postmaster 进程逻辑说明initdb命令生成的postgres数据库是 PostgreSQL 的模板库所有新创建数据库均从此克隆。崔巍实验中多次要求CREATE DATABASE school;若跳过此步直接pg_ctl start会因缺少基础模板而报database postgres does not exist。参数-U postgres指定超级用户为postgres手册所有实验脚本默认以此用户登录--localeC强制 ASCII 排序避免中文字段排序异常导致实验 5ORDER BY 中文姓名结果与答案不符。2.2 pgAdmin 4 配置要点绕过「连接失败」玄学学生最常卡在 pgAdmin 登录页弹出Unable to connect to server。这不是密码错而是 PostgreSQL 默认禁止远程连接且 pgAdmin 4 v7 改变了认证方式配置文件需修改项手册关联实验postgresql.conflisten_addresses localhostport 5432实验 1连接测试pg_hba.conf新增一行host all all 127.0.0.1/32 md5实验 2用户权限修改后必须执行pg_ctl -D /usr/local/var/postgres reload # 注意是 reload不是 restart参数说明pg_hba.conf中host表示 TCP/IP 连接127.0.0.1/32限定仅本地回环访问安全md5要求密码加密传输。若写成trust虽能连上但实验 2 要求的CREATE USER student WITH PASSWORD 123;将失效——因为trust模式下密码根本不会被校验。2.3 创建实验专用数据库与用户避免权限污染崔巍实验要求严格区分角色postgresDBA、teacher教学管理员、student实验者。必须手动创建而非用默认用户-- 在 psql 中执行注意分号手册实验脚本全带分号 CREATE DATABASE school OWNER postgres; \c school -- 切换到 school 库 CREATE USER teacher WITH PASSWORD t123 NOSUPERUSER; CREATE USER student WITH PASSWORD s123 NOSUPERUSER; GRANT CONNECT ON DATABASE school TO teacher, student; GRANT USAGE ON SCHEMA public TO teacher, student; -- 关键实验 4视图要求 teacher 可查 student 表但不可改此处预埋权限 GRANT SELECT ON ALL TABLES IN SCHEMA public TO teacher; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO teacher;逻辑说明NOSUPERUSER是硬性要求——实验 6存储过程明确禁止student用户执行DROP FUNCTION若赋予SUPERUSER权限后续实验将无法验证权限控制效果。ALTER DEFAULT PRIVILEGES确保后续新建表自动继承SELECT权限否则实验 4 创建视图时teacher会报permission denied for table student_info。3. 实验 3 深度拆解参照完整性不是加个 FOREIGN KEY 就完事崔巍实验 3 标题是「实体完整性与参照完整性实验」但实际考察的是级联行为的精确控制。90% 的学生卡在「删除系部记录时下属教师记录未自动清除」根源在于没理解ON DELETE子句的三种策略在 PostgreSQL 中的触发条件。3.1 建表语句必须显式声明 CASCADE手册易忽略的坑手册给出的建表语句片段CREATE TABLE teacher ( tno CHAR(8) PRIMARY KEY, tname VARCHAR(20) NOT NULL, dno CHAR(4) REFERENCES dept(dno) );这段代码在 PostgreSQL 中不会自动启用级联删除必须显式补全CREATE TABLE teacher ( tno CHAR(8) PRIMARY KEY, tname VARCHAR(20) NOT NULL, dno CHAR(4) REFERENCES dept(dno) ON DELETE CASCADE -- 必加 );逻辑说明SQL 标准中REFERENCES仅表示约束存在ON DELETE是独立子句。PostgreSQL 默认行为是NO ACTION延迟检查即删除dept记录时若存在关联teacher会立即报错update or delete on table dept violates foreign key constraint。CASCADE则触发联动删除SET NULL会将teacher.dno置空需字段允许 NULLRESTRICT等同于默认的NO ACTION。实验要求验证CASCADE效果漏写即失败。3.2 验证级联删除的完整操作链-- 步骤 1插入测试数据严格按手册 P23 表 3-1 数据 INSERT INTO dept VALUES (001, 计算机系); INSERT INTO teacher VALUES (T001, 张三, 001); -- 步骤 2执行删除关键必须用 BEGIN 显式事务包裹 BEGIN; DELETE FROM dept WHERE dno 001; -- 此时 teacher 表应自动清空但尚未提交 -- 步骤 3验证手册要求的检查点 SELECT COUNT(*) FROM teacher; -- 应返回 0 SELECT * FROM dept WHERE dno 001; -- 应返回空 -- 步骤 4回滚并重试其他策略实验对比要求 ROLLBACK; -- 改用 SET NULL需先修改 teacher.dno 允许 NULL ALTER TABLE teacher ALTER COLUMN dno DROP NOT NULL; UPDATE teacher SET dno NULL WHERE tno T001; -- 重建外键 ALTER TABLE teacher DROP CONSTRAINT teacher_dno_fkey; ALTER TABLE teacher ADD CONSTRAINT teacher_dno_fkey FOREIGN KEY (dno) REFERENCES dept(dno) ON DELETE SET NULL;参数说明BEGIN不是可选——实验 3 明确要求「观察事务中约束检查时机」。PostgreSQL 的ON DELETE CASCADE在DELETE语句执行时立即触发但若不在事务中无法回滚验证不同策略。ALTER TABLE ... DROP CONSTRAINT是必须步骤因为 PostgreSQL 不支持ALTER CONSTRAINT ... ON DELETE直接修改。3.3 为什么实验报告要截图 pg_stat_activity手册实验 3 最后一问「级联操作是否产生额外锁如何验证」答案不是查文档而是看实时锁视图-- 在执行 DELETE 期间另一个 psql 窗口运行 SELECT pid, usename, application_name, state, query FROM pg_stat_activity WHERE query LIKE %DELETE FROM dept%; -- 再查锁 SELECT locktype, database, relation::regclass, mode, granted FROM pg_locks WHERE pid IN (SELECT pid FROM pg_stat_activity WHERE query LIKE %DELETE FROM dept%);现象relation列会显示teacher和dept两个表名mode为RowExclusiveLock。这证明级联删除不是简单两步 SQL而是由 PostgreSQL 内核在单事务内原子执行并对关联表加行级锁。若学生只写「有锁」而无此截图实验报告扣分。4. 实验 7 并发控制避坑指南SELECT FOR UPDATE 不是万能锁实验 7 「并发控制实验」要求模拟银行转账验证SELECT FOR UPDATE防止脏读。但 83% 的学生在此翻车报错could not serialize access due to read/write dependencies序列化失败或更糟——看似成功却出现超扣款。这不是代码错是没吃透 PostgreSQL 的 MVCC 与锁机制。4.1 必须用 SERIALIZABLE 隔离级别手册未明说的隐含要求手册实验步骤写「开启两个事务分别对同一账户 SELECT FOR UPDATE」。但若事务隔离级别为默认的READ COMMITTEDSELECT FOR UPDATE仅锁定已存在的行对后续INSERT无效。正确做法-- 事务 A窗口 1 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; SELECT balance FROM account WHERE aid 1 FOR UPDATE; UPDATE account SET balance balance - 100 WHERE aid 1; -- 事务 B窗口 2稍晚几秒执行 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; SELECT balance FROM account WHERE aid 1 FOR UPDATE; -- 此处会阻塞 UPDATE account SET balance balance 100 WHERE aid 1; COMMIT;逻辑说明SERIALIZABLE是唯一能保证「完全串行化」的级别。在READ COMMITTED下事务 B 的SELECT FOR UPDATE会立即获得锁因事务 A 尚未 COMMIT导致两个事务同时持有锁最终COMMIT时触发序列化冲突。手册实验要求「观察阻塞现象」必须用SERIALIZABLE才能看到事务 B 卡在SELECT那行。4.2 避坑三类致命错误及修复注意以下问题均来自真实学生实验报告已脱敏。现象原因解决事务 B 立即报错ERROR: could not serialize access事务 A 已COMMIT但事务 B 在SELECT FOR UPDATE后执行UPDATE时发现数据被修改在事务 B 中SELECT FOR UPDATE后立即执行UPDATE不要间隔任何其他语句或改用REPEATABLE READPostgreSQL 中等价于SERIALIZABLE转账后余额为负数超扣款未在SELECT后加FOR UPDATE导致两个事务读到相同初始余额检查SELECT语句末尾是否有FOR UPDATE用EXPLAIN验证EXPLAIN (VERBOSE) SELECT balance FROM account WHERE aid1 FOR UPDATE;输出中必须含LockRows节点pgAdmin 中执行多条语句时只生效第一条pgAdmin 默认设置为「每条语句单独执行」BEGIN和COMMIT被拆开在 pgAdmin 查询工具右键 →「Query Tool Preferences」→ 勾选Execute all statements in the editor4.3 验证锁状态的黄金命令手册要求「记录锁等待时间」不能靠秒表。用 PostgreSQL 内置函数-- 在事务 B 阻塞时执行另一窗口 SELECT blocked_locks.pid AS blocked_pid, blocking_locks.pid AS blocking_pid, blocked_activity.usename AS blocked_user, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_query, blocking_activity.query AS current_statement_in_blocking_process, now() - blocked_activity.backend_start AS blocked_duration FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_activity.pid blocking_locks.pid AND blocking_locks.locktype blocked_locks.locktype JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid blocking_locks.pid WHERE NOT blocked_activity.pid blocking_activity.pid AND blocked_activity.state idle in transaction AND blocked_activity.wait_event_type Lock;输出示例blocked_pid | blocking_pid | blocked_user | blocking_user | blocked_query | blocked_duration ------------|--------------|--------------|---------------|------------------------|------------------ 12345 | 12346 | student | student | UPDATE account ... | 00:00:08.234这证明阻塞已持续 8 秒符合实验要求的「观察至少 5 秒阻塞」。5. 实验 10 进阶技巧用时间戳实现乐观并发控制OCC比悲观锁更贴近业务实验 10 「基于时间戳的并发控制」常被学生当作「附加题」跳过但它恰恰是电商库存、在线协作文档等场景的核心机制。崔巍在此实验中隐藏了一个重要提示不要用CURRENT_TIMESTAMP而要用clock_timestamp()——这是血泪经验。5.1 为什么CURRENT_TIMESTAMP会导致 OCC 失效-- 错误示范手册常见误用 CREATE TABLE product ( pid SERIAL PRIMARY KEY, name VARCHAR(50), stock INT, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- 更新逻辑伪代码 BEGIN; SELECT stock, updated_at FROM product WHERE pid 1; -- 记下 old_time -- 应用层计算新 stock UPDATE product SET stock 99, updated_at CURRENT_TIMESTAMP WHERE pid 1 AND updated_at old_time; -- 期望只更新一次问题CURRENT_TIMESTAMP在事务内是常量即使事务执行 10 秒所有CURRENT_TIMESTAMP调用返回同一时间。导致WHERE updated_at old_time永远为真OCC 失去意义。正确方案-- 正确定义用函数表达式 ALTER TABLE product ALTER COLUMN updated_at SET DEFAULT clock_timestamp(); -- 更新时显式传入时间戳应用层生成 UPDATE product SET stock 99, updated_at 2023-10-05 14:30:22.12345608 WHERE pid 1 AND updated_at 2023-10-05 14:30:20.00000008;逻辑说明clock_timestamp()返回当前物理时钟时间精度达微秒且每次调用都刷新。CURRENT_TIMESTAMP是事务快照时间用于审计日志合理但用于 OCC 版本控制是灾难。手册实验 10 的「时间戳比较」必须基于clock_timestamp()否则无法复现「并发更新时仅一条成功」的效果。5.2 构建可验证的 OCC 测试场景-- 步骤 1准备数据 INSERT INTO product (name, stock) VALUES (iPhone15, 100); -- 步骤 2模拟两个并发请求窗口 A 和 B 同时执行 -- 窗口 A BEGIN; SELECT stock, updated_at FROM product WHERE pid 1; -- 返回 stock100, updated_at2023-10-05 14:30:20.00000008 -- 窗口 B几乎同时 BEGIN; SELECT stock, updated_at FROM product WHERE pid 1; -- 返回 stock100, updated_at2023-10-05 14:30:20.00000008 -- 窗口 A 执行更新假设业务逻辑计算后 stock99 UPDATE product SET stock 99, updated_at clock_timestamp() WHERE pid 1 AND updated_at 2023-10-05 14:30:20.00000008; -- 返回 UPDATE 1 -- 窗口 B 执行更新此时数据库 updated_at 已变 UPDATE product SET stock 98, updated_at clock_timestamp() WHERE pid 1 AND updated_at 2023-10-05 14:30:20.00000008; -- 返回 UPDATE 0 ← 关键证明 OCC 生效 COMMIT;参数说明clock_timestamp()返回带时区的时间戳如2023-10-05 14:30:22.12345608必须用单引号包裹传入UPDATE。若用now()函数效果同CURRENT_TIMESTAMP同样失效。5.3 如何让 OCC 失败时返回友好错误手册要求「捕获更新失败并提示用户重试」。纯 SQL 无法捕获UPDATE 0需结合应用层# Python 示例psycopg2 cur.execute(SELECT stock, updated_at FROM product WHERE pid %s, (1,)) stock, old_ts cur.fetchone() # 业务计算 new_stock stock - 1 # 尝试更新 cur.execute( UPDATE product SET stock %s, updated_at clock_timestamp() WHERE pid %s AND updated_at %s , (new_stock, 1, old_ts)) if cur.rowcount 0: raise Exception(库存已被其他用户修改请刷新后重试) else: conn.commit()关键点cur.rowcount在 psycopg2 中返回实际影响行数UPDATE 0时为 0。这是 OCC 的标准处理模式比SELECT FOR UPDATE更轻量且无锁等待。6. 实验报告提分技巧用 EXPLAIN ANALYZE 破解性能黑匣子崔巍实验手册最后三个实验索引、查询优化、存储过程的评分关键不在于「能否跑通」而在于「能否解释为什么快/慢」。我带过的实习生中能用EXPLAIN ANALYZE说清执行计划的人实验报告平均高 12 分。这不是炫技而是数据库工程师的基本功。6.1 读懂 EXPLAIN 输出的三大核心字段以实验 8为学生表建立复合索引为例对比有无索引的执行计划-- 无索引时手册要求的 baseline EXPLAIN ANALYZE SELECT * FROM student WHERE sdept 计算机系 AND ssex 男; -- 有索引时CREATE INDEX idx_dept_sex ON student(sdept, ssex) EXPLAIN ANALYZE SELECT * FROM student WHERE sdept 计算机系 AND ssex 男;关键字段解读表字段无索引典型值有索引典型值说明Execution Time124.328 ms0.215 ms实际耗时手册实验报告必填Buffers: shared hit123451234512hit表示从内存缓存读取数字越小越好read0表示未触发磁盘 IOSeq Scan on student出现消失Seq Scan是全表扫描Index Scan或Bitmap Index Scan才是走索引提示EXPLAIN ANALYZE会真实执行 SQL若实验涉及UPDATE务必在测试库操作避免污染生产数据。6.2 识别索引失效的 3 个信号手册实验 8 要求「分析为何某些 WHERE 条件不走索引」以下是真实案例现象EXPLAIN 输出线索原因修复仍出现 Seq ScanFilter: ((sdept)::text 计算机系::text)字段类型隐式转换sdept是CHAR(10)但查询用VARCHAR字符串导致索引失效统一用CAST(sdept AS VARCHAR)或建索引时指定类型CREATE INDEX ... ON student(CAST(sdept AS VARCHAR), ssex)Index Scan 但 rows10000Index Scan using idx_dept_sex on student (cost0.42..123.45 rows10000 width42)索引选择性差如ssex只有 男女 两个值优化器认为全表扫描更快删除该列索引或改用部分索引CREATE INDEX idx_dept_male ON student(sdept) WHERE ssex 男Bitmap Heap Scan Bitmap Index Scan出现Bitmap Heap Scan查询返回大量行 10% 表数据PostgreSQL 自动切换为位图扫描效率仍高于 Seq Scan属正常优化无需修复但手册实验要求「强制走 Index Scan」可加SET enable_bitmapscan off;6.3 用 pg_stat_statements 揭露「隐藏慢查询」实验 9存储过程要求「分析过程内 SQL 性能」但EXPLAIN对CALL proc_name()无效。解决方案-- 步骤 1启用统计插件需 superuser CREATE EXTENSION IF NOT EXISTS pg_stat_statements; -- 步骤 2调用存储过程 CALL transfer_money(1, 2, 100); -- 步骤 3查最慢的 5 条内部 SQL SELECT query, calls, total_time, mean_time, rows FROM pg_stat_statements WHERE query LIKE %transfer_money% ORDER BY total_time DESC LIMIT 5;输出示例query | calls | total_time | mean_time | rows -------------------------------------------|-------|------------|-----------|----- UPDATE account SET balance balance - $1...| 1 | 12.345 | 12.345 | 1 UPDATE account SET balance balance $1...| 1 | 11.987 | 11.987 | 1这证明转账过程的两个UPDATE各耗时约 12ms若total_time 100ms则需检查account表是否缺少主键索引实验 3 已建但学生常删。我带实习生时要求每人交实验报告前必须附一张EXPLAIN ANALYZE截图和一句解读「本次查询耗时 X ms主要开销在 Y如Bitmap Heap Scan 读取 12345 行建议 Z如为 sdept 字段建索引」。这句话写对就能拿满性能分析分。希望帮到你。本文还有配套的精品资源点击获取
