Linux in Shell read and write operations MySQL tutorial and advantages and disadvantages of "recommended"

1. Connect MySQL database

The way to connect to a database in a shell is simply to specify a username, password, the name of the database to connect to, and then redirect to enter the MySQL statement as follows:

Mysql-u username-p PASSWORD DATABASENAME <<eof 2>/dev/null
show databases;
Eof

But this is not a good idea, any user using the script can see the database user's account and password, to solve this problem, you can use a MySQL database special configuration file. The MySQL database uses the $home/.my.cnf file to read special startup commands and settings. One of these settings is the default password for the MySQL session initiated by the user account. To set the default password in this file, you can add the following:

[Client]
Password = 123456

Then, don't forget to modify the permissions:

chmod my.cnf.

This allows you to access the MySQL database through scripting, as follows:

#!/bin/bash
Mysql= ' which MySQL '
$MYSQL test-u Root << EOF
show databases;
Show tables;
SELECT * FROM Employees where salary > 4000;
Eof

2. Create a database

Through the above method to connect the database, and then by redirecting input MySQL statements, the shell to read and write MySQL basically finished. As long as you write the SQL statement right, you can do it by redirecting, and here's an example:

#!/bin/bash
##############################
# @file Create_db_mysql.sh
# @brief CREATE DATABASE and tables in MySQL
# @author Mingxing LAI
# @version 0.1
# @date 2013-01-20
##############################
User= "Root"
Database= "Students"
Table= "Students"
######################
#crate Database
Mysql-u $USER << EOF 2>/dev/null
CREATE DATABASE $DATABASE
Eof
[$-eq 0] && echo "created DB" | | Echo DB already exists
######################
#create table
Mysql-u $USER $DATABASE << EOF 2>/dev/null
CREATE TABLE $TABLE (
ID int,
Name varchar (100),
Mark Int,
Dept varchar (4)
);
Eof
[$-eq 0] && echo "Created table students" | | echo "Table students already exist"
######################
#delete data
Mysql-u $USER $DATABASE << EOF 2>/dev/null
DELETE from $TABLE;
Eof

This script is relatively simple, is a few SQL statements, there is nothing to explain, the following look, how to read the CSV file, and then inserted into the MySQL database.

3. Insert CSV file

The above created a student's table, the student's number, name, grade, and so on, assuming there is a CSV file, the contents are as follows:

$cat data
1,navin M,98,cs
2,kavya N,70,cs
3,nawaz O,80,cs
4,hari S,80,ec
5,alex M,50,ec
6,neenu J,70,ec
7,bob A,30,ec
8,anu M,90,ae
9,sruthi,89,ae
10,andrew,89,ae

In order to insert a CSV file into the database, we need to read it one line at a time, then enclose the string in double quotes, and finally generate the following statement:

INSERT INTO students VALUES (1, "Navin M", "the CS");

The best tool to parse a CSV file is awk, specifying the separator for the field as a comma-f,,awk automatically splits the fields out, and then prints out a double quote in a place where double quotes are required, so you can easily get the following data:
1, "Navin M", "CS" awk code is as follows:

