lr mysql增删改查脚本_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

1001

zhaolei

1990-1001-1001

male

编写脚本处理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"