Oracle_day02

1. 多表查询

1.1 笛卡尔积

select * from dept;--4行
select * from emp;--14行
select * from emp,dept;--笛卡尔积，56行，dept和emp表的乘积，无太大意义
select * from emp e1,dept d1 where e1.deptno=d1.deptno;
--14行，通过限制条件过滤出有意义的数据

1.2 Oracle连接条件的类型

等值连接 不等值连接 外连接 自连接

1.3 内连接

• 隐式内连接：
  等值内连接：where e1.deptno=d1.deptno;
  不等值内连接：where e1.deptno<>d1.deptno;
  自连接：自己连接自己
• 显式内连接：
  select * from tab1 inner join tab2 on 连接条件
  inner关键字可省略

--1. 查询员工编号,员工姓名,经理的编号,经理的姓名
select e1.empno,e1.ename,e1.mgr,e2.ename from 
emp e1,emp e2 where e1.mgr=e2.empno;

--2. 查询员工编号,员工姓名,员工部门名称,经理编号,经理姓名
select e1.empno,e1.ename,d1.dname,e1.mgr,e2.ename from 
emp e1,emp e2,dept d1 where e1.mgr=e2.empno and e1.deptno=d1.deptno;

--3. 查询员工编号,员工姓名,员工部门名称,经理编号,经理姓名,经理部门名称
select e1.empno,e1.ename,d1.dname,e1.mgr,e2.ename,d2.dname 
from emp e1,emp e2,dept d1,dept d2 
where e1.deptno=d1.deptno and e1.mgr=e2.empno and
e2.deptno=d2.deptno;

--错误写法，强制了员工和经理同一个部门
select e1.empno,e1.ename,d1.dname,e1.mgr,e2.ename,d1.dname 
from emp e1,emp e2,dept d1
where e1.deptno=d1.deptno and e1.mgr=e2.empno and
e2.deptno=d1.deptno;
/*
e1.deptno=d1.deptno和e2.deptno=d1.deptno综合起来就是：
e1.deptno=d1.deptno=e2.deptno，强制了员工和经理同一个部门。
*/

--4. 查询员工编号,员工姓名,员工的部门名称,员工的工资等级,经理的编号,
--经理的姓名,经理的部门名称
select e1.empno,e1.ename,d1.dname,g1.grade,e1.mgr,e2.ename,d2.dname from 
emp e1,emp e2,dept d1,dept d2,salgrade g1 where e1.deptno=d1.deptno and
e1.mgr=e2.empno and e2.deptno=d2.deptno and e1.sal between g1.losal 
and g1.hisal;

--****5. 查询员工编号,员工姓名,员工的部门名称,员工的工资等级,经理的编号,经理的姓名,经理的部门名称,经理的工资等级
select e1.empno,e1.ename,d1.dname,g1.grade,e1.mgr,e2.ename,d2.dname,g2.grade
from emp e1,emp e2,dept d1,dept d2,salgrade g1,salgrade g2 where e1.deptno=d1.deptno
and e1.mgr=e2.empno and e2.deptno=d2.deptno and e1.sal between 
g1.losal and g1.hisal and e2.sal between 
g2.losal and g2.hisal; 
/*求员工的工资等级和经理的工资等级时也要使用两张表，如果使用一张表会导致
强制使员工和经理工资等级一样*/


--*****6. 查询员工编号,员工姓名,员工部门名称,员工工资等级,经理编号,经理姓名,经理部门名称,经理工资等级，将工资等级 1,2,3,4 显示成一级 二级 三级...
--使用两种方式:case...when...和decode
select e1.empno,e1.ename,d1.dname,
case g1.grade
  when 1 then '一级'
  when 2 then '二级'
  when 3 then '三级'
  when 4 then '四级'
    else '五级'
      end "中文名",e1.mgr,e2.ename,d2.dname,
decode(g2.grade,1,'一级',2,'二级',3,'三级',4,'四级',5,'五级') "中文名"
from emp e1,emp e2,dept d1,dept d2,salgrade g1,salgrade g2 where e1.deptno=d1.deptno
and e1.mgr=e2.empno and e2.deptno=d2.deptno and e1.sal between g1.losal and
g1.hisal and e2.sal between g2.losal and g2.hisal;

--7. 查询员工姓名和员工部门所处的位置
--method1:隐式内连接之等值连接
select e1.ename,d1.loc from emp e1,dept d1 where e1.deptno=d1.deptno;
--method2:使用显式内连接
select e1.ename,d1.loc from emp e1 inner join dept d1 on e1.deptno=d1.deptno;

1.4 外连接

• 左外连接：left outer join on
  查询出左表的所有记录，如果右表没有对应记录，就显示空
