2023年河北职业院校-数据库技能大赛
试卷(B)参考答案
目录
EXAM01-数据库部署
一、部署数据库软件及服务（12分）
1、查看/install目录中的安装文件和授权文件，并挂载KES的安装文件。
（1）查看安装文件，KingbaseES_V008R006C005B0023_Lin64_single_install.iso；（1分）
（2）查看授权文件，安装时请选择企业版授权license_enterprise.dat；（1分）
（3）挂载安装文件到/mnt目录。（2分）
2、执行安装KingbaseES V8R6（图形化界面或字符界面安装均可）,要求如下：
（1）数据库管理员SYSTEM的密码设置为kingbase；
（2）数据库监听端口号设置为52022； （2分）
（3）数据库字符集设置为UTF8； （2分）
（4）配置操作系统启动时自动启动数据库服务。 （4分）
| # mount /install/KingbaseES_V008R006C005B0023_Lin64_single_install.iso /mnt # su - kingbase $ bash /mnt/setup.sh (后续安装步骤略) $ su - root # /KingbaseES/V8/Scripts/root.sh |
二、配置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模式至/backup/dump/s01.tar,备份为tar格式；（2分）
（2）将备份文件/backup/dump/s01.tar恢复到oadb库的oa模式中。 （2分）
| $ sys_dump -Usystem -dtest -n s01 -Ft -f '/backup/dump/s01.tar' $ sys_restore -Usystem -doadb -g s01 -G oa /backup/dump/s01.tar |
六、视图的管理(3分)
使用system用户登录oadb库执行以下操作：
1、为表oa.course和oa.score创建视图oa.v_info，视图列含sno,cno,cname,score。（1分）
2、要求视图只显示课程编号为20，考试分数大于90分的记录。（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=20 and s.score>90; |
七、权限的管理(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数据库执行以下操作:
1、从exam.score表中查询满足条件的记录。 （4分）
（1）查找每门课程考试成绩低于本门课程平均成绩的学员信息；
（2）显示学员编号，学员姓名、课程编号、课程名称、考试分数，本门课程平均成绩；
（3）请将sql语句和运行结果保存至/backup/sql-B01.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; 学员编号 | 学员姓名 | 课程编号 | 课程名称 | 考试分数 | 本门课程平均成绩 ----------+--------------+----------+----------+----------+--------------------- 1001 | Zhang San | 10 | KCA | 85 | 85.1428571428571429 1005 | Li Xiaofeng | 10 | KCA | 68 | 85.1428571428571429 1005 | Li Xiaofeng | 10 | KCA | 85 | 85.1428571428571429 1007 | Feng Xiaoyue | 10 | KCA | 80 | 85.1428571428571429 1001 | Zhang San | 20 | KCP | 88 | 89.0000000000000000 1002 | Yang Yang | 20 | KCP | 86 | 89.0000000000000000 1001 | Zhang San | 30 | KCM | 83 | 87.0000000000000000 1003 | Liu Wei | 30 | KCM | 85 | 87.0000000000000000 1008 | Qin Shanshan | 30 | KCM | 83 | 87.0000000000000000 (9 行记录) |
2、查询exam.score表中20号课程的考试分数按从高到低排列前三的学员信息。 （4分）
（1）显式学员编号、课程编号、考试分数；
（2）请将sql语句和运行结果保存至/backup/sql-B02.txt。
| test=# SELECT sno AS "学员编号",cno AS "学员姓名",score AS "考试分数" FROM exam.score WHERE cno=20 ORDER BY score desc LIMIT 3; 或者 test=# SELECT sno AS "学员编号",cno AS "学员姓名",score AS "考试分数" FROM (SELECT sno,cno,score FROM exam.score WHERE cno=20 ORDER BY score desc) WHERE rownum<=3; 学员编号 | 学员姓名 | 考试分数 ----------+----------+---------- 1004 | 20 | 93 1003 | 20 | 89 1001 | 20 | 88 (3 行记录) |
3、查询每门课程的统计信息。 （4分）
（1）要求显示课程编号、课程名称、课程最高分数、参加考试人数、通过考试人数。
（2）请将sql语句和运行结果保存至/backup/sql-B03.txt；
| test=# SELECT c.cno AS "课程编号", c.cname AS "课程名称", max(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 "课程名称", max(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 | 93 | 4 | 4 10 | KCA | 96 | 7 | 6 30 | KCM | 97 | 4 | 4 (3 行记录) |
三、SQL优化（6分）
1、system用户登录oadb库后，使用以下语句创建测试表和插入测试数据。 （2分）
| $ 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; |
2、手动释放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 |
EXAM06-DBA职业素养
1、请对DBA的职业素养进行描述（5分）
扫一扫
3019
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
