sql语法DEMO9

当前有一张表 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 ;
/