顯示多表數(shù)據(jù)

1、顯示多表數(shù)據(jù)目標(biāo)完成本課，將可以: 書寫SELECT語句，使用等連接和不等連接來訪問多表中的數(shù)據(jù)使用外連接來查看原本不符合連接條件的數(shù)據(jù)對表使用自身連接 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.從多表取得數(shù)據(jù)EMP DEPT EMPNOENAME.DEPTNO-.- 7839KING. 10 7698BLAKE. 30 . 7934MILLER. 10DEPTNO

2、DNAME LOC - - 10ACCOUNTINGNEW YORK 20RESEARCHDALLAS 30SALESCHICAGO 40OPERATIONSBOSTON什么是連接(join)?使用連接來查詢多表中的數(shù)據(jù).在WHERE子句中使用連接條件.如果兩個表中有相同的列，在列名前面加上表名限定.SELECTtable1.column, table2.columnFROMtable1, table2WHEREtable1.column1 = table2.column2;迪卡爾積(Cartesian product)在下面的情況出現(xiàn)迪卡爾積:一個連接條件被忽略了一個連接條件無效一個表的所有

3、行連接到另一個表的所有行要避免迪卡爾積, 總是在WHERE子句中包含有效的連接條件.(至少要表的數(shù)目減1個連接條件)迪卡爾積的產(chǎn)生ENAME DNAME- -KINGACCOUNTINGBLAKE ACCOUNTING .KINGRESEARCHBLAKE RESEARCH.56 rows selected.EMP (14 行) DEPT (4 行) EMPNOENAME.DEPTNO-.- 7839KING. 10 7698BLAKE. 30 . 7934MILLER. 10DEPTNO DNAME LOC - - 10ACCOUNTINGNEW YORK 20RESEARCHDALLAS

4、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 - -

5、 - 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.l

6、oc 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.修飾有歧義的列名用表名前綴來修飾多表中重復(fù)的列名.使用表名前綴可以提高性能.用列的別名來區(qū)別不同表中的重名列.使用AND操作符附加查詢條件EMP DEPT EMPNO ENAME DEP

7、TNO- - - 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 SA

8、LES 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

9、, d.loc 3 FROM emp e, dept d 4 WHERE e.deptno=d.deptno;連接兩個以上的表NAMECUSTID-JOCKSPORTS 100TKB SPORT SHOP 101VOLLYRITE 102JUST TENNIS 103K+T SPORTS 105SHAPE UP 106WOMENS 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 ORD

10、ID ITEMID- - 610 3 611 1 612 1 601 1 602 1.64 rows selected. ITEM 不等連接EMPSALGRADE“在EMP中的工資在SALGRADE表的最低值和最高值之間” EMPNO ENAME SAL- - - 7839 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- - -

11、1 70012002 120114003 140120004200130005 30019999使用不等查詢檢索數(shù)據(jù)ENAME SAL GRADE- - -JAMES 950 1SMITH 800 1ADAMS 1100 1.14 rows selected.SQL SELECT e.ename, e.sal, s.grade 2FROMemp e, salgrade s 3WHERE e.sal 4BETWEEN s.losal AND s.hisal;外連接EMP DEPT 沒有雇員在OPERATIONS 部門ENAMEDEPTNO-KING10BLAKE30CLARK10JONES20.

12、DEPTNO DNAME- -10 ACCOUNTING30 SALES10 ACCOUNTING20RESEARCH.40OPERATIONS外連接你可以使用外連接看到本不滿足連接條件的行.外連接操作符是加號 (+)，放在需要補充數(shù)據(jù)(空行)的一邊。SELECTtable.column, table.columnFROMtable1, table2WHEREtable1.column(+) = table2.column;SELECTtable.column, table.columnFROMtable1, table2WHEREtable1.column = table2.column(+

13、);使用外連接SQL SELECTe.ename, d.deptno, d.dname 2 FROMemp e, dept d 3 WHEREe.deptno(+) = d.deptno 4 ORDER BYe.deptno;ENAME DEPTNO DNAME- - -KING 10 ACCOUNTINGCLARK 10 ACCOUNTING. 40 OPERATIONS15 rows selected.自連接EMP (WORKER)EMP (MANAGER)“WORKER表的MGR等于MANAGER表的EMPNO EMPNOENAME MGR- 7839KING 7698BLAKE7839

14、 7782CLARK7839 7566JONES7839 7654MARTIN7698 7499ALLEN7698EMPNOENAME- 7839KING 7839KING 7839KING 7698BLAKE 7698BLAKE使用自連接WORKER.ENAME|WORKSFOR|MANAG-BLAKE works for KINGCLARK works for KINGJONES works for KINGMARTIN works for BLAKE.13 rows selected.SQL SELECT worker.ename| works for |manager.ename 2 FR