1.1 实验介绍

openGauss 是华为开源发布的多模态数据库管理系统，具有多种优越的特性。openGauss 数据库在内核架构上具有多方面的创新与改进。在本实验中，我们将进行 openGauss 数据库的初探。首先介绍 openGauss 的特性与架构，学习如何准备 openGauss 数据库的安装环境，掌握 openGauss 数据库服务器的启动控制操作，学习 gsql 客户端连接数据库的基本命令用法。本实验是本系列 openGauss 数据库“实验之旅”的开端。

作为关系数据库，openGauss 的主要功能是进行关系数据的管理，包括数据定义、数据查询、数据更新等基本数据管理操作。数据定义是进行数据管理的第一步工作，其要创建数据库和定义所有数据表，作为装载具体数据的框架结构；数据查询是要在数据库中查找满足各种条件要求的数据，是数据库最重要的功能之一，需要通过大量实验操作和练习才能掌握编写查询的技巧；同时，数据更新是要实现数据添加、修改和删除操作。在本章中，我们将在 openGauss 中使用关系数据库语言 SQL 创建数据库和数据表，完成基本查询、连接查询、子查询和集合查询等各类数据查询，进行数据更新和数据表结构的修改。本章内容是对openGauss 的基本数据管理功能进行实践。

1.2 实验目的

了解 openGauss 数据库的特性。
了解 openGauss 数据库的架构。
掌握 openGauss 数据库极简版的安装方法。
掌握 openGauss 数据库服务器的控制方法。
掌握 openGauss 数据库客户端 gsql 连接数据库。
掌握 openGauss 数据库客户端 Data Studio 连接数据库。
掌握在 openGauss 中使用 SQL 创建数据库和数据表。
掌握在 openGauss 中使用 SQL 进行基本查询。
掌握在 openGauss 中使用 SQL 进行连接查询。
掌握在 openGauss 中使用 SQL 进行子查询。
掌握在 openGauss 中使用 SQL 进行集合查询。
掌握在 openGauss 中使用 SQL 进行数据更新，包括添加、修改、删除数据。
掌握在 openGauss 中使用 SQL 修改表结构。

1.3实验步骤和结果

第一章 openGauss初探

安装完openGauss数据库后，使用 ps 查看 gaussdb 进程。

使用 gs_ctl 查看 openGauss 数据库服务器进行状态。服务器正常运行，显示如下：

通过 gsql 测试数据库服务器执行 gsql 命令，并执行 SQL 语句查询数据库版本信息，最终退出：

使用testuser连接到数据库testdb

创建表并插入数据，最终的查询结果如下

第二章基本SQL语句

一、创建数据库和数据表

然后使用bsc连接数据库

接下来根据文档创建course、sc、student三个表，表的具体结构如下

Course表

Sc表

Student表

最后，根据文档插入数据

二、查询数据——基本查询

1.选择表中的列

（1）查询指定列

【查询 1】查询全体学生的姓名与性别。

1	SELECT sname,sgender FROM student;

【查询 2】查询全体学生的姓名、学号和所在系。

1	SELECT sname,sno,sdept FROM student;

（2）查询全部列

【查询 3】查询全体学生的所有信息。

1	SELECT * FROM student;

对查询列进行计算

【查询 4】查询全体学生的姓名和年龄。

1	SELECT sname AS 姓名, AGE(CURRENT_DATE, sbirth) AS 年龄 FROM student;

这里使用了openGauss的内置函数AGE，用于计算年龄

消除重复行

【查询 5】查询选修了课程的学生的学号。

1	SELECT DISTINCT sno FROM sc;

2.选择表中的行

（1）比较大小

【查询 6】查询计算机科学系（CS）全体学生的名单。

1	SELECT sname FROM student WHERE sdept = 'CS';

【查询 7】查询有不及格课程成绩的学生学号。

1	SELECT DISTINCT sno FROM sc WHERE score < 60;

确定范围

【查询 8】查询 2 号课程成绩在 80 到 89 之间的学生学号。

1	SELECT sno FROM sc WHERE cno=2 AND score BETWEEN 80 AND 89;

注意在BETWEEN···AND···中的范围包括上下限

【查询 9】查询 2 号课程成绩不在 80 到 89 之间的学生学号。

