實(shí)驗(yàn)六.-多表查詢doc

1、精品實(shí)驗(yàn)六多表查詢實(shí)驗(yàn)?zāi)康?、掌握嵌套查詢的概念2、掌握連接查詢3、進(jìn)一步掌握分組操作4、掌握 Union的使用實(shí)驗(yàn)環(huán)境硬件環(huán)境： PC 機(jī)一臺(tái)軟件環(huán)境： SQL Server 2008/2005實(shí)驗(yàn)內(nèi)容在供應(yīng)商數(shù)據(jù)庫中供應(yīng)商關(guān)系： S(SNO， SNAME，ADDR)零件關(guān)系： P(PNO，PNAME， COLOR，WEIGHT)工程項(xiàng)目關(guān)系：J(JNO,JNAME，CITY， BALANCE)供應(yīng)情況關(guān)系：SPJ(SNO,PNO,JNO，PRICE， QTY)上述各屬性的含義是：供應(yīng)商號(hào) (SNO) 、供應(yīng)商名 (SNAME) 和地址(ADDR) ，零件號(hào) (PNO) 、零件名（ PNAM

2、E ）、顏色（ COLOR）、重量（ WEIGHT ）、單價(jià) (PRICE)、工程項(xiàng)目號(hào) (JNO) 、工程項(xiàng)目名稱 (JNAME) 、城市 (CITY) 、余額（ BALANCE ）、供應(yīng)數(shù)量 (QTY)。welcome精品供應(yīng)商m供應(yīng)np項(xiàng)目?jī)r(jià)格供應(yīng)量零件1、查詢供應(yīng)商的名字， 以及各自負(fù)責(zé)的項(xiàng)目數(shù)， 結(jié)果要求按照項(xiàng)目數(shù)的降序排列（連接、分組、排序）Select sname, count（distinct JNO）From S, SPjWhere S.sno = SPJ .snoGroup by snameSelectsname,count（ distinctJNO ）From s, sp

3、jWhere S.sno = spj.snoGroup by snamewelcome精品Order by count（distinct JNO）2、查詢重量比螺栓重的零件名稱，零件重量（子查詢或自身連接）Select pname,weightFrom PWhere weight >（ Select weight From PWhere pname =螺栓 ）Select ,a.weightfrom P a, P bwhere a.weight > b.weight and b.pname= 螺栓 3、 查詢 P（零件）表中各種顏色及其對(duì)應(yīng)的零件種類的數(shù)目（分組和聚集查

4、詢）Select color, count（PNO ）welcome精品From PGroup by color4、統(tǒng)計(jì)天津地區(qū)的項(xiàng)目使用零件的種數(shù)(超過 3 種)和零件總數(shù)量。要求查詢結(jié)果按零件的種數(shù)升序排列，種數(shù)相同時(shí)按總數(shù)量降序排列。（連接、分組、聚集函數(shù)、排序）Select JNO, count（PNO ），sum(qty)From J,SPJWhere J.jno = spj.jno and city=天津Group by JNOHaving count（PNO ）>35、查詢既供應(yīng) P1 零件又供應(yīng) P2 零件的供應(yīng)商編號(hào)， 供應(yīng)商名稱（多重條件查詢）6、查詢每個(gè)供應(yīng)商供應(yīng)零

