shell操作mysql

#!/bin/bash

# 一次数据shell脚本 #
#使用方法，命令行下运行： ./yici.sh 201410 201411 201412    #需要多少个月就写多少  月份之间用空格隔开

HOSTNAME='12.34.56.78'
PORT='3306'
USER='root'
PASSWD='123456'
DBNAME='test'

tmp=0
for value in $@
do
    months[$tmp]=$value
    tmp=`expr $tmp + 1`
done
  
mysql -h${HOSTNAME} -P${PORT}  -u${USER} -p${PASSWD} ${DBNAME} -e " drop table if exists yicidata33 "
sql=" create table yicidata33 ( SELECT a.ch_calling AS number,a.int_cardtype AS type,a.ch_PinNumber AS cardno,a.ch_IDDCode AS idd,b.vc_Name AS cardname,round(c.r_Balance, 2) AS balance,c.dt_ActiveTime from ( "
len=${#months[@]}
count=0
while [ $count -lt ` expr $len - 1 ` ]
do
    sql="${sql} select  ch_Calling,int_cardtype,ch_PinNumber,ch_IDDCode,int_talkduration from ts_his_precdr_${months[$count]} where int_TalkDuration > 0 group by ch_Calling union all"
    count=`expr $count + 1`
done
sql="$sql select  ch_Calling,int_cardtype,ch_PinNumber,ch_IDDCode,int_talkduration from ts_his_precdr_${months[`expr $len - 1 `]} where int_TalkDuration > 0 group by ch_Calling"
sql="${sql} ) as a left join gs_acc_cardtype as b on a.int_cardtype = b.int_CardTypeID left join gs_acc_carduser as c on a.ch_PinNumber = c.vc_CardPin where a.int_talkduration > 0 group by a.ch_Calling order by c.dt_ActiveTime desc )"

#寻找符合条件的月份的活跃数据到yicidata表中
mysql -h${HOSTNAME} -P${PORT}  -u${USER} -p${PASSWD} ${DBNAME} -e "${sql}"
sql='delete from yicidata33 where `number` in (select `number` from number ) '
#去重，去掉在number表中已经有的数据
mysql -h${HOSTNAME} -P${PORT}  -u${USER} -p${PASSWD} ${DBNAME} -e "${sql}"
sql='delete from  yicidata33  where `number` in (select `number` from blacklist where remarks!="" ) '
#去掉黑名单
mysql -h${HOSTNAME} -P${PORT}  -u${USER} -p${PASSWD} ${DBNAME} -e "${sql}"
sql='delete from yicidata33 where `number` in (select `number` from number_inactive )'
#去掉非活跃
mysql -h${HOSTNAME} -P${PORT}  -u${USER} -p${PASSWD} ${DBNAME} -e "${sql}"
sql='delete from yicidata33 where `number` in (select vc_mobile from npn_account_bind where ti_type="2" )'
#去掉已经绑定npn的数据
mysql -h${HOSTNAME} -P${PORT}  -u${USER} -p${PASSWD} ${DBNAME} -e "${sql}"
sql='delete from yicidata33 where balance>200'
#去掉余额大于200的
mysql -h${HOSTNAME} -P${PORT}  -u${USER} -p${PASSWD} ${DBNAME} -e "${sql}"
sql='delete from yicidata33 where number in ( select number from number  where opertime >"2014-04-04" and iscalled=1 )'
#去掉近期打过的
mysql -h${HOSTNAME} -P${PORT}  -u${USER} -p${PASSWD} ${DBNAME} -e "${sql}"
sql='insert into number( number,type,cardno,idd,cardname,balance,importtime ) ( select number,type,cardno,idd,cardname,balance,now() from yicidata33 where number not in ( select number from number ))'
#把数据插入到number表
mysql -h${HOSTNAME} -P${PORT}  -u${USER} -p${PASSWD} ${DBNAME} -e "${sql}"