1	SELECT sno FROM sc WHERE cno=2 AND score NOT BETWEEN 80 AND 89;

确定集合

【查询 10】查询计算机科学系（CS）和计算机工程系（CE）的学生姓名和性别。

1	SELECT sname,sgender FROM student WHERE sdept IN ('CS', 'CE');

【查询 11】查询不是计算机科学系（CS）和计算机工程系（CE）的学生姓名、性别和所在系。

1	SELECT sname,sgender FROM student WHERE sdept NOT IN ('CS', 'CE');

字符匹配

【查询 12】查询所有姓张的学生的学号、姓名和性别。

1	SELECT sno,sname,sgender FROM student WHERE sname LIKE '张%';

【查询 13】查询所有姓张的且姓名为两个字的学生的学号、姓名和性别。

1	SELECT sno,sname,sgender FROM student WHERE sname LIKE '张_';

【查询 14】查询所有不姓张的学生的学号、姓名和性别。

1	SELECT sno,sname,sgender FROM student WHERE sname NOT LIKE '张%';

【查询 15】查询课程名为 DB_Course 的课程的课号和学分。

1	SELECT cno,ccredit FROM course WHERE cname LIKE 'DB\_Course';

【查询 16】查询课程名以 DB_开头且倒数第 2 个字符为 s 的课程的课号和学分。

使用 ESCAPE 关键字指定某字符为转义符。

1	SELECT cno,ccredit FROM course WHERE cname LIKE 'DB^_%_' ESCAPE '^';

【查询 17】查询所在系的名称以 C 开头的学生的学号、姓名和系名。

使用正则表达式进行匹配，下面是openGauss支持的匹配运算符

1	SELECT sno,sname,sdept FROM student WHERE sdept ~ '^C.*';

判断空值

【查询 18】查询成绩为空的学生的学号和所选课程的课号。

1	SELECT sno,cno FROM sc WHERE score IS NULL; --注意这里不是score==NULL

【查询 19】查询成绩不为空的学生的学号和所选课程的课号。

1	SELECT sno,cno FROM sc WHERE score IS NOT NULL;

逻辑运算

【查询 20】查询计算机科学系（CS）的男生信息。

1	SELECT * FROM student WHERE sdept = 'CS' AND sgender = '男';

【查询 21】查询计算机科学系（CS）、计算机工程系（CE）和信息科学系（IS）的学生姓名和性别。

1	SELECT sname,sgender FROM student WHERE sdept = 'CS' OR sdept = 'CE' OR sdept = 'IS';

3.排序

【查询 22】查询选修了 2 号课程的学生学号及其成绩，按分数降序排列。

1	SELECT sno,score FROM sc WHERE cno = 2 ORDER BY score DESC;

【查询 23】查询全体学生的信息，查询结果按照所在系的名称升序排列，同一个系的学生按照姓名的降序排列。

1	SELECT * FROM student ORDER BY sdept ASC, sname COLLATE "zh_CN.utf8" DESC;

注意，要使用 COLLATE "zh_CN.utf8"指明按照中文 UTF-8 字符集进行排序，才能保证按照中文拼音排序。

4.聚合函数

常见SQL聚合函数如下：

【查询 24】查询学生的总人数。

1	SELECT COUNT(*) FROM student;

【查询 25】查询选修了课程的学生人数。

1	SELECT COUNT(DISTINCT sno) FROM sc;

【查询 26】计算 3 号课程的平均成绩。

1	SELECT ROUND(AVG(score),2) AS avg FROM sc WHERE cno = '3';

其中ROUND函数是进行舍入，和python中的round类似

【查询 27】计算 3 号课程的最高分数。

1	SELECT MAX(score) FROM sc WHERE cno = '3';

【查询 28】计算学生 22001 所选课程的总成绩。

1	SELECT SUM(score) FROM sc WHERE sno = '22001';

【查询 29】求各个课程号及相应的选课人数。

1	SELECT cno, COUNT(sno) FROM sc GROUP BY cno;

【查询 30】查询至少选修了 3 门课程的学生学号。

1	SELECT sno FROM sc GROUP BY sno HAVING COUNT(*) >= 3;

三、查询数据——连接查询

1.内连接

（1）等值连接

【查询 31】查询每个学生及其选课情况。

1	SELECT * FROM student INNER JOIN sc ON student.sno = sc.sno;

