数据库三大范式、纵横表、sql引擎

一、数据库3大范式

1、定义：设计良好结构的数据库，可以有效减小数据冗余（有些冗余是必须存在的，所以有些时候是可以存在数据的冗余），减少增删改中出现的问题。深入理解数据库设计的三范式，对于设计“健壮的数据库“十分有必要。数据库三范式是设计数据库 时参考的准则。

2、第一范式:每列字段不可再拆分，即为每列都要符合原子性

2.1、不符合第一范式的例子：

表：字段1，字段2（字段2.1，字段2.2...），字段3

字段2可以拆分成字段2.1，字段2.2，不符合第一范式

修正：字段1，字段2.1，字段2.2，字段3

3、第二范式：必须满足第一范式，实体的每个非主键属性完全函数依赖于主键属性（消除部分依赖）

表要有主键，非主键字段要依赖主键。

唯一性：一个表只能说明一个事物

3.1、不符合第二范式的例子：

表：学号，姓名，年龄，课程名，成绩，学分

分析：这个表说明了2个事物，学生信息，课程信息

3.2、以上表存在的问题：

1）数据冗余，每条记录都含有相同的信息

2）删除异常：删除所有学生成绩，那课程信息就全删除了

3）插入异常：如果学生没有选课，就无法insert数据

4）更新异常：如果要调整某个课程的学分，涉及到的行都需要调整

3.3、修正：

学生表：学号、姓名、年龄

课程表：课程名、学分

选课表：学号、课程名、成绩

4、第三范式：非主键字段不能互相依赖

每个字段都与主键有直接关系，不存在传递依赖

4.1、不符合第三范式的例子

表：学号、姓名、年龄、所在学院、学院主任

其中主键：学号。

存在依赖传递：学号->所在学院->学院主任

4.2、存在的问题：

1）数据冗余，有重复值（只要所在学院一样，学院主任就一样）

2）更新异常：比如学院主任换人了，更新这个学院的所有学员主任信息，但是跟学生没有直接关系

3）删除异常

4.3、修正：

学生表：学号、姓名、年龄、所在学院

学院表：学院名、学院主任

二、纵横表

1、横表：横表就是普通的建表方式

1.1、表结构为：主键、字段1、字段2、字段3...。

1.2、优点：一行表示了一个实体记录，清晰可见，一目了然。

1.3、缺点：如果现在要给这个表加一个字段，那么就必须重建表结构。

2、纵表：

2.1、表结构为： 主键、字段代码、字段值。而字段代码则为字段1、字段2、字段3...。

2.2、特点：是行数多，字段少。纵表在使用时由于行数多，统计用户数或对用户进行分档时还需要进行GROUP BY 操作，性能低，且操作不便，

2.3、为了提高性能，通常根据需要将纵表进行汇总，形成横表

2.4、优点：如果现在要给这个表加一个字段，只需要添加一些记录。

2.5、缺点：数据描述不是很清晰，而且会造成数据库数据很多。另如果需要分组统计，要先group by，较繁琐。

3、总结：应该把不容易改动表结构的设计成横表，把容易经常改动不确定的表结构设计成纵表。

4、纵表转横表：

列转行：公式，把纵表转横表

SELECT 分组列,

聚合函数（case 转换列 when 转换列的值1 then 数据列 else ... end ） as 列名1，

聚合函数（case 转换列 when 转换列的值2 then 数据列 else ... end ） as 列名2，

聚合函数（case 转换列 when 转换列的值3 then 数据列 else ... end ） as 列名3

....

FROM 表名

GROUP BY 分组列;

5、横表转纵表：

公式

SELECT 非转换列 ,'新增列的值1' AS 新加列的别名, 转换列1 AS 转换列的别名 FROM 表名

UNION ALL

SELECT 非转换列 ,'新增列的值2' AS 新加列的别名, 转换列2 AS 转换列的别名 FROM 表名

UNION ALL

SELECT 非转换列 ,'新增列的值3' AS 新加列的别名, 转换列3 AS 转换列的别名 FROM 表名

...

ORDER BY ... ;

6、注意;

1、可以把查询结果当做一个表

2、给表结果添加一列，可以直接写在查询结果列的位置

，别名不用加引号，只有表示值的时候才加''

语法：select 列值 as 列名 ...

SELECT *, 100 as 总分数, 1 as 好 from sql_3;

三、mysql引擎：

1、Innodb引擎

1.1、Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别。

1.2、该引擎还提供了行级锁和外键约束，

1.3、该引擎不支持FULLTEXT类型的索引，而且它没有保存表的行数，

1.4、当需要使用数据库事务时，该引擎当然是首选

1.5、所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。

2、MyIASM引擎

2.1、没有提供对数据库事务的支持，也不支持行级锁和外键，

2.2、当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。

2.3、如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。

3、主要区别：

3.1、MyIASM是非事务安全的，而InnoDB是事务安全的

3.2、MyIASM锁的粒度是表级的，而InnoDB支持行级锁

3.3、MyIASM支持全文类型索引，而InnoDB不支持全文索引

3.4、MyIASM相对简单，效率上要优于InnoDB，小型应用可以考虑使用MyIASM

3.5、MyIASM表保存成文件形式，跨平台使用更加方便