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

經(jīng)典表關(guān)聯(lián)與多表查詢目的：掌握從多個(gè)表查詢數(shù)據(jù)的基本知識(shí)了解和學(xué)習(xí)外連接(outjoin)掌握內(nèi)連接授課內(nèi)容：對(duì)多于一個(gè)表的數(shù)據(jù)查詢現(xiàn)實(shí)情況中，在數(shù)據(jù)庫應(yīng)用中，數(shù)據(jù)存在于多個(gè)相關(guān)聯(lián)的表中?；旧蠜]有數(shù)據(jù)只存在于一個(gè)表中的情況。小的應(yīng)用系統(tǒng)一般也有十幾個(gè)表，大型系統(tǒng)一般有上千個(gè)表。你經(jīng)常要作的就是在多個(gè)表中進(jìn)行數(shù)據(jù)查詢。Oracle對(duì)多表查詢使用表連接的技術(shù)（tablejoin）表連接的基本條件：2個(gè)表必須有公共字段（同名字段或不同名字段）在一個(gè)表中，這個(gè)公共字段必須是主鍵（PK）二個(gè)表中的公共字段，在一個(gè)表中是主鍵，在另外一個(gè)表中就是外鍵(FK)。二表關(guān)聯(lián)中，公共字段是主鍵的表稱為父表（主表）。是外鍵的表稱為子表（詳細(xì)表）。研究一下scott下的emp和dept表的關(guān)系。研究一下oe下的表：CATEGORIES_TABCUSTOMERSINVENTORIESORDERSORDER_ITEMSPRODUCT_DESCRIPTIONSPRODUCT_INFORMATION多表查詢的語法select子句from表1[別名]，表2[別名]，視圖[別名]，（select子句）別名where連接語句and其他條件語句[oupyby分類項(xiàng)目][having子句][orderby子句]任務(wù)：查詢每個(gè)員工的編號(hào)，姓名，部門名稱，部門位置selectempno,ename,dname,locfromempa,deptbwhere=多表查詢的原則：對(duì)N個(gè)表連接，至少要有N-1個(gè)相等的條件。而且每個(gè)表的公共字段必須出現(xiàn)一次。多表關(guān)聯(lián)中，如果沒有指定關(guān)聯(lián)等式，將產(chǎn)生無效的結(jié)果，它將每個(gè)關(guān)聯(lián)的表的記錄跟其他表的所有記錄組合，產(chǎn)生笛卡爾積的數(shù)據(jù)。測試：selectempno,ename,dname,locfromempa,deptb對(duì)OE用戶的測試查詢公司庫存信息，顯示倉庫名稱，產(chǎn)品名稱，庫存數(shù)量，庫存金額select,,,*fromINVENTORIESa,PRODUCT_INFORMATIONb,WAREHOUSEScwhere=and=內(nèi)連接(selfjoin)當(dāng)多表關(guān)聯(lián)使用一個(gè)表進(jìn)行數(shù)據(jù)進(jìn)行數(shù)據(jù)查詢，這種連接叫自連接。自連接的主要功能是查詢表中除了主鍵外，是否有重復(fù)的記錄。任務(wù)：查詢員工表中，有同名，職位相同的員工信息（編號(hào)，項(xiàng)目，職位，工資）select,,fromempa,empbwhere<>and=and=日常生活中在數(shù)據(jù)錄入時(shí)產(chǎn)生的錯(cuò)誤由于工作失誤，一個(gè)數(shù)據(jù)錄入到系統(tǒng)2次或多次。一般在進(jìn)行自動(dòng)的數(shù)據(jù)導(dǎo)入時(shí)，產(chǎn)生大量的重復(fù)記錄。子連接的要求：自連接至少要2個(gè)或2個(gè)以上的等式條件，一個(gè)用于關(guān)聯(lián)，其他用于表示重復(fù)的數(shù)據(jù)。外連接(outjoin):內(nèi)連接是關(guān)聯(lián)的表的公共字段值必須相同，所有不同的值的記錄都沒有了。外連接是值一個(gè)表的中的公共字段的值可以不與另一個(gè)表的公共字段值相同。一般時(shí)它是null.任務(wù)：查詢員工表，顯示員工的項(xiàng)目，部門名稱，部門位置，要求顯示所有的員工，即使員工沒有部門。select,,fromempaleftouterjoindeptbon=注：此任務(wù)無法使用正常的內(nèi)連接。因?yàn)橛幸粋€(gè)員工沒有部門，它的部門編號(hào)為空。常見的任務(wù)如：信息系統(tǒng)中的文檔，申請審批，當(dāng)剛創(chuàng)建時(shí)，所有審批信息為null..但有的審批已經(jīng)完成。如果與審批人表關(guān)聯(lián)的話，要顯示所有的申請，就必須使用外連接。外連接語法：（1）左連接：取出左邊的表的所有記錄select子句from表1leftouterjoin表2on表1.公共字段＝表2.公共字段（2）右連接:取出右邊表的所有記錄select子句from表1rightouterjoin表2on表1.公共字段＝表2.公共字段（3）全連接（左右連接）：左右兩邊的表的記錄都取。select子句from表1fullouterjoin表2on表1.公共字段＝表2.公共字段select,,,fromempaleftouterjoindeptbon=select,,,fromemparightouterjoindeptbon=select,,fromempafullouterjoindeptbon=select,fromdeptafullouterjoinempbon=一般情況下，不使用上述的語法，而使用如下的語法：select,fromdepta,empbwhere(+)=--一般情況情況下，(+)放在關(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,fromempa,deptbwhere=(+)下列的語句是無法通過的：select,fromempa,deptbwhere(+)=(+)任務(wù)：自關(guān)聯(lián)(self-join)有些情況下，需要關(guān)聯(lián)一個(gè)表，這種關(guān)聯(lián)叫自關(guān)聯(lián)。自關(guān)聯(lián)經(jīng)常使用的一般是查看表中的記錄是否重復(fù)。在信息管理系統(tǒng)中，有時(shí)出現(xiàn)數(shù)據(jù)錄入的錯(cuò)誤。同一個(gè)數(shù)據(jù)，被輸入了2次以上，除了主鍵不一樣，其他字段基本上一樣。即查詢重復(fù)的記錄。數(shù)據(jù)錄入錯(cuò)誤的發(fā)生可能的情況：數(shù)據(jù)的自動(dòng)導(dǎo)入，新建系統(tǒng)從老系統(tǒng)中批量導(dǎo)入數(shù)據(jù)，導(dǎo)致大量的重復(fù)記錄。用戶輸入錯(cuò)誤的數(shù)據(jù)，將一個(gè)數(shù)據(jù)輸入的2次。如SCOTT的員工表EMP,MGR字段是員工的經(jīng)理的員工號(hào)。要查詢每個(gè)員工的經(jīng)理的姓名。就需要使用自關(guān)聯(lián)。select,fromempa,empbwhere=查詢emp表中可能同名的員工的記錄。select,fromempa,empbwhere<>and=查詢員工表emp的重復(fù)記錄：select,fromempa,empbwhere<>and=and=and=子連接會(huì)導(dǎo)致對(duì)表的大量的操作，需要很大的內(nèi)存。其他用戶對(duì)自連接的表的操作會(huì)等待很長的時(shí)間。一般情況下最好不要使用自關(guān)聯(lián)。表的自關(guān)聯(lián)的與內(nèi)關(guān)聯(lián)不同，自關(guān)聯(lián)至少要2個(gè)或2個(gè)以上的等式條件。查詢結(jié)果的聯(lián)合（UNION）（UNIONALL）將多個(gè)查詢結(jié)果聯(lián)合在一起：UNION將多個(gè)結(jié)果集聯(lián)合在一起，去除重復(fù)的記錄UNIONALL將多個(gè)結(jié)果聯(lián)合在一起，不去除重復(fù)的記錄Table7-1:SetOperatorsOperatorDescriptionUNIONALLReturnsalltherowsretrievedbythequeries,includingduplicaterows.UNIONReturnsallnon-duplicaterowsretrievedbythequeries.INTERSECTReturnsrowsthatareretrievedbybothqueries.MINUSReturnstheremainingrowswhentherowsretrievedbythesecondqueryaresubtractedfromtherowsretrievedbythefirstquery.union語法：selectunionselectunionselect例子1：selectempno,enamefromempwheredeptno=10unionselectdeptno,dnamefromdept例子2：select*fromempwheredeptno=10unionselect*fromempwherejob='CLERK'-unionall語法selectunionallselectunionallselect例子1：select*fromempwheredeptno=10unionallselect*fromempwherejob='CLERK'查詢結(jié)果的交集（INTERSECT）：將多個(gè)查詢結(jié)果集聯(lián)合在一起，只保留相同的記錄。摘除不同的記錄語法：select語句intersectselectintersectselect例子：select*fromempwheredeptno=10intersectselect*fromempwherejob='CLERK'查詢結(jié)果的差集（MINUS）：將多個(gè)結(jié)果集聯(lián)合在一起，保留它們差異的記錄，將包含第2個(gè)結(jié)果集的記錄減去。語法：selectminusselectminusselect例子：select*fromempwheredeptno=10minusselect*fromempwherejob='CLERK'注：Oracle在合并2個(gè)結(jié)果集時(shí)，Oracle并不關(guān)心合并運(yùn)算符的任何一邊的列名，合并的結(jié)果集以第一個(gè)結(jié)果集的列名為新的列名。select語句必須有相同的列，如果被查詢的結(jié)果集有不同的列，可使用Oracle的內(nèi)置表達(dá)式合成為相同的列數(shù)。select的相對(duì)應(yīng)的列必須為相同的類型。長度可以不同。在對(duì)輸出進(jìn)行排序時(shí)，Oracle使用第1個(gè)select語句的列名給出查詢結(jié)果，因?yàn)?，只有?個(gè)select的列作為查詢結(jié)果，因此只有第一個(gè)select的列名出現(xiàn)在orderby子句中。Oracle9i實(shí)現(xiàn)SQLServer2000中的selecttopn的SQL語句：在Oracle9i中沒有類似的selecttopn的語句。但是它提供了ROWNUM內(nèi)置函數(shù)?？梢詫?shí)現(xiàn)topn的查詢語句。SelectFromWhererownum<=nROWNUM是Oracle在做查詢時(shí)自動(dòng)計(jì)算的。它會(huì)隨著記錄集的變化而動(dòng)態(tài)變化。ROWNUM返回第一次從表中選擇時(shí)返回行的序列號(hào)。第1行的ROWNUM為1。如果想返回一個(gè)復(fù)雜查詢的結(jié)果集的topn,要把此結(jié)果集作為中間結(jié)果集放在from中，再使用rownum函數(shù)。如下例子：select*from(selectdeptno,sum(sal)fromempgroupbydeptno)whererownum<=2課前提問：查詢采購金額多于5000元的客戶清單，以及每個(gè)客戶的采購金額，并按總采購金額排序（客戶名稱，采購金額）select||''||CUST_LAST_NAME,sum*astotalRMBfromCUSTOMERSa,ORDERSb,ORDER_ITEMScwhere=and=groupby||''||CUST_LAST_NAMEhavingsum*>5000orderbytotalRMBdesc查詢總銷售額大于5000元的產(chǎn)品清單，以及每種產(chǎn)品的累計(jì)銷售額，按總銷售額降序排序（產(chǎn)品名稱，銷售金額）select,sum*astotalRMBfromPRODUCT_INFORMATION