sql语法DEMO7

1、数据库对象管理及 SQL 语法 1
基于以下学生成绩表，完成以下实验要求
--创建表
create table su(
id int primary key not null,--编号
firstname varchar(50) not null, --姓名
familyname 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(firstname||'.'||familyname) from su;
(2)插入一条新数据(2,'tom','jerry','tom','H',63),当出现主键冲突时，将
mark 修改为'F'
--------------------------------------------------- -----------------------------------------------------
insert into su values(2,'tom','jerry','tom','H',63)on duplicate key update mark='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 position from su;
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）使用两个查询时，查看’postgres’数据库的最大连接数和已使用连接数
--------------------------------------------------- -----------------------------------------------------
--数据库的最大连接数
Select datname,datconnlimit from pg_database where datname=’postgres’;
--数据库已使用连接数
select datname,count(*) from pg_stat_activity where datname='postgres' group by datname;
（2）创建用户 user_test,并指定该用户具有创建数据库和创建角色的权限
--------------------------------------------------- -----------------------------------------------------
create user user_test createdb createrole password "Test@123";
（3）创建表table_test,此表中包含一个名为col_test的列，为用户user_test
授权在 table_test 表的 col_test 列上的查询、更新权限
--------------------------------------------------- -----------------------------------------------------
create table table_test(col_test int);
grant select(col_test) ,update(col_test) on table_test to user_test;
（4）收回用户 user_test 在 table_test 表上 col_test 列上的更新权限
--------------------------------------------------- -----------------------------------------------------
Revoke update(col_test) on table_test from user_test;
（5）创建角色 role_test，此角色拥有审计权限
--------------------------------------------------- -----------------------------------------------------
create role role_test password "test@123" auditadmin poladmin;（6）将角色 role_test 的权限授权给用户 user_test,并允许用户将此权限再授
权给其他用户或角色使用
--------------------------------------------------- -----------------------------------------------------
grant role_test to user_test with admin option;
（7）用户 user_test 账号被盗，请手动锁定此账号
--------------------------------------------------- -----------------------------------------------------
Alter user user_test account lock;
（8）级联删除用户 user_test，并重新创建，将账号设置为在 2023 年过去期间
有效
--------------------------------------------------- -----------------------------------------------------
--级联删除用户
drop user user_test cascade;
--创建用户
create user user_test createdb createrole password "test@123"
valid begin '2023-10-01 00:00:00' valid until '2023-10-07 23:59:59';
4、行级访问控制
执行以下 SQL 创建表和数据
--创建表
create table bank_card(
b_number nchar(30) primary key,
b_type nchar(20),
b_c_id int not null);
--导入数据
INSERT INTO bank_card(b_number, b_type, b_c_id) VALUES ('6222021302020000001','信用卡',1);
INSERT INTO bank_card(b_number, b_type, b_c_id) VALUES ('6222021302020000002','信用卡',3);
INSERT INTO bank_card(b_number, b_type, b_c_id) VALUES ('6222021302020000003','信用卡',5);
INSERT INTO bank_card(b_number, b_type, b_c_id) VALUES ('6222021302020000004','信用卡',7);
INSERT INTO bank_card(b_number, b_type, b_c_id) VALUES ('6222021302020000005','储蓄卡',9);
INSERT INTO bank_card(b_number, b_type, b_c_id) VALUES ('6222021302020000006','储蓄卡',1);
INSERT INTO bank_card(b_number, b_type, b_c_id) VALUES ('6222021302020000007','储蓄卡',3);
INSERT INTO bank_card(b_number, b_type, b_c_id) vALUES ('6222021302020000008','储蓄卡',5);
INSERT INTO bank_card(b_number, b_type, b_c_id) VALUES ('6222021302020000009','储蓄卡',7);
INSERT INTO bank_card(b_number, b_type, b_c_id) VALUES ('6222021302020000010','储蓄卡',9);（1）创建用户 crecard_mger
savcard_mger 密码均为"test@123";
--------------------------------------------------- -----------------------------------------------------
create user crecard_mger password "test@123";
create user savcard_mger password "test@123";
(2)给上题中创建的两个用户授予 bank_card 表的读取权限
--------------------------------------------------- -----------------------------------------------------
grant usage on schema root to crecard_mger ,savcard_mger;
grant select on bank_card to crecard_mger,savcard_mger;
（3）打开 bank_card 表的行级访问控制开关
--------------------------------------------------- -----------------------------------------------------
alter table bank_card enable row level security;
（4）创建行级访问控制策略 bank_card_rls,要求 crecard_mger 用户只能查看
信用卡信息，savcard_mger 用户只能查看储蓄卡信息
--------------------------------------------------- -----------------------------------------------------
create row level security policy bank_card_rls on bank_card to
crecard_mger,savcard_mger using(b_type=case when current_user='crecard_mger'
then '信用卡' else '储蓄卡' end);
（5）切换到 crecard_mger 用户查看 bank_card 表的内容
--------------------------------------------------- -----------------------------------------------------
--切换登录用户（DAS 右上角切换）
--查看表信息
Select * from bank_card;
（6）使用 root 用户删除行级控制策略 bank_card_rls ，并关闭表的行级访问
控制开关
--------------------------------------------------- -----------------------------------------------------
--删除行级访问控制策略
drop row level security policy
--关闭表的行级访问控制开关
alter table bank_card disable row level security;
5、触发器根据如下表信息，完成如下实验
create table student(sno int,sname varchar(50),ssex varchar(5),sage int);
create table course(cno int,cname varchar(50),credit int);
create table elective(sno int,cno int,grade int);
（1）创建视图 SELECT_SD 查看学生成绩信息，查看学生姓名，课程名称，课程
成绩
--------------------------------------------------- -----------------------------------------------------
create view SELECT_SD as select sname,cname,grade from student s,
course c,elective e where e.sno=s.sno and e.cno=c.cno;
（2）编写函数 FUNC_SUM,返回某个学生的分数总和
--------------------------------------------------- -----------------------------------------------------
create or replace function FUNC_SUM(stuid int) returns integer as
$$
declare result integer ;
begin
select sum(grade) into result from elective where sno=stuid;
return result;
end;
$$language plpgsq;
（3）创建触发器 DELETE_ELE,在 student 表上绑定触发器 DELETE_ELE,在删除
表中某个学生时将 ELECTIVE 表中该学生的选课记录一并删除
--------------------------------------------------- -----------------------------------------------------
--删除 elective 表记录的函数
create ore replace function func_delete_ele() returns trigger as
$$
begin
delete from elective where sno=old.sno;
return old;
end;
$$language plpgsql;
--绑定到 student 表的触发器
create trigger delete_ele before delete on student for each row execute
procedure func_delete_ele();6、游标
以下为表创建 SQL 语句
create table teacher(
id integer not null,
name varchar(50) not null,
deptno integer not null,
salary float not null,
title varchar(100) not null --职称：讲师，副教授，教授
);
create table department(
id integer not null,
name varchar(50) not null);
(1)创建存储过程 pro_curs_1,使用有游标打印各部门总人数，按人数降序排序，
打印格式如下：
部门名称 1 ---人数
部门名称 2 ---人数
打印操作可以使用 DBE_OUTPUT.PRINT_LINE(outputstr)接口
--------------------------------------------------- -----------------------------------------------------
create or replace procedure pro_curs_1()
as
declare cursor cur1 is select d.name as dname ,count(*)
as scount from teacher t,department d where t.deptno=d.id
group by d.name order by scount desc;
begin
for i in cur1 loop
DBE_OUTPUT.PRINT_LINE(concat(i.dname,'---',i.scount::varchar));
end loop;
end;
/
call pro_curs_1;
(2)创建存储过程 pro_curs_2，使用游标读取薪水降序排序的前三位老师和后三
位老师的信息，分别获取 ID,姓名，部门名称，薪水和职称，请按一下格式
打印。
ID-姓名-部门名称-薪水-职称--------------------------------------------------- -----------------------------------------------------
create or procedure pro_curs_2()
as
declare cursor cur1 is select sid,sname,dname,salary titile from
((select * from teacher order by salary desc limit 3) union all
(select * from teacher order by salary limit 3)) t join department d
on t.partno=d.id;
begin
for i in cur1 loop
DBE_OUTPUT.PRINT_LINE(concat(i.sid,'-',i.sname,'-',i.dname,'-',
i.salary,'-',i.title));
end;
/
Call pro_curs_2():
7、数据库优化
通常的 SQL 优化会通过参数调优的方式进行调整，例如如下参数
set enable_fast_query_shipping=off;
set enable_stream_operator =on;
请根据以下表完成数据库优化
--创建表
create table tb_user(stu_no int ,stu_name varchar(20),age int ,hobby_type int)
distribute by hash(age);
--插入数据
insert into tb_user select id,'xiaoming'||(random()*60+10)::int ,
(random()*60+10)::int,
(random()*5+1)::int from (select generate_series(1,100000) id ) tb_user;
(1) 收集表 tb_user 的统计信息
--------------------------------------------------- -----------------------------------------------------
Analyze tb_user;
(2)为以下 SQL 语句创建索引使执行计划和索引最合理
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=20;
--------------------------------------------------- -----------------------------------------------------
--SQL1
select * from gs_index_advise('select * from tb_user where age=29 and stu_name=''xiaoming''');
Create index idx01 on tb_user( stu_name,age );
--SQL2
select * from gs_index_advise('select * from tb_user where stu_no=100 and age=20');Create index idx02 on tb_user(stu_no,age);
(2) 在上题操作的基础上，用 3 种不同方式使如下 SQL 不走索引
select * from tb_user where stu_no=100
and age=20;
--------------------------------------------------- -----------------------------------------------------
--方法 1 使用 hint 强制不走索引
explain analyze select * /*+ tablescan(tb_user)*/ from tb_user where stu_no=100 and
age=20;
--方法 2 调大 index 开销
Set cpu_index_tuple_cost=1000000;
--方法 3 禁用索引
alter index idx02 unusable;