Linux-shell编写mysql脚本

Linux

发布日期: 2021-02-03

更新日期: 2022-06-01

文章字数: 440

阅读时长: 2 分

阅读次数:

Linux-shell编写mysql脚本

声明：以下代码中 <<表示 << 因为会导致乱码，这漏洞真实多。。

Ax 编写脚本创建数据库、表

#!/bin/bash
# 创建数据库表


USER="root"
PASS="xxx"

mysql -u $USER  -p$PASS <<EOF 2> /dev/null
create database students;
EOF

[ $? -eq 0 ] && echo Created DB || echo DB already exist
mysql -u $USER -p$PASS 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 stduents already exist

mysql -u $USER -p$PASS students <\<EOF
delete from students;
EOF

运行脚本

bash create_db.sh

创建数据库表成功

Bx 编写脚本插入数据

作为csv文件的数据，创建一个csv文件添加数据。

1,Navin M,98,CS
2,kavya M,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

编写第二个脚本，插入数据

#!/bin/bash
# 插入数据

USER="root"
PASS="xxx"

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 students <\<EOF
INSERT INTO students VALUES($query);
EOF
done< $data
echo Wrote data into DB

运行第二个脚本

$ chmod +x write_to_db.sh
$ ./write_to_db.sh data.csv

Cx 编写脚本查询数据

编写查询脚本

#! /bin/bash
# 查询

USER="root"
PASS="xxx"

depts=`mysql -u $USER -p$PASS 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 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