shell脚本操作数据库

#!/bin/sh
HOSTNAME="172.27.68.127"
PORT="3306"
USERNAME="root"
PASSWORD="password"
EMA_DBNAME="enigma"
CNG_6_DBNAME="cng_6"
CNG_7_DBNAME="cng_7"
DBNAME="test"

#delete db
delete_db_sql="drop databse ${DBNAME}"

#create db
create_db_sql="create database if not exists ${DBNAME}"
#mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "${create_db_sql}"

#create table
create_table_sql="create table if not exists stu (name varchar(20), age int(10))"
#mysql -u${USERNAME} -p${PASSWORD} -e "${create_table_sql}"

#insert data
insert_sql="insert into stu values('zhangsan',10)"
#mysql -u${USERNAME} -p${PASSWORD} -e "${insert_sql}"

#query data
query_sql="select * from stu"
#mysql -u${USERNAME} -p${PASSWORD} -e "${query_sql}"

#update data
update_sql="update stu set name='lisi' where name='zhangsan'"
#mysql -u${USERNAME} -p${PASSWORD} -e "${update_sql}"

#delete data
delete_sql="delete from stu where name='lisi'"
#mysql -u${USERNAME} -p${PASSWORD} -e "${delete_sql}"

mysql -u${USERNAME} -p${PASSWORD} <<EOF
${create_db_sql};
use ${DBNAME};
${create_table_sql};
${insert_sql};
${query_sql};
EOF

单条sql语句执行可以如下语句：

#mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "${create_db_sql}"

或者

#mysql -u${USERNAME} -p${PASSWORD} -e "${create_db_sql}"

多条sql语句执行可以如下：

#shell会将EOF分隔符之间的所有内容都重定向给mysql命令
mysql -u${USERNAME} -p${PASSWORD} <<EOF
${create_db_sql};
use ${DBNAME};
${create_table_sql};
${insert_sql};
${query_sql};
EOF
#注意EOF必须为一行的开头，不能缩进，否则不起作用