MySQL：查询某年某月的数据

需求：

查询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)