mysql 左连接 三个表 mysql四个数据表左连接

1. 绪言

• 不管是面试还是实际工作中，总是会听到你对两个表做连接查询试试呢，你知道数据库有哪些连接吗，诸如此类的对话
• 数据库中，表与表之间大部分都是存在关系的，比如经典的学生表和班级表、班级表与学校表等。
• 这些表之间可以通过关联属性连接（join）在一起，提供了数据库多表查询的能力。
• 数据库中的连接主要分为两大类：内连接和外连接，其中外连接包括左外连接、右外连接、全外连接。
• 四大连接简称：内连接、左连接、右连接、全连接，它们的定义如下：

内连接（innner join，或称等值连接）：返回两张表中匹配的记录
左连接（left join）：返回两张表匹配的记录，以及左表中多余的记录
右连接（right join）：返回两张表匹配的记录，以及右表中多余的记录
全连接（full join）：返回两张表匹配的记录，以及左右两表中各自多余的记录

2. 图解四大连接

2.1 基础准备

• 为了更好的解释四大连接，需要在MySQL数据库中的创建两张表。
• 建表语句如下：

create table IF NOT EXISTS stu (
id bigint unsigned AUTO_INCREMENT comment '学号',
name varchar(50) not null comment '姓名',
age int unsigned not null comment '年龄',
class_id bigint unsigned  not null comment '班级号',
PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 comment '学生表';


create table IF NOT EXISTS class(
id bigint unsigned comment '班级号',
master varchar(50) not null comment '班主任',
number int unsigned not null comment '学生人数',
type varchar(20) not null comment '班级类型：文科、理科',
PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 comment '班级表';

• 使用insert into插入数据后，两张表中的数据如下：

2.2 内连接

• 内连接，又叫等值连接，顾名思义只有两张表中匹配的数据才会发生连接
• Venn图表示如下：
• 对于学生和班级表，通过班级编号进行关联，两表中匹配的记录应该是：

个人心得

1. 遍历左表数据，用左表数据的联属性去匹配右表的每一条数据。
2. 如果关联属性相同，则匹配到一条数据。

• 内连接的MySQL语句：

select * from stu inner join class on stu.class_id=class.id;
select * from stu join class on stu.class_id=class.id;  -- MySQL的join默认为inner jion
/* 对应的where语句 */
select * from stu,class where stu.class_id=class.id;

2.3 左连接

• Venn图表示如下：
• 对学生表和班级表进行左连接，结果如下：

个人心得

1. 遍历左表数据，用左表数据的关联属性去匹配右表中的每一条数据。
2. 如果关联属性相同，则匹配到一条数据；
3. 如果左表数据没有匹配到右表的任何一条数据，则将右表数据的所有字段置为NULL，仍记作匹配到一条数据。

• 左连接对应的SQL语句如下：

select * from stu left join class on stu.class_id=class.id;

2.4 右连接

• Venn图表示如下：
• 对学生和班级表进行右连接，结果如下：

个人心得

1. 遍历右表数据，用右表数据的关联属性去匹配左表中的每一条数据。
2. 如果关联属性相同，则匹配到一条数据；
3. 如果右表数据没有匹配到左表的任何一条数据，则将左表数据的所有字段置为NULL，仍记作匹配到一条数据。

• 右连接的SQL语句：

select * from stu right join class on stu.class_id=class.id;

2.5 全连接

• Venn图表示如下：
• 对学生和班级表进行全连接，结果如下：

个人心得

1. 先对左右表做左连接，再对左右表做右连接
2. 两次连接的结果，去重后就是全连接的结果

• MySQL目前不支持全连接，但可以对左连接、右连接的结果进行union操作实现。

select * from stu left join class on stu.class_id=class.id
union
select * from stu right join class on stu.class_id=class.id;

---

---

3. 笛卡尔乘积

• 笛卡尔乘积其实是数学领域的概念，就是对两个集合做乘法
• 数据库中也引入笛卡尔乘积的概念，笛卡尔乘积对两表关联时，不要求指定关联属性。
• 如果左表有m行，右表有n行，则两表的笛卡尔乘积将会有$m*n$行
• 对学生和班级表做笛卡尔乘积的结果如下：

个人心得： 遍历左表的每一行数据，用左表每一行数据分别于与右表的每一行数据做关联

• 笛卡尔乘积的SQL实现：

select * from stu cross join class;
 select * from stu,class;

• 可以看出，笛卡尔乘积的运算量超级大，一般不会使用笛卡尔乘积做表的关联查询