Python学习之复习MySQL-Day7（多表查询）

---

文章声明???

1. 该文章为我（有编程语言基础，非编程小白）的 MySQL复习笔记
2. 知识来源为 B站UP主（黑马程序员）的MySQL课程视频，归纳为自己的语言与理解记录于此并加以实践
3. 此前我已经学习过了MySQL，现在是在复习阶段，所以不是面向小白的教学文章
4. 不出意外的话，我大抵会 持续更新
5. 想要了解前端开发（技术栈大致有：Vue2/3、微信小程序、uniapp、HarmonyOS、NodeJS、Typescript）与Python的小伙伴，可以关注我！谢谢大家！

---

让我们开始今天的学习吧！

多表查询

多表关系

由于业务需求、业务模块之间存在着很多关系，所以各个表结构之间也存在着各种联系，基本分为以下三种：

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

一对多（多对一）

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

多对多

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

一对一

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

---

多表查询概述

概念

从多张表中查询数据

笛卡尔积

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

实例演示

mysql> select * from emp;
+----+--------+------+--------+---------+
| id | name   | age  | gender | dept_id |
+----+--------+------+--------+---------+
|  7 | Richie |   21 | 男     |       1 |
|  8 | Taylor |   34 | 女     |       2 |
|  9 | Mike   |   26 | 男     |       3 |
| 10 | Lucy   |   74 | 女     |       2 |
| 11 | Jack   |   45 | 男     |       2 |
| 12 | Nancy  |   19 | 女     |       1 |
+----+--------+------+--------+---------+
6 rows in set (0.00 sec)

mysql> select * from dept;
+----+-----------+
| id | dept_name |
+----+-----------+
|  3 | 保安部    |
|  1 | 职能部    |
|  2 | 销售部    |
+----+-----------+
3 rows in set (0.00 sec)

mysql> select * from emp,dept; # 下方出现了笛卡尔积现象，总共6*3=18条数据
+----+--------+------+--------+---------+----+-----------+
| id | name   | age  | gender | dept_id | id | dept_name |
+----+--------+------+--------+---------+----+-----------+
|  7 | Richie |   21 | 男     |       1 |  2 | 销售部    |
|  7 | Richie |   21 | 男     |       1 |  1 | 职能部    |
|  7 | Richie |   21 | 男     |       1 |  3 | 保安部    |
|  8 | Taylor |   34 | 女     |       2 |  2 | 销售部    |
|  8 | Taylor |   34 | 女     |       2 |  1 | 职能部    |
|  8 | Taylor |   34 | 女     |       2 |  3 | 保安部    |
|  9 | Mike   |   26 | 男     |       3 |  2 | 销售部    |
|  9 | Mike   |   26 | 男     |       3 |  1 | 职能部    |
|  9 | Mike   |   26 | 男     |       3 |  3 | 保安部    |
| 10 | Lucy   |   74 | 女     |       2 |  2 | 销售部    |
| 10 | Lucy   |   74 | 女     |       2 |  1 | 职能部    |
| 10 | Lucy   |   74 | 女     |       2 |  3 | 保安部    |
| 11 | Jack   |   45 | 男     |       2 |  2 | 销售部    |
| 11 | Jack   |   45 | 男     |       2 |  1 | 职能部    |
| 11 | Jack   |   45 | 男     |       2 |  3 | 保安部    |
| 12 | Nancy  |   19 | 女     |       1 |  2 | 销售部    |
| 12 | Nancy  |   19 | 女     |       1 |  1 | 职能部    |
| 12 | Nancy  |   19 | 女     |       1 |  3 | 保安部    |
+----+--------+------+--------+---------+----+-----------+
18 rows in set (0.00 sec)

mysql> select * from emp,dept where emp.dept_id = dept.id; # 添加条件即可消除笛卡尔积
+----+--------+------+--------+---------+----+-----------+
| id | name   | age  | gender | dept_id | id | dept_name |
+----+--------+------+--------+---------+----+-----------+
|  9 | Mike   |   26 | 男     |       3 |  3 | 保安部    |
|  7 | Richie |   21 | 男     |       1 |  1 | 职能部    |
| 12 | Nancy  |   19 | 女     |       1 |  1 | 职能部    |
|  8 | Taylor |   34 | 女     |       2 |  2 | 销售部    |
| 10 | Lucy   |   74 | 女     |       2 |  2 | 销售部    |
| 11 | Jack   |   45 | 男     |       2 |  2 | 销售部    |
+----+--------+------+--------+---------+----+-----------+
6 rows in set (0.00 sec)

