2022年河北职业院校-数据库技能大赛试卷(A) 参考答案_数据库运行与管理技能竞赛题库及答案-CSDN博客

# mount /install/KingbaseES_V008R006C005B0023_Lin64_single_install.iso /mnt
# su - kingbase
$ bash /mnt/setup.sh (后续安装步骤略)
$ su - root
# /KingbaseES/V8/Scripts/root.sh

EXAM01-数

二、配置KES安装用户的环境变量（4分）

1、允许数据库命令不带路径就可以执行。（2分）

2、通过环境变量指定数据库默认所在的目录和默认端口号。（2分）

$ vim /home/kingbase/.bashrc

export PATH=$PATH:/KingbaseES/V8/Server/bin/

export KINGBASE_DATA=/data

export KINGBASE_PORT=52022

$ source /home/kingbase/.bashrc

EXAM02-数据库参数配置

一、KES检查点参数优化（3分）

在KES的主配置文件中配置参数checkpoint_completion_target的值为0.9，并使参数生效。

$ echo 'checkpoint_completion_target = 0.9' >>/data/kingbase.conf

$ sys_ctl reload

二、配置共享内存(3分)

要求使用alter system命令配置数据库共享内存约为512MB，并使参数生效。

test=# alter system set shared_buffers='512MB';

$ sys_ctl restart -D /data

三、配置用户登录认证 (4分)

1、配置system用户只能在服务器本地执行免密登录。（2分）

2、配置其它用户只能在和数据库服务器同一个子网内执行MD5认证登录。（2分）

$ vim /data/sys_hba.conf

local all system trust

host all system 127.0.0.1/32 trust

host all system 0.0.0.0/0 reject

host all all 192.168.40.0/24 md5

host all all ::1/128 md5

host all all ::0/0 md5

$ sys_ctl restart 或 sys_ctl reload

EXAM03-数据库管理

一、用户的管理(4分)

创建用户user_admin、 user_rw、user_r1、user_r2，要求如下：

1、创建用户时使用默认权限,密码均为kingbase (2分)

2、允许用户user_r1最多可同时创建10个连接 (2分)

test=# create user user_admin password 'kingbase';

test=# create user user_rw password 'kingbase';

test=# create user user_r1 connection limit 10 password 'kingbase';

test=# create user user_r2 password 'kingbase';

二、表空间的管理(3分)

1、创建目录/home/kingbase/oa并设置合适的权限 (2分)

2、创建表空间tbsoa并指定存储目录为/home/kingbase/oa (1分)

$ mkdir /home/kingbase/oa

$ chmod 700 /home/kingbase/oa

test=# create tablespace tbsoa location '/home/kingbase/oa';

三、数据库的管理(3分)

创建数据库oadb，要求如下

1、默认字符集UTF8 (1分)

2、数据库所有者为user_admin (1分)

3、默认表空间为tbsoa (1分)

test=# create database oadb owner user_admin encoding UTF8 tablespace tbsoa;

四、模式的管理(2分)

1、在数据库oadb中新建模式oa (1分)

2、模式oa的所有者为用户user_rw (1分)

$ ksql oadb system

oadb=# create schema oa authorization user_rw;

五、表的管理(9分)

1、创建和修改表（5分）

（1）使用system用户登录test数据库,创建模式s01；（1分）

（2）执行脚本/install/ EXAM_v0.11.sql，生成样例模式exam；（1分）

（3）参照exam中score和 course表，使用CTAS的方式在s01模式中创建同名表；（1分）

（4）增加约束，实现s01.score表的cno列参照引用s01.course表的cno列；（2分）

$ ksql test system

test=# create schema s01;

test=# \i /install/EXAM_v0.11.sql

test=# \q

$ ksql test system

test=# create table s01.score as select * from exam.score;

test=# create table s01.course as select * from exam.course;

test=# alter table s01.course add constraint course_pk primary key (cno);

test=# alter table s01.score add constraint score_fk foreign key (cno) references s01.course(cno);

2、表的备份和还原

（1）将s01.score和s01.course表备份至/backup/dump/s01.dmp,备份为dump格式；（2分）

（2）将备份文件/backup/dump/s01.dmp恢复到oadb库的oa模式中。（2分）

$ sys_dump -Usystem -dtest -n s01 -Fc -f '/backup/dump/s01.dmp'

$ sys_restore -Usystem -doadb -g s01 -G oa /backup/dump/s01.dmp

六、视图的管理(3分)

使用system用户登录oadb库执行以下操作：

1、为表oa.course和oa.score表创建视图oa.v_info，视图列含sno,cno,cname,score。（1分）

