整型/浮点型/日期类型

修改sql_mode为严格模式,,必须重启客户端
    set global sql_mode="strict_trans_tables";

查看sql_mode
    select @@sql_mode;

整型:
    tinyint    默认有符号,范围(-128,127)
    设置成默认无符号,在新建表时最后加上unsigned  范围(0,255)
        create table 表名(a tinyint unsigned);
    int (自己不设置显示宽度)
    强调:
        整型类型后面的宽度限制的不是存储宽度,限制的是显示宽度
        create table 表名(id int);


浮点型:
             (全部位数,小数位数)
    float    (255,30)
    double   (255,30)
    decimal  (65,30)

    create table 表名(a float(255,30));
    create table 表名(a double(255,30));
    create table 表名(a decimal(65,30));

    insert into t8 values(1.111111111111111111111111111111);
    insert into t9 values(1.111111111111111111111111111111);
    insert into t10 values(1.111111111111111111111111111111);


日期类型

year 1998
date 1998-06-02
time 08:30:00
datetime /  timestamp  1998-06-02 08:30:00
1000-01-01 9999-12-31   / 1970---2037



示例:

mysql> create table student(
    id int primary key auto_increment,
    name char(16),
    born_year year,
    birth date,
    class_time time,
    reg_time datetime
);

mysql> desc student;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| name       | char(16) | YES  |     | NULL    |                |
| born_year  | year(4)  | YES  |     | NULL    |                |
| birth      | date     | YES  |     | NULL    |                |
| class_time | time     | YES  |     | NULL    |                |
| reg_time   | datetime | YES  |     | NULL    |                |
+------------+----------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

mysql> insert into student(name,born_year,birth,class_time,reg_time) values
('hgq',now(),now(),now(),now());

mysql> select * from student;
+----+------+-----------+------------+------------+---------------------+
| id | name | born_year | birth      | class_time | reg_time            |
+----+------+-----------+------------+------------+---------------------+
|  1 | hgq  |      2018 | 2018-09-12 | 16:17:04   | 2018-09-12 16:17:04 |
+----+------+-----------+------------+------------+---------------------+
1 row in set (0.00 sec)

mysql> insert into student(name,born_year,birth,class_time,reg_time) values
('何桂清',1998,' 1998-06-02','08:30:00','2018-07-17 08:30:00');

mysql> select * from student;
+----+-----------+-----------+------------+------------+---------------------+
| id | name      | born_year | birth      | class_time | reg_time            |
+----+-----------+-----------+------------+------------+---------------------+
|  1 | hgq       |      2018 | 2018-09-12 | 16:17:04   | 2018-09-12 16:17:04 |
|  2 | 何桂清    |      1998 | 1998-06-02 | 08:30:00   | 2018-07-17 08:30:00 |
+----+-----------+-----------+------------+------------+---------------------+
2 rows in set (0.00 sec)