当前位置: 萬仟网 > IT编程>数据库>Mysql > MySQL 连接查询的原理和应用

MySQL 连接查询的原理和应用

2020年11月20日  | 萬仟网IT编程  | 我要评论
概述mysql最强大的功能之一就是能在数据检索的执行中连接(join)表。大部分的单表数据查询并不能满足我们的需求,这时候我们就需要连接一个或者多个表,并通过一些条件过滤筛选出我们需要的数据。了解my

概述

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

了解mysql连接查询之前我们先来理解下笛卡尔积的原理。

数据准备

依旧使用上节的表数据(包含classes 班级表和students 学生表):

mysql> select * from classes;
+---------+-----------+
| classid | classname |
+---------+-----------+
|    1 | 初三一班 |
|    2 | 初三二班 |
|    3 | 初三三班 |
|    4 | 初三四班 |
+---------+-----------+
4 rows in set

mysql> select * from students;
+-----------+-------------+-------+---------+
| studentid | studentname | score | classid |
+-----------+-------------+-------+---------+
|     1 | brand    | 97.5 |    1 |
|     2 | helen    | 96.5 |    1 |
|     3 | lyn     | 96  |    1 |
|     4 | sol     | 97  |    1 |
|     7 | b1     | 81  |    2 |
|     8 | b2     | 82  |    2 |
|    13 | c1     | 71  |    3 |
|    14 | c2     | 72.5 |    3 |
|    19 | lala    | 51  |    0 |
+-----------+-------------+-------+---------+
9 rows in set

笛卡尔积

笛卡尔积:也就是笛卡尔乘积,假设两个集合a和b,笛卡尔积表示a集合中的元素和b集合中的元素任意相互关联产生的所有可能的结果。

比如a中有m个元素,b中有n个元素,a、b笛卡尔积产生的结果有m*n个结果,相当于循环遍历两个集合中的元素,任意组合。

笛卡尔积在sql中的实现方式既是交叉连接(cross join)。所有连接方式都会先生成临时笛卡尔积表,笛卡尔积是关系代数里的一个概念,表示两个表中的每一行数据任意组合。

所以上面的表就是 4(班级表)* 9(学生表) = 36条数据;

笛卡尔积语法格式:

 select cname1,cname2,... from tname1,tname2,...;
 or
 select cname from tname1 join tname2 [join tname...];

图例表示:

上述两个表实际执行结果如下:

mysql> select * from classes a,students b order by a.classid,b.studentid;
+---------+-----------+-----------+-------------+-------+---------+
| classid | classname | studentid | studentname | score | classid |
+---------+-----------+-----------+-------------+-------+---------+
|    1 | 初三一班 |     1 | brand    | 97.5 |    1 |
|    1 | 初三一班 |     2 | helen    | 96.5 |    1 |
|    1 | 初三一班 |     3 | lyn     | 96  |    1 |
|    1 | 初三一班 |     4 | sol     | 97  |    1 |
|    1 | 初三一班 |     7 | b1     | 81  |    2 |
|    1 | 初三一班 |     8 | b2     | 82  |    2 |
|    1 | 初三一班 |    13 | c1     | 71  |    3 |
|    1 | 初三一班 |    14 | c2     | 72.5 |    3 |
|    1 | 初三一班 |    19 | lala    | 51  |    0 |
|    2 | 初三二班 |     1 | brand    | 97.5 |    1 |
|    2 | 初三二班 |     2 | helen    | 96.5 |    1 |
|    2 | 初三二班 |     3 | lyn     | 96  |    1 |
|    2 | 初三二班 |     4 | sol     | 97  |    1 |
|    2 | 初三二班 |     7 | b1     | 81  |    2 |
|    2 | 初三二班 |     8 | b2     | 82  |    2 |
|    2 | 初三二班 |    13 | c1     | 71  |    3 |
|    2 | 初三二班 |    14 | c2     | 72.5 |    3 |
|    2 | 初三二班 |    19 | lala    | 51  |    0 |
|    3 | 初三三班 |     1 | brand    | 97.5 |    1 |
|    3 | 初三三班 |     2 | helen    | 96.5 |    1 |
|    3 | 初三三班 |     3 | lyn     | 96  |    1 |
|    3 | 初三三班 |     4 | sol     | 97  |    1 |
|    3 | 初三三班 |     7 | b1     | 81  |    2 |
|    3 | 初三三班 |     8 | b2     | 82  |    2 |
|    3 | 初三三班 |    13 | c1     | 71  |    3 |
|    3 | 初三三班 |    14 | c2     | 72.5 |    3 |
|    3 | 初三三班 |    19 | lala    | 51  |    0 |
|    4 | 初三四班 |     1 | brand    | 97.5 |    1 |
|    4 | 初三四班 |     2 | helen    | 96.5 |    1 |
|    4 | 初三四班 |     3 | lyn     | 96  |    1 |
|    4 | 初三四班 |     4 | sol     | 97  |    1 |
|    4 | 初三四班 |     7 | b1     | 81  |    2 |
|    4 | 初三四班 |     8 | b2     | 82  |    2 |
|    4 | 初三四班 |    13 | c1     | 71  |    3 |
|    4 | 初三四班 |    14 | c2     | 72.5 |    3 |
|    4 | 初三四班 |    19 | lala    | 51  |    0 |
+---------+-----------+-----------+-------------+-------+---------+
36 rows in set

这样的数据肯定不是我们想要的,在实际应用中,表连接时要加上限制条件,才能够筛选出我们真正需要的数据。

我们主要的连接查询有这几种:内连接、左(外)连接、右(外)连接,下面我们一 一来看。

内连接查询 inner join

语法格式:

 select cname from tname1 inner join tname2 on join condition;
 或者
 select cname from tname1 join tname2 on join condition;
 或者
 select cname from tname1,tname2 [where join condition];

说明:在笛卡尔积的基础上加上了连接条件,组合两个表,返回符合连接条件的记录,也就是返回两个表的交集(阴影)部分。如果没有加上这个连接条件,就是上面笛卡尔积的结果。

mysql> select a.classname,b.studentname,b.score from classes a inner join students b on a.classid = b.classid;
+-----------+-------------+-------+
| classname | studentname | score |
+-----------+-------------+-------+
| 初三一班 | brand    | 97.5 |
| 初三一班 | helen    | 96.5 |
| 初三一班 | lyn     | 96  |
| 初三一班 | sol     | 97  |
| 初三二班 | b1     | 81  |
| 初三二班 | b2     | 82  |
| 初三三班 | c1     | 71  |
| 初三三班 | c2     | 72.5 |
+-----------+-------------+-------+
8 rows in set

从上面的数据可以看出 ,初三四班 classid = 4,因为没有关联的学生,所以被过滤掉了;lala 同学的classid=0,没法关联到具体的班级,也被过滤掉了,只取两表都有的数据交集

mysql> select a.classname,b.studentname,b.score from classes a,students b where a.classid = b.classid and a.classid=1;
+-----------+-------------+-------+
| classname | studentname | score |
+-----------+-------------+-------+
| 初三一班 | brand    | 97.5 |
| 初三一班 | helen    | 96.5 |
| 初三一班 | lyn     | 96  |
| 初三一班 | sol     | 97  |
+-----------+-------------+-------+
4 rows in set

查找1班同学的成绩信息,上面语法格式的第三种,这种方式简洁高效,直接在连接查询的结果后面进行where条件筛选。 

左连接查询 left join

left join on / left outer join on,语法格式:

select cname from tname1 left join tname2 on join condition;

说明: left join 是left outer join的简写,全称是左外连接,外连接中的一种。 左(外)连接,左表(classes)的记录将会全部出来,而右表(students)只会显示符合搜索条件的记录。右表无法关联的内容均为null。