多表查询分类

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

---

内连接

内连接简介

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

内连接查询语法

隐式内连接：

select 字段列表 from 表1,表2 where 连接条件;

显式内连接：

select 字段列表 from 表1 [inner] join 表2 on 连接条件;

实例演示

mysql> select emp.name,dept.dept_name from emp,dept where emp.dept_id = dept.id; # 查询每一个员工的姓名以及他们部门的名 称（隐式内连接实现）
+--------+-----------+
| name   | dept_name |
+--------+-----------+
| Mike   | 保安部    |
| Richie | 职能部    |
| Nancy  | 职能部    |
| Taylor | 销售部    |
| Lucy   | 销售部    |
| Jack   | 销售部    |
+--------+-----------+
6 rows in set (0.00 sec)

mysql> select e.name,d.dept_name from emp e inner join dept d on e.dept_id = d.id; # 查询每一个员工的姓名以及他们部门的
名称（显式内连接实现）
+--------+-----------+
| name   | dept_name |
+--------+-----------+
| Richie | 职能部    |
| Taylor | 销售部    |
| Mike   | 保安部    |
| Lucy   | 销售部    |
| Jack   | 销售部    |
| Nancy  | 职能部    |
+--------+-----------+
6 rows in set (0.00 sec)

---

外连接

外连接查询语法

左外连接：查询左表所有数据，以及两张表交集部分的数据，语法如下：

select 字段列表 from 表1 left [outer] join 表2 on 条件;

右外连接：查询右表所有数据，以及两张表交集部分的数据，语法如下：

select 字段列表 from 表1 right [outer] join 表2 on 条件;

实例演示

mysql> select e.*,d.dept_name from emp e left outer join dept d on e.dept_id = d.id; # 查询emp表的所有数据，和对应的部门信息（左外连接）
+----+--------+------+--------+---------+-----------+
| id | name   | age  | gender | dept_id | dept_name |
+----+--------+------+--------+---------+-----------+
|  7 | Richie |   21 | 男     |       1 | 职能部    |
|  8 | Taylor |   34 | 女     |       2 | 销售部    |
|  9 | Mike   |   26 | 男     |       3 | 保安部    |
| 10 | Lucy   |   74 | 女     |       2 | 销售部    |
| 11 | Jack   |   45 | 男     |       2 | 销售部    |
| 12 | Nancy  |   19 | 女     |       1 | 职能部    |
+----+--------+------+--------+---------+-----------+
6 rows in set (0.00 sec)

mysql> select e.name,d.* from emp e right outer join dept d on e.dept_id = d.id; # 查询dept表的所有数据，和对应的员工姓名信息（右外连接）
+--------+----+-----------+
| name   | id | dept_name |
+--------+----+-----------+
| Mike   |  3 | 保安部    |
| Nancy  |  1 | 职能部    |
| Richie |  1 | 职能部    |
| Jack   |  2 | 销售部    |
| Lucy   |  2 | 销售部    |
| Taylor |  2 | 销售部    |
+--------+----+-----------+
6 rows in set (0.00 sec)

mysql> select e.*,d.* from emp e right outer join dept d on e.dept_id = d.id; # 查询dept表的所有数据，和对应的员工信息（右外连接）
+------+--------+------+--------+---------+----+-----------+
| id   | name   | age  | gender | dept_id | id | dept_name |
+------+--------+------+--------+---------+----+-----------+
|    9 | Mike   |   26 | 男     |       3 |  3 | 保安部    |
|   12 | Nancy  |   19 | 女     |       1 |  1 | 职能部    |
|    7 | Richie |   21 | 男     |       1 |  1 | 职能部    |
|   11 | Jack   |   45 | 男     |       2 |  2 | 销售部    |
|   10 | Lucy   |   74 | 女     |       2 |  2 | 销售部    |
|    8 | Taylor |   34 | 女     |       2 |  2 | 销售部    |
+------+--------+------+--------+---------+----+-----------+
6 rows in set (0.00 sec)

---

自连接

自连接语法

自连接查询可以时内连接查询也可以是外连接查询，语法如下：

select 字段列表 from 表A 别名A join 表A 别名B on 条件; 

实例演示

mysql> select * from user;
+----+--------+------+-----------+
| id | name   | age  | boss_name |
+----+--------+------+-----------+
|  1 | Richie |   21 | Mike      |
|  2 | Mike   |   87 | NULL      |
|  3 | Nancy  |   19 | Richie    |
|  4 | Taylor |   34 | Richie    |
|  5 | Jack   |   45 | Taylor    |
|  6 | Lucy   |   23 | Taylor    |
+----+--------+------+-----------+
6 rows in set (0.00 sec)

