常见的数据类型
一、整型
1.如何设置无符号和有符号
DROP TABLE IF EXISTS tab_int;
CREATE TABLE tab_int(
t1 INT ( 7 ) ZEROFILL,
t2 INT ( 7 ) ZEROFILL
) ;
DESC tab_int;
INSERT INTO tab_int VALUES ( - 123456 ) ;
INSERT INTO tab_int VALUES ( - 123456 , - 123456 ) ;
INSERT INTO tab_int VALUES ( 2147483648 , 4294967296 ) ;
INSERT INTO tab_int VALUES ( 123 , 123 ) ;
SELECT * FROM tab_int;
二、小数
测试M和D
DROP TABLE tab_float;
CREATE TABLE tab_float(
f1 FLOAT ,
f2 DOUBLE ,
f3 DECIMAL
) ;
SELECT * FROM tab_float;
DESC tab_float;
INSERT INTO tab_float VALUES ( 123.4523 , 123.4523 , 123.4523 ) ;
INSERT INTO tab_float VALUES ( 123.456 , 123.456 , 123.456 ) ;
INSERT INTO tab_float VALUES ( 123.4 , 123.4 , 123.4 ) ;
INSERT INTO tab_float VALUES ( 1523.4 , 1523.4 , 1523.4 ) ;
原则:
三、字符型
CREATE TABLE tab_char(
c1 ENUM ( 'a' , 'b' , 'c' )
) ;
INSERT INTO tab_char VALUES ( 'a' ) ;
INSERT INTO tab_char VALUES ( 'b' ) ;
INSERT INTO tab_char VALUES ( 'c' ) ;
INSERT INTO tab_char VALUES ( 'm' ) ;
INSERT INTO tab_char VALUES ( 'A' ) ;
SELECT * FROM tab_set;
CREATE TABLE tab_set(
s1 SET ( 'a' , 'b' , 'c' , 'd' )
) ;
INSERT INTO tab_set VALUES ( 'a' ) ;
INSERT INTO tab_set VALUES ( 'A,B' ) ;
INSERT INTO tab_set VALUES ( 'a,c,d' ) ;
四、日期型
CREATE TABLE tab_date(
t1 DATETIME ,
t2 TIMESTAMP
) ;
INSERT INTO tab_date VALUES ( NOW ( ) , NOW ( ) ) ;
SELECT * FROM tab_date;
SHOW VARIABLES LIKE 'time_zone' ;
SET time_zone= '+9:00' ;