SQL 数据库多表连接详细讲解

SQL多表连接

应用背景

数据库是由多张表组成的存储结构，并通过多张表之间的关系建立起完整的有效的数据存储形式，形成关系型数据库。作为数据查询语言SQL，提供了功能强大的数据表连接查询功能，使多张表格之间形成有效的数据联系，使得关系数据库在大型数据库应用中占据了主角地位。

一个普通的大型数据库应用程序所使用的数据库中，有多达几百张表的数据，那么如何将这些表高效的有机的联系起来，就成为设计关系数据库的一个重要指标。优良的数据库设计指标包括：

1．减少数据冗余，去除掉多余的数据冗余，可以通过建立表之间的连接关系完成。

2．数据更新正确，不能因为表之间存在关系后，使得更新记录出现不正常的数据。

3．添加数据正常，添加数据过程中，应该保持数据表之间的关系，确定表之间的连接。

4．查询简便灵活，在建立数据连接的查询过程中，连接清晰简便，操作灵活准确。

数据库设计是应用软件成功与否的一项重要标志。设计数据库，除与系统分析结果，设计员的水平等有关外，还可以参考一些规范的设计范式，下面简单介绍数据库的2个基本设计范式：

1．第一范式：要求表的每列都是不可再分的简单数据项，所以1对N 关系就必须用多表表示，而不能用一张表表示。

2．第二范式：表中的每一个非主键列必须完全函数依赖于主键，就是说表中除主键之外的其他列，都必须通过主键能够唯一确定。

数据库的设计非常复杂，没有一成不变的东西，需要就地取材，解决问题，简单化问题。

知识要点

(1) 传统连接

连接就是将多个表中的数据连接到一起的查询，即连接操作可以在一个Select语句中完成从多个表中查找和处理数据，使用连接时可以使用名字相同的不同表的列，也可以不同，但要求连接的列不需可连接，即数据类型相同。

传统的连接语法如下：

Select * from Tblname1 T1,Tblname2 T2 where T1.column=T2.column 连接SQL语句的明显标志为在From子句后边，有多个表Tblname1,

Tblname2 ,Where子句后有表连接键T1.column=T2.column。

例1：以销售单据为例，察看每个销售单据的真实客户姓名，单据号，单据金额，销售代表等信息。Select 语句如下：

SELECT T1.rid, T1.cid, https://www.360docs.net/doc/534045407.html,ame, T1.rmoney, T1.remployee

FROM retail T1 , customer T2 where T1.cid = T2.cid

查询结果如下：

品的零售单价，实际销售单价，销售金额等等信息，Select语句如下：SELECT T1.rid, T1.pcode, T2.pname, T2.ptype, T2.pprice, T1.dprice,T1.dmoney

FROM Detail T1 ,product T2 where T1.pcode = T2.pcode

查询结果如下：

产品代码与产品信息中的代码连接，销售单据的客户代码与客户信息的客户代码连接，构成4张表的多表连接，Select 语句如下：

select T1.rid,T1.cid,https://www.360docs.net/doc/534045407.html,ame,T2.pcode,T4.pname,T2.dprice, T2.dnumber,T2.dmoney

from retail T1,detail T2,customer T3,product T4

where T1.rid=T2.rid and T1.cid=T3.cid and T2.pcode=T4.pcode

(2) 内连接Inner Join

前面的多表连接写法，把表连接条件写在Where子句之后，导致表连接之间与记录筛选条件混合，使查询语句不清晰，故新的表连接条件采用关键字Join表示，连接又分为内连接，左连接，右连接，全连接。

标准内连接写法为：

Select [Distinct] select_list from Tblname1 [T1] Inner join Tblname2 T2 On T1.colomn=T2.colomn [where condition] [Group By] [Order By ]

关键字Inner Join 前后为需要连接的表名，关键字On 后边是连接条件，在执行完连接条件后，才进行记录的筛选语句Where 子句，这样更符合结构化的查询语法。

