MySQL数据库基础2

数据类型

1、数值类型：BIT、TINYINT、BOOL、SMALLINT、INT、BIGINT、FLOAT[(M,D)]、DOUBLE[(M,D)]、DECIMAL[(M,D)]

• FLOAT[(M,D)]：占用四个字节，M表示显示位数，D表示小数位数，精度保证：6~7位
• DOUBLE[(M,D)]：占用八个字节，M表示显示位数，D表示小数位数，精度15~16位

上面两种类型需要引起注意的是，M和D会限制数据的大小范围，举例如下：

在上表中插入数据：

报错原因是尝试插入的值1.2超过了该列的数据类型范围。

Type的double(3,3)表示了插入的数据不能大于1了，所以要注意M和D带来的数据范围的限制。

• DECIMAL[(M,D)]：是MySQL自有的一个类型，M表示显示位数，D表示小数位数，它不是按照浮点数来进行存储的，它是按照字符串来进行存储的，所以理论上来说，精度不会丢失，但是在存储的时候，一定确定存储的小数和D的关系。M最大为65，D最大为30.
  

2、文本、二进制类型：CHAR(size)、VARCHAR(size)、TEXT(大文本类型)、BLOB(二进制类型)

• 固定长度字符串

char(size)：固定长度字符串，size是可以存储的长度（个数），单位为字符，最大长度值可以为255
每个字符所占的字节和字符集强相关

• 可变长度字符串

varchar(size)：可变长度字符串，size表示该字段可以存储的最大字符数，最大长度为65535个字节

size最大有多大：

需要1~3字节用来记录字符串的长度（因为长度可变），按照最大情况计算，所以剩余字节数量就是65532；
在utf8字符集中，字符占用的字节数量为1~3字节，所以按照最大3字节计算，可以存储65535/3=21844个字符；

size的大小和字符集息息相关。size所指定的字符数是指该字段所占用的字节数的上限，并不是存储的实际字符数，例如，如果使用‘utf8mb4’字符集(一个中文字符占用四个字节)，则 varchar(50)的字段可以存储12个中文字符。

如何选择定长或变长字符串呢？

如果数据确定长度都一样，就使用定长(char)，比如身份证号、手机号等
定长的磁盘空间比较浪费，但是效率高，直接开辟好对应的空间
变长的磁盘空间比较节省，但是效率低，在不超过自定义范围的情况下，用多少，开辟多少

3、时间/日期类型

• date：日期，yyyy-mm-dd，年月日，占用三个字节
• datetime：时间日期，yyyy-mm-dd HH:ii::ss ，表示范围从1000到9999，占用八字节
• timestamp：时间戳，从1970年开始的，yyyy-mm-dd HH:ii:ss，格式和datetime完全一致，计算的是秒数，占用四个字节，时间戳可用于统一软件时间
  

我们在t3这张表中插入数据，多次执行下面这条语句：

insert into t3(d1,d2) values('2023-05-13','2023-05-13 17:11:10');

插入多条数据后，我们来看一下表中数据结果：

时间戳是自动插入的，可以看到，它是在变化的，随着时间流动而流动。

4、枚举类型

enum(’ 选项1’，选项2’，’选项3’,…);

枚举的设定提供了若干个选项的值，最终的一个单元格中，实际只存储了枚举选项中的一个值。
在插入时，不能插入枚举选项中没有的值。

5、集合类型

set(’ 选项1’，选项2’，’选项3’,…);

集合设定提供了若干个选项的值，最终的一个单元格中，可以存储选项的任意多个值。这点是和枚举的差别，枚举只允许存储一个值。

集合查询使用find_in_set函数：

find_in_set(sub, str_list)：如果sub在str_list中，则返回下标，如果不在，返回0；str_list是用逗号分隔的字符串

表的约束

表的约束是指除列字段类型约束之外的额外约束。

1、空约束

null：该属性值可以为空
not null：该属性值不可以为空，如果要求哪个属性值不能为空，在create表的时候加上not null。

2、默认值

在列定义的后面，加上default [value]；

当插入数据的时候，提供的数据不包含有默认值的列时，采用默认值

3、列描述

comment：列描述，对于插入/删除/查询没有影响，相当于是列注释（给开发者准备的）

我们创建一个表：

create table t1(id int comment 'stu id', name varchar(10) default 'xh' comment  'name');

显示一下表的创建过程：

4、zerofill格式化输出

设置了zerofill的字段会按照设定的宽度进行输出。如果本身宽度不够，高位补0，但是在真实存储的时候，还是按照原生的数字进行存储的。

int(5)在输出的时候，就会输出5位。

5、主键（主键约束）

主键是用于唯一标识数据库表中每一行记录的一列或者一组列，主键的值必须唯一、非空，并且在表的每一行记录中唯一标识该行记录，主键可以由一列或多列组成，如果由多列组成，则称为复合主键。

主键可以帮助提高数据的查询效率，并确保数据的一致性和完整性，主键常常和外键一起使用，来构建数据库之间的关系。

约束列字段关键词：primary key

需要注意的是：

主键不能为空，不能重复；
一张表只能有一个主键；
主键列通常为整数（方便建立索引）

主键不能为空：

主键不能重复：

创建主键的几种方式：

1）创建表时指定主键

• 直接在某个字段后面指定

create table [表名称] (字段1 字段1类型 primary key,…);

• 在所有字段后面指定主键

create table [表名称] (字段1 字段1类型，字段2 字段2类型，字段3 字段3类型 primary key，(字段名称));

2）alter修改表结构

alter table [表名称] add primary key([列名称])

删除主键：

alter table [表名字] drop primary key；

复合主键：

指的是多个列共同组成的主键，可以唯一标识一条记录，由于复合主键包含多列，因此可以更加准确地确定记录的唯一性。

6、自增长

auto_increment是MySQL中自增长的关键字，被设置为自增长的字段，如果不提供数据，会自动的被系统触发，系统会从当前字段中已有的最大值+1操作，得到一个新的不同的值。

自增长的特点：

任何一个字段要做自增长，前提是本身是一个索引；自增长字段必须是整数；一张表最多只能有一个自增长

7、唯一键
一张表中往往有很多字段数据不能重复，需要唯一性，可能会有人想，那就把这样的字段设置有主键，但是需要注意的是一张表中只能有一个主键，所以主键解决不了这个问题，这个时候，唯一键就出现了，用来解决表中有多个字段需要唯一性约束的问题。

关键字：unique
唯一键不能重复，可以为空。

8、外键

外键是用来建立两个表之间关联的一种机制，用于保证数据的一致性和完整性。
在关系型数据库中，可以通过外键将两个表联系起来，使得一个表中的某一个或某几列可以引用另一个表中的主键，从而在两个表之间建立关联关系。

在MySQL中，外键约束可以通过FOREIGN KEY关键字来实现，需要满足以下条件：

• 外键必须引用另一个表的主键或唯一键
• 外键约束只能在InnoDB存储引擎中使用
• 外键约束需要满足参照完整性约束，即要求在添加或更新记录时，外键值必须与另一个表中的主键或唯一键值相匹配

两个表从逻辑上区分为主表和从表，外键的约束定义在从表上，主表的字段需要是主键。
从表定义的外键，本质上是通过主表的字段值，来约束从表当中的外键列

如何定义外键：

foreign key（字段名）reference 主表(列)
在从表中定义外键。

使用外键约束可以避免不必要的数据冗余，提高数据的一致性和完整性，但是同时会增加操作的复杂度。