lizs

	MySQL三大列类型：数值型、字符串型、日期时间类型。ggj

一、数值型：

1、整形：（默认是有符号）
	tinyint：1字节，8位。无符号：0-255 有符号(0正1负)：-128- 127
	smallint：2字节，16位。无符号：0-2^16-1 有符号：-2^15- 2^15-1
	mediumint：3字节，24位。
	int：4字节，32位。
	bigint：8字节，64位。
    整形列的可选属性：(默认有符号)
	类型(M) unsigned/zerofill
	M：宽度(在0填充的时候才有意义)
	unsigned：无符号类型(非负)
	zerofill：0填充(默认无符号)

mysql> create table class (
    -> id int primary key auto_increment,
    -> name varchar(10),
    -> age tinyint
    -> ) charset utf8;
Query OK, 0 rows affected (0.32 sec)

mysql> show tables;
+---------------+
| Tables_in_lzs |
+---------------+
| class         |
| messages      |
| shop          |
+---------------+
3 rows in set (0.00 sec)

mysql> desc class;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
| age   | tinyint(4)  | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.03 sec)

mysql> insert into class
    -> (name, age)
    -> values
    -> (a, 1),
    -> (b,2);
ERROR 1054 (42S22): Unknown column 'a' in 'field list'
//varchar型要用单引号括起来
mysql> insert into class
    -> (name, age)
    -> values
    -> ('a', 1),
    -> ('b',2);
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from class;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | a    |    1 |
|  2 | b    |    2 |
+----+------+------+
2 rows in set (0.00 sec)

//增加一列，age1，类型是tinyint unsigned
mysql> alter table class
    -> add
    -> age1 tinyint unsigned;
Query OK, 0 rows affected (0.62 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc class;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | int(11)             | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)         | YES  |     | NULL    |                |
| age   | tinyint(4)          | YES  |     | NULL    |                |
| age1  | tinyint(3) unsigned | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql> delete from class where id=2;
Query OK, 1 row affected (0.07 sec)

mysql> select * from class;
+----+------+------+------+
| id | name | age  | age1 |
+----+------+------+------+
|  1 | a    |    1 | NULL |
+----+------+------+------+
1 row in set (0.00 sec)

mysql> insert into class
    -> (name, age)
    -> values
    -> ('b', 2);
Query OK, 1 row affected (0.06 sec)

mysql> select * from class;
+----+------+------+------+
| id | name | age  | age1 |
+----+------+------+------+
|  1 | a    |    1 | NULL |
|  3 | b    |    2 | NULL |
+----+------+------+------+
2 rows in set (0.00 sec)

mysql> update class set
    -> id=id-1
    -> where id>1;
