在shell脚本中使用Mysql数据库

连接数据库

查找

which mysql

登录

#!/bin/bash
MYSQL=`which mysql`
echo $MYSQL
$MYSQL test -h 127.0.0.1 -uroot -p123456

• 使用密码配置文件

cat ~/.my.cnf

[client]
password = 123456

shell script

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

执行SQL

• 单条SQL

#!/bin/bash
MYSQL=`which mysql`
echo $MYSQL
$MYSQL test -h127.0.0.1 -uroot -e 'select count(*) from app'

• 多条SQL

#!/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

#!/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

格式化数据

将输出赋值给变量

#!/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

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

参考文档

• 《Linux Command Line and Shell Scripting Bible》