使用連接《SQLServer程序設(shè)計(jì)語(yǔ)言》

1、本部分主要所用的表結(jié)構(gòu)要所用的表結(jié)構(gòu)SQL Server 2005 數(shù)據(jù)庫(kù)程序設(shè)計(jì)語(yǔ)言數(shù)據(jù)庫(kù)程序設(shè)計(jì)語(yǔ)言第第8章章 數(shù)據(jù)操作數(shù)據(jù)操作8.2 使用連接使用連接1、使用表的別名、使用表的別名2、命名列、命名列3、組合多個(gè)表中的數(shù)據(jù)、組合多個(gè)表中的數(shù)據(jù)4、推薦操作、推薦操作 在查詢中，同時(shí)涉及兩個(gè)或兩在查詢中，同時(shí)涉及兩個(gè)或兩個(gè)以上的表，要根據(jù)表中數(shù)據(jù)的情個(gè)以上的表，要根據(jù)表中數(shù)據(jù)的情況作連接。況作連接。 SELECT buyer_name ,qty FROM sales cross join buyers一個(gè)示例：一個(gè)示例：例例1：本例列出：本例列出buyers. buyer_name和和sale

2、s. qty列中所有可能的值的組合列中所有可能的值的組合 SELECT buyer_name ,qty FROM sales,buyers查詢查詢結(jié)果結(jié)果buyers表表sales表表連接操作的執(zhí)行過(guò)程連接操作的執(zhí)行過(guò)程首先在表首先在表1中找到第一個(gè)元組，然后從頭開(kāi)始掃描中找到第一個(gè)元組，然后從頭開(kāi)始掃描表表2，逐一查找滿足條件的元組，找到后就將表，逐一查找滿足條件的元組，找到后就將表1中的第一個(gè)元組與該元組拼接起來(lái)，形成結(jié)果表中的第一個(gè)元組與該元組拼接起來(lái)，形成結(jié)果表中一個(gè)元組。中一個(gè)元組。表表2全部查找完后，再找表全部查找完后，再找表1中第二個(gè)元組，然后中第二個(gè)元組，然后再?gòu)念^開(kāi)始掃描表再

3、從頭開(kāi)始掃描表2，逐一查找滿足條件的元組，逐一查找滿足條件的元組，找到后就將表找到后就將表1中的第二個(gè)元組與該元組拼接起來(lái)，中的第二個(gè)元組與該元組拼接起來(lái)，形成結(jié)果表中一個(gè)元組。形成結(jié)果表中一個(gè)元組。重復(fù)上述操作，直到表重復(fù)上述操作，直到表1中的全部元組都處理完畢中的全部元組都處理完畢 8.2 使用連接使用連接1、使用表的別名、使用表的別名2、命名列、命名列3、組合多個(gè)表中的數(shù)據(jù)、組合多個(gè)表中的數(shù)據(jù)4、推薦操作、推薦操作使用表的別名使用表的別名指派表的別名時(shí)，可以使用也可以不使用指派表的別名時(shí)，可以使用也可以不使用 AS 關(guān)鍵字關(guān)鍵字： table_name AS table_aliastab

4、le_name table_alias 如果為表指派了別名，那么在該如果為表指派了別名，那么在該 Transact-SQL 語(yǔ)句中語(yǔ)句中對(duì)該表的所有顯式引用都必須使用別名，而不能使對(duì)該表的所有顯式引用都必須使用別名，而不能使用表名。用表名。有時(shí)復(fù)雜的有時(shí)復(fù)雜的 JOIN 語(yǔ)句和子查詢必須使用表的別名。語(yǔ)句和子查詢必須使用表的別名。 例如，聯(lián)接一個(gè)表和它自身時(shí)，必須使用別名例如，聯(lián)接一個(gè)表和它自身時(shí)，必須使用別名 使用表的別名使用表的別名在下例中，為在下例中，為 publishers 指派了別名指派了別名 p。USE pubsSELECT p.pub_id, p.pub_nameFROM pub

