William Jiang

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

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.


One response to “Simply sql routine by using shell script

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: