openGauss数据库课程设计实战：从安装到触发器完整指南

简介面向广东工业大学数据库课程学习者这份基于openGauss平台的2022年实验与课程设计资料解决了完成实验报告和课设的核心需求。内容覆盖五个核心实验数据库及基本表的建立、查询数据库、创建和使用索引和视图、创建和使用存储过程、创建和使用触发器并附带基于JDBC的课程设计大作业适合正在学习数据库、需要交实验和课设的本科生也适合期末系统复习。资料包共64个文件以Java源码和class字节码为主便于阅读和运行另有JPG实验截图、配置文件、MySQL驱动包及数据库文件整体约6.93MB目录清晰可按需提取。目前已有2930人学习下载在同类资源中具有一定参考热度。借助这份资料读者能系统掌握openGauss从建表、查询到索引、视图、存储过程、触发器的完整链路还能参考一套含源码、截图和运行记录的JDBC课设实例对理解数据库编程与项目整合、撰写实验报告都很有帮助。1. 广工数据库实验走上 openGauss这份课设先过环境关很多同学的数据库课程设计卡在第一步实验平台写着 openGauss自己笔记本却装不上装一半就报错退回 MySQL可学校交作业、答辩都要以 openGauss 为准最后又得回来死磕。其实 openGauss 没有想象中难只是它和 MySQL 在权限、编码、事务处理上的习惯差别很大照着 MySQL 的路径来必然翻车。这篇文章以图书借阅系统为例把数据库实验和课设完整拆开安装部署、建表、增删改查、存储过程、触发器、备份验证。适合正在做 school 实验和课设的学生也适合给团队搭建 openGauss 教学环境的新人。先装起来、连得上后面的实验才有意义。2. 拉通 openGauss 安装部署流程本机先能跑再去碰实验服务器2.1 部署方式怎么选多数课设单机安装就够openGauss 有企业版、极简版、容器化几种落地方案。学校实验平台如果是老师已经装好的服务端你只需要一个 gsql 客户端去连但课设要做到本地能跑、能复制、能交报告最好自己装一个单机实例。单机安装对内存和 CPU 要求低做实验完全够用官方文档里动辄写几十个节点的高可用方案不需要碰。我在踩坑过程中最常用的是极简安装方式只初始化一个 primary 节点不用 OMOperations Manager去管理集群。这样目录简单、进程少、出问题时定位快。如果只是为了跑一门课设不建议一上来就折腾容器因为容器网络和宿主机端口映射会让你分不清是数据库问题还是 Docker 问题。等本机环境稳定了再考虑要不要用远程连接去连老师的实验服务器。2.2 最小安装步骤从建 omm 用户到 gsql 能敲 SQLopenGauss 不能用 root 直接跑数据库服务它要求用专门的用户执行初始化。常见做法是先建一个 omm 用户把安装包解压到 /opt/software 下然后用 omm 身份初始化数据目录# 创建专用操作系统用户 useradd omm # 解压 openGauss 安装包到 /opt/software tar -xvf openGauss-*-x86_64.tar.bz2 -C /opt/software # 目录属主必须改为 omm否则初始化会提示权限不足 chown -R omm:omm /opt/software # 用密码文件先写好超级用户密码再用 gs_initdb 初始化 echo Omm2022 /home/omm/app.pwd chown omm:omm /home/omm/app.pwd su - omm -c /opt/software/bin/gs_initdb -D /opt/software/data --nodenameprimary --encodingUTF8 --pwfile/home/omm/app.pwd # 启动数据库-D 指向刚才初始化的数据目录 su - omm -c gs_ctl start -D /opt/software/data这段命令里有几个参数很容易理解错。-D指定数据目录所有表数据、配置文件、日志以后都在这个目录下路径不要带中文和空格。--nodenameprimary是 openGauss 对单节点的称呼不是随便填集群模式下会用它做节点标识。--encodingUTF8是我强烈建议加上的很多中文乱码问题都源于初始化时没有显式指定字符集。--pwfile用文件传入密码避免在 shell 历史里留下明文密码。初始化过程会打印“Successfully created。”之类的信息但真正判断有没有成功要看能不能启动。启动后跑一条最简单的查询验证su - omm -c gsql -d postgres -p 5432 -r -c SELECT version();能返回 openGauss 版本号说明服务起来了。如果提示“could not connect to server”去数据目录下看日志通常是端口被占用或目录属主不对。2.3 连接数据库本地、远程和免密三种方式都要会用gsql 是 openGauss 的命令行客户端日常实验几乎都在它里面完成。最基本的连接方式是gsql -d postgres -p 5432 -U omm -r其中-d指定数据库名-p是端口-U是用户名-r把 SQL 的提示符显示出来方便区分有没有进到 SQL 编辑器。远程连接实验服务器时格式一样只是加上-hgsql -h 192.168.1.20 -p 5432 -d lab_db -U lab_user -W -r-W会交互式输入密码比把密码写在命令里安全。如果脚本里要自动连可以把密码放到环境变量PGPASSWORD或者写到~/.pgpass文件。我在课设提交脚本里一般用PGPASSWORD简单直接但注意这个变量只在当前 shell 会话可见。还有一个容易被忽略的细节openGauss 默认只监听本机回环地址远程连接前必须修改listen_addresses和 pg_hba.conf。单机实验不需要动但如果要连老师服务器这两项没改掉就会一直提示连接超时。2.4 初始化实验库别把表全堆在 postgres 库里postgres库是系统维护库实验数据混在里面既乱又容易产生权限冲突。我一般拿到新环境第一件事是创建独立用户和独立数据库gsql -d postgres -p 5432 -U omm -r CREATE USER lab_user WITH PASSWORD Lab12345; CREATE DATABASE lab_db OWNER lab_user ENCODING UTF8; \c lab_db GRANT ALL ON SCHEMA public TO lab_user;第一行创建用户时openGauss 对密码强度有要求太短或纯数字会报错第二行把数据库归属给 lab_user这样他拥有这个库的绝大部分权限。\c lab_db是 gsql 的切库命令相当于 MySQL 里的use lab_db。最后一行 GRANT 是课设里最常见的权限坑openGauss 默认的 public schema 对新用户不是完全放开的不授权的话后面连表都建不了。这个动作做完后续实验全部用 lab_user 操作omm 只负责管理避免实验报告里出现一堆 root 级操作。3. 数据库增删改查与建表用一套图书借阅系统过完实验3.1 三张表怎么建字段类型、约束和默认值图书借阅管理是数据库课程设计里出现频率最高的题目正好把实验要求的建库建表、增删改查、完整性约束全串起来。设计三张表学生表、图书表、借阅表。这是典型的三范式结构借阅表做成宽表会冗余拆开管理起来才顺手。CREATE TABLE student ( sid INTEGER PRIMARY KEY, sname VARCHAR(32) NOT NULL, age INTEGER CHECK (age BETWEEN 0 AND 120), dept VARCHAR(64) DEFAULT 计算机学院 ); CREATE TABLE book ( bid INTEGER PRIMARY KEY, bname VARCHAR(128) NOT NULL, author VARCHAR(64), stock INTEGER DEFAULT 1 CHECK (stock 0) ); CREATE TABLE borrow ( sid INTEGER REFERENCES student(sid) ON DELETE CASCADE, bid INTEGER REFERENCES book(bid) ON DELETE RESTRICT, borrow_date DATE DEFAULT CURRENT_DATE, return_date DATE, PRIMARY KEY (sid, bid, borrow_date) );字段类型要贴合业务。学号和书号用 INTEGER 足够不要一上来就 BIGINT姓名和书名用 VARCHAR 控制长度比 TEXT 更利于索引和存储效率。CHECK约束写在建表语句里是实验评分时很加分的点age 限制 0-120、stock 非负都体现完整性设计。外键约束里我故意用了两种行为ON DELETE CASCADE表示删学生时借阅记录一起删符合业务逻辑ON DELETE RESTRICT表示有未还记录时不允许删书防止数据悬空。主键选(sid, bid, borrow_date)因为一个学生对同一本书可以多次借阅以日期区分。3.2 增删改查与查询数据库每条语句都得能解释建完表先造测试数据实验报告里最怕粘贴了一堆 SQL 却说不出每句在干什么。插入语句我一般这样写INSERT INTO student (sid, sname, age, dept) VALUES (1, 张伟, 20, 计算机学院), (2, 李静, 21, 机器人学院); INSERT INTO book (bid, bname, author, stock) VALUES (1001, 数据库系统概论, 王珊, 5), (1002, 深入理解计算机系统, Randal E.Bryant, 3); INSERT INTO borrow (sid, bid, borrow_date) VALUES (1, 1001, CURRENT_DATE), (2, 1002, CURRENT_DATE - 2);CURRENT_DATE 是数据库内置函数比在应用层拼日期字符串更可靠。更新库存这种操作要带 WHERE 条件UPDATE book SET stock stock - 1 WHERE bid 1001;不加 WHERE 会把整个表都改掉课设里犯这个错很有可能因为一次误操作毁掉全部测试数据。删除语句同理先 SELECT 确认再 DELETE 是基本习惯。日常查询数据库最常用的组合是连表加条件SELECT s.sname, b.bname, br.borrow_date FROM borrow br JOIN student s ON br.sid s.sid JOIN book b ON br.bid b.bid WHERE br.return_date IS NULL ORDER BY br.borrow_date DESC;这条查询找到所有还没归还的借阅记录。JOIN 的驱动顺序在数据量大时会影响速度但课设数据量小重点放在逻辑正确。实验里还有一道经典的统计题按学院统计借阅次数SELECT s.dept, COUNT(*) AS borrow_cnt FROM borrow br JOIN student s ON s.sid br.sid GROUP BY s.dept HAVING COUNT(*) 1 ORDER BY borrow_cnt DESC;COUNT 配合 GROUP BY 是必考语法HAVING 在 GROUP BY 之后过滤分组与 WHERE 过滤行有本质区别。3.3 事务别忘提交并发场景下的最大扣分点openGauss 默认每条语句自动提交但课设里借书操作要更新库存、插入借阅记录两件事一起完成必须用事务包起来BEGIN; UPDATE book SET stock stock - 1 WHERE bid 1001; INSERT INTO borrow (sid, bid, borrow_date) VALUES (1, 1001, CURRENT_DATE); COMMIT;如果中间任何一条语句失败可以用 ROLLBACK 把库存回滚避免出现“书借出去了但库存没减”的数据不一致。事务支持隔离级别设置openGauss 默认是 READ COMMITTED可用下面语句调整SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;隔离级别越高并发控制越严格但锁竞争也越明显。实验里能说清“默认读已提交为什么课设选它”就足够。常见翻车场景是开了一个事务忘了 COMMIT后续会话更新同一行就一直卡住这不是死锁而是锁等待。排查时看 pg_stat_activitySELECT pid, state, wait_event_type, query FROM pg_stat_activity WHERE query LIKE %UPDATE book%;做到课设答辩时能指着这个查询说出锁等待现象比背十个概念都管用。3.4 索引和视图让设计文档多两个加分项建索引一般跟着查询走。借阅量大的系统经常按书名查可以在 bname 上建索引CREATE INDEX idx_book_bname ON book(bname);查询时用EXPLAIN能看到执行计划从 Seq Scan 变成 Index ScanEXPLAIN SELECT * FROM book WHERE bname 数据库系统概论;索引不是越多越好每个索引都占用磁盘空间、拖慢写入。课设里建两三个贴合实际业务查询的就够重点在说明“我为什么建它”。视图是另一项加分设计。把多表连接查询存成视图应用层直接查CREATE VIEW v_borrow_info AS SELECT s.sname, b.bname, br.borrow_date, br.return_date FROM borrow br JOIN student s ON br.sid s.sid JOIN book b ON br.bid b.bid;后续查询直接SELECT * FROM v_borrow_info;不用每次重写 JOIN。视图不是物理数据底层表变了它自动跟随适合做权限控制只暴露必要字段给应用层。4. openGauss 存储过程与触发器从实验加分做到课设答辩4.1 存储过程骨架参数、变量和异常处理一次说清openGauss 的存储过程语法和 PostgreSQL 的 PL/pgSQL 一脉相承和 MySQL 的 DECLARE/BEGIN 风格差别很大很多同学照着 MySQL 写必然报语法错。写存储过程最常考的场景就是“借书”这个完整动作CREATE OR REPLACE PROCEDURE borrow_book(p_sid INTEGER, p_bid INTEGER) AS DECLARE v_stock INTEGER; BEGIN SELECT stock INTO v_stock FROM book WHERE bid p_bid; IF v_stock IS NULL THEN RAISE NOTICE 图书不存在书号 %, p_bid; RETURN; END IF; IF v_stock 0 THEN RAISE EXCEPTION 图书库存不足书号 %, p_bid; END IF; INSERT INTO borrow (sid, bid, borrow_date) VALUES (p_sid, p_bid, CURRENT_DATE); UPDATE book SET stock stock - 1 WHERE bid p_bid; RAISE NOTICE 借书成功学生 % 借出图书 %, p_sid, p_bid; END; /执行调用用 CALL不是 SELECTCALL borrow_book(1, 1001);这段代码里几个点值得展开。SELECT INTO是 PL/pgSQL 里给变量赋值的标准写法不能直接写SELECT stock FROM而不接 INTO 变量否则运行时报错。RAISE NOTICE 打印提示信息和控制台的 print 类似能在调试时看到过程走到哪一步。RAISE EXCEPTION 会终止事务并把前面的改动滚回比如先插入了 borrow 又发现库存为负EXCEPTION 会整个回滚。注意我在存储过程结尾没有写 COMMIT。openGauss 允许在 PROCEDURE 里用事务控制但把 COMMIT 留给调用方更安全尤其触发器里面绝对不允许 COMMIT这是血泪经验。课设答辩时被问“为什么不在过程里提交”回答“事务边界由调用方控制便于集成到应用层统一提交”面试观感会好很多。4.2 触发器联动借书后库存自动扣减用触发器实现库存自动扣减是课设里最出彩的一笔。先建一个返回 TRIGGER 的函数再绑定到 borrow 表的 INSERT 事件上CREATE OR REPLACE FUNCTION trg_update_stock() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN UPDATE book SET stock stock - 1 WHERE bid NEW.bid; IF NOT FOUND THEN RAISE EXCEPTION 图书不存在无法借阅; END IF; RETURN NEW; END; $$; CREATE TRIGGER trg_after_borrow AFTER INSERT ON borrow FOR EACH ROW EXECUTE FUNCTION trg_update_stock();这里NEW代表新插入的行NEW.bid直接取出书号。RETURN NEW 在触发器里不是可选项AFTER INSERT 触发器也要返回新行否则 openGauss 会认为事件处理失败这次插入被回滚。这是新手栽得最多的坑后面避坑章节还会展开。用了触发器之后4.1 里的存储过程就可以去掉手动 UPDATE只保留 INSERT库存扣减全部交给触发器。这样业务逻辑集中到数据库层应用层即使忘了更新库存数据依然一致。注意两种做法不能重否则库存扣两次CREATE OR REPLACE PROCEDURE borrow_book_simple(p_sid INTEGER, p_bid INTEGER) AS DECLARE v_stock INTEGER; BEGIN SELECT stock INTO v_stock FROM book WHERE bid p_bid; IF v_stock IS NULL OR v_stock 0 THEN RAISE EXCEPTION 库存不足; END IF; INSERT INTO borrow (sid, bid, borrow_date) VALUES (p_sid, p_bid, CURRENT_DATE); RAISE NOTICE 借书成功; END; /库存负数由建表时的 CHECK (stock 0) 兜底触发器负责扣减存储过程负责业务校验各司其职。4.3 验证方法用 NOTICE 和结果集确认逻辑写完存储过程和触发器验证不是跑一次 CALL 就完事。正确步骤是先查库存、再执行借书、再查库存和借阅表SELECT bid, stock FROM book WHERE bid 1001; CALL borrow_book_simple(1, 1001); SELECT bid, stock FROM book WHERE bid 1001; SELECT * FROM borrow WHERE sid 1 AND bid 1001;如果库存没减优先查触发器是不是建成功了如果提示函数不存在可能是函数名拼写或参数不匹配。调试时可以在函数体里临时加一些 RAISE NOTICE打印中间变量DO $$ DECLARE v_stock INTEGER; BEGIN SELECT stock INTO v_stock FROM book WHERE bid 1001; RAISE NOTICE 当前库存为 %, v_stock; END; $$;DO 块适合快速跑一段匿名代码不用建正式存储过程。我调触发器时习惯把 BEFORE 和 AFTER 分开测先用普通 INSERT 触发一次确认逻辑顺序正确再套进存储过程这样能把问题隔离到一个环节。5. openGauss 实验联调避坑清单五类报错一次说清5.1 远程连不上不是密码错是 listen_addresses 没开现象本地psql或 gsql 连接正常换到另一台机器用-h连接一直超时报错信息类似“No route to host”或“Connection timed out”。原因openGauss 安装后默认监听 127.0.0.1不对外开放。实验室环境里老师给了一台服务器但没改监听配置自己本机自然也连不上。解决用 gs_guc 修改监听地址再在 pg_hba.conf 里放行客户端网段gs_guc set -D /opt/software/data -c listen_addresses 0.0.0.0然后在数据目录的 pg_hba.conf 末尾追加一条host all all 0.0.0.0/0 sha256改完重启gs_ctl restart -D /opt/software/data。注意这是实验内网做法生产环境不能这样全放行。如果只连一台开发机把 0.0.0.0/0 改成具体 IP 更稳妥。5.2 插中文乱码或直接报错初始化编码没指定 UTF8现象插入中文姓名或书名时要么报invalid byte sequence for encoding要么查出来是一串问号。原因初始化数据目录时没指定编码默认值无法正确处理中文。这个问题一旦发生数据库里的数据可能已损坏靠 ALTER 很难完全修复。解决最省事的做法是初始化时加--encodingUTF8上一步命令里已经体现。如果你的库已经踩坑数据还没写入太多时直接重建数据目录如果已经写了大量测试数据用gs_dump导出再导入新库旧库编码不正确导出的内容也可能乱所以我的原则是初始化这一步绝不省。5.3 permission denied for schema publicGRANT 没给到位现象用 lab_user 登录后执行CREATE TABLE报ERROR: permission denied for schema public。原因数据库是 omm 建的owner 不是 lab_userpublic schema 也没有显式授权。openGauss 出于安全考虑没有把 public 完全放开给任意用户。解决在目标数据库里执行授权GRANT ALL ON SCHEMA public TO lab_user;同时可以把数据库 owner 改给 lab_userALTER DATABASE lab_db OWNER TO lab_user;。这一步建议在建库时就做好避免实验做到一半才发现所有表都要重建。5.4 UPDATE 卡住不动锁等待和死锁排查现象第二条 UPDATE 语句一直不返回结果或者报deadlock detected数据库像卡死了一样。原因另一个会话开了事务但没提交UPDATE 同一行时行锁被持住。如果两个会话互相等对方锁住的资源就升级成死锁openGauss 默认会在超时后回滚其中一个事务。解决先查锁和活动会话SELECT pid, state, wait_event_type, query FROM pg_stat_activity WHERE state active;确认哪个 pid 卡住后可以选择终止该会话SELECT pg_terminate_backend(pid);日常写实验语句时要养成提交习惯BEGIN 之后要么 COMMIT 要么 ROLLBACK。避免死锁的另一招是给 INSERT 和 UPDATE 设定固定顺序比如先更新书表再插入借阅表在所有代码里保持这个顺序就能大幅降低互等概率。还可以在会话级设置锁超时SET lock_timeout 2s;超时后自动放弃不会无限等下去。5.5 触发器不生效RETURN NEW 忘写了现象往 borrow 表插入一条记录一切正常去查 book 表发现库存没变。原因触发器函数体里没有写RETURN NEW;或者写成了RETURN NULL;。在 RETURN NULL 的情况下各触发器取决于执行时机AFTER INSERT 触发器返回 NULL 会中断该操作整个 INSERT 被取消但如果你外部操作看起来成功往往是因为函数里没有按预期更新目标行而不是 INSERT 被取消。解决函数拉到底确认RETURN NEW;这是触发器的标准出口。调试时把RAISE NOTICE after insert, bid%, NEW.bid;加在 UPDATE 之前能看到函数有没有进入。还有一个容易忽视的地方执行CREATE TRIGGER前要先删掉旧触发器和旧函数否则同名触发器重复创建时行为会很迷我一般用DROP TRIGGER IF EXISTS trg_after_borrow ON borrow;先清场再重建。6. 交课设前的最后两步备份和查询计划验证6.1 用 gs_dump 留一个后悔药课程设计做到答辩前我习惯先把整个库备份成文件。openGauss 自带 gs_dump和 PostgreSQL 的 pg_dump 用法基本一致gs_dump -h localhost -p 5432 -U lab_user -d lab_db -F c -f lab_db_$(date %Y%m%d).dump-F c指定自定义压缩格式体积小恢复时可以用gs_restore选择性恢复。恢复测试数据时用gs_restore -d lab_db -U lab_user lab_db_20221210.dump这个备份文件就是后悔药实验报告写完、数据被误删都不慌。我每次交课设前都先备份再改最后一批数据至少能保住一个可复现的版本。平时代码改崩了可以 git checkout数据库没有自动版本管理所以备份必须自己做。6.2 用 EXPLAIN 验证索引价值给设计文档一个具体结论课设报告里写了索引答辩时很容易被问“你怎么知道它有效”。用 EXPLAIN ANALYZE 跑一条实际查询看执行计划就能拿真实数字说话。先在未建索引状态下执行EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM book WHERE bname 数据库系统概论;注意看输出里的Seq Scan on book和Execution Time。再建索引之后跑同样的查询计划变成Index Scan using idx_book_bnamecost 明显下降计时也变短。这种对比放进报告就是一手实测结论比写满屏“提高查询效率”有说服力得多。如果实验数据只有几十行普通查询可能全表扫更快索引优势不明显这是正常现象。课设里可以在种子数据里放几千行再验证或者用SET enable_seqscan off强制走索引看看计划形状变化。最后一点经验是索引别建太多两三个贴合的就好要为每个索引写一句业务理由答辩才站得住。我自己的习惯是每次答辩前把备份和 EXPLAIN 输出重新跑一遍确保报告里的数据是当前库的真实状态。这个习惯救过我很多次希望帮到你。本文还有配套的精品资源点击获取