《數(shù)據(jù)庫(kù)中的連接》PPT課件.ppt

第7章 多表連接查詢(xún) 重點(diǎn)內(nèi)容： SQL連接語(yǔ)法 連接的處理步驟 連接的類(lèi)型 SELECT select_list FROM Table1name CROSS | NATURAL JOIN Table2name ON Table1name.column operator Table2name.column WHERE conditions 一、連接的語(yǔ)法 SQL中為連接提供了關(guān)鍵字JOIN、CROSS JOIN及NATURAL JOIN，用于連接 操作。 第一行中的select_list仍然是選擇的列的名稱(chēng)，但是需要注意的是 ，如果使用兩個(gè)表中有相同名稱(chēng)的列，則必須限定是哪個(gè)表中的列； 第二行中關(guān)鍵字CROSS與NATURAL是可選項(xiàng)，可以使用也可以不使用。 如果選擇了CROSS或NATURAL關(guān)鍵字，那么就不能使用ON關(guān)鍵字； 單獨(dú)使用JOIN關(guān)鍵字時(shí)，需要使用ON關(guān)鍵字來(lái)設(shè)定連接的條件； 使用CROSS JOIN時(shí)，不能使用關(guān)鍵字ON，因此必須使用WHERE子句設(shè) 定連接的條件。因?yàn)槿绻辉O(shè)定連接條件，將返回表之間的每一種可能組 合(即笛卡爾積)，連接結(jié)果將非常龐大； NATURAL JOIN只有在兩個(gè)表有相同名稱(chēng)的列時(shí)才能使用，將在同名列 上進(jìn)行連接。因此，不必使用關(guān)鍵字ON或WHERE子句設(shè)定連接條件。當(dāng)然， 使用WHERE子句限制查找的行仍然是可以的。 二、使用連接的原因 連接是關(guān)系模型的關(guān)鍵操作，因?yàn)楸碇g的數(shù)據(jù)并不是互不相關(guān) 的，而是存在一定的聯(lián)系的，數(shù)據(jù)庫(kù)中僅僅知道各表單獨(dú)的數(shù)據(jù)是不 足夠的。這時(shí)就需要通過(guò)連接來(lái)發(fā)現(xiàn)數(shù)據(jù)之間的關(guān)系，找出數(shù)據(jù)之間 的聯(lián)系，而不僅僅局限于數(shù)據(jù)庫(kù)中已有的一些關(guān)系。 使用連接還有一個(gè)好處：連接對(duì)于結(jié)果沒(méi)有特別的限制，具有很 大的靈活性。通?？梢酝ㄟ^(guò)連接創(chuàng)建包含不同表中數(shù)據(jù)的新表或者視 圖。這就使得數(shù)據(jù)庫(kù)的擴(kuò)展具有很大的靈活性。 三、處理連接的方法 1、笛卡兒積 有的時(shí)候，由于連接條件設(shè)置的不好，甚至沒(méi)有設(shè)置連接條件，結(jié)果將包含太多 的行。這是由于笛卡爾積造成的。從概念上說(shuō)，連接首先將形成表的笛卡爾積，即形成 用于連接的表中所有的行的組合。 用于連接的表越多，其笛卡爾積的結(jié)果將越大，越需要很好的指定連接條件。當(dāng)然 ，在一些特殊情況下，如一個(gè)表中只包含一個(gè)記錄時(shí)，可以不指定連接條件。因?yàn)檫@時(shí) 的笛卡爾積是另外一個(gè)表中記錄的個(gè)數(shù)。 進(jìn)行連接操作時(shí)，都是先形成表之間的笛卡爾積，獲得笛卡爾積之后再使用指定 的連接條件及WHERE子句中其它的限制條件對(duì)形成的笛卡爾積進(jìn)行刪除，保留符合條件 的記錄。正確的指定連接條件和限制條件將有助于產(chǎn)生良好的連接結(jié)果。當(dāng)返回的結(jié)果 過(guò)多時(shí)，請(qǐng)注意檢查連接條件是否合適。 此外，由于連接過(guò)程中使用了笛卡爾積，因此連接操作會(huì)導(dǎo)致性能的下降。因此 ，在使用時(shí)最好遵循以下一些原則： （1）用于連接的列已經(jīng)創(chuàng)建了索引。因?yàn)樗饕龝?huì)單獨(dú)保存在磁盤(pán)上，且將數(shù)據(jù) 按照一定順序進(jìn)行了排列，索引的使用可以加快訪(fǎng)問(wèn)的速度； （2）用于連接的列具有相同的數(shù)據(jù)類(lèi)型，包括是否允許空值。如果需要系統(tǒng)自 動(dòng)進(jìn)行類(lèi)型轉(zhuǎn)換是需要花費(fèi)較多時(shí)間的，特別是在表中記錄很多時(shí)，類(lèi)型轉(zhuǎn)換所花費(fèi)的 時(shí)間將會(huì)很多. 三、處理連接的方法 2、連接中運(yùn)算符的使用 在進(jìn)行連接時(shí)，可以使用前面介紹的運(yùn)算符指定連接的條件。最 常見(jiàn)的是使用等號(hào)進(jìn)行連接，也可以使用大于或小于等其它運(yùn)算符進(jìn) 行連接操作。此外，在進(jìn)行連接時(shí)，還可以使用WHERE子句限制查找 的行。 3、多表連接 在FROM子句中添加表名，在連接條件中設(shè)定新的連接條件即可實(shí) 現(xiàn)多表連接。在多表連接時(shí)也可以使用JOIN關(guān)鍵字，不過(guò)用起來(lái)比較 繁瑣，不如FROM/WHERE來(lái)的簡(jiǎn)便。多表連接一個(gè)很重要的應(yīng)用就是結(jié) 合連接表進(jìn)行使用。 三、處理連接的方法 4、連接結(jié)果的處理 限制返回的列 當(dāng)使用連接來(lái)連接多個(gè)表時(shí)，一般來(lái)說(shuō)都不會(huì)使用“*”來(lái)選擇所 有的列，而是在SELECT列表中列出需要返回的列。如果使用“*” 包含所有的列，那么顯示時(shí)列將按照創(chuàng)建表時(shí)指定的順序顯示。 刪除重復(fù) 返回連接結(jié)果時(shí)，不一定要顯示用于連接的列。需要顯示時(shí)，也應(yīng) 該注意不要出現(xiàn)重復(fù)。 使用別名 在進(jìn)行連接時(shí)，也可以為表或列創(chuàng)建別名以方便使用或使顯示結(jié)果 更加清晰。特別是表中有相同名稱(chēng)的列時(shí)，別名的使用可以使代碼 更易于輸入，且更易于閱讀。 空值的處理 如果連接表的連接列中存在NULL時(shí)，NULL將不會(huì)參與連接，即使 兩個(gè)表的連接列都存在NULL，連接結(jié)果也不會(huì)存在NULL。因?yàn)椋?空值代表的是未知、丟失或不可用的值，所以連接時(shí)不會(huì)對(duì)空值進(jìn) 行連接。 三、處理連接的方法 5、連接的處理步驟 處理、生成查詢(xún)結(jié)果有很多步驟。使用連接操作時(shí)的處理過(guò)程是比較復(fù)雜的，下 面是包含連接操作時(shí)的處理步驟： (1)為連接表生成笛卡爾積。這是連接操作引起性能下降的主要原因。笛卡爾積 是連接表包含的所有行之間的組合。當(dāng)用于連接的表中記錄較多時(shí)，形成的笛卡爾積將 非常的大，形成笛卡爾積花費(fèi)的時(shí)間也將更多。 (2)使用WHERE子句從形成的笛卡爾積中去除所有不符合限制條件的行。這時(shí) SELECT子句還沒(méi)有進(jìn)行執(zhí)行，因此，連接表形成的笛卡爾積中包含了用于連接的表的所 有列。 (3)使用WHERE子句進(jìn)行刪除后，如果語(yǔ)句中包含了GROUP BY子句，將按照GROUP BY子句中指定的列對(duì)剩下的行進(jìn)行分組。雖然，這時(shí)SELECT子句還沒(méi)有執(zhí)行，但是如果 SELECT列表中包含了聚集函數(shù)將會(huì)對(duì)分好的組進(jìn)行計(jì)算。 (4)如果語(yǔ)句中使用了HAVING子句，將使用其設(shè)定的條件對(duì)分組結(jié)果進(jìn)行刪除， 不符合條件的組將會(huì)被刪除。 (5) SELECT子句將被應(yīng)用到余下的數(shù)據(jù)中，表達(dá)式也被執(zhí)行，選出在SELECT列表中 的列，不在其內(nèi)的列將會(huì)被刪除。如果有ORDER BY子句就將結(jié)果按照ORDER BY子句指定 的列進(jìn)行排序顯示出來(lái)。 四、連接的類(lèi)型 1、一般的連接 一般的連接通常通過(guò)兩個(gè)表中的某個(gè)特定的列進(jìn)行連接。最通常 使用的是相等連接，當(dāng)然也可以使用大于連接、小于連接等。 SELECT name, city, pnumber, accepter FROM Employee e, Paccepter a WHERE e.city = a.city AND pno = pnumber Results name city pnumber accepter 龐文凱 杭州 301 科華貿(mào)易公司 張峰 杭州 301 科華貿(mào)易公司 林雪飛 廈門(mén) 104 神州發(fā)展有限公司 梁朝陽(yáng) 杭州 301 科華貿(mào)易公司 林國(guó)榮 天津 105 四海貿(mào)易有限公司 王力剛 天津 105 四海貿(mào)易有限公司 李大平 北京 204 CESA. COMPANY 魏華翔 北京 204 東勝有限公司 姜艷成 北京 204 東勝有限公司 9rows 四、連接的類(lèi)型 2、自然連接 自然連接是一種特殊的連接。自然連接只有在兩個(gè)表有相同名稱(chēng)的列且 列的涵義相似時(shí)才能使用，將在同名列上進(jìn)行相等連接。這也就是SQL標(biāo)準(zhǔn)中 定義的NATURAL JOIN關(guān)鍵字。使用NATURAL JOIN關(guān)鍵字時(shí)，不必使用關(guān)鍵字 ON或WHERE子句設(shè)定連接條件。當(dāng)然，使用WHERE子句限制查找的行仍然是可 以的。使用傳統(tǒng)的FROM/WHERE子句，則需要在WHERE子句中指定連接列的相等 條件。 SELECT pnumber, accepter, pmgrid FROM Project p NATURAL JOIN Paccepter a SELECT pnumber, accepter, pmgrid FROM Project p, Paccepter a WHERE p.pnumber = a.pnumber Results pnumber accepter pmgrid 101 藍(lán)科通訊公司 1002 102 華夏技術(shù)有限公司 1001 103 漢升咨詢(xún)公司 1001 104 神州發(fā)展有限公司 1002 105 四海貿(mào)易有限公司 1004 201 化天通訊公司 4003 202 日勝公司 4004 203 華田汽修公司 4003 204 東勝有限公司 4003 301 科華貿(mào)易公司 4001 302 新安有限公司 4001 303 華田汽修公司 1001 12rows 四、連接的類(lèi)型 3、自連接 自連接用于單個(gè)表內(nèi)進(jìn)行比較。使用自連接時(shí)，需要注意兩點(diǎn)：一是由 于是將表與其自身相連接，因此必須對(duì)表設(shè)定別名；二是自連接時(shí)會(huì)將行與 其自身相連，因此必須設(shè)定條件來(lái)防止。 SELECT DISTINCT a1.accepter, a1.city, a2.accepter, a2.city FROM Paccepter a1, Paccepter a2 WHERE a1.city = a2.city AND a1.accepter a2.accepter AND a1.city IN(杭州,北京) Results accepter city accepter city 藍(lán)科通訊公司 杭州 漢升咨詢(xún)公司 杭州 藍(lán)科通訊公司 杭州 科華貿(mào)易公司 杭州 漢升咨詢(xún)公司 杭州 藍(lán)科通訊公司 杭州 漢升咨詢(xún)公司 杭州 科華貿(mào)易公司 杭州 科華貿(mào)易公司 杭州 藍(lán)科通訊公司 杭州 科華貿(mào)易公司 杭州 漢升咨詢(xún)公司 杭州 化天通訊公司 北京 華田汽修公司 北京 化天通訊公司 北京 東勝有限公司 北京 華田汽修公司 北京 東勝有限公司 北京 華田汽修公司 北京 化天通訊公司 北京 東勝有限公司 北京 化天通訊公司 北京 東勝有限公司 北京 華田汽修公司 北京 12rows 四、連接的類(lèi)型 4、外連接 有時(shí)候用戶(hù)可能希望顯示某個(gè)表中所有記錄，包括不符合連接條 件的記錄，這正是外連接的作用。使用外連接可以方便的在連接結(jié)果 中包含某個(gè)表中的其它記錄。SQL中提供了兩個(gè)關(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è)的表所 包含的所有記錄。 五、對(duì)集合的支持 1、UNON運(yùn)算 UNOIN運(yùn)算是集合運(yùn)算中應(yīng)用的最多的，得到了廣泛的支持。 UNION運(yùn)算可以從多個(gè)表中組合數(shù)據(jù)進(jìn)行顯示，但是與連接不同， UNION不是在FROM子句中添加多個(gè)表并指定連接條件實(shí)現(xiàn)，而是通 過(guò)將多個(gè)查詢(xún)的結(jié)果結(jié)合到一起實(shí)現(xiàn)的。 SELECT select_list FROM table_list/view_list WHERE conditions UNION ALL SELECT select_list FROM table_list/view_list WHERE conditions 五、對(duì)集合的支持 2、INTERSECT運(yùn)算 INTERSECT運(yùn)算與UNION運(yùn)算相似，UNION運(yùn)算中應(yīng)該注意的 問(wèn)題、提高結(jié)果可讀性的方法都可以在INTERSECT運(yùn)算中使用。 INTERSECT運(yùn)算將返回多個(gè)查詢(xún)結(jié)果中相同的行。 SELECT city FROM Employee WHERE dno = 2 INTERSECT SELECT DISTINCT city FROM Paccepter Results city 北京 上海 廈門(mén) 3rows 五、對(duì)集合