【查询 32】使用自然连接查询每个学生及其选课情况。

1	SELECT student.sno, sname, sgender, sbirth, sdept, cno, score FROM student INNER JOIN sc ON student.sno = sc.sno;

注意这里的自然连接只是通过指定列的名称来实现，并不是NATURAL JOIN

（2）交叉连接

【查询 33】对 student 表和 sc 表进行交叉连接操作。

1	SELECT * FROM student CROSS JOIN sc;

2.外连接

（1）左外连接

【查询 34】查询每个学生及其选修课程的情况，没有选课的学生也要列出。

首先将null值设置显示为NULL

1	\pset null 'NULL'

然后查询

1	SELECT * FROM student LEFT OUTER JOIN sc ON student.sno = sc.sno;

(2)右外连接

【查询 35】查询选课情况及相应每门课程信息，没有被选的课程也要列出。

1	SELECT * FROM sc RIGHT OUTER JOIN course ON sc.cno = course.cno;

(3)完全外连接

数据准备

CREATE TABLE department
(
id CHAR(2),
name VARCHAR(20)
);
INSERT INTO department VALUES('CS', '计算机科学');
INSERT INTO department VALUES('CE', '计算机工程');
INSERT INTO department VALUES('IS', '信息科学');
INSERT INTO department VALUES('SE', '软件工程');
INSERT INTO Student VALUES ('22005', '孙七', '男', '2001-11-01', NULL);

【查询 36】查询每个系及其中学生的情况，要求同时列出尚无学生的系和尚未分配系的学生。

1	SELECT * FROM department FULL OUTER JOIN student ON department.id = student.sdept;

3.自连接

数据准备

CREATE TABLE student_p
(
sno CHAR(5),
sname VARCHAR(10) NOT NULL,
sgender CHAR(4) NOT NULL,
sbirth DATE NOT NULL,
sdept CHAR(2),
province VARCHAR(10),
CONSTRAINT pk_student_p PRIMARY KEY(sno)
);
INSERT INTO student_p VALUES ('22001', '张三', '男', '2002-05-19', 'CS', '北京');
INSERT INTO student_p VALUES ('22002', '李四', '女', '2002-01-09', 'CS', '天津');
INSERT INTO student_p VALUES ('22003', '王五', '女', '2001-12-08', 'CE', '河北');
INSERT INTO student_p VALUES ('22004', '赵六', '男', '2001-08-30', 'IS', '北京');

【查询 37】列出来自相同省份的学生学号、姓名和省份。

SELECT s1.sno, s1.sname, s2.sno, s2.sname, s1.province 
FROM student_p s1 INNER JOIN student_p s2 ON s1.province = s2.province 
WHERE s1.sno < s2.sno 
ORDER BY s1.sno, s2.sno;

注意自连接大多是通过给一个表起两个别名来实现的，WHERE语句s1.sno < s2.sno如果改成s1.sno <> s2.sno，会出现两行相同的数据（只是左右颠倒）。

4.多表连接。

【查询 38】查询每个学生的学号、姓名、选修的课程名及成绩。

学生的学号和姓名在 student 表中，课程名在 course 表中，成绩在 sc 表中。本查询涉及 3 张表，应使用多表连接完成。

1	SELECT s.sno,s.sname,c.cname,sc.score FROM student s INNER JOIN sc ON s.sno = sc.sno INNER JOIN course c ON sc.cno = c.cno;

四、查询数据——子查询

1.IN谓词子查询

【查询 39】查询与“李四”在同一个系学习的学生。

1	SELECT sno,sname,sdept FROM student WHERE sdept IN (SELECT sdept FROM student WHERE sname = '李四');

【查询 40】查询选修了“数据库系统”课程的学生学号和姓名。

SELECT sno,sname FROM student 
WHERE sno IN 
	(SELECT sno FROM sc WHERE cno IN 
     	(SELECT cno FROM course WHERE cname = '数据库系统')
    );

2.比较运算符子查询

【查询 41】找出每个学生超过他选修课程平均成绩的课程号。

1 2	SELECT sno,cno FROM sc x WHERE score > (SELECT AVG(score) FROM sc y WHERE x.sno = y.sno);

注意这里是相关子查询，即每次子查询的结果都依赖父查询