mysql> select a.classname,b.studentname,b.score from classes a left join students b on a.classid = b.classid;
+-----------+-------------+-------+
| classname | studentname | score |
+-----------+-------------+-------+
| 初三一班 | brand    | 97.5 |
| 初三一班 | helen    | 96.5 |
| 初三一班 | lyn     | 96  |
| 初三一班 | sol     | 97  |
| 初三二班 | b1     | 81  |
| 初三二班 | b2     | 82  |
| 初三三班 | c1     | 71  |
| 初三三班 | c2     | 72.5 |
| 初三四班 | null    | null |
+-----------+-------------+-------+
9 rows in set

从上面结果中可以看出,初三四班无法找到对应的学生,所以后面两个字段使用null标识。 

右连接查询 right join

right join on / right outer join on,语法格式:

select cname from tname1 right join tname2 on join condition;

说明:right join是right outer join的简写,全称是右外连接,外连接中的一种。与左(外)连接相反,右(外)连接,左表(classes)只会显示符合搜索条件的记录,而右表(students)的记录将会全部表示出来。左表记录不足的地方均为null。 

mysql> select a.classname,b.studentname,b.score from classes a right join students b on a.classid = b.classid;
+-----------+-------------+-------+
| classname | studentname | score |
+-----------+-------------+-------+
| 初三一班 | brand    | 97.5 |
| 初三一班 | helen    | 96.5 |
| 初三一班 | lyn     | 96  |
| 初三一班 | sol     | 97  |
| 初三二班 | b1     | 81  |
| 初三二班 | b2     | 82  |
| 初三三班 | c1     | 71  |
| 初三三班 | c2     | 72.5 |
| null   | lala    | 51  |
+-----------+-------------+-------+
9 rows in set

从上面结果中可以看出,lala同学无法找到班级,所以班级名称字段为null。  

连接查询+聚合函数

使用连接查询的时候,经常会配合使用聚集函数来进行数据汇总。比如在上面的数据基础上查询出每个班级的人数和平均分数、班级总分数。

mysql> select a.classname as '班级名称',count(b.studentid) as '总人数',sum(b.score) as '总分',avg(b.score) as '平均分'
from classes a inner join students b on a.classid = b.classid
group by a.classid,a.classname;
+----------+--------+--------+-----------+
| 班级名称 | 总人数 | 总分  | 平均分  |
+----------+--------+--------+-----------+
| 初三一班 |   4 | 387.00 | 96.750000 |
| 初三二班 |   2 | 163.00 | 81.500000 |
| 初三三班 |   2 | 143.50 | 71.750000 |
+----------+--------+--------+-----------+
3 rows in set

这边连表查询的同时对班级(classid,classname)做了分组,并输出每个班级的人数、平均分、班级总分。

连接查询附加过滤条件

使用连接查询之后,大概率会对数据进行在过滤筛选,所以我们可以在连接查询之后再加上where条件,比如我们根据上述的结果只取出一班的同学信息。

mysql> select a.classname,b.studentname,b.score from classes a inner join students b on a.classid = b.classid where a.classid=1;
+-----------+-------------+-------+
| classname | studentname | score |
+-----------+-------------+-------+
| 初三一班 | brand    | 97.5 |
| 初三一班 | helen    | 96.5 |
| 初三一班 | lyn     | 96  |
| 初三一班 | sol     | 97  |
+-----------+-------------+-------+
4 rows in set

如上,只输出一班的同学,同理,可以附件 limit 限制,order by排序等操作。

总结

1、连接查询必然要带上连接条件,否则会变成笛卡尔乘积数据,使用不正确的联结条件,也将返回不正确的数据。

2、sql规范推荐首选inner join语法。但是连接的几种方式本身并没有明显的性能差距,性能的差距主要是由数据的结构、连接的条件,索引的使用等多种条件综合决定的。

