多表查询
定义
多表查询就是指从多张表中查询数据
多表关系
一对多
案例：部门 与 员工的关系
关系：一个部门对应多个员工，一个员工对应一个部门
实现：在多的一方建立外键，指向一的一方的主键
多对多
案例：学生 与 课程的关系
关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
一对一
案例：用户 与 用户详情的关系
关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)
笛卡尔积
笛卡尔乘积是指在数学中，两个集合A集合 和 B集合的所有组合情况。
而在多表查询中，我们是需要消除无效的笛卡尔积的，只保留两张表关联部分的数据。
多表查询
- 内连接：相当于查询A、B交集部分数据
- 外连接：外连接分为两种，分别是：左外连接 和 右外连接。
- 左外连接：查询左表所有数据，以及两张表交集部分数据。
- 右外连接：查询右表所有数据，以及两张表交集部分数据。
- 自连接：当前表与自身的连接查询，自连接必须使用表别名
内连接
内连接查询的是两张表交集部分的数据。(也就是绿色部分的数据)
内连接的语法分为两种隐式内连接、显式内连接
隐式内连接
SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;显式内连接
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;外连接
外连接分为两种，分别是：左外连接(A + 绿色部分的数据) 和 右外连接(B + 绿色部分的数据)。
左外连接
SELECT
字段列表
FROM 表1 LEFT [ OUTER ] JOIN 表2
ON
条件 ... ;左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。
右外连接
SELECT
字段列表
FROM 表1 RIGHT [ OUTER ] JOIN 表2
ON 条件 ... ;右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。
注意事项
左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺序就可以了。而我们在日常开发使用时，更偏向于左外连接。
自连接
自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。
SELECT
字段列表
FROM 表A 别名A JOIN 表A 别名B
ON 条件 ... ;对于自连接查询，可以是内连接查询，也可以是外连接查询。
注意事项
在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底是哪一张表的字段。
联合查询
对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。
举例
将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来
select * from emp where salary < 5000
union all
select * from emp where age > 50;union all：查询出来的结果，仅仅进行简单的合并，并未去重。
union：联合查询，会对查询出来的结果进行去重处理。
子查询
SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT的任何一个
子查询分类
根据子查询结果不同
- 标量子查询（子查询结果为单个值）
- 列子查询(子查询结果为一列)
- 行子查询(子查询结果为一行)
- 表子查询(子查询结果为多行多列)
根据子查询位置不同
- WHERE之后
- FROM之后
- SELECT之后
标量子查询
子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。
常用的操作符：= <> > >= < <=
列子查询
子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。
常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL
| 操作符 | 描述 |
|---|---|
| IN | 在指定的集合范围之内，多选一 |
| NOT IN | 不在指定的集合范围之内 |
| ANY | 子查询返回列表中，有任意一个满足即可 |
| SOME | 与ANY等同，使用SOME的地方都可以使用ANY |
| ALL | 子查询返回列表的所有值都必须满足 |
举例
查询 "销售部" 和 "市场部" 的所有员工信息
查询 "销售部" 和 "市场部" 的部门ID
select id from dept where name = '销售部' or name = '市场部';根据部门ID，查询员工信息
select * from
emp
where dept_id
in
(select id from dept where name = '销售部' or name = '市场部');行子查询
子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。
常用的操作符：= 、<> 、IN 、NOT IN
举例
查询与 "张无忌" 的薪资及直属领导相同的员工信息
查询 "张无忌" 的薪资及直属领导
select salary, managerid from emp where name = '张无忌';查询与 "张无忌" 的薪资及直属领导相同的员工信息
select * from
emp
where
(salary,managerid)
=
(select salary, managerid from emp where name = '张无忌');表子查询
子查询返回的结果是多行多列(相当于一张表)，这种子查询称为表子查询。
常用的操作符：IN
举例
查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
入职日期是 "2006-01-01" 之后的员工信息
select * from emp where entrydate > '2006-01-01';查询这部分员工, 对应的部门信息;
select
e.*, d.*
from
(select * from emp where entrydate > '2006-01-01') e
left join dept d
on e.dept_id = d.id ;