Mysql数据库（三） MySQL多表&外键&数据库设计

最新推荐文章于 2022-08-18 18:12:45 发布

是你得不到的人

最新推荐文章于 2022-08-18 18:12:45 发布

阅读量475

点赞数

分类专栏： JavaSE 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_43835305/article/details/108784878

版权

JavaSE 专栏收录该内容

37 篇文章 1 订阅

订阅专栏

本文详细介绍了MySQL中的多表设计，包括外键约束的创建与删除，以及级联删除操作。讨论了一对多、多对多和一对一的表关系设计，并通过实例解释了如何在数据库中实现这些关系。此外，还讲解了内连接、外连接查询和子查询的基本概念及应用场景，帮助理解多表查询的不同策略。最后，提到了数据库设计的三范式及其重要性，以及反三范式设计在性能优化上的考虑。

摘要由CSDN通过智能技术生成

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

多表

基本概念

实际开发中，一个项目通常需要很多张表才能完成。例如一个商城项目的数据库,需要有很多张表：用户表、分类表、商品表、订单表…
单表的缺点：冗余, 同一个字段中出现大量的重复数据

外键约束

基本概念

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

创建外键约束

语法格式

-- 新建表时添加外键
[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名) 

-- 已有表添加外键 -- 可以省略外键名称, 系统会自动生成一个 
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主 键字段名);

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

删除外键约束

语法格式

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

外键约束的注意事项

从表外键类型必须与主表主键类型一致，否则创建失败
添加数据时, 应该先添加主表中的数据
删除数据时,应该先删除从表中的数据

级联删除操作

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

语法格式

-- 级联删除 
ON DELETE CASCADE

-- 重新创建添加级联操作 
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 
);

多表关系设计

实际开发中，一个项目通常需要很多张表才能完成。
例如：一个商城项目就需要分类表(category)、商品表(products)、订单表(orders)等多张表。且这些表的数据之间存在一定的关系
表与表之间的三种关系
- 一对多关系: 最常见的关系, 学生对班级,员工对部门
- 多对多关系: 学生与课程, 用户与角色
- 一对一关系: 使用较少,因为一对一关系可以合成为一张表

一对多关系

一对多关系（1:n）
- 例如：班级和学生，部门和员工，客户和订单，分类和商品
一对多建表原则:
- 在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键

多对多关系

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

一对一关系

一对一（1:1）
- 在实际的开发中应用不多.因为一对一可以创建成一张表。
一对一建表原则
- 外键唯一主表的主键和从表的外键（唯一），形成主外键关系，外键唯一 UNIQUE

多表查询

基本概念

DQL: 查询多张表,获取到需要的数据
比如我们要查询家电分类下都有哪些商品,那么我们就需要查询分类与商品这两张表

笛卡尔积

交叉连接查询,因为会产生笛卡尔积,所以基本不会使用
语法格式
```
SELECT 字段名 FROM  表1, 表2;
```

多表查询的分类

内连接查询

内连接的特点: 通过指定的条件去匹配两张表中的数据, 匹配上就显示,匹配不上就不显示
比如通过: 从表的外键 = 主表的主键方式去匹配

隐式内连接

from子句后面直接写多个表名使用where指定连接条件的这种连接方式是隐式内连接.

使用where条件过滤无用的数据

语法格式

SELECT 字段名 FROM 左表, 右表 WHERE 连接条件;

显式内连接

使用 inner join …on 这种方式, 就是显式内连接

语法格式

SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件   -- inner 可以省

外连接查询

左外连接

左外连接 , 使用 LEFT OUTER JOIN , OUTER 可以省略左外连接的特点以左表为基准, 匹配右边表中的数据

如果匹配的上,就展示匹配到的数据如果匹配不到, 左表中的数据正常展示, 右边的展示为null

语法格式

SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件

右外连接

右外连接 , 使用 RIGHT OUTER JOIN , OUTER 可以省略右外连接的特点以右表为基准，匹配左边表中的数据

如果能匹配到，展示匹配到的数据如果匹配不到，右表中的数据正常展示, 左边展示为null

语法格式

SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件

各种连接方式的总结

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

子查询 (SubQuery)

基本概念

一条select 查询语句的结果, 作为另一条 select 语句的一部分
子查询的特点
- 子查询必须放在小括号中子查询一般作为父查询的查询条件使用
子查询常见分类
- where型子查询: 将子查询的结果, 作为父查询的比较条件
- from型子查询 : 将子查询的结果, 作为一张表,提供给父层查询使用
- exists型子查询: 子查询的结果是单列多行, 类似一个数组, 父层查询使用 IN 函数 ,包含子查询的结果

子查询的结果作为查询条件

语法格式

SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;

子查询的结果作为一张表

语法格式

SELECT 查询字段 FROM （子查询）表别名 WHERE 条件;

注意：当子查询作为一张表的时候，需要起别名，否则无法访问表中的字段

子查询结果是单列多行

子查询的结果类似一个数组, 父层查询使用 IN 函数 ,包含子查询的结果

语法格式

SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;

子查询总结

子查询如果查出的是一个字段(单列), 那就在where后面作为条件使用
子查询如果查询出的是多个字段(多列), 就当做一张表使用(要起别名)

数据库设计

数据库三范式(空间最省)

基本概念

三范式就是设计数据库的规则.
为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则；在关系型数据库中这种规则就称为范式。
范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式
满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以此类推。一般说来，数据库只需满足第三范式(3NF）就行了