shell编程系列22--shell操作数据库实战之shell脚本与MySQL数据库交互(增删改查) Shell脚本与MySQL数据库交互(增删改查) # 环境准备：安装mariadb 数据库 [root@localhost shell]# yum install mariadb mariadb-server mariadb-libs -y [root@localhost shell]# systemctl start mariadb [root@localhost shell]# netstat -tnlp |grep :3306 tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 102146/mysqld # 默认没有密码，直接mysql即可进入数据库管理控制台 # 新建数据库 create database school default character set utf8; # 导入测试数据 --建表 --学生表 CREATE TABLE student( s_id varchar(20), s_name varchar(20) not null default '', s_birth varchar(20) not null default '', s_sex varchar(10) not null default '', primary key(s_id) ); --课程表 create table course( c_id varchar(20), c_name varchar(20) not null default '', t_id varchar(20) not null, primary key(c_id) ); --教师表 create table teacher( t_id varchar(20), t_name varchar(20) not null default '', primary key(t_id) ); --成绩表 create table score( s_id varchar(20), c_id varchar(20), s_score int(3), primary key(s_id,c_id) ); --插入学生测试数据 insert into student values('1001','zhaolei','1990-1001-1001','male'); insert into student values('1002','lihang','1990-12-21','male'); insert into student values('1003','yanwen','1990-1005-20','male'); insert into student values('1004','hongfei','1990-1008-1006','male'); insert into student values('1005','ligang','1991-12-1001','male'); insert into student values('1006','zhousheng','1992-1003-1001','male'); insert into student values('1007','wangjun','1989-1007-1001','male'); insert into student values('1008','zhoufei','1990-1001-20','male'); --课程表测试数据 insert into course values('1001','chinese','1002'); insert into course values('1002','math','1001'); insert into course values('1003','english','1003'); --教师表测试数据 insert into teacher values('1001', 'aidisheng'); insert into teacher values('1002', 'aiyinsitan'); insert into teacher values('1003', 'qiansanqiang'); --成绩表测试 insert into score values('1001','1001',80); insert into score values('1001','1002',90); insert into score values('1001','1003',99); insert into score values('1002','1001',70); insert into score values('1002','1002',60); insert into score values('1002','1003',80); insert into score values('1003','1001',80); insert into score values('1003','1002',80); insert into score values('1003','1003',80); insert into score values('1004','1001',50); insert into score values('1004','1002',30); insert into score values('1004','1003',20); insert into score values('1005','1001',76); insert into score values('1005','1002',87); insert into score values('1006','1001',31); insert into score values('1006','1002',34); insert into score values('1007','1001',58); insert into score values('1007','1002',88); # 添加指定数据权限的用户 MariaDB [school]> grant all on school.* to dbuser@'%' identified by '123456'; # % 默认是不允许localhost登录的，需要单独添加localhost的权限 MariaDB [mysql]> grant all on school.* to dbuser@'localhost' identified by '123456'; [root@localhost ~]# mysql -h 10.11.0.215 -u dbuser -p123456 Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 15 Server version: 5.5.60-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> mysql的常用选项 [root@localhost ~]# mysql -udbuser -p123456 -h10.11.0.215 -D school -e "select * from student;"; +------+-----------+----------------+-------+ | s_id | s_name | s_birth | s_sex | +------+-----------+----------------+-------+ | 1001 | zhaolei | 1990-1001-1001 | male | | 1002 | lihang | 1990-12-21 | male | | 1003 | yanwen | 1990-1005-20 | male | | 1004 | hongfei | 1990-1008-1006 | male | | 1005 | ligang | 1991-12-1001 | male | | 1006 | zhousheng | 1992-1003-1001 | male | | 1007 | wangjun | 1989-1007-1001 | male | | 1008 | zhoufei | 1990-1001-20 | male | +------+-----------+----------------+-------+ # -N 不显示列信息，-B 去除多余信息， -D 指定操作的数据库 [root@localhost ~]# mysql -udbuser -p123456 -h10.11.0.215 -B -N -D school -e "select * from student;"; 1001 zhaolei 1990-1001-1001 male 1002 lihang 1990-12-21 male 1003 yanwen 1990-1005-20 male 1004 hongfei 1990-1008-1006 male 1005 ligang 1991-12-1001 male 1006 zhousheng 1992-1003-1001 male 1007 wangjun 1989-1007-1001 male 1008 zhoufei 1990-1001-20 male # -E 垂直显示 [root@localhost ~]# mysql -udbuser -p123456 -h10.11.0.215 -E -B -N -D school -e "select * from student;"; *************************** 1. row *************************** 1001 zhaolei 1990-1001-1001 male *************************** 2. row *************************** 1002 lihang 1990-12-21 male *************************** 3. row *************************** 1003 yanwen 1990-1005-20 male *************************** 4. row *************************** 1004 hongfei 1990-1008-1006 male *************************** 5. row *************************** 1005 ligang 1991-12-1001 male *************************** 6. row *************************** 1006 zhousheng 1992-1003-1001 male *************************** 7. row *************************** 1007 wangjun 1989-1007-1001 male *************************** 8. row *************************** 1008 zhoufei 1990-1001-20 male # -H 以HTML格式显示 # mysql -udbuser -p123456 -h10.11.0.215 -H -B -N -D school -e "select * from student;"; <TABLE BORDER=1><TR><TR><TD>1001</TD><TD>zhaolei</TD><TD>1990-1001-1001</TD><TD>male</TD></TR><TR><TD>1002</TD><TD>lihang</TD><TD>1990-12-21</TD><TD>male</TD></TR><TR><TD>1003</TD><TD>yanwen</TD><TD>1990-1005-20</TD><TD>male</TD></TR><TR><TD>1004</TD><TD>hongfei</TD><TD>1990-1008-1006</TD><TD>male</TD></TR><TR><TD>1005</TD><TD>ligang</TD><TD>1991-12-1001</TD><TD>male</TD></TR><TR><TD>1006</TD><TD>zhousheng</TD><TD>1992-1003-1001</TD><TD>male</TD></TR><TR><TD>1007</TD><TD>wangjun</TD><TD>1989-1007-1001</TD><TD>male</TD></TR><TR><TD>1008</TD><TD>zhoufei</TD><TD>1990-1001-20</TD><TD>male</TD></TR></TABLE># -X 以xml格式显示 [root@localhost ~]# mysql -udbuser -p123456 -h10.11.0.215 -H -B -N -D school -e "select * from student;" > result.html [root@localhost ~]# mysql -udbuser -p123456 -h10.11.0.215 -X -B -N -D school -e "select * from student;" > result.xml
批量删除生产环境数据库表的示例：
tables="templates_201904181553 templates_201904251425 templates_201904281550 templates_201904292018 templates_201905101118 templates_201905210938 templates_201905231928 templates_201906052000 templates_201906061640 templates_201907021640 templates_201907021922 templates_201907090936 templates_201907111535 templates_201907111545 templates_201907112119 templates_201907151538 templates_20190715194257 templates_201907161805 templates_201907171414 templates_20190717192927 templates_201907190923 templates_20190723214110 templates_201907290909 templates_201907291026 templates_201907300956 templates_20190731 templates_20190805 templates_20190807" for i in $tables;do mysql -uroot -p'pass' -D cms_db -e "drop table ${i};" 2>/dev/nul;done