試驗六-多表查詢

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

2、) 單價(PRICE)、工程項目號(JNO)、工程項目名稱(JNAME城市(CITY)、 余額(BALANCE供應(yīng)數(shù)量(QTY)。供應(yīng)商項目價格|供應(yīng)量i 零件I,|'1、查詢供應(yīng)商的名字，以及各自負(fù)責(zé)的項目數(shù)，結(jié)果要求按照項目 數(shù)的降序排列(連接、分組、排序)Select sname, count (distinct JNO)From S, SPjWhere S sno = SPJ .snoGroup by snameSelect sn ame, coun t(disti net JNO)From s, spjWhere S.sno = spj .snoGroup by sn ame

3、Order by count( distinct JNO)2、查詢重量比螺栓重的零件名稱，零件重量(子查詢或自身連接)Select pn ame,weightFrom PWhere weight >(Select weightFrom PWhere pname =螺栓 ')Select ,a.weight from P a, P bwhere a.weight > b.weight and b.pn ame=螺栓'3、查詢P （零件）表中各種顏色及其對應(yīng)的零件種類 的數(shù)目（分組和聚集查詢）Select color, coun（t PNO）From PGr

4、oup by color4、統(tǒng)計天津地區(qū)的項目使用零件的種數(shù) （超過 3 種）和零件總數(shù)量。 要求查詢結(jié)果按零件的種數(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)商編號，供應(yīng)商名稱（多 重條件查詢）6、查詢每個供應(yīng)商供應(yīng)零件的情況， 要求列出供應(yīng)商名稱， 零件名稱，供應(yīng)數(shù)量，并按供應(yīng)數(shù)量的

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

6、各自負(fù)責(zé)的項目數(shù)，結(jié)果要求按照項目數(shù) 的降序排列（連接、分組、排序）解題思路: 1）本題要查詢供應(yīng)商名，這項數(shù)據(jù)僅在供應(yīng)商表（S）中才有，而要 統(tǒng)計每個供應(yīng)商向幾個項目供貨，則需要對SPJ表中的數(shù)據(jù)進(jìn)行統(tǒng)計。 這里需要對S和SPJ進(jìn)行連接，兩表的共同字段是SNO,用該字段進(jìn)行 連接。SELECT SNAME, JNO, PNOFROMS , SPJWHERES . SNO=SPJ. SNO 代碼執(zhí)行結(jié)果:由結(jié)果匕消息1SNAMEJNO PNO:精益| JIPI精益J3 PI精益J4PI4一精益J2 P25萬勝JI P3萬勝J2 P37一萬勝J4 P3萬勝J5 P3b萬勝j1 p510萬勝J2

7、P5口New EastJ1 P112NewEaslJ1 P3Co_BandJ1 P5CowayJ2 P615CowayJ4 P6圖i-i說明：該結(jié)果表明，這些供應(yīng)商向項目提供零件的信息，這里項目和 零件都用編號表示。2）這還沒有達(dá)到題目要求的效果。 題目要求統(tǒng)計每個供應(yīng)商負(fù)責(zé)的項目數(shù)。這里需要分組。用供應(yīng)商名進(jìn)行分組，計算每個組中項目號的數(shù)目。這里請注意，“萬勝”這個供應(yīng)商的供應(yīng)記錄有 6條，但是仔細(xì) 看，不難發(fā)現(xiàn)，他供應(yīng)的項目其實是 4個，這里需要考慮到項目編號 重復(fù)的情況（由于供應(yīng)商每向一個項目供一種零件，數(shù)據(jù)庫中都會產(chǎn) 生相應(yīng)的記錄。）因此，對代碼再進(jìn)行修改。SELECT SNAME,

8、COUNT JNO）FROMS , SPJWHERES . SNO=SPJ. SNOGROUPBYSNAME執(zhí)行結(jié)果：圖1-2運行結(jié)果說明：這里的執(zhí)行結(jié)果已經(jīng)可以統(tǒng)計出各供應(yīng)商負(fù)責(zé)的項目 數(shù)，但是由于沒有考慮到一個供應(yīng)商有可能存在多條供貨記錄，因此,這個結(jié)果依然不正確。3）對代碼添加關(guān)鍵字“ DISTINCT ”可不統(tǒng)計重復(fù)的字段。SELECT SNAME, COUNT DISTINCT JNO ）FROMS , SPJWHERES . SNO=SPJ. SNOGROUPBYSNAMEORDER BY COUNT DISTINCT JNO) DESC執(zhí)行結(jié)果：這才是最終的正確結(jié)果。2、查詢重量

9、比螺栓重的零件名稱，零件重量（子查詢或自身連接） 解法：子查詢解題思路：先查詢螺栓的重量，再用這重量與零件表中的其他零件的 重量進(jìn)行比較。1）SELECT WEIGHTFROMPWHER田NAME二螺栓'i|第木淚.啟WEIGHT1I?|圖2-1螺栓的重量2）將第一步查詢中的重量作為外層查詢的條件。SELECT PNAME, WEIGHTFROMPWHEREWEIGHT >(SELECT WEIGHTFROMPWHER田NAME二'螺栓')結(jié)果|肉屈PNAMEWEIGHT12鈾子28圖2-2執(zhí)行結(jié)果說明：先執(zhí)行內(nèi)層查詢，其返回結(jié)果就是螺栓的重量，用這 個重量與外層

10、查詢中的零件重量進(jìn)行比較，查詢“ WEIGHT ”字段的 值大于17的記錄。得到如上結(jié)果。這里注意，在外層查詢的 WHERE 字句中的比較字段與子查詢的返回結(jié)果應(yīng)該相同，此外，為了讓查詢 層次明確，子查詢部分最好縮進(jìn)。解法2 :自身連接SELECT A . PNAME A. WEIGHTFROMPA , P BWHEREA . WEIGHT>B. WEIGHT ANDB . PNAME='螺栓'結(jié)果丨創(chuàng)消劇PNAKIEWEIGHT1402補(bǔ)子2B圖2-2說明：在自身連接時，需將一個表理解成兩個表。在本題中，將P理解成A,B兩表，用B表查詢螺栓的重量，再用A表找出重量比它重

11、的 零件信息。3、查詢P （零件）表中各種顏色及其對應(yīng)的零件種類的數(shù)目（分組和聚集查詢)SELECT COLOR, COUNT PNC)FROMPGROUP BY COLOR結(jié)果|內(nèi)消息COLOR阮列名）1紅I 22藍(lán)33綠1圖3-1說明：本題僅對零件信息進(jìn)行查詢，不涉及到項目和供應(yīng)商，因此無 需多表連接。但是請大家注意分析題目的要求：求各種顏色的零件各有幾類。即， 題目要查詢的是“紅色的零件有幾類，綠色的零件有幾類”這樣的信 息，思考題目要求，不難發(fā)現(xiàn)這時需要用到分組，分組的依據(jù)就是顏 色。因此在GROUP BY子句中用的是COLOR字段。4、統(tǒng)計天津地區(qū)的項目使用零件的種數(shù)（超過3種）和零

12、件總數(shù)量。要 求查詢結(jié)果按零件的種數(shù)升序排列，種數(shù)相同時按總數(shù)量降序排列。（連接、分組、聚集函數(shù)、排序）1）本題需要分成兩個步驟考慮。其一，考慮項目的編號，項目的名稱, 項目使用的零件（這里只要統(tǒng)計每個項目用的零件類別，因此只要零 件號），每次使用的數(shù)量。因此，需要連接項目表（J ）和供應(yīng)關(guān)系表（SPJ）。并且需要指明項目所在的城市是天津。SELECT J . JNO, JNAME PNQ QTYFROMJ , SPJWHEREJ . JNO二SPJ. JNO AND CITY ='天津'JNO JNAME PNO QTY -1 pF禪簧廠 P1 1002 JJ誥船廠卩17D0

13、3 J4造船廠門5004 J4造船廠P6500圖4-1由查詢結(jié)果可知，天津地區(qū)只有彈簧廠和造船廠兩個項目， 用目測就知道前者使用了一種零件，后者有3種。按照題目要求，還需要進(jìn)行分組。2）統(tǒng)計零件類別數(shù)，用COUNT函數(shù)，統(tǒng)計總數(shù)量，則要用SUMSELECT J . JNO, JNAME COUNT PNC）, SUM QTY）FROMJ , SPJWHEREJ . JNO=SPJ. JNO AND CITY ='天津'GROUP BY J . JNO, JNAMEJNOJhlAME 無列名）兀列名1i"j3=諌簧廠11002J4造船廠31700圖4-25、查詢既供應(yīng)P

