mysql数据类型 和 约束

一、整形

1、分类：tinyint    smallint    mediumint   int   bigint

（1）有符号：有正负数

（2）无符号：没有负数     类型后面使用unsigned 和zerofill修饰符

存储数据大小范围：

1个字节         2个字节     3个字节     4个字节     8个字节
8bit                        16               24                 32             64

11111111         2^16-1

无符号：范围运算公式  0到2^n-1

           比如：

                        tinyint：0到255

                        bigint：0到2^64-1

有符号：范围运算公式-2^(n-1)到2^(n-1)-1

        比如：

                tinyint：-128到127

（3）显示宽度

            类型后的小括号内的数字是显示宽度，不能限制插入数值的大小

            比如：bigint(2)       2是显示宽度

（4）定义无符号整型：

          unsigned：

            mysql>create  table  t1(id  bigint(2)  unsigned);

          zerofill:

             mysql>create  table  t2( id1  int  zerofill, id2  int(6) zerofill);

             mysql>insert  into  t2  values(2,2);

            mysql>select * from t2;

    +------------+--------+
    | id1 | id2 |
    +------------+--------+
    | 0000000002 | 000002 |
    +------------+--------+

    1 row in set (0.00 sec)

二、浮点型

   1. 单精度：float(5.3)     5宽度(总共长度)    3精度(小数点后三位)

    mysql>create  table  t3(id  float(6,2));

   2.双精度：double(5.3)

    mysql>create table  t4(id  double(5,3));

   3.定点型

     定点数在Mysql内部以字符串形式存储，比浮点数更精确，适合用来表示货币等精度高的数据。

    decimal(5,3)

   4.位类型BIT(M)

           M范围从1—64，如果不写默认为0

        bit( )  显示为二进制

       mysql>create  table  t3(id  bit(4));   4bit 能存储的最大值是15

三、字符串类型

1.字符类型     char    varchar

（1）char(10)   根据小括号中的10，占10个。

                            列的长度固定为创建表时的声明的长度：0～255

（2）varchar(10)  根据实际字符串长度占空间，最多10个

                                列中的值为可变长字符串，长度：0～65535

2.总结字符类型使用：

(1)经常变化的字段用varchar

(2)知道固定长度的用char

(3)尽量用varchar

(4)超过255字符的只能用varchar或者text

(5)能用varchar的地方不用text（text是i当varchar长度不够时再使用text）

3.枚举类型  enum （enumerate[ɪˈnju:məreɪt]）

作用：在给的固定范围内多选一

mysql>create  table t3(name  enum('wing','tom'));

mysql>insert  into t3  values('wing');

只能从给的wing和tom两个中2选1

4.集合类型  set 

作用：在给的固定范围内可选多个

mysql>create  table t3(name  set('wing','tom','haha'));

mysql>insert  into t3  set  anme="wing,haha";

四、时间类型

时间和日期类型：year       date    time    datetime    timestamp(时间戳)

作用：用于存储用户的注册时间、文件的发布时间、文章的更新时间、员工的入职时间deng

(1)date

06/04

06-04

(2)year

00到69   为21世纪

70到99    为20世纪

(3)time

050510

(4)datetime  不给默认值为null

180509221810

(5)timestamp 默认把当前时间自动加上

180509221810

和datetime不同的是：当插入值为null时，显示值也会是当前的时间。

例题1：mysql>create  table  t(date  timestamp);

            mysql>insert  into  t  values(null);

            mysql>select  *  from  t;

            +---------------------+
            | id |
            +---------------------+
            | 2013-12-18 00:08:41 |
            +---------------------+

            1 row in set (0.00 sec)   

2.   mysql>create  table  t6(d date,t time,dt datetime);

      mysql>insert  into  t6  values(now(),nwo(),nwo());   -----引用now( )函数

       mysql> select * from t6;
+------------+----------+---------------------+
| d | t | dt |
+------------+----------+---------------------+
| 2018-01-11 | 10:59:57 | 2018-01-11 10:59:57 |

+------------+----------+---------------------+

五、约束（修饰符）

1.当一个字段被设置不能为空（not null）但是又没有给它设置值的时候，系统会按照不同的类型给予默认值

（1）数值   0

（2）字符串   空

（3）枚举类型enum      第一个预定义值

（4）时间戳timestamp     当前时间

2.设置系统默认值default

mysql>alter  table  t2  modify  id int  not null  default  8;      修改表t2的id字段的默认值为8

3.unique （key）唯一 

（唯一性不能重复：如果id=1已经被设置则不能再设置为id=1）

mysql>alter  table  t2  modify  id  int  unique;

4.auto_increment   自增    (额外的属性)

mysql>create table t2(id int unique  auto_increment,name  varchar(10));

删除时先modify修改属性再drop删除掉

5.primary  key  （key）  主键     ----->not  null + unique(key)     类似身份证ID

每张表中只能有一个主键

主键不能为空，而且唯一

方法一：mysql>create  table  t4(hostname char(20) primary  key,ip char(150));

方法二：mysql>create  table  t3(hostname  char(20),ip  char(150),primary  key(hostname));

6.联合主键（把多列设置为一个主键）

（1）设置主键：mysql>create  table  t4(hostname  char(10),ip  char(150),primary  key(hostname,ip));

（2）删除主键：mysql>alter  table  t3  drop  primary  key;

7.index(key) 把某一个字段（列）设置成index

索引   优化查询速度，便于查询

（1）创建索引的三种方法：

mysql> create table t4(hostname char(20) primary key,ip char(150),index (ip));

mysql> create table t3(hostname char(20) primary key,ip char(150),index dazhi(ip));

mysql> create index  index_name on t3(ip);

  语法：CREATE INDEX index_name ON table_name (column_list)

      不设置index_name默认把该字段（列）的名字作为索引名

（2）删除索引

mysql>alter table t3  drop  index  index_name；

（3）查看索引

mysql> SHOW INDEX FROM table_name\G

· Key_name

索引的名称。

· Column_name

列名称。

8.查看key

查看：mysql>desc  t2\G
PRI主键约束；
UNI唯一约束；
MUL非唯一的索引。

9.外键foreign key (key)

mysql> create table t1(id int,manager char(10) primary key) engine = innodb；
mysql> create table t2(id int,admin char(10),foreign key (admin) references t1 (manager)) engine = innodb ；

带关联删除的外键：on delete cascade
mysql> create table xingzheng(id int,admin char(10),foreign key (admin) references t4(manager) on delete cascade) engine = innodb;