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

經(jīng)典表關聯(lián)與多表查詢目的：掌握從多個表查詢數(shù)據(jù)的基本知識了解和學習外連接(outjoin）掌握內(nèi)連接授課內(nèi)容：對多于一個表的數(shù)據(jù)查詢1.1現(xiàn)實情況中，在數(shù)據(jù)庫應用中，數(shù)據(jù)存在于多個相關聯(lián)的表中.基本上沒有數(shù)據(jù)只存謝謝閱讀在于一個表中的情況.小的應用系統(tǒng)一般也有十幾個表,大型系統(tǒng)一般有上千個表。1.2你經(jīng)常要作的就是在多個表中進行數(shù)據(jù)查詢。謝謝閱讀1.3Oracle對多表查詢使用表連接的技術（tablejoin)謝謝閱讀1.4表連接的基本條件：2個表必須有公共字段(同名字段或不同名字段）在一個表中，這個公共字段必須是主鍵（PK)1.5二個表中的公共字段,在一個表中是主鍵,在另外一個表中就是外鍵（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其他條件語句［oupyby分類項目］[having子句]［orderby子句]1.10任務：查詢每個員工的編號，姓名，部門名稱，部門位置selectempno,ename，dname,loc感謝閱讀fromempa,deptbwherea。DEPTNO=b.DEPTNO1.11多表查詢的原則：對N個表連接,至少要有N-1個相等的條件.而且每個表的公共字謝謝閱讀段必須出現(xiàn)一次。1.12多表關聯(lián)中，如果沒有指定關聯(lián)等式，將產(chǎn)生無效的結(jié)果，它將每個關聯(lián)的表的記錄跟其他表的所有記錄組合，產(chǎn)生笛卡爾積的數(shù)據(jù).感謝閱讀測試：selectempno,ename，dname，loc精品文檔放心下載fromempa,deptb1.13對OE用戶的測試查詢公司庫存信息,顯示倉庫名稱，產(chǎn)品名稱,庫存數(shù)量,庫存金額謝謝閱讀select c。WAREHOUSE_NAME,b。PRODUCT_NAME，a.QUANTITY_ON_HAND,a。精品文檔放心下載QUANTITY_ON_HAND*b.LIST_PRICE謝謝閱讀fromINVENTORIESa,PRODUCT_INFORMATIONb,WAREHOUSESc感謝閱讀wherea.WAREHOUSE_ID=c.WAREHOUSE_IDanda。PRODUCT_ID=b。PRODUCT_ID精品文檔放心下載內(nèi)連接（selfjoin）當多表關聯(lián)使用一個表進行數(shù)據(jù)進行數(shù)據(jù)查詢，這種連接叫自連接.精品文檔放心下載自連接的主要功能是查詢表中除了主鍵外,是否有重復的記錄.精品文檔放心下載任務：查詢員工表中,有同名,職位相同的員工信息(編號,項目，職位，工資)selecta.empno，a。ename，a。job謝謝閱讀fromempa，empbwherea。empno<〉b.empnoanda。deptno=b。deptnoanda。job=b。job感謝閱讀日常生活中在數(shù)據(jù)錄入時產(chǎn)生的錯誤由于工作失誤,一個數(shù)據(jù)錄入到系統(tǒng)2次或多次。一般在進行自動的數(shù)據(jù)導入時,產(chǎn)生大量的重復記錄。子連接的要求：自連接至少要2個或2個以上的等式條件,一個用于關聯(lián),其他用于表示重復的數(shù)據(jù)。精品文檔放心下載外連接（outjoin）：1.1內(nèi)連接是關聯(lián)的表的公共字段值必須相同,所有不同的值的記錄都沒有了。感謝閱讀1.2外連接是值一個表的中的公共字段的值可以不與另一個表的公共字段值相同.一般精品文檔放心下載時它是null。1.3任務：查詢員工表，顯示員工的項目,部門名稱,部門位置，要求顯示所有的員工，即使員工沒有部門。謝謝閱讀selecta.ename，b。dname，b。loc感謝閱讀fromempaleftouterjoindeptb感謝閱讀ona。deptno=b。deptno注：此任務無法使用正常的內(nèi)連接。因為有一個員工沒有部門，它的部門編號為空。精品文檔放心下載常見的任務如:信息系統(tǒng)中的文檔，申請審批,當剛創(chuàng)建時，所有審批信息為null。.但有的審批已經(jīng)完成。如果與審批人表關聯(lián)的話，要顯示所有的申請,就必須使用外連接。感謝閱讀1.4外連接語法：(1）左連接：取出左邊的表的所有記錄select子句from表1 leftouterjoin表2謝謝閱讀on表1。公共字段＝表2。公共字段(2）右連接：取出右邊表的所有記錄select子句from表1 rightouterjoin表2謝謝閱讀on表1。公共字段＝表2.公共字段（3)全連接（左右連接）：左右兩邊的表的記錄都取。感謝閱讀select子句from表1 full outerjoin表2精品文檔放心下載on表1。公共字段＝表2.公共字段selecta.empno，a.deptno,b。deptno,b.dname感謝閱讀fromempaleftouterjoindeptb精品文檔放心下載ona.deptno=b.deptnoselecta.empno，a.deptno，b.deptno,b.dname謝謝閱讀fromemparightouterjoindeptb感謝閱讀ona。deptno=b.deptnoselecta。ename,b。dname,b。loc謝謝閱讀fromempafullouterjoindeptb精品文檔放心下載ona.deptno=b。deptnoselecta。dname，b.enamefromdeptafullouterjoinempb感謝閱讀ona。deptno=b.deptno一般情況下，不使用上述的語法，而使用如下的語法：selecta.dname,b.enamefromdepta,empbwherea。deptno（+）=b。deptno —-精品文檔放心下載一般情況情況下，(+）放在關聯(lián)表的主鍵的一側(cè)，才有實際的意義。感謝閱讀沒有(+）的表的取所有的記錄，關聯(lián)的表如果有記錄對應就顯示關聯(lián)的值,沒有關聯(lián)的值顯示null.精品文檔放心下載但使用（+）的情況下,無法實現(xiàn)全連接。因為無法在where的左右同時使用（+)。謝謝閱讀selecta.ename,b。dnamefromempa，deptbwherea.deptno=b。deptno(+)謝謝閱讀下列的語句是無法通過的：selecta.ename,b.dnamefromempa，deptbwherea。deptno（+）=b。deptno（+)精品文檔放心下載1.5任務：自關聯(lián)（self-join)4.1有些情況下,需要關聯(lián)一個表，這種關聯(lián)叫自關聯(lián).感謝閱讀4.2自關聯(lián)經(jīng)常使用的一般是查看表中的記錄是否重復。在信息管理系統(tǒng)中，有時出現(xiàn)數(shù)據(jù)錄入的錯誤.同一個數(shù)據(jù)，被輸入了2次以上，除了主鍵不一樣，其他字段基本上一樣.即查詢重復的記錄。謝謝閱讀4.3數(shù)據(jù)錄入錯誤的發(fā)生可能的情況：數(shù)據(jù)的自動導入，新建系統(tǒng)從老系統(tǒng)中批量導入數(shù)據(jù)，導致大量的重復記錄.感謝閱讀用戶輸入錯誤的數(shù)據(jù),將一個數(shù)據(jù)輸入的2次。4.4如SCOTT的員工表EMP，MGR字段是員工的經(jīng)理的員工號。要查詢每個員工的經(jīng)理的姓名。就需要使用自關聯(lián)。謝謝閱讀selecta。ename,b。enamefromempa,empbwherea。mgr=b.empno4.5查詢emp表中可能同名的員工的記錄。selecta.empno,a。enamefromempa，empbwherea.empno<>b。empnoanda.ename=b。ename感謝閱讀4.6查詢員工表emp的重復記錄：selecta.empno,a。enamefromempa，empbwherea。empno〈〉b。empnoanda。ename=b.enameanda。job=b。jobanda.sal=b。sal謝謝閱讀4.7子連接會導致對表的大量的操作，需要很大的內(nèi)存。其他用戶對自連接的表的操作謝謝閱讀會等待很長的時間.一般情況下最好不要使用自關聯(lián)。4.8表的自關聯(lián)的與內(nèi)關聯(lián)不同，自關聯(lián)至少要2個或2個以上的等式條件.謝謝閱讀查詢結(jié)果的聯(lián)合（UNION）(UNIONALL)將多個查詢結(jié)果聯(lián)合在一起：UNION將多個結(jié)果集聯(lián)合在一起，去除重復的記錄UNIONALL將多個結(jié)果聯(lián)合在一起,不去除重復的記錄感謝閱讀Table7—1:SetOperatorsOperator