5、lishers AS p下列下列 SELECT 語(yǔ)句將產(chǎn)生語(yǔ)法錯(cuò)誤，請(qǐng)問(wèn)為什么？：語(yǔ)句將產(chǎn)生語(yǔ)法錯(cuò)誤，請(qǐng)問(wèn)為什么？： SELECT Employees.EmployeeID,emp.FirstName, emp.LastName FROM Northwind.dbo.Employees AS emp8.2 使用連接使用連接1、使用表的別名、使用表的別名2、命名列、命名列3、組合多個(gè)表中的數(shù)據(jù)、組合多個(gè)表中的數(shù)據(jù)4、推薦操作、推薦操作命名列命名列在查詢中使用多表聯(lián)接時(shí)，必須注意列的命在查詢中使用多表聯(lián)接時(shí)，必須注意列的命名名任何數(shù)據(jù)庫(kù)對(duì)象的全名包括四個(gè)標(biāo)識(shí)符：任何數(shù)據(jù)庫(kù)對(duì)象的全名包括四個(gè)標(biāo)識(shí)符：

6、服務(wù)器名服務(wù)器名.數(shù)據(jù)庫(kù)名數(shù)據(jù)庫(kù)名.架構(gòu)名稱架構(gòu)名稱.對(duì)象名對(duì)象名 在多表聯(lián)接中，如果表?yè)碛邢嗤淖侄蚊?，在多表?lián)接中，如果表?yè)碛邢嗤淖侄蚊?，則在指定字段時(shí)，必須包含表名則在指定字段時(shí)，必須包含表名例如：例如：school.dbo.student.sno8.2 使用連接使用連接1、使用表的別名、使用表的別名2、命名列、命名列3、組合多個(gè)表中的數(shù)據(jù)、組合多個(gè)表中的數(shù)據(jù)4、推薦操作、推薦操作3、組合多個(gè)表中的數(shù)據(jù)、組合多個(gè)表中的數(shù)據(jù)聯(lián)接概述聯(lián)接概述使用內(nèi)聯(lián)接使用內(nèi)聯(lián)接使用外聯(lián)接使用外聯(lián)接使用交叉聯(lián)接使用交叉聯(lián)接聯(lián)接兩個(gè)以上的表聯(lián)接兩個(gè)以上的表自聯(lián)接自聯(lián)接 （Self-Join）聯(lián)接概述聯(lián)接概述從

7、多個(gè)表中選擇指定的字段從多個(gè)表中選擇指定的字段關(guān)鍵字關(guān)鍵字 JOIN :指定要聯(lián)接的表，以及這些表聯(lián)指定要聯(lián)接的表，以及這些表聯(lián)接的方式接的方式關(guān)鍵字關(guān)鍵字 ON: 指定聯(lián)接條件指定聯(lián)接條件部分語(yǔ)法：部分語(yǔ)法： := on | CROSS JOIN 聯(lián)接概述（續(xù)）聯(lián)接概述（續(xù)）Join_type的格式：的格式： INNER | LEFT|RIGHT|FULL OUTER JOINANSI JOIN 操作符操作符 INNER JOIN LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN、 GROSS JOIN查詢兩個(gè)或多個(gè)表并生成單個(gè)結(jié)果集查詢兩個(gè)或

8、多個(gè)表并生成單個(gè)結(jié)果集所有聯(lián)接的表必須所有聯(lián)接的表必須共同擁有共同擁有某些字段，這些字某些字段，這些字段必須有段必須有相同的或兼容相同的或兼容的數(shù)據(jù)類型的數(shù)據(jù)類型如果聯(lián)接的表有相同字段，則引用這些字段時(shí)如果聯(lián)接的表有相同字段，則引用這些字段時(shí)必須指定表名必須指定表名盡量在聯(lián)接中盡量在聯(lián)接中限制限制表的表的個(gè)數(shù)個(gè)數(shù)聯(lián)接概述（續(xù)）聯(lián)接概述（續(xù)）使用內(nèi)聯(lián)接使用內(nèi)聯(lián)接l內(nèi)聯(lián)接通過(guò)比較被聯(lián)接的表所共同擁有的字段，內(nèi)聯(lián)接通過(guò)比較被聯(lián)接的表所共同擁有的字段，把多個(gè)表聯(lián)接起來(lái)。把多個(gè)表聯(lián)接起來(lái)。將只返回滿足聯(lián)接條件的行將只返回滿足聯(lián)接條件的行。l為什么要使用內(nèi)聯(lián)接為什么要使用內(nèi)聯(lián)接l內(nèi)聯(lián)接是內(nèi)聯(lián)接是 SQL

