数据类型(表相关操作)
一. 介绍
存储引擎决定了表的类型，而表内存放的数据也要有不同的类型，每种数据类型都有自己的宽度，但宽度是可选的.
二. 数值类型
1. 整型
medium /ˈmiːdiəm/ 中等
分类: tinyint(1字节) smallint(2字节) mediumint(3字节) int或integer(4字节) bigint(8字节)
作用: 存储年龄、等级、id、号码等等
默认显示宽度: 11
默认符号类型: 有符号
注意!!!: 只有整型括号里面的数字不是表示限制位数.
"""
前提: 非严格模式下
如果数字没有超出括号内指定的长度, 那么默认用空格填充至括号内的长度.
如果数字超出括号内指定的长度, 那么有几位就存几位.(但是还是要遵守最大范围.)
"""
举例: int的存储宽度是4个Bytes，即32个bit，即2**32
无符号最大值为：4294967296-1
有符号最大值：2147483648-1
有符号和无符号的最大数字需要的显示宽度均为10，而针对有符号的最小值则需要11位才能显示完全，所以int类型默认的显示宽度为11是非常合理的.
# 无符号约束条件指定(默认不指定有符号)
unsigned /ʌn'saɪnd/ 无符号
create table 表名(id 类型(宽度) unsigned);
# 用0填充宽度(默认不指定用空格填充)
create table 表名(id 类型(宽度) zerofill);
验证:
# ============ tinyint(1字节): 2 ** 8 = 256 ============
# 无符号: 0 ~ 255
drop table t1;
create table t1(id tinyint(2) unsigned);
insert into t1 values(-1),(0),(255),(256);
select * from t1;
# 有符号: -128 ~ 127
drop table t1;
create table t1(id tinyint(2));
insert into t1 values(-129),(-128),(127),(128);
select * from t1;
# ============ smallint(2字节): 2 ** 16 = 65536 ============
# 无符号: 0 ~ 65535
drop table t1;
create table t1(id smallint(2) unsigned);
insert into t1 values(-1),(0),(65535),(65536);
select * from t1;
# 有符号: -32768 ~ 32767
drop table t1;
create table t1(id smallint(2));
insert into t1 values(-32769),(-32768),(32767),(32768);
select * from t1;
# ============ mediumint(3字节): 2 ** 24 = 16777216 ============
# 无符号: 0 ~ 16777215
drop table t1;
create table t1(id mediumint(2) unsigned);
insert into t1 values(-1),(0),(16777215),(16777216);
select * from t1;
# 有符号: -8388608 ~ 8388607
drop table t1;
create table t1(id mediumint(2));
insert into t1 values(-8388609),(-8388608),(8388607),(8388608);
select * from t1;
# ============ int或integer(4字节): 2 ** 32 = 4294967296 ============
# 无符号: 0 ~ 4294967295
drop table t1;
create table t1(id int(6) unsigned);
insert into t1 values(-1),(0),(4294967295),(4294967296);
select * from t1;
# 有符号: -2147483648 ~ 2147483647
drop table t1;
create table t1(id int(6));
insert into t1 values(-2147483649),(-2147483648),(2147483647),(2147483648);
select * from t1;
# ============ bigint(8字节): 2 ** 64 = 18446744073709551616 ============
# 无符号: 0 ~ 18446744073709551615
drop table t1;
create table t1(id bigint(6) unsigned);
insert into t1 values(-1),(0),(18446744073709551615),(18446744073709551616);
select * from t1;
# 有符号: -9223372036854775808 ~ 9223372036854775807
drop table t1;
create table t1(id bigint(6));
insert into t1 values(-9223372036854775809),(-9223372036854775808),(9223372036854775807),(9223372036854775808);
select * from t1;
# ============ 用zerofill测试整数类型的显示宽度 ============
# 无符号: 0 ~ 4294967295
# 有符号: -2147483648 ~ 2147483647
# 以上都是10位数
drop table t1;
create table t1(id int(6) zerofill);
insert into t1 values(-12345),(12345),(123456),(9223372036854775808);
select * from t1;
# 总结: 针对整型字段 括号内无需指定宽度 因为它默认的宽度以及足够显示所有的数据了#
2. 浮点型
medium /ˈmiːdiəm/ 中等
分类: float double decimel
作用: 存储身高、体重、薪资等等
# 存储限制
'''
255类型: 代表所有的数总共的位数是255位, 小数位占到了30位
65类型: 代表所有的位数总共是65位, 小数位占30位
'''
float(255,30) # 总共255位 小数部分占30位
double(255,30) # 总共255位 小数部分占30位
decimal(65,30) # 总共65位 小数部分占30位
# 精度: float < double < decimal
精确度验证:
# =============== float(255, 30) ===============
drop table t1;
create table t1(id float(255, 30));
insert into t1 values(1.012345678901234567890123456789);
select * from t1;
des
# =============== double(255, 30) ===============
drop table t1;
create table t1(id double(255, 30));
insert into t1 values(1.012345678901234567890123456789);
select * from t1;
# =============== decimal(65, 30) ===============
drop table t1;
create table t1(id decimal(65, 30));
insert into t1 values(1.012345678901234567890123456789);
select * from t1;
三. 字符串类型
1. char和varchar介绍
char默认没有指定宽度默认为： 1
varchar没有默认必须指定指定宽度.
"""
char: 定长
char(4) 数据超过四个字符直接报错 不够四个字符空格补全
varchar: 变长
varchar(4) 数据超过四个字符直接报错 不够几个存几个
"""
# 修改sql_mode=pad_char_to_full_length让MySQL不要做自动剔除操作. 使用char_length就可以统计没有被剔除的char字段长度.
select char_length(字段) from 表名;
set session sql_mode='no_engine_substitution,pad_char_to_full_length';
# ================ char ================
drop table t1;
create table t1(name char(4));
insert into t1 values('a');
select * from t1;
select char_length(name) from t1; # 输出4(1个字符 + 3个空格)
# ================ varchar ================
drop table t1;
create table t1(name varchar(4));
insert into t1 values('a');
select * from t1;
select char_length(name) from t1; # 输出1(1个字符)
'''
首先可以肯定的是 char硬盘上存的绝对是真正的数据 带有空格的. 只是mysql在查寻时的时候默认会将右边的空格会默认去掉.
'''
2. char类型和varchar类型比较
# char类型：定长，简单粗暴，浪费空间，存取速度快
字符长度范围：0-255（一个中文是一个字符，是utf8编码的3个字节）
存储：
存储char类型的值时，会往右填充空格来满足长度
例如：指定长度为10，存>10个字符则报错，存<10个字符则用空格填充直到凑够10个字符存储
检索：
在检索或者说查询时，查出的结果会自动删除尾部的空格，除非我们打开pad_char_to_full_length SQL模式（SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';）
空间节省情况:
当存得时候, 字符数超过了或者刚刚等于指定得宽度 , char类型更加节省空间.(提示: 在非严格模式下)
# varchar类型：变长，精准，节省空间，存取速度慢
字符长度范围：0-65535（如果大于21845会提示用其他类型 。mysql行最大限制为65535字节，字符编码为utf-8：https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html）
存储：
varchar类型存储数据的真实内容，不会用空格填充，如果'ab ',尾部的空格也会被存起来
强调：varchar类型会在真实数据前加1-2Bytes的前缀，该前缀用来表示真实数据的bytes字节数（1-2Bytes最大表示65535个数字，正好符合mysql对row的最大字节限制，即已经足够使用）
如果真实的数据<255bytes则需要1Bytes的前缀（1Bytes=8bit 2**8最大表示的数字为255）
如果真实的数据>255bytes则需要2Bytes的前缀（2Bytes=16bit 2**16最大表示的数字为65535）
检索：
尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容
空间节省情况:
当存得时候, 字符数小于指定得宽度varchar类型, 更加节省空间, 但是大多数情况下varchar更加节省空间.
使用varchar得真正的地方: 基于网络IO得情况, varchar更好.
因为mysql客户端提交命令通过网络到达mysqld服务端执行这条命令, 虽然char查询速度比varchar在一定程度上要快, 但是它们都基于网络IO.
举个例子:
char查询需要1秒 网络IO5秒
varchar查询需要2秒 网络IO4秒
这时char查询在这种情况下单纯的说char的查询速度块, 似乎和通过varchar查询数据反馈给客户端并没有任何的差别. 一共都是6秒.
# 到底如何取舍可以根据一下几个方面考虑:
1、对于MyISAM表,尽量使用Char:
对于那些经常需要修改而容易形成碎片的myisam和isam数据表就更是如此，它的缺点就是占用磁盘空间;
2、对于InnoDB表,尽量使用varVhar:
因为它的数据行内部存储格式对固定长度的数据行和可变长度的数据行不加区分(所有数据行共用一个表头部分，这个标头部分存放着指向各有关数据列的指针)，所以使用char类型不见得会比使用varchar类型好。事实上,因为char类型通常要比varchar类型占用更多的空间，所以从减少空间占用量和减少磁盘i/o的角度，使用varchar类型反而更有利;
3、存储很短的信息，尽量使用Char:
比如门牌号码101, 20....这样很短的信息应该用char,因为varchar还要 占个byte用于存储信息长度，本来打算节约存储的现在得不偿失。
4、固定长度的数据, 尽量使用Char:
比如使用uuid作为主键,那用char应该更合适。因为他固定长度，varchar动态根据长度的特性就消失了，而且还要占个长度信息。
5、十分频繁改变的字段列, 尽量使用varchar:
因为varchar每次存储都要 有额外的计算,得到长度等工作,如果一个非常频繁改变的，那就要有很多的精力用于计算，而这些对于char来说是不需要的。
3. 模拟char(5) 和 varchar(5) 在存储方面得不同
char(5): egon |axx |lxx |fm |
varchar(5): 1-2bytes+egon|1-2bytes+axx|1-2bytes+lxx|1-2bytes+fm|
四. 日期类型
1. 日期类型的基本用法介绍
datetime/timestamp: 年月日时分秒 2020-5-4 11:11:11
date: 年月日 2020-5-4
time: 时分秒11:11:11
Year: 2020
# 时间的3种插入格式:
'''
1. 使用now()函数截取当前时间
2. 使用纯数字紧挨顺序写入 --> 080808
3. 使用自定义格式插值 --> 要用双引号引起来'08:08:08'
'''
drop table student;
create table student(
id int,
name varchar(16),
born_year year,
brith date,
study_time time,
reg_time datetime
);
insert into student values(1, 'egon', '2020', '1998-07-15', '08:08:08', '2020-11-11 11:11:11');
insert into student values(2, 'tank', 2022, 19980707, 080808, now());
select * from student;
2. datetime字段 与 timestamp字段区别
详细解析网址: https://www.cnblogs.com/linhaifeng/articles/7233411.html#_label2
# 区别
"""
<1> timestamp字段 比 datetime字段 的空间利用率更高, 存取速度快(最关键一点)
<2> timestamp字段 比 datetime字段 建表的时候更方便一点
<3> timestamp字段不能为空, 默认指定了当前时间, 不插值默认获取当前时间
<4> timestamp缺点, 时间上可能不够用
"""
# 使用timestamp字段同时也实现datetime字段的默认功能
drop table t1;
create table t1(x timestamp not null default now());
insert into t1 values();
select * from t1;
# datetime字段 和 timestamp字段的应用场景
'''
用户注册, 提交作业任务等等
'''
五. 枚举类型与集合类型
"""
枚举enum(‘a’, ‘b’, ‘c’) : 多选一
enum --> enumerate
集合set(‘a’, ‘b’, ‘c’) : 可选多, 可选单
"""
drop table user;
create table user(
id int,
name varchar(16),
sex enum('male', 'female'),
hobbies set('read', 'play', 'jump')
);
insert into user values
(1, 'egon', 'male', 'read'),
(2, 'tank', 'female', 'play, jump'); # 注意: 集合类型中只能由逗号格开.
select * from user;
六. 总结
# 类型的概念是创建表时对字段的限制与归类.
# 数值类型: 非严格模式下
"""
整型: tinyint(1字节), smallint(2字节), mediumint(3字节), int or integer(4字节), bigint(8字节)
<1> 宽度的指定并不会限制数字类型的显示, 限制数字类型显示是遵循最大范围. 所以说整型不需要指定宽度, 没有意义, 因为默认宽度就会包容当前遵循的最大范围.
<2> 在范围大于宽度的指定时, 就会以该范围显示.在范围小于宽度的指定时, 默认会以空格补全.
<3> 约束条件: unsigned zerofull
浮点型: float(255, 30), double(255, 30), decimal(65, 30)
<1> 前面的指定的时一共能显示的宽度, 后面指定的是在前面总显示宽度的基础之上会拿出30为来显示小数部分
<2> 精度: float < double < decimal
"""
# 字符串类型
"""
设置模式不让其剔除char的空格: pad_char_to_full_length. 设置完毕就可以使用char_length(字段)函数进行查看了.
char: 定长. 由宽度存取字符的个数. 默认不指定宽度为1个字符
<1> 如果存储数据时, 存储的字符个数刚刚等于或者超出(非严格模式下)指定的宽度就用char. 更加节省空间
varchar: 变长. 由1~2Bytes头部 + 数据. (2 ** 16 =65535, 头部最多可以对65535个数的字符进行描述). 必须自己制定宽度, 没有默认宽度.
<1> 如果存储数据时, 存储的字符个数不等于或者远远低于指定的宽度就用varchar. 更加节省空间
<2> IO情况下使用varchar是必然的.
"""
# 日期类型
"""
datatime/timestamp: 年月日 时分秒
data 年月日
time 时分秒
year 年
插入时间的三种方式:
<1> now()
<2> 紧紧连接的数字: 080808 表示 08:08:08
<3> 字符串: '08:08:08'
timestamp除了时间上可能不够用. 一般都用timestamp取代datetime
"""
# 枚举 & 集合
"""
枚举: 多选一. eum(1, 2, 3)
集合: 多选多, 多选一. set(1, 2, 3)
"""