2、要求视图只显示课程编号为10且考试分数小于80分的记录。（2分）

$ ksql oadb system

oadb=# create view oa.v_info

oadb-# as

oadb-# select sno,s.cno,cname,score from oa.score s,oa.course c where c.cno=s.cno and c.cno=10 and s.score<80;

七、权限的管理(6分)

使用system用户登录oadb库执行以下操作：

1、授权user_r1和user_r2用户对oa模式具有usage权限；（2分）

2、授权用户user_r1只能查询表oa.score和oa.course；（2分）

3、授权用户user_r2能查询和更新表oa.score和oa.course。（2分）

$ ksql oadb system

oadb=# grant usage on schema oa to user_r1,user_r2;

oadb=# grant select on oa.score,oa.course to user_r1;

oadb=# grant select ,update on oa.score,oa.course to user_r2;

EXAM04-物理备份与还原

执行物理备份 (10分)

1、物理备份存储目录设置为/backup/rman/。（1分）

2、设置操作系统定时备份策略：

（1）每日凌晨2点全备；（1分）

（2）每日凌晨4点增备；（1分）

（3）备份文件保留策略设置成冗余份数为2；（1分）

3、执行全量物理备份。（2分）

4、执行增量物理备份。（2分）

5、执行差异物理备份。（2分）

$ cd /KingbaseES/V8/Server/share/

$ cp sys_backup.conf ../bin/

$ vi /KingbaseES/V8/Server/bin/sys_backup.conf

#file: sys_backup.conf（修改标黄部分内容）

#dest dir: <cluster_dir>/kingbase/bin/sys_backup.conf

#dest dir: <cluster_dir>/kingbase/share/sys_backup.conf

# target db style enum: single/cluster

_target_db_style="single"

# one kingbase node IP

# just provide one IP, script will use 'repmgr cluster show' get other node IP

_one_db_ip="127.0.0.1"

# local repo IP, inner-REPO, must be same as one_db_ip, means repo located in one db node

# outer repo IP, outer-REPO, means repo located in outer node

_repo_ip="127.0.0.1"

# label of this cluster

_stanza_name="kingbase"

# OS user name of database

_os_user_name="kingbase"

# !!!! dir to store the backup files

# should be accessable for the OS user

_repo_path="/backup/rman"

# count of keep, over the count FULL-backup will be remove

_repo_retention_full_count=2

# count of days, interval to do FULL-backup

_crond_full_days=1

# count of days, interval to do DIFF-backup

_crond_diff_days=0

# count of days, interval to do INCR-backup

_crond_incr_days=1

# HOUR to do the FULL-backup

_crond_full_hour=2

# HOUR to do the DIFF-backup

_crond_diff_hour=0

# HOUR to do the INCR-backup

_crond_incr_hour=4

# OS cmd define

_os_ip_cmd="/sbin/ip"

_os_rm_cmd="/bin/rm"

_os_sed_cmd="/bin/sed"

_os_grep_cmd="/bin/grep"

# !!! these follow 4 parameter ONLY for single style

# data dir of single

_single_data_dir="/data"

# bin dir of single

_single_bin_dir="/KingbaseES/V8/Server/bin"

# database user of single

_single_db_user="system"

# database port of single

_single_db_port="52022"

$ vi /data/sys_hba.conf（在指定位置添加标黄内容）

# TYPE DATABASE USER ADDRESS METHOD

# "local" is for Unix domain socket connections only

local all all trust

# IPv4 local connections:

host all system 127.0.0.1/32 trust

host all all 127.0.0.1/32 scram-sha-256

host all all 0.0.0.0/0 scram-sha-256

# IPv6 local connections:

host all all ::1/128 scram-sha-256

host all all ::0/0 scram-sha-256

# Allow replication connections from localhost, by a user with the

# replication privilege.

local replication all trust

host replication all 127.0.0.1/32 scram-sha-256

host replication all ::1/128 scram-sha-256

$ vi /data/kingbase.conf（添加如下参数）

archive_mode = on

archive_command = ''

$ sys_ctl restart -D /data

$ sys_backup.sh init

$ sys_backup.sh start

-- 全量

$ sys_rman --config=/backup/rman/sys_rman.conf \

--stanza=kingbase \

--archive-copy \

--type=full backup

-- 差异

$ sys_rman --config=/backup/rman/sys_rman.conf \

--stanza=kingbase \

--archive-copy \

--type=diff backup

-- 增量

$ sys_rman --config=/backup/rman/sys_rman.conf \

--stanza=kingbase \

--archive-copy \

--type=incr backup

-- 查看备份集

$ sys_rman --config=/backup/rman/sys_rman.conf --stanza=kingbase info

EXAM05-运维与优化

一、数据库巡检（4分）

1、查看数据库列表。（1分）

2、通过数据库version函数检查数据库版本信息。（1分）

3、通过数据库get_license_validdays函数检查数据库license有效期。（1分）

4、通过数据库sys_postmaster_start_time函数查看实例启动时间。（1分）

5、将以上巡检命令和巡检结果保存/backup/dbcheck.txt；

$ ksql -dtest -Usystem

test=# \o /backup/dbcheck.txt

test=# \l

test=# select version;

test=# select get_license_validdays;

test=# select sys_postmaster_start_time;

test=# \o

二、SQL基础(12分)

使用system用户登录test数据库，使用“\d”查看exam模式中相关表的结构，执行以下操作:

1、从exam模式的score、course、student表中查询满足条件的记录。（4分）

（1）查找每门课程考试成绩高于本门课程平均成绩的学员信息；

（2）显示学员编号，学员姓名、课程编号、课程名称、考试分数，本门课程平均成绩；

（3）请将sql语句和运行结果保存至/backup/sql-A01.txt；

test=# SELECT s.sno AS "学员编号",

st.sname AS "学员姓名",

s.cno AS "课程编号",

c.cname AS "课程名称",

s.score AS "考试分数",

tt.ascore AS "本门课程平均成绩"

FROM

exam.score s

INNER JOIN exam.course c ON c.cno=s.cno

INNER JOIN exam.student st ON s.sno=st.sno

INNER JOIN (SELECT cno,avg(score) ascore FROM exam.score GROUP BY cno) tt

ON s.cno=tt.cno AND s.score>tt.ascore;

或者

test=# SELECT s.sno AS "学员编号",

st.sname AS "学员姓名",

s.cno AS "课程编号",

c.cname AS "课程名称",

s.score AS "考试分数",

s.ascore AS "本门课程平均成绩"

FROM

(SELECT *,avg(score) OVER(PARTITION BY cno) ascore FROM exam.score) s

INNER JOIN exam.course c ON c.cno=s.cno

INNER JOIN exam.student st ON s.sno=st.sno

WHERE s.score>s.ascore;

----------+-----------+----------+----------+----------+---------------------

1002 | Yang Yang | 10 | KCA | 94 | 85.1428571428571429

1003 | Liu Wei | 10 | KCA | 96 | 85.1428571428571429

1004 | Sun Juan | 10 | KCA | 88 | 85.1428571428571429

1004 | Sun Juan | 20 | KCP | 93 | 89.0000000000000000

1002 | Yang Yang | 30 | KCM | 97 | 87.0000000000000000

(5 行记录)

2、查询exam.score表中10号课程的考试分数按从高到低排列前三的学员信息。（4分）

（1）显式学员编号、课程编号、考试分数；

（2）请将sql语句和运行结果保存至/backup/sql-A02.txt；

test=# SELECT sno AS "学员编号",cno AS "学员姓名",score AS "考试分数"

FROM exam.score

WHERE cno=10

ORDER BY score desc

LIMIT 3;

或者

test=# SELECT sno AS "学员编号",cno AS "学员姓名",score AS "考试分数"

FROM (SELECT sno,cno,score FROM exam.score

WHERE cno=10

ORDER BY score desc)

WHERE rownum<=3;

学员编号 | 学员姓名 | 考试分数

----------+----------+----------

1003 | 10 | 96

1002 | 10 | 94

1004 | 10 | 88

(3 行记录)

3、从exam模式的course、score表中查询每门课程的统计信息。（4分）

（1）要求显示课程编号、课程名称、课程平均分数、参加考试人数、通过考试人数。

（2）请将sql语句和运行结果保存至/backup/sql-A03.txt；

test=# SELECT c.cno AS "课程编号",

c.cname AS "课程名称",

avg(nvl(score,0)) AS "课程平均分数",

count(sno) AS "参加考试人数",

(SELECT count(*) FROM exam.score WHERE cno=c.cno AND certificate='t') AS "通过考试人数"

FROM exam.course c LEFT JOIN exam.score s ON c.cno=s.cno

GROUP BY c.cno,c.cname;

或者

test=# SELECT c.cno AS "课程编号",

c.cname AS "课程名称",

avg(nvl(score,0)) AS "课程平均分数",

count(sno) AS "参加考试人数",

sum(CASE certificate WHEN 't' THEN 1 ELSE 0 END) AS "通过考试人数"