5、件的情況， 要求列出供應(yīng)商名稱， 零件名稱，供應(yīng)數(shù)量，并按供應(yīng)數(shù)量的升序排序（連接、分組、聚集函數(shù)、排序）7、查詢與“萬勝”供應(yīng)商在同一城市的供應(yīng)商的詳細(xì)資料（子查詢）welcome精品8、查詢供應(yīng)商品種類最多的供應(yīng)商編號(hào)、供應(yīng)商名。9、查詢既生產(chǎn)螺母，也生產(chǎn)螺栓的供應(yīng)商編號(hào)和供應(yīng)商名。10 、查詢生產(chǎn)螺絲刀但不生產(chǎn)螺母的供應(yīng)商名11 、查詢“萬勝”和“精益”兩個(gè)供應(yīng)商的供貨情況，查詢結(jié)果中包括供應(yīng)商名、項(xiàng)目名、零件名和供應(yīng)數(shù)量。用兩個(gè) select 語句實(shí)現(xiàn)查詢，并用 union 將兩個(gè)語句的查詢結(jié)果合并在一起。三、實(shí)驗(yàn)步驟1、對(duì)文件夾中的 SPJ數(shù)據(jù)庫進(jìn)行附加還原。2、按照以上要求完成題目

6、。3、要求寫出查詢代碼并將查詢結(jié)果截圖附在代碼后。參考解答1、查詢供應(yīng)商的名字， 以及各自負(fù)責(zé)的項(xiàng)目數(shù)， 結(jié)果要求按照項(xiàng)目數(shù)的降序排列（連接、分組、排序）解題思路：1)本題要查詢供應(yīng)商名，這項(xiàng)數(shù)據(jù)僅在供應(yīng)商表（S）中才有，而要統(tǒng)計(jì)每個(gè)供應(yīng)商向幾個(gè)項(xiàng)目供貨，則需要對(duì)SPJ表中的數(shù)據(jù)進(jìn)行統(tǒng)計(jì)。這里需要對(duì) S和SPJ進(jìn)行連接，兩表的共同字段是SNO ，用該字段進(jìn)行連接。SELECT SNAME ,JNO ,PNOFROM S,SPJwelcome精品WHERE S.SNO = SPJ.SNO代碼執(zhí)行結(jié)果：圖 1-1說明：該結(jié)果表明，這些供應(yīng)商向項(xiàng)目提供零件的信息，這里項(xiàng)目和零件都用編號(hào)表示。2）這還

7、沒有達(dá)到題目要求的效果。題目要求統(tǒng)計(jì)每個(gè)供應(yīng)商負(fù)責(zé)的項(xiàng)目數(shù)。這里需要分組。用供應(yīng)商名進(jìn)行分組，計(jì)算每個(gè)組中項(xiàng)目號(hào)的數(shù)目。這里請(qǐng)注意，“萬勝”這個(gè)供應(yīng)商的供應(yīng)記錄有6 條，但是仔細(xì)看，不難發(fā)現(xiàn)，他供應(yīng)的項(xiàng)目其實(shí)是4 個(gè)，這里需要考慮到項(xiàng)目編號(hào)重復(fù)的情況（由于供應(yīng)商每向一個(gè)項(xiàng)目供一種零件，數(shù)據(jù)庫中都會(huì)產(chǎn)生相應(yīng)的記錄。）因此，對(duì)代碼再進(jìn)行修改。SELECT SNAME ,COUNT (JNO )FROM S,SPJWHERE S.SNO = SPJ.SNOwelcome精品GROUP BY SNAME執(zhí)行結(jié)果：圖 1-2運(yùn)行結(jié)果說明：這里的執(zhí)行結(jié)果已經(jīng)可以統(tǒng)計(jì)出各供應(yīng)商負(fù)責(zé)的項(xiàng)目數(shù)，但是由于沒有考慮

8、到一個(gè)供應(yīng)商有可能存在多條供貨記錄，因此，這個(gè)結(jié)果依然不正確。3）對(duì)代碼添加關(guān)鍵字“ DISTINCT ”可不統(tǒng)計(jì)重復(fù)的字段。SELECT SNAME ,COUNT (DISTINCT JNO )FROM S,SPJWHERE S.SNO = SPJ.SNOGROUP BY SNAMEORDER BY COUNT (DISTINCT JNO ) DESC 執(zhí)行結(jié)果：圖 1-3這才是最終的正確結(jié)果。welcome精品2、查詢重量比螺栓重的零件名稱，零件重量（子查詢或自身連接）解法：子查詢解題思路：先查詢螺栓的重量，再用這重量與零件表中的其他零件的重量進(jìn)行比較。1)SELECT WEIGHTFRO

