---
title: MySQL笔记： 数据类型
date: 2017-03-31 21:06:00
updated: 2017-03-31 21:06:00
description: "数据类型"
categories: [个人笔记]
tags: [MySQL, MySQL笔记]
---

```sql
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
```