数据库第4天

--一、集合操作
--UNION/UNION ALL
--合并多个SELECT查询结果
--查询职位是‘MANAGER’的员工和薪水大于2500的员工的集合
select * from emp where job='MANAGER'
UNION
select * from emp where sal>2500;
--相当于：
select * from emp where job='MANAGER' or sal>2500;
--UNION 与 UNION ALL的区别:
--UNION ALL 返回两个结果集中所有的记录，包括重复的行
--UNION 会根据第一个字段进行排序，UNION ALL 不会排序
select * from emp where job='MANAGER'
UNION ALL
select * from emp where sal>2500;
select * from emp;
--INTERSECT
--交集
select * from emp where job='MANAGER'
intersect
select * from emp where sal>2500;
--相当于
select * from emp where job='MANAGER' AND sal>2500;
--MINUS
--差集
--在第一个结果集中存在，在第二个结果集中不存在：结果集1-结果集2
select * from emp where job='MANAGER'
MINUS
select * from emp where sal>2500;
--相当于
select * from emp where job='MANAGER' and sal<=2500;
--注意问题：
--集合操作SELECT语句的列的个数和类型必须一致
--ORDER BY字句只能写在最后一个查询语句中
select ename,job from emp
union all
select ename,job from emp
order by ename;

--二、高级函数
--1.DECODE函数
--decode(expr,search1,resutl1[,search2,result2,...][,defualt])
--'MANAGER'，'ANALYST', 'SALESMAN',分别给奖励
--1.2倍,1.1倍,1倍的薪水，如果这三个职位都不是，给0.5倍薪水
select ename,job,sal,decode(job,
'MANAGER',sal*1.2,
'ANALYST',sal*1.1,
'SALESMAN',sal*1,
0.5*sal) bonus
from emp;
--DECODE函数在order by中的使用
create table dept_cn(
  deptno number(4),
  dname varchar2(50),
  loc varchar2(50)
)
insert into dept_cn values(1,'研发部','大连');
insert into dept_cn values(2,'财务部','大连');
insert into dept_cn values(3,'行政部','大连');
commit;
select * from dept_cn
order by decode(dname,'研发部','yfb',
'财务部','cwb','行政部','xzb') ;

--排序函数
--1.ROW_NUMBER()OVER(partition by col1 order by col2)
--根据col1分组，在分组的内部根据col2排序
--此函数计算的值就是表示每组内部排序的顺序号，组内连续且唯一
select deptno,ename,empno,
row_number()over(partition by deptno order by empno) emp_id
from emp;
--partition 部分

--RANK()OVER(PARTITION BY COL1 ORDER BY COL2)
--如果出现并列编号相同，下一个序号跳过去，组内不连续也不唯一
select deptno,ename,sal,
rank()over(partition by deptno order by sal) rank_id
from emp;
select deptno,ename,sal,
row_number()over(partition by deptno order by sal) rn_id
from emp;
--DENSE_RANK()OVER(PARTITION BY COL1 ORDER BY COL2)
--如果出现平列的情况，不跳跃，组内编号连续不唯一
select deptno,ename,sal,
dense_rank()over(partition by deptno order by sal) dr_id
from emp;
--练习题：
--查询薪水最高的前两名员工的信息
select *
from (
select ename,nvl(sal,0) salary,
dense_rank()over(order by nvl(sal,0) desc) dr from emp)
where dr<=2;
--查询30部门薪水最高前两名员工的信息
select *
from(
  select ename,nvl(sal,0),deptno,dense_rank()over(partition by deptno order by nvl(sal,0) desc ) rank_id
  from emp
)
where rank_id<=2 and deptno=30;
select *
from(
  select ename,nvl(sal,0),deptno,dense_rank()over(order by nvl(sal,0) desc ) rank_id
  from emp
  where deptno=30
)
where rank_id<=2 and deptno=30;

--高级分组函数
--GROUPING SETS((a),(b))
--相当于分别根据a,b进行分组查询得到并集结果
---GROUPING SETS((a,b),(b))
--相当于分别根据(a,b),b进行分组查询得到并集结果
select deptno,job,round(avg(sal),2),count(ename) from emp
group by grouping sets(deptno,job);
--相当于
select deptno,null,round(avg(sal),2),count(ename) from emp
group by deptno
union all
select null,job,round(avg(sal),2),count(ename) from emp
group by job;
select deptno,job,round(avg(sal),2),count(ename) from emp
group by grouping sets((deptno,job),job);
--相当于
select deptno,job,round(avg(sal),2),count(ename) from emp
group by deptno,job
union all
select null,job,round(avg(sal),2),count(ename) from emp
group by job;

--3.视图（View）
--视图也被称为虚表，是一组数据的逻辑表示，其本质是一条SELECT语句
--创建一个视图
CREATE VIEW v_emp_20
AS
(
  select * from emp where deptno = 20
);
select * from v_emp_20;
--v_emp_20是视图（虚表），emp是基表
select job,avg(sal) from v_emp_20 group by job;
--作用
--限制数据的访问：通过视图只能访问给定的字段，对其他字段起到
--安全和保密的作用。
create view v_emp_base
as(select empno,ename,job,hiredate,deptno from emp);
select * from v_emp_base;

