实验六多表查询doc.docx

1、实验六多表查询doc实验六 多表查询 实验目的1、 掌握嵌套查询的概念2、 掌握连接查询3、 进一步掌握分组操作4、 掌握Union的使用实验环境 硬件环境：PC机一台 软件环境：SQL Server 2008/2005实验内容在供应商数据库中供应商关系：S(SNO，SNAME，ADDR)零件关系：P(PNO，PNAME，COLOR，WEIGHT)工程项目关系：J(JNO,JNAME，CITY，BALANCE)供应情况关系：SPJ(SNO,PNO,JNO，PRICE，QTY)上述各属性的含义是：供应商号(SNO)、供应商名(SNAME)和地址(ADDR)，零件号(PNO)、零件名（PNAME）

2、、颜色（COLOR）、重量（WEIGHT）、单价(PRICE)、工程项目号(JNO)、工程项目名称(JNAME)、城市(CITY)、余额（BALANCE）、供应数量(QTY)。1、 查询供应商的名字，以及各自负责的项目数，结果要求按照项目数的降序排列（连接、分组、排序）Select sname, count（distinct JNO）From S, SPjWhere S.sno = SPJ .sno Group by snameSelect sname, count（distinct JNO）From s, spjWhere S.sno = spj.snoGroup by snameOrder

3、 by count（distinct JNO）2、 查询重量比螺栓重的零件名称，零件重量（子查询或自身连接）Select pname,weightFrom PWhere weight （Select weight From PWhere pname = 螺栓）Select a.name,a.weightfrom P a, P bwhere a.weight b.weightand b.pname= 螺栓3、 查询P（零件）表中各种颜色及其对应的零件种类的数目（分组和聚集查询）Select color, count（PNO）From P Group by color4、 统计天津地区的项目使用零

4、件的种数(超过3种)和零件总数量。要求查询结果按零件的种数升序排列，种数相同时按总数量降序排列。（连接、分组、聚集函数、排序）Select JNO, count（PNO），sum(qty)From J,SPJWhere J.jno = spj.jno and city=天津Group by JNOHaving count（PNO）35、查询既供应P1零件又供应P2零件的供应商编号，供应商名称（多重条件查询）6、查询每个供应商供应零件的情况，要求列出供应商名称，零件名称，供应数量，并按供应数量的升序排序（连接、分组、聚集函数、排序）7、查询与“万胜”供应商在同一城市的供应商的详细资料（子查询）8

5、、查询供应商品种类最多的供应商编号、供应商名。9、查询既生产螺母，也生产螺栓的供应商编号和供应商名。10、查询生产螺丝刀但不生产螺母的供应商名11、查询“万胜”和“精益”两个供应商的供货情况，查询结果中包括供应商名、项目名、零件名和供应数量。用两个select语句实现查询，并用union将两个语句的查询结果合并在一起。三、实验步骤1、对文件夹中的SPJ数据库进行附加还原。2、按照以上要求完成题目。3、要求写出查询代码并将查询结果截图附在代码后。参考解答1、查询供应商的名字，以及各自负责的项目数，结果要求按照项目数的降序排列（连接、分组、排序）解题思路：1)本题要查询供应商名，这项数据仅在供应商

6、表（S）中才有，而要统计每个供应商向几个项目供货，则需要对SPJ表中的数据进行统计。这里需要对S和SPJ进行连接，两表的共同字段是SNO，用该字段进行连接。SELECT SNAME,JNO,PNOFROM S,SPJWHERE S.SNO=SPJ.SNO代码执行结果：图1-1说明：该结果表明，这些供应商向项目提供零件的信息，这里项目和零件都用编号表示。2）这还没有达到题目要求的效果。题目要求统计每个供应商负责的项目数。这里需要分组。用供应商名进行分组，计算每个组中项目号的数目。这里请注意，“万胜”这个供应商的供应记录有6条，但是仔细看，不难发现，他供应的项目其实是4个，这里需要考虑到项目编号重

7、复的情况（由于供应商每向一个项目供一种零件，数据库中都会产生相应的记录。）因此，对代码再进行修改。SELECT SNAME,COUNT(JNO)FROM S,SPJWHERE S.SNO=SPJ.SNOGROUP BY SNAME执行结果：图1-2运行结果说明：这里的执行结果已经可以统计出各供应商负责的项目数，但是由于没有考虑到一个供应商有可能存在多条供货记录，因此，这个结果依然不正确。3）对代码添加关键字“DISTINCT”可不统计重复的字段。SELECT SNAME,COUNT(DISTINCT JNO)FROM S,SPJWHERE S.SNO=SPJ.SNOGROUP BY SNAME

