Last active
July 7, 2018 14:00
-
-
Save shau-lok/468590b84aa9be151a4e9aa55c86275b to your computer and use it in GitHub Desktop.
mysql-command
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- get now | |
select DATE(NOW()); | |
-- date between | |
WHERE CONVERT(start_date, DATE) => DATE(NOW()) AND CONVERT(end_date, DATE) => DATE(NOW(); | |
-- 7天前 2017-10-25 | |
select convert(NOW(), DATE) - interval 7 day; | |
-- 今天 | |
select * from 表名 where to_days(时间字段名) = to_days(now()); | |
-- 昨天 | |
SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1 | |
-- 7天 | |
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名) | |
-- 近30天 | |
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名) | |
-- 本月 | |
SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) | |
-- 上一月 | |
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1 | |
-- 爬取某城市数据的个数 | |
SELECT COUNT(id), city FROM article GROUP BY city; | |
-- 当发现查询时,查询变得很慢的话,需要清空一下查询缓存 | |
reset query cache; | |
-- 删除索引 | |
alter table article drop index article_unque; | |
-- 新建索引 | |
alter table article add UNIQUE KEY `article_unque` (`link`,`code`); | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment