shell操作mysql数据库 基本命令

一、连接数据库

#!/bin/bash
HOSTNAME="10.204.96.210"
PORT="3306"
USERNAME="realmarket"
PASSWORD="3&mW#iSq"
DBNAME="realmarketdb"

#连接数据库并执行sql语句
mysql -h${HOSTNAME}  -P${PORT}  -u${USERNAME} -p${PASSWORD}<<EOF
use ${DBNAME}
SELECT * FROM test2 ;
EOF

二、插入数据

#!/bin/bash
HOSTNAME="10.204.96.210"
PORT="3306"
USERNAME="realmarket"
PASSWORD="3&mW#iSq"
DBNAME="realmarketdb"

#连接数据库并执行sql语句
mysql -h${HOSTNAME}  -P${PORT}  -u${USERNAME} -p${PASSWORD}<<EOF
use ${DBNAME}
insert into test2(Sname,Ssex,Sbirthday,SClass,Sno)  values('王小钟','男','2000-10-12','3','18136666');
EOF

三、将查询到的数据 写入一个txt文件

#!/bin/bash
HOSTNAME="10.204.96.210"
PORT="3306"
USERNAME="realmarket"
PASSWORD="3&mW#iSq"
DBNAME="realmarketdb"
SQL="select CONCAT_WS('-',Sname,'Sitech') as Sname,Ssex,Sbirthday  from test2 where Sname like '王%';"
#连接数据库并执行sql语句
mysql -h${HOSTNAME}  -P${PORT}  -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${SQL}" > wangxz_a.txt

四、读取文件 存入数据库

#!/bin/bash
STNAME="10.204.96.210"
PORT="3306"
USERNAME="realmarket"
PASSWORD="3&mW#iSq"
DBNAME="realmarketdb"
#导入的文件路径
loadDataFile="../two/wangxz_a.txt"

#导入sql语句(忽略第一行 列名)
loadCommand="load data local infile '$loadDataFile' into table test2 IGNORE 1 LINES (Sname,Ssex,Sbirthday) ;"

#连接数据库之星
mysql -h${HOSTNAME}  -P${PORT}  -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "$loadCommand"
exit 0

#lines terminated BY '\n'  拿换行分割列
#fields terminated BY ',' 拿，分割行

五、ftp上传单个文件

#!/bin/bash
PUTFILE=../connect.txt
ftp -i -v -n 10.209.198.78 <<EOF
user ngmkt Waxzz33!!
binary
#cd tianyf/wangxz/backups
#lcd ./
#prompt
put $PUTFILE ./tianyf/wangxz/backups/1.txt
by
EOF
echo "up file end . . ."