shell脚本操作mysql数据库，使用mysql的-e参数可以执行各种sql的(创建，删除，增，删，改、查)等各种操作

骑猪看日落 2023-10-17 00:22 9阅读 0赞

来源:http://blog.163.com/xychenbaihu@yeah/blog/static/13222965520114116289991/

mysql -hhostname -Pport -uusername -ppassword -e 相关mysql的sql语句，不用在mysql的提示符下运行mysql，即可以在shell中操作mysql的方法。

\#!/bin/bash

HOSTNAME="192.168.111.84"  \#数据库信息  
PORT="3306"  
USERNAME="root"  
PASSWORD=""

DBNAME="test\_db\_test"  \#数据库名称  
TABLENAME="test\_table\_test"  \#数据库中表的名称

\#创建数据库  
create\_db\_sql="create database IF NOT EXISTS $\{DBNAME\}"  
mysql -h$\{HOSTNAME\} -P$\{PORT\} -u$\{USERNAME\} -p$\{PASSWORD\}  \-e "$\{create\_db\_sql\}"

\#创建表  
create\_table\_sql="create table IF NOT EXISTS $\{TABLENAME\} ( name varchar(20), id int(11) default 0 )"  
mysql -h$\{HOSTNAME\} -P$\{PORT\} -u$\{USERNAME\} -p$\{PASSWORD\} $\{DBNAME\} \-e "$\{create\_table\_sql\}"

\#插入数据  
insert\_sql="insert into $\{TABLENAME\} values('billchen',2)"  
mysql -h$\{HOSTNAME\} -P$\{PORT\} -u$\{USERNAME\} -p$\{PASSWORD\} $\{DBNAME\} \-e "$\{insert\_sql\}"

\#查询  
select\_sql="select \* from $\{TABLENAME\}"  
mysql -h$\{HOSTNAME\} -P$\{PORT\} -u$\{USERNAME\} -p$\{PASSWORD\} $\{DBNAME\} \-e "$\{select\_sql\}"

\#更新数据  
update\_sql="update $\{TABLENAME\} set id=3"  
mysql -h$\{HOSTNAME\} -P$\{PORT\} -u$\{USERNAME\} -p$\{PASSWORD\} $\{DBNAME\}  \-e "$\{update\_sql\}"  
mysql -h$\{HOSTNAME\} -P$\{PORT\} -u$\{USERNAME\} -p$\{PASSWORD\} $\{DBNAME\}  \-e "$\{select\_sql\}"

\#删除数据  
delete\_sql="delete from $\{TABLENAME\}"  
mysql -h$\{HOSTNAME\} -P$\{PORT\} -u$\{USERNAME\} -p$\{PASSWORD\} $\{DBNAME\}  \-e "$\{delete\_sql\}"  
mysql -h$\{HOSTNAME\} -P$\{PORT\} -u$\{USERNAME\} -p$\{PASSWORD\} $\{DBNAME\}  \-e "$\{select\_sql\}"