sql语法DEMO

1. 数据库对象管理及SQL应用

当前有两张表分别是学生表和班级表，请基于两张表完成以下实验要求。

--创建表

create table student(sno int,sname varchar(50),score int,cno int);

create table classes(cno int,cname varchar(50));

--导入数据

insert into student values(123,'a',456,1);

insert into student values(124,'b',546,1);

insert into student values(125,'c',548,1);

insert into student values(126,'d',569,1);

insert into student values(127,'e',540,1);

insert into student values(128,'f',536,2);

insert into student values(129,'g',512,2);

insert into student values(130,'h',546,2);

insert into student values(131,'i',508,2);

insert into student values(132,'j',456,2);

insert into  classes values(1,'1 班');

insert into  classes values(2,'2 班');

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

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

2、 查看每个班级（cno）月考总分（score）前三名，其中要求分设相同的人具有相同的编号

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

2. 常用系统表查询

1、 表创建

请按要求创建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),

partition p3 values less than(30),

partition p4 values less than(40));

2、 查询表的模式名和表名

--第一种解法：基于系统表pg_class、pg_namespace查询

select c.relname,n.nspname from pg_class c join pg_namespace n on c.relnamespace=n.oid where c.relname='p_table';

--第二种解法：基于系统视图pg_tables查询

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

3、 查询表的所在节点nodeoids信息

--schema过滤是为了避免不同schema下表同名的问题，指定查询本题的表

select t1.relname,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';

--pg_class   pgxc_class

select nodeoids from pg_class c join pgxc_class pc on c.oid=pc.pcrelid where c.relname='p_table';

4、 查询表所在的节点实例信息

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 cast(t2.nodeoids as varchar(20))=cast(t4.oid as varchar(20)) and t1.relname='p_table' and t3.nspname='root';

select * from pgxc_node where oid in (16673,16678,16680);

3. 用户及权限管理

当前有一张表sjh_test(a int,b int)，和角色sjh112，请给予当前环境完成以下用户及权限相关管理操作。

--创建表

create table sjh_test(a int,b int);

--创建角色

create role sjh112 password 'Huawei@123';

1、 创建用户sjh111

create user sjh111 password 'Huawei@123';

2、 将表sjh_test表的读取，删除权限授权给sjh111用户

--普通用户只有public模式的权限，需要将当前schema使用权赋予给用

  grant usage on schema root to sjh111;

--赋权

grant select,delete on sjh_test to sjh111;

3、 为用户sjh111授权在sjh_test表的a、b列上的查询、添加和更新权限

grant  select(a,b),insert(a,b),update(a,b) on sjh_test to sjh111;

4、 将用户sjh111授权在sjh_test表的a列上的查询、添加和更新权限收回

  revoke select(a),insert(a),update(a) on sjh_test from sjh111;

-select * from  information_schema.column_privileges where  table_name='sjh_test' and grantee='sjh111'

5、 创建角色sjh_audit，此角色拥有审计权限

create user sjh_audit with auditadmin password 'Huawei@123';

6、 将sjh112角色权限授予给用户sjh111

grant sjh112 to sjh111;

7、 创建新用户sjh113，设置使用有效期”2023-01-28”至”2026-01-01”

create user sjh113 password 'Huawei@123' valid begin '2023-01-28' valid until '2026-01-01';

---\du

4. 行级访问控制

当前有all_data表，字段如下，请给予此表实现行级访问控制

--创建表

create table all_data(role varchar(50),name varchar(50),age int);

--导入数据

insert into all_data values('root','zhangsan',18),('sjh111','lisi',43),('sjh113','wangwu',35);

1、 打开all_data表的行访问控制策略开关

alter table all_data enable row level security;

2、 创建行级访问控制策略，当前用户只能查看用户自身的数据

create row level security policy all_data_rls on all_data using(role = current_user);

3、 删除策略

drop row level security policy all_data_rls on all_data;

4、 关闭行级访问策略

alter table all_data disable row level security;

5. 触发器

create table student(sno integer,sname varchar(50),ssex varchar(5),sage integer);

create table course(cno integer,cname varchar(50),credit integer);

create table elective(sno integer,cno integer,grade integer); 

1、 创建视图SELECT_SD，查看学生成绩信息，查看学生姓名，课程名称(cname：course name)，课程成绩。

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_SUN(stuid int) returns integer as

$$

declare result integer;

begin

select sum(grade) into result from elective where sno=stuid;

return result;

end;

$$ language plpgsql;

3、 创建触发器DELETE_ELE，在STUDENT表上绑定触发器DELETE_ELE，在删除表中某个学生时，将ELECTIVE表中该学生的选课记录一并删除。

--删除elective表记录的函数

create or 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 perscount from teacher t,department where t.deptno=d.id group by d.name order by perscount desc;

dname varchar(50);

perscount integer;

begin

open cur1;

loop

fetch next from cur1 into dname,perscount;

DBE_OUTPUT.PRINT_LINE(concat(dname,'---',cast(perscount as varchar(10))));

if cur1 % notfound then exit;

close cur1;

end if;

end loop;

END;

call pro_curs_1();

2、 创建存储过程pro_curs_2，使用游标读取薪水降序排序的前三位老师和后三位老师的信息，分别获取ID，姓名，部门名称，薪水和职称，请按以下格式打印。

ID-姓名-部门名称-薪水-职称

create or replace procedure pro_curs_2()

as

declare

cursor cur2 is select t.id as sid,t.name as sname, d.name as dname,salary,title 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.deptno=d.id;

sid integer;

sname varchar(50);

dname varchar(50);

salary float;

title varchar(50);

begin

open cur2;

loop

fetch next from cur2 into sid,sname,dname,salary,title;

DBE_OUTPUT.PRINT_LINE(concat(cast(sid as varchar(20),'-',sname,'-',dname,'-',cast(salary as varchar(20)),'-',title)));

if cur2 % notfound then exit;

close cur2;

end if;

end loop;

END;

create or replace procedure pro_curs_2()
as
declare cursor cur1 is select t.id as sid,t.name as sname, d.name as dname,salary,title 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.deptno=d.id;
begin
for i in cur1 loop
DBE_OUTPUT.PRINT_LINE(concat(i.sid,'-',i.sname,'-',i.dname,'-',i.salary,'-',i.title));
end loop;
end; 

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()*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、 为下面两个查询语句创建索引，让执行计划和索引最合理

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、 在上题操作的基础上，用3种不同方式使如下SQL不走索引

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

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

explain analyze select /*+tablescan(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