mysql--④多表操作_alter table 多个表

• 多表关系

一对一（一般是合并表），
一对多/多对一（部门和员工），
多对多（学生和课程）－－用中间表

• 外键约束概念

专门用于多表关系的约束
通过主表的主键来控制从表的外键

外键约束：

1、主表必须已经存在，或者正在创建
2、必须要给主表设置主键列
3、主键不能包括空值，但是外键可以包括空值
4、在主表的表名后面指定的列明或者列名的组合。 这个列或列的组合必须是主表的主键或者候选键
5、外键中列的数目必须和主键中列的数目相同
6、外键中列的数据类型必须和主键中对应列的数据类型相同

• 创建外键约束 foreign key
在创建表之前添加外键约束

在创建表之后添加外键约束

• 验证外键约束的作用

1、数据插入：
要先给主表添加约束
从表添加约束要依赖于主表，主表没有的数据不能添加

2、数据删除
主表的数据被从表依赖时，不能删除，否则可以删除
从表的数据可以任意删除

eg：

delete from dept where deptno = '1001';-----不可以删除（被依赖了）

delete from dept where deptno = '1004';------可以删除

delete from emp where eid = '7';  -----可以删除
1
2
3
4
5

• 删除外键约束

删除之后，表与表之间就没有关系了
语法：

alter table 表名字 drop foreign key 外键约束名

alter table emp2 drop foreign key emp2_fk;
1
2
3

• 多对多关系－构建外键约束
A表的一行对应B表的多行，B表的一行对应A表的多行，这时候需要再重新建立一个中间表，记录表关系

注意：
修改和删除时，中间从表可以随便删除和修改，但是两边从表手主表依赖的数据不能删除或者修改。

多表联合查询（非常重要）

• 概念
就是同时 查询两个或者两个以上的表，因为有时候用户在查看数据的时候，需要显示的数据来自多张表

• 数据准备
注意：
外键约束只对数据的增删改起作用，对于数据查询没有影响

• 交叉连接查询 select * from A ，B；－－－会产生冗余数据

1、交叉连接查询返回返回被连接的两个表所有数据行的笛卡尔积
2、笛卡尔集可以理解为一张表的每一行去和另外一张表的任意一行进行匹配
3、假如A表有 m 行数据，B表有 n 行数据，则返回 m * n 行数据
4、笛卡尔积会产生很多冗余的数据，后期的其他查询可以在该集合的基础上进行条件筛选

• 内连接查询

求的是两个表之间的交集

inner 可以省略

隐式内连接（SQL92标准）：

select * from A,B where 条件；
1

显式内连接（SQL99标准）；

select * from A inner join B on 条件
1

－－－查询每个部门的所属员工  //隐式内连接
1

select* from dept3,emp3 where dept3.deptno = emp3.dept_id;  //这样写标准
1

还可以给表起别名，如；

select* from dept3  a ,emp3 b  where a.deptno = b.dept_id;
1

	－－－查询每个部门的所属员工  //显式内连接
select *from dept3 inner join emp3  on dept3.deptno = emp3.dept_id;  //这样写标准
1
2

还可以给表起别名，如；

select *from dept3 a join emp3 b on a.deptno = b.dept_id;
1

 －－－

• 外连接查询

分为：（outer可以 省略）
左外连接left outer join、

select* from A left outer join B on 条件；
1

右外连接right outer join、

select* from A right outer join B on 条件；
1

满外连接full outer join

select* from A full outer join B on 条件;
1

注意：
Oracle 里面有 full join ,可是在mysql 对 full join 支持的不好，我们可以使用 union来达到目的

－－－－外连接查询
－－－－查询哪些部门有员工，哪些部门没有员工

use mydb3;
select* from dept3 left outer join emp3 on dept3.deptno =emp3.dept_id;
1
2

－－－－查询哪些员工有对应的部门，哪些没有

select* from dept3 right outer join emp3 on dept3.deptno =emp3.dept_id;
1

－－－－使用 union 关键字实现左外连接和右外连接的并集

select* from dept3 left outer join emp3 on dept3.deptno=emp3.dept_id
union
select* from dept3 right outer join emp3 on dept3.deptno =emp3.dept_id;
1
2
3

－－－－外连接查询
－－－－查询哪些部门有员工，哪些部门没有员工

usemydb3;
select* from dept3 a left outer join emp3 b on a.deptno = b.dept.id
select* from dept3 a left join emp3 b on a.deptno = b.dept_id;
1
2
3

－－－－外连接多个表

select* from A
left join B on 条件1
left join C on 条件2
left join D on 条件3；
 
1
2
3
4
5

－－－－查询哪些员工有对应的部门，哪些没有

select * from dept3 a right outer join emp3 b on a.deptno = b.dept_id;
select* from dept3 a right join emp3 b on a.deptno = b,dept_id;

select
*
from A
right joinB on条件1，
right joinC on条件2，
right joinD on条件3；
1
2
3
4
5
6
7
8
9

－－－－实现满外连接： full join
－－－－使用 union 关键字实现左外连接和右外连接的并集
－－－－select * from dept3 a full join emp3 b on a.deptno = b.dept_id; －－不能执行

－－－－union是将两个查询结果上下拼接，并去重

select* from dept3 a left join emp3 b on a.deptno = b.dept_id
union
select* from dept3 a right join emp3 b on a.deptno = b.dept_id  
1
2
3

－－－－union all 是将两个查询结果上下拼接，不去重

select* from dept3 a left join emp3 b on a.deptno = b.dept_id
union all
select* from dept3 a right join emp3 b on a.deptno= b.dept_id
1
2
3

• 基本子查询
• 子查询关键字－ALL
• 子查询关键字－ANY ,SOME
• 子查询关键字－IN
• 子查询关键字－EXISTS
• 自关联查询

总结

练习