shell脚本--脚本与MySQL数据库交互(增删改查)

student.sql 下载

 mysql 命令参数详解

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

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

 

1.安装服务

apt-get install mariadb-server

2.启用启动服务

sudo systemctl start mariadb
sudo systemctl enable mariad

3.后续设置命令

sudo mysql_secure_installation  

 root身份登录

$ sudo mysql

 创建school数据库

create database school default character set utf8;

  创建低权限的用户

grant all on school.* to chencl@'%' identified by '123456';

 创建低权限的用户

grant all on school.* to chencl@'localhost' identified by '123456';

 使用创建的低权限登录数据库

mysql -uchencl -p123456 -h localhost

 导入数据到school数据库中

$ mysql -uchencl -p123456 -h localhost school < /home/chencl/桌面/student.sql 

 登录数据库

$ mysql -uchencl -p123456 -h localhost

 显示所有的数据库

 show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| school             |
+--------------------+
2 rows in set (0.00 sec)

 切换到school库中

 use school;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

 查询数据库school中的所有的表

show tables;
+------------------+
| Tables_in_school |
+------------------+
| course           |
| score            |
| student          |
| teacher          |
+------------------+
4 rows in set (0.00 sec)

 查询 course 表的所有内容

 select * from course;
+------+---------+------+
| c_id | c_name  | t_id |
+------+---------+------+
| 1001 | chinese | 1002 |
| 1002 | math    | 1001 |
| 1003 | english | 1003 |
+------+---------+------+
3 rows in set (0.00 sec)

查询表的描述 表结构 

describe course;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| c_id   | varchar(20) | NO   | PRI | NULL    |       |
| c_name | varchar(20) | NO   |     |         |       |
| t_id   | varchar(20) | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

 

mysql的常用选项：

 -E 垂直显示 

mysql -uchencl -p123456 -h localhost -E -B -N -D school -e "select * from student;";
*************************** 1. row ***************************
1001
zhaolei
1990-1001-1001
male

-H 以HTML格式显示

 mysql -uchencl -p123456 -h localhost -H -B -N -D school -e "select * from student;" > result.html

-X 为XML文件格式

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

<?xml version="1.0"?>
<resultset statement="select * from student" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
	<field name="s_id">1001</field>
	<field name="s_name">zhaolei</field>
	<field name="s_birth">1990-1001-1001</field>
	<field name="s_sex">male</field>
  </row>
</resultset>

编写脚本处理SQL数据  opera.sh 

  sh opera.sh "select * from score" school

 sh opera.sh "insert into score values('1020','1002','100')" school

 opera.sh 内容

#!/bin/bash

user="chencl"
password="123456"
host="localhost"

SQL="$1"

DBNAME="$2"

mysql -u"$user" -p"$password" -h"$host" -D "$DBNAME" -B -e "$SQL"