8、ORDER BY COUNT(DISTINCT JNO) DESC执行结果：图1-3这才是最终的正确结果。2、查询重量比螺栓重的零件名称，零件重量（子查询或自身连接）解法：子查询解题思路：先查询螺栓的重量，再用这重量与零件表中的其他零件的重量进行比较。1)SELECT WEIGHTFROM PWHERE PNAME=螺栓图2-1螺栓的重量2）将第一步查询中的重量作为外层查询的条件。SELECT PNAME,WEIGHTFROM PWHERE WEIGHT( SELECT WEIGHT FROM P WHERE PNAME=螺栓)图2-2执行结果说明：先执行内层查询，其返回结果就是螺栓的重量，用

9、这个重量与外层查询中的零件重量进行比较，查询“WEIGHT”字段的值大于17的记录。得到如上结果。这里注意，在外层查询的WHERE字句中的比较字段与子查询的返回结果应该相同，此外，为了让查询层次明确，子查询部分最好缩进。解法2：自身连接SELECT A.PNAME,A.WEIGHTFROM P A,P BWHERE A.WEIGHTB.WEIGHT AND B.PNAME=螺栓图2-2说明：在自身连接时，需将一个表理解成两个表。在本题中，将P理解成A,B两表，用B表查询螺栓的重量，再用A表找出重量比它重的零件信息。3、查询P（零件）表中各种颜色及其对应的零件种类的数目（分组和聚集查询）SELE

10、CT COLOR,COUNT(PNO)FROM PGROUP BY COLOR图3-1说明：本题仅对零件信息进行查询，不涉及到项目和供应商，因此无需多表连接。但是请大家注意分析题目的要求：求各种颜色的零件各有几类。即，题目要查询的是“红色的零件有几类，绿色的零件有几类”这样的信息，思考题目要求，不难发现这时需要用到分组，分组的依据就是颜色。因此在GROUP BY 子句中用的是COLOR字段。4、统计天津地区的项目使用零件的种数(超过3种)和零件总数量。要求查询结果按零件的种数升序排列，种数相同时按总数量降序排列。（连接、分组、聚集函数、排序）1)本题需要分成两个步骤考虑。其一，考虑项目的编号，

11、项目的名称，项目使用的零件（这里只要统计每个项目用的零件类别，因此只要零件号），每次使用的数量。因此，需要连接项目表（J）和供应关系表（SPJ）。并且需要指明项目所在的城市是天津。SELECT J.JNO,JNAME,PNO,QTYFROM J,SPJWHERE J.JNO=SPJ.JNO AND CITY=天津图4-1由查询结果可知，天津地区只有弹簧厂和造船厂两个项目，用目测就知道前者使用了一种零件，后者有3种。按照题目要求，还需要进行分组。2）统计零件类别数，用COUNT函数，统计总数量，则要用SUMSELECT J.JNO,JNAME,COUNT(PNO),SUM(QTY)FROM J,

12、SPJWHERE J.JNO=SPJ.JNO AND CITY=天津GROUP BY J.JNO,JNAME图4-25、查询既供应P1零件又供应P2零件的供应商编号，供应商名称（多重条件查询）解题思路：题目要求查询供应商号和供应商名，这两项信息要通过供应商表（S）获得，同时，还需要供应的一些具体情况，这时就要用到SPJ。这题可以用子查询或连接查询实现。本例用子查询。1） 先到SPJ表中查询供应P1零件的供应商号：SELECT SNO FROM SPJ WHERE PNO=P1图5-12） 在再到SPJ表中查询供应P2零件的供应商号：SELECT SNO FROM SPJ WHERE PNO=P

13、2图5-23） 由以上两个步骤的查询结果可知，两种零件都有提供的供应商只有S1，本题求的是两个集合的交集。最后的查询是，用两个子查询获得同时提供两种零件的供应商号，再用此结果到供应商表（S）中获取相应供应商信息。SELECT SNO,SNAMEFROM SWHERE SNO IN(SELECT SNO FROM SPJ WHERE PNO=P1)AND SNO IN(SELECT SNO FROM SPJ WHERE PNO=P2)图5-3思考：本题如果用以下语句是否可以实现SELECT SNO,SNAMEFROM SWHERE SNO IN(SELECT SNO FROM SPJ WHERE

14、 PNO=P1 AND PNO=P2)6、查询每个供应商供应零件的情况，要求列出供应商名称，零件名称，供应数量，并按供应数量的升序排序（连接、分组、聚集函数、排序）解题思路：1）本题要求查询供应商名字、零件名称、数量，从题目要求的字段可以看出，本题需要连接供应商表（S），零件表（P）和供应情况表（SPJ）SELECT SNAME,PNAME,JNO,QTYFROM S,P,SPJWHERE S.SNO=SPJ.SNO AND P.PNO=SPJ.PNO图6-1这个查询步骤显示供应商名，零件名，项目号，以及这次供应零件的数量。按照题目的要求，要统计每个供应商提供的某一类零件的总数量，比如，“精益

