《第三章mysql支持的数据类型》

《《第三章mysql支持的数据类型》》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、第三章MySQL支持的数据类型3.1数值类型3.2日期时间类型3.3字符串类型3.1数值类型整数类型字节最小值最大值TINYINT1有符号-128无符号0127255SMALLINT2有符号-32768无符号03276765535MEDIUMINT3有符号-8388608无符号083886071677215INT、INTEGER4有符号-2147483648无符号021474836474294967295BEGINT8有符号-9223372036854775808无符号092233720368547758071844674407370955

2、1615浮点数类型字节最小值最大值FLOAT4±1.17549351E-38±3.402823466E+38DOUBLE8±2.2250738585072014E-308±1.7976931348623157E+308位类型字节最小值最大值BIT(M)1~8BIT(1)BIT(64)定点数类型字节描述DEC(M,D)M+2最大取值范围与DOUBLE相同，给定DECIMAL的有效取值范围有M和D决定。DECIMAL(M,D)整型数据INT(5),表示指定显示宽度zerofill填充mysql>createtablet1(id1int(11),

3、id2int(5));mysql>desct1;mysql>insertintot1values(1,1);mysql>select*fromt1;mysql>altertablet1modifyid1int(11)zerofill;mysql>altertablet1modifyidint2(5)zerofill;mysql>select*fromt1;整型数据mysql>insertintot1values(1,1111111);mysql>select*fromt1;小数的表示浮点数(FLOAT,DOUBLE)(M,D)定点数DECI

4、MAL(M,D)FLOAT(7,4)-999.9999FLOAT,DOUBLE不指定，实际精度DECIMAL不指定，默认整数位为10比较FLOAT、DOUBLE、DECIMALmysql>createtablet2{id1,float(5,2)default,null,id2,double(5,2)defaultnull,id3,decimal(5,2)defaultnull};mysql>insertintot2values(1.23,1.23,1.23);mysql>select*fromt2;mysql>insertintot2val

5、ues(1.234,1.234,1.23);mysql>select*fromt2;mysql>insertintot2values(1.234,1.234,1.234);mysql>showwarnings;mysql>select*fromt2;mysql>altertablet2modifyid1float;mysql>altertablet2modifyid2double;mysql>altertablet2modifyid3decimal;mysql>desct2;mysql>insertintot2values(1.234,1.

6、234,1.234);mysql>showwarnings;mysql>select*fromt2;BIT类型mysql>createtablet3(idbit(1));mysql>desct3;mysql>insertintot3values(1);mysql>select*fromt3;mysql>selectbin(id),hex(id)fromt3;mysql>insertintot3values(2);mysql>showwarnings;mysql>altertablet3modifyidbit(2);mysql>inserti

7、ntot3values(2);mysql>selectbin(id),hex(id)fromt3;3.2日期时间类型日期和时间类型字节最小值最大值DATE41000-01-019999-12-31DATETIME81000-01-0100:00:009999-12-3123:59:59TIMESTAMP4197001010800012038的某个时刻TIME3-838:59:59838:59:59YEAR119012155数据类型零值表示DATETIME0000-00-0000:00:00DATE0000-00-00TIMESTAMP000

8、00000000000TIME00:00:00YEAR0000MySQL中日期和时间类型的零值表示比较DATE、TIME、DATETIMEMysql>createtablet4