shell脚本实战 pdf_Shell 脚本操作数据库实战

最新推荐文章于 2023-04-11 15:39:20 发布

weixin_39758618

最新推荐文章于 2023-04-11 15:39:20 发布

阅读量120

点赞数

文章标签： shell脚本实战 pdf

安装mariadb 数据库（默认没有密码，直接mysql即可进入数据库管理控制台）

yum install mariadb mariadb-server mariadb-libs -y
systemctl start mariadb
netstat -tnlp |grep :3306

新建数据库

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);

school.sql

导入数据

mysql school < school.sql

mysql 常用选项

mysql命令参数详解

-u    用户名
    -p    用户密码
    -h    服务器ip地址
    -D    连接的数据库
    -N    不输出列信息
    -B    使用tab键代替默认交互分隔符
    -e    执行sql语句

    其他选项
    -E    垂直输出
    -H    以HTML格式输出
    -X    以XML格式输出

-D 指定数据库 -e 不用交互模式

mysql -D school -e "select * from student;";

-N 不显示列信息，-B 去除多余信息， -D 指定操作的数据库

mysql -B -N -D school -e "select * from student;";

-E 垂直显示

mysql -E -B -N -D school -e "select * from student;";

-H 以HTML格式显示

mysql -H -B -N -D school -e "select * from student;";

-X 以xml格式显示

mysql  -X -B -N -D school -e "select * from student;" > result.xml

在 shell 脚本中操作mysql 数据库　　

operate_mysql.sh

#!/bin/bash
#
 
user="dbuser"
password="123456"
host="10.11.0.215"
db_name="$1"
 
SQL="$2"
 
mysql -h"$host" -u"$user" -p"$password" -D"$1" -B -e "$SQL"

执行 sql 语句

sh operate_mysql.sh school "select * from score"

插入数据

sh operate_mysql.sh school "insert into score values('1020','1002','100');"
sh operate_mysql.sh school "select * from score"

导出txt文本，-B去掉多余的符号可以导入到excel表格中

sh operate_mysql.sh school "select * from score" > result.txt

利用shell脚本将文本数据导入到mysql中

创建表结构和student一样结构的student1表

create table student1 like student;

data.txt

1010    jerry    1991-12-13    male
1011    mike    1991-12-13    female
1012    tracy    1991-12-13    male
1013    kobe    1991-12-13    male
1014    allen    1991-12-13    female
1015    curry    1991-12-13    male
1016    tom    1991-12-13    female

编写导入数据脚本 import_mysql.sh

#!/bin/bash
#
 
user="dbuser"
password="123456"
host="10.11.0.215"
 
mysql_conn="mysql -h"$host" -u"$user" -p"$password""
 
cat data.txt | while read id name birth sex
do
    $mysql_conn -e "INSERT INTO school.student1 values('$id','$name','$birth','$sex')"
done

执行脚本

sh import_mysql.sh

导入数据可以用load，有时候有一些特殊需求比如插入s_id大于1014的行，这个时候就需要使用 shell 语句进行过滤了

import_mysql.sh

#!/bin/bash
#
 
user="dbuser"
password="123456"
host="10.11.0.215"
 
mysql_conn="mysql -h"$host" -u"$user" -p"$password""
 
cat data.txt | while read id name birth sex
do
    # 有插入条件
    if [ $id -gt 1014 ];then
    $mysql_conn -e "INSERT INTO school.student1 values('$id','$name','$birth','$sex')"
    fi
done

执行脚本

sh import_mysql.sh
sh operate_mysql.sh school "select * from student1"

需求2：

data2.txt

2021|hao|1989-12-21|male
2022|zhang|1989-12-21|male
2023|ouyang|1989-12-21|male
2024|li|1989-12-21|female

cat import_mysql-2.sh

#!/bin/bash
#
 
user="dbuser"
password="123456"
host="10.11.0.215"
 
# IFS是系统自带的变量，分隔符 input filre saperator
IFS="|"
 
cat data2.txt | while read id name birth sex
do
    # 注意，当使用|类似这种特殊符号时，需要将mysql命令不写成命令，否则会报错
    mysql -u"$user" -p"$password" -h"$host" -e "INSERT INTO school.student2 values('$id','$name','$birth','$sex')"
done
#
 
# 使用冒号: 分隔也没有问题

执行脚本　　

sh import_mysql-2.sh
sh operate_mysql.sh school "select * from student2"

data3.txt

2025:hao:1989-12-21:male
2026:zhang:1989-12-21:male
2027:ouyang:1989-12-21:male
2028:li:1989-12-21:female

import_mysql-2.sh

#!/bin/bash
#
 
user="dbuser"
password="123456"
host="10.11.0.215"
 
#mysql_conn="mysql -h"$host" -u"$user" -p"$password""
 
# IFS是系统自带的变量，分隔符 input filre saperator
IFS=":"
 
cat data3.txt | while read id name birth sex
do
    mysql -u"$user" -p"$password" -h"$host" -e "INSERT INTO school.student2 values('$id','$name','$birth','$sex')"
done

执行脚本

sh import_mysql-2.sh
sh operate_mysql.sh school "select * from student2"

以上内容希望帮助到大家，很多PHPer在进阶的时候总会遇到一些问题和瓶颈，业务代码写多了没有方向感，不知道该从那里入手去提升，对此我整理了一些资料，包括但不限于：分布式架构、高可扩展、高性能、高并发、服务器性能调优、TP6，laravel，YII2，Redis，Swoole、Swoft、Kafka、Mysql优化、shell脚本、Docker、微服务、Nginx等多个知识点高级进阶干货需要的可以免费分享给大家，需要戳这里PHP进阶架构师>>>视频、面试文档免费获取

或者关注我每天分享技术文章

PHP架构师之路www.zhihu.com

作者：Crazymagic
来源：博客园
地址：https://www.cnblogs.com/crazymagic/p/11167073.html

weixin_39758618

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
shell脚本实战 pdf_Shell 脚本操作数据库实战

安装mariadb 数据库（默认没有密码，直接mysql即可进入数据库管理控制台）yum install mariadb mariadb-server mariadb-libs -ysystemctl start mariadbnetstat -tnlp |grep :3306新建数据库create database school default character set utf8;...
复制链接

扫一扫