sql语法DEMO8

数据库对象管理及 SQL 应用 1
学生表（成绩里面有空值）
--建表&导入数据
create table student(id int ,score int ,month int);
insert into student values(1,56,1),(2,68,1),(3,null,1),(4,67,1),(5,99,1),(6,null,2);
（1）查询月考平均成绩比学生编号 5 的大的学生
--------------------------------------------------- -----------------------------------------------------
select id ,round(avg(nvl(score,0)),2) avgscore from student group by id having
avgscore >(select round(avg(nvl(score,0)),2) from student where id=5);
（2）查询每次月考成绩大于平均成绩的学生
--------------------------------------------------- -----------------------------------------------------
select s1.*,avgscore from student as s1 join(select month,round(avg(nvl(score,0)),2)
as avgscore from student group by month ) as s2 on s1.month=s2.month where score>avgscore;
（3）查询每次平均成绩差值
--------------------------------------------------- -----------------------------------------------------
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;
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 NOT 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
--备注
--导入数据
insert into lineitem
values
(33,306676,44195,1,31,52162.46,0.09,0.04,'A','F','1993-10-29','1993-11-08','1993-11-01','COLLECT
COD','TRUCK','ng to thefuriously ...'),
(33,169588,19589,4,41,67960.78,0.09,0.00,'R','F','1993-11-09','1994-01-24','1993-11-11','TAKE
BACK','RETURN','unusualpacka ...'),
(68,174896,37400,3,46,90660.94,0.04,0.05,'n','o','1998-08-13','1998-07-08','1998-08-29','NONE','RAIL','ehulor
depen ...odeng');
（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 NOT 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 THEN('1993-01-01'),
partition L_SHIPDATE_1 VALUES LESS THEN('1994-01-01'),
partition L_SHIPDATE_1 VALUES LESS THEN('1995-01-01'),
partition L_SHIPDATE_1 VALUES LESS THEN('1996-01-01'),
partition L_SHIPDATE_1 VALUES LESS THEN('1997-01-01'),
partition L_SHIPDATE_1 VALUES LESS THEN('1998-01-01'),
partition L_SHIPDATE_1 VALUES LESS THEN('1999-01-01'),
partition L_SHIPDATE_1 VALUES LESS THEN('1920-01-01')
);
(2) 查询表的 SCHEMA 名称，展示表名，schema 名称
--------------------------------------------------- -----------------------------------------------------
select tablename,schemaname from pg_tables where tablename='lineitem';（3）查看表分布节点的 oid，展示表名，nodeoids
--------------------------------------------------- -----------------------------------------------------
select relname,nodeoids from pg_class t1,pgxc_class t2,pg_namespace t3
where t1.oid=t2.pcrelid and t1.relnamespace=t3.oid and t1.relname='lineitem'
and t3.nspname='public';
（2）查看表所在实例的信息
--------------------------------------------------- -----------------------------------------------------
select t4.* from pg_class t1,pg_namespace t2,pgxc_class t3,pgxc_node t4
where
t1.oid=t3.pcrelid
and
t1.relnamespace=t2.oid
and
t3.nodeoids::varchar=t4.oid::varchar
and t1.relname='lineitem' and t2.nspname='public';
3、数据库连接
（1）创建 user2 用户，user2 用户需要具备创建数据的权限
--------------------------------------------------- -----------------------------------------------------
create user user2 createdb password "test@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';（6）查看所有用户已使用的会话连接数
--------------------------------------------------- -----------------------------------------------------
select count(*) from dv_sessions;
（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）当前有一张表 t_test(id ,name) ,有 2 万以上的数据，请授权只允许 user3
能看 id=1 的数据
--------------------------------------------------- -----------------------------------------------------
--将表 select 权限赋予 user3
grant select on t_test to user3;
--打开表行级访问控制开关
alter table t_test enable row level security;
--创建行级访问控制策略
create row level security policy rls01 on t_test to user3 using(id=1);
（3）修改赋权能看 id=1 或 2 的数据
--------------------------------------------------- -----------------------------------------------------
alter row level security policy rls01 on t_test to user3 using(id=1 or id=2);
（4）当前有一张用户表 t_user(id ,age) ,请创建两名用户 u1 和 u2，密码均为
"test@123"
--------------------------------------------------- -----------------------------------------------------
Create user u1 password “test@123”
Create user u2 password “test@123”（5）设置行级访问控制，u，u2 设置只能看自己的用户信息
--------------------------------------------------- -----------------------------------------------------
--授予 select 的权限
grant select on t_user to u1,u2;
--打开表行级访问控制
alter table t_user enable row level security;
--创建行级访问控制策略
create row level security policy rls02 on t_user to u1,u2
using(id=current_user);
（6）加一个行级访问控制让 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);
（7）删除上述配置的所有行级访问控制策略
--------------------------------------------------- -----------------------------------------------------
drop row level security policy rls01 on t_test;
drop row level security policy rls02 on t_test;
drop row level security policy rls03 on t_test;
（8）关闭表的行级访问控制开关，并且级联删除用户
--------------------------------------------------- -----------------------------------------------------
Alter table t_test disable row level security;
Alter table t_user disable row level security;
--级联删除用户
Drop user user3,u1,u2 cascade;
5、存储过程
--创建表&导入数据
create table score(id varchar(20),score int,cid varchar(20));
insert into score values('''001''',86,'''c1'''),('''002''',95,'''c2''')（1）要求输入 c1 返回该课程的成绩信息（课程和 id 本身带有单引号）
--------------------------------------------------- -----------------------------------------------------
create or replace procedure getscore(cid_score inout varchar(20))
as
begin
cid_score=concat('''',cid_score,'''');
select score into cid_score from score where cid=cid_score;
end;
/
6、触发器
本体根据教授详情表和部门表完成相应触发器创建使用
--创建表
create table teacher(id int primary key,
name varchar(20) not null,
deptno int not null,
tetle varchar(20) not null);
create table department(
id int primary key,
name varchar(20) not null,
number_of_senior int 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 primary');
（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;
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 表中 ID=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、性能调优
当前有三个表，分别是学生信息表 student(id,name,sex,class)和 202201 班级
成绩表 score1(id,course,score),202202 班级成绩表 score2 结果与 score1 相
同
（1）用 union 查询输出 student 所有列，score1 和 2 的 course,grade 列，按
照 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 t4 on
t3.id=t4.id) order by id,score desc;（2）对以上 SQL 进行优化
--------------------------------------------------- -----------------------------------------------------
--将 union 改成 union all
(select t1.*,t2.course,t2.score from student t1 join score1 t2 on
t1.id=t2.id)
Union all
(select t3.*,t4.course,t4.score from student t3 join score2 t4 on
t3.id=t4.id) order by id,score desc;
（3）查看两个班级相同的科目，202201 班在 score2 中不存在的成绩，要求使
用 not in
--------------------------------------------------- -----------------------------------------------------
select course,score from score1 where score not in(
select score from score2 where score2.course=score1.course);
（4）对以上 SQL 进行优化
--------------------------------------------------- -----------------------------------------------------
--将 not in 更改为 not exists
select course,score from score1 where not exists(
select score from score2 where score2.course=score1.course
and score1.score=score2.score);