简介这份郑州大学《数据库系统原理实验》报告书面向计算机科学与技术、软件工程等专业学生围绕数据库课程实验提供从理论到实践的完整记录可用于实验预习、过程对照与报告撰写参考。压缩包内仅含1个docx文档大小约3.23MB正文按实验一至实验九组织覆盖DBMS认识、数据库表与索引创建、交互式SQL、视图、完整性控制、安全性控制、事务与并发控制、备份恢复及JDBC连接数据库等模块。文档不仅列出实验目的、内容与学时安排还记录了OpenGauss、gsql等工具的操作过程、问题处理与结果分析便于读者理解关键步骤和排错思路。目前已有1527人学习适合需要完成数据库实验、梳理SQL操作与复盘课程重点的学习者参考。1. 从一份实验报告书说起九次实验的骨架与可复现路径数据库实验最容易翻车的地方从来不是 SQL 写不出来而是换了台机器照着报告敲第一步就卡在 gsql 连不上、omm 用户切不过去、表空间目录权限不对。这份《数据库系统原理实验》报告书的价值不在于能交作业而在于它把九个实验串成了一条能从头跑到尾的主线认识 DBMS 与 gsql 连接、建库建表建索引、交互式 SQL、视图、完整性控制、安全性控制、事务与并发、备份恢复最后用 JDBC 把程序接进来。它适合三类人要按 openGauss 完成课程实验并留下可复现记录的学生手里只有 MySQL 经验想摸清 openGauss 差异的开发者以及需要一套现成教学用 DDL 脚本当脚手架的人。下面按这条主线拆重点放在每一步的顺序、参数和会卡住人的地方。2. openGauss 环境落地三种装法、omm 用户与 gsql 元命令速查环境这一章看着最没技术含量实际上九个实验里有七个的失败都追溯到这里。装法选错、用户切错、端口记错后面所有 SQL 都白写。2.1 三种安装形态的取舍边界报告书里列了三种路子在云主机镜像上直接部署、用虚拟机一键安装包、先装 openEuler 再手动装 openGauss。这三种不是难度递进关系而是适用场景完全不同选错了会在无关的地方耗掉半天。安装方式大致耗时前置准备适合的场景常见绊脚点云主机镜像部署十几分钟一台可远程登录的主机只想尽快跑通 SQL不折腾系统端口没放行本地连不上 26000虚拟机一键安装半小时到一小时虚拟机软件、安装镜像要反复重装、要截图留档内存给太小实例起不来先装系统再装库一到两小时系统镜像、安装包、依赖想顺带练一遍部署流程依赖缺失、目录属主不对我的建议很直接如果目标是完成这九个实验选虚拟机一键安装。原因不在于它简单而在于它把安装用户、数据目录、环境变量一次性配好了你不需要在GAUSSHOME、LD_LIBRARY_PATH、PGDATA这几个变量上纠缠。等你把九个实验都跑完再回头做一次手动安装那时候你才知道每个变量是干什么的顺序反过来只会让人放弃。云主机那条路要额外注意一点数据库端口默认是 26000不是 5432。很多人按 PostgreSQL 的习惯去连 5432然后怀疑服务没起来。实际上gs_om -t status显示 Normal只是你连错了门。2.2 su - omm 与 gs_om -t start 的启动顺序数据库装完之后操作系统上会多出一个专用的安装用户报告书里写的是 omm。这一步的顺序是先切用户再看状态再启动最后连接。顺序错了会得到一堆命令不存在。# 切到数据库安装用户减号不能省它决定是否加载完整登录环境 su - omm # 先看实例状态确认是 Normal 还是 Down别上来就 start gs_om -t status --detail # 启动数据库服务stop 停库restart 重启 gs_om -t start # 启动后确认监听端口26000 是默认主节点端口 ss -lntp | grep 26000su - omm里的那个减号是整个流程里最容易被忽略的字符。带减号表示以登录方式切换会读取 omm 用户的 shell 配置文件把GAUSSHOME、LD_LIBRARY_PATH、PATH全部加载进来不带减号只是切换身份环境变量还是原来那个用户的于是gs_om、gsql全都提示 command not found。我见过不止一次有人在这上面怀疑安装失败重装了三次。gs_om -t status --detail的--detail会多打印每个实例的数据目录和端口排错时比只看一个 Normal 有用得多。gs_om -t start如果卡在启动中先去看数据目录下的日志常见原因是磁盘满或者上次非正常关机留下的残留进程gs_om -t stop之后再 start 一次通常能解决。2.3 gsql 连接串参数逐项拆解gsql 是 openGauss 自带的命令行客户端报告书里那条连接命令是gsql -d postgres -p 26000 -r。参数不多但每个都有实际作用乱填会连到别的库或者根本连不上。# 连接默认库 postgres端口 26000开启行编辑 gsql -d postgres -p 26000 -r # 指定用户和主机连接跨机或非 omm 用户时用 gsql -d db_school -p 26000 -h 127.0.0.1 -U u_student -W # 连接成功后先确认自己在哪个库、哪个用户下 SELECT current_database(), current_user;参数含义使用建议-d目标数据库名首次连 postgres后续换成自己建的库-p端口号默认 26000不是 5432-r开启 readline 行编辑强烈建议加否则上下键翻历史不能用-h主机地址本机可省跨机必须写-U数据库用户名不写则用当前操作系统用户-W交互式输入密码别在命令行直接跟明文密码-W后面不要直接跟密码。命令行里的明文密码会进 shell 历史也会出现在ps的进程列表里这是最常见的安全习惯问题。正确做法是让 gsql 交互式提示输入或者在用户家目录下配置密码文件由客户端自动读取。另一个细节-d postgres里的 postgres 是安装完成后默认生成的数据库它同时也是初始管理员的用户名两个东西同名初学时很容易混淆。连上去之后用\c db_school切换到自己建的库而不是重新敲一遍 gsql这样会话里的变量和设置能保留。2.4 元命令与系统表\l、\dt、\d 与 pg_database 的对照报告书里花了不少篇幅讲反斜杠开头的元命令这是 gsql 的交互式快捷方式本质上是帮你查系统表。把它们和等价的 SQL 对上号排错时才不会只会敲元命令。-- 元命令 \l 的等价查询列出所有数据库 SELECT datname FROM pg_database; -- 元命令 \dt 的等价查询列出当前 schema 下的表 SELECT schemaname, tablename FROM pg_tables WHERE schemaname public; -- 元命令 \di 的等价查询列出索引及其定义 SELECT indexname, indexdef FROM pg_indexes WHERE schemaname public; -- 元命令 \dn 的等价查询列出所有 schema SELECT nspname FROM pg_namespace WHERE nspname NOT LIKE pg_%; -- 元命令 \db 的等价查询列出表空间 SELECT spcname FROM pg_tablespace;这几条 SQL 的意义在于元命令只能在交互式会话里用而 SQL 可以写进脚本、可以配-c参数一次性执行、可以在 JDBC 里跑。写自动化检查脚本时用系统表查询比调元命令靠谱。\d 表名是使用频率最高的一个它输出列名、类型、是否可空、默认值、存储方式、分布列等一整套信息比information_schema.columns直观。看索引用\di看某张表的完整定义用\d 表名两个配合基本能覆盖建表阶段的所有核对需求。还有两个概念要在这里分清schema 和用户不是一回事。schema 是一个命名空间用户是登录身份一个用户可以拥有多个 schema一个 schema 也可以被多个用户以不同权限访问。报告书里那句一个数据库用户所拥有的数据库对象构成 schema说得通俗但在 openGauss 里 schema 并不从属于用户它是独立对象只是创建时默认归属创建者。3. 建库建表建索引表空间、schema 与六张教学表的 DDL 顺序这一章是整份报告书里最能直接抄作业的部分也是顺序最容易搞错的部分。表空间、数据库、schema、表、索引这五层是有严格先后依赖的。3.1 表空间与数据库先有目录还是先有库创建表空间的语句长这样但它的前提条件是目录已经存在并且属主正确。这一点报告书里只写了后边是空目录一句带过实际执行时这是第一个会报错的地方。-- 表空间指向的目录必须事先存在且属主为数据库安装用户 CREATE TABLESPACE tpcds_local RELATIVE LOCATION tablespace/tablespace_1; -- 建库时挂到刚建的表空间上不指定则用默认表空间 CREATE DATABASE db_sc WITH TABLESPACE tpcds_local; -- 库改名报告书里从 db_sc 改成 db_school 的那一步 ALTER DATABASE db_sc RENAME TO db_school;RELATIVE LOCATION后面的路径是相对于实例数据目录的不是相对于当前工作目录。这几个字看漏了你就会在$GAUSSHOME下面到处找那个 tablespace 文件夹。手懒的话可以直接用LOCATION写绝对路径语义更直白出错时也更容易定位。目录没建就执行 CREATE TABLESPACE报错信息通常指向路径不存在或无权限目录建了但属主是 root报错会变成权限不足。两种情况都要在 omm 用户下先mkdir -p再chmod或者干脆用 omm 身份建目录。这个坑在实验二里出现频率极高因为大部分人是在 root 下顺手把目录建了。ALTER DATABASE ... RENAME TO要注意一点改名时不能有其他会话连着这个库。如果有连接残留会提示数据库正在被访问。做法是先SELECT datname, count(*) FROM pg_stat_activity GROUP BY 1看一眼把空闲连接清掉再改。3.2 schema、用户、角色三者的权限边界报告书在实验一里用了一段仓库管理员做比喻解释用户和角色这个比喻是对的但落到 SQL 上还要补一层 schema 的关系。-- 建 schema归属当前用户 CREATE SCHEMA book_eg; -- 在指定 schema 下建表表名必须带 schema 前缀或先 SET search_path CREATE TABLE book_eg.teachers_demo ( tno CHAR(20) PRIMARY KEY, tname CHAR(20) NOT NULL ); -- 建用户会自动创建一个同名 schema CREATE USER u_student PASSWORD Abc123456; -- 建角色角色是权限集合不用于登录 CREATE ROLE r_readonly; -- 把角色授予用户用户即拥有该角色的全部权限 GRANT r_readonly TO u_student; -- 级联删除 schema 及其下所有对象慎用 DROP SCHEMA book_eg CASCADE;CREATE USER和CREATE ROLE的区别在于前者默认带 LOGIN 属性后者默认不带。openGauss 里创建用户时会顺带建一个同名 schema这个行为很多人不知道结果发现自己建完用户\dn里多出来一个 schema还以为是谁动过库。密码是另一个高频报错点。openGauss 对密码有复杂度要求长度、大小写、数字、特殊字符至少满足若干项且不能与用户名相同。报错信息里会明确写出不满足哪一条照着改就行别反复试同一个弱密码。DROP SCHEMA ... CASCADE这条要单独说。CASCADE 会把它下面所有表、视图、函数一起删掉不弹任何确认。报告书里用它演示级联删除没问题但在真实库里执行前先用\dt book_eg.*确认一下里面到底有什么这个动作花不了十秒。3.3 六张教学表的建表语句与约束落位教师、院系、学生、课程、选课、教学六张表之间存在循环引用教师表里的院系号指向院系表院系表里的系主任工号又指回教师表。直接按业务含义顺序建无论先建哪张都会因外键找不到被引用列而失败。步骤操作原因1建 Teachers暂不加 Dno 外键打破循环依赖2建 Departments带 Dheadno 外键此时 Teachers 已存在3ALTER Teachers 补 Dno 外键回填引用关系4建 Students、Courses单向依赖 Departments5建 SC、Teaches依赖前面所有表-- 第一步教师表先建出来Dno 只留列不加外键 CREATE TABLE Teachers ( Tno CHAR(20) PRIMARY KEY, Tname CHAR(20) NOT NULL, Sex CHAR(20) CHECK (Sex IN (男, 女)), Birthday DATE, Title CHAR(20), Dno CHAR(20) ); -- 第二步院系表带外键引用教师表的主键 CREATE TABLE Departments ( Dno CHAR(20) PRIMARY KEY, Dname CHAR(20), Dheadno CHAR(20), CONSTRAINT fk_dept_head FOREIGN KEY (Dheadno) REFERENCES Teachers(Tno) ); -- 第三步回填教师表的外键打破循环 ALTER TABLE Teachers ADD CONSTRAINT fk_teacher_dept FOREIGN KEY (Dno) REFERENCES Departments(Dno); -- 第四步学生与课程表 CREATE TABLE Students ( Sno CHAR(20) PRIMARY KEY, Sname CHAR(20) NOT NULL, Sex CHAR(20) CHECK (Sex IN (男, 女)), Birthday DATE, Enrollyear CHAR(20), Speciality CHAR(20), Dno CHAR(20), CONSTRAINT fk_stu_dept FOREIGN KEY (Dno) REFERENCES Departments(Dno) ); CREATE TABLE Courses ( Cno CHAR(20) PRIMARY KEY, Cname CHAR(20) NOT NULL, Period SMALLINT, Credit SMALLINT ); -- 第五步选课表与教学表组合主键更贴合业务 CREATE TABLE SC ( Sno CHAR(20), Cno CHAR(20), Grade SMALLINT CHECK (Grade BETWEEN 0 AND 100), PRIMARY KEY (Sno, Cno), FOREIGN KEY (Sno) REFERENCES Students(Sno), FOREIGN KEY (Cno) REFERENCES Courses(Cno) ); CREATE TABLE Teaches ( Tno CHAR(20), Cno CHAR(20), Term CHAR(20), PRIMARY KEY (Tno, Cno, Term), FOREIGN KEY (Tno) REFERENCES Teachers(Tno), FOREIGN KEY (Cno) REFERENCES Courses(Cno) );这段脚本的核心是外键的依赖顺序不是语法本身。第二步和第三步的拆分就是处理循环引用的标准手法先建一张不带闭环外键的表把闭环的另一半建出来再 ALTER 回去补上。任何多表互相引用的场景都适用这个套路不限于教学表。CHECK (Sex IN (男, 女))比报告书里的CHECK (Sex男 OR Sex女)更短语义等价。带空值的列要注意三值逻辑CHECK 约束在结果为 NULL 时视为通过所以如果 Sex 允许为空插 NULL 不会被拦。要真正强制非空得额外加 NOT NULL。SC 表的主键我用的是(Sno, Cno)组合主键而不是加一个自增 id。这是教学场景下的合理选择一个学生一门课只选一次组合主键天然去重。真实业务里要考虑重修、多次选课那就得引入学期字段把主键扩成三列。选哪种取决于你的业务假设写报告时把假设写清楚比写对更重要。列级约束和表级约束的区别报告书里用了一大段解释落到实操上只有一条判断标准这个约束是否涉及多个列。涉及多列比如组合主键、复合外键、跨列 CHECK必须写表级只涉及单列写列级更紧凑。性能上没有区别纯粹是可读性问题。3.4 索引创建、自动索引与 \di 验证建索引这一步很多人建完就完事从不验证到底生效没有。验证方法很简单一条 SQL 加一个元命令。-- 在学生表的院系号上建普通索引 CREATE INDEX student_dept ON Students(Dno); -- 查看当前 schema 下所有索引 -- \di -- 用系统表确认索引是否真的建上了 SELECT indexname, indexdef FROM pg_indexes WHERE tablename students; -- 看某条查询是否走索引重点看 Seq Scan 还是 Index Scan EXPLAIN SELECT * FROM Students WHERE Dno D01;主键和唯一约束会自动创建索引所以\di里看到的索引数量一定比手建的多这是正常现象。判断某个索引有没有必要存在看两点这一列是否经常出现在 WHERE 或 JOIN 条件里以及这一列的取值重复度高不高。在性别这种只有两个值的列上建索引优化器大概率直接忽略走了还不如不走。EXPLAIN输出的Seq Scan表示全表扫描Index Scan表示走索引。教学表数据量只有几十行优化器很可能认为全表扫描更快这是正常的不代表索引失效。想验证索引效果塞几千行数据进去再看执行计划报告里如果写了索引提升了查询效率最好附上这个对比。删索引的语句是DROP INDEX 索引名注意不需要带表名。同一张表上建太多索引会拖慢插入和更新因为每次写操作都要维护索引结构。教学场景无所谓真实场景下每加一个索引都要能说出它服务于哪条查询。4. 交互式 SQL 与视图从多表连接到一个可更新视图的判断SQL 这一章覆盖实验三和实验四表面上是最熟悉的内容但 openGauss 在类型转换和视图可更新性上的行为和 MySQL 有差异照搬经验会踩坑。4.1 单表查询到三表连接的写法收敛查询需求一旦从单表跨到多表写法就有多种。以查某位学生选修的课程名和成绩为例内连接是最直观的一种。-- 三表内连接学生 → 选课 → 课程 SELECT s.Sname, c.Cname, sc.Grade FROM Students s JOIN SC sc ON s.Sno sc.Sno JOIN Courses c ON sc.Cno c.Cno WHERE s.Sno S001 ORDER BY sc.Grade DESC; -- 等价写法用 EXISTS 表达存在选课记录 SELECT c.Cname FROM Courses c WHERE EXISTS ( SELECT 1 FROM SC sc WHERE sc.Cno c.Cno AND sc.Sno S001 );两种写法的结果集不完全一样第一种会把学生姓名和成绩一起带出来一行对应一条选课记录第二种只输出课程名去掉了重复。选择哪种取决于你要的列不是哪种更高级。内连接会丢掉没有选课的学生。如果需求是列出所有学生及其选课数没选课的显示 0就得换成 LEFT JOIN 配合 COALESCE。-- 左连接保留没有选课记录的学生NULL 转 0 SELECT s.Sno, s.Sname, COALESCE(COUNT(sc.Cno), 0) AS course_cnt FROM Students s LEFT JOIN SC sc ON s.Sno sc.Sno GROUP BY s.Sno, s.Sname ORDER BY course_cnt DESC;GROUP BY后面必须列出所有非聚合的输出列这是标准 SQL 的要求openGauss 严格执行。MySQL 在宽松模式下允许省略从 MySQL 迁过来的写法会直接报错。COALESCE用来把 NULL 转成 0不用它的话没选课的学生那一行会显示 NULL 而不是 0报告里的统计表就对不上。4.2 聚合与分组GROUP BY 与 HAVING 的边界WHERE 和 HAVING 的分工是个老问题判断标准只有一条过滤发生在分组前还是分组后。分组前能确定的用 WHERE依赖聚合结果的用 HAVING。-- 统计每门课的选课人数与平均分只保留人数超过 2 的课 SELECT c.Cno, c.Cname, COUNT(sc.Sno) AS stu_cnt, ROUND(AVG(sc.Grade), 2) AS avg_grade FROM Courses c JOIN SC sc ON c.Cno sc.Cno WHERE sc.Grade IS NOT NULL -- 分组前过滤去掉没有成绩的记录 GROUP BY c.Cno, c.Cname HAVING COUNT(sc.Sno) 2 -- 分组后过滤按人数筛 ORDER BY avg_grade DESC;WHERE sc.Grade IS NOT NULL在分组前就干掉了空成绩避免AVG把 NULL 算进去影响分母。HAVING COUNT(sc.Sno) 2依赖聚合结果只能放在 HAVING。把 HAVING 的条件挪到 WHERE 会直接报错因为 WHERE 执行时 COUNT 还没有值。ROUND(AVG(...), 2)里的第二个参数是保留位数。openGauss 的 AVG 返回的是数值类型不加 ROUND 会输出一长串小数放进报告表格里很难看。这类细节不影响正确性但影响可读性。还有一个容易被忽略的点COUNT(*) 和 COUNT(列名) 结果可能不同。前者数行数后者跳过 NULL。统计选课人数时如果用 COUNT(sc.Grade)没成绩的记录不会被计入得到的数字比实际选课人数少。写统计 SQL 时想清楚要数的是记录数还是非空值数。4.3 视图创建、查询改写与可更新性判断视图在报告书里是实验四的全部内容。创建视图本身没有难度难的是判断一个视图能不能 UPDATE。判断标准不是靠猜是有明确规则的。-- 建一个学生成绩视图隐藏出生日期等无关列 CREATE VIEW v_student_score AS SELECT s.Sno, s.Sname, c.Cname, sc.Grade FROM Students s JOIN SC sc ON s.Sno sc.Sno JOIN Courses c ON sc.Cno c.Cno; -- 查看视图定义确认列来源 -- \d v_student_score -- 尝试更新视图多表连接的视图通常会失败 UPDATE v_student_score SET Grade 90 WHERE Sno S001;上面这条 UPDATE 大概率报错原因是视图涉及三张表连接数据库无法确定要改哪张基表。可更新视图的成立条件大致是只来自一张表、不含聚合函数、不含 DISTINCT、不含 GROUP BY 或 HAVING、不含 UNION、不含子查询里的聚合。满足这些条件的视图叫简单视图可以当成表来增删改否则就是只读视图。需要可更新时把视图收敛到单表-- 单表视图只暴露必要列具备可更新性 CREATE VIEW v_student_basic AS SELECT Sno, Sname, Sex, Dno FROM Students WHERE Dno IS NOT NULL; -- 这条可以正常执行会作用到 Students 基表 UPDATE v_student_basic SET Sname 张三 WHERE Sno S001;带WHERE条件的单表视图依然可更新只是更新后的行如果不再满足视图条件会从视图里消失。这不算 bug是视图的语义决定的。报告里如果要做这个实验建议把这个现象记录下来比单纯贴一句更新成功有价值得多。4.4 用视图做列级抽象与权限收口视图的第二个用途是权限控制。数据库的 GRANT 是表级和列级的但列级授权写起来繁琐用视图包一层更清爽。-- 面向查询角色的视图屏蔽出生日期和入学年份 CREATE VIEW v_student_public AS SELECT Sno, Sname, Speciality, Dno FROM Students; -- 只授予视图的查询权限不授予基表权限 GRANT SELECT ON v_student_public TO r_readonly; -- 验证以只读角色连接后只能看到视图里的列 SELECT * FROM v_student_public LIMIT 5;这个做法的关键在于被授权者拿不到基表的 SELECT 权限只能通过视图访问于是列级隔离就实现了。要注意视图的属主必须有基表权限否则视图建出来查不了。视图默认以属主权限执行类似 SECURITY DEFINER 的效果这一点和某些数据库的 definer/invoker 选项不同跨用户使用前先确认属性。视图还有一层价值是隔离表结构变更。基表加列、拆表的时候只要视图的输出列保持不变上层查询就不用改。教学场景体会不到这个好处但这是视图在工程里最常被使用的理由。5. 完整性、安全性、事务与备份四类控制实验的排查清单实验五到实验八这四类控制单独看每条语句都很短组合起来才见真章。这一章按怎么做 为什么推进最后把五条实际踩过的坑整理成现象、原因、解决三段式。5.1 约束追加ALTER TABLE 与外键的先后顺序建表时没加约束后期补加是常态。补加约束的语句不复杂复杂的是表里已经有数据的时候。-- 给已有表补加外键前提是被引用列必须是主键或唯一键 ALTER TABLE Students ADD CONSTRAINT fk_stu_dept FOREIGN KEY (Dno) REFERENCES Departments(Dno); -- 补加 CHECK 约束注意已有数据必须全部满足否则整条语句失败 ALTER TABLE SC ADD CONSTRAINT ck_grade CHECK (Grade BETWEEN 0 AND 100); -- 删除约束 ALTER TABLE SC DROP CONSTRAINT ck_grade; -- 外键的删除行为可以单独指定 ALTER TABLE SC DROP CONSTRAINT sc_sno_fkey, ADD CONSTRAINT sc_sno_fkey FOREIGN KEY (Sno) REFERENCES Students(Sno) ON DELETE CASCADE;补加外键时最常见的报错是被引用列没有唯一约束。外键的语义是我的值必须出现在对方的唯一记录里对方列如果不唯一这个语义就成立不了。报错信息里会明确写出找不到匹配的唯一约束看到这句就去检查对方表的主键定义。CHECK 约束补加失败八成是表里已有脏数据。比如成绩列里存了一个 120加BETWEEN 0 AND 100的约束就会整条语句回滚。解决办法是先查出来改掉SELECT * FROM SC WHERE Grade NOT BETWEEN 0 AND 100;改完再加约束。这个顺序不能反ALTER TABLE 是原子的一条不满足就全部不生效。ON DELETE CASCADE是把双刃剑。删学生时自动删掉他的选课记录业务上合理但如果 SC 表还被别的表引用级联会一路传下去。教学库里无所谓生产库里加 CASCADE 之前先用SELECT把会被删掉的行数数出来。5.2 授权与角色GRANT 的作用域与回收安全性控制实验的核心是 GRANT 和 REVOKE。语法简单作用域容易搞混。-- 建只读角色与读写角色 CREATE ROLE r_readonly; CREATE ROLE r_readwrite; -- 表级授权查询与插入 GRANT SELECT ON Students TO r_readonly; GRANT SELECT, INSERT, UPDATE ON SC TO r_readwrite; -- 把角色授予具体用户用户继承角色的全部权限 GRANT r_readonly TO u_student; -- 回收权限回收角色用 REVOKE 角色名 REVOKE INSERT ON SC FROM r_readwrite; -- 查看某个用户最终拥有的权限 SELECT grantee, table_name, privilege_type FROM information_schema.table_privileges WHERE grantee u_student;GRANT 权限 ON 表 TO 角色和GRANT 角色 TO 用户是两条完全不同的语句前者授对象权限后者授角色成员关系。初学者经常把两者混着写然后困惑为什么权限没生效。记法很简单ON 后面跟表就是对象权限TO 后面跟用户就是角色分配。权限回收之后已存在的连接不会立刻生效需要重新连接或者执行SET ROLE刷新。这个行为和很多数据库一致但在实验里容易被当成REVOKE 没起作用。验证时最好退出重连一次再查。还有一个细节information_schema.table_privileges只显示直接授予的权限通过角色继承来的权限要查role_table_grants。查权限时两个视图对照着看才不会漏。5.3 事务与并发两条会话验证隔离与锁等待事务和并发控制这一节光看语句看不出效果必须开两个会话对着做。这是整份报告书里唯一必须双窗口操作的实验。-- 会话 A开事务改一行但不提交 BEGIN; UPDATE SC SET Grade 95 WHERE Sno S001 AND Cno C001; -- 此处先不 COMMIT保持事务打开 -- 会话 B改同一行观察是否阻塞 BEGIN; UPDATE SC SET Grade 60 WHERE Sno S001 AND Cno C001; -- 此处会一直等待直到会话 A 提交或回滚 -- 会话 A提交 COMMIT; -- 会话 B 随即返回改为 60 生效 COMMIT;默认隔离级别是 READ COMMITTED会话 B 会一直等会话 A 释放行锁。如果不想无限等可以在会话 B 里先设一个超时-- 超过 5 秒拿不到锁就报错返回避免会话挂死 SET lock_timeout 5s;排查锁等待要看两个视图。pg_stat_activity看有哪些会话在跑、状态是什么pg_locks看锁的持有和等待关系。state列显示active且wait_event不为空基本就是卡在等锁。找到阻塞的会话后可以用pg_terminate_backend(pid)干掉但这是最后手段先确认对方是不是有未提交的事务。死锁在实验里不容易复现因为需要两个会话以相反顺序锁两行。构造方法A 锁第一行B 锁第二行然后 A 去锁第二行B 去锁第一行数据库检测到环路会主动回滚其中一个报错信息里会写明死锁。这个实验做一次比看十页教材印象深。5.4 备份与恢复gs_dump 的逻辑备份与恢复顺序备份恢复实验用的是 gs_dump这是逻辑备份工具导出的是 SQL 文本或自定义格式不是物理文件拷贝。# 切到安装用户执行输出目录必须 omm 可写 su - omm # 导出整个库为纯 SQL 文本 gs_dump -p 26000 -d db_school -f /home/omm/backup/db_school.sql -F p # 导出为自定义格式支持并行恢复和选择性还原 gs_dump -p 26000 -d db_school -f /home/omm/backup/db_school.dmp -F c # 恢复纯 SQL 用 gsql 直接灌 gsql -p 26000 -d db_school_new -f /home/omm/backup/db_school.sql # 恢复自定义格式用 gs_restore gs_restore -p 26000 -d db_school_new /home/omm/backup/db_school.dmp-F p是 plain输出纯文本 SQL-F c是 custom输出压缩的自定义格式。前者能直接用编辑器打开看内容适合教学和排查后者支持-t只还原某张表适合真实场景。选哪种取决于你要不要挑着恢复。恢复的目标库必须事先存在gs_dump 不会帮你建库。这是新手最容易卡住的一步拿着备份文件执行恢复报错说数据库不存在然后怀疑备份坏了。正确顺序是先CREATE DATABASE db_school_new;再执行恢复。输出目录的属主问题在实验八里几乎必现。用 root 建了/home/omm/backup切到 omm 后写不进去报权限拒绝。解决就是chown omm:omm /home/omm/backup一行命令的事但不知道的话会绕很久。5.5 五条踩坑记录现象、原因、解决下面五条是这类实验里复现率最高的失败按现象、原因、解决写清楚遇到时对号入座即可。第一条建外键报there is no unique constraint matching given keys。现象是 ALTER TABLE 加外键时直接失败提示找不到匹配的唯一约束。原因是被引用的列上既没有主键也没有唯一索引外键的语义要求对方唯一。解决办法是给被引用列补上 PRIMARY KEY 或 UNIQUE 约束或者改引用对方已有主键的另一列。第二条ALTER TABLE 加 CHECK 约束报数据违规。现象是语句失败提示已有行不满足新约束。原因是表里存在历史脏数据比如成绩超范围、性别写成未知。解决办法是先查出违规行清洗或修正后再执行 ALTER顺序不能反。第三条新建用户连接报认证失败。现象是 gsql 用新用户连库提示 password authentication failed。原因通常是两处密码不满足复杂度要求长度、字符类型、不能与用户名相同或者连接来源没有被放行。解决办法是重建用户并用合规密码同时确认服务端的访问控制配置允许该来源连接。第四条两个会话卡住不动界面无响应。现象是会话 B 的 UPDATE 一直不返回也不报错。原因是会话 A 持有行锁且未提交事务。解决办法是在会话 B 设lock_timeout再去pg_locks里找到阻塞的 pid让会话 A 提交或回滚。第五条gs_dump 备份文件为空或权限拒绝。现象是导出命令执行完目标文件是 0 字节或者干脆报错。原因是输出目录属主不是 omm或者磁盘写满。解决办法是先chown再重跑跑完用ls -lh确认文件大小别假设成功。6. JDBC 打通最后一公里驱动、连接串与一次可复现的验证实验九是把前面八个实验的成果接进程序很多人到这一步才发现前面建的库连不上问题往往出在驱动和连接串上。openGauss 的 JDBC 驱动类名是org.opengauss.Driver不是 PostgreSQL 那个org.postgresql.Driver用错类名会直接抛 ClassNotFound。驱动包从 openGauss 的官方发布渠道下载加进工程依赖即可。// 连接串关键参数协议头、主机、端口、库名 String url jdbc:opengauss://127.0.0.1:26000/db_school; String user u_student; String pass Abc123456; try (Connection conn DriverManager.getConnection(url, user, pass); PreparedStatement ps conn.prepareStatement( SELECT Sname, Grade FROM SC JOIN Students USING (Sno) WHERE Cno ?)) { ps.setString(1, C001); // 参数化别拼字符串 try (ResultSet rs ps.executeQuery()) { while (rs.next()) { System.out.println(rs.getString(1) - rs.getInt(2)); } } }连接串参数作用常见误设jdbc:opengauss://协议前缀写成jdbc:postgresql://导致驱动不认端口26000服务端口沿用 5432 连不上?currentSchema指定默认 schema不设则用 search_path找不到表?connectTimeout连接超时毫秒数不设则跨机时长时间挂起PreparedStatement加?占位符这两件事一起说它不只是防注入还能让数据库复用执行计划。字符串拼接的 SQL 每次文本都不同数据库要重新解析参数化之后同样的语句结构可以命中缓存。我最早做这个实验时把连接串写成jdbc:postgresql://本地跑通了因为工程里恰好还留着 PostgreSQL 驱动结果换到只装了 openGauss 驱动的机器上就崩了排查了半天才发现是协议头的问题。从那以后我每次搭 JDBC 环境都强制先单独跑一个只做SELECT 1的最小验证确认驱动加载、连接串、账号密码三件事全通再往里加业务 SQL。希望帮到你。本文还有配套的精品资源点击获取 
长河君 · 2026/11/4 3:07:39 · 阅读 1.2k openGauss数据库实验九讲：从gsql连接到JDBC可复现路径
简介这份郑州大学《数据库系统原理实验》报告书面向计算机科学与技术、软件工程等专业学生围绕数据库课程实验提供从理论到实践的完整记录可用于实验预习、过程对照与报告撰写参考。压缩包内仅含1个docx文档大小约3.23MB正文按实验一至实验九组织覆盖DBMS认识、数据库表与索引创建、交互式SQL、视图、完整性控制、安全性控制、事务与并发控制、备份恢复及JDBC连接数据库等模块。文档不仅列出实验目的、内容与学时安排还记录了OpenGauss、gsql等工具的操作过程、问题处理与结果分析便于读者理解关键步骤和排错思路。目前已有1527人学习适合需要完成数据库实验、梳理SQL操作与复盘课程重点的学习者参考。1. 从一份实验报告书说起九次实验的骨架与可复现路径数据库实验最容易翻车的地方从来不是 SQL 写不出来而是换了台机器照着报告敲第一步就卡在 gsql 连不上、omm 用户切不过去、表空间目录权限不对。这份《数据库系统原理实验》报告书的价值不在于能交作业而在于它把九个实验串成了一条能从头跑到尾的主线认识 DBMS 与 gsql 连接、建库建表建索引、交互式 SQL、视图、完整性控制、安全性控制、事务与并发、备份恢复最后用 JDBC 把程序接进来。它适合三类人要按 openGauss 完成课程实验并留下可复现记录的学生手里只有 MySQL 经验想摸清 openGauss 差异的开发者以及需要一套现成教学用 DDL 脚本当脚手架的人。下面按这条主线拆重点放在每一步的顺序、参数和会卡住人的地方。2. openGauss 环境落地三种装法、omm 用户与 gsql 元命令速查环境这一章看着最没技术含量实际上九个实验里有七个的失败都追溯到这里。装法选错、用户切错、端口记错后面所有 SQL 都白写。2.1 三种安装形态的取舍边界报告书里列了三种路子在云主机镜像上直接部署、用虚拟机一键安装包、先装 openEuler 再手动装 openGauss。这三种不是难度递进关系而是适用场景完全不同选错了会在无关的地方耗掉半天。安装方式大致耗时前置准备适合的场景常见绊脚点云主机镜像部署十几分钟一台可远程登录的主机只想尽快跑通 SQL不折腾系统端口没放行本地连不上 26000虚拟机一键安装半小时到一小时虚拟机软件、安装镜像要反复重装、要截图留档内存给太小实例起不来先装系统再装库一到两小时系统镜像、安装包、依赖想顺带练一遍部署流程依赖缺失、目录属主不对我的建议很直接如果目标是完成这九个实验选虚拟机一键安装。原因不在于它简单而在于它把安装用户、数据目录、环境变量一次性配好了你不需要在GAUSSHOME、LD_LIBRARY_PATH、PGDATA这几个变量上纠缠。等你把九个实验都跑完再回头做一次手动安装那时候你才知道每个变量是干什么的顺序反过来只会让人放弃。云主机那条路要额外注意一点数据库端口默认是 26000不是 5432。很多人按 PostgreSQL 的习惯去连 5432然后怀疑服务没起来。实际上gs_om -t status显示 Normal只是你连错了门。2.2 su - omm 与 gs_om -t start 的启动顺序数据库装完之后操作系统上会多出一个专用的安装用户报告书里写的是 omm。这一步的顺序是先切用户再看状态再启动最后连接。顺序错了会得到一堆命令不存在。# 切到数据库安装用户减号不能省它决定是否加载完整登录环境 su - omm # 先看实例状态确认是 Normal 还是 Down别上来就 start gs_om -t status --detail # 启动数据库服务stop 停库restart 重启 gs_om -t start # 启动后确认监听端口26000 是默认主节点端口 ss -lntp | grep 26000su - omm里的那个减号是整个流程里最容易被忽略的字符。带减号表示以登录方式切换会读取 omm 用户的 shell 配置文件把GAUSSHOME、LD_LIBRARY_PATH、PATH全部加载进来不带减号只是切换身份环境变量还是原来那个用户的于是gs_om、gsql全都提示 command not found。我见过不止一次有人在这上面怀疑安装失败重装了三次。gs_om -t status --detail的--detail会多打印每个实例的数据目录和端口排错时比只看一个 Normal 有用得多。gs_om -t start如果卡在启动中先去看数据目录下的日志常见原因是磁盘满或者上次非正常关机留下的残留进程gs_om -t stop之后再 start 一次通常能解决。2.3 gsql 连接串参数逐项拆解gsql 是 openGauss 自带的命令行客户端报告书里那条连接命令是gsql -d postgres -p 26000 -r。参数不多但每个都有实际作用乱填会连到别的库或者根本连不上。# 连接默认库 postgres端口 26000开启行编辑 gsql -d postgres -p 26000 -r # 指定用户和主机连接跨机或非 omm 用户时用 gsql -d db_school -p 26000 -h 127.0.0.1 -U u_student -W # 连接成功后先确认自己在哪个库、哪个用户下 SELECT current_database(), current_user;参数含义使用建议-d目标数据库名首次连 postgres后续换成自己建的库-p端口号默认 26000不是 5432-r开启 readline 行编辑强烈建议加否则上下键翻历史不能用-h主机地址本机可省跨机必须写-U数据库用户名不写则用当前操作系统用户-W交互式输入密码别在命令行直接跟明文密码-W后面不要直接跟密码。命令行里的明文密码会进 shell 历史也会出现在ps的进程列表里这是最常见的安全习惯问题。正确做法是让 gsql 交互式提示输入或者在用户家目录下配置密码文件由客户端自动读取。另一个细节-d postgres里的 postgres 是安装完成后默认生成的数据库它同时也是初始管理员的用户名两个东西同名初学时很容易混淆。连上去之后用\c db_school切换到自己建的库而不是重新敲一遍 gsql这样会话里的变量和设置能保留。2.4 元命令与系统表\l、\dt、\d 与 pg_database 的对照报告书里花了不少篇幅讲反斜杠开头的元命令这是 gsql 的交互式快捷方式本质上是帮你查系统表。把它们和等价的 SQL 对上号排错时才不会只会敲元命令。-- 元命令 \l 的等价查询列出所有数据库 SELECT datname FROM pg_database; -- 元命令 \dt 的等价查询列出当前 schema 下的表 SELECT schemaname, tablename FROM pg_tables WHERE schemaname public; -- 元命令 \di 的等价查询列出索引及其定义 SELECT indexname, indexdef FROM pg_indexes WHERE schemaname public; -- 元命令 \dn 的等价查询列出所有 schema SELECT nspname FROM pg_namespace WHERE nspname NOT LIKE pg_%; -- 元命令 \db 的等价查询列出表空间 SELECT spcname FROM pg_tablespace;这几条 SQL 的意义在于元命令只能在交互式会话里用而 SQL 可以写进脚本、可以配-c参数一次性执行、可以在 JDBC 里跑。写自动化检查脚本时用系统表查询比调元命令靠谱。\d 表名是使用频率最高的一个它输出列名、类型、是否可空、默认值、存储方式、分布列等一整套信息比information_schema.columns直观。看索引用\di看某张表的完整定义用\d 表名两个配合基本能覆盖建表阶段的所有核对需求。还有两个概念要在这里分清schema 和用户不是一回事。schema 是一个命名空间用户是登录身份一个用户可以拥有多个 schema一个 schema 也可以被多个用户以不同权限访问。报告书里那句一个数据库用户所拥有的数据库对象构成 schema说得通俗但在 openGauss 里 schema 并不从属于用户它是独立对象只是创建时默认归属创建者。3. 建库建表建索引表空间、schema 与六张教学表的 DDL 顺序这一章是整份报告书里最能直接抄作业的部分也是顺序最容易搞错的部分。表空间、数据库、schema、表、索引这五层是有严格先后依赖的。3.1 表空间与数据库先有目录还是先有库创建表空间的语句长这样但它的前提条件是目录已经存在并且属主正确。这一点报告书里只写了后边是空目录一句带过实际执行时这是第一个会报错的地方。-- 表空间指向的目录必须事先存在且属主为数据库安装用户 CREATE TABLESPACE tpcds_local RELATIVE LOCATION tablespace/tablespace_1; -- 建库时挂到刚建的表空间上不指定则用默认表空间 CREATE DATABASE db_sc WITH TABLESPACE tpcds_local; -- 库改名报告书里从 db_sc 改成 db_school 的那一步 ALTER DATABASE db_sc RENAME TO db_school;RELATIVE LOCATION后面的路径是相对于实例数据目录的不是相对于当前工作目录。这几个字看漏了你就会在$GAUSSHOME下面到处找那个 tablespace 文件夹。手懒的话可以直接用LOCATION写绝对路径语义更直白出错时也更容易定位。目录没建就执行 CREATE TABLESPACE报错信息通常指向路径不存在或无权限目录建了但属主是 root报错会变成权限不足。两种情况都要在 omm 用户下先mkdir -p再chmod或者干脆用 omm 身份建目录。这个坑在实验二里出现频率极高因为大部分人是在 root 下顺手把目录建了。ALTER DATABASE ... RENAME TO要注意一点改名时不能有其他会话连着这个库。如果有连接残留会提示数据库正在被访问。做法是先SELECT datname, count(*) FROM pg_stat_activity GROUP BY 1看一眼把空闲连接清掉再改。3.2 schema、用户、角色三者的权限边界报告书在实验一里用了一段仓库管理员做比喻解释用户和角色这个比喻是对的但落到 SQL 上还要补一层 schema 的关系。-- 建 schema归属当前用户 CREATE SCHEMA book_eg; -- 在指定 schema 下建表表名必须带 schema 前缀或先 SET search_path CREATE TABLE book_eg.teachers_demo ( tno CHAR(20) PRIMARY KEY, tname CHAR(20) NOT NULL ); -- 建用户会自动创建一个同名 schema CREATE USER u_student PASSWORD Abc123456; -- 建角色角色是权限集合不用于登录 CREATE ROLE r_readonly; -- 把角色授予用户用户即拥有该角色的全部权限 GRANT r_readonly TO u_student; -- 级联删除 schema 及其下所有对象慎用 DROP SCHEMA book_eg CASCADE;CREATE USER和CREATE ROLE的区别在于前者默认带 LOGIN 属性后者默认不带。openGauss 里创建用户时会顺带建一个同名 schema这个行为很多人不知道结果发现自己建完用户\dn里多出来一个 schema还以为是谁动过库。密码是另一个高频报错点。openGauss 对密码有复杂度要求长度、大小写、数字、特殊字符至少满足若干项且不能与用户名相同。报错信息里会明确写出不满足哪一条照着改就行别反复试同一个弱密码。DROP SCHEMA ... CASCADE这条要单独说。CASCADE 会把它下面所有表、视图、函数一起删掉不弹任何确认。报告书里用它演示级联删除没问题但在真实库里执行前先用\dt book_eg.*确认一下里面到底有什么这个动作花不了十秒。3.3 六张教学表的建表语句与约束落位教师、院系、学生、课程、选课、教学六张表之间存在循环引用教师表里的院系号指向院系表院系表里的系主任工号又指回教师表。直接按业务含义顺序建无论先建哪张都会因外键找不到被引用列而失败。步骤操作原因1建 Teachers暂不加 Dno 外键打破循环依赖2建 Departments带 Dheadno 外键此时 Teachers 已存在3ALTER Teachers 补 Dno 外键回填引用关系4建 Students、Courses单向依赖 Departments5建 SC、Teaches依赖前面所有表-- 第一步教师表先建出来Dno 只留列不加外键 CREATE TABLE Teachers ( Tno CHAR(20) PRIMARY KEY, Tname CHAR(20) NOT NULL, Sex CHAR(20) CHECK (Sex IN (男, 女)), Birthday DATE, Title CHAR(20), Dno CHAR(20) ); -- 第二步院系表带外键引用教师表的主键 CREATE TABLE Departments ( Dno CHAR(20) PRIMARY KEY, Dname CHAR(20), Dheadno CHAR(20), CONSTRAINT fk_dept_head FOREIGN KEY (Dheadno) REFERENCES Teachers(Tno) ); -- 第三步回填教师表的外键打破循环 ALTER TABLE Teachers ADD CONSTRAINT fk_teacher_dept FOREIGN KEY (Dno) REFERENCES Departments(Dno); -- 第四步学生与课程表 CREATE TABLE Students ( Sno CHAR(20) PRIMARY KEY, Sname CHAR(20) NOT NULL, Sex CHAR(20) CHECK (Sex IN (男, 女)), Birthday DATE, Enrollyear CHAR(20), Speciality CHAR(20), Dno CHAR(20), CONSTRAINT fk_stu_dept FOREIGN KEY (Dno) REFERENCES Departments(Dno) ); CREATE TABLE Courses ( Cno CHAR(20) PRIMARY KEY, Cname CHAR(20) NOT NULL, Period SMALLINT, Credit SMALLINT ); -- 第五步选课表与教学表组合主键更贴合业务 CREATE TABLE SC ( Sno CHAR(20), Cno CHAR(20), Grade SMALLINT CHECK (Grade BETWEEN 0 AND 100), PRIMARY KEY (Sno, Cno), FOREIGN KEY (Sno) REFERENCES Students(Sno), FOREIGN KEY (Cno) REFERENCES Courses(Cno) ); CREATE TABLE Teaches ( Tno CHAR(20), Cno CHAR(20), Term CHAR(20), PRIMARY KEY (Tno, Cno, Term), FOREIGN KEY (Tno) REFERENCES Teachers(Tno), FOREIGN KEY (Cno) REFERENCES Courses(Cno) );这段脚本的核心是外键的依赖顺序不是语法本身。第二步和第三步的拆分就是处理循环引用的标准手法先建一张不带闭环外键的表把闭环的另一半建出来再 ALTER 回去补上。任何多表互相引用的场景都适用这个套路不限于教学表。CHECK (Sex IN (男, 女))比报告书里的CHECK (Sex男 OR Sex女)更短语义等价。带空值的列要注意三值逻辑CHECK 约束在结果为 NULL 时视为通过所以如果 Sex 允许为空插 NULL 不会被拦。要真正强制非空得额外加 NOT NULL。SC 表的主键我用的是(Sno, Cno)组合主键而不是加一个自增 id。这是教学场景下的合理选择一个学生一门课只选一次组合主键天然去重。真实业务里要考虑重修、多次选课那就得引入学期字段把主键扩成三列。选哪种取决于你的业务假设写报告时把假设写清楚比写对更重要。列级约束和表级约束的区别报告书里用了一大段解释落到实操上只有一条判断标准这个约束是否涉及多个列。涉及多列比如组合主键、复合外键、跨列 CHECK必须写表级只涉及单列写列级更紧凑。性能上没有区别纯粹是可读性问题。3.4 索引创建、自动索引与 \di 验证建索引这一步很多人建完就完事从不验证到底生效没有。验证方法很简单一条 SQL 加一个元命令。-- 在学生表的院系号上建普通索引 CREATE INDEX student_dept ON Students(Dno); -- 查看当前 schema 下所有索引 -- \di -- 用系统表确认索引是否真的建上了 SELECT indexname, indexdef FROM pg_indexes WHERE tablename students; -- 看某条查询是否走索引重点看 Seq Scan 还是 Index Scan EXPLAIN SELECT * FROM Students WHERE Dno D01;主键和唯一约束会自动创建索引所以\di里看到的索引数量一定比手建的多这是正常现象。判断某个索引有没有必要存在看两点这一列是否经常出现在 WHERE 或 JOIN 条件里以及这一列的取值重复度高不高。在性别这种只有两个值的列上建索引优化器大概率直接忽略走了还不如不走。EXPLAIN输出的Seq Scan表示全表扫描Index Scan表示走索引。教学表数据量只有几十行优化器很可能认为全表扫描更快这是正常的不代表索引失效。想验证索引效果塞几千行数据进去再看执行计划报告里如果写了索引提升了查询效率最好附上这个对比。删索引的语句是DROP INDEX 索引名注意不需要带表名。同一张表上建太多索引会拖慢插入和更新因为每次写操作都要维护索引结构。教学场景无所谓真实场景下每加一个索引都要能说出它服务于哪条查询。4. 交互式 SQL 与视图从多表连接到一个可更新视图的判断SQL 这一章覆盖实验三和实验四表面上是最熟悉的内容但 openGauss 在类型转换和视图可更新性上的行为和 MySQL 有差异照搬经验会踩坑。4.1 单表查询到三表连接的写法收敛查询需求一旦从单表跨到多表写法就有多种。以查某位学生选修的课程名和成绩为例内连接是最直观的一种。-- 三表内连接学生 → 选课 → 课程 SELECT s.Sname, c.Cname, sc.Grade FROM Students s JOIN SC sc ON s.Sno sc.Sno JOIN Courses c ON sc.Cno c.Cno WHERE s.Sno S001 ORDER BY sc.Grade DESC; -- 等价写法用 EXISTS 表达存在选课记录 SELECT c.Cname FROM Courses c WHERE EXISTS ( SELECT 1 FROM SC sc WHERE sc.Cno c.Cno AND sc.Sno S001 );两种写法的结果集不完全一样第一种会把学生姓名和成绩一起带出来一行对应一条选课记录第二种只输出课程名去掉了重复。选择哪种取决于你要的列不是哪种更高级。内连接会丢掉没有选课的学生。如果需求是列出所有学生及其选课数没选课的显示 0就得换成 LEFT JOIN 配合 COALESCE。-- 左连接保留没有选课记录的学生NULL 转 0 SELECT s.Sno, s.Sname, COALESCE(COUNT(sc.Cno), 0) AS course_cnt FROM Students s LEFT JOIN SC sc ON s.Sno sc.Sno GROUP BY s.Sno, s.Sname ORDER BY course_cnt DESC;GROUP BY后面必须列出所有非聚合的输出列这是标准 SQL 的要求openGauss 严格执行。MySQL 在宽松模式下允许省略从 MySQL 迁过来的写法会直接报错。COALESCE用来把 NULL 转成 0不用它的话没选课的学生那一行会显示 NULL 而不是 0报告里的统计表就对不上。4.2 聚合与分组GROUP BY 与 HAVING 的边界WHERE 和 HAVING 的分工是个老问题判断标准只有一条过滤发生在分组前还是分组后。分组前能确定的用 WHERE依赖聚合结果的用 HAVING。-- 统计每门课的选课人数与平均分只保留人数超过 2 的课 SELECT c.Cno, c.Cname, COUNT(sc.Sno) AS stu_cnt, ROUND(AVG(sc.Grade), 2) AS avg_grade FROM Courses c JOIN SC sc ON c.Cno sc.Cno WHERE sc.Grade IS NOT NULL -- 分组前过滤去掉没有成绩的记录 GROUP BY c.Cno, c.Cname HAVING COUNT(sc.Sno) 2 -- 分组后过滤按人数筛 ORDER BY avg_grade DESC;WHERE sc.Grade IS NOT NULL在分组前就干掉了空成绩避免AVG把 NULL 算进去影响分母。HAVING COUNT(sc.Sno) 2依赖聚合结果只能放在 HAVING。把 HAVING 的条件挪到 WHERE 会直接报错因为 WHERE 执行时 COUNT 还没有值。ROUND(AVG(...), 2)里的第二个参数是保留位数。openGauss 的 AVG 返回的是数值类型不加 ROUND 会输出一长串小数放进报告表格里很难看。这类细节不影响正确性但影响可读性。还有一个容易被忽略的点COUNT(*) 和 COUNT(列名) 结果可能不同。前者数行数后者跳过 NULL。统计选课人数时如果用 COUNT(sc.Grade)没成绩的记录不会被计入得到的数字比实际选课人数少。写统计 SQL 时想清楚要数的是记录数还是非空值数。4.3 视图创建、查询改写与可更新性判断视图在报告书里是实验四的全部内容。创建视图本身没有难度难的是判断一个视图能不能 UPDATE。判断标准不是靠猜是有明确规则的。-- 建一个学生成绩视图隐藏出生日期等无关列 CREATE VIEW v_student_score AS SELECT s.Sno, s.Sname, c.Cname, sc.Grade FROM Students s JOIN SC sc ON s.Sno sc.Sno JOIN Courses c ON sc.Cno c.Cno; -- 查看视图定义确认列来源 -- \d v_student_score -- 尝试更新视图多表连接的视图通常会失败 UPDATE v_student_score SET Grade 90 WHERE Sno S001;上面这条 UPDATE 大概率报错原因是视图涉及三张表连接数据库无法确定要改哪张基表。可更新视图的成立条件大致是只来自一张表、不含聚合函数、不含 DISTINCT、不含 GROUP BY 或 HAVING、不含 UNION、不含子查询里的聚合。满足这些条件的视图叫简单视图可以当成表来增删改否则就是只读视图。需要可更新时把视图收敛到单表-- 单表视图只暴露必要列具备可更新性 CREATE VIEW v_student_basic AS SELECT Sno, Sname, Sex, Dno FROM Students WHERE Dno IS NOT NULL; -- 这条可以正常执行会作用到 Students 基表 UPDATE v_student_basic SET Sname 张三 WHERE Sno S001;带WHERE条件的单表视图依然可更新只是更新后的行如果不再满足视图条件会从视图里消失。这不算 bug是视图的语义决定的。报告里如果要做这个实验建议把这个现象记录下来比单纯贴一句更新成功有价值得多。4.4 用视图做列级抽象与权限收口视图的第二个用途是权限控制。数据库的 GRANT 是表级和列级的但列级授权写起来繁琐用视图包一层更清爽。-- 面向查询角色的视图屏蔽出生日期和入学年份 CREATE VIEW v_student_public AS SELECT Sno, Sname, Speciality, Dno FROM Students; -- 只授予视图的查询权限不授予基表权限 GRANT SELECT ON v_student_public TO r_readonly; -- 验证以只读角色连接后只能看到视图里的列 SELECT * FROM v_student_public LIMIT 5;这个做法的关键在于被授权者拿不到基表的 SELECT 权限只能通过视图访问于是列级隔离就实现了。要注意视图的属主必须有基表权限否则视图建出来查不了。视图默认以属主权限执行类似 SECURITY DEFINER 的效果这一点和某些数据库的 definer/invoker 选项不同跨用户使用前先确认属性。视图还有一层价值是隔离表结构变更。基表加列、拆表的时候只要视图的输出列保持不变上层查询就不用改。教学场景体会不到这个好处但这是视图在工程里最常被使用的理由。5. 完整性、安全性、事务与备份四类控制实验的排查清单实验五到实验八这四类控制单独看每条语句都很短组合起来才见真章。这一章按怎么做 为什么推进最后把五条实际踩过的坑整理成现象、原因、解决三段式。5.1 约束追加ALTER TABLE 与外键的先后顺序建表时没加约束后期补加是常态。补加约束的语句不复杂复杂的是表里已经有数据的时候。-- 给已有表补加外键前提是被引用列必须是主键或唯一键 ALTER TABLE Students ADD CONSTRAINT fk_stu_dept FOREIGN KEY (Dno) REFERENCES Departments(Dno); -- 补加 CHECK 约束注意已有数据必须全部满足否则整条语句失败 ALTER TABLE SC ADD CONSTRAINT ck_grade CHECK (Grade BETWEEN 0 AND 100); -- 删除约束 ALTER TABLE SC DROP CONSTRAINT ck_grade; -- 外键的删除行为可以单独指定 ALTER TABLE SC DROP CONSTRAINT sc_sno_fkey, ADD CONSTRAINT sc_sno_fkey FOREIGN KEY (Sno) REFERENCES Students(Sno) ON DELETE CASCADE;补加外键时最常见的报错是被引用列没有唯一约束。外键的语义是我的值必须出现在对方的唯一记录里对方列如果不唯一这个语义就成立不了。报错信息里会明确写出找不到匹配的唯一约束看到这句就去检查对方表的主键定义。CHECK 约束补加失败八成是表里已有脏数据。比如成绩列里存了一个 120加BETWEEN 0 AND 100的约束就会整条语句回滚。解决办法是先查出来改掉SELECT * FROM SC WHERE Grade NOT BETWEEN 0 AND 100;改完再加约束。这个顺序不能反ALTER TABLE 是原子的一条不满足就全部不生效。ON DELETE CASCADE是把双刃剑。删学生时自动删掉他的选课记录业务上合理但如果 SC 表还被别的表引用级联会一路传下去。教学库里无所谓生产库里加 CASCADE 之前先用SELECT把会被删掉的行数数出来。5.2 授权与角色GRANT 的作用域与回收安全性控制实验的核心是 GRANT 和 REVOKE。语法简单作用域容易搞混。-- 建只读角色与读写角色 CREATE ROLE r_readonly; CREATE ROLE r_readwrite; -- 表级授权查询与插入 GRANT SELECT ON Students TO r_readonly; GRANT SELECT, INSERT, UPDATE ON SC TO r_readwrite; -- 把角色授予具体用户用户继承角色的全部权限 GRANT r_readonly TO u_student; -- 回收权限回收角色用 REVOKE 角色名 REVOKE INSERT ON SC FROM r_readwrite; -- 查看某个用户最终拥有的权限 SELECT grantee, table_name, privilege_type FROM information_schema.table_privileges WHERE grantee u_student;GRANT 权限 ON 表 TO 角色和GRANT 角色 TO 用户是两条完全不同的语句前者授对象权限后者授角色成员关系。初学者经常把两者混着写然后困惑为什么权限没生效。记法很简单ON 后面跟表就是对象权限TO 后面跟用户就是角色分配。权限回收之后已存在的连接不会立刻生效需要重新连接或者执行SET ROLE刷新。这个行为和很多数据库一致但在实验里容易被当成REVOKE 没起作用。验证时最好退出重连一次再查。还有一个细节information_schema.table_privileges只显示直接授予的权限通过角色继承来的权限要查role_table_grants。查权限时两个视图对照着看才不会漏。5.3 事务与并发两条会话验证隔离与锁等待事务和并发控制这一节光看语句看不出效果必须开两个会话对着做。这是整份报告书里唯一必须双窗口操作的实验。-- 会话 A开事务改一行但不提交 BEGIN; UPDATE SC SET Grade 95 WHERE Sno S001 AND Cno C001; -- 此处先不 COMMIT保持事务打开 -- 会话 B改同一行观察是否阻塞 BEGIN; UPDATE SC SET Grade 60 WHERE Sno S001 AND Cno C001; -- 此处会一直等待直到会话 A 提交或回滚 -- 会话 A提交 COMMIT; -- 会话 B 随即返回改为 60 生效 COMMIT;默认隔离级别是 READ COMMITTED会话 B 会一直等会话 A 释放行锁。如果不想无限等可以在会话 B 里先设一个超时-- 超过 5 秒拿不到锁就报错返回避免会话挂死 SET lock_timeout 5s;排查锁等待要看两个视图。pg_stat_activity看有哪些会话在跑、状态是什么pg_locks看锁的持有和等待关系。state列显示active且wait_event不为空基本就是卡在等锁。找到阻塞的会话后可以用pg_terminate_backend(pid)干掉但这是最后手段先确认对方是不是有未提交的事务。死锁在实验里不容易复现因为需要两个会话以相反顺序锁两行。构造方法A 锁第一行B 锁第二行然后 A 去锁第二行B 去锁第一行数据库检测到环路会主动回滚其中一个报错信息里会写明死锁。这个实验做一次比看十页教材印象深。5.4 备份与恢复gs_dump 的逻辑备份与恢复顺序备份恢复实验用的是 gs_dump这是逻辑备份工具导出的是 SQL 文本或自定义格式不是物理文件拷贝。# 切到安装用户执行输出目录必须 omm 可写 su - omm # 导出整个库为纯 SQL 文本 gs_dump -p 26000 -d db_school -f /home/omm/backup/db_school.sql -F p # 导出为自定义格式支持并行恢复和选择性还原 gs_dump -p 26000 -d db_school -f /home/omm/backup/db_school.dmp -F c # 恢复纯 SQL 用 gsql 直接灌 gsql -p 26000 -d db_school_new -f /home/omm/backup/db_school.sql # 恢复自定义格式用 gs_restore gs_restore -p 26000 -d db_school_new /home/omm/backup/db_school.dmp-F p是 plain输出纯文本 SQL-F c是 custom输出压缩的自定义格式。前者能直接用编辑器打开看内容适合教学和排查后者支持-t只还原某张表适合真实场景。选哪种取决于你要不要挑着恢复。恢复的目标库必须事先存在gs_dump 不会帮你建库。这是新手最容易卡住的一步拿着备份文件执行恢复报错说数据库不存在然后怀疑备份坏了。正确顺序是先CREATE DATABASE db_school_new;再执行恢复。输出目录的属主问题在实验八里几乎必现。用 root 建了/home/omm/backup切到 omm 后写不进去报权限拒绝。解决就是chown omm:omm /home/omm/backup一行命令的事但不知道的话会绕很久。5.5 五条踩坑记录现象、原因、解决下面五条是这类实验里复现率最高的失败按现象、原因、解决写清楚遇到时对号入座即可。第一条建外键报there is no unique constraint matching given keys。现象是 ALTER TABLE 加外键时直接失败提示找不到匹配的唯一约束。原因是被引用的列上既没有主键也没有唯一索引外键的语义要求对方唯一。解决办法是给被引用列补上 PRIMARY KEY 或 UNIQUE 约束或者改引用对方已有主键的另一列。第二条ALTER TABLE 加 CHECK 约束报数据违规。现象是语句失败提示已有行不满足新约束。原因是表里存在历史脏数据比如成绩超范围、性别写成未知。解决办法是先查出违规行清洗或修正后再执行 ALTER顺序不能反。第三条新建用户连接报认证失败。现象是 gsql 用新用户连库提示 password authentication failed。原因通常是两处密码不满足复杂度要求长度、字符类型、不能与用户名相同或者连接来源没有被放行。解决办法是重建用户并用合规密码同时确认服务端的访问控制配置允许该来源连接。第四条两个会话卡住不动界面无响应。现象是会话 B 的 UPDATE 一直不返回也不报错。原因是会话 A 持有行锁且未提交事务。解决办法是在会话 B 设lock_timeout再去pg_locks里找到阻塞的 pid让会话 A 提交或回滚。第五条gs_dump 备份文件为空或权限拒绝。现象是导出命令执行完目标文件是 0 字节或者干脆报错。原因是输出目录属主不是 omm或者磁盘写满。解决办法是先chown再重跑跑完用ls -lh确认文件大小别假设成功。6. JDBC 打通最后一公里驱动、连接串与一次可复现的验证实验九是把前面八个实验的成果接进程序很多人到这一步才发现前面建的库连不上问题往往出在驱动和连接串上。openGauss 的 JDBC 驱动类名是org.opengauss.Driver不是 PostgreSQL 那个org.postgresql.Driver用错类名会直接抛 ClassNotFound。驱动包从 openGauss 的官方发布渠道下载加进工程依赖即可。// 连接串关键参数协议头、主机、端口、库名 String url jdbc:opengauss://127.0.0.1:26000/db_school; String user u_student; String pass Abc123456; try (Connection conn DriverManager.getConnection(url, user, pass); PreparedStatement ps conn.prepareStatement( SELECT Sname, Grade FROM SC JOIN Students USING (Sno) WHERE Cno ?)) { ps.setString(1, C001); // 参数化别拼字符串 try (ResultSet rs ps.executeQuery()) { while (rs.next()) { System.out.println(rs.getString(1) - rs.getInt(2)); } } }连接串参数作用常见误设jdbc:opengauss://协议前缀写成jdbc:postgresql://导致驱动不认端口26000服务端口沿用 5432 连不上?currentSchema指定默认 schema不设则用 search_path找不到表?connectTimeout连接超时毫秒数不设则跨机时长时间挂起PreparedStatement加?占位符这两件事一起说它不只是防注入还能让数据库复用执行计划。字符串拼接的 SQL 每次文本都不同数据库要重新解析参数化之后同样的语句结构可以命中缓存。我最早做这个实验时把连接串写成jdbc:postgresql://本地跑通了因为工程里恰好还留着 PostgreSQL 驱动结果换到只装了 openGauss 驱动的机器上就崩了排查了半天才发现是协议头的问题。从那以后我每次搭 JDBC 环境都强制先单独跑一个只做SELECT 1的最小验证确认驱动加载、连接串、账号密码三件事全通再往里加业务 SQL。希望帮到你。本文还有配套的精品资源点击获取