【MySQL 系列】MySQL 按照当前年月周日统计数据

SELECT * FROM 表名 WHERE TO_DAYS( 表中时间字段 ) = TO_DAYS(NOW());

SELECT * FROM 表名 WHERE YEARWEEK(DATE_FORMAT( 表中时间字段，'%Y-%m-%d')) = YEARWEEK(NOW());

SELECT * FROM 表名 WHERE YEARWEEK(DATE_FORMAT( 表中时间字段，'%Y-%m')) = DATE_FORMAT(CURDATE(),'%Y-%m');

SELECT * FROM 表名 WHERE DATE_SUB(CURDATE(),INTERVAL 1 WEEK) <= DATE( 表中时间字段 );

SELECT * FROM 表名 WHERE DATE_SUB(CURDATE(),INTERVAL 1 MONTH) <= DATE( 表中时间字段 );

SELECT MONTH('表中日期字段'), COUNT(*) FROM '表名' WHERE YEAR(CURDATE()) GROUP BY MONTH('表中日期字段');

SELECT MONTH( 日期字段 ),COUNT( 1 ) FROM 表名 WHERE YEAR ( 日期字段 ) = 查询年份  GROUP BY MONTH ( 日期字段 );

SELECT *  FROM 表名  WHERE YEAR ( FROM_UNIXTIME( 日期字段 ) ) = YEAR (curdate( ));

SELECT *, QUARTER ( FROM_UNIXTIME( ` 日期字段 ` ) )  FROM 表名 ;

SELECT * FROM 表名 WHERE quarter( FROM_UNIXTIME( 日期字段 ) ) = quarter(curdate( ));

SELECT * FROM 表名 WHERE DATE_SUB( CURDATE(), INTERVAL 7 DAY ) <= date( 日期字段 );

SELECT *  FROM 表名  WHERE MONTH ( 日期字段 ) = MONTH (curdate()) AND WEEK ( 日期字段 ) = WEEK (curdate());

SELECT count(*) FROM 表名  GROUP BY date( 日期字段 );

SELECT TABLE_NAME,( data_length + index_length )/ 1024 / 1024 AS TABLE_MB,
TABLE_ROWS 
FROM
	information_schema.`TABLES` 
WHERE
	TABLE_SCHEMA = 'database_name';

SELECT
	r.trx_id waiting_trx_id,
	r.trx_mysql_thread_Id waiting_thread,
	r.trx_query waiting_query,
	b.trx_id blocking_trx_id,
	b.trx_mysql_thread_id blocking_thread,
	b.trx_query blocking_query 
FROM
	information_schema.innodb_lock_waits w
	INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
	INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

SELECT
	SUBSTRING_INDEX( HOST, ':', 1 ) AS ip,
	COUNT(*) 
FROM
	information_schema.PROCESSLIST 
GROUP BY
	ip 
ORDER BY
	COUNT(*) DESC 
	LIMIT 10;