mysql学习一之列类型（整型、浮点、日期时间等）及列属性

数据类型（列类型）
所谓的数据类型：对数据进行统一的分类，从系统的角度出发为了能够使用统一的
方式进行管理：更好的利用有限的空间。

SQL的三大数值类型：数值、字符串、日期时间

1、数值型
系统将数值型分为整数型和小数型。

整数型：
存放整型数据：在SQL中因为更多考虑如何节省磁盘空间，细分为
tinyint:迷你整型，使用1个字节存储，表示的状态最多为256种（常用）
smallint：小整型，使用2个字节存储
mediumint:中整型，使用3个字节
int :标准整型，使用4个字节（常用）
bigint：大整型，使用8个字节

--创建整型表
create table my_int(
int_1 tinyint,
int_2 smallint,
int_3 int,
int_4 bigint
)charset utf8;

--插入数据
insert into my_int values(100,100,100,100);  --有效

insert into my_int values('a','b','199','f'); --无效：类型限定

insert into my_int values(255,10000,100000,1000000); --无效：超出范围

SQL中的数值类型全部都是默认有符号：分正负

有时候需要使用无符号数据：需要给数据类型限定：int unsigned

--给表增加一个无符号类型
alter table my_int add int_5 tinyint unsigned;
--插入数据
insert into my_int values(12,1000,10000,10000,255); --有效

查看表结构的时候，发现每个字段的数据类型之后都会自带一个括号，里面有指定的数字
desc my_int;
叫显示宽度：数据最终显示的位数，如-123是4位（包含符号位），255是3位
显示宽度没有特别含义，只是默认的告诉用户可以显示的形式，实际上用户是可以控制的，但并不能
影响数据

alter table my_int add int_6 tinyint(1) unsigned; --指定显示宽度为1

insert into my_int values(13,0,0,0,0,255);
显示宽度的意义：在于当数据不够显示宽度的时候，会自动让数据变成对应的显示宽度，
通常需要搭配一个前导0来增加宽度，不改变值大小：zerofill（零填充）

alter table my_int add int_7 tinyint(2) zerofill; --显示宽度为2，0填充
一旦使用零填充，会导致数值自动变成无符号
insert into my_int values(13,0,0,0,0,255,1);
如果没有零填充，显示宽度无效。

零填充的意义（显示宽度）：保证数据格式。

小数型
小数型：带有小数点
SQL中：将小数型细分成两种：浮点型和定点型
浮点型：小数点浮动，精度有限，而且会丢失精度
定点型：小数点固定，精度固定，不会丢失精度

浮点型 
是一种精度型数据：因为超出指定范围之后，会丢失精度，会自动四舍五入
理论分为两种精度：
  float 单精度，占用4个字节存储数据，精度范围大概为7位左右
  double双精度，占用8个字节存储数据，精度范围大概为15位左右

--创建浮点数表：浮点的使用方式：直接float表示没有小数部分，
fload(M,D)代表总长度，D代表小数据部分长度

--浮点数表 --10位在精度范围之外  --6位在精度范围之内
create table my_float(
f1 float,
f2 float(10,2), 
f3 float(6,2)  
)charset utf8;

--插入数据
insert into my_float values(1000.10,1000.10,1000.10);
insert into my_float values(9999999999,999999.9999,99.99);
insert into my_float values(3e38,3.01e7,1234.56);

浮点型数据的插入：整型部分是不能超出长度的，但是小数部分可以超出长度（系统会自动四舍五入）
--超出长度
insert into my_float values(12345,123.12345,123.123323);  --小数部分超出
insert into my_float values(12345,1234.34,12345.234); --整数部分超出

定点型：绝对的保证整数部分不会被四舍五入（不会丢失精度），小数部分有可能
Decimal :采用4个字节存储，整数和小数分开计算,M最大是65，D最大是30默认是10,2

创建定点数表：以浮点数对比

create table my_decimal(
f1 float(10,2),
d1 decimal(10,2)
)charset utf8;

