MySQL中用sql语句插入时期

最新推荐文章于 2026-01-01 22:00:33 发布

原创最新推荐文章于 2026-01-01 22:00:33 发布 · 1.6k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #sql #warnings #duplicates #query #2010

本文演示了如何使用MySQL创建包含日期和时间字段的数据表，并通过插入、更新数据来展示日期时间类型的使用方法。此外，还介绍了如何通过SQL语句查询特定月份的记录。

mysql> create table testdate(
-> id int not null auto_increment primary key,
-> time date);
Query OK, 0 rows affected (0.30 sec)

mysql> insert into testdate(time) values('2010-4-23');
Query OK, 1 row affected (0.06 sec)

mysql> select * from testdate;
+----+------------+
| id | time |
+----+------------+
| 1 | 2010-4-23|
+----+------------+
1 row in set (0.00 sec)

mysql> alter table testdate add column current time;
Query OK, 1 row affected (0.25 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> update testdate set current='21:18:00' where id=1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from testdate;
+----+------------+----------+
| id | time | current |
+----+------------+----------+
| 1 | 2010-4-23 | 21:18:00 |
+----+------------+----------+
1 row in set (0.00 sec)

mysql> alter table testdate add column combine timestamp;
Query OK, 1 row affected (0.14 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> update testdate set combine='20050504212000' where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

mysql> select * from testdate;
+----+------------+----------+---------------------+
| id | time | current | combine |
+----+------------+----------+---------------------+
| 1 | 2010-4-23 | 21:18:00 | 2010-4-23 21:20:00 |
+----+------------+----------+---------------------+
1 row in set (0.00 sec)

mysql> update testdate set combine=2010-4-23 21:22:0' where id=1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from testdate;
+----+------------+----------+---------------------+
| id | time | current | combine |
+----+------------+----------+---------------------+
| 1 | 2010-4-23 | 21:18:00 | 2010-4-23 21:22:00 |
+----+------------+----------+---------------------+
1 row in set (0.00 sec)

mysql> select * from testdate where month(time)=5;
+----+------------+----------+---------------------+
| id | time | current | combine |
+----+------------+----------+---------------------+
| 1 | 2010-4-23 | 21:18:00 | 2010-4-23 21:22:00 |
+----+------------+----------+---------------------+
1 row in set (0.00 sec)