用shell脚本对数据库进行基本操作(连接,插入,选择插入,更新,if,调用另一个脚本)

      全部代码如下

     1 #/bin/bash 

     2 aa=$(pa aux | grep "mysql" |grep -v "mysql")
     3 if [ -z "$aa" ] 
     4 then
     5 service mysqld start
     6 fi
     7 echo "接下来进行数据库的连接"
     8 ip="127.0.0.1"
     9 account="root"
    10 password="xusong"
    11 databasename="warehouse"
    12 table_name="warehouse1"
    13 name="ck"
    14 time="2009-08-08 08:09:09"
    15 good_name="goods3"
    16 cunqu="add"
    17 cunqushuliang=66
    18 read -p "the database of ip:" ip
    19 read -p "the database account:" account
    20 read -p "the database of password:" -s password
    21 mysql -h$ip -u$account -p$password -P3306 -e "SHOW DATABASES"
    22 read -p "please select databasename:" databasename
    23 echo "下面显示您选择数据库中的内容"
    24 mysql -h$ip -u$account -p$password -P3306 $databasename -e "SHOW TABLES FROM $databasename"
    25 echo "以上内容为第一步显示的内容"
    26 read -p "please select your table name:" table_name
    27 echo "下面显示您选择数据表的内容"
    28 mysql -h$ip -u$account -p$password -P3306 $databasename -e "SELECT * FROM $table_name"
    29 echo "以上内容为第二步显示的内容"
    30 echo "下面需要您的输入操作,格式如下: 姓名:XXX    时间:XXXX-XX-XX XX:XX:XX   物品名称:输入名称参考第二步内容   存入:输入字符add  取出:输入字符disadd  存取数量:xx"
    31 read -p "please input your name:" name
    32 read -p "please input Please enter your access time:" time
    33 read -p "please input goods name:" good_name
    34 read -p "Please enter into or out:" cunqu
    35 read -p "Please enter your access number:" cunqushuliang
    36 source /root/jiaoben1
    37 if [ "$tt" == '0' ]
    38 then
    39 echo "请输入存取货物的正确时间"
    40 echo "请重新执行该脚本"
    41 fi
    42 exit 12
    43 if [ "$cunqu" == "add" && "$tt" == '1' ]
    44    then
    45 ff="INSERT $table_name (time,person,$good_name,Accessarticlename,NAMES) VALUES ('$time','$name',$cunqushuliang,'+$cunqushuliang','$good_name')"
    46 mysql -h$ip -u$account -p$password -P3306 $databasename -e "$ff"
    47 mysql -u$account -p$password -P3306 -h$ip $databasename -e "INSERT shorttable (goods1) SELECT SUM(goods1) FROM $table_name WHERE id=((SELECT id FROM $table_name WHERE time='$time')-1) OR id=(SELECT id FROM $table_name WHERE time='$time')"
    48 mysql -u$account -p$password -P3306 -h$ip $databasename -e "UPDATE $table_name SET goods1=(SELECT goods1 FROM shorttable ORDER BY id DESC LIMIT 0,1)  WHERE time='$time')"
    49 mysql -u$account -p$password -P3306 -h$ip $databasename -e "INSERT shorttable (goods2) SELECT SUM(goods2) FROM $table_name WHERE id=((SELECT id FROM $table_name WHERE time='$time')-1) OR id=(SELECT id FROM $table_name WHERE time='$time')"
    50 mysql -u$account -p$password -P3306 -h$ip $databasename -e "UPDATE $table_name SET goods2=(SELECT goods2 FROM shorttable ORDER BY id DESC LIMIT 0,1)  WHERE time='$time')"
    51 mysql -u$account -p$password -P3306 -h$ip $databasename -e "INSERT shorttable (goods3) SELECT SUM(goods3) FROM $table_name WHERE id=((SELECT id FROM $table_name WHERE time='$time')-1) OR id=(SELECT id FROM $table_name WHERE time='$time')"
    52 mysql -u$account -p$password -P3306 -h$ip $databasename -e "UPDATE $table_name SET goods3=(SELECT goods3 FROM shorttable ORDER BY id DESC LIMIT 0,1)  WHERE time='$time')"
    53 mysql -u$account -p$password -P3306 -h$ip $databasename -e "INSERT shorttable (goods4) SELECT SUM(goods4) FROM $table_name WHERE id=((SELECT id FROM $table_name WHERE time='$time')-1) OR id=(SELECT id FROM $table_name WHERE time='$time')"
    54 mysql -u$account -p$password -P3306 -h$ip $databasename -e "UPDATE $table_name SET goods4=(SELECT goods4 FROM shorttable ORDER BY id DESC LIMIT 0,1)  WHERE time='$time')"
    55 mysql -u$account -p$password -P3306 -h$ip $databasename -e "INSERT shorttable (goods5) SELECT SUM(goods5) FROM $table_name WHERE id=((SELECT id FROM $table_name WHERE time='$time')-1) OR id=(SELECT id FROM $table_name WHERE time='$time')"
    56 mysql -u$account -p$password -P3306 -h$ip $databasename -e "UPDATE $table_name SET goods5=(SELECT goods5 FROM shorttable ORDER BY id DESC LIMIT 0,1)  WHERE time='$time')"
    57 mysql -u$account -p$password -P3306 -h$ip $databasename -e "INSERT shorttable (goods6) SELECT SUM(goods6) FROM $table_name WHERE id=((SELECT id FROM $table_name WHERE time='$time')-1) OR id=(SELECT id FROM $table_name WHERE time='$time')"
    58 mysql -u$account -p$password -P3306 -h$ip $databasename -e "UPDATE $table_name SET goods6=(SELECT goods6 FROM shorttable ORDER BY id DESC LIMIT 0,1)  WHERE time='$time')"
    59 echo "数据表的数据内容如下:"
    60 mysql -h$ip -u$account -p$password -P3306 $databasename -e "SELECT * FROM $table_name "  
    61 fi 
    62 if [ "$cunqu" == "disadd" && "$tt" == '1' ]
    63 then
    64 qq="INSERT $table_name (time,person,$good_name, Accessarticlename,NAMES) VALUES ('$time','$name',$cunqushuliang,'-$cunqushuliang','$good_name')"
    65 mysql -h$ip -u$account -p$password -P3306 $databasename -e "$qq"
    66 mysql -h$ip -u$account -p$password -P3306 $databasename -e "UPDATE $table_name SET goods1=-goods1 WHERE time='$time'"
    67 mysql -h$ip -u$account -p$password -P3306 $databasename -e "UPDATE $table_name SET goods2=-goods2 WHERE time='$time'"
    68 mysql -h$ip -u$account -p$password -P3306 $databasename -e "UPDATE $table_name SET goods3=-goods3 WHERE time='$time'"
    69 mysql -h$ip -u$account -p$password -P3306 $databasename -e "UPDATE $table_name SET goods4=-goods4 WHERE time='$time'"
    70 mysql -h$ip -u$account -p$password -P3306 $databasename -e "UPDATE $table_name SET goods5=-goods5 WHERE time='$time'"
    71 mysql -h$ip -u$account -p$password -P3306 $databasename -e "UPDATE $table_name SET goods6=-goods6 WHERE time='$time'"
    72 mysql -u$account -p$password -P3306 -h$ip $databasename -e "UPDATE $table_name SET goods1=(SELECT goods1 FROM shorttable ORDER BY id DESC LIMIT 0,1)  WHERE time='$time')"
    73 mysql -u$account -p$password -P3306 -h$ip $databasename -e "INSERT shorttable (goods2) SELECT SUM(goods2) FROM $table_name WHERE id=((SELECT id FROM $table_name WHERE time='$time')-1) OR id=(SELECT id FROM $table_name WHERE time='$time')"
    74 mysql -u$account -p$password -P3306 -h$ip $databasename -e "UPDATE $table_name SET goods2=(SELECT goods2 FROM shorttable ORDER BY id DESC LIMIT 0,1)  WHERE time='$time')"
    75 mysql -u$account -p$password -P3306 -h$ip $databasename -e "INSERT shorttable (goods3) SELECT SUM(goods3) FROM $table_name WHERE id=((SELECT id FROM $table_name WHERE time='$time')-1) OR id=(SELECT id FROM $table_name WHERE time='$time')"
    76 mysql -u$account -p$password -P3306 -h$ip $databasename -e "UPDATE $table_name SET goods3=(SELECT goods3 FROM shorttable ORDER BY id DESC LIMIT 0,1)  WHERE time='$time')"
    77 mysql -u$account -p$password -P3306 -h$ip $databasename -e "INSERT shorttable (goods4) SELECT SUM(goods4) FROM $table_name WHERE id=((SELECT id FROM $table_name WHERE time='$time')-1) OR id=(SELECT id FROM $table_name WHERE time='$time')"
    78 mysql -u$account -p$password -P3306 -h$ip $databasename -e "UPDATE $table_name SET goods4=(SELECT goods4 FROM shorttable ORDER BY id DESC LIMIT 0,1)  WHERE time='$time')"
    79 mysql -u$account -p$password -P3306 -h$ip $databasename -e "INSERT shorttable (goods5) SELECT SUM(goods5) FROM $table_name WHERE id=((SELECT id FROM $table_name WHERE time='$time')-1) OR id=(SELECT id FROM $table_name WHERE time='$time')"
    80 mysql -u$account -p$password -P3306 -h$ip $databasename -e "UPDATE $table_name SET goods5=(SELECT goods5 FROM shorttable ORDER BY id DESC LIMIT 0,1)  WHERE time='$time')"
    81 mysql -u$account -p$password -P3306 -h$ip $databasename -e "INSERT shorttable (goods6) SELECT SUM(goods6) FROM $table_name WHERE id=((SELECT id FROM $table_name WHERE time='$time')-1) OR id=(SELECT id FROM $table_name WHERE time='$time')"
    82 mysql -u$account -p$password -P3306 -h$ip $databasename -e "UPDATE $table_name SET goods6=(SELECT goods6 FROM shorttable ORDER BY id DESC LIMIT 0,1)  WHERE time='$time')"
    83 echo "数据表的数据内容如下:"
    84 mysql -h$ip -u$account -p$password -P3306 $databasename -e "SELECT * FROM $table_name "
    85 fi
    86 read -p "请确认您所更新的数据是否正确 y/n:" enter
    87 if [ "$enter" == "y" ] 
    88   then
    89    echo  "数据表已经确认正确,欢迎下次使用" 
    90   echo "223"
    91 exit 10
    92  fi
    93 if [ "$enter" == "n" ] 
    94   then
    95   echo "请重新执行该脚本并且按照正确格式填写"

    96  fi

  前期数据准备

                           创建数据表
