第三部分MySQL支持数据类型.ppt

第三章 MySQL支持的数据类型 3.1 数值类型 3.2 日期时间类型 3.3 字符串类型 3.1 数值类型 整型数据 INT(5),表示指定显示宽度 zerofill填充 mysqlcreate table t1(id1 int(11),id2 int(5)); mysqldesc t1; mysqlinsert into t1 values(1,1); mysqlselect * from t1; mysqlalter table t1 modify id1 int(11) zerofill; mysqlalter table t1 modify id int2(5) zerofill; mysqlselect * from t1; 整型数据 mysqlinsert into t1 values(1,1111111); mysqlselect * from t1; 小数的表示 浮点数(FLOAT,DOUBLE)(M,D) 定点数DECIMAL(M,D) FLOAT(7,4) -999.9999 FLOAT,DOUBLE不指定，实际精度 DECIMAL不指定，默认整数位为10 比较FLOAT、DOUBLE、DECIMAL mysqlcreate table t2 { id1,float(5,2) default,null, id2,double(5,2) default null, id3,decimal(5,2) default null }; mysqlinsert into t2 values(1.23,1.23,1.23); mysqlselect * from t2; mysqlinsert into t2 values(1.234,1.234,1.23); mysqlselect * from t2; mysqlinsert into t2 values(1.234,1.234,1.234); mysqlshow warnings; mysqlselect * from t2; mysqlalter table t2 modify id1 float; mysqlalter table t2 modify id2 double; mysqlalter table t2 modify id3 decimal; mysqldesc t2; mysqlinsert into t2 values(1.234,1.234,1.234); mysqlshow warnings; mysqlselect * from t2; BIT类型 mysqlcreate table t3(id bit(1)); mysqldesc t3; mysqlinsert into t3 values(1); mysqlselect * from t3; mysqlselect bin(id),hex(id) from t3; mysqlinsert into t3 values(2); mysqlshow warnings; mysqlalter table t3 modify id bit(2); mysqlinsert into t3 values(2); mysqlselect bin(id),hex(id) from t3; 3.2 日期时间类型 比较DATE、TIME、DATETIME Mysqlcreate table t4(d date,t time,dt datetime); Mysqldesc t4; Mysqlinsert into t4 values(now(),now(),now()); Mysqlselect * from t4; TIMESTAMP Mysqlcreate table t5(id5 timestamp); Mysqldesc t5; Mysqlinsert into t5 values(null); Mysqlselect * from t5; Mysqlalter table t5 add id6 timestamp; Mysqlshow create table t5 \G; Mysqlalter table t5 modify id6 timestamp default current_timestamp; Mysqlcreate table t6 ( id7 timestamp not null default c