6多表连接.docx

1、6多表连接第六节 多表连接应用背景数据库是由多张表组成的存储结构，并通过多张表之间的关系建立起完整的有效的数据存储形式，形成关系型数据库。作为数据查询语言SQL，提供了功能强大的数据表连接查询功能，使多张表格之间形成有效的数据联系，使得关系数据库在大型数据库应用中占据了主角地位。一个普通的大型数据库应用程序所使用的数据库中，有多达几百张表的数据，那么如何将这些表高效的有机的联系起来，就成为设计关系数据库的一个重要指标。优良的数据库设计指标包括：1减少数据冗余，去除掉多余的数据冗余，可以通过建立表之间的连接关系完成。2数据更新正确，不能因为表之间存在关系后，使得更新记录出现不正常的数据。3添加数

2、据正常，添加数据过程中，应该保持数据表之间的关系，确定表之间的连接。4查询简便灵活，在建立数据连接的查询过程中，连接清晰简便，操作灵活准确。数据库设计是应用软件成功与否的一项重要标志。设计数据库，除与系统分析结果，设计员的水平等有关外，还可以参考一些规范的设计范式，下面简单介绍数据库的2个基本设计范式：1第一范式：要求表的每列都是不可再分的简单数据项，所以1对N关系就必须用多表表示，而不能用一张表表示。2第二范式：表中的每一个非主键列必须完全函数依赖于主键，就是说表中除主键之外的其他列，都必须通过主键能够唯一确定。数据库的设计非常复杂，没有一成不变的东西，需要就地取材，解决问题，简单化问题。知

3、识要点(1) 传统连接连接就是将多个表中的数据连接到一起的查询，即连接操作可以在一个Select语句中完成从多个表中查找和处理数据，使用连接时可以使用名字相同的不同表的列，也可以不同，但要求连接的列不需可连接，即数据类型相同。传统的连接语法如下：Select * from Tblname1 T1,Tblname2 T2 where T1.column=T2.column连接SQL语句的明显标志为在From子句后边，有多个表Tblname1, Tblname2 ,Where子句后有表连接键T1.column=T2.column。例1：以销售单据为例，察看每个销售单据的真实客户姓名，单据号，单据金

4、额，销售代表等信息。Select 语句如下：SELECT T1.rid, T1.cid, Tame, T1.rmoney, T1.remployeeFROM retail T1 , customer T2 where T1.cid = T2.cid查询结果如下：ridcidcnamermoneyremployee1kh01 张经理 1200liuhong 2kh02 李经理 1600liuhong 3kh01 张经理 1600liuhong 4kh01 张经理 3200zhangling 5kh02 李经理 1800zhangling 例2：以销售明细为例，查询每个销售明细记录的真实产品名称，

5、产品的零售单价，实际销售单价，销售金额等等信息，Select语句如下：SELECT T1.rid, T1.pcode, T2.pname, T2.ptype, T2.pprice, T1.dprice,T1.dmoneyFROM Detail T1 ,product T2 where T1.pcode = T2.pcode查询结果如下：ridpcodepnameptypeppricedpricedmoney1001 motorolaV302800120024001002 sonyC303200240048001003 nokiasony61104500150015002002 sonyC303

6、200220044002003 nokiasony61104500150030003001 motorolaV302800120024003002 sonyC303200230046003003 nokiasony6110450015003000例3：多表连接，建立查询销售单据与销售明细连接，销售明细中的产品代码与产品信息中的代码连接，销售单据的客户代码与客户信息的客户代码连接，构成4张表的多表连接，Select 语句如下：select T1.rid,T1.cid,Tame,T2.pcode,T4.pname,T2.dprice, T2.dnumber,T2.dmoney from retai

7、l T1,detail T2,customer T3,product T4 where T1.rid=T2.rid and T1.cid=T3.cid and T2.pcode=T4.pcode查询结果如下：ridcidcnamepcodepnamedpricednumberdmoney1kh01 张经理 001 motorola1200224001kh01 张经理 002 sony2400248001kh01 张经理 003 nokia1500115002kh02 李经理 002 sony2200244002kh02 李经理 003 nokia1500230003kh01 张经理 001 m

8、otorola1200224003kh01 张经理 002 sony2300246003kh01 张经理 003 nokia150023000(2) 内连接Inner Join前面的多表连接写法，把表连接条件写在Where子句之后，导致表连接之间与记录筛选条件混合，使查询语句不清晰，故新的表连接条件采用关键字Join表示，连接又分为内连接，左连接，右连接，全连接。标准内连接写法为：Select Distinct select_list from Tblname1 T1 Inner join Tblname2 T2 On T1.colomn=T2.colomn where condition G

