jiang DEMO3

1、数据库对象管理及 SQL 应用 1
基于以下学生成绩表，完成以下实验要求
--创建表
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)查询姓名和姓氏，以姓名.姓氏的格式输出，要求首字母大写，姓名和姓氏之
间使用“.”拼接
 select initcap(firstname||'.'||familyname) from su;
(2)插入一条新数据(2,'tom','jerry','tom','H',63),当出现主键冲突时，将
mark 修改为'F'

insert into su values(2,'tom','jerry','tom','H',63)on duplicate key update mark='F' (3) 查询表，检查姓名是否是 sec 开头，展示姓名、判断结果 result
 select firstname,(case when firstname like 'sec%' then 't' else 'f' end) as result from su;
(4)查询表中所有列的数据，按照成绩进行排序,并显示名次(position),名次为
连续的。要求展示所有字段，名字字段 position
 select * ,dense_rank() over(order by score desc) as position from su;
2、数据库对象管理及 SQL 应用 2
基于以下学生成绩表和维度表，完成以下实验
--创建表
create table student(student_id int,math int,phy int,art int,m2 int);
create table weight(weight_no int,math numeric,phy numeric,art numeric,m2
numeric); --导入数据
insert into student values(1,80,70,87,90);
insert into student values(2,80,70,87,90);
insert into student values(3,81,80,69,96);
insert into student values(4,89,86,89,93);
insert into student values(5,84,87,97,90);
insert into student values(6,89,79,88,91);
insert into student values(7,83,78,84,92);
insert into student values(8,84,79,89,93);
insert into student values(9,85,76,87,91);
insert into student values(10,90,90,84,93);
insert into weight values(1,0.4,0.3,0.2,0.1);
insert into weight values(2,0.1,0.2,0.3,0.4);
（1）求 math、phy 总成绩以及 art、m2 的总成绩
 select student_id,sum(math+phy) as m_p_sum,sum(art+m2) as a_m_sum
from student group by student_id;
(2) 根据维度表，按照两种加权算法计算初每个学生的加权成绩，展示包含
student_id,weight_sum,当个学生加权成绩可以两行输出
 select s.student_id ,(s.math*w.math+s.phy*w.phy+s.art*w.art+s.m2*w.m2) as
weight_sum from student s,weight w
(3)根据维度表，按照两种加权算法计算出每个学生的加权成绩，展示包含
stdent_id,weight_sum,单个学生加权成绩要求一行输出
 select
w1.student_id,weightl_sum,weight2_sum from
(select s.student_id,(s.math*w.math + s.phy* w.phy + s.art*w.art + s.m2*w.m2) as
weightl_sum from
student s,weight w where w.weight_no=1) w1
join
(select s.student_id,(s.math*w.math + s.phy*w.phy + s.art*w.art + s.m2 + w.m2) as
weight2_sum from
student s,weight w where w.weight_no=2) w2 on
w1.student_id=w2.student_id;
（ 4 ） 对 两 种 加 权 总 成 绩 进 行 排 序 ， 要 求 输 出 格 式
student_id,weight1_sum,rank1,weight2_sum, rank2
 select w1.student_id,weight1_sum,dense_rank() over (partition by 1 order by
weight1_sum desc) as rank1,weight2_sum,dense_rank() over (partition by 1 order by
weight2_sum desc) as rank2 from (select s.student_id, (s.math*w.math + s.phy*w.phy+
s.art*w.art + s.m2*w.m2) as weight1_sum from student s,weight w where
w.weight_no=1) w1
join
(select s.student_id, (s.math*w.math + s.phy*w.phy + s.art*w.art + s.m2*w.m2) as
weight2_sum from student s,weight w where w.weight_no=2) w2 on
w1.student_id=w2.student_id
3、账本数据库
（1）创建防篡改模式 ledgernsp
 Create schema ledgernsp with blockchain;
(2)创建防篡改用户表 usertable
 Create table ledgernsp.usertable(id int);
(3) 校验指定防篡改用户表的表级数据 hash 值与其对应的历史表 hash 一致性
 select ledger_hist_check('ledgernsp','usertable');
（4）校验指定防篡改用户表对应的历史表 hash 与全局历史表对应的 relhash 一致性
 select ledger_gchain_check('ledgernsp','usertable');
4、安全审计
（1）用 SQL 查看审计是否打开
 show audit_enabled
（2）用 SQL 查看日志存储最大空间
 show audit_space_limit;
（3）查看过去一天所有产生审计日志的总数，当前时间要求使用 now()
 select count(*) from pg_query_audit(now()-1 ,now());
（4）查看过去一天 user1 用户登录 postgres 数据库，当前时间要求使用 now()
 select * from pg_query_audit(now()-1,now() ) where type='login_success' and username='user1' and database='postgres';
（5）删除'2020-09-20 00:00:00'到'2020-09-21 23:59:59'时间段的审计记录
 select pg_delete_audit('2020-09-20 00:00:00','2020-09-21 23:59:59')
（6）删除数据库 DB2；级联删除 user1 用户
 Drop database db2;
Drop user user1 cascade;
5、存储过程
基于以下信息表，完成以下实验要求
--成绩表
create table aps_students(
logid serial, starttime timestamp(0) not null, primary key(logid));
(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;
6、触发器
当前有两张表一张学生表 student(id 、name),score (id,math ,XX xx)
（1）创建触发器，删除学生表中记录时，同步删除 score 中学生的记录
 --创建触发器函数
create or replace function TRI_DELETE_FUNC() RETURNS TRIGGER AS
$$
BEGIN
DELETE FROM SCORE WHERE ID=OLD.ID;
RETURN OLD;
END ;
$$LANGUAGE PLPGSQL; --创建触发器
CREATE TRIGGER DELETE_TRIGGER BEFORE DELETE ON STUDENT FOR EACH ROW
EXECUTE PROCEDURE TRI_DELETE_FUNC();
7、性能优化
当前有一张表 test(id ,kemu,classid,grade),里面有 8 万条数据
（1）查看 202202 班级里面语文最低分时多少，要保障走索引
 --表分析
Analyze test; --查看语文最低分
Select min(grade) from test where kemu=’yuwen’ and classid=’202202’ --使用索引推荐
Select * from gs_index_advise(‘Select min(grade) from test where kemu=’yuwen’ and
classid=’’202202’’’); --创建索引
Create index idx01 on test(kemu,classid):
（2）查 202202 班级统一科目成绩比 202201 班级最低分低的同学，根据以下 SQL
优化重写
原 SQL:select * from test t1 where t1.classid='202202' and grade <
(select min(grade) from test t2 where t2.classid='202201');
 
--使用 join on 关联消除子查询中每次查询都需要扫描子表
select * from test t1
join (select min(grade) min_grade from test t2 where t2.classid='202201') t2
on t1.grade<t2.min_grade where t1.classid='202202';