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

清疚 2022-08-04 12:42 129阅读 0赞

> 
> 
>   
> 
> 
> **情景：**  
> 有一个包含多个系的学生详细信息的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 students;
>     EOF
> 
>   
> 
> 
>   
> **脚本二、将数据插入数据表的脚本如下：**
> 
>     #!/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 ' ' '#' `\""
>         values[3]="\"`echo ${values[3]}`\""
>     
>         query=`echo ${values[@]} | tr ' #' ', ' `
>         IFS=$oldIFS
>                                                 
>         mysql -u $USER -p$PASS -S $SOCKET students <<EOF
>     insert into students values($query);
>     EOF
>     
>     done< $data
>     echo Wrote data into DB
> 
>   
> 
> 
>   
> 
> 
>   
> **脚本三、查询数据库的脚本如下：**
> 
>     #!/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 | tail -n +2
>     select distinct dept from students;
>     EOF`
>     
>     for d in $depts;
>     do
>     
>     echo Department : $d
>     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="$d" order by mark desc;
>     EOF`"
>     
>     echo "$result"
>     echo 
>     
>     done
> 
>   
> 
> 
>   
> 
> 
>   
> 
> 
> **脚本按照顺序执行结果如下：**  
> 
> 
>     # 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
> 
>   
> 
> 
>   
> **在MySQL数据库中确认插入后结果：**  
> 
> 
>     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>
> 
>   
> 
> 
>   
> 
> 
>   
> 
> 
>   
> 
> 
>   
>