MySQL数据类型

数据类型

数据类型: 对数据进行统一的分类, 从系统的角度出发为了能够使用统一的方式进行管理: 更好的利用有限的空间.

SQL中将数据类型分成了三大类: 数值类型, 字符串类型和时间日期类型.

数值型

数值型数据:都是数值

数值型分类: 1.整数型. 2.小数型

整数型

存放整型数据:在SQL中因为更多要考虑如何节省磁盘空间,所以又细分为五大类.

tinyint:迷你整型,使用一个字节存储,表示的状态最多为256种.

smallint:小整型,使用2个字节存储,表示的状态最多为65536中.

mediumint:中整型,使用三个字节存储

int/integer:标准整型,使用4个字节存储

bigint:大整型,使用8个字节存储

类型	字节	最小值(有符号/无符号)	最大值(有符号/无符号)
tinyint	1	-128/0	127/255
smallint	2	-32768/0	32767/65535
mediumint	3
int/integer	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',100,100,100);  -- 无效数据:类型限定

INSERT INTO my_int VALUES (100,65539,100,100);  -- 超出范围

-- 增加一个无符号类型
ALTER TABLE my_int add int_5 TINYINT UNSIGNED;

-- 插入数据(无符号)
INSERT INTO my_int VALUES(127,1000,10000,100000,255);

-- 显示宽度的意义: 在于当数据不够显示宽度的时候,会自动让数据变成对应的显示宽度: 通常需要搭配一个前导0来增加宽度, 不改变值大小: zerofill(零填充): 零填充会导致数值自动变成无符号
-- 设置显示宽度为2,0填充
ALTER TABLE my_int add int_7 TINYINT(2) ZEROFILL; 
-- 和上方一样的效果
ALTER TABLE my_int add int_8 TINYINT(2) UNSIGNED ZEROFILL; 
-- 只有在CMD,终端窗口生效
INSERT INTO my_int VALUES(127,1000,10000,100000,255,1,1);

小数型

小数型:带有小数点或者范围超出整型的数值类型

SQL中:将小数型细分非两种: 1.浮点型 2.定点型
浮点型:小数点浮动,精度有限,而且会丢失精度
定点型:小数点固定,精度固定,不会丢失精度

浮点型

浮点型数据是一种精度型数据:因为超出制定范围之后,会丢失精度(自动四舍五入)

浮点型:理论分为两种精度
1.float:单精度,占用4个字节存储数据,精度范围大概为7位左右
2.double:双精度,占用8个字节存储数据,精度范围大概为15位左右

创建浮点数表: 浮点的使用方式: 直接float表示没有小数部分; float(M,D): M代表总长度,D代表小数部分长度, 整数部分长度为M-D

-- 浮点数表
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(1234567890,12345678.90,1234.56); -- 符合条件
INSERT INTO my_float VALUES(9999999999,99999999.99,9999.99); -- 最大值
INSERT INTO my_float VALUES(3e33,3.01e7,1234.56); -- 最大值

--浮点型数据的插入: 整型部分是不能超出长度的,但是小数部分可以超出长度(系统会自动四舍五入)

-- 超出长度插入数据
insert into my_float values(123456,1234.123456768,123.9876543);	-- 小数部分OK
insert into my_float values(123456,1234.12,12345.56);	-- 整数部分超出

定点型

定点型:绝对的保证整数部分不会被四舍五入(不会丢失精度),小数部分有可能(理论小数部分也不会丢失精度)

-- 创建定点数表:以浮点数作为对比
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.1234356); -- 小数部分超出:ok

insert into my_decimal values(99999999.99,99999999.99); -- 没有问题
insert into my_decimal values(99999999.99,99999999.999); -- 进位超出范围

时间日期类型

datetime:时间日期,格式YYYY-mm-dd HH:ii:ss,表示的范围是从1000到9999年,有0值 0000-00-00 00:00:00

date:日期,就是datetime中的date部分

time:时间(段),指定某个区间,-时间到+时间

timetamp:时间戳,并不是时间戳,只是从1970年开始的YYYY-mm-dd HH:ii:ss格式与datetime完全一致

year:年份,两种形式 year(2) 和year(4)

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

-- 查看表结构和数据
DESC  my_date;
SELECT * FROM my_date;

-- 插入数据
insert into my_date values('2015-9-28 11:50:36','2015-9-28','11:50:54','2015-9-28 11:51:08',2015);

-- 时间使用负数
insert into my_date values('2015-9-28 11:50:36','2015-9-28','-11:50:54','2015-9-28 11:51:08',2015);
insert into my_date values('2015-9-28 11:50:36','2015-9-28','-211:50:54','2015-9-28 11:51:08',2015);
insert into my_date values('2015-9-28 11:50:36','2015-9-28','-2 11:50:54','2015-9-28 11:51:08',2015); -- -2过去2天:48


-- year可以使用2位或者4位
insert into my_date values('2015-9-28 11:50:36','2015-9-28','11:50:54','2015-9-28 11:51:08',69);
insert into my_date values('2015-9-28 11:50:36','2015-9-28','11:50:54','2015-9-28 11:51:08',70);


-- timestamp: 修改记录(这种类型每次更新会修改)
update my_date set d1 = '2015-9-28 11:55:45' where d5 = 2069;

-- 查询时间YYYY-mm-dd HH:ii:ss
SELECT SYSDATE();
-- 时间戳
SELECT UNIX_TIMESTAMP();

字符串类型

在SQL中,将字符串类型分了6类:char,varchar,text,blob,enum和set.

定长字符串

定长字符串:char,磁盘(二维表)在定义结构的时候,就已经确定了最终数据的存储长度.

