数据库第08章多表联接.ppt

《数据库第08章多表联接.ppt》由会员分享，可在线阅读，更多相关《数据库第08章多表联接.ppt（31页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Copyright2008Copyright20081第第8章章 多表联接多表联接使用表的别名组合多个表中的数据合并多个结果集Copyright2008Copyright20082使用表的别名使用表的别名两种方式给字段起名 使用多表联接时，必须慎重地指定字段名，任何数使用多表联接时，必须慎重地指定字段名，任何数据库对象名称包含四个标识：据库对象名称包含四个标识：服务器名、数据库名、主人名称、对象名服务器名、数据库名、主人名称、对象名SELECT*FROM AS 别名SELECT*FROM 别名Copyright2008Copyright20083使用表的别名（续）使用表的别名（续）举例从订单详

2、细表中查询单价高于100的订单号、产品号、单价SELECT od.orderid,od.productid,od.unitprice FROM order details AS odWhere od.unitprice 100表的表的别名别名Copyright2008Copyright20084第第8章章 多表联接多表联接使用表的别名组合多个表中的数据合并多个结果集Copyright2008Copyright20085组合多个表中的数据组合多个表中的数据联接概述使用内联接使用外联接 使用交叉联接联接两个以上的表自联接（Self-Join）Copyright2008Copyright20086联

3、接概述联接概述从多个表中选择指定的字段关键字 JOIN 指定要联接的表，以及这些表联接的方式关键字 ON 指定联接条件查询两个或多个表并生成单个结果集所有联接的表必须共同拥有某些字段，这些字段必须有相同的或兼容的数据类型如果联接的表有相同字段，则引用这些字段时必须指定表名Copyright2008Copyright20087联接概述（续）联接概述（续）连接类型连接类型交叉连接：交叉连接：Cross Join （不太用）（不太用）内连接：内连接：Inner Join （最常用）（最常用）外连接：外连接：左外连接：左外连接：Left Outer Join右外连接：右外连接：Right Outer

4、Join完全连接：完全连接：Full Outer Join 自连接：自连接：Self JoinCopyright2008Copyright20088使用内联接使用内联接内联接通过比较被联接的表所共同拥有的字段，内联接通过比较被联接的表所共同拥有的字段，把多个表联接起来把多个表联接起来语法语法SELECT 列名列表列名列表FROM 表名表名1 INNER JOIN 表名表名2ON 表名表名1.列名列名 表名表名2.列名列名注意注意INNER可以省略。可以省略。包括包括=、等。等。Copyright2008Copyright20089使用内联接（续）使用内联接（续）buyer_idbuyer_id

5、 prod_idprod_id qtyqty114323151553711421003buyersbuyer_namebuyer_nameabcdbuyer_idbuyer_id1234结果结果buyer_namebuyer_nameaa cdbuyer_idbuyer_id qtyqty11341551137d41003USE joindbSELECT buyer_name,sales.buyer_id,qtyFROM buyers INNER JOIN salesON buyers.buyer_id=sales.buyer_idGOsales列出购买产品列出购买产品的顾客信息的顾客信息Co

6、pyright2008Copyright200810使用内联接（续）使用内联接（续）USE joindbSELECT buyer_name,sales.buyer_id,qtyFROM buyers，salesWHERE buyers.buyer_id=sales.buyer_idGOUSE joindbSELECT buyer_name,sales.buyer_id,qtyFROM buyers INNER JOIN salesON buyers.buyer_id=sales.buyer_idGO示例示例比较之一比较之一Copyright2008Copyright200811使用内联接（续）

7、使用内联接（续）USE joindbSELECT buyer_name,sales.buyer_id,qtyFROM buyers INNER JOIN salesON buyers.buyer_id=sales.buyer_idGOUSE joindbSELECT*FROM buyers INNER JOIN salesON buyers.buyer_id=sales.buyer_idGObuyer_namebuyer_nameaa dcbuyer_idbuyer_id qtyqty11431553711d41003buyer_namebuyer_nameaa dcdbuyer_idbuye

8、r_id11434buyer_idbuyer_id11434prod_idprod_id23 152qtyqty15537111003尽量不要尽量不要使用使用*比较之二比较之二Copyright2008Copyright200812使用外联接使用外联接左外联接的结果表中除了满足连接条件的行，左外联接的结果表中除了满足连接条件的行，还包括左表中的所有剩余行，但不包括右表中还包括左表中的所有剩余行，但不包括右表中的不匹配行的不匹配行使用左外联接时，请注意使用左外联接时，请注意不满足联接条件的记录将显示空值左联接可以显示左表中所有记录显示左表中所有记录可以把 LEFT OUTER JOIN 简写为