3.ANY和ALL谓词子查询

【查询 42】查询其他系中比 IS 系某一学生出生日期晚的学生姓名、出生日期和所在系。

1 2	SELECT sname,sbirth,sdept FROM student WHERE sbirth > ANY (SELECT sbirth FROM student WHERE sdept = 'IS') AND sdept <> 'IS';

【查询 43】查询其他系中比 IS 系所有学生出生日期都晚的学生姓名、出生日期和所在系。

1	SELECT sname,sbirth,sdept FROM student WHERE sbirth > ALL (SELECT sbirth FROM student WHERE sdept = 'IS') AND sdept <> 'IS';

注意等价关系> ANY 是 > 最小值, > ALL 是 > 最大值，所以可以进行相应的转换

4.EXISTS 谓词子查询

【查询 44】查询所有选修了 2 号课程的学生姓名。

1	SELECT sname FROM student s WHERE EXISTS (SELECT * FROM sc WHERE s.sno = sc.sno AND cno = 2);

注意这里也是相关子查询

【查询 45】查询没有选修 2 号课程的学生姓名。

1	SELECT sname FROM student s WHERE NOT EXISTS (SELECT * FROM sc WHERE s.sno = sc.sno AND cno = 2);

【查询 46】查询选修了全部课程的学生的学号和姓名。

数据准备

1	INSERT INTO sc VALUES ('22001', 4, NULL);

SELECT s.sno,s.sname FROM student s 
WHERE NOT EXISTS( SELECT * FROM course c 			--没有一门课程
                 WHERE NOT EXISTS (SELECT * FROM sc 		--是该学生不选的
                                   WHERE s.sno = sc.sno AND c.cno = sc.cno)
                );

分析：选课即代表student表和sc表中的sno相对应并且 course表和sc表中的cno相对应

五、查询数据——集合查询

【查询 47】查询 CS 系的学生及所有男学生。

1
2
3

SELECT * FROM student WHERE sdept = 'CS' 
UNION 
SELECT * FROM student WHERE sgender = '男';

【查询 48】查询 CS 系的男学生。

1
2
3

SELECT * FROM student WHERE sdept = 'CS' 
INTERSECT 
SELECT * FROM student WHERE sgender = '男';

【查询 49】查询不在 CS 系的女学生。

1
2
3

SELECT * FROM student WHERE sgender = '女' 
EXCEPT 
SELECT * FROM student WHERE sdept = 'CS';

六、更新数据

1.插入数据

【查询 50】将一条新学生记录

（学号：22005，姓名：孙七，性别：男，出生日期：2001-11-01，系别：CE）插入到 student 表中。

1 2	INSERT INTO student VALUES ('22005', '孙七', '男', '2001-11-01', 'CE');

【查询 51】插入一条选课记录('22005', 4)。

1	INSERT INTO sc (sno,cno) VALUES ('22005', 4);

【查询 52】将 CS 系学生的学号和姓名存放到一张单独的表中。

数据准备

CREATE TABLE student_cs
(
 sno CHAR(5),
 sname VARCHAR(10)
);

查询操作

1 2	INSERT INTO student_cs(sno, sname) SELECT sno, sname FROM student WHERE sdept = 'CS';

2.修改数据

【查询 53】将学生 22005 的系别改为 IS。

1	UPDATE student SET sdept = 'IS' WHERE sno = '22005';

【查询 54】将所有学生所选课程成绩增加 2 分。

1	UPDATE sc SET score = score + 2;

【查询 55】将 CS 系全体学生的成绩设置为零。

1 2	UPDATE sc SET score = 0 WHERE 'CS' = (SELECT sdept FROM student s WHERE s.sno = sc.sno); --利用相关子查询

3.删除数据

【查询 56】删除学号为 22005 的学生记录。

1 2	DELETE FROM sc WHERE sno = '22005'; --在sc表中有student的外键约束，所以先删sc表里的 DELETE FROM student WHERE sno = '22005';

【查询 57】删除所有的学生选课记录。

1	DELETE FROM sc

由于后续操作还需用到sc，故没有执行操作

【查询 58】删除 CE 系所有学生的选课记录。

1	DELETE FROM sc WHERE 'CE' = (SELECT sdept FROM student WHERE student.sno = sc.sno);

七、修改表的结构