9、M PWHERE PNAME = '螺栓 '圖 2-1 螺栓的重量2）將第一步查詢中的重量作為外層查詢的條件。SELECT PNAME ,WEIGHTFROM PWHERE WEIGHT >(SELECT WEIGHTFROM PWHERE PNAME = '螺栓 ')圖 2-2執(zhí)行結(jié)果說明：先執(zhí)行內(nèi)層查詢，其返回結(jié)果就是螺栓的重量，用這welcome精品個(gè)重量與外層查詢中的零件重量進(jìn)行比較，查詢“ WEIGHT ”字段的值大于 17 的記錄。得到如上結(jié)果。這里注意，在外層查詢的 WHERE 字句中的比較字段與子查詢的返回結(jié)果應(yīng)該相同，此外，為了讓查詢層次

10、明確，子查詢部分最好縮進(jìn)。解法 2：自身連接SELECT A .PNAME ,A.WEIGHTFROM PA,PBWHERE A .WEIGHT > B.WEIGHT AND B .PNAME = '螺栓 '圖 2-2說明：在自身連接時(shí)，需將一個(gè)表理解成兩個(gè)表。在本題中，將P 理解成 A,B 兩表，用 B 表查詢螺栓的重量， 再用 A 表找出重量比它重的零件信息。3、查詢 P（零件）表中各種顏色及其對(duì)應(yīng)的零件種類的數(shù)目（分組和聚集查詢）SELECT COLOR ,COUNT (PNO )FROM PGROUP BY COLORwelcome精品圖 3-1說明：本題僅對(duì)零件

11、信息進(jìn)行查詢，不涉及到項(xiàng)目和供應(yīng)商，因此無需多表連接。但是請(qǐng)大家注意分析題目的要求：求各種顏色的零件各有幾類。即，題目要查詢的是“紅色的零件有幾類，綠色的零件有幾類”這樣的信息，思考題目要求，不難發(fā)現(xiàn)這時(shí)需要用到分組，分組的依據(jù)就是顏色。因此在 GROUP BY 子句中用的是 COLOR 字段。4、統(tǒng)計(jì)天津地區(qū)的項(xiàng)目使用零件的種數(shù)(超過 3 種)和零件總數(shù)量。要求查詢結(jié)果按零件的種數(shù)升序排列，種數(shù)相同時(shí)按總數(shù)量降序排列。（連接、分組、聚集函數(shù)、排序）1)本題需要分成兩個(gè)步驟考慮。其一，考慮項(xiàng)目的編號(hào)，項(xiàng)目的名稱，項(xiàng)目使用的零件（這里只要統(tǒng)計(jì)每個(gè)項(xiàng)目用的零件類別，因此只要零件號(hào)），每次使用的數(shù)量

12、。因此，需要連接項(xiàng)目表（J）和供應(yīng)關(guān)系表（SPJ）。并且需要指明項(xiàng)目所在的城市是天津。SELECT J.JNO ,JNAME ,PNO ,QTYFROM J,SPJWHERE J.JNO = SPJ.JNO AND CITY= '天津 '圖 4-1由查詢結(jié)果可知，天津地區(qū)只有彈簧廠和造船廠兩個(gè)項(xiàng)目，welcome精品用目測(cè)就知道前者使用了一種零件，后者有3 種。按照題目要求，還需要進(jìn)行分組。2）統(tǒng)計(jì)零件類別數(shù)，用 COUNT 函數(shù)，統(tǒng)計(jì)總數(shù)量，則要用SUMSELECT J.JNO ,JNAME ,COUNT (PNO ),SUM (QTY)FROM J,SPJWHERE J.J

