多表查
交叉连接 cross join
一般不用（因为无用信息太多）
1
select * from emp cross join dept -- 依次从m行的emp中取一行emp，和n行dept中的每一行进行组合，也就是会产生一个nxm行的表格
1
2
3
4
-- 根据以上结合的结果，如何查找到Smith的人所在的部门名称
select emp.ename, dept.deptno, dept.dname from emp
cross join dept
where emp.deptno = dept.deptno and emp.ename = 'SMITH';
缺点：
- 直接叉乘，得到的是全部集合（数据量太大）
- 然后才能where进行条件运算（性能太差）
内连接 inner join
可简写为join
1
select * from emp inner join dept on '条件语句'
1
2
3
4
5
6
7
8
-- 查找MANAGER岗位的所有员工，包括编号、姓名、部门号、部门名称
select emp.empno, emp.ename, dept.deptno, dept.dname
from emp inner join dept
on emp.deptno = dept.deptno and emp.job = 'MANAGER'
-- 查找reserch部门的所有员工的信息
select dept.dname, emp.empno, emp.ename, emp.job, emp.mgr, emp.hiredate, emp.sal, emp.comm, emp.deptno
from emp inner join dept
on dept.dname = upper('research') and emp.deptno = dept.deptno;
非等值连接
1
2
3
4
5
6
7
8
9
10
-- 显示一个人的薪水等级
select e.ename, e.sal, s.grade from emp e
join salgrade s
on e.sal >= s.losal and e.sal <= s.hisal
order by s.grade ASC;
select e.ename, e.sal, s.grade from emp e
join salgrade s
on e.sal between s.losal and s.hisal
order by s.grade ASC;
1
2
3
4
5
6
-- 显示员工完整信息，包括部门名称
select e.*, d.* from emp e
join salgrade s
on sal between losal and hisal
join dept d
on dept.deptno = emp.deptno;
1
2
3
4
5
-- 统计每一个工资等级的员工人数
select grade, count(e.empno) from emp e
join salgrade s
on e.sal between s.losal and s.hisal
group by grade;
自连接
1
2
3
4
5
-- 查找员工与其上级的编号，姓名
select e1.empno, e1.ename, e2.empno, e2.ename from emp e1
join emp e2
on e1.mgr = e2.empno;
-- 可以发现，结果中少了boss的信息
连接（等值连接、非等值连接、自连接）中，一张表离得记录必须得再另一张表中存在匹配的记录，否则不能出现在结果集合中
外连接
-
Left \ Rigth \ Full outer join
1 2 3 4
-- 查找所有职工，并显示出其上级的姓名、编号 select * from emp e1 left outer join emp e2 on e1.mgr = e2.empno
对于左连接的理解：
-
先按内连接查出所有数据并显示（能满足on的条件都显示出来）
-
如果左边无法根据 on 匹配出相应的右边的数据，也要显示出来，但右边会写空
1 2 3 4 5
-- 查询所有部门的人数清单，部门编号，名称，人数 select d.deptno, d.dname, nvl(T.cnt, 0) from dept d left outer join (select e.deptno, count(e.empno) cnt from emp e group by e.deptno) T on d.detpno = T.deptno
1 2 3 4 5 6 7 8 9 10 11 12
-- 哪些员工没有下属（不是别人领导） -- 外连接 + 匹配表 pk is null 表示否定问题，不是，不包括，等 -- 右连接： select e2.empno, e2.ename from emp e1 right outer join emp e2 on e1.mgr = e2.empno where e1.empno is null -- 左连接： select e1.empno, e1.ename from emp e1 left outer join emp e2 on e2.mgr = e1.empno where e2.empno is null
1 2 3 4 5
-- 查出员工里没有叫SMITH的部门 select * from dept d left outer join emp e on e.deptno = d.deptno and e.ename = 'SMITH' where e.empno is null;
-
课后问题
1
2
3
4
-- 每一个部门的收入大户,收入是多少，精确到个人
select e.deptno, e.deptno, e.ename, T.maxsal from emp e
join (select e.deptno, max(e.sal) maxsal from emp e group by e.deptno) T
on e.deptno = T.deptno and e.sal = T.maxsal;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 求平均薪水大于1600的部门号，部门名称
-- 在外面筛选
select d.deptno, d.dname from dept d
join (select e.deptno, avg(e.sal) avgsal from emp e group by e.deptno) T
on d.deptno = T.deptno and T.avgsal > 1600;
-- 在里面筛选
select d.deptno, d.dname from dept d
join (
select e.deptno, avg(e.sal)
from emp e group by e.deptno
having avg(e.sal)>1600
) T
on d.deptno = T.deptno;