Bash 腳本實現 CSV 匯入 MySQL 並按系所分組排名：三腳本協作模式

Bash 腳本實現 CSV 匯入 MySQL 並按系所分組排名：三腳本協作模式

將包含多系所學生資料的 CSV 匯入 MySQL，並產生各系所成績排名。採用三腳本分工：建表、寫入、查詢排名，展示 Bash + MySQL 處理資料流程。

資料樣本 (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

腳本一：建立資料庫與表 (create_db.sh)

#!/bin/bash
# filename: create_db.sh

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

# 建立資料庫
mysql -u "$USER" -p"$PASS" -S "$SOCKET" </dev/null
CREATE DATABASE students;
EOF
[ $? -eq 0 ] && echo "Created DB" || echo "DB already exist"

# 建立表
mysql -u "$USER" -p"$PASS" -S "$SOCKET" students </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"

腳本二：CSV 匯入資料表 (write_to_db.sh)

#!/bin/bash
# filename: write_to_db.sh

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

if [ $# -ne 1 ]; then
    echo "Usage: $0 "
    exit 2
fi

data="$1"
while IFS= read -r line; do
    # 保存原 IFS，設定逗號分隔
    oldIFS=$IFS
    IFS=','
    values=($line)
    IFS=$oldIFS
    
    # 處理名稱含空格：暫時替換空格為 #，SQL 中再還原
    values[1]="\"$(echo ${values[1]} | tr ' ' '#')\""
    values[3]="\"${values[3]}\""
    
    # 組裝 VALUES 字串
    query=$(echo ${values[@]} | tr '#' ',')
    
    # 執行插入
    mysql -u "$USER" -p"$PASS" -S "$SOCKET" students </dev/null
INSERT INTO students VALUES ($query);
EOF
    echo "Wrote data into DB"
done < "$data"

腳本三：查詢各系所排名 (read_db.sh)

#!/bin/bash
# filename: read_db.sh

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

# 獲取所有系所
depts=$(mysql -u "$USER" -p"$PASS" -S "$SOCKET" students -N -e "SELECT DISTINCT dept FROM students;")

for d in $depts; do
    echo "Department : $d"
    
    # 使用用戶變數實現排名 (MySQL 8.0 前無 ROW_NUMBER)
    result=$(mysql -u "$USER" -p"$PASS" -S "$SOCKET" students <

# chmod +x create_db.sh write_to_db.sh read_db.sh
# ./create_db.sh
Created DB
Created table students

# ./write_to_db.sh studentdata.csv
Wrote data into DB
Wrote data into DB
... (10 行)

# ./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> 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   |
+------+---------+------+------+