连接查询

迷南。 2022-05-23 00:50 310阅读 0赞

# **连接查询** #

连接查询：将多张表（可以大于2张）进行记录的连接（按照某个指定的条件进行数据拼接）。最终结果：记录数有可能变化，字段数一定会增加（至少两张表的合并）。

连接查询会产生笛卡尔积，假设集合A=\{a,b\}，集合B=\{0,1,2\}，则两个集合的笛卡尔积为\{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)\}。可以扩展到多个集合的情况。那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的、不想要的记录呢？当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

你能想像到emp和dept表连接查询的结果么？emp一共14行记录，dept表一共4行记录，那么连接后查询出的结果是56行记录。但你只是想在查询emp表的同时，把每个员工的所在部门信息显示出来，那么就需要使用主外键来去除无用信息了，即使用主外键关系去除。

连接查询的分类：内连接、外连接、自然连接和交叉连接。

## **内连接** ##

内连接：\[inner\] join，从左表中取出每一条记录，去右表中与所有的记录进行匹配，匹配必须是某个条件在左表中与右表中相同，最终才会保留结果，否则不保留。

**基本语法：**

左表 **\[inner\] join** 右表 **on** 左表.字段 = 右表.字段；

on表示连接条件：条件字段就是代表相同的业务含义（如my\_student.c\_id和my\_class.id），大多数情况下为两张表中的主外键关系。

**什么是内连接？内连接的关键字是什么？**

**答：**仅将两个表中满足连接条件的行组合起来作为结果集，称为内连接。在内连接中，只有在两个表中匹配的行才能在结果集中出现。关键字：**\[inner\] join ... on**。

![70][]

## **外连接** ##

外连接：outer join，以某张表为主，取出里面的所有记录，然后每条与另外一张表进行连接，不管能不能匹配上条件，最终都会保留。能匹配，正确保留；不能匹配，其它表的字段都置空（null）。

外连接分为两种：是以某张表为主，有主表

left join：左外连接（左连接），以左表为主表

right join：右外连接（右连接），以右表为主表

**基本语法：**

左表 **left/right join** 右表 **on** 左表.字段 = 右表.字段；

on表示连接条件：条件字段就是代表相同的业务含义（如my\_student.c\_id和my\_class.id），大多数情况下为两张表中的主外键关系。

**什么是左外连接？左外连接的关键字是什么？**

**答：**在内连接的基础上，还包含左表中所有不符合条件的数据行，并在其中的右表列填写NULL。关键字：left join ... on。

**什么是右外连接？右外连接的关键字是什么？**

**答：**在内连接的基础上，还包含右表中所有不符合条件的数据行，并在其中的左表列填写NUL。关键字：right join ... on。

![70 1][]

左表为主表，最终记录数至少不少于左表已有的记录数。

** ![70 2][]**

右表为主表，最终记录数至少不少于右表已有的记录数。

虽然左连接和右连接有主表差异，但是显示的结果是：左表的数据在左边，右表的数据在右边。左连接和右连接可以互转。

## **自然连接** ##

自然连接：natural join，自然连接，就是自动匹配连接条件，系统以字段名字作为匹配模式（同名字段就作为条件，多个同名字段就都作为条件）。

自然连接：可以分为自然内连接和自然外连接。

自然内连接：左表 natural join 右表;

![70 3][]

自然外连接：左表 natural left/right join 右表;

![70 4][]

其实，内连接和外连接都可以模拟自然连接，使用同名字段，合并字段。

左表 left/right/inner join 右表 using(字段名)；\--使用同名字段作为连接条件，自动合并条件

** ![70 5][]**

## **交叉连接** ##

交叉连接：cross join，从一张表中循环取出每一条记录，每条记录都去另外一张表进行匹配，匹配一定保留（没有条件匹配），而连接本身字段就会增加（保留），最终形成的结果叫做：笛卡尔积。

**基本语法**：左表 cross join 右表； <==>  from 左表，右表；

![70 6][]![70 7][]

笛卡尔积没有意义：应该尽量避免（交叉连接没用）

交叉连接存在的价值：保证连接这种结构的完整性。

** **

**【补充】**

**连接查询心得**：

连接查询不限于两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除。

两张表的连接查询一定有一个主外键关系，三张表的连接查询就一定有两个主外键关系，所以在大家不是很熟悉连接查询时，首先要学会去除无用笛卡尔积，那么就是用主外键关系作为条件来处理。如果两张表的查询，那么至少有一个主外键条件，三张表连接至少有两个主外键条件,N张表连接至少有N-1个主外键条件*。*

1. 交叉连接查询（基本不会使用\-得到的是两个表的乘积）

语法：select \* from A,B;

2. 内连接查询（使用的关键字 inner join  -- inner可以省略）

隐式内连接：select \* from A,B where 条件；

显示内连接：select \* from A inner join B on 条件；

3. 外连接查询(使用的关键字 outer join  --outer可以省略)

左外连接：left outer join

select \* from A left outer join B on 条件；

右外连接：right outer join

select \* from A right outer join B on 条件；

**什么时候使用连接查询？什么时候使用子查询？**

**答：**查询的是两张表及以上的信息，或者查询的是两个字段及以上的信息，这两个字段不在同一张表内。有时候是两个字段，有时候是两张表，总之是两个，而不是一个。

子查询一般查的是一个字段的信息或一张表里面的信息，只是给的条件并不能直接映射到这张表，需要推出来。当没有给出任何条件时,查询的是两张表综合在一起的信息,并且两张表是主外键相关的。

内连接就是前面所讲的连接查询,只是前面的省略了inner关键字而已。

**-- 多表查询方法 （要用distinct关键字去除重复数据 ）（答案有争议）**

**select** distinct i.\*

**from** ajia\_cart\_item c,ajia\_user u, ajia\_item i

**where** u.username='lisi' and c.user\_id=u.id and c.item\_id=i.id;

【解析】

这个题很重要,我研究了一个小时,总结如下:

什么时候用子查询呢?查询的是一个字段,但是给的条件无法直接作用到这个条件上,需要中间推几张表才能作用上这个字段，什么时候用多表查询呢?查询的是两个字段,但是这两个字段不在同一个表中,在两张表或三张表中,并且这几张表间具有主外键关联，拿上面的题来说,lisi用户的购物车商品信息,它最终要查的是商品信息,一个字段,只是它给的条件无法直接用在这上面,需要推,怎么推呢?

我知道用户的username叫lisi,通过ajia\_user表，我可以得到用户编号，知道了用户编号，我通过ajia\_cart\_item这个表可以得到商品编号，知道了商品编号，我就可以通过ajia\_item表得到商品的信息。所以，我选择用子查询的方法。但是老师用的是多表查询的方法，这就出了一个问题，查出来的数据有大量的重复，所以我们要用distinct关键字来去除重复数据，不建议使用，或者直接就不对。

**例子：**

1. **查询所有用户的订单信息包括没有下过订单的用户**

select u.id,u.username,o.\* from ajia\_user u left join ajia\_order o on u.id=o.user\_id;

select u.id,u.username,o.\* from ajia\_user u,ajia\_order o where u.id=o.user\_id;（错误）

\-- 过滤掉了没有下订单的用户

2. **查询所有用户的下订单数量**

\-- 左外连接

**select** u.id,u.username,count(o.order\_id) 订单数量

**from** ajia\_user u left join ajia\_order o on u.id=o.user\_id

**group by** u.id;

\-- 右外连接

**select** u.id,u.username,count(o.order\_id) 订单数量

**from** ajia\_order o right join ajia\_user u

**on** u.id=o.user\_id

**group by** u.id;

3. **查找用户编号为14的商品收藏夹**

**select** u.id,u.username,c.\*

**from** ajia\_user u left join ajia\_collect\_item c

**on** u.id=c.user\_id

**where** u.id=14;

**4. 统计所有用户收藏夹中的商品个数**

**select** u.id,u.username,count(c.id)

**from** ajia\_user u left join ajia\_collect\_item c

**on** u.id=c.user\_id

**group by** u.id;

**面试题：**

部门表（deptcode:部门编号，deptname:部门名称）

员工表（humancode:员工编号，humanname:员工姓名，deptcode:部门编号）

考核表（resultcode:结果编号，humancode:人员编号，kpicode:考核结果---分 不合格，合格，优秀）

1. 统计各部门合格的人数；

2. 统计考核结果情况；

3. 统计部门考核结果情况；

答：

select count(\*),C.deptname  
from 考核表 A  
inner join 员工表 B on A.humancode=B.humancode  
inner join 部门表 C on B.deptcode=C.deptcode  
where A.kpicode<>'不合格'  
group by ,C.deptname;

select A.\*,B.\*,C.\*  
from 考核表 A  
inner join 员工表 B on A.humancode=B.humancode  
inner join 部门表 C on B.deptcode=C.deptcode;

select count(\*),A.kpicode,C.deptname  
from 考核表 A  
inner join 员工表 B on A.humancode=B.humancode  
inner join 部门表 C on B.deptcode=C.deptcode  
group by ,C.deptname,A.kpicode;

[70]: /images/20220523/0267b4287477455caffdf84e65bbac8d.png
[70 1]: /images/20220523/d9f8d848fa3d4fc39a06e61bbec4b471.png
[70 2]: /images/20220523/02c284afa68e47e1b32be1d219c33b9d.png
[70 3]: /images/20220523/f470d13b023d4a5ab8bd6b860d0ec1cd.png
[70 4]: /images/20220523/951d52a3d03642d49a2d382034de22e5.png
[70 5]: /images/20220523/af431c1da24944e58c9e587c32025d7a.png
[70 6]: https://img-blog.csdn.net/2018053010070465?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6MTE3MDA2MzkxMQ==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70
[70 7]: /images/20220523/1be85e0f3ee04da48128ed242708092f.png