13、NO = SPJ.JNO AND CITY= '天津 'GROUP BY J.JNO ,JNAME圖 4-25、查詢既供應(yīng) P1 零件又供應(yīng) P2 零件的供應(yīng)商編號(hào)， 供應(yīng)商名稱（多重條件查詢）解題思路：題目要求查詢供應(yīng)商號(hào)和供應(yīng)商名，這兩項(xiàng)信息要通過供應(yīng)商表（ S）獲得，同時(shí)，還需要供應(yīng)的一些具體情況，這時(shí)就要用到SPJ。這題可以用子查詢或連接查詢實(shí)現(xiàn)。本例用子查詢。1）先到 SPJ表中查詢供應(yīng) P1 零件的供應(yīng)商號(hào)：SELECT SNO FROM SPJ WHERE PNO = 'P1'圖 5-12）在再到 SPJ表中查詢供應(yīng) P2 零件的供應(yīng)商號(hào)：welc

14、ome精品SELECT SNO FROM SPJ WHERE PNO = 'P2'圖 5-23）由以上兩個(gè)步驟的查詢結(jié)果可知，兩種零件都有提供的供應(yīng)商只有 S1，本題求的是兩個(gè)集合的交集。最后的查詢是，用兩個(gè)子查詢獲得同時(shí)提供兩種零件的供應(yīng)商號(hào)，再用此結(jié)果到供應(yīng)商表（S）中獲取相應(yīng)供應(yīng)商信息。SELECT SNO ,SNAMEFROM SWHERE SNO IN( SELECT SNO FROM SPJ WHERE PNO = 'P1' )AND SNO IN( SELECT SNO FROM SPJ WHERE PNO = 'P2' )圖 5-

15、3思考：本題如果用以下語句是否可以實(shí)現(xiàn)SELECT SNO ,SNAMEFROM SWHERE SNO IN( SELECT SNO FROM SPJ WHERE PNO = 'P1' AND PNO = 'P2' )6、查詢每個(gè)供應(yīng)商供應(yīng)零件的情況， 要求列出供應(yīng)商名稱， 零件名稱，供應(yīng)數(shù)量，并按供應(yīng)數(shù)量的升序排序（連接、分組、聚集函數(shù)、排序）解題思路：1）本題要求查詢供應(yīng)商名字、零件名稱、數(shù)量，從題目要求的字段可welcome精品以看出，本題需要連接供應(yīng)商表 （S），零件表（P）和供應(yīng)情況表（SPJ）SELECT SNAME ,PNAME ,JNO ,QTY

16、FROM S,P,SPJWHERE S.SNO = SPJ.SNO AND P.PNO = SPJ.PNO圖 6-1這個(gè)查詢步驟顯示供應(yīng)商名，零件名，項(xiàng)目號(hào)，以及這次供應(yīng)零件的數(shù)量。按照題目的要求，要統(tǒng)計(jì)每個(gè)供應(yīng)商提供的某一類零件的總數(shù)量，比如，“精益”供應(yīng)的螺母總量是200+100+700+100.2）還需要用供應(yīng)商名和零件名共同分組,并排序。SELECT SNAME ,PNAME ,SUM (QTY) '供應(yīng)數(shù)量 'FROM S,P,SPJWHERE S.SNO = SPJ.SNO AND P.PNO = SPJ.PNOGROUP BY SNAME ,PNAMEORDER

17、BY SUM (QTY)welcome精品圖 6-27、查詢與“萬勝”供應(yīng)商在同一城市的供應(yīng)商的詳細(xì)資料（子查詢）解題思路：本題用子查詢，先查詢“萬勝”所在的城市：然后用子查詢（內(nèi)層查詢）的返回結(jié)果作為外層查詢的條件。1）查詢“萬勝”的城市SELECT CITY FROM S WHERE SNAME = '萬勝 '圖 7-12)用第一步中的結(jié)果作為外層查詢的條件。SELECT* FROM SWHERE CITY=( SELECT CITY FROM S WHERE SNAME = '萬勝 ' )AND SNAME != '萬勝 '圖 7-2題目要

