情景:
有一个包含多个系的学生详细信息的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> 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>
转载于:https://blog.51cto.com/yumushui/1688928