九、MySQL多表查询

1、组合查询

1.1、多条查询结果组合显示

格式：

   SELECT 
       列名1
   FROM
       表名
UNION
   SELECT
       列名2
   FROM
       表名2;

演示：

1.2、使用UNION组合3个以上的表

命令：

   (SELECT * FROM WHERE empid=’A102’) 
UNION 
   (SELECT * FROM empid=’A103’) 
UNION 
   (SELECT * FROM WHERE empid=’A104’) 
UNION 
   (SELECT * FROM tb WHERE empid=’A107’);

等价于

命令：

SELECT * FROM tb WHERE empid NOT IN(‘A101);

或者

SELECT * FROM WHERE empid IN(‘A102’,’A103’,’A104’,’A107’);

1.3、按条件合并多条提取结果并显示

操作命令：

(SELECT empid FROM tb WHERE sales>=200) UNION (SELECT empid FROM tb1 WHERE age>=35);

演示：

操作命令：

(SELECT empid FROM tb WHERE sales>=200) UNION ALL (SELECT empid FROM tb1 WHERE age>=35);

演示：

通过对比，我们发现，事实上，当我们使用UNION提取记录时，系统自动去除重复记录。而想要提取数据中包含重复数据，这时候就需要使用UNION ALL了。

1.4、UNION规则

1. 从1.1~1.3的实际操作就可以看出，UNION中的每个查询必须包含相同的列，表达式或聚集函数（不过，各个列不需要以相同的次序列出）。
2. 列的数据类型必须兼容，类型不必完全相同，但必须是DBMS可以隐含转换的类型。
3. 如果结合UNION使用的SELECT语句遇到不同的列名，它会返回第一个名字。这样行为带来一个有意思的副作用。由于只使用第一个名字，那么想要排序也只能用这个名字。
4. 在用UNION组合查询时，只能使用一条ORDER BY子句，它必须位于最后一条SELECT语句之后。

2、连接多个表进行查询（内连接）

2.1、使用其他表的记录进行处理

格式：

SELECT 列名 FROM 表1 JOIN 要连接的表2 ON 表1的列 = 表2的列;

演示：

像这样把不同的表中匹配的记录提取出来的连接方式称为内连接。如果要明确指出某一处理是内连接，可以将JOIN部分写出INNER JOIN。

2.2、选择列进行显示

操作命令：

SELECT tb.empid,tb1.name,tb.sales
FROM tb
JOIN tb1
ON tb.empid=tb1.empid;

演示：

2.3、给表添加别名

格式：

表名 AS 别名

操作命令：

SELECT x.empid,y.name,x.sales
FROM tb as x
JOIN tb1 as y
ON x.empid=y.empid;

演示：

2.4、使用USING使ON~的部分更容易阅读

在前面的实例中，作为连接键的两个列恰好都是empid这个名字。在使用相同列名进行指定情况下，我们可以使用USING（作为连接的键名）。

操作命令：

SELECT tb.empid,tb1.name,tb.sales FROM tb JOIN tb1 USING(empid);

演示：

2.5、通过WHERE设置条件从连接表中查询记录

操作命令：

SELECT tb.empid AS 员工号,tb1.name AS 姓名,tb.sales AS 销售额
FROM tb
JOIN tb1
USING(empid)
WHERE tb.sales>=100;

演示：

2.6、查询多个表中的记录

格式：

SELECT ~ FROM
表名1
JOIN 表名2 连接条件
JOIN 表名3 连接条件
...
;

演示：

3、外连接

3.1、什么是外连接？

外连接具有以下特征：即使与连接键不匹配，外连接也会提取另一个表中的所有记录。

3.2、外连接的种类

1. 左外连接（LETF JOIN）：显示相匹配的记录和左表的全部记录。
2. 右外连接（RIGHT JOIN）：显示相匹配的记录和右表的全部记录。
3. LEFT JOIN也可以写出LEFT OUTER JOIN。同样，RIGHT JOIN也可以写出RIGHT OUTER JOIN。

3.3、使用左外连接

格式：

SELECT 列名 FROM 表1 LEFT JOIN 要连接的表2 ON 表1的列=表2的列;

演示：

3.4、使用右外连接

格式：

SELECT 列名 FROM 表1 RIGHT JOIN 要连接的表2 ON 表1的列 = 表2的列;

演示：

4、自连接

我们可以将表与其自身，也就是和相同的表进行连接。这种连接方式称为自连接。

格式：

SELECT 列名 FROM 表名 AS 别名1 JOIN 表名 AS 别名2;

演示：

注意：使用自连接来提取数据，结果是取笛卡尔积所得。

5、子查询（从SELECT的记录中SELECT）

5.1、显示最大值

操作命令：

SELECT * FROM tb WHERE sales IN (SELECT MAX(sales) FROM tb);

演示：

5.2、提取大于等于平均值的记录

操作命令：

SELECT * FROM tb1 WHERE age >=(SELECT AVG(age) FROM tb1);

演示：

5.3、使用IN（返回列的子查询）

格式：

SELECT 显示的列 FROM 表名 WHERE 列名 IN (通过子查询SELECT语句提取的列);

演示：

5.4、使用EXISTS,仅以存在的记录为对象

操作命令：

SELECT * FROM tb1 WHERE EXISTS (SELECT * FROM tb WHERE tb.empid=tb1.empid);

演示：

5.5、使用NOT EXISTS

操作命令：

SELECT * FROM tb1 WHERE NOT EXISTS (SELECT * FROM tb WHERE tb.empid=tb1.empid);

演示：