MySQL数据库查询语句记录学习

以上图数据库表为例，表名moncenter_data_air，表结构如下图

---

1.查询今天数据指令
SELECT *, DATATIME FROM moncenter_data_air WHERE TO_DAYS(DATATIME) = TO_DAYS(NOW());

2.查询昨天数据指令
SELECT *, DATATIME FROM moncenter_data_air WHERE TO_DAYS(NOW()) - TO_DAYS(DATATIME) <= 1;

3.查询近7天数据指令 
SELECT *, DATATIME FROM moncenter_data_air WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= DATE(DATATIME);

4.查询近30天数据指令    
SELECT *, DATATIME FROM moncenter_data_air WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(DATATIME); 

5.查询本月数据指令    
SELECT *, DATATIME FROM moncenter_data_air WHERE DATE_FORMAT(DATATIME, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m');  

6.查询上月数据指令    
SELECT *, DATATIME FROM moncenter_data_air WHERE PERIOD_DIFF(DATE_FORMAT(NOW(), '%Y%m'), DATE_FORMAT(DATATIME, '%Y%m')) = 1;

7.查询本季度数据指令
SELECT *, DATATIME FROM moncenter_data_air WHERE QUARTER(DATATIME) = QUARTER(now());

8.查询上季度数据指令
SELECT *, DATATIME FROM moncenter_data_air WHERE QUARTER(DATATIME) = QUARTER(DATE_SUB(now(), interval 1 QUARTER));

9.查询本年数据指令
SELECT *, DATATIME FROM moncenter_data_air WHERE YEAR(DATATIME) = YEAR(NOW());

10.查询上年数据指令
SELECT *, DATATIME FROM moncenter_data_air WHERE YEAR(DATATIME) = YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR));

11.查询距离当前现在6个月的数据指令
SELECT *, DATATIME FROM moncenter_data_air WHERE DATATIME BETWEEN DATE_SUB(NOW(), INTERVAL 6 MONTH) AND NOW();

12.查询当前这周的数据指令
SELECT *, DATATIME FROM moncenter_data_air WHERE YEARWEEK(DATE_FORMAT(DATATIME, '%Y-%m-%d')) = YEARWEEK(NOW());

13.查询上周的数据指令
SELECT *, DATATIME FROM moncenter_data_air WHERE YEARWEEK(DATE_FORMAT(DATATIME, '%Y-%m-%d')) = YEARWEEK(NOW()) - 1;