mysql数据类型和表约束实例讲解

========================================
        tinyint[(m)] [unsigned] [zerofill]

            小整数，数据类型用于保存一些范围的整数数值范围：
            有符号(默认)：
                 范围:-128 ～ 127(顾头顾尾)

　　　　　　　　　　mysql> create table t1(x tinyint);
　　　　　　　　　　mysql> insert into t1 values(-128);

            无符号：
　　　　　　　　　　创建表时定义记录的字符为无符号类型,要使用unsigned
　　　　　　　　　　范围:　0 ～ 255(顾头顾尾)

　　　　　　　　　　mysql> create table t2(x tinyint unsigned);
　　　　　　　　　　mysql> insert into  t2 values(255);

　　　　　　　　注意:若插入的数字范围超过规定则会报错.

            PS： MySQL中无布尔值，使用tinyint(1)构造。



========================================
        int[(m)][unsigned][zerofill]

            整数，数据类型用于保存一些范围的整数数值范围：
            有符号：
                    -2147483648 ～ 2147483647
            无符号：
　　　　　　　　　　　　　　　　　　0 ～ 4294967295



========================================
        bigint[(m)][unsigned][zerofill]
            大整数，数据类型用于保存一些范围的整数数值范围：
            有符号：
                    -9223372036854775808 ～ 9223372036854775807
            无符号：
 　　　　　　　　　　　　　　　　　　　　　　　0  ～  18446744073709551615

mysql> create table t3(id int(1) unsigned);
#插入255555记录也是可以的
mysql> insert into t3 values(255555);
mysql> select * from t3;
+--------+
| id     |
+--------+
| 255555 |
+--------+

以上操作还不能够验证，再来一张表验证用zerofill 用0填充
# zerofill 用0填充
mysql> create table t4(id int(5) unsigned zerofill);
mysql> insert into t4 value(1);
Query OK, 1 row affected (0.00 sec)
#插入的记录是1，但是显示的宽度是00001
mysql> select * from t4;
+-------+
| id    |
+-------+
| 00001 |
+-------+
row in set (0.00 sec)

-------------------------FLOAT-------------------
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
参数解释：单精度浮点数（非准确小数值），M是全长,即显示的宽度，D是小数点后个数。M最大值为255，D最大值为30
　　　　unsigned定义为无符号型,常与zerofill搭配,若实际显示宽度不满足给定的宽度,则用0补充.
有符号：
           -3.402823466E+38 to -1.175494351E-38,
           1.175494351E-38 to 3.402823466E+38

无符号：
           1.175494351E-38 to 3.402823466E+38
精确度： 随着小数的增多，精度变得不准确 ****

mysql> create table t3(x float(255,30));
Query OK,0 rows affected (0.03 sec)
mysql> create table t4(y float(255,30) unsigned zerofill);
Query OK, 0 rows affected (0.27 sec)
insert into t4 values(1.0111123111);
Query OK, 0 rows affected (0.14 sec);
mysql> desc t4;
mysql> select *from t4;

 -------------------------DOUBLE-----------------------
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

参数解释： 双精度浮点数（非准确小数值），M是全长，D是小数点后个数。M最大值为255，D最大值为30


有符号：
           -1.7976931348623157E+308 to -2.2250738585072014E-308
           2.2250738585072014E-308 to 1.7976931348623157E+308

无符号：
           2.2250738585072014E-308 to 1.7976931348623157E+308

精确度：随着小数的增多，精度比float要高，但也会变得不准确

mysql> create table t5(x double(255,30));
Query OK, 0 row affected (0.03 sec)

--------------------DECIMAL------------------------
decimal[(m[,d])] [unsigned] [zerofill]

参数解释：准确的小数值，M是整数部分总个数（负号不算），D是小数点后个数。 M最大值为65，D最大值为30。

精确度：随着小数的增多，精度始终准确 
           对于精确数值计算时需要用此类型
           decaimal能够存储精确值的原因在于其内部按照字符串存储。

mysql> create table t6(x decimal(65,30));
Query OK, 0 rows affected (0.00 sec)

YEAR
            YYYY（1901~2155）
　　　　　　mysql> create table t6(year_t year);#无论year制定何种宽度,最后默认是year(4).
　　　　　　Query OK, 0 rows affected (0.03 sec)

　　　　　　mysql> insert into t8 values
    　　　　　　-> (1905),
    　　　　　　-> (2018);
　　　　　　Query OK, 2 rows affected (0.00 sec) #插入记录成功
　　　　　　Records: 2  Duplicates: 0  Warnings: 0

        DATE   
　　　　　　　　YYYY-MM-DD（1000-01-01~9999-12-31）


        TIME
            HH:MM:SS（'-838:59:59'~'838:59:59'）

        DATETIME

            YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59  Y）

　　　　mysql> create table t7(d date,t time,dt datetime);#d为文件或id名,date为类型.
　　　　Query OK, 0 rows affected (0.25 sec)
　　　　
　　　　

　　　　注意:now()为mysql自带函数,获取当前类型指定的时间.

       TIMESTAMP(了解)

            YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）

mysql> create table t10(time timestamp);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t10 values();
Query OK, 1 row affected (0.00 sec)

