MySQL数据类型、运算符以及数据库范式

一、数据类型

1. 数值类型

MySQL数据类型定义了数据的大小范围，因此使用时选择合适的类型，会降低表占用的磁盘空间，间接减少了磁盘I/O的次数，提高表的访问效率，而且索引的效率也和数据的类型息息相关。

使用数值类型的时候若写成：INT(M)表示显示M位，而不是该数值占M字节

2. 字符串类型

使用字符串类型的时候若写成：CHAR(M)表示该字符串占M+1字节

4. 时间和日期类型

5. enum和set

这两个类型，都是限制该字段只能取固定的值，但是枚举字段只能取一个唯一的值，而集合字段可以取任意的值

创建表的时候可以使用：

sex enum('man','woman') default 'man'

二、MySQL运算符

1. 算数运算符

此外作为通配符：%可以匹配任意一个或多个字符，_匹配任意单个字符

2. 逻辑运算符

3. 比较运算符

三、完整性约束

1. 主键约束：primary key
2. 自增键约束：auto_increment
3. 唯一键约束：unique
4. 非空约束：not null
5. 默认值约束：default
6. 外键约束：foreign key

create table user(
	id int unsigned primary key auto_increment comment '用户的id',
	nickname varchar(50) unique not null comment '用户的昵称',
	age tinyint unsigned not null default 18,
	sex enum('man', 'woman')
);

四、关系型数据库表设计

1. 一对一

在子表中增加一列，关联父表的主键

2. 一对多

电商系统的用户-订单是一对多的关系

在子表中增加一列关联父表的主键，在Order表中添加用户的id

缺点：Order表有很多冗余信息，order_id、uid、total_price、addr_info

3. 多对多

电商系统的商品-订单是一对多的关系

为解决冗余信息过多的问题，我们添加一个中间表，根据order_id就可以查看订单的详细信息

实际情况中，一个订单不可能记录一种商品多次，在OrderList表中可以使用order_id和pid联合创建一个索引，加速查找

五、关系型数据库范式

使用数据库范式有很多好处，但是最重要的好处归结为三点：

1. 减少数据冗余（最主要的好处，其他好处都是因此附带的）
2. 消除异常（插入异常、更新异常、删除异常）
3. 让数据组织得更加和谐

数据库范式绝对不是越高越好，范式越高，说明表越多，多表联合查询的机率越大，SQL效率越低

1. 第一范式

每一属性都保持原子特性，不可再分割，否则设计成一对多的实体关系。

应该将地区分成省、市、区等。如果直接就是一个地址，在电商系统中若我们需要统计某个省的订单量，如果没有把一整个地址划分成省市区，则无法统计

2. 第二范式

在1NF的基础上，消除非主属性对候选码的部分依赖，非主属性完全依赖于候选码。 如果不是完全依赖候选码，则应该拆分成新的实体，设计成一对多的实体关系。

例如：选课关系表SelectCourse(学号，姓名，年龄，课程名称，成绩，学分)中，(学号，课程名称)是联合主键，但是学分字段只和课程名称有关，和学号无关，相当于只依赖联合主键中的一个字段，不符合第二范式

由于姓名、年龄只和学号有关，和课程名称无关，所以在存储的多行中很有可能学号、姓名、年龄都相同，只有课程名称不同，这就造成了数据冗余。将来无论是更新、删除都会有大量的数据同时改变，效率低下。

实际上，学生和课程是多对多的关系，应该插入一个中间表

这样的话，在选课表中学号和课程id作为联合主键，成绩这个属性依赖于联合主键

参考博客：三张图搞透第一范式(1NF)、第二范式(2NF)和第三范式(3NF)的区别

3. 第三范式

符合2NF，不符合3NF：

3NF消除所有非主属性对键的传递依赖以及部分依赖，就是非主属性之间不得有依赖关系，都必须直接完全依赖于候选码

示例：学生关系表为Student（学号，姓名，年龄，学院名称，学院地点，学院电话），{学号}是主键，但是{学院电话}只依赖于{学院名称}，并依赖于主犍{学号}，因此该设计不符合第三范式，应该把学院专门设计成一张表，学生表和学院表，两个是一对多的关系。

存在的传递依赖：{学号}–>{学院名称}–>{学院电话}

注意： 不是什么时候都需要拆分表避免冗余数据，由于差分表后会造成多表查询，也会影响查询速度。有时候可能也会有意增加冗余数据避免多表查询，这要视情况而定。

一般来说，数据库表设计满足第三范式即可，若采用更高的范式，它带来的收益就不足以补偿它带来的性能损耗了

4. BC范式

一个满足BCNF的关系模式有(参考教材《数据库系统概论》)：

• 所有非主属性对每一个码都是完全函数依赖
• 所有主属性对每一个不包含它的码也是完全函数依赖
• 没有任何属性完全函数依赖于非码的任何一组属性

上述关系满足3NF，非主属性都是依赖于候选键。但是存在主属性{班级}部分依赖于候选键{班主任，订购教材}，需要进行表的拆分

这样两张表就不存在任何属性（包括主属性和非主属性）对候选键的部分依赖以及传递依赖，比如第二张表中主键是{班级，订购教材}，非主属性{数量}完全依赖于主键。

5. 范式总结

从上面对于数据库范式进行分解的过程中不难看出，应用的范式越高，表越多，表多会带来很多问题：

• 查询时需要连接多个表，增加了SQL查询的复杂度
• 查询时需要连接多个表，降低了数据库查询性能

因此，并不是应用的范式越高越好，视实际情况而定。3NF已经很大程度上减少了数据冗余，并且基本预防了数据插入异常，更新异常，和删除异常了