CREATE TABLE emp ( -- 创建emp表
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
age INT,
dep_name VARCHAR(30), -- 部门名称
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, '销售部', '深圳');
SELECT*FROM emp; -- 发现数据有冗余 研发部广州 销售部深圳 重复太多
-- 做一个表的拆分 一个表存放部分一个存放地址
-- 解决方案:分成 2 张表
-- 创建部门表(id,dep_name,dep_location)
-- 一方,主表
CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(20),
dep_location VARCHAR(20)
);
-- 创建员工表(id,name,age,dep_id)
-- 多方,从表
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_id INT -- 外键对应主表的主键
);
-- 添加 2 个部门
INSERT INTO department VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳');
SELECT * FROM department;
-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
SELECT * FROM employee; -- 查询员工信息
SELECT *FROM department; -- 查询部门信息 分两个地方查询
这样就是查询两张表 先员工表
找到dep-id 再去查部门信息表
但是有弊端 如果这里在department表里删除了研发部门 呢就会出现问题 员工没有部门了
所以外键约束 就是把dep-id 和id产生关联 发现两张表有引用关系 所以用了外键约束之后就不能随意删除了
* 外键约束:foreign key,让表于表产生关系,从而保证数据的正确性。
1. 在创建表时,可以添加外键
* 语法:
create table 表名(
....字段列表
外键列
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
);
-- 创建部门表(id,dep_name,dep_location)
-- 一方,主表
CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(20),
dep_location VARCHAR(20)
);
-- 创建员工表(id,name,age,dep_id)
-- 多方,从表
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT, -- 字段列
NAME VARCHAR(20),-- 字段列
age INT,-- 字段列
dep_id INT,-- 外键列 外键约束 就是把dep-id 和id产生关联
CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)
-- constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
);
-- 添加 2 个部门
INSERT INTO department VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳');
SELECT * FROM department;
-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
因为关联所以不能添加超出范围的外键
因为外键关联不能删除
2. 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
3. 创建表之后,添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
4. 级联操作
1. 添加级联操作
语法:
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE ;
部门id就能改了 而且自动同步到另一张表
2. 分类:
1. 级联更新:ON UPDATE CASCADE
级联更新的作用是 department里的外键id改了 员工的外键也该了
2. 级联删除:ON DELETE CASCADE
级联删除是部门一删 员工信息直接也给删了