一、外键的约束
创建一个部门表dept，包括部门id，部门名字
mysql> select * from dept;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 1 | 教学部 |
| 2 | 品保部 |
| 3 | 技术部 |
+---------+-----------+
创建一张员工表emp，包括员工id，员工姓名，部门id，地区时间
mysql> select * from emp;
+--------+----------+---------+---------------------+
| emp_id | emp_name | dept_id | reg_time |
+--------+----------+---------+---------------------+
| 1 | laoxing | 1 | 2023-04-10 09:39:49 |
| 2 | sl | 1 | 2023-04-10 09:39:55 |
| 3 | eb | 2 | 2023-04-10 09:40:09 |
| 4 | ej | 2 | 2023-04-10 09:40:13 |
| 5 | js | 3 | 2023-04-10 09:40:19 |
+--------+----------+---------+---------------------+
对这两张表进行增删改查发现：添加员工的时候，可以随便添加，尽管这个部门不存在；删除一个部门，部门下面的员工也还在。这些在实际开发中都是不合理的，造成不合理的原因是，这两张表是没有关系的！！！
怎么让这两张表产生关系呢？使用外键！！！
mysql> create table emp (
-> emp_id int primary key auto_increment,
-> emp_name varchar(32) ,
-> dept_id int,
-> reg_time timestamp default current_timestamp,
-> constraint fk_emp_dept foreign key(dept_id) references dept(dept_id)
-> );
#constraint 约束的意思
#fk_emp_dept 外键的名字 随意起一般是fk和关联的两个表的名字
#foreign key(dept_id) 外键 使用本表中的一个字段作为外键去和别的表产生关系
#references 关联 引用的意思，去关联主表里面的某一个字段（外键所在的表叫副表）
这时，在员工表中插入一条信息，若部门id在部门表中不存在，结果报错：
insert into emp (emp_name, dept_id) values("gd", 4);#不存在dept_id=4
在部门表中删除或者修改一条数据，也可能报错：
delete from dept where dept_id = 1;#在员工表中有与dept_id=1的员工与之对应，不能删
update dept set dept_id = 5 where dept_id = 1;#在员工表中有与dept_id=1的员工与之对应，不能改
加上外键约束以后，增删改 有时会报错 主表（部门表） 副表（员工表）
增加的时候：先看主表，然后增加副表
删除的时候：先删除副表，再删除主表
修改的时候：先修改副表，再修改主表 这样才能不报错
太麻烦了，可以借助级联操作 级联操作！！！级联修改 和 级联删除
mysql> create table emp (
-> emp_id int primary key auto_increment,
-> emp_name varchar(32) ,
-> dept_id int,
-> reg_time timestamp default current_timestamp,
-> constraint fk_emp_dept foreign key(dept_id) references dept(dept_id)
#加的两个状态
-> on delete cascade
-> on update cascade
-> );
mysql> select * from dept;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 1 | 教学部 |
| 2 | 品保部 |
+---------+-----------+
mysql> select * from emp;
+--------+----------+---------+---------------------+
| emp_id | emp_name | dept_id | reg_time |
+--------+----------+---------+---------------------+
| 3 | xueke | 2 | 2023-04-10 10:46:04 |
| 4 | erbei | 2 | 2023-04-10 10:46:08 |
| 5 | erjia | 2 | 2023-04-10 10:46:11 |
+--------+----------+---------+---------------------+
这时再对部门表进行删除操作：
delete from dept where dept_id =1;
#删除了部门表中与dept_id=1相关信息，查看员工表，与部门表dept_id=1对应的员工也删除了
mysql> select * from emp;
+--------+----------+---------+---------------------+
| emp_id | emp_name | dept_id | reg_time |
+--------+----------+---------+---------------------+
| 3 | xueke | 2 | 2023-04-10 10:46:04 |
| 4 | erbei | 2 | 2023-04-10 10:46:08 |
| 5 | erjia | 2 | 2023-04-10 10:46:11 |
+--------+----------+---------+---------------------+
对部门表进行修改：
update dept set dept_id = 4 where dept_id =2;
#修改部门表dept_id=1为dept_id=4，查看员工表，与部门表dept_id=1对应的员工也修改为dept_id=4了
mysql> select * from emp;
+--------+----------+---------+---------------------+
| emp_id | emp_name | dept_id | reg_time |
+--------+----------+---------+---------------------+
| 3 | xueke | 4 | 2023-04-10 10:46:04 |
| 4 | erbei | 4 | 2023-04-10 10:46:08 |
| 5 | erjia | 4 | 2023-04-10 10:46:11 |
+--------+----------+---------+---------------------+
外键约束和增删改有关 查询无关
真实开发的时候，特别是外键约束的表的时候，要加上级联删除和级联修改！！！
二、联表查询
2.1常规的联表查询
mysql> select * from dept;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 4 | 品保部 |
| 5 | 教学部 |
| 6 | 技术部 |
+---------+-----------+
mysql> select * from emp;
+--------+----------+---------+---------------------+
| emp_id | emp_name | dept_id | reg_time |
+--------+----------+---------+---------------------+
| 3 | xueke | 4 | 2023-04-10 10:46:04 |
| 4 | erbei | 4 | 2023-04-10 10:46:08 |
| 5 | erjia | 4 | 2023-04-10 10:46:11 |
| 6 | 东哥 | 5 | 2023-04-10 11:00:59 |
| 7 | 飞哥 | 5 | 2023-04-10 11:01:11 |
+--------+----------+---------+---------------------+
联表查询 查看 erbei 属于哪个部门：
mysql> select emp.emp_name, dept.dept_name#查什么
-> from emp, dept#从哪查
-> where emp.dept_id = dept.dept_id and emp.emp_name = "erbei";#查询条件
+----------+-----------+
| emp_name | dept_name |
+----------+-----------+
| erbei | 品保部 |
+----------+-----------+
对表起表明：
mysql> select e.emp_name as "员工名字", d.dept_name as "部门名字"
-> from emp e, dept d
-> where e.dept_id = d.dept_id and e.emp_name = "erbei";
+----------+-----------+
| emp_name | dept_name |
+----------+-----------+
| erbei | 品保部 |
+----------+-----------+
找出教学部有哪些员工：
mysql> select d.dept_name, e.emp_name
-> from dept d, emp e
-> where d.dept_id = e.dept_id and d.dept_name= "教学部";
+-----------+----------+
| dept_name | emp_name |
+-----------+----------+
| 教学部 | 东哥 |
| 教学部 | 飞哥 |
+-----------+----------+
2.2内连接与外连接
2.2.1内连接
inner join 与join 是相同的。
查看erjia在哪个部门：
mysql> select e.emp_name, d.dept_name #查什么
-> from emp e #从第一个表
-> inner join dept d #关联第二表
-> on e.dept_id = d.dept_id #表之间的连接的字段
-> where e.emp_name = "erjia"; #条件
+----------+-----------+
| emp_name | dept_name |
+----------+-----------+
| erjia | 品保部 |
+----------+-----------+
查看品保部有哪些员工：
mysql> select d.dept_name, e.emp_name
-> from dept d
-> join emp e
-> on d.dept_id = e.dept_id
-> where d.dept_name= "品保部";
+-----------+----------+
| dept_name | emp_name |
+-----------+----------+
| 品保部 | xueke |
| 品保部 | erbei |
| 品保部 | erjia |
+-----------+----------+
2.2.2外连接【开发中用的较少】
外连接分为 左外连接 和 右外连接
LEFT OUTER JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。
mysql> select *
-> from dept d
-> left outer join emp e
-> on d.dept_id = e.dept_id;
+---------+-----------+--------+----------+---------+---------------------+
| dept_id | dept_name | emp_id | emp_name | dept_id | reg_time |
+---------+-----------+--------+----------+---------+---------------------+
| 4 | 品保部 | 3 | xueke | 4 | 2023-04-10 10:46:04 |
| 4 | 品保部 | 4 | erbei | 4 | 2023-04-10 10:46:08 |
| 4 | 品保部 | 5 | erjia | 4 | 2023-04-10 10:46:11 |
| 5 | 教学部 | 6 | 东哥 | 5 | 2023-04-10 11:00:59 |
| 5 | 教学部 | 7 | 飞哥 | 5 | 2023-04-10 11:01:11 |
| 6 | 技术部 | NULL | NULL | NULL | NULL |
| 7 | 总经办 | NULL | NULL | NULL | NULL |
+---------+-----------+--------+----------+---------+---------------------+
RIGHT OUTER JOIN 关键字会从右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。
mysql> select *
-> from emp e
-> right outer join dept d
-> on e.dept_id = d.dept_id;
+--------+----------+---------+---------------------+---------+-----------+
| emp_id | emp_name | dept_id | reg_time | dept_id | dept_name |
+--------+----------+---------+---------------------+---------+-----------+
| 3 | xueke | 4 | 2023-04-10 10:46:04 | 4 | 品保部 |
| 4 | erbei | 4 | 2023-04-10 10:46:08 | 4 | 品保部 |
| 5 | erjia | 4 | 2023-04-10 10:46:11 | 4 | 品保部 |
| 6 | 东哥 | 5 | 2023-04-10 11:00:59 | 5 | 教学部 |
| 7 | 飞哥 | 5 | 2023-04-10 11:01:11 | 5 | 教学部 |
| NULL | NULL | NULL | NULL | 6 | 技术部 |
| NULL | NULL | NULL | NULL | 7 | 总经办 |
+--------+----------+---------+---------------------+---------+-----------+
三、多表联查在开发中的使用
3.1一对多或多对一
场景：一个老师教多个学生
mysql> create table teacher (
-> t_id int primary key auto_increment,
-> t_name varchar(32)
-> );
mysql> create table student(
-> s_id int primary key auto_increment,
-> s_name varchar(32),
-> teacher_id int
-> );
mysql> select * from teacher;
+------+--------+
| t_id | t_name |
+------+--------+
| 1 | 王博 |
| 2 | 老邢 |
| 3 | 扫雷 |
+------+--------+
mysql> select * from student;
+------+--------+------+
| s_id | s_name | t_id |
+------+--------+------+
| 1 | 文博 | 1 |
| 2 | 开放 | 1 |
| 3 | 狗蛋 | 2 |
| 4 | 张三 | 3 |
| 5 | 李四 | 2 |
| 6 | 王五 | 3 |
| 7 | 赵六 | 1 |
| 8 | 嬴政 | 1 |
+------+--------+------+
查看老邢带的学生有哪些：
#方法一：常规联表查询
mysql> select teacher.t_name, student.s_name
-> from teacher, student
-> where teacher.t_id = student.teacher_id and teacher.t_name = "老邢";
#方法二：内连接联表查询
mysql> select t.t_name,s.s_name
-> from teacher t
-> inner join student s
-> on t.t_id = s.teacher_id
-> where t.t_name = "老邢";
+--------+--------+
| t_name | s_name |
+--------+--------+
| 老邢 | 狗蛋 |
| 老邢 | 李四 |
+--------+--------+
3.2多对多
场景： 一个学生对应着多门课程 一个课程可以被多个学生选择
mysql> create table stu (#学生表
-> s_id int primary key auto_increment,
-> s_name varchar(32)
-> );
mysql> create table course (#课程表
-> c_id int primary key auto_increment,
-> c_name varchar(32)
-> );
mysql> create table stu_course (#中间表
-> id int primary key auto_increment,
-> s_id int,
-> c_id int
-> );
有三张表就意味着着必须使用三表联查
司马懿选了哪些课程：
#方法一：常规联表查询
mysql> select stu.s_name, course.c_name
-> from stu, stu_course, course
-> where stu.s_id = stu_course.s_id and stu_course.c_id = course.c_id and stu.s_name = "司马懿";
#方法二：内连接联表查询
mysql> select s.s_name, c.c_name
-> from stu s
-> inner join stu_course sc
-> on s.s_id = sc.s_id
-> inner join course c
-> on sc.c_id = c.c_id
-> where s.s_name = "司马懿";
+------+-----------+----+------+------+------+--------+
| s_id | s_name | id | s_id | c_id | c_id | c_name |
+------+-----------+----+------+------+------+--------+
| 1 | 司马懿 | 1 | 1 | 1 | 1 | 物理 |
| 1 | 司马懿 | 2 | 1 | 3 | 3 | Java |
| 1 | 司马懿 | 3 | 1 | 4 | 4 | python |
+------+-----------+----+------+------+------+--------+
挖掘机被哪些人选了
mysql> select c.c_name, s.s_name
-> from course c
-> inner join stu_course sc
-> on c.c_id = sc.c_id
-> inner join stu s
-> on sc.s_id = s.s_id
-> where c.c_name = "挖掘机";
+-----------+-----------+
| c_name | s_name |
+-----------+-----------+
| 挖掘机 | 孙悟空 |
| 挖掘机 | 林黛玉 |
+-----------+-----------+
