多表查询

1.笛卡尔积

笛卡尔积就是两个表的乘积，如emp表是14条记录，dept表示4条记录，通过select * from emp，dept；得到的结果是56条记录

2.内连接

2.1 隐式内连接

eg：查询员工信息和部门信息

select * from emp e , dept d where e.deptno=d.deptno;

2.2 显示内连接

select * from A inner join B on A.列=B.列

eg：查询员工信息和部门信息

select * from emp inner join dept on emp.deptno=d.deptno;

3.外连接

3.1 左外连接

select * from A left join B on A.列=B.列 ; 以左表为基准，左表数据全部显示 右表数据作为补充显示，没有数据显示空

eg：查询部门信息和部门下的员工信息 没有员工的部门也要显示

select * from dept left join emp on dept.deptno=emp.deptno;

3.2 右外连接

select * from B right join A on A.列=B.列; 以右表为基准，右表数据全部显示，左表数据作为补充显示，没有数据显示空

eg：查询部门信息和部门下的员工信息 ，没有员工的部门也要显示

select * from emp right join dept on emp.deptno=dept.deptno;

3.3 oracle数据库特有的外连接

使用符号 (+) 可以实现外连接 符号的放置位置 作为补充显示数据的表的列后面，跟等号左右无关

eg：使用(+)，实现查询部门信息和部门下的员工信息，没有员工的部门也要显示

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

4.自连接

select * from A A1,A A2 where A1.列=A2.列

eg：查询员工的信息和员工的领导信息

select * from emp e,emp m where e.mgr=m.empno;

5.多表联查

1.查询员工的信息和员工的领导信息

select e.empno,e.name,e.job,e.hiredate,e.sal,e.comm,e.mgr 领导编号,m.ename 领导姓名
from emp e,emp m where e.mgr=m.empno;

2.在上面的基础上查询员工的部门信息

select e.empno,e.name,e.job,e.hiredate,e.sal,e.comm,d.dname,e.mgr 领导编号,m.ename 领导姓名
from emp e,emp m ,dept d where e.mgr=m.empno and e.deptno=d.deptno;

3.在上面的基础上查询员工的工资等级

select e.empno,e.name,e.job,e.hiredate,e.sal,e.comm,d.dname,s1.grade 员工工资等级,
e.mgr 领导编号,m.ename 领导姓名
from emp e,emp m ,dept d ,salgrade s1
where e.mgr=m.empno and e.deptno=d.deptno
and e.sal between s1.losal and s1.hisal;

4.在上面的基础上，再查询领导的工资等级

select e.empno,e.name,e.job,e.hiredate,e.sal,e.comm,d.dname,s1.grade 员工工资等级,
e.mgr 领导编号,m.ename 领导姓名,s2.grade 领导工资等级
from emp e,emp m ,dept d ,salgrade s1 ,salgrade s2
where e.mgr=m.empno and e.deptno=d.deptno
and e.sal between s1.losal and s1.hisal
and m.sal between s2.losal and s2.hisal;

结合decode

select e.empno,e.ename,e.job,e.hiredate,e.sal,e.comm,d.dname,
decode(s1.grade,1,'一级',
2,'二级',
3,'三级',
4,'四级',
5,'五级') 员工工资等级,
e.mgr as 领导编号,m.ename 领导姓名,
decode(s2.grade,1,'一级',
2,'二级',
3,'三级',
4,'四级',
5,'五级') 领导工资等级 
from emp e,emp m ,dept d ,salgrade s1,salgrade s2
where e.mgr = m.empno and e.deptno=d.deptno
and e.sal between s1.losal and s1.hisal
and m.sal between s2.losal and s2.hisal;

6.子查询

子查询（内查询）在主查询之前一次执行完成。子查询的结果被主查询使用（外查询）.

子查询的关键字有in ，exists，any，all。in表示值是否存在于子查询结果集中，exists表示子查询是否返回结果，而不管返回的具体内容。any表示子查询结果中的任意一个，all表示子查询结果中的所有。