FROM exam.course c LEFT JOIN exam.score s ON c.cno=s.cno

GROUP BY c.cno,c.cname;

课程编号 | 课程名称 | 课程平均分数 | 参加考试人数 | 通过考试人数

----------+----------+---------------------+--------------+--------------

20 | KCP | 89.0000000000000000 | 4 | 4

10 | KCA | 85.1428571428571429 | 7 | 6

30 | KCM | 87.0000000000000000 | 4 | 4

(3 行记录)

三、SQL优化（6分）

1、system用户登录oadb库后，使用以下语句创建测试表和插入测试数据。（1分）

$ ksql oadb system

create table oa.t01(col1 int,col2 text,col3 int,col4 int);

create table oa.t02(col1 int,col2 text);

insert into oa.t01 select generate_series(1,1000000),md5(random()),random()*10000,random()*1000;

insert into oa.t02 select generate_series(1,100000),md5(random());

2、通过添加索引的方式来优化查询，控制实际执行时间在1ms之内。（4分）

$ ksql oadb system

oadb=# create table oa.t01(col1 int,col2 text,col3 int,col4 int);

oadb=# create table oa.t02(col1 int,col2 text);

oadb=# insert into oa.t01 select generate_series(1,1000000),md5(random()),random()*10000,random()*1000;

oadb=# insert into oa.t02 select generate_series(1,100000),md5(random());

oadb=# create index idx_t01 on oa.t01(col1);

oadb=# create index idx_t02 on oa.t02(col1);

oadb=# explain analyze SELECT a.* FROM oa.t01 a,oa.t02 b WHERE a.col4=b.col1 AND a.col1 = 100;

QUERY PLAN

--------------------------------------------------------------------------------------------------------------------

Merge Join (cost=8.75..10.61 rows=1 width=48) (actual time=0.041..0.043 rows=1 loops=1)

Merge Cond: (b.col1 = a.col4)

-> Index Only Scan using idx_t02 on t02 b (cost=0.29..3452.29 rows=100000 width=6) (actual time=0.005..0.011 rows=40 loops=1)

Heap Fetches: 40

-> Sort (cost=8.45..8.46 rows=1 width=48) (actual time=0.025..0.025 rows=1 loops=1)

Sort Key: a.col4

Sort Method: quicksort Memory: 25kB

-> Index Scan using idx_t01 on t01 a (cost=0.42..8.44 rows=1 width=48) (actual time=0.021..0.022 rows=1 loops=1)

Index Cond: (col1 = '100'::numeric)

Planning Time: 0.311 ms

Execution Time: 0.058 ms

(11 行记录)

四、对象占用空间管理（7分）

使用system用户登录oadb数据库执行以下操作:

1、使用下面的SQL在oa模式中创建表t03。（1分）

CREATE TABLE oa.t03 with (autovacuum_enabled=off) as SELECT * FROM oa.t01;

2、在t03表的col1字段上创建索引idx_t03。（2分）

3、使用下面的SQL删除t03表中50%的数据。（1分）

delete from oa.t03 where col1<=250000 or col1>750000;

4、手动释放t03表删除的数据行所占用的空间，查看空间占用结果。（3分）

$ ksql oadb system

oadb=# CREATE TABLE oa.t03 with (autovacuum_enabled=off) as SELECT * FROM oa.t01;

oadb=# CREATE INDEX idx_t03 on oa.t03(col1);

oadb=# delete from oa.t03 where col1<=250000 or col1>750000;

oadb=# vacuum full oa.t03;

oadb=# select sys_relation_size('oa.t03'); 或 \dt+ oa.t03

2022年河北职业院校-数据库技能大赛 试卷(A) 参考答案

EXAM01-数据库部署

一、部署数据库软件及服务（12分）

EXAM01-数

二、配置KES安装用户的环境变量（4分）

EXAM02-数据库参数配置

一、KES检查点参数优化（3分）

二、配置共享内存(3分)

三、配置用户登录认证 (4分)

EXAM03-数据库管理

一、用户的管理(4分)

二、表空间的管理(3分)

三、数据库的管理(3分)

四、模式的管理(2分)

五、表的管理(9分)

六、视图的管理(3分)

七、权限的管理(6分)

EXAM04-物理备份与还原

执行物理备份 (10分)

EXAM05-运维与优化

一、数据库巡检 （4分）

二、SQL基础(12分)

三、SQL优化（6分）

四、对象占用空间管理（7分）

2022年河北职业院校-数据库技能大赛试卷(A) 参考答案

一、数据库巡检（4分）