7多數(shù)據(jù)表連接和集合運算

1、 了解表連接的基本原理，分清連接的類型； 掌握各種類型連接的使用方法； 掌握利用SQL:1999的語法創(chuàng)建連接的方法； 掌握各種類型連接之間的區(qū)別與聯(lián)系； 使用UNION,UNION ALL,INTERSECT和MINUS等集合運算； 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. EMPNO ENAME. DEPTNO- -. - 7839 KING. 10 7698

2、BLAKE. 30 . 7934 MILLER . 10DEPTNO DNAME LOC - - - 10 ACCOUNTING NEW YORK 20 RESEARCHDALLAS 30 SALESCHICAGO 40 OPERATIONS BOSTONl使用連接從多個表中查詢數(shù)據(jù)。l在 WHERE子句中書寫連接條件,通常都是使用主鍵和外鍵進行連接。l如果在多個表中出現(xiàn)相同的列名，則需要使用表名作為來自該表的列名的前綴。lN個表相連時，至少需要N-1個連接條件。SELECTtable1.column, table2.columnFROMtable1, table2WHEREtable1.co

3、lumn1 = table2.column2;等值連接等值連接非等值連接非等值連接外部連接外部連接內(nèi)部連接內(nèi)部連接l笛卡爾積在下列情況產(chǎn)生：l連接條件被省略l連接條件是無效的l第一個表中的所有行和第二個表中的所有行都發(fā)生連接。l為了避免笛卡爾積，通常需要在WHERE子句中包含一個有效的連接條件。ENAME DNAME- -KINGACCOUNTINGBLAKE ACCOUNTING .KINGRESEARCHBLAKE RESEARCH.已選擇已選擇64行。行。 EMPNO ENAME. DEPTNO- -. - 7839 KING. 10 7698 BLAKE. 30 . 7934 MILL

4、ER . 10DEPTNO DNAME LOC - - - 10 ACCOUNTING NEW YORK 20 RESEARCHDALLAS 30 SALESCHICAGO 40 OPERATIONS BOSTON 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 selecte

5、d. DEPTNO DNAME LOC - - - 10 ACCOUNTING NEW YORK 30 SALES CHICAGO 10 ACCOUNTING NEW 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.注注: 等值連接通常被稱為簡單連接或內(nèi)部連接。等值連接通常被稱為簡單連接或內(nèi)部連接。 SQL SELECT e

6、mp.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.已選擇已選擇16行。行。 EMPNO ENAME DEPTNO- - - 7839 KING 10 7698 BL

7、AKE 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 ACCOUNTING NEW YORK 20 RESEARCHDALLAS 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICA

8、GO 30 SALES CHICAGO 30 SALES CHICAGO 20 RESEARCHDALLAS 20 RESEARCHDALLAS.14 rows selected.l在用到多個表時使用表名作前綴來限定列l(wèi)通過使用表前綴可以提高性能l通過使用列的別名可以區(qū)分來自不同表但是名字相同的列l(wèi)通過使用表的別名來簡化查詢語句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

9、, e.ename, e.deptno, 2 d.deptno, 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. CUSTID ORDID- - 101 610 102 611 104 612 106 601 102 602 106 604 106 605. 21 rows

10、selected. ORDID ITEMID- - 610 3 611 1 612 1 601 1 602 1.64 rows selected. 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- - -1 70012002 120114003 14012000420013000

11、5 30019999ENAME 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;ENAMEDEPTNO-KING10BLAKE30CLARK10JONES20.DEPTNO DNAME- -10 ACCOUNTING30 SALES10 ACCOUNTING20RESEARCH.40OPERATIONSl您可以使用

12、外部連接來查看哪些行沒有符合連接條件l外部連接的符號是 (+).SELECT table1.column, table2.columnFROMtable1, table2WHEREtable1.column(+) = table2.column;SELECT table1.column, table2.columnFROMtable1, table2WHEREtable1.column = table2.column(+);ENAME DEPTNO DNAME- - -KING 10 ACCOUNTINGCLARK 10 ACCOUNTING. 40 OPERATIONS已選擇已選擇17行。行

13、。SQL SELECTe.ename, d.deptno, d.dname 2 FROMemp e, dept d 3 WHEREe.deptno(+) = d.deptno 4 ORDER BY e.deptno;外部連接條件不能使用外部連接條件不能使用IN運算符，也不能用運算符，也不能用or運運算符與其它條件相連。算符與其它條件相連。EMPNO ENAME MGR- - - 7839 KING 7698 BLAKE7839 7782 CLARK7839 7566 JONES7839 7654 MARTIN 7698 7499 ALLEN7698EMPNO ENAME- - 7839 KIN

14、G 7839 KING 7839 KING 7698 BLAKE 7698 BLAKEWORKER.ENAME|WORKSFOR|MANAG-BLAKE works for KINGCLARK works for KINGJONES works for KINGMARTIN works for BLAKE.已選擇已選擇15行。行。SQL SELECT worker.ename| works for |manager.ename 2 FROM emp worker, emp manager 3 WHERE worker.mgr = manager.empno;自身連接：一個表與其自己進行連接需要

15、給表起別名以示區(qū)別由于所有屬性名都是同名屬性，因此必須使用別名前綴例 查詢每一門課的間接先修課（即先修課的先修課） FIRST表（表（Course表）表） Ccredit 5 3 4 4 5 3 2Cno CnameCpno 1數(shù)據(jù)庫數(shù)據(jù)庫 5 2數(shù)學數(shù)學 3信息系統(tǒng)信息系統(tǒng) 1 4操作系統(tǒng)操作系統(tǒng) 6 5數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)結(jié)構(gòu) 7 6數(shù)據(jù)處理數(shù)據(jù)處理 7PASCAL語言語言 6 SECOND表（表（Course表）表） SELECT FIRST.Cno, SECOND.Cpno FROM Course FIRST, Course SECOND WHERE FIRST.Cpno = SECOND.C

