MySQL多表&外键&数据库设计

多表 多表简述

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

单表的缺点 外键

外键指的是在从表中与主表的主键对应的字段

外键约束的作用

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

主从表

主表 主键id所在的表，一的一方
从表 外键字段所在的表，多的一方

添加外键约束的语法格式 创建表的时候添加外键

create table 表名(
字段…
[constraint] [外键约束名] foreign key (外键字段名) references 主表 (主键字段)
)；

创建员工表 添加外键
create table emloyee(
           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)
);

删除外键

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

创建表之后添加外键

• 语法格式：alter table 从表 add constraint emp_dept_fk foreign key(dept_id) references department(id)
• 简写：不写外键约束名 自动生成外键约束
  alter table 从表 add foreign key(dept_id) references department(id)
• 外键约束的注意事项

1.从表的外键类型必须与主表的主键类型一致
2.添加数据时，应该先添加主表的数据

级联删除

• 级联删除指的是在删除主表的数据的同时，也可以删除与之相关的从表中的数据
• 级联删除： on delete cascade

表与表的关系

• 一对多关系（1:n常见）：建表原则 在多的一方建立外键 指向一的一方的主键
• 多对多关系（n:n）建表原则：需要创建第三张表，中间表，中间表中至少要有两个字段是两张表中的主键字段，作为中间表中的外键
• 一对一关系（1:1）建表原则：可以在任意一方建立外键指向另一方的主键

多表查询 什么是多表查询

• DQL：查询多张表，获取到需要的数据
• 语法格式：select 字段列表 from 表名列表;
• 多表查询 交叉连接查询的查询结果会产生笛卡尔积 是不能使用的

内连接查询

• 特点：通过指定的条件去匹配两张表中的内容，匹配不上的不显示
• 隐式内连接：语法格式：select 字段名… from 左表,右表 where 连接条件
• 显式内连接：语法格式：select 字段名…from左表 [inner ] join 右表 on 连接条件
• inner可以省略

查询之前要确定的事

1. 查询几张表 products & category
2. 表的连接条件 从表.外键 = 主表.主键
3. 查询所用到的字段
4. 查询的条件

外连接查询

• 左外连接语法格式:select 字段名 from 左表 left join 右表 on 连接条件
• 左外连接的特点：以左表为基准，匹配右表中的数据 如果能匹配上就显示；如果匹配不上，左表中的数据正常显示，右表数据显示为null

查询每个分类下的商品数
1.查询的表   
2.查询的条件     分组  统计
3.查询的字段    分类  分类下商品个数信息
4.表的连接条件

• 右外连接语法格式：select 字段名 from 左表 right join 右表 on 条件
• 右外连接的特点：以右表为基准 匹配左表中的数据 如果能够匹配上 就显示，如果匹配不到 右表中的数据正常显示，左表显示null

各种连接方式的总结

• 内连接：inner join,只获取两张表中交集部分的数据
• 左外连接：left join ，以左表为基准，查询左表的所有数据，以及与右表有交集的部分
• 右外连接：right join，以右表为基准，查询右表的所有数据，以及与左表有交集的部分

子查询 什么是子查询

• 子查询的概念
  一条select查询语句的结果，作为另一条select语句的一部分
• 子查询的特点

    子查询必须放在小括号中
    子查询一般作为父查询的查询条件使用

• 子查询的常见分类

where型子查询：将子查询的结果，作为父查询的比较条件
from型 子查询：将子查询的结果，作为一张表，提供给父层查询使用
exists型 子查询：子查询的结果类似一个数组，父层查询使用in函数，包含子查询的结果（单列多行）

子查询作为查询条件

• 语法格式：select 查询字段 from 表 where 字段=（子查询）;
• 注意子查询的结果作为一张表时，要起一个别名 否则无法访问表中的字段

范式 数据库三范式

三范式指的是数据库设计的一个原则
作用是为了创建 冗余较小 结构合理的数据库
范式是设计数据库的要求

• 第一范式（1NF） 满足最低要求的范式
• 第二范式（2NF） 在满足第一范式的基础上 进一步满足更多的规范
• 第三范式（3NF）以此类推

第一范式

1NF：列具有原子性，设计列要做到列不可拆分

第二范式

2NF:一张表只能描述一件事情

第三范式

3NF：消除传递依赖 表中信息但如果能够被推导出来，就不要设计一个字段单独来记录
三范式就是空间最省原则

反三范式

• 指的是通过增加冗余或重复数据 来提高数据库的读性能
• 浪费存储空间 节省查询时间（以空间换时间）
• 冗余字段：某一个字段 属于一张表，但是它又在多张表中都有出现

总结

1. 尽量根据三范式的规则去设计数据库
2. 可以合理的加冗余字段，减少join操作，让数据库执行的更快