MySQL基础篇之多表查询（内连接、外连接、自连接、子查询、union）

05、多表查询

5.1、多表关系

1、概述

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

• 一对多（多对一）
• 多对多
• 一对一

2、一对多（多对一）

• 案例：部门与员工的关系
• 关系：一个部门对应多个员工，一个员工对应一个部门
• 实现：在多的一方建立外键，指向一的一方的主键

3、多对多

• 案例：学生与课程的关系
• 关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
• 实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

4、一对一

• 案例：用户与用户详情的关系
• 关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率。
• 实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）

5.2、多表查询概念

1、概述：指从多张表中查询数据

2、笛卡尔积：笛卡尔乘积是指在数学中，两个集合 A集合和B集合的所有组合情况。（在多表查询时，需要消除无效的笛卡尔积）

3、多表查询分类

（1）连接查询

• 内连接：相当于查询A、B交集部分数据
• 外连接：
  • 左外连接：查询左表所有数据，以及两张表交集部分数据
  • 右外连接：查询右表所有数据，以及两张表交集部分数据
• 自连接：当前表与自身的连接查询，自连接必须使用表别名

（2）子查询

5.3、内连接

1、内连接查询的是两张表交集的部分

2、内连接查询语法：

• 隐式内连接

  SELECT 字段列表 FROM 表1,表2 WHERE 条件...;
  1

• 显式内连接

  SELECT 字段列表 FROM 表1 [INNER] JION 表2 ON 连接条件...;
  1

3、案例

-- 内连接演示
-- 1.查询每一个员工的姓名，及关联的部门的名称（隐式内连接实现）
-- 表结构：emp,dept
-- 连接条件：emp.dept_id = dept.id
select emp.name, dept.name from emp,dept where emp.dept_id = dept.id;
select e.name, d.name from emp e,dept d where e.dept_id = d.id; #给表起别名

-- 2.查询每一个员工的姓名，及关联的部门的名称（显式内连接实现）
select emp.name, dept.name from emp inner join dept on emp.dept_id = dept.id;
select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;
select e.name, d.name from emp e join dept d on e.dept_id = d.id;
1
2
3
4
5
6
7
8
9
10
11

5.4、外连接

1、外连接查询语法：

• 左外连接

  SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;
  1

  相当于查询表1（左表）的所有数据，包含表1和表2交集部分的数据

• 右外连接

  SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;
  1

  相当于查询表2（右表）的所有数据，包含表1和表2交集部分的数据

2、案例

-- 外连接演示
-- 1.查询emp表的所有数据，和对应的部门信息（左外连接）
-- 表结构：emp,dept
-- 连接条件：emp.dept_id = dept.id
select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;
select e.*, d.name from emp e left join dept d on e.dept_id = d.id;

-- 2.查询dept表的所有数据，和对应的员工信息（右外连接）
select d.*,e.* from emp e right join dept d on d.id = e.dept_id;
select d.*,e.* from dept d left join emp e on d.id = e.dept_id;
1
2
3
4
5
6
7
8
9
10

5.5、自连接

1、自连接查询语法：

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;
1

自连接查询，可以是内连接查询，也可以是外连接查询。

2、案例：

-- 自连接查询
-- 1.查询员工及其所属领导的名字
select e1.name, e2.name from emp e1 , emp e2 where e1.managerid = e2.id;

-- 2.查询所有员工 emp 及其领导的名字 emp,如果员工没有领导，也需要查询出来
select e1.name, e2.name from emp e1 left join emp e2 on e1.managerid = e2.id;
1
2
3
4
5
6

5.6、联合查询-union，union all

1、对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

2、语法：

SELECT 字段列表 FROM 表A...
UNION [ALL]
SELECT 字段列表 FROM 表B...;
1
2
3

3、案例

-- union all, union
-- 1.将薪资低于5000的员工，和年龄大于50岁的员工全部查询出来
select * from emp where salary < 5000
union 
select * from emp where age > 50;
1
2
3
4
5

注意：

1、对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

2、union将查询到的结果合并，并去重；union all会将查询到的全部数据直接合并在一起