9、Server 缺省的聯(lián)接方式缺省的聯(lián)接方式l在選擇列表中指定結(jié)果集要顯示的字段名在選擇列表中指定結(jié)果集要顯示的字段名l使用使用 WHERE 子句來(lái)限制結(jié)果集要返回的記錄子句來(lái)限制結(jié)果集要返回的記錄l在聯(lián)接條件中不要使用空值，因?yàn)榭罩蹬c其他在聯(lián)接條件中不要使用空值，因?yàn)榭罩蹬c其他值不會(huì)相等值不會(huì)相等使用內(nèi)聯(lián)接（續(xù)）使用內(nèi)聯(lián)接（續(xù)）SELECT buyer_name, sales.buyer_id, qtyFROM buyers INNER JOIN salesON buyers.buyer_id = sales.buyer_idGOsales114323151553711421003buyers

10、Adam BarrSean ChaiEva CoretsErin OMelia1234結(jié)果結(jié)果Adam BarrAdam BarrErin OMeliaEva Corets11431553711Erin OMelia41003示例示例列出購(gòu)買產(chǎn)品的顧客信息列出購(gòu)買產(chǎn)品的顧客信息buyer_name, sales.buyer_id, qty使用外聯(lián)接使用外聯(lián)接l左（右）外聯(lián)接可以從兩個(gè)表中返回符合聯(lián)接條左（右）外聯(lián)接可以從兩個(gè)表中返回符合聯(lián)接條件的記錄，同時(shí)也將返回左（右）邊不符合聯(lián)接件的記錄，同時(shí)也將返回左（右）邊不符合聯(lián)接條件的記錄條件的記錄l使用左（右）外聯(lián)接時(shí)，請(qǐng)注意使用左（右）外聯(lián)接時(shí)

11、，請(qǐng)注意l不滿足聯(lián)接條件的記錄將顯示空值不滿足聯(lián)接條件的記錄將顯示空值l左聯(lián)接可以顯示第一個(gè)表中所有記錄左聯(lián)接可以顯示第一個(gè)表中所有記錄l右聯(lián)接可以顯示第二個(gè)表中所有記錄右聯(lián)接可以顯示第二個(gè)表中所有記錄使用外聯(lián)接（續(xù)）使用外聯(lián)接（續(xù)）SELECT buyer_name, sales.buyer_id, qty FROM buyers LEFT OUTER JOIN sales ON buyers.buyer_id = sales.buyer_idGO示例示例sales114323151553711421003buyersAdam BarrSean ChaiEva CoretsErin OMel

12、ia1234結(jié)果結(jié)果Adam BarrAdam BarrSean ChaiEva Corets11NULL3155NULL11Erin OMelia437Erin OMelia41003列出購(gòu)買產(chǎn)品的顧客信息列出購(gòu)買產(chǎn)品的顧客信息buyer_name, sales.buyer_id, qty。結(jié)果集也將返回沒(méi)有購(gòu)買任何產(chǎn)品的顧客結(jié)果集也將返回沒(méi)有購(gòu)買任何產(chǎn)品的顧客使用交叉聯(lián)接使用交叉聯(lián)接l交叉聯(lián)接將從被聯(lián)接的表中返回所有可能的記錄交叉聯(lián)接將從被聯(lián)接的表中返回所有可能的記錄組合組合l為什么使用交叉聯(lián)接為什么使用交叉聯(lián)接 l在規(guī)范化的數(shù)據(jù)庫(kù)中，很少使用交叉聯(lián)接在規(guī)范化的數(shù)據(jù)庫(kù)中，很少使用交叉聯(lián)接l

