Mysql进阶 ----- 多表查询 --- 外键约束

为什么要有多表？
– 创建一个员工表包含如下列(id, name, age, dep_name, dep_location),id主键并自动增长,添加5条数据
代码示例：

CREATE TABLE emp (
id INT PRIMARY KEY auto_increment,
name VARCHAR(40),
age int,
dep_name VARCHAR(40),
dep_location VARCHAR(30)
);


INSERT INTO emp (name, age, dep_name, dep_location) VALUES ('张三', 20, '研发部','广州');
INSERT INTO emp (name, age, dep_name, dep_location) VALUES ('李四', 21, '研发部','广州');
INSERT INTO emp (name, age, dep_name, dep_location) VALUES ('王五', 20, '研发部','广州');
INSERT INTO emp (name, age, dep_name, dep_location) VALUES ('老王', 20, '销售部','深圳');
INSERT INTO emp (name, age, dep_name, dep_location) VALUES ('大王', 22, '销售部','深圳');
INSERT INTO emp (name, age, dep_name, dep_location) VALUES ('小王', 18, '销售部','深圳');

– 单表的缺点:表中出现了很多重复的数据(数据冗余)，如果要修改研发部的地址需要修改3个地方。

解决方案:将一张表分成2张表(员工表和部门表)
代码示例：

-- 创建部门表
CREATE TABLE department(
id INT PRIMARY KEY auto_increment,
dep_name VARCHAR(40),
dep_location VARCHAR(30)
);

-- 创建员工表
CREATE TABLE employee(
id INT PRIMARY KEY auto_increment,
name VARCHAR(40),
age INT,
dep_id INT
);

-- 添加两个部门
INSERT INTO department(dep_name , dep_location) VALUES('研发部' , '广州');
INSERT INTO department(dep_name , dep_location) VALUES('销售部' , '深圳');

-- 添加员工
INSERT INTO employee (name, age, dep_id) VALUES
('张三', 20, 1),
('李四', 21, 1),
('王五', 20, 1),
('老王', 20, 2),
('大王', 22, 2),
('小王', 18, 2);

问题： 当我们在employee的dep_id里面输入不存在的部门,数据依然可以添加.但是并没有对应的部
门，不能出现这种情况。employee的dep_id中的内容只能是department表中存在的id

**目标:**需要约束dep_id字段的值, 只能是department表中已经存在id 解决方式:使用外键约束

外键约束作用
用来维护多表之间关系
外键: 从表中的某个字段,该字段的值是引用主表中主键的值 主表： 约束别人的表 副表/从表： 被别人约束的表
如下图：

**外键的语法
添加外键
1. 新建表时增加外键：
[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
关键字解释：
CONSTRAINT – 约束关键字
FOREIGN KEY(外键字段名) –- 某个字段作为外键
REFERENCES – 主表名(主键字段名) 表示参照主表中的某个字段
2. 已有表增加外键：
ALTER TABLE 从表名 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名)
REFERENCES 主表(主键字段名);

已有表添加外键：
代码示例：

-- 已有表添加外键
ALTER TABLE employee ADD  CONSTRAINT dep_exm_fk1 FOREIGN KEY(dep_id) REFERENCES department(id);

-- 删除外键
ALTER TABLE employee DROP FOREIGN KEY dep_exm_fk1;

新建表的时候添加外键：
代码示例：

-- 语法：	
-- 		[CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);

-- 创建员工表
CREATE TABLE employee(
id INT PRIMARY KEY auto_increment,
name VARCHAR(40),
age INT,
dep_id INT,
CONSTRAINT dep_exm_fk1 FOREIGN KEY(dep_id) REFERENCES department(id)
);

-- 删除外键
alter table employee drop foreign key dep_exm_fk1;

– 级联操作
– 在修改和删除主键时，同时更新或者删除副表的外键值，称为级联操作
– 语法：
– on update cascade 级联更新，主表主键发生更新时，外键也会更新
– on delete cascade 级联删除，主表主键发生删除时，外键也会删除

-- 添加外键，并设置外键级联更新和级联删除
alter table employee add constraint dep_emp_fk1 foreign key(dep_id) references department(id) on update cascade on delete cascade; 
 
--  修改销售部id 为 5
update department set id = 5 where id = 2;

delete from department where id = 5 ;