首页 > 资讯 > 数据库 >MySQL数据库连接查询 join原理

542

分享到

MySQL数据库连接查询 join原理

2024-04-02 19:04:59 542人浏览八月长安

摘要

目录1、连接查询的分类2、交叉连接2.1、原理2.2、基本语法2.3、应用3、内连接3.1、原理3.2、基本语法3.3、应用4、外连接4.1、原理4.2、基本语法4.3、特点4.4、

1、连接查询的分类

交叉连接
内连接
外连接
- 左外链接（左连接）
- 右外连接（右连接）
自然连接

2、交叉连接

将两张表的数据与另外一张表彼此交叉

2.1、原理

笛卡尔积：

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

2.2、基本语法

表1 cross join 表2;

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)

2.3、应用

基本没有实际意义

等价于

select * from my_student, tb_teacher;

3、内连接

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

3.1、原理

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

3.2、基本语法

表1 inner join 表2 on 匹配条件

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 |
+----+--------+----+--------+----------+------+--------+

3.3、应用

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

4、外连接

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

外连接分为两种：

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

4.1、原理

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

4.2、基本语法

-- 左连接
主表 left join 从表 on 连接条件;
-- 右连接
从表 right join 主表 on 连接条件;

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

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 | 三班   |
+------+--------+----------+------+--------+----+--------+

4.3、特点

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

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

4.4、应用

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

通常使用左连接

5、using关键字

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

5.1、原理

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

5.2、基本语法

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

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;

这个方法通常不使用

到此这篇关于MySQL数据库连接查询 join原理的文章就介绍到这了,更多相关MySQL连接查询内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网！

您可能感兴趣的文档:

点击免费下载>>软考高级考试备考技巧/历年真题/备考精华资料

--结束END--

本文标题: MySQL数据库连接查询 join原理

本文链接: https://www.lsjlt.com/news/150825.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑，方便收藏和打印～

下载Word文档

去做题

猜你喜欢

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、...

99+

2022-11-13
MySQL数据库如何连接查询join

今天小编给大家分享一下MySQL数据库如何连接查询join的相关知识点，内容详细，逻辑清晰，相信大部分人都还太了解这方面的知识，所以分享这篇文章给大家参考一下，希望大家阅读完这篇文章后有所收获，下面我们一起来了解一下吧。1、连接查询的分类交...

99+

2023-07-02
Mysql连接join查询的原理是什么

Mysql连接join查询的原理是什么，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。Mysql连接（join）查询1、基本概念将两个表的每...

99+

2022-10-18
Mysql Join-连接查询(中)

Mysql Join-连接查询(中) 认识就我平时的数据接触来看, 连接查询也没有很复杂,不够是非常需要耐心和逻辑的, 一点点将数据查出来, 拼接等. 没有什么技巧, 多练习就会了. 无非就是表之间根据共同key来连接, 查询时结合uni...

99+

2016-09-20

Mysql Join-连接查询(中)
MySQL连接查询INNER JOIN、LEFT JOIN、RIGHT JOIN

一、内连接（INNER JOIN）获取两个表中字段匹配关系的记录，需要两个表都满足条件的数据才会返回。如下图阴影部分所示区域。实例：有一张表table_a，数据如下：表table_b，数据如下：内连接SQL（...

99+

2017-06-25

MySQL连接查询INNER JOIN LEFT JOIN RIGHT JOIN
Mysql数据库连接查询

Mysql数据库连接查询连接是关系数据库模型的主要特点。连接查询是关系数据库中最主要的查询，主要包括内连接、外连接等。通过连接运算可以实现多个表查询。当查询数...

99+

2022-10-18
MYSQL数据库--连接查询

连接查询连接是把不同表的记录连到一起的最普遍的方法。一种错误的观念认为由于MySQL的简单性和源代码开放性，使它不擅长连接。这种观念是错误的。MySQL从一开始就能够很好地支持连接，现在还以支持标准的S...

99+

2022-10-18
MySQL数据库：多表连接查询

