第6章-3 数据库设计

数据库范式

第一范式： 

        数据库第一范式（1NF）要求数据表中的每一列都是不可再分的原子值，确保数据的原子性

        比如：“联系方式”字段若同时存储邮箱和电话，则违反1NF，需拆分为“邮箱”和“电话”两列。

第二范式

1，要求符合第一范式

2，表必须有一个主键(—列或多列)

3，其他字段可由主键确定

4，二范式目的是通过拆表减少数据冗余

        主键可以是一列或者多列组成的，只要能够根据主键，马上能精确到特定的一行数据即可。

不能解决插入，更新异常的问题。

第三范式

1，要求符合第二范式

2，字段要求直接依赖于主键，不允许间接依赖，必须相互独立。

3，第三范式目的在于拆分实体(完善主从表) 

反范式设计的优缺点

优点：

1，单表查询易于优化，易于管理（把高频使用的字段，放到一起。）

2， SQL语句简单，有利于程序开发,团队协作

缺点：

3，存在数据冗余，写操作时需要额外更新从表数据

4，不合理的反范式设计会让表变得臃肿不堪

类型选择原则

1， 选择小而简单的数据类型

2，尽量避免NULL

字段类型优先级

        1，数字类型

        2，日期类型与二进制类型

        3， 字符串类型

数据类型常见种类

1，整数类型

2，实数类型

3，字符类型

4，日期及时间类型

5，特殊类型

整数类型

踩坑

        1. 有符号和无符号类型使用相同的存储空间，具有相同的性能。

        2. MySQL可以为整数类型指定显示宽度，例如INT(11)，但它不会限制值的范围。对于存储和计算，INT(1)和INT(20)是相同的

实数类型

1，浮点数

2，定点数

浮点数

浮点数就是小数点可以任意浮动的数字

浮点数的存储

        1. 将十进制小数转换成二进制小数

        2. 用二进制科学记数法表示二进制小数

        3. 示例:78.375

浮点数

1，float(单精度，4字节)

2，double(双精度，8字节)

float的存储

double的存储

float类型的存储

符号位是:0

指数位是:6 + 127=133，二进制表示为: 10000101

小数部分是:001110011，不足部分请自动补0

结果表示:

指数位的偏移量与无符号表示(了解)

        指数是有符号整数，而有符号整数的计算是比无符号整数麻烦。在实际存储指数的时候，需要把指数转换成无符号整数。float的指数部分是8位，IEEE规定这个指数的取值范围是 -126到+127，为了消除负数带来的计算上的影响（比如比较大小，加减法等），在实际存储的时候，给指数做一个映射（加上一个偏移量），即float的指数偏移量为127(double的偏移量是1023)。比如：

        指数如果是6，则实际存储的是6+127=133，即把133转换为二进制之后再存储。

        指数如果是-3，则实际存储的是-3+127=124，即把124转换为二进制之后再存储

浮点数的精度

        float：尾数位是23位（二进制），换算到十进制只能保证6位的精度运算，不能百分百保证7

位的精度运算。

        double：尾数数位是52位（二进制），换算为十进制只能保证15位的精度运算

定点数

定点数的存储

        1，DECIMAL(M，D)表示可以存储D位小数的M位数。

        2，分别为整数和小数分配存储空间。

浮点数与定点数本质区别

        浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值

使用场景

        浮点数：重量、体积、面积等计量单位。

        定点数：价格、工资、账户余额等货币数据。

字符类型

VARCHAR类型

VARCHAR变体长度,根据实际内容保存数据

        1，使用最小的符合需求的长度

        2，VARCHAR(255)以下使用额外一个字节保存长度

        3，VARCHAR(255)以上使用额外两个字节保存长度

        4，VARCHAR(5)与VARCHAR(200)内存占用不同

VARCHAR的使用场景

        1，VARCHAR适合存储长度波动大的数据,(如博客文章

        2，字符串很少被更新的场景

        3，VARCHAR适合保存多字节字符

CHAR类型

CHAR属于定长数据

        1，CHAR最大长度255

        2，CHAR类型会自动删除末尾的空格

        3，CHAR检索效率比VARCHAR高

CHAR的使用场景

        1，CHAR适合存储长度波动不大的数据,如MD5摘要

        2，CHAR适合存储短字符串

        3，CHAR类型适合存储经常更新的字符串

小字段，适合经常更新。

CHAR与VARCHAR区别

        char类型是定长的，根据定义的字符串长度分配存储空间。适合如身份证号、手机号等具有固定长度或长度变化不大的字符串

        varchar类型是变长的， 仅分配必要的存储空间，但varchar需要使用1或2个额外字节记录字符串的长度。

日期和时间类型

DATETIME日期类型

DATETIME日期时间类型，占用8个字节

        与时区无关

        可保存到毫秒

        可保存时间范围大

PS：不要使用字符串存储日期类型

TIMESTAMP时间戳

TIMESTAMP 时间戳，占用4个字节。时间范围：1970-01-01到2038-01-19

        精确到秒

        采用整型存储

        依赖于时区

        自动更新timestamp列的值

datetime与timestamp的区别

        datetime的默认值为null，timestamp的默认值不为null，且为系统当前时间（current_timestatmp）。

        对于timestamp，它把客户端插入的时间从当前时区转化为世界标准时间（UTC）进行存储，查询时再逆向返回。但对于datetime，不做转换处理。

特殊类型

IP地址

        IPv4地址实际是32位无符号整数，所以应该用无符号整数存储IP地址，而不是用VARCHAR(15)来存储。

        MySQL提供INET_ATON()和INET_NTOA()函数在整数与字符串之间转换。

小结：

        设计表字段的时候，遵循：选择小而简单的数据类型，数值类型优先。

        上一篇：《第6章-2 schema管理》

        下一篇：