William Jiang

JavaScript,PHP,Node,Perl,LAMP Web Developer – http://williamjxj.com; https://github.com/williamjxj?tab=repositories

Tag Archives: pma

Simply sql routine by using shell script

Suppose we frequently do some sql queries, it could be pretty tied if every time we need to key-in, especially the queries are multi queries, long statements. To make it easier, we can use pma (PhpMyAdmin) gui, however, there is a better way to do so.

In Linux, we can use shell scripts to implement it. The following short scripts (put sqls in ‘here doc’) simplify tied routines:

Code: batch_sqls.sh
  1. #!/bin/bash
  2. MYSQL=”mysql -u user -ppassword -D database”
  3. if [ $# -ne 1 ]; then
  4. echo “What date of data do you want to check? like: 2010-11-16, or 2010-10-18.”
  5. exit;
  6. fi
  7. date1=$1
  8. $MYSQL <<- __EOT__
  9. select count(distinct email) as “$date1’s emails:” from table where date like ‘$date1%’;
  10. select count(distinct email) as “$date1’s emails not in @mysite:” from table where date like ‘$date1%’ and ( email !=” and email not like ‘%@mysite%’ );
  11. __EOT__

By using ‘batch_sql.sh’, we can do a lot of things like these:

  • $ batch_sqls.sh 2010-11-16
    display the results in standard output – screen
  • $ batch_sqls.sh 2010-11-16 >archieve_`date +’%Y-%m-%d’`.txt
    keep the results in today’s file.
  • $ batch_sqls.sh `date +’%Y-%m-%d’`| grep … | sed … | awk {} | tee somefile.
    further parse the results and output the extracted data to new file.
  • $ batch_sqls.sh `date +’%Y-%m-%d’`| /usr/bin/mail -s subject $mail_list
    send the results to email.
  • batch_sqls.sh `date +’%Y-%m-%d’` 1>archieve.`date +’%Y-%m-%d’`.txt 2>>&1
    put the batch_sql.sh in cron job, to let it automatically runing.

These cases are very common in Linux env, for database query, backup, logfiles processing, file operation, system admin, etc, using shell script for such things can relieve the routines.