【Mysql教程】数据库连接查询join原理详解

零

5229
文章

44
评论

2022 年 6 月 8 日11:29:08Mysql教程评论66字数 6518阅读21分43秒阅读模式2022 年 6 月 8 日

所需工具：

Mysql

聪明的大脑文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/12382.html

勤劳的双手文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/12382.html

文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/12382.html

注意：本站只提供教程，不提供任何成品+工具+软件链接，仅限用于学习和研究，禁止商业用途，未经允许禁止转载/分享等文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/12382.html

文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/12382.html

教程如下

1、连接查询的分类

交叉连接
内连接
外连接文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/12382.html

左外链接（左连接）
右外连接（右连接）文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/12382.html

自然连接文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/12382.html

2、交叉连接

将两张表的数据与另外一张表彼此交叉文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/12382.html

2.1、原理

笛卡尔积：文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/12382.html

从第一张表一次取出每一条数据
取出每一条记录之后，与另外一张表的全部记录挨个匹配
没有任何匹配条件，所有的结果都会保留
记录数=第一张表记录数 * 第二张表记录数
字段数 = 第一张表字段数 + 第二章表字段数

2.2、基本语法

[php]
表1 cross join 表2;
[/php]

[php]
mysql> select * from tb_teacher;
+--------+------+
| name | age |
+--------+------+
| Jack | 24 |
| Tom | 26 |
| Steve | NULL |
| 张三 | 23 |
| 张三 | 23 |
+--------+------+
5 rows in set (0.00 sec)

mysql> select * from my_student;
+----+--------+----------+------+--------+
| id | name | class_id | age | gender |
+----+--------+----------+------+--------+
| 1 | 刘备 | 1 | 18 | 2 |
| 2 | 李四 | 1 | 19 | 1 |
| 3 | 王五 | 2 | 20 | 2 |
| 7 | 张飞 | 2 | 21 | 1 |
| 8 | 关羽 | 1 | 22 | 2 |
| 9 | 曹操 | 1 | 20 | NULL |
+----+--------+----------+------+--------+
6 rows in set (0.01 sec)

mysql> select * from my_student cross join tb_teacher;
+----+--------+----------+------+--------+--------+------+
| id | name | class_id | age | gender | name | age |
+----+--------+----------+------+--------+--------+------+
| 1 | 刘备 | 1 | 18 | 2 | Jack | 24 |
| 1 | 刘备 | 1 | 18 | 2 | Tom | 26 |
| 1 | 刘备 | 1 | 18 | 2 | Steve | NULL |
| 1 | 刘备 | 1 | 18 | 2 | 张三 | 23 |
| 1 | 刘备 | 1 | 18 | 2 | 张三 | 23 |
| 2 | 李四 | 1 | 19 | 1 | Jack | 24 |
| 2 | 李四 | 1 | 19 | 1 | Tom | 26 |
| 2 | 李四 | 1 | 19 | 1 | Steve | NULL |
| 2 | 李四 | 1 | 19 | 1 | 张三 | 23 |
| 2 | 李四 | 1 | 19 | 1 | 张三 | 23 |
| 3 | 王五 | 2 | 20 | 2 | Jack | 24 |
| 3 | 王五 | 2 | 20 | 2 | Tom | 26 |
| 3 | 王五 | 2 | 20 | 2 | Steve | NULL |
| 3 | 王五 | 2 | 20 | 2 | 张三 | 23 |
| 3 | 王五 | 2 | 20 | 2 | 张三 | 23 |
| 7 | 张飞 | 2 | 21 | 1 | Jack | 24 |
| 7 | 张飞 | 2 | 21 | 1 | Tom | 26 |
| 7 | 张飞 | 2 | 21 | 1 | Steve | NULL |
| 7 | 张飞 | 2 | 21 | 1 | 张三 | 23 |
| 7 | 张飞 | 2 | 21 | 1 | 张三 | 23 |
| 8 | 关羽 | 1 | 22 | 2 | Jack | 24 |
| 8 | 关羽 | 1 | 22 | 2 | Tom | 26 |
| 8 | 关羽 | 1 | 22 | 2 | Steve | NULL |
| 8 | 关羽 | 1 | 22 | 2 | 张三 | 23 |
| 8 | 关羽 | 1 | 22 | 2 | 张三 | 23 |
| 9 | 曹操 | 1 | 20 | NULL | Jack | 24 |
| 9 | 曹操 | 1 | 20 | NULL | Tom | 26 |
| 9 | 曹操 | 1 | 20 | NULL | Steve | NULL |
| 9 | 曹操 | 1 | 20 | NULL | 张三 | 23 |
| 9 | 曹操 | 1 | 20 | NULL | 张三 | 23 |
+----+--------+----------+------+--------+--------+------+
30 rows in set (0.00 sec)
[/php]

