Shulou(Shulou.com)06/01 Report--

You can use shell scripts to operate the mysql database, and use the-e parameter of mysql to perform a variety of sql (create, delete, add, delete, change, check) and other operations.

The sql statement of mysql-hhostname-Pport-uusername-ppassword-e related mysql can manipulate the method of mysql in shell without running mysql at the prompt of mysql.

Example:

#! / bin/bash

HOSTNAME= "192.168.111.84" # Database Information

PORT= "3306"

USERNAME= "root"

PASSWORD= ""

DBNAME= "test_db_test" # Database name

Name of the table in the TABLENAME= "test_table_test" # database

# create a database

Create_db_sql= "create database IF NOT EXISTS ${DBNAME}"

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

# create a table

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 data

Insert_sql= "insert into ${TABLENAME} values ('billchen',2)"

Mysql-h$ {HOSTNAME}-P$ {PORT}-u$ {USERNAME}-p$ {PASSWORD} ${DBNAME}-e "${insert_sql}"

# query

Select_sql= "select * from ${TABLENAME}"

Mysql-h$ {HOSTNAME}-P$ {PORT}-u$ {USERNAME}-p$ {PASSWORD} ${DBNAME}-e "${select_sql}"

# updating data

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 data

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

The role of v in mysql-e when the results generated by mysql-e are imported into the specified file:

● to display the statement itself at the same time:-v

● to increase the number of query result rows:-vv

To increase the execution time of ●:-vvv

Can be used to simplify code in some monitoring scripts

[plain] view plaincopyprint? ㈠ without v test: [mysql@even ~] $mysql-uroot-poracle-e "SELECT VERSION (); SELECT NOW ()" > / home/mysql/test.sql [mysql@even ~] $cat / home/mysql/test.sqlVERSION () 5.5.16-logNOW () 2013-05-08 18:06:35 ㈡ with a v test: [mysql@even ~] $mysql-uroot-poracle-e "SELECT VERSION () SELECT NOW () "- v > / home/mysql/test02.sql [mysql@even ~] $cat / home/mysql/test02.sql-SELECT VERSION ()-VERSION () 5.5.16-log-SELECT NOW ()-NOW () 2013-05-08 18:08:40 ㈢ with two v tests: [mysql@even ~] $mysql-uroot-poracle-e "SELECT VERSION () SELECT NOW () "- vv > / home/mysql/test03.sql [mysql@even ~] $cat / home/mysql/test03.sql-SELECT VERSION ()-VERSION () 5.5.16-log1 row in set-SELECT NOW ()-NOW () 2013-05-08 18: 14 mysql@even 051 row in setBye ㈢ with three v tests: [mysql@even ~] $mysql-uroot-poracle-e "SELECT VERSION () SELECT NOW () "- vvv > / home/mysql/test04.sql [mysql@even ~] $cat / home/mysql/test04.sql-SELECT VERSION ()-+-+ | VERSION () | +-+ | 5.5.16-log | +-+ 1 row in set (0.00 sec)-SELECT NOW ()-+-+ | NOW () | +-+ | 2013-05-08 18:14:49 | +-+ 1 row in set (0.00 sec) Bye

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.