6多表连接.docx
《6多表连接.docx》由会员分享，可在线阅读，更多相关《6多表连接.docx（14页珍藏版）》请在冰豆网上搜索。
6多表连接
第六节多表连接
应用背景
数据库是由多张表组成的存储结构，并通过多张表之间的关系建立起完整的有效的数据存储形式，形成关系型数据库。
作为数据查询语言SQL，提供了功能强大的数据表连接查询功能，使多张表格之间形成有效的数据联系，使得关系数据库在大型数据库应用中占据了主角地位。
一个普通的大型数据库应用程序所使用的数据库中，有多达几百张表的数据，那么如何将这些表高效的有机的联系起来，就成为设计关系数据库的一个重要指标。
优良的数据库设计指标包括：
1．减少数据冗余，去除掉多余的数据冗余，可以通过建立表之间的连接关系完成。
2．数据更新正确，不能因为表之间存在关系后，使得更新记录出现不正常的数据。
3．添加数据正常，添加数据过程中，应该保持数据表之间的关系，确定表之间的连接。
4．查询简便灵活，在建立数据连接的查询过程中，连接清晰简便，操作灵活准确。
数据库设计是应用软件成功与否的一项重要标志。
设计数据库，除与系统分析结果，设计员的水平等有关外，还可以参考一些规范的设计范式，下面简单介绍数据库的2个基本设计范式：
1．第一范式：
要求表的每列都是不可再分的简单数据项，所以1对N关系就必须用多表表示，而不能用一张表表示。
2．第二范式：
表中的每一个非主键列必须完全函数依赖于主键，就是说表中除主键之外的其他列，都必须通过主键能够唯一确定。
数据库的设计非常复杂，没有一成不变的东西，需要就地取材，解决问题，简单化问题。
知识要点
（1）传统连接
连接就是将多个表中的数据连接到一起的查询，即连接操作可以在一个Select语句中完成从多个表中查找和处理数据，使用连接时可以使用名字相同的不同表的列，也可以不同，但要求连接的列不需可连接，即数据类型相同。
传统的连接语法如下：
Select*fromTblname1T1,Tblname2T2whereT1.column=T2.column
连接SQL语句的明显标志为在From子句后边，有多个表Tblname1,Tblname2,Where子句后有表连接键T1.column=T2.column。
例1：
以销售单据为例，察看每个销售单据的真实客户姓名，单据号，单据金额，销售代表等信息。
Select语句如下：
SELECTT1.rid,T1.cid,Tame,T1.rmoney,T1.remployee
FROMretailT1,customerT2whereT1.cid=T2.cid
查询结果如下：
rid
cid
cname
rmoney
remployee
1
kh01
张经理
1200
liuhong
2
kh02
李经理
1600
liuhong
3
kh01
张经理
1600
liuhong
4
kh01
张经理
3200
zhangling
5
kh02
李经理
1800
zhangling
例2：
以销售明细为例，查询每个销售明细记录的真实产品名称，产品的零售单价，实际销售单价，销售金额等等信息，Select语句如下：
SELECTT1.rid,T1.pcode,T2.pname,T2.ptype,T2.pprice,T1.dprice,T1.dmoney
FROMDetailT1,productT2whereT1.pcode=T2.pcode
查询结果如下：
rid
pcode
pname
ptype
pprice
dprice
dmoney
1
001
motorola
V30
2800
1200
2400
1
002
sony
C30
3200
2400
4800
1
003
nokia
sony6110
4500
1500
1500
2
002
sony
C30
3200
2200
4400
2
003
nokia
sony6110
4500
1500
3000
3
001
motorola
V30
2800
1200
2400
3
002
sony
C30
3200
2300
4600
3
003
nokia
sony6110
4500
1500
3000
例3：
多表连接，建立查询销售单据与销售明细连接，销售明细中的产品代码与产品信息中的代码连接，销售单据的客户代码与客户信息的客户代码连接，构成4张表的多表连接，Select语句如下：
selectT1.rid,T1.cid,Tame,T2.pcode,T4.pname,T2.dprice,T2.dnumber,T2.dmoney
fromretailT1,detailT2,customerT3,productT4
whereT1.rid=T2.ridandT1.cid=T3.cidandT2.pcode=T4.pcode
查询结果如下：
rid
cid
cname
pcode
pname
dprice
dnumber
dmoney
1
kh01
张经理
001
motorola
1200
2
2400
1
kh01
张经理
002
sony
2400
2
4800
1
kh01
张经理
003
nokia
1500
1
1500
2
kh02
李经理
002
sony
2200
2
4400
2
kh02
李经理
003
nokia
1500
2
3000
3
kh01
张经理
001
motorola
1200
2
2400
3
kh01
张经理
002
sony
2300
2
4600
3
kh01
张经理
003
nokia
1500
2
3000
（2）内连接InnerJoin
前面的多表连接写法，把表连接条件写在Where子句之后，导致表连接之间与记录筛选条件混合，使查询语句不清晰，故新的表连接条件采用关键字Join表示，连接又分为内连接，左连接，右连接，全连接。
标准内连接写法为：
Select[Distinct]select_listfromTblname1[T1]InnerjoinTblname2T2OnT1.colomn=T2.colomn[wherecondition][GroupBy][OrderBy]
关键字InnerJoin前后为需要连接的表名，关键字On后边是连接条件，在执行完连接条件后，才进行记录的筛选语句Where子句，这样更符合结构化的查询语法。
例1：
以销售单据为例，察看每个销售单据的真实客户姓名，单据号，单据金额，销售代表等信息，且要求客户姓名中包含“李”，那么修改后的Select语句如下：
SELECTT1.rid,T1.cid,Tame,T1.rmoney,T1.remployee
FROMretailT1INNERJOIN
customerT2ONT1.cid=T2.cid
WHERE（TameLIKE'%李%'）
ORDERBYT1.rid
查询结果如下：
rid
cid
cname
rmoney
remployee
2
kh02
李经理
1600
liuhong
5
kh02
李经理
1800
zhangling
例2：
多表连接，建立查询销售单据与销售明细连接，销售明细中的产品代码与产品信息中的代码连接，销售单据的客户代码与客户信息的客户代码连接，构成4张表的多表连接，要求客户名称包含“张”，产品名称包含“Nokia”，Select语句如下：
SELECTT1.rid,T1.cid,Tame,T2.pcode,T4.pname,T2.dprice,T2.dnumber,
T2.dmoney
FROMretailT1INNERJOIN
detailT2ONT1.rid=T2.ridINNERJOIN
customerT3ONT1.cid=T3.cidINNERJOIN
productT4ONT2.pcode=T4.pcode
WHERE（T4.pnameLIKE'%nokia%'）AND（TameLIKE'%张%'）
查询结果如下：
rid
cid
cname
pcode
pname
dprice
dnumber
dmoney
1
kh01
张经理
003
nokia
1500
1
1500
3
kh01
张经理
003
nokia
1500
2
3000
（3）左连接
内连接一般把所有符合条件的记录都显示出来，而不符合连接条件的记录过滤掉。
但有时候可能期望显示某个表中的所有记录，包括不符合条件的记录，那么就需要使用外连接。
使用外连接可以方便地将连接结果中包含某个表中的所有记录。
外连接，包括左连接，右连接，全连接。
左连接LeftOuterJoin关键字左侧的表包含所有记录，右侧的表则只包含部分符合连接条件的记录。
语法如下：
Select[Distinct]select_listfromTblname1[T1]LeftOuterjoinTblname2T2OnT1.colomn=T2.colomn[wherecondition][GroupBy][OrderBy]
左连接语句将列出所有Tblname1中的所有记录，而Tblname2的记录则只显示符合连接条件的记录。
例1：
列出所有客户信息，并把相关客户的销售单据信息列出。
Select语句如下：
SELECTT1.cid,Tame,T2.cdateASExpr1,T2.rmoneyASExpr2
FROMcustomerT1LEFTOUTERJOIN
retailT2ONT1.cid=T2.cid
查询结果如下：
cid
cname
Expr1
Expr2
kh01
张经理
2006-9-1
1200
kh01
张经理
2006-9-3
1600
kh01
张经理
2006-9-3
3200
kh02
李经理
2006-9-2
1600
kh02
李经理
2006-9-4
1800
kh03
老刘
liubang
刘工
mingren
韩先生
例2：
列出所有产品信息，并将相关产品信息的销售数量合计，销售金额合计列出，Select语句如下：
SELECTT1.pcode,SUM（T2.dnumber）ASExpr1,SUM（T2.dmoney）ASExpr2
FROMproductT1LEFTOUTERJOIN
detailT2ONT1.pcode=T2.pcode
GROUPBYT1.pcode
上述Select语句没有列出产品名称，是因为在分组列表中没有列出产品名称，查询结果如下：
pcode
Expr1
Expr2
001
4
4800
002
6
13800
003
5
7500
004
005
006
（4）右连接
右连接与左连接正好相反，右连接RightOuterJoin关键字右侧的表包含所有记录，左侧的表则只包含部分符合连接条件的记录。
语法如下：
Select[Distinct]select_listfromTblname1[T1]RightOuterjoinTblname2T2OnT1.colomn=T2.colomn[wherecondition][GroupBy][OrderBy]
右连接语句将列出所有Tblname2中的所有记录，而Tblname1的记录则只显示符合连接条件的记录。
例1：
列出所有销售单据，并将每个单据的客户名称列出，Select语句如下：
SELECTT2.*,TameASExpr1
FROMcustomerT1RIGHTOUTERJOIN
retailT2ONT1.cid=T2.cid
查询结果如下：
rid
cid
cdate
remployee
rrate
rprice
rmoney
Expr1
1
kh01
2006-9-1
liuhong
90
1200
1200
张经理
2
kh02
2006-9-2
liuhong
80
2000
1600
李经理
3
kh01
2006-9-3
liuhong
80
2000
1600
张经理
4
kh01
2006-9-3
zhangling
80
4000
3200
张经理
5
kh02
2006-9-4
zhangling
90
2000
1800
李经理
（5）全连接
如果希望将连接的两个表都包含所有记录，则使用全连接FullOuterJoin关键字，他将结合作连接和右连接的结果全部显示出来。
例如：
查询所有客户数据，销售单据，销售明细，产品信息全部内容都显示出来，使用全连接语句的Select语句如下：
SELECTT1.rid,T1.cid,Tame,T2.pcode,T4.pname,T2.dprice,T2.dnumber,
T2.dmoney
FROMretailT1FULLOUTERJOIN
detailT2ONT1.rid=T2.ridFULLOUTERJOIN
customerT3ONT1.cid=T3.cidFULLOUTERJOIN
productT4ONT2.pcode=T4.pcode
WHERE（T4.pnameLIKE'%nokia%'）AND（TameLIKE'%张%'）
查询结果如下：
rid
cid
cname
pcode
pname
dprice
dnumber
dmoney
1
kh01
张经理
003
nokia
1500
1
1500
3
kh01
张经理
003
nokia
1500
2
3000
（6）联合Unoin
Select语句结果可以看成一个集合，这样就可以使用集合运算符对集合进行处理。
Union运算是集合运算中使用最多的，得到广泛支持。
Union运算的基本形式如下：
Selectselect_listfromTblname1[wherecondition]
Union[All]
Selectselect_listfromTblname2[wherecondition]
如果Union带All关键字,则结果列出全部记录，否则将去掉重复的记录。
例1：
选定产品代码，产品名称集合与选定客户代码，客户名称集合，将两个集合进行合并，语句如下：
SELECTT1.cid,Tame
FROMcustomerT1
WHERET1.ctelISNOTNULL
UNION
SELECTT2.pcode,T2.pname
FROMproductT2
WHERET2.pbrandISNOTNULL
查询结果如下：
cid
cname
001
motorola
002
sony
003
nokia
004
sunsung
005
bird
006
changhong
kh01
张经理
kh02
李经理
kh03
老刘
Union运算注意的问题：
两个集合的列选项目数量相同，且类型相同。
应用举例：
例1：
以销售单据管理为例，按下列要求查询销售单据的相关信息。
1．建立销售明细与产品信息的连接，按销售产品名称分组，计算销售金额合计，产品销售数量合计。
Select语句如下：
SELECTT2.pname,SUM（T1.dmoney）ASExpr1,SUM（T1.dnumber）ASExpr2
FROMdetailT1INNERJOIN
productT2ONT1.pcode=T2.pcode
GROUPBYT2.pname
查询结果如下：
pname
Expr1
Expr2
motorola
4800
4
nokia
7500
5
sony
13800
6
2．建立销售单据与销售明细的连接，查询客户代码，购买金额合计，并按客户购买金额合计排序。
Select语句如下：
SELECTT1.cid,SUM（T1.rmoney）ASExpr1
FROMretailT1INNERJOIN
detailT2ONT1.rid=T2.rid
GROUPBYT1.cid
ORDERBYExpr1DESC
查询结果如下：
cid
Expr1
kh01
8400
kh02
3200
3．建立客户信息与销售单据的连接，销售单据与销售明细的连接，查找所有客户购买产品数量的合计。
Select语句如下：
SELECTT1.cid,SUM（T3.dnumber）ASExpr1
FROMcustomerT1LEFTOUTERJOIN
retailT2ONT1.cid=T2.cidLEFTOUTERJOIN
detailT3ONT2.rid=T3.rid
GROUPBYT1.cid
查询结果如下：
cid
Expr1
kh01
11
kh02
4
kh03
liubang
mingren
4.查询客户代码，客户名称集合，与查询产品代码、产品信息集合合并，语句如下：
SELECTcid,cname
FROMcustomer
UNION
SELECTpcode,pname
FROMproduct
查询结果如下：
cid
cname
001
motorola
002
sony
003
nokia
004
sunsung
005
bird
006
changhong
kh01
张经理
kh02
李经理
kh03
老刘
liubang
刘工
mingren
韩先生
知识扩展：
（1）笛卡尔积
有时候数据表之间的连接没有设置好时，甚至没有设置条件，结果将包含太多的行记录，这就是由于所谓“笛卡尔积”造成的。
从概念上说，连接首先形成笛卡尔积，即形成用于连接的表中所有行的组合，例如：
假设有两个表A,B。
表A记录如下：
阴影部分为数据记录
ABC
abc
efg
hij
表B记录如下：
阴影部分为数据记录
DE
de
fg
那么连个表的笛卡尔积为：
ABCDE
abcde
abcfg
efgde
efgfg
hijde
hijfg
表A有3条记录，表B有2条记录，那么笛卡尔积有3*2=6条记录，可见笛卡尔积表中的记录个数为两个连接表的记录的所有可能组合。
所有连接操作，都是在先形成笛卡尔积记录后，在进行查询筛选计算的。
培训练习：
（1）以销售单据为例，察看每个销售单据的真实客户姓名，单据号，单据金额，销售代表等信息。
（2）建立客户信息与销售单据的连接，销售单据与销售明细的连接，查找所有客户购买产品数量的合计，购买金额合计。
（3）建立查询销售单据与销售明细连接，销售明细中的产品代码与产品信息中的代码连接，销售单据的客户代码与客户信息的客户代码连接，构成4张表的多表连接。
（4）列出所有客户信息，并把相关客户的销售单据信息列出。
（5）使用全连接语句，查询所有客户数据，销售单据，销售明细，产品信息全部内容都显示出来。
（6）选定产品代码，产品名称含有“moto”的集合与选定客户代码，客户名称含有“张”的集合，将两个集合进行合并。
升级会员 