經(jīng)典表關(guān)聯(lián)及多表查詢

1、. 經(jīng)典表關(guān)聯(lián)與多表查詢目的：1. 掌握從多個(gè)表查詢數(shù)據(jù)的根本知識(shí)2. 了解和學(xué)習(xí)外連接(out join)3. 掌握內(nèi)連接授課內(nèi)容：1. 對(duì)多于一個(gè)表的數(shù)據(jù)查詢1.1 現(xiàn)實(shí)情況中，在數(shù)據(jù)庫(kù)應(yīng)用中，數(shù)據(jù)存在于多個(gè)相關(guān)聯(lián)的表中。根本上沒有數(shù)據(jù)只存在于一個(gè)表中的情況。小的應(yīng)用系統(tǒng)一般也有十幾個(gè)表，大型系統(tǒng)一般有上千個(gè)表。1.2 你經(jīng)常要作的就是在多個(gè)表中進(jìn)展數(shù)據(jù)查詢。1.3 Oracle對(duì)多表查詢使用表連接的技術(shù)table join1.4 表連接的根本條件：(1) 2個(gè)表必須有公共字段同名字段或不同名字段(2) 在一個(gè)表中，這個(gè)公共字段必須是主鍵PK1.5 二個(gè)表中的公共字段，在一個(gè)表中是主鍵，在

2、另外一個(gè)表中就是外鍵(FK)。1.6 二表關(guān)聯(lián)中，公共字段是主鍵的表稱為父表主表。是外鍵的表稱為子表詳細(xì)表。1.7 研究一下scott下的emp和dept表的關(guān)系。1.8 研究一下oe下的表：CATEGORIES_TABCUSTOMERSINVENTORIESORDERSORDER_ITEMSPRODUCT_DESCRIPTIONSPRODUCT_INFORMATION1.9 多表查詢的語法select 子句from 表1 別名，表2 別名，視圖 別名，select 子句別名where 連接語句 and 其他條件語句oupy by 分類工程having 子句order by 子句1.10 任務(wù)

3、：查詢每個(gè)員工的編號(hào)，部門名稱，部門位置select empno,ename, dname,locfromemp a,dept bwhere a.DEPTNO=b.DEPTNO1.11 多表查詢的原那么：對(duì)N個(gè)表連接，至少要有N-1個(gè)相等的條件。而且每個(gè)表的公共字段必須出現(xiàn)一次。1.12 多表關(guān)聯(lián)中，如果沒有指定關(guān)聯(lián)等式，將產(chǎn)生無效的結(jié)果，它將每個(gè)關(guān)聯(lián)的表的記錄跟其他表的所有記錄組合，產(chǎn)生笛卡爾積的數(shù)據(jù)。測(cè)試：select empno,ename, dname,locfromemp a,dept b1.13 對(duì)OE用戶的測(cè)試 查詢公司庫(kù)存信息，顯示倉(cāng)庫(kù)名稱，產(chǎn)品名稱，庫(kù)存數(shù)量，庫(kù)存金額sele

4、ct c.WAREHOUSE_NAME, b.PRODUCT_NAME, a.QUANTITY_ON_HAND,a.QUANTITY_ON_HAND*b.LIST_PRICEfromINVENTORIESa,PRODUCT_INFORMATION b,WAREHOUSES cwhere a.WAREHOUSE_ID=c.WAREHOUSE_ID and a.PRODUCT_ID =b.PRODUCT_ID2. 內(nèi)連接 (self join)1. 當(dāng)多表關(guān)聯(lián)使用一個(gè)表進(jìn)展數(shù)據(jù)進(jìn)展數(shù)據(jù)查詢，這種連接叫自連接。2. 自連接的主要功能是查詢表中除了主鍵外，是否有重復(fù)的記錄。3. 任務(wù)： 查詢員工表中，

5、有同名，職位一樣的員工信息編號(hào)，工程，職位，工資select a.empno, a.ename, a.jobfrom emp a, emp bwhere a.empno<>b.empno and a.deptno=b.deptno and a.job=b.job4. 日常生活中在數(shù)據(jù)錄入時(shí)產(chǎn)生的錯(cuò)誤(1) 由于工作失誤，一個(gè)數(shù)據(jù)錄入到系統(tǒng)2次或?qū)掖巍?2) 一般在進(jìn)展自動(dòng)的數(shù)據(jù)導(dǎo)入時(shí)，產(chǎn)生大量的重復(fù)記錄。5. 子連接的要求：自連接至少要2個(gè)或2個(gè)以上的等式條件，一個(gè)用于關(guān)聯(lián)，其他用于表示重復(fù)的數(shù)據(jù)。3. 外連接(out join): 1.1 內(nèi)連接是關(guān)聯(lián)的表的公共字段值必須一樣，所

