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 . . ."