Shell腳本中怎么執行sql語句

本篇文章為大家展示了Shell腳本中怎么執行sql語句，內容簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細介紹希望你能有所收獲。

1、將SQL語句直接嵌入到shell腳本文件中

代碼如下:

--演示環境 
[root@SZDB ~]# more /etc/issue 
CentOS release 5.9 (Final) 
Kernel \r on an \m 
 
root@localhost[(none)]> show variables like 'version'; 
+---------------+------------+ 
| Variable_name | Value      | 
+---------------+------------+ 
| version       | 5.6.12-log | 
+---------------+------------+ 
 
[root@SZDB ~]# more shell_call_sql1.sh  
#!/bin/bash 
# Define log 
TIMESTAMP=`date +%Y%m%d%H%M%S` 
LOG=call_sql_${TIMESTAMP}.log 
echo "Start execute sql statement at `date`." >>${LOG} 
 
# execute sql stat 
mysql -uroot -p123456 -e " 
tee /tmp/temp.log 
drop database if exists tempdb; 
create database tempdb; 
use tempdb 
create table if not exists tb_tmp(id smallint,val varchar(20)); 
insert into tb_tmp values (1,'jack'),(2,'robin'),(3,'mark'); 
select * from tb_tmp; 
notee 
quit" 
 
echo -e "\n">>${LOG} 
echo "below is output result.">>${LOG} 
cat /tmp/temp.log>>${LOG} 
echo "script executed successful.">>${LOG} 
exit; 
 
[root@SZDB ~]# ./shell_call_sql1.sh  
Logging to file '/tmp/temp.log' 
+------+-------+ 
| id   | val   | 
+------+-------+ 
|    1 | jack  | 
|    2 | robin | 
|    3 | mark  | 
+------+-------+ 
Outfile disabled. 

2、命令行調用單獨的SQL文件

代碼如下:

[root@SZDB ~]# more temp.sql  
tee /tmp/temp.log 
drop database if exists tempdb; 
create database tempdb; 
use tempdb 
create table if not exists tb_tmp(id smallint,val varchar(20)); 
insert into tb_tmp values (1,'jack'),(2,'robin'),(3,'mark'); 
select * from tb_tmp; 
notee 
 
[root@SZDB ~]# mysql -uroot -p123456 -e "source /root/temp.sql" 
Logging to file '/tmp/temp.log' 
+------+-------+ 
| id   | val   | 
+------+-------+ 
|    1 | jack  | 
|    2 | robin | 
|    3 | mark  | 
+------+-------+ 
Outfile disabled. 

3、使用管道符調用SQL文件

代碼如下:

[root@SZDB ~]# mysql -uroot -p123456 </root/temp.sql 
Logging to file '/tmp/temp.log' 
id      val 
1       jack 
2       robin 
3       mark 
Outfile disabled. 
 
#使用管道符調用SQL文件以及輸出日志 
[root@SZDB ~]# mysql -uroot -p123456 </root/temp.sql >/tmp/temp.log 
[root@SZDB ~]# more /tmp/temp.log 
Logging to file '/tmp/temp.log' 
id      val 
1       jack 
2       robin 
3       mark 
Outfile disabled. 

4、shell腳本中MySQL提示符下調用SQL

代碼如下:

[root@SZDB ~]# more shell_call_sql2.sh 
#!/bin/bash 
mysql -uroot -p123456 <<EOF 
source /root/temp.sql; 
select current_date(); 
delete from tempdb.tb_tmp where id=3; 
select * from tempdb.tb_tmp where id=2; 
EOF 
exit; 
[root@SZDB ~]# ./shell_call_sql2.sh 
Logging to file '/tmp/temp.log' 
id      val 
1       jack 
2       robin 
3       mark 
Outfile disabled. 
current_date() 
2014-10-14 
id      val 
2       robin 

5、shell腳本中變量輸入與輸出

代碼如下:

[root@SZDB ~]# more shell_call_sql3.sh 
#!/bin/bash 
cmd="select count(*) from tempdb.tb_tmp" 
cnt=$(mysql -uroot -p123456 -s -e "${cmd}") 
echo "Current count is : ${cnt}" 
exit  
[root@SZDB ~]# ./shell_call_sql3.sh  
Warning: Using a password on the command line interface can be insecure. 
Current count is : 3 
 
[root@SZDB ~]# echo "select count(*) from tempdb.tb_tmp"|mysql -uroot -p123456 -s 
3 
 
[root@SZDB ~]# more shell_call_sql4.sh 
#!/bin/bash 
id=1 
cmd="select count(*) from tempdb.tb_tmp where id=${id}" 
cnt=$(mysql -uroot -p123456 -s -e "${cmd}") 
echo "Current count is : ${cnt}" 
exit  
 
[root@SZDB ~]# ./shell_call_sql4.sh  
Current count is : 1 
 

上述內容就是Shell腳本中怎么執行sql語句，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關注億速云行業資訊頻道。