mysql 常用sql总结

1. 查询记录中created_at=0的数据 并将pay_time的值赋值到created_at，updated_at中

UPDATE `n_book_paid` k1, (SELECT id,pay_time FROM `n_book_paid` WHERE created_at=0) k2 SET k1.created_at=k2.pay_time,k1.updated_at=k2.pay_time WHERE created_at=0 AND k1.id=k2.id;

 2. 查询今天数据 created_at为时间戳

select * from m_user where to_days(from_unixtime(字段))=to_days(now()) ;#今天

where to_days(from_unixtime(字段))=to_days(now())   今天

where to_days(now()) - to_days(from_unixtime(字段))=1  昨天

where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(from_unixtime(字段))  最近7天

where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(from_unixtime(字段)) 最近30天

3. 统计近7天每天新增用户数量

SELECT FROM_UNIXTIME( created_at, "%Y-%m-%d" ) AS `time`, COUNT(uid) AS total FROM n_user WHERE to_days(from_unixtime(created_at)) > to_days(now())-7 GROUP BY FROM_UNIXTIME( created_at, "%Y-%m-%d");

4.