几种MySQL中的联接查询操作方法总结
作者:杨国栋 发布时间:2024-01-25 05:15:15
前言
现在系统的各种业务是如此的复杂,数据都存在数据库中的各种表中,这个主键啊,那个外键啊,而表与表之间就依靠着这些主键和外键联系在一起。而我们进行业务操作时,就需要在多个表之间,使用sql语句建立起关系,然后再进行各种sql操作。那么在使用sql写出各种操作时,如何使用sql语句,将多个表关联在一起,进行业务操作呢?而这篇文章,就对这个知识点进行总结。
联接查询是一种常见的数据库操作,即在两张表(多张表)中进行匹配的操作。MySQL数据库支持如下的联接查询:
CROSS JOIN(交叉联接)
INNER JOIN(内联接)
OUTER JOIN(外联接)
其它
在进行各种联接操作时,一定要回忆一下在《SQL逻辑查询语句执行顺序》这篇文章中总结的SQL逻辑查询语句执行的前三步:
执行FROM语句(笛卡尔积)
执行ON过滤
添加外部行
每个联接都只发生在两个表之间,即使FROM子句中包含多个表也是如此。每次联接操作也只进行逻辑查询语句的前三步,每次产生一个虚拟表,这个虚拟表再依次与FROM子句的下一个表进行联接,重复上述步骤,直到FROM子句中的表都被处理完为止。
前期准备
1.新建一个测试数据库TestDB;
create database TestDB;
创建测试表table1和table2;
CREATE TABLE table1
(
customer_id VARCHAR(10) NOT NULL,
city VARCHAR(10) NOT NULL,
PRIMARY KEY(customer_id)
)ENGINE=INNODB DEFAULT CHARSET=UTF8;
CREATE TABLE table2
(
order_id INT NOT NULL auto_increment,
customer_id VARCHAR(10),
PRIMARY KEY(order_id)
)ENGINE=INNODB DEFAULT CHARSET=UTF8;
插入测试数据;
INSERT INTO table1(customer_id,city) VALUES('163','hangzhou');
INSERT INTO table1(customer_id,city) VALUES('9you','shanghai');
INSERT INTO table1(customer_id,city) VALUES('tx','hangzhou');
INSERT INTO table1(customer_id,city) VALUES('baidu','hangzhou');
INSERT INTO table2(customer_id) VALUES('163');
INSERT INTO table2(customer_id) VALUES('163');
INSERT INTO table2(customer_id) VALUES('9you');
INSERT INTO table2(customer_id) VALUES('9you');
INSERT INTO table2(customer_id) VALUES('9you');
INSERT INTO table2(customer_id) VALUES('tx');
准备工作做完以后,table1和table2看起来应该像下面这样:
mysql> select * from table1;
+-------------+----------+
| customer_id | city |
+-------------+----------+
| 163 | hangzhou |
| 9you | shanghai |
| baidu | hangzhou |
| tx | hangzhou |
+-------------+----------+
4 rows in set (0.00 sec)
mysql> select * from table2;
+----------+-------------+
| order_id | customer_id |
+----------+-------------+
| 1 | 163 |
| 2 | 163 |
| 3 | 9you |
| 4 | 9you |
| 5 | 9you |
| 6 | tx |
+----------+-------------+
7 rows in set (0.00 sec)
准备工作做的差不多了,开始今天的总结吧。
CROSS JOIN联接(交叉联接)
CROSS JOIN对两个表执行FROM语句(笛卡尔积)操作,返回两个表中所有列的组合。如果左表有m行数据,右表有n行数据,则执行CROSS JOIN将返回m*n行数据。CROSS JOIN只执行SQL逻辑查询语句执行的前三步中的第一步。
CROSS JOIN可以干什么?由于CROSS JOIN只执行笛卡尔积操作,并不会进行过滤,所以,我们在实际中,可以使用CROSS JOIN生成大量的测试数据。
对上述测试数据,使用以下查询:
select * from table1 cross join table2;
就会得到以下结果:
+-------------+----------+----------+-------------+
| customer_id | city | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163 | hangzhou | 1 | 163 |
| 9you | shanghai | 1 | 163 |
| baidu | hangzhou | 1 | 163 |
| tx | hangzhou | 1 | 163 |
| 163 | hangzhou | 2 | 163 |
| 9you | shanghai | 2 | 163 |
| baidu | hangzhou | 2 | 163 |
| tx | hangzhou | 2 | 163 |
| 163 | hangzhou | 3 | 9you |
| 9you | shanghai | 3 | 9you |
| baidu | hangzhou | 3 | 9you |
| tx | hangzhou | 3 | 9you |
| 163 | hangzhou | 4 | 9you |
| 9you | shanghai | 4 | 9you |
| baidu | hangzhou | 4 | 9you |
| tx | hangzhou | 4 | 9you |
| 163 | hangzhou | 5 | 9you |
| 9you | shanghai | 5 | 9you |
| baidu | hangzhou | 5 | 9you |
| tx | hangzhou | 5 | 9you |
| 163 | hangzhou | 6 | tx |
| 9you | shanghai | 6 | tx |
| baidu | hangzhou | 6 | tx |
| tx | hangzhou | 6 | tx |
+-------------+----------+----------+-------------+
INNER JOIN联接(内联接)
INNER JOIN比CROSS JOIN强大的一点在于,INNER JOIN可以根据一些过滤条件来匹配表之间的数据。在SQL逻辑查询语句执行的前三步中,INNER JOIN会执行第一步和第二步;即没有第三步,不添加外部行,这是INNER JOIN和接下来要说的OUTER JOIN的最大区别之一。
现在来看看使用INNER JOIN来查询一下:
select *
from table1
inner join table2
on table1.customer_id=table2.customer_id;
就会得到以下结果:
+-------------+----------+----------+-------------+
| customer_id | city | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163 | hangzhou | 1 | 163 |
| 163 | hangzhou | 2 | 163 |
| 9you | shanghai | 3 | 9you |
| 9you | shanghai | 4 | 9you |
| 9you | shanghai | 5 | 9you |
| tx | hangzhou | 6 | tx |
+-------------+----------+----------+-------------+
对于INNER JOIN来说,如果没有使用ON条件的过滤,INNER JOIN和CROSS JOIN的效果是一样的。当在ON中设置的过滤条件列具有相同的名称,我们可以使用USING关键字来简写ON的过滤条件,这样可以简化sql语句,例如:
select * from table1 inner join table2 using(customer_id);
在实际编写sql语句时,我们都可以省略掉INNER关键字,例如:
select *
from table1
join table2
on table1.customer_id=table2.customer_id;
但是,请记住,这还是INNER JOIN。
OUTER JOIN联接(外联接)
哦,记得有一次参加面试,还问我这个问题来着,那在这里再好好的总结一下。通过OUTER JOIN,我们可以按照一些过滤条件来匹配表之间的数据。OUTER JOIN的结果集等于INNER JOIN的结果集加上外部行;也就是说,在使用OUTER JOIN时,SQL逻辑查询语句执行的前三步,都会执行一遍。关于如何添加外部行,请参考《SQL逻辑查询语句执行顺序》这篇文章中的添加外部行部分内容。
MySQL数据库支持LEFT OUTER JOIN和RIGHT OUTER JOIN,与INNER关键字一样,我们可以省略OUTER关键字。对于OUTER JOIN,同样的也可以使用USING来简化ON子句。所以,对于以下sql语句:
select *
from table1
left outer join table2
on table1.customer_id=table2.customer_id;
我们可以简写成这样:
select *
from table1
left join table2
using(customer_id);
但是,与INNER JOIN还有一点区别是,对于OUTER JOIN,必须指定ON(或者using)子句,否则MySQL数据库会抛出异常。
NATURAL JOIN联接(自然连接)
NATURAL JOIN等同于INNER(OUTER) JOIN与USING的组合,它隐含的作用是将两个表中具有相同名称的列进行匹配。同样的,NATURAL LEFT(RIGHT) JOIN等同于LEFT(RIGHT) JOIN与USING的组合。比如:
select *
from table1
join table2
using(customer_id);
与
select *
from table1
natural join table2;
等价。
在比如:
select *
from table1
left join table2
using(customer_id);
与
select *
from table1
natural left join table2;
等价。
STRAIGHT_JOIN联接
STRAIGHT_JOIN并不是一个新的联接类型,而是用户对sql优化器的控制,其等同于JOIN。通过STRAIGHT_JOIN,MySQL数据库会强制先读取左边的表。举个例子来说,比如以下sql语句:
explain select *
from table1 join table2
on table1.customer_id=table2.customer_id;
它的主要输出部分如下:
+----+-------------+--------+------+---------------+
| id | select_type | table | type | possible_keys |
+----+-------------+--------+------+---------------+
| 1 | SIMPLE | table2 | ALL | NULL |
| 1 | SIMPLE | table1 | ALL | PRIMARY |
+----+-------------+--------+------+---------------+
我们可以很清楚的看到,MySQL是先选择的table2表,然后再进行的匹配。如果我们指定STRAIGHT_JOIN方式,例如:
explain select *
from table1 straight_join table2
on table1.customer_id=table2.customer_id;
上述语句的主要输出部分如下:
+----+-------------+--------+------+---------------+
| id | select_type | table | type | possible_keys |
+----+-------------+--------+------+---------------+
| 1 | SIMPLE | table1 | ALL | PRIMARY |
| 1 | SIMPLE | table2 | ALL | NULL |
+----+-------------+--------+------+---------------+
可以看到,当指定STRAIGHT_JOIN方式以后,MySQL就会先选择table1表,然后再进行的匹配。
那么就有读者问了,这有啥好处呢?性能,还是性能。由于我这里测试数据比较少,大进行大量数据的访问时,我们指定STRAIGHT_JOIN让MySQL先读取左边的表,让MySQL按照我们的意愿来完成联接操作。在进行性能优化时,我们可以考虑使用STRAIGHT_JOIN。
多表联接
在上面的所有例子中,我都是使用的两个表之间的联接,而更多时候,我们在工作中,可能不止要联接两张表,可能要涉及到三张或者更多张表的联接查询操作。
对于INNER JOIN的多表联接查询,可以随意安排表的顺序,而不会影响查询的结果。这是因为优化器会自动根据成本评估出访问表的顺序。如果你想指定联接顺序,可以使用上面总结的STRAIGHT_JOIN。
而对于OUTER JOIN的多表联接查询,表的位置不同,涉及到添加外部行的问题,就可能会影响最终的结果。
总结
这是MySQL中联接操作的全部内容了,内容虽多,但是都还比较简单,结合文章中的例子,再自己实际操作一遍,完全可以搞定的。这一篇文章就这样了。
猜你喜欢
- //定义一个对象数组 var data = [{ name: "jiang", age: 22 }, { name: &
- 在命令行中运行python代码是很常见的,下面介绍如何定义命令后面跟的参数。1 常规用法Python代码中主要使用下面
- 接触了一下docker和k8s,感觉是非常不错的东西。能够方便的部署线上环境,而且还能够更好的利用机器的资源,感觉是以后的大趋势。最近刚好有
- 使用SQL对数据进行提取和分析时,我们经常会遇到数据重复的场景,需要我们对数据进行去重后分析。以某电商公司的销售报表为例,常见的去重方法我们
- 其中 offset and fetch 最重要的新特性是 用来 分页,既然要分析 分页,就肯定要和之前的分页方式来比较了,特别是 Row_N
- 如下所示:(x,y)为要转的点,(pointx,pointy)为中心点,如果顺时针角度为anglesrx = (x-pointx)*cos(
- progress库安装和介绍1.安装progress库progress是Python第三方库,在终端执行 pip 命令安装。pip inst
- 左右布局,图片的宽度不固定。正好前几天测试闭合浮动元素时发现了overflow的特殊现象,倒是正好可以用在这里。测试通过:ie6/7&nbs
- Anaconda 是一个基于 Python 的数据处理和科学计算平台,它已经内置了许多非常有用的第三方库,装上Anaconda,就相当于把
- 本文实例为大家分享了python实现QQ空间自动点赞的具体代码,供大家参考,具体内容如下项目github地址使用python实现qq空间自动
- 定义行为要定义行为,通过继承 yii\base\Behavior 或其子类来建立一个类。如:namespace app\components
- Python socket网络编程初学 python,前段时间买了两本书《python 编程从入门到实践》《Python 核心编程第三版》,
- 需求:该接口,含两个参数,一个是file,一个是paperName。其中file为上传的文件。content-type为form-data。
- 一.基本数据类型整数:int字符串:str(注:\t等于一个tab键)布尔值: bool列表:list (元素的集合)列表用[]元祖:tup
- 在获取 IP 时,已经成功将各个网站的代理 IP 获取下来了,然后就需要一个检测模块来对所有的代理进行一轮轮的检测,检测可用就设置为满分,不
- 背景有时本地服务器的时间不准了,需要同步互联网上的时间。解决方案NTP时间同步,找到一些可用的NTP服务器进行同步即可。通过获取一些大型网站
- python生成随机数都有哪些办法呢使用 random 模块:random模块是python内置的模块,使用方法如random.randin
- 老板由于事务繁忙无法经常亲临教研室,于是让我搞个监控系统,让他在办公室就能看到教研室来了多少人。o(>﹏<)o|||最初我的想法
- concat()是将tensor沿着指定维度连接起来。其中tensorflow1.3版中是这样定义的:concat(values,axis,
- MybatisPlus 是一款在 Mybatis 基础上进行的增强 orm 框架,可以实现不写 sql 就完成数据库相关的操作。普通的 ma