sql语法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,68,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_cal_point(id1 int,coursename varchar(30)) returns decimal(10,1)

as

$$

declare point decimal(10,1);

begin

if 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.2 end)

into point from stu where id=id1;

elsif 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.2 end ) into point from stu where id=id1;

elsif 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.2 end) into point from stu where id=id1;

else raise notice 'please input right course name;';

end if;

return point;

end;

$$ language plpgsql;

 

3、 id含’3’的同学，求总的绩点，返回绩点最大的ID和总绩点

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.2 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.4 else 0.2 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.2 end)as "phyjidian",

mathjidian+artjidian+phyjidian as totaljidian from stu where id like '%3%' order by totaljidian desc limit 1 ;

4、 求总绩点，返回绩点最大的ID和总绩点

select id, (case when math<60   then 0

when math>=60 and math<70  then 0.1

when math>=70 and math<  80  then 0.2

when math>=80 and math<90  then 0.3

when math>=90 and math<=100  then 0.4

else 0 end)+(case when art<60  then 0

when art>=60 and art<70  then 0.1

when art>=70 and art<80  then 0.2

when art>=80 and art<90  then 0.3

when art>=90 and art<=100  then 0.4

else 0 end)+(case when phy<60  then 0

when phy>=60 and phy<70  then 0.1

when phy>=70 and phy<80  then 0.2

when phy>=80 and phy<90  then 0.3

when phy>=90 and phy<=100  then 0.4

else 0 end) as max_jidian from stu  order by max_jidian desc limit 1 ;

5、 按照总绩点输出

select id,total_jidian 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.2 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.4 else 0.2 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.2 end)as "phyjidian",

mathjidian+artjidian+phyjidian as total_jidian from stu order by total_jidian)as t1;

6、 编写add_mask(id1,id2)函数，当id1是当前查询用户时，显示正常ID，如果不是则显示为id2

create or replace function add_mask(id1 varchar(200),id2 varchar(200))

returns varchar(200)

as

$$

begin

if id1=current_user then --current_user 当前登录用户名

return id1;

else

return id2;

end if;

END;

$$ language plpgsql;

2. 用户权限管理

1、 创建用户user1

create user user1 password 'Huawei@123';

2、 查看用户user1和数据库的相关权限，要求显示数据库名、用户名、数据库的权限

select datname,(aclexplode(datacl)).grantee as grantee,(aclexplode(datacl)).privilege_type as pri_t from pg_database where datname not like 'template%';

 

select a.datname,b.rolname,string_agg(a.pri_t,',') from

(select datname

,(aclexplode(COALESCE(datacl, acldefault('d'::"char",datdba)))).grantee as grantee

,(aclexplode(COALESCE(datacl, acldefault('d'::"char", datdba)))).privilege_type as pri_t

from pg_database where datname not like 'template%') a,pg_roles b where (a.grantee=b.oid or a.grantee=0) and b.rolname='shenglu' group by a.datname,b.rolname;

 

SELECT A.datname,

  b.rolname,

  string_agg (A.pri_t, ',')

FROM

  (

    SELECT

      datname,

      aclexplode (datacl).grantee AS grantee,

      aclexplode (datacl).privilege_type AS pri_t

    FROM

      pg_database

    WHERE

      datname NOT LIKE'template%'

  ) A,

  pg_roles b

WHERE

  (A.grantee = b.OID OR A.grantee = 0)

  AND b.rolname = 'user2'

GROUP BY

  A.datname,

  b.rolname;

 

--aclexplode(datacl) 一共有四个属性，分别是grantor（授权人）、grantee（被授权人）、privilege_type（权限类型）、is_grantable（是否拥有该权限）

3、 将表table1的select和alter权限赋给user1；

grant select,alter on tenk1 to user1;

4、 查询table1的owner，要求显示表名和owner

select tablename,tableowner from pg_tables where tablename=’table1’;

5、 查询table1的表权限，要求显示表名、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 is_grantable='t' and table_name='table1';

---如果用户有GRANT OPTION权限，则为yes，否则为no。 这里的is_grantable='t'可以不要

3. 数据库连接

1、 查看全局最大连接数

show max_connections;

2、 创建数据库指定最大连接数，指定最大连接为100000，并使用SQL查看展示数据库名称，最大连接数

--创建数据库，connection limit指定最大连接数

create database test_db connection limit 100000;

--查看数据库、最大连接数

select datname,datconnlimit from pg_database where datname='test_db';

3、 创建用户并指定最大连接数，指定最大连接为20000，并使用SQL查看展示用户名称，最大连接数

--创建用户，connection limit指定最大连接数

create user sjh_max password 'Huawei@123' connection limit 20000;

--对已存在用户设置也可以设置最大连接数的指定

alter user sjh_max connection limit 2000;

--查看用户名、最大连接数

select rolname,rolconnlimit from pg_roles where rolname='sjh_max';

4、 修改数据库的最大连接数，将最大连接数修改为200000

alter database test_db connection limit 200000;

4. 动态脱敏  ---搜索alter masking policy

1、 创建dev_mask和bob_mask用户

create user dev_mask password 'Huawei@123';

create user bob_mask password 'Huawei@123';

 

2、 创建表tb_for_masking，字段信息包括（col1 text,col2 text,col3 text）

create table tb_for_masking(col1 text,col2 text,col3 text);

3、 为col1设置脱敏策略，使用maskall对col1列进行数据脱敏

--使用资源标签标记col1列

create resource label mask_lb1 add column(tb_for_masking.col1);

--为col1列制定脱敏策略

create masking policy maskpol1 maskall on label(mask_lb1);

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 

--使用资源标签标记col2列

create resource label mask_lb2 add column(tb_for_masking.col2);

--修改maskpol1添加脱敏方式

alter masking policy maskpol1 add randommasking on label(mask_lb2);

6、 修改maskpol1移除在col2列上的randommasking脱敏方式

alter masking policy maskpol1 remove randommasking on label(mask_lb2);

7、 修改maskpol1修改一项脱敏方式，将在col1列上的maskall脱敏策略改为randommasking脱敏

alter masking policy maskpol1 modify randommasking on label(mask_lb1);

8、 修改脱敏策略maskpol1使之仅对用户dev_mask和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;

 

11、 ····----产品文档搜索alter masking

 

select * from pg_class where relname like '%masking%' ;

select * from pg_class where relname like '%label%' ;

 

select * from tb_for_masking;

select * from gs_masking;

select * from gs_masking_policy;

 

select * from gs_labels;

select * from gs_policy_label;

 

DROP RESOURCE LABEL mask_lb1

·········

5. 存储过程

基于以下信息表，完成以下实验要求。

--创建表

create table student(id serial,starttime timestamp(0));

1、 编写存储过程，生成记录，输入个数，生成student，id从100000开始，starttime是当前时间；

create or replace procedure create_student_information(num int)

as

declare i int;

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_student_informantion(90000);

3、 查看表记录数

select count(*) from student;