in相当于=any，not in 相当于<>all。>any表示只要大于子查询结果找那个的任一个，表达式就成立，=any表示等于子查询中的任意一个，相当于in。>all表示要大于子查询结果中的所有，才会返回true。in在子查询不返回数据的时候，为false，子查询找结果中有null的时候，null不会用于比较。any同样在子查询不反悔数据的时候，为false，子查询结果中有null的时候，null不会用于比较。all在子查询不返回数据的时候，为true，子查询结果中有null 的时候，不会返回数据。<>any表示只要不等于其中的任意一个就成立。

运行效率。子查询的运行效率不如连表查询，所以要尽量避免进行子查询。使用子查询的地方改为内联，外联。in和exists效率比较。in是把内表和外表做hash连接，而exists是对外表进行loop循环，每次loop循环之后再对内表进行查询，所以，如果两个表差不多大小，in和exists效率差不多。

如果一个表大，一个表小，子查询中表大的用exists，子查询中表小的用in。例如表A为小表，表B为大表，select * from A where cc in (select cc from B),由于用到了A表上cc的索引，因此效率较低；select * from A where exists(select cc from B where cc=A.cc),由于用到了B表cc索引，因此效率高。相反的，select * from B where cc in (select cc from A)​，由于用到了B表中cc索引，因此效率高；​select * from B where exists(select cc from A where cc=B.cc)，由于用到了​A表中的索引，因此效率低；

not in和not exists

如果查询语句用到了not in​，那么内外表都要进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists 都比not in 要快。 待验证

6.1 单行子查询

• 只返回一条记录

select * from A where A.列=(select 语句返回唯一的值)

• 单行操作符

= > >= < <= <>

• eg:

1.查询出和编号是7566一样工作，工资比7782高的员工姓名，工作，工资

select ename,job ,sal from emp where job =(select job from emp where empno=7566)
and sal>(select sal from emp where empno =7782);

2.查询最少工资的员工姓名，工作，工资

select ename, job ,sal from emp where sal = (select min(sal) from emp);

3.查询出每个部门的最低工资，显示比部门编号为20最低工资高的部门编号，部门最低工资

select deptno ,min(sal) from emp group by deptno having min(sal) > (select min(sal) from
emp where deptno=20);

6.2 多行子查询

返回多条记录 多行操作符 in exists any all

select * from A where A.列 in (select 语句返回一列多个值)

select * from A ,(sql语句返回多行多列作为临时表) t where A.列 = t.列

eg：

查询出每个部门的最低工资和最低工资的雇员和部门名称

select d.dname, a.minsal, e.ename from dept d inner join (select deptno, min(sal) minsal
from emp group by deptno) a on d.deptno = a.deptno left join emp e on e.sal = a.minsal;

6.3 子查询的null值问题

查询不是领导的员工信息

select * from emp where empno not in (select mgr from emp); 因为子查询中有空值，所以此语句无结果

解决空值问题

select * from emp where empno not in (select mgr from emp where mgr is not null);

或select * from emp where empno not in (select nvl(mgr,0) from emp);

6.4 子查询的特殊使用exists

exists存在，用来判断结果集是否存在，存在返回true，不存在返回false

eg：

1. select * from emp where exists (select * from dept) --全部的emp表记录

select * from where exists (select * from dept where deptno=1234) --没有结果

2. 查询有员工的部门信息

select * from dept where deptno in (select deptno from emp)

使用exists实现

select * from dept where exists (select * from emp where emp.deptno=dept.deptno)

7.Oracle的分页查询

rownum：表示行号，实际上只是一个列，但是这个列是一个伪列，此列可以在每张表找那个出现

rowid ：表示每行数据指向磁盘上的物理地址

eg:

1. select rownum,emp.* from emp;

