SQL> --在了解多表查询之前，我们首先要了解一下笛卡儿积。
SQL> --在之前的数据库课程中，我们学习到，笛卡儿积就是所有可能的结果集合
SQL> --那么里面的结果是把所有可能的结果列举出来，其中很多数据是错误的
SQL> --那么我们要使用连接条件来得到我们想要的正确信息
SQL> --在多表查询中，我们主要学习的就是连接条件
SQL> --连接条件分为等值/不等值连接，外连接，自连接
SQL> --等值连接的例子：查询员工号，姓名，薪水，部门
SQL> select e.empno,e.ename,e.sal,d.deptno
2 from emp e,dept d
3 where e.deptno=d.deptno;
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7369 SMITH 800 20
7499 ALLEN 1600 30
7521 WARD 1250 30
7566 JONES 2975 20
7654 MARTIN 1250 30
7698 BLAKE 2850 30
7782 CLARK 2450 10
7788 SCOTT 3000 20
7839 KING 5000 10
7844 TURNER 1500 30
7876 ADAMS 1100 20
7900 JAMES 950 30
7902 FORD 3000 20
7934 MILLER 1300 10
已选择14行。
SQL> --在上面的例子中，我们给两张表定义了一个别名，然后在调用其里面的列名时，可以直接引用
SQL> --不等值连接：在多表连接的连接条件中不是等号
SQL> --查询员工的编号，名字，薪水，薪水级别
SQL> --先把员工的薪水级别找出来
SQL> select * from salgrade;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
SQL> --从查找的结果可以看到，每一个等级都对应有上限和下限。
SQL> --所以当某一个员工的薪水在某个范围以内的时候，就是属于这个级别的
SQL> select e.empno,e.ename,e.sal,s.grade
2 from emp e,salgrade s
3 where e.sal between 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行。
SQL> --上面的losal和hisal忘记写别名了
SQL> ed
已写入 file afiedt.buf
1 select e.empno,e.ename,e.sal,s.grade
2 from emp e,salgrade s
3* where e.sal between s.losal and s.hisal
SQL> /
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行。
SQL> --外连接
SQL> --查找每个部门各有多少人
SQL> --查询各部门的部门号 部门名称 人数
SQL> select d.deptno 部门号,d.dname 部门名称,count(e.deptno) 人数
2 from emp e,dept d
3 where e.deptno=d.deptno
4 group by d.deptno,d.dname;
部门号 部门名称 人数
---------- -------------- ----------
10 ACCOUNTING 3
20 RESEARCH 5
30 SALES 6
SQL> --分析上面的例子：上面的例子，其实就是等值连接，因为把有些没有满足条件的记录过滤掉了
SQL> select *
2 from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> --其实dept表中，有4条数据，但是在查询的结果中，并没有显示部门编号为40的人数为0
SQL> --所以要显示不满足条件的记录的连接，叫外连接
SQL> --但是大家都知道，外连接分为左外连接和右外连接
SQL> --左外连接就是显示左表中不符合条件的记录
SQL> --右外连接就是显示右表中不符合条件的记录
SQL> --其中左外连接跟右外连接的用法是直接加上(+)
SQL> --左外连接在右表加，右外连接在左表的名字后边加
SQL> --具体用法如下
SQL> --考虑到我们上面例子的需求，我们是需要使用右表的所有数据的。
SQL> select d.deptno 部门号,d.dname 部门名称,count(e.deptno) 人数
2 from emp e,dept d
3 where e.deptno(+)=d.deptno
4 group by d.deptno,d.dname;
部门号 部门名称 人数
---------- -------------- ----------
10 ACCOUNTING 3
40 OPERATIONS 0
20 RESEARCH 5
30 SALES 6
SQL> --我们排一下序
SQL>
SQL>
SQL>
SQL>
SQL> select d.deptno 部门号,d.dname 部门名称,count(e.deptno) 人数
2 from emp e,dept d
3 where e.deptno(+)=d.deptno
4 group by d.deptno,d.dname
5 order by 1;
部门号 部门名称 人数
---------- -------------- ----------
10 ACCOUNTING 3
20 RESEARCH 5
30 SALES 6
40 OPERATIONS 0
SQL> host cls
SQL> --自连接
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-12月-80 800
20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300
30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500
30
7566 JONES MANAGER 7839 02-4月 -81 2975
20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7698 BLAKE MANAGER 7839 01-5月 -81 2850
30
7782 CLARK MANAGER 7839 09-6月 -81 2450
10
7788 SCOTT ANALYST 7566 19-4月 -87 3000
20
7839 KING PRESIDENT 17-11月-81 5000
10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7876 ADAMS CLERK 7788 23-5月 -87 1100
20
7900 JAMES CLERK 7698 03-12月-81 950
30
7902 FORD ANALYST 7566 03-12月-81 3000
20
7934 MILLER CLERK 7782 23-1月 -82 1300
10
已选择14行。
SQL> set linesize 120;
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择14行。
SQL> --现在有一个需求：要求输出的语句为***的老板是***
SQL> --首先在emp表每条员工的记录都包含有他老板的编号
SQL> select e.ename||'的老板是'||b.empno
2 from emp e,emp b
3 where e.mgr=b.empno;
E.ENAME||'的老板是'||B.EMPNO
----------------------------------------------------------
SMITH的老板是7902
ALLEN的老板是7698
WARD的老板是7698
JONES的老板是7839
MARTIN的老板是7698
BLAKE的老板是7839
CLARK的老板是7839
SCOTT的老板是7566
TURNER的老板是7698
ADAMS的老板是7788
JAMES的老板是7698
FORD的老板是7566
MILLER的老板是7782
已选择13行。
SQL> --分析上面的例子，因为我们需要的数据都在同一张表中
SQL> --并且第一张表的老板的编号等于第二张表的编号，
SQL> --这时候可以用自连接
SQL> --但是自连接有它自己的问题，由于自连接的笛卡儿积是按照幂增长的，如果有多少张表就多少次方
SQL> --如果非常大，那么会极大降低效率。
SQL> --同时由于这里查询的员工号跟老板编号存在一个树的关系，所以可以采取层次查询
SQL> --层次查询只需要一张表
SQL> select empno 员工编号,ename 姓名,sal 薪水,mgr 老板编号
2 from emp
3 connect by prior empno = mgr
4 start with mgr is null
5 order by 1;
员工编号 姓名 薪水 老板编号
---------- ---------- ---------- ----------
7369 SMITH 800 7902
7499 ALLEN 1600 7698
7521 WARD 1250 7698
7566 JONES 2975 7839
7654 MARTIN 1250 7698
7698 BLAKE 2850 7839
7782 CLARK 2450 7839
7788 SCOTT 3000 7566
7839 KING 5000
7844 TURNER 1500 7698
7876 ADAMS 1100 7788
7900 JAMES 950 7698
7902 FORD 3000 7566
7934 MILLER 1300 7782
已选择14行。
SQL> select level 层次, empno 员工编号,ename 姓名,sal 薪水,mgr 老板编号
2 from emp
3 connect by prior empno = mgr
4 start with mgr is null
5 order by 1;
层次 员工编号 姓名 薪水 老板编号
---------- ---------- ---------- ---------- ----------
1 7839 KING 5000
2 7566 JONES 2975 7839
2 7698 BLAKE 2850 7839
2 7782 CLARK 2450 7839
3 7902 FORD 3000 7566
3 7521 WARD 1250 7698
3 7900 JAMES 950 7698
3 7934 MILLER 1300 7782
3 7499 ALLEN 1600 7698
3 7788 SCOTT 3000 7566
3 7654 MARTIN 1250 7698
3 7844 TURNER 1500 7698
4 7876 ADAMS 1100 7788
4 7369 SMITH 800 7902
已选择14行。
SQL> --下面是员工与老板的树状图，可以看到这里面多了一列，叫level
SQL> --这一列是在层次查询中Oracle自带的伪列，就像我们的伪表dual一样,在以后查询带有树状图关系的表时，都可以使用层次查询
SQL> --例子分析，上面的prior empno=mgr的意思就是上面一层level的员工编号等于下一层level员工的老板编号
SQL> --start with 的意思是 从哪里开始遍历，在这里我们使用顶层开始。
SQL> --层次查询的结果并没有自连接那么直观，它主要是解决遍历这棵树的查询，同时提高了查询效率
SQL> spool off
1117
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
