MySQL学习笔记（2）--列类型

·整数类型

·tinyint
迷你整形，一个字节保存，最大是255
MySQL默认给整形增加负数（-128->127）
·smallint
小整形，两个字节保存，最大值65535
·mediumint
中整形，三个字节保存
·int
整形，四个字节保存
·bigint
大整形，八个字节保存
·无符号标识设定（unsigned）

alter table teacher add age int unsigned;

·显示长度

指数据（多是整型）在数据显示的时候，可以显示多长位

如果想保持最高位显示，需要加一个zerofill

alter table teacher modify age int zerofill;

zerofill是从左侧开始填充（加在左侧不会改变值大小），所以负数不能用zerofill
注：tinyint(2) ：100显示100 1显示01

·小数类型

MySQL中分为浮点型和定点型。

·浮点型（精度类型）

一种有可能丢失精度的数据类型，数据有可能不那么准确。
浮点型之所以能够存储较大的数值（不精确），原因是利用存储数据的位来存储指数。
·float（单精度）
系统提供四个字节来存储数据，能表示的数据范围比整型大得多，大概是10^38，只能保证7位数以内的准确度。
float：不指定小数位。
float (M,D)：一共存储M个有效数字，小数部分占D位。

float (10,2)：整数8位，小数2位。

1. 插入12345678.90 显示12345679.00 （不精确，按四舍五入计算）
2. 插入99999999.99 显示100000000.00
3. 插入10e5 显示1000000 （10*10^5）

·double（双精度）
系统提供八个字节来存储数据，能表示的数据范围比整型大得多，大概是10^308，只能保证15位数以内的准确度。

·定点型

能够保证数据精确的小数类型（小数可能不精确，整数一定精确）
·decimal
decimal：系统自动根据存储的数据来分配存储空间，每大概九个数会分配四个字节来存储，小数和整数是分开的
decimal(M,D)：M表示总长度（不能超过65），D表示小数部分长度（不能超过30）

1. 插入12345678.90 显示12345678.90（不精确，按四舍五入计算）
2. 插入99999999.99 显示99999999.99

·时间日期型

1. date
三个字节存储，格式：YYYY-MM-DD 范围1000-01-01到9999-12-12 初始值 0000-00-00。
2. time
能表示某个指定的时间，同样提供三个字节，格式：HH:II:SS
MySQL中的time能表示的范围：-838:59:59 到 -838:59:59 ，用来描述时间段。

插入5 12:12:12 （五天，可以是负数） 显示132:12:12
3. datetime
日期时间类型，合并date和time，使用八个字节。
4. timestamp
时间戳类型：MySQL中表示从格林威治时间开始，但是其格式依然是datetime
timestamp对应的数据被修改时会自动更新到当前时间
5. year
年类型：占用一个字节来保存，能表示1900到2155年，但是year有两种数据插入方式：

1. 0~99（小于等于69时是20+，大于69时是19+）
2. 四位数的具体年

INSERT INTO my_date VALUES(
'2012-12-12',
'12:12:12',
'2012-12-12 12:12:12',
'2012-12-12 12:12:12',
12)

·字符串类型

1. char
定长字符：指定长度之后系统一定会分配指定空间用于存储数据
char（L）：L代表字符数（中文与英文字母一样） 范围：0 到255
2. varchar
变长字符：指定长度后，系统会根据实际存储的数据来计算长度，分配合适的长度（数据没有超出长度）
varchar（L）：L代表字符数 范围：0到65535
因为varchar要记录数据长度（系统根据数据长度自动分配空间），所以每个varchar数据产生后，系统都会在数据后面增加1~2个字节的额外开销，用来保存数据所占用的空间长度，如果数据本身小于127个字符，额外开销一个字节，如果大于127个就开销两个字节。
3. text
文本类型：
blob：存储二进制文本（图片，文件），一般不用blob用链接来指向文件
text：存储普通的字符文本（四种）

1. tinytext：用一个字节来保存对应数据所在位置，实际能存储的数据为2^8+1
2. text：用两个字节来保存，2^16+2
3. mediumtext：2^24+3
4. longtext：2^32+4

在选择对应的存储文本的时候，不用刻意去选择text类型，系统会自动根据存储的数据长度来选择合适的文本类型。
在选择字符存储的时候，如果数据超过255个字符，那么一定选择text存储(varchar效率不高)。
4. enum
枚举类型：在数据插入之前先设定几个项，这几个项就是可能最终出现的结果。比如性别，系统可以在设定字段的时候规定当前字段，只能存放固定的几个值。
系统提供了1~2个字节来存储枚举数据，通过计算列举的具体值来选择实际的存储空间，如果数据值列表在255个以内，那么一个字节就够，如果超过255但是小于65535，那么系统采用两个字节来保存。

create table my_enum(
    gender enum('男','女','保密')
)charset utf8;
select * from my_enum;
select gender + 0 from my_enum;
SELECT name + 1 from my_teacher;

存储原理：实际上字段上所存储的值并不是真正的字符串，而是字符串对应的下标，当系统设定枚举类型的时候，会给枚举中的每个元素定义一个下标，这个下标规则从1开始
enum(1=> ‘男’, 2=> ‘女’, 3=> ‘保密’)
注：在MySQL中，系统是自动进行类型转换的，如果数据碰到加减乘除，系统就会自动将数据转换成数值，而普通的字符串转换成数值为0

既然实际枚举字段存储的结果是数值，那么在进行数据插入的时候，就可以使用对应的数值来进行

insert into my_enum values(3);

5. set
集合：是一种将多个数据选项可以同时保存的数据类型，本质是将指定的项按照对应的二进制位来进行控制，1表示该选项被选中，0表示该选项没有被选中。
系统为set提供多个字节保存，会自动计算来选择具体存储单元：
1个字节：set只能有8个选项
2个字节：set只能有16个选项
set和enum一样，最终存储到数据字段中的依然是数字，不是真实的字符串。
显示顺序只和选项顺序有关
可以插入数值，用二进制转换的十进制表示

·记录长度

MySQL记录长度总长度（row）不能超过65,535个字节

create table my_varchar(
    name varchar(65535)
)charset utf8;            --报错
--utf8 65535/3 = 21845
--gbk  65535/2 = 32767 余1 
--但是采用varchar需要额外两个字节来保存长度
create table my_utf(
    name varchar(21844)
)charset utf8;         --utf8能存的最大长度
create table my_gbk(
    name varchar(32766)
)charset gbk;         --gbk能存的最大长度