论初学Mysql

基础操作3

-- 日期和时间类型

类型	描述	存储需求	范围
DATETIME	格式为YYYY-MM-DD HH:MM:SS	8	1000-01-01 00:00:00~9999-12-31 23:59:59
DATE	格式为YYYY-MM-DD	3	1000-01-01~9999-12-31
TIMESTAMP	格式为YYYY-MM-DD HH:MM:SS	4	1970-01-01 00:00:01 UTC~2038-01-19 03:14:07
TIME	HH:MM:SS	3	-838:59:59~838:59:59
YEAR	YYYY	1	1901-2155

-- TIME类型用在只需要时间信息的值，在插入数据时要注意，因为可以采用非严格语法，支持

HH:MM:SS  10:05:05 10:05:05 HH:MM        23:23    23:23:00 D HH:MM     2 10:10  58:10:00  24*2+10:10  D代表天 D HH            3 02     74:00:00  24*3+10     小时部分小于10，前面补0 SS                10       00:00:10 HHMMSS     101112   11:11:12  如果分钟部分大于59，插入的会失败

mysql> create table temp4(a time);

Query OK, 0 rows affected (0.05 sec)

mysql> insert into temp4 values('10:10:10');

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from temp4

    -> ;

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

| a        |

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

| 10:10:10 |

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

1 row in set (0.00 sec)

 

mysql> insert into temp4 values(10:10:10);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that

corresponds to your MySQL server version for the right syntax to use near ':10:1

0)' at line 1

mysql> insert into temp4 values('23:23');

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from temp4;

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

| a        |

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

| 10:10:10 |

| 23:23:00 |

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

2 rows in set (0.00 sec)

 

mysql> insert into temp4 values('2 8');

ERROR 1292 (22007): Incorrect time value: '2 8' for column 'a' at row 1

mysql> insert into temp4 values('2 08');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from temp4;

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

| a        |

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

| 10:10:10 |

| 23:23:00 |

| 56:00:00 |

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

3 rows in set (0.00 sec)

 

mysql> insert into temp4 values('10');

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from temp4;

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

| a        |

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

| 10:10:10 |

| 23:23:00 |

| 56:00:00 |

| 00:00:10 |

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

4 rows in set (0.00 sec)

 

mysql> insert into temp4 values('106011');

ERROR 1292 (22007): Incorrect time value: '106011' for column 'a' at row 1

mysql> insert into temp4 values('107011');

ERROR 1292 (22007): Incorrect time value: '107011' for column 'a' at row 1

mysql> insert into temp4 values('105911');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from temp4;

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

| a        |

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

| 10:10:10 |

| 23:23:00 |

| 56:00:00 |

| 00:00:10 |

| 10:59:11 |

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

5 rows in set (0.00 sec)

 

mysql>

 mysql> insert into temp4 values(CURRENT_TIME),(now());

Query OK, 2 rows affected (0.06 sec)

Records: 2  Duplicates: 0  Warnings: 0