15、”供应的螺母总量是200+100+700+100. 2）还需要用供应商名和零件名共同分组,并排序。SELECT SNAME,PNAME,SUM(QTY) 供应数量FROM S,P,SPJWHERE S.SNO=SPJ.SNO AND P.PNO=SPJ.PNOGROUP BY SNAME,PNAMEORDER BY SUM(QTY)图6-27、查询与“万胜”供应商在同一城市的供应商的详细资料（子查询）解题思路：本题用子查询，先查询“万胜”所在的城市：然后用子查询（内层查询）的返回结果作为外层查询的条件。1）查询“万胜”的城市SELECT CITY FROM S WHERE SNAME=万胜图7

16、-12)用第一步中的结果作为外层查询的条件。SELECT * FROM SWHERE CITY=(SELECT CITY FROM S WHERE SNAME=万胜)AND SNAME!=万胜图7-2题目要求查询供应商的信息，这就包括了供应商号、供应商名、供应商所在城市这些信息。所以在SELECT子句中用*替代所有的字段。另外，题目要插叙你的目标应该是万胜之外的其他供应商，因此应该用“SNAME!=万胜”这个条件排除万胜这个供应商自身的信息。代码的执行结果如上图所示。8、查询供应商品种类最多的供应商编号、供应商名。解题思路：本题即要查询供应商编号，又要供应商的名字和供应的零件类别数，因此，本题

17、需要连接供应商表(S)和供应关系表(SPJ)。1)先进行连接查询，获取每个供应商名及其供应的零件编号,并用DISTINCT关键字消除重复的记录。SELECT DISTINCT S.SNO,SNAME,PNOFROM S,SPJWHERE S.SNO=SPJ.SNO图8-12）对第一步中的查询结果进行目测，可以得知，S1供应2种零件，S2供应2种，所以，这里要用供应商进行分组，统计每组的零件类别数。SELECT DISTINCT S.SNO,SNAME,COUNT(DISTINCT PNO)FROM S,SPJWHERE S.SNO=SPJ.SNOGROUP BY S.SNO,SNAME图8-2

18、3）由第2步的执行结果可知，S1，S2，S3是供应零件种类最多的供应商，现在需要再对代码进行改进，导出数量最多的三个供应商。SELECT DISTINCT S.SNO,SNAMEFROM S,SPJWHERE S.SNO=SPJ.SNOGROUP BY S.SNO,SNAMEHAVING COUNT(DISTINCT PNO)=ALL(SELECT COUNT(DISTINCT PNO) FROM S,SPJ WHERE S.SNO=SPJ.SNO GROUP BY S.SNO,SNAME)图8-3在第三步中，在HAVING子句中使用子查询，获取每个供应商提供的零件类别数，外层查询的逻辑与内层

19、查询相同，如果满足=ALL这一条件，则是供应种类数目最多的供应商。这个部分需要各位同学多花时间好好理解。9、查询既生产螺母，也生产螺栓的供应商编号和供应商名。解题思路：本题求的是两个集合的交集。一个集合是上次螺母的集合，另一个集合是生产螺栓的集合。先编写语句，分别求生产螺母的供应商号和生产螺栓的供应商号。1）先分别从零件表（P）中查询螺母、螺栓的编号，然后用此编号到供应关系（SPJ）中查询有提供此商品的供应商号（SNO）SELECT DISTINCT SNO FROM SPJWHERE PNO IN (SELECT PNO FROM P WHERE PNAME=螺母)图9-1SELECT DI

20、STINCT SNO FROM SPJWHERE PNO IN (SELECT PNO FROM P WHERE PNAME=螺栓)图9-22）从查询结果可知，这两个集合的交集是S1，因此语句可以修改为以下代码：SELECT SNO,SNAME FROM SWHERE SNO IN( SELECT DISTINCT SNO FROM SPJ WHERE PNO IN (SELECT PNO FROM P WHERE PNAME=螺母) )AND SNO IN( SELECT DISTINCT SNO FROM SPJ WHERE PNO IN (SELECT PNO FROM P WHERE

21、PNAME=螺栓) )图9-3注意，在这里，两个条件之间是且的关系，必须用AND连接。10、查询生产螺丝刀但不生产螺母的供应商名解题思路：本题的思路与第9题类似，也是集合运算，但是它的要求是要在螺丝刀的生产商中扣除生产螺母的产商。代码如下：SELECT SNAME FROM SWHERE SNO IN(SELECT SNO FROM SPJ WHERE PNO IN (SELECT PNO FROM P WHERE PNAME=螺丝刀)AND SNO NOT IN( SELECT DISTINCT SNO FROM SPJ WHERE PNO IN (SELECT PNO FROM P WHERE PNAME=螺母) )图10-1