Query= ' echo $line | Awk-f, ' {printf ('%s, '%s ',%s, '%s ', $, $, $, $)} '
Statement= ' echo ' INSERT into $TABLE VALUES ($query); " `
Echo $statement

Of course, there are other ways to do it, but there is hardly any simpler than awk, and the 2nd approach is as follows:

oldifs= $IFS
Ifs=,
Values= ($line)

values[1]= "" ' Echo ${values[1]} | Tr ' ' # ' ""
values[3]= "" ' Echo ${values[3]} ' ""

Query= ' echo ${values[@]} | Tr ' # ', '
ifs= $oldIFS

Statement= ' echo ' INSERT into $TABLE VALUES ($query); " `
echo "$statement"

First, the CSV file is parsed into an array by specifying the domain delimiter. The space is then replaced with a special symbol "#" (because in the next substitution, the array is output at once, and the array is separated by a space, so we replace the space of the delimited array with a comma, so that the space in the data is replaced with "#") , enclose the string in double quotes, then replace the space with a comma and replace "#" with a space. This method is really maddening, I did not understand the first time, especially why to replace the space with "#".

The complete procedure for inserting data is as follows:

#!/bin/bash
#
# @file Write_to_db_mysql.sh
# @brief Wirte data to database in MySQL
# @author Mingxing LAI
# @version 0.1
# @date 2013-01-20
#

User= "Root"
Database= "Students"
Table= "Students"
If [$#-ne 1]; Then
echo $ datafile
Echo
Exit 2
Fi
Data=$1
while read line;
Todo
# query= ' echo $line | Awk-f, ' {printf ('%s, '%s ',%s, '%s ', $, $, $, $)} '
oldifs= $IFS
Ifs=,
Values= ($line)
values[1]= "" ' Echo ${values[1]} | Tr ' ' # ' ""
values[3]= "" ' Echo ${values[3]} ' ""
Query= ' echo ${values[@]} | Tr ' # ', '
ifs= $oldIFS
Statement= ' echo ' INSERT into $TABLE VALUES ($query); " `
# echo $statement

Mysql-u $USER $DATABASE << EOF
INSERT into $TABLE VALUES ($query);
Eof
Done < $data
if [[$]-eq 0]]; Then
echo "wrote data into DB"
Fi

4. Reading data

Know how to connect MySQL in the shell, also know how to batch execute SQL statements in the shell, read the data, there is no difficulty.

#!/bin/bash
#
# @file Read_db_mysql.sh
# @brief read data from MySQL
# @author Mingxing LAI
# @version 0.1
# @date 2013-01-20
#

User= "Root"
Database= "Students"
Table= "Students"

#用tail Remove the head of the table
Depts= ' mysql-u $USER $DATABASE <<eof | Tail-n +2
SELECT DISTINCT Dept from $TABLE;
EOF '

For d in $depts; Todo
echo Department: $d
result= "' Mysql-u $USER $DATABASE << EOF
Set @i:=0;
SELECT @i:=@i+1 as rank, name, mark from students WHERE dept= the "$d" Order by Mark DESC;
EOF ' "

echo "$result"
Echo
Done

We can also use options in the MySQL statement to control the output format of the data

-H output to HTML
-X output is XML

As shown below:

#!/bin/bash
User= "Root"
Database= "Students"
Table= "Students"

Mysql-u $USER $DATABASE-H << EOF
SELECT * FROM $TABLE
Eof

HTML format is less readable and the output is as follows:

<table border=1><tr><th>id</th><th>name</th><th>mark</th><th >dept</th></tr><tr><td>1</td><td>navin M</TD><TD>98</TD ><TD>CS</TD></TR><TR><TD>2</TD><TD> Kavya n</td><td>70 </TD><TD>CS</TD></TR><TR><TD>3</TD><TD> Nawaz O&LT;/TD&GT;&LT;TD >80</td><td>cs</td></tr><tr><td>4</td><td>hari S</TD> <td>80</td><td>ec</td></tr><tr><td>5</td><td>alex M</ Td><td>50</td><td>ec</td></tr><tr><td>6</td><td>neenu J</td><td>70</td><td>ec</td></tr><tr><td>7</td><td >bob a</td><td>30</td><td>ec</td></tr><tr><td>8</td> <td> Anu M&LT;/TD&GT;&LT;TD&GT;90&LT;/TD&GT;&LT;TD&GT;AE&LT;/TD&GT;&LT;/TR&GT;&LT;TR&GT;&LT;TD&GT;9&LT;/TD&GT;&LT;TD >sruthi</td><td>89</td><td>ae</td></tr><tr><td>10</td ><TD>Andrew</TD><TD>89</TD><TD>AE</TD></TR></TABLE>

Readability is also understandable, because people feel that you do not need to modify it, directly in HTML to show the data can be.

ID Name Mark Dept
1 Navin M CS
2 Kavya N CS
3 Nawaz O CS
4 Hari EC
5 Alex M EC
6 Neenu J EC
7 Bob A EC
8 Anu M-AE
9 Sruthi-AE
Andrew the AE

The XML form of data display is more normal, directly to the upper-H to X, the output is as follows:

<?xml version= "1.0"?>
<resultset statement= "SELECT * from students" xmlns:xsi= "Http://www.w3.org/2001/XMLSchema-instance" >
<row>
<field name= "id" >1</field>
<field name= "name" >navin m</field>
<field name= "Mark" >98</field>
<field name= "Dept" >CS</field>
</row>
<row>
<field name= "id" >2</field>
<field name= "name" > Kavya n</field>
<field name= "Mark" >70</field>
<field name= "Dept" >CS</field>
</row>
</resultset>

Shell operation MySQL and pros and cons

In shell development, many times we need to manipulate the MySQL database (such as: query data, export data, etc.), but we can not access the MySQL command line environment, we need to simulate the environment of MySQL in the shell environment, the use of MySQL-related commands, This article summarizes several shell operation MySQL method, for everyone's reference.

Programme 1

MYSQL-UUSER-PPASSWD-E "Insert logtable values (...)"

Advantages: Simple Statement
Disadvantage: Supported SQL is relatively simple

Programme 2

Prepare an SQL script named Update.sql, for example:

CREATE TABLE ' user ' (
' ID ' varchar not NULL COMMENT ' primary key ',
' username ' varchar not NULL COMMENT ' username ',
' Password ' varchar not NULL COMMENT ' user password ',
' CreateDate ' date not NULL COMMENT ' creation time ',
' Age ' int (one) not NULL COMMENT ' ages ',
PRIMARY KEY (' id ')
) Engine=myisam DEFAULT Charset=utf8 comment= ' user Information table ';
DROP TABLE IF EXISTS ' Visit_log ';
CREATE TABLE ' Visit_log ' (
' ID ' varchar () character set UTF8 not NULL,
' Type ' int (one) not NULL,
' Content ' text character set UTF8 not NULL,
' CreateDate ' date not NULL,
PRIMARY KEY (' id ')
) Engine=myisam DEFAULT charset=latin1 comment= ' access log ';


Create a new update_mysql.sh that reads as follows:

Use CHBDB;
SOURCE Update.sql

Then execute the following command:

Cat update_mysql.sh | MySQL--user=root-ppassword

Benefits: Support for complex SQL scripts
Disadvantages:
1> requires two files: Update.sql and update_mysql.sh
2> Once there is an error in the middle, the script will not execute, for example:
If the first table already exists, the following exception is reported:
ERROR 1050 (42S01) at line 1 in file: ' Update.sql ': Table ' user ' already exists
The script then exits, and the second table is not created.

Programme 3

Create a new shell script with the following format:

#!/bin/bash
mysql-u*-h*-p* <<eof
Your SQL script.
Eof

For example:

#!/bin/bash
Mysql-uroot-ppassword <<eof
Use CHBDB;
CREATE TABLE User (
ID varchar not NULL COMMENT ' primary key ',
Username varchar not NULL COMMENT ' username ',
Password varchar not NULL COMMENT ' user password ',
CreateDate date not NULL COMMENT ' creation time ',
Age int (one) not NULL COMMENT ' ages ',
PRIMARY KEY (' id ')
) Engine=myisam DEFAULT Charset=utf8 comment= ' user Information table ';


Advantages:
1> support for complex SQL scripts
2> no additional documents required
Disadvantages:
1> table names, fields can not use single quotes, you need to modify the original SQL statement
2> Once there is an error in the middle, the script will not execute, for example:
If the first table already exists, the following exception is reported:
ERROR 1050 (42S01) at line 1 in file: ' Update.sql ': Table ' user ' already exists
The script then exits, and the second table is not created.
Programme 4
Prepare an SQL script, such as Update.sql, and then execute the following command:

Mysql-uroot-ppassword < Update.sql

Benefits: Support for complex SQL scripts
Disadvantages:
1> Once there is an error in the middle, the script will not execute, for example:
If the first table already exists, the following exception is reported:
ERROR 1050 (42S01) at line 1 in file: ' Update.sql ': Table ' user ' already exists
The script then exits, and the second table is not created.