MySQL-多表关系、多表查询
一. 一对多(多对一)
1. 例如；一个部门下有多个员工
在数据库表中多的一方(员工表)、添加字段，来关联一的一方(部门表)的主键
二. 外键约束
1.如将部门表的部门直接删除，然而员工表还存在其部门下的员工，出现了数据的不一致问题，是因为在数据库层面，员工表与部门表并未建立关联，所以无法保证数据的一致性和完整性。此时就需要外键约束
可以在创建表时 或 表结构创建完成后，为字段添加外键约束：
例如：
ALTER TABLE EMP ADD CONSTRAINT FK_EMP_DEPT_id foreign key (dept_id) references dept(id);
-- 创建表时指定
create table 表名(字段名 数据类型,...[constraint] [外键名称] foreign key (外键字段名) references 主表 (字段名));-- 建表完成后，添加外键
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表 (字段名);物理外键：
使用foreign key 定义外键关联另外一张表（已经被逻辑外键取代）
缺点：① 影响增、删、改的效率(需要检查外键关系)
② 仅用于单节点数据库，不适用与分布式、集群场景。
③ 容易引发数据库的死锁问题，消耗性能
逻辑外键：
在业务层逻辑中，解决外键关联，通过逻辑外键，就可以很方便的解决上述问题-----推荐使用
三.一对一
1. 关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率。
2. 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)
四.多对多
1. 例如；学生与课程之间的关系，一个学生可以选择多门课程，一门课程也可以供多个学生选择
2. 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
五. 多表查询
从多张表中查询数据
1. 笛卡尔积
指在数学中，两个集合（A集合和B集合）的所有组合情况--
select * from emp, dept;
在多表查询时，需要消除无效的笛卡尔积。
select * from emp, dept where emp.dept_id = dept.id;
2. 连接查询
内连接
相当于查询A、B两表交集的部分数据。
-- 1. 隐式内连接 (常用)
select 字段列表 from 表1, 表2 where 连接条件...;-- 2. 显示内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件 ..;-- 给表起别名
select 字段列表 from 表1 [as] 别名1， 表2 [as] 别名2 where 条件...;-- 例如
select emp.id, emp.name,dept.name from emp, dept where emp.dept_id = dept.id;
select emp.id, emp.name,dept.name from emp inner join dept on emp.dept_id = dept.id;
select emp.id, emp.name,dept.name from emp join dept on emp.dept_id = dept.id;select e.id, e.name,d.name from emp e, dept d where e.dept_id = d.id and e.salary > 5000;
select e.id, e.name,d.name from emp e inner join dept d on e.dept_id = d.id where e.salary > 5000;
外连接
左外连接
查询左表所有数据(包括两张表交集部分的数据)
右外连接
查询右表所有数据(包括两张表交集部分的数据)
-- 左外连接 (常见)
select 字段列表 from 表1 left [outer] join 表2 on 连接条件...;-- 右外连接
select 字段列表 from 表1 right [outer] join 表2 on 连接条件...;-- 左外连接 包含左表所有数据
select e.name,d.name from emp e left join dept d on e.dept_id = d.id;
select e.name,d.name,e.salary from emp e left join dept d on e.dept_id = d.id where e.salary > 5000;-- 右外连接 包含右表表所有数据
select d.name,e.name from emp e right join dept d on d.id = e.dept_id对于外连接，常用的是左外连接，因为右外连接的SQL也可以改造成左外连接（两张表换个顺序）
3. 子查询
(1) SQL语句中嵌套select语句，称为嵌套查询，又称子查询
(2) 格式：select * from 表1 where 字段 = (select 字段 for 表2...)
-- 子查询
select * from emp where emp.entry_date = (select min(e.entry_date) from emp e) ;(3) 说明：子查询外部的语句可以是insert / update /delete /select 的任何一个，常见的是select
(4) 分类：
① 标量子查询：子查询返回的结果为单个值
② 列子查询：子查询返回的结果为一列
③ 行子查询：子查询返回的结果为一行
④ 表子查询：子查询返回的结果为多行多列
-- 例如-- 标量子查询
select * from emp where emp.entry_date = (select min(e.entry_date) from emp e) ;
select * from emp where emp.entry_date > (select e.entry_date from emp e where e.name = '武松' ) ;-- 列子查询
select e.* from emp e where e.dept_id in (select d.id from dept d where d.name in ('人事部','就业部'));-- 行子查询
select * from emp where (salary, job) = (select salary, job from emp where emp.name = '武松');-- 表子查询
select e.* from emp e, (select dept_id, max(salary) maxSa from emp group by dept_id) e2where e.dept_id = e2.dept_id and e.salary = e2.maxSa;