5.7、子查询

5.7.1、子查询介绍

1、概念：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

2、表现形式：

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
1

3、子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个。

4、根据子查询结果不同，分为：

• 标量子查询（子查询结果为单个值）
• 列子查询（子查询结果为一列）
• 行子查询（子查询结果为一行）
• 表子查询（子查询结果为多行多列）

5、根据子查询位置，分为：WHERE之后、FROM之后、SELECT之后。

5.7.2、标量子查询

1、子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。

2、常用的操作符：=、<>、>、>=、<、<=

3、案例：

-- 标量子查询
-- 1.查询"销售部"的所有员工信息
-- a.查询销售部的部门ID
-- b.根据销售部部门ID，查询员工信息
select * from emp where dept_id = (select id from dept where name = '销售部');

-- 2.查询在"方东白"入职之后的员工信息
-- a.查询"方东白"入职日期
-- b.查询指定入职日期之后的员工信息
select * from emp where entrydate > (select entrydate from emp where name = '方东白');
1
2
3
4
5
6
7
8
9
10

5.7.3、列子查询

1、子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。

2、常用的操作符：IN、NOT IN、ANY、SOME、ALL

3、案例

-- 列子查询
-- 1.查询“销售部”和“市场部”的所有员工信息
-- a.查询“销售部”和“市场部”的部门ID
-- b.根据部门ID，查询员工信息
select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');

-- 2.查询比财务部所有人工资都高的员工信息
select * from emp where salary > all (select salary from emp where dept_id = (select id from dept where name = '财务部'));

-- 3.查询比研发部其中任意一人工资高的员工信息
select * from emp where salary > any (select salary from emp where dept_id = (select id from dept where name = '研发部'));
1
2
3
4
5
6
7
8
9
10
11

5.7.4、行子查询

1、子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。

2、常用的操作符：=、<>、IN、NOT IN

3、案例：

-- 行子查询
-- 1.查询与“张无忌”的薪资及直属领导相同的员工信息
-- a.查询“张无忌”的薪资及直属领导
select salary,managerid from emp where name = '张无忌';

-- b.查询与“张无忌”的薪资及直属领导相同的员工信息
select * from emp where (salary,managerid) = (select salary,managerid from emp where name = '张无忌');
1
2
3
4
5
6
7

5.7.5、表子查询

1、子查询返回的结果是多行多列，这种子查询称为表子查询。

2、常用的操作符：IN

3、案例：

-- 表子查询
-- 1.查询与“鹿杖客”、“张无忌”的薪资及职位相同的员工信息
select * from emp where (salary,job) in (select salary,job from emp where name = '鹿杖客' or name = '张无忌');

-- 2.查询入职时间是“2006-01-01”之后的员工信息，及其部门信息
-- a.查询入职时间是“2006-01-01”之后的员工信息
select * from emp where entrydate > '2006-01-01' ;
-- b.查询这部分员工，对应的部门信息
select * from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id;
1
2
3
4
5
6
7
8
9

5.8、多表查询案例

（1）根据需求，完成SQL语句的编写

1. 查询员工的姓名、年龄、职位、部门信息。
2. 查询年龄小于30岁的员工姓名、年龄、职位、部门信息。
3. 查询拥有员工的部门ID、部门名称。
4. 查询所有年龄大于40岁的员工，及其归属的部门名称；如果员工没有分配部门，也要展示出来。
5. 查询所有员工的工资等级。
6. 查询“研发部”所有员工的信息及工资等级。
7. 查询“研发部”员工的平均工资。
8. 查询工资比“灭绝”高的员工信息。
9. 查询比平均薪资高的员工信息。
10. 查询低于本部门平均工资的员工信息。
11. 查询所有的部门信息，并统计部门的员工人数。
12. 查询所有学生的选课情况，展示出学生名称，学号，课程名称。

-- 1. 查询员工的姓名、年龄、职位、部门信息。(隐式内连接)
-- 表结构：emp,dept
-- 连接条件：emp.dept_id = dept.id
select e.name, e.age, e.job, d.name from emp e, dept d where e.dept_id = d.id;  #隐式内连接
select e.name, e.age, e.job, d.name from emp e join dept d on e.dept_id = d.id; #显式内连接

