sql语法DEMO4

1、 查看全局最大连接数

  show max_connections;

2、 创建用户并指定最大连接数，指定最大连接数为20000

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

3、 查看用户的连接数，展示用户、最大连接数

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

4、 修改用户最大连接数，将最大连接数修改为1000

alter user sjh_max connection limit 10000;

5、 创建数据库指定最大连接数，指定最大连接数为100000

create database test_db connection limit 100000;

6、 查看数据库最大连接数，展示数据库名称，最大连接数

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

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

alter database test_db connection limit 200000;

1. 安全审计

1、 创建用户user1，密码‘Huawei@123’

create user user1 password 'Huawei@123';

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

alter user user1 auditadmin poladmin;

3、 切换至user1，创建审计策略adt1，对数据库执行create操作创建

--切换用户（DAS在右上角切换）

\c user1

--1.切换到实例管理界面的参数管理模块，修改enable_security_policy为on

--2.创建审计

create audit policy adt1 privileges create;

4、 创建审计策略adt2，数据库执行select操作创建审计策略

create audit policy adt2 privileges select;

5、 修改adt1，对ip地址为’10.20.30.40’进行审计

alter audit policy adt1 modify(filter on ip('10.20.30.40'));

6、 创建表tb1，字段自定义

create table tb1(c1 int,c2 varchar(50));

7、 创建审计策略adt3，仅审计记录用户root，在执行针对表tb1资源进行select、insert、delete操作数据库创建审计策略

--给tb1打上资源标签

create resource label tb1_label add table(‘tb1’);

--创建审计策略

create audit policy adt3 access select on label(tb1_label),insert on label(tb1_label),delete on label'tb1_label) filter on roles('root');

8、 关闭adt1审计策略

alter audit policy adt1 disable;

9、 删除以上创建的审计策略，级联删除用户user1

--删除策略

drop audit policy adt1,adt2,adt3;

--删除资源标签

drop resource label tb1_label;

--级联删除用户

drop user user1 cascade;

2. 存储过程

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

--创建表

create table student(student_id int not null,math int not null,pysical int not null,art int not null,music int not null);

--导入数据

insert into student values(1001,56,84,65,35),(1001,63,46,82,46),(1001,85,65,32,85);

insert into student values(1002,81,86,95,72),(1002,65,46,96,45),(1002,76,54,85,68);

insert into student values(1003,69,85,76,76),(1003,78,68,31,57),(1003,46,95,94,65);

insert into student values(1004,76,95,76,62),(1004,63,96,45,96),(1004,16,58,34,69);

insert into student values(1005,96,63,52,75),(1005,95,86,42,85),(1005,96,45,78,65);

insert into student values(1006,85,68,26,76),(1006,95,76,85,45),(1006,86,95,54,68);

insert into student values(1007,76,58,95,49),(1007,85,65,45,88),(1007,46,85,75,35);

insert into student values(1008,76,85,96,45),(1008,66,22,33,88),(1008,46,89,56,85);

insert into student values(1009,56,78,96,59),(1009,75,86,95,75),(1009,89,65,45,25);

insert into student values(1010,76,86,95,45),(1010,76,95,85,36),(1010,76,82,96,35);

insert into student values(1011,88,99,77,66),(1011,56,85,69,85),(1011,76,85,69,85);

1、 对学习math和pysical排名前十的学生，art加5分，求所有学生总成绩

--先找到排名前十的学生，art+5，然后计算总成绩，第二部分offset 10表示跳过前10条记录从第11条开始，也就是除了排名前10的学生计算他们的总成绩

--union all用来做数据集的并集，union也可以，带all表示不去重，不带all表示去重，在使用union的时候只要保证前面的数据集列数相等，不要求列名相同也能合并

(select student_id,sum(math+pysical+art+5+music) as score from student group by student_id order by score desc limit 10)

union all

(select student_id,sum(math+pysical+art+music) as score from student group by student_id order by score desc offset 10);

--