13、可以為數(shù)據(jù)庫(kù)生成測(cè)試數(shù)據(jù)可以為數(shù)據(jù)庫(kù)生成測(cè)試數(shù)據(jù)l為清單及企業(yè)模板生成所有可能的組合數(shù)據(jù)為清單及企業(yè)模板生成所有可能的組合數(shù)據(jù)使用交叉聯(lián)接（續(xù)）使用交叉聯(lián)接（續(xù)）SELECT buyer_name, qty FROM buyers CROSS JOIN salesGO結(jié)果結(jié)果Adam BarrAdam BarrAdam BarrAdam Barr1553711 Adam Barr1003 Sean Chai15 Sean Chai5 Sean Chai37 Sean Chai11 Sean Chai1003 Eva Corets15 .sales114323151553711421003buy

14、ers1234Adam BarrSean ChaiEva CoretsErin OMelia示例示例列出列出buyers.buyer_name, sales. Qty的所有可能的值的組合的所有可能的值的組合聯(lián)接兩個(gè)以上的表聯(lián)接兩個(gè)以上的表l聯(lián)接任意數(shù)目的表都有可能聯(lián)接任意數(shù)目的表都有可能l通過(guò)使用共同擁有的字段，任何通過(guò)使用共同擁有的字段，任何一個(gè)表都可以和其他表聯(lián)接一個(gè)表都可以和其他表聯(lián)接聯(lián)接兩個(gè)以上的表（續(xù)）聯(lián)接兩個(gè)以上的表（續(xù)）SELECT buyer_name, prod_name, qty FROM buyers INNER JOIN sales ON buyers.buyer_id

15、 = sales.buyer_id INNER JOIN produce ON d_id = d_idproduce1234ApplesPearsOrangesBananas5Peachesbuyers1234Adam BarrSean ChaiEva CoretsErin OMeliasales114323154215537111003結(jié)果結(jié)果Erin OMeliaAdam BarrErin OMeliaAdam BarrEva CoretsApplesPearsPearsOrangesPeaches37151003511示例示例自聯(lián)接自聯(lián)接 （Sel

16、f-Join）l使用自聯(lián)接，可以使用自聯(lián)接，可以查詢一個(gè)表中各查詢一個(gè)表中各記錄之間的關(guān)系記錄之間的關(guān)系l使用自聯(lián)接時(shí)，應(yīng)注意使用自聯(lián)接時(shí)，應(yīng)注意 l引用表的兩份副本時(shí)，必須使用表的別名引用表的兩份副本時(shí)，必須使用表的別名l生成自聯(lián)接時(shí)，表中每一行都和自身比較一生成自聯(lián)接時(shí)，表中每一行都和自身比較一下，并生成重復(fù)的記錄，使用下，并生成重復(fù)的記錄，使用 WHERE WHERE 子句來(lái)子句來(lái)消除這些重復(fù)記錄消除這些重復(fù)記錄Sales 檢索出購(gòu)買了相同產(chǎn)品的顧客檢索出購(gòu)買了相同產(chǎn)品的顧客ID號(hào)以及其購(gòu)買的號(hào)以及其購(gòu)買的產(chǎn)品產(chǎn)品ID?Sales bSales aSELECT a.buyer_id AS

17、 buyer1, d_id ,b.buyer_id AS buyer2 FROM sales AS a JOIN sales AS b ON d_id = d_idsales b114323151553711421003sales a114323151553711421003SELECT a.buyer_id AS buyer1, d_id ,b.buyer_id AS buyer2 FROM sales AS a JOIN sales AS b ON d_id = d_idWHERE a.buyer_id b.buyer_idsal

18、es b114323151553711421003sales a114323151553711421003SELECT a.buyer_id AS buyer1, d_id ,b.buyer_id AS buyer2 FROM sales AS a JOIN sales AS b ON d_id = d_idWHERE a.buyer_id b.buyer_idsales b114323151553711421003sales a114323151553711421003421UNION 和和 JOIN 的區(qū)別的區(qū)別lJOIN 是合并多個(gè)表并生存是合并多個(gè)表并生存一個(gè)單獨(dú)的結(jié)果集一個(gè)單獨(dú)的結(jié)果集，該結(jié)果集將包含多個(gè)表中的字段該結(jié)果集將包含多