CREATE TABLE warehouse1(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> person VARCHAR(20) NOT NULL,
    -> time DATETIME NOT NULL,
    -> goods1 SMALLINT  NOT NULL DAEFAULT 0,
    -> goods2 SMALLINT  NOT NULL DAEFAULT 0,
    -> goods3 SMALLINT  NOT NULL DAEFAULT 0,
    -> goods4 SMALLINT  NOT NULL DAEFAULT 0,
    -> goods5 SMALLINT  NOT NULL DAEFAULT 0,
    -> goods6 SMALLINT  NOT NULL DAEFAULT 0,
    -> Accessarticlename VARCHAR(20) NOT NULL,
    -> NAMES VARCHAR(20) NOT NULL
    -> );
              插入原始数据

 INSERT warehouse1 VALUES (0,'root','2018-05-05 12:23:00',100,100,100,100,100,100,'X','root')

            在Linux中安装mysql,mysql-server。

            用shell运行(用Linux的终端运行)

       注意:8~17行代码与31~35行和18,19,20,21,26行代码不能同时使用。(也就是8~17行代码与read开头的行不能同时使用,不能使用的行在行首用#注释)

阅读更多
个人分类: 个人代码
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

不良信息举报

用shell脚本对数据库进行基本操作(连接,插入,选择插入,更新,if,调用另一个脚本)

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