文档章节

Mysql多表查询笔记

bithup
 bithup
发布于 2017/12/25 20:16
字数 1329
阅读 18
收藏 1

MySQL连接的种类,JOIN左边(前边)的表是左边,右边(后边)的是右表

  • 逗号:做笛卡尔集连接
  • INNER JOIN: 如果表中有至少一个匹配,则返回行
  • LEFT JOIN: 即使右表中没有匹配,也从左表返回所有的行
  • RIGHT JOIN: 即使左表中没有匹配,也从右表返回所有的行
  • NATURAL  JOIN
  • NATURAL LEFT JOIN
  • NATURAL RIGHT JOIN
  • CROSS JOIN

  • MySQL不支持FULL JOIN(只要其中一个表中存在匹配,就返回行),可以结合使用UNION、LEFT/RIGHT JOIN来实现;
  • MySQL中LEFT/INNER JOIN还可以写成LEFT/INNER OUTER JOIN;
  • CROSS JOIN在标准SQL中是不带ON子句的,作用是生成笛卡尔积,在MySQL中CROSS JOIN后带ON子句时,CROSS会被忽略,就相当于JOIN;
  • 不使用ON子句时,JOIN和INNER JOIN和CROSS JOIN和逗号是等价的,做笛卡尔积;
  • 使用ON子句时,INNER/LEFT/RIGHT JOIN不会生成笛卡尔集,直接根据条件生成临时表
  • 逗号操作符和其他几种连接操作符的优先级不同,尽量不要同时使用避免出错。
  • 使用逗号连接加where子句和使用INNER JOIN加ON子句效果一样
  • join、inner join、cross join支持on和using语法,逗号不支持on和using语法

多表查询的过程

  1. 由多张表生成一张临时表(有on子句时由on子句中的条件生成)
  2. 使用JOIN的方式以及ON子句后面的条件进行筛选
  3. 使用使用WHERE子句后面的条件筛选
  4. 使用GROUP BY条件分组
  5. 使用HAVING+聚合函数筛选
  6. 使用ORDER BY排序
  7. 使用LIMIT筛选

 

假设对表a、b、c进行联合查询

提示错误:Unknown column 'xxx' in 'on clause'

原因是FROM后面的两张表没有使用括号包裹起来

--错误的写法
select * from a,b left join c on 条件 where  条件

--正确的写法
select * from (a,b) left join c on 条件 where 条件 

如果左连接时某个字段为空,就让他等于另外一个字段,使用IFNULL()

SELECT
	h.id AS id,
	h.goods_id AS goodsId,
	g.goods_advert AS goodsAdvert,
	IFNULL(aos.activity_price,g.sell_price) AS sellPrice,--注意g.sell_price不能用别名price代替
	g.goods_name AS goodsName,
	g.logo_url AS logoUrl,
	g.is_delivery AS isDelivery,
	g.sell_price AS price
FROM
	(tb_house h,
	tb_goods g)
 LEFT JOIN tb_aos_goods_rt aos ON h.goods_id = aos.goods_id
WHERE
	1 = 1
AND h.member_id = 107
AND h.goods_id = g.id
AND h. STATUS = 1
ORDER BY
	h.create_date DESC

如果某条数据的aos.activity_price为null,最后的结果集要以sellPrice排序,ORDER by后面应该写别名sellPrice而不是写aos.activity_price

CROSS JOIN的用法:http://www.yiibai.com/mysql/cross-join.html

左右连接能否同时使用

SQL 定义了两种不同语法方式去表示"连接"。首先是"显式连接符号",它显式地使用关键字 JOIN,其次是"隐式连接符号",它使用所谓的"隐式连接符号"。隐式连接符号把需要连接的表放到 SELECT 语句的 FROM 部分,并用逗号隔开。这样就构成了一个"交叉连接",WHERE 语句可能放置一些过滤谓词(过滤条件)。那些过滤谓词在功能上等价于显式连接符号. SQL 89标准只支持内部连接与交叉连接,因此只有隐式连接这种表达方式;SQL 92标准增加了对外部连接的支持,这才有了JOIN表达式。

 

逗号连接加where子句和inner join加on子句的异同

在查询条件相同时它们的查询结果是相同的,但是查询的过程不完全相同;

使用逗号操作符加where子句:首先生成笛卡尔积,因为在没有on子句时,逗号和CROSS JOIN是相同的,然后通过条件去过滤。

使用inner join加on子句:直接通过条件去过滤生成临时表,不会生成笛卡尔集,速度更快