9、LEFT JOINCopyright2008Copyright200813使用外联接（左联接）使用外联接（左联接）USE joindbSELECT buyer_name,sales.buyer_id,qty FROM buyers LEFT OUTER JOIN sales ON buyers.buyer_id=sales.buyer_idGOsalesbuyer_idbuyer_id prod_idprod_idqtyqty114323151553711421003buyersbuyer_namebuyer_nameabcdbuyer_idbuyer_id1234结果结果buyer_name

10、buyer_nameaabcbuyer_idbuyer_idqtyqty11NULL3155NULL11d437d41003不满足条件不满足条件的显示的显示null查询所有购买查询所有购买者的购买情况者的购买情况Copyright2008Copyright200814使用外联接（右联接）使用外联接（右联接）右外联接结果表中除了满足连接条件的行，还右外联接结果表中除了满足连接条件的行，还包括右表中的所有剩余行，但不包括左表中的包括右表中的所有剩余行，但不包括左表中的不匹配行不匹配行使用右外联接时，请注意使用右外联接时，请注意不满足联接条件的记录将显示空值右联接可以显示右表中所有记录显示右表中所有

11、记录可以把 RIGHT OUTER JOIN 简写为 RIGHT JOINCopyright2008Copyright200815使用外联接（右联接）使用外联接（右联接）USE joindbSELECT buyer_name,sales.buyer_id,qty FROM buyers RIGHT OUTER JOIN sales ON buyers.buyer_id=sales.buyer_idGOsalesbuyer_idbuyer_id prod_idprod_idqtyqty114323151553711421003buyersbuyer_namebuyer_nameabcdbuyer

12、_idbuyer_id1234结果结果buyer_namebuyer_nameaacdbuyer_idbuyer_idqtyqty11341551137d41003示例示例Copyright2008Copyright200816使用交叉联接使用交叉联接交叉联接将从被联接的表中返回所有可能的记交叉联接将从被联接的表中返回所有可能的记录组合录组合为什么使用交叉联接为什么使用交叉联接在规范化的数据库中，很少使用交叉联接可以为数据库生成测试数据为清单及企业模板生成所有可能的组合数据Copyright2008Copyright200817使用交叉联接（续）使用交叉联接（续）USE joindbSELEC

13、T buyer_name,qty FROM buyers CROSS JOIN salesGO结果结果buyer_namebuyer_nameaaaaqtyqty1553711 a1003 b15 b5 b37 b11 b1003 c15.salesbuyer_idbuyer_id prod_idprod_idqtyqty114323151553711421003buyersbuyer_idbuyer_id1234buyer_namebuyer_name a b c d示例示例Copyright2008Copyright200818ABCa1b1c1a2b2c2a3b3c3CDc1d1c2d2

14、c4d3select R.*,S.*from R inner join S on R.C=S.CABR.CS.CDa1b1c1c1d1a2b2c2c2d2RSselect R.*,S.*from R,Swhere R.C=S.C内内连连接接内连接与几种外连接的对比内连接与几种外连接的对比Copyright2008Copyright200819select*from R left outer join S on R.C=S.CABR.CS.CDa1b1c1c1d1a2b2c2c2d2a3b3c3nullnullselect R.A,R.B,S.*from R right outer join S

15、on R.C=S.CABCDa1b1c1d1a2b2c2d2nullnullc4d3左左外外连连接接右右外外连连接接ABCa1b1c1a2b2c2a3b3c3CDc1d1c2d2c4d3R RS SCopyright2008Copyright200820select R.*,S.*from R full outer join S on R.C=S.CAB R.C S.CDa1b1c1c1d1a2b2c2c2d2a3b3c3nullnullnullnullnullc4d3全全外外连连接接ABCa1b1c1a2b2c2a3b3c3CDc1d1c2d2c4d3R RS SCopyright2008C

16、opyright200821联接两个以上的表联接两个以上的表联接任意数目的表都有可能，通过使用共同拥联接任意数目的表都有可能，通过使用共同拥有的字段，任何一个表都可以和其他表联接有的字段，任何一个表都可以和其他表联接为什么要联接两个以上的表为什么要联接两个以上的表 使用多重联接可以从多个表中得到彼此相关的信息至少有一个表具有外键，把要联接的表按一定关系联系起来组合键中的每一字段都必须由一个相应的 ON 子句引用可以使用 WHERE 子句限制结果集所返回的记录Copyright2008Copyright200822联接两个以上的表（续）联接两个以上的表（续）SELECT buyer_name,p

