SQL實用簡明教程-第7章-多表連接查詢課件

1、第7章 多表連接查詢 重點內(nèi)容： SQL連接語法 連接的處理步驟 連接的類型SELECT select_listFROM Table1name CROSS | NATURAL JOIN Table2nameON Table1name.column operator Table2name.columnWHERE conditions一、連接的語法SQL中為連接提供了關(guān)鍵字JOIN、CROSS JOIN及NATURAL JOIN，用于連接操作。 第一行中的select_list仍然是選擇的列的名稱，但是需要注意的是，如果使用兩個表中有相同名稱的列，則必須限定是哪個表中的列； 第二行中關(guān)鍵字CROS

2、S與NATURAL是可選項，可以使用也可以不使用。如果選擇了CROSS或NATURAL關(guān)鍵字，那么就不能使用ON關(guān)鍵字； 單獨使用JOIN關(guān)鍵字時，需要使用ON關(guān)鍵字來設(shè)定連接的條件； 使用CROSS JOIN時，不能使用關(guān)鍵字ON，因此必須使用WHERE子句設(shè)定連接的條件。因為如果不設(shè)定連接條件，將返回表之間的每一種可能組合(即笛卡爾積)，連接結(jié)果將非常龐大； NATURAL JOIN只有在兩個表有相同名稱的列時才能使用，將在同名列上進行連接。因此，不必使用關(guān)鍵字ON或WHERE子句設(shè)定連接條件。當然，使用WHERE子句限制查找的行仍然是可以的。 二、使用連接的原因連接是關(guān)系模型的關(guān)鍵操作，

3、因為表之間的數(shù)據(jù)并不是互不相關(guān)的，而是存在一定的聯(lián)系的，數(shù)據(jù)庫中僅僅知道各表單獨的數(shù)據(jù)是不足夠的。這時就需要通過連接來發(fā)現(xiàn)數(shù)據(jù)之間的關(guān)系，找出數(shù)據(jù)之間的聯(lián)系，而不僅僅局限于數(shù)據(jù)庫中已有的一些關(guān)系。 使用連接還有一個好處：連接對于結(jié)果沒有特別的限制，具有很大的靈活性。通常可以通過連接創(chuàng)建包含不同表中數(shù)據(jù)的新表或者視圖。這就使得數(shù)據(jù)庫的擴展具有很大的靈活性。 三、處理連接的方法1、笛卡兒積 有的時候，由于連接條件設(shè)置的不好，甚至沒有設(shè)置連接條件，結(jié)果將包含太多的行。這是由于笛卡爾積造成的。從概念上說，連接首先將形成表的笛卡爾積，即形成用于連接的表中所有的行的組合。 用于連接的表越多，其笛卡爾積的結(jié)

4、果將越大，越需要很好的指定連接條件。當然，在一些特殊情況下，如一個表中只包含一個記錄時，可以不指定連接條件。因為這時的笛卡爾積是另外一個表中記錄的個數(shù)。 進行連接操作時，都是先形成表之間的笛卡爾積，獲得笛卡爾積之后再使用指定的連接條件及WHERE子句中其它的限制條件對形成的笛卡爾積進行刪除，保留符合條件的記錄。正確的指定連接條件和限制條件將有助于產(chǎn)生良好的連接結(jié)果。當返回的結(jié)果過多時，請注意檢查連接條件是否合適。 此外，由于連接過程中使用了笛卡爾積，因此連接操作會導致性能的下降。因此，在使用時最好遵循以下一些原則： （1）用于連接的列已經(jīng)創(chuàng)建了索引。因為索引會單獨保存在磁盤上，且將數(shù)據(jù)按照一定