6、有不同的值的記錄都沒有了。1.2 外連接是值一個(gè)表的中的公共字段的值可以不與另一個(gè)表的公共字段值一樣。一般時(shí)它是null.1.3 任務(wù)：查詢員工表，顯示員工的工程，部門名稱，部門位置，要求顯示所有的員工，即使員工沒有部門。select a.ename,b.dname,b.locfromemp a left outer join dept bon a.deptno=b.deptno注：此任務(wù)無法使用正常的內(nèi)連接。因?yàn)橛幸粋€(gè)員工沒有部門，它的部門編號(hào)為空。 常見的任務(wù)如：信息系統(tǒng)中的文檔，申請(qǐng)審批，當(dāng)剛創(chuàng)立時(shí)，所有審批信息為null. 但有的審批已經(jīng)完成。 如果與審批人表關(guān)聯(lián)的話，要顯示所有的申請(qǐng)

7、，就必須使用外連接。 1.4 外連接語法：1左連接：取出左邊的表的所有記錄select 子句from 表1 left outer join 表2 on 表1.公共字段表2.公共字段2 右連接: 取出右邊表的所有記錄select 子句from 表1 right outer join 表2 on 表1.公共字段表2.公共字段3 全連接左右連接：左右兩邊的表的記錄都取。select 子句from 表1 full outer join 表2 on 表1.公共字段表2.公共字段select a.empno,a.deptno,b.deptno,b.dnamefromemp a left outer joi

8、n dept bon a.deptno=b.deptnoselect a.empno,a.deptno,b.deptno,b.dnamefromemp a right outer join dept bon a.deptno=b.deptnoselect a.ename,b.dname,b.locfromemp a full outer join dept bon a.deptno=b.deptnoselect a.dname, b.enamefromdept a full outer join emp bon a.deptno=b.deptno一般情況下，不使用上述的語法，而使用如下的語法：

9、select a.dname, b.enamefromdept a,emp bwhere a.deptno(+)=b.deptnoß- 一般情況情況下，(+)放在關(guān)聯(lián)表的主鍵的一側(cè)，才有實(shí)際的意義。沒有(+)的表的取所有的記錄，關(guān)聯(lián)的表如果有記錄對(duì)應(yīng)就顯示關(guān)聯(lián)的值，沒有關(guān)聯(lián)的值顯示null.但使用(+)的情況下，無法實(shí)現(xiàn)全連接。 因?yàn)闊o法在where 的左右同時(shí)使用(+).select a.ename,b.dnamefromemp a,dept bwhere a.deptno=b.deptno(+)以下的語句是無法通過的：select a.ename,b.dnamefromemp a

10、,dept bwhere a.deptno(+)=b.deptno(+)1.5 任務(wù)：4. 自關(guān)聯(lián)(self-join)4.1 有些情況下，需要關(guān)聯(lián)一個(gè)表，這種關(guān)聯(lián)叫自關(guān)聯(lián)。4.2 自關(guān)聯(lián)經(jīng)常使用的一般是查看表中的記錄是否重復(fù)。在信息管理系統(tǒng)中，有時(shí)出現(xiàn)數(shù)據(jù)錄入的錯(cuò)誤。同一個(gè)數(shù)據(jù)，被輸入了2次以上，除了主鍵不一樣，其他字段根本上一樣。即查詢重復(fù)的記錄。4.3 數(shù)據(jù)錄入錯(cuò)誤的發(fā)生可能的情況：(1) 數(shù)據(jù)的自動(dòng)導(dǎo)入，新建系統(tǒng)從老系統(tǒng)中批量導(dǎo)入數(shù)據(jù)，導(dǎo)致大量的重復(fù)記錄。(2) 用戶輸入錯(cuò)誤的數(shù)據(jù)，將一個(gè)數(shù)據(jù)輸入的2次。4.4 如SCOTT的員工表EMP, MGR字段是員工的經(jīng)理的員工號(hào)。要查詢每個(gè)員

11、工的經(jīng)理的XX。就需要使用自關(guān)聯(lián)。select a.ename, b.enamefrom emp a, emp bwhere a.mgr=b.empno4.5 查詢emp表中可能同名的員工的記錄。select a.empno,a.enamefromemp a,emp bwhere a.empno<>b.empno and a.ename=b.ename4.6 查詢員工表emp的重復(fù)記錄：select a.empno,a.enamefromemp a,emp bwhere a.empno<>b.empno and a.ename=b.ename and a.job=b.j

