简介本资源是西南交通大学计算机类专业《数据库原理与设计实验》课程的完整实验报告范本面向数据库初学者及高校实践教学场景聚焦SQL建表、约束实现主键、外键、CHECK、DEFAULT、规则绑定与数据增删改查等核心实操能力训练。压缩包为1个1.11MB的docx文件内容结构规范涵盖实验目的、分步代码含person_2282、salary_2282等多表创建及约束定义、执行结果截图、典型问题排错如规则绑定报错及GO语句修复、完整性验证分析等关键模块严格遵循该校2021–2022学年实验报告模板与评分标准含独立性、代码正确性、结果分析深度等五维指标。已有444人学习下载可直接用于参考撰写、格式自查或教学辅助尤其适合需快速掌握SQL DDL/DML实战要点与报告规范性的本科生。1. 西南交通大学数据库原理与设计实验不是抄作业是把SQL从“能跑”练到“敢上线”这门课的名字听起来像教科书目录——但实际做下来你会发现自己卡在「明明语法没错执行却报错」的黑匣子边缘建个视图提示“权限不足”写个触发器发现UPDATE后数据没变调试存储过程时连RETURN值都抓不到。这不是SQL基础不牢而是高校实验环境和真实工程场景之间存在三道隐形墙权限模型差异、事务边界模糊、以及DDL/DML混合操作的执行时序陷阱。西南交大这门实验课的价值恰恰在于它用PostgreSQL或SQL Server 2019搭建了一个“半生产级沙盒”——所有实验都强制要求你手写CREATE SCHEMA、显式定义WITH GRANT OPTION、在触发器里处理REFERENCING NEW TABLE、甚至用pg_stat_statements分析慢查询。适合两类人一是刚学完《数据库系统概论》但写不出可维护SQL的本科生二是想补足“事务隔离级别实操感”“触发器副作用收敛”“视图物化成本预判”这些硬缺口的转行开发者。别指望靠Navicat点点就过关——这里每个实验都在逼你直面SQL作为声明式语言的“反直觉性”。2. 用PostgreSQL 15本地跑通实验最小环境避开Windows服务冲突与中文路径坑西南交大实验指导书默认使用PostgreSQL部分班级用SQL Server 2019但学生常因环境配置失败直接放弃后续实验。我建议跳过官网一键安装包改用解压即用的Portable版PostgreSQL 15.5非官方编译但经校内实验室验证稳定。关键不是版本号而是绕开Windows服务注册冲突和中文路径导致的initdb失败。2.1 下载与初始化用cmd而非PowerShell执行initdb# 下载地址校内镜像站https://mirror.swjtu.edu.cn/postgresql/portable/pg15.5-win64.zip # 解压到 D:\pg155注意路径不能含中文、空格、括号 cd D:\pg155\bin initdb.exe -D D:\pg155\data -E UTF8 --localeChinese_China.936 -U postgres -W注意--localeChinese_China.936是必须项否则创建中文表名/注释时会报错“invalid locale name”。PowerShell中-W参数常被吞掉密码输入务必用CMD执行。-D指定的数据目录若已存在initdb会拒绝覆盖——删掉旧data文件夹再重试别试图强行--no-clean。2.2 启动服务用pg_ctl而非Windows服务管理器# 启动后台运行日志输出到logs目录 pg_ctl.exe -D D:\pg155\data -l D:\pg155\logs\server.log start # 验证是否监听localhost:5432 netstat -ano | findstr :5432 # 应返回类似TCP 127.0.0.1:5432 0.0.0.0:0 LISTENING 12345启动失败常见原因端口被占用Skype/VMware常抢5432、data目录权限不足右键data文件夹→属性→安全→添加Users组“完全控制”、或logs目录不存在手动创建。血泪经验不要用pgAdmin自带的“服务启动”按钮——它调用的是系统服务注册表而Portable版根本没注册服务点它只会弹出“服务未安装”错误框。2.3 连接与建库用psql命令行而非图形界面建第一个实验库-- 在psql中执行避免Navicat自动加引号导致大小写敏感问题 postgres# CREATE DATABASE exp_db OWNER postgres ENCODING UTF8 LC_COLLATE Chinese_China.936 LC_CTYPE Chinese_China.936 TEMPLATE template0; postgres# \c exp_db exp_db# CREATE SCHEMA lab1 AUTHORIZATION postgres; exp_db# SET search_path TO lab1, public;关键点TEMPLATE template0防止从template1继承可能存在的乱码对象LC_COLLATE/LC_CTYPE必须与initdb一致否则后续ORDER BY 中文字段会乱序SET search_path让后续所有CREATE TABLE默认落在lab1 schema下——这是实验要求的“多schema隔离”前提也是后续触发器跨schema引用的基础。3. 存储过程实验用PL/pgSQL实现带事务控制的学生成绩批量更新实验2要求编写存储过程完成“按课程ID批量更新学生成绩并记录操作日志”。很多同学直接用UPDATE ... WHERE course_id $1结果发现日志表没写入、或部分成绩更新失败但日志已记——这就是没理解存储过程内事务边界的典型翻车。3.1 创建日志表与目标表显式指定主键与约束-- 实验要求的表结构简化版 CREATE TABLE students ( stu_id CHAR(10) PRIMARY KEY, name VARCHAR(20) NOT NULL, major VARCHAR(30) ); CREATE TABLE courses ( course_id CHAR(8) PRIMARY KEY, course_name VARCHAR(50) NOT NULL, credit INT CHECK (credit BETWEEN 1 AND 6) ); CREATE TABLE scores ( stu_id CHAR(10) REFERENCES students(stu_id) ON DELETE CASCADE, course_id CHAR(8) REFERENCES courses(course_id) ON DELETE CASCADE, score NUMERIC(4,1) CHECK (score BETWEEN 0 AND 100), PRIMARY KEY (stu_id, course_id) ); -- 日志表必须带事务ID字段用于关联回滚操作 CREATE TABLE score_update_log ( log_id SERIAL PRIMARY KEY, course_id CHAR(8) NOT NULL, update_time TIMESTAMP WITH TIME ZONE DEFAULT NOW(), updated_count INT NOT NULL, transaction_id TEXT -- pg backend_pid() clock_timestamp() 拼接 );为什么不用AUTO_INCREMENTPostgreSQL的SERIAL本质是SEQUENCE实验要求日志需与事务强绑定transaction_id字段用于后续排查“某次批量更新到底影响了几行”这是企业级审计刚需。3.2 编写带异常捕获的存储过程重点看EXCEPTION块与GET STACKED DIAGNOSTICSCREATE OR REPLACE FUNCTION update_scores_by_course( p_course_id CHAR(8), p_new_score NUMERIC(4,1) ) RETURNS TABLE(result TEXT, affected_rows INT) AS $$ DECLARE v_rowcount INT : 0; v_trans_id TEXT; v_error_msg TEXT; BEGIN -- 生成唯一事务标识避免并发时日志混淆 v_trans_id : pg_backend_pid()::TEXT || _ || to_char(clock_timestamp(), YYYYMMDDHH24MISSUS); -- 开启事务块注意函数内默认不开启新事务需显式BEGIN BEGIN -- 更新成绩 UPDATE scores SET score p_new_score WHERE course_id p_course_id; GET DIAGNOSTICS v_rowcount ROW_COUNT; -- 写入日志即使更新0行也要记 INSERT INTO score_update_log (course_id, updated_count, transaction_id) VALUES (p_course_id, v_rowcount, v_trans_id); -- 返回成功结果 result : SUCCESS; affected_rows : v_rowcount; RETURN NEXT; EXCEPTION WHEN foreign_key_violation THEN v_error_msg : 外键约束失败课程ID || p_course_id || 不存在; WHEN check_violation THEN v_error_msg : 检查约束失败分数 || p_new_score || 超出0-100范围; WHEN OTHERS THEN GET STACKED DIAGNOSTICS v_error_msg PG_EXCEPTION_DETAIL; v_error_msg : 未知错误 || v_error_msg; -- 记录错误日志不抛出让调用方决定是否重试 INSERT INTO score_update_log (course_id, updated_count, transaction_id) VALUES (p_course_id, 0, v_trans_id || _ERROR); result : ERROR; affected_rows : 0; RETURN NEXT; END; END; $$ LANGUAGE plpgsql;逻辑说明GET DIAGNOSTICS v_rowcount ROW_COUNT获取UPDATE影响行数比COUNT(*)高效且原子EXCEPTION块捕获三类典型错误外键、CHECK、其他并用GET STACKED DIAGNOSTICS提取详细堆栈——这是调试存储过程的核心技能比RAISE NOTICE更精准错误日志仍写入score_update_log但transaction_id加_ERROR后缀便于DBA用SELECT * FROM score_update_log WHERE transaction_id LIKE %_ERROR%快速定位故障批次。3.3 调用与验证用DO块模拟真实业务调用链-- 测试更新不存在的课程ID观察错误日志 DO $$ DECLARE r RECORD; BEGIN FOR r IN SELECT * FROM update_scores_by_course(CS101, 95.0) LOOP RAISE NOTICE 结果% | 影响行数% , r.result, r.affected_rows; END LOOP; END $$; -- 验证日志表是否写入重点看transaction_id是否唯一 SELECT * FROM score_update_log WHERE course_id CS101 ORDER BY update_time DESC LIMIT 3;参数说明p_new_score NUMERIC(4,1)的精度定义必须严格匹配scores表的score字段否则隐式转换可能导致四舍五入偏差如传95.05存成95.1v_trans_id拼接pg_backend_pid()确保同一连接内事务ID唯一clock_timestamp()提供微秒级时间戳——这两者组合比单纯用txid_current()更利于分布式场景追踪。4. 触发器实验用BEFORE INSERT触发器实现学号格式自动标准化实验3要求对students表插入时自动校验并标准化学号如20230001→202300012023 0001→20230001。学生常犯的错是写成AFTER触发器导致UPDATE students引发递归触发或用NEW.stu_id : trim(NEW.stu_id)却忘记RETURN NEW——这是PL/pgSQL触发器最经典的“玄学失效”。4.1 创建BEFORE触发器函数必须RETURN NEW且禁止在函数内UPDATE自身表CREATE OR REPLACE FUNCTION normalize_stu_id() RETURNS TRIGGER AS $$ BEGIN -- 校验长度实验要求学号为10位纯数字 IF LENGTH(TRIM(NEW.stu_id)) ! 10 THEN RAISE EXCEPTION 学号长度必须为10位当前为%位, LENGTH(TRIM(NEW.stu_id)); END IF; -- 移除空格、制表符等不可见字符 NEW.stu_id : REPLACE(TRIM(NEW.stu_id), E\t, ); NEW.stu_id : REPLACE(NEW.stu_id, , ); -- 校验是否全数字 IF NEW.stu_id !~ ^[0-9]{10}$ THEN RAISE EXCEPTION 学号必须为10位纯数字当前值%, NEW.stu_id; END IF; -- 强制转大写虽数字无大小写但为后续扩展预留 NEW.stu_id : UPPER(NEW.stu_id); -- 关键必须RETURN NEW否则INSERT被取消 RETURN NEW; END; $$ LANGUAGE plpgsql;为什么不能用AFTERAFTER触发器在INSERT完成后执行此时NEW已是只读状态NEW.stu_id : ...无效若在此处UPDATE students SET stu_id ... WHERE stu_id OLD.stu_id会再次触发该触发器造成无限递归PostgreSQL默认递归深度10超限报错。BEFORE触发器则允许修改NEW且在INSERT前生效。4.2 绑定触发器到students表注意触发时机与条件-- 创建触发器仅对INSERT生效UPDATE时不触发 CREATE TRIGGER trig_normalize_stu_id BEFORE INSERT ON students FOR EACH ROW EXECUTE FUNCTION normalize_stu_id(); -- 验证触发器是否启用实验报告需截图此命令结果 SELECT tgname, tgtype, tgenabled FROM pg_trigger WHERE tgrelid students::regclass;触发类型说明BEFORE INSERT在INSERT语句执行前修改NEWFOR EACH ROW每行触发一次非语句级tgenabled O表示触发器启用D禁用R复制模式实验环境必须为O。4.3 测试用例设计覆盖边界场景-- 测试1正常插入应成功 INSERT INTO students (stu_id, name, major) VALUES (20230001, 张三, 计算机科学); -- 测试2含空格学号应自动清理 INSERT INTO students (stu_id, name, major) VALUES (2023 0001, 李四, 软件工程); -- 预期实际插入的stu_id为20230001 -- 测试3长度错误应报错 INSERT INTO students (stu_id, name, major) VALUES (2023001, 王五, 网络工程); -- 预期报错学号长度必须为10位 -- 测试4非数字字符应报错 INSERT INTO students (stu_id, name, major) VALUES (2023000A, 赵六, 信息安全); -- 预期报错学号必须为10位纯数字血泪经验测试时用\set VERBOSITY verbose开启详细错误输出否则RAISE EXCEPTION只显示ERROR: 看不到具体哪行代码抛出的异常。另外!~是正则取反操作符^[0-9]{10}$确保开头结尾都是10位数字——漏掉^或$会导致abc1234567这种非法值通过校验。5. 视图实验创建可更新视图并解决“权限不足”报错实验4要求创建视图v_student_scores学生姓名、课程名、成绩并支持通过视图UPDATE成绩。学生常遇到ERROR: permission denied for table scores以为是没授权其实根源在于可更新视图的底层规则限制——PostgreSQL要求视图必须基于单表、无聚合、无DISTINCT、且UPDATE列必须直接映射到基表列。5.1 创建基础视图用JOIN但确保可更新性-- 正确写法用LEFT JOIN保证scores为主表且SELECT列表只含scores和students的列 CREATE VIEW v_student_scores AS SELECT s.stu_id, s.name AS student_name, c.course_name, sc.score, sc.course_id -- 必须包含course_id否则UPDATE时无法定位行 FROM scores sc LEFT JOIN students s ON sc.stu_id s.stu_id LEFT JOIN courses c ON sc.course_id c.course_id; -- 验证视图是否可更新关键 SELECT table_name, is_updatable, is_insertable_into, is_trigger_updatable FROM information_schema.views WHERE table_name v_student_scores; -- 预期is_updatable YES, is_insertable_into NO因含JOIN不支持INSERT为什么用LEFT JOININNER JOIN会导致没有成绩的学生被过滤但实验要求视图包含所有学生信息LEFT JOIN以scores为主表确保UPDATE sc.score时能准确定位到scores表的行。sc.course_id必须显式SELECT否则UPDATE v_student_scores SET score 90 WHERE student_name 张三会因无法确定course_id而失败。5.2 授予视图操作权限区分OWNER与USAGE权限-- 步骤1给postgres用户OWNER授予基表权限必须 GRANT SELECT, UPDATE(score) ON scores TO postgres; GRANT SELECT ON students TO postgres; GRANT SELECT ON courses TO postgres; -- 步骤2给实验用户如student_user授予视图权限 CREATE USER student_user WITH PASSWORD swjtu2024; GRANT USAGE ON SCHEMA lab1 TO student_user; GRANT SELECT, UPDATE ON v_student_scores TO student_user; -- 步骤3关键设置视图的security_invoker实验要求 ALTER VIEW v_student_scores SET (security_invoker true);权限链解析GRANT UPDATE ON v_student_scores只是授予视图操作权但PostgreSQL执行UPDATE时会检查基表权限。security_invoker true表示以调用者身份student_user检查基表权限而非视图OWNERpostgres——所以必须先GRANT UPDATE(score) ON scores TO postgres再让student_user通过视图间接更新。若设为false默认则只检查postgres是否有scores表权限student_user永远报“权限不足”。5.3 测试可更新视图用student_user身份验证-- 切换到student_user连接用psql -U student_user -d exp_db -- 执行更新应成功 UPDATE v_student_scores SET score 88.5 WHERE student_name 张三 AND course_name 数据库原理; -- 验证基表是否更新 SELECT stu_id, course_id, score FROM scores WHERE stu_id 20230001 AND course_id CS101; -- 尝试更新不可更新列应报错 UPDATE v_student_scores SET student_name 张三丰 WHERE stu_id 20230001; -- 预期ERROR: column student_name of relation students is not updatable避坑 / 常见问题 / 排查现象执行UPDATE v_student_scores SET score 90报错“permission denied for table scores”原因未对基表scores授予UPDATE(score)权限或security_invoker未设为true解决GRANT UPDATE(score) ON scores TO postgres; ALTER VIEW v_student_scores SET (security_invoker true);现象SELECT * FROM v_student_scores返回空结果但scores表有数据原因JOIN条件错误如ON sc.stu_id s.stu_id写成ON sc.stu_id c.course_id或courses表为空解决用EXPLAIN VERBOSE SELECT * FROM v_student_scores查看执行计划确认JOIN路径单独SELECT * FROM courses验证数据完整性现象通过视图UPDATE后SELECT查不到新值原因未提交事务psql默认autocommit关闭或UPDATE WHERE条件匹配到0行解决执行COMMIT;后重查用RETURNING *确认UPDATE是否生效UPDATE v_student_scores SET score90 WHERE ... RETURNING *;现象创建视图时报错“column reference xxx is ambiguous”原因JOIN的两个表有同名列如students和courses都有id字段SELECT中未加表别名前缀解决所有列显式用别名如s.stu_id,c.course_name现象information_schema.views.is_updatable NO原因视图含聚合函数、GROUP BY、DISTINCT、UNION或SELECT列表包含表达式如s.name || 同学解决简化视图定义确保所有列直接来自基表字段无计算列6. 综合实验技巧用pg_stat_statements定位慢SQL与触发器性能瓶颈实验5要求优化一个含触发器的复杂查询但学生常陷入“改SQL语句”的误区——实际上触发器执行时间被计入父SQL总耗时却不会出现在EXPLAIN ANALYZE中。西南交大评分标准明确要求用pg_stat_statements分析这才是真实工程中的排查路径。6.1 启用pg_stat_statements扩展必须重启服务# 修改postgresql.conf在D:\pg155\data\postgresql.conf末尾添加 shared_preload_libraries pg_stat_statements pg_stat_statements.max 10000 pg_stat_statements.track all # 重启服务重要不重启不生效 pg_ctl.exe -D D:\pg155\data stop pg_ctl.exe -D D:\pg155\data start为什么必须重启shared_preload_libraries是超级用户参数动态加载会失败。track all确保捕获触发器内执行的SQL默认只跟踪顶层SQL。6.2 创建触发器性能对比实验量化“隐式开销”-- 场景向scores表插入1000条记录对比有/无触发器的耗时 -- 步骤1禁用触发器实验对照组 ALTER TABLE scores DISABLE TRIGGER ALL; -- 步骤2清空统计重置计数器 SELECT pg_stat_statements_reset(); -- 步骤3执行批量插入 INSERT INTO scores (stu_id, course_id, score) SELECT 2023 || lpad((i%1000)::text, 4, 0), CS101, (random()*100)::numeric(4,1) FROM generate_series(1,1000) i; -- 步骤4查询pg_stat_statements获取top耗时SQL SELECT query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements WHERE query LIKE INSERT INTO scores% ORDER BY total_exec_time DESC LIMIT 5;预期结果无触发器时total_exec_time ≈ 50msmean_exec_time ≈ 0.05ms启用触发器后total_exec_time ≈ 200msmean_exec_time ≈ 0.2ms多出的150ms就是触发器函数执行时间含PL/pgSQL解释开销6.3 优化触发器用STATEMENT级替代ROW级减少调用次数-- 原BEFORE ROW触发器每行调用1次1000行1000次函数调用 -- 优化为BEFORE STATEMENT触发器整个INSERT只调用1次 CREATE OR REPLACE FUNCTION batch_normalize_scores() RETURNS TRIGGER AS $$ DECLARE v_count INT; BEGIN -- 统一校验检查本次INSERT中所有score是否合规 SELECT COUNT(*) INTO v_count FROM NEW_TABLE WHERE score 0 OR score 100; IF v_count 0 THEN RAISE EXCEPTION 检测到%行成绩超出0-100范围, v_count; END IF; -- 批量更新将NEW_TABLE中score四舍五入到小数点后1位 UPDATE NEW_TABLE SET score ROUND(score, 1); RETURN NULL; -- STATEMENT级触发器必须RETURN NULL END; $$ LANGUAGE plpgsql; -- 创建STATEMENT触发器 CREATE TRIGGER trig_batch_normalize BEFORE INSERT ON scores REFERENCING NEW TABLE AS new_data FOR EACH STATEMENT EXECUTE FUNCTION batch_normalize_scores();关键改进点REFERENCING NEW TABLE AS new_data访问本次INSERT的所有行PostgreSQL 13特性FOR EACH STATEMENT整个INSERT只触发1次避免1000次函数调用开销RETURN NULLSTATEMENT级触发器固定返回NULL否则报错。6.4 验证优化效果用pg_stat_statements对比数据-- 启用触发器后重复步骤2-4 -- 对比优化前后 -- 优化前calls1000, total_exec_time200ms -- 优化后calls1, total_exec_time15ms函数调用开销大幅降低 -- 查看触发器内SQL耗时定位瓶颈 SELECT query, total_exec_time, calls FROM pg_stat_statements WHERE query ~ ROUND|COUNT.*new_data ORDER BY total_exec_time DESC;我的习惯每次写完触发器/存储过程必做三件事① 用pg_stat_statements查calls和total_exec_time确认是否被高频调用② 在函数内RAISE LOG debug: %, clock_timestamp();打时间戳确认执行路径③ 用EXPLAIN (ANALYZE, BUFFERS)跑触发器内SQL看是否走索引。这比盯着错误信息猜原因快十倍。希望帮到你。本文还有配套的精品资源点击获取
