字段类型

一、整型

整型的宽度是显示宽度，存储宽度是固定死的 无需指定

例如：
create table t5(id tinyint)

mysql> create table t5(id tinyint)

mysql> desc t4;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | tinyint(4) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> insert t4 values(128);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql>
mysql>
mysql> insert t4 values(127);
Query OK, 1 row affected (0.05 sec)

mysql> select * from t4;
+------+
| id |
+------+
| 127 |
+------+
1 row in set (0.00 sec)

mysql>

 

 

二、表字段类型之浮点类型

create table t7(x float(255,30),y double(255,30),z decimal(65,30));

insert t7 values
(1.111111111111111111111111111111,1.111111111111111111111111111111,1.111111111111111111111111111111);

 

传值精度越来越高 越来越准确

 

 

三、表字段类型之日期类型

 

year（1901/2155）

time 时:分:秒 （'-838:59:59'/'838:59:59'）

date 年:月:日 （1000-01-01/9999-12-31）

datetime 年:月:日 时:分:秒 1000-01-01 00:00:00/9999-12-31 23:59:59#用于记录注册时间

timestamp 年:月:日 时:分:秒 1970-01-01 00:00:00/2037  #用于记录更新时间

create table t8(y year,t time,d date,dt datetime,ts timestamp);
insert t8 values(now(),now(),now(),now(),now());


create table student(
    id int,
    name char(10),
    born_year year,
    bitrh date,
    reg_time datetime
);

insert student values
(1,"wangjing","1911","1911-11-11","1911-11-11 11:11:11"),
(2,"lxx","1988","1988-11-11","1988-11-11 11:11:11");

insert student values
(3,"wangjing","1911","19111111","19111111111111");

 

 

注意：

timestamp应该用于记录更新时间

create table t9(
    id int,
    name varchar(16),
    -- update_time datetime not null default now() on update now(),
    update_time timestamp,
    reg_time datetime not null default now()
);


insert into t9(id,name) values(1,"egon");

解读：

datetime not null default now() on update now(),   #用datetime类型  不为空 默认使用下载的时间 只要有一处改动 就更新到现在的时间
    update_time timestamp,

 

测试效果(虚拟表）

mysql> select * from t9;

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

| id | name | update_time | reg_time 　　　　|

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

| 1 | egon | 2020-09-01 16:45:51 | 2020-09-01 16:45:51

| +------+------+---------------------+---------------------+

1 row in set (0.00 sec)

mysql> update t9 set name="EGON" where id=1;

Query OK, 1 row affected (0.06 sec) 

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from t9;

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

| id | name | update_time | reg_time

| +------+------+---------------------+---------------------+

| 1 | EGON | 2020-09-01 16:46:50 | 2020-09-01 16:45:51 |

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

1 row in set (0.00 sec)

mysql>