MySQL 多表连接查询详解

　　一天一个关于测试知识点，5分钟内讲解你最关心的软件测试问题，今天就接着来谈谈关于软件测试中的“MySQL 多表连接查询详解”。

　　连接：就是将多个表连城一个表输出的过程叫连接(关联)。

　　连接类型：交叉连接、内连接、外连接(左外连接、右外连接)、全连接、等值连接、非等值连接、自然连接。

　　1.交叉连接：

　　A={1,2} B={a,b,c} C={X,Y}

　　A*B={(1,a),(1,b),(1,c),(2,a),(2,b),(2,c)}

　　A*B*C={(1,a,X),(1,b,X),(1,c,X),(2,a,X),(2,b,X),(2,c,X),(1,a,Y),(1,b,Y),(1,c,Y),(2,a,Y),(2,b,Y),(2,c,Y)}

　　交叉连接：用左表的每一行数据和右表的每一行数据两两相连，形成的新表就是交叉连接，交叉连接又叫笛卡尔积。我们把每个表看做是一个集合，表里每行数据看做是集合的一个元素，笛卡尔积就好比上边两个集合的乘积。现有a、b、c三张表如下。

　　语法：

　　select * from a cross join b; #结果如图1

　　以上语法也可以这样写：select * from a,b;

　　select * from a cross join b cross join c; #结果如图2、3

　　以上语法也可以这样写：select * from a,b,c;

　　2.内连接

　　内连接：在交叉连接的基础上，加上了连接条件(左表的某一列=右表某一列)，从交叉连接的结果集里取出满足连接条件的行就是内连接。

　　语法：

　　select * from a inner join b on a2=b2; #结果如图3

　　select * from a inner join b on a2=b2 inner join c on b2=c2; #结果如图4

　　select * from a inner join (b inner join c on b2=c2) on a2=b2; #该语法和第二条语法等价，结果如图4

　　注意事项：

　　1)n个表进行内连接时，至少要n-1个连接条件。

　　2)两个表之间连接的列名如果一样，则必须要在列名前边加表名作为前缀。如果给表起了别名，则只能以别名作为列名的前缀。

　　3)一般情况下两个表之间连接条件，多取的是两个表相同的列名，或者意思相同的列名。

　　3.等值连接

　　等值连接：和内连接本质上是一样的都是在交叉连接的基础上加了连接条件，从交叉连接的结果集里边取出满足连接条件的行。连接的效率、连接结果和内连接都是一样的，区别就是语法不一样。

　　语法：

select * from a,b where a2=b2; #结果如图3

　　select * from a,b,c where a2=b2 and b2=c2; #结果如图4

　　4.自然连接

　　自然连接：如果两个表有相同的列名，则以相同的列名作为连接条件进行内连接，且查询结果中会自动把重复的列去掉，如果两个表没有相同列名就进行交叉连接。

　　语法：

select * from a NATURAL join b;

　　select * from a NATURAL join b NATURAL join c;

　　5.外连接

• 　　外连接：分为左外连接和右外连接。

• 　　左外连接：在内连接的基础上，额外返回左表在内连接中没有出现过的行，右侧用null代替，左外连接会保证左表的每一行数据至少出现一次。

• 　　右外连接：在内连接的基础上，额外返回右表在内连接中没有出现过的行，左侧用null代替，右外连接会保证右表的每一行数据至少出现一次。

　　注意事项：如果多个表进行外连接，要用左外连接，就一直用左外连接，不能前两个表左外连接后边的表又用右外连接或者内连接。且要想把某个表的所有行数据都显示出来则，应该把其作为最左边的表。右外连接类似。

　　左外连接语法：

　　select * from a left join b on a2=b2; #结果如图5

　　select * from a left join b on a2=b2 left join c on b2=c2; #结果如图6

　　右外连接语法：

　　select * from a right join b on a2=b2 ;

　　select * from a right join b on a2=b2 right join c on b2=c2;

　　6.全连接(满连接)

　　全连接：在内连接的基础上，额外返回左表在内连接中没出现的行，右侧用null代替，以及右表在内连接中没出现的行，左侧用null代替。注意mysql数据库中并没有直接的全连接语法，需要使用UNION关键字，将左外连接的结果和右外连接的结果取其并集，取两个查询结果集并集时需要注意列的个数，列的数据类型要一致。

　　语法：

select * from a left join b on a2=b2

　　UNION

　　select * from a right join b on a2=b2; #结果如图9

　　oracle 数据库有直接语法：

select * from a full join b on a2=b2;

　　7.非等值连接

　　非等值连接：多个表直接没有连接条件，只有过滤条件的连接。(这里可先对各个表的行过滤，过滤后在进行交叉连接，也可以是先两个表交叉连接，连接之后在用过滤条件过滤)。

　　非等值连接没有具体的语法，举例如下：

　　select * from a,b where a2 in(2,3); #结果如图10

　　select * from a,b where a2=2 and b2 in(2,3); #结果如图11

　　多表连接时书写的思路：

　　1)先判断用什么连接方式。

　　2)接着分析需要用到哪些表，有时两个表直接没有直接的连接关系时，就要考虑用某个中间表来将这两个表连接起来。

　　3)确定表和表之间的连接条件，也就是列名，一般多是两个表相同的列名，或者意思相同的列名。

　　4)套连接语法。

　　5)把select * 中的*换成具体的列名，尽量不要使用*。

　　6)然后再看是否还需要进一步对连接后的数据进行过滤。如果要过滤，如果当前语句里有where，则加and过滤条件，如果没有where，则加where 过滤条件。

　　怎么判一个题目是用外连接还是内连接?

　　1)如果题目是查询一个表所有的...... 或者每一个.....有类似表的的多用外连接。

　　2)如果题目是查询满足...... 特定条件的数据，一般多用内连接。

　　3)只要是想显示某个表所有行数据，且还涉及到其他表的，一般多用外连接。