mysql的连接代码(MySQL 连接查询的原理和应用)
mysql的连接代码
MySQL 连接查询的原理和应用概述
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 连接查询的资料请关注开心学习网其它相关文章!
原文链接:https://www.cnblogs.com/wzh2010/p/13843033.html?utm_source=tuicool&utm_medium=referral
- mysql死锁是什么原因(MySQL的MaxIdleConns不合理,会变成短连接的原因)
- mysql中命令大全(MySQL中ESCAPE关键字的用法详解)
- mysql错误代码之1064的解决方案(mysql错误代码之1064的解决方案)
- phpmysql网站开发入门与提高(PHP+MySQL+sphinx+scws实现全文检索功能详解)
- docker 增大mysql连接数(docker中修改mysql最大连接数及配置文件的实现)
- 怎么用docker中的mysql连接数据库(连接docker里面的mysql失败解决方法)
- mysql 分片键规则(MySql8 WITH RECURSIVE递归查询父子集的方法)
- 对mysql索引的理解(详解MySQL 8.0 之不可见索引)
- mysql架构示意图(MySQL创建横向直方图的解决方案)
- mysql8.0安装教程win10(Windows10下mysql 8.0.22 安装配置方法图文教程)
- mysql实现数据的备份的命令(Windows下MySQL定时备份脚本的实现)
- mysql建立分区表指令(MySQL高级特性——数据表分区的概念及机制详解)
- mysqlupdate语句(MySQL将select结果执行update的实例教程)
- mysql中基本语句(MySQL中explain语句的基本使用教程)
- mysql存储过程声明(MySQL存储过程的深入讲解in、out、inout)
- mysql怎么给查询权限(MySql设置指定用户数据库查看查询权限)
- 小说 顾瑾岚拿出一套飞行棋,别说你连飞行棋都不会哦(顾瑾岚拿出一套飞行棋)
- 金品公司 界界乐中秋限定飞行棋礼盒 露营藤篮礼盒全新上市(界界乐中秋限定飞行棋礼盒)
- 必看 8月,相比七夕,更需要注意的是这些事(必看8月相比七夕)
- 8月23日11时16分将迎处暑,逐渐进入气象意义上的秋天(8月23日11时16分将迎处暑)
- 花不语 下 如果重来一次的话,你还会这么选择吗(花不语下如果重来一次的话)
- 城市记忆之上海 最难忘的是老弄堂里的市井味道(城市记忆之上海)
热门推荐
- 协程在python中怎么使用(python协程之动态添加任务的方法)
- cssbackground(用 CSS background 实现刻度线的呈现)
- 微信小程序swiper大小调整(微信小程序swiper-dot中的点如何改成滑块详解)
- 跨站请求伪造的几个解决方法
- sql语句中leftjoin的作用(sql中的left join及on、where条件关键字的区别详解)
- sql如何合并多个表中的数据(SQL 列不同的表查询结果合并操作)
- html基本标签大全(HTML中meta标签及Keywords)
- UML对象图的介绍
- css垂直居中
- laravel自定义数据库驱动(laravel框架数据库配置及操作数据库示例)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9