需求：
查询2020年2月创建的所有用户，显示具体几号注册的
分析：
查询某年某月，可以使用一下方法
date_format函数：date_format(date, "%Y-%m")="2020-02"
year和month函数： year(date)=2020 and month(date)=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)