多表连接查询注意：使用连接技术建议将表经行重命名！ # explain 检索连接是否达标 # 内连接 # 语法1 from 表1 inner join 表2 on 主键字段=外键字段 [where 条件表达式] # 语法2 ...

99+

2022-01-30

MySQL数据库：多表连接查询
解析MySQL join查询的原理

MySQL用Nested-Loop Join算法实现join查询区分驱动表和被驱动表，以驱动表的结果集为循环的基础，访问被驱动表过滤数据，然后合并结果，驱动表在外循环、被驱动表在内...

99+

2022-11-13
MySQL多表查询内连接外连接详解，join、left join、right join、full join的使用

目录 1、多表查询概览 1.1、分类 1.2、外连接的分类 1.3、常用的SQL语法标准 2、内外联接案例 2.1、初始化表 2.2、内连接 2.3、外连接案例 2.4、全连接案例 2.5、union和union all...

99+

2023-09-02

mysql 数据库 java
Mysql 多表连接查询 inner join 和 outer join 的使用

首先先列举本篇用到的分类(内连接，外连接，交叉连接)和连接方法(如下)： A）内连接：join，inner join B）外连接：left join，left outer join，right join，right out...

99+

2014-07-14

Mysql 多表连接查询 inner join 和 outer join 的使用
Mysql查询数据库连接状态及连接信息

Mysql查询数据库连接状态及连接信息使用MySQL时，需要了解当前数据库的情况，例如当前的数据库大小、字符集、用户等等。下面总结了一些查看数据库相关信息的命令查看显示所有数据库 mysql> s...

99+

2023-09-10

mysql 数据库 sql
MySQL 连接查询的原理和应用

概述 MySQL最强大的功能之一就是能在数据检索的执行中连接（join）表。大部分的单表数据查询并不能满足我们的需求，这时候我们就需要连接一个或者多个表，并通过一些条件过滤筛选出我们需要的数据。了解MySQL连接查...

99+

2022-05-25

MySQL 查询 MySQL 连接查询
MySQL数据查询之连接查询的案例

小编给大家分享一下MySQL数据查询之连接查询的案例，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！连接关系是关系数据库模型的主要...

99+

2022-10-18
Mysql怎么查询数据库连接状态及连接信息

本文小编为大家详细介绍“Mysql怎么查询数据库连接状态及连接信息”，内容详细，步骤清晰，细节处理妥当，希望这篇“Mysql怎么查询数据库连接状态及连接信息”文章能帮助大家解决疑惑，下面跟着小编的思路慢慢深...

99+

2023-04-19

mysql 数据库
SQL数据查询——连接查询

文章目录一、等值和非等值连接查询1.等值连接查询2.非等值连接查询二、自连接与非自连接三、内连接与外连接1.内连接2.外连接使用左外连接还是右外连接？满外连接四、UNIO&...

99+

2023-10-26

sql 数据库 mysql oracle
MySQL JOIN关联查询的原理及优化

目录1 关联查询的执行2 没有索引的算法1 关联查询的执行关联查询的执行过程是：先遍历关联表t1（驱动表，全表扫描），然后根据从表t1中取出的每行数据中的a值，去表t2（被关联表，被驱动表）中查找满足条件的记录，可以走...

99+

2022-08-22

MySQLJOIN关联查询 MySQLJOIN
mysql数据库查询缓存原理详解

这篇文章将为大家详细讲解有关mysql数据库查询缓存原理详解，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。mysql数据库查询缓存原理是：1、缓存SELECT操作...

99+

2022-10-18
Mysql查询数据库连接状态以及连接信息详解

目录查看显示所有数据库查看当前使用的数据库查看数据库使用端口查看当前数据库大小查看数据所占的空间大小查看索引所占的空间大小查看数据库编码查看数据库的表信息查看数据库的所有用户信息查看某个具体用户的权限查看数据库的最大连接...

99+

2023-04-14

mysql查询数据库连接信息的命令 MySQL数据库连接 mysql查询数据库连接状态信息
MySQL数据库中多表查询之内连接，外连接，子查询的示例分析

小编给大家分享一下MySQL数据库中多表查询之内连接，外连接，子查询的示例分析，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！多表...

99+

2022-10-18