1.备份数据库，会锁表 mysqldump -uroot -pzhewen@sql book > book_20190301.sql
压缩备份 mysqldump -uroot -pzhewen@sql book 2>/dev/null |gzip > book_20190301.sql.gz
还原压缩的备份
gunzip -c book_20190301.sql.gz | mysql -uroot -proot book InnoDB不缩表导数据 mysqldump -uroot -pzhewen@sql --single-transaction book 2>/dev/null |gzip > book_20190301_02.sql.gz MyISAM
不缩表导数据
mysqldump -uroot -pzhewen@sql --skip-lock-table book 2>/dev/null |gzip > book_20190301.sql.gz 混合InnoDB和MyISAM
数据备份
mysqldump -uroot -pzhewen@sql --single-transaction --skip-lock-tables book 2>/dev/null |gzip > book_20190301_03.sql.gz
//统计今天关注和注册用户
SELECT id,username,FROM_UNIXTIME(ien_admin_user.guanzhu_time) AS '关注时间', FROM_UNIXTIME(ien_admin_user.create_time) AS '注册时间', CASE isguanzhu WHEN 1 THEN '已关注' ELSE '未关注' END AS '关注状态' FROM ien_admin_user WHERE sxid=62280 AND create_time > UNIX_TIMESTAMP('2019-01-21 00:00:00') ORDER BY create_time DESC;
//------------//
SELECT (SELECT COUNT(1) FROM ien_zhineng_log WHERE create_time > UNIX_TIMESTAMP('2019-01-22 00:00:00') AND create_time < UNIX_TIMESTAMP('2019-01-23 00:00:00') AND sxid=66770 AND zntype='shouchong') AS '星耀看看首充推送', (SELECT COUNT(1) FROM ien_zhineng_log WHERE create_time > UNIX_TIMESTAMP('2019-01-22 00:00:00') AND create_time < UNIX_TIMESTAMP('2019-01-23 00:00:00') AND sxid=66770 AND zntype='weizhifu') AS '星耀看看未支付推送', (SELECT COUNT(1) FROM ien_zhineng_log WHERE create_time > UNIX_TIMESTAMP('2019-01-22 00:00:00') AND create_time < UNIX_TIMESTAMP('2019-01-23 00:00:00') AND sxid=66770 AND zntype='weichongzhi') AS '星耀看看未充值推送', (SELECT COUNT(1) FROM ien_zhineng_log WHERE create_time > UNIX_TIMESTAMP('2019-01-22 00:00:00') AND create_time < UNIX_TIMESTAMP('2019-01-23 00:00:00') AND sxid=67238 AND zntype='shouchong') AS '橘子书咖首充推送', (SELECT COUNT(1) FROM ien_zhineng_log WHERE create_time > UNIX_TIMESTAMP('2019-01-22 00:00:00') AND create_time < UNIX_TIMESTAMP('2019-01-23 00:00:00') AND sxid=67238 AND zntype='weizhifu') AS '橘子书咖未支付推送', (SELECT COUNT(1) FROM ien_zhineng_log WHERE create_time > UNIX_TIMESTAMP('2019-01-22 00:00:00') AND create_time < UNIX_TIMESTAMP('2019-01-23 00:00:00') AND sxid=67238 AND zntype='weichongzhi') AS '橘子书咖未充值推送', (SELECT COUNT(1) FROM ien_zhineng_log WHERE create_time > UNIX_TIMESTAMP('2019-01-22 00:00:00') AND create_time < UNIX_TIMESTAMP('2019-01-23 00:00:00') AND sxid=67403 AND zntype='shouchong') AS '开心读书馆首充推送', (SELECT COUNT(1) FROM ien_zhineng_log WHERE create_time > UNIX_TIMESTAMP('2019-01-22 00:00:00') AND create_time < UNIX_TIMESTAMP('2019-01-23 00:00:00') AND sxid=67403 AND zntype='weizhifu') AS '开心读书馆未支付推送', (SELECT COUNT(1) FROM ien_zhineng_log WHERE create_time > UNIX_TIMESTAMP('2019-01-22 00:00:00') AND create_time < UNIX_TIMESTAMP('2019-01-23 00:00:00') AND sxid=67403 AND zntype='weichongzhi') AS '开心读书馆未充值推送', (SELECT COUNT(1) FROM ien_zhineng_log WHERE create_time > UNIX_TIMESTAMP('2019-01-22 00:00:00') AND create_time < UNIX_TIMESTAMP('2019-01-23 00:00:00') AND sxid=37805 AND zntype='shouchong') AS '玲珑书台首充推送', (SELECT COUNT(1) FROM ien_zhineng_log WHERE create_time > UNIX_TIMESTAMP('2019-01-22 00:00:00') AND create_time < UNIX_TIMESTAMP('2019-01-23 00:00:00') AND sxid=37805 AND zntype='weizhifu') AS '玲珑书台未支付推送', (SELECT COUNT(1) FROM ien_zhineng_log WHERE create_time > UNIX_TIMESTAMP('2019-01-22 00:00:00') AND create_time < UNIX_TIMESTAMP('2019-01-23 00:00:00') AND sxid=37805 AND zntype='weichongzhi') AS '玲珑书台 未充值推送', (SELECT COUNT(1) FROM ien_zhineng_log WHERE create_time > UNIX_TIMESTAMP('2019-01-22 00:00:00') AND create_time < UNIX_TIMESTAMP('2019-01-23 00:00:00') AND sxid=18787 AND zntype='shouchong') AS 'mantou首充推送', (SELECT COUNT(1) FROM ien_zhineng_log WHERE create_time > UNIX_TIMESTAMP('2019-01-22 00:00:00') AND create_time < UNIX_TIMESTAMP('2019-01-23 00:00:00') AND sxid=18787 AND zntype='weizhifu') AS 'mantou未支付推送', (SELECT COUNT(1) FROM ien_zhineng_log WHERE create_time > UNIX_TIMESTAMP('2019-01-22 00:00:00') AND create_time < UNIX_TIMESTAMP('2019-01-23 00:00:00') AND sxid=18787 AND zntype='weichongzhi') AS 'mantou未充值推送'
//查询书的某日充值总额和UV
SELECT * FROM ( SELECT book.id,book.title, (SELECT COUNT(DISTINCT uid) FROM ien_read_log WHERE bid=book.id AND ((create_time > UNIX_TIMESTAMP('2019-03-01 00:00:00') AND create_time < UNIX_TIMESTAMP('2019-03-02 00:00:00')) OR (update_time > UNIX_TIMESTAMP('2019-03-01 00:00:00') AND update_time < UNIX_TIMESTAMP('2019-03-02 00:00:00'))) ) AS 'user', (SELECT SUM(money) FROM ien_pay_log WHERE bookid=book.id AND STATUS=1 AND paytime > UNIX_TIMESTAMP('2019-03-01 00:00:00') AND paytime < UNIX_TIMESTAMP('2019-03-02 00:00:00') ) AS 'money' FROM ien_book AS book ) AS t WHERE t.user>0 ORDER BY t.user DESC;
//查询某本书某一天的充值用户
SELECT ien_admin_user.nickname,ien_pay_log.bookid, ien_pay_log.money,FROM_UNIXTIME(ien_pay_log.paytime) FROM ien_pay_log, ien_admin_user WHERE ien_pay_log.bookid=726 AND ien_pay_log.status=1 AND ien_pay_log.paytime > UNIX_TIMESTAMP('2019-02-28 00:00:00') AND ien_pay_log.paytime < UNIX_TIMESTAMP('2019-03-01 00:00:00') AND ien_pay_log.uid=ien_admin_user.openid;
