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

1、几种mysql中的联接查询操作方法总结现在系统的各种业务是如此的复杂，数据都存在数据 库中的各种表中，这个主键啊，那个外键啊，而表与表之 间就依靠着这些主键和外键联系在一起。而我们进行业务 操作时，就需要在多个表之间，使用sql语句建立起关系, 然后再进行各种sql操作。那么在使用sql写出各种操作 时，如何使用sql语句，将多个表关联在一起，进行业务 操作呢？而这篇文章，就对这个知识点进行总结。联接查询是一种常见的数据库操作，即在两张表（多张表）中进行匹配的操作。mysq l数据库支持如下的联接cross joi n （交叉联接）inne rjoin （内联接）0 uterjoin （外联接

2、）其它在进行各种联接操作时，一定要回忆一下在sql逻辑 查询语句执行顺序这篇文章中总结的sql逻辑查询语句 执行的前三步：执行from语句（笛卡尔积） 执行on过滤 添加外部行含多个表也是如此。每次联接操作也只进行逻辑查询语句 的前三步，每次产生一个虚拟表，这个虚拟表再依次与from子句的下一个表进行联接，重复上述步骤，直到from子句中的表都被处理完为止。前期准备1.新建一个测试数据库testdb；createdatab asetestdb;创建测试表tablel和table2；cr eatetableta blel (customr(1o)notnul ey (customer utf8;

3、create o_incrementl,cityvarch _id)engine tabletable2 ,customer_iar(1o)notnu =innodbdefa (order_idin dvarchar(10er_idvarchall, primarykultcharset=tnotnullaut),primarykey (order_id) engine=inn odbdefaultc harset=utf8 ; 插入测试数据；i nsertintota blel (custom er_id, city) values c 163,'hangzhou '):in

4、sertintotablel(cu stomer_id, city)values( '9you,'shanghai，);ins ertintotablel (customerinsertintotidu' ,' hangz_id,city)va ablel(custo hou');inserlues c tx mer_id, city tint0table2hangzhou'); )values (' ba. (customer_icustomer_id)values (' 163) jinsertint0table2(custo

5、mer_id)valuesc 9you ) jinsertint0table2(customer_.id)valuesc 9yof) jinsertint0table2(customer_id)valuesc 9you');insertint0table2(customer_id)values c tx');准备工作做完以后，tablet和ta ble2看起来应该像 下面这样：mysql gt; select*f romtable;+| custome r_id| city | + +|163|han gzhou|9you |shanghai| baidu|hangz hou

6、| | tx | han gzhou | + +- +4rowsinset (0 .00sec)mysq lgt;select*fromtable2;+1 order_id | customer _id | + + +|1|1631 |2|163|3|9you| |49you|5|9you|6|tx|+ + +7rowsinset (0.0osec)准备工作做的差不多了，开始今天的总结吧。cros sjoin联接(交叉联接)crossjo in对两个表执行from语句(笛卡尔积)操作, 返回两个表中所有列的组合。如果左表有m行数据，右表 有n行数据，则执行crossjoin将返回m*n行数据。

7、crossjoin只执行sql逻辑查询语句执行的前三步中的第一crossjoin可以干什么？由于cros sjoin只执行笛卡 尔积操作，并不会进行过滤，所以，我们在实际中，可以 使用crossjo in生成大量的测试数据。对上述测试数据，使用以下查询:sele ct*fromtabl elcross join table2;就会得到以下结果:卜 icustome r_id|city|o rder_id| cus tomer_id | + 163 hangzhou|1|1 ngzhou|1|16 u|2|163|9y2|163|txou|9you|shyou|tx|han|9you|shan

8、u|tx|hangz 9you|shangh tx|hangzho |shanghai|663|9you|sh 3|tx|hangz ou|shanghai hangzhou|2 anghai|3|9y gzhou|3|9yo ghai|4|9you hou|4|9you| ai|5|9you| u|5|9you|1 tx|baiduanghai|1|16 hou|1|163| 2|163|bai163|163|haou|baidu|h u|163|hang |baidu|han163|hangzh baidu|hangz 63|hangzhou hangzhou|63|baidu|ha 16

