jiang DEMO4

1、数据库连接
（1）查看全局最大连接数
 Show max_connections
（2）创建用户并指定最大连接数指定最大连接数为 20000
 Create user test password “test@123” connection limit 20000;
（3）查看用户的连接数，展示用户、最大连接数
 select rolname,rolconnlimit from pg_rolse where rolname='test';
（4）修改用户最大连接数，将最大连接数修改为 10000
 Alter user test connection limit 10000;
(5)创建数据库并指定最大连接数，指定最大连接数为 100000，并使用 SQL 查看
展示数据库名称、最大连接数
 --创建数据库
create database testdb connection limit 100000; --查看数据库最大连接数
select datname,datconnlimit from pg_database where datname='testdb';
（7）修改数据库的最大连接数，将最大连接数更改为 200000；
 Alter database testdb connection limit 200000;
2、安全审计
（1）创建用户 user1，密码为“test@123”
 Create user user1 password “test@123” （2）给用户授予查看审计权限，同时可以创建审计策略
 Alter user user1 auditadmin poladmin;
(3)切换值 user1 用户，创建审计策略 adt1，对数据库执行 create 操作审计
 --DAS 右上角切换
--切换到实例管理界面在参数管理模块，修改参数 enable_security_policy 为 On
--创建审计策略 adt1
Create audit policy adt1 privilege create;
（4）创建审计策略 adt2，数据库执行 select 操作创建审计策略
 Create audit policy adt2 access select;
（5）修改 adt1 对 IP 地址‘10.20.30.40’进行审计
 Alter audit policy adt1 modify (filter ip(‘10.20.30.40’));
（6）创建表 tb1，字段自定义
 Create table tb1(id int);
（7）创建审计策略 adt3,仅审计记录用户 root 在执行针对表 tb1 资源进行
select 、 insert、delete 操作。
 --给表创建资源标签
Create resource label rl01 add table(tb1); --创建审计策略
Create audit policy adt3 access select on label(rl01),insert on label(rl01),delete on
label(rl01) filter on roles(root);
(8)关闭审计策略 adt1
 Alter audit policy adt1 disable ;
(9)删除以上创建审计策略，级联删除用户 user1

--删除审计策略
Drop audit policy adt1,adt2,adt3; --删除资源标签
Drop resource label rl01; --删除用户
Drop user user1 cascade;

6、游标
以下为表创建 SQL 语句
create table teacher(
id integer not null, name varchar(50) not null, deptno integer not null, salary float not null, title varchar(100) not null --职称：讲师，副教授，教授
);
create table department(
id integer not null, name varchar(50) not null);
(1)创建存储过程 pro_curs_1,使用有游标打印各部门总人数，按人数降序排序，
打印格式如下：
部门名称 1 ---人数
部门名称 2 ---人数
打印操作可以使用 DBE_OUTPUT.PRINT_LINE(outputstr)接口
 create or replace procedure pro_curs_1()
as
declare cursor cur1 is select d.name as dname ,count(*)
as scount from teacher t,department d where t.deptno=d.id
group by d.name order by scount desc;
begin
for i in cur1 loop
DBE_OUTPUT.PRINT_LINE(concat(i.dname,'---',i.scount::varchar));
end loop;
end;
/
call pro_curs_1;
(2)创建存储过程 pro_curs_2，使用游标读取薪水降序排序的前三位老师和后三
位老师的信息，分别获取 ID,姓名，部门名称，薪水和职称，请按一下格式
打印。
ID-姓名-部门名称-薪水-职称
 create or procedure pro_curs_2()
as
declare cursor cur1 is select sid,sname,dname,salary titile from
((select * from teacher order by salary desc limit 3) union all
(select * from teacher order by salary limit 3)) t join department d
on t.partno=d.id;
begin
for i in cur1 loop
DBE_OUTPUT.PRINT_LINE(concat(i.sid,'-',i.sname,'-',i.dname,'-',
i.salary,'-',i.title));
end;
/
Call pro_curs_2():

第七套模拟题：
1、数据库对象管理及 SQL 应用 1
学生表（成绩里面有空值）
--建表&导入数据
create table student(id int ,score int ,month int);
insert into student values(1,56,1),(2,68,1),(3,null,1),(4,67,1),(5,99,1),(6,null,2);
（1）查询月考平均成绩比学生编号 5 的大的学生
 select id ,round(avg(nvl(score,0)),2) avgscore from student group by id having
avgscore >(select round(avg(nvl(score,0)),2) from student where id=5);
（2）查询每次月考成绩大于平均成绩的学生
 select s1.*,avgscore from student as s1 join(select month,round(avg(nvl(score,0)),2)
as avgscore from student group by month ) as s2 on s1.month=s2.month where score>avgscore;
（3）查询每次平均成绩差值
 select month ,round(avg(nvl(score,0)),2) as avgscore ,avgscore -lag(avgscore)
