32. 基本数据类型、约束条件

1. 基本数据类型

1.1 整型

整数类型	所占字节	所占位数	无符号数的取值范围 unsigned	有符号数的取值范围
TINYINT	1	8	0~255	-128~127
SMALLINT	2	16	0~65535	-32768~32767
MEDIUMINT	3	24	0~16777215	-8388608~8388607
INT	4	32	0~4294967295	-2147483648~2147483647
BIGINT	8	64	0~18446744073709551615	-9223372036854774808~9223372036854774807

1. 无符号数在创建表的时候要给列加约束条件unsigned

 2. 对于所有整数类型而言，括号内的参数代表显示宽度，而不是最大长度

     显示宽度并不影响存储大小

　 显示宽度一般与zerofill配合使用，在MySQL 8.0.17及以后的版本中已经不推荐使用显示宽度

 1.2 浮点型

两种浮点型：FLOAT、DOUBLE

这两种类型只能用来表示近似数值，可能会导致精度损失。

浮点数不适用于要求精确结果的计算，比如财务应用。

如果需要更高的精度，应该使用DECIMAL类型。

浮点数类型	中文名称	小数点后有效位数	语法	语法解释	所占字节	所占位数	取值范围
FLOAT	单精度浮点数	7位	FLOAT(M, D)	M是数字的总位数(精度),包括小数点左边和右边,范围是0~255 D是小数点后的位数(标度),必须小于等于M,范围是0~30，	4	32	-2^128~2 ^128 换算成小数约等于： −3.40282347×10^38~3.40282347×10^38
DOUBLE	双精度浮点数	15位	DOUBLE(M, D)	同上	8	64	-2^1024~ 2^1024 换算成小数约等于： −1.7976931348623157×10^308~ 1.7976931348623157×10^308

 一个定点数类型：DECIMAL

DECIMAL不是浮点型，但它用于存储定点数，适合需要精确计算的场景，如金融数据。

DECIMAL类型存储为字符串，具有更高的精度和范围。

语法：DECIMAL(M, D)　　

解释：M是数字的总位数(精度)，包括小数点左边和右边，范围是1~65

　　　D是小数点后的位数(标度)，必须小于等于M，范围是0~30

如果不指定 m 和 d，MySQL 会使用默认值 DECIMAL(10,0)

浮点型与定点数类型应用：

使用三种类型分别创建三个表

 插入数据，小数点后的位数为22位

 查看插入的数据

float的前7位有效，之后的数据发生变化

num的前15位有效，之后的数据发生变化

decimal的有效位数与定义时保持一致

1.3 字符串

[1]CHAR

固定长度：CHAR是固定长度的字符串数据类型，占用固定长度的空间，不足的部分用空格填充

性能：由于CHAR的长度是固定的，在处理大量相似长度的字符串时，具有更快的读写速度，因为数据库引擎不需要计算每个值的实际长度

空间效率：如果很多数据远小于最大长度，将导致空间浪费

[2]VARCHAR

可变长度：VARCHAR是可变长度的字符串数据类型，只占用实际字符串所需的长度加上一个或两个字节（用于存储字符串长度信息）。

性能：VARCHAR 在处理变长字符串时更高效，特别是当存储的字符串长度差异较大时。

空间效率：对于长度变化很大的数据，VARCHAR 通常更节省空间，因为它不会为较短的字符串分配多余的空间。

[3]实际应用

使用char可以不指定长度，默认长度为1；

不指定长度时插入字符串的长度不能超过1。

 使用varchar必须指定长度，不指定长度会报错。

 数据长度超过varchar指定长度会报错

[4]补充知识点：统计字符长度char_length

语法：select char_length(列名) from 表名;

varchar查询到的字符串长度为实际长度：

 char查询到的字符串长度也为实际长度：

[5]补充知识点：系统变量sql_mode与PAD_CHAR_TO_FULL_LENGTH模式

sql_mode是一个系统变量，它用于控制MySQL如何解析和执行SQL语句，以及如何进行数据校验。这个变量可以看作是一组约束和规范，确保数据的准确性、完整性和一致性。

这包括对无效数据的处理、日期格式的严格程度、是否允许隐式类型转换等。

使用模糊查询查看sql_mode：SHOW VARIABLES LIKE 'sql_mode';

PAD_CHAR_TO_FULL_LENGTH 是MySQL中的一个SQL模式选项，它影响CHAR类型字段值在检索时的行为。默认情况下，当检索CHAR类型字段的值时，MySQL会删除尾部的空格。如果启用了PAD_CHAR_TO_FULL_LENGTH模式，则不会删除尾部空格，检索到的CHAR值将填充到它们的全长度。

这个模式不适用于VARCHAR类型的字段，因为VARCHAR字段在检索时保留尾随空格。需要注意的是，从MySQL 8.0.13开始，PAD_CHAR_TO_FULL_LENGTH已经不被推荐使用，并且在未来的MySQL版本中可能会被移除。

临时修改sql_mode：set sessison sql_mode="......,PAD_CHAR_TO_FULL_LENGTH ";　　　　

永久修改sql_mode：set global sql_mode="......,PAD_CHAR_TO_FULL_LENGTH ";