17、rod_name,qty FROM buyers INNER JOIN sales ON buyers.buyer_id=sales.buyer_id INNER JOIN products ON sales.prod_id=products.prod_idGOproductsprod_idprod_id prod_nameprod_name1234ApplesPearsOrangesBananas5Peachesbuyersbuyer_idbuyer_id1234buyer_namebuyer_name a b cdsalesbuyer_idbuyer_id1143prod_idprod_i

18、d231542qtyqty15537111003结果结果buyer_namebuyer_name aacddprod_nameprod_namePearsOrangesPeachesApplesPearsqtyqty15511371003什么顾客购买什么顾客购买了什么产品，了什么产品，多少数量？多少数量？Copyright2008Copyright200823自联接自联接（Self-Join）使用自联接，可以查询一个表中各记录之间的使用自联接，可以查询一个表中各记录之间的关系关系使用自联接时，应注意使用自联接时，应注意引用表的两份副本时，必须使用表的别名必须使用表的别名生成自联接时，表中每一行

19、都和自身比较一下，并生成重复的记录，使用使用 WHERE 子句来消除这些子句来消除这些重复记录重复记录Copyright2008Copyright200824自联接自联接（Self-Join）（续）（续）USE joindbSELECT a.buyer_id AS buyer1,a.prod_id ,b.buyer_id AS buyer2 FROM sales AS a JOIN sales AS b ON a.prod_id=b.prod_idWHERE a.buyer_id b.buyer_idGOsales bbuyer_idbuyer_id prod_idprod_id qtyqty

20、114323151553711421003sales abuyer_idbuyer_id prod_idprod_id qtyqty114323151553711421003结果结果buyer1buyer14prod_idprod_idbuyer2buyer221显示拥有相显示拥有相同产品的顾同产品的顾客列表。客列表。Copyright2008Copyright200825第第8章章 多表联接多表联接使用表的别名组合多个表中的数据合并多个结果集Copyright2008Copyright200826合并多个结果集合并多个结果集使用使用 UNION 操作符可以将多个查询产生的结果操作符可以将多个

21、查询产生的结果集合并成一个结果集集合并成一个结果集每一个查询必须有类似的数据、相同的字段数目相同的字段数目，并且在选择列表中字段顺序相同顺序相同UNION 与与UNION ALL的区别的区别UNION在进行表联接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。UNION ALL只是简单的将两个结果合并后就返回。如果需要排序，则要在最后一个有 UNION 的子句中使用 ORDER BY，以 指定排序方式。Copyright2008Copyright200827合并多个结果集（续）合并多个结果集（续）举例：举例：列出系号为列出系号为12的所有人员（包括

22、老师的所有人员（包括老师和学生）的姓名和出生日期。和学生）的姓名和出生日期。Copyright2008Copyright200828合并多个结果集（续）合并多个结果集（续）SELECT 学生姓名 AS 姓名,出生日期 FROM 学生信息表 where 系号=12Union SELECT 教师姓名,出生日期 FROM 教师信息表 where 系号=12GO 姓名姓名姓名姓名 出生日期出生日期出生日期出生日期PeterTom张三张三李四李四1967-01-01 1970-01-011981-01-011980-01-01结果结果示例示例Copyright2008Copyright200829合并多

23、个结果集（续）合并多个结果集（续）SELECT 学生姓名 AS 姓名,出生日期 FROM 学生信息表 where 系号=12Union all SELECT 教师姓名,出生日期 FROM 教师信息表 where 系号=12GO 姓名姓名姓名姓名 出生日期出生日期出生日期出生日期张三张三李四李四PeterTom1981-01-01 1980-01-011967-01-011970-01-01结果结果示例示例Copyright2008Copyright200830合并多个结果集（续）合并多个结果集（续）UNION 和和 JOIN 的区别的区别使用操作符 UNION，要求所引用的表必须具有相似的数据类型、相同的字段数，每个查询中的选择列表必须具有相同的顺序。使用操作符 JOIN，只要求联接的表共同拥有某些字段。用 UNION 分解复杂的查询会提高查询速度，而JOIN联接表越多，查询速度越慢。Copyright2008Copyright200831回顾回顾学习完本章后，将能够：使用表的别名组合多个表中的数据合并多个结果集