gaussdb笔记2

一

1.查询学号为130的学生名字、总成绩以及所在班级。

select s.sname,s.score,c.cname from student s,classes c where s.sno=130 and s.cno=c.cno;

2.查看每个班级（cno）月考总分前三名，其中要求分数相同的人具有相同的编号（且排名不中断）。

select * from (select *,dense_rank() over(partition by cno order by score desc) as position  from student) where position <=3;

3.按要求创建p_table(a int,b int,c int,d int)表，指定以b字段作为分区键，按10以下，10-20、20-30、30-40分区，以a字段作为分布键的列存表。

create table p_table(a int,b int,c int,d int) with(orientation=column)

distribute by hash(a) partition by range(b)(

partition p1 values less than (10),

partition p2 values less than (20),

partirion p3 values less than (30),

partition p4 values less than (40)

);

4.查询表的模式名和表名。

select schemaname,tablename from pg_tables where tablename='p_table';

5.查询表所在的节点nodeoids信息。

select t2.nodeoids from pg_class t1,pgxc_class t2,pg_namespace t3 where t1.oid=t2.pcrelid and t1.relnamespace=t3.oid

and t1.relname='p_table' and t3.nspname='root';

6.查询表所在的实例信息

select t4.* from pg_class t1,pgxc_class t2,pg_namespace t3,pgxc_node t4 where t1.oid=t2.pcrelid and t1.relnamespace=t3.oid

and t4.oid=any(t2.nodeoids) and t1.relname='p_table' and t3.nspname='root';

7.将表sjh_test的读取、删除权限授权给sjh111用户。

grant usage on schema root to sjh111;

grant select,delete on sjh_test to sjh111;

8.创建角色sjh_audit，此角色拥有审计权限。

create role sjh_audit with auditadmin password 'test@123';

9.用3种不同方式使如下sql不走索引。

explain analyze select * /*+ tablescan(tb_user) */ from tb_user where stu_no=100 and age=20;

set cpu_index_tuple_cost=1000000;

alter index idx02 unusable;

二

1.编写函数，获取成绩绩点，输入学生id和科目名称，输出对应的绩点值。

create or replace function func01(iid int,coursename varchar(20)) returns numeric as $$

