4:24 |
| 00:00:00 |
| 10:10:10 |
+----------+
3 rows in set (0.00 sec)
插入时间是在前面加上天：
mysql> INSERT INTO testtime VALUES('310:10:10');
Query OK, 1 row affected (0.16 sec)
mysql> SELECT * FROM testtime;
+----------+
| a |
+----------+
| 13:14:24 |
| 00:00:00 |
| 10:10:10 |
| 82:10:10 |
+----------+
4 rows in set (0.00 sec)
mysql> INSERT testtime VALUES('0');
Query OK, 1 row affected (0.02 sec)
mysql> SELECT * FROM testtime;
+----------+
| a |
+----------+
| 13:14:24 |
| 00:00:00 |
| 10:10:10 |
| 82:10:10 |
| 15:51:51 |
| 00:00:00 |
+----------+
6 rows in set (0.00 sec)?
mysql> INSERT testtime VALUES(123546);
Query OK, 1 row affected (0.03 sec)
mysql> SELECT * FROM testtime;
+----------+
| a |
+----------+
| 13:14:24 |
| 00:00:00 |
| 10:10:10 |
| 82:10:10 |
| 15:51:51 |
| 00:00:00 |
| 15:53:16 |
| 12:35:46 |
+----------+
8 rows in set (0.00 sec)
时间类型的插入形式总结：
INSERT testtime VALUES(‘13:14:23’);13:14:23
INSERT testtime VALUES(0);00:00:00
INSERT testtime VALUES(‘101010’);10:10:10
INSERT testtime VALUES(‘3 10：10：10’);82:10:10
INSERT testtime VALUES(‘5 10-10-20’);//不成功的
INSERT testtime VALUES(‘0’);00:00:00
INSERT testtime VALUES(124536);12:45:36
得到当前系统时间
NOW();
CURRENT_TIME
CURRENT_TIME()
mysql> INSERT testtime VALUES(NOW());
Query OK, 1 row affected (0.03 sec)
mysql> SELECT * FROM testtime;
+----------+
| a |
+----------+
| 13:14:24 |
| 00:00:00 |
| 10:10:10 |
| 82:10:10 |
| 15:51:51 |
+----------+
5 rows in set (0.00 sec)
mysql> INSERT testtime VALUES(CURRENT_TIME);
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM testtime;
+----------+
| a |
+----------+
| 13:14:24 |
| 00:00:00 |
| 10:10:10 |
| 82:10:10 |
| 15:51:51 |
| 00:00:00 |
| 15:53:16 |
+----------+
7 rows in set (0.00 sec)
//时间类型的最小值；
mysql> INSERT testtimeva lUES('-838:59:59');
Query OK, 1 row affected (0.01 sec)
//时间类型的最大值：
mysql> INSERT testtimeva lUES('838:59:59');
Query OK, 1 row affected (0.02 sec)
mysql> SELECT * FROM testtime;
+------------+
| a |
+------------+
| 13:14:24 |
| 00:00:00 |
| 10:10:10 |
| 82:10:10 |
| 15:51:51 |
| 00:00:00 |
| 15:53:16 |
| 12:35:46 |
| -838:59:59 |
| 838:59:59 |
+------------+
10 rows in set (0.00 sec)
//超出时间类型范围报错：
mysql> INSERT testtimeva lUES('838:69:59');
ERROR 1292 (22007): Incorrect time value:'838:69:59' for column 'a' at row 1
（2）DATE日期类型3个字节
1000-01-01~9999-12-31
CREATE TABLE IF NOT EXISTS testdate(
A DATE);
INSERT testdate VALUES(‘1000-01-01’);
INSERT testdate VALUES(‘2014/12/16’);
INSERT testdate VALUES(‘20120523’);
INSERT testdate VALUES(‘2014@12@16’);
得到当前系统的日期
NOW();
CURRENT_DATE
CURRENT_DATE();
INSERT testdate VALUES(NOW());
INSERT testdate VALUES(CURRENT_DATE);
INSERT testdate VALUES(CURRENT_DATE());
INSERT testdate VALUES(0);
实验结果：
mysql> ? DATE
Name: 'DATE'
Description:
DATE
A date. The supported range is '1000-01-01'to '9999-12-31'. MySQL
displays DATE values in 'YYYY-MM-DD'format, but permits assignment of
values to DATE columns using either stringsor numbers.
URL:http://dev.mysql.com/doc/refman/5.5/en/date-and-time-type-overview.html
mysql> CREATE TABLE IF NOT EXISTStestdate(
-> a DATE);
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT testdateva lUES('1000-01-01');
Query OK, 1 row affected (0.03 sec)
mysql> INSERT testdateva lUES('2014/12/13');
Query OK, 1 row affected (0.03 sec)
mysql> INSERT testdate VALUES(20151223);
Query OK, 1 row affected (0.06 sec)