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 :
tcp 0.0.0.0: 0.0.0.0:* LISTEN /mysqld # 默认没有密码，直接mysql即可进入数据库管理控制台 # 新建数据库
create database school default character set utf8; # 导入测试数据
--建表
--学生表 CREATE TABLE student(
s_id varchar(),
s_name varchar() not null default '',
s_birth varchar() not null default '',
s_sex varchar() not null default '',
primary key(s_id)
); --课程表
create table course(
c_id varchar(),
c_name varchar() not null default '',
t_id varchar() not null,
primary key(c_id)
); --教师表
create table teacher(
t_id varchar(),
t_name varchar() not null default '',
primary key(t_id)
); --成绩表
create table score(
s_id varchar(),
c_id varchar(),
s_score int(),
primary key(s_id,c_id)
); --插入学生测试数据
insert into student values('','zhaolei','1990-1001-1001','male');
insert into student values('','lihang','1990-12-21','male');
insert into student values('','yanwen','1990-1005-20','male');
insert into student values('','hongfei','1990-1008-1006','male');
insert into student values('','ligang','1991-12-1001','male');
insert into student values('','zhousheng','1992-1003-1001','male');
insert into student values('','wangjun','1989-1007-1001','male');
insert into student values('','zhoufei','1990-1001-20','male'); --课程表测试数据
insert into course values('','chinese','');
insert into course values('','math','');
insert into course values('','english',''); --教师表测试数据
insert into teacher values('', 'aidisheng');
insert into teacher values('', 'aiyinsitan');
insert into teacher values('', 'qiansanqiang'); --成绩表测试
insert into score values('','',);
insert into score values('','',);
insert into score values('','',); insert into score values('','',);
insert into score values('','',);
insert into score values('','',); insert into score values('','',);
insert into score values('','',);
insert into score values('','',); insert into score values('','',);
insert into score values('','',);
insert into score values('','',); insert into score values('','',);
insert into score values('','',); insert into score values('','',);
insert into score values('','',); insert into score values('','',);
insert into score values('','',); # 添加指定数据权限的用户
MariaDB [school]> grant all on school.* to dbuser@'%' identified by '';
# % 默认是不允许localhost登录的，需要单独添加localhost的权限
MariaDB [mysql]> grant all on school.* to dbuser@'localhost' identified by ''; [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
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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. -D school -e "select * from student;";
+------+-----------+----------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+-----------+----------------+-------+
| | zhaolei | -- | male |
| | lihang | -- | male |
| | yanwen | -- | male |
| | hongfei | -- | male |
| | ligang | -- | male |
| | zhousheng | -- | male |
| | wangjun | -- | male |
| | zhoufei | -- | male |
+------+-----------+----------------+-------+ # -N 不显示列信息，-B 去除多余信息， -D 指定操作的数据库
[root@localhost ~]# mysql -udbuser -p123456 -h10.11.0. -B -N -D school -e "select * from student;";
zhaolei -- male
lihang -- male
yanwen -- male
hongfei -- male
ligang -- male
zhousheng -- male
wangjun -- male
zhoufei -- male # -E 垂直显示
[root@localhost ~]# mysql -udbuser -p123456 -h10.11.0. -E -B -N -D school -e "select * from student;";
*************************** . row *************************** zhaolei
--
male
*************************** . row *************************** lihang
--
male
*************************** . row *************************** yanwen
--
male
*************************** . row *************************** hongfei
--
male
*************************** . row *************************** ligang
--
male
*************************** . row *************************** zhousheng
--
male
*************************** . row *************************** wangjun
--
male
*************************** . row *************************** zhoufei
--
male # -H 以HTML格式显示
# mysql -udbuser -p123456 -h10.11.0. -H -B -N -D school -e "select * from student;";
<TABLE BORDER=><TR><TR><TD></TD><TD>zhaolei</TD><TD>--</TD><TD>male</TD></TR><TR><TD></TD><TD>lihang</TD><TD>--</TD><TD>male</TD></TR><TR><TD></TD><TD>yanwen</TD><TD>--</TD><TD>male</TD></TR><TR><TD></TD><TD>hongfei</TD><TD>--</TD><TD>male</TD></TR><TR><TD></TD><TD>ligang</TD><TD>--</TD><TD>male</TD></TR><TR><TD></TD><TD>zhousheng</TD><TD>--</TD><TD>male</TD></TR><TR><TD></TD><TD>wangjun</TD><TD>--</TD><TD>male</TD></TR><TR><TD></TD><TD>zhoufei</TD><TD>--</TD><TD>male</TD></TR></TABLE>
# -X 以xml格式显示
[root@localhost ~]# mysql -udbuser -p123456 -h10.11.0. -H -B -N -D school -e "select * from student;" > result.html
[root@localhost ~]# mysql -udbuser -p123456 -h10.11.0. -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};" >/dev/nul;done