linux:使用shell读写MySQL数据库

linux:使用shell读写MySQL数据库

本文用一个示例，简单演示了如何使用shell脚本读写MySQL数据库。

主要工作如下：
1. 创建数据库sample
create database sample;

2. 创建表employee
drop table if exists employee;
create table employee(EMPNO char(6), FIRSTNME varchar(12), JOB char(8));

3. 从以下文件中读取内容，并按行插入表employee中
/tmp/info.txt
000010 CHRISTINE PRES
000020 MICHAEL MANAGER
000030 SALLY DESIGNER
000050 JOHN MANAGER
000060 IRVING DESIGNER
000070 EVA CLERK
000090 EILEEN SALESREP

4.查询表employee
select * from employee;

脚本内容如下：

qingsong@db2a:/tmp$ cat mysql.sh

#!/bin/bash
#filename:mysql.sh

username=root
passwd=qingsong

# 1. create db 
mysql -u $username -p$passwd <<EOF 2> /dev/null
create database sample;
EOF

[ $? -eq 0 ] && echo "create db sample succeed" || echo "DB sample already exist"

# 2. create table
mysql -u $username -p$passwd sample <<EOF 2> /dev/null
drop table if exists employee;
EOF

mysql -u $username -p$passwd sample <<EOF 2> /dev/null
create table employee(EMPNO char(6), FIRSTNME varchar(12), JOB char(8));
EOF

[ $? -eq 0 ] && echo "create table employee succeed" || echo "Create table employee succeed"

# 3. insert data
while read line
do
        empno=`echo $line | awk '{print $1}'`
        firstname=`echo $line | awk '{print $2}'`
        job=`echo $line | awk '{print $3}'`
        query=`echo "'"$empno"'","'"$firstname"'","'"$job"'"`

        mysql -u $username -p$passwd sample <<EOF 2> /dev/null
insert into employee values($query);
EOF

        [ $? -eq 0 ] && echo "Insert succeed on empno $empno" || echo "Insert failed on empno $empno"

done < /tmp/info.txt

# 4. select data

echo "Data from table employee"
mysql -u $username -p$passwd sample <<EOF 2> /dev/null
select * from employee;
EOF

运行效果：
qingsong@db2a:/tmp$ bash mysql.sh
DB sample already exist
create table employee succeed
Insert succeed on empno 000010
Insert succeed on empno 000020
Insert succeed on empno 000030
Insert succeed on empno 000050
Insert succeed on empno 000060
Insert succeed on empno 000070
Insert succeed on empno 000090
Data from table employee
EMPNO FIRSTNME JOB
000010 CHRISTINE PRES
000020 MICHAEL MANAGER
000030 SALLY DESIGNER
000050 JOHN MANAGER
000060 IRVING DESIGNER
000070 EVA CLERK
000090 EILEEN SALESREP

脚本的一些说明：

说明1
第3步， insert data中，有这么一行：
query=`echo "'"$empno"'","'"$firstname"'","'"$job"'"`
原因是employee表的三个字段都是char或者varchar型的，插入的时候两边要加上单引号

说明2
每一个EOF必须要在行首，即不能缩进