5、順序進行了排列，索引的使用可以加快訪問的速度； （2）用于連接的列具有相同的數(shù)據(jù)類型，包括是否允許空值。如果需要系統(tǒng)自動進行類型轉(zhuǎn)換是需要花費較多時間的，特別是在表中記錄很多時，類型轉(zhuǎn)換所花費的時間將會很多. 三、處理連接的方法2、連接中運算符的使用 在進行連接時，可以使用前面介紹的運算符指定連接的條件。最常見的是使用等號進行連接，也可以使用大于或小于等其它運算符進行連接操作。此外，在進行連接時，還可以使用WHERE子句限制查找的行。3、多表連接 在FROM子句中添加表名，在連接條件中設(shè)定新的連接條件即可實現(xiàn)多表連接。在多表連接時也可以使用JOIN關(guān)鍵字，不過用起來比較繁瑣，不如FROM/WH

6、ERE來的簡便。多表連接一個很重要的應(yīng)用就是結(jié)合連接表進行使用。 三、處理連接的方法4、連接結(jié)果的處理 限制返回的列 當使用連接來連接多個表時，一般來說都不會使用“*”來選擇所有的列，而是在SELECT列表中列出需要返回的列。如果使用“*”包含所有的列，那么顯示時列將按照創(chuàng)建表時指定的順序顯示。 刪除重復 返回連接結(jié)果時，不一定要顯示用于連接的列。需要顯示時，也應(yīng)該注意不要出現(xiàn)重復。 使用別名 在進行連接時，也可以為表或列創(chuàng)建別名以方便使用或使顯示結(jié)果更加清晰。特別是表中有相同名稱的列時，別名的使用可以使代碼更易于輸入，且更易于閱讀。 空值的處理 如果連接表的連接列中存在NULL時，NULL將

7、不會參與連接，即使兩個表的連接列都存在NULL，連接結(jié)果也不會存在NULL。因為，空值代表的是未知、丟失或不可用的值，所以連接時不會對空值進行連接。 三、處理連接的方法5、連接的處理步驟 處理、生成查詢結(jié)果有很多步驟。使用連接操作時的處理過程是比較復雜的，下面是包含連接操作時的處理步驟： (1)為連接表生成笛卡爾積。這是連接操作引起性能下降的主要原因。笛卡爾積是連接表包含的所有行之間的組合。當用于連接的表中記錄較多時，形成的笛卡爾積將非常的大，形成笛卡爾積花費的時間也將更多。 (2)使用WHERE子句從形成的笛卡爾積中去除所有不符合限制條件的行。這時SELECT子句還沒有進行執(zhí)行，因此，連接表

8、形成的笛卡爾積中包含了用于連接的表的所有列。 (3)使用WHERE子句進行刪除后，如果語句中包含了GROUP BY子句，將按照GROUP BY子句中指定的列對剩下的行進行分組。雖然，這時SELECT子句還沒有執(zhí)行，但是如果SELECT列表中包含了聚集函數(shù)將會對分好的組進行計算。 (4)如果語句中使用了HAVING子句，將使用其設(shè)定的條件對分組結(jié)果進行刪除，不符合條件的組將會被刪除。 (5) SELECT子句將被應(yīng)用到余下的數(shù)據(jù)中，表達式也被執(zhí)行，選出在SELECT列表中的列，不在其內(nèi)的列將會被刪除。如果有ORDER BY子句就將結(jié)果按照ORDER BY子句指定的列進行排序顯示出來。 四、連接的

9、類型1、一般的連接 一般的連接通常通過兩個表中的某個特定的列進行連接。最通常使用的是相等連接，當然也可以使用大于連接、小于連接等。 SELECT name, city, pnumber, accepterFROM Employee e, Paccepter aWHERE e.city = a.city AND pno = pnumberResults name city pnumber accepter 龐文凱 杭州 301 科華貿(mào)易公司張峰 杭州 301 科華貿(mào)易公司林雪飛 廈門 104 神州發(fā)展有限公司梁朝陽 杭州 301 科華貿(mào)易公司 林國榮 天津 105 四海貿(mào)易有限公司 王力剛 天津

