TIME
- 当只输入数字时,一次省略小时,分钟,秒。
不能超过小时,分钟,秒的范围
mysql> CREATE TABLE test13(
-> time TIME
-> );
Query OK, 0 rows affected (0.79 sec)
mysql> INSERT test13 VALUES(21);
Query OK, 1 row affected (0.11 sec)
mysql> INSERT test13 VALUES(1221);
Query OK, 1 row affected (0.17 sec)
mysql> INSERT test13 VALUES(361221);
Query OK, 1 row affected (0.18 sec)
mysql> SELECT*FROM test13;
+
| time |
+
| 00:00:21 |
| 00:12:21 |
| 36:12:21 |
| -12:34:55 |
+
4 rows in set (0.00 sec)
DATE
- 三个字节存储日期
- 12-06-07与12/06/07都可表示2012年6月7号
mysql> INSERT test10 VALUES('12-06-07');
Query OK, 1 row affected (0.46 sec)
mysql> INSERT test10 VALUES('12/06/07');
Query OK, 1 row affected (0.12 sec)
mysql> SELECT*FROM test10;
+
| day |
+
| 2012-06-07 |
| 2012-06-07 |
+
DATETIME
TIMESTAMP
YEAR
- 大小一个字节
- 测试year
mysql> CREATE TABLE IF NOT EXISTS test9(
-> birth YEAR
-> );
Query OK, 0 rows affected (0.80 sec)
mysql> INSERT test9 VALUES(1901);
Query OK, 1 row affected (0.48 sec)
mysql> INSERT test9 VALUES(2155);
Query OK, 1 row affected (0.20 sec)
mysql> SELECT*FROM test9;
+
| birth |
+
| 1901 |
| 2155 |
+
2 rows in set (0.00 sec)
- 如果大于2155 就会报错
mysql> INSERT test9 VALUES(2156);
ERROR 1264 (22003): Out of range value for column 'birth' at row 1
- 字符串同样可以添加
mysql> INSERT test9 VALUES("1996");
Query OK, 1 row affected (0.49 sec)
mysql> SELECT*FROM test9;
+
| birth |
+
| 1901 |
| 2155 |
| 1996 |
+
3 rows in set (0.00 sec)
- 当你输入两位数时代表的是(20xx)
mysql> INSERT test9 VALUE(90);
Query OK, 1 row affected (0.44 sec)
mysql> SELECT*FROM test9;
+
| birth |
+
| 1901 |
| 2155 |
| 1996 |
| 1990 |
+
4 rows in set (0.00 sec)
- 当你输入两位数字符串/数字时
– “00”-“69” 之间都会转换成20xx年
– “70”-"99"之间都会转换成19xx年
– 输入0时会出现0000
– 输入“0”或者“00”会出现2000
mysql> INSERT test9 VALUES("69");
Query OK, 1 row affected (0.48 sec)
mysql> INSERT test9 VALUES("79");
Query OK, 1 row affected (0.45 sec)
mysql> INSERT test9 VALUES(90);
Query OK, 1 row affected (0.13 sec)
mysql> INSERT test9 VALUES(65);
Query OK, 1 row affected (0.46 sec)
mysql> SELECT*FROM test9;
+
| birth |
+
| 1901 |
| 2155 |
| 1996 |
| 1990 |
| 2069 |
| 1979 |
| 1990 |
| 2065 |
+