我的第一次shell之旅

为了统计信息，写了此脚本。首先介绍一下三张mysql表的定义：

/**
ups建表数据初始化及数据样例
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `tbl_ipinfo`
-- ----------------------------
DROP TABLE IF EXISTS `ipinfo`;
CREATE TABLE `ipinfo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `country` varchar(150) DEFAULT NULL COMMENT 'ip所在的国家',
  `city` varchar(150) DEFAULT NULL COMMENT 'ip所在的城市',
  `ipAddress` varchar(50) DEFAULT NULL COMMENT 'ip地址',
  `ipv4Flag` int(11) DEFAULT NULL COMMENT '是否是ipv4地址，1-是 0-否',
  `resolved` int(11) DEFAULT NULL COMMENT '是否解析过 1-为解析 2-已解析',
  PRIMARY KEY (`id`),
  KEY `ipinfo_index` (`ipAddress`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;



-- ----------------------------
-- Table structure for `tbl_playerbaseinfo`
-- ----------------------------
DROP TABLE IF EXISTS `playerbaseinfo`;
CREATE TABLE `playerbaseinfo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deviceType` varchar(50) DEFAULT NULL COMMENT '设备型号',
  `mode` int(11) DEFAULT NULL COMMENT '上报的模式 0-手动 1-自动',
  `accountId` varchar(50) NOT NULL COMMENT '玩家的账号id',
  `osVersion` varchar(20) DEFAULT NULL COMMENT '客户端操作系统版本号',
  `isp` varchar(250) DEFAULT NULL COMMENT '网络提供商',
  `packageName` varchar(80) DEFAULT NULL COMMENT '客户端包名',
  `netWorkState` int(11) DEFAULT NULL COMMENT '客户端使用的网络类型',
  `totalHops` int(11) DEFAULT NULL COMMENT '路由总的条数',
  `version` varchar(50) DEFAULT NULL COMMENT '客户端版本号',
  `reportTime` varchar(20) NOT NULL COMMENT '上报的时间',
  PRIMARY KEY (`id`),
  KEY `playerbaseinfo_index` (`accountId`,`reportTime`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
alter table playerbaseinfo add column avgPacketLoss double(12,3);
-- ----------------------------
-- Records of tbl_playerbaseinfo
-- ----------------------------


-- ----------------------------
-- Table structure for `tbl_playerrouteinfo`
-- ----------------------------
DROP TABLE IF EXISTS `playerrouteinfo`;
CREATE TABLE `playerrouteinfo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `varianceDelay` double(12,3) DEFAULT NULL COMMENT 'ip所在的城市',
  `maxDelay` double(12,3) DEFAULT NULL COMMENT '最大延迟',
  `userStamp` varchar(50) DEFAULT NULL COMMENT '用户id和当前时间组成的',
  `ipAddr` varchar(50) DEFAULT NULL COMMENT 'ip地址',
  `packetLoss` double(12,3) DEFAULT NULL COMMENT '丢包率',
  `hopNum` int(11) DEFAULT NULL COMMENT '第几条',
  `avgDelay` double(12,3) DEFAULT NULL COMMENT '平均延迟',
  `minDelay` double(12,3) DEFAULT NULL COMMENT '最小延迟',
  `probes` int(11) DEFAULT NULL COMMENT '探测次数',
  `detailDelay0` double(12,3) DEFAULT NULL COMMENT '第1次探测延迟',
  `detailDelay1` double(12,3) DEFAULT NULL COMMENT '第2次探测延迟',
  `detailDelay2` double(12,3) DEFAULT NULL COMMENT '第3次探测延迟',
  `detailDelay3` double(12,3) DEFAULT NULL COMMENT '第4次探测延迟',
  `detailDelay4` double(12,3) DEFAULT NULL COMMENT '第5次探测延迟',
  PRIMARY KEY (`id`),
  KEY `playerrouteinfo_index` (`userStamp`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tbl_playerrouteinfo
-- ----------------------------

shell脚本如下：

#!/bin/bash

HOST="127.0.0.1"
PORT="3306"
USERNAME="upsadmin"
PASSWORD="ggDjIbegt0OTPF3f"
DATABASE="ups"
SQL="select count(1) ,1 as mode from playerbaseinfo where mode=1;"
SQL1="select sum(avgLoss) as totalLoss ,count(1) as sum ,'500' as value   from (select  max(r.avgDelay) delay, r.hopNum,r.userStamp,b.accountId , b.avgPacketLoss avgLoss from playerbaseinfo as b left join playerrouteinfo r  on (r.userStamp = CONCAT(b.accountId,'_',b.reportTime)) where b.mode = 1 GROUP BY r.userStamp) B where B.delay>500  order by avgLoss"
SQL11="select (r.totalLoss/r.sum) as avgLoss,r.value  from (${SQL1}) r;"
#echo ${SQL11}
SQL2="select sum(avgLoss)as totalLoss,count(1) as sum ,'400~500' as value   from (select  max(r.avgDelay) delay, r.hopNum,r.userStamp,b.accountId , b.avgPacketLoss avgLoss from playerbaseinfo as b left join playerrouteinfo r  on (r.userStamp = CONCAT(b.accountId,'_',b.reportTime)) where b.mode = 1 GROUP BY r.userStamp) B where B.delay>400 and B.delay<500  order by avgLoss"
SQL12="select (r.totalLoss/r.sum) as avgLoss,r.value  from (${SQL2}) r;"

SQL3="select sum(avgLoss) as totalLoss,count(1) as sum ,'300~400'  as value   from (select  max(r.avgDelay) delay, r.hopNum,r.userStamp,b.accountId , b.avgPacketLoss avgLoss from playerbaseinfo as b left join playerrouteinfo r  on (r.userStamp = CONCAT(b.accountId,'_',b.reportTime)) where b.mode = 1 GROUP BY r.userStamp) B where B.delay>300 and B.delay<=400  order by avgLoss"
SQL13="select (r.totalLoss/r.sum) as avgLoss,r.value  from (${SQL3}) r;"

SQL4="select sum(avgLoss) as totalLoss,count(1) as sum ,'200~300' as value   from (select  max(r.avgDelay) delay, r.hopNum,r.userStamp,b.accountId , b.avgPacketLoss avgLoss from playerbaseinfo as b left join playerrouteinfo r  on (r.userStamp = CONCAT(b.accountId,'_',b.reportTime)) where b.mode = 1 GROUP BY r.userStamp) B where B.delay>200 and B.delay<=300  order by avgLoss"
SQL14="select (r.totalLoss/r.sum) as avgLoss,r.value  from (${SQL4}) r;"

SQL5="select sum(avgLoss)as totalLoss,count(1) as sum,'100~200' as value   from (select  max(r.avgDelay) delay, r.hopNum,r.userStamp,b.accountId , b.avgPacketLoss avgLoss from playerbaseinfo as b left join playerrouteinfo r  on (r.userStamp = CONCAT(b.accountId,'_',b.reportTime)) where b.mode = 1 GROUP BY r.userStamp) B where B.delay>100  and B.delay<=200  order by avgLoss"
SQL15="select (r.totalLoss/r.sum) as avgLoss,r.value  from (${SQL5}) r;"

SQL6="select sum(avgLoss) as totalLoss,count(1) as sum ,'0~100'  as value   from (select  max(r.avgDelay) delay, r.hopNum,r.userStamp,b.accountId , b.avgPacketLoss avgLoss from playerbaseinfo as b left join playerrouteinfo r  on (r.userStamp = CONCAT(b.accountId,'_',b.reportTime)) where b.mode = 1 GROUP BY r.userStamp) B where B.delay>0  and B.delay<=100  order by avgLoss"
SQL16="select (r.totalLoss/r.sum) as avgLoss,r.value  from (${SQL6}) r;"

mysql -h${HOST} -P${PORT} -u${USERNAME}  -p${PASSWORD} ${DATABASE} -e "${SQL}"
mysql -h${HOST} -P${PORT} -u${USERNAME}  -p${PASSWORD} ${DATABASE} -e "${SQL1};${SQL2};${SQL3};${SQL4};${SQL5};${SQL6}"

mysql -h${HOST} -P${PORT} -u${USERNAME}  -p${PASSWORD} ${DATABASE} -e "${SQL11}${SQL12}${SQL13}${SQL14}${SQL15}${SQL16}"

shell脚本确实方便，不用再linux上连上mysql不停的输入sql语句。还学会了mysql算是比较高级的查询吧。