SQL 字段类型-上

总

数据类型	关键字	描述
	整数
迷你整型	tinyint	使用1个字节存储整数
短整型	smallint	使用2个字节存储整数
中整型	mediumint	使用3个字节存储整数
标准整型	int	使用4个字节存储整数
	小数
大整型	bigint	使用8个字节存储
单进度	float (.. , ..)	使用4个字节 ...表示宽度 后面的... 表示小数位
双精度	double(.. , ..)	使用8个字节
定点型	decimal(.. , ..)	不是固定长度每9个数字使用4个字节存储
	字符串
定长型	char(L) L<=255	使用L * 字符集的字节长度
变长型	varchar(Max_L) Max_L<=65535	空间由实际存储数据 + 记录长度决定，但是小于Max_L.
text：普通字符
迷你文本	tinytext	不超过2 ^ 8 -1个字符
普通文本	text	不超过 2 ^ 16 - 1个字符
中型文本	mediumtext	不超过 2 ^ 24 - 1 个字符
长文本	longtext	不超过 2 ^ 32 - 1 个字符（4G）
blob：二进制字符（与text类似）
迷你文本	tinyblob	不超过2 ^ 8 -1个字符
普通文本	blob	不超过 2 ^ 16 - 1个字符
中型文本	mediumblob	不超过 2 ^ 24 - 1 个字符
长文本	longblob	不超过 2 ^ 32 - 1 个字符（4G）
	映射
枚举型	set(元素1,元素2,....) 最大64个元素	数据只能出现定义时的其中的的数据类型
集合	set(元素1,元素2,....) 最大64个元素	数据只能出现定义时的其中的的数据类型
	时间日期
年	year(4) == year	1个字节范围为1901-2155年（256年）
时间戳	timestamp	4个字节范围为 1971-1-1-0-0-0 ~ 2155-12-31-59-59
日期	date	3个字节 1001-01-01~9999-12-31
日期时间	datetime	8个字节 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
时间	time	3个字节 -838:59:59 - 838:59:59

SQL 字段类型

定义方式

use xxxx; 使用xxxx数据库后
create table table_name
{
   username   char(20)/*数据类型*/  null/*属性*/,
   password    varchar(10) not null; 
   字段名...
}

整型数据

• 和高级语言一样可以用 int unsigned 修饰无符号放在后面

数据类型	关键字	描述
迷你整型	tinyint	使用1个字节存储整数
短整型	smallint	使用2个字节存储整数
中整型	mediumint	使用3个字节存储整数
标准整型	int	使用4个字节存储整数
大整型	bigint	使用8个字节存储

显示宽度：int(L)，

• 整数在数据库中显示的符号（数字+符号）个数

• 显示宽度一般是类型能表示的最大值对应的数字个数（通过desc查看表字段显示）

• 显示宽度包含符号（如果允许为负数，-负号会增加一个宽度）

• 显示宽度可以主动控制：创建字段时加括号确定

• 显示宽度不会影响类型能表示的最大数值

• 可以通过zerofill让不够宽度的数值补充到对应宽度：在字段类型后使用zerofill

浮点型数据

基础型

• 浮点数超过精度范围会自动进行四舍五入

  • 单精度：float，使用4个字节存储，精度范围为6-7位有效数字

  • 双精度：double，使用8个字节存储，精度范围为14-15位有效数字。

• 精度可以指定整数和小数部分

  • 默认不指定，整数部分不超过最大值，小数部分保留2位

  • 可以指定：float/double(总长度,小数部分长度)

  • 如 float(6,3) ， 后面插入的数据被限制在 有效数位==6 小数部分数位 == 3

• 可以使用科学计数法 ...E...

定点型

• 定点型可以指定整数部分长度和小数部分长度

  • 可以指定：decimal(有效数位,小数部分数位)

  • 如 123.123 有效数位==6 小数部分数位 == 3

  • 默认不指定，10位有效整数，0位小数

  • 有效数位不超过65个

• 数据规范

  • 整数部分超出报错

  • 小数部分超出四舍五入

数据类型	关键字	描述
单进度	float (.. , ..)	使用4个字节 ...表示宽度 后面的... 表示小数位
双精度	double(.. , ..)	使用8个字节
定点型	decimal(.. , ..)	不是固定长度每9个数字使用4个字节存储

字符串类型

定长型

• 定长的访问效率较高，但是空间利用率较低

• 字符串数据使用单引号或者双引号包裹

变长型

• 变长需要额外产生1-2个字节，用来记录实际数据的长度

  • 数据长度小于256个，多1个字节

  • 数据长度大于256个，多2个字节

• 利用存储空间 ↑ 效率 ↓

文本字符串

• text：普通字符用于存储文本数据，例如文档、文章等

• blob：二进制字符（与text类似）用于存储二进制数据，例如图像、音频、视频等。

• 文本字符串会自动根据文本长度选择适合的具体类型。

• 一般在文本超过255个字符时，都会使用text（blob现在极少使用）

数据类型	关键字	描述
定长型	char(L) L<=255	使用L * 字符集的字节长度
变长型	varchar(Max_L) Max_L<=65535	空间由实际存储数据 + 记录长度决定，但是小于Max_L.
text：普通字符
迷你文本	tinytext	不超过2 ^ 8 -1个字符
普通文本	text	不超过 2 ^ 16 - 1个字符
中型文本	mediumtext	不超过 2 ^ 24 - 1 个字符
长文本	longtext	不超过 2 ^ 32 - 1 个字符（4G）
blob：二进制字符（与text类似）
迷你文本	tinyblob	不超过2 ^ 8 -1个字符
普通文本	blob	不超过 2 ^ 16 - 1个字符
中型文本	mediumblob	不超过 2 ^ 24 - 1 个字符
长文本	longblob	不超过 2 ^ 32 - 1 个字符（4G）

