MySQL中的连接查询

一、作用

  连接查询又称多表查询，当查询的字段来自于多个表时，就会用到连接查询。笛卡尔乘积现象：表 1 有 m 行，表 2 有 n 行，结果为 m*n 行。如何解决：添加有效的连接条件。

二、分类

1、按 SQL 标准分

  sql92 标准：仅仅支持内连接。
  sql99 标准（推荐）：支持内连接 + 外连接（左外 + 右外）+ 交叉连接。

2、按功能分类

1）内连接：

  等值连接、非等值连接、自连接。

2）外连接：

  左外连接、右外连接、全外连接。

3）交叉连接

三、案例

1、数据

1）student 表

  表结构

名	类型	长度	键
id	int	11	主键
name	varchar	255	无
age	int	11	无
class	varchar	255	无
deskmate_id	int	11	无

  数据表

id	name	age	class	deskmate_id
1	小张	10	一班	4
2	小王	12	二班	3
3	小姣	18	二班	2
4	小兴	15	一班	1

2）curriculum 表

  表结构

名	类型	长度	键
id	int	11	主键
student_id	int	11	无
curriculum_name	varchar	255	无

  数据表

id	name	student_id
1	1	C
2	2	C
3	5	C#
4	3	Python
5	1	Java

2、内连接

  语法

select 字段列表 from 表1  
inner join 表2  on 表1.字段 = 表2.字段;

1）等值连接

  查找小张的班级和所有课程

select student.name,student.class,curriculum.curriculum_name
from student
inner join curriculum  on student.id = curriculum.student_id
where student.name='小张';

  运行结果：

name	class	curriculum_name
小张	一班	C
小张	一班	Java

2）非等值连接

  这条语句的意思是，当 curriculum.id < student.id 数据进行连接。但是在我这个数据例子下，这样连接是毫无意义的，开发中根据业务情况进行应用。

select a.name,a.class,b.curriculum_name
from student a
inner join curriculum b on b.id < a.id;

  运行结果：

name	class	curriculum_name
小王	二班	C
小姣	二班	C
小姣	二班	C
小兴	一班	C
小兴	一班	C
小兴	一班	C#

  假如现在有俩个 A 表和 B 表，需要得到指定用户购买的商品打了几折。

A 表

开始个数	结束个数	折扣
1	10	0.8
11	20	0.6

B 表

名字	购买个数
小张	25
小王	13

select 名字,购买个数,折扣    
from 表B  
inner join 表A on 表B.购买个数 between 表A.开始个数 and 表A.结束个数;

3）自连接

  获得所有人的的名字和同桌的名字。

select a.name,b.name '同桌'
from student a 
inner join student b on a.id = b.deskmate_id;

  运行结果：

name	同桌
小兴	小张
小姣	小王
小王	小姣
小张	小兴

4）隐式内连接

  语法：

select *
from 表1,表2;

  上面展示的都是显式内连接，该语句结果和上面1）等值连接的结果一样。但是隐式内连接是将查询的结果去做 where 条件过滤，而显式内连接是带着 on 后面的条件去查询结果，效率高。

select student.name,student.class,curriculum.curriculum_name
from student ,curriculum 
where student.id = curriculum.student_id and student.name='小张';

3、外连接

1）左外连接

select a.name,a.class,b.curriculum_name
from student a
left join curriculum b on a.id = b.student_id;

  由运行结果可以看出左连接是以关键字"left join"左边的表为主表，右边的为副表，主表的数据必须存在，没有的填 null。而副表的数据只有和主表的数据有关系时才存在。
  运行结果：

name	class	curriculum_name
小张	一班	C
小王	二班	C
小姣	二班	Python
小张	一班	Java
小兴	一班	null

2）右外连接

select a.name,a.class,b.curriculum_name
from student a
right join curriculum b on a.id = b.student_id;

  由运行结果可以看出右连接正好和左连接相对。
  运行结果：

name	class	curriculum_name
小张	一班	C
小王	二班	C
null	null	C#
小姣	二班	Python
小张	一班	Java

3）全外连接

  全外链接就是左连接和右连接的结合，目前 MySQL 不支持全外连接。

3、交叉连接

  交叉连接的结果就是左右表的笛卡尔乘积，数据量太大就不展示运行结果了。

select a.name,a.class,b.curriculum_name
from student a
cross join curriculum b 

4、总结

1）内连接：inner

  ①等值连接：在连接条件中使用符号 =。
  ②非等值连接：在连接条件中使用除 = 外其他符号，如 >,<,between...and...等。
  ③自连接：表自身的连接查询，这种情况下，需要要对表起别名。

2）外连接：outer

  ①左外连接 ：以左边的表作为主表。left
  ②右外连接：以右边的表作为主表。right
  ③全外连接：MySQL 不支持。full

3）交叉连接：cross

  左右表的笛卡尔乘积。

4）图解

  由于 MySQL 不支持全连接，所以必须使用联合查询来实现。

select *  
from A left join B on A.key=B.key  
union  
select *  
from A right join B on A.key=B.key;  

select *  
from A left join B on A.key=B.key  
where B.key is null  
union  
select *  
from A left join B on A.key=B.key  
where A.key is null