jiang DEMO2

1、数据库对象管理及 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)查看每门成绩是否大于每门平均成绩
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;

(2)编写函数，获取成绩绩点，输入学生 id 和科目名称输出对应的绩点值,
0-59 为 0,60-69 为 0.1,70-79 为 0.2,80-89 为 0.3,90-100 为 0.4
create or replace function fun_point(id1 int,coursename varchar(20)) returns
float as
$$
declare point float ;
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 stu where id=id1;
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 stu where id=id1;
when coursename='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 end ) into point from stu where id=id1;
end case;
return point ;
end;
$$language plpgsql;

create or replace function fun_point() RETURNS
float as  

(3)id 含'3'的同学，求总的绩点，返回绩点最大的 ID 和总绩点
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 totalpoint
from stu where id like '%3%';
(4) 求总绩点，返回绩点最大的 ID 和总绩点
select id,maxpoint from (select id, max((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 totalpoint from stu group by id) as t1, (select max((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 maxpoint from stu ) t2
where t1.totalpoint=t2.maxpoint;
(5) 按照总绩点排名输出
select id ,total_jidian,dense_rank() over(order by total_jidian desc) from (
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)as "mathjidian"
,(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.4else 0 end)as "artjidian"
,(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 "phyjidian"
,mathjidian+artjidian+phyjidian as total_jidian from stu)as t1;
(6) 编写 add_mask(id1,id2)函数，当 id1 是当前查询用户时，显示正常 id,如
果不是则显示为 id2
create or replace function add_mask(id1 varchar(20),id2 varchar(20))
returns varchar(20) as
$$
begin
if id1=current_user then
return idx1;
else
return idx2;
end if;
end;
$$language plpgsql;
2、用户权限管理
(1)创建用户 user1
create user user1 password "test@123";
(2)查看用户 user1 和数据库的相关权限，要求显示数据库名、用户名、数据库的权限
select datname,(aclexplode(datacl)).grantee as grantee, (aclexplode(datacl)).privilege_type as pri_type from pg_database
where datname not like '%template%';
(3)把表 table1 的 select 和 alter 权限赋给 user1
Grant select,alter on table1 to user1;
(4)查询表 table1 的 owner，要求显示表名和 owner
select tablename,tableowner from pg_tables where tablename='table1';
(5) 查询 user1 的表权限，要求显示表名、schema 名、用户名、相关表权限
select grantee,table_name,table_schema,privilege_type from
information_schema.table_privileges where grantee='user1';
(6)查询对表 table1 有操作权限的用户，要求显示 2 列:用户名、操作权限
select grantee as user,privilege_type from information_schema.table_privileges
where table_name='table1';
3、数据库连接
(1)查看全局最大连接数
show max_connections ;
(2)创建数据库指定最大连接数，指定最大连接数为 100000，并使用 SQL 查看展
示数据库名称，最大连接数。
创建数据库
create database testdb connection limit 100000; --查看数据库、最大连接数
select datname,datconnlimit from pg_database where datname='testdb';
(3)创建用户并指定最大连接数，指定最大连接数为 20000，并使用 SQL 查看展
示用户名称，最大连接数
--创建用户
create user test password "test@123" connection limit 20000; --查看用户名、最大连接数
select rolname,rolconnlimit from pg_roles where rolname='test';
(4)修改数据库的最大连接数，将最大连接数修改为 200000
Alter database testdb connection limit 200000;
4、动态脱敏
(1) 创建 dev_mask 和 bob_mask 用户
create user dev_mask password "test@123";
create user bob_mask password "test@123";
(2) 创建表 tb_for_masking ,字段信息包括(col text,col2 text,col3 text)
create table tb_for_masking (col1 text,col2 text,col3 text);
(3)为 col1 设置脱敏策略，使用 maskall 对 col1 列进行数据脱敏
--创建资源标签
create resource label lb01 add column(tb_for_masking.col1); --为 col1 列指定脱敏策略
create masking policy maskpol1 maskall on label(lb01);
(4) 为 maskpol1 脱 敏 策 略 添 加 描 述 信 息 "masking policy for
tb_for_masking.col1"
alter masking policy maskpol1 comments 'masking policy for tb_for_masking.col1';
(5) 为 maskpol1 脱敏策略在原基础上新增 col2 列做随机脱敏，脱敏函数使用
randommasking
--创建资源标签
create resource label lb02 add column(tb_for_masking.col2); --为 col1 列指定脱敏策略
alter masking policy maskpol1 add randommasking on label(lb02);
(6)修改 maskpol1 移除 col2 列上的 randommasking 脱敏方式
alter masking policy maskpol1 remove randommasking on label(lb02);
(7)修改 maskpol1 修改一项脱敏方式，将在 col1 列上的 maskall 脱敏方式修改
为 randommasking 脱敏
alter masking policy maskpol1 modify randommasking on label(lb01);
(8)修改脱敏策略 maskpol1 使之仅对用户 dev_maskh 和 bob_mask，客户端工具
为 psql 和 gsql,ip 地址为'10.20.30.40','127.0.0.0/24'场景生效
 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'));
(9)修改脱敏策略 maskpol1，使之对所有用户场景生效
alter masking policy maskpol1 drop filter;
(10) 禁用脱敏策略
Alter masking policy maskpol1 disable;
5、触发器
--学生表
create table student(sno integer,sname varchar(50),ssex varchar(5), sage int); --课程表
create table course(cno integer ,cname varchar(50),credit integer);
选课表
create table elective(sno integer,cno integer,grade integer);
(1) 创建试图 SELECT_SD,查询学生成绩信息，查询学生姓名，课程名称，课程成绩
create view SELECT_SD as select sname,cname,grade from student s, corese c,elective e where e.sno=s.sno and e.cno=c.cno;
(2) 编写函数 FUNC_SUM,返回某个学生的分数总和
create or replace function FUNC_SUM(stuid int) returns integer as
$$
declare result integer;
begin
select sum(grade) into result from elective where sno=stuid;
return result;
end;
$$language plpgsql;
(3)创建触发器 DELETE_ELE,在 student 表上绑定触发器 DELETE_ELE,在删除
表中某个学生时,将 ELECTIVE 表中该学生的选课记录一并删除;
--删除 elective 表记录的函数
create or replace function func_01() returns trigger as
$$
begin
delete from elective where sno =old.sno;
return old;
end;
$$language plpgsql; --创建触发器 DELETE_ELE
create trigger DELETE_ELE before delete on student for
each row execute procedure func_01();
6、存储过程
基于以下信息表，完成以下实验要求
--创建表
create table student(id serial,starttime timestamp(0));
(1)编写存储过程，生成记录，输入个数，生成 student, id 从 100000 开始，
starttime 为当前时间
create or replace procedure create_stu(num int)
as
declare i int;
declare id int;
begin
id:=100000;
for i in 1 .. num loop
insert into student values(id,sysdate);
id:=id+1;
DBE_OUTPUT.PRINT_LINE('id='||id);
end loop;
end;
/
(2)调用存储过程，生成 90000 条记录
Call create_stu(90000);
(3)查看表记录数
Select count(*) from student;
7、数据库优化
通常的 SQL 优化会通过参数调优的方式进行调整，例如如下参数
set enable_fast_query_shipping=off;
set enable_stream_operator =on;
请根据以下表完成数据库优化
--创建表
create table tb_user(stu_no int ,stu_name varchar(20),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;
(1) 收集表 tb_user 的统计信息
 Analyze tb_user;
(2)为以下 SQL 语句创建索引使执行计划和索引最合理
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=20;
--SQL1
select * from gs_index_advise('select * from tb_user where age=29 and stu_name=''xiaoming''');
Create index idx01 on tb_user( stu_name,age ); --SQL2
select * from gs_index_advise('select * from tb_user where stu_no=100 and age=20');
Create index idx02 on tb_user(stu_no,age);
(2) 在上题操作的基础上，用 3 种不同方式使如下 SQL 不走索引
select * from tb_user where stu_no=100 and age=20;
--方法 1 使用 hint 强制不走索引
explain analyze select * /*+ tablescan(tb_user)*/ from tb_user where stu_no=100 and
age=20; --方法 2 调大 index 开销
Set cpu_index_tuple_cost=1000000; --方法 3 禁用索引
alter index idx02 unusable;
8、论述
(1)使用存储过程的优点(至少 4 个）
1、存储过程极大的提高 SQL 语言和灵活性，可以完成复杂的运算
2、可以保障数据的安全性和完整性
3、极大的改善 SQL 语句的性能，在运行存储过程之前，数据库已对其进行语法
和句法分析，并给出优化执行优化方案。这种已经编译好的过程极大地改善
了 SQL 语句性能。
4、可以降低网络的通信量，客户端通过调用存储过程只需要存储过程名和相关
参数即可，与传输 SQL 语句相比自然数据量少很多。
(2)存储过程和函数的区别（3 个）
1、存储过程不能使用 return 返回数据，return 为 void，并且不允许更改函数可以
使用 return 返回数据，并且必须指定 return
2、函数可以用于为触发器设置被触发后执行的操作，存储过程不可以
3、函数支持对同名函数重写，增加参数数量或者修改现有参数类型可以创建，但
不可以函数名相同的情况下只修改参数名,存储过程不支持重写
(3)存储过程和匿名块的区别(2 个)
1、存储过程是经过预编译并存储在数据库中的，可以重复使用；而匿名块是未存
储在数据库中，从应用程序缓存区擦除后，除非应用重新输入代码，否则无法重新
执行
2、你命块无需命名，存储过程必须声明名字