2.3、应用

基本没有实际意义

等价于

[php]
select * from my_student, tb_teacher;
[/php]

3、内连接

从一张表中取出所有的记录，去另外一张表中匹配，利用匹配条件进行匹配，成功则保留，失败则放弃

3.1、原理

从第一张表中取出一条记录，然后去另外一张表中进行匹配
利用匹配条件进行匹配
匹配到则保留，继续向下匹配
匹配失败则放弃

3.2、基本语法

[php]
表1 inner join 表2 on 匹配条件
[/php]

[php]
create table my_class(
id int primary key auto_increment,
name varchar(10) not null
);

insert into my_class (name) values ('一班'), ('二班');

mysql> select * from my_class;
+----+--------+
| id | name |
+----+--------+
| 1 | 一班 |
| 2 | 二班 |
+----+--------+

mysql> select * from my_student;
+----+--------+----------+------+--------+
| id | name | class_id | age | gender |
+----+--------+----------+------+--------+
| 1 | 刘备 | 1 | 18 | 2 |
| 2 | 李四 | 1 | 19 | 1 |
| 3 | 王五 | 2 | 20 | 2 |
| 4 | 张飞 | 2 | 21 | 1 |
| 5 | 关羽 | 1 | 22 | 2 |
| 6 | 曹操 | 1 | 20 | NULL |
+----+--------+----------+------+--------+

-- 如果内连接没有条件，其实就是交叉连接（笛卡尔积）
mysql> select * from my_student inner join my_class;
+----+--------+----------+------+--------+----+--------+
| id | name | class_id | age | gender | id | name |
+----+--------+----------+------+--------+----+--------+
| 1 | 刘备 | 1 | 18 | 2 | 1 | 一班 |
| 1 | 刘备 | 1 | 18 | 2 | 2 | 二班 |
| 2 | 李四 | 1 | 19 | 1 | 1 | 一班 |
| 2 | 李四 | 1 | 19 | 1 | 2 | 二班 |
| 3 | 王五 | 2 | 20 | 2 | 1 | 一班 |
| 3 | 王五 | 2 | 20 | 2 | 2 | 二班 |
| 4 | 张飞 | 2 | 21 | 1 | 1 | 一班 |
| 4 | 张飞 | 2 | 21 | 1 | 2 | 二班 |
| 5 | 关羽 | 1 | 22 | 2 | 1 | 一班 |
| 5 | 关羽 | 1 | 22 | 2 | 2 | 二班 |
| 6 | 曹操 | 1 | 20 | NULL | 1 | 一班 |
| 6 | 曹操 | 1 | 20 | NULL | 2 | 二班 |
+----+--------+----------+------+--------+----+--------+
12 rows in set (0.00 sec)

-- 表的设计，通常会有同名字段，通常使用`表名.字段`来保证唯一性
mysql> select * from my_student inner join my_class on my_student.class_id = my_class.id;
+----+--------+----------+------+--------+----+--------+
| id | name | class_id | age | gender | id | name |
+----+--------+----------+------+--------+----+--------+
| 1 | 刘备 | 1 | 18 | 2 | 1 | 一班 |
| 2 | 李四 | 1 | 19 | 1 | 1 | 一班 |
| 3 | 王五 | 2 | 20 | 2 | 2 | 二班 |
| 4 | 张飞 | 2 | 21 | 1 | 2 | 二班 |
| 5 | 关羽 | 1 | 22 | 2 | 1 | 一班 |
| 6 | 曹操 | 1 | 20 | NULL | 1 | 一班 |
+----+--------+----------+------+--------+----+--------+

