經典表關聯(lián)及多表查詢

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

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

3、：查詢每個員工的編號，部門名稱，部門位置select empno,ename, dname,locfromemp a,dept bwhere a.DEPTNO=b.DEPTNO1.11 多表查詢的原那么：對N個表連接，至少要有N-1個相等的條件。而且每個表的公共字段必須出現(xiàn)一次。1.12 多表關聯(lián)中，如果沒有指定關聯(lián)等式，將產生無效的結果，它將每個關聯(lián)的表的記錄跟其他表的所有記錄組合，產生笛卡爾積的數(shù)據(jù)。測試：select empno,ename, dname,locfromemp a,dept b1.13 對OE用戶的測試 查詢公司庫存信息，顯示倉庫名稱，產品名稱，庫存數(shù)量，庫存金額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. 內連接 (self join)1. 當多表關聯(lián)使用一個表進展數(shù)據(jù)進展數(shù)據(jù)查詢，這種連接叫自連接。2. 自連接的主要功能是查詢表中除了主鍵外，是否有重復的記錄。3. 任務： 查詢員工表中，

5、有同名，職位一樣的員工信息編號，工程，職位，工資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ù)錄入時產生的錯誤(1) 由于工作失誤，一個數(shù)據(jù)錄入到系統(tǒng)2次或屢次。(2) 一般在進展自動的數(shù)據(jù)導入時，產生大量的重復記錄。5. 子連接的要求：自連接至少要2個或2個以上的等式條件，一個用于關聯(lián)，其他用于表示重復的數(shù)據(jù)。3. 外連接(out join): 1.1 內連接是關聯(lián)的表的公共字段值必須一樣，所

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

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ß- 一般情況情況下，(+)放在關聯(lián)表的主鍵的一側，才有實際的意義。沒有(+)的表的取所有的記錄，關聯(lián)的表如果有記錄對應就顯示關聯(lián)的值，沒有關聯(lián)的值顯示null.但使用(+)的情況下，無法實現(xiàn)全連接。 因為無法在where 的左右同時使用(+).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 任務：4. 自關聯(lián)(self-join)4.1 有些情況下，需要關聯(lián)一個表，這種關聯(lián)叫自關聯(lián)。4.2 自關聯(lián)經常使用的一般是查看表中的記錄是否重復。在信息管理系統(tǒng)中，有時出現(xiàn)數(shù)據(jù)錄入的錯誤。同一個數(shù)據(jù)，被輸入了2次以上，除了主鍵不一樣，其他字段根本上一樣。即查詢重復的記錄。4.3 數(shù)據(jù)錄入錯誤的發(fā)生可能的情況：(1) 數(shù)據(jù)的自動導入，新建系統(tǒng)從老系統(tǒng)中批量導入數(shù)據(jù)，導致大量的重復記錄。(2) 用戶輸入錯誤的數(shù)據(jù)，將一個數(shù)據(jù)輸入的2次。4.4 如SCOTT的員工表EMP, MGR字段是員工的經理的員工號。要查詢每個員

11、工的經理的XX。就需要使用自關聯(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的重復記錄：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 子連接會導致對表的大量的操作，需要很大的內存。其他用戶對自連接的表的操作會等待很長的時間。一般情況下最好不要使用自關聯(lián)。4.8 表的自關聯(lián)的與內關聯(lián)不同，自關聯(lián)至少要2個或2個以上的等式條件。5. 查詢結果的聯(lián)合UNIONUNION ALL- 將多個查詢結果聯(lián)合在一起：- UNION將多個結果集聯(lián)合在一起，去除重復的記錄- UNION ALL將多個結果聯(lián)合在一起，不去除重復的記錄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. 查詢結果的交集INTERSECT：- 將多個查詢結果集聯(lián)合在一起，只保存一樣的記錄。摘除不同的記錄- 語法：select 語句intersectselectintersectselect例子：select*from emp where deptno=10intersectselect*from emp where job='CLERK'7. 查詢結果的差集MINUS：- 將多個結果集聯(lián)合在一起，保存它們差異的記錄，將包含第2個結果集的記錄減去。- 語法：select minusselectminusselect例子：se

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

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

18、wnum函數(shù)。如下例子：select*from(select deptno, sum(sal)from empgroupby deptno)whererownum<=2課前提問：1. 查詢采購金額多于5000元的客戶清單，以及每個客戶的采購金額，并按總采購金額排序 客戶名稱， 采購金額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