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(),age int);
insert into stu values('wangkun',),('amei',),('Jack',);
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 :: CST below is output result :
+---------+------+
| name | age |
+---------+------+
| wangkun | |
| amei | |
| Jack | |
+---------+------+
scrtip execute successful.
2.通过管道符号
select2.sql
tee /home/hadoop_admin/mysql.log
use test
drop table if exists stu;
create table stu(name varchar(),age int);
insert into stu values('wangkun',),('amei',),('Jack',);
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
amei
Jack
Outfile disabled.
3.命令行单独调用sql文件
select2.sql
tee /home/hadoop_admin/mysql.log
use test
drop table if exists stu;
create table stu(name varchar(),age int);
insert into stu values('wangkun',),('amei',),('Jack',);
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 | |
| amei | |
| Jack | |
+---------+------+
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(),age int);
insert into stu values('wangkun',),('amei',),('Jack',);
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
amei
Jack
Outfile disabled.