查询当前日期
SELECT CURRENT_DATE ( ) ;
SELECT CURDATE( ) ;
查询当前日期和时间
SELECT NOW ( ) ;
查询今天的数据
SELECT * FROM ` 表名` WHERE TO_DAYS( NOW ( ) ) = TO_DAYS( ` 字段` ) ;
SELECT * FROM ` 表名` WHERE TO_DAYS( NOW ( ) ) - TO_DAYS( ` 字段` ) = 0 ;
查询昨天的数据
SELECT * FROM ` ` 表名` ` WHERE TO_DAYS( ` 字段` ) = TO_DAYS( NOW ( ) ) - 1 ;
SELECT * FROM ` ` 表名` ` WHERE TO_DAYS( ` 字段` ) - TO_DAYS( NOW ( ) ) = - 1 ;
SELECT * FROM ` ` 表名` ` WHERE TO_DAYS( NOW ( ) ) = TO_DAYS( ` 字段` ) + 1 ;
SELECT * FROM ` ` 表名` ` WHERE TO_DAYS( NOW ( ) ) - TO_DAYS( ` 字段` ) = 1 ;
查询最近七天的数据
SELECT * FROM ` 表名` WHERE DATE_SUB( CURDATE( ) , INTERVAL 7 DAY ) <= DATE ( ` 字段` ) ;
查询最近三十天的数据
SELECT * FROM table WHERE DATE_SUB( CURDATE( ) , INTERVAL 30 DAY ) <= DATE ( ` 字段` ) ;
查询本周的数据
SELECT * FROM ` 表名` WHERE YEARWEEK( date_format( ` 字段` , '%Y-%m-%d' ) ) = YEARWEEK( CURDATE( ) ) ;
SELECT * FROM ` 表名` WHERE YEARWEEK( date_format( ` 字段` , '%Y-%m-%d' ) ) - YEARWEEK( CURDATE( ) ) = 0 ;
SELECT * FROM ` 表名` WHERE YEARWEEK( DATE_FORMAT( ` 字段` , '%Y-%m-%d' ) ) = YEARWEEK( DATE_FORMAT( CURDATE( ) , '%Y-%m-%d' ) ) ;
SELECT * FROM ` 表名` WHERE YEARWEEK( DATE_FORMAT( ` 字段` , '%Y-%m-%d' ) ) - YEARWEEK( DATE_FORMAT( CURDATE( ) , '%Y-%m-%d' ) ) = 0 ;
查询上周的数据
SELECT * FROM ` 表名` WHERE YEARWEEK( DATE_FORMAT( ` 字段` , '%Y-%m-%d' ) ) = YEARWEEK( DATE_FORMAT( CURDATE( ) , '%Y-%m-%d' ) ) - 1 ;
SELECT * FROM ` 表名` WHERE YEARWEEK( DATE_FORMAT( ` 字段` , '%Y-%m-%d' ) ) - YEARWEEK( DATE_FORMAT( CURDATE( ) , '%Y-%m-%d' ) ) = - 1 ;
SELECT * FROM ` 表名` WHERE YEARWEEK( DATE_FORMAT( ` 字段` , '%Y-%m-%d' ) ) = YEARWEEK( CURDATE( ) ) - 1 ;
SELECT * FROM ` 表名` WHERE YEARWEEK( DATE_FORMAT( ` 字段` , '%Y-%m-%d' ) ) - YEARWEEK( CURDATE( ) ) = - 1 ;
查询当月的数据
SELECT * FROM ` 表名` WHERE DATE_FORMAT( ` 字段` , '%Y-%m' ) = DATE_FORMAT( CURDATE( ) , '%Y-%m' ) ;
SELECT * FROM ` 表名` WHERE MONTH ( ` 字段` ) - MONTH ( NOW ( ) ) = 0 ;
SELECT * FROM ` 表名` WHERE MONTH ( ` 字段` ) = MONTH ( NOW ( ) ) ;
查询上月的数据
SELECT * FROM ` 表名` WHERE DATE_FORMAT( ` 字段` , '%Y-%m' ) = DATE_FORMAT( DATE_SUB( NOW ( ) , INTERVAL 1 MONTH ) , '%Y-%m' ) ;
查询本季度的数据
SELECT * FROM ` 表名` WHERE QUARTER( ` 字段` ) = QUARTER( NOW ( ) ) AND YEAR ( ` 字段` ) = YEAR ( NOW ( ) ) ;
查询上季度的数据
SELECT * FROM ` 表名` WHERE QUARTER( ` 字段` ) = QUARTER( DATE_SUB( NOW ( ) , INTERVAL 1 QUARTER) ) AND YEAR ( ` 字段` ) = YEAR ( NOW ( ) ) ;
查询上半年(六个月)的数据
SELECT * FROM ` 表名` WHERE ` 字段` BETWEEN DATE_SUB( NOW ( ) , INTERVAL 6 MONTH ) AND NOW ( ) ;
查询今年的数据
SELECT * FROM ` 表名` WHERE DATE_FORMAT( ` 字段` , '%Y' ) = DATE_FORMAT( NOW ( ) , '%Y' ) ;
SELECT * FROM ` 表名` WHERE DATE_FORMAT( ` 字段` , '%Y' ) - DATE_FORMAT( NOW ( ) , '%Y' ) = 0 ;
SELECT * FROM ` 表名` WHERE YEAR ( NOW ( ) ) = YEAR ( ` 字段` ) ;
SELECT * FROM ` 表名` WHERE YEAR ( NOW ( ) ) - YEAR ( ` 字段` ) = 0 ;
查询去年的数据
SELECT * FROM ` 表名` WHERE DATE_FORMAT( ` 字段` , '%Y' ) - DATE_FORMAT( NOW ( ) , '%Y' ) = - 1 ;
SELECT * FROM ` 表名` WHERE YEAR ( NOW ( ) ) = YEAR ( ` 字段` ) + 1 ;
SELECT * FROM ` 表名` WHERE YEAR ( NOW ( ) ) - YEAR ( ` 字段` ) = 1 ;