使用bash将csv文件数据读写到MySQL数据库的脚本之一_MySQL



情景：
有一个包含多个系的学生详细信息的csv文件，需要将文件的内容插入到一个数据表中，保证每一个系生成一个单独的排名列表。

学习信息表 studentdata.csv 文件的数据如下：

 

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

 

 

脚本思路
这个问题有两种处理思路，从shell脚本的角度看，可以用sort,awk等bash工具解决，也可以用一个sql数据库的数据表也可以解决。
下面需要编写3个脚本，分别用于创建数据库及数据表、想数据表中插入学生数据、从数据表中读取并显示处理过的数据。

注意：下面脚本中mysql数据连接的user，pass，socket等变量是我测试环境中的连接，在使用脚本时可以根据情况修改；
另外对于实际导入的csv格式和行数不同，可以对照进行脚本中的数据库名、表名、列名进行修改，这样这三个脚本就可以解决这一类问题了。

脚本一、创建数据库及数据表的脚本如下：

 

#!/bin/bash
#filename : create_db.sh
#use : create mysql database and tables

USER="root"
PASS="123456"
SOCKET="/data/mysqldata/3306/mysql.sock"

mysql -u $USER -p$PASS -S $SOCKET <<eof 2=""> /dev/null
create database students;
EOF

[ $? -eq 0 ] && echo Created DB || echo DB already exist
mysql -u $USER -p$PASS -S $SOCKET students <<eof 2=""> /dev/null
create table students(
id int,
name varchar(100),
mark int,
dept varchar(4)
);
EOF

[ $? -eq 0 ]  && echo Created table students || echo Table students already exist

mysql -u $USER -p$PASS -S $SOCKET students <<eof delete="" from="" pre=""></eof></eof></eof>

<eof 2=""><eof 2=""><eof delete="" from="" pre=""></eof></eof></eof>

<eof 2=""><eof 2=""><eof delete="" from="" pre=""><p> </p><p>

<strong>脚本二、将数据插入数据表的脚本如下：</strong></p><p> </p><pre class="prebrush">#!/bin/bash
#filename : write_to_db.sh
#use : read data from csv files and insert into mysql db

USER="root"
PASS="123456"
SOCKET="/data/mysqldata/3306/mysql.sock"

if [ $# -ne 1 ];
then
    echo $0 DATAFILE
    echo
    exit 2
fi
data=$1

while read line;
do
    oldIFS=$IFS
    IFS=,
    values=($line)
    values[1]="\"`echo ${values[1]} | tr &#39; &#39; &#39;#&#39; `\""
    values[3]="\"`echo ${values[3]}`\""

    query=`echo ${values[@]} | tr &#39; #&#39; &#39;, &#39; `
    IFS=$oldIFS
                                            
    mysql -u $USER -p$PASS -S $SOCKET students <<eof data="" echo="" eof="" insert="" into="" pre="" students="" wrote=""></eof>

<eof data="" echo="" eof="" insert="" into="" pre="" students="" wrote=""></eof>

<eof data="" echo="" eof="" insert="" into="" pre="" students="" wrote=""><p> </p><p>

<strong>脚本三、查询数据库的脚本如下：</strong></p><p> </p><pre class="prebrush">#!/bin/bash
#filename: read_db.sh
#use : read data from mysql db

USER="root"
PASS="123456"
SOCKET="/data/mysqldata/3306/mysql.sock"

depts=`mysql -u $USER -p$PASS -S $SOCKET students <<eof -n="" 2="" :="" by="" d="" department="" dept="" distinct="" do="" echo="" for="" from="" in="" mark="" order="" pre="" result="`mysql -u $USER -p$PASS -S $SOCKET students <<EOF
SET @i:=0;
select @i:=@i+1 as rank,name,mark from students where dept=" select="" tail=""></eof>

<eof -n="" 2="" :="" by="" d="" department="" dept="" distinct="" do="" echo="" for="" from="" in="" mark="" order="" pre="" result="`mysql -u $USER -p$PASS -S $SOCKET students <<EOF
SET @i:=0;
select @i:=@i+1 as rank,name,mark from students where dept=" select="" tail=""></eof>

<eof -n="" 2="" :="" by="" d="" department="" dept="" distinct="" do="" echo="" for="" from="" in="" mark="" order="" pre="" result="`mysql -u $USER -p$PASS -S $SOCKET students <<EOF
SET @i:=0;
select @i:=@i+1 as rank,name,mark from students where dept=" select="" tail=""><p> </p><p><strong>脚本按照顺序执行结果如下：</strong></p><p> </p><pre class="prebrush"># chmod +x create_db.sh write_to_db.sh read_db.sh
# ./create_db.sh                                             
DB already exist
Table students already exist
# 
# ./write_to_db.sh studentdata.csv       
Wrote data into DB
# 
# ./read_db.sh                         
Department : CS
rank    name    mark
1       Navin M 98
2       Nawaz O 80
3       Kavya N 70

Department : EC
rank    name    mark
1       Hari S  80
2       Neenu J 70
3       Alex M  50
4       Bob A   30

Department : AE
rank    name    mark
1       Anu M   90
2       Sruthi  89
3       Andrew  89

<eof -n="" 2="" :="" by="" d="" department="" dept="" distinct="" do="" echo="" for="" from="" in="" mark="" order="" pre="" result="`mysql -u $USER -p$PASS -S $SOCKET students <<EOF
SET @i:=0;
select @i:=@i+1 as rank,name,mark from students where dept=" select="" tail=""></eof>

<eof -n="" 2="" :="" by="" d="" department="" dept="" distinct="" do="" echo="" for="" from="" in="" mark="" order="" pre="" result="`mysql -u $USER -p$PASS -S $SOCKET students <<EOF
SET @i:=0;
select @i:=@i+1 as rank,name,mark from students where dept=" select="" tail=""><p> </p><p>
<strong>在MySQL数据库中确认插入后结果：</strong></p><p> </p><pre class="prebrush">mysql> 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| students           |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> 
mysql> use students;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 
mysql> show tables;
+--------------------+
| Tables_in_students |
+--------------------+
| students           |
+--------------------+
1 row in set (0.00 sec)

mysql> 
mysql> select * from students;
+------+---------+------+------+
| 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   |
+------+---------+------+------+
10 rows in set (0.00 sec)

mysql>