【MySQL】 join连接使用基本知识

以前用过Oracle，新项目接触mysql，很多东西都差不多。

关于mysql join连接的基本使用，网上资料很多，这里更多的是谈谈我自己的理解。A、B两表连接可以理解为根据on条件构建一个虚拟表，其中包含A、B所有字段。

A表结构：id，name，code；
B表结构：id，name，class，grade；

1. 内连接 inner join
   以name关联A、B两表，并显示出其全部字段。
   select * from A (inner) join B on A.name = B.name;

2. 左外连接 left join
   select * from A left join B on A.name = B.name;
   A左连接B表，A表内容全部检索出来，B表只检索出来name相当的结果。同时可以加Where条件来筛选内容。

   如果想要检索出A表有而B表没有的字段，那么就需要到Where条件了。
   select * from A left join B on A.name = B.name where B.name is null。
   现在开始设想由A左连接B构成的虚拟表中，去除B.name为NULL的行，就可以得到想要的结果集。

3. 右外连接 right join
   用法同左外连接。

4. 交叉连接 cross join
   交叉连接就是返回的记录数为两个表的记录数乘积，也就是将两表的数据做组合处理，保证每条数据都有与另一表中的每条数据有匹配结果。
   在行列转换时，可以配合一个序列化表实现交叉连接实现。
   1、对其检索结果用coalesce函数和case when函数做列转行处理，得到行列结果。
   2、利用REPALCE函数，SUBSTRING、SUBSTRING_INDEX函数和LENGTH函数对检索结果做一行转多行，实现属性拆分。
   PS：coalesce函数的使用：coalesce(item1,item2,item3)，依次判断item值，返回第一个不为null的值，若全为null，则返回null。

5. 利用连接删除重复数据
   例如：删除重复数据，保留id最大的记录
   delete a from user1_test a join (select user_name,count(1),max(id) from user1_test group by user_name having count(1) > 1 ) b on a.user_name = b.user_name where a.id < b.id;

6. 利用join更新表的用法
   例如，更新B表中name为张三的grade值为100，且张三必须在A表中存在。
   update A innner join B on A.name = B.name set B.grade = 100 where A.name = “张三”;