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

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