从多个表中显示数据-Itpub技术培训Eygle 2003-06目的n使用相等和不等连结写SELET语句来访问 多个表n使用外连接查询数据n表的自连接EMPNO DEPTNO LOC - - - 7839 10 NEW YORK7698 30 CHICAGO7782 10 NEW YORK7566 20 DALLAS7654 30 CHICAGO7499 30 CHICAGO . 14 rows selected.从多个表中获得数据EMP EMP DEPT DEPT EMPNO ENAME. DEPTNO - -. -7839 KING. 107698 BLAKE. 30.7934 MILLER . 10DEPTNO DNAME LOC - - -10 ACCOUNTING NEW YORK20 RESEARCHDALLAS30 SALESCHICAGO40 OPERATIONS BOSTON什么是连接n使用连接从多个表中查询数据n在WHERE子句中写连接条件n在多个表中具有相同的列名SELECTtable.column, table.column FROMtable1, table2 WHEREtable1.column1 = table2.column2;笛卡尔结果n笛卡尔结果形成于: 连接条件被省略 连接条件无效 第一个表的所有记录连接到第二个表的所有记录n为了避免笛卡尔结果我们总是在WHERET子 句中使用有效连接生成笛卡尔结果ENAME DNAME - - KINGACCOUNTING BLAKE ACCOUNTING . KINGRESEARCH BLAKE RESEARCH . 56 rows selected.EMP (14 rows) EMP (14 rows) DEPT (4 rows) DEPT (4 rows) EMPNO ENAME. DEPTNO - -. -7839 KING. 107698 BLAKE. 30.7934 MILLER . 10DEPTNO DNAME LOC - - -10 ACCOUNTING NEW YORK20 RESEARCHDALLAS30 SALESCHICAGO40 OPERATIONS BOSTON笛卡尔结果笛卡尔结果: : 14*4=56 14*4=56 条记录条记录连接的类型EquijoinNon-equijoinOuter joinSelf join什么是等值连接EMP EMP DEPT DEPT EMPNO ENAME DEPTNO - - -7839 KING 107698 BLAKE 307782 CLARK 107566 JONES 207654 MARTIN 307499 ALLEN 307844 TURNER 307900 JAMES 307521 WARD 307902 FORD 207369 SMITH 20 . 14 rows selected.DEPTNO DNAME LOC - - -10 ACCOUNTING NEW YORK30 SALES CHICAGO10 ACCOUNTING NEW YORK 20 RESEARCHDALLAS30 SALES CHICAGO30 SALES CHICAGO30 SALES CHICAGO30 SALES CHICAGO30 SALES CHICAGO20 RESEARCHDALLAS20 RESEARCHDALLAS . 14 rows selected.主键主键外键外键使用等值连接所获得的记录SQL SELECT emp.empno, emp.ename, emp.deptno,2dept.deptno, dept.loc3 FROM emp, dept4 WHERE emp.deptno=dept.deptno;EMPNO ENAME DEPTNO DEPTNO LOC - - - - -7839 KING 10 10 NEW YORK7698 BLAKE 30 30 CHICAGO7782 CLARK 10 10 NEW YORK7566 JONES 20 20 DALLAS . 14 rows selected.限定列名n使用表名作为前缀在多个表中指定列名n使用表前缀改进性能n使用列的别名以区分不同表的同名列使用AND运算符附加查询条件 EMP EMP DEPT DEPT EMPNO ENAME DEPTNO - - -7839 KING 107698 BLAKE 307782 CLARK 107566 JONES 207654 MARTIN 307499 ALLEN 307844 TURNER 307900 JAMES 307521 WARD 307902 FORD 207369 SMITH 20 . 14 rows selected.DEPTNO DNAME LOC - -10 ACCOUNTING NEW YORK30 SALES CHICAGO10 ACCOUNTING NEW YORK 20 RESEARCHDALLAS30 SALES CHICAGO30 SALES CHICAGO30 SALES CHICAGO30 SALES CHICAGO30 SALES CHICAGO20 RESEARCHDALLAS20 RESEARCHDALLAS . 14 rows selected.使用表的别名n使用别名简化查询SQL SELECT emp.empno, emp.ename, emp.deptno, 2 dept.deptno, dept.loc3 FROM emp, dept4 WHERE emp.deptno=dept.deptno;SQL SELECT e.empno, e.ename, e.deptno, 2 d.deptno, d.loc3 FROM emp e, dept d4 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 CUSTOMER CUSTID ORDID - -101 610102 611104 612106 601102 602106 604106 605 . 21 rows selected.ORD ORD ORDID ITEMID - -610 3611 1612 1601 1602 1 . 64 rows selected. ITEM ITEM 非等值连接EMPEMPSALGRADESALGRADEEMPEMP表中的表中的SalarySalary列值在列值在SALGRADESALGRADE 表的低表的低salarysalary和高和高salarysalary之间之间 EMPNO ENAME SAL - - -7839 KING 50007698 BLAKE 28507782 CLARK 24507566 JONES 29757654 MARTIN 12507499 ALLEN 16007844 TURNER 15007900 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.grade2FROMemp e, salgrade s3WHERE e.sal4BETWEEN s.losal AND s.hisal;外连接EMP EMP DEPT DEPT 在在OPERATIONSOPERATIONS部门没有员工部门没有员工ENAMEDEPTNO - KING10 BLAKE30 CLARK10 JONES20 .DEPTNO DNAME - - 10 ACCOUNTING 30 SALES 10 ACCOUNTING 20RESEARCH . 40OPERATIONS外连接n使用外连接看到不满足连接条件的记录n外连接运算符是加号(+)SELECT table.column, table.column FROMtable1, table2 WHEREtable1.column(+) = table2.column;SELECT table.column, table.column FROMtable1, table2 WHEREtable1.column = table2.column(+);使用外连接SQL SELECTe.ename, d.deptno, d.dname2 FROMemp e, dept d3 WHEREe.deptno(+) = d.deptno4 ORDER BY e.deptno;ENAME DEPTNO DNAME - - - KING 10 ACCOUNTING CLARK 10 ACCOUNTING .40 OPERATIONS 15 rows selected.自连接EMP (WORKER)EMP (WO