06. 数据类型

CREATE DATABASE example;
USE example;
-- 1. 整数
CREATE TABLE testint(
a TINYINT ZEROFILL,
b SMALLINT ZEROFILL,
c MEDIUMINT ZEROFILL,
d INT ZEROFILL,
e INTEGER ZEROFILL,
f BIGINT ZEROFILL
);
 
INSERT INTO testint VALUES(1,1,1,1,1,1);
INSERT INTO testint VALUES(256,1,1,1,1,1);
INSERT INTO testint VALUES(10001,1,1,1,1,1);
SELECT * FROM testint;
 
-- 2. float double decimal
CREATE TABLE  testfloat(
a FLOAT,
b DOUBLE,
c DECIMAL(6,2)
);
INSERT INTO testfloat VALUES(3.1415926535,3.1415926535,3.1415926535);
SELECT * FROM testfloat;\
 
-- 3. 字符串
-- char varchar text tinytext text mediumtext longtext
 
-- 4. 日期时间类型
-- dateYYYY-MM-DD 1000-01-01~9999-12-31
-- time HH:ii:ss        -838:59:59~838:59:59
-- year YYYY
-- datetimeYYYY-MM-DD HH:ii:ss1000-01-01 00:00:00~9999-12-31 23:59:59
-- timestamp YYYY-MM-DD HH:ii:ss1970-01-01 00:00:00~2037-12-31 23:59:59 <- 和时区相关
CREATE TABLE testdate(
a DATE,
b TIME,
c YEAR,
d DATETIME,
e TIMESTAMP
);
INSERT INTO testdate VALUES('2016-08-03','14:19:00','2016',NOW(),NOW());
INSERT INTO testdate VALUES(NULL,NOW(),'16',NOW(),NOW());
SELECT * FROM testdate;
 
-- 查看变量
SHOW VARIABLES LIKE 'time_zone';
SET time_zone = '+12:00'; -- 设定时区
SELECT * FROM testdate;
 
-- 5. 复合类型
--   enum 枚举  单选框
--   set  集合  复选框
CREATE TABLE person(
sex ENUM('男','女'),
interest SET('看电影','游泳','打乒乓','下棋')
);
INSERT INTO person VALUES('男','看电影,打乒乓');
INSERT INTO person VALUES('不男不女','打lol');
INSERT INTO person VALUES('男女','看电影,打乒乓');
SELECT * FROM person;
 
-- 6 二进制类
-- binary(n)   n个字节
-- varbinary(n)
-- bit(n)n个位
-- tinyblob1个字节
-- blob图片、声音、视频
-- mediumblob
-- longblob