MySQL: 4、多表、外键、数据库设计
一、多表
1、主键：
用来唯一标识一条记录，不能有重复的，不允许为空，用来保证数据的完整性，并且主键只能有一个
2、外键:
表的外键是另一个表的主键，外键可以重复，可以 为空，但是为空数据就跟与之相关联的表没关系了
外键主要使用来和其他表建立联系用的，其他表就叫做主表。一个表可以多有个外键
3、 从表：
外键所在的表就叫做从表，也就是被约束的表
4、 主表：
主键id所在的表就叫做主表，也就是约束别的表
5、 外键约束:
1）外键约束主要是让两张表之间产生一个对应的关系，从而保证主从表引用的完整性
2）创建外键约束:
1) 新建表时添加外键
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)
);
[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
2) 已有表添加外键
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);
3）删除外键约束：
ALTER TABLE 从表 DROP FOREIGN KEY 外键约束名称;
ps:如果不知道约束名可以通过: show create table 表名; 来查看外键名
4）删除外键约束的注意事项
1) 从表外键类型必须与主表主键类型一致 否则创建失败.
2) 添加数据时, 应该先添加主表中的数据.
3) 删除数据时,应该先删除从表中的数据.
6、级联删除操作: 在删除主表数据的同时也需要删除从表的数据的话，就可以使用级联删除操作
1) 级联删除: ON DELETE CASCADE
2) 案例：创建表时添加级联删除
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 -- 添加级联删除
);
添加级联删除之后，删除主表的数据之后，主表对应的从表数据也会被删除
假设有个主表: 部门表和 从表: 员工表，当删除部门表对应主键id 为2的数据之后，
从表也就是员工表中外键对应为2的数据也会自动删除
2、多表关系设计
1、表与表之间的三种关系：
一对多关系：最常见的关系，例如班级对学生，部门对员工
多对多关系：学生对应课程，用户对应角色
一对一关系：使用较少，因为一对一关系可以合成为一张表
2、一对多关系（1:n）
例如：班级和学生，部门和员工，客户和订单，分类和商品
一对多建表原则：
在从表（多方）创建一个字段，该字段作为外键指向主表（一方）的主键
3、多对多关系（m:n）
例如：老师和学生，学生和课程，用户和角色
n 多对多关系建表原则：
需要创建第三张表（中间表),中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键
4.一对一关系（了解）
在实际开发钟应用不用多，因为一对一可以创建成一张表
一对一建表原则：
外键唯一 主表的主键和从表的外键（唯一），形成外键关系，外键唯一 UNIQUE
3、多表查询：内连接、外连接
0) 多表查询前需要确定的事情：
1. 查询几张表
2.表的连接条件
3.查询所用到的字段
4.查询的条件
1）交叉查询：两张表交叉连接查询会产生笛卡尔积，所以基本不会使用
select 字段名 from 表1，表2；
2）内连接查询：通过指定的条件去匹配两张表中的内容，匹配不上就不显示
- 隐式内连接
语法格式： select 字段名... from 左表,右表 where 链接条件；
- 显式内链接
语法给是：select 字段名... from 左表 [inner] join 右表 on 连接条件;
3） 外连接查询：
- 左外连接： left [outer] join
SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件;
特点： 以左表为基准，匹配右边表中的数据，如果匹配得上，就展示匹配到的数据
如果匹配不到，左表中的数据正常展示，右边的展示为null
- 右外连接：right [outer] join
SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件
特点： 以右表为基准，匹配左表中的数据，如果能匹配到，就展示匹配到的数据
如果匹配不到，右表中的数据正常展示，左边展示为null
4）内连接、左外链接、右外链接方式总结：
内连接: inner join , 只获取两张表中 交集部分的数据.
左外连接: left join , 以左表为基准 ,查询左表的所有数据, 以及与右表有交集的部分
右外连接: right join , 以右表为基准,查询右表的所有的数据,以及与左表有交集的部分
4、子查询 SubQuery
概念：一条select 查询语句的结果, 作为另一条 select 语句的一部分
特点：子查询必须放在小括号中
子查询一般作为父查询的查询条件使用
子查询常见分类：
where型 子查询: 子查询如果是一个字段（单列）就将子查询的结果, 作为父查询的比较条件
from型 子查询 : 将子查询的结果是多个字段就 作为 一张表,提供给父层查询使用,子查询的
结果作为一张表 时，一定要起一个别名，否则无法访问表中的字段
exists型 子查询: 子查询的结果是单列多行, 类似一个数组, 父层查询使用 IN 函数 ,包含子查 询的结果
5、数据库三范式（空间最省）
概念：三范式就是设计数据库的规则
- 为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。
范式是符合某一种设计要求的总结。要想设计一个结构合理的关 系型数据库，必须满足一定的范式
- 满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的 称为第二范式（2NF） ，
其余范式以此类推。一般说来，数据库只需满足第三范式(3NF）就 行了
第一范式 1NF:
- 原子性，做到列不可拆分
- 第一范式是最基本的范式，数据库表里面的字段都是单一属性的，不可再分，如果数据表中每个字段都是不可
再分的最小数据单元，则满足第一范式。
- 示例：地址信息表中, contry这一列,还可以继续拆分,不符合第一范式
第二范式 2NF:
- 在第一范式的基础上更进一步，目标是确保表钟的每个列和主键相关
- 一张表只能表述一件事
- 示例：
学员信息表中其实在描述两个事物 , 一个是学员的信息,一个是课程信息
如果放在一张表中,会导致数据的冗余,如果删除学员信息, 成绩的信息也被删除了
第三范式 3NF:
- 消除传递依赖
- 表的信息，如果能够被推到出来，就不应该单独的设计一个字段来存放
- 示例：通过number 与 price字段就可以计算出总金额,不要在表中再做记录(空间最省)
6、数据库反三范式
1、概念
反范式化指的是通过增加冗余或重复的数据来提高数据库的读性能
浪费存储空间,节省查询时间 (以空间换时间)
2、什么是冗余字段 ?
设计数据库时，某一个字段属于一张表，但它同时出现在另一个或多个表，
且完全等同于它在其本 来所属表的意义表示，那么这个字段就是一个冗余字段
3、示例
使用场景：
- 当需要查询“订单表”所有数据并且只需要“用户表”的name字段时, 没有冗余字段 就需要去join
连接用户表,假设表中数据量非常的大, 那么会这次连接查询就会非常大的消耗系统的性能.
- 这时候冗余的字段就可以派上用场了, 有冗余字段我们查一张表就可以了.
4、总结：
创建一个关系型数据库设计，我们有两种选择
1，尽量遵循范式理论的规约，尽可能少的冗余字段，让数据库设计看起来精致、优雅、让人心醉。
2，合理的加入冗余字段这个润滑剂，减少join，让数据库执行性能更高更快。
