This article describes how to read and write a MySQL database in a shell. This paper mainly introduces how to connect the MySQL database in the shell, how to create the database in the shell, create the table, insert the CSV file, read the MySQL database, export the MySQL database as XML or HTML file, and analyze the core statement. The method described in this article is suitable for PostgreSQL, and in the case of MySQL, it is simpler to read and write PostgreSQL in the shell.
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:
Copy Code code 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:
Copy Code code as follows:
[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:
Copy 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 (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:
Copy 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 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:
Copy Code code as follows:
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:
Copy Code code as follows:
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:
Copy 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, 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:
Copy 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 $ dataf ILE
echo
exit 2
Fi
data=$1
while read line;
do
#&n bsp; query= ' echo $line | Awk-f, ' {printf ("%s,\"%s\ ",%s,\"%s\ "", $ $, $ $, $)} '
oldifs= $IFS
IFS =,
values= ($line)
values[1]= "\" Echo ${values[1]} | tr ' ' # ' \ ""
&nbs p; values[3]= "\" ' Echo ${values[3]} ' \ "
query= ' echo ${values[@]} | | tr ' # ', '
&NB sp; 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.
Copy 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 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:
Copy 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 less readable and the output is as follows:
Copy 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</TD><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</TD><TD>90</TD><TD>AE</TD></TR><TR><TD>9</TD><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.
Copy Code code as follows:
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:
Copy 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>
Finish.
