情景:
有一个包含多个系的学生详细信息的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> 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>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24638123/viewspace-1784198/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24638123/viewspace-1784198/