Query OK, 1 row affected (0.17 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from class;
+----+------+------+------+
| id | name | age  | age1 |
+----+------+------+------+
|  1 | a    |    1 | NULL |
|  2 | b    |    2 | NULL |
+----+------+------+------+
2 rows in set (0.00 sec)

mysql> insert into class
    -> (name, age1)
    -> values
    -> ('c', 3);
Query OK, 1 row affected (0.03 sec)

mysql> select * from class;
+----+------+------+------+
| id | name | age  | age1 |
+----+------+------+------+
|  1 | a    |    1 | NULL |
|  2 | b    |    2 | NULL |
|  4 | c    | NULL |    3 |
+----+------+------+------+
3 rows in set (0.00 sec)

mysql> alter table class add age2 tinyint(5) zerofill;
Query OK, 0 rows affected (0.38 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc class;
+-------+------------------------------+------+-----+---------+
| Field | Type                         | Null | Key | Default |
+-------+------------------------------+------+-----+---------+
| id    | int(11)                      | NO   | PRI | NULL    |
| name  | varchar(10)                  | YES  |     | NULL    |
| age   | tinyint(4)                   | YES  |     | NULL    |
| age1  | tinyint(3) unsigned          | YES  |     | NULL    |
| age2  | tinyint(5) unsigned zerofill | YES  |     | NULL    |
+-------+------------------------------+------+-----+---------+
5 rows in set (0.01 sec)

mysql> insert into class
    -> (name, age2)
    -> values
    -> ('d', 4);
Query OK, 1 row affected (0.03 sec)

mysql> select * from class;
+----+------+------+------+-------+
| id | name | age  | age1 | age2  |
+----+------+------+------+-------+
|  1 | a    |    1 | NULL |  NULL |
|  2 | b    |    2 | NULL |  NULL |
|  4 | c    | NULL |    3 |  NULL |
|  5 | d    | NULL | NULL | 00004 | //5位，自动填充0
+----+------+------+------+-------+
4 rows in set (0.02 sec)

mysql> alter table class add age3 tinyint(1) zerofill;
Query OK, 0 rows affected (0.41 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc class;
+-------+------------------------------+------+-----+---------+
| Field | Type                         | Null | Key | Default |
+-------+------------------------------+------+-----+---------+
| id    | int(11)                      | NO   | PRI | NULL    |
| name  | varchar(10)                  | YES  |     | NULL    |
| age   | tinyint(4)                   | YES  |     | NULL    |
| age1  | tinyint(3) unsigned          | YES  |     | NULL    |
| age2  | tinyint(5) unsigned zerofill | YES  |     | NULL    |
| age3  | tinyint(1) unsigned zerofill | YES  |     | NULL    |
+-------+------------------------------+------+-----+---------+
6 rows in set (0.01 sec)

mysql> insert into class
    -> (name, age3)
    -> values
    -> ('e', 5);
Query OK, 1 row affected (0.07 sec)

mysql> select * from class;
+----+------+------+------+-------+------+
| id | name | age  | age1 | age2  | age3 |
+----+------+------+------+-------+------+
|  1 | a    |    1 | NULL |  NULL | NULL |
|  2 | b    |    2 | NULL |  NULL | NULL |
|  4 | c    | NULL |    3 |  NULL | NULL |
|  5 | d    | NULL | NULL | 00004 | NULL |
|  6 | e    | NULL | NULL |  NULL |   5  | //zerofill，0填充1位
+----+------+------+------+-------+------+
5 rows in set (0.00 sec)

mysql> insert into class
    -> (name, age3)
    -> ('f', 255); //age3是tinyint(1) zerofill，0填充1位，只是填充效果，不影响数据的填充，一样是可以执行的

mysql> alter table class add age4 tinyint(1);
Query OK, 0 rows affected (0.46 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc class;
+-------+------------------------------+------+-----+---------+
| Field | Type                         | Null | Key | Default |
+-------+------------------------------+------+-----+---------+
| id    | int(11)                      | NO   | PRI | NULL    |
| name  | varchar(10)                  | YES  |     | NULL    |
| age   | tinyint(4)                   | YES  |     | NULL    |
| age1  | tinyint(3) unsigned          | YES  |     | NULL    |
| age2  | tinyint(5) unsigned zerofill | YES  |     | NULL    |
| age3  | tinyint(1) unsigned zerofill | YES  |     | NULL    |
| age4  | tinyint(1)                   | YES  |     | NULL    |
+-------+------------------------------+------+-----+---------+
7 rows in set (0.01 sec)

mysql> insert into class
    -> (name, age4)
    -> values
    -> ('f', 16); //tinyint(1)后面的1在0填充时才有意义
Query OK, 1 row affected (0.07 sec)

mysql> select * from class;
+----+------+------+------+-------+------+------+
| id | name | age  | age1 | age2  | age3 | age4 |
+----+------+------+------+-------+------+------+
|  1 | a    |    1 | NULL |  NULL | NULL | NULL |
|  2 | b    |    2 | NULL |  NULL | NULL | NULL |
|  4 | c    | NULL |    3 |  NULL | NULL | NULL |
|  5 | d    | NULL | NULL | 00004 | NULL | NULL |
|  6 | e    | NULL | NULL |  NULL |    5 | NULL |
|  7 | f    | NULL | NULL |  NULL | NULL |   16 |
+----+------+------+------+-------+------+------+
6 rows in set (0.00 sec)

//我们可以看到很多没有填充的值都是NULL，因为Default默认是NULL值。
//我们可以设置默认值，not NULL default 某值
mysql> alter table class add age5 tinyint not null default 0;
Query OK, 0 rows affected (0.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from class;
+----+------+------+------+-------+------+------+------+
| id | name | age  | age1 | age2  | age3 | age4 | age5 |
+----+------+------+------+-------+------+------+------+
|  1 | a    |    1 | NULL |  NULL | NULL | NULL |    0 |
|  2 | b    |    2 | NULL |  NULL | NULL | NULL |    0 |
|  4 | c    | NULL |    3 |  NULL | NULL | NULL |    0 |
|  5 | d    | NULL | NULL | 00004 | NULL | NULL |    0 |
|  6 | e    | NULL | NULL |  NULL |    5 | NULL |    0 |
|  7 | f    | NULL | NULL |  NULL | NULL |   16 |    0 |
|  8 | f    | NULL | NULL |  NULL | NULL |   16 |    0 |
|  9 | f    | NULL | NULL |  NULL |   16 | NULL |    0 |
+----+------+------+------+-------+------+------+------+
8 rows in set (0.00 sec)

mysql> update class set
    -> id=id-1
    -> where id>2;
Query OK, 6 rows affected (0.03 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql> select * from class;
+----+------+------+------+-------+------+------+------+
| id | name | age  | age1 | age2  | age3 | age4 | age5 |
+----+------+------+------+-------+------+------+------+
|  1 | a    |    1 | NULL |  NULL | NULL | NULL |    0 |
|  2 | b    |    2 | NULL |  NULL | NULL | NULL |    0 |
|  3 | c    | NULL |    3 |  NULL | NULL | NULL |    0 |
|  4 | d    | NULL | NULL | 00004 | NULL | NULL |    0 |
|  5 | e    | NULL | NULL |  NULL |    5 | NULL |    0 |
|  6 | f    | NULL | NULL |  NULL | NULL |   16 |    0 |
|  7 | f    | NULL | NULL |  NULL | NULL |   16 |    0 |
|  8 | f    | NULL | NULL |  NULL |   16 | NULL |    0 |
+----+------+------+------+-------+------+------+------+
8 rows in set (0.00 sec)


2、小数型：
	浮点型：float(M,D)
	定点型：decimal(M,D)  //定点型更精确

	M：精度(总位数，不包含点)
	D：标度(小数位)

    同样，小数型也是有unsigned型的。
    float和double容易产生误差，对精确度要求比较高时，建议使用decimal来存放，decimal在mysql内存是以字符串存储的，用于定义货币要求精确度高的数据。在数据迁移中，float(M,D)是非标准定义，最好不要这样使用。M为精度，D为标度。
    float和real数据类型被称为近似的数据类型。不存储精确值.当要求精确的数字状态时，比如在财务应用程序中，在那些需要舍入的操作中，或在等值核对的操作中，就不使用这些数据类型。这时就要用integer、decimal、money或smallmone数据类型。
　　在 WHERE 子句搜索条件中(特别是 = 和 <> 运算符)，应避免使用float或real列。最好限制使用float和real列做> 或 < 的比较。

mysql> create table goods (
    -> id tinyint(3) zerofill primary key auto_increment,
    -> name varchar(10) not null default '',
    -> price float(5.2) not null default 0.00,
    -> decprice decimal(5.2) not null default 0.00
    -> )charset utf8;
Query OK, 0 rows affected (0.36 sec)

mysql> desc goods;
+----------+------------------------------+------+-----+---------+
| Field    | Type                         | Null | Key | Default |
+----------+------------------------------+------+-----+---------+
| id       | tinyint(3) unsigned zerofill | NO   | PRI | NULL    |
| name     | varchar(10)                  | NO   |     |         |
| price    | float                        | NO   |     | 0       |
| decprice | decimal(5,0)                 | NO   |     | 0       |
+----------+------------------------------+------+-----+---------+
4 rows in set (0.05 sec)


二、字符型：

    char：定长类型，1字节。
	char(M)，0<=M<=255，当存入N个字符小于M个字符时，实占M个字符空间，会在N个字符后面加空格补齐。所以，对定长char而言，如果存入的字符最后有空格字符，取出来的时候，空格字符会被当作是填充的，空格字符就会丢失掉。但是，varchar类型不会丢掉，varchar会取存入的个数。

    varchar：变长类型，2字节。
	varchar(M)，0<=M<=65535（以ascii字符为例，utf8是22000左右），当存入小于M个字符时，实占存入字符个数的字符空间。

    text：文本类型。
	text，不用加默认值(加了也是无效的).可以存放比较大的文本段，约2W-6W个字符(受字符集影响)。因此，如果不是特别大的内容，建议使用char、varchar。

mysql> alter table student
    -> add
    -> essay text not null default ''; //添加默认值会失败
ERROR 1101 (42000): BLOB/TEXT column 'essay' can not have a default value.

mysql> create table student (
    -> id tinyint(3) zerofill primary key auto_increment,
    -> firstname char(8) not null default '',
    -> lastname varchar(8) not null default ''
    -> );
Query OK, 0 rows affected (2.08 sec)

mysql> desc student;
+-----------+------------------------------+------+-----+---------+
| Field     | Type                         | Null | Key | Default |
+-----------+------------------------------+------+-----+---------+
| id        | tinyint(3) unsigned zerofill | NO   | PRI | NULL    |
| firstname | char(8)                      | NO   |     |         |
| lastname  | varchar(8)                   | NO   |     |         |
+-----------+------------------------------+------+-----+---------+
3 rows in set (0.38 sec)

mysql> insert into student
    -> (firstname, lastname)
    -> values
    -> ('first', 'last'),
    -> ('first ', 'last ');
Query OK, 2 rows affected (0.14 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from student;
+-----+-----------+----------+
| id  | firstname | lastname |
+-----+-----------+----------+
| 001 | first     | last     |
| 002 | first     | last     |
+-----+-----------+----------+
2 rows in set (0.00 sec)

mysql> select concat(firstname, '!'), concat(lastname, '!') from student
+------------------------+-----------------------+
| concat(firstname, '!') | concat(lastname, '!') |
+------------------------+-----------------------+
| first!                 | last!                 |
| first!                 | last !                |
+------------------------+-----------------------+
2 rows in set (0.03 sec)


三、日期时间类型：
    year：年类型，1字节，一共可以表示256种年份：0000、1901-2155。(0000，表示不输人或者选择错误)。

    年份一般是4位的，但是，以前有用2位来表示年份的，如97，表示1997年。但是如果输入的是10呢？1910？2010？还是2110？所以，当输入2位的时候：
    00-69：表示2000-2069； 70-99：表示1970-1999

    date：日期类型，典型格式：如，1991-04-02。
范围：1000-01-01- 9999-12-31

    time：时间类型，典型格式： hh:mm:ss

    datetime：日期时间类型，典型格式：如1991-04-02 15:23:23
    范围：1000-01-01 00:00:00 - 9999-12-31 23:59:59
    注意：在开发中，很少用日期时间类型来表示一个需要的精确到秒的列。一般用时间戳来表示。
    时间戳：用int型来存储，表示1970-01-01 00:00:00 到当前的秒数。
    一般存注册时间、商品发布时间等，并不是用datetime存储，而是用时间戳。因为，datetime虽然直观，但是计算不方便。而用int型存储时间戳，方便计算，对于显示，也可以方便格式化成不同的显示样式。
    在MySQL中提供了当前时间的函数：now()
MySQL - Column types

MySQL - CURD/CRUD