2. select rownum ,emp.* from emp where rownum>5 --没有任何记录

select rownum ,emp.* from emp where rownum<5 --前四条记录

select rownum,emp.* from emp where rownum =1 --第一条记录

select rownum,emp.* from emp where rownum>0 --所有记录 >1 所有记录

select * from (select * from emp order by dbms_random.value) where rownum<5 --随机4条记录

3. 如果想用rownum做大于的条件过滤，先把所有的rownum生成出来，在结果集之上判断

select * from (select rownum r,emp.* from emp ) t where t.r>5               --查询5条以后的记录

4. 查询员工表中工资前三名的员工信息

select rownum ,t.* from (select * from emp order by sal desc ) t where rownum<4

5. 统计每年入职的员工人数

select to_char(hiredate,'yyyy') hire_year ,count(*) hire_count from emp group by to_char(hiredate ,'yyyy')

显示如右图样式

--对结果集做处理 转化格式

--1.先尝试竖起来一列

select decode(t.hire_year,'1987',t.hire_count) "1987"
from (select to_char(hiredate, 'yyyy') hire_year, count(*) hire_count
from emp
group by to_char(hiredate, 'yyyy')) t 

--2.取出空值的记录，聚合函数的特点，都忽略空值记录

select avg(decode(t.hire_year,'1987',t.hire_count)) "1987" from 
(select to_char(hiredate, 'yyyy') hire_year, count(*) hire_count
from emp
group by to_char(hiredate, 'yyyy')) t 

--3.补充其余的列

select avg(decode(t.hire_year,'1987',t.hire_count)) "1987", 
avg(decode(t.hire_year,'1982',t.hire_count)) "1982" ,
avg(decode(t.hire_year,'1981',t.hire_count)) "1981",
avg(decode(t.hire_year,'1980',t.hire_count)) "1980"
from 
(select to_char(hiredate, 'yyyy') hire_year, count(*) hire_count
from emp
group by to_char(hiredate, 'yyyy')) t 

--4.补充上total

select sum(t.hire_count) total,
avg(decode(t.hire_year,'1987',t.hire_count)) "1987", 
avg(decode(t.hire_year,'1982',t.hire_count)) "1982" ,
avg(decode(t.hire_year,'1981',t.hire_count)) "1981",
avg(decode(t.hire_year,'1980',t.hire_count)) "1980"
from 
(select to_char(hiredate, 'yyyy') hire_year, count(*) hire_count
from emp
group by to_char(hiredate, 'yyyy')) t 

8.集合运算

8.1 交集 intersect

取两个集合共同的部分 A (1,2,3) B(2,3,4) intersect 交集结果（2,3）

8.2 并集 union

取两个集合最大的范围 A（1,2,3） B (2,3,4) union 并集结果(1,2,3,4)

union all 并集结果 (1,2,3,2,3,4)

8.3 差集 minus except

从一个集合去掉另一个集合剩余的部分 A(1,2,3) B(2,3,4) minus A差B结果(1)

eg: --工资大于1500,或者是20部门下的员工

select * from emp where sal>1500 or deptno=20

--使用集合实现

select * from emp where sal>1500
union
select * from emp where deptno=20

--包含重复记录

select * from emp where sal>1500
union all
select * from emp where deptno=20

--工资大于1500,并且是20部门下的员工

select * from emp where sal>1500 and deptno=20

--使用集合实现

select * from emp where sal>1500
intersect
select * from emp where deptno=20

--1981年入职的普通员工（不包括经理，总裁）

select * from emp where to_char(hiredate,'yyyy') ='1981'
and job not in ('MANAGER','PRESIDENT')

--使用集合实现

select * from emp where to_char(hiredate,'yyyy') ='1981'
minus 
select * from emp where job in ('MANAGER','PRESIDENT')

8.4 应用场景

跨表做数据合并使用

使用规则 如果数据合并 必须满足 列的数量一致， 类型一致。