Mysql应用几种MySQL中的联接查询操作方法总结

作者：VEPHP 时间 2017-10-17

《Mysql应用几种MySQL中的联接查询操作方法总结》要点：
本文介绍了Mysql应用几种MySQL中的联接查询操作方法总结，希望对您有用。如果有疑问，可以联系我们。

MYSQL学习前言

MYSQL学习现在系统的各种业务是如此的复杂,数据都存在数据库中的各种表中,这个主键啊,那个外键啊,而表与表之间就依靠着这些主键和外键联系在一起.而我们进行业务操作时,就需要在多个表之间,使用sql语句建立起关系,然后再进行各种sql操作.那么在使用sql写出各种操作时,如何使用sql语句,将多个表关联在一起,进行业务操作呢?而这篇文章,就对这个知识点进行总结.

MYSQL学习联接查询是一种常见的数据库操作,即在两张表(多张表)中进行匹配的操作.MySQL数据库支持如下的联接查询：

CROSS JOIN(交叉联接)
INNER JOIN(内联接)
OUTER JOIN(外联接)
其它

MYSQL学习在进行各种联接操作时,一定要回忆一下在《SQL逻辑查询语句执行顺序》这篇文章中总结的SQL逻辑查询语句执行的前三步：

执行FROM语句(笛卡尔积)
执行ON过滤
添加外部行

MYSQL学习每个联接都只发生在两个表之间,即使FROM子句中包括多个表也是如此.每次联接操作也只进行逻辑查询语句的前三步,每次产生一个虚拟表,这个虚拟表再依次与FROM子句的下一个表进行联接,重复上述步骤,直到FROM子句中的表都被处理完为止.
前期准备

MYSQL学习 1.新建一个测试数据库TestDB；

MYSQL学习

MYSQL学习
create database TestDB;

MYSQL学习创建测试表table1和table2；

MYSQL学习
   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;

MYSQL学习插入测试数据；

MYSQL学习
   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');

MYSQL学习准备工作做完以后,table1和table2看起来应该像下面这样：

MYSQL学习
   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)

MYSQL学习准备工作做的差不多了,开始本日的总结吧.
CROSS JOIN联接(交叉联接)

MYSQL学习CROSS JOIN对两个表执行FROM语句(笛卡尔积)操作,返回两个表中所有列的组合.如果左表有m行数据,右表有n行数据,则执行CROSS JOIN将返回m*n行数据.CROSS JOIN只执行SQL逻辑查询语句执行的前三步中的第一步.

MYSQL学习CROSS JOIN可以干什么?由于CROSS JOIN只执行笛卡尔积操作,并不会进行过滤,所以,我们在实际中,可以使用CROSS JOIN生成大量的测试数据.

MYSQL学习对上述测试数据,使用以下查询：

MYSQL学习
select * from table1 cross join table2;

MYSQL学习就会得到以下结果：

MYSQL学习
+-------------+----------+----------+-------------+
| 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     |
+-------------+----------+----------+-------------+

MYSQL学习INNER JOIN联接(内联接)

MYSQL学习INNER JOIN比CROSS JOIN强大的一点在于,INNER JOIN可以根据一些过滤条件来匹配表之间的数据.在SQL逻辑查询语句执行的前三步中,INNER JOIN会执行第一步和第二步；即没有第三步,不添加外部行,这是INNER JOIN和接下来要说的OUTER JOIN的最大区别之一.

MYSQL学习现在来看看使用INNER JOIN来查询一下：

MYSQL学习
select * 
from table1 
inner join table2 
on table1.customer_id=table2.customer_id;

MYSQL学习就会得到以下结果：

MYSQL学习
+-------------+----------+----------+-------------+
| 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     |
+-------------+----------+----------+-------------+

MYSQL学习对于INNER JOIN来说,如果没有使用ON条件的过滤,INNER JOIN和CROSS JOIN的效果是一样的.当在ON中设置的过滤条件列具有相同的名称,我们可以使用USING关键字来简写ON的过滤条件,这样可以简化sql语句,例如：

MYSQL学习
select * from table1 inner join table2 using(customer_id);

MYSQL学习在实际编写sql语句时,我们都可以省略掉INNER关键字,例如：

MYSQL学习
select * 
from table1 
join table2 
on table1.customer_id=table2.customer_id;

MYSQL学习但是,请记住,这还是INNER JOIN.
OUTER JOIN联接(外联接)

MYSQL学习哦,记得有一次参加面试,还问我这个问题来着,那在这里再好好的总结一下.通过OUTER JOIN,我们可以依照一些过滤条件来匹配表之间的数据.OUTER JOIN的结果集等于INNER JOIN的结果集加上外部行；也就是说,在使用OUTER JOIN时,SQL逻辑查询语句执行的前三步,都会执行一遍.关于如何添加外部行,请参考《SQL逻辑查询语句执行顺序》这篇文章中的添加外部行部分内容.

