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

文章目录

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

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

文章图片

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

文章图片

使用字符串类型的时候若写成：CHAR(M)表示该字符串占M+1字节
MySQL中char与varchar的类型区别
4. 时间和日期类型

文章图片

5. enum和set 这两个类型，都是限制该字段只能取固定的值，但是枚举字段只能取一个唯一的值，而集合字段可以取任意的值
创建表的时候可以使用：

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

二、MySQL运算符
1. 算数运算符

文章图片

此外作为通配符：%可以匹配任意一个或多个字符，_匹配任意单个字符
2. 逻辑运算符

文章图片

3. 比较运算符

文章图片

三、完整性约束

主键约束：primary key
自增键约束：auto_increment
唯一键约束：unique
非空约束：not null
默认值约束：default
外键约束：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. 一对多电商系统的用户-订单是一对多的关系

文章图片

【数据库|MySQL数据类型、运算符以及数据库范式】在子表中增加一列关联父表的主键，在Order表中添加用户的id
缺点：Order表有很多冗余信息，order_id、uid、total_price、addr_info
3. 多对多电商系统的商品-订单是一对多的关系
为解决冗余信息过多的问题，我们添加一个中间表，根据order_id就可以查看订单的详细信息

文章图片

实际情况中，一个订单不可能记录一种商品多次，在OrderList表中可以使用order_id和pid联合创建一个索引，加速查找
五、关系型数据库范式
使用数据库范式有很多好处，但是最重要的好处归结为三点：

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

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

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

文章图片

应该将地区分成省、市、区等。如果直接就是一个地址，在电商系统中若我们需要统计某个省的订单量，如果没有把一整个地址划分成省市区，则无法统计
2. 第二范式在1NF的基础上，消除非主属性对候选码的部分依赖，非主属性完全依赖于候选码。如果不是完全依赖候选码，则应该拆分成新的实体，设计成一对多的实体关系。
例如：选课关系表SelectCourse(学号，姓名，年龄，课程名称，成绩，学分)中，(学号，课程名称)是联合主键，但是学分字段只和课程名称有关，和学号无关，相当于只依赖联合主键中的一个字段，不符合第二范式
由于姓名、年龄只和学号有关，和课程名称无关，所以在存储的多行中很有可能学号、姓名、年龄都相同，只有课程名称不同，这就造成了数据冗余。将来无论是更新、删除都会有大量的数据同时改变，效率低下。
实际上，学生和课程是多对多的关系，应该插入一个中间表

文章图片

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

文章图片

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

文章图片

3NF消除所有非主属性对键的传递依赖以及部分依赖，就是非主属性之间不得有依赖关系，都必须直接完全依赖于候选码
示例：学生关系表为Student（学号，姓名，年龄，学院名称，学院地点，学院电话），{学号}是主键，但是{学院电话}只依赖于{学院名称}，并依赖于主犍{学号}，因此该设计不符合第三范式，应该把学院专门设计成一张表，学生表和学院表，两个是一对多的关系。
存在的传递依赖：{学号}–>{学院名称}–>{学院电话}

文章图片

注意：不是什么时候都需要拆分表避免冗余数据，由于差分表后会造成多表查询，也会影响查询速度。有时候可能也会有意增加冗余数据避免多表查询，这要视情况而定。
一般来说，数据库表设计满足第三范式即可，若采用更高的范式，它带来的收益就不足以补偿它带来的性能损耗了
4. BC范式一个满足BCNF的关系模式有(参考教材《数据库系统概论》)：