1.添加列

【查询 59】在 course 表中添加 description 列，用于保存课程描述。

1	ALTER TABLE course ADD COLUMN description TEXT;

2.删除列

【查询 60】将 description 列从 course 表中删除。

1	ALTER TABLE course DROP COLUMN description;

注意：如果有其他表通过外键约束依赖该列，为了维护参照完整性，数据库将进行提示，而非直接删除该列。可以最后添加 CASCADE关键字，在删除列时，一并删除所有依赖该列的数据库对象。

3.添加约束

【查询 61】在 sc 表中添加约束，确保 score 大于等于 0 且小于等于 100。

1	ALTER TABLE sc ADD CHECK (score>=0 AND score <=100);

【查询 62】在 sc 表中添加约束，确保 score 列不为空值。

1	ALTER TABLE sc ALTER COLUMN score SET NOT NULL;

4.删除约束

【查询 63】在 sc 表中删除外键约束 fk_student。

1	ALTER TABLE sc DROP CONSTRAINT fk_student;

【查询 64】在 sc 表中删除 score 列上的非空约束。

1	ALTER TABLE sc ALTER COLUMN score DROP NOT NULL;

5.设置默认值

【查询 65】在 sc 表中，为 score 列设置默认值为 0。

1	ALTER TABLE sc ALTER COLUMN score SET DEFAULT 0;

【查询 66】在 sc 表中，删除 score 列上设置的默认值。

1	ALTER TABLE sc ALTER COLUMN score DROP DEFAULT;

6.修改列数据类型

【查询 67】在 sc 表中，将 score 列的数据类型改为 NUMERIC(5, 2)。

1	ALTER TABLE sc ALTER COLUMN score TYPE NUMERIC(5,2);

注意，只有当该列的已有数据值能够通过默认类型转换转为指定的新数据类型时，修改列数据类型语句才能执行成功。

7.重命名列

【查询 68】在 sc 表中，将 score 列名称改为 grade。

1	ALTER TABLE sc RENAME COLUMN score TO grade;

8.重命名表

【查询 69】将 sc 表的名称改为 student_course。

1	ALTER TABLE sc RENAME TO student_course;

1.4实验总结

本次实验较为基础，主要是对SQL语句增删改查操作的考察，不过仍然有一下需要理解的地方，如自连接、对已知查询语句的转化等等，具体需要注意的地方均已用红色标明。openGauss整体上和postgreSQL差不多，不过也有区别，如openGauss的自然连接是通过指定列名进行，而postgreSQL（或MySQL）是通过NATURAL JOIN来实现。

Charoneo's Blog

openGauss_lab1

1.1 实验介绍

1.2 实验目的

1.3实验步骤和结果

第一章 openGauss初探

第二章基本SQL语句

一、创建数据库和数据表

二、查询数据——基本查询

1.选择表中的列

2.选择表中的行

3.排序

4.聚合函数

三、查询数据——连接查询

1.内连接

2.外连接

3.自连接

4.多表连接。

四、查询数据——子查询

1.IN谓词子查询

2.比较运算符子查询

3.ANY和ALL谓词子查询

4.EXISTS 谓词子查询

五、查询数据——集合查询

六、更新数据

1.插入数据

2.修改数据

3.删除数据

七、修改表的结构

1.添加列

2.删除列

3.添加约束

4.删除约束

5.设置默认值

6.修改列数据类型

7.重命名列

8.重命名表

1.4实验总结

1.1 实验介绍

1.2 实验目的

1.3实验步骤和结果

第一章 openGauss初探

第二章 基本SQL语句

一、创建数据库和数据表

二、查询数据——基本查询

1.选择表中的列

2.选择表中的行

3.排序

4.聚合函数

三、查询数据——连接查询

1.内连接

2.外连接

3.自连接

4.多表连接。

四、查询数据——子查询

1.IN谓词子查询

2.比较运算符子查询

3.ANY和ALL谓词子查询

4.EXISTS 谓词子查询

五、查询数据——集合查询

六、更新数据

1.插入数据

2.修改数据

3.删除数据

七、修改表的结构

1.添加列

2.删除列

3.添加约束

4.删除约束

5.设置默认值

6.修改列数据类型

7.重命名列

8.重命名表

1.4实验总结

第二章基本SQL语句