Oracle SQL&PLSQL课件：第4章 多表查询.ppt

单击此处编辑母版标题样式,*,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,Oracle SQL&PL/SQL,第,4,章 多表查询,了解表连接的基本原理，分清连接的类型；,掌握各种类型连接的使用方法；,掌握利用,SQL:1999,的语法创建连接的方法；,掌握各种类型连接之间的区别与联系。,本章要点,EMPNO DEPTNO LOC,-,7839 10 NEW YORK,7698 30 CHICAGO,7782 10 NEW YORK,7566 20 DALLAS,7654 30 CHICAGO,7499 30 CHICAGO,.,14 rows selected.,从多个表中获取数据,EMP,DEPT,EMPNOENAME.DEPTNO-.-7839KING.10,7698BLAKE.30,.,7934MILLER.10,DEPTNO DNAME LOC,-,10ACCOUNTINGNEW YORK,20RESEARCHDALLAS,30SALESCHICAGO,40OPERATIONSBOSTON,什么是连接,使用连接从多个表中查询数据。,在,WHERE,子句中书写连接条件。,如果在多个表中出现相同的列名，则需要使用表名作为来自该表的列名的前缀。,N,个表相连时，至少需要,N1,个连接条件。,SELECT,table1.column,table2.column,FROM,table1,table2,WHERE,table1.column1,=,table2.column2,;,连接的类型,等值连接,非等值连接,外部连接,内部连接,笛卡尔积,笛卡尔积在下列情况产生：,连接条件被省略,连接条件是无效的,第一个表中的所有行和第二个表中的所有行都发生连接。,为了避免笛卡尔积，通常需要在,WHERE,子句中包含一个有效的连接条件。,笛卡尔积的产生,ENAME DNAME,-,KINGACCOUNTING,BLAKE ACCOUNTING,.,KINGRESEARCH,BLAKE RESEARCH,.,56 rows selected.,EMP(14,行,),DEPT(4,行,),EMPNOENAME.DEPTNO-.-7839KING.10,7698BLAKE.30,.,7934MILLER.10,DEPTNO DNAME LOC,-,10ACCOUNTINGNEW YORK,20RESEARCHDALLAS,30SALESCHICAGO,40OPERATIONSBOSTON,“,笛卡尔积,:14*4=56,行”,等值连接,EMP,DEPT,EMPNO ENAME DEPTNO,-,7839 KING 10,7698 BLAKE 30,7782 CLARK 10,7566 JONES 20,7654 MARTIN 30,7499 ALLEN 30,7844 TURNER 30,7900 JAMES 30,7521 WARD 30,7902 FORD 20,7369 SMITH 20,.,14 rows selected.,DEPTNO DNAME LOC,-,10 ACCOUNTING NEW YORK,30 SALES CHICAGO,10 ACCOUNTINGNEW YORK,20 RESEARCHDALLAS,30 SALES CHICAGO,30 SALES CHICAGO,30 SALES CHICAGO,30 SALES CHICAGO,30 SALES CHICAGO,20 RESEARCHDALLAS,20 RESEARCHDALLAS,.,14 rows selected.,外键,主键,用等值连接检索数据,SQL SELECT,emp,.,empno,emp,.,ename,emp,.,deptno,2dept.,deptno,dept.loc,3 FROM,emp,dept,4 WHERE,emp,.,deptno,=dept.,deptno,;,EMPNO ENAME DEPTNO DEPTNO LOC,-,7839 KING 10 10 NEW YORK,7698 BLAKE 30 30 CHICAGO,7782 CLARK 10 10 NEW YORK,7566 JONES 20 20 DALLAS,.14 rows selected.,使用,AND,运算符增加其它查询条件,EMP,DEPT,EMPNO ENAME DEPTNO,-,7839 KING 10,7698 BLAKE 30,7782 CLARK 10,7566 JONES 20,7654 MARTIN 30,7499 ALLEN 30,7844 TURNER 30,7900 JAMES 30,7521 WARD 30,7902 FORD 20,7369 SMITH 20,.,14 rows selected.,DEPTNO DNAME LOC,-,10 ACCOUNTINGNEW YORK,30SALES CHICAGO,10 ACCOUNTINGNEW YORK,20 RESEARCHDALLAS,30 SALES CHICAGO,30 SALES CHICAGO,30 SALES CHICAGO,30 SALES CHICAGO,30,SALES CHICAGO,20 RESEARCHDALLAS,20 RESEARCHDALLAS,.,14 rows selected.,限制歧义列名,在用到多个表时使用表名作前缀来限定列,通过使用表前缀可以提高性能,通过使用列的别名可以区分来自不同表但是名字相同的列,使用表的别名,通过使用表的别名来简化查询语句,SQL SELECT,emp,.,empno,emp,.,ename,emp,.,deptno,2 dept.,deptno,dept.loc,3 FROM,emp,dept,4 WHERE,emp,.,deptno,=dept.,deptno,;,SQL SELECT e.,empno,e.,ename,e.,deptno,2 d.,deptno,d.loc,3 FROM,emp,e,dept d,4 WHERE e.,deptno,=d.,deptno,;,多于两个表的连接,NAMECUSTID,-,JOCKSPORTS 100,TKB SPORT SHOP 101,VOLLYRITE 102,JUST TENNIS 103,K+T SPORTS 105,SHAPE UP 106,WOMENS SPORTS 107,.,9 rows selected.,CUSTOMER,CUSTID ORDID,-,101 610,102 611,104 612,106 601,102 602,106 604,106 605,.,21 rows selected.,ORD,ORDID ITEMID,-,610 3,611 1,612 1,601 1,602 1,.,64 rows selected.,ITEM,非等值连接,EMP,SALGRADE,“,EMP,表中的薪水是在,SALGRADE,表所规定的,最低和最高范围内。”,EMPNO ENAME SAL7839 KING 5000,7698 BLAKE 2850,7782 CLARK 2450,7566 JONES 2975,7654 MARTIN 1250,7499 ALLEN 1600,7844 TURNER 1500,7900 JAMES 950,.,14 rows selected.,GRADE LOSAL HISAL,-,1 7001200,2 12011400,3 14012000,420013000,5 30019999,非等值连接的数据检索,ENAME SAL GRADE,-,JAMES 950 1,SMITH 800 1,ADAMS 1100 1,.,14 rows selected.,SQL SELECT e.,ename,e.,sal,s.grade,2FROM,emp,e,salgrade,s,3WHERE e.,sal,4BETWEEN s.,losal,AND s.,hisal,;,外部连接,EMP,DEPT,没有雇员工作在,OPERATIONS,部门,ENAMEDEPTNO-KING10,BLAKE30,CLARK10,JONES20,.,DEPTNO DNAME,-,10 ACCOUNTING,30 SALES,10 ACCOUNTING,20RESEARCH,.,40OPERATIONS,外部连接,您可以使用外部连接来查看哪些行没有符合连接条件,外部连接的符号是,(+),.,SELECT,table1.column,table2.column,FROM,table1,table2,WHERE,table1.column,(+),=,table2.column,;,SELECT,table1.column,table2.column,FROM,table1,table2,WHERE,table1.column,=,table2.column,(+),;,外部连接,外部连接就好象是为符号(+)所在边的表增加一个“万能”的行，这个行全部由空值组成。它可以和另一边的表中,所有不满足,连接条件的元组进行连接。由于这个“万能”行的各列全部是空值，因此在连接结果中，来自“万能”行属性值全部为空值。,使用外部连接,SQL SELECTe.,ename,d.,deptno,d.,dname,2 FROM,emp,e,dept d,3 WHEREe.,deptno,(+)=d.,deptno,4 ORDER BYe.,deptno,;,ENAME DEPTNO DNAME,-,KING 10 ACCOUNTING,CLARK 10 ACCOUNTING,.,40 OPERATIONS,15 rows selected.,自身连接,EMP(WORKER),EMP(MANAGER),“,WORKER,表中的,MGR,等于,MANAGER,表中的,EMPNO”,EMPNOENAME MGR-7839KING,7698BLAKE7839,7782CLARK7839,7566JONES7839,7654MARTIN7698,7499ALLEN7698,EMPNOENAME-,7839KING,7839KING,7839KING,7698BLAKE,7698BLAKE,表的自身连接,WORKER.ENAME|WORKSFOR|MANAG,-,BLAKE works for KING,CLARK works for KING,JONES works for KING,MARTIN works for BLAKE,.,13 rows selected.,SQL SELECT worker.,ename,|works for|manager.,ename,2 FROM,emp,worker,emp,manager,3 WHERE worker.mgr=manager.,empno,;,SQL:1999,语法的连接,Use a join to query data from more than one table.,SELECT,table1.column,table2.column,FROM,table1,CROSS JOIN,table2,|,NATURAL JOIN,table2,|,JOIN,table2,USING(,column_name,)|,JOIN,table2,ON(,table1.column_name,=,table2.column_name,)|,LEFT|RIGHT|FULL OUTER JOIN,table2,ON(,table1.column_name,=,table2.column_name,);,创建交叉连接,CROSS JOIN,子句会产生两个表的交叉乘积，和两个表之间的笛卡尔积是一样的。,SELECT,emp,.,empno,emp,.,ename,emp,.,sal,emp,.,deptno,dept.loc,FROM,emp,CROSS JOIN dept;,;,EMPNO ENAME SAL DEPTNO LOC,-,7369 SMITH 800 20 NEW YORK,7499 ALLEN 1600 30 NEW YORK,7521 WARD 1250 30 NEW YORK,7566 JONES 2975 20 NEW YORK,7934 MILLER 1300 10 BOSTON,已选择,56,行。,自然连接,使用自然连接时应注意以下事项：,自然连接子句是基于两个表存在相同名字的列；,返回两个表相匹配列中具有相同值的记录；,如果相同名称的列的数据类型不同，则会产生错误。,SELECT,empno,ename,sal,deptno,loc,FROM,emp,NATURAL JOIN dept;,EMPNO ENAME SAL DEPTNO LOC,-,7369 SMITH 800 20 DALLAS,7499 ALLEN 1600 30 CHICAGO,7521 WARD 1250 30 CHICAGO,7566 JONES 2975 20 DALLAS,7934 MILLER 1300 10 NEW YORK,已选择,14,行。,自然连接,使用,USING,子句创建连接,自然连接使用所有名称和数据类型相匹配的列，,USING,子句可以用于指定产生连接的列。,SELECT e.,ename,e.,ename,e.,sal,deptno,d.loc,FROM,emp,e JOIN dept d USING(,deptno,),WHERE,deptno,=20,;,ENAME ENAME SAL DEPTNO LOC,-,SMITH SMITH 800 20 DALLAS,JONES JONES 2975 20 DALLAS,SCOTT SCOTT 3000 20 DALLAS,ADAMS ADAMS 1100 20 DALLAS,FORD FORD 3000 20 DALLAS,使用,USING,子句创建连接,使用,USING,子句创建连接,使用,USING,子句创建连接时，应注意以下几点：,如果有若干个列名称相同但数据类型不同，自然连接子句可以用,USING,子句来替换，以指定产生等值连接的列。,如果有多于一个列都匹配的情况，使用,USING,子句只能指定其中的一列。,USING,子句中的用到的列不能使用表名和别名作为前缀。,NATURAL JOIN,子句和,USING,子句是相互排斥的，不能同时使用。,使用,ON,子句创建连接,自然连接条件基本上是具有相同列名的表之间的等值连接，要指定任意连接条件或特殊的列可以使用,ON,子句，用,ON,将连接条件和其它检索条件分隔开可以提高代码的可读性。,SELECT e.,empno,e.,ename,e.,deptno,d.,deptno,d.loc,FROM,emp,e,JOIN dept d,ON (e.,deptno,=d.,deptno,);,使用,ON,子句创建连接,EMPNO ENAME DEPTNO DEPTNO LOC,-,7369 SMITH 20 20 DALLAS,7499 ALLEN 30 30 CHICAGO,7934 MILLER 10 10 NEW YORK,已选择,14,行。,用,ON,子句创建三向连接,SELECT e.,empno,e.,ename,d.loc,m.,ename,FROM,emp,e,JOIN dept d,ON e.,deptno,=d.,deptno,JOIN manager m,ON e.mgr=m.,empno,EMPNO ENAME LOC ENAME,-,7788 SCOTT DALLAS JONES,7902 FORD DALLAS JONES,7499 ALLEN CHICAGO BLAKE,7521 WARD CHICAGO BLAKE,7369 SMITH DALLAS FORD,已选择,13,行。,SELECT e.,ename,e.,deptno,d.loc,FROM,emp,e,LEFT OUTER JOIN dept d,ON (e.,deptno,=d.,deptno,);,左外连接,ENAME DEPTNO LOC,-,MILLER 10 NEW YORK,KING 10 NEW YORK,CLARK 10 NEW YORK,FORD 20 DALLAS,WARD,已选择,14,行。,SELECT e.,ename,e.,deptno,d.loc,FROM,emp,e,RIGHT OUTER JOIN dept d,ON (e.,deptno,=d.,deptno,);,右外连接,ENAME DEPTNO LOC,-,SMITH 20 DALLAS,ALLEN 30 CHICAGO,JONES 20 DALLAS,MARTIN 30 CHICAGO,BOSTON,已选择,14,行。,SELECT e.,ename,e.,deptno,d.loc,FROM,emp,e,FULL OUTER JOIN dept d,ON (e.,deptno,=d.,deptno,);,全外连接,ENAME DEPTNO LOC,-,MILLER 10 NEW YORK,KING 10 NEW YORK,CLARK 10 NEW YORK,WARD,BOSTON,已选择,15,行。,