3. 多表设计&子查询&范式

3. 多表&子查询&范式

• 一个项目开发通常要许多表才能完成，单表中会出现大量重复数据，为此刻采取多表设计方式。

1.外键约束

• 作用：外键约束可以让两张表之间产生有一个对应的关系,从而保证了主从表引用的完整性

• 主表：主键id所在的表，一的一方

• 从表：外键字段所在的表，多的一方。(外键可为空)

• 添加外键的语法格式：

  -- 1.创建表的时候添加外键
  create table 表名(
  	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)
  	[constraint] [外键约束名] foreign key(外键字段名) references 主表(主键字段)
  		); 
  -- 2.创建表之后添加外键
  alter table 从表 add 外键名 FOREIGN KEY(dept_id) REFERENCES 主表(id);
  -- 3.删除外键约束
  alter table 从表 drop foreign key 外键约束的名称
  

(1).注意事项

• 从表的外键类型必须与主表的主键类型一致

• 添加数据时,应该先添加主表的数据

• 删除数据的时候 要先删除从表中的数据

(2). 级联删除

• 在删除主表的数据的同时,可以删除与之相关的从表中的数据
• 格式：on delate cascade;

CREATE TABLE employee(
	…………
	ON DELETE CASCADE
);
DELETE FROM department WHERE id=2;-- 删除部门表中id为2的部门，同时将从表关联数据删除

2. 多表关系设计

(1) .一对多关系

• 例如：班级对学生、部门对员工、客户对订单、分类和商品（N—>1）

• 原则：在从表（多）创建外键指向主表（一）的主键。

(2).多对多关系

• 例如：老师和学生、学生和课程、用户和角色
• 原则：需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的 主键

(3).一对一关系（了解）

• 实际开发应用不多，可直接创建成一张表。
• 原则：主表主键和外键（唯一）形成主外键关系

(4).省、市表设计

-- 一对多关系 省表与市表
-- 创建省表 主表 一的一方
CREATE TABLE province(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	description VARCHAR(20)
)