注意：在运行以上修改sql_mode的命令时不能忽略系统原有的sql_mode，否则会造成原有sql_mode丢失

临时添加PAD_CHAR_TO_FULL_LENGTH ：

 添加PAD_CHAR_TO_FULL_LENGTH的效果是：char_length变成了定长而不是实际长度

1.4 时间类型

DATE：年月日 　　　　　　　　YYYY-MM-DD
TIME：时分秒 　　 　　　　　　HH:MM:SS
DATETIME：年月日时分秒 　　  YYYY-MM-DD HH:MM:SS
YEAR：年 　　　　　　　　　   YYYY 

create table t1(
    concrete_time datetime,
    nian year,    
    NianYueRi date,
    ShiFenMiao time
);

时间数据类型为字符串，一定要用单引号或双引号括起来。

insert into t1 (concrete_time, nian, NianYueRi, ShiFenMiao) values(
    '1999-06-09 09:25:36',
    '2000',
    '2001-09-01',
    '09:26:31'
);

 1.5 字符串对象之枚举类型与集合类型

[1]枚举类型

多个预定义的值中选一个

enum(值1,值2,值3,...)　　括号内的值必须为字符串类型，用引号括起来

create table student(
    id int,
    name varchar(10),
    grade enum('1','2','3','4','5','6')
);

 只能选择一个值不能选择多个值

insert into student (id,name,grade) values(1,'avril','1');
insert into student (id,name,grade) values(2,'lavigne','1,2'); 

[2]集合类型

多个预定义的值中选多个

set(值1,值2,值3,...)　　括号内的值必须为字符串类型，用引号括起来

create table t1(
    id int,
    name varchar(10),
    hobby set('run', 'game', 'jqk')
);

 既可以选多个值，也可以选一个值

insert into t1 (id, name, hobby) values(1, 'avril', 'run');
insert into t1 (id,name,hobby) values(2, 'lavigne', 'run,game,jqk');

2. 约束条件

2.1 创建表语法回顾

create table 表名(
       列名1 数据类型[(最多字符个数)]  [约束条件],
       列名2 数据类型[(最多字符个数)]  [约束条件],
       ...
       列名n 数据类型[(最多字符个数)]  [约束条件]
);

列名和数据类型必填，中括号里的内容选填。
列名不可重复。

2.2 null / not null

如果约束条件为not null，那么在插入数据时该列的值不能为空

如果约束条件为null，那么在插入数据时该列的值可以为空

建表：

create table t1(id int not null, name varchar(10) null);

 插入数据：

insert into t1 (id) values(1);　　--id不能为空，name可以为空，运行成功
insert into t1 (name) values('avril');　　--id不能为空，name可以为空，运行失败

 2.3 唯一性约束

关键字：unique　　列的值必须唯一

建表：

create table t1(id int, name varchar(10) unique);

 插入数据：

insert into t1 values(1, 'abc');
insert into t1 values(2, 'abc');　　--name的值传入重复，报错

 2.4 唯一性+不为空≈主键

unique+not nulll建表：

create table t1(id int not null unique, name varchar(20));

 primary key建表与unique+not null建表效果几乎一样：

create table t2(id int primary key, name varchar(20));

主键约束：

主键约束是一种数据库约束，用来强制数据的完整性和唯一性。当一个或一组字段被定义为主键时，这个约束会自动应用到这些字段上。
主键约束保证了：
该列或这组列的值在整个表中是唯一的（不允许重复）。
该列或这组列不能包含NULL值。

主键字段：

被设置为主键的单个字段或多字段组合。可以是一个字段作为主键，也可以是多个字段一起构成复合主键。
单一主键：例如，id INT PRIMARY KEY，这里id就是主键字段。
复合主键：例如，PRIMARY KEY (column1, column2)，这里column1和column2共同构成了复合主键。

创建一个复合主键示例：

create table t3(id int,name varchar(20),hobby varchar(10),primary key(id, name));

主键值：

主键值是插入到主键字段中的具体值。这些值必须满足主键约束的要求，即唯一且非空。

主键的作用：

添加主键primary key的字段既有unique+not null唯一、非空的约束，还会在定义主键时自动在该列上创建一个唯一索引，这个索引有助于提高基于主键的查询、插入和更新操作的性能。

primary key 与unique+not null区别：

 一个表只能有一个主键，但可以有多个NOT NULL UNIQUE字段。

2.5 AUTO_INCREMENT

自增，是列的约束条件，通常用于主键，自动生成唯一的数字

代码示例：

create table t1(id int primary key auto_increment, name varchar(20));
insert into t1(name) values('avril'),('lavigne');

自动生成后，一个主键自然数只会出现一次；若删除改行记录，或者delele from 表名清空表中所有数据后，重新递增记录时，主键字然数会跳过直接+1

 指定主键起始位置：

使用表修改语句将AUTO_INCREMENT的字段数指定为自定义位置

alter table 表名 AUTO_INCREMENT=指定主键起始位置；

如果表中主键数值间隔，插入数据时以最后的主键数值为准递增。

如果表中主键数值间隔，指定起始位置的数值只能大于表中最大的数值，起始位置为表中间隔的数值无效。