查询2020年2月创建的所有用户,显示具体几号注册的
查询某年某月,可以使用一下方法
1、创建表
select version();
8.0.16
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`create_time` datetime NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='用户表';
2、插入测试数据
insert into user (name, create_time) values ('Tom', '2020-01-01 10:20:09');
insert into user (name, create_time) values ('Tom', '2020-02-01 10:20:09');
insert into user (name, create_time) values ('Tom', '2020-03-01 10:20:09');
insert into user (name, create_time) values ('Tom', '2020-02-01 10:20:09');
insert into user (name, create_time) values ('Tom', '2020-03-01 10:20:09');
-- 查看表数据
mysql> select * from user;
+----+------+---------------------+
| id | name | create_time |
+----+------+---------------------+
| 1 | Tom | 2020-01-01 10:20:09 |
| 2 | Tom | 2020-02-01 10:20:09 |
| 3 | Tom | 2020-03-01 10:20:09 |
| 4 | Tom | 2020-02-01 10:20:09 |
| 5 | Tom | 2020-03-01 10:20:09 |
+----+------+---------------------+
5 rows in set (0.00 sec)
3、查询数据
-- 方式一:year和month函数
select
day(create_time), id, name
from
user
where
year(create_time) = 2020 and month(create_time) = 2;
+------------------+----+------+
| day(create_time) | id | name |
+------------------+----+------+
| 1 | 2 | Tom |
| 1 | 4 | Tom |
+------------------+----+------+
-- 方式二:date_format函数
select
day(create_time), id, name
from
user
where date_format(create_time, "%Y-%m")="2020-02";
+------------------+----+------+
| day(create_time) | id | name |
+------------------+----+------+
| 1 | 2 | Tom |
| 1 | 4 | Tom |
+------------------+----+------+
2 rows in set (0.00 sec)