• 右外连接：right outer join on
  查询出右表的所有记录，如果左表没有对应记录，就显示空
  outer关键字可以省略
• Oracle中的外连接：(+)：有(+)的表如果这张表没有对应的记录就加上空值；
  不加(+)的表显示的值是全的。

insert into emp(empno,ename) values(9527,'hahaha');--在emp表插入一张与dept表无关记录
select * from emp e1 left outer join dept d1 on e1.deptno=d1.deptno;
select * from emp e1 right outer join dept d1 on e1.deptno=d1.deptno;

select * from emp e1,dept d1 where e1.deptno=d1.deptno(+);
select * from emp e1,dept d1 where e1.deptno(+)=d1.deptno;

1.5 子查询

子查询：查询语句中嵌套查询语句，用于解决复杂的查询语句。

• 单行子查询：只返回一条记录。

​ 单行操作符> >= = < <= <> !=

• 多行子查询：返回多条记录。

  多行操作符号：in，not in，any，all， exists，not exists

1.5.1 单行子查询

--1. 查询最高工资的员工信息 
--Step1:查询出最高工资
select max(sal) from emp;--5000
--Step2:查询出工资等于5000的员工信息
select * from emp where sal=5000;
--Ans
select * from emp where sal=(select max(sal) from emp);

--2. 查询出比雇员7654的工资高,同时和7788从事相同工作的员工信息
--Step1:查询出7654员工的工资
select sal from emp where empno=7654;--1250
--Step2:查询出7788员工的工作
select job from emp where empno=7788;--ANALYST
--Step3:查询出工资大于1250，且工作为ANALYST的员工信息
select * from emp where sal>1250 and job='ANALYST'and empno<>7788;
--Ans
select * from emp where sal>(select sal from emp where empno=7654) and 
job=(select job from emp where empno=7788) and empno<>7788;

1.5.2 多行子查询

/*
in, not in, any, all, exists
exists:存在，判断一张表里的记录是否存在于另一张表中
    当布尔值来处理：有查询结果，返回true;否则返回false
    数据量大时比in要更高效
*/
--1. 查询领导信息(in)
--Step1:查询所有的经理编号(去重，过滤空值,虽然这里空值和重复并不影响结果)
select distinct mgr from emp where mgr is not null;
--Step2:查询empno在Step1的经理编号中
--Ans
select * from emp where empno in(select distinct mgr from emp where mgr is not null);

--2. 查询不是领导信息(not in)
select * from emp where empno not in(select distinct mgr from emp where mgr is not null);

--3. 查询出比10号部门任意一个员工薪资高的员工信息(any)
select * from emp where sal>any(select sal from emp where deptno=10);

--4. 查询出比20号部门所有员工薪资高的员工信息 (all)
select * from emp where sal>all(select sal from emp where deptno=20);
--method2:使用多行子查询
select * from emp where sal>(select max(sal) from emp where deptno=20);

--***5. 查询有员工的部门信息(用in和exists做)
--in
select * from dept where deptno in(select deptno from emp);
--exists
select * from dept d1 where exists(select * from emp e1 where e1.deptno=d1.deptno);
/*
in和exists的区别：
in:只执行一次
   执行流程(以上题为例)：
   先执行from dept找出dept表，再执行in里面的子查询得到deptno表，将dept表和
deptno表做一个笛卡儿积，再根据where条件筛选出结果。

exists:执行dept.length次
   执行流程(以上题为例)：
   先执行from dept，根据查询出的每一条记录，执行exists语句，如果返回true，
  则该行记录保留，如果返回false，则删除该行记录，保留的记录即为结果集。
*/

--***6. 查询每个部门最低工资的员工信息和他所在的部门信息
--Step1:查询每个部门的最低工资
select min(sal) minsal,deptno from emp group by deptno;
--Step2:查询对应员工信息
--把Step1的查询结果作为一张最低工资及部门表t1
select * from emp e1,(select min(sal) minsal,deptno from emp group by deptno) t1
where e1.sal=t1.minsal and e1.deptno=t1.deptno;
--Step3:查询所在部门信息
--Ans
select * from emp e1,dept d1,(select min(sal) minsal,deptno from emp group by deptno) t1
where e1.sal=t1.minsal and e1.deptno=t1.deptno and e1.deptno=d1.deptno;

1.5.3 关联子查询&非关联子查询

--找到员工表中薪水大于本部门平均工资的员工
--Step1:按部门查询平均工资
select avg(sal) avgsal,deptno from emp group by deptno;
--Step2:找到各部门大于平均工资的员工信息
--Ans1
/*
关联子查询：对于外部查询返回的每一行数据，内部查询都要执行一次。
在关联子查询中信息流是双向的，外部查询的每行数据传递给子查询，
子查询为每一行数据执行一次并返回它的记录。
最后，外部查询根据返回的记录做出决策。
先执行外层查询，再执行内层查询。
*/
select * from emp e1,(select avg(sal) avgsal,deptno from emp group by deptno) a1 
where e1.deptno=a1.deptno and e1.sal>a1.avgsal;

