MySQL（基础命令7）

1.多表

1.1 多表简述

• 实际开发中，一个项目通常需要很多张表才能完成。
• 例如一个商城项目的数据库,需要有很多张表：用户表、分类表、商品表、订单表....

 1.2 单表的缺点

1.2.1 数据准备

1. 创建一个数据库 db3

CREATE DATABASE db3 CHARACTER SET utf8;

2. 数据库中 创建一个员工表 emp

• 包含如下列 eid, ename, age, dep_name, dep_location
• eid 为主键并 自动增长, 添加 5 条数据

-- 创建 emp 表 主键自增 CREATE TABLE emp( eid INT PRIMARY KEY AUTO_INCREMENT , ename VARCHAR ( 20 ), age INT , dep_name VARCHAR ( 20 ), dep_location VARCHAR ( 20 ) );

-- 添加数据 INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ( ' 张百万 ' , 20 , ' 研发 部 ' , ' 广州 ' ); INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ( ' 赵四 ' , 21 , ' 研发 部 ' , ' 广州 ' ); INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ( ' 广坤 ' , 20 , ' 研发 部 ' , ' 广州 ' ); INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ( ' 小斌 ' , 20 , ' 销售 部 ' , ' 深圳 ' ); INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ( ' 艳秋 ' , 22 , ' 销售 部 ' , ' 深圳 ' ); INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ( ' 大玲子 ' , 18 , ' 销售 部 ' , ' 深圳 ' );

1.2.2 单表的问题

1. 冗余, 同一个字段中出现大量的重复数据

 1.3 解决方案

1.3.1 设计两张表

1. 多表方式设计

• department 部门表 : id, dep_name, dep_location
• employee 员工表: eid, ename, age, dep_id

2. 删除emp表, 重新创建两张表

-- 创建部门表 -- 一方 , 主表 CREATE TABLE department( id INT PRIMARY KEY AUTO_INCREMENT , dep_name VARCHAR ( 30 ), dep_location VARCHAR ( 30 ) ); -- 创建员工表 -- 多方 , 从表 CREATE TABLE employee( eid INT PRIMARY KEY AUTO_INCREMENT , ename VARCHAR ( 20 ), age INT , dept_id INT );

3. 添加部门表 数据

-- 添加 2 个部门 INSERT INTO department VALUES ( NULL , ' 研发部 ' , ' 广州 ' ),( NULL , ' 销售部 ' , ' 深圳 ' ); SELECT * FROM department;

4. 添加员工表 数据

-- 添加员工 ,dep_id 表示员工所在的部门 INSERT INTO employee (ename, age, dept_id) VALUES ( ' 张百万 ' , 20 , 1 ); INSERT INTO employee (ename, age, dept_id) VALUES ( ' 赵四 ' , 21 , 1 ); INSERT INTO employee (ename, age, dept_id) VALUES ( ' 广坤 ' , 20 , 1 ); INSERT INTO employee (ename, age, dept_id) VALUES ( ' 小斌 ' , 20 , 2 ); INSERT INTO employee (ename, age, dept_id) VALUES ( ' 艳秋 ' , 22 , 2 ); INSERT INTO employee (ename, age, dept_id) VALUES ( ' 大玲子 ' , 18 , 2 ); SELECT * FROM employee;

1.3.2 表关系分析

• 部门表与员工表的关系

 

• 员工表中有一个字段dept_id 与部门表中的主键对应,员工表的这个字段就叫做 外键
• 拥有外键的员工表 被称为 从表 , 与外键对应的主键所在的表叫做 主表

1.3.3 多表设计上的问题

• 当我们在 员工表的 dept_id 里面输入不存在的部门id ,数据依然可以添加 显然这是不合理的.