9、roup By Order By 关键字Inner Join 前后为需要连接的表名，关键字On 后边是连接条件，在执行完连接条件后，才进行记录的筛选语句Where 子句，这样更符合结构化的查询语法。例1：以销售单据为例，察看每个销售单据的真实客户姓名，单据号，单据金额，销售代表等信息，且要求客户姓名中包含“李”，那么修改后的Select 语句如下：SELECT T1.rid, T1.cid, Tame, T1.rmoney, T1.remployeeFROM retail T1 INNER JOIN customer T2 ON T1.cid = T2.cidWHERE (Tame LIKE

10、%李%)ORDER BY T1.rid查询结果如下：ridcidcnamermoneyremployee2kh02 李经理 1600liuhong 5kh02 李经理 1800zhangling 例2：多表连接，建立查询销售单据与销售明细连接，销售明细中的产品代码与产品信息中的代码连接，销售单据的客户代码与客户信息的客户代码连接，构成4张表的多表连接，要求客户名称包含“张”，产品名称包含“Nokia”，Select 语句如下：SELECT T1.rid, T1.cid, Tame, T2.pcode, T4.pname, T2.dprice, T2.dnumber, T2.dmoneyFROM

11、 retail T1 INNER JOIN detail T2 ON T1.rid = T2.rid INNER JOIN customer T3 ON T1.cid = T3.cid INNER JOIN product T4 ON T2.pcode = T4.pcodeWHERE (T4.pname LIKE %nokia%) AND (Tame LIKE %张%)查询结果如下：ridcidcnamepcodepnamedpricednumberdmoney1kh01 张经理 003 nokia1500115003kh01 张经理 003 nokia150023000(3) 左连接内连接一

12、般把所有符合条件的记录都显示出来，而不符合连接条件的记录过滤掉。但有时候可能期望显示某个表中的所有记录，包括不符合条件的记录，那么就需要使用外连接。使用外连接可以方便地将连接结果中包含某个表中的所有记录。外连接，包括左连接，右连接，全连接。左连接Left Outer Join 关键字左侧的表包含所有记录，右侧的表则只包含部分符合连接条件的记录。语法如下：Select Distinct select_list from Tblname1 T1 Left Outer join Tblname2 T2 On T1.colomn=T2.colomn where condition Group By O

13、rder By 左连接语句将列出所有Tblname1中的所有记录，而Tblname2的记录则只显示符合连接条件的记录。例1：列出所有客户信息，并把相关客户的销售单据信息列出。Select语句如下：SELECT T1.cid, Tame, T2.cdate AS Expr1, T2.rmoney AS Expr2FROM customer T1 LEFT OUTER JOIN retail T2 ON T1.cid = T2.cid查询结果如下：cidcnameExpr1Expr2kh01 张经理 2006-9-11200kh01 张经理 2006-9-31600kh01 张经理 2006-9-

14、33200kh02 李经理 2006-9-21600kh02 李经理 2006-9-41800kh03 老刘 liubang 刘工 mingren 韩先生 例2：列出所有产品信息，并将相关产品信息的销售数量合计，销售金额合计列出，Select语句如下：SELECT T1.pcode, SUM(T2.dnumber) AS Expr1, SUM(T2.dmoney) AS Expr2FROM product T1 LEFT OUTER JOIN detail T2 ON T1.pcode = T2.pcodeGROUP BY T1.pcode上述Select语句没有列出产品名称，是因为在分组列表

15、中没有列出产品名称，查询结果如下：pcodeExpr1Expr2001 44800002 613800003 57500004 005 006 (4) 右连接右连接与左连接正好相反，右连接Right Outer Join 关键字右侧的表包含所有记录，左侧的表则只包含部分符合连接条件的记录。语法如下：Select Distinct select_list from Tblname1 T1 Right Outer join Tblname2 T2 On T1.colomn=T2.colomn where condition Group By Order By 右连接语句将列出所有Tblname2中

16、的所有记录，而Tblname1的记录则只显示符合连接条件的记录。例1：列出所有销售单据，并将每个单据的客户名称列出，Select语句如下：SELECT T2.*, Tame AS Expr1FROM customer T1 RIGHT OUTER JOIN retail T2 ON T1.cid = T2.cid查询结果如下：ridcidcdateremployeerraterpricermoneyExpr11kh01 2006-9-1liuhong 9012001200张经理 2kh02 2006-9-2liuhong 8020001600李经理 3kh01 2006-9-3liuhong

17、8020001600张经理 4kh01 2006-9-3zhangling 8040003200张经理 5kh02 2006-9-4zhangling 9020001800李经理 (5) 全连接如果希望将连接的两个表都包含所有记录，则使用全连接Full Outer Join关键字，他将结合作连接和右连接的结果全部显示出来。例如：查询所有客户数据，销售单据，销售明细，产品信息全部内容都显示出来，使用全连接语句的Select语句如下：SELECT T1.rid, T1.cid, Tame, T2.pcode, T4.pname, T2.dprice, T2.dnumber, T2.dmoneyFR

