04.Shell脚本登录mysql并执行sql命令

#!/bin/bash

# get 7 days ago according to input date. e.g. if input date is 20180410,it will delete those records on or before 20180403
wanted_date=`date -d "$1 7 days ago" +%Y%m%d`

echo "0==}=========> CAUTION! Those records on or before $wanted_date will be removed!"
echo "0==}=========> Are you sure to continue? yes/no"
read option
if [ "$option" == "yes" ]; then
  echo You made a good choice.
  echo ----------
elif [ "$option" == "no" ];then
  echo Goodbye~
  exit 0
else
  echo PLASE INPUT yes OR no THEN TRY AGAIN!
  exit 0
fi

# to call SQL statement at MySQL prompt
mysql -h 172.33.101.123 -P 3306 -u tony -pYourPassword -D YourDbName <<EOF
select current_date();
use tony_db;
desc confirmed_order_data;
select count(*) from confirmed_order_data where paid_date<="$wanted_date";
delete from confirmed_order_data WHERE paid_date<="$wanted_date";
select count(*) from confirmed_order_data where paid_date<="$wanted_date";

EOF