14、1零件又供應(yīng)P2零件的供應(yīng)商編號，供應(yīng)商名稱（多 重條件查詢）解題思路：題目要求查詢供應(yīng)商號和供應(yīng)商名，這兩項信息要通過供應(yīng)商表（S）獲得，同時，還需要供應(yīng)的一些具體情況，這時就要用到 SPJo這題可以用子查詢或連接查詢實現(xiàn)。本例用子查詢。SELECT SNO, SNAME1) 先到SPJ表中查詢供應(yīng)'1零件的供應(yīng)商號:SELECT SNO FROMSPJ WHERBPNO二'P1'SNO1i SI !2SI3SI4S3圖5-12) 在再到SPJ表中查詢供應(yīng)'2零件的供應(yīng)商號:SELECT SNO FROMSPJ WHEREPNO = 'P2'S

15、NO1圖5-23)由以上兩個步驟的查詢結(jié)果可知，兩種零件都有提供的供應(yīng)商只有S1,本題求的是兩個集合的交集。 最后的查詢是，用兩個子查 詢獲得同時提供兩種零件的供應(yīng)商號，再用此結(jié)果到供應(yīng)商表( S) 中獲取相應(yīng)供應(yīng)商信息。SELECT SNO, SNAMEFROMSWHERESNO IN( SELECT SNO FROMSPJ WHEREPNO = 'P1') ANDSNO IN( SELECT SNO FROMSPJ WHEREPNO = 'P2')SNO SNAME1 sFj蓿益圖5-3思考：本題如果用以下語句是否可以實現(xiàn)FROMSWHERESNO IN(

16、SELECT SNO FROMSPJ WHERBPNO二'P1' ANDPNO='P2'）6、查詢每個供應(yīng)商供應(yīng)零件的情況，要求列出供應(yīng)商名稱，零件名稱， 供應(yīng)數(shù)量，并按供應(yīng)數(shù)量的升序排序（連接、分組、聚集函數(shù)、排序） 解題思路：1）本題要求查詢供應(yīng)商名字、零件名稱、數(shù)量，從題目要求的字段可 以看出，本題需要連接供應(yīng)商表（S）,零件表（P）和供應(yīng)情況表（SPJ）SELECT SNAME, PNAME JNO, QTYFROMS , P, SPJWHERES . SNO=SPJ. SNO AND P . PNC=SPJ. PNO二錯果山消息SNAMEPNAMEJN

17、QQTY1灑益j螺母J12002精益J31003精益蛭母J47004據(jù)程J21005萬勝J14006萬勝J22007萬勝J45008萬勝螺絲刀J54009萬勝凸輪|140010萬勝凸輪J210011NewEaat螺母J120012NewEastJ120013Coway鉗子J240014Coway鉗子J4500圖6-1這個查詢步驟顯示供應(yīng)商名，零件名，項目號，以及這次供應(yīng)零件的 數(shù)量。按照題目的要求，要統(tǒng)計每個供應(yīng)商提供的某一類零件的總數(shù)量，比如，“精益”供應(yīng)的螺母總量是 200+100+700+100.2）還需要用供應(yīng)商名和零件名共同分組，并排序。SELECT SNAME, PNAME SUM

18、（ QTY）'供應(yīng)數(shù)量'FROMS , P, SPJWHERES . SNO=SPJ. SNO AND P . PNOSPJ. PNOGROUP BY SNAME, PNAMEORDER BY SUM QTY)結(jié)果J消息SNAMEPNAME供應(yīng)數(shù)星1I精益寒栓1002New«t螺絲刀2003NeYvEast螺母2004萬勝凸輪5005鈾子9006藉益10007萬勝摞絲刀1500圖6-27、查詢與“萬勝”供應(yīng)商在同一城市的供應(yīng)商的詳細(xì)資料（子查詢） 解題思路：本題用子查詢，先查詢“萬勝”所在的城市：然后用子查 詢（內(nèi)層查詢）的返回結(jié)果作為外層查詢的條件。1）查詢“萬勝”

