shell编程系列22--shell操作数据库实战之shell脚本与MySQL数据库交互(增删改查)

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