例1：以销售单据为例，察看每个销售单据的真实客户姓名，单据号，单据金额，销售代表等信息，且要求客户姓名中包含“李”，那么修改后的Select 语句如下：

SELECT T1.rid, T1.cid, https://www.360docs.net/doc/534045407.html,ame, T1.rmoney, T1.remployee

FROM retail T1 INNER JOIN

customer T2 ON T1.cid = T2.cid

WHERE (https://www.360docs.net/doc/534045407.html,ame LIKE '%李%')

ORDER BY T1.rid

查询结果如下：

例2：多表连接，建立查询销售单据与销售明细连接，销售明细中的产品代码与产品信息中的代码连接，销售单据的客户代码与客户信息的客户代码连接，构成4张表的多表连接，要求客户名称包含“张”，产品名称包含“Nokia”，Select 语句如下：

SELECT T1.rid, T1.cid, https://www.360docs.net/doc/534045407.html,ame, T2.pcode, T4.pname, T2.dprice, T2.dnumber,

T2.dmoney

FROM 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.pcode

WHERE (T4.pname LIKE '%nokia%') AND (https://www.360docs.net/doc/534045407.html,ame LIKE '%张%')

(3) 左连接

内连接一般把所有符合条件的记录都显示出来，而不符合连接条件的记录过滤掉。但有时候可能期望显示某个表中的所有记录，包括不符合条件的记录，那么就需要使用外连接。使用外连接可以方便地将连接结果中包含某个表中的所有记录。外连接，包括左连接，右连接，全连接。

左连接Left Outer Join 关键字左侧的表包含所有记录，右侧的表则只包含部分符合连接条件的记录。语法如下：

Select [Distinct] select_list from Tblname1 [T1] Left Outer join Tblname2 T2 On T1.colomn=T2.colomn [where condition] [Group By] [Order By ]

左连接语句将列出所有Tblname1中的所有记录，而Tblname2的记录则只显示符合连接条件的记录。

例1：列出所有客户信息，并把相关客户的销售单据信息列出。Select 语句如下：

SELECT T1.cid, https://www.360docs.net/doc/534045407.html,ame, T2.cdate AS Expr1, T2.rmoney AS Expr2 FROM customer T1 LEFT OUTER JOIN

retail T2 ON T1.cid = T2.cid

金额合计列出，Select语句如下：

SELECT T1.pcode, SUM(T2.dnumber) AS Expr1, SUM(T2.dmoney) AS Expr2

FROM product T1 LEFT OUTER JOIN

detail T2 ON T1.pcode = T2.pcode

GROUP BY T1.pcode

上述Select语句没有列出产品名称，是因为在分组列表中没有列出产品名称，查询结果如下：

(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中的所有记录，而Tblname1的记录则只显示符合连接条件的记录。

例1：列出所有销售单据，并将每个单据的客户名称列出，Select语句如下：

SELECT T2.*, https://www.360docs.net/doc/534045407.html,ame AS Expr1

FROM customer T1 RIGHT OUTER JOIN

retail T2 ON T1.cid = T2.cid

查询结果如下：

(5) 全连接

如果希望将连接的两个表都包含所有记录，则使用全连接Full Outer Join关键字，他将结合作连接和右连接的结果全部显示出来。

例如：查询所有客户数据，销售单据，销售明细，产品信息全部内容都显示出来，使用全连接语句的Select语句如下：

SELECT T1.rid, T1.cid, https://www.360docs.net/doc/534045407.html,ame, T2.pcode, T4.pname, T2.dprice, T2.dnumber,

T2.dmoney

FROM 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.pcode

WHERE (T4.pname LIKE '%nokia%') AND (https://www.360docs.net/doc/534045407.html,ame LIKE '%张%')

查询结果如下：

(6) 联合Unoin

Select语句结果可以看成一个集合，这样就可以使用集合运算符对集合进行处理。Union运算是集合运算中使用最多的，得到广泛支持。Union 运算的基本形式如下：

Select select_list from Tblname1 [where condition]

Union [All]

Select select_list from Tblname2 [where condition]

如果Union带All关键字,则结果列出全部记录，否则将去掉重复的记录。

例1：选定产品代码，产品名称集合与选定客户代码，客户名称集合，将两个集合进行合并，语句如下：

SELECT T1.cid, https://www.360docs.net/doc/534045407.html,ame

FROM customer T1

WHERE T1.ctel IS NOT NULL

UNION

SELECT T2.pcode, T2.pname

FROM product T2

WHERE T2.pbrand IS NOT NULL

Union运算注意的问题：两个集合的列选项目数量相同，且类型相同。应用举例：

例1：以销售单据管理为例，按下列要求查询销售单据的相关信息。

1．建立销售明细与产品信息的连接，按销售产品名称分组，计算销售金额合计，产品销售数量合计。Select语句如下：

SELECT T2.pname, SUM(T1.dmoney) AS Expr1, SUM(T1.dnumber) AS Expr2

FROM detail T1 INNER JOIN

product T2 ON T1.pcode = T2.pcode

GROUP BY T2.pname

查询结果如下：

并按客户购买金额合计排序。Select语句如下：

SELECT T1.cid, SUM(T1.rmoney) AS Expr1

FROM retail T1 INNER JOIN

detail T2 ON T1.rid = T2.rid

GROUP BY T1.cid

ORDER BY Expr1 DESC

3．建立客户信息与销售单据的连接，销售单据与销售明细的连接，查找所有客户购买产品数量的合计。Select语句如下：

SELECT T1.cid, SUM(T3.dnumber) AS Expr1

FROM customer T1 LEFT OUTER JOIN

retail T2 ON T1.cid = T2.cid LEFT OUTER JOIN

detail T3 ON T2.rid = T3.rid

GROUP BY T1.cid

4.查询客户代码，客户名称集合，与查询产品代码、产品信息集合合并，语句如下：

SELECT cid, cname

FROM customer

UNION

SELECT pcode, pname

FROM product

查询结果如下：

知识扩展：

(1)笛卡尔积

有时候数据表之间的连接没有设臵好时，甚至没有设臵条件，结果将包含太多的行记录，这就是由于所谓“笛卡尔积”造成的。从概念上说，连接首先形成笛卡尔积，即形成用于连接的表中所有行的组合，例如：假设有两个表A,B。

表A记录如下：阴影部分为数据记录

A B C

a b c

e f g

h i j

表B记录如下：阴影部分为数据记录

D E

d e

f g

那么连个表的笛卡尔积为：

A B C D E

a b c d e

a b c f g

e f g d e

e f g f g

h i j d e

h i j f g

表A有3条记录，表B有2条记录，那么笛卡尔积有3*2=6条记录，可见笛卡尔积表中的记录个数为两个连接表的记录的所有可能组合。所有连接操作，都是在先形成笛卡尔积记录后，在进行查询筛选计算的。

培训练习：

(1)以销售单据为例，察看每个销售单据的真实客户姓名，单据号，单据金额，销售代表等信息。

(2) 建立客户信息与销售单据的连接，销售单据与销售明细的连接，查找所有客户购买产品数量的合计，购买金额合计。

(3) 建立查询销售单据与销售明细连接，销售明细中的产品代码与产品信息

中的代码连接，销售单据的客户代码与客户信息的客户代码连接，构成4张表的多表连接。

(4) 列出所有客户信息，并把相关客户的销售单据信息列出。

(5) 使用全连接语句，查询所有客户数据，销售单据，销售明细，产品信息全部内容都显示出来。

(6) 选定产品代码，产品名称含有“moto”的集合与选定客户代码，客户名称含有“张”的集合，将两个集合进行合并。