over(order by month) as diff from student group by month order by month;
2、数据库对象管理及 SQL 应用 2
当前有一张订单表 lineitem,具体字段如下：
L_ORDERKEY BIGINT NOT NULL, --订单 key
L_PARTKEY BIGINT NOT NULL , --配件 key
L_SUPPKEY BIGINT NOT NULL , --供应商 key
L_LINENUMBER BIGINT NOT NULL , --流水号
L_QUANTITY float8 NOT NULL , --数量
L_EXTENDEPPRICE float8 NOT NULL, --出厂价
L_DISCOUNT float8 NOT NULL, --折扣
L_TAX float8 NOT NULL , --税点
L_RETURNFLAG CHAR(1) NOT NULL , --原返标志
L_LINESTATUS CHAR(1) NOT NULL , --明细
L_SHIPDATE DATE NOT NULL , --发货日期
L_COMMITDATE DATE NOT NULL , --预计到达日期
L_ARRIVALDATE DATE NOT NULL, --到达时间
L_ORDERSTRATEGY CHAR(32) NOT NULL, --订单处理策略
L_TRANSPORTROUTE CHAR(32) NOT NULL, --运输路径
L_COMMENT VARCHAR(64) NOT NULL --备注
--导入数据
insert into lineitem
values
(33,306676,44195,1,31,52162.46,0.09,0.04,'A','F','1993-10-29','1993-11-08','1993-11-01','COLLECT
COD','TRUCK','ng to thefuriously ...'), (33,169588,19589,4,41,67960.78,0.09,0.00,'R','F','1993-11-09','1994-01-24','1993-11-11','TAKE
BACK','RETURN','unusualpacka ...'), (68,174896,37400,3,46,90660.94,0.04,0.05,'n','o','1998-08-13','1998-07-08','1998-08-29','NONE','RAIL','ehulor
depen ...odeng');
（1）创建分区表，根据上述字段信息创建分区表，按 L_SHIPDATE 分区，按年份
1993，1994，1995，1996，1997，1998，1999 ，分区名称分别为 L_SHIPDATE_1, 第二个分区名称 L_SHIPDATE_2 依次类推，使用 L_ORDERKEY 进行哈希分布，建表
完成后执行上方数据导入代码导入数据。
 create table lineitem(
L_ORDERKEY BIGINT NOT NULL, L_PARTKEY BIGINT NOT NULL, L_SUPPKEY BIGINT NOT NULL, L_LINENUMBER BIGINT NOT NULL, L_QUANTITY float8 NOT NULL , L_EXTENDEPPRICE float8 NOT NULL , L_DISCOUNT float8 NOT NULL , L_TAX float8 NOT NULL , L_RETURNFLAG CHAR(1) NOT NULL, L_LINESTATUS CHAR(1) NOT NULL, L_SHIPDATE DATE NOT NULL, L_COMMITDATE DATE NOT NULL , L_ARRIVALDATE DATE NOT NULL, L_ORDERSTRATEGY CHAR(32) NOT NULL, L_TRANSPORTROUTE CHAR(32) NOT NULL, L_COMMENT VARCHAR(64) NOT NULL
)distribute by hash(L_ORDERKEY)
partition by range(L_SHIPDATE)
(
partition L_SHIPDATE_1 VALUES LESS THEN('1993-01-01'), partition L_SHIPDATE_1 VALUES LESS THEN('1994-01-01'), partition L_SHIPDATE_1 VALUES LESS THEN('1995-01-01'), partition L_SHIPDATE_1 VALUES LESS THEN('1996-01-01'), partition L_SHIPDATE_1 VALUES LESS THEN('1997-01-01'), partition L_SHIPDATE_1 VALUES LESS THEN('1998-01-01'), partition L_SHIPDATE_1 VALUES LESS THEN('1999-01-01'), partition L_SHIPDATE_1 VALUES LESS THEN('1920-01-01')
);
(2) 查询表的 SCHEMA 名称，展示表名，schema 名称
 select tablename,schemaname from pg_tables where tablename='lineitem';
（3）查看表分布节点的 oid，展示表名，nodeoids
 select relname,nodeoids from pg_class t1,pgxc_class t2,pg_namespace t3
where t1.oid=t2.pcrelid and t1.relnamespace=t3.oid and t1.relname='lineitem' and t3.nspname='public';
（2）查看表所在实例的信息
 select t4.* from pg_class t1,pg_namespace t2,pgxc_class t3,pgxc_node t4
where t1.oid=t3.pcrelid and t1.relnamespace=t2.oid and
t3.nodeoids::varchar=t4.oid::varchar
and t1.relname='lineitem' and t2.nspname='public';
3、数据库连接
（1）创建 user2 用户，user2 用户需要具备创建数据的权限
 create user user2 createdb password "test@123";
（2）查询用户的连接数上限
 select rolname,rolconnlimit from pg_roles where rolname='user2';
（3）设置 user2 用户的连接数为 100；
 alter user user2 connection limit 100;
（4）查询 postgres 数据库连接上限，显示库、上限数量
 select datname,datconnlimit from pg_database where datname='postgres' 
 （5）查询 postgres 数据库中用户已使用的会话数量
 select count(datname) from pg_stat_activity where datname='postgres';
（6）查看所有用户已使用的会话连接数
 select count(*) from dv_sessions;
（7）查询库最大连接数
 show max_connections;
（8）查询会话状态，显示 datid、pid、state
 select datid,pid ,state from pg_stat_activity;
4、行级访问控制
（1）创建 user3 用户，密码"test@123"
 create user user3 password "test@123";
（2）当前有一张表 t_test(id ,name) ,有 2 万以上的数据，请授权只允许 user3
能看 id=1 的数据

select * from student;

--将表 select 权限赋予 user3
grant select on student to user3; --打开表行级访问控制开关
alter table student enable row level security; --创建行级访问控制策略
create row level security policy rls01 on student to user3 using(student_id=1);
（3）修改赋权能看 id=1 或 2 的数据
 alter row level security policy rls01 on student to user3 using(student_id=1 or student_id=2);
（4）当前有一张用户表 t_user(id ,age) ,请创建两名用户 u1 和 u2，密码均为
"test@123"
 Create user u1 password “test@123” Create user u2 password “test@123”
（5）设置行级访问控制，u，u2 设置只能看自己的用户信息

--授予 select 的权限
grant select on student to u1,u2; --打开表行级访问控制
alter table student enable row level security; --创建行级访问控制策略
create row level security policy rls02 on student to u1,u2
using(id=current_user);
（6）加一个行级访问控制让 u1 只能看自己且年龄 30 以上

--需要先将原行级访问控制影响用户 u1 取消，
alter row level security policy rls02 on t_user to u2
using(id=current_user); --重新创建行级访问控制
create row level security policy rls03 on t_user to u1
using(id=current_user and age>30);
（7）删除上述配置的所有行级访问控制策略
 drop row level security policy rls01 on t_test;
drop row level security policy rls02 on t_test;
drop row level security policy rls03 on t_test;
（8）关闭表的行级访问控制开关，并且级联删除用户
 Alter table t_test disable row level security;
Alter table t_user disable row level security; --级联删除用户
Drop user user3,u1,u2 cascade;
5、存储过程
--创建表&导入数据
create table score(id varchar(20),score int,cid varchar(20));
insert into score values('''001''',86,'''c1'''),('''002''',95,'''c2''')
（1）要求输入 c1 返回该课程的成绩信息（课程和 id 本身带有单引号）
 create or replace procedure getscore(cid_score inout varchar(20))
as
begin
cid_score=concat('''',cid_score,'''');
select score into cid_score from score where cid=cid_score;
end;
/
6、触发器
本体根据教授详情表和部门表完成相应触发器创建使用
--创建表
create table teacher(id int primary key, name varchar(20) not null, deptno int not null, tetle varchar(20) not null);
create table department(
id int primary key, name varchar(20) not null, number_of_senior int default 0); --导入数据
insert into department values(1,'physics',0), (2,'mathmetics',0),(3,'chemistry',0);
insert into teacher values(1,'tom',1,'associate professor'), (2,'bill',1,'professor'),(11,'eiston',3,'associate primary');
（1）创建 Tri_update_D 触发器，如果修改 Number_of_senior 字段时提示"不能
随便修改部门教授职称人数",如果已经有 Tri_update_D 触发器，则删除后重新
创建

--创建触发器函数
create or replace function print_info() returns trigger as
$$
begin
DBE_OUTPUT.PRINT_LINE('能随便修改部门教授职称人数');
return null;
end;
$$language plpgsql;
--当触发器存在时删除触发器
drop trigger if exists Tri_update_D on department; --创建触发器
create trigger Tri_update_D before update of number_of_senior
on department for each row execute procedure print_info();
（2）禁止触发器，修改 department 表中 ID=1 的 number_of_senior=10
并查出表中的数据

--禁用触发器
alter table department disable trigger Tri_update_D; --执行更新操作
update department set number_of_senior=10 where id=1; --查询表数据
select * from department;
（3）启动触发器，修改 department 表中 ID=1 的 number_of_senior=20;

--启动触发器
alter table department enable trigger Tri_update_D; --执行更新操作
update department set number_of_senior=20 where id=1; --查询部门表，查看更新结果
select * from department;
7、性能调优
当前有三个表，分别是学生信息表 student(id,name,sex,class)和 202201 班级
成绩表 score1(id,course,score),202202 班级成绩表 score2 结果与 score1 相
同
（1）用 union 查询输出 student 所有列，score1 和 2 的 course,grade 列，按
照 id 升序，成绩降序

(select t1.*,t2.course,t2.score from student t1 join score1 t2 on
t1.id=t2.id)
union
(select t3.*,t4.course,t4.score from student t3 join score2 t4 on
t3.id=t4.id) order by id,score desc;
（2）对以上 SQL 进行优化

--将 union 改成 union all
(select t1.*,t2.course,t2.score from student t1 join score1 t2 on
t1.id=t2.id)
Union all
(select t3.*,t4.course,t4.score from student t3 join score2 t4 on
t3.id=t4.id) order by id,score desc;
（3）查看两个班级相同的科目，202201 班在 score2 中不存在的成绩，要求使
用 not in
 select course,score from score1 where score not in(
select score from score2 where score2.course=score1.course);
（4）对以上 SQL 进行优化

--将 not in 更改为 not exists
select course,score from score1 where not exists(
select score from score2 where score2.course=score1.course
and score1.score=score2.score);

1、数据库对象管理及 SQL 应用 1
基于以下代码创建表和插入数据，并完成以下实验
drop table if exists student;
drop table if exists class;
create table student(sno int,sname varchar(20),score int , month int,cno int); --score 表示当月月考总分，月考总分为 Null，说明当月缺考
create table class(cno int ,cname varchar(20)); --导入数据
insert into class values (1,'class1'),(2,'class2');
insert into student values (1,'Lee',610,1,1),(2,'Jerry',510,1,1),(5,'Lee',410,1,1), (3,'Tom',400,1,2),(4,'Jack',300,1,2),(6,'Jacy',NULL,1,2),(1,'Lee',410,2,1),(2,'Jenry',510,2,1)
,(5,'Lee',210,2,1),(3,'Tom',600,2,2),(4,'Jack',300,2,2),(6,'Jack',510,2,2),(1,'Lee',410,3,1), (2,'Jerry',510,3,1),(5,'Lee',NULL,3,1),(3,'Tom',NULL,3,2),(4,'Jack',300,3,2),(6,'Jack',410,3,2);
（1）输出每月月考总分都比学号为 5 的同学分数高的所有学生信息

--解题思路：统计每个学生成绩大学学号为 5 的学生次数，计算月考次数，判断学生
出现次数是否等于月考次数
select * from student where sno in(select t1.sno from student t1
join (select month, nvl(score,0) as score from student where sno=5) t2
on t1.month=t2.month and t1.score>t2.score group by t1.sno
having count(t1.sno)=(select count(distinct month) from student));
（2）输出每次月考缺考的学生信息，要求打印姓名、班级编号和缺考次数
 select sname,cno,count(1) from student where score is null group by sname,cno;
（3）输出每次月考都和 tom 同时缺考的所有学生信息，要求打印学号、姓名和
月考总分

--解题思路：分别统计 tom 那几个月缺考以及总缺考几次，计算其他学生缺考月份和
缺考次数是否与 tom 相同
select student.sno,sname,score from student join
(select sno,month,count(1) over(partition by sno) from student where score is null and
sname<>'Tom')t1 on student.sno=t1.sno join
(select month,count(1) over(partition by sno) from student where sname='Tom' and
score is null) t2 on t1.month=t2.month and t1.count=t2.count;
（4）输出全校月考中位数分数。

--解题思路：使用 row_number 根据月粉分组，成绩排序，给所有成绩生产一个从 1 开始的序
号，根据序号选取中间值，
如果序号最大值为偶数，选取序号=最大学号/2 和下位数取平均值，如果需要为奇数，选取序
号为 ceil（最大序号/2)
select month,round(avg(score),2) from
(select student.*,count,row_number() over(partition by student.month order by
nvl(score,0)) as rowno from student, (select month,count(1) as count from student group by month ) tcount where
student.month=tcount.month)
where case when count%2=0 then rowno=(count/2) or rowno=(count/2)+1 else
rowno=ceil(count/2) end group by month;
（5）统计每个班每次月考的最高分数，要求打印班级名称，考试时间和月考分
数
 select cname,month,max(score) from student,class where student.cno=class.cno
group by cname,month order by cname,month;
2、数据库对象管理及 SQL 应用 2
表同上题
（1）输出 class1 班级中比 class2 班级每月月考最低分还低的学生信息，要求
打印学号、姓名和月考总分
 select t1.sno,t1.sname,t1.score from
(select * from student where cno=(select cno from class where cname='class1')) t1
join
(select month,min(nvl(score,0)) score from student where cno=(select cno from class
where cname='class2') group by month )t2
on t1.month=t2.month and nvl(t1.score,0) <t2.score;
（2）打印月考总分平均分最高的学生信息、输出学号，姓名和月考总分平均分
 select sno,sname ,round(avg(nvl(score,0)),2) avgscore from student group by
sno ,sname having avgscore=(select max(score) from (select round(avg(nvl(score,0)),2)
from student group by sno,sname));
（3）输出每个学生月考平均分和最高月考平均分学生之间的分数差距，打印学
号、姓名、月考平均分和差距分数
 select sno,sname,round(avg(nvl(score,0)),2) avgscore ,(select max(avgscore) from
(select round(avg(nvl(score,0)),2) avgscore from student group by sno))-avgscore
from student group by sno ,sname;
3、数据库对象管理及 SQL 应用 3
基于以下学生成绩表，完成以下实验要求

--创建表
create table stu(id int,math int,art int,phy int ,music int); --导入数据
insert into stu values(1,60,33,66,86);
insert into stu values(2,61,53,86,75);
insert into stu values(3,70,63,66,53);
insert into stu values(4,90,63,76,65);
insert into stu values(5,59,69,79,95);
insert into stu values(6,63,73,66,36);
insert into stu values(7,61,53,88,75);
insert into stu values(8,74,63,64,53);
insert into stu values(9,40,83,78,35);
insert into stu values(10,59,49,89,65);
（1）求 math、phy 总成绩以及 art、music 的总成绩
 select sum(math+phy),sum(art+music) from stu;
（2）计算学生总成绩，并基于总成绩排序
 select id,math+phy+art+music all_score from stu order by all_score desc;
（3）art 和 music 总分排名前 5 的总成绩加 5 分，查询最终的所有学生总成绩

(select stu.id,score+math+phy allscore from stu,(select id,art+music+5 score,rank()
over(order by score desc) as ranknum from stu) stu2 where stu.id=stu2.id and
ranknum<=5)
union all
(select stu.id ,score+math+phy allscore ,rank() over(order by score desc ) as ranknum
from stu,(select id,art+music score from stu) stu2 where stu.id=stu2.id and
ranknum>5);
4、数据库对象管理及 SQL 应用 4
基于以下学生成绩表，完成以下实验
--创建表
create table scopes(student_id int,chinese int ,math int,english int ,music int); --导入数据
insert into scopes values(1,90,88,100,88);
insert into scopes values(2,88,88,100,99);
insert into scopes values(3,87,89,98,89);
insert into scopes values(4,91,88,76,99);
insert into scopes values(5,92,88,78,98);
insert into scopes values(6,93,88,76,87);
（1）计算每个学生的 chinese 和 math 总分，以及 english 和 music 总分。要求
一条 SQL 语句实现，不能使用临时表
 select student_id ,chinese+math,english+music from scopes;
（2）目前有一张权重表(各科有不同的权重，目前权重策略有 2 各），请算出每
个学生结合权重计算后的成绩总和。要求一条 SQL 语句实现，不能使用临时表。
每个学生都对应两个权重成绩
--权重表结构
create table weight(weight_id int ,chinese decimal(10,2),math decimal(10,2), english decimal(10,2),music decimal(10,2));
insert into weight values(1,0.3,0.2,0.2,0.3);
insert into weight values(2,0.2,0.1,0.3,0.4);
要求最终效果如下
Student_id Weight_id Weight_sum
1 1 1 87.7
2 1 2 67.7
3 2 1 78.8
4 2 2 66.7
 select student_id,weight_id ,(t1.chinese*t2.chinese +t1.math*t2.math+
t1.english*t2.english +t1.music*t2.music) as weight_sum from scopes t1, weight t2 order by 1,2;
（3）结合上面的结果，将一个学生对应的两个权重成绩，合到一行。要求一条
SQL 语句实现，不能使用临时表。
Student_id Weight_sum1 Weight_sum2
1 1 87.7 67.7
2 2 78.8 66.7
 select tb1.student_id,weight_sum1,weight_sum2 from
(select student_id,(t1.chinese*t2.chinese+t1.math*t2.math+t1.english*t2.english+
t1.music*t2.music) weight_sum1 from scopes t1,weight t2 where weight_id=1)tb1
join
(select student_id,(t1.chinese*t2.chinese+t1.math*t2.math+t1.english*t2.english+
t1.music*t2.music) weight_sum2 from scopes t1,weight t2 where weight_id=2)tb2
on tb1.student_id=tb2.student_id order by 1;
（4）按照两个权重成绩之和的大小，进行从大到小排序，且生成排序序号，要
求生成连续排序序号，相同的值具有相同的序号。一条 SQL 语句实现，不能使用
临时表。
效果如下：
Student_id Weight_sum1 Weight_rank Weight_sum2 Weight_rank
1 87.8 1 67.7 1
2 78.7 2 66.7 2
 select tb1.student_id ,weight_sum1,dense_rank() over(order by weight_sum1 desc)
weight_rank1 ,weight_sum2,dense_rank() over(order by weight_sum2 desc)
weight_rank2 from
(select student_id,(t1.chinese*t2.chinese+t1.math*t2.math+t1.english*t2.english+
t1.music*t2.music) weight_sum1 from scopes t1,weight t2 where weight_id=1)tb1
join
(select student_id,(t1.chinese*t2.chinese+t1.math*t2.math+t1.english*t2.english+
t1.music*t2.music) weight_sum2 from scopes t1,weight t2 where weight_id=2)tb2
on tb1.student_id=tb2.student_id
5、性能优化 1
当 前 有 一 张 表 create table test(student_id int ,class_id int ,kemu
varchar(20) ,score int)
里面有 8 万条数据

insert into test values(101,202201,'语文','80');
insert into test values(102,202201,'语文','85');
insert into test values(103,202201,'语文','66');
insert into test values(101,202201,'数学','65');
insert into test values(102,202201,'数学','62');
insert into test values(103,202201,'数学','73');
insert into test values(101,202201,'英语','90');
insert into test values(102,202201,'英语','88');
insert into test values(103,202201,'英语','78');
insert into test values(101,202201,'英语','90');
insert into test values(102,202201,'英语','88');
insert into test values(103,202201,'英语','78');
insert into test values(201,202202,'语文','90');
insert into test values(202,202202,'语文','95');
insert into test values(203,202202,'语文','86');
insert into test values(201,202202,'数学','85');
insert into test values(202,202202,'数学','72');
insert into test values(203,202202,'数学','83');
insert into test values(201,202202,'英语','95');
insert into test values(202,202202,'英语','88');
insert into test values(203,202202,'英语','88');
（1）查 202202 班级里面语文最低分是多少，要保障走索引

--收集统计信息
analyze test; --获取推荐索引
select * from gs_index_advise('select min(score) from test where kemu=''语文'' and
classid=''202202'''); --创建索引
create index idx01 on test(classid,kemu ) --查询 202202 班级里面语文最低分
select /*+indexscan(test,idx01)*/ min(score) from test where kemu='语文' and class_id='202202';
（2）查 202202 班级同一科目成绩比 202201 班级最高分高的同学，根据以下 SQL
优化重写
原 SQL:select * from test where score>(select max(score) from test wehre
class_id='202201') and class_id='202202'
 select * from test t1 join (select max(score) as max_score from test
where class_id='202201' )t2 on t1.score>t2.max_score where t1.class_id='202202';
6、性能优化 2
当前有三张表，分别是学生信息表 student 和 202201 班级成绩表 score1，2022
班级成绩表 score2。考试时详细查看题目并确定各表字段
（1）查 202201 班级和 202202 班级所有人语文成绩前 10 的记录，第一个查询使
用 union
 select * from score1 order by chinese limit 10
union
select * from score2 order by chinese limit 10;
（2）对以上 SQL 语句进行优化
 --将 union 更改为 union all
select * from score1 order by chinese limit 10
union all
select * from score2 order by chinese limit 10;
(3）查看两个班级相同的科目，202201 班在 score2 中不存在的成绩，要求使用
not in（考试时详细确认题目要求，查看是具体哪些科目成绩）
 select chinese from score where chinese not in (select chinese from score2);
（4）对以上 SQL 语句进行优化
 --将 not in 更改为 not exists
select chinese from score where not exists (select chinese from score2);
（5）查询班级 202201 语文成绩最高的学生，要求先创建索引，并且能保证一定
会使用索引
 --查询 SQL 语句
select max(chinese) from score1; --使用索引推荐
Select gs_index_advise(‘select max(chinese) from score1); --创建索引
Create index idx01 on score1(chinese);
7、性能优化 3
基于学生表(sno,sname,cno),班级表(cno,cname),课程表(courid,courname)，
成绩表(sno,courid,score)完成关联查询
--创建表
create table student(sno varchar(20),sname varchar(50),cno int);
create table class(cno int,cname varchar(50));
create table course(courid int ,courname varchar(50));
create table score(sno varchar(20),courid int ,score int); --数据插入
insert into student values('1001','张三',1),('1002','李四',1),('1003','王五',2),('1004','赵六',2);
insert into class values(1,'1 班'),(2,'2 班');
insert into course values(1,'语文'),(2,'数学'),(3,'英语'),(4,'物理');
insert into score values('1001',1,84),('1001',1,64),('1001',2,86),('1001',2,94);
insert into score values('1001',3,76),('1001',3,56),('1001',4,48),('1001',4,84);
insert into score values('1002',1,83),('1002',1,85),('1002',2,46),('1002',2,74);
insert into score values('1002',3,65),('1002',3,76),('1002',4,56),('1002',4,98);
insert into score values('1003',1,86),('1003',1,74),('1003',2,88),('1003',2,54);
insert into score values('1003',3,86),('1003',3,76),('1003',4,67),('1003',4,76);
insert into score values('1004',1,100),('1004',1,100),('1004',2,87),('1004',2,86);
insert into score values('1004',3,69),('1004',3,67),('1004',4,84),('1004',4,92);
（1）语文平均成绩大于 80 的所有成绩，输出班级名，学号（或班级号），平均
成绩，要求 where 推荐里有两个非相关子查询

--in where 均属于子查询
select t1.sno,t3.cname,avg(score) from score t1 ,student t2,class t3
where t1.sno=t2.sno and t2.cno=t3.cno and
t1.sno in(select sno from score where courid in (select courid from course
where courname='语文') group by sno having avg(score)>80)
and courid in (select courid from course where courname='语文')
group by t1.sno,t3.cname;
（2）在上一题基础上，将 where 条件中的非相关子查询改为 from 后边的范围表
 select t1.sno,cname,avgscore from (select t3.sno,t3.cno,avg(score) avgscore from
score t1,course t2,student t3 where t1.sno=t3.sno and t1.courid=t2.courid and
courname='语文' group by t3.sno,t3.cno having avgscore>80) t1,class t2 where
t1.cno=t2.cno
（3）在上一题的基础上，将 from 后面的子查询优化为附表的关联查询
 select t3.cno,t3.cname,avg(score) avgscore from score t1,student t2,class t3, course t4 where t1.sno=t2.sno and t2.cno=t3.cno and t1.courid=t4.courid and
courname='语文' group by t1.sno ,t3.cno ,t3.cname having avgscore>80;
8、存储过程
当前有一张表 stu(sno,math,art,physics,cno);
create table stu(sno varchar(30),math float ,art float ,physics float,cno int);
insert into stu values('1001',56,85,72,1),('1002',66,75,82,1);
（1）查看学生每门成绩与每门平均成绩的差值
 select sno,math,math-avgmath,art,art-avgart,physics,physics-avgphysic
from stu,(select
round(avg(nvl(math,0)),2) avgmath, round(avg(nvl(art,0)),2) avgart, round(avg(nvl(physics,0)),2) avgphysic
from stu);
（2）编写存储过程，输入学生 id 和对应科目名称输出对应的绩点值，对于学生
每科成绩，0-59,绩点 0，60-69 绩点 0.1，70-79，绩点 0.2，80-89，绩点 0.3，
90-100 绩点 0.4
 create or replace procedure pro_cal_point(id1 int,coursename varchar(30),point out
float) as
begin
case when coursename='math' then
select (case when math<=59 then 0
when math<=69 then 0.1
when math<=79 then 0.2
when math<=89 then 0.3
when math<=100 then 0.4
else 0 end) into point from student where student_id=id1 limit 1;
when coursename='physical' then
select (case when physical<=59 then 0
when physical<=69 then 0.1
when physical<=79 then 0.2
when physical<=89 then 0.3
when physical<=100 then 0.4
else 0 end) into point from student where student_id=id1 limit 1;
when coursename='art' then
select (case when art<=59 then 0
when art<=69 then 0.1
when art<=79 then 0.2
when art<=89 then 0.3
when art<=100 then 0.4
else 0 end) into point from student where student_id=id1 limit 1;
when coursename='music' then
select (case when music<=59 then 0
when music<=69 then 0.1
when music<=79 then 0.2
when music<=89 then 0.3
when music<=100 then 0.4
else 0 end) into point from student where student_id=id1 limit 1;
end case;
end;
/
（3）编写存储过程，根据学号，班级，获取学生的总分
 create or replace procedure get_score(stuid varchar(30),cid int ,all_score out float)
as
begin
select (math+art+physcis) into all_score from stu where sno=stuid and cno=cid;
end;
/
9、触发器 1
本体根据以下表完成相应触发器创建使用
create table tab1(sname text,deptno int ,salary float,title text);
create table dept(id int,dept_name text);
create table logger(sname text, dept_name text ,log_date date);
创建触发器，要求在 tab1 表插入一行数据库时，自动往 logger 表中插入一条记
录，记录 sname 和部门名称，并用当天的日期来标记该行数据的生成时间
（1）创建触发器函数 T_INS_F
 create or replace function T_INS_F() returns trigger as
$$
begin
insert into logger values(new.name,(select dept_name from dept
where id=new.deptno),sysdate);
return new;
end;
$$language plpgsql;
（2）创建触发器 T_INS_TR
 create trigger T_INS_TR after insert on tab1 for each row execute
procedure T_INS_TR();
(3) 禁用表 tab1 上的所有触发器
 Alter table tab1 disable trigger all;
（4）删除 T_INS_TR 触发器
 Drop trigger T_INS_TR ON TAB1
10、触发器 2
根据以下表完成相应的触发器创建使用
--创建表
create table stu(
sid int, sname varchar(20))with (orientation=row,compression=no)
distribute by hash(sid) to group group_version1;
create table selecttive(
sid int, course_name varchar(20))with (orientation=row,compression=no)
distribute by hash(sid) to group group_version1; --导入数据
insert into stu values(1,'tom'),(2,'marry'),(3,'lzy');
insert into selecttive values(1,'数学'),(2,'语文'),(3,'英语');
（1）创建触发器函数 delete_stive
 create or replace FUNCTION delete_stive() returns trigger as
$$
begin
delete from selecttive where sid=old.sid;
return old;
end;
$$language plpgsql;
（2）创建触发器 delete_selecttive_trigger
 create trigger delete_selecttive_trigger before delete on stu
for each row execute procedure delete_stive();

1、数据库对象管理及 SQL 应用
当前有一张表 test，请基于该表完成以下操作
--创建表
drop table if exists test;
create table test(id int primary key ,name varchar(20),age int)
distribute by hash(name); --数据导入
insert into test values(1,'zhangsan',45),(2,'lisi',56), (3,'wangwu',22),(4,'zhaoliu',23),(5,'tom',45),(6,'bob',25), (7,'jack',26),(8,'mary',27),(9,'mali',30),(10,'zhazha',31), (11,'lulu',23),(12,'fengfeng',32),(13,'caicai',33),(14,'zhouzhou',35), (15,'yunyun',39);
（1）请为 name 字段创建索引
 Create index idx01 on test(name);
（2）请查看数据在各个节点的分布情况，显示表名、节点名、数据量
 select tablename,nodename,dnsize from table_distribution();
（3）删除主键索引

--查看约束名称
select * from pg_constraint c1,pg_class c2 where c1.conrelid=c2.oid
and c2.relname='test' --删除主键索引，即删除主键约束
alter table test drop constraint test_pkey;
（4）重建主键索引

--如果主键已经被删除不存在了，重新创建
alter table test add constraint test_pkey primary key(id);
（5）对 age 列添加检查约束，要求只能写入大于 18 的值
 alter table test add constraint age_check check(age>18);
（6）对 name 列添加非空约束
 alter table test alter name set not null;
2、安全审计
（1）请创建一个审计管理员用户 hcie_audit
 create user hcie_audit password "test@123" auditadmin poladmin;
（2）切换用户查看 guc 参数审计总开关是否开启

--切换用户，在 DAS 右上角进行切换
--查看审计开关是否打开
Show audit_enabled;
（3）查看用户 hcie_audit 成功登录 postgres 的记录
 select * from pg_query_audit(now()-interval '1 hour',now()) where
username='hcie_audit' and database='postgres' and type='login_succes' and result='ok';
（4）统计一天内的审计数据量要求使用 now()
 select count(*) from pg_query_audit(now()-1,now());
（5）删除指定时间的审计记录（如删除过去 10 分钟内的
 select pg_delete_audit(now()-interval '10 min',now());
3、用户权限管理
当前有一张表 sjh_test(a int,b int) 和角色 sjh112，请根据当前环境完成以下用户及权限管理
操作
--创建表
create table sjh_test(a int,b int); --创建角色
create role sjh112 password “test@123”;
(1)创建用户 sjh111
 create user sjh111 password “test@123”;
(2)将表 sjh_test 表的读取、删除权限授权给 sjh111 用户
 ----普通用户只有 public 模式的权限，需要将当前 schema 使用权限赋予给用户
grant usage on schema root to sjh111; --赋权
grant select ,delete on sjh_test to sjh111;
(3)为用户 sjh111 授权在 sjh_test 表的 a,b 列上的查询、添加和更新权限
 grant select(a,b),insert(a,b),update(a,b) on sjh_test to sjh111;
(4)将用户 sjh111 授权在 sjh_test 表的 a 列上的查询、添加和更新权限回收
 revoke select(a),insert(a),update(a) on sjh_test from sjh111;
（5）查看用户 sjh111 和数据库的相关权限，要求显示数据库名、用户名、数据
库的权限
 select t1.*,rolname from (select datname,(aclexplode(datacl)).grantee, (aclexplode(datacl)).privilege_type from pg_database) t1,pg_roles where
grantee=pg_roles.oid and rolname='sjh111' and datname not like '%template%' （6）查询 sjh_test 的 owner，要求显示表名和 owner
 select tablename ,tableowner from pg_tables where tablename='sjh_test';
（7）查询 sjh111 的表权限，要求显示表名、schema 名、用户名、相关表权限
 select grantee,table_name,table_schema,privilege_type from
information_schema.table_privileges where grantee='sjh111';
（8）查询对表 sjh_test 有操作权限的用户，要求显示 2 列：用户名、操作权限
 select grantee as user,privilege_type from information_schema.table_privileges
where table_name='sjh_test';
（9）创建用户 user3，密码为 test@123
 create user user3 password "test@123";
（10）当前有一张表 t_test(id ,name) ,有 2 万条数据，请授权只允许 user3 能看 id=1
的数据
 grant select on t_test to user3;
alter table t_test enable row level security;
create row level security policy rls_1 on t_test to user3 using(id=1);
（12）修改赋权能看 id=1 或 2 的数据
 alter row level security policy rls_1 on t_test to user3 using(id=1 or id=2);
4、存储过程
根据如下表，完成存储过程实验
--创建表&导入数据
create table course(cid varchar(20),cname varchar(50));
create table score(id varchar(20),score int,cid varchar(20));
insert into course values('c1','chinese'),('c2','math');
insert into score values('001',86,'c1'),('002',95,'c2')
（1）编写存储过程输入课程 c1 获取平均成绩、数据编号和课程名称，根据平均成绩获取
绩点数，0~59 为 0，60~69 为 0.1，70~79 为 0.2，80~89 为 0.3，90~100 为 0.4
 create or replace porcedure getscore(ccid varchar(20),avgscore out float,
iid out varchar(20),ccname out varchar(20),gpa out float) as
begin
--数据编号
select id into iid from score where cid=ccid; --平均成绩
select round(avg(score),2) into avgscore from score where cid=ccid; --课程名称
select cname into ccname from course where cid=ccid; --根据平均成绩判断绩点数
case when avgscore <=59 then gpa=0;
when avgscore<70 then gpa=0.1;
when avgscore<80 then gpa=0.2;
when avgscore<90 then gpa=0.3;
else gpa=0.4
end case;
end ;
/