Shell processing MySQL Add, delete, change, check

#shell是如何操作mysql的?

The shell operation of MySQL is actually through the MySQL command through the parameters to execute the statement, and other programs inside the same, look at the following parameters:

-E,--execute=name execute command and quit. (Disables--force and history file.)

So we can execute the statement through MYSQL-E, just like this:

mysql-hlocalhost-p3306-uroot-p123456 $test--default-character-set=utf8-e "SELECT * from Users"

#在shell脚本中操作mysql

1. Export Data

Mysql= "mysql-h192.168.1.102-uroot-p123456--default-character-set=utf8-a-N"

#这里面有两个参数,-A,-n,-a means not to read all of the data table information, which can solve the problem of the card when the data table is many

#-n, it's simple, Don ' t write column names in results, get the data information to omit the columns name

Sql= "SELECT * from Test.user"

Result= "$ ($MYSQL-E" $sql ")"

Dump_data=./data.user.txt

> $dump _data

Echo-e "$result" > $dump _data

#这里要额外注意, Echo-e "$result" > $dump _data must be enclosed in double quotes to keep the exported data from being squeezed in one line

2. Inserting data

#先看看要导入的数据格式, three columns, respectively ID, name, age (data is casually fabricated), put into data.user.txt

#OLF_IFS = $IFS

#IFS = ","

#临时设置默认分隔符为逗号

Cat Data.user.txt | While the Read ID name age

Do

Sql= "INSERT into Test.user (ID, name, age) VALUES (${id}, ' ${name} ', ${age});

$MYSQL-E "$sql"

Done

3. Update data

#先看看更新数据的格式, replace the left column with the right column, only the left column is deleted, and the following data is put into update.user.txt

TF Twofile

West military power of Xidian University

Xi ' an Jiaotong Jiaotong University

Peking university

Cat Update.user.txt | While read SRC DST

Do

if [!-Z "${SRC}"-A!-Z "${DST}"]

Then

sql= "Update test.user set name= ' ${dst} ' where Name= ' ${src} '"

Fi

if [!-Z "${src}"-a-z "${dst}"]

Then

Sql= "Delete from Test.user where name= ' ${src} '"

Fi

$MYSQL-E "$sql"

Done

4. Dump data to SQL file

#利用mysqldump这个命令可以很轻松的导出所有数据的sql语句到指定文件

#导出 the exp under [email protected]. All data in Opes to Tt.sql

Mysqldump-h localhost-u root-p exp opes >/tt.sql

#回车之后输入密码就可以将所有sql语句输出到tt. sql

Import data to MySQL database

#设置编码, or it could be garbled.

Mysql-hlocalhost-uroot--default-character-set=gbk-p exp<./tt.sql

#回车之后输入密码, import all the data in the Tt.sql into the EXP database

Reference post: http://www.cnblogs.com/iforever/p/4459857.html

Shell processing MySQL Add, delete, change, check