用指令碼完成mysql工作

標籤：

1. 用mysql -e在指令碼中執行mysql的sql語句

#!/bin/bash#simple mysql shell usagelogtime=`date "+%Y-%m-%d"`LOG=call_sql_${logtime}.logecho "Start execute sql statement at `date`" >>${LOG}#execute sql statmysql -u root -p19930309 -e "tee /tmp/temp.loguse testdrop table if exists stu;create table  stu(name varchar(20),age int);insert into stu values(‘wangkun‘,12),(‘amei‘,12),(‘Jack‘,14);select * from stu;noteequit"echo -e "\n" >> ${LOG}echo "below is output result :" >> ${LOG}cat /tmp/temp.log >> ${LOG}echo "scrtip execute successful." >> ${LOG}exit

　　執行結果

　　

Start execute sql statement at Mon Apr 18 23:23:01 CST 2016below is output result :+---------+------+| name    | age  |+---------+------+| wangkun |   12 || amei    |   12 || Jack    |   14 |+---------+------+scrtip execute successful.

2.通過管道符號

　select2.sql

tee /home/hadoop_admin/mysql.loguse testdrop table if exists stu;create table  stu(name varchar(20),age int);insert into stu values(‘wangkun‘,12),(‘amei‘,12),(‘Jack‘,14);select * from stu;noteequit

　　

[[email protected] mysql_shell]$ mysql -u root -p19930309 < ./select2.sqlWarning: Using a password on the command line interface can be insecure.Logging to file ‘/home/hadoop_admin/mysql.log‘name    agewangkun 12amei    12Jack    14Outfile disabled.

 

3.命令列單獨調用sql檔案

　select2.sql

tee /home/hadoop_admin/mysql.loguse testdrop table if exists stu;create table  stu(name varchar(20),age int);insert into stu values(‘wangkun‘,12),(‘amei‘,12),(‘Jack‘,14);select * from stu;noteequit

 

[[email protected] mysql_shell]$ mysql -u root -p19930309 -e "source select2.sql"Warning: Using a password on the command line interface can be insecure.Logging to file ‘/home/hadoop_admin/mysql.log‘+---------+------+| name    | age  |+---------+------+| wangkun |   12 || amei    |   12 || Jack    |   14 |+---------+------+Outfile disabled.

 

4. shell指令碼中MySQL提示符下調用SQL , oracle 也可以這樣幹

　　select3.sh

　　

#!/bin/bashmysql -u root -p19930309 <<EOFtee /home/hadoop_admin/mysql.loguse testdrop table if exists stu;create table  stu(name varchar(20),age int);insert into stu values(‘wangkun‘,12),(‘amei‘,12),(‘Jack‘,14);select * from stu;noteequitEOFexit

　　執行情況

　　

[[email protected] mysql_shell]$ ./select3.shWarning: Using a password on the command line interface can be insecure.Logging to file ‘/home/hadoop_admin/mysql.log‘name    agewangkun 12amei    12Jack    14Outfile disabled.

 

用指令碼完成mysql工作