加入数据：定点数的整数部分一定不能超出长度，进位也不可以，小数部分可以（四舍五入）
insert into my_decimal values(12345678.90,12345678.90);  --有效 
insert into my_decimal values(1234.123456,1234.123456);  --有效，小数部分超出

--查看警告
show warnings;

浮点数如果进位导致整数位超出，允许，定点数不允许
insert into my_decimal values(99999999.99,99999999.99);
insert into my_decimal values(99999999.99,99999999.9999);


时间日期类型
datetime :时间日期，存储空间8,格式是YYYY-mm-dd HH:MM:SS,表示的范围是从1000到9999年，有0值
0000-00-00 00:00:00
Date 日期，存储空间3,就是datetime中的date部分
time 时间(段)，存储空间3,指定某个区间之间,格式HH:MM:SS
timestamp:时间戳，存储空间4,只是从1970年开始的YYYY-mm-dd HH:MM:SS
year 年份，存储空间1,两种形式，year(2)和year(4)：1901-2155

创建时间日期表
create table my_date(
d1 datetime,
d2 date,
d3 time,
d4 timestamp,
d5 year
)charset utf8;

插入数据：时间time可以是负数，year可以使用2位数插入，也可以用4位插入
insert into my_date values('2016-3-16 11:50:34','2016-3-16','11:50:34','2016-3-16 11:50:34',2016);
insert into my_date values('2016-3-16 11:50:34','2016-3-16','-11:50:34','2016-3-16 11:50:34',2016);
insert into my_date values('2016-3-16 11:50:34','2016-3-16','-2 11:50:34','2016-3-16 11:50:34',2016); --过去两天

timestamp只要当前所在记录改变，timestamp肯定会自动更新为当前时间
update my_date set d1='2016-9-18 11:33:33' where d3='11:50:34';

字符串类型
在SQL中，将字符串类型分为6类：char,varchar,text,blob,enum,set 

char 定长字符串
磁盘在定义结构的时候，就已经确定了最终数据存储长度。
char(L) L代表length，可以存储的长度，单位为字符，最大可以为255
char(4) :空间4*3=12个字节

varchar 变长字符串
在分配空间的时候，按照最大的空间分配：但实际上最终用了多少是根据具体的数据来确定
varchar(L) L表示字符长度，理论长度是65536个字符，但是会多出1到2个字节来确定存储的
实际长度，但是实际上如果长度超过255，使用文本字符串text存储
varchar(10) :存了10个汉字，空间：utf8环境下10*3+1=31(bytes)
             存了3个汉字，空间：3*3+1=10(bytes)

如何选择定长或者是变长字符串？
定长的磁盘空间比较浪费，但是效率高；
	如果数据长度基本上确定长度都一样就使用定长，如身份证，电话号码，手机号码等
变长的磁盘空间比较节省，但是效率低；
	如果数据不能确定长度（不同数据有变化），如姓名，家庭地址等

text 文本字符串
如果数据量非常大，通常说超过255个字符就会使用文本字符串		
文本字符串根据存储的数据的格式进行分类：text和blob
		text:存储文字（二进制数据实际上都是存储路径）
		blob:存储二进制数据（通常不用）

枚举字符串
枚举：enum,事先将所有可能出现的结果都设计好，实际上存储的数据必须是规定好的数据中的一个
枚举使用方式
	定义：enum(可能出现的元素列表); //enum('男','女','保密');
	使用：存储数据，只能存储定义好的数据

--创建枚举表
create table my_enum(
gender enum('男','女','保密')
)charset utf8;	

加入数据：作用之一：规范数据格式：数据只能是规定的数据中的其中一个
insert into my_enum values('男'),('保密'); --有效数据
insert into my_enum values('male');  --错误，没有该元素
作用之二：节省存储空间（枚举通常有一个别名，单选框）
insert into my_enum values(1),(2);

在mysql中，系统也是自动转换数据格式的：而且基本与PHP一样，字符串转数字

证明字段存储的数据是数值：将数据取出来+0就可以判断出原来的数据存的到底是字符
串还是数值：如果是字符串最终结果永远是0，否则就是其他值
select gender+0,gender from my_enum;
找到的枚举元素的实际规律，按照元素出现的顺序，从1开始

