数据库第08章多表联接教学材料.ppt

Copyright2008Copyright20081第8章 多表联接 使用表的别名 组合多个表中的数据 合并多个结果集Copyright2008Copyright20082使用表的别名 两种方式 给字段起名 使用多表联接时，必须慎重地指定字段名，任何数据库对象名称包含四个标识：服务器名、数据库名、主人名称、对象名SELECT * FROM server.database.schema.table AS 别名SELECT * FROM server.database.schema.table 别名Copyright2008Copyright20083使用表的别名（续） 举例从订单详细表中查询单价高于100的订单号、产品号、单价SELECT od.orderid, od.productid, od.unitprice FROM order details AS odWhere od.unitprice 100表的别名Copyright2008Copyright20084Copyright2008Copyright20085组合多个表中的数据 联接概述 使用内联接 使用外联接 使用交叉联接 联接两个以上的表 自联接 （Self-Join）Copyright2008Copyright20086联接概述 从多个表中选择指定的字段 关键字 JOIN 指定要联接的表，以及这些表联接的方式 关键字 ON 指定联接条件 查询两个或多个表并生成单个结果集 所有联接的表必须共同拥有某些字段，这些字段必须有相同的或兼容的数据类型 如果联接的表有相同字段，则引用这些字段时必须指定表名Copyright2008Copyright20087联接概述（续） 连接类型 交叉连接：Cross Join （不太用） 内连接：Inner Join （最常用） 外连接： 左外连接：Left Outer Join 右外连接：Right Outer Join 完全连接：Full Outer Join 自连接：Self JoinCopyright2008Copyright20088使用内联接 内联接通过比较被联接的表所共同拥有的字段，把多个表联接起来 语法SELECT 列名列表FROM 表名1 INNER JOIN 表名2ON 表名1.列名 表名2.列名 注意 INNER可以省略。

包括= 、 、等Copyright2008Copyright20089使用内联接（续）buyer_idbuyer_id 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 列出购买产品的顾客信息Copyright2008Copyright200810使用内联接（续）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使用内联接（续）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_idbuyer_id11434buyer_idbuyer_id11434prod_idprod_id23 152qtyqty15537111003尽量不要使用* 比较之二Copyright2008Copyright200812使用外联接 左外联接的结果表中除了满足连接条件的行，还包括左表中的所有剩余行，但不包括右表中的不匹配行 使用左外联接时，请注意 不满足联接条件的记录将显示空值 左联接可以显示左表中所有记录 可以把 LEFT OUTER JOIN 简写为 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_namebuyer_nameaabcbuyer_idbuyer_idqtyqty11NULL3155NULL11d437d41003不满足条件的显示null 查询所有购买者的购买情况Copyright2008Copyright200814使用外联接（右联接） 右外联接结果表中除了满足连接条件的行，还包括右表中的所有剩余行，但不包括左表中的不匹配行 使用右外联接时，请注意 不满足联接条件的记录将显示空值 右联接可以显示右表中所有记录 可以把 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_idbuyer_id1234结果buyer_namebuyer_nameaacdbuyer_idbuyer_idqtyqty11341551137d41003示例Copyright2008Copyright200816使用交叉联接 交叉联接将从被联接的表中返回所有可能的记录组合 为什么使用交叉联接 在规范化的数据库中，很少使用交叉联接 可以为数据库生成测试数据 为清单及企业模板生成所有可能的组合数据Copyright2008Copyright200817使用交叉联接（续）USE joindbSELECT 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示例Copyright2008Copyright200818ABCa1b1c1a2b2c2a3b3c3CDc1d1c2d2c4d3select 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 Son R.C = S.CABCDa1b1c1d1a2b2c2d2nullnullc4d3左外连接右外连接ABCa1b1c1a2b2c2a3b3c3CDc1d1c2d2c4d3RSCopyright2008Copyright200820select R.*,S.*from R full outer join S on R.C = S.CAB R.C S.CDa1b1c1c1d1a2b2c2c2d2a3b3c3nullnullnullnullnullc4d3全外连接ABCa1b1c1a2b2c2a3b3c3CDc1d1c2d2c4d3RSCopyright2008Copyright200821联接两个以上的表 联接任意数目的表都有可能，通过使用共同拥有的字段，任何一个表都可以和其他表联接 为什么要联接两个以上的表 使用多重联接可以从多个表中得到彼此相关的信息 至少有一个表具有外键，把要联接的表按一定关系联系起来 组合键中的每一字段都必须由一个相应的 ON 子句引用 可以使用 WHERE 子句限制结果集所返回的记录Copyright2008Copyright200822联接两个以上的表（续）SELECT buyer_name, prod_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_id231542qtyqty15537111003结果buyer_namebuyer_name aacddprod_nameprod_namePearsOrangesPeachesApplesPearsqtyqty15511371003 什么顾客购买了什么产品，多少数量？Copyright2008Copyright200823自联接 （Self-Join） 使用自联接，可以查询一个表中各记录之间的关系 使用自联接时，应注意 引用表的两份副本时，必须使用表的别名 生成自联接时，表中每一行都和自身比较一下，并生成重复的记录，使用 WHERE 子句来消除这些重复记录Copyright2008Copyright200824自联接 （Self-Join）（续）USE joindbSELECT a.buyer_id AS buyer1, a.prod。