Shell scripts Connect, read, and manipulate MySQL database instances

This article describes how to read and write MySQL databases in the shell. It mainly describes how to connect the MySQL database in the shell, how to create a database in the shell, create a table, insert a CSV file, read the MySQL database, export the MySQL database as an XML or HTML file, and analyze the core statement. The method described in this article applies to PostgreSQL, which is easier to read and write PostgreSQL in the shell than MySQL.

1. Connect to MySQL Database

The method of connecting the database in the shell is simple, just specify the user name, password, the database name of the connection, and then, through redirection, enter the MySQL statement as follows:

Copy the Code code as follows:

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

But this is not a good way, any user who uses the script can see the database user's account and password, to solve this problem, you can use a special configuration file MySQL database. 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 a default password in this file, you can add the following content:

Copy the Code code as follows:

[Client]
Password = 123456
Then, don't forget to modify the permissions:
chmod. my.cnf
This allows the MySQL database to be accessed through a script, 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 to the database, and then through the redirection input MySQL statement, the shell read and write MySQL basic is finished. As long as the SQL statement is written in the right direction, by redirecting execution, here is an example:

Copy the Code code as follows:

#!/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 (+),
Mark Int,
Dept varchar (4)
);
EOF
[$?-eq 0] && echo "Created table students" | | echo "TABLE students already exist" &NBSP;
############# #########
#delete Data
mysql-u $USER $DATABASE << EOF 2>/dev/null
Delete from $TABLE;
EOF

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

3. Insert a CSV file

The above creates a student table with the student's school number, name, score, and department, assuming a CSV file with the following content:

Copy the Code code 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 the CSV file into the database, we need to read the text line by row, then enclose the string in double quotes, and the resulting statement is as follows:

Copy the Code code as follows:

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

To parse a CSV file, the best tool is awk, which automatically splits the fields by specifying the delimiter as a comma-f,,awk, and then prints out a double quotation mark where double quotes are needed to easily get the following data:

Copy the Code code as follows:

1, "Navin M", 98, "CS" awk code is as follows:
Query= ' echo $line | Awk-f, ' {printf ("%s,\"%s\ ",%s,\"%s\ "", $, $, $ $, $4)} '
Statement= ' echo ' INSERT into $TABLE VALUES ($query); " `
Echo $statement

Of course, there are other ways you can do it, but there's almost nothing simpler than awk, and the 2nd method is as follows:

Copy the Code code 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, by specifying the domain delimiter, parse the CSV file into an array, and then replace the space with a special symbol "#" (because the subsequent substitution, the array is a one-time output array, and the arrays are separated by a space of the fields, we want to separate the space of the array into a comma, so here the data in the space to replace the " , enclose the string in double quotation marks, and finally replace the space with a comma, and replace "#" with a space. This method is really crazy, I did not see the first time, especially why the space to replace the "#."

The complete procedure for inserting data is as follows:

Copy the Code code 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
  & nbsp Exit 2
Fi
data=$1
while  read line;
Do
#   query= ' echo $line | awk-f, ' {printf ("%s,\"%s\ ",%s,\"%s\ "", $, $ $, $ $, $4)} '
    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

Knowing how to connect to MySQL in the shell, and how to execute SQL statements in the shell in batches, and read the data, is no more difficult.

Copy the Code code as follows:

#!/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 table header
Depts= ' mysql-u $USER $DATABASE <<eof | Tail-n +2
SELECT DISTINCT Dept from $TABLE;
EOF '

For d in $depts; Do
echo Department: $d
result= "' Mysql-u $USER $DATABASE << EOF
Set @i:=0;
Select @i:[email protected]+1 as rank, name, mark from students WHERE dept= ' $d ' ORDER by Mark DESC;
EOF ' "

echo "$result"
Echo
Done

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

-H output to HTML
-X output to XML

As shown below:

Copy the Code code as follows:

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

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

HTML format is poor readability, the output is as follows:

Copy the Code code 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 the form of HTML display data.

Copy the Code code as follows:

id    name    mark    Dept
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

The data in the form of XML is relatively normal, directly to the above-H to-X, the output is as follows:

Copy the Code code 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>

This article is from the "Dream to Reality" blog, please be sure to keep this source http://lookingdream.blog.51cto.com/5177800/1859718

Shell scripts Connect, read, and manipulate MySQL database instances