MySQL:查询某年某月的数据
需求:查询2020年2月创建的所有用户,显示具体几号注册的分析:查询某年某月,可以使用一下方法date_format函数:date_format(date, "%Y-%m")="2020-02"year和month函数: year(date)=2020 and month(date)=82实战:1、创建表select version();8.0.16CREATE TABLE `user` (`id
·
需求:
查询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)
更多推荐
已为社区贡献34条内容
所有评论(0)