MySQL多表查询

一、什么是多表查询

1、概述

多表查询就是从多张表中去查询数据，在实际开发中大多数情况数据都是存储在多张中的，它们通过一个关联关系连接起来，这样就可以通过这个关联关系去查询到想要的数据。比如：下面两个表的数据就是通过stu_num这个字段关联的，通过stu_num即可查询到此名学生的成绩。
关联关系：可以是一对一，也可以是一对多的关系

（关于这两张表的sql在文章最后附录中）

2、笛卡尔积现象

上文提到了需要通过一个关联关系连接起来，如果不通过关系连接会出现什么情况呢？答案就是笛卡尔积现象。

(1)、使用此SQL去查询观察结果：
SELECT * FROM t_student,t_score
查询出了两个表的全部组合结果，共计49条记录。

（图片中只展示了部分数据）

(2)、使用连接关系查询结果：
SELECT * FROM t_student,t_score WHERE t_student.stu_num = t_score.stu_num
查询出期望的结果6条记录，因为关联关系是字段stu_num，所有可以对应查询到的结果只有6条记录。

(3)、查询出最终结果（可以设置别名查询想要的列）

SELECT a.stu_num,a.stu_name,a.stu_age,b.stu_score 
FROM t_student a,t_score b 
WHERE a.stu_num = b.stu_num   # 关联关系
1
2
3

二、多表查询的分类

1、等值连接 vs非等值连接

1、等值连接：就是通过一个表的字段和另一个表的字段相等来连接

SELECT a.stu_num,a.stu_name,a.stu_age,b.stu_score 
FROM t_student a,t_score b 
WHERE a.stu_num = b.stu_num
1
2
3

2、非等值连接：不是这种两表之间通过字段相等的连接。例如我们统计每个学生的分数在那个等级

（引入第三张表，成绩等级表）

SELECT
	* 
FROM
	t_score a,
	t_grade b
WHERE
	a.stu_score BETWEEN b.score_min and b.score_max
1
2
3
4
5
6
7

3、连接多张表
如果想查查询出最终的结果需要连接三张表才可以。

SELECT
	a.stu_num,
	a.stu_name,
	a.stu_age,
	b.stu_score,
	c.score_grade 
FROM
	t_student a,
	t_score b,
	t_grade c 
WHERE
	a.stu_num = b.stu_num 
	AND 
	b.stu_score BETWEEN c.score_min AND c.score_max
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2、自连接 vs 非自连接

(1)、自连接：就是同一张表，利用取别名的方式虚拟成两张表以代表不同的意义的表，然后这两个表再进行内连接或者外连接。

SELECT
	a.employee_id,
	a.employee_name,
	b.employee_name as manager_name 
FROM
	t_employee a,
	t_employee b 
WHERE
	a.manager_id = b.employee_id
1
2
3
4
5
6
7
8
9

只有两条记录是因为只有依据连接关系只又两条记录符合条件

(2)、非自连接：不同表之间进行连接

3、内连接 vs 外连接

(1)、内连接：只查出符合关联条件的记录。上面的所有连接均为内连接
语法：
a、直接在from后面接关联表，where后面写关联关系

SELECT
	a.stu_num,
	a.stu_name,
	a.stu_age,
	b.stu_score 
FROM
	t_student a,
	t_score b 
WHERE
	a.stu_num = b.stu_num
1
2
3
4
5
6
7
8
9
10

b、INNER JOIN 表名 ON 关联关系 WHERE 其他条件 ，其中INNER可以省略不写

SELECT
	a.stu_num,
	a.stu_name,
	a.stu_age,
	b.stu_score 
FROM
	t_student a
	INNER JOIN t_score b ON a.stu_num = b.stu_num
1
2
3
4
5
6
7
8

查询结果

(2)、外连接：外连接分为左外连接和右外连接。
左外连接：在查出符合关联条件的同时会将左表中的记录全部展示出来，没有关联到记录右表补充为null。
语法：LEFT OUTER JOIN 表名 ON 关联关系 WHERE 其他条件，其中OUTER可以省略

• 例一：查出所有学生的成绩

• 例一SQL:

SELECT
	a.stu_num,
	a.stu_name,
	a.stu_age,
	b.stu_score 
FROM
	t_student a
	LEFT JOIN t_score b ON a.stu_num = b.stu_num
1
2
3
4
5
6
7
8

• 例二：查出所有员工和他们的经理
  
• 例二SQL

SELECT
	a.employee_id,
	a.employee_name,
	b.employee_name AS manager_name 
FROM
	t_employee a
	LEFT JOIN t_employee b ON a.manager_id = b.employee_id
1
2
3
4
5
6
7

右外连接：在查出符合关联条件的同时会将右表中的记录全部展示出来，没有关联到记录左表补充为null。
语法：RIGHT OUTER JOIN 表名 ON 关联关系 WHERE 其他条件，其中OUTER可以省略

• 例三：查询所有成绩对应的学生信息

• 例三SQL

SELECT
	a.stu_num,
	a.stu_name,
	a.stu_age,
	b.stu_score 
FROM
	t_student a
	RIGHT JOIN t_score b ON a.stu_num = b.stu_num
1
2
3
4
5
6
7
8

• 例四：查询所有经理的员工
  

• 例四SQL

SELECT
	a.employee_id,
	a.employee_name,
	b.employee_name AS manager_name 
FROM
	t_employee a
	RIGHT JOIN t_employee b ON a.manager_id = b.employee_id
1
2
3
4
5
6
7

4、满外连接（FULL OUTER JOIN）

• 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
• SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
• 需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替

三、UNION[ALL]的使用

利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。其中UNION ALL不会对结果集去重，但是效率较高，日常开发中更建议使用UNION All

条件：两个表对应的列数和数据类型必须相同，并且相互对应
语法：

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2
1
2
3

演示：

• 例五：查询stu_num小于5和stu_age大于10的全部记录

• 例五SQL

SELECT stu_num,stu_name,stu_age FROM t_student WHERE stu_num < 5 
UNION ALL 
SELECT stu_num, stu_name, stu_age FROM t_student WHERE stu_age > 10
1
2
3

• 例六：查询stu_num小于5和stu_age大于10的全部记录，去除重复
  
• 例六SQL

SELECT stu_num,stu_name,stu_age FROM t_student WHERE stu_num < 5 
UNION
SELECT stu_num, stu_name, stu_age FROM t_student WHERE stu_age > 10
1
2
3

四、7种SQL JOINS的实现