（十二）多表查询
理论基础：笛卡尔积
笛卡尔积的行数 = table1的行数 x table2的行数
笛卡尔积的列数 = table1的列数 + table2的列数
在操作笛卡尔积的时候，应避免使用“笛卡尔全集”，因为里面含有大量错误信息
多表查询就是按照给定的条件（连接条件），从笛卡尔全集中挑选出正确的结果
跟据连接条件不同，可以分为：等值连接，不等值连接，外连接，自连接
1.等值连接 Equijion
where子句后面的条件是 “ = ” ，为等值连接，不是 “ = ” 为不等值连接
查询员工信息：员工号，姓名，月薪，和部门名称
这个问题涉及到两张表，emp，dept——即为多表查询
通常在进行多表查询是时候，会给表起一个别名，使用 “别名.列名” 的方式来获取数据，直接使用 “表名.列名” 语法上是允许的，但是实际很少用，因为表名太长
select e.empno,e.ename,e.sal,d.dname from emp e,dept d 得到笛卡尔积，其中有很多错误的信息，所以应该加 where 条件进行过滤
1* select e.empno,e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno SQL> / EMPNO ENAME SAL DNAME ---------- ---------- ---------- -------------- 7369 SMITH 800 RESEARCH 7499 ALLEN 1600 SALES 7521 WARD 1250 SALES 7566 JONES 2975 RESEARCH 7654 MARTIN 1250 SALES 7698 BLAKE 2850 SALES 7782 CLARK 2450 ACCOUNTING 7788 SCOTT 3000 RESEARCH 7839 KING 5000 ACCOUNTING 7844 TURNER 1500 SALES 7876 ADAMS 1100 RESEARCH 7900 JAMES 950 SALES 7902 FORD 3000 RESEARCH 7934 MILLER 1300 ACCOUNTING 已选择14行。 已用时间: 00: 00: 00.06
如果有N个表，where后面的条件至少应该有N-1个
2.不等值连接
查询员工信息：员工号，姓名，月薪，和薪水级别
SQL> select * from salgrade; GRADE LOSAL HISAL ---------- ---------- ---------- 1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999 已用时间: 00: 00: 00.03 SQL>
SQL> select e.empno,e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and hisal; EMPNO ENAME SAL GRADE ---------- ---------- ---------- ---------- 7369 SMITH 800 1 7900 JAMES 950 1 7876 ADAMS 1100 1 7521 WARD 1250 2 7654 MARTIN 1250 2 7934 MILLER 1300 2 7844 TURNER 1500 3 7499 ALLEN 1600 3 7782 CLARK 2450 4 7698 BLAKE 2850 4 7566 JONES 2975 4 7788 SCOTT 3000 4 7902 FORD 3000 4 7839 KING 5000 5 已选择14行。 已用时间: 00: 00: 00.05 SQL>
3.外连接
按部门统计员工人数：显示部门号，部门名称，人数
SQL> ed 已写入 file afiedt.buf 1 select d.deptno,d.dname,count(e.empno) 2 from dept d,emp e 3 where d.deptno = e.deptno 4* group by d.deptno,d.dname SQL> / DEPTNO DNAME COUNT(E.EMPNO) ---------- -------------- -------------- 10 ACCOUNTING 3 20 RESEARCH 5 30 SALES 6 已用时间: 00: 00: 00.06 SQL>
由于使用了组函数，所以组函数外的d.deptno,d.dname必须放到group by之后
结果显示40号部门没有出来，
我们希望在最后的结果中，包含某些对于where条件来说不成立的记录（外连接的作用）
SQL> ed 已写入 file afiedt.buf 1 select d.deptno,d.dname,count(e.empno) 2 from dept d,emp e 3 where d.deptno = e.deptno(+) 4 group by d.deptno,d.dname 5* order by d.deptno SQL> / DEPTNO DNAME COUNT(E.EMPNO) ---------- -------------- -------------- 10 ACCOUNTING 3 20 RESEARCH 5 30 SALES 6 40 OPERATIONS 0 已用时间: 00: 00: 00.01 SQL>
左外连接：当 where d.deptno = e.deptno 不成立的时候， = 左边所表示的信息，仍被包含
写法：与叫法相反，where d.deptno = e.deptno(+)
右外连接：。。。
4.自连接
核心：通过表的别名，将同一张表视为多张表。
查询员工信息：xxx的老板是yyy
1* select e.ename || ' 的老板是 '|| p.ename from emp e,emp p where e.mgr=p.empno SQL> / E.ENAME||'的老板是'||P.ENAME ------------------------------ FORD 的老板是 JONES SCOTT 的老板是 JONES JAMES 的老板是 BLAKE TURNER 的老板是 BLAKE MARTIN 的老板是 BLAKE WARD 的老板是 BLAKE ALLEN 的老板是 BLAKE MILLER 的老板是 CLARK ADAMS 的老板是 SCOTT CLARK 的老板是 KING BLAKE 的老板是 KING JONES 的老板是 KING SMITH 的老板是 FORD 已选择13行。 已用时间: 00: 00: 00.08
但是KING没有显示出来
SQL> select e.ename || ' 的老板是 '|| p.ename from emp e,emp p where e.mgr=p.empno(+); E.ENAME||'的老板是'||P.ENAME ------------------------------ FORD 的老板是 JONES SCOTT 的老板是 JONES JAMES 的老板是 BLAKE TURNER 的老板是 BLAKE MARTIN 的老板是 BLAKE WARD 的老板是 BLAKE ALLEN 的老板是 BLAKE MILLER 的老板是 CLARK ADAMS 的老板是 SCOTT CLARK 的老板是 KING BLAKE 的老板是 KING JONES 的老板是 KING SMITH 的老板是 FORD KING 的老板是 已选择14行。 已用时间: 00: 00: 00.08 SQL>
SQL> select e.ename || ' 的老板是 '|| nvl(p.ename,'null') from emp e,emp p where e.mgr=p.empno(+); E.ENAME||'的老板是'||NVL(P.ENA ------------------------------ FORD 的老板是 JONES SCOTT 的老板是 JONES JAMES 的老板是 BLAKE TURNER 的老板是 BLAKE MARTIN 的老板是 BLAKE WARD 的老板是 BLAKE ALLEN 的老板是 BLAKE MILLER 的老板是 CLARK ADAMS 的老板是 SCOTT CLARK 的老板是 KING BLAKE 的老板是 KING JONES 的老板是 KING SMITH 的老板是 FORD KING 的老板是 null 已选择14行。 已用时间: 00: 00: 00.06 SQL>
5.层级查询
以上自连接存在一个问题，我们将一张表视为两张表来操作，会产生笛卡尔积问题，所以子连接效率不是最好的
根据SQL语句画出员工老板关系图，发现是一个树状结构：
由于这种树状结构，我们提出层次查询的概念，取代操作大表时，自连接可能带来的问题
level伪列，是orcale数据库帮助我们在表中添加的隐性剂，查则有，不查则无
关键：前一层的员工号 = 下一层员工的老板号
connect by prior empno=mgr (prior指定前一层)
遍历一棵树，应该指定起始点（start with）
start with mgr is null （King之上再没有老板了）
SQL> ed 已写入 file afiedt.buf 1 select level,empno,ename,mgr from emp 2 connect by prior empno=mgr 3 start with mgr is null 4* order by 1 SQL> / LEVEL EMPNO ENAME MGR ---------- ---------- ---------- ---------- 1 7839 KING 2 7566 JONES 7839 2 7698 BLAKE 7839 2 7782 CLARK 7839 3 7902 FORD 7566 3 7521 WARD 7698 3 7900 JAMES 7698 3 7934 MILLER 7782 3 7499 ALLEN 7698 3 7788 SCOTT 7566 3 7654 MARTIN 7698 3 7844 TURNER 7698 4 7876 ADAMS 7788 4 7369 SMITH 7902 已选择14行。 已用时间: 00: 00: 00.10 SQL>
总结：层次查询使用的场景一定只有一张表，同一张表的前后多次操作，进行连接，避免笛卡尔积问题
缺点：不直观