19、的城市SELECT CITY FROMS WHERESNAME二'萬勝'DTY »:ui Ji-lha1丨北京i圖7-12）用第一步中的結(jié)果作為外層查詢的條件SELECT * FROMSWHERBDITY =( SELECT CITY FROMS WHERESNAME二萬勝')ANDSNAME!二'萬勝'SNOSNAMECITY1Nevfa&t北京圖7-2題目要求查詢供應(yīng)商的信息，這就包括了供應(yīng)商號、供應(yīng)商名、供應(yīng) 商所在城市這些信息。所以在 SELECT子句中用*'替代所有的字 段。另外，題目要插敘你的目標(biāo)應(yīng)該是萬勝之外的其他

20、供應(yīng)商，因此 應(yīng)該用“SNAME二'萬勝”這個條件排除萬勝這個供應(yīng)商自身的信息。 代碼的執(zhí)行結(jié)果如上圖所示。8查詢供應(yīng)商品種類最多的供應(yīng)商編號、供應(yīng)商名。解題思路：本題即要查詢供應(yīng)商編號，又要供應(yīng)商的名字和供應(yīng)的零 件類別數(shù)，因此，本題需要連接供應(yīng)商表（S）和供應(yīng)關(guān)系表（SPJ）。1）先進(jìn)行連接查詢，獲取每個供應(yīng)商名及其供應(yīng)的零件編號，并用DISTINCT關(guān)鍵字消除重復(fù)的記錄。SELECT DISTINCT S . SNQ SNAME PNOFROMS , SPJWHERES . SNO=SPJ. SNQ圖#-12)對第一步中的查詢結(jié)果進(jìn)行目測，可以得知，S1供應(yīng)2種零件,S2供應(yīng)2種

21、，所以，這里要用供應(yīng)商進(jìn)行分組，統(tǒng)計每組的零件類別 數(shù)。SELECT DISTINCT S . SNQ SNAME COUNT DISTINCT PNO) FROMS , SPJWHERES . SNO=SPJ. SNOGROUP BY S . SNQ SNAMESNOSNAME無列名1精益22s2萬勝23NewEasl24$5Coway1圖8-23) 由第2步的執(zhí)行結(jié)果可知，S1，S2，S3是供應(yīng)零件種類最多的供 應(yīng)商，現(xiàn)在需要再對代碼進(jìn)行改進(jìn)，導(dǎo)出數(shù)量最多的三個供應(yīng)商。SELECT DISTINCT S . SNQ SNAMEFROMS , SPJWHERES . SNO=SPJ. SNO

22、GROUP BY S . SNQ SNAMEHAVING COUNT DISTINCT PNO)>=ALL( SELECTCOUNT DISTINCT PNO)FROMS , SPJWHERES . SNO=SPJ. SNOGROUP BY S . SNQ SNAME)SNOSNAME1二精益2萬勝3s3NewEast圖8-3在第三步中，在 HAVING子句中使用子查詢，獲取每個供應(yīng)商提供的零件類別數(shù)，外層查詢的邏輯與內(nèi)層查詢相同，如果滿足>=ALL '這一條件，則是供應(yīng)種類數(shù)目最多的供應(yīng)商。 這個部分 需要各位同學(xué)多花時間好好理解。9、查詢既生產(chǎn)螺母，也生產(chǎn)螺栓的供應(yīng)商編號和供應(yīng)商名。解題思路：本題求的是兩個集合的交集。一個集合是上次螺母的集合， 另一個集合是生產(chǎn)螺栓的集合。先編寫語句，分別求生產(chǎn)螺母的供應(yīng) 商號和生產(chǎn)螺栓的供應(yīng)商號。1）先分別從零件表（P）中查詢螺母、螺栓的編號，然后用此編號到 供應(yīng)關(guān)系（SPJ）中查詢有提供此商品的供應(yīng)商號（SNO）SELECT DISTINCT SNO FROMSPJWHER田NO IN (