MYSQL学习MySQL数据库支持LEFT OUTER JOIN和RIGHT OUTER JOIN,与INNER关键字一样,我们可以省略OUTER关键字.对于OUTER JOIN,同样的也可以使用USING来简化ON子句.所以,对于以下sql语句：

MYSQL学习
select * 
from table1 
left outer join table2 
on table1.customer_id=table2.customer_id;

MYSQL学习我们可以简写成这样：

MYSQL学习
select * 
from table1 
left join table2 
using(customer_id);

MYSQL学习但是,与INNER JOIN还有一点区别是,对于OUTER JOIN,必须指定ON(或者using)子句,否则MySQL数据库会抛出异常.
NATURAL JOIN联接(自然连接)

MYSQL学习NATURAL JOIN等同于INNER(OUTER) JOIN与USING的组合,它隐含的作用是将两个表中具有相同名称的列进行匹配.同样的,NATURAL LEFT(RIGHT) JOIN等同于LEFT(RIGHT) JOIN与USING的组合.比如：

MYSQL学习
select * 
from table1 
join table2 
using(customer_id);

MYSQL学习与

MYSQL学习
select * 
from table1 
natural join table2;

MYSQL学习等价.

MYSQL学习在比如：

MYSQL学习
select * 
from table1 
left join table2 
using(customer_id);

MYSQL学习与

MYSQL学习
select * 
from table1 
natural left join table2;

MYSQL学习等价.
STRAIGHT_JOIN联接

MYSQL学习STRAIGHT_JOIN并不是一个新的联接类型,而是用户对sql优化器的控制,其等同于JOIN.通过STRAIGHT_JOIN,MySQL数据库会强制先读取左边的表.举个例子来说,比如以下sql语句：

MYSQL学习
explain select * 
from table1 join table2 
on table1.customer_id=table2.customer_id;

MYSQL学习它的主要输出部分如下：

MYSQL学习
+----+-------------+--------+------+---------------+
| id | select_type | table | type | possible_keys |
+----+-------------+--------+------+---------------+
| 1 | SIMPLE   | table2 | ALL | NULL     |
| 1 | SIMPLE   | table1 | ALL | PRIMARY    |
+----+-------------+--------+------+---------------+

MYSQL学习我们可以很清楚的看到,MySQL是先选择的table2表,然后再进行的匹配.如果我们指定STRAIGHT_JOIN方式,例如：

MYSQL学习
explain select * 
from table1 straight_join table2 
on table1.customer_id=table2.customer_id;

MYSQL学习上述语句的主要输出部分如下：

MYSQL学习
+----+-------------+--------+------+---------------+
| id | select_type | table | type | possible_keys |
+----+-------------+--------+------+---------------+
| 1 | SIMPLE   | table1 | ALL | PRIMARY    |
| 1 | SIMPLE   | table2 | ALL | NULL     |
+----+-------------+--------+------+---------------+

MYSQL学习可以看到,当指定STRAIGHT_JOIN方式以后,MySQL就会先选择table1表,然后再进行的匹配.

MYSQL学习那么就有读者问了,这有啥好处呢?性能,还是性能.由于我这里测试数据比较少,大进行大量数据的访问时,我们指定STRAIGHT_JOIN让MySQL先读取左边的表,让MySQL依照我们的意愿来完成联接操作.在进行性能优化时,我们可以考虑使用STRAIGHT_JOIN.
多表联接

MYSQL学习在上面的所有例子中,我都是使用的两个表之间的联接,而更多时候,我们在工作中,可能不止要联接两张表,可能要涉及到三张或者更多张表的联接查询操作.

MYSQL学习对于INNER JOIN的多表联接查询,可以随意支配表的顺序,而不会影响查询的结果.这是因为优化器会自动根据成本评估出访问表的顺序.如果你想指定联接顺序,可以使用上面总结的STRAIGHT_JOIN.

MYSQL学习而对于OUTER JOIN的多表联接查询,表的位置不同,涉及到添加外部行的问题,就可能会影响最终的结果.
总结

MYSQL学习这是MySQL中联接操作的全部内容了,内容虽多,但是都还比较简单,结合文章中的例子,再本身实际操作一遍,完全可以搞定的.这一篇文章就这样了.

《Mysql应用几种MySQL中的联接查询操作方法总结》是否对您有启发，欢迎查看更多与《Mysql应用几种MySQL中的联接查询操作方法总结》相关教程，学精学透。维易PHP学院为您提供精彩教程。

转载请注明本页网址：
http://www.vephp.com/jiaocheng/9854.html

标签：

PHP教程

WEB前端开发

数据库

WEB服务器

APP开发

LINUX学习

后端开发课程

前端开发课程

数据库课程

Mysql应用几种MySQL中的联接查询操作方法总结

同类教程排行

特辑教程