jiang DEMO9
【摘要】 jiang DEMO9
## 1. 数据库对象管理及SQL应用
### 1.1 数据处理与查询
基于以下学生成绩表，完成以下实验要求
```sql
-- 创建表
create table su(
id int primary key not null, -- 编号
firstname varchar(50) not null, -- 姓名
familyname varchar(50) not null, -- 姓氏
shorterform varchar(50) not null, -- 简称
mark char(1) not null,
score int not null -- 成绩
) distribute by replication;
-- 导入数据
insert into su values(1,'secebbie','peter','peter','S',86),
(2,'tom','jerry','tom','H',63),
(3,'amanda','lee','lee','H',67),
(4,'homas','brooke','homas','H',67),
(5,'elizabeth','katharine','elizabeth','H',67);
```
#### 1.1.1 请查询姓名和姓氏，以姓名-姓氏的格式输出，要求首字母大写，姓名和姓氏之间使用"."拼接
```sql
--1.
select initcap(firstname||'.'||familyname) from su;
--2.
select initcap(concat(firstname,'.',familyname)) from su;
```
#### 1.1.2 插入一条新数据(2,'tom','jerry','tom','H',63),当出现主键冲突时，将mark修改为'F'
```sql
insert into su values(2,'tom','jerry','tom','H',63) on duplicate key update mark='F'
```
#### 1.1.3 查询表，检查姓名是否是sec开头，展示姓名，判断结果result
```sql
select firstname,(case when firstname like 'sec%' then 't' else 'f' end) as result from su;
```
#### 1.1.4 查询表中所有列的数据，按照成绩进行排序，并显示名次(position)，名次为连续的。要求展示所有字段，名次字段position
```sql
select * ,dense_rank() over(order by score desc) as position from su;
```
### 1.2. 分区表和系统表查询
当前有一张订单表lineitem，具体字段如下
```sql
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 the furiously ...'),(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','unusual packa ...'),(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.2.1 创建分区表，根据上述字段信息创建分区表，按L_SHIPDATE分区，按年分1993,1994,1995,1996,1997,1998,1999。分区名称分别为L_SHIPDATE_1,第二个分区名称为L_SHIPDATE_2,以此类推,使用L_ORDERKEY进行哈希分布，建表完成执行上方数据导入代码，进行数据导入
```sql
--老师给的答案
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(date_part('year',L_SHIPDATE))
(
PARTITION L_SHIPDATE_1 values less than ('1993'),
PARTITION L_SHIPDATE_2 values less than ('1994'),
PARTITION L_SHIPDATE_3 values less than ('1995'),
PARTITION L_SHIPDATE_4 values less than ('1996'),
PARTITION L_SHIPDATE_5 values less than ('1997'),
PARTITION L_SHIPDATE_6 values less than ('1998'),
PARTITION L_SHIPDATE_7 values less than ('1999'),
PARTITION L_SHIPDATE_8 values less than ('2000')
partition L_SHIPDATE_9 values less than(maxvalue)
);
--我的答案
create table 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 --备注
)
DISTRIBUTE BY HASH (L_ORDERKEY)
PARTITION BY RANGE (L_SHIPDATE) (
PARTITION L_SHIPDATE_1 VALUES LESS THAN ('1994-01-01'),
PARTITION L_SHIPDATE_2 VALUES LESS THAN ('1995-01-01'),
PARTITION L_SHIPDATE_3 VALUES LESS THAN ('1996-01-01'),
PARTITION L_SHIPDATE_4 VALUES LESS THAN ('1997-01-01'),
PARTITION L_SHIPDATE_5 VALUES LESS THAN ('1998-01-01'),
PARTITION L_SHIPDATE_6 VALUES LESS THAN ('1999-01-01'),
PARTITION L_SHIPDATE_7 VALUES LESS THAN ('2000-01-01'),
PARTITION L_SHIPDATE_8 VALUES LESS THAN (MAXVALUE)
);
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 the furiously ...'),(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','unusual packa ...'),(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.2.2 查询表的schema名称，展示表名、schema名称
```sql
select schemaname,tablename from pg_tables where tablename='lineitem';
```
#### 1.2.3 查看表分布节点的oid,展示表名、nodeoids
```sql
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';
```
#### 1.2.4 查看表所在实例的信息
```sql
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 t4.oid in (select unnest(t3.nodeoids))
and t1.relname='lineitem' and t2.nspname='public';
```
### 1.3. SQL基础
由于疫情管控，某小区进行封锁，收集了residents居民表数据，包含姓名、年龄、性别、楼栋信息。
```sql
-- 创建表
CREATE TABLE RESIDENTS(
name varchar(200), --姓名
age INT, --年龄
sex char(1), --性别
building INT --楼号
);
-- 导入数据
INSERT INTO RESIDENTS VALUES('a',0,'m',02);
INSERT INTO RESIDENTS VALUES('b',24,'m',03);
INSERT INTO RESIDENTS VALUES('c',25,'f',05);
INSERT INTO RESIDENTS VALUES('d',26,'f',09);
INSERT INTO RESIDENTS VALUES('e',27,'f',10);
INSERT INTO RESIDENTS VALUES('f',28,'m',07);
INSERT INTO RESIDENTS VALUES('g',0,'f',06);
INSERT INTO RESIDENTS VALUES('h',30,'m',12);
INSERT INTO RESIDENTS VALUES('I',31,'f',13);
INSERT INTO RESIDENTS VALUES('k',52,'m',13);
INSERT INTO RESIDENTS VALUES('L',53,'m',12);
INSERT INTO RESIDENTS VALUES('m',34,'f',12);
INSERT INTO RESIDENTS VALUES('n',15,'f',13);
INSERT INTO RESIDENTS VALUES('o',17,'f',03);
INSERT INTO RESIDENTS VALUES('p',0,'f',04);
INSERT INTO RESIDENTS VALUES('q',66,'f',02);
INSERT INTO RESIDENTS VALUES('r',39,'m',01);
INSERT INTO RESIDENTS VALUES('s',40,'m',13);
```
#### 1.3.1 为了方便按楼栋给婴儿送纸尿裤，查出每栋楼age<1的数量，最后显示楼栋信息和对应数量
```sql
select building,count(*) from residents where age < 1 group by building;
```
#### 1.3.2 由于每栋楼各个年龄段的人都有，故按age年龄段分组(0-18)(18-35)(35-55)(55-100)形成age_group字段，每个组命名group1,group2,group3,group4。
```sql
--没有声明给表添加字段
select *,
(case when age < 18 then 'group1'
when age < 35 then 'group2'
when age < 55 then 'group3'
else 'group4' end) as age_group
from residents;
--声明给表添加字段
alter table residents add age_group varchar(20)
update residents set agent_group=(case when age < 18 then 'group1'
when age < 35 then 'group2'
when age < 55 then 'group3'
else 'group4' end);
```
#### 1.3.3 age_group按每组人数多少排序，查询出age_group、人数、最大年龄、最小年龄、平均年龄(平均年龄向下取整)
```sql
select age_group,count(*) as "人数",max(age) as "最大年龄",min(age) as "最小年龄",floor(avg(age)) as "平均年龄" from
(select *,
(case when age <= 18 then 'group1'
when age <= 35 then 'group2'
when age <= 55 then 'group3'
else 'group4' end) as age_group
from residents)
group by age_group
;
```
#### 1.3.4 由于需要每天送食物，增加如下每个年龄段所需食物营养价值表，需要统计出该小区每天总营养值
```sql
--创建表
create table nutrition(age_group varchar(20),nutrition_value int);
--插入数据
insert into nutrition values('group1',5), ('group2',7), ('group3',6), ('group4',5);
```
```sql
select sum(n.nutrition_value) as "每天总营养值" from
(select (case when age <= 18 then 'group1'
when age <= 35 then 'group2'
when age <= 55 then 'group3'
else 'group4' end) as age_group
from residents) r , nutrition n
where r.age_group = n.age_group
;
```
#### 1.3.5 按楼栋求出每栋楼所需营养值
```sql
select r.building as "楼号",sum(n.nutrition_value) as "每栋楼所需营养值" from
(select *,
(case when age <= 18 then 'group1'
when age <= 35 then 'group2'
when age <= 55 then 'group3'
else 'group4' end) as age_group
from residents) r , nutrition n
where r.age_group = n.age_group
group by r.building
;
```
### 1.4 成绩绩点计算
基于以下学生成绩表，完成以下实验要求。
```sql
-- 创建表
create table stu(id int ,math int ,art int ,phy int);
-- 导入数据
insert into stu values (1,60,33,66);
insert into stu values (2,61,53,86);
insert into stu values (3,70,63,66);
insert into stu values (4,90,63,76);
insert into stu values (5,59,69,79);
```
#### 1.4.1. 查看每门成绩是否大于每门平均成绩
```sql
--第一种解法
select *,
case when math > avg(math)over() then '大于' else '不大于' end as is_math_bigger,
case when art > avg(art)over() then '大于' else '不大于' end as is_art_bigger,
case when phy > avg(phy)over() then '大于' else '不大于' end as is_phy_bigger
from stu;
--第二种解法(这种是笛卡儿积，不过第二个表确定就一行，对性能影响有限)
select id,
math,case when math > a.avg_math then '大于' else '不大于' end,
art,case when art > a.avg_art then '大于'else '不大于' end,
phy,case when phy > a.avg_phy then '大于' else '不大于' end
from stu s,(select avg(math) as avg_math,avg(art) as avg_art,avg(phy) as avg_phy from stu) a;
```
#### 1.4.2. 编写函数，获取成绩绩点，输入学生id和科目名称，输出对应的绩点值，0-59给0，60-69给0.1，70-79给0.2，80-89给0.3，90-100给0.4。
```sql
create or replace function func_get_point(sid int,name varchar(10)) returns float as
$$
declare
point float;
begin
case
when name ='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.4 end) into point from stu where id=sid;
when name ='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.4 end) into point from stu where id=sid;
when name ='phy' then
select (case when phy <= 59 then 0
when phy <= 69 then 0.1
when phy <= 79 then 0.2
when phy <= 89 then 0.3
when phy <= 100 then 0.4
else 0.4 end) into point from stu where id=sid;
else
dbe_output.print_line('error.');
--raise notice 'error';
end case;
return point;
end;
$$ language plpgsql;
--怀疑老师给的答案有问题，返回类型numeric，应该是float
```
#### 1.4.3. id含'3'的同学，求总的绩点，返回绩点最大的id和总绩点
```sql
--第一种方法
select id,
((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) +
(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) +
(case when phy <= 59 then 0
when phy <= 69 then 0.1
when phy <= 79 then 0.2
when phy <= 89 then 0.3
when phy <= 100 then 0.4
else 0 end)) as point
from stu
where id like '%3%' order by 2 desc limit 1;
--第二种直接使用上一题建的函数
select id,
func_get_point(id,'math') +
func_get_point(id,'art') +
func_get_point(id,'phy') as point
from stu
where id like '%3%' order by 2 desc limit 1;
--第三种方法,考虑有并列第一的情况
select id,point from (
select *,rank() over(order by point desc) from (
select id,
func_get_point(id,'math') +
func_get_point(id,'art') +
func_get_point(id,'phy') as point
from stu
where id like '%3%'))
where rank=1;
```
#### 1.4.4. 求总绩点，返回绩点最大的id和总绩点
```sql
--第一种方法
select id,
((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) +
(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) +
(case when phy <= 59 then 0
when phy <= 69 then 0.1
when phy <= 79 then 0.2
when phy <= 89 then 0.3
when phy <= 100 then 0.4
else 0 end)) as point
from stu order by 2 desc limit 1;
--第二种方法
select id,
func_get_point(id,'math') +
func_get_point(id,'art') +
func_get_point(id,'phy') as point
from stu order by 2 desc limit 1;
--第三种方法,考虑有并列第一的情况
select id,point from (
select *,rank() over(order by point desc) from (
select id,
func_get_point(id,'math') +
func_get_point(id,'art') +
func_get_point(id,'phy') as point
from stu))
where rank=1;
```
#### 1.4.5. 按照总绩点排名输出
```sql
--第一种方法
select id,
((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 <= 99 then 0.4
else 0 end) +
(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 <= 99 then 0.4
else 0 end) +
(case when phy <= 59 then 0
when phy <= 69 then 0.1
when phy <= 79 then 0.2
when phy <= 89 then 0.3
when phy <= 99 then 0.4
else 0 end)) as point
from stu order by 2;
--第二种方法
select *,rank() over(order by point desc) from (
select id,
func_get_point(id,'math') +
func_get_point(id,'art') +
func_get_point(id,'phy') as point
from stu);
```
#### 1.4.6. 编写add_mask(id1,id2)函数，当id1是当前查询用户时，显示正常id，如果不是则显示为id2
```sql
create or replace function add_mask(id1 varchar(20), id2 varchar(20))returns varchar(20)as
$$
begin
if id1=current_user then
return id1;
else
return id2;
end if;
end;
$$ language plpgsql;
```
### 1.5 学生成绩统计(权重)
基于以下学生成绩表，完成以下实验要求
```sql
-- 创建表
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.5.1 求chinese和math总成绩以及english和music的总成绩。
```sql
select sum(chinese + math) as sum_c_m,sum(english + music) as sum_e_m from scopes;
```
#### 1.5.2 计算每个学生的chinese和math总分，以及english和music总分。要求一条sql语句实现，不能使用临时表
```sql
select student_id,chinese + math as sum_c_m,english + music as sum_e_m from scopes;
```
#### 1.5.3 目前有一张权重表(各科有不同的权重，目前权重策略有2个)，请算出每个学生结合权重计算后的成绩总和。要求一条sql语句实现，不能使用临时表。每个学生都对应两个权重成绩。
权重表结构如下
```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 |
```sql
select student_id,weight_id,round(t1.chinese*t2.chinese + t1.math*t2.math + t1.english*t2.english + t1.music*t2.music,1) as weight_sum
from scopes t1,weight t2
order by 1,2;
```
#### 1.5.4 结合上面的结果，将一个学生对应的两个权重成绩，合到一行。要求一条sql语句实现，不能使用临时表
最终效果如下：
| 序 号| student_id | weight_sum1 | weight_sum2 |
| --- | ------------ | ---------- | ----------- |
| 1 | 1 | 87.7 | 67.7 |
| 2 | 1 | 78.8 | 66.7 |
```sql
select tb1.student_id,weight_sum1,weight_sum2 from (
select student_id,weight_id,round(t1.chinese*t2.chinese + t1.math*t2.math + t1.english*t2.english + t1.music*t2.music,1) as weight_sum1
from scopes t1,weight t2 where t2.weight_id=1) tb1,
(select student_id,weight_id,round(t1.chinese*t2.chinese + t1.math*t2.math + t1.english*t2.english + t1.music*t2.music,1) as weight_sum2
from scopes t1,weight t2 where t2.weight_id=2) tb2
where tb1.student_id=tb2.student_id
order by 1;
```
#### 1.5.5 按照两个权重成绩之和的大小，进行从大到小排序，且生成排序序号，要求生成连续排序序号，相同的值具有相同序号。要求一条sql语句实现，不能使用临时表。
最终效果如下：
| student_id | weight_sum1 | weight_rank1 | weight_sum2 | weight_rank2 |
| ------------ | ---------- | ----------- | ---------- | ----------- |
| 1 | 87.7 | 1 | 67.7 | 1 |
| 2 | 78.8 | 2 | 66.7 | 2 |
```sql
select tb1.student_id,weight_sum1,dense_rank() over(order by weight_sum1 desc) as weight_rank1,
weight_sum2,dense_rank() over(order by weight_sum2 desc) as weight_rank2 from
(select student_id,weight_id,round(t1.chinese*t2.chinese + t1.math*t2.math + t1.english*t2.english + t1.music*t2.music,1) as weight_sum
from scopes t1,weight t2 where t2.weight_id=1) tb1,
(select student_id,weight_id,round(t1.chinese*t2.chinese + t1.math*t2.math + t1.english*t2.english + t1.music*t2.music,1) as weight_sum
from scopes t1,weight t2 where t2.weight_id=2) tb2
where tb1.student_id=tb2.student_id
```
### 1.6 学生成绩统计1
有一张记录学生成绩的表，记录了学号、各科成绩等信息
请执行以下sql并完成题目要求
```sql
-- 创建表
drop table if exists grades;
create table grades(
student_id char(4) not null,
math int,
physics int,
music int,
art int
);
-- 导入数据
insert into grades values
('0001',87,92,77,69),
('0003',67,72,56,88),
('0004',77,97,65,96),
('0008',98,82,89,68),
('0023',56,52,56,69),
('0011',78,81,87,64),
('0032',82,99, 4,74),
('0009',64,54,97,99),
('0056',79,79,78,78),
('0034',66,96,57,80);
```
#### 1.6.1 写一个查询，查询每位学生每科成绩是否高于该科所有学生成绩的平均值。(禁止使用临时表)如下所示：
| student_id | math | physics | music | art |
| ---- | ---- | ---- | ---- | ---- |
| 0001 | t | t | t | f |
| 0002 | f | f | f | t |
```sql
--第一种方法
select student_id,(case when math > (select avg(math) from grades) then 't' else 'f' end) as math,
(case when physics > (select avg(physics) from grades) then 't' else 'f' end) as physics,
(case when music > (select avg(music) from grades) then 't' else 'f' end) as music,
(case when art > (select avg(art) from grades) then 't' else 'f' end) as art
from grades;
--第二种方法
select student_id,
(case when math > t2.avg_math then 't' else 'f' end) as math,
(case when physics > t2.avg_physics then 't' else 'f' end) as physics,
(case when music > t2.avg_music then 't' else 'f' end) as music,
(case when art > t2.avg_art then 't' else 'f' end) as art
from grades t1,
(select avg(math) as avg_math,avg(physics) as avg_physics,avg(music) as avg_music,avg(art) as avg_art from grades) t2;
```
#### 1.6.2 写一个查询。查询学号中有'3'的学生中总成绩最高的学号及其总成绩。(禁止使用临时表)
```sql
select student_id,(math+physics+music+art) as total from grades where student_id like '%3%' order by total desc limit 1;
```
#### 1.6.3 写一个查询，创建一个函数get_credit，输入一个整数表示成绩，返回一个小数表示该成绩可以获得的绩点。
其中0-59获得0绩点，60-69获得0.1绩点，70-79获得0.2绩点，80-89获得0.3绩点，90-100获得0.4绩点。(禁止使用临时表)
```sql
create or replace function get_credit(i in int,o out float) as
$$
begin
if i <= 59 then o := 0;
elsif i <= 69 then o := 0.1;
elsif i <= 79 then o := 0.2;
elsif i <= 89 then o := 0.3;
elsif i <= 100 then o := 0.4;
else o := null;
end if;
return o;
end;
$$LANGUAGE plpgsql;
```
#### 1.6.4 写一个查询，查询每位学生的总绩点及其按照总绩点排序的排名，并按照排名从高到低排序结果。(禁止使用临时表)
```sql
--第一种方法
select student_id,((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 end) +
(case when physics <= 59 then 0
when physics <= 69 then 0.1
when physics <= 79 then 0.2
when physics <= 89 then 0.3
when physics <= 100 then 0.4 end) +
(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 end) +
(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 end)) as total_point,
DENSE_RANK() OVER(ORDER BY total_point) as paimi
from grades
order by paimi desc;
--第二种方法
select *,dense_rank() over(order by total_point desc) as paimi
from (select student_id,get_credit(math) + get_credit(physics) + get_credit(music) + get_credit(art) as total_point from grades)
```
#### 1.6.5 学号脱敏
创建函数add_mask，输入第一个字符串表示需要展示的学号，第二个字符串表示需要替换为"***"的学号。为保护学生查询成绩时仅看到自己的成绩与排名，不展示其他学生的学号，利用该函数，当学号为"0001"的学生执行上一题的查询时，对学号做模糊处理
```sql
--创建函数
create or replace function add_mask(a in varchar(10), b in varchar(10), c out varchar(10))
as $$
begin
if b = a then
c := b;
else
c := '***';
end if;
end;
$$ LANGUAGE plpgsql;
select add_mask('0001',student_id),math,physics,music,art from grades;
```
### 1.7 学生成绩统计2
学生表(成绩里面有空值)
| 学生编号 | 成绩 | 月份 |
| ---------- | ----- | ----- |
| student_id | score | month |
| 1 | 56 | 1 |
| 2 | 68 | 1 |
| 3 | | 1 |
| 4 | 67 | 1 |
| 5 | | 1 |
| 6 | 53 | 1 |
| 1 | 56 | 2 |
| 2 | 68 | 2 |
| 3 | | 2 |
| 4 | 67 | 2 |
| 5 | 99 | 2 |
| 6 | | 2 |
```sql
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,NULL,1),(6,53,1)
,(1,56,2),(2,68,2),(3,NULL,2),(4,67,2),(5,99,2),(6,NULL,2);
```
#### 1.7.1 查询月考平均成绩比学生编号5大的学生
```sql
select id,round(avg(nvl(score,0)),2) as avgscore
from student
group by id
having avgscore > (select round(avg(nvl(score,0)),2) from student where id=5);
```
#### 1.7.2 查询每次月考成绩大于平均成绩的学生
```sql
select s.*,a.avgscore from student s ,(select month,round(avg(nvl(score,0)),2) as avgscore from student group by month) a
where s.month=a.month
and s.score > a.avgscore;
```
#### 1.7.3 查询每次平均成绩差值
```sql
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;
```
### 1.8 学生成绩统计(缺考、中位数)
请基于以下代码创建表及插入数据，并完成以下要求
```sql
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,'Jerry',510,2,1),(5,'Lee',210,2,1),(3,'Tom',600,2,2),(4,'Jack',300,2,2),(6,'Jacy',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,'Jacy',410,3,2);
```
#### 1.8.1 输出每月月考总分都比学号为5的同学分数高的所有学生信息
```sql
select sno,sname,score,month,cno from
(select *,count(sno)over(partition by sno) as stunum
from student s1
where score > (select nvl(score,0) from student where sno=5 and month=s1.month))
where stunum = (select count(distinct month) from student);
```
#### 1.8.2 输出每次月考缺考的学生信息，要求打印姓名、班级编号和缺考次数
```sql
select sname,cno,count(*) from student where score is null group by sname,cno;
```
#### 1.8.3 输出每次月考都和tom同时缺考的所有学生信息，要求打印学号、姓名和月考总分
```sql
select a.sno,a.sname,a.score FROM
student a,
(select * FROM student where sname='Tom' and score is null) b
where a.month = b.month
and a.sname != 'Tom'
and a.score is null;
```
#### 1.8.4 输出全校月考中位数分数。(GaussDB分布式中没有median函数)
```sql
select month,round(avg(score),2) from
(select a.*,b.num,row_number()over(partition by a.month order by a.score) as rowno
from student a,
(select month,count(*) as num from student group by month) b
where a.month = b.month and a.score is not null)
where case when num%2 = 0 then rowno=(num/2) or rowno=(num/2)+1 else rowno=ceil(num/2)end
group by month;
```
#### 1.8.5 统计每个班每次月考的最高分数，要求打印班级名称，考试时间和月考分数
```sql
select c.cname,s.month,max(s.score) as maxscore from student s,class c
where s.cno = c.cno
group by c.cname,s.month
order by 1,2
;
```
### 1.9 班级成绩分析比较
表同上题
#### 1.9.1 输出class1班级中比class2班级每月月考最低分还低的学生信息，要求打印学号、姓名和月考总分
```sql
select c1.sno,c1.sname,c1.score from
(select * from student where cno=(select cno from class where cname='class1')) c1,
(select s.month,min(nvl(s.score,0)) as min_score from student where cno=(select cno from class where cname='class2') group by s.month) c2
where c1.month = c2.month and nvl(c1.score,0) < c2.min_score;
```
#### 1.9.2 打印月考总分,平均分最高的学生信息，输出学号、姓名和月考总分平均分
```sql
select sno,sname ,round(avg(nvl(score,0)),2) avgscore from student
group by sno ,sname
order by avgscore desc limit 1;
```
#### 1.9.3 输出每个学生月考平均分和最高月考平均分学生之间的分数差距，打印学号、姓名、月考平均分和差距分数
```sql
select av.sno,av.sname,av.avgscore,ma.maxscore - av.avgscore as diff from
(select sno,sname,round(avg(nvl(score,0)),2) as avgscore from student group by sno,sname) av,
(select round(avg(nvl(score,0)),2) as avg_score from student group by sno order by avg_score desc limit 1) ma
;
```
### 1.10 科目成绩统计1
基于以下学生成绩表，完成以下实验要求
```sql
-- 创建表
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.10.1 求math和phy总成绩以及art和music的总成绩
```sql
select sum(math + phy),sum(art+music) from stu;
```
#### 1.10.2 计算学生总成绩，并基于总成绩排序
```sql
select id,(math+art+phy+music) as total from stu order by 2;
```
#### 1.10.3 art和music总分排名前5的，总成绩加5分，查询最终的所有学生总成绩
```sql
select sum(case when id in (select id from (select id,round(art+music,2) from stu order by 2 desc limit 5)) then math+art+phy+music+5 else math+art+phy+music end) as total from stu;
```
### 1.11 科目成绩统计2
基于以下学生成绩表，完成以下实验要求
```sql
-- 创建表
create table student(
student_id int not null, -- 编号
math int not null, -- 数学成绩
pysical int not null, -- 物理成绩
art int not null, -- 艺术成绩
music int not null -- 音乐成绩
);
-- 数据导入
insert into student values(1001,56,84,65,35);
insert into student values(1002,81,86,95,72);
insert into student values(1003,69,85,76,76);
insert into student values(1004,76,95,76,62);
insert into student values(1005,96,63,52,75);
insert into student values(1006,85,68,26,76);
insert into student values(1007,76,58,95,49);
insert into student values(1008,76,85,96,45);
insert into student values(1009,56,78,96,59);
insert into student values(1010,76,85,95,45);
insert into student values(1011,88,99,77,66);
```
#### 1.11.1 对math和pysical排名前十的学生，art加5分，求所有学生总成绩
```sql
select sum(case when student_id in (select student_id from student order by (math+pysical) desc limit 10) then math+pysical+art+music+5 else math+pysical+art+music end) as total_score from student;
```
#### 1.11.2 获取art和music排名前十，同时math和pysical在art和music前十名的学生信息
```sql
select s1.*,s2.mpscore from
(select student_id,(art+music) as asmscore from student order by 2 desc limit 10) s1
join
(select student_id,(math+pysical) as mpscore from student order by 2 desc limit 10) s2
on s1.student_id=s2.student_id;
```
#### 1.11.3 查看学生每门成绩与每门平均成绩的差值
```sql
select s1.student_id,s1.math,(s1.math-s2.math_avg) as math_diff,s1.pysical,(s1.pysical-s2.pysical_avg) as pysical_diff,s1.art,(s1.art-s2.art_avg) as art_diff,s1.music,(s1.music-s2.music_avg) as music_diff
from student s1,
(select round(avg(nvl(math,0)),2) as math_avg,round(avg(nvl(pysical,0)),2) as pysical_avg,round(avg(nvl(art,0)),2) as art_avg,round(avg(nvl(music,0)),2) as music_avg from student) s2;
```
#### 1.11.3 编写存储过程，输入学生id返回总成绩
```sql
create or replace procedure get_total(a in integer,b out integer)
as
begin
select (math+pysical+art+music) into b from student where student_id=a;
end;
```
#### 1.11.4 编写存储过程，输入学号和科目名称，返回对应的平均成绩
```sql
create or replace procedure get_avg(a in integer,kemu in varchar(20),b out float)
as
begin
case when kemu = 'math' then select avg(nvl(math,0)) into b from student where student_id = a;
when kemu = 'pysical' then select avg(nvl(pysical,0)) into b from student where student_id = a;
when kemu = 'art' then select avg(nvl(art,0)) into b from student where student_id = a;
when kemu = 'music' then select avg(nvl(music,0)) into b from student where student_id = a;
else b := null;
end case;
end;
select get_avg(1010,'math');
```
#### 1.11.5 编写存储过程，对于学生每科成绩0-59,绩点0，60-69,绩点0.1，70-79,绩点0.2，80-89,绩点0.3，90-100,绩点0.4，输入学号和科目名称，返回对应的绩点
```sql
create or replace procedure get_point(a in integer, kemu in varchar(20), b out float)
as
begin
case when kemu = '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 null end) into b from student where student_id=a;
when kemu = 'pysical' then select (case when pysical <= 59 then 0
when pysical <= 69 then 0.1
when pysical <= 79 then 0.2
when pysical <= 89 then 0.3
when pysical <= 100 then 0.4
else null end) into b from student where student_id=a;
when kemu = '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 null end) into b from student where student_id=a;
when kemu = '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 null end) into b from student where student_id=a;
else b := null;
end case;
end;
select get_point(1001,'math');
```
### 1.12 索引与数据分布情况
当前有一张表test，请给予该表完成以下操作
```sql
drop table if exists test;
create table test (id int primary key, name varchar(50), age int) distribute by hash(name);
```
数据导入
```sql
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,'zhozhou',35),(15,'yunyun',39);
```
#### 1.12.1 请为name字段上创建索引，并查看索引的详细信息
```sql
--1.创建索引
create index idx_name on test (name);
--2.查看索引信息
select * from pg_indexes where tablename='test';
```
#### 1.12.2 请查看test表的分布类型和分片信息
```sql
--分布类型
select pclocatortype from pgxc_class where pcrelid=(select oid from pg_class where relname='test');
--分片信息
select * from pgxc_node where node_id in (select distinct xc_node_id from test);
```
#### 1.12.3 请查看数据在各个节点的分布情况，显示表名、节点名、数据量
```sql
select tablename,nodename,dnsize from table_distribution();
--如果题目要求了具体的表
select * from table_distribution('root','test');
--table_distribution函数只能在分布式下使用
```
#### 1.12.4 删除主键索引
```sql
alter table test drop constraint test_pkey cascade;
```
#### 1.12.5 重建主键索引
```sql
alter table test add constraint test_pk primary key (id);
alter index test_pk rebuild;
```
#### 1.12.6 对age列添加检查约束，要求只能写入大于18的值
```sql
alter table test add constraint age_check check (age>18);
```
#### 1.12.7 对name列添加非空约束
```sql
alter table test modify name not null;
```
## 2. 安全管理技术
### 2.1 用户权限查询
#### 2.1.1 创建用户user1
```sql
create user user1 identified by '1qaz@WSX';
```
#### 2.1.2 查看用户user1和数据库的相关权限，要求显示数据库名、用户名、数据库的权限
```sql
select a.* from
(select datname,(aclexplode(datacl)).grantee, (aclexplode(datacl)).privilege_type from pg_database) a,pg_roles b
where (a.grantee=b.oid or a.grantee=0) and b.rolname='user1' and a.datname not like 'template%';
```
#### 2.1.3 把表table1的select和alter权限赋给user1
```sql
select schemaname,tablename,tableowner FROM pg_tables where tablename='table1';
grant usage on schema root to user1;
grant select,alter on root.table1 to user1
```
#### 2.1.4 查询table1的owner，要求显示表名和owner
```sql
select tablename,tableowner from pg_tables where tablename='table1';
```
#### 2.1.5 查询user1的表权限，要求显示表名、shcema名、用户名、相关表权限
```sql
select table_name,table_schema,privilege_type from information_schema.table_privileges where grantee='user1';
```
#### 2.1.6 查询对表table1有操作权限的用户，要求显示2列：用户名、操作权限
```sql
select grantee,privilege_type from information_schema.table_privileges where table_name='table1';
```
### 2.2 用户及权限管理1
使用root用户，登录postgres数据库，创建数据库hcie,同时在hcie库下创建模式名为root的模式，在该模式下创建表school_department.
```sql
--创建数据库
create database hcie; --建完后，注意切换数据库
--创建模式
create schema root; --建完后，注意切换模式
--创建表
drop table if exists root.school_department;
create table root.school_department(
depart_id int primary key,
depart_name nchar(30) not null,
depart_teacher int not null
);
```
#### 2.2.1 创建用户test_user,密码为Hice@123
```sql
create user test_user identified by 'Hice@123';
```
#### 2.2.2 写一个查询，通过系统表pg_database和系统视图pg_roles进行关联查询，查看test_user拥有的数据库权限。显示结果包括数据库名、用户名、拥有的数据库权限，共三列。(禁止使用临时表)
```sql
select a.datname,b.rolname,a.privilege_type from
(select datname,(aclexplode(datacl)).grantee,(aclexplode(datacl)).privilege_type from pg_database) a,
pg_roles b
where (a.grantee=b.oid or a.grantee=0) and b.rolname='test_user';
```
#### 2.2.3 将表school_department的alter权限赋予test_user
```sql
grant usage on schema root to test_user;
grant alter on root.school_department to test_user;
```
#### 2.2.4 写一个查询。查看用户test_user的table权限。显示结果包括表名、模式名、用户名及相关权限。共四列。(禁止使用临时表)
```sql
select table_name,table_schema,grantee,privilege_type from information_schema.table_privileges where grantee='test_user';
```
#### 2.2.5 写一个查询。查看表school_department的owner。显示结果包括表名、用户名，共两列。(禁止使用临时表)
```sql
select tablename,tableowner from pg_tables where tablename='school_department';
```
#### 2.2.6 写一个查询。查看哪些用户可以对表school_department进行操作，及操作类型。显示结果包括用户名、操作类型，共两列。(禁止使用临时表)
```sql
select grantee,privilege_type from information_schema.table_privileges where table_name='school_department';
```
#### 2.2.7 切换到postgres数据库中，并用test_user用户创建一张表t_test，这张表有两列，分别是c1 integer,c2 float.
```sql
--root用户登录postgres
grant create any table to test_user
--test_user用户登录postgres
create table t_test(c1 integer,c2 float);
```
#### 2.2.8 删除用户test_user
```sql
--root用户登录postgres
drop table t_test;
revoke create any table from test_user;
--root用户登录hcie
drop user test_user cascade;
```
### 2.3 用户及权限管理2
#### 2.3.1 使用两个查询语句，查看"postgres"数据库的最大连接数和已使用连接数
```sql
--查看最大连接数
select datname,datconnlimitfrom pg_database where datname='postgres';
--查看已使用连接数
select datname,count(*) from pg_stat_activity where datname='postgres' group by datname;
```
#### 2.3.2 创建用户user_test,并指定该用户具有创建数据库和创建角色的权限
```sql
create user user_test createrole createrole identified by '1qaz@WSX';
```
#### 2.3.3 创建表table_test,此表中包含一个名为col_test的列，为用户user_test授权在table_test表的col_test列上的查询、更新权限。
```sql
--创建表
create table table_test(col_test int);
--授权
grant select(col_test),update(col_test) on table_test to user_test;
--确认方法
select * FROM information_schema.column_privileges where table_name='table_test';
```
#### 2.3.4 收回用户user_test在table_test表上col_test列上的更新权限
```sql
revoke update(col_test) on table_test from user_test;
```
#### 2.3.5 创建角色role_test,此角色拥有审计权限
```sql
create role role_test auditadmin identified by '1qaz@WSX';
```
#### 2.3.6 将角色role_test的权限授权给用户user_test,并允许用户将此权限在授权给其他用户或角色。
```sql
grant role_test to user_test with admin option;
```
#### 2.3.7 用户user_test账号被盗，请手动锁定此账号
```sql
alter user user_test account lock;
```
#### 2.3.8 级联删除用户user_test,并重新创建，将账号设置为在2023年国庆期间有效。
```sql
--删除用户
drop user user_test cascade;
--创建用户
create user user_test createdb createrole identified by '1qaz@WSX' valid begin '2023-10-01 00:00:00' valid until '2023-10-08 00:00:00';
```
### 2.4 行级访问控制(查看自身是数据)
当前有all_data表，字段信息如下，请给予此表实现行级访问控制
```sql
-- 创建表
create table all_data(
role varchar(50), -- 账号
name varchar(50), --姓名
age int -- 年龄
);
-- 导入数据
insert into all_data values('root','zhangsan',18),('sjh111','lisi',43),('sjh113','wangwu',35);
```
#### 2.4.1. 打开all_data表的行访问控制策略开关
```sql
alter table all_data enable row level security;
```
#### 2.4.2. 创建行访问控制策略，当前用户只能查看用户自己的数据
```sql
CREATE ROW LEVEL SECURITY POLICY all_data_rls ON all_data USING(role = CURRENT_USER);
```
#### 2.4.3. 删除策略
```sql
DROP ROW LEVEL SECURITY POLICY all_data_rls ON all_data;
```
#### 2.4.4. 关闭行级访问策略
```sql
alter table all_data disable row level security;
```
### 2.5 列级访问控制(医生只能看到自己病人信息)
使用root用户，登录postgres数据库，在root模式下创建三个用户carl、nora、bob，密码均为test@123
```sql
create user carl identified by 'test@123';
create user nora identified by 'test@123';
create user bob identified by 'test@123';
```
创建一张汇总的病人信息表patient_info,记录病人的编号(id)、姓名(name)、接待医生(doctor)、手机号(num)和年龄(age),并插入数据
```sql
drop table if exists patient_info;
create table patient_info
(
id int,
name varchar(10),
doctor varchar(10),
num varchar(15),
age int);
insert into patient_info values (1,'peter', 'carl','13265894589',10);
insert into patient_info values (2,'bob', 'nora','16265894589', 20);
insert into patient_info values (3,'amy', 'nora','15165425589', 50);
insert into patient_info values (4,'avi','carl','13164784589',35);
insert into patient_info values (5, 'tom','carl','13065785589',26);
insert into patient_info values (6, 'mary', 'carl','18167584589',15);
```
#### 2.5.1 root登录数据库postgres，进行合理的权限分配，使得carl和ngra有权限查看完整的病人信息表patient_info,bob只有权限查看表patient_info的id和doctor列的信息
```sql
grant usage on schema root to carl,nora,bob;
grant select on root.patient_info to carl,nora;
grant select(id,doctor) on root.patient_info to bob;
```
#### 2.5.2 设置合理的行级访问控制策略，使得医生carl和ngra只能看到由自己接待的病人信息，例如carl只能看到doctor列值为carl的那些行数据
```sql
--开启行级访问控制
alter table root.patient_info ENABLE ROW LEVEL SECURITY;
--创建访问策略
CREATE ROW LEVEL SECURITY POLICY patient_info_rls ON root.patient_info to carl,nora USING(doctor=current_user);
```
#### 2.5.3 carl登录数据库postgres,查看数据表patient_info成功，且只显示doctor列值为carl的4行数据。
nora登录数据postgres,查看数据表patient_info成功，且只显示doctor列值为nora的2行数据
```sql
--切换到carl用户
select * from root.patient_info;
--切换到nora用户
select * from root.patient_info;
```
#### 2.5.4 再设置一条合理的行级访问控制策略，使得医生carl只能看到由自己接待的且年龄大于30的病人的信息，医生ngra任然能看到所有由自己接待的病人
```sql
alter ROW LEVEL SECURITY POLICY patient_info_rls ON root.patient_info to nora USING(doctor=current_user);
CREATE ROW LEVEL SECURITY POLICY patient_info_rls1 ON root.patient_info to carl using(doctor=current_user and age>30);
```
#### 2.5.5 carl登录数据库postgres,查看数据表patient_info成功，且只显示doctor列值为carl,且age>30的1行数据
```sql
--切换到carl用户
select * from root.patient_info;
```
#### 2.5.6 删除上述配置的所有行级访问控制策略
```sql
drop ROW LEVEL SECURITY POLICY patient_info_rls ON root.patient_info;
drop ROW LEVEL SECURITY POLICY patient_info_rls1 ON root.patient_info;
```
#### 2.5.7 关闭表的行控制开关，并且级联删除用户
```sql
alter table root.patient_info disable ROW LEVEL SECURITY;
drop user carl cascade;
drop user nora cascade;
drop user bob cascade;
```
### 2.6 行级访问控制(银行用户查看对应卡型)
执行以下sql还原表和数据
```sql
-- 创建表
CREATE TABLE bank_card(
b_number NCHAR(30) PRIMARY KEY,
b_type NCHAR(20),
b_c_id INT NOT NULL);
-- 导入数据
INSERT INTO bank_card(b_number, b_type, b_c_id) VALUES ('6222021302020000001','信用卡', 1);
INSERT INTO bank_card(b_number, b_type, b_c_id) VALUES ('6222021302020000002','信用卡', 3);
INSERT INTO bank_card(b_number, b_type, b_c_id) VALUES ('6222021302020000003','信用卡',5);
INSERT INTO bank_card(b_number, b_type, b_c_id) VALUES ('6222021302020000004','信用卡', 7);
INSERT INTO bank_card(b_number, b_type, b_c_id) VALUES ('6222021302020000005','储蓄卡', 9);
INSERT INTO bank_card(b_number, b_type, b_c_id) VALUES ('6222021302020000006','储蓄卡', 1);
INSERT INTO bank_card(b_number, b_type, b_c_id) VALUES ('6222021302020000007','储蓄卡', 3);
INSERT INTO bank_card(b_number, b_type, b_c_id) VALUES ('6222021302020000008','储蓄卡',5);
INSERT INTO bank_card(b_number, b_type, b_c_id) VALUES ('6222021302020000009','储蓄卡', 7);
INSERT INTO bank_card(b_number, b_type, b_c_id) VALUES ('6222021302020000010','储蓄卡', 9);
```
#### 2.6.1 创建用户crecard_mger、savcard_mger，密码均为"Test@123"
```sql
create user crecard_mger identified by "Test@123";
create user savcard_mger identified by "Test@123";
```
#### 2.6.2 给上题中创建的两个用户授予bank_card表的读取权限
```sql
grant usage on schema root to crecard_mger,savcard_mger;
grant select on root.bank_card to crecard_mger,savcard_mger;
```
#### 2.6.3 打开bank_card表的行级访问控制开关
```sql
alter table root.bank_card ENABLE ROW LEVEL SECURITY;
```
#### 2.6.4 创建行级访问控制策略bank_card_rls，要求crecard_mger用户只能查看信用卡信息，savcard_mger用户只能查看储蓄卡信息
```sql
CREATE ROW LEVEL SECURITY POLICY bank_card_rls ON root.bank_card to crecard_mger,savcard_mger USING(b_type = (case when current_user = 'crecard_mger' then '信用卡'
when current_user = 'savcard_mger' then '储蓄卡'
else null end));
```
#### 2.6.5 切换到crecard_mger用户查看bank_card表内容
```sql
das右上角切换用户
select * from bank_card;
```
#### 2.6.6 使用root用户删除行级控制策略bank_card_rls，并关闭表的行级访问控制开关
```sql
drop ROW LEVEL SECURITY POLICY bank_card_rls ON root.bank_card;
alter table root.bank_card disable ROW LEVEL SECURITY;
```
### 2.7 数据库连接查看
#### 2.7.1 创建user2用户，user2用户需要具备创建数据库的权限
```sql
create user user2 createdb identified by '1qaz@WSX';
```
#### 2.7.2 查询用户的连接数上限
```sql
select rolname,rolconnlimit from pg_roles where rolname='user2';
```
#### 2.7.3 设置user2用户连接数为100
```sql
alter user user2 connection limit 100;
```
#### 2.7.4 查询postgres数据库连接上限，显示数据库名、上限数量
```sql
select datname,datconnlimit from pg_database where datname='postgres';
```
#### 2.7.5 查询postgres数据库中用户已使用的会话数量
```sql
select count(*) from pg_stat_activity where datname='postgres';
```
#### 2.7.6 查看所有用户已使用的会话连接数
```sql
select count(*) from dv_sessions;
```
#### 2.7.7 查询全局最大连接数
```sql
--1.
show max_connections;
--2.
select name,setting from pg_settings where name='max_connections';
```
#### 2.7.8 查询会话状态，显示datid、pid、state
```sql
select datid,pid,state from pg_stat_activity;
```
### 2.8 数据库连接设置
#### 2.8.1 查询全局最大连接数
```sql
--1.
show max_connections;
--2.
select name,setting from pg_settings where name='max_connections';
```
#### 2.8.2 创建用户并指定最大连接数，指定最大连接为20000
```sql
create user aa identified by '1qaz@WSX' connection limit 20000;
```
#### 2.8.3 查看用户的连接数，展示用户名、最大连接数
```sql
select rolname,rolconnlimit from pg_roles where rolname='aa';
```
#### 2.8.4 修改用户的最大连接数为10000
```sql
alter user aa connection limit 10000;
```
#### 2.8.5 创建数据库指定最大连接数，指定最大连接数为100000，并使用sql查看展示数据库名、最大连接数
```sql
create database bb with connection limit 100000;
select datname,datconnlimit from pg_database where datname='bb';
```
#### 2.8.6 修改数据库的最大连接数，将最大连接数修改为200000
```sql
alter database bb connection limit 200000;
```
### 2.9 动态数据脱敏
#### 2.9.1 创建dev_mask和bob_mask用户
```sql
create user dev_mask identified by '1qaz@WSX';
create user bob_mask identified by '1qaz@WSX';
```
#### 2.9.2 创建表tb_for_masking,字段信息包括(col1 text, col2 text, col3 text)
```sql
create table tb_for_masking(col1 text,col2 text,col3 text);
```
#### 2.9.3 为col1设置脱敏策略，使用maskall对col1列进行数据脱敏
```sql
--创建资源
CREATE RESOURCE LABEL mask_lb1 ADD COLUMN(tb_for_masking.col1);
--创建脱敏
CREATE MASKING POLICY maskpol1 maskall ON LABEL(mask_lb1);
```
#### 2.9.4 为maskpol1脱敏策略添加描述信息 "masking policy for tb_for_masking.col1"
```sql
alter masking policy maskpol1 comments 'masking policy for tb_for_masking.col1';
```
#### 2.9.5 为maskpol1脱敏策略在原基础上新增对col2列做随机脱敏，脱敏函数使用randommasking
```sql
--1.创建资源标签
create resource lable if not exists mask_lb2 add column(tb_for_masking.col2);
--2.修改脱敏策略
alter masking policy maskpol1 add randommasking on lable(mask_lb2);
```
#### 2.9.6 修改maskpol1移除在col2列上的randommasking脱敏方式
```sql
alter masking policy maskpol1 remove randommasking on lable(mask_lb2);
```
#### 2.9.7 修改maskpol1一项脱敏方式，将在col1列上的maskall脱敏方式修改为randommasking脱敏
```sql
alter masking policy maskpol1 modify randommasking on lable(mask_lb2);
```
#### 2.9.8 修改脱敏策略maskpol1使之仅对用户dev_mask和bob_mask,客户端工具为psql和gsql,IP地址为'10.20.30.40','127.0.0.0/24'场景生效
```sql
alter masking policy maskpol1 modify (filter on roles(dev_mask,bob_mask),app(psql,gsql),IP('10.20.30.40','127.0.0.0/24'));
```
#### 2.9.9 修改脱敏策略maskpol1,使之对所有用户场景生效
```sql
alter masking policy maskpol1 drop filter;
```
#### 2.9.10 禁用脱敏策略
```sql
alter masking policy maskpol1 disable;
```
### 2.10 传统审计功能
#### 2.10.1 请创建一个审计管理员用户hcie_audit
```sql
create user hcie_audit auditadmin identified by '1qaz@WSX';
```
#### 2.10.2 切换用户查看guc参数审计开关是否开启
```sql
--切换用户(das在右上角切换)
\c - hcie_audit --命令行切换用户
--查看guc参数
show enable_audit;
select name,setting from pg_settings where name like '%enable_audit%';
```
#### 2.10.3 用sql查看日志存储最大空间
```sql
--1.
show audit_space_limit;
--2.
select name,setting from pg_settings where name like '%audit_space_limit%';
```
#### 2.10.4 查看用户hcie_audit成功登录postgres的记录
```sql
select * from pg_query_audit(now()-1/24,now()) where database='postgres' and type='login_success' and result='ok' and username='hcie_audit';
```
#### 2.10.5 查看过去一天所有产生审计日志的总数，当前时间要求使用now()
```sql
select count(*) from pg_query_audit(now()-1,now());
```
#### 2.10.6 删除'2020-09-20 00:00:00'到'2020-09-21 00:00:00'时间段的审计记录
```sql
select * from pg_delete_audit('2020-09-20 00:00:00','2020-09-21 00:00:00');
```
#### 2.10.7 删除数据库db2;级联删除用户user1
```sql
--1.删除数据库
drop database db2;
--2.级联删除用户
drop user user1 cascade;
```
### 2.11 统一审计策略
#### 2.11.1 创建用户user1,密码'test@123'
```sql
create user user1 identified by 'test@123';
```
#### 2.11.2 给用户授予查看审计权限，同时可以创建审计策略
```sql
alter user user1 auditadmin poladmin;
```
#### 2.11.3 切换至user1,创建审计策略adt1,对数据库执行create操作创建
```sql
--1.切换用户(das在右上角切换)
\c - user1 --命令行切换用户
--2.确认参数
enable_security_policy=on --cn 和 dn都要开启
--3.创建审计策略
create audit policy adt1 privileges create;
```
#### 2.11.4 创建审计策略adt2,数据库执行select操作创建审计策略
```sql
create audit policy adt2 access select;
```
#### 2.11.5 修改adt1,对IP地址为'10.20.30.40'进行审计
```sql
alter audit policy adt1 modify (filter on IP('10.20.30.40'));
```
#### 2.11.6 为统一审计对象策略adt1,添加描述信息 "audit policy for tb1"
```sql
alter audit policy adt1 comments 'audit policy for tb1';
```
#### 2.11.7 创建表tb1,字段自定义
```sql
--切换到root用户
create table tb1(id int);
```
#### 2.11.8 创建审计策略adt3,仅审计记录用户root,在执行针对表tb1资源进行的select、insert、delete操作数据库创建审计策略
```sql
--1.创建资源标签
create resource label if not exists tb1_label add table(tb1);
--2.创建审计策略
create audit policy adt3 access select on label(tb1_label) ,insert on label(tb1_label),delete on label(tb1_label) filter on roles(root);
```
#### 2.11.9 禁用adt1审计策略
```sql
alter audit policy adt1 disable;
```
#### 2.11.10 删除以上创建的审计策略，级联删除用户user1
```sql
DROP AUDIT POLICY adt1;
DROP AUDIT POLICY adt2;
DROP AUDIT POLICY adt3;
--切换为root用户
drop user if exists user1 cascade;
```
### 2.12 账本数据库
#### 2.12.1 创建防篡改模式ledgernsp
```sql
--检查参数enable_ledger=on,默认是关闭
create schema ledgernsp WITH BLOCKCHAIN;
```
#### 2.12.2 创建防篡改用户表usertable
```sql
create table ledgernsp.usertable(id int);
```
#### 2.12.3 查看全局表(所有账本表共有),历史表(每张账本表独有)
```sql
--1.hash列为隐藏列,需要指定才能查看
select *,hash from ledgernsp.usertable;
--2.全局表
select * from pg_global_chain;
--3.历史表
select * from blockchain.ledgernsp.usertable_history;
```
#### 2.12.4 校验指定防篡改用户表的表级数据hash值与其对应历史表hash一致性
```sql
--1.校验
select ledger_hist_check('ledgernsp',.'usertable');
--2.修复
select ledger_hist_repair('ledgernsp',.'usertable');
```
#### 2.12.5 校验指定防篡改用户表对应的历史表hash与全局历史表对应的relhash一致性.
```sql
--1.校验
select ledger_gchain_check('ledgernsp',.'usertable');
--2.修复
select ledger_gchain_repair('ledgernsp',.'usertable');
```
## 3. 存储过程与触发器
### 3.1 函数(统计总分)触发器(delete)
<table>
<tr>
<td>
<table>
<thead>
<tr><th colspan="3">学生表 STUDENT</th></tr>
</thead>
<tbody>
<tr><td>序号</td><td>字段名</td><td>类型</td></tr>
<tr><td>1</td><td>sno</td><td>integer</td></tr>
<tr><td>2</td><td>sname</td><td>varchar(50)</td></tr>
<tr><td>3</td><td>ssex</td><td>varchar(5)</td></tr>
<tr><td>4</td><td>sage</td><td>integer</td></tr>
</tbody>
</table>
</td>
<td>
<table>
<thead>
<tr><th colspan="3">课程表 COURSE</th></tr>
</thead>
<tbody>
<tr><td>序号</td><td>字段名</td><td>类型</td></tr>
<tr><td>1</td><td>cno</td><td>integer</td></tr>
<tr><td>2</td><td>cname</td><td>varchar(50)</td></tr>
<tr><td>3</td><td>credit</td><td>integer</td></tr>
</tbody>
</table>
</td>
<td>
<table>
<thead>
<tr><th colspan="3">选课表 ELECTIVE</th></tr>
</thead>
<tbody>
<tr><td>序号</td><td>字段名</td><td>类型</td></tr>
<tr><td>1</td><td>sno</td><td>integer</td></tr>
<tr><td>2</td><td>cno</td><td>integer</td></tr>
<tr><td>3</td><td>grade</td><td>integer</td></tr>
</tbody>
</table>
</td>
</tr>
</table>
#### 3.1.1. 创建视图select_sd，查看学生成绩信息，包括学生姓名，课程名称，课程成绩
```sql
create view select_sd as select s.sname,c.cname,e.grade from student s,course c,elective e where e.sno=s.sno and e.cno=c.cno;
```
#### 3.1.2. 创建函数func_sum，返回某个学生的分数总和。
```sql
create or replace function func_sum(a in varchar(50),b out integer)
as $$
begin
select sum(nvl(ele.grade,0)) into b from student stu,elective ele where stu.sno = ele.sno and stu.sname=a;
end;
$$ LANGUAGE plpgsql;
```
#### 3.1.3. 创建触发器delete_ele，在student表上绑定触发器delete_ele，在删除表中某个学生时，将elective表中该学生的选课记录一并删除。
```sql
--创建删除函数
CREATE OR REPLACE FUNCTION delete_ele() RETURNS TRIGGER AS
$$
DECLARE
BEGIN
DELETE FROM elective WHERE sno=OLD.sno;
RETURN OLD;
END
$$ LANGUAGE plpgsql;
--创建触发器
CREATE TRIGGER delete_ele BEFORE DELETE ON student FOR EACH ROW EXECUTE PROCEDURE delete_ele();
```
### 3.2 存储过程(数据打印)
以下为表创建sql语句(此题无数据)
```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);
```
#### 3.2.1. 创建存储过程pro_curs_1，使用游标打印各部门总人数，按人数降序排序，打印格式如下：
部门名称1----人数
部门名称2----人数
打印操作可以使用dbe_ouput.print_line(outputstr)接口
```sql
create or replace procedure pro_curs_1()
as
declare
aa varchar(50);
bb integer;
cursor c1 is
select b.name,count(a.name) from teacher a,department b where a.deptno = b.id group by b.name;
begin
OPEN C1;--打开游标
LOOP
--通过游标取值
FETCH C1 INTO aa, bb;
EXIT WHEN C1%NOTFOUND;
DBE_OUTPUT.PRINT_LINE(aa||'---'||bb);
END LOOP;
CLOSE C1;--关闭游标
end;
```
#### 3.2.2. 创建存储过程pro_curs_2，使用游标读取薪水降序排序的前三位老师和后三位老师的信息，分别获取id、姓名、部门名称、薪水和职称，请按以下格式打印：
ID-姓名-部门名称-薪水-职称
```sql
create or replace procedure pro_curs_2()
as
declare
pid integer;
pname varchar(50);
pdname varchar(50);
psalary float;
ptitle varchar(100);
cursor c2 is
(select a.id,a.name,b.name,a.salary,a.title from student a,department b where a.deptno=b.id order by salary desc limit 3)
union all
(select a.id,a.name,b.name,a.salary,a.title from student a,department b where a.deptno=b.id order by salary limit 3);
begin
OPEN C2;--打开游标
LOOP
--通过游标取值
FETCH C2 INTO pid, pname, pdname, psalary, ptitle;
EXIT WHEN C2%NOTFOUND;
DBE_OUTPUT.PRINT_LINE(pid||'-'||pname||'-'||pdname||'-'||psalary||'-'||ptitle);
END LOOP;
CLOSE C2;--关闭游标
end;
--执行
call pro_curs_2();
```
### 3.3 存储过程(生成记录)
基于以下信息表，完成以下实验要求。
```sql
-- 创建表
create table student(id serial, starttime timestamp(0));
```
#### 3.3.1. 编写存储过程，生成记录，输入个数，生成student,id从100000开始，starttime是当前时间
```sql
create or replace procedure pro_insert(a in integer)
as
declare
aa integer := 100000;
begin
for I in 1..a loop
insert into student values (aa,sysdate);
aa := aa + 1;
end loop;
end;
```
#### 3.3.2. 调用存储过程，生成90000条记录
```sql
call pro_insert(90000)
```
#### 3.3.3. 查看表记录
```sql
select * from student;
```
### 3.4 存储过程(成绩统计)
成绩表
| 编号 | 成绩 | 课程 |
| ---- | ---- | ---- |
| 001 | 86 | c1 |
| 002 | 95 | c2 |
课程表
| 课程编号 | 课程名称 |
| ---- | ---- |
| c1 | chinese |
| c2 | math |
```sql
--创建表 & 导入数据
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');
```
#### 3.4.1 编写存储过程输入课程c1获取平均成绩、数据编号和课程名称，根据平均成绩获取绩点数，0-59给0，60-69给0.1，70-79给0.2，80-89给0.3，90-100给0.4
```sql
create or replace procedure get_point(a in varchar(20),avgscore out float, kemu out varchar(20),point out float)
as
begin
--平均成绩
select avg(nvl(score,0)) into avgscore from score where cid=a;
--课程名称
select cname into kemu from course where cid=a;
--绩点
case when avgscore <= 59 then point := 0;
when avgscore <= 69 then point := 0.1;
when avgscore <= 79 then point := 0.2;
when avgscore <= 89 then point := 0.3;
when avgscore <= 100 then point := 0.4;
else point := null;
end case;
end;
select * from get_point('c1');
```
#### 3.5 存储过程(格式调整)
| 编号 | 成绩 | 课程 |
| ------ | ----- | ----- |
| '001' | 86 | 'c1' |
| '002' | 95 | 'c2' |
```sql
-- 创建表 & 导入数据
create table score(id varchar(20), score int,cid varchar(20));
insert into score values('''001''',86,'''c1'''),('''002''',95,'''c2''');
```
#### 3.5.1 要求输入c1返回该课程的成绩信息(课程和id本身带有单引号)
```sql
create or replace procedure get_score(a in varchar(20),b out integer)
as
declare
aa varchar(20);
begin
aa := concat('''',a,'''');
select score into b from score where cid=aa;
end;
call pro_xx('c1')
```
### 3.6 触发器(update)
本题根据教授详情表和部门表完成相应触发器创建使用
```sql
-- 创建表
CREATE TABLE TEACHER(ID INTEGER PRIMARY KEY,
NAME VARCHAR(50) NOT NULL ,
DEPTND INTEGER NOT NULL,
TETLE VARCHAR(50) NOT NULL);
CREATE TABLE DEPARTMENT(
ID INTEGER PRIMARY KEY,
NAME VARCHAR(50) NOT NULL ,
NUMBER_OF_SENIOR INTEGER 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 professor');
```
#### 3.6.1 创建tri_update_d触发器，如果修改number_of_senior字段时提示"不能随便修改部门教授职称人数"，如果已经有了tri_update_d触发器，则删除后在重新创建
```sql
--创建函数
create or replace function print_info() RETURNS TRIGGER as
$$
declare
begin
dbe_output.print_line("不能随便修改部门教授职称人数");
return null; --如果还能继续修改返回new,如果提示后不允许修改返回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();
```
#### 3.6.2 禁止触发器，修改department表中id=1的number_of_senior=10，并查出表中数据
```sql
--禁用触发器
alter table department disable trigger tri_update_d;
--修改表中数据
update department set number_of_senior=10 where id=1;
--查询表数据
select * from department;
```
#### 3.6.3 启动触发器，修改department表中id=1的number_of_senior=20
```sql
--启动触发器
alter table department enable trigger tri_update_d;
--修改表中数据
update department set number_of_senior=20 where id=1;
--查询表数据
select * from department;
```
### 3.7 触发器(insert)
本题根据以下表完成相应触发器创建使用
```sql
-- 创建表
create table staffs(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);
```
创建触发器，要求在staffs表插入一行数据时，自动往logger表中插入一条记录，记录sname和部门名称，并用当天的日期来标记该行数据的生成时间。
#### 3.7.1 创建触发器函数T_INS_F
```sql
create or replace function T_INS_F() returns trigger as
$$
begin
insert into logger values(new.sname, (select dept_name from dept where id=new.deptno), sysdate);
return new;
end;
$$ LANGUAGE plpgsql;
```
#### 3.7.2 创建触发器 T_INS_TR
```sql
CREATE TRIGGER T_INS_TR
AFTER INSERT ON staffs
FOR EACH ROW
EXECUTE PROCEDURE T_INS_F();
```
#### 3.7.3 禁用表staffs上的所有触发器
```sql
ALTER TABLE staffs DISABLE TRIGGER all;
```
#### 3.7.4 删除T_INS_TR触发器
```sql
DROP TRIGGER T_INS_TR ON staffs;
```
## 4. 数据库性能优化
### 4.1 性能优化(合适的索引)
通常的sql优化会通过参数调优的方式进行调整，例如如下参数
set enable_fast_query_shipping=off;
set enable_stream_operator=on;
请根据以下表完成数据库优化
```sql
-- 建表
create table tb_user(stu_no INT,stu_name VARCHAR(32),age INT,hobby_type INT) DISTRIBUTE BY HASH(age);
-- 插入数据
insert into tb_user select id,'xiaoming'||(random()*60+10)::int,
(random()*60+10)::int,
(random()*5+1)::int
from (select generate_series(1,100000)id)tb_user;
```
#### 4.1.1. 收集tb_user表的统计信息
```sql
analyze tb_user;
```
#### 4.1.2. 为下面两个查询语句创建索引，让执行计划和索引最合理
SQL1: explain analyze select * from tb_user where age=29 and stu_name='xiaoming';
SQL2: explain analyze select * from tb_user where stu_no=100 and age=29;
```sql
--1.
select gs_index_advise('select * from tb_user where age=29 and stu_name=''xiaoming''')
create index idx_stu_name_age on tb_user(stu_name,age);
--2.
select gs_index_advise('select * from tb_user where stu_no=100 and age=29')
create index idx_stu_no_age on tb_user(stu_no,age);
```
#### 4.1.3. 在上题操作的基础上，用3种不同方式使如下SQL不走索引
explain analyze select * from tb_user where stu_no=100 and age=29;
```sql
--1.
explain analyze select /*+ tablescan(tb_user) */ * from tb_user where stu_no=100 and age=29;
--2.
set cpu_index_tuple_cost=100000;
--3.
alter index idx_stu_no_age unusable;
```
### 4.2 性能优化(索引、重写)
当前有一张表test(id、kemu、classid、grade),里面有8万条数据
#### 4.2.1 查202202班级里面语文最低分是多少，要保障走索引
```sql
--收集统计信息
analyze test;
--索引建议
explain analyze select * from gs_index_advise('select min(grade) from test where classid=202202 and kemu=''yuwen''')
--创建索引
create index idx_classid_kemu on test(classid,kemu);
--查询
select /*+ indexscan(test idx_classid_kemu) */ min(grade) from test where classid=202202 and kemu='yuwen';
```
#### 4.2.2 查202202班级同一科目成绩比202201班级最高分高的同学，根据以下sql优化重写
原sql: select * from test t1 where t1.classid='202202' and grade>(select max(grade) from test t2 where t2.classid='202201' and t2.kemu=t1.kemu);
```sql
select *
from test t1,
(select id,kemu,max(grade) as maxgrade from test where classid='202201' group by id,kemu) t2
where t1.kemu = t2.kemu
and t1.classid = '202202'
and t1.grade > maxgrade
;
```
### 4.3 性能优化(sql重写)
当前有三个表，分别是学生信息表student(sid,sname,cno)和202201班级成绩表score1(sid,course,score)和202202班级成绩表score2(sid,course,score)。
#### 4.3.1 查202201班级和202202班级所有人语文成绩前10的记录，第一个查询要使用union
```sql
(select * from score1 where coures='语文' order by score desc limit 10)
union
(select * from score2 where coures='语文' order by score desc limit 10)
;
```
#### 4.3.2 对以上sql语句进行优化
```sql
(select * from score1 where coures='语文' order by score desc limit 10)
union all
(select * from score2 where coures='语文' order by score desc limit 10)
;
```
#### 4.3.3 查看两个班级相同的科目，202201班在202202班中不存在的成绩，要求使用not in
```sql
select * from score1 s1 where score not in (select score from score2 where course=s1.course );
```
#### 4.3.4 对以上sql语句进行优化
```sql
select * from score1 s1 where not exists (select score from score2 where course=s1.course and score=s1.score);
```
#### 4.3.5 查询班级202201语文成绩最高的学生，要求先创建索引，并且能保证一定会使用索引
```sql
--收集统计信息
analyze score1
--建议索引
explain analyze select * from pg_index_advise('select s.sname,max(s1.score) from score1 s1, student s where s1.sid=s.sid and s1.course=''语文'' group by s.sname');
--创建索引
create index idx_score on score1(score,course);
--查询
select /*+ indexscan(score1 idx_score) */ s.sname,max(s1.score) from score1 s1, student s where s1.sid=s.sid and s1.course=''语文'' group by s.sname;
```
#### 4.3.6 查询202201班级的学生的成绩比202202班级的学生最高成绩还要大的学生信息，对以下给出的sql进行改写
sql: select stu.sid,stu.sname,sum(score) sumscore from student stu,score1 s1 where stu.sid=s1.sid group by stu.sid,stu.sname having sumscore >(select max(score) from (select sum(score) from score2 group by sid));
```sql
select b.sid,a.sname,b.sumscore
from student a,
(select sid,sum(score) as sumscore from score1 group by sid) b,
(select max(sumscore) maxscore from (select sum(score) as sumscore FROM score2 group by sid)) c
where a.sid = b.sid
and b.sumscore > c.maxscore;
```
### 4.4 性能调优(sql重写)
当前有三个表，分别是学生信息表student(id,name,sex,class)和202201班级成绩表score1(id,course,score)，202202班级成绩表score2结构与score1相同
#### 4.4.1 用union查询输出student所有列，score1和2的course,score列，按照id升序，成绩降序
```sql
(select s.*,s1.course,s1.score from student s,score1 s1 where s.id=s1.id)
union
(select s.*,s2.course,s2.score from student s,score2 s2 where s.id=s2.id)
order by id,score desc;
```
#### 4.4.2 对以上sql语句进行优化
```sql
(select s.*,s1.course,s1.score from student s,score1 s1 where s.id=s1.id)
union all
(select s.*,s2.course,s2.score from student s,score2 s2 where s.id=s2.id)
order by id,score desc;
```
#### 4.4.3 查看两个班级相同的科目，score1在score2中不存在的成绩，要求使用not in
```sql
select course,score from score1 s1 where s1.score not in (select score from score2 s2 where s2.course=s1.course);
```
#### 4.4.4 对以上sql语句进行优化
```sql
select score from score1 s1 where not exists (select score from score2 s2 where s2.course=s1.course and s2.score=s1.score);
```
### 4.5 性能优化(子查询优化)
一个学生成绩查询系统，其中包含四张表：班级信息表class、学生信息表student、课程信息表course、成绩表sc。
```sql
DROP TABLE IF EXISTS class;
CREATE TABLE class
(
classno varchar(20) PRIMARY KEY,
classname varchar(20) NOT NULL,
gno varchar(20) NOT NULL
);
DROP TABLE IF EXISTS student;
CREATE TABLE student
(
sno varchar(20) PRIMARY KEY,
sname varchar(30) NOT NULL,
sex varchar(5) NOT NULL,
age integer NOT NULL,
nation varchar(20) NOT NULL,
classno varchar(20) NOT NULL
);
DROP TABLE IF EXISTS course;
CREATE TABLE course
(
cno varchar(20) PRIMARY KEY,
cname varchar(30) NOT NULL,
credit integer NOT NULL,
priorcourse varchar(20) NOT NULL
);
DROP TABLE IF EXISTS sc;
CREATE TABLE sc
(
sno varchar(20) NOT NULL,
cno varchar(20) NOT NULL,
score integer NOT NULL
);
-- 插入数据
insert into class values('H1C1','高一一班','2020');
insert into class values('H1C2','高一二班','2020');
insert into class values('H1C3','高一三班','2020');
insert into student values('s0031101', 'Jack', 'm', 15, 'China', 'H1C1');
insert into student values('s0032101', 'Peter', 'm', 16, 'China', 'H1C2');
insert into student values('s0033101', 'Jane', 'f', 15, 'China', 'H1C3');
insert into student values('s0031102','Mary', 'f', 15, 'China', 'H1C1');
insert into student values('s0032102','Tom', 'm', 16, 'China','H1C2');
insert into student values('s0033102','Lily', 'f', 15, 'China', 'H1C3');
insert into student values('s0031103', 'Chris','m', 15, 'China','H1C1');
insert into student values('s0032103', 'Bruce', 'm', 16, 'China', 'H1C2');
insert into student values('s0033103', 'Jason', 'm', 15, 'China', 'H1C3');
insert into course values('s101','数学', 5, 'null');
insert into course values('s102','语文',5, 'null');
insert into course values('s103','英语',5, 'null');
insert into sc values('s0031101','s101', 59);
insert into sc values('s0032101','s101',71);
insert into sc values('s0033101','s101',86);
insert into sc values('s0031102','s101',93);
insert into sc values('s0032102','s101',89);
insert into sc values('s0033102','s101',95);
insert into sc values('s0031103','s101',88);
insert into sc values('s0032103','s101',75);
insert into sc values('s0033103','s101',80);
insert into sc values('s0031101','s102',89);
insert into sc values('s0032101','s102',75);
insert into sc values('s0033101','s102',83);
insert into sc values('s0031102','s102',91);
insert into sc values('s0032102','s102',95);
insert into sc values('s0033102','s102',92);
insert into sc values('s0031103','s102',81);
insert into sc values('s0032103','s102',65);
insert into sc values('s0033103','s102',70);
insert into sc values('s0031101','s103',99);
insert into sc values('s0032101','s103',73);
insert into sc values('s0033101','s103',81);
insert into sc values('s0031102','s103',87);
insert into sc values('s0032102','s103',85);
insert into sc values('s0033102','s103',93);
insert into sc values('s0031103','s103',81);
insert into sc values('s0032103','s103',73);
insert into sc values('s0033103','s103',79);
```
#### 4.5.1 写一个查询，查询2020级各班语文的平均成绩，且只查询平均分在80以上的班级，并将结果按升序排列。显示结果为班级号、班级名、平均成绩。要求where条件包含两个非相关子查询
```sql
select c.classno,c.classname,avg(nvl(b.score,0)) as avgscore
from student a,sc b,class c
where a.sno = b.sno and a.classno = c.classno
and b.cno = (select cno from course where cname='语文')
and c.classno in (select classno from class where gno='2020')
group by c.classno,c.classname
having avgscore > 80
order by 3;
```
#### 4.5.2 写一个查询，在上一题的基础上，对sql语句进行优化。要求将where子句中的非相关子查询提升为from子句中的范围表
```sql
select a.classno,a.classname,avg(nvl(c.score,0)) as avgscore
from class a,course b,
(select aa.sno,aa.classno,bb.cno,bb.score FROM student aa,sc bb where aa.sno = bb.sno) c
where a.classno = c.classno and a.gno = '2020' and c.cno = b.cno
and b.cname='语文'
group by a.classno,a.classname
having avgscore > 80
order by 3
;
```
#### 4.5.3 写一个查询，在上一题(第2题)的基础上，对sql语句进一步优化。要求将from子句中包含的非相关子查询提升为父查询，进一步消减查询中的子查询数量。
```sql
select c.classname,round(avg(s.score),2) avg_score
from sc s,student st,class c,course co
where st.sno = s.sno and st.classno = c.classno and s.cno = co.cno and c.gno='2020' and co.cname='语文'
group by c.classname
having avg_score > 80;
```
### 4.6 性能优化(子查询优化)
基于学生表(sno,sname,cno),班级表(cno,cname),课程表(courid,courname),成绩表(sno,courid,score)完成关联查询
```sql
-- 创建表（考试时不一定有建表和数据插入代码）
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);
```
#### 4.6.1 语文平均成绩大于80的所有成绩，输出班级名，学号(或班级号)，平均成绩，要求使用两where非相关的子查询
```sql
select a.sno,b.cname,avg(nvl(c.score,0)) avgscore
from student a,class b,score c
where a.sno = c.sno and a.cno = b.cno
and c.courid = (select courid from course where courname='语文')
and a.sno in (select sno from (select sno,courid,avg(nvl(score,0)) as avgscore from score where courid = (select courid from course where courname='语文') group by sno,courid) where avgscore > 80)
group by a.sno,b.cname
;
```
#### 4.6.2 在上一题基础上，使用from查询优化
```sql
select t1.sno,t2.cname,t1.avgscore from
(select st.sno,st.cno,avg(s.score) as avgscore from student st,course c,score s
where s.sno=st.sno
and s.courid = c.courid
and c.courname = '语文'
group by st.sno,st.cno
having avgscore > 80) t1,class t2
where t1.cno = t2.cno
;
```
#### 4.6.3 在上一题基础上，使用父查询(消除子查询)
```sql
select t1.sno,t3.cname,avg(t2.score) as avgscore
from student t1,score t2,class t3,course t4
where t1.sno = t2.sno
and t1.cno = t3.cno
and t2.courid = t4.courid
and t4.courname = '语文'
group by t1.sno,t3.cname
having avgscore > 80
;
```
## 5. 论述
### 5.1 论述(权限模型、加密方式)
#### 5.1.1 权限管理模型 RBAC 和 ABAC 区别
```sql
RBAC: 基于角色的访问控制，角色通常是指具有某些共同特征的一组人，例如：部门、地点、资历、级别、工作职责等。在系统初始时Admin根据业务需要创建多个拥有不同权限组合的不同角色，当需要赋予某个用户权限的时候，把用户归到相应角色里即可赋予符合需要的权限。
不同于常用的将用户通过某种方式关联的权限的方式，ABAC则时通过动态计算一个或一组属性来是否满足某种条件来进行授权判断(可以编写简单的逻辑)。属性通常来说分为四类：用户属性(如用户年龄)、环境属性(如当前时间)、操作属性(如读取)和对象属性(如一篇文章，又称资源属性)，所以理论上能够实现非常灵活的权限控制，几乎能满足所有类型的需求。权限判断需要实时执行，规则过多会导致性能问题
两者区别：RBAC和ABAC之间的主要区别在于RBAC基于用户角色提供对资源或信息的访问，而ABAC提供基于用户、环境或资源属性的访问权限。
--我的答案
RBAC:基于角色的权限控制，角色是具有共同特征的一组人。例如：部门、地点、资历、级别、工作职责等。管理员可以根据用户在组织中的角色设置访问权限。
ABAC:基于属性的权限控制，属性可以包括用户属性、环境属性、操作属性和对象属性，ABAC允许更细粒度的访问控制，可以根据多个属性决定是否授予访问权限。
RBAC和ABAC的主要区别在于权限控制的方式，RBAC基于角色，ABAC基于属性，RBAC适用于需求简单、快速设置场景。ABAC设置粒度更细，适合复杂权限场景。
```
#### 5.1.2 数据库数据加密方式有哪些？至少3中
```sql
1. 函数加密：字段级，通过调用函数，如md5()等函数对传入参数进行加密，业务感知加密，不支持密文条件安全，数据在会话中临时解密，数据库无法自动解密，防止高权限账户窃取数据。
2. 透明加密：表级，数据在文件落盘时加密，对用户及上层使用SQL的应用无感知，对于需要加密的表创建时通过TDE参数指定加密算法，数据库无感知，内存明文处理，防止基于物理磁盘的数据窃取，TDE密钥管理分三层，分别是根密钥，主密钥，数据加密密钥。
3. 全密态：字段级，支持密态等值查询，数据库无法解密，防止运维、管理、高权限账户等窃取隐私数据，在业务中仅在DDL层做了扩展，在create table或者alter table新增列时可以将列设置为加密列，给需要加密的列绑定列加密密钥即可，DML操作于其他表一致，但需要以密态方式(-c)创建客户端连接才可以，如果是非密态模式，那么查看到的数据是密文，未指定加密的列数据均已明文处理，密钥管理分三层，分别是根密钥，主密钥，列加密密钥，密钥均存储于GaussDB Client，减少攻击面；
总结来说，函数加密，是用户把密钥给到数据库，数据库在执行函数时做一个加密动作，是在数据库里加密。透明加密是数据库自己找一个密钥，在数据落盘时做加密，是磁盘做加密。全密态是客户端找到密钥之后先把数据加密，在交给数据库，全生命周期都是密文的。
```
### 5.2 论述(存储过程)
#### 5.2.1 使用存储过程的优点(至少4个)
```sql
1.存储过程极大的提高SQL语言的灵活性，可以完成复杂的运算
2.存储过程可以封装，隐藏复杂的商业逻辑
3.一次编写，反复调用，从而减少SQL语句的重复编写数量，提高工作效率
4.一次交互，发送更多SQL，减少了客户端服务器端交互次数
5.对于多次运行的复杂SQL,存储过程支持缓存SQL计划，减少了生成计划的时间
```
#### 5.2.2 存储过程和函数的区别(3个)
```sql
1.存储过程不能使用return返回数据，return未void，并且不允许更改，函数可以使用return返回数据，并且必须指定return
2.函数可以做为触发器设置被触发后执行的操作，存储过程不可以
3.函数支持对同名函数重写，支持基于增加参数数量或者修改现有参数类型下的同名重写，但不支持函数名相同的情况下只修改参数名的同名重写，存储过程不支持重写
--帮助文档路径，感觉比提供的答案要好
文档首页/ 云数据库 GaussDB/ 开发指南（分布式_V2.0-8.x）/ FAQ/ 函数和存储过程的区别是什么？
```
#### 5.2.3 存储过程和匿名块的区别(2个)
```sql
1.存储过程是经过编译并存储在数据库中的，可以重复使用，而匿名块并未存储在数据库中，从应用程序缓存区擦除后，除非应用重新输入代码，否则无法重新执行
2.匿名块无需命名，存储过程必须申明名字
```
### 5.3 论述(事务)
#### 5.3.1 什么是数据库事务？介绍GaussDB数据库事务管理的实现
```sql
事务是作为单个逻辑单元执行的一系列操作，这些操作作为一个整体一起向系统提交要么都执行，要么都不执行。事务是一个不可分割的工作逻辑单元，GaussDB数据库事务支持ACID特性
GaussDB基于MVCC(多版本并发控制)并结合两阶段锁的方式进行事务管理，其特点是读写之间不阻塞。SELECT是纯读操作，UPDATE和DELETE是读写操作，在准备阶段就会把提交操作所需的信息和资源全部写入磁盘，进行持久化，提交阶段根据之前准备好的提交信息和资源，故障或执行失败发生在准备阶段和提交阶段之间时，事务的最终状态为回滚
GaussDB基于GTM组件协助管理分布式事务，采用多版本并发控制MVCC机制。GaussDB提供了不同的GTM模式，GTM-Lite，GTM-Free。在GTM-Lite模式下，中心事务处理节点的压力得到减轻，事务处理流程进一步优化，GTM的性能和并发瓶颈得到减轻，可实现强一致性；在GTM-Free模式下，中心事务管理节点不在参与事务管理，消除了GTM单点瓶颈，可达到更高的事务处理性能，不支持强一致性，仅支持最终一致性。
```
#### 5.3.2 GaussDB数据库有哪些事务隔离级别，并说明含义
```sql
READ COMMITTED: 读已提交隔离级别，事务只能读到已提交的数据而不会都熬未提交的数据，这是缺省值。在读已提交模式里，每个新的命令都是从一个新的快照开始的，而这个快照包含所有到该时刻为止已提交的事务，因此同一事务中后面的命令将看到任何已提交的其它事务的效果。这里关心的问题是在单个命令里是否看到数据库里完全一致的视图。
REPEATABLE READ: 事务可重复读隔离级别，事务只能读到事务开始之前已提交的数据，不能读到未提交的数据以及事务执行期间其它并发事务提交的修改(但是，查询能查看到自身所在事务中先前更新的执行结果，即使先前更新尚未提交)。可重复读事务中的查询看到的是事务开始时的快照，不是该事务内部当前查询开始时的快照，就是说，单个事务内部的select命令总是查看到同样的数据，查看不到自身事务开始之后其他并发事务修改后提交的数据。
SERIALIZABLE: GaussDB Kernel目前功能上不支持此隔离级别，等价于REPEATABLE READ
```
#### 5.3.3 输出命令，启动事务，事务隔离级别为读已提交，只读模式
```sql
--开启事务
start transaction;
--设置事务隔离级别
set local transaction isolation level read committed read only;
--结束事务
commit;
```
### 5.4 论述(备份---新考法)
#### 5.4.1 增量备份和全量备份的区别
```sql
增量备份：基于上一次备份(可以是全备也可以是增量备份)，备份上一次备份到当前备份时间点变化的数据，对于未变化的数据不用备份。由于只备份变化数据，理论来说相比较全备速度会快一些。恢复的时候需要依赖全备
全量备份：备份当前时间点的所有数据，由于是所有数据，理论来说速度会慢一些。恢复的时候不依赖别的备份。
两者的区别：增量备份只备份变化数据，恢复的时候有依赖。全量备份是备份全部数据，恢复时没有依赖。
```
#### 5.4.2 增量备份可以分为两种：差分增量备份与累计增量备份
##### 5.4.2.1 这两种增量备份分别基于什么来做的备份
```sql
差分增量备份：基于上一次备份(全备或者增量备份都可以)，备份上一次备份到当前备份时间点的变化数据
累计增量备份：基于上一次全备，备份上一次全备到备份时间点的变化数据
```
##### 5.4.2.2 全量备份、差分增量备份、累积增量备份，三者备份文件占用空间的大小顺序是什么样的？
```sql
三种备份文件占用空间的大小顺序为: 全量备份>累计增量备份>差分增量备份
```
#### 5.4.3 数据可以恢复到指定时间点，使用什么技术实现，与物理文件备份相比，这种依赖哪个关键文件。
```sql
将数据恢复到指定时间点需要基于PITR技术实现，主要需要依赖于全量备份文件、增量备份文件和WAL日志。
恢复时先根据指定时间点找到最近一次全量备份进行恢复，然后逐个恢复这次全量后的增量备份，直到恢复到时间点前最后一次增量备份，从最后一次增量备份到时间点这段时间的数据通过WAL日志进行恢复。
```
【声明】本内容来自华为云开发者社区博主，不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息，否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）