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、基本语法

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连接查询内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • mysql多个left join连接查询用法分析

    本文实例讲述了mysql多个left join连接查询用法.分享给大家供大家参考,具体如下: mysql查询时需要连接多个表时,比如查询订单的商品表,需要查询商品的其他信息,其他信息不在订单的商品表,需要连接其他库的表,但是连接的条件基本都是商品ID就可以了,先给一个错误语句(查询之间的嵌套,效率很低): SELECT A.order_id, A.wid, A.work_name, A.supply_price, A.sell_price, A.total_num, A.sell_profit,

  • Mysql join连接查询的语法与示例

    连接查询: 是将两个查询(或表)的每一行,以"两两横同对接"的方式,所得到的所有行的结果,即一个表中的某行,跟另一个表中的某行.进行"横向对接",得到一个新行. 连接查询包括以下这些不同形式,连接方式: 交叉连接.内连接.外连接(分:左外连接,右外连接) 连接查询语法: select * from 表名 [连接方式] join 表名 [on 连接条件] where ...; 测试数据: mysql> select * from test; +----+----

  • MySQL查询优化:连接查询排序limit(join、order by、limit语句)介绍

    不知道有没有人碰到过这样恶心的问题:两张表连接查询并limit,SQL效率很高,但是加上order by以后,语句的执行时间变的巨长,效率巨低. 情况是这么一个情况:现在有两张表,team表和people表,每个people属于一个team,people中有个字段team_id. 下面给出建表语句: 复制代码 代码如下: create table t_team ( id int primary key, tname varchar(100) ); create table t_people (

  • MySQL优化之使用连接(join)代替子查询

    使用连接(JOIN)来代替子查询(Sub-Queries) MySQL从4.1开始支持SQL的子查询.这个技术可以使用SELECT语句来创建一个单列的查询结果,然后把这个结果作为过滤条件用在另一个查询中.例如,我们要将客户基本信息表中没有任何订单的客户删除掉,就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来,然后将结果传递给主查询,如下所示: DELETE FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FR

  • Mysql连接join查询原理知识点

    Mysql连接(join)查询 1.基本概念 将两个表的每一行,以"两两横向对接"的方式,所得到的所有行的结果. 假设: 表A有n1行,m1列: 表B有n2行,m2列: 则表A和表B"对接"之后,就会有: n1*n2行: m1+m2列. 2.则他们对接(连接)之后的结果类似这样: 3.连接查询基本形式: from  表1  [连接方式] join  表2  [on连接条件]连接查询基本形式: from  表1  [连接方式] join  表2  [on连接条件] 1

  • 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.基本语法 1.连接查询的分类 交叉连接 内连接 外连接 左外链接(左连接) 右外连接(右连接) 自然连接 2.交叉连接 将两张表的数据与另外一张表彼此交叉 2.1.原理 笛卡尔积: 从第一张表一次取出每一条数据 取出每一条记录之后,与另外一

  • MySQL 连接查询的原理和应用

    概述 MySQL最强大的功能之一就是能在数据检索的执行中连接(join)表.大部分的单表数据查询并不能满足我们的需求,这时候我们就需要连接一个或者多个表,并通过一些条件过滤筛选出我们需要的数据. 了解MySQL连接查询之前我们先来理解下笛卡尔积的原理. 数据准备 依旧使用上节的表数据(包含classes 班级表和students 学生表): mysql> select * from classes; +---------+-----------+ | classid | classname |

  • 解析MySQL join查询的原理

    MySQL用Nested-Loop Join算法实现join查询 区分驱动表和被驱动表,以驱动表的结果集为循环的基础,访问被驱动表过滤数据,然后合并结果,驱动表在外循环.被驱动表在内循环.如果还有第三张参与join查询的表,则以合并的结果为驱动表,第三张表作为被驱动表,以此类推. left join中的左表是驱动表.右表是被驱动表,right join刚好相反. Nested-Loop Join有三种实现 SNLJ Simple Nested-Loop Join 假设A是驱动表,B是被驱动表.

  • MySQL JOIN关联查询的原理及优化

    目录 1 关联查询的执行 2 没有索引的算法 1 关联查询的执行 关联查询的执行过程是:先遍历关联表t1(驱动表,全表扫描),然后根据从表t1中取出的每行数据中的a值,去表t2(被关联表,被驱动表)中查找满足条件的记录,可以走t2的索引搜索.在形式上,这个过程就跟我们写程序时的嵌套查询类似,并且可以用上被驱动表的索引,所以我们称之为“Index Nested-Loop Join”,简称NLJ.在join语句的执行流程中,驱动表是走全表扫描,而被驱动表是走索引树搜索. 假设被驱动表的行数是M.每次

  • mysql连接查询、联合查询、子查询原理与用法实例详解

    本文实例讲述了mysql连接查询.联合查询.子查询原理与用法.分享给大家供大家参考,具体如下: 本文内容: 连接查询 联合查询 子查询 from子查询 where子查询 exists子查询 首发日期:2018-04-11 连接查询: 连接查询就是将多个表联合起来查询,连接查询方式有内连接.外连接.自然连接.交叉连接.连接查询使得可以同时查看多张表中数据. 内连接:有条件连接,多个表之间依据指定条件连接,匹配结果是保留符合匹配结果的记录. 外连接:与内连接不同的是不管匹配符不符合都保留,根据外连接

  • MySQL子查询原理的深入分析

    目录 01前言 02准备内容 03子查询的语法形式和分类 3.1 语法形式 3.1.1  FROM子句中 3.1.2 WHERE或IN子句中 3.2 分类 3.2.1 按返回的结果集区分 3.2.2 按与外层查询关系来区分 04子查询在MySQL中是怎么执行的 4.1 标量子查询.行子查询的执行方式 4.1.1 不相关子查询 4.1.2 相关的子查询 4.2 IN子查询 4.2.1 物化 4.2.2 半联接的实现: 4.2.3 半联接的适用条件 4.2.4 转为 EXISTS 子查询 05总结

  • mysql高效查询left join和group by(加索引)

    mysql高效查询 mysql牺牲了group by来增加left join的速度(前提是加了索引). user表:10万数据 实例1: 200秒左右 SELECT U.id, A.favorite_count FROM (SELECT id from user) U LEFT JOIN ( -- 点赞数 SELECT favorite_by AS user_id, SUM(favorite_count) AS favorite_count FROM favorite GROUP BY favo

  • MySql 缓存查询原理与缓存监控和索引监控介绍

    查询缓存 1.查询缓存操作原理 mysql执行查询语句之前,把查询语句同查询缓存中的语句进行比较,且是按字节比较,仅完全一致才被认为相同.如下,这两条语句被视为不同的查询 SELECT * FROM tb1_name Select * from tb1_name 1)不同数据库.不同协议版本,或字符集不同的查询被视为不同的查询并单独缓存. 2)以下两种类型的查询不被缓存 a.预处理语句 b.嵌套查询的子查询 3)从查询缓存抓取查询结果前,mysql检查用户对查询涉及的所有数据库和表是否有查询权限

  • 详解Mysql两表 join 查询方式

    目录 一.SQL基本语法格式 二.3种join方式 1. left join(左连接) 2. right join(右连接) 3. inner join(内连接) 4. 在理解上面的三种join下,查询(A -  A∩B) 5. 查询 ( B - A∩B ) 6. 查询(A∪B - A∩B) 7. 查询 AUB 一.SQL基本语法格式 SELECT DISTINCT < select_list > FROM < left_table > < join_type > JO

  • MySQL慢查询相关参数原理解析

    MySQL的慢查询,全名是慢查询日志,是MySQL提供的一种日志记录,用来记录在MySQL中响应时间超过阀值的语句.具体环境中,运行时间超过long_query_time值的SQL语句,则会被记录到慢查询日志中.long_query_time的默认值为10,意思是记录运行10秒以上的语句.默认情况下,MySQL数据库并不启动慢查询日志,需要手动来设置这个参数. 当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响. 此外,慢查询日志支持将日志记录写入文

随机推荐