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图表示如下:
- 对于学生和班级表,通过班级编号进行关联,两表中匹配的记录应该是:
个人心得
- 遍历左表数据,用左表数据的联属性去匹配右表的每一条数据。
- 如果关联属性相同,则匹配到一条数据。
-
内连接的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图表示如下:
- 对学生表和班级表进行左连接,结果如下:
个人心得
- 遍历左表数据,用左表数据的关联属性去匹配右表中的每一条数据。
- 如果关联属性相同,则匹配到一条数据;
- 如果左表数据没有匹配到右表的任何一条数据,则将右表数据的所有字段置为
NULL
,仍记作匹配到
一条数据。
-
左连接对应的SQL语句如下:
select * from stu left join class on stu.class_id=class.id;
2.4 右连接
- Venn图表示如下:
- 对学生和班级表进行右连接,结果如下:
个人心得
- 遍历右表数据,用右表数据的关联属性去匹配左表中的每一条数据。
- 如果关联属性相同,则匹配到一条数据;
- 如果右表数据没有匹配到左表的任何一条数据,则将左表数据的所有字段置为
NULL
,仍记作匹配到
一条数据。
-
右连接的SQL语句:
select * from stu right join class on stu.class_id=class.id;
2.5 全连接
- Venn图表示如下:
- 对学生和班级表进行全连接,结果如下:
个人心得
- 先对左右表做左连接,再对左右表做右连接
- 两次连接的结果,去重后就是全连接的结果
-
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 m*n m∗n行
- 对学生和班级表做笛卡尔乘积的结果如下:
个人心得: 遍历左表的每一行数据,用左表每一行数据分别于与右表的每一行数据做关联
-
笛卡尔乘积的SQL实现:
select * from stu cross join class; select * from stu,class;
-
可以看出,笛卡尔乘积的运算量超级大,一般不会使用笛卡尔乘积做表的关联查询