mysql> select a.name,b.name from user a,user b where a.boss_name = b.name; # 查询员工的及其领导的名字
+--------+--------+
| name   | name   |
+--------+--------+
| Richie | Mike   |
| Nancy  | Richie |
| Taylor | Richie |
| Jack   | Taylor |
| Lucy   | Taylor |
+--------+--------+
5 rows in set (0.00 sec)

mysql> select a.name,b.name from user a left join user b on a.boss_name = b.name; # 查询员工的及其领导的名字，员工没有领导也要查询出来
+--------+--------+
| name   | name   |
+--------+--------+
| Richie | Mike   |
| Mike   | NULL   |
| Nancy  | Richie |
| Taylor | Richie |
| Jack   | Taylor |
| Lucy   | Taylor |
+--------+--------+
6 rows in set (0.00 sec)

---

子查询

概念

SQL 语句中嵌套 select 语句，称为嵌套语句，又称子查询

子查询的外部语句可以是 insert、update、delete、select（最多）

语法

select * from table1 where column1 = (select column2 from table2); 

分类

根据子查询结果不同，可以分为：

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

根据子查询位置，可以分为：

• where 之后
• from 之后
• select 之后

标量子查询

标量子查询简介

子查询返回的结果是单个值（数字、字符串、日期等）最简单的形式，这种子查询称为标量子查询
常用操作符为：=、<>、>、>=、<、<=

实例演示

mysql> # 查询销售部的所有员工信息
mysql> select * from emp where dept_id = (select id from dept where dept_name = '销售部');
+----+--------+------+--------+---------+
| id | name   | age  | gender | dept_id |
+----+--------+------+--------+---------+
|  8 | Taylor |   34 | 女     |       2 |
| 10 | Lucy   |   74 | 女     |       2 |
| 11 | Jack   |   45 | 男     |       2 |
+----+--------+------+--------+---------+
3 rows in set (0.01 sec)

列子查询

列子查询简介

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

常用的操作符有：

操作符	描述
in	在指定的集合范围之内，多选一
not in	不在指定的集合范围之内
any/some	子查询返回列表中，有任意一个满足即可
all	子查询返回列表的所有值都必须满足

实例演示

mysql> # 查询保安部和销售部的员工信息
mysql> select * from emp where dept_id in (select id from dept where dept_name in ('保安部','销售部'));
+----+--------+------+--------+---------+
| id | name   | age  | gender | dept_id |
+----+--------+------+--------+---------+
|  9 | Mike   |   26 | 男     |       3 |
|  8 | Taylor |   34 | 女     |       2 |
| 10 | Lucy   |   74 | 女     |       2 |
| 11 | Jack   |   45 | 男     |       2 |
+----+--------+------+--------+---------+
4 rows in set (0.03 sec)

mysql> # 查询比职能部所有人年龄都大的员工信息
mysql> select * from emp where age > all (select age from emp where dept_id = (select id from dept where dept_name = '职能部'));
+----+--------+------+--------+---------+
| id | name   | age  | gender | dept_id |
+----+--------+------+--------+---------+
|  8 | Taylor |   34 | 女     |       2 |
|  9 | Mike   |   26 | 男     |       3 |
| 10 | Lucy   |   74 | 女     |       2 |
| 11 | Jack   |   45 | 男     |       2 |
+----+--------+------+--------+---------+
4 rows in set (0.03 sec)

mysql> select * from emp;
+----+--------+------+--------+---------+
| id | name   | age  | gender | dept_id |
+----+--------+------+--------+---------+
|  7 | Richie |   21 | 男     |       1 |
|  8 | Taylor |   34 | 女     |       2 |
|  9 | Mike   |   26 | 男     |       3 |
| 10 | Lucy   |   74 | 女     |       2 |
| 11 | Jack   |   45 | 男     |       2 |
| 12 | Nancy  |   19 | 女     |       1 |
+----+--------+------+--------+---------+
6 rows in set (0.00 sec)

行子查询

行子查询简介

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

常用操作符为：=、<>、in、not in

实例演示

mysql> # 查询与Taylor的性别以及部门id相同的员工信息
mysql> select gender,dept_id from emp where name = 'Taylor';
+--------+---------+
| gender | dept_id |
+--------+---------+
| 女     |       2 |
+--------+---------+
1 row in set (0.00 sec)

