首页 » 数据库 » MySql

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

MySql 2022-01-12

文章目录

      • 一、数据类型
        • 1. 数值类型
        • 2. 字符串类型
        • 4. 时间和日期类型
        • 5. enum和set
      • 二、MySQL运算符
        • 1. 算数运算符
        • 2. 逻辑运算符
        • 3. 比较运算符
      • 三、完整性约束
      • 四、关系型数据库表设计
        • 1. 一对一
        • 2. 一对多
        • 3. 多对多
      • 五、关系型数据库范式
        • 1. 第一范式
        • 2. 第二范式
        • 3. 第三范式
        • 4. BC范式
        • 5. 范式总结

一、数据类型

1. 数值类型

MySQL数据类型定义了数据的大小范围,因此使用时选择合适的类型,会降低表占用的磁盘空间,间接减少了磁盘I/O的次数,提高表的访问效率,而且索引的效率也和数据的类型息息相关。
MySQL数据类型、运算符以及数据库范式
使用数值类型的时候若写成:INT(M)表示显示M位,而不是该数值占M字节

2. 字符串类型

MySQL数据类型、运算符以及数据库范式
使用字符串类型的时候若写成:CHAR(M)表示该字符串占M+1字节

4. 时间和日期类型

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

5. enum和set

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

创建表的时候可以使用:

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

二、MySQL运算符

1. 算数运算符

MySQL数据类型、运算符以及数据库范式
此外作为通配符:%可以匹配任意一个或多个字符,_匹配任意单个字符

2. 逻辑运算符

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

3. 比较运算符

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

三、完整性约束

  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') ); 

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

四、关系型数据库表设计

1. 一对一

在子表中增加一列,关联父表的主键
MySQL数据类型、运算符以及数据库范式

2. 一对多

电商系统的用户-订单是一对多的关系
MySQL数据类型、运算符以及数据库范式

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

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

3. 多对多

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

为解决冗余信息过多的问题,我们添加一个中间表,根据order_id就可以查看订单的详细信息
MySQL数据类型、运算符以及数据库范式
实际情况中,一个订单不可能记录一种商品多次,在OrderList表中可以使用order_id和pid联合创建一个索引,加速查找

五、关系型数据库范式

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

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

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

1. 第一范式

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

MySQL数据类型、运算符以及数据库范式
应该将地区分成省、市、区等。如果直接就是一个地址,在电商系统中若我们需要统计某个省的订单量,如果没有把一整个地址划分成省市区,则无法统计

2. 第二范式

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

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

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

实际上,学生和课程是多对多的关系,应该插入一个中间表
MySQL数据类型、运算符以及数据库范式
这样的话,在选课表中学号和课程id作为联合主键,成绩这个属性依赖于联合主键

MySQL数据类型、运算符以及数据库范式
参考博客:三张图搞透第一范式(1NF)、第二范式(2NF)和第三范式(3NF)的区别

3. 第三范式

符合2NF,不符合3NF:
MySQL数据类型、运算符以及数据库范式

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

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

存在的传递依赖:{学号}–>{学院名称}–>{学院电话}
MySQL数据类型、运算符以及数据库范式
注意: 不是什么时候都需要拆分表避免冗余数据,由于差分表后会造成多表查询,也会影响查询速度。有时候可能也会有意增加冗余数据避免多表查询,这要视情况而定。

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

4. BC范式

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

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

MySQL数据类型、运算符以及数据库范式
上述关系满足3NF,非主属性都是依赖于候选键。但是存在主属性{班级}部分依赖于候选键{班主任,订购教材},需要进行表的拆分

MySQL数据类型、运算符以及数据库范式
这样两张表就不存在任何属性(包括主属性和非主属性)对候选键的部分依赖以及传递依赖,比如第二张表中主键是{班级,订购教材},非主属性{数量}完全依赖于主键。

5. 范式总结

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

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

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


上一篇:MySQL配置主从服务器(一主多从)下一篇:MySQL的driverClassName与url使用方式
程序园_程序员的世界 Copyright © 2020- www.580doc.com. Some Rights Reserved.