-- 如果表名比较长，可以使用别名简化
mysql> select * from my_student as a inner join my_class b on a.class_id = b.id;
+----+--------+----------+------+--------+----+--------+
| id | name | class_id | age | gender | id | name |
+----+--------+----------+------+--------+----+--------+
| 1 | 刘备 | 1 | 18 | 2 | 1 | 一班 |
| 2 | 李四 | 1 | 19 | 1 | 1 | 一班 |
| 3 | 王五 | 2 | 20 | 2 | 2 | 二班 |
| 4 | 张飞 | 2 | 21 | 1 | 2 | 二班 |
| 5 | 关羽 | 1 | 22 | 2 | 1 | 一班 |
| 6 | 曹操 | 1 | 20 | NULL | 1 | 一班 |
+----+--------+----------+------+--------+----+--------+

-- 可以交换两张表的先后顺序
mysql> select * from my_class b inner join my_student as a on a.class_id = b.id;
+----+--------+----+--------+----------+------+--------+
| id | name | id | name | class_id | age | gender |
+----+--------+----+--------+----------+------+--------+
| 1 | 一班 | 1 | 刘备 | 1 | 18 | 2 |
| 1 | 一班 | 2 | 李四 | 1 | 19 | 1 |
| 2 | 二班 | 3 | 王五 | 2 | 20 | 2 |
| 2 | 二班 | 4 | 张飞 | 2 | 21 | 1 |
| 1 | 一班 | 5 | 关羽 | 1 | 22 | 2 |
| 1 | 一班 | 6 | 曹操 | 1 | 20 | NULL |
+----+--------+----+--------+----------+------+--------+

-- on 可以使用 where 替换，推荐使用 on
mysql> select * from my_class b inner join my_student as a where a.class_id = b.id;
+----+--------+----+--------+----------+------+--------+
| id | name | id | name | class_id | age | gender |
+----+--------+----+--------+----------+------+--------+
| 1 | 一班 | 1 | 刘备 | 1 | 18 | 2 |
| 1 | 一班 | 2 | 李四 | 1 | 19 | 1 |
| 2 | 二班 | 3 | 王五 | 2 | 20 | 2 |
| 2 | 二班 | 4 | 张飞 | 2 | 21 | 1 |
| 1 | 一班 | 5 | 关羽 | 1 | 22 | 2 |
| 1 | 一班 | 6 | 曹操 | 1 | 20 | NULL |
+----+--------+----+--------+----------+------+--------+

[/php]

3.3、应用

内连接通常是在对数据有精确要求的地方使用，必须保证两张表中都能进行数据匹配，内连接匹配到才会保存

4、外连接

按照某一张表作为主表（表中所有记录在最后都会保留）根据条件取连接另外一张表，从而得到目标数据

外连接分为两种：

左连接：左表是主表
右连接：右表是主表

4.1、原理

确定主表，左连接就是左边的表为主表，右连接就是右边的表为主表
拿主表的每一条记录，去匹配另外的一张表（从表）的每一条记录
如果满足匹配条件，保留，不满足即不保留
如果主表记录在从表中一条都没有匹配成功，那么也要保留记录, 从表对应的字段值都是null

4.2、基本语法

[php]
-- 左连接
主表 left join 从表 on 连接条件;
-- 右连接
从表 right join 主表 on 连接条件;
[/php]

左表的数据在前，右表的数据在后

