MySQL中的数据类型-----日期时间

 

-- HH:MM:SS [D HH:MM:SS] D表示天数 0~34
-- 测试TIME类型
CREATE TABLE test_time(
a TIME
);
INSERT test_time(a) VALUES('12:23:45');
INSERT test_time(a) VALUES('2 12:23:45');
INSERT test_time(a) VALUES('22:22');
INSERT test_time(a) VALUES('22');
INSERT test_time(a) VALUES('2 22');
-- HHMMSS
INSERT test_time(a) VALUES('121212');
INSERT test_time(a) VALUES('0');
INSERT test_time(a) VALUES(0);
INSERT test_time(a) VALUES('787878');

INSERT test_time(a) VALUES(NOW());
INSERT test_time(a) VALUES(CURRENT_TIME);

-- 测试DATE类型 YYYY-MM-DD YYYYMMDD
CREATE TABLE test_date(
    a DATE
);
INSERT test_date(a) VALUES('2017-03-04');
INSERT test_date(a) VALUES('2017-2-13');
INSERT test_date(a) VALUES('4007-03-23');
INSERT test_date(a) VALUES('40071212');
INSERT test_date(a) VALUES('4007@12@12');
INSERT test_date(a) VALUES('4008#13#13');
INSERT test_date(a) VALUES('4009.8.14');


-- YY-MM-DD YYMMDD
-- 70~99 转换成1970~1999 00~69 2000~2069
INSERT test_date(a) VALUES('780902');
INSERT test_date(a) VALUES('650902');
INSERT test_date(a) VALUES('880902');
INSERT test_date(a) VALUES(NOW());
INSERT test_date(a) VALUES(CURRENT_DATE);

-- 测试DATETIME

CREATE TABLE test_datetime(
a DATETIME
);
INSERT test_datetime(a) VALUES('1004-09-12 13:24:56');
INSERT test_datetime(a) VALUES('720305121212');
INSERT test_datetime(a) VALUES(NOW());
INSERT test_datetime(a) VALUES(DATETIME);

-- 测试TIMESTAMP
CREATE TABLE test_timestamp(
    a TIMESTAMP
);
INSERT test_timestamp(a) VALUES('1978-10-23 12:12:12');
INSERT test_timestamp(a) VALUES('1999/10/01 00:00:00'); --1999/10/01: 时间, 日期的分隔符任意


mysql> INSERT test_timestamp(a) VALUES('1978-10-23 12:12:12');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM test_timestamp;
+---------------------+
| a                   |
+---------------------+
| 1999-10-01 00:00:00 |
| 1978-10-23 12:12:12 |
+---------------------+
2 rows in set (0.00 sec)
 


-- 插入CURRENT_TIMESTAMP
INSERT test_timestamp VALUES(CURRENT_TIMESTAMP);

-- 插入NULL
INSERT test_timestamp VALUES(NULL);

-- 什么也不写 得到当前系统日期和时间
INSERT test_timestamp VALUES();

mysql> SELECT * FROM test_timestamp;
+---------------------+
| a                   |
+---------------------+
| 1999-10-01 00:00:00 |
| 1978-10-23 12:12:12 |
| 2019-09-20 11:09:19 |<--INSERT test_timestamp VALUES(CURRENT_TIMESTAMP);
| 2019-09-20 11:10:15 |<--INSERT test_timestamp VALUES(NULL);
| 2019-09-20 11:12:06 |<--INSERT test_timestamp VALUES();
+---------------------+
5 rows in set (0.00 sec)


-- 测试YEAR

CREATE TABLE test_year(
    a YEAR
);

INSERT test_year(a) VALUES(1901);

-- 00~69 2000~2069 70~99 1970~1999
-- 0 插入的结果是0000
-- '0' 插入的结果是2000

 

--HH:MM:SS [D HH:MM:SS] D: 表示天数
--测试TIME类型

mysql> CREATE TABLE test_time(a TIME);

mysql> DESC test_time;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| a     | time | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> INSERT test_time(a) VALUES('10:26:28');

mysql> INSERT test_time(a) VALUES('10:26:28');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM test_time;
+----------+
| a        |
+----------+
| 10:26:28 |
+----------+
1 row in set (0.00 sec)

mysql> INSERT test_time(a) VALUES('2 10:26:28');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM test_time;
+----------+
| a        |
+----------+
| 10:26:28 |
| 58:26:28 |-----<<<<-------VALUES('2 10:26:28')  <=> 2*24+10(小时):26(分):28(秒)
+----------+
2 rows in set (0.00 sec)

--在啊VALUES('22')中只写入一个22
mysql> INSERT test_time(a) VALUES('22');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM test_time;
+----------+
| a        |
+----------+
| 10:26:28 |
| 58:26:28 |
| 00:00:22 |-----<----------只写入一个22的情况
+----------+
3 rows in set (0.00 sec)


mysql> INSERT test_time(a) VALUES(NOW()); --VALUES(NOW()) 插入系统的当前时间
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> SELECT * FROM test_time;
+----------+
| a        |
+----------+
| 10:26:28 |
| 58:26:28 |
| 00:00:22 |
| 10:37:20 |--<<--NOW()
+----------+
4 rows in set (0.00 sec)

--测试DATE类型 YYYY-MM-DD  <=> YYYYMMDD

CREATE TABLE test_date(a DATE);
INSERT test_date(a) VALUES(NOW());

mysql> CREATE TABLE test_date(a DATE);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT test_date(a) VALUES(NOW());
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> SELECT * FROM test_date;
+------------+
| a          |
+------------+
| 2019-09-20 |
+------------+
1 row in set (0.00 sec)

CREATE TABLE test_date_and_time(d DATE,  t TIME);
INSERT test_date_and_time(d, t) VALUES(NOW(), NOW());


 CREATE TABLE test_date_and_time(d DATE,  t TIME);
Query OK, 0 rows affected (0.01 sec)

mysql> DESC test_date_and_time;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| d     | date | YES  |     | NULL    |       |
| t     | time | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> INSERT test_date_and_time(d, t) VALUES(NOW(), NOW());
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql> select * from test_date_and_time;
+------------+----------+
| d          | t        |
+------------+----------+
| 2019-09-20 | 10:46:42 |
+------------+----------+
1 row in set (0.00 sec)