枚举原理：枚举在进行数据规范的时候（定义的时候），系统会自动建立一个数字与枚举元素
的对应（关系放到日志中）；然后在进行数据插入的时候，系统自动将字符转换成对应的数字存储，
然后在进行数据提取的时候，系统自动将数值转换成对应的字符显示。

集合字符串

集合和枚举很类似：实际存储的是数值，而不是字符串（集合是多选）
集合使用方式：
	定义：set(元素列表)
	使用逗号分隔

--创建集合表
create table my_set(
hobby set('篮球','足球','乒乓球','排球','棒球','网球')
)charset utf8;


--插入数据：可以使用多个元素字符串组全，也可以直接插入
insert into my_set values('足球,网球,篮球');
insert into my_set values(3); --篮球和足球(0011)
 hobby set('篮球','足球','乒乓球','排球','棒球','网球')
--集合中：每一个元素都是对应一个二进制位，被选中为1，没有则为0，最后反过来
--3(0011b)    1      1       0       0      0      0 
--35(100011b) 1      1       0       0      0      1
select hobby+0,hobby from my_set;

集合中元素的顺序没有关系：最终系统中都会去匹配顺序
集合的强大在于能够规范数据和节省空间


mysql记录长度
mysql中规定：任何一条记录最长不能超过65535个字节。

varchar的实际存储长度能达到多少？看字符集编码
utf8下varchar的实际最大值
create table my_utf8(
name varchar(65535)
)charset utf8;
--最大21845  
create table my_utf8(
name varchar(21845)
)charset utf8;
--21844*3+2=65532+2=65534
gbk下varchar的实际最大值
create table my_gbk(
name varchar(65535)
)charset gbk;
--最大32767  --32766*2+2=65532+2=65534

想用完整个65535个字节长度：增加一个tinyint字段
create table my_utf81(
age tinyint,
name varchar(21844)
)charset utf8;
--mysql记录中：如果有任何一个字段允许为空，那么系统会自动从整个记录中保留一个
字节来存储NULL（若想释放NULL所占用的字节：必须保证所有字段不能为空）
--释放NULL
create table my_utf81(
age tinyint not null,
name varchar(21844) not null
)charset utf8;

mysql中text文本字符串，不占用记录长度：额外存储，但是text文本字符串也是属于记录
的一部分：一定占据记录中的部分长度：10个字节（保存数据的地址及长度）。

--text占用10个字节
create table my_text( 
name varchar(21841) not null, -- 21841*3+2=65523+2=65525
content text                  -- 10
)charset utf8;

--null占用一个字节
create table my_text( 
name varchar(21841) not null,  
content text not null           
)charset utf8;


列属性
真正约束字段的是数据类型，但是数据类型的约束很单一，需要一些额外的约束来更
加保证数据的合法性。

列属性有很多：NULL/NOT NULL,default,primary key,unique key,auto_increment,comment

1、空属性
两个值：NULL(默认值)和NOT NULL(不为空)
虽然默认的，数据库基本都是字段为空，但是实际上在真实开发的时候，尽可能要保证
所有的数据不为空，空数据没有意义，空数据没有办法参与运算。
select NULL;     
select 1+NULL ;  --NULL 

创建一个实际案例表：班级表(名字，教室)
create table my_class(
name varchar(20) not null,
room varchar(20) null
)charset utf8;

列描述
comment，描述，没有实际含义，是专门用来描述字段，会根据表创建语句保存；
create table my_teacher(
name varchar(20) not null comment '姓名',
money decimal(10,2) not null comment '工资'
)charset utf8;

show create table my_teacher;

默认值
某一种数据会经常性的出现在某个具体的值，可以在一开始指定好，在需要真实数据时，
用户可以选择性的使用默认值；
create table my_default(
name varchar(20) not null,
age tinyint unsigned default 0,
gender enum('男','女','保密') default '男'
)charset utf8;

insert into my_default(name) values('ct');
想要使用默认值，可以不一定去指定列表
insert into my_default values('中国',12,default);