数据库技术-杭州电子科技大学8#-第8章-多表联接-内-外-交叉-自连接-UNION 和 JOIN

Copyright@2006CollegeofITSoft(HZIEE)1VersionNo:1.0第8章多表联接使用表的别名组合多个表中的数据合并多个结果集Copyright@2006CollegeofITSoft(HZIEE)2VersionNo:1.0使用表的别名两种方式给字段起名使用多表联接时，必须慎重地指定字段名，任何数据库对象名称包含四个标识：服务器名、数据库名、主人名称、对象名SELECT*FROMserver.database.schema.tableAS别名SELECT*FROMserver.database.schema.table别名Copyright@2006CollegeofITSoft(HZIEE)3VersionNo:1.0使用表的别名（续）举例从订单详细表中查询单价高于100的订单号、产品号、单价SELECTod.orderid,od.productid,od.unitpriceFROM[orderdetails]ASodWhereod.unitprice100表的别名Copyright@2006CollegeofITSoft(HZIEE)4VersionNo:1.0第8章多表联接使用表的别名组合多个表中的数据合并多个结果集Copyright@2006CollegeofITSoft(HZIEE)5VersionNo:1.0组合多个表中的数据联接概述使用内联接使用外联接使用交叉联接联接两个以上的表自联接（Self-Join）Copyright@2006CollegeofITSoft(HZIEE)6VersionNo:1.0联接概述从多个表中选择指定的字段关键字JOIN指定要联接的表，以及这些表联接的方式关键字ON指定联接条件查询两个或多个表并生成单个结果集所有联接的表必须共同拥有某些字段，这些字段必须有相同的或兼容的数据类型如果联接的表有相同字段，则引用这些字段时必须指定表名Copyright@2006CollegeofITSoft(HZIEE)7VersionNo:1.0联接概述（续）连接类型交叉连接：CrossJoin（不太用）内连接：[Inner]Join（最常用）外连接：左外连接：Left[Outer]Join右外连接：Right[Outer]Join完全连接：Full[Outer]Join自连接：SelfJoinCopyright@2006CollegeofITSoft(HZIEE)8VersionNo:1.0使用内联接内联接通过比较被联接的表所共同拥有的字段，把多个表联接起来语法SELECT列名列表FROM表名1[INNER]JOIN表名2ON表名1.列名比较运算符表名2.列名注意INNER可以省略。比较运算符包括=、、、等。Copyright@2006CollegeofITSoft(HZIEE)9VersionNo:1.0使用内联接（续）buyer_idprod_idqty114323151553711421003buyersbuyer_nameabcdbuyer_id1234结果buyer_nameaacdbuyer_idqty11341551137d41003USEjoindbSELECTbuyer_name,sales.buyer_id,qtyFROMbuyersINNERJOINsalesONbuyers.buyer_id=sales.buyer_idGOsales列出购买产品的顾客信息Copyright@2006CollegeofITSoft(HZIEE)10VersionNo:1.0使用内联接（续）USEjoindbSELECTbuyer_name,sales.buyer_id,qtyFROMbuyers，salesWHEREbuyers.buyer_id=sales.buyer_idGOUSEjoindbSELECTbuyer_name,sales.buyer_id,qtyFROMbuyersINNERJOINsalesONbuyers.buyer_id=sales.buyer_idGO示例比较之一Copyright@2006CollegeofITSoft(HZIEE)11VersionNo:1.0使用内联接（续）USEjoindbSELECTbuyer_name,sales.buyer_id,qtyFROMbuyersINNERJOINsalesONbuyers.buyer_id=sales.buyer_idGOUSEjoindbSELECT*FROMbuyersINNERJOINsalesONbuyers.buyer_id=sales.buyer_idGObuyer_nameaadcbuyer_idqty11431553711d41003buyer_nameaadcdbuyer_id11434buyer_id11434prod_id23152qty15537111003尽量不要使用*比较之二Copyright@2006CollegeofITSoft(HZIEE)12VersionNo:1.0使用外联接左外联接的结果表中除了满足连接条件的行，还包括左表中的所有剩余行，但不包括右表中的不匹配行使用左外联接时，请注意不满足联接条件的记录将显示空值左联接可以显示左表中所有记录可以把LEFTOUTERJOIN简写为LEFTJOINCopyright@2006CollegeofITSoft(HZIEE)13VersionNo:1.0使用外联接（左联接）USEjoindbSELECTbuyer_name,sales.buyer_id,qtyFROMbuyersLEFTOUTERJOINsalesONbuyers.buyer_id=sales.buyer_idGOsalesbuyer_idprod_idqty114323151553711421003buyersbuyer_nameabcdbuyer_id1234结果buyer_nameaabcbuyer_idqty11NULL3155NULL11d437d41003不满足条件的显示null查询所有购买者的购买情况Copyright@2006CollegeofITSoft(HZIEE)14VersionNo:1.0使用外联接（右联接）右外联接结果表中除了满足连接条件的行，还包括右表中的所有剩余行，但不包括左表中的不匹配行使用右外联接时，请注意不满足联接条件的记录将显示空值右联接可以显示右表中所有记录可以把RIGHTOUTERJOIN简写为RIGHTJOINCopyright@2006CollegeofITSoft(HZIEE)15VersionNo:1.0使用外联接（右联接）USEjoindbSELECTbuyer_name,sales.buyer_id,qtyFROMbuyersRIGHTOUTERJOINsalesONbuyers.buyer_id=sales.buyer_idGOsalesbuyer_idprod_idqty114323151553711421003buyersbuyer_nameabcdbuyer_id1234结果buyer_nameaacdbuyer_idqty11341551137d41003示例Copyright@2006CollegeofITSoft(HZIEE)16VersionNo:1.0使用交叉联接交叉联接将从被联接的表中返回所有可能的记录组合为什么使用交叉联接在规范化的数据库中，很少使用交叉联接可以为数据库生成测试数据为清单及企业模板生成所有可能的组合数据Copyright@2006CollegeofITSoft(HZIEE)17VersionNo:1.0使用交叉联接（续）USEjoindbSELECTbuyer_name,qtyFROMbuyersCROSSJOINsalesGO结果buyer_nameaaaaqty1553711a1003b15b5b37b11b1003c15......salesbuyer_idprod_idqty114323151553711421003buyersbuyer_id1234buyer_nameabcd示例Copyright@2006CollegeofITSoft(HZIEE)18VersionNo:1.0ABCa1b1c1a2b2c2a3b3c3CDc1d1c2d2c4d3selectR.*,S.*fromRinnerjoinSonR.C=S.CABR.CS.CDa1b1c1c1d1a2b2c2c2d2RSselectR.*,S.*fromR,SwhereR.C=S.C内连接内连接与几种外连接的对比Copyright@2006CollegeofITSoft(HZIEE)19VersionNo:1.0select*fromRleftouterjoinSonR.C=S.CABR.CS.CDa1b1c1c1d1a2b2c2c2d2a3b3c3nullnullselectR.A,R.B,S.*fromRrightouterjoinSonR.C=S.CABCDa1b1c1d1a2b2c2d2nullnullc4d3左外连接右外连接ABCa1b1c1a2b2c2a3b3c3CDc1d1c2d2c4d3RSCopyright@2006CollegeofITSoft(HZIEE)20VersionNo:1.0selectR.*,S.*fromRfullouterjoinSonR.C=S.CABR.CS.CDa1b1c1c1d1a2b2c2c2d2a3b3c3nullnullnullnullnullc4d3全外连接ABCa1b1c1a2b2c2a3b3c3CDc1d1c2d2c4d3RSCopyright@2006CollegeofITSoft(HZIEE)21VersionNo:1.0联接两个以上的表联接任意数目的表都有可能，通过使用共同拥有的字段，任何一个表都可以和其他表联接为什么要联接两个以上的表使用多重联接可以从多个表中得到彼此相关的信息至少有一个表具有外键，把要联接的表按一定关系联系起来组合键中的每一字段都必须由一个相应的ON子句引用可以使用WHERE子句限制结果集所返回的记录Copyright@2006CollegeofITSoft(HZIEE)22VersionNo:1.0联接两个以上的表（续）SELECTbuyer_name,prod_name,qtyFROMbuyersINNERJOINsalesONbuyers.buyer_id=sales.buyer_idINNERJOINproductsONsales.prod_id=products.prod_idGOproductsprod_idprod_name1234ApplesPearsOrangesBananas5Peachesbuyersbuyer_id1234buyer_nameabcdsalesbuyer_id1143prod_id231542qty15537111003结果buyer_nameaacddprod_namePearsOrangesPeachesApplesPearsqty15511371003什么顾客购买了什么产品，多少数量？Copyright@2006CollegeofITSoft(HZIEE)23VersionNo:1.0自联接（Self-Join）使用自联接，可以查询一个表中各记录之间的关系使用自联接时，应注意引用表的两份副本时，必须使用表的别名生成自联接时，表中每一行都和自身比较一下，并生成重复的记录，使用WHERE子句来消除这些重复记录Copyright@2006CollegeofITSoft(HZIEE)24VersionNo:1.0自联接（Self-Join）（续）USEjoindbSELECTa.buyer_idASbuyer1,a.prod_id,b.buyer_idASbuyer2FROMsalesASaJOINsalesAS