常用sql写法记录

日常记录：

1 mysql查询近一个月数据：

SELECT * 
FROM mrbs_prepare_book 
WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(CREATE_TIME)
ORDER BY CREATE_TIME DESC

2 查看表结构和所占内存大小

select
table_name as TABLE_NAME,
table_rows AS ROW_NUM,
concat(round(DATA_LENGTH/1024/1024,2),"MB") as TABLE_DATA_SIZE,
concat(round(INDEX_LENGTH/1024/1024,2),"MB") as TABLE_INDEX_SIZE,
concat(round((DATA_LENGTH + INDEX_LENGTH)/1024/1024,2),"MB") as TABLE_TOTAL_SIZE
from information_schema.TABLES
where table_schema="xxxx"
order by (DATA_LENGTH + INDEX_LENGTH) desc;

还有很多，放的比较散落，慢慢收集起来放在这里