我们应该根据实际的业务场景来决定,比如上述数据场景:如果要求返回返回有学生的班级就使用 inner join;如果必须输出所有班级则使用left join;如果必须输出所有学生,则使用right join。

3、性能上的考虑,mysql在运行时会根据关联条件处理连接的表,这种处理可能是非常耗费资源的,连接的表越多,性能下降越厉害。所以要分析去除那些不必要的连接和不需要显示的字段。

之前我的项目团队在优化旧的业务代码时,发现随着业务的变更,某些数据不需要显示,对应的某个连接也不需要了,去掉之后,性能较大提升。

以上就是mysql 连接查询的原理和应用的详细内容,更多关于mysql 连接查询的资料请关注萬仟网其它相关文章!

如您对本文有疑问或者有任何想说的,请点击进行留言回复,万千网友为您解惑!

相关文章:

  • Mac下mysql 8.0.22 找回密码的方法

    Mac下mysql 8.0.22 找回密码的方法

    mac 最新版 mysql 8.0.22 找回密码大全问题描述:昨天心血来潮, 想在mac上体验一下最新版的mysql的更改密码的流程, 一下子手贱用mysql... [阅读全文]
  • Sysbench对Mysql进行基准测试过程解析

    Sysbench对Mysql进行基准测试过程解析

    前言1.基准测试(benchmarking)是性能测试的一种类型,强调的是对一类测试对象的某些性能指标进行定量的、可复现、可对比的测试。进一步来理解,基准测试是... [阅读全文]
  • 助听器什么品牌最好有什么危害

      在学,听歌要注意时间,不能长时间听太久,会影响  戴了助听器带来无尽的痛苦伤害  戴了助听器给我带来无尽的痛... [阅读全文]
  • MYSQL的null(空值),还有空字符串的坑

    MYSQL的null(空值),还有空字符串的坑

    1建立数据。2简单对比null和空字符串。3判断NULL。4判断空字符串。5综合案例。6总结。 ... [阅读全文]
  • Mysql sql慢查询监控脚本代码实例

    1、修改my.cnf#整体的效果,全局开启表和日志文件都写,但是对于general_log只写表,对于slow_query_log,表和日志文件都记录。gene... [阅读全文]
  • MySQL数据库基于sysbench实现OLTP基准测试

    MySQL数据库基于sysbench实现OLTP基准测试

    sysbench是一款非常优秀的基准测试工具,它能够精准的模拟mysql数据库存储引擎innodb的磁盘的i/o模式。因此,基于sysbench的这个特性,下面... [阅读全文]
  • 通过实例解析布隆过滤器工作原理及实例

    通过实例解析布隆过滤器工作原理及实例

    布隆过滤器布隆过滤器是一种数据结构,比较巧妙的概率型数据结构(probabilistic data structure),特点是高效地插入和查询,可以用来告诉你... [阅读全文]
  • mysql 递归查找菜单节点的所有子节点的方法

    mysql 递归查找菜单节点的所有子节点的方法

    背景项目中遇到一个需求,要求查处菜单节点的所有节点,在网上查了一下,大多数的方法用到了存储过程,由于线上环境不能随便添加存储过程,因此在这里采用类似递归的方法对... [阅读全文]
  • MySQL的表空间是什么

    MySQL的表空间是什么

    今天我要跟你分享的话题是:“大家常说的表空间到底是什么?究竟什么又是数据表?”这其实是一个概念性的知识点,当作拓展知识。涉及到的概念大家了解一下就好,涉及的参数... [阅读全文]
  • mysql 重要日志文件汇总

    mysql 重要日志文件汇总

    作者:丁仪来源:日志是所有应用的重要数据,mysql 也有错误日志、查询日志、慢查询日志、事务日志等。本文简单总结下各种日志,以备查阅。二进制日志 binlog... [阅读全文]
验证码: