Mysql常用语法（时间篇）

我们经常会遇到统计月份数据的情况，本文将为您介绍一下，MySQL查询实现的是查询本周、上周、本月、上个月份的数据，如果您对MySQL查询方面感兴趣的话，不妨一看！

---

# 0.创建表sql语句查询

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 |

---

# 1.今天

select * from byzp_personinfo where to_days(birthday) <= to_days(now());

---

# 2.昨天

select * from byzp_personinfo where to_days(NOW()) - TO_DAYS(birthday) <= 1;

---

# 3.近7天

select * from byzp_personinfo where date_sub(CURDATE(),INTERVAL 7 DAY) <= DATE(birthday);

---

# 4.近30天

select * from byzp_personinfo where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(birthday);

---

# 5.本月

select * from byzp_personinfo WHERE DATE_FORMAT( birthday, '%Y%m' ) = DATE_FORMAT( CURDATE() , '%Y%m' );

---

# 6.上一月

select * from byzp_personinfo WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( birthday, '%Y%m' ) ) =1;

---

# 7.查询本季度数据

select * from byzp_personinfo where QUARTER(birthday)=QUARTER(now());

---

# 8.查询上季度数据

select * from byzp_personinfo where QUARTER(birthday)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

---

# 9.查询本年数据

select * from byzp_personinfo where YEAR(birthday)=YEAR(NOW());

---

# 10.查询上年数据

select * from byzp_personinfo where year(birthday)=year(date_sub(now(),interval 1 year));

---

# 11.查询距离当前现在6个月的数据

select * from byzp_personinfo where birthday between date_sub(now(),interval 6 month) and now();

---

# 12.查询当前这周的数据

select * from byzp_personinfo WHERE YEARWEEK(date_format(birthday,'%Y-%m-%d')) = YEARWEEK(now());

---

# 13.查询上周的数据

select * from byzp_personinfo WHERE YEARWEEK(date_format(birthday,'%Y-%m-%d')) = YEARWEEK(now())-1;

---

# 14.查询上个月的数据

select * from byzp_personinfo where date_format(birthday,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m');

---

# 15.查询当前月份的数据

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');

---

# 16.查询指定时间段的数据

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 birt
hday >= '2018-6-1 00:00:00' and birthday < '2019-1-1 00:00:00';

---

17.查询月份数据

语法：
    select * from table_name where month(date)='2';
示例：
    查询表byzp_personinfo中2月份生日的人
    select * from byzp_personinfo where month(birthday) = '2'; 

18.查询年份数据

语法：
    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);

---