shell与mysql交互_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 :3306tcp0 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_scoreint(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 connectionid is 15Server 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 clearthe 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-1001male1002 lihang 1990-12-21male1003 yanwen 1990-1005-20male1004 hongfei 1990-1008-1006male1005 ligang 1991-12-1001male1006 zhousheng 1992-1003-1001male1007 wangjun 1989-1007-1001male1008 zhoufei 1990-1001-20male

#-E 垂直显示

[root@localhost~]# mysql -udbuser -p123456 -h10.11.0.215 -E -B -N -D school -e "select * from student;";*************************** 1. row ***************************

1001zhaolei1990-1001-1001male*************************** 2. row ***************************

1002lihang1990-12-21male*************************** 3. row ***************************

1003yanwen1990-1005-20male*************************** 4. row ***************************

1004hongfei1990-1008-1006male*************************** 5. row ***************************

1005ligang1991-12-1001male*************************** 6. row ***************************

1006zhousheng1992-1003-1001male*************************** 7. row ***************************

1007wangjun1989-1007-1001male*************************** 8. row ***************************

1008zhoufei1990-1001-20male

#-H 以HTML格式显示

# mysql-udbuser -p123456 -h10.11.0.215 -H -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

#-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