mysql> select * from emp where (gender,dept_id) = ('女',2);
+----+--------+------+--------+---------+
| id | name   | age  | gender | dept_id |
+----+--------+------+--------+---------+
|  8 | Taylor |   34 | 女     |       2 |
| 10 | Lucy   |   74 | 女     |       2 |
+----+--------+------+--------+---------+
2 rows in set (0.00 sec)

mysql> select * from emp where (gender,dept_id) = (select gender,dept_id from emp where name = 'Taylor');
+----+--------+------+--------+---------+
| id | name   | age  | gender | dept_id |
+----+--------+------+--------+---------+
|  8 | Taylor |   34 | 女     |       2 |
| 10 | Lucy   |   74 | 女     |       2 |
+----+--------+------+--------+---------+
2 rows in set (0.00 sec)

表子查询

表子查询简介

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

常用的操作符有：in

实例演示

mysql> select * from emp;
+----+--------+------+--------+---------+
| id | name   | age  | gender | dept_id |
+----+--------+------+--------+---------+
|  7 | Richie |   21 | 男     |       1 |
|  8 | Taylor |   34 | 女     |       2 |
|  9 | Mike   |   26 | 男     |       3 |
| 10 | Lucy   |   74 | 女     |       2 |
| 11 | Jack   |   45 | 男     |       2 |
| 12 | Nancy  |   19 | 女     |       1 |
| 13 | Doggy  |   21 | 男     |       1 |
+----+--------+------+--------+---------+
7 rows in set (0.00 sec)

mysql> # 查询与Taylor，Richie的性别和部门id相同的员工信息
mysql> select * from emp where (gender,dept_id) in
    -> (select gender,dept_id from emp where name = 'Taylor' or name = 'Richie');
+----+--------+------+--------+---------+
| id | name   | age  | gender | dept_id |
+----+--------+------+--------+---------+
|  7 | Richie |   21 | 男     |       1 |
|  8 | Taylor |   34 | 女     |       2 |
| 10 | Lucy   |   74 | 女     |       2 |
| 13 | Doggy  |   21 | 男     |       1 |
+----+--------+------+--------+---------+
4 rows in set (0.03 sec)

mysql> # 查询性别是男的员工信息以及他们的相关部门信息
mysql> select * from emp where gender = '男'; # 学会拆分需求
+----+--------+------+--------+---------+
| id | name   | age  | gender | dept_id |
+----+--------+------+--------+---------+
|  7 | Richie |   21 | 男     |       1 |
|  9 | Mike   |   26 | 男     |       3 |
| 11 | Jack   |   45 | 男     |       2 |
| 13 | Doggy  |   21 | 男     |       1 |
+----+--------+------+--------+---------+
4 rows in set (0.00 sec)

mysql> select e.*,d.* from (select * from emp where gender = '男') e left join dept d on e.dept_id = d.id;
+----+--------+------+--------+---------+------+-----------+
| id | name   | age  | gender | dept_id | id   | dept_name |
+----+--------+------+--------+---------+------+-----------+
|  7 | Richie |   21 | 男     |       1 |    1 | 职能部    |
|  9 | Mike   |   26 | 男     |       3 |    3 | 保安部    |
| 11 | Jack   |   45 | 男     |       2 |    2 | 销售部    |
| 13 | Doggy  |   21 | 男     |       1 |    1 | 职能部    |
+----+--------+------+--------+---------+------+-----------+
4 rows in set (0.00 sec)

---

联合查询

简介

联合查询可以把多次查询的结果合并起来，并形成一个新的查询结果集

联合查询的多张表的列数必须保持一致并且字段类型也需要保持一致

union all 会把所有数据合并在一起不考虑去重，而 union 会给合并的数据去重

语法

# union 加 all 则不会去重，单独一个union会给上下两个结果去重再合并
select 字段列表 from 表A ... union [all] select 字段列表 from 表B ... ;

实例演示

# 既查询薪资低于5000的员工，也查询年龄大于50岁的员工并将查询结果合并起来，同时满足薪资低于5000和年龄大于50的员工会出现两次，因为 union all 不去重
mysql> select * from emp where salary < 5000 union all
    -> select * from emp where age > 50;

# 既查询薪资低于5000的员工，也查询年龄大于50岁的员工并将查询结果合并起来，同时满足薪资低于5000和年龄大于50的员工只会出现一次，因为 union 去重
mysql> select * from emp where salary < 5000 union
    -> select * from emp where age > 50;