Oracle学习(五)----多表查询

select * from table1, table2
where ......
order by ......
group by ......
having ......

多表查询(重点)

1 笛卡尔积基本原理

  两个表进行笛卡尔积运算，结果是列数相加，行数相乘。

  例：1
	SQL> select count(e.ename) from emp e, dept d;

	COUNT(E.ENAME)
	--------------
            	60

  例2：

	SQL> select count(e.ename) from emp e, dept d where e.deptno = d.deptno;

	COUNT(E.ENAME)
	--------------
            1	5

2 等值连接

  例：查询员工信息(在表emp中)，员工号，姓名，月薪，部门名称(在表dept中)
  思路：从两个表中获得数据，需要用到等值连接

	SQL> select e.ename, e.empno, e.ename, e.sal, d.dname from emp e, dept d
  	2  where e.deptno = d.deptno;

	ENAME           EMPNO ENAME             SAL DNAME
	---------- ---------- ---------- ---------- --------------
	tom_abc             1 tom_abc          8000 ACCOUNTING
	MILLER           7934 MILLER           1300 ACCOUNTING
	KING             7839 KING             5000 ACCOUNTING
	CLARK            7782 CLARK            2450 ACCOUNTING
	JONES            7566 JONES            2975 RESEARCH
	SMITH            7369 SMITH             800 RESEARCH
	FORD             7902 FORD             3000 RESEARCH
	ADAMS            7876 ADAMS            1100 RESEARCH
	SCOTT            7788 SCOTT            3000 RESEARCH
	ALLEN            7499 ALLEN            1600 SALES
	TURNER           7844 TURNER           1500 SALES
	JAMES            7900 JAMES             950 SALES
	WARD             7521 WARD             1250 SALES
	BLAKE            7698 BLAKE            2850 SALES
	MARTIN           7654 MARTIN           1250 SALES

	已选择15行。

3 不等值连接

  表salgrade中的内容：

	SQL> select * from salgrade;

     	GRADE      LOSAL      HISAL
	---------- ---------- ----------
         	1        700       1200
         	2       1201       1400
         	3       1401       2000
         	4       2001       3000
         	5       3001       9999

  例：查询员工信息，员工号，姓名，月薪，薪水级别(在表salgrade中)

	SQL> select e.ename, e.empno, e.ename, e.sal, s.grade from emp e, salgrade s
  	2  where e.sal>=s.losal and e.sal<=s.hisal;

	ENAME           EMPNO ENAME             SAL      GRADE
	---------- ---------- ---------- ---------- ----------
	SMITH            7369 SMITH             800          1
	JAMES            7900 JAMES             950          1
	ADAMS            7876 ADAMS            1100          1
	WARD             7521 WARD             1250          2
	MARTIN           7654 MARTIN           1250          2
	MILLER           7934 MILLER           1300          2
	TURNER           7844 TURNER           1500          3
	ALLEN            7499 ALLEN            1600          3
	CLARK            7782 CLARK            2450          4
	BLAKE            7698 BLAKE            2850          4
	JONES            7566 JONES            2975          4
	FORD             7902 FORD             3000          4
	SCOTT            7788 SCOTT            3000          4
	KING             7839 KING             5000          5
	tom_abc             1 tom_abc          8000          5

已选择15行。

4 外连接
  按部门统计员工人数：部门号 部门名称 各部门人数

	SQL> select * from dept;

    	DEPTNO DNAME          LOC
	---------- -------------- -------------
        	10 ACCOUNTING     NEW YORK
        	20 RESEARCH       DALLAS
        	30 SALES          CHICAGO
        	40 OPERATIONS     BOSTON


  思路一：求各个部门的人数...要把各个部门数据划分开...要分组

	已写入 file afiedt.buf

  	1  select d.deptno, d.dname, count(e.empno) from emp e, dept d
  	2  where d.deptno = e.deptno
  	3* group by d.deptno, d.dname
	SQL> /

    	DEPTNO DNAME          COUNT(E.EMPNO)
	---------- -------------- --------------
        	10 ACCOUNTING                  4
        	20 RESEARCH                    5
        	30 SALES                       6

  我们的期望 想把40号部门统计出来......
  40号部门没有被统计出来的原因：员工表中没有40号部门的员工,where处的条件造成了这种结果
  本质：等值条件不满足
  ========>这就是外连接技术出现的原因

  +左外连接：+号在...=号右边，左外连接....
  -右外连接：+号在...=号左边，右外连接....

     我想把部门编号的所有列都显示出来，就需要找到部门表...在对方写（+）

	SQL> select d.deptno, d.dname, count(e.empno) from emp e, dept d
  	2  where d.deptno = e.deptno (+)
  	3  group by d.deptno, d.dname;

    	DEPTNO DNAME          COUNT(E.EMPNO)
	---------- -------------- --------------
        	10 ACCOUNTING                  4
        	40 OPERATIONS                  0
        	20 RESEARCH                    5
        	30 SALES                       6

5 自连接

  5.1：

	SQL> select e.ename || '的老板是'||b.ename  "boss 查询" from emp e, emp b
  	2  where e.mgr = b.empno;

	boss 查询
	----------------------------
	FORD的老板是JONES
	SCOTT的老板是JONES
	TURNER的老板是BLAKE
	ALLEN的老板是BLAKE
	WARD的老板是BLAKE
	JAMES的老板是BLAKE
	MARTIN的老板是BLAKE
	MILLER的老板是CLARK
	ADAMS的老板是SCOTT
	BLAKE的老板是KING
	JONES的老板是KING
	CLARK的老板是KING
	SMITH的老板是FORD

	已选择13行。

  5.2：显示king

	SQL> select e.ename || '的老板是'||b.ename  "boss 查询" from emp e, emp b
 	 2  where e.mgr = b.empno (+);

	boss 查询
	----------------------------
	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的老板是
	tom_abc的老板是

	已选择15行。

  5.3：显示king的老板是他自己

	SQL> select e.ename || '的老板是'||nvl(b.ename, '他自己')  "boss 查询" from emp e, emp b
  	2  where e.mgr = b.empno (+);

	boss 查询
	----------------------------
	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的老板是他自己
	tom_abc的老板是他自己