枚举型 enum：

• 枚举型：enum,是一种映射存储方式，以较小的空间存储较多的数据

• 数据只能出现定义时的其中的一种的数据类型

• 枚举使用1-2个字节存储，最多可以设计65535个选项

• 枚举实际存储是使用数值，映射对应的元素数据，从1开始

• 使用枚举的作用：

  • 规范数据模型

  • 优化存储空间，因为保存的是映射值

枚举数据	映射值
数据1	1
数据2	2
…	…
数据N	N（小于65535）

# 要保证未来数据只能出现在某种可能中，所以要先列出来，可以使用enum
create table t_16(
    type enum('小朋友','少年','青年','中年','老年')
)charset utf8;
​
insert into t_16 values('少年');
insert into t_16 values('仙人');  # 不存在的数据不能插入
​

数据类型	关键字	描述
枚举型	enum(元素1,元素2,....) 最大65535--2个字节	数据只能出现定义时的其中的一种的数据类型

集合 set:

• 集合是在定义时确定可能出现的元素进行穷举，而后数据只能出现定义时其中的元素（可以是多个 enum 是单个）

• 集合使用1-8个字节存储数据，最多可以设计64个元素，

执行步骤

1. 指令开始

2. 插入数据

   • 读取映射关系

   • 元素选中==位值为1

   • 元素未选中==位值为0

3. 转化成十进制存储

4. 结束

集合数据	映射位
数据1	00000001
数据2	00000010
…	…
数据8	10000000

数据类型	关键字	描述
集合	set(元素1,元素2,....) 最大64个元素	数据只能出现定义时的其中的的数据类型

# 爱好可以是多种，并非固定的，但是只能从规定的类型中选择
create table t_17(
    hobby set('足球','篮球','羽毛球','网球','乒乓球','排球','台球','冰球')
)charset utf8;
​
insert into t_17 values('足球');
insert into t_17 values('冰球,台球,篮球');
​

时间日期类型

年year

• MySQL中使用1个字节存储年份

• year能够表示的范围是1901-2155年（256年）

  • year的特殊值是：0000

• year允许用户使用两种方式设计（效果一样）

  • year

  • year(4)

• 因为year字段表示的范围有限，所以通常会使用字符串来存储（牺牲空间换安全）

create table t_18(
    y1 year,
    y2 year(4)
)charset utf8;
insert into t_18 values(1901,2155);

时间戳：timestamp

• MySQL中时间戳表现形式不是秒数，而是年月日时分秒格式

• 输入方式

  • YYYY-MM-DD HH:II::SS

  • YYYYMMDDHHIISS

• timestamp使用4个字节存储

  • 表示范围是 1971年1月1日0时0分0秒-2155年12月31日23是59分59秒

  • timestamp可以使用 0000-00-00 00:00:00

• 所对应的记录不论哪个字段被更新，该字段都会更新到当前时间

• 但在MySQL8中需要主动使用on update current_timestamp才会自动更新

create table t_19(
    goods_name varchar(10),
    goods_inventory int unsigned,
    change_time timestamp
)charset utf8;
​
insert into t_19 values('Nokia3110',100,'1971-01-01 00:00:00');
insert into t_19 values('Nokia7100',100,'19710101000000');
--自动更新
alter table t_19 add c_time timestamp on update current_timestamp;
update t_19 set goods_inventory = 80; --自动跟新为单曲日期

日期date

• 使用3个字节存储数据

• 存储日期的格式为：YYYY-MM-DD

• 存储的范围跨度很大，存储区间是1000 - 9999年：1001-01-01~9999-12-31

--记录个人生日
create table t_20(
    name varchar(10),
    birth date
)charset utf8;
​
insert into t_20 values('Jim','2000-12-12');
insert into t_20 values('Tom','10011212');
​

日期时间datatime

• 使用8个字节存储数据

• 存储格式为：YYYY-MM-DD HH:II:SS

• 存储区间为：1000-01-01 00:00:00 到9999-12-31 23:59:59

create table t_21(
    name varchar(10),
    birth datetime
)charset utf8;
​
insert into t_21 values('Jim','2000-12-12 12:12:12');
insert into t_21 values('Tom','10011212182323');
​

时间time

• 使用3个字节存储数据

• 数据范围是 -838:59:59 - 838:59:59

• 数据插入的格式分为两种

  • 时间格式：[H]HH:II:SS（[ ]表示可以没有）

  • 时间段格式：D HH:II:SS（D表示天）

  • time类型通常被用来做时间段计算：如多少天后的什么时间点（可以理解为过期检查）

# 记录用户登录的具体时间
# 具体登录时间可以使用时间戳（包含年月日时分秒信息）
# 也可以时间datetime格式，或者date+time双字段格式（具体后面学习范式时会知道该怎么用）
create table t_22(
    login_time1 int unsigned,
    login_time2 datetime,
    login_date date,
    login_time3 time
)charset utf8;
​
insert into t_22 values(12345678,'2000-12-12 12:12:12','2000-12-12','12:12:12');
insert into t_22 values(1234567,'2000-12-12 12:12:12','2000-12-12','3 12:12:12');
​

数据类型	关键字	描述
年	year(4) == year	1个字节范围为1901-2155年（256年）
时间戳	timestamp	4个字节范围为 1971-1-1-0-0-0 ~ 2155-12-31-59-59
日期	date	3个字节 1001-01-01~9999-12-31
日期时间	datetime	8个字节 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
时间	time	3个字节 -838:59:59 - 838:59:59