shell脚本查询数据库
#!/bin/bash
HOSTNAME="数据库IP"
PORT="端口"
USERNAME="用户"
PASSWORD="密码"
DBNAME="库名"
select_sql="SELECT  
       DATE_FORMAT(t.orderdate,\'%Y-%m-%d\') as \'日期\', 
       t.store_name as \'店铺名称\', 
       t.store_id, 
       (case when t.channel=\'1\' then \'线下\' else \'线上\' end) as \'渠道\', 
       t.type_name as \'商品品类\', 
       t.product_name as \'商品\', 
       t.brand as \'品牌\',
       count(t.order_id) as \'订单数\', 
       sum(t.units * t.price) as \'订单总金额\'
FROM mk_mrp_order t 
where DATE_FORMAT(t.orderdate,\'%Y-%m-%d\')=date_format(DATE_SUB(CURDATE(),INTERVAL 1 day),\'%Y-%m-%d\')
group by  
         DATE_FORMAT(t.orderdate,\'%Y-%m-%d\'),
         t.store_id,
         t.store_name,
         t.channel,
         t.type_id,
         t.type_name,
         t.product_id,
         t.product_name,
         t.brand
order by DATE_FORMAT(t.orderdate,\'%Y-%m-%d\') desc;"  
mysql -h${HOSTNAME} -p${PASSWORD} -u${USERNAME} -P${PORT} -D ${DBNAME} -e "${select_sql}"

#!/bin/sh
date=`date +%F`
source  /root/scripts/prd09.sh > /root/scripts/test-$date.xls   #调用上面的数据库脚本，将查询出的数据重定向到xls表
iconv -f UTF-8 -t GBK /root/scripts/test-$date.xls -o /root/scripts/$date.xls   #将上面的xls表转成GBK编码
echo -e "Dear all:\n    附件为$date营销数据,请查阅" | mailx -s "$date营销数据" -a /root/scripts/$date.xls  zouhong@biiby.com liutao@biiby.com  # -e "Dear all:\n    附件为$date营销数据,请查阅"  此段为:邮件内容 -e和-n为换行

mailx -s "$date营销数据"   #邮件标题

-a /root/scripts/$date.xls  #附件为转换编码后的xls表

zouhong@biiby.com liutao@biiby.com   #发送给外部需要查看数据的邮箱    此处需要将邮箱域名添加到白名单，可以先发送到qq邮箱或者腾讯企业邮箱，邮箱首页有自助查询，可看到拦截的邮件，直接添加到白名单即可
50 08 * * * runuser -c /root/scripts/2.sh system >/dev/null 2>&1    #每天早上8点50发送邮件   runuser -c 定时执行的脚本  system为指定发送邮件的账户为system

#!/bin/sh
date=`date +%F`
echo -e "Dear All:\n    以下为协议监控前10条数据,请查阅.\n        `cat /home/zabbix/datadir/协议监控.${date}.txt`\n\n\n监控SQL语句如下:\nSELECT id FROM protocol.t_protocol WHERE STATUS IN(0,3) AND retry_times = 5 limit 10;"| mailx -s "[异常]协议监控"  liutao@zhuifintech.com