Shell腳本中執(zhí)行sql語(yǔ)句操作MySQL數(shù)據(jù)庫(kù)的幾個(gè)方法

實(shí)驗(yàn)如下:
[root@idb1 ~]# more /etc/issue
CentOS release 6.5 (Final)
Kernel \r on an \m


MySQL> show variables like 'version';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| version       | 5.6.27-log |
+---------------+------------+
1 row in set (0.00 sec)


1、將SQL語(yǔ)句直接嵌入到shell腳本文件中
[root@idb1 ~]# cat shell_example01.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 -umdba -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@idb1 ~]# chmod +x shell_example01.sh

[root@idb1 ~]# sh shell_example01.sh
Warning: Using a password on the command line interface can be insecure.
Logging to file '/tmp/temp.log'
+------+-------+
| id   | val   |
+------+-------+
|    1 | jack  |
|    2 | robin |
|    3 | mark  |
+------+-------+
Outfile disabled.


2、命令行調(diào)用單獨(dú)的SQL文件例子:
[root@idb1 ~]# cat 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,'duansf'),(2,'liuyb'),(3,'jack');
select * from tb_tmp;
notee
quit

[root@idb1 ~]# mysql -umdba -p -e "source /root/temp.sql"
Enter password:
Logging to file '/tmp/temp.log'
+------+--------+
| id   | val    |
+------+--------+
|    1 | duansf |
|    2 | liuyb  |
|    3 | jack   |
+------+--------+
Outfile disabled.

#使用管道符調(diào)用SQL文件以及輸出日志
[root@idb1 ~]# mysql -umdba -p </root/temp.sql
Enter password:
Logging to file '/tmp/temp.log'
id      val
1       duansf
2       liuyb
3       jack
Outfile disabled.


4、shell腳本中MySQL提示符下調(diào)用SQL命令例子:
[root@idb1 ~]# cat shell_example02.sh
#!/bin/bash
mysql -umdba -pdsf0723 <<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@idb1 ~]# sh shell_example02.sh
Warning: Using a password on the command line interface can be insecure.
Logging to file '/tmp/temp.log'
id      val
1       duansf
2       liuyb
3       jack
Outfile disabled.
current_date()
2017-03-14
id      val
2       liuyb

分享文章：Shell腳本中執(zhí)行sql語(yǔ)句操作MySQL數(shù)據(jù)庫(kù)的幾個(gè)方法
文章出自：http://muchs.cn/article16/ihgidg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供企業(yè)網(wǎng)站制作、用戶體驗(yàn)、品牌網(wǎng)站設(shè)計(jì)、電子商務(wù)、網(wǎng)站維護(hù)、移動(dòng)網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)