1. 多表
1.1 多表简述
实际开发中，一个项目通常需要很多张表才能完成。
例如一个商城项目的数据库,需要有很多张表：用户表、分类表、商品表、订单表….
1.2 单表的缺点
1.2.1 数据准备
（1） 创建一个数据库db3
CREATE DATABASE db3 CHARACTER SET utf8;-- 创建emp表 主键自增CREATE TABLE emp(eid INT PRIMARY KEY AUTO_INCREMENT,ename VARCHAR(25),age INT,dep_name VARCHAR(30),dep_location VARCHAR(40));-- 添加数据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, '销售部', '深圳');
（2） 数据库中创建一个员工表emp,
1） 包含如下列 eid, ename, age, dep_name, dep_location
2） eid 为主键并 自动增长, 添加6条数据
1.2.2 单表的问题
1.3 解决方案
1.3.1 设计为两张表
- 多表方式设计
department 部门表 : id, dep_name, dep_location
employee 员工表: eid, ename, age, dep_id
- 删除emp表，重新创建两张表
- 添加部门表 数据
- 添加员工表 数据 ```sql — 创建部门表 — 一方,主表 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 );
— 添加2个部门 INSERT INTO department VALUES(NULL, ‘研发部’,’广州’),(NULL, ‘销售部’, ‘深圳’); SELECT * FROM department;
— 添加员工,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;
<a name="kY5nn"></a>
### 1.3.2 表关系分析
- 部门表与员工表的关系
<br />1) 员工表中有一个字段dept_id 与部门表中的主键对应,员工表的这个字段就叫做外键<br />2) 拥有外键的员工表 被称为 `从表` , 与外键对应的主键所在的表叫做 `主表`
<a name="J7L0n"></a>
### 1.3.3 多表设计上的问题
- 当我们在 员工表的 dept_id 里面输入不存在的部门id ,数据依然可以添加 显然这是不合理的.
- 实际上我们应该保证,员工表所添加的 dept_id , 必须在部门表中存在.
**解决方案: **
- 使用外键约束,约束 dept_id ,必须是 部门表中存在的id
<a name="adkXu"></a>
## 1.4 外键约束
<a name="p0Esr"></a>
### 1.4.1 什么是外键
- 外键指的是在从表中与主表的主键对应的那个字段，比如员工表的dept_id就是外键
- 使用外键约束可以让两张表之间产生一个对应关系，从而保证主从表引用的完整性
- 多表关系中的主表和从表
- 主表：主键id所在的表，约束别人的表
- 从表：外键所在的表，被约束的表
<a name="acj52"></a>
### 1.4.2 创建外键约束
语法格式:
1. 在新建表时添加外键
```sql
[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
- 给已有表添加外键
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);1.4.3 删除外键约束
语法格式alter table 从表 drop foreign key 外键约束名称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 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. 多表关系设计
2.1 一对多关系（常见）
2.2 多对多关系（常见）
2.3 一对一关系（了解）
3. 多表查询
4. 子查询
4.1 什么是子查询
4.2 子查询结果作为查询条件
4.3 子查询的结果作为一张表
4.4 子查询结果单列多行
4.5 子查询总结
5. 数据库设计
5.1 数据库三范式（空间最省原则）
5.1.1 第一范式 1NF
5.1.2 第二范式 2NF
5.1.3 第三范式 3NF
5.2 数据库反三范式
若有收获，就点个赞吧
0 人点赞
