MySQL笔记: 数据类型

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
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. 日期时间类型
-- date YYYY-MM-DD 1000-01-01~9999-12-31
-- time HH:ii:ss -838:59:59~838:59:59
-- year YYYY
-- datetime YYYY-MM-DD HH:ii:ss 1000-01-01 00:00:00~9999-12-31 23:59:59
-- timestamp YYYY-MM-DD HH:ii:ss 1970-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个位
-- tinyblob 1个字节
-- blob 图片、声音、视频
-- mediumblob
-- longblob
文章目录