--Ans2
/*
非关联子查询:非关联子查询是独立于外部查询的子查询，子查询执行完毕后将值传递给外部查询
先执行内层查询，再执行外层查询。
*/
select * from emp e1 where e1.sal>(select avg(sal) from emp e2 group by deptno
having e1.deptno=e2.deptno);

1.6 rownum&rowid

• rownum:伪列，系统自动生成的一列，用来表示行号，oracle特有的，默认值/起始值为1，
  再每查询出一条结果后，才给rownum 赋值，此后每条记录的rownum依次加1。每张表中都有该列
• rowid:伪列，表示每行记录所存放的真实物理地址，每张表中都有该列

--rownum--
--找到员工表中工资最高的前三名(降序排序)
select rownum,e1.* from emp e1 where rownum<=3 order by sal desc;
/*
查询出的表中，rownum并不是按工资顺序来排序的，不妥；此句中rownum在from emp e1
where rownum<=3之后被赋值，而并不是在order by之后被赋值，所以导致rownum无序
*/
--Ans
select rownum,s1.* from (select * from emp e1 order by sal desc) s1 where rownum <=3;

--1. 查询rownum大于2的所有记录 
select rownum,e1.* from emp e1 where rownum >2; --null
--查询结果为空，还是因为rownum是查询出结果之后才赋值的
--查询rownum大于等于1的所有记录 
select rownum,e1.* from emp e1 where rownum >=1; --所有记录

--2. 使用rownum做分页查询，在oracle中只能用子查询来做分页查询
--查询第6-10条记录
select * from (select rownum,emp.* from emp) t1 where t1.rownum between 6 and 10;--错误
select * from (select rownum r,emp.* from emp) t1 where t1.r between 6 and 10;--Ans
--不知道为什么这里rownum要取别名才能调用

--rowid--
--利用rowid去除表中重复记录，保留rowid较小的记录
create table p(
       name varchar2(10)
/*varchar2是oracle特有的数据类型，与varchar的区别在于：varchar存储为定长，
varchar2存储为不定长。例：varchar(10),存储这个字段时，无论有没有10个字符，
都存储10个字符，不足部分补足空格；varchar2(10)则按实际值来存储，不补足空格。
*/
);
--这三条插入语句执行两次
insert into p values('aaa');
insert into p values('bbb');
insert into p values('ccc');
--Ans
delete from p p1 where rowid>(select min(rowid) from p p2 where p1.name=p2.name);
select rowid,p.* from p;

1.7 集合运算

​ 集合运算：并集，交集，差集
​ 查询结果可能不是来自同一张表

--并集运算：union,union all
--工资大于1500,或者20号部门下的员工
--union:去除重复的，并且排序
select * from emp where sal>1500 union select * from emp where deptno=20;
--union all:不会去除重复的
select * from emp where sal>1500 union all select * from emp where deptno=20;

--交集运算：intersect
--工资大于1500且20号部门下的员工
select * from emp where sal>1500 intersect select * from emp where deptno=20;

--差集运算:minus
--查询1981年入职的员工，不包括president和manager
select * from emp where to_char(hiredate,'yyyy') =1981 minus select * from emp
where job='president' or job='manager';
/*
集合运算的注意事项：
  select ename,sal from emp where sal>1500 
  union 
  select ename,sal from emp where deptno=20;
1.列的类型一致
  两部分ename,sal数据类型要一致   
2.按照顺序编写
  两部分ename,sal编写顺序要一致
3.列的数量一致，如果不足，用空值填充
  第一部分有ename,sal，第而部分也要有，如果不足，用null代替
*/

1.8 变态例题

--统计每年入职的员工个数
--Step1:只显示年
select to_char(hiredate,'yyyy') from emp;
--Step2:按年份分组统计
--Ans
select to_char(hiredate,'yyyy') yyyy,count(1) count from emp e1 group by to_char(hiredate,'yyyy');
--*****将竖向排列的查询结果改造成如下形式
/*
Total    1980    1981    1982    1987    
14        1       10      1        2       
*/
--Ans
select sum(count) total,
  sum(case yyyy when '1980' then count end) "1980",
  sum(case yyyy when '1981' then count end) "1981",
  sum(case yyyy when '1982' then count end) "1982",
  sum(case yyyy when '1987' then count end) "1987"
from (select to_char(hiredate,'yyyy') yyyy,count(1) count from emp e1 group by to_char(hiredate,'yyyy'));