mysql> insert into t10 values(null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t10;
+------+
| time |
+------+
| NULL |
| NULL |
+------+

mysql> insert into t10 values(now());
Query OK, 1 row affected (0.01 sec)

mysql> select * from t10;
+---------------------+
| time |
+---------------------+
| 2018-06-09 09:44:48 |
+---------------------+
row in set (0.01 sec)

在实际应用的很多场景中，MySQL的这两种日期类型都能够满足我们的需要，存储精度都为秒，但在某些情况下，会展现出他们各自的优劣。
下面就来总结一下两种日期类型的区别。

1.DATETIME的日期范围是1001——9999年，TIMESTAMP的时间范围是1970——2038年。

2.DATETIME存储时间与时区无关，TIMESTAMP存储时间与时区有关，显示的值也依赖于时区。在mysql服务器，
操作系统以及客户端连接都有时区的设置。

3.DATETIME使用8字节的存储空间，TIMESTAMP的存储空间为4字节。因此，TIMESTAMP比DATETIME的空间利用率更高。

4.DATETIME的默认值为null；TIMESTAMP的字段默认不为空（not null）,默认值为当前时间（CURRENT_TIMESTAMP），
如果不做特殊处理，并且update语句中没有指定该列的更新值，则默认更新为当前时间。

============注意啦，注意啦，注意啦===========
#1. 单独插入时间时，需要以字符串的形式，按照对应的格式插入
#2. 插入年份时，尽量使用4位值
#3. 插入两位年份时，<=69，以20开头，比如50,  结果2050      
                >=70，以19开头，比如71，结果1971
 create table t12(y year);
 insert into t12 values  (50),(71);
 select * from t12;
+------+
| y    |
+------+
| 2050 |
| 1971 |
+------+

# 创建t1表,分别指明字段x为char类型，字段y为varchar类型
mysql> create table t1(x char(5),y varchar(4));
#此时规定char类型的显示宽度为5,varchar显示宽度为4.
Query OK, 0 rows affected (0.16 sec)

# char存放的是5个字符，而varchar存4个字符
mysql>  insert into t1 values('你谁啊 ','你谁呀 ');
Query OK, 1 row affected (0.01 sec)

# 在检索时char却不显示填充的宽度部分，而varchar存了多少，就显示多少
mysql> select x,char_length(x),y,char_length(y) from t1;
+-----------+----------------+------------+----------------+
| x         | char_length(x) | y          | char_length(y) |
+-----------+----------------+------------+----------------+
| 你瞅啥    |              3 | 你瞅啥     |              4 |
+-----------+----------------+------------+----------------+
row in set (0.02 sec)

 #略施小计，让char现原形
 mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected (0.00 sec)

#查看当前mysql的mode模式
mysql> select @@sql_mode;
+-------------------------+
| @@sql_mode              |
+-------------------------+
| PAD_CHAR_TO_FULL_LENGTH |
+-------------------------+
row in set (0.00 sec)

显示出本有的宽度
mysql> select x,char_length(x) y,char_length(y) from t1;
+-------------+------+----------------+
| x           | y    | char_length(y) |
+-------------+------+----------------+
| 你瞅啥      |    5 |              4 |
+-------------+------+----------------+
row in set (0.00 sec)

 查看字节数
char类型：3个中文字符+2个空格=11Bytes
　　#三个中文字符为9个字节,之前规定的显示宽度为5,所以要填充俩空格.
varchar类型:3个中文字符+1个空格=10Bytes
mysql> select x,length(x),y,length(y) from t1;
+-------------+-----------+------------+-----------+
| x           | length(x) | y          | length(y) |
+-------------+-----------+------------+-----------+
| 你谁啊      |        11 | 你谁呀    |        10 |
+-------------+-----------+------------+-----------+
row in set (0.02 sec)

mysql> create table consumer(
    -> id int,
    -> name varchar(50),
    -> sex enum('male','female','other'),
    -> level enum('vip1','vip2','vip3','vip4'),#在指定范围内，多选一
    -> fav set('play','music','read','study') #在指定范围内，多选多
    -> );
Query OK, 0 rows affected (0.03 sec)


mysql> insert into consumer values
    -> (1,'赵云','male','vip2','read,study'),
    -> (2,'赵云2','other','vip4','play');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from consumer;
+------+---------+-------+-------+------------+
| id   | name    | sex   | level | fav        |
+------+---------+-------+-------+------------+
|    1 | 赵云    | male  | vip2  | read,study |
|    2 | 赵云2   | other | vip4  | play       |
+------+---------+-------+-------+------------+
rows in set (0.00 sec)

PRIMARY KEY (PK)    #标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK)    #标识该字段为该表的外键
NOT NULL    #标识该字段不能为空
UNIQUE KEY (UK)    #标识该字段的值是唯一的
AUTO_INCREMENT    #标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT    #为该字段设置默认值

UNSIGNED #无符号
ZEROFILL #使用0填充

#1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
#2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
sex enum('male','female') not null default 'male'

#必须为正值（无符号） 不允许为空 默认是20
age int unsigned NOT NULL default 20 
# 3. 是否是key
主键 primary key
外键 foreign key
索引 (index,unique...)

#第一种创建unique的方式
create table department(
    id int unique,
    name char(10) unique  #若后面插入的名字重复,则会报错.
);
insert into department values(1,'it'),(2,'sale');


#第二种创建unique的方式
create table department(
    id int,
    name char(10) ,
    unique(id),
    unique(name)
);
insert into department values(1,'it'),(2,'sale');

# 创建t14表，为id字段设置主键，唯一的不同的记录
create table t14(
    id int primary key,
    name char(16)
);

insert into t14 values
(1,'xiaoma'),
(2,'xiaohong');

mysql> insert into t14 values(2,'wxxx');
ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'


#   not null + unique，相当于给id设置primary key
create table t15(
    id int not null unique,
    name char(16)
);
mysql> create table t15(
    -> id int not null unique,
    -> name char(16)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc t15;
+-------+----------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id        | int(11)  | NO     | PRI | NULL       |             |
| name