表關(guān)聯(lián)與多表查詢_第1頁(yè)
表關(guān)聯(lián)與多表查詢_第2頁(yè)
表關(guān)聯(lián)與多表查詢_第3頁(yè)
表關(guān)聯(lián)與多表查詢_第4頁(yè)
表關(guān)聯(lián)與多表查詢_第5頁(yè)
已閱讀5頁(yè),還剩3頁(yè)未讀, 繼續(xù)免費(fèi)閱讀

下載本文檔

版權(quán)說明:本文檔由用戶提供并上傳,收益歸屬內(nèi)容提供方,若內(nèi)容存在侵權(quán),請(qǐng)進(jìn)行舉報(bào)或認(rèn)領(lǐng)

文檔簡(jiǎ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 join)1.4 表連接的基本條件:(1) 2個(gè)表必須有公共字段(同名字段或不同名字段)(2) 在一個(gè)表中,這個(gè)公共字段必須是主鍵(PK)1.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 多表查詢的語(yǔ)法select 子句from 表1 別名,表2 別名,視圖 別名,(select 子句)別名where 連接語(yǔ)句 and 其他條件語(yǔ)句oupy by 分類項(xiàng)目having 子句order b

3、y 子句1.10 任務(wù):查詢每個(gè)員工的編號(hào),姓名,部門名稱,部門位置select empno,ename, dname,locfrom emp 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)生無(wú)效的結(jié)果,它將每個(gè)關(guān)聯(lián)的表的記錄跟其他表的所有記錄組合,產(chǎn)生笛卡爾積的數(shù)據(jù)。測(cè)試:select empno,ename, dname,locfrom emp a, dept b1.13 對(duì)OE用戶的測(cè)試 查詢公司庫(kù)存信息,顯示倉(cāng)庫(kù)名稱,

4、產(chǎn)品名稱,庫(kù)存數(shù)量,庫(kù)存金額 select c.WAREHOUSE_NAME, b.PRODUCT_NAME, a.QUANTITY_ON_HAND,a.QUANTITY_ON_HAND*b.LIST_PRICEfrom INVENTORIES a, 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. 自連接的主要功能是查詢表中除了主鍵外,

5、是否有重復(fù)的記錄。3. 任務(wù): 查詢員工表中,有同名,職位相同的員工信息(編號(hào),項(xiàng)目,職位,工資)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次或多次。(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)

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

7、批已經(jīng)完成。 如果與審批人表關(guān)聯(lián)的話,要顯示所有的申請(qǐng),就必須使用外連接。 1.4 外連接語(yǔ)法:(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.dep

8、tno,b.dnamefrom emp a left outer join dept bon a.deptno=b.deptnoselect a.empno,a.deptno,b.deptno,b.dnamefrom emp a right outer join dept bon a.deptno=b.deptnoselect a.ename,b.dname,b.locfrom emp a full outer join dept bon a.deptno=b.deptnoselect a.dname, b.enamefrom dept a full outer join emp bon a.

9、deptno=b.deptno一般情況下,不使用上述的語(yǔ)法,而使用如下的語(yǔ)法:select a.dname, b.enamefrom dept 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.但使用(+)的情況下,無(wú)法實(shí)現(xiàn)全連接。 因?yàn)闊o(wú)法在where 的左右同時(shí)使用(+).select a.ename,b.dnamefrom emp a, dept bwhere a.deptno=b.deptno(+

10、)下列的語(yǔ)句是無(wú)法通過的:select a.ename,b.dnamefrom emp a, 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ù)

11、輸入的2次。(3)4.4 如SCOTT的員工表EMP, MGR字段是員工的經(jīng)理的員工號(hào)。要查詢每個(gè)員工的經(jīng)理的姓名。就需要使用自關(guān)聯(lián)。select a.ename, b.enamefrom emp a, emp bwhere a.mgr=b.empno4.5 查詢emp表中可能同名的員工的記錄。select a.empno,a.enamefrom emp a, emp bwhere a.empno<>b.empno and a.ename=b.ename 4.6 查詢員工表emp的重復(fù)記錄: select a.empno,a.enamefrom emp a, emp bwhere

12、a.empno<>b.empno and a.ename=b.ename and a.job=b.job 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)合(UNION)(UNION ALL)- 將多個(gè)查詢結(jié)果聯(lián)合在一起:- UNION將多個(gè)結(jié)果集聯(lián)合在一起,去除重復(fù)的記錄- UNION ALL將多個(gè)結(jié)果聯(lián)合在一起,不去除重復(fù)的記錄Table 7-1: Set Operators Op

13、eratorDescriptionUNION ALL Returns all the rows retrieved by 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

14、subtracted from the rows retrieved by the first query.- union語(yǔ)法: selectunionselectunionselect例子1:select empno,enamefrom empwhere deptno=10unionselect deptno,dnamefrom dept例子2:select * from emp where deptno=10union select * from emp where job='CLERK' - union all 語(yǔ)法selectunion allselectunion a

15、llselect例子1:select * from emp where deptno=10union allselect * from emp where job='CLERK' 6. 查詢結(jié)果的交集(INTERSECT):- 將多個(gè)查詢結(jié)果集聯(lián)合在一起,只保留相同的記錄。摘除不同的記錄- 語(yǔ)法:select 語(yǔ)句intersectselectintersectselect例子:select * from emp where deptno=10intersectselect * from emp where job='CLERK'7. 查詢結(jié)果的差集(MINUS

16、):- 將多個(gè)結(jié)果集聯(lián)合在一起,保留它們差異的記錄,將包含第2個(gè)結(jié)果集的記錄減去。- 語(yǔ)法:select minusselectminusselect例子:select * 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語(yǔ)句必須有相同的列,如果被查詢的結(jié)果集有不同的列,可使用Oracle的內(nèi)置表達(dá)式合成為相同的列數(shù)。3. select的相對(duì)應(yīng)的列必

17、須為相同的類型。長(zhǎng)度可以不同。4. 在對(duì)輸出進(jìn)行排序時(shí),Oracle使用第1個(gè)select語(yǔ)句的列名給出查詢結(jié)果,因?yàn)椋挥械?個(gè)select的列作為查詢結(jié)果,因此只有第一個(gè)select的列名出現(xiàn)在order by 子句中。8. Oracle9i實(shí)現(xiàn)SQL Server 2000 中的select top n 的SQL語(yǔ)句:在Oracle9i中沒有類似的select top n 的語(yǔ)句。但是它提供了ROWNUM內(nèi)置函數(shù)。可以實(shí)現(xiàn)top n的查詢語(yǔ)句。Select From Where rownum<=nROWNUM是Oracle在做查詢時(shí)自動(dòng)計(jì)算的。它會(huì)隨著記錄集的變化而動(dòng)態(tài)變化。ROW

18、NUM返回第一次從表中選擇時(shí)返回行的序列號(hào)。第1行的ROWNUM為1。如果想返回一個(gè)復(fù)雜查詢的結(jié)果集的top n, 要把此結(jié)果集作為中間結(jié)果集放在from中,再使用rownum函數(shù)。如下例子:select * from ( select deptno, sum(sal)from empgroup by deptno)where rownum<=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 totalRMBfrom CUSTOMERS a, ORDERS b, ORDER_ITEMS cwhere a.customer_ID=b.Customer_ID a

溫馨提示

  • 1. 本站所有資源如無(wú)特殊說明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請(qǐng)下載最新的WinRAR軟件解壓。
  • 2. 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請(qǐng)聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶所有。
  • 3. 本站RAR壓縮包中若帶圖紙,網(wǎng)頁(yè)內(nèi)容里面會(huì)有圖紙預(yù)覽,若沒有圖紙預(yù)覽就沒有圖紙。
  • 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
  • 5. 人人文庫(kù)網(wǎng)僅提供信息存儲(chǔ)空間,僅對(duì)用戶上傳內(nèi)容的表現(xiàn)方式做保護(hù)處理,對(duì)用戶上傳分享的文檔內(nèi)容本身不做任何修改或編輯,并不能對(duì)任何下載內(nèi)容負(fù)責(zé)。
  • 6. 下載文件中如有侵權(quán)或不適當(dāng)內(nèi)容,請(qǐng)與我們聯(lián)系,我們立即糾正。
  • 7. 本站不保證下載資源的準(zhǔn)確性、安全性和完整性, 同時(shí)也不承擔(dān)用戶因使用這些下載資源對(duì)自己和他人造成任何形式的傷害或損失。

評(píng)論

0/150

提交評(píng)論