数据库范式以及drop、delete 与 truncate区别

数据库范式了解吗?

 我们从一个数据库设计的初始化阶段开始, 但是并没有规范化设计的背景知识

而要求我们设计一个员工管理系统, 可能得到表结构如下:

数据异常

首先这个表有如下问题:

1. 数据冗余: 我们可以看到部门名称相同时, 部门地址也是重复的  因此会重复存储数据

2. 插入异常: 因为这是一个以员工为对象的表,当我们想新建一个部门时,如果这个部门还没有员工, 就会导致无法维护这个表的信息

3. 更新异常: 如果我们需要修改部门信息, 必须先找到员工再从员工找到对应部门, 如果遗漏员工则会导致数据更新遗漏

4. 删除异常: 假设财政部只有两个员工, 如果将两个员工全部删除, 会导致财政部也随之消失

所有问题的来源只有一个: 数据冗余

规范化

因此我们引入规范化解决数据冗余问题 

第一范式

 表中电话字段存在可再分属性, 根据业务中的要求不满足原子属性这个概念

同时并未指定表的主键 , 我们做出如下修改令表满足第一范式:

 我们假设部门名称+姓名  是不重复的   作为复合主键

并将电话中的两个属性拆分成工作电话和个人电话

注意:以下两个图都满足第一范式要求: 姓名=姓氏+名字 并不一定是可再分,需要根据业务确定原子属性定义

第二范式

接下来我们介绍第二范式:

    非主键字段(部门地址等)必须完全依赖于主键字段(部门名称 + 姓名),  那么要想满足第二范式则必须保证所有非主键字段都要靠整个主键字段推出   即部门名称 + 姓名 ==> 部门地址等

但我们发现只需要部门名称就可以推出部门地址  部门名称==>部门地址,

即不符合第二范式的要求

我们将部门单独可以推出的信息摘出, 单独作为一个小表

 

并使用部门编号和工号作为两个表的唯一主键, 方便修改 

第三范式

接下来引入第三范式:

 

我们可以看到 工号 作为主键, 可以推出职位, 当然也可以推出月薪

但是职位作为非主键  却也可以推出月薪  工号=>职位=>月薪  因此 我们称这个表存在传递依赖

 我们再把传递依赖部分摘出, 当然也加入一个职位编号作为新表的主键

上表以满足第三范式的要求

反规范化

有时查询结果需要跨库查询,连接查询时 可能会导致降低性能

因此我们也会进行反规范化来直接返回我需要的数据 通过冗余空间的方式获得时间的提升

drop、delete 与 truncate 区别?

数据库基础知识总结 | JavaGuide(Java面试 + 学习指南)

用法不同

• drop(丢弃数据): drop table 表名，直接将表都删除掉，在删除表的时候使用。
• truncate(清空数据) : truncate table 表名 ，只删除表中的数据，再插入数据的时候自增长 id 又从 1 开始，在清空表中数据的时候使用。
• delete（删除数据） : delete from 表名 where 列名=值，删除某一行的数据，如果不加 where 子句和truncate table 表名作用类似。

truncate 和不带 where子句的 delete、以及 drop 都会删除表内的数据，但是 truncate 和 delete 只删除数据不删除表的结构(定义)，执行 drop 语句，此表的结构也会删除，也就是执行drop 之后对应的表不复存在

属于不同的数据库语言

truncate 和 drop 属于 DDL(数据定义语言)语句，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。而 delete 语句是 DML (数据库操作语言)语句，这个操作会放到 rollback segment 中，事务提交之后才生效。

DML 语句和 DDL 语句区别：

• DML 是数据库操作语言（Data Manipulation Language）的缩写，是指对数据库中表记录的操作，主要包括表记录的插入、更新、删除和查询，是开发人员日常使用最频繁的操作。
• DDL （Data Definition Language）是数据定义语言的缩写，简单来说，就是对数据库内部的对象进行创建、删除、修改的操作语言。它和 DML 语言的最大区别是 DML 只是对表内部数据的操作，而不涉及到表的定义、结构的修改，更不会涉及到其他对象。

另外，由于select不会对表进行破坏，所以有的地方也会把select单独区分开叫做数据库查询语言 DQL（Data Query Language）。

执行速度不同

一般来说：drop > truncate > delete（这个我没有实际测试过）。

• delete命令执行的时候会产生数据库的binlog日志，而日志记录是需要消耗时间的，但是也有个好处方便数据回滚恢复。
• truncate命令执行的时候不会产生数据库日志，因此比delete要快。除此之外，还会把表的自增值重置和索引恢复到初始大小等。
• drop命令会把表占用的空间全部释放掉。