9、3|hangzho du|hangzhou ngzhou|3|9y angzhou|3|9 zhou|4|9you gzhou|4|9yo ou|5|9you| hou|5|9you| 6|tx|9you tx|tx|hangzhou 6 tx +inner join联接（内联接）innerjoin 比 crossjoin 强大的一点在于，inne rjoin 可以根据一些过滤条件来匹配表之间的数据。在sql逻辑 查询语句执行的前三步中，i nnerjoin会执行第一步和第 二步；即没有第三步，不添加外部行，这是innerjoin和 接下来要说的oute rjoin的最大区别之一。现在来看看使

10、用innerjoin来查询一下：selec t*fromtable linnerjoint able2ontabl el. customer _id二table2 customer_id ;就会得到以下结果：卜 cu stomer_id c ity ordeti d customer_id +163|hangzho u|1|163|163|hangzhou| 2|163|9you|shanghai|3|9you|9you |shanghai|4|9you|9you|shanghai|5 |9you|tx|h angzhou|6|t x|+对于innerjoin来说，如果没有使用on条件的过滤，

11、 inner join和crossj 0in的效果是一样的。当在on中设置 的过滤条件列具有相同的名称，我们可以使用using关键字来简写on的过滤条件，这样可以简化sql语句，例如:seiect*fromtablei innerjointable2using(customer_id);在实际编写sql语句时，我们都可以省略掉inner关键 字，例如：select*fromtableljointable 2ontablel customer_id=table2 cust omer_id;但是，请记住，这还是inne rjoinoouter join联接(外联接)哦，记得有一次参加面试，还问我这

12、个问题来着，那在这里再好好的总结一下。通过outer join,我们可以按 照一些过滤条件来匹配表之间的数据。outerj 0in的结果集等于in nerj0in的结果集加上外部行；也就是说，在使用outerjoi n时，sql逻辑查询语句执行的前三步，都会执行一遍。关于如何添加外部行，请参考sql逻辑查询语 句执行顺序这篇文章中的添加外部行部分内容。mysql 数据库支持 leftou terjoin 和 rig htouterjoin , 与inner关键字一样，我们可以省略out er关键字。对于 out erjoin,同样的也可以使用using来简化on子句。所 以，对于以下sql语句

13、：s elect*fromt ablelleftou terjointabl e2ontablel.customer id =table2> cus tomer id;我们可以简写成这样：s elect*fromt ablelleft jo intable2usi ng (customer _id);但是，与innerjoin还有一点区别是，对于0ut erjoin, 必须指定on (或者using)子句，否则mysql数据库会抛出 异常。nat uraljoin联接(自然连接)natu raljoin 等同于 i nner(outer) join 与 using 的组 合，它隐含的作用

14、是将两个表中具有相同名称的列进行匹 配。同样的，n aturalleft(right) join 等同于 left(righ t)join与using的组合。比如：s elect*fromt ableljointa ble2using(c ustomer_id); 与select*fromtableln atural joint able2;等价。在比如：select* fromtablell eftjointabl e2using(cus tomer_id);与select*fr omtablelnat uralleft joi ntable2;等价。straight_jo in 联接str

15、ai ght_join并不是一个新的联接类型，而是用户 对sql优化器的控制，其等同于joino通过straight, join, mysql数据库会强制先读取左边的表。举个例子来说, 比如以下sql语句：e xplainselec t*fromtable 1jointable2 ontable1.cu stomer_id二t able2 custo mer_id;它的主要输出部分如下：卜|id|s elect_type|table|type|possible_ke ys|+1| simple11 able2 |all|n ull| 111 simp le| ta.blel| all | pr

16、imary | +我们可以很清楚的看到，m ysql是先选择的ta ble2表, 然后再进行的匹配。如果我们指定s traight_joi n方式， 例如：ex plainselect *fromtablel straight_jo intable2ont ablel custo mer_id=tabl e2. customer _id; 上述语句的主要输出部分如下：+ 卜|id|s elect_type|table|type|possible_ke ys|*1|simple|t ablel|all|p rimary|1|s imple|table 2|all|null|4可以看到，当指定stra ight_join方式以后，mysql 就会先选择tablet表，然后再进行的匹配。那么就有读者问了，这有啥好处呢？性能，还是性能。 由于我这里测试数据比较少，大进行大量数据的访问时， 我们指定str aight_join让mysql先读取左边的表，让 mysql按照我们的意愿来完成联接操作。在进行性能优化时, 我们可以考虑使用stra ight_joino多表联接在上面的所有例子中，我都是使用的两个表之间的联接，而更多时候，我们在工作中