-- 2. 查询年龄小于30岁的员工姓名、年龄、职位、部门信息。(显式内连接)
-- 表结构：emp,dept
-- 连接条件：emp.dept_id = dept.id
select e.name, e.age, e.job, d.* from (select * from emp where age < 30) e inner join dept d on e.dept_id = d.id; #表子查询，显式内连接
select e.name, e.age, e.job, d.* from emp e inner join dept d on e.dept_id = d.id where age < 30; #显式内连接

-- 3. 查询拥有员工的部门ID、部门名称。
-- 表结构：emp,dept
-- 连接条件：emp.dept_id = dept.id
select * from dept where id in (select dept_id from emp); #列子查询
select distinct d.id, d.name from dept d, emp e where d.id = e.dept_id; #隐式内连接

-- 4. 查询所有年龄大于40岁的员工，及其归属的部门名称；如果员工没有分配部门，也要展示出来。
-- 表结构：emp,dept
-- 连接条件：emp.dept_id = dept.id
select e.*,d.name from (select * from emp where age > 40) e left join dept d on e.dept_id = d.id;#表子查询、外连接
select e.*,d.name from emp e left join dept d on e.dept_id = d.id where age > 40;#外连接


-- 5. 查询所有员工的工资等级。
-- 表结构：emp,salary_grade
-- 连接条件：emp.salary >= salary_grade.losal && emp.salary <= salary_grade.hisal
select e.name, s.grade from emp e,salary_grade s where e.salary >= s.losal and e.salary <= s.hisal; #隐式内连接
select e.name, s.grade from emp e,salary_grade s where e.salary between s.losal and s.hisal; #隐式内连接

-- 6. 查询“研发部”所有员工的信息及工资等级。
-- 表结构：emp,salary_grade,dept (查询N个表则会有N-1个连接条件)
-- 连接条件：emp.salary between salary_grade.losal and salary_grade.hisal,emp.dept_id = dept.id
-- 查询条件：dept.name = '研发部'
select e.*, s.grade from emp e,salary_grade s where e.salary between s.losal and s.hisal and e.dept_id = (select id from dept where name = '研发部'); #隐式内连接，标量子查询
select e.*, s.grade from emp e,dept d,salary_grade s where (e.salary between s.losal and s.hisal) and e.dept_id = d.id and d.name = '研发部'; #隐式内连接

-- 7. 查询“研发部”员工的平均工资。
-- 表结构：emp,dept
-- 连接条件：emp.dept_id = dept.id
-- 查询条件：dept.name = '研发部'
select avg(salary) avgSalary from emp where dept_id = (select id from dept where name = '研发部');#标量子查询
select avg(e.salary) avgSalary from emp e, dept d where e.dept_id = d.id and d.name = '研发部';#隐式内连接

-- 8. 查询工资比“灭绝”高的员工信息。
select * from emp where salary > (select salary from emp where name = '灭绝');#标量子查询

-- 9. 查询比平均薪资高的员工信息。
select * from emp where salary > (select avg(salary) from emp);#标量子查询

-- 10. 查询低于本部门平均工资的员工信息。
-- a.查询指定部门平均工资
select avg(e1.salary) from emp e1 where e1.dept_id = 1;
select avg(e1.salary) from emp e1 where e1.dept_id = 2;
-- b.查询低于本部门平均工资的员工信息
select * from emp e where e.salary < (select avg(salary) from emp e1 where  e1.dept_id = e.dept_id) ;

-- 11. 查询所有的部门信息，并统计部门的员工人数。
select d.id, d.name, (select count(*) from emp e where e.dept_id = d.id) '人数' from dept d;

-- 12. 查询所有学生的选课情况，展示出学生名称，学号，课程名称。
-- 表结构：student,course,student_course
-- 连接条件：student.id = student_course.studentid,course.id = student_course.courseid
select s.name, s.no, c.name from student s, course c, student_course sc where s.id = sc.studentid and c.id = sc.courseid;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65