我们经常会遇到统计月份数据的情况，本文将为您介绍一下，MySQL查询实现的是
查询本周、上周、本月、上个月份的数据，
如果您对MySQL查询方面感兴趣的话，不妨一看！
mysql> show create table byzp_personinfo;
CREATE TABLE
`byzp_personinfo` (
`id`
int(
11) NOT NULL AUTO_INCREMENT,
`username` varchar(
32) NOT NULL,
`birthday` date NOT NULL,
`create_data` datetime(
6),
PRIMARY KEY (
`id`)
) ENGINE=InnoDB AUTO_INCREMENT=
22 DEFAULT CHARSET=utf8 |
select *
from byzp_personinfo
where
to_days(birthday) <=
to_days(
now());
select *
from byzp_personinfo
where
to_days(
NOW()) -
TO_DAYS(birthday) <=
1;
select *
from byzp_personinfo
where
date_sub(
CURDATE(),
INTERVAL
7
DAY) <=
DATE(birthday);
select *
from byzp_personinfo
where
DATE_SUB(
CURDATE(),
INTERVAL
30
DAY) <=
date(birthday);
select *
from byzp_personinfo
WHERE
DATE_FORMAT( birthday,
'%Y%m' ) =
DATE_FORMAT(
CURDATE() ,
'%Y%m' );
select *
from byzp_personinfo
WHERE
PERIOD_DIFF(
date_format(
now( ) ,
'%Y%m' ) ,
date_format( birthday,
'%Y%m' ) ) =
1;
select *
from byzp_personinfo
where
QUARTER(birthday)=
QUARTER(
now());
select *
from byzp_personinfo
where
QUARTER(birthday)=
QUARTER(
DATE_SUB(
now(),
interval
1
QUARTER));
select *
from byzp_personinfo
where
YEAR(birthday)=
YEAR(
NOW());
select *
from byzp_personinfo
where
year(birthday)=
year(
date_sub(
now(),
interval
1
year));
select *
from byzp_personinfo
where birthday
between
date_sub(
now(),
interval
6
month)
and
now();
select *
from byzp_personinfo
WHERE
YEARWEEK(
date_format(birthday,
'%Y-%m-%d')) =
YEARWEEK(
now());
select *
from byzp_personinfo
WHERE
YEARWEEK(
date_format(birthday,
'%Y-%m-%d')) =
YEARWEEK(
now())-
1;
select *
from byzp_personinfo
where
date_format(birthday,
'%Y-%m')=
date_format(
DATE_SUB(
curdate(),
INTERVAL
1
MONTH),
'%Y-%m');
select *
from byzp_personinfo
where
DATE_FORMAT(birthday,
'%Y%m') =
DATE_FORMAT(
CURDATE(),
'%Y%m');
select *
from byzp_personinfo
where
date_format(birthday,
'%Y-%m')=
date_format(
now(),
'%Y-%m');
select *
from byzp_personinfo
where birthday <
'2018-4-1 00:00:00'
or birthday >
'2018-10-31 00:00:00';
select *
from byzp_personinfo
where birthday >=
'2018-6-1 00:00:00'
and birthday <
'2019-1-1 00:00:00';
语法：
select *
from table_name
where
month(
date)=
'2';
示例：
查询表byzp_personinfo中2月份生日的人
select *
from byzp_personinfo
where
month(birthday) =
'2';
语法：
select *
from table_name
where
year(
date)=年份;
示例：
查询表byzp_personinfo中2019年的数据
select *
from byzp_personinfo
where
year(birthday) =
'2019';
注：year,month,dayofyear是mysql的函数，分别是取得年、月、当前时间在本年是第几天的3个函数。
mysql数据库查询某一年内各月份数据，按月份分组
select
month(birthday)
as
month,
sum(id)
from byzp_personinfo
where
year(birthday) =
2018
group
by
month (birthday);