10、 105 四海貿(mào)易有限公司李大平 北京 204 CESA. COMPANY魏華翔 北京 204 東勝有限公司姜艷成 北京 204 東勝有限公司9rows四、連接的類型2、自然連接 自然連接是一種特殊的連接。自然連接只有在兩個表有相同名稱的列且列的涵義相似時才能使用，將在同名列上進行相等連接。這也就是SQL標準中定義的NATURAL JOIN關(guān)鍵字。使用NATURAL JOIN關(guān)鍵字時，不必使用關(guān)鍵字ON或WHERE子句設(shè)定連接條件。當然，使用WHERE子句限制查找的行仍然是可以的。使用傳統(tǒng)的FROM/WHERE子句，則需要在WHERE子句中指定連接列的相等條件。 SELECT pnumber,

11、 accepter, pmgridFROM Project p NATURAL JOIN Paccepter aSELECT pnumber, accepter, pmgridFROM Project p, Paccepter aWHERE p.pnumber = a.pnumber Resultspnumber accepter pmgrid 101 藍科通訊公司 1002 102 華夏技術(shù)有限公司 1001 103 漢升咨詢公司 1001 104 神州發(fā)展有限公司 1002 105 四海貿(mào)易有限公司 1004201 化天通訊公司 4003202 日勝公司 4004203 華田汽修公司 40

12、03204 東勝有限公司 4003301 科華貿(mào)易公司 4001302 新安有限公司 4001303 華田汽修公司 100112rows 四、連接的類型3、自連接 自連接用于單個表內(nèi)進行比較。使用自連接時，需要注意兩點：一是由于是將表與其自身相連接，因此必須對表設(shè)定別名；二是自連接時會將行與其自身相連，因此必須設(shè)定條件來防止。SELECT DISTINCT a1.accepter, a1.city, a2.accepter, a2.cityFROM Paccepter a1, Paccepter a2WHERE a1.city = a2.city AND a1.accepter a2.acce

13、pter AND a1.city IN(杭州,北京) Results accepter city accepter city 藍科通訊公司 杭州 漢升咨詢公司 杭州藍科通訊公司 杭州 科華貿(mào)易公司 杭州漢升咨詢公司 杭州 藍科通訊公司 杭州漢升咨詢公司 杭州 科華貿(mào)易公司 杭州科華貿(mào)易公司 杭州 藍科通訊公司 杭州科華貿(mào)易公司 杭州 漢升咨詢公司 杭州化天通訊公司 北京 華田汽修公司 北京化天通訊公司 北京 東勝有限公司 北京華田汽修公司 北京 東勝有限公司 北京華田汽修公司 北京 化天通訊公司 北京東勝有限公司 北京 化天通訊公司 北京東勝有限公司 北京 華田汽修公司 北京12rows 四、

14、連接的類型4、外連接 有時候用戶可能希望顯示某個表中所有記錄，包括不符合連接條件的記錄，這正是外連接的作用。使用外連接可以方便的在連接結(jié)果中包含某個表中的其它記錄。SQL中提供了兩個關(guān)鍵字：LEFT OUTER JOIN與RIGHT OUTER JOIN。LEFT OUTER JOIN關(guān)鍵字使外連接顯示LEFT OUTER JOIN左側(cè)的表包含的所有記錄。而RIGHT OUTER JOIN關(guān)鍵字則使外連接顯示RIGHT OUTER JOIN右側(cè)的表所包含的所有記錄。 五、對集合的支持1、UNON運算 UNOIN運算是集合運算中應(yīng)用的最多的，得到了廣泛的支持。UNION運算可以從多個表中組合數(shù)據(jù)

15、進行顯示，但是與連接不同，UNION不是在FROM子句中添加多個表并指定連接條件實現(xiàn)，而是通過將多個查詢的結(jié)果結(jié)合到一起實現(xiàn)的。 SELECT select_listFROM table_list/view_listWHERE conditionsUNION ALLSELECT select_listFROM table_list/view_listWHERE conditions五、對集合的支持2、INTERSECT運算 INTERSECT運算與UNION運算相似，UNION運算中應(yīng)該注意的問題、提高結(jié)果可讀性的方法都可以在INTERSECT運算中使用。INTERSECT運算將返回多個查詢結(jié)果中相同的行。 SELECT cityFROM EmployeeWHERE dno = 2INTERSECTSELECT DISTINCT cityFROM PaccepterResults city 北京 上海廈門3r