declare result numeric;

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 result from stu where id = iid;  (如果有多条记录，题目没明确取平均值还是什么的时候加limit 1）

             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 result from stu where id=iid;

             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 result from stu where id=iid;

         end case;

return result;

end;

$$language plpgsql;

2.id包含'3'的同学，求总的绩点，返回绩点最大的ID和总绩点。

---求包含3同学的总绩点

with gpawith3 as (

select id,(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 where cast(id as varchar) like('%3%');

)

select id,totalpoint from gpawith3 where totalpoint=(select max(totalpoint) from gpawith3);

3.求总绩点，返回绩点最大的id和总绩点。

with gpa as (

select id,(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 

)

select id,totalpoint from gpa where totalpoint=(select max(totalpoint) from gpa);

4.按照总绩点排名输出。

with gpa as (

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

                (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 

)

select *,dense_rank() over(order by totalpoint desc) as ranks from gpa;

5.编写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 id1;

     else

         return id2;

     end if;

end;

$$language plpgsql;

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

select t1.datname,t2.rolname,t1.pri_type from

(select datname,(aclexplode(datacl)).grantee as grantee,(aclexplode(datacl)).privilege_type as pri_type from pg_databasewhere datname not like '%template%') t1,

pg_roles t2

where (t1.grantee=t2.oid or t1.grantee =0) and t2.rolname='user1';

7.查询user1的表权限，要求显示表名、schema名、用户名、相关表权限。

select grantee,table_name,table_schema,privilege_type from information_schema.table_privileges where grantee='user1';

8.查询对表table1有操作权限的用户，要求显示2列:用户名、操作权限。

select grantee as user,privilege_type from infomation_schema.table_privileges where table_name='table1';

9.编写存储过程，生成记录，输入个数，生成student，id从100000开始，starttime为当前时间。

create or replace procedure proc1(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;

end loop;

end;

三

1.查询姓名和姓氏，以姓名.姓氏的格式输出，要求首字母大写，姓名和姓氏之间用“.”连接。

select initcap(firstname||'.'||familyname) from su;

2.查看过去一天所有产生审计日志的总数，当前时间要求用now()。

select count(*) from pg_query_audit(now()-1,now());

3.查看过去一天user1用户登录postgres数据库，当前时间要求使用now()。

select * from pg_query_audit(now()-1,now()) where type='login_success' and database='postgres' and username='user1';

4.删除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');

5.查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 from test t2 where t2.classid='202201') t3 on t1.grade <t3.min_grade where t1.classid='202202';

四

1.给用户授予查看审计权限，同时可以创建审计策略。

alter user user1 auditadmin poladmin;

2.对math和pysical排名前十的学生，art加5分，求所有学生的总成绩。

with mpranks as(

select *,(math+pisical) as mpscore,dense_rank() over(order by (math+pysical) desc) as mprank from student

),

addart2 as (

select *,(case when mprank <=10 then 5 else 0 end) as art2 from mpranks

)

select stu_id,sum(math+pysical+art+music+art2) as totalscore from addart2 group by stu_id;

3.获取art和music排名前十，同时math和pysical在art和music前十名的学生信息。

with amranks as(

select *,(art+music) as amscore ,dense_rank() over(order by amscore desc) as amrank from student

),

mpranks as(

select *,(math+pysical) as mpscore,dense_rank() over(order by mpscore desc) as mprank from student

),

top10 as (

select t1.stu_id,t1.math,t1.pysical,t1.art,t1.music from amranks t1 where t1.amranks<=10 

union

select t2.stu_id,t2.math,t2.pysical,t2.art,t2.music from mpranks t2 where t2.mpranks<=10 

)

select * from top10;

如果是总成绩前十：

--每个学生sum(math+pysical)之和是前10的学生，同时 每个学生sum(art+music)之和是前10的学生

SELECT distinct s.*

FROM student s

WHERE s.student_id IN

(

       SELECT a.student_id FROM

       (

              SELECT DISTINCT student_id FROM

              (

                     SELECT student_id,

                               sum(NVL(,0)+NVL(s.music,0)) AS sum_am,

                               DENSE_RANK() OVER(ORDER BY sum_am DESC ) AS rank 

                     FROM student s GROUP BY s.student_id

              ) am_id WHERE am_id.rank<=10

       ) a inner JOIN

       (

              SELECT DISTINCT student_id FROM

              (

                     SELECT student_id,

                               sum(NVL(s.math,0)+NVL(s.pysical,0)) AS sum_mp,

                               DENSE_RANK() OVER(ORDER BY sum_mp DESC ) AS rank 

                     FROM student s GROUP BY s.student_id

              ) mp_id WHERE mp_id.rank<=10

       ) b ON a.student_id=b.student_id

);

4.查看两个班级相同的科目，202201班在score2中不存在的成绩，要求使用not in。

单个科目：select chinese from score1 where  chinese not in (select chinese from score2);

所有科目：select * from score1 t1 where t1.score not in ( select score from score2 where t1.kemu=t2.kemu );

优化上述语句：

单个科目：select chinese from score1 t1 where not exists (select chinese from score2 t2 where t2.chinese=t1.chinese);

所有科目：select * from score1 t1 where not exists(select score from score2 where t2.kemu=t1.kemu and t2.score=t1.score);

五

1.age_group按每组人数多少排序，查询age_group、人数、最大年龄、最小年龄、平均年龄（向下取整）。

select age_group,count(*) as "人数",max(age) as "最大年龄",min(age) as "最小年龄",floor(avg(age))as "平均年龄" from (

select *,case when age<18 then 'group1'

when age<35 then 'group2'

when age<55 then 'group3'

else 'group4' end as age_group from residents

) t1

group by age_group

order by "人数" desc;

2.查询postgres数据库中用户已使用的会话数量。

select count(datname) from pg_stat_activity where datname='postgres';

3.查询所有用户已使用的会话连接数。

select count(*) from dv_sessions;

4.查询库最大连接数。

show max_connections;

5.创建tri_update_d触发器，如果修改number_of_senior 字段时提示“不能随便修改部门教授职称人数”，如果已经有tri_update_d触发器，则删除后重新创建。

drop trigger if exists tri_update_d;

create or replace function func01() returns trigger as $$

begin

raise exception '不能随便修改部门教授职称人数不能随便修改部门教授职称人数';

return null;

end

$$language plpgsql;

create trigger tri_update_d before update of number_of_senior on department for each row execute procedure func01();

七

1.查询月考平均成绩比学生编号5的大的学生。

select id,round(avg(nvl(score,0)),2) as avgscore from student group by id having avgscore >(select round(avg(nvl(score,0)),2) from student where id=5);

2.查询每次月考成绩大于平均成绩的学生。

---先查月平均成绩，再比较

select s1.* from student join (select month,round(avg(nvl(score,0)),2) as avgscore from student group by month) s2 on s1.month=s2.month where s1.score >s2.avgscore;

3.查询每次平均成绩差值（当前月减去上月，窗口函数lag向前，lead向后）。

select month,round(avg(nvl(score,0)),2) as avgscore, avgscore-lag(avgscore) over(order by month) as diff from student group by month order by month;

4.加一个行级访问控制u1只能查看自己且年龄30以上。

---先将原来的行级访问控制中的u1去掉，再创建

alter row level security policy rls02 on t_user to u2 using(id=current_user);

create row level security policy rls03 on t_user to u1 using(id=current_user and age>30);

5.要求输入c1返回该课程的成绩信息（课程和id本身带有单引号）。

create or replace procedure proc1(icid inout varchar) as

declare ccid varchar;

begin

ccid:=concat('''',icid,'''');

select score into icid from score where cid=icid;

end;

6.用union查询输出student所有列，score1和score2的course，score列，按照id升序，成绩降序。

(select t1.*,t2.course,t2.score from student t1 join score1 t2 on t1.id=t2.id)

union

(select t3.*,t4.course,t4.score from student t3 join score2 on t3.id=t4.id)

order by id,score desc;

八

1.输出每月月考总分都比学号为5的同学分数高的所有学生信息。

---先查5号每月的成绩，再比较

select a.*

from student a,

(select month,nvl(score,0) as fscore from student where sno=5) b

where a.month=b.month and nvl(a.score,0) > b.fscore and a.sno <>5;

2.输出每次月考缺考的学生信息，要求打印姓名、班级编号和缺考次数。

select sname,cno,count(1) from student where score is null group by sname,cno;

3.输出每次月考都和tom同时缺考的所有学生信息，要求打印学号、姓名和月考总分。

---先找tom缺考的月份，然后再找相同月份缺考的非tom学生，然后再算总分（sno in）

select sno,sname,sum(nvl(score,0)) as total_score

from student

where sno in

(

select a.sno from student a

       inner join (select sno,month from student where sname='Tom' and score is null) b  

       on a.month=b.month and a.sno <> b.sno

       where a.score is null

       )

group by sno,sname;

4.输出全校月考中位数分数。

第一种：select month,round(percentile_count(0.5) with in group (order by nvl(score,0)),2) as median_score from student

group by month order by month;

第二种：先按分数排序并给一个行号，然后再通过行号向上和向下取整得到中位数的位置，在计算

with rankscore as (

select nvl(score,0) as score,row_number() over(order by score) as rn,

count(*) over() as totalnum from student

),

median_values as(

select score,rn,totalnum from rankscore where rn in (floor((totalnum+1)/2),ceil((totalnum+1)/2))

)

select avg(score) from median_values;

5.统计每个班每次月考的最高分数，要求打印班级名称、考试时间和月考分数。

select cname,month,max(score) from student,class where student.cno=class.cno group by cname,month order by cname,month;

6.输出class1班级中比class2班级每月月考最低分还低的学生信息，要求打印学号、姓名和月考总分。

select t1.sno,t1.name,t1.score from (

select * from student where cno=(select cno from class where cname='class1')

) t1 -----先查class1的学生信息

join

(select month,min(nvl(score,0))) as minscore from student where cno=(select cno from class where cname='class2') group by month ----再查class的月考最低分集

t2

on t1.month=t2.month and nvl(t1.score,0)<t2.minscore;

7.打印月考总分平均分最高的学生信息，输出学号、姓名和月考总分平均分。

---先查月考平均分最高分，然后再通过最高平均分比较，等于最高平均分的学生

select sno,sname,round(avg(nvl(score,0)),2) as avgscore from student group by sno,sname having(

avgscore =select max(score) from (select round(avg(nvl(score,0)),2) from student group by sno,sname)

);

8.输出每个学生月考平均分和最高月考平均分之间的分数差距，打印学号、姓名、月考平均分和差距分数。

----先算最高平均分，然后再算差距

select sno,sname,round(avg(nvl(score,0)),2) as avgscore ,(select max(avgscore) from

(select round(avg(nvl(score,0)),2) as avgscore from student group by sno ))-avgscore from student group by sno,sname;

9.语文平均成绩打大于80的所有成绩，输出班级名、学号（或班级号）、平均成绩，要求where条件里有两个非相关子查询。

----in where

select t1.sno,t3.cname,avg(score) from score t1,student t2,class t3

where t1.sno=t2.sno and t2.cno=t3.cno and

t1.sno in (select sno from score where courid in (select courid from course where courname='语文') group by sno having avg(score)>80)

and courid in (select courid from course where courname='语文') group by t1.sno,t3.cname;

10.在上题基础上，将where条件中的非相关子查询改为from后边的范围表。

select t1.sno,cname,avg(score) from (

select t3.sno,t3.cno,avg(score)as avgscore from score t1,course t2,student t3 where t1.sno=t3.sno and t1.courid=t2.courid and courname='语文'

group by t3.sno,t3.cno having avgscore >80

) t1,class t2 where t1.cno=t2.cno;

11.在上一题的基础上，将from后面的子查询优化为附表的关联查询。

select t3.cno,t3.cname,avg(score)as avgscore from score t1,student t2,class t3,course t4

where t1.sno=t2.sno and t2.cno=t3.cno and t1.courid=t4.courid and courname='语文'

group by t1.sno,t3.cno,t3.cname having avgscore>80;

九

1.请查看数据在各个节点的分布情况，显示表名、节点名、数据量。

select tablename,nodename,dnsize from table_distribution('root','test');

2.删除主键索引。

---查看主键名字再删除

select * from pg_constraint c1,pg_class c2 where c1.conrelid=c2.oid and c2.relname='test';

alter table test drop constraint test_pkey;

3.对age列添加检查约束，要求只能写入大于18的值。

alter table test add contraint age_check(age>18);