--操作
desc v_emp_base;
--对视图的操作，会影响到基表
--插入数据,只能插入对视图可见的字段，视图不可见的字段插入值为NULL
--不是所有的视图都可以进行插入操作，不可见视图中包括了非空字段，则
--不能用视图插入数据
insert into v_emp_base
values(7979,'liulaoshi','SALESMAN',sysdate,30);
select * from emp;
--删除
delete from v_emp_base where empno=7979;
--修改
update v_emp_base set ename='LiuLaoShi' where ename='SMITH';
--不能修改视图中不可见字段
update v_emp_base set sal=sal+1200 where ename='LiuLaoShi';
--不在视图可见范围内的记录，不能做修改和删除，对其操作影响0行
update v_emp_20 set ename='laosiji' where ename='siji' ;
delete from v_emp_20 where ename='siji';
--创建一个视图，v_emp_avg_sal_dept
--按照部门分组，获取部门平均薪水，视图中只包含：deptno,avg_sal
create view  v_emp_avg_sal_dept
as(
  select deptno,avg(sal) avg_sal
  from emp
  group by deptno
);
select * from v_emp_avg_sal_dept;
--视图分类
--简单视图：基于单表的，不包含任何函数运算，表达式，或分组函数，此
--视图是基表的子集。
--复杂视图：基于单表，但包含了单行函数，分组函数，表达式，group by子句
--连接视图：基于多表，比如关联查询，多表子查询的。

--复杂视图和连接视图 无法做DML操作

--添加CHECK OPTION 约束
create view v_emp_20_ck
as
(
  select * from emp where deptno=20
)
with check option;
--update,修改后的结果必须能够通过视图查看到
update v_emp_20_ck set deptno=30;
--insert,新增的记录必须能在视图中查看到
insert into v_emp_20_ck(empno,ename,deptno)
values(1234,'LLS',30);
--添加READ ONLY约束
--不能进行DML操作
create view v_emp_20_ro
as(select * from emp where deptno=20)
with read only;
--抛异常
insert into v_emp_20_ro(empno,ename,deptno)
values(1222,'LLS',20);
--删除视图,不会删除基表中内容
drop view v_emp_20;

--序列
--sequence
--用来产生唯一数字值的数据库对象。序列由Oracle程序按递增或递减
--自动产生数值，通常用来自动生成主键值。
--primary key 包含主键约束的字段 非空且唯一 通过主键查找唯一记录
select * from emp;
create sequence  emp_sq ;

select emp_sq.nextval from dual;
select emp_sq.currval from dual;

insert into emp (empno,ename,deptno)
values(emp_sq.nextval,'LLS',20);
select * from emp;

--创建一个起始100,每次增加10的序列
drop sequence emp_sq;
create sequence  emp_sq
start with 100
increment by 10;
select emp_sq.nextval from dual;
select dept_sq.nextval from dual;
select dept_sq.currval from dual;

--索引 index
--提高查找效率的对象，索引对象中保存索引关键字和指向表中数据的
--指针（地址），以此来提高查找效率。
--索引一旦被创建，则用Oracle系统自行维护，不需要在查询语句中指定
--使用索引，自动使用索引提高查询效率。

create index emp_ename_index on emp(ename);
select ename from emp;

create index emp_job_sal_index on emp(job,sal);
select ename,job,sal
from emp
order by job,sal;

--何时使用索引
--为经常出现在WHERE子句后的字段创建索引
--为经常在order by 子句，distinct 后边的字段添加索引
--如果是复合索引，需要保证索引字段顺序和这些关键字后面的字段一致
--为经常作为表的连接条件的字段添加索引
--不要在经常做DML操作的表添加索引
--不要在小表上添加索引

--作业：
--1.查询姓“叶”的老师的个数
select count(tname) from teacher where tname like '尹%';
--2.查询没有学过“叶平”老师的同学的学号，和姓名
select s.s#,s.sname
from student s
where s# not in(
select distinct sc.s# from sc,course,teacher
where sc.c#=course.c# and teacher.t#=course.t#
and tname='叶平'
);

--3.查询学过“001”并且也学过“002”课程的同学的学号和姓名
select s.s#,s.sname from student s,sc
where sc.s#=s.s# and sc.c ='001'
intersect
select s.s#,s.sname from student s,sc
where sc.s#=s.s# and sc.c ='002'

select  s#,sname
from student
where s# in (
  select s# from sc where c#='001' or c#='002'
  group by s# having count(c#)=2
);

select s#,sname
from student
where s# in(
select a.s# from (select s# from sc where c#='001' ) a,
(select s# from sc where c#='002' ) b
where a.s#=b.s#;
)
--4.查询学过“叶平”老师教的全部课程的同学的学号，姓名
--选过一科就显示
select distinct s.s#,s.sname
from student s,sc
where sc.c# in (select c.c# from course c ,teacher t
where c.t#=t.t# and t.tname='尹文浩'
)and sc.s#=s.s#;
--只有全选才显示
select s#,sname
from student
where s# in (
  select s# from sc where c# in (
  select c# from course c join teacher t on t.t#=c.t#
  where tname='叶平')
  group by s# having count(s#)=(
  select count(c#) from course c join teacher t on t.t#=c.t#
  where tname='叶平'
  )
);
--5.查询所有课程成绩小于60分的同学的学号，姓名
--只要一科挂就显示
select s#,sname
from student
where s# in (select distinct s# from sc where score<60)
--所有都挂才显示
select s#,sname
from student
where s# in(select s# from sc
group by s# having max(score)<60)
--6.查询各科成绩前三名的学生姓名，课程编号，成绩，排名（考虑并列情况：跳跃式）
select * from (select s.sname,sc.c#,sc.score,
rank()over(partition by c# order by score desc) rank_score
from student s join sc on s.s#=sc.s#
)
where rank_score<=3;