12、ob and a.sal=b.sal4.7 子連接會(huì)導(dǎo)致對(duì)表的大量的操作，需要很大的內(nèi)存。其他用戶對(duì)自連接的表的操作會(huì)等待很長(zhǎng)的時(shí)間。一般情況下最好不要使用自關(guān)聯(lián)。4.8 表的自關(guān)聯(lián)的與內(nèi)關(guān)聯(lián)不同，自關(guān)聯(lián)至少要2個(gè)或2個(gè)以上的等式條件。5. 查詢結(jié)果的聯(lián)合UNIONUNION ALL- 將多個(gè)查詢結(jié)果聯(lián)合在一起：- UNION將多個(gè)結(jié)果集聯(lián)合在一起，去除重復(fù)的記錄- UNION ALL將多個(gè)結(jié)果聯(lián)合在一起，不去除重復(fù)的記錄Table 7-1: Set Operators OperatorDescriptionUNION ALL Returns all the rows retrieved by

13、 the queries, including duplicate rows.UNION Returns all non-duplicate rows retrieved by the queries.INTERSECT Returns rows that are retrieved by both queries.MINUS Returns the remaining rows when the rows retrieved by the second query are subtracted from the rows retrieved by the first query.- unio

14、n語法：selectunionselectunionselect例子1：select empno,enamefrom empwhere deptno=10unionselect deptno,dnamefrom dept例子2：select*from emp where deptno=10unionselect*from emp where job='CLERK' - union all 語法selectunion allselectunion allselect例子1：select*from emp where deptno=10unionallselect*from emp

15、 where job='CLERK'6. 查詢結(jié)果的交集INTERSECT：- 將多個(gè)查詢結(jié)果集聯(lián)合在一起，只保存一樣的記錄。摘除不同的記錄- 語法：select 語句intersectselectintersectselect例子：select*from emp where deptno=10intersectselect*from emp where job='CLERK'7. 查詢結(jié)果的差集MINUS：- 將多個(gè)結(jié)果集聯(lián)合在一起，保存它們差異的記錄，將包含第2個(gè)結(jié)果集的記錄減去。- 語法：select minusselectminusselect例子：se

16、lect*from emp where deptno=10minusselect*from emp where job='CLERK'注：1. Oracle在合并2個(gè)結(jié)果集時(shí)，Oracle并不關(guān)心合并運(yùn)算符的任何一邊的列名，合并的結(jié)果集以第一個(gè)結(jié)果集的列名為新的列名。2. select語句必須有一樣的列，如果被查詢的結(jié)果集有不同的列，可使用Oracle的內(nèi)置表達(dá)式合成為一樣的列數(shù)。3. select的相對(duì)應(yīng)的列必須為一樣的類型。長(zhǎng)度可以不同。4. 在對(duì)輸出進(jìn)展排序時(shí)，Oracle使用第1個(gè)select語句的列名給出查詢結(jié)果，因?yàn)?，只有?個(gè)select的列作為查詢結(jié)果，因此只有

17、第一個(gè)select的列名出現(xiàn)在order by 子句中。8. Oracle9i實(shí)現(xiàn)SQL Server 2000 中的select top n 的SQL語句：在Oracle9i中沒有類似的select top n 的語句。但是它提供了ROWNUM內(nèi)置函數(shù)?？梢詫?shí)現(xiàn)top n的查詢語句。Select From Where rownum<=nROWNUM是Oracle在做查詢時(shí)自動(dòng)計(jì)算的。它會(huì)隨著記錄集的變化而動(dòng)態(tài)變化。ROWNUM返回第一次從表中選擇時(shí)返回行的序列號(hào)。第1行的ROWNUM為1。如果想返回一個(gè)復(fù)雜查詢的結(jié)果集的top n, 要把此結(jié)果集作為中間結(jié)果集放在from中，再使用ro

18、wnum函數(shù)。如下例子：select*from(select deptno, sum(sal)from empgroupby deptno)whererownum<=2課前提問：1. 查詢采購(gòu)金額多于5000元的客戶清單，以及每個(gè)客戶的采購(gòu)金額，并按總采購(gòu)金額排序 客戶名稱， 采購(gòu)金額select a.CUST_FIRST_NAME|' '| CUST_LAST_NAME, sum(c.UNIT_PRICE*C.QUANTITY)as totalRMBfromCUSTOMERS a,ORDERS b,ORDER_ITEMS cwhere a.customer_ID=b.Customer_ID