-- 插入一条 不存在部门的数据 INSERT INTO employee (ename,age,de

 

• 实际上我们应该保证,员工表所添加的 dept_id , 必须在部门表中存在.
• 解决方案：使用外键约束,约束 dept_id ,必须是 部门表中存在的id

1.4 外键约束

1.4.1 什么是外键

• 外键指的是在 从表 中 与 主表 的主键对应的那个字段,比如员工表的 dept_id,就是外键
• 使用外键约束可以让两张表之间产生一个对应关系,从而保证主从表的引用的完整性

• 多表关系中的主表和从表
• 主表: 主键id所在的表, 约束别人的表
• 从表: 外键所在的表多, 被约束的表

 1.4.2 创建外键约束

语法格式：

1.新建表时添加外键

[ CONSTRAINT ] [ 外键约束名称 ] FOREIGN KEY ( 外键字段名 ) REFERENCES 主表名 ( 主键字段名 )

 2. 已有表添加外键

ALTER TABLE 从表 ADD [ CONSTRAINT ] [ 外键约束名称 ] FOREIGN KEY ( 外键字段名 ) REFERENCES 主表 ( 主 键字段名 );

3.重新创建employee表, 添加外键约束

-- 先删除 employee 表 DROP TABLE employee; -- 重新创建 employee 表 , 添加外键约束 CREATE TABLE employee( eid INT PRIMARY KEY AUTO_INCREMENT , ename VARCHAR ( 20 ), age INT , dept_id INT , -- 添加外键约束 CONSTRAINT emp_dept_fk FOREIGN KEY (dept_id) REFERENCES department(id) );

4. 插入数据

-- 正常添加数据 ( 从表外键 对应主表主键 ) INSERT INTO employee (ename, age, dept_id) VALUES ( ' 张百万 ' , 20 , 1 ); INSERT INTO employee (ename, age, dept_id) VALUES ( ' 赵四 ' , 21 , 1 ); INSERT INTO employee (ename, age, dept_id) VALUES ( ' 广坤 ' , 20 , 1 ); INSERT INTO employee (ename, age, dept_id) VALUES ( ' 小斌 ' , 20 , 2 ); INSERT INTO employee (ename, age, dept_id) VALUES ( ' 艳秋 ' , 22 , 2 ); INSERT INTO employee (ename, age, dept_id) VALUES ( ' 大玲子 ' , 18 , 2 ); -- 插入一条有问题的数据 ( 部门 id 不存在 ) -- Cannot add or update a child row: a foreign key constraint fails INSERT INTO employee (ename, age, dept_id) VALUES ( ' 错误 ' , 18 , 3 );

• 添加外键约束,就会产生强制性的外键数据检查, 从而保证了数据的完整性和一致性

 1.4.3 删除外键约束

1.语法格式

alter table 从表 drop foreign key 外键约束名称

2.删除外键约束

-- 删除 employee 表中的外键约束 , 外键约束名 emp_dept_fk ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;

3.再将外键 添加回来

语法格式

ALTER TABLE 从表 ADD [ CONSTRAINT ] [ 外键约束名称 ] FOREIGN KEY ( 外键字段名 ) REFERENCES 主表 ( 主 键字段名 );

-- 可以省略外键名称 , 系统会自动生成一个 ALTER TABLE employee ADD FOREIGN KEY (dept_id) REFERENCES department (id);

1.4.4 外键约束的注意事项

1. 从表外键类型必须与主表主键类型一致 否则创建失败.

 2. 添加数据时, 应该先添加主表中的数据.

-- 添加一个新的部门 INSERT INTO department(dep_name,dep_location) VALUES ( ' 市场部 ' , ' 北京 ' ); -- 添加一个属于市场部的员工 INSERT INTO employee(ename,age,dept_id) VALUES ( ' 老胡 ' , 24 , 3 );

3.  删除数据时,应该先删除从表中的数据.

-- 删除数据时 应该先删除从表中的数据 -- 报错 Cannot delete or update a parent row: a foreign key constraint fails -- 报错原因 不能删除主表的这条数据 , 因为在从表中有对这条数据的引用 DELETE FROM department WHERE id = 3 ;

-- 先删除从表的关联数据 DELETE FROM employee WHERE dept_id = 3 ; -- 再删除主表的数据 DELETE FROM department WHERE id = 3 ;

1.4.5 级联删除操作(了解)

• 如果想实现删除主表数据的同时,也删除掉从表数据,可以使用级联删除操作

级联删除 ON DELETE CASCADE

1. 删除 employee表,重新创建,添加级联删除

-- 重新创建添加级联操作 CREATE TABLE employee( eid INT PRIMARY KEY AUTO_INCREMENT , ename VARCHAR ( 20 ), age INT , dept_id INT , CONSTRAINT emp_dept_fk FOREIGN KEY (dept_id) REFERENCES department(id) -- 添加级联删除 ON DELETE CASCADE ); -- 添加数据 INSERT INTO employee (ename, age, dept_id) VALUES ( ' 张百万 ' , 20 , 1 ); INSERT INTO employee (ename, age, dept_id) VALUES ( ' 赵四 ' , 21 , 1 ); INSERT INTO employee (ename, age, dept_id) VALUES ( ' 广坤 ' , 20 , 1 ); INSERT INTO employee (ename, age, dept_id) VALUES ( ' 小斌 ' , 20 , 2 ); INSERT INTO employee (ename, age, dept_id) VALUES ( ' 艳秋 ' , 22 , 2 ); INSERT INTO employee (ename, age, dept_id) VALUES ( ' 大玲子 ' , 18 , 2 );

-- 删除部门编号为 2 的记录 DELETE FROM department WHERE id = 2 ;

• 员工表中 外键值是2的记录,也被删除了