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

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