GaussDB数据库操作与管理综合实验
该实验旨在指导用户短时间内熟悉并使用数据库语句进行数据库的操作与管理。
界面常用操作说明： 收起
全屏：点击全屏，实验手册和操作桌面进入全屏，再次点击退出全屏。
复制粘贴：作为媒介，支持从本地复制内容粘贴至云主机上，或者从云主机上复制内容到本地。双击或者选择左边手册内容也可以复制所选内容。
实验报告：可以查看实验步骤完成情况、实验问题列表、过程记录。
新手引导：查看更多功能区指引。
手册中代码复制，请点击命令框右上角拷贝代码按钮，鼠标挪到粘贴位置，右键粘贴
准备环境 收起
1. 预置环境
注意：开始实验之前请点击手册上方“预置实验环境”按钮。
-
预置实验环境需几分钟，成功后将会创建本实验所需的云资源（例如VPC、安全组或云服务器）。
-
如有预置的 ECS 资源，其用户、密码信息可点击实验操作桌面下方按钮查看。
2. 登录云账户
进入【实验操作桌面】，打开Chrome浏览器，默认会显示华为云页面，如下图，切换“IAM 用户”登录，并在对话框中输入系统为您分配的华为云实验账号和密码进行登录。
注意：请使用实验手册上方账号信息，切勿使用您自己的华为云账号登录。
一 购买 GaussDB 实例 收起
- 进入云数据库GaussDB服务，点击左侧的【服务列表】，选择其中【数据库】下的【云数据库GaussDB】。
-
进入云数据库GaussDB，点击页面右上角的【购买数据库实例】按钮。
-
进入购买页后，根据以下参数信息购买数据库。
主要参数如下，其他请保持默认。
• 计费模式 ：选择【按需计费】
• 区域和项目 ：默认的【华北-北京四】
• 实例名称 ：可以设置为【gauss-hccdp】
• 产品类型 ：基础版
• 数据库引擎版本 ：选择最新版即可
• 实例类型 ：集中式版
• 部署形态 ：1主2备
• 可用区 ：选择默认的可用区一、可用区二、可用区三， 如果这些可用区购买完了，可以自行选择其他可用区。
• 性能规格 ： 选择 “通用型（1:4） | 4 vCPUs | 16 GB”， 如果购买完了 ，选择其他最小的规格即可，如独享型（1:4） | 4 vCPUs | 16 GB。
• 存储类型、存储空间、磁盘加密 ：保持默认
• 虚拟私有云、内网安全组 ：选择已预置的资源vpc-hce和subnet-hce
• 安全组 ：选择已预置的sg-hce
• 数据库端口 ： 使用默认的端口8000
• 管理员密码 ：设置符合安全要求的root用户密码，并记住以便后用，其他选择默认
- 确认信息无误后，点击【提交】。
注意：gaussDB实例创建需要20分钟，请耐心等待
二 登录 ECS 服务器，下载 gsql 客户端并解压 收起
1 通过桌面上的Xfce终端登录ECS服务器。
输入以下命令，并将命令中的IP地址替换为ECS弹性公网IP。
ssh root@EIP
注意：EIP地址可进入华为云ECS服务查看：
IP地址信息能够在IP地址列被拷贝：
登录成功后，如图所示：
ECS服务器密码点击眼睛可以获取，如下图所示：
2 下载并解压gsql客户端，最后用source命令设置环境变量。
unzip GaussDB_opengauss_client_tools.zip
cd /root/GaussDB_opengauss_client_tools/Euler2.5_X86_64
cp GaussDB-Kernel-V500R001C20-EULER-64bit-gsql.tar.gz /opt
cd /opt/
tar -zxvf GaussDB-Kernel-V500R001C20-EULER-64bit-gsql.tar.gz
source gsql_env.sh
3 使用gsql客户端连接数据库，并创建数据库及对应用户
以下命令用来连接数据库，xxx.xx.xx.x是GaussDB主节点的IP，数据库默认的是postgres, 端口号也是创建时的默认值 8000，用户是默认是root, yourpassword是设置的数据库密码，请根据实际情况替换。
gsql -h xxx.xx.xx.x -d postgres -p 8000 -U root -W yourpassword -r
注意：GaussDB主节点的IP可通过点击GaussDB实例名称，进入信息页面查看：
滑动到节点列表处，就可以看到主节点IP:
下面的命令用来创建数据库：
CREATE DATABASE devdb ENCODING ‘UTF8’ template = template0;
退出当前数据库postgres :
\q
登录到新创建的数据库devdb:
gsql -h xxx.xx.xx.x -d devdb -p 8000 -U root -W yourpassword -r
创建新用户hccdp，并授权sysadmin的权限，yourpassword是数据库密码，可自行设置符号要求的密码并记住，以便后用。
CREATE USER hccdp SYSADMIN IDENTIFIED BY “yourpassword” ;
退出当前数据库devdb：
\q
用新创建的用户hccdp登录到新创建的数据库devdb，yourpassword是上面新用户hccdp的密码:
gsql -h xxx.xx.xx.x -d devdb -p 8000 -U hccdp -W yourpassword -r
\q
注意： xxx.xx.xx.x是GaussDb的主节点IP，请用实际值替换。
三 数据库的创建、查看、修改与删除 收起
1 登录数据库
用新创建的用户hccdp登录到新创建的数据库devdb ， 密码yourpassword 是上面设置的密码：
cd /opt/
source gsql_env.sh
gsql -h xxx.xx.xx.x -d devdb -p 8000 -U hccdp -W yourpassword -r
注意：命令中的IP xxx.xx.xx.x需替换为GaussDb的主节点IP
2 新建数据库 testdb
执行下面的命令去创建数据库testdb：
CREATE DATABASE testdb;
3 查看数据库
方式一：通过系统表pg_database查询数据库列表。
SELECT datname FROM pg_database;
方式二：使用\l元命令查看数据库系统的数据库列表。
\l
4 修改数据库
执行下面的命令去修改数据库设置默认的模式搜索路径。
ALTER DATABASE testdb SET search_path TO pa_catalog,public;
切换到testdb数据库：
\c testdb
输入登录数据库的用户hccdp的密码后，输入以下命令验证搜索路径是否修改：
show search_path;
5 重命名数据库
切换回默认数据库postgres ：
\c postgres
使用以下命令修改数据库名称testdb为testdb1 ：
ALTER DATABASE testdb RENAME TO testdb1;
执行下面的命令去验证名称是否已修改：
\l
6 删除数据库
下面的命令删除数据库testdb1：
DROP DATABASE testdb1;
四 行存表、列存表的创建、查看、修改与删除 收起
1 创建行存表
下面的命令创建行存表PART：
CREATE TABLE PART
(
P_PARTKEY BIGINT NOT NULL,
P_NAME VARCHAR(55) NOT NULL,
P_MFGR CHAR(25) NOT NULL,
P_BRAND CHAR(10) NOT NULL,
P_TYPE VARCHAR(25) NOT NULL,
P_SIZE BIGINT NOT NULL,
P_CONTAINER CHAR(10) NOT NULL,
P_RETAILPRICE DECIMAL(15,2) NOT NULL,
P_COMMENT VARCHAR(23) NOT NULL
)WITH (ORIENTATION = ROW);
3 查看表信息
执行下面的命令可查看表的相关信息：
\d
4 修改表的属性为表增加一列
执行下面的命令为表增加一列p_col1， 类型是bigint ：
ALTER TABLE part ADD COLUMN p_col1 bigint;
验证新增的列。
\d part
5 增加列上的默认值
下面的命令设置列p_col1的默认值为1 ：
ALTER TABLE part ALTER COLUMN p_col1 SET DEFAULT 1;
验证默认值：
\d part
6 删除列上的默认值
下面的命令删除列p_col1的默认值 ：
ALTER TABLE part ALTER COLUMN p_col1 drop DEFAULT ;
验证是否删除了该默认值 ：
\d part
7 修改字段的数据类型
下面的命令修改字段p_col1的数据类型为INT ：
ALTER TABLE part MODIFY p_col1 INT;
验证类型是否修改 ：
\d part
8 修改列的名称
下面的命令修改字段p_col1名称为p_col ：
ALTER TABLE part RENAME p_col1 to p_col;
验证是否已更改名称：
\d part
9 删除列
删除part表中的p_col列 ：
ALTER TABLE part DROP COLUMN p_col;
验证列是否删除 ：
\d part
10 删除表
删除PART表 ：
DROP TABLE PART;
查看是否还存在表信息 ：
\d
五 用户管理 收起
1 用户管理，创建用户jim
创建新用户jim, yourpassword 是自定义密码，请记住以便后用：
CREATE USER jim PASSWORD “yourpassword” ;
2 查看用户列表
SELECT * FROM pg_user;
3 创建用户，使其拥有创建数据库的权限
创建新用户jim, 使其具有CREATEDB权限，yourpassword 是自定义密码，请记住以便后用。
CREATE USER dim CREATEDB PASSWORD “yourpassword”;
4 将用户jim的登录密码由oldpassword修改为newpassword
将用户jim的登录密码由oldpassword修改为newpassword，oldpassword是自定义的密码，newpassword是新密码，请记住以便后用。
ALTER USER jim IDENTIFIED BY ‘newpassword’ REPLACE ‘oldpassword’;
5 假设原会话窗口为A，点击Xfce终端,用ssh登录ECS服务器后新建会话窗口B，如何登录 ECS 服务器，请参考“第二部分 登录ECS服务器” 。
会话窗口B：
下面的测试将在B会话中验证用户jim密码是否修改 ：
进入目录，运行gsql
cd /opt/source gsql_env.sh
使用用户jim登录数据库postgres ，xxx.xx.xx.x需替换为GaussDb的主节点IP，密码是上面修改后的新密码newpassword：
gsql -h xxx.xx.xx.x -d postgres -U jim -W newpassword -p 8000 -r
注意：命令中的IP需替换为GaussDb的主节点IP,如何获取GaussDb的主节点IP，请参考上面的第二部分的“## 3 使用gsql客户端连接数据库，并创建数据库及对应用户” 。
6 返回原会话窗口A，给用户jim追加有创建角色的CREATEROLE权限。
ALTER USER jim CREATEROLE;
注意：步骤7、8请继续在会话窗口A中执行。
7 验证权限是否添加
\du jim
8 锁定jim帐户
ALTER USER jim ACCOUNT LOCK;
9 点击Xfce终端,用ssh登录ECS服务器后，新建会话窗口C，使用jim登录，验证账户是否锁定
会话窗口C：
进入目录，启动gsql
cd /opt/source gsql_env.sh
使用gsql使用jim登录数据库postgres，xxx.xx.xx.x需替换为GaussDb的主节点IP, 密码是修改后的新密码newpassword：
gsql -h xxx.xx.xx.x -d postgres -U jim -W newpassword -p 8000 -r
返回如下信息，说明jim用户已被锁定。
10 返回原会话窗口A，解锁jim帐户
ALTER USER jim ACCOUNT UNLOCK;
11 返回会话窗口C，使用jim登录，验证账户是否解锁
使用gsql使用jim登录数据库postgres，xxx.xx.xx.x需替换为GaussDb的主节点IP, 密码是修改后的新密码newpassword ：
gsql -h xxx.xx.xx.x -d postgres -U jim -W newpassword -p 8000 -r
12 返回原会话窗口A，删除用户
退出数据库用户:
\q
重新用root用户登录postgres， yourpassword是root用戶的密码，请自行替换：
gsql -h xxx.xx.xx.x -d postgres -U root -W yourpassword -p 8000 -r
执行下面的命令删除用户jim :
DROP USER jim CASCADE;
六 Schema管理 收起
1 创建模式ds
通过管理Schema，允许多个用户使用同一数据库而不相互干扰；
每个数据库包含一个或多个Schema；
在数据库创建用户时，系统会自动帮助用户创建一个同名Schema ：
CREATE SCHEMA ds;
验证schema是否创建
\dn
2 将当前模式ds更名为ds_new
ALTER SCHEMA ds RENAME TO ds_new;
验证schema名字是否修改：
\dn
3 创建用户jack
创建用户jack，密码yourpassword请自行设定：
CREATE USER jack PASSWORD ‘yourpassword’;
4 将DS_NEW的所有者修改为jack
ALTER SCHEMA ds_new OWNER TO jack;
5 查看Schema所有者
SELECT s.nspname,u.usename AS nspowner FROM pg_namespace s, pg_user u WHERE s.nspowner = u.usesysid;
6 删除用户jack和模式ds_new
删除模式ds_new：
DROP SCHEMA ds_new;
删除用户jack：
DROP USER jack;
七 将系统权限和对象权限授权给用户或者角色 收起
1 创建名为joe的用户，并将sysadmin权限授权给他
创建用户jack，密码yourpassword请自行设定：
CREATE USER joe PASSWORD ‘yourpassword’;
将sysadmin权限授权给joe：
ALTER USER joe with sysadmin;
查看用户权限变化
\du
2 撤销joe用户的sysadmin权限，然后创建tpcds模式，并给tpcds模式下创建一张reason表。
撤销joe用户的sysadmin权限:
ALTER USER joe with nosysadmin;
创建tpcds模式:
CREATE SCHEMA tpcds;
在tpcds模式下创建一张reason表:
CREATE TABLE tpcds.reason
(
r_reason_sk INTEGER NOT NULL,
r_reason_id CHAR(16) NOT NULL,
r_reason_desc VARCHAR(20)
);
假设当前会话为A，点击Xfce终端,用ssh登录ECS服务器后，新建会话B。
会话B:
在会话B中，使用joe用户连接数据库，验证joe用户是否拥有tpcds模式下reason表的权限。
cd /opt/source gsql_env.sh
使用joe用户登录数据库postgres ，xxx.xx.xx.x 是数据库的主节点IP， yourpassword是上面创建时的密码 :
gsql -h xxx.xx.xx.x -d postgres -p 8000 -U joe -W yourpassword -r
验证joe用户是否拥有tpcds模式下reason表的权限 ：
select * from tpcds.reason;
结果显示joe没有权限访问：
3 返回会话A，将模式tpcds的使用权限和表tpcds.reason的所有权限授权给用户joe
将模式tpcds的使用权限授权给用户joe：
GRANT USAGE ON SCHEMA tpcds TO joe;
将表tpcds.reason的所有权限授权给用户joe：
GRANT ALL PRIVILEGES ON tpcds.reason TO joe;
切换至会话B，验证joe用户是否拥有tpcds模式下reason表的权限：
select * from tpcds.reason;
4 返回会话A，将tpcds.reason表中r_reason_sk、r_reason_id、r_reason_desc列的查询权限，r_reason_desc的更新权限授权给joe
GRANT select (r_reason_sk,r_reason_id,r_reason_desc),update (r_reason_desc) ON tpcds.reason TO joe;
5 将数据库postgres的连接权限授权给用户joe，并给予其在postgres中创建schema的权限，而且允许joe将此权限授权给其他用户
GRANT create,connect on database postgres TO joe WITH GRANT OPTION;
切换至会话B，验证joe用户对表tpcds.reason的查询和更新权限。
验证查询权限，下面的SQL如果能执行成功，表明joe有查询权限：
SELECT r_reason_sk,r_reason_id,r_reason_desc FROM tpcds.reason;
验证更新权限，下面的SQL如果能执行成功，表明joe有更新权限：
UPDATE tpcds.reason SET r_reason_desc=‘test’;
6 返回会话A，创建角色tpcds_manager，将模式tpcds的访问权限授权给角色tpcds_manager，并授予该角色在tpcds下创建对象的权限，不允许该角色中的用户将权限授权给他人
创建角色tpcds_manager，请自行设置密码yourpassword ：
CREATE ROLE tpcds_manager PASSWORD ‘yourpassword’;
验证角色tpcds_manager是否添加：
\du
将模式tpcds的访问权限授权给角色tpcds_manager，并授予该角色在tpcds下创建对象的权限：
GRANT USAGE,CREATE ON SCHEMA tpcds TO tpcds_manager;
7 创建角色manager，将joe的权限授权给manager，并允许该角色将权限授权给其他人
创建角色manager，请自行设置密码yourpassword :
CREATE ROLE manager PASSWORD ‘yourpassword’;
将joe的权限授权给manager，并允许该角色将权限授权给其他人:
GRANT joe TO manager WITH ADMIN OPTION;
8 创建用户senior_manager，将用户manager的权限授权给该用户
创建角色senior_manager ，请自行设置密码yourpassword :
CREATE USER senior_manager PASSWORD ‘yourpassword’;
将用户manager的权限授权给senior_manager:
GRANT manager TO senior_manager;
9 撤销权限，并清理用户
删除用户manager：
DROP USER manager;
撤销用户joe的表tpcds.reason的所有权限：
REVOKE ALL PRIVILEGES ON tpcds.reason FROM joe;
撤销用户joe的表tpcds 的所有权限：
REVOKE ALL PRIVILEGES ON SCHEMA tpcds FROM joe;
撤销用户tpcds_manager的tpcds的USAGE,CREATE权限：
REVOKE USAGE,CREATE ON SCHEMA tpcds FROM tpcds_manager;
删除角色tpcds_manager：
DROP ROLE tpcds_manager;
删除用户senior_manager：
DROP USER senior_manager;
删除用户joe ：
DROP USER joe CASCADE;
八 创建数据库对象前判断该对象是否已存在 收起
1 创建新用户前查看新用户是否已存在
查看dbtest用户是否已存在：
SELECT usename FROM pg_user WHERE usename=‘dbtest’;
创建角色dbtest ，请自行设置密码yourpassword :
CREATE USER dbtest WITH sysadmin PASSWORD ‘yourpassword’;
2 退出当前用户hccdp对数据库的连接，切换到新用户dbtest连接数据库。
退出当前数据库连接。
\q
使用新用户dbtest连接数据库devdb, xxx.xx.xx.x需替换为Gaussdb主节点IP, yourpassword是上面dbtest的密码:
gsql -h xxx.xx.xx.x -d postgres -p 8000 -U dbtest -W yourpassword -r
3 查看是否模式已自动创建
查看模式dbtest已自动创建:
SELECT nspname FROM pg_namespace WHERE nspname=‘dbtest’;
此时会看到有一条记录，再次创建会看到已存在的告警:
CREATE SCHEMA dbtest;
4 创建数据库前查看已存在的数据库中是否存在同名数据库
查看是否存在数据库db_test:
SELECT datname FROM pg_database WHERE datname=‘db_test’;
创建数据库db_test：
CREATE DATABASE db_test;
5 创建表之前查看表是否已存在
查看是否存在表dbtest:
SELECT tablename FROM pg_tables WHERE schemaname=‘dbtest’;
创建数据库表test：
CREATE TABLE test (id int, name varchar(100));
6 查看当前用户创建的所有对象
创建用户dbtest的所有对象：
SELECT relname FROM pg_class WHERE relowner=(select usesysid from pg_user where usename=‘dbtest’);
7 清理数据库对象并删除用户后，退出当前数据库连接。
DROP TABLE test;
DROP SCHEMA dbtest;
DROP DATABASE db_test;
\q
九 查看数据库进程并释放 收起
1 使用hccdp用户登录数据库
cd /opt/source gsql_env.sh
使用新用户hccdp连接数据库postgres, xxx.xx.xx.x需替换为Gaussdb主节点IP，密码为当初设置密码:
gsql -h xxx.xx.xx.x -d postgres -p 8000 -U hccdp -W yourpassword -r
2 执行以下SQL生成会话
SELECT pg_sleep(300);
3 另外打开Xfce终端，ssh连接ECS服务器后，在新窗口登录数据库
cd /opt/source gsql_env.sh
使用新用户hccdp连接数据库postgres, xxx.xx.xx.x需替换为Gaussdb主节点IP，密码yourpassword为当初设置密码:
gsql -h xxx.xx.xx.x -d postgres -p 8000 -U hccdp -W yourpassword -r
4 以sleep为条件查看pg_stat_activity
SELECT pid,usename,query FROM pg_stat_activity WHERE QUERY LIKE ‘%sleep%’;
5 使用pg_terminate_backend函数结束会话
注意：括号内的pid需替换为上面对应的pid数字序列
SELECT pg_terminate_backend(pid);
6 查看原shell界面
会看到如下报错：
十 设计搜索表格输出特定值的存储过程 收起
1 创建表格
CREATE TABLE employee (id varchar(100),name varchar(100),salary int);
CREATE TABLE ep_grade (id varchar(100),grade varchar(100),flag int);
2 导入数据
Insert into employee values(1,‘张三’,5000);Insert into employee values(2,‘李四’,5000);Insert into employee values(3,‘王五’,5000);Insert into employee values(4,‘赵六’,5000);Insert into employee values(5,‘太一’,5000);Insert into employee values(6,‘太二’,5000);Insert into employee values(7,‘太三’,5000);Insert into employee values(8,‘太四’,5000);Insert into ep_grade values(1,‘A’,2022);Insert into ep_grade values(2,‘S’,2021);Insert into ep_grade values(3,‘B’,2021);Insert into ep_grade values(4,‘B’,2021);Insert into ep_grade values(5,‘A’,2021);Insert into ep_grade values(6,‘C’,2022);Insert into ep_grade values(7,‘C’,2021);Insert into ep_grade values(8,‘B’,2022);
3 整合前面设计的各个语句，按照存储过程的结构进行组合
CREATE OR REPLACE PROCEDURE proc_emp()AS
1）声明参数
DECLARE
EP_ID VARCHAR(100);
GRADE VARCHAR(10);
SALARY INT;
2）声明游标
CURSOR C1 IS select distinct id,grade FROM ep_grade where Flag = 2021;
3）设计循环语句
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO EP_ID,GRADE;
EXIT WHEN C1%NOTFOUND;
Case GRADE
when ‘S’
then update employee set salary = salary+1000 where id=EP_ID ;
when ‘A’
then update employee set salary = salary+500 where id=EP_ID ;
when ‘B’
then update employee set salary = salary+100 where id=EP_ID;
when ‘C’
then update employee set salary = salary-200 where id=EP_ID;
END Case;
4）设计存储过程的内容输出
DBE_OUTPUT.PRINT_LINE(‘ID: ‘||EP_ID||’, Grade: ‘||GRADE||’, updated.’);END LOOP;CLOSE C1;END;
/
4 调用存储过程
call proc_emp();
看到如下回显：
恭喜您，您已完成该实验!