2023-2024-openGauss
【摘要】 2023-2024全国总决赛-openGauss-答题卡 3.25 考试题目 任务1opengGauss实验环境搭建 考点1：快速安装opengauss要求： a. 请使用root用户登录系统，创建/opt/software/openGauss目录，并上传installopenGauss.zip到/opt/software/openGauss目录下，然后解压并执行sh install.s...
2023-2024全国总决赛-openGauss-答题卡
3.25 考试题目
任务1opengGauss实验环境搭建
考点1：快速安装opengauss
要求：
a. 请使用root用户登录系统，创建/opt/software/openGauss目录，并上传installopenGauss.zip到/opt/software/openGauss目录下，然后解压并执行sh install.sh
mkdir -p /opt/software/openGauss
chmod -R 755 /opt/software/openGauss
命令完成安装
任务2 openGauss SQL基础操作（50分）
实验任务场景：
SQL基本操作是使用openGauss数据库的基础，需要连接其中的基本操作，如DDL、DML等。现要求将如下数据导入到如下所示的两张表中：
数据1：
7369,SMITH,CLERK,7902,1980/12/17,800,0,20
7499,ALLEN,SALESMAN,7698,1981/2/20,1600,300,30
7521,WARD,SALESMAN,7698,1981/2/22,1250,500,30
7566,JONES,MANAGER,7839,1981/4/2,2975,0,20
7654,MARTIN,SALESMAN,7698,1981/9/28,1250,1400,30
7698,BLAKE,MANAGER,7839,1981/5/1,2850,0,30
7782,CLARK,MANAGER,7839,1981/6/9,2450,0,10
7788,SCOTT,ANALYST,7566,1987/4/19,3000,0,20
7839,KING,PRESIDENT,0,1981/11/17,5000,0,10
7844,TURNER,SALESMAN,7698,1981/9/8,1500,0,30
7876,ADAMS,CLERK,7788,1987/5/23,1100,0,20
7900,JAMES,CLERK,7698,1981/12/3,9500,0,30
7902,FORD,ANALYST,7566,1981/12/3,3000,0,20
7934,MILLER,CLERK,7782,1982/1/23,1300,0,10
数据2：
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON
10,ACCOUNTING,NEW YORK
emp 表
| 列名 | 描述 |
|---|---|
| id | 员工编号 |
| ename | 员工姓名 |
| job | 岗位 |
| mgrid | 直属领导编号 |
| hiredate | 入职时间 |
| salary | 工资 |
| bonus | 奖金 |
| deptid | 部门号 |
dept 表
| 列名 | 描述 |
|---|---|
| id | 部门编号 |
| dname | 部门名称 |
| address | 部门所在地 |
考点1：创建数据库dbtest且切换到该库
要求：
a. 以omm用户登录postgres数据库，使用sql命令创建dbtest数据库。
[omm@openGauss1 ~]$ gs_om -t start
Starting cluster.
=========================================
[SUCCESS] openGauss1:
[2025-03-04 12:06:02.189][16190][][gs_ctl]: gs_ctl started,datadir is /opt/install/data/dn
[2025-03-04 12:06:02.192][16190][][gs_ctl]: another server might be running; Please use the restart command
=========================================
Successfully started.
[omm@openGauss1 ~]$ gsql -d postgres -p 15400 -r
gsql ((openGauss 2.1.0 build 590b0f8e) compiled at 2021-09-30 14:29:27 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
openGauss=# create database dbtest;
CREATE DATABASE
b。使用元命令切换到dbtest
openGauss=# \c dbtest
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "dbtest" as user "omm".
考点2: 创建表emp
要求：
a. 在dbtest数据库中创建emp表，表字段如表1所述。
create table emp (
id varchar(100) primary key,
ename varchar(10) not null,
job varchar(30) not null,
mgeid varchar(30) not null,
hiredate varchar(20) not null,
salary varchar(1000) not null,
bonus varchar(1000),
deptid varchar(100) not null
);
b. 在dbtest数据库中创建与员工信息表dept，表字段如表1所述。
create table dept (
id varchar(100) primary key,
dname varchar(100) not null,
address varchar(100) not null
);
考点3：导入数据
要求:
a. 使用 vim 编辑器新建文件 emp.csv，将表1所示数据1写入emp.csv 文件中，并利用 copy元命令导入到 emp 表中。
\q
mkdir /opt/software/tablespace
cat >> /opt/software/tablespace/emp.csv << EOF
7369,SMITH,CLERK,7902,1980/12/17,800,0,20
7499,ALLEN,SALESMAN,7698,1981/2/20,1600,300,30
7521,WARD,SALESMAN,7698,1981/2/22,1250,500,30
7566,JONES,MANAGER,7839,1981/4/2,2975,0,20
7654,MARTIN,SALESMAN,7698,1981/9/28,1250,1400,30
7698,BLAKE,MANAGER,7839,1981/5/1,2850,0,30
7782,CLARK,MANAGER,7839,1981/6/9,2450,0,10
7788,SCOTT,ANALYST,7566,1987/4/19,3000,0,20
7839,KING,PRESIDENT,0,1981/11/17,5000,0,10
7844,TURNER,SALESMAN,7698,1981/9/8,1500,0,30
7876,ADAMS,CLERK,7788,1987/5/23,1100,0,20
7900,JAMES,CLERK,7698,1981/12/3,9500,0,30
7902,FORD,ANALYST,7566,1981/12/3,3000,0,20
7934,MILLER,CLERK,7782,1982/1/23,1300,0,10
EOF
gsql -d dbtest -p 15400 -r
\copy emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) FROM '/opt/software/tablespace/emp.csv' WITH (FORMAT csv, DELIMITER ',', HEADER false);
b. 使用 vim 编辑器新建文件 dept.csv，将表1所示数据2写入dept.csv 文件中，并利用 copy元命令导入到 dept 表中。
\q
cat >> /opt/software/tablespace/dept.csv << EOF
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON
10,ACCOUNTING,NEW YORK
EOF
gsql -d dbtest -p 15400 -r
\copy dept (id,dname,address) from '/opt/software/tablespace/dept.csv' with (format csv, delimiter ',',header false)
考点 4:查询数据
要求:
a. 在 emp 表，按照部门分组统计各个部门的工资总额，结果显示部门号和工资总额。
select deptid as 部门号,sum(salary) as 工资总额 from emp group by deptid;
b. 结合部门表 dept 和 emp，使用左连接的方式查询每个员工所属部门名称，查询结果只需显示员工姓名和部门名称。
select e.ename,d.dname from emp e left join dept d on (e.deptid = d.id);
任务3openGauss复杂SQL查询(100分)
实验任务场景:
某银行 dbtest 数据库中有客户、银行卡、理财产品、保险和基金等几张表。结合学生考试场景，编写 SQL语句。
| 表名 | 列名 |
|---|---|
| client（客户表） | c_id 客户编号，c_name 客户姓名，c_mail 客户邮箱，c_id_card 客户身份证，c_phone 客户手机号，c_password 客户登录密码 |
| bank_card（银行卡表） | b_number 银行卡号，b_type 银行卡类型，b_c_id 所属客户编号 |
| finances_pro duct（理财产品表） | p_name 产品名称，p_id 产品编号，p_description 产品描述，p_amount 购买金额，p_year 理财年限 |
| insurance（保险表） | i_name 保险名称，i_id 保险编号，i_amount 保险金额，i_person 适用人群，i_year 保险年限，i_project 保障项目 |
| fund（基金表） | f_name 基金名称，f_id 基金编号，f_type 基金类型，f_amount 基金金额，risk_level 风险等级，f_manager 基金管理者 |
| property（资产表） | pro_id 资产编号，pro_c_id 客户编号，pro_pif_id 商品编号，pro_type 商品类型，pro_status 商品状态，pro_quantity 商品数量，pro_income 商品收益，pro_purchase_time 购买时间 |
考点 1:单表查询
要求:
a. 请使用元命令将 finance_en.sql 导入到 dbtest 数据库中。
\i /opt/software/openGauss/simpleInstall/finance.sql
b. 请查询 property 资产表中 pro_status 为【Available】的数据，查询结果显示所有字段。
finance=# select * from property where pro_status = '可用';
pro_c_id | pro_id | pro_status | pro_quantity | pro_income | pro_purchase_time
----------+--------+--------------------+--------------+------------+---------------------
5 | 1 | 可用 | 4 | 8000 | 2018-07-01 00:00:00
10 | 2 | 可用 | 4 | 8000 | 2018-07-01 00:00:00
15 | 3 | 可用 | 4 | 8000 | 2018-07-01 00:00:00
(3 rows)
c. 请统计 client 客户表中有多少条记录。
finance=# select count(*) from client ;
count
-------
30
(1 row)
d. 请按照银行卡类型统计 bank card 银行卡表中不同类型银行卡的数量，结果显示为类别及其对应的总数。
finance=# select b_type as 类型,count(*) as 总数 from bank_card group by b_type;
类型 | 总数
-------------------+------
储蓄卡 | 10
信用卡 | 10
(2 rows)
e. 在 insurance 保险表中，请计算保险金额的平均值。
finance=# select avg(i_amount) from insurance ;
avg
-----------------------
2700.0000000000000000
(1 row)
f. 在 insurance 保险表中，请查询保险编号大于2，查询结果按照保险金额字段降序排序，显示字段为保险名称、保额和适用人群。
finance=# select i_name,i_amount,i_person from insurance where i_id > 2 order by i_amount desc;
i_name | i_amount | i_person
--------------+----------+-------------------
意外保险 | 5000 | 所有人
医疗保险 | 2000 | 所有人
财产损失保险 | 1500 | 中年人
(3 rows)
g. 在 finances_product 表中请按照 p_year 理财年限字段统计理财产品的数量，统计结果只包含 p_year 字段和满足该年限的总数，如理财年限 2 年有 10 种理
finance=# select p_year as 年限,count(p_name) as 理财产品 from finances_product group by p_year;
年限 | 理财产品
------+----------
6 | 4
(1 row)
考点 2: 多表查询
a. 要求:请查询用户编号在银行卡表中出现的用户的编号，用户姓名和身份证
finance=# select c.c_id as 编号,c.c_name as 姓名,c.c_id_card as 身份证 from client c join bank_card b on (c.c_id = b.b_c_id);
编号 | 姓名 | 身份证
------+------+----------------------
1 | 张一 | 340211199301010001
3 | 张三 | 340211199301010003
5 | 张五 | 340211199301010005
7 | 张七 | 340211199301010007
9 | 张九 | 340211199301010009
10 | 李一 | 340211199301010010
12 | 李三 | 340211199301010012
14 | 李五 | 340211199301010014
16 | 李七 | 340211199301010016
18 | 李九 | 340211199301010018
19 | 王一 | 340211199301010019
21 | 王三 | 340211199301010021
7 | 张七 | 340211199301010007
23 | 王五 | 340211199301010023
24 | 王六 | 340211199301010024
3 | 张三 | 340211199301010003
26 | 王八 | 340211199301010026
27 | 王九 | 340211199301010027
12 | 李三 | 340211199301010012
29 | 钱二 | 340211199301010029
(20 rows)
b. 请查询保险信息表中保险金额的最大值和最小值所对应的险种和金额，
finance=# select i_name,i_amount from insurance where i_amount = (select max(i_amount) from insurance) union all select i_name,i_amount from insurance where i_amount = ( select min(i_amount) from insurance);
i_name | i_amount
--------------+----------
意外保险 | 5000
财产损失保险 | 1500
(2 rows)
c. 请根据子查询原理，查询保险产品中保险金额大于平均值的保险名称和适用人群。
finance=# select i_name,i_amount,i_person from insurance where i_amount > (select avg(i_amount) from insurance );
i_name | i_amount | i_person
----------+----------+--------------------
人寿保险 | 3000 | 老人
意外保险 | 5000 | 所有人
(2 rows)
任务4 openGauss系统运维管理(90分)
实验任务场景:
数据库运维人员和 DBA 工程师平时都需要对 openGauss 进行管理，尤其是运维人员更是openGauss 系统运维管理的主要人员，需要对openGauss 添加用户、系统进行巡检，比如数据库启动、数据库状态、实例主备切换、例行维护、备份与恢复等。
考点1:查看数据库运行状态
要求:
a. 请使用 gs_om 检查当前数据库集群的详细状态。
[omm@openGauss1 simpleInstall]$ gs_om -t status --detail
[ Cluster State ]
cluster_state : Normal
redistributing : No
current_az : AZ_ALL
[ Datanode State ]
node node_ip port instance state
------------------------------------------------------------------------------------------
1 openGauss1 192.168.0.123 15400 6001 /opt/install/data/dn P Primary Normal
b. 请使用 gs_om 检查指定数据库所在节点的详细状态(如本机)。
[omm@openGauss1 simpleInstall]$ gs_om -t status -h openGauss1 --detail
-----------------------------------------------------------------------
cluster_state : Normal
redistributing : No
-----------------------------------------------------------------------
node : 1
node_name : openGauss1
instance_id : 6001
node_ip : 192.168.0.123
data_path : /opt/install/data/dn
instance_port : 15400
type : Datanode
instance_state : Normal
az_name : AZ1
instance_role : Normal
-----------------------------------------------------------------------
考点 2:日常维护项检查
要求:
a. 请使用 SOL语句查询当前数据库的锁数据，结果显示所有字段。
dbtest=# select * from pg_locks ;
locktype | database | relation | page | tuple | bucket | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | sessionid | mode
| granted | fastpath | locktag | global_sessionid
------------+----------+----------+------+-------+--------+------------+---------------+---------+-------+----------+--------------------+-----------------+-----------------+-----------------+---------+----------+-------------------+------------------
relation | 16384 | 11986 | | | | | | | | | 7/2480 | 281459603740016 | 281459603740016 | AccessShareLock | t | t | 4000:2ed2:0:0:0:0 | 0:0#0
virtualxid | | | | | | 7/2480 | | | | | 7/2480 | 281459603740016 | 281459603740016 | ExclusiveLock
| t | t | 7:9b0:0:0:0:7 | 0:0#0
(2 rows)
b. 使用 gs_dump 命令以 sql文件模式导出 finance 数据库所有表到/home/omm/backup 目录下，文件名为 finance.sql。
[omm@openGauss1 simpleInstall]$ gs_dump dbtest -p 15400 -f /home/omm/backup/finance.sql -U omm -W Dxy@962464
gs_dump[port='15400'][dbtest][2025-03-04 14:35:58]: The total objects number is 396.
gs_dump[port='15400'][dbtest][2025-03-04 14:35:58]: [100.00%] 396 objects have been dumped.
gs_dump[port='15400'][dbtest][2025-03-04 14:35:58]: dump database dbtest successfully
gs_dump[port='15400'][dbtest][2025-03-04 14:35:58]: total time: 958 ms
c. 使用 gs_dump 以 tar 文件模式导出 finance 数据库中所有表到/home/omm/backup 目录下，文件名为 finance.tar。
[omm@openGauss1 simpleInstall]$ gs_dump dbtest -p 15400 -F t -f /home/omm/backup/finance.tar -U omm -W Dxy@962464
gs_dump[port='15400'][dbtest][2025-03-04 14:44:09]: The total objects number is 396.
gs_dump[port='15400'][dbtest][2025-03-04 14:44:09]: [100.00%] 396 objects have been dumped.
gs_dump[port='15400'][dbtest][2025-03-04 14:44:09]: dump database dbtest successfully
gs_dump[port='15400'][dbtest][2025-03-04 14:44:09]: total time: 1004 ms
d. 请使用 gs_check 命令检查 openGauss 集群的健康状态。
[omm@openGauss1 simpleInstall]$ gs_check -i CheckClusterState
Parsing the check items config file successfully
Distribute the context file to remote hosts successfully
Start to health check for the cluster. Total Items:1 Nodes:1
Checking... [=========================] 1/1
Start to analysis the check result
CheckClusterState...........................OK
The item run on 1 nodes. success: 1
Analysis the check result successfully
Success. All check items run completed. Total:1 Success:1
For more information please refer to /opt/install/om/script/gspylib/inspection/output/CheckReport_202503045327255817.tar.gz
e. 请检查 openGauss 数据库中慢 SQL，起止时间:以当前时间作为结束时间，开始时间为前P1 小时的时间。
openGauss#= ALTER USER jacK IDENTIFIEDBY'openGauss@2024'REPLACE 'openGauss@2022';
f. 请利用 gs_restore 工具将 finance.tar 导入到 copy finance 数据库中(如没有，则需要新建)
[omm@openGauss1 simpleInstall]$ gs_restore -p 15400 -U omm -W Dxy@962464 -d finance /home/omm/backup/finance.tar
考点3：用户权限管理
要求：
a. 请利用 gsql连接数据库后，创建用户jack、登录密码为 openGauss@2022。
openGauss=# create user jack with password 'openGauss@2022';
CREATE ROLE
b. 将用户 jack 的登录密码由 openGauss@2022 修改为 openGauss@2024。
openGauss=# alter user jack password 'openGauss@2024';
ALTER ROLE
c. 将用户 jack 授予数据库管理员权限。
openGauss=# GRANT ALL PRIVILEGES To jack;
ALTER ROLE
d. 将 jack 用户进行锁定。
openGauss=# alter user jack with nologin ;
ALTER ROLE
e. 将 jack 用户进行解锁。
openGauss=# alter user jack with login ;
ALTER ROLE
任务5openGauss数据库开发(50分)
实验任务场景:
数据库包括存储过程的开发，自定义函数的开发以及JDBC 开发等，
考点1:存储过程开发
要求:
a. 在 dbtest 数据库中，新建一张学生信息表 student，字段包括学生 ID、学生姓名 sname、学生年龄 age、学生性别 gender、学生所属院系 deptld。
dbtest=# create table student (
s_id varchar(10) primary key,
s_ename varchar(10) not null,
s_age varchar(100) not null,
s_gender varchar(10) not null,
s_deptld varchar(100) not null
);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "student_pkey" for table "student"
CREATE TABLE
b. 创建存储过程 insert student 实现将5 条数据插入到 student 表中。
dbtest=# create or replace procedure st_or()
as
begin
insert into student (s_id,s_ename,s_age,s_gender,s_deptld) values ('1','冉总','18','男','红警IE班');
insert into student (s_id,s_ename,s_age,s_gender,s_deptld) values ('2','王总','19','男','冰天雪地班');
insert into student (s_id,s_ename,s_age,s_gender,s_deptld) values ('3','赵总','20','男','openhnjm');
insert into student (s_id,s_ename,s_age,s_gender,s_deptld) values ('4','李总','21','男','秋天不回来');
insert into student (s_id,s_ename,s_age,s_gender,s_deptld) values ('5','范总','21','男','重装大兵班');
END;
dbtest$# /
CREATE PROCEDURE
c. 调用 insert student 存储过程。
dbtest=# call st_or();
st_or
-------
(1 row)
考点2：自定义函数开发
要求：
a. 在 dbtest 数据库中，创建一张商品价格表，字段包括商品ID，商品名称、商品价格、商品库存。
dbtest=# CREATE TABLE goods (
dbtest(# g_id SERIAL PRIMARY KEY,
dbtest(# g_name VARCHAR(255) NOT NULL,
dbtest(# g_price DECIMAL(10, 2) NOT NULL,
dbtest(# g_stock INT NOT NULL DEFAULT 0
dbtest(# );
NOTICE: CREATE TABLE will create implicit sequence "goods_g_id_seq" for serial column "goods.g_id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "goods_pkey" for table "goods"
CREATE TABLE
b. 写一个存储过程 insert_goods，将10 条记录插入 goods 表中。
dbtest=# CREATE OR REPLACE PROCEDURE insert_goods()
AS
BEGIN
INSERT INTO goods (g_id, g_name, g_price, g_stock) VALUES (1, '香蕉', 4.31, 91);
INSERT INTO goods (g_id, g_name, g_price, g_stock) VALUES (2, '萝卜', 0.39, 46);
INSERT INTO goods (g_id, g_name, g_price, g_stock) VALUES (3, '老鼠药', 6.00, 1168);
INSERT INTO goods (g_id, g_name, g_price, g_stock) VALUES (4, '好东西', 11.00, 88888);
INSERT INTO goods (g_id, g_name, g_price, g_stock) VALUES (5, '坏东西', 3.00, 0); -- 修正为整数
INSERT INTO goods (g_id, g_name, g_price, g_stock) VALUES (6, 'v3火箭', 120.00, 19);
INSERT INTO goods (g_id, g_name, g_price, g_stock) VALUES (7, '自爆卡车', 130.00, 21);
INSERT INTO goods (g_id, g_name, g_price, g_stock) VALUES (8, '谭雅', 10.00, 20);
INSERT INTO goods (g_id, g_name, g_price, g_stock) VALUES (9, '牛牛', 100.00, 2000);
INSERT INTO goods (g_id, g_name, g_price, g_stock) VALUES (10, '灰熊坦克', 900.00, 400);
END;
dbtest$# /
CREATE PROCEDURE
c. 调用 insert_goods 存储过程，执行存储过程。
dbtest=# call insert_goods();
insert_goods
--------------
(1 row)
d. 创建自定义函数，实现对每种商品库存加100。
dbtest=# create or replace procedure update_goods_stock()
dbtest-# as
dbtest$# begin
dbtest$# update goods set g_stock = g_stock + 100 ;
dbtest$# end;
dbtest$# /
CREATE PROCEDURE
e. 调用自定义函数。
dbtest=# call update_goods_stock();
update_goods_stock
--------------------
(1 row)
【版权声明】本文为华为云社区用户原创内容，未经允许不得转载，如需转载请自行联系原作者进行授权。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）