一、整数类型
1.1、tinyint [(M)][unsigned][zerofill]
1.2、smallint [(M)][unsigned][zerofill]
1.3、mediumint [(M)][unsigned][zerofill]
1.4、int [(M)][unsigned][zerofill]
1.5、bigint [(M)][unsigned][zerofill]
参数说明:
M:取值范围。
unsigned:无符号,控制是否有正负数。
zerofill:用来进行前导零填充,如tinyint的值为1,而其最长取值位数是3位,则填充后的结果会变成001。类型后面写了zerofill,默认就是unsigned无符号。
示例1:无参数控制
create table study01 (id01 tinyint,id02 int);desc study01;insert into study01 (id01,id02) values (100,101);insert into study01 (id01,id02) values (-1,-2);select * from study01;
示例2:有参数控制1
create table study02 (id01 tinyint(3) unsigned zerofill,id02 int(3) unsigned zerofill);desc study02;insert into study02 (id01,id02) values (1,1);insert into study02 (id01,id02) values (12,1234);select * from study02;
示例3:有参数控制2
create table study03 (id01 tinyint(3) unsigned,id02 int(3) unsigned);desc study03;insert into study03 (id01,id02) values (1,1);insert into study03 (id01,id02) values (12,1234);select * from study03;
二、小数类型
2.1、decimal (M,D)
2.2、float (M,D)
2.3、double (M,D)
参数说明:
zerofill
unsigned
示例1:无参数控制
create table studyf1 (f1 float,f2 double);desc studyf1;insert into studyf1 (f1,f2) values (-12.123,-1234.5678);select * from studyf1;
示例2:有参数控制1
create table studyf2 (f1 float(5,2),f2 double(6,3) zerofill);desc studyf2;insert into studyf2 (f1,f2) values (12.34,12.34);insert into studyf2 (f1,f2) values (1.1,1.2);insert into studyf2 (f1,f2) values (123.45,123.456);/*科学计数法(E),小数点移动几位。*/insert into studyf2 (f1,f2) values (0.1234E2,0.123456E3);/*插入多了,就会四舍五入。*/insert into studyf2 (f1,f2) values (12.126,12.34);select * from studyf2;
示例3:有参数控制2
create table studyf3 (f1 float(10,4) unsigned zerofill);desc studyf3;insert into studyf3 (f1) values (12.345);insert into studyf3 (f1) values (12.3456);insert into studyf3 (f1) values (12.34567);select * from studyf3;
三、日期类型
3.1、datetime(年月日时分秒)
create table studyd1 (mydate datetime);insert into studyd1 (mydate) values ('20200902230130');insert into studyd1 (mydate) values (20200902230130);insert into studyd1 (mydate) values ('2020-09-02 23:01:30');insert into studyd1 (mydate) values (null);select * from studyd1;
3.2、timestamp(年月日时分秒/整数)
create table studyd2 (mytime timestamp);insert into studyd2 (mytime) values ('20200902230130');insert into studyd2 (mytime) values ('2020-09-02 23:01:30');select * from studyd2;/*+0 查看时间戳,显示整数。*/select mytime+0 fro.........