18、求查詢供應(yīng)商的信息，這就包括了供應(yīng)商號(hào)、供應(yīng)商名、供應(yīng)商所在城市這些信息。 所以在 SELECT子句中用 *替代所有的字段。另外，題目要插敘你的目標(biāo)應(yīng)該是萬勝之外的其他供應(yīng)商，因此應(yīng)該welcome精品用“ SNAME != '萬勝 ' ”這個(gè)條件排除萬勝這個(gè)供應(yīng)商自身的信息。代碼的執(zhí)行結(jié)果如上圖所示。8、查詢供應(yīng)商品種類最多的供應(yīng)商編號(hào)、供應(yīng)商名。解題思路：本題即要查詢供應(yīng)商編號(hào)，又要供應(yīng)商的名字和供應(yīng)的零件類別數(shù)，因此，本題需要連接供應(yīng)商表(S)和供應(yīng)關(guān)系表 (SPJ)。1) 先進(jìn)行連接查詢，獲取每個(gè)供應(yīng)商名及其供應(yīng)的零件編號(hào), 并用DISTINCT 關(guān)鍵字消除重復(fù)的記錄。

19、SELECTDISTINCT S.SNO ,SNAME ,PNOFROM S,SPJWHERE S.SNO = SPJ.SNO圖 8-12）對(duì)第一步中的查詢結(jié)果進(jìn)行目測(cè)，可以得知，S1 供應(yīng) 2 種零件，S2 供應(yīng) 2 種，所以，這里要用供應(yīng)商進(jìn)行分組，統(tǒng)計(jì)每組的零件類別數(shù)。SELECTDISTINCT S.SNO ,SNAME ,COUNT (DISTINCT PNO )FROM S,SPJWHERE S.SNO = SPJ.SNOGROUP BY S.SNO ,SNAMEwelcome精品圖 8-23）由第 2 步的執(zhí)行結(jié)果可知， S1，S2，S3 是供應(yīng)零件種類最多的供應(yīng)商，現(xiàn)在需要再對(duì)

20、代碼進(jìn)行改進(jìn)，導(dǎo)出數(shù)量最多的三個(gè)供應(yīng)商。SELECTDISTINCT S.SNO ,SNAMEFROM S,SPJWHERE S.SNO = SPJ.SNOGROUP BY S.SNO ,SNAMEHAVING COUNT (DISTINCT PNO )>=ALL( SELECT COUNT (DISTINCT PNO )FROM S,SPJWHERE S.SNO = SPJ.SNOGROUP BY S.SNO ,SNAME )圖 8-3在第三步中，在HAVING子句中使用子查詢，獲取每個(gè)供應(yīng)商提供的零件類別數(shù)，外層查詢的邏輯與內(nèi)層查詢相同，如果滿足 >=ALL 這一條件，則是供應(yīng)

21、種類數(shù)目最多的供應(yīng)商。這個(gè)部分需要各位同學(xué)多花時(shí)間好好理解。welcome精品9、查詢既生產(chǎn)螺母，也生產(chǎn)螺栓的供應(yīng)商編號(hào)和供應(yīng)商名。解題思路：本題求的是兩個(gè)集合的交集。一個(gè)集合是上次螺母的集合，另一個(gè)集合是生產(chǎn)螺栓的集合。先編寫語句，分別求生產(chǎn)螺母的供應(yīng)商號(hào)和生產(chǎn)螺栓的供應(yīng)商號(hào)。1）先分別從零件表（ P）中查詢螺母、螺栓的編號(hào)，然后用此編號(hào)到供應(yīng)關(guān)系（ SPJ）中查詢有提供此商品的供應(yīng)商號(hào)（SNO ）SELECTDISTINCT SNO FROM SPJWHERE PNO IN (SELECT PNO FROM P WHERE PNAME = '螺母 ' )圖 9-1SELECTDISTINCT