[php]
mysql> select * from my_student;
+----+--------+----------+------+--------+
| id | name | class_id | age | gender |
+----+--------+----------+------+--------+
| 1 | 刘备 | 1 | 18 | 2 |
| 2 | 李四 | 1 | 19 | 1 |
| 3 | 王五 | 2 | 20 | 2 |
| 4 | 张飞 | 2 | 21 | 1 |
| 5 | 关羽 | 1 | 22 | 2 |
| 6 | 曹操 | 1 | 20 | NULL |
+----+--------+----------+------+--------+
mysql> select * from my_class;
+----+--------+
| id | name |
+----+--------+
| 1 | 一班 |
| 3 | 三班 |
| 2 | 二班 |
+----+--------+
mysql> select * from my_student as s left join my_class c on s.class_id = c.id;
+----+--------+----------+------+--------+------+--------+
| id | name | class_id | age | gender | id | name |
+----+--------+----------+------+--------+------+--------+
| 1 | 刘备 | 1 | 18 | 2 | 1 | 一班 |
| 2 | 李四 | 1 | 19 | 1 | 1 | 一班 |
| 3 | 王五 | 2 | 20 | 2 | 2 | 二班 |
| 4 | 张飞 | 2 | 21 | 1 | 2 | 二班 |
| 5 | 关羽 | 1 | 22 | 2 | 1 | 一班 |
| 6 | 曹操 | 1 | 20 | NULL | 1 | 一班 |
+----+--------+----------+------+--------+------+--------+
select * from my_student as s right join my_class c on s.class_id = c.id;
mysql> select * from my_student as s right join my_class c on s.class_id = c.id;
+------+--------+----------+------+--------+----+--------+
| id | name | class_id | age | gender | id | name |
+------+--------+----------+------+--------+----+--------+
| 1 | 刘备 | 1 | 18 | 2 | 1 | 一班 |
| 2 | 李四 | 1 | 19 | 1 | 1 | 一班 |
| 3 | 王五 | 2 | 20 | 2 | 2 | 二班 |
| 4 | 张飞 | 2 | 21 | 1 | 2 | 二班 |
| 5 | 关羽 | 1 | 22 | 2 | 1 | 一班 |
| 6 | 曹操 | 1 | 20 | NULL | 1 | 一班 |
| NULL | NULL | NULL | NULL | NULL | 3 | 三班 |
+------+--------+----------+------+--------+----+--------+
[/php]

4.3、特点

外连接中主表的数据一定会保存，连接之后不会出现记录数少于主表（内连接可能少数据）

左连接和右连接可以相互转换，但是数据对应的位置（字段顺序）会改变

4.4、应用

获取对应主表以及其他数据（关联）

通常使用左连接

5、using关键字

在连接查询中用来代替对应on关键字进行条件匹配

5.1、原理

在连接查询时，使用on的地方用using代替
使用using的前提是对应的两张表连接的字段名是同名的（类似自然连接）
如果使用using关键字，那么对应的同名字段，最后在结果中只会保留一个

5.2、基本语法

[php]
表1 [inner, left, right] join 表2 using (同名字段列表);
[/php]

[php]
select * from my_student left join my_class using(class_id);
-- 等价于
select * from my_student left join my_class on my_student.class_id = my_class.class_id;
[/php]

这个方法通常不使用

历史上的今天

6 月

【Mysql教程】数据库连接查询join原理详解

所需工具：

教程如下

1、连接查询的分类

2、交叉连接

2.1、原理

2.2、基本语法

2.3、应用

3、内连接

3.1、原理

3.2、基本语法

3.3、应用

4、外连接

4.1、原理

4.2、基本语法

4.3、特点

4.4、应用

5、using关键字

5.1、原理

5.2、基本语法

历史上的今天

MySQL是如何给表加字段的？

上传资源管理进阶：优化资源映射

目前深度进阶学习数据库，有那些书籍推荐？

《揭秘MySQL主从复制：数据同步的幕后故事》

docker中mysql设置lower_case_table_names配置的坑

SQL性能优化神器

提升SQL查询效率的终极指南

记录一次使用 Mysqldump 备份数据的bug排查过程，你绝对想不到是什么？

深入理解InnoDB中的页分裂与页合并

在数据库设计时，表记录唯一ID应该如何设置？

加载中...

发表评论

热门搜索

所需工具：

教程如下

1、连接查询的分类

2、交叉连接

2.1、原理

2.2、基本语法

2.3、应用

3、内连接

3.1、原理

3.2、基本语法

3.3、应用

4、外连接

4.1、原理

4.2、基本语法

4.3、特点

4.4、应用

5、using关键字

5.1、原理

5.2、基本语法

历史上的今天

发表评论