(select stu.student_id,sum(math+pysical+art+5+music) allscore from student stu,(select student_id,sum(math+pysical)+5 score from student group by student_id order by score desc limit 10)  stu2 where stu.student_id=stu2.student_id group by stu.student_id)

union

(select stu.student_id,sum(math+pysical+art+music) allscore from student stu,(select student_id,sum(math+pysical) score from student group by student_id order by score desc offset 10)  stu2 where stu.student_id=stu2.student_id group by stu.student_id);

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

select s1.*,s2.mscore from (select *,(art+music) ascore from student order by ascore desc limit 10) s1 join (select *,(math+pysical) mscore from student  order by mscore desc limit 10) s2 on s1.student_id=s2.student_id;

3、 编写存储过程，输入学生id返回总成绩

create or replace procedure pro_total_score(id int,totalscore out int)

as

begin

select sum(math+pysical+art+music) into totalscore from student where student_id=id;

--return totalscore;

end;

/

--调用存储过程call pro_total_score(1001,@totalscore );

4、 编写存储过程，输入学号和科目名称，返回对应的平均成绩

create or replace procedure pro_avg_score(id int,coursename varchar(20),avgscore out int)

as

begin

case when coursename='math' then select avg(math) into avgscore from student where student_id=id;

when coursename='pysical' then select avg(pysical) into avgscore from student where student_id='id';

when coursename='art' then select avg(art) into avgscore from student where student_id=id;

when coursename='music' then select avg(music) avgscore from student where student_id=id;

end case;

end;

/

5、编写存储过程，对于学生每科成绩，0-59，绩点0,60-69绩点0.1,70-79绩点0.2,80-89绩点0.3,90-100绩点0.4，输入学号和科目名称，返回对应的绩点

create or replace procedure pro_cal_point(id1 int,coursename varchar(30),point  out float)

as

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.2 end) into point from student  where student_id=id1 limit 1;

when coursename='pysical' then select (case when pysical<=59 then 0

when pysical<=69 then 0.1

when pysical<=79 then 0.2

when pysical<=89 then 0.3

when pysical<=100 then 0.4 else 0.2 end) into point from student  where student_id=id1 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.2 end) into point from student  where student_id=id1 limit 1;

when coursename='music' then select (case when music<=59 then 0

when music<=69 then 0.1

when music<=79 then 0.2

when music<=89 then 0.3

when music<=100 then 0.4 else 0.2 end) into point from student  where student_id=id1 limit 1;

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

end case;

end;

/

3. 性能优化

当前有三个表，分别是学生信息表student和202201班级成绩表score1，202202班级成绩表score2（需要确定各表字段）

1、 查202201班级和202202班级所有人语文成绩前10的记录，第一个查询要使用union

select * from score1 order by chinese limit 10

union

select * from score2 order by chinese limit 10;

2、 对以上SQL语句进行优化

--将union修改为union all

select * from score1 order by chinese limit 10

union all

select * from score2 order by chinese limit 10;

3、 查看两个班级相同的科目，202201班在score2中不存在的成绩，要求使用not in（需要确定score1、score2表具体字段有哪些科目，以及题目所谓相同科目是一个具体科目还是所有科目都要做判断）

select * from score1 where score not in (select score from score2 where score1.course=score2.course);

4、 对以上SQL语句进行优化

--not in修改为not exits

select * from score1 where not exists (select score from score2 where score1.score=score2.score and score1.course=score2.course);

5、 查询班级202201语文成绩最高的学生，要求先创建索引，并且能保证一定会使用索引

create index chi_index on score1(chinese);

select max(chinese) from score1;

6、 查询202201班级的学生的成绩比202202班级的学生最高成绩还要大的学生信息，对以下给出的SQL进行改写

SQL：（需要确定原SQL是什么）

select id,totalscore1 from (select id,max(math+chinese) totalscore1 from score1) s1,(select max(math+chinese) totalscore2 from score2) s2 where totalscore1>totalscore2;

改下后：

SELECT id, math + chinese AS totalscore1 FROM score WHERE class = '202201' AND math + chinese > ( SELECT MAX(math + chinese) FROM score WHERE class = '202202' );