MySQL多表查询
一、多表关系
1.1 一对一关系
案例：用户和用户详情
关系：一对一多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提高效率
实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（unique）
1.2 一对多关系
案例：部门与员工的关系
关系：一个部门对应多个员工，一个员工对应一个部门
实现：在多的一方建立外键，指向一的一方的主键
1.3 多对多关系
案例：学生与课程的关系
关系：一个学生可以选修多们课程，一门课程也可以供多个学生选择
实现：建立第三张中间表，中间表至少包含两个外键，分别关联两个主键
二、多表查询
2.1 介绍
概念：从多表中查询数据
笛卡尔积：在数学中，两个集合A和B所有的组合情况，A*B（多表查询时，通过外键消除无效的笛卡尔积）
#多表查询语法(消除笛卡尔积)
select * from 表1，表2 where 外键关系2.2 多表查询的分类
2.2.1 连接查询
内连接：相当于查询两张表交集部分数据
#隐式内连接
select 字段列表 from 表1，表2 where 条件
#显式内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件外连接
#左外连接：查询左表所有数据，以及两张表交集部分数据,相当于查询表1(左表)的所有数据包含表1和表2交集部分数据
select 字段列表 from 表1 left [outer] join 表2 on 条件....
#右外连接：查询右表所有数据，以及两张表交集部分数据,相当于查询表2(右表)的所有数据包含表1和表2交集部分数据
select 字段列表 from 表1 right [outer] join 表2 on 条件....自连接：当前表与自身的连接查询，自连表必须使用表别名，可以是内连接也可以是外连接
select 字段列表 from 表A 别名A join 表A 别名B on 条件联合查询(union/union all)：对于union查询，就是把多次查询的结果合并，形成一个新的查询结果
#联合查询表A和表B数据
select 字段列表 from 表A....
union [all]
select 字段列表 from 表B....注意：
- 1.多张表的列数必须保持一致，字段类型也要保持一致
- 2.union：去除重复数据，union all：不去除重复数据，直接显示所有数据
2.2.2 子查询
SQL(insert/update/delete/select)语句中嵌套select语句，称为嵌套查询，又叫子查询
select * from tb1 where column1=(select column1 from tb2);1. 标量子查询
标量子查询：子查询返回的结果是单个值(数字，字符串，日期等)，是最简单的形式
常用操作符：=、<>、 >、 >= 、< 、<=
#例题：查询销售部所有员工的信息，部门表(dept)和员工表(emp)通过外键部门ID(dept_id)关联
select * from emp where dept_id=(select id from dept);2. 列子查询
列子查询：子查询返回结果是一列(一列可以有多行)
常用操作符：
in：在指定集合范围之内，多选一
not in：不在指定的集合范围之内
any：子查询返回列表中，有任意一个满组即可 例如：where 条件字段 > any/some ()
some：与any等同，使用some的地方都可以使用any
all：子查询返回列表的所有值都必须满足 例如：比所有人都大 where 条件字段 > all ()
#例题1：查询市场部和销售部所有员工的信息，部门表(dept)和员工表(emp)通过外键部门ID(dept_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 in (select id from dept where name="财务部"))3. 行子查询
行子查询：子查询返回的结果是一行(一行可以有多列)
常用操作符：=、<>、in、not in
#例题：查询与张三工资和直属部门领导相同的员工信息
select * from emp where (salary,manageid)=(select salary,manageid from emp where name='张三');4. 表子查询
表子查询：子查询结果为多行多列
常用操作符：in
#例题1：查询查询与张三和李四职位和工资相同的员工信息
select * from emp where (salary,job) in (select salary,job from emp where name='张三' or name='李四');
#例题2：查询入职日期是"2006-01-01"之后的员工信息，以及部门信息(将子查询结果当成一张新的SQL表)
select e.*,d.* from (select * from emp where enterdate > '2006-01-01') as e left join dept as d on e.dept_id=d.id;根据子查询位置分为：where之后、from之后、select之后