发布于 2016-04-01 05:36:25 | 82 次阅读 | 评论: 0 | 来源: 网友投递

这里有新鲜出炉的Mysql教程,程序狗速度看过来!

Mysql关系型数据库管理系统

MySQL是一个开放源码的小型关联式数据库管理系统,开发者为瑞典MySQL AB公司。MySQL被广泛地应用在Internet上的中小型网站中。由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。


这篇文章主要介绍了MySQL页面访问统计及排名情况,涉及到mysql页面访问统计,mysql统计排名相关知识,感兴趣的朋友一起学习吧

MySQL页面访问统计及排名情况

统计访问页面数量,以分辨率进行排名


SELECT CONCAT(`height` , '*', `width`) AS `resolution` , COUNT(CONCAT(`height`, '*', `width`)) AS `total` 
FROM `wifi_status_page` 
GROUP BY CONCAT(`height`, '*', `width`) 
ORDER BY `total` DESC 
LIMIT 0 , 30 

最近7天页面访问量,每日统计数量,以日期为序


SELECT td AS showdate, COALESCE( totalcount, 0 ) AS totalcount 
FROM ( 
SELECT CURDATE( ) AS td 
UNION ALL SELECT DATE_ADD( CURDATE( ) , INTERVAL -1 DAY ) 
UNION ALL SELECT DATE_ADD( CURDATE( ) , INTERVAL -2 DAY ) 
UNION ALL SELECT DATE_ADD( CURDATE( ) , INTERVAL -3 DAY ) 
UNION ALL SELECT DATE_ADD( CURDATE( ) , INTERVAL -4 DAY ) 
UNION ALL SELECT DATE_ADD( CURDATE( ) , INTERVAL -5 DAY ) 
UNION ALL SELECT DATE_ADD( CURDATE( ) , INTERVAL -6 DAY ) 
ORDER BY td 
)a 
LEFT JOIN ( 
SELECT FROM_UNIXTIME(add_time, '%Y-%m-%d') add_data, COUNT( id ) AS totalcount 
FROM wifi_status_page 
WHERE FROM_UNIXTIME(add_time, '%Y-%m-%d') BETWEEN DATE_ADD(CURDATE(), INTERVAL -6 DAY) AND CURDATE() 
GROUP BY add_data 
)b ON a.td = b.add_data 

最近30天页面访问量,每日统计数量,以日期为序


SELECT td AS showdate, COALESCE( totalcount, 0 ) AS totalcount 
FROM ( 
SELECT CURDATE( ) AS td 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -1 DAY) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -2 DAY) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -3 DAY) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -4 DAY) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -5 DAY) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -6 DAY) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -7 DAY) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -8 DAY) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -9 DAY) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -10 DAY) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -11 DAY) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -12 DAY) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -13 DAY) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -14 DAY) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -15 DAY) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -16 DAY) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -17 DAY) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -18 DAY) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -19 DAY) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -20 DAY) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -21 DAY) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -22 DAY) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -23 DAY) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -24 DAY) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -25 DAY) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -26 DAY) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -27 DAY) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -28 DAY) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -29 DAY) 
ORDER BY td 
)a 
LEFT JOIN ( 
SELECT FROM_UNIXTIME(add_time, '%Y-%m-%d') add_data, COUNT( id ) AS totalcount 
FROM wifi_status_page 
WHERE FROM_UNIXTIME(add_time, '%Y-%m-%d') BETWEEN DATE_ADD(CURDATE(), INTERVAL -29 DAY) AND CURDATE() 
GROUP BY add_data 
)b ON a.td = b.add_data 

昨天24小时分时


SELECT td AS showhour 
FROM ( 
SELECT DATE_ADD(CURDATE(), INTERVAL -1 HOUR) as td 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -2 HOUR) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -3 HOUR) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -4 HOUR) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -5 HOUR) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -6 HOUR) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -7 HOUR) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -8 HOUR) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -9 HOUR) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -10 HOUR) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -11 HOUR) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -12 HOUR) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -13 HOUR) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -14 HOUR) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -15 HOUR) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -16 HOUR) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -17 HOUR) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -18 HOUR) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -19 HOUR) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -20 HOUR) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -21 HOUR) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -22 HOUR) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -23 HOUR) 
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -24 HOUR) 
ORDER BY td 
)a 

今天过去的11个小时


SELECT td AS showhour 
FROM ( 
SELECT NOW() AS td 
UNION ALL SELECT DATE_ADD(NOW(), INTERVAL -1 HOUR) 
UNION ALL SELECT DATE_ADD(NOW(), INTERVAL -2 HOUR) 
UNION ALL SELECT DATE_ADD(NOW(), INTERVAL -3 HOUR) 
UNION ALL SELECT DATE_ADD(NOW(), INTERVAL -4 HOUR) 
UNION ALL SELECT DATE_ADD(NOW(), INTERVAL -5 HOUR) 
UNION ALL SELECT DATE_ADD(NOW(), INTERVAL -6 HOUR) 
UNION ALL SELECT DATE_ADD(NOW(), INTERVAL -7 HOUR) 
UNION ALL SELECT DATE_ADD(NOW(), INTERVAL -8 HOUR) 
UNION ALL SELECT DATE_ADD(NOW(), INTERVAL -9 HOUR) 
UNION ALL SELECT DATE_ADD(NOW(), INTERVAL -10 HOUR) 
UNION ALL SELECT DATE_ADD(NOW(), INTERVAL -11 HOUR) 
ORDER BY td 
)a 

以上所述是小编给大家分享的MySQL页面访问统计及排名情况,希望对大家有所帮助。



相关阅读 :
MySQL页面访问统计及排名情况
在MySQL中修改密码及访问限制的设置方法详解
mysql导出中文乱码及phpmyadmin导入中文乱码的解决方法
MySQL Proxy的安装及基本命令使用教程
MySQL入门完全指南及Linux系统下基本的安装教程
MySQL的子查询及相关优化学习教程
mysql jdbc连接步骤及常见参数
mysql数据库索引损坏及修复经验分享
MySQL中slave监控的延迟情况分析
MySQL远程访问设置终极方法
MySQL存储引擎简介及MyISAM和InnoDB的区别
MySQL数据库命名规范及约定
最新网友评论  共有(0)条评论 发布评论 返回顶部

Copyright © 2007-2017 PHPERZ.COM All Rights Reserved   冀ICP备14009818号  版权声明  广告服务