sql语法DEMO5

1. 数据库对象管理及SQL语法1

由于疫情管控，某小区进行封锁，收集了RESIDENTS居民表，包含姓名、年龄、性别、楼栋信息。

--创建表

create table RESIDENTS(name varchar(20),age int,sex char(1),building int);

--导入数据

insert into RESIDENTS VALUES('a',0,'m',02);

insert into RESIDENTS VALUES('b',24,'m',03);

insert into RESIDENTS VALUES('c',25,'f',05);

1、为了方便按楼栋给婴儿送纸尿裤，查出每栋age<1的数量，最后显示楼栋信息和对应数量

select building,count(*) from residents where age<1 group by building;

2、由于每栋楼各个年龄段的人都有，故按age年龄段分组[0-18）,[18-35),[35-55),[55-maxvalues)形成age_group，每个组命名group1,group2,group3,group4

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

when age>=18 and age<35 then 'group2'

when age>=35 and age<55 then 'group3'

 else 'group4' end as age_group from residents;

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

select  case when  age<18 then 'group1'

when  age>=18 and age<35 then 'group2'

when age>=35 and age<55 then 'group3'

 else 'group4' end as age_group,count(age) as "人数",

max(age) as "最大年龄",min(age) as  "最小年龄",floor(avg(age)) as  "平均年龄"

from residents group by age_group order by "人数" desc;

1、 由于需要每天要送食物，增加如下每个年龄段所需食物营养价值表，需要统计出该小区每天总营养值

 

select sum(case when age<18 then 5 when age>=18 and age<35 then 7

when age>=35 and age<55 then 6

else 5 end) as "每天总营养值" from residents;

--如果需要计算每个人需要的总营养值如下：

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

when age>=18 and age<35 then 'group2'

when age>=35 and age<55 then 'group3'

else 'group4' end as age_group,

case when age_group='group1' then 5

when age_group='group2' then 7

when age_group='group3' then 6

when age_group='group5' then 5

end as nutrition_value from residents;

2、 按楼栋求出每栋楼所需营养值

select building,sum(case when age<18 then 5

when age>=18 and age<35 then 7

when age>=35 and age<55 then 6

else 5 end) as "每栋楼所需营养价值"

from residents group by building;

2. 数据库对象管理及SQL语法2

当前有一张订单表lineitem，具体字段如下

L_ORDERKEY BIGINT NOT NULL --订单key

L_PARTKEY BIGINT NOT NULL --配件key

L_SUPPKEY BIGINT NOT NULL --供应商key

L_LINENUMBER BIGINT NOT NULL --流水号

L_QUANTITY float8 null --数量

L_EXTENDEPPRICE float8 NOT NULL --出厂价

L_DISCOUNT float8 NOT NULL --折扣

L_TAX float8 NOT NULL --税点

L_RETURNFLAG CHAR(1) NOT NULL --原返标志

L_LINESTATUS CHAR(1) NOT NULL --明细

L_SHIPDATE DATE NOT NULL --发货日期

L_COMMITDATE DATE NOT NULL --预计到达日期

L_ARRIVALDATE DATE NOT NULL --达到时间

L_ORDERSTRATEGY CHAR(32) NOT NULL --订单处理策略

L_TRANSPORTROUTE CHAR(32) NOT NULL --运输路径

L_COMMENT VARCHAR(64) NOT NULL --备注

--数据导入

 

1、创建分区表，根据上述字段信息创建分区表，按L_SHIPDATE分区，按年分1993,1994,1995,1996,1997,1998,1999，分区名称分别为L_SHIPDATE_1，第二个分区名称为L_SHIPDATE_2以此类推，使用L_ORDERKEY进行哈希分布，建表完成后执行上方数据导入代码，进行数据导入

create table lineitem(

L_ORDERKEY BIGINT NOT NULL ,

L_PARTKEY BIGINT NOT NULL ,

L_SUPPKEY BIGINT NOT NULL ,

L_LINENUMBER BIGINT NOT NULL,

L_QUANTITY float8 null,

L_EXTENDEPPRICE float8 NOT NULL,

L_DISCOUNT float8 NOT NULL ,

L_TAX float8 NOT NULL,

L_RETURNFLAG CHAR(1) NOT NULL ,

L_LINESTATUS CHAR(1) NOT NULL ,

L_SHIPDATE DATE NOT NULL ,

L_COMMITDATE DATE NOT NULL ,

L_ARRIVALDATE DATE NOT NULL ,

L_ORDERSTRATEGY CHAR(32) NOT NULL ,

L_TRANSPORTROUTE CHAR(32) NOT NULL ,

L_COMMENT VARCHAR(64) NOT NULL)

distribute by hash(L_ORDERKEY)

partition by range(L_SHIPDATE)

(partition L_SHIPDATE_1 values less than ('1993-01-01 00:00:00'),

partition L_SHIPDATE_2 values less than ('1994-01-01 00:00:00'),

partition L_SHIPDATE_3 values less than ('1995-01-01 00:00:00'),

partition L_SHIPDATE_4 values less than ('1996-01-01 00:00:00'),

partition L_SHIPDATE_5 values less than ('1997-01-01 00:00:00'),

partition L_SHIPDATE_6 values less than ('1998-01-01 00:00:00'),

partition L_SHIPDATE_7 values less than ('1999-01-01 00:00:00'),

partition L_SHIPDATE_8 values less than ('2000-01-01 00:00:00')

);

 

2、查询表的schema名称，展示表名，schema名称

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

或

select c.relname,n.nspname from pg_class c ,pg_namespace n where c.relnamespace=n.oid and c.relname='lineitem';

3、产看表分布节点的oid，展示表名，nodeoids

select relname,nodeoids from pg_class,pgxc_class where oid=pcrelid where relname='lineitem';

1、 查看表所在实例的信息

select relname,pgxc_node.* from pg_class,pgxc_class,pgxc_node where pg_class.oid=pgxc_class.pcrelid and cast(pgxc_class.nodeoids as varchar(20))=cast(pgxc_node.oid as varchar(20)) and relname='p_table';

3. 数据库连接

1、 创建user2用户，user2用户需要具备创建数据库的权限

create user user2 createdb password 'Huawei@123';

2、 查看用户的连接数上限

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

3、 设置user2用连接数为100

alter user user2 connection limit 100;

4、 查询postgres用户连接数上限，显示库，上限数量

select datname,datconnlimit from pg_database where datname=’postgres’;

 

5、 查询postgres用户已使用的会话连接数

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

--如果查询已结束的会话数量，筛选state

-- select count(datname) from pg_stat_activity where datname=’postgres’ and state=’idle’;

6、 查看所有用户已使用的会话连接数

Select count(datname) from pg_stat_activity;

7、 查询库最大连接数

show max_connections;

8、 查询会话状态，显示datid、pid、state

select datid,pid,state from pg_stat_activity;

4. 安全审计

1、 创建user3用户，密码’test@123’

create user user3 password 'test@123';

2、 给用户授予查看审计权限

alter user user3 with auditadmin; 

3、 登录postgres，创建统一审计策略adt1，对所有数据库执行create审计操作

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

--创建统一审计策略

create audit policy adt1 privileges create;

4、 登录postgres，创建审计策略adt2，对所有数据库执行select审计操作

create audit policy adt2 access select;

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

--创建表

create table tb1(c1 int);

--创建资源标签标记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);

6、 为统一审计对象策略adt1，添加描述’audit policy for tb1’

alter audit policy adt1 comments 'audit policy for tb1';

7、 修改adt1，使之对IP地址为’10.20.30.40’的场景生效

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

8、 禁用统一审计策略adt1

alter audit policy adt1 disable;

9、 删除策略adt1、adt2、adt3和相应的资源标签，级联删除用户user1

--删除审计策略

drop audit policy adt1,adt2,adt3;

--删除资源标签

drop resource label tb1_label;

--级联删除用户

drop user user1 cascade;

5. 存储过程

基于以下信息表，完成以下实验要求。

--创建表

CREATE TABLE APS_STUDENTS(LOGID SERIAL,STARTIME TIMESTAMP(0) NOT NULL,PRIMARY KEY(LOGID));

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

create or replace procedure pro_s(num int)

as

begin

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

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

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

end loop;

end;

/

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

call pro_s(9000);

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

select count(*) from aps_students;

6. 触发器

本题根据教授详情表和部门表完成相应触发器创建使用

--创建表

create table teacher(id integer primary key,name varchar(50) not null,deptnd integer not null,tetle varchar(50) not null);

create table department(id integer primary key,name varchar(50) not null,number_of_senior integer default 0);

--导入数据

insert into department values(1,'physics',0),(2,'mathmetics',0),(3,'chemistry',0);

insert into teacher values(1,'tom',1,'associate professor'),(2,'bill',1,'professor'),(11,'eiston','3','associate professor');

1、 创建Tri_update_D触发器，如果修改Number_of_senior字段时提示“不能随便修改部门教授职称人数”，如果已经有了Tri_update_D触发器，则删除后再重建

--创建触发器函数

create or replace function print_info() returns trigger

as

$$

begin

dbe_output.print_line('不能随便修改不能教授职称人数');

return null;--返回null表示不允许修改，返回old表示可以修改

end;

$$ language plpgsql;

--当触发器存在时将触发器删除，然后创建触发器

drop trigger if exists Tri_update_D on department;

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

2、 禁止触发器，修改DEPARTMENT表中D=1的NUMBER_OF_SENIOR=10，并查出表中数据

--禁用触发器

alter table department disable trigger Tri_update_D;

--执行更新操作

update department set number_of_senior=10 where id=1;

--查询部门表，查看更新结果

select * from department;

3、 启动触发器，修改DEPARTMENT表中ID=1的NUMBER_OF_SENIOR=20

--启动触发器

alter table  department enable trigger Tri_update_D;

--执行更新操作

update department set number_of_senior=20 where id=1;

--查询部门表，查看更新结果

select * from department;

7. 性能调优

通常的SQL优化会通过参数调优的方式进行调整，例如如下参数

set enable_fast_query_shipping = off;

set enable_stream_operator = on;

请根据以下表完成数据库优化

--建表

create table tb_user(stu_no int,stu_name varchar(32),age int,hobby_type int) distribute by hash(age);

--插入数据

insert into tb_user select id,'xiaoming'||(random()*50+10)::int,

(random()*50+10)::int,

(random()*5+1)::int

from (select generate_series(1,100000)id)tb_user;

1、 收集tb_user的统计信息

analyze tb_user;

2、 为下面两个查询语句创建索引，让执行计划和索引最合理

sql1:explain analyze select * from tb_user where age=29 and stu_name='xiaoming';

sql2:explain analyze select * from tb_user where  stu_no=100 and age=29;

--SQL1

select gs_index_advise('select * from tb_user where age=29 and stu_name=’'xiaoming'’');

create index age_name on tb_user(stu_name,age);

--SQL2

select gs_index_advise('select * from tb_user where stu_no=100 and age=29');

create index age_no on tb_user(stu_no,age);

3、 在上题的基础上，三种不同的方式使如下SQL不走索引

explain analyze select * from tb_user where stu_no=100 and age=29;

--1.通过hint干预优化不走索引

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

--2.调大index开销

set cpu_index_tuple_cost = 100000;

--3.直接禁用索引

alter index age_no,age_name unusable;

--4.禁止优化器使用indexscan:

set enable_indexscan=off