18、OM retail T1 FULL OUTER JOIN detail T2 ON T1.rid = T2.rid FULL OUTER JOIN customer T3 ON T1.cid = T3.cid FULL OUTER JOIN product T4 ON T2.pcode = T4.pcodeWHERE (T4.pname LIKE %nokia%) AND (Tame LIKE %张%)查询结果如下：ridcidcnamepcodepnamedpricednumberdmoney1kh01 张经理 003 nokia1500115003kh01 张经理 003 nokia150

19、023000(6) 联合UnoinSelect语句结果可以看成一个集合，这样就可以使用集合运算符对集合进行处理。Union运算是集合运算中使用最多的，得到广泛支持。Union运算的基本形式如下：Select select_list from Tblname1 where condition Union AllSelect select_list from Tblname2 where condition如果Union带All关键字,则结果列出全部记录，否则将去掉重复的记录。例1：选定产品代码，产品名称集合与选定客户代码，客户名称集合，将两个集合进行合并，语句如下：SELECT T1.cid,

20、TameFROM customer T1WHERE T1.ctel IS NOT NULLUNIONSELECT T2.pcode, T2.pnameFROM product T2WHERE T2.pbrand IS NOT NULL查询结果如下：cidcname001 motorola002 sony003 nokia004 sunsung005 bird006 changhongkh01 张经理 kh02 李经理 kh03 老刘 Union运算注意的问题：两个集合的列选项目数量相同，且类型相同。应用举例：例1：以销售单据管理为例，按下列要求查询销售单据的相关信息。1建立销售明细与产品信息的

21、连接，按销售产品名称分组，计算销售金额合计，产品销售数量合计。Select语句如下：SELECT T2.pname, SUM(T1.dmoney) AS Expr1, SUM(T1.dnumber) AS Expr2FROM detail T1 INNER JOIN product T2 ON T1.pcode = T2.pcodeGROUP BY T2.pname查询结果如下：pnameExpr1Expr2motorola48004nokia75005sony1380062建立销售单据与销售明细的连接，查询客户代码，购买金额合计，并按客户购买金额合计排序。Select语句如下：SELECT

22、T1.cid, SUM(T1.rmoney) AS Expr1FROM retail T1 INNER JOIN detail T2 ON T1.rid = T2.ridGROUP BY T1.cidORDER BY Expr1 DESC查询结果如下：cidExpr1kh01 8400kh02 32003建立客户信息与销售单据的连接，销售单据与销售明细的连接，查找所有客户购买产品数量的合计。Select语句如下：SELECT T1.cid, SUM(T3.dnumber) AS Expr1FROM customer T1 LEFT OUTER JOIN retail T2 ON T1.cid

23、= T2.cid LEFT OUTER JOIN detail T3 ON T2.rid = T3.ridGROUP BY T1.cid查询结果如下：cidExpr1kh01 11kh02 4kh03 liubang mingren 4.查询客户代码，客户名称集合， 与查询产品代码、产品信息集合合并，语句如下：SELECT cid, cnameFROM customerUNIONSELECT pcode, pnameFROM product查询结果如下：cidcname001 motorola002 sony003 nokia004 sunsung005 bird006 changhongkh

24、01 张经理 kh02 李经理 kh03 老刘 liubang 刘工 mingren 韩先生 知识扩展：(1) 笛卡尔积有时候数据表之间的连接没有设置好时，甚至没有设置条件，结果将包含太多的行记录，这就是由于所谓“笛卡尔积”造成的。从概念上说，连接首先形成笛卡尔积，即形成用于连接的表中所有行的组合，例如：假设有两个表A,B。表A记录如下：阴影部分为数据记录A B Ca b ce f gh i j表B记录如下：阴影部分为数据记录D Ed ef g那么连个表的笛卡尔积为：A B C D Ea b c d ea b c f ge f g d ee f g f gh i j d eh i j f g表

25、A有3条记录，表B有2条记录，那么笛卡尔积有3*2=6条记录，可见笛卡尔积表中的记录个数为两个连接表的记录的所有可能组合。所有连接操作，都是在先形成笛卡尔积记录后，在进行查询筛选计算的。培训练习：(1)以销售单据为例，察看每个销售单据的真实客户姓名，单据号，单据金额，销售代表等信息。(2) 建立客户信息与销售单据的连接，销售单据与销售明细的连接，查找所有客户购买产品数量的合计，购买金额合计。(3) 建立查询销售单据与销售明细连接，销售明细中的产品代码与产品信息中的代码连接，销售单据的客户代码与客户信息的客户代码连接，构成4张表的多表连接。(4) 列出所有客户信息，并把相关客户的销售单据信息列出。(5) 使用全连接语句，查询所有客户数据，销售单据，销售明细，产品信息全部内容都显示出来。(6) 选定产品代码，产品名称含有“moto”的集合与选定客户代码，客户名称含有“张”的集合，将两个集合进行合并。