16、no；Ccredit 5 3 4 4 5 3 2Cno CnameCpno 1數(shù)據(jù)庫數(shù)據(jù)庫 5 2數(shù)學數(shù)學 3信息系統(tǒng)信息系統(tǒng) 1 4操作系統(tǒng)操作系統(tǒng) 6 5數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)結(jié)構(gòu) 7 6數(shù)據(jù)處理數(shù)據(jù)處理 7PASCAL語言語言 6查詢結(jié)果：查詢結(jié)果：CnoPcno17354567Use a join to query data from more than one table.SELECT table1.column, table2.columnFROMtable1CROSS JOIN table2 |NATURAL JOIN table2 |JOIN table2 USING (column_n

17、ame) |JOIN table2 ON(table1.column_name = table2.column_name) |LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name);lCROSS JOIN 子句會產(chǎn)生兩個表的交叉乘積，和兩個表之間的笛卡爾積是一樣的。 SELECT emp.empno,emp.ename,emp.sal,emp.deptno,dept.loc FROM emp CROSS JOIN dept; EMPNO ENAME SAL DEPTNO LOC- - - -

18、- 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已選擇64行。 l使用自然連接時應注意以下事項：l自然連接子句是基于兩個表存在相同名字的列；l返回兩個表相匹配列中具有相同值的記錄；l如果相同名稱的列的數(shù)據(jù)類型不同，則會產(chǎn)生錯誤。 SELECT empno,ename,sal,deptno,loc FROM emp NATURAL JOIN dept; EMPNO ENA

19、ME 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已選擇已選擇16行。行。l自然連接使用所有名稱和數(shù)據(jù)類型相匹配的列，USING子句可以用于指定產(chǎn)生連接的列。ENAME ENAME SAL DEPTNO LOC- - - - -SMITH SMITH 800 20 DALLASJONES JONES 2975 20 DALLASS

20、COTT SCOTT 3000 20 DALLASADAMS ADAMS 1100 20 DALLASFORD FORD 3000 20 DALLAS已選擇已選擇5行。行。 SELECT e.ename,e.ename,e.sal,deptno,d.loc FROM emp e JOIN dept d USING (deptno) WHERE deptno = 20 ;l自然連接條件基本上是具有相同列名的表之間的等值連接，要指定任意連接條件或特殊的列可以使用ON子句，用ON將連接條件和其它檢索條件分隔開可以提高代碼的可讀性。 EMPNO ENAME DEPTNO DEPTNO LOC- - -

21、 - - 7369 SMITH 20 20 DALLAS 7499 ALLEN 30 30 CHICAGO 7934 MILLER 10 10 NEW YORK 已選擇已選擇15行。行。 SELECT e.empno, e.ename, e.deptno, d.deptno, d.loc FROM emp e JOIN dept d ON (e.deptno = d.deptno); 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.m

22、gr = m.empno; EMPNO ENAME LOC ENAME- - - - 7788 SCOTT DALLAS JONES 7902 FORD DALLAS JONES 7499 ALLEN CHICAGO BLAKE 7521 WARD CHICAGO BLAKE 7934 MILLER NEW YORK CLARK 已選擇已選擇8行。行。 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 N

23、EW YORKKING 10 NEW YORKCLARK 10 NEW YORKFORD 20 DALLASALLEN已選擇已選擇16行。行。 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 DALLASALLEN 30 CHICAGOJONES 20 DALLASMARTIN 30 CHICAGO BOSTON已選擇已選擇17行。行。 SELECT e.ename,e.deptno,d.loc FRO

24、M emp e FULL OUTER JOIN dept d ON (e.deptno = d.deptno); ENAME DEPTNO LOC- - -MILLER 10 NEW YORKKING 10 NEW YORKCLARK 10 NEW YORKALLEN BOSTON已選擇17行。l 集合運算符lUNION 返回兩個查詢的所有行，但剔除重復行l(wèi)UNION ALL 返回兩個查詢的所有行，保留所有重復行l(wèi)INTERSECT 返回同時出現(xiàn)在兩個查詢結(jié)果中的所有行l(wèi)MINUS 從第一個查詢返回的行中減去第二個查詢返回的行，剩余的行即為返回結(jié)果多個查詢語句的結(jié)果可以做集合運算，結(jié)果集的字段

25、類型、數(shù)量和順序應該一樣。l返回兩個查詢的結(jié)果，重復行只顯示一次l例：SELECT * FROM EMPlUNIONlSELECT * FROM MANAGERl與UNION運算相似，返回兩個查詢的結(jié)果，顯示重復行l(wèi)例：SELECT * FROM EMPlUNION ALLlSELECT * FROM MANAGERl返回兩個查詢中都出現(xiàn)的結(jié)果，即 交運算l例：SELECT * FROM EMPlINTERSECTlSELECT * FROM MANAGERl返回那些在第一個查詢結(jié)果中但不在第二個查詢結(jié)果中的行l(wèi)例：SELECT * FROM MANAGERlMINUSlSELECT * FROM EMP一、從一、從student表中找出在同一個系中的不同學表中找出在同一個系中的不同學生名生名二、以下語句的作用是：二、以下語句的作用是：SELECT ename,sal FROM empWHERE sal(SELECT min(sal) FROM emp) ;A. 顯示工資低于顯示工資低于1000元的雇員信息元的雇員信息B. 將雇員工資小于將雇員工資小于1000元的工資增加元的工資增加1000后后顯示顯示C. 顯示超過最低工資顯示超過最低工資1000元的雇員信息元的雇員信息D. 顯示不超過最低工資顯示不超過最低工資1000元