char(L):代表length,可以存储的长度,单位为字符,最大长度值可以为255.
char(4):在UTF8环境下,需要4*3 =12 字节

变长字符串:varchar,在分配空间的时候,按照最大的空间分配:但是实际用了多少,根据具体的数据确定.

varchar(L) :L表示字符长度,理论长度是65535个字符,但是会多出一(255以内)到两个字节存储实际长度,实际上如果长度超过255,既不用定长也不用边长,使用text

varchar(10):存了10个汉字,utf8环境,10*3+1=31(bytes)
            存了3个汉字,utf8环境,3*3+1=10(bytes)
            

定长与变长的存储空间(UTF8)

实际存储长度	char(4)	varchar(4)	char占用字节	varchar(占用字节)
ABCD	ABCD	ABCD	4*3=12	4*3+1=13
A	A	A	4*3=12	1*3+1=4
ABCDE	x	x	数据超过长度	数据超过长度

如何选择定长或者是变长字符串呢?
定长的磁盘空间比较浪费,但是效率高:如果数据基本上确定长度都一样,就是使用定长:如身份证,电话号码,手机号码等

变长的磁盘空间比较节省,但是效率低:如果数据不能确定长度(不同数据有变化).如:姓名,地址等

文本字符串

如果数据量非常大,通常说超过255个字符就会使用文本字符串.

文本字符串根据存储的数据格式进行分类:text和blob

text:存储文字(二进制数据实际上都是存储路径)
blob:存储二进制数据(通常不用)

枚举字符串

枚举:enum,事先将所有可能出现的结果都设计好,实际上存储的数据必须是规定好的数据中的一个.

枚举的使用方式
    定义:enum(可能出现的元素列表); //如:enum('男','女','不男不女','保密')
    
    使用:存储数据,只能存储上面定义好的数据.
    
作用:
1. 规范数据格式,数据只能是规定的数据中的一个.
2. 节省存储空间(枚举通常有一个别名:单选框)

在mysql中,系统也是自动转换数据格式的:基本与PHP一样(尤其是字符串串数字).
证明字段存储的是数值:将数据取出来+0,就可以判断出原来的数据存的到底是字符串还是数值: 如果是字符串最终结果永远为0, 否则就是其他值.

找出了枚举元素的实际规律: 按照元素出现的顺序, 从1开始编号

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

因为枚举实际存储的是数值,所以可以直接插入数值.

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

-- 插入数据
insert into my_enum values('男'),('保密'); -- 有效数据

-- 错误数据
insert into my_enum values('male');	-- 错误: 没有该元素

-- 将字段结果取出来进行+0运算
select gender + 0, gender from my_enum;

-- 数值插入枚举元素
insert into my_enum values(1),(2);

集合字符串

集合跟枚举很类似:实际存储的是数值,而不是字符串(集合是多选的).

集合使用方式:
    定义:set(元素列表)
    使用:可以使用元素列表中的元素(多个), 使用逗号分隔.
  
  
-- 创建集合表
create table my_set(
hobby set('篮球','足球','乒乓球','羽毛球','排球','台球','网球','棒球')
--		   足球			          台球    网球
-- 集合中: 每一个元素都是对应一个二进制位,被选中为1,没有则为0: 最后反过来
--          0      1       0         0      0       1       1     0
-- 反过来    01100010 = 98

)charset utf8;

-- 插入数据
insert into my_set values('足球,台球,网球');
insert into my_set values(3); 

-- 查看集合数据
select hobby + 0, hobby from my_set;

-- 98转成二进制 = 64 + 32 + 2 = 01100010

-- 颠倒元素出现的顺序
insert into my_set values('网球,台球,足球');

mysql 记录长度

mysql中规定:任何一条记录最长不超过65535个字节.(varchar永远达不到理论值)

varchar的实际存储长度能达到多少呢?看字符集编码.

-- 求出varchar在utf8和GBK下的实际最大值
create table my_utf8(
name varchar(21844) -- 21844 * 3 + 2 = 65532 + 2 = 65534
)charset utf8;

create table my_gbk(
name varchar(32766) -- 32766 * 2 + 2 = 65532 + 2 = 65534
)charset gbk;

create table my_utf81(
age tinyint,	    -- 1
name varchar(21844) -- 21844 * 3 + 2 = 65532 + 2 = 65534
)charset utf8;

create table my_gbk1(
age tinyint,	    -- 1	
name varchar(32766) -- 32766 * 2 + 2 = 65532 + 2 = 65534
)charset gbk;

-- 释放NULL
create table my_utf82(
age tinyint not null,	    -- 1
name varchar(21844) not null -- 21844 * 3 + 2 = 65532 + 2 = 65534
)charset utf8;

create table my_gbk2(
age tinyint not null,	    -- 1	
name varchar(32766) not null -- 32766 * 2 + 2 = 65532 + 2 = 65534
)charset gbk;


Mysql中text文本字符串,不占用记录长度: 额外存储. 但是text文本字符串也是属于记录的一部分: 一定需要占据记录中的部分长度: 10个字节(保存数据的地址以及长度).

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

列属性

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

列属性有很多: NULL/NOT NULL, default, Primary key, unique key, auto_increment,comment

空属性

两个值: NULL(默认的)和NOT 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;

默认值

默认值: 某一种数据会经常性的出现某个具体的值, 可以在一开始就指定好: 在需要真实数据的时候,用户可以选择性的使用默认值.

默认值关键字: default
默认值的生效: 使用, 在数据进行插入的时候,不给改字段赋值

-- 默认值
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('高强');

insert into my_default values('范立峰',18,default);