在shell脚本中使用Mysql数据库

连接数据库

查找

1
which mysql

登录

1
2
3
4
#!/bin/bash
MYSQL=`which mysql`
echo $MYSQL
$MYSQL test -h 127.0.0.1 -uroot -p123456
  • 使用密码配置文件

cat ~/.my.cnf

1
2
[client]
password = 123456

shell script

1
2
3
4
#!/bin/bash
MYSQL=`which mysql`
echo $MYSQL
$MYSQL test -h 127.0.0.1 -uroot

执行SQL

  • 单条SQL
1
2
3
4
#!/bin/bash
MYSQL=`which mysql`
echo $MYSQL
$MYSQL test -h127.0.0.1 -uroot -e 'select count(*) from app'
  • 多条SQL
1
2
3
4
5
6
7
#!/bin/bash
MYSQL=`which mysql`
echo $MYSQL
$MYSQL test -h127.0.0.1 -uroot <<EOF
show tables;
select * from app;
EOF
  • Insert

./mysql_insert.sh 123 test

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
#!/bin/bash
MYSQL=`which mysql`
if [ $# -ne 2 ]
then
echo 'parameter error'
else
statement="INSERT INTO app (app_id, app_name) VALUES ('$1', '$2')"
$MYSQL test -h127.0.0.1 -u root <<EOF
$statement
EOF
if [ $? -eq 0 ]
then
echo Data successfully added
else
echo Problem adding data
fi
fi

格式化数据

将输出赋值给变量

1
2
3
4
5
6
7
#!/bin/bash
MYSQL=`which mysql`
tables=`$MYSQL test -h127.0.0.1 -uroot -Bse 'show tables'`
for t in $tables
do
echo $t
done

使用格式化标签

  • 输出为XML
1
2
3
#!/bin/bash
MYSQL=`which mysql`
$MYSQL test -h127.0.0.1 -uroot -X -e 'select app_id, app_name from app limit 2'

参考文档

坚持原创技术分享,您的支持将鼓励我继续创作!