sql语法DEMO3

1. 数据库对象管理及SQL应用1

基于以下学生成绩表，完成以下实验要求。

--创建表

create table su(id int primary key not null,firstname varchar(50) not null,fmailyname 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(firstnaem||'·'||familyname) from su;

或

select initcap(concat(firstname, '·'familyname)) from su;

2、 插入一条新数据（1,’tom’,’jerry’,’tom’,’H’,63），当出现主键冲突时，将mask改为’F’

insert into su values(1,’tom’,’jerry’,’tom’,’H’,63) on duplicate key update mask='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 postition from su;

2. 数据库对象管理及应用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,math+phy as sum_m_p,art+m2 as sum_a_m from student;

2、 根据维度表、安装两种加权算法算出每个学生的加权成绩，展示包含student_id,weitht_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、 根据维度表，安装两种加权算法技术出每个学生的加权成绩，展示包含student_id,weight_sum，单个学生加权成绩要求一行输出

select s.student_id,w1.weight1_sum,w2.weight2_sum from

student s join

(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 on s.student_id=w1.student_id

 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 s.student_id=w2.student_id;

4、 对两种加权总成绩进行排序。要求输出格式student_id,weight_sum、rank1、weight2_sum、rank2

--将第三题作为查询表进行分组排序

select student_id,weight1_sum,dense_rank() over (partition by 1 order by weight1_sum)as rank1,weight2_sum,dense_rank() over (partition by 1 order by weight2_sum)as rank2 from

(select s.student_id,w1.weight1_sum,w2.weight2_sum from student s

join

(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 on s.student_id=w1.student_id

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 s.student_id=w2.student_id);

3. 账本数据库

1、 创建防篡改模式ledgernsp

gs_guc reload -Z coordinator -Z datanode -N all -I all -c "enable_ledger=on";

create schema ledgernsp with blockchain;

2、 创建防篡改用户表usertable

create table ledgernsp.usertable(id int,name text);--with(storage_type=astore)

3、 校验指定防篡改用户表的表级数据hash值与其对应历史表hash一致性

select ledger_hist_check('ledgernsp','usertable');

· ---如果返回TRUE或成功信息，说明usertable的所有历史变更记录完整且未被篡改；

4、 校验指定防篡改用户表对应的历史表hash与全局历史表对应的relhash一致性

select ledger_gchain_check('ledgernsp','usertable');

· ledger_hist_check关注历史记录的存在性和逻辑完整性（是否有缺失或矛盾）；

· ledger_gchain_check关注数据变更的链式哈希关联（是否被篡改，基于密码学验证）。

4. 安全审计，搜索audit

1、 用SQL查看审计是否打开

show audit_enabled;

--也可以基于gs_setting系统视图查看

select name,setting from pg_settings where name='audit_enabled';

2、 用SQL查看日志存储最大空间

select name ,setting from pg_settings where name='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_student(logid serial,starttime timestamp(0) not null,primary key (logid));

1、 编写存储过程，生成记录，传入学生个数，学生LOGID从1000000开始，starttime为当前时间

create procedure create_student_informantion(num int)as

declare id int;

begin

for id in 1000000..(1000000+num-1) loop

insert into aps_student values(id,now());

dbe_output.print_line('id='||id); --|| 用于做字符拼接

end loop;

end;

/

2、 用上一操作初始化90000学生

call create_student_informantion(90000);

3、 查出aps_student表中初始化学生个数

select count(*) from aps_student;

1.  触发器

当前有两张表一张学生表student(id、name)、score(id,math,XX,xx);

1、 创建触发器，删除学生表中记录时，同步删除score中学生的记录

--创建触发器函数

create or replace function tri_delete_func() returns trigger as

$$

declare

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

2. 性能优化

当前有一张test(id、kemu、classID、grade)，里面有8万条数据

1、 查202202班级里面语文最低分是多少，要保障走索引

--创建索引

create index index_kemu on test(kemu);

create index index_classid on test(classid);

--查询202202班级里面语文最低分

select min(grade) from test where kemu='yuwen' and classid=202202;

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);

select * from test t1 join (select min(grade) as min_grade feom test t2 where t2.clasid=202201) t3 on t1.grade<t3.min_grade where t1.classid=202202;

--原SQL中存在子查询，每扫描异常t1表，会遍历子查询结果，性能较差，改成join方式，消除子查询，性能有xxxms提升至xxms。