用指令碼完成mysql工作

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

#!/bin/bash
#simple mysql shell usage

logtime=`date "+%Y-%m-%d"`
LOG=call_sql_${logtime}.log
echo "Start execute sql statement at `date`" >>${LOG}

#execute sql stat
mysql -u root -p19930309 -e "
tee /tmp/temp.log
use test
drop 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;
notee
quit
"
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 2016


below is output result :
+---------+------+
| name    | age  |
+---------+------+
| wangkun |   12 |
| amei    |   12 |
| Jack    |   14 |
+---------+------+
scrtip execute successful.

2.通過管道符號

　select2.sql

tee /home/hadoop_admin/mysql.log
use test
drop 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;
notee
quit

　　

[hadoop_admin@master mysql_shell]$ mysql -u root -p19930309 < ./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.

 

3.命令列單獨呼叫sql檔案

　select2.sql

tee /home/hadoop_admin/mysql.log
use test
drop 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;
notee
quit

 

[hadoop_admin@master 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/bash
mysql -u root -p19930309 <<EOF
tee /home/hadoop_admin/mysql.log
use test
drop 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;
notee
quit
EOF
exit

　　執行情況

　　

[hadoop_admin@master mysql_shell]$ ./select3.sh
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.