连接查询

连接查询

连接查询：将多张表（可以大于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。

 

外连接

外连接：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。

 

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

 

 

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

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

自然连接

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

 

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

 

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

 

 

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

 

 

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

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

 

交叉连接

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

 

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

 

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

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

 

【补充】

连接查询心得：

连接查询不限于两张表，连接查询也可以是三张、四张，甚至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. 统计部门考核结果情况；

答：

1.

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;

2.

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

3.

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;