开启优化参数后MySQL会自动优化,通常使用,不会造成资源浪费;不使用逗号,全部使用JOIN是最好的,这样既不会因为运算符优先级不同而出错,也不会因为没有开启优化参数而造成资源浪费。

 

join的方式不同,cross join生成的是先生成笛卡尔集,然后on连接条件被视为了filter用于数据过滤,inner join是直接基于join condition做连接,生成的join集合就是最终的输出结果,产生的中间数据更小。实际上MySQL优化器会将这两条查询都优化成同一种join方式,比如merge join或者nested loop join,如果你没有开启对应的优化参数,那么MySQL只有傻傻的去按指定的方式去做join

查询条件放在ON后面和放在WHERE后面有什么差异

有上可知,在没有ON时,INNER JOIN和CROSS JOIN是一样的 ,所以推荐使用INNER JOIN ON代替逗号加WHERE;

ON条件是生成临时表时使用的条件,与JOIN的类型有关,不同类型产生的临时表也不同,WHERE条件时在临时表生成之后进行筛选的条件,在LEFT或RIGHT JOIN中条件是不能随便放的

在where后面有多个条件时使用加个1=1的原因

© 著作权归作者所有

bithup
粉丝 8
博文 101
码字总数 82172
作品 0
朝阳
程序员
私信 提问
MySQL学习笔记一

MySQL目录结构 配置my.ini MySQL5.7的my.ini位于ProgramDataMySQLMySQL Server 5.7目录下(可能有的版本的my.ini就在安装目录下),该该目录下还有一个data目录存放我们的创建的数据库。 打开...

Aaron_DMC
2016/12/16
40
0
MySql5.6性能优化学习笔记

目标  了解什么是优化  掌握优化查询的方法  掌握优化数据库结构的方法  掌握优化MySQL服务器的方法 什么是优化?  合理安排资源、调整系统参数使MySQL运行更快、更节省资源。  ...

知止内明
2018/06/28
0
0
MySQL学习笔记-子查询和连接

MySQL学习笔记-子查询和连接 使客户端进入gbk编码方式显示: mysql> SET NAMES gbk; 1.子查询 子查询的定义: 子查询(Subquery)是指出现在其他SQL语句内的SELECT子句。 例如: SELECT * FRO...

谢育政
2018/06/26
0
0
Mysql数据碎片的产生与优化

Mysql数据碎片的产生与优化 赵伊凡's Blog2015-04-2481 阅读 性能技术innodbmyisammysql优化 Mysql常用的数据存储引擎一般就两个,一个是InnoDB,一个是MyISAM。而无论那种存储引擎都可能阐述...

赵伊凡's Blog
2015/04/24
0
0
MySql优化相关概念的理解笔记

思维导图 MySQL架构 查询执行流程 查询执行的流程是怎样的: 连接 1.1客户端发起一条Query请求,监听客户端的‘连接管理模块’接收请求 1.2将请求转发到‘连接进/线程模块’ 1.3调用‘用户模...

嘎嘎鸭-
2018/11/27
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Spring使用ThreadPoolTaskExecutor自定义线程池及实现异步调用

多线程一直是工作或面试过程中的高频知识点,今天给大家分享一下使用 ThreadPoolTaskExecutor 来自定义线程池和实现异步调用多线程。 一、ThreadPoolTaskExecutor 本文采用 Executors 的工厂...

CREATE_17
今天
5
0
CSS盒子模型

CSS盒子模型 组成: content --> padding --> border --> margin 像现实生活中的快递: 物品 --> 填充物 --> 包装盒 --> 盒子与盒子之间的间距 content :width、height组成的 内容区域 padd......

studywin
今天
7
0
修复Win10下开始菜单、设置等系统软件无法打开的问题

因为各种各样的原因导致系统文件丢失、损坏、被修改,而造成win10的开始菜单、设置等系统软件无法打开的情况,可以尝试如下方法解决 此方法只在部分情况下有效,但值得一试 用Windows键+R打开...

locbytes
昨天
8
0
jquery 添加和删除节点

本文转载于:专业的前端网站➺jquery 添加和删除节点 // 增加一个三和一节点function addPanel() { // var newPanel = $('.my-panel').clone(true) var newPanel = $(".triple-panel-con......

前端老手
昨天
8
0
一、Django基础

一、web框架分类和wsgiref模块使用介绍 web框架的本质 socket服务端 与 浏览器的通信 socket服务端功能划分: 负责与浏览器收发消息(socket通信) --> wsgiref/uWsgi/gunicorn... 根据用户访问...

ZeroBit
昨天
10
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部