DescriptionUNIONALL

Returnsalltherowsretrievedbythequeries,includingduplicaterows。謝謝閱讀UNION

Returnsallnon-duplicaterowsretrievedbythequeries。感謝閱讀INTERSECT

Returnsrowsthatareretrievedbybothqueries.精品文檔放心下載MINUS

Returnstheremainingrowswhentherowsretrievedbythesecondqueryaresubtractedfromtherowsretrievedbythefirstquery.謝謝閱讀-

union語法：selectunionselectunionselect例子1：selectempno,enamefromempwheredeptno=10unionselectdeptno，dnamefromdept例子2：select＊fromempwheredeptno=10謝謝閱讀unionselect＊fromempwherejob='CLERK'感謝閱讀unionall語法selectunionallselect精品文檔放心下載unionallselect例子1:select*fromempwheredeptno=10感謝閱讀unionallselect*fromempwherejob='CLERK’謝謝閱讀查詢結(jié)果的交集(INTERSECT)：將多個查詢結(jié)果集聯(lián)合在一起,只保留相同的記錄。摘除不同的記錄精品文檔放心下載語法：select語句intersectselect精品文檔放心下載intersectselect例子：select*fromempwheredeptno=10謝謝閱讀intersectselect*fromempwherejob='CLERK'精品文檔放心下載查詢結(jié)果的差集（MINUS）:將多個結(jié)果集聯(lián)合在一起，保留它們差異的記錄，將包含第2個結(jié)果集的記錄減去.感謝閱讀語法：selectminusselectminusselect例子：select＊fromempwheredeptno=10感謝閱讀minusselect*fromempwherejob='CLERK'精品文檔放心下載注:Oracle在合并2個結(jié)果集時,Oracle并不關心合并運算符的任何一邊的列名，合并的結(jié)果集以第一個結(jié)果集的列名為新的列名.謝謝閱讀select語句必須有相同的列，如果被查詢的結(jié)果集有不同的列，可使用Oracle的內(nèi)置表達式合成為相同的列數(shù)。精品文檔放心下載select的相對應的列必須為相同的類型。長度可以不同。精品文檔放心下載在對輸出進行排序時，Oracle使用第1個select語句的列名給出查詢結(jié)果，因為，只有第1個select的列作為查詢結(jié)果，因此只有第一個select的列名出現(xiàn)在orderby子句中.謝謝閱讀Oracle9i實現(xiàn)SQLServer2000中的selecttopn的SQL語句：謝謝閱讀Oracle9i中沒有類似的selecttopn的語句。但是它提供了ROWNUM內(nèi)置函數(shù).謝謝閱讀可以實現(xiàn)topn的查詢語句。SelectFromWhererownum<=nROWNUM是Oracle在做查詢時自動計算的。它會隨著記錄集的變化而動態(tài)變化。感謝閱讀ROWNUM返回第一次從表中選擇時返回行的序列號.第1行的ROWNUM為1。精品文檔放心下載如果想返回一個復雜查詢的結(jié)果集的topn,要把此結(jié)果集作為中間結(jié)果集放在from中，再使用rownum函數(shù)。如下例子：精品文檔放心下載select*from（selectdeptno，sum（sal）fromempgroupbydeptno)whererownum〈=2課前提問：查詢采購金額多于5000元的客戶清單，以及每個客戶的采購金額,并按總采購金額排序(客戶名稱,采購金額）精品文檔放心下載selecta.CUST_FIRST_NAME｜|’'｜|CUST_LAST_NAME,sum(c.UNIT_PRICE*C.QUANTITY)astotalRMBfromCUSTOMERSa，ORDERSb,ORDER_ITEMSc精品文檔放心下載wherea.customer_ID=b.Customer_I