【042】Python全栈日记-MySQL（五）

 

本次学比较重要的两个东西用户管理和触发器函数过程，工作中经常会使用的

 

 

第一部分：用户管理

一、MySQL用户的特点和注意事项

MySQL是多用户的数据库管理系统，可以通过授予或撤销权限来控制允许或不允许用户执行的操作

 

用户从职权上可分为超级用户（root）和普通用户

root用户不同于操作系统的root用户，默认对所有数据库和表具有完全访问权限

普通用户在创建数据库时具有连接数据库的权限，仅对information_schema数据库中的表的部分行具有读取权限（select）

对于实际的生产库，应尽量避免应用程序使用root用户直接操纵数据库

应根据业务需求建立普通用户并授权使其能够完成权限内的任务，防止未经授权用户访问超出其特权的数据。

 

这个我在工作中深有体会，同样是打开综合信息网，领导的侧边栏的选项卡比我们多得多，这就说明我们并没有权限使用那些选项卡，所以直接对没权限的人进行隐藏。而且当人员职位发生变更时，我们就要对他进行权限的增减。

 

 

二、MySQL用户验证的三个基本要素

MySQL 使用“用户名”、“客户端主机”和“密码” 三个基本要素来验证用户

MySQL数据库使用“用户名”和“客户端主机”来区分不同的用户，例如：root@192.168.2.3和root@localhost为不同的用户

MySQL的用户以行的形式存放在mysql数据库的user表中

 

1、查看MySQL用户

（1）select user,host,authentication_string from mysql.user;

（2）select * from mysql.user\G

*_priv 字段中的值 Y 表示已具有相应权限。root 帐户具有完全访问权限，该帐户的所有权限列的值均为 Y ,新建的普通用户liu均为N

上面两张图里都有一个host，这是代表值顶客户机进行连接：

 

（3）客户端主机（host）的含义

MySQL早期应用于超市，每个pos机具有不同的IP，客户希望能够区分来自于不同pos机的刷卡记录，不希望收银员在超市以外的地方访问MySQL数据库，因此MySQL使用host列来限制客户机连接数据库以提高安全性，只有host列中指定的IP或主机名才能使用用户名和密码连接数据库。例如：用户知道root@localhost的密码，但是用户想从远程主机（IP:192.168.2.1）来连接数据库是不会成功的。

 

意思就是：host如果是%：就是所有用户都可以对服务器进行访问。

Host如果是localhost：就是只能本地进行访问，也就是数据库所在的机器才能访问数据库。

Host如果是一个ip地址：就是指定ip可以对服务器进行访问。

 

 

允许的客户端主机名（host）格式示例：

主机名： localhost

合格的主机名： mis.offcn.com

IP 地址： 192.168.2.1

IP 网络地址加掩码： 10.1.100.0/255.255.255.0

模式或通配符： % (任意字符) 或 _ (任意1个字符)

%.offcn.com

192.168.%

192.168.2.1_

%

使用包含 % 通配符字符的host允许用户从整个域或子网中的任何主机连接数据库，存在潜在安全隐患
 

 

2、MySQL创建用户

create user 'username'@'host' identified by 'password';

username最长16个字符，如果包含特殊字符如：下划线 _ 必须使用单引号，如果没有单引号可选，password必须用单引号括起来，否则报错

示例：

create user zhang@localhost identified by ‘zhang’;

create user ma identified by ‘ma’;

create user ‘tom’@’192.168.2.1’identified by ‘tom’;

 

测试连接

本地

#>mysql -uzhang –p   --可以本地连接

#>mysql -uma –p        --可以本地连接

#>mysql -utom -p       --不可以本地连接，因为只能ip为192.168.2.1的主机连接

windows远程（测试主机IP 192.168.2.1）(mysql服务器IP 192.168.2.3)

c:\ mysql -uzhang -h192.168.2.3 -p   --不可以远程连接，因为host=localhost

c:\ mysql -uma -h192.168.2.3 -p        --可以远程连接，因为host=%

c:\ mysql -utom -h192.168.2.3 -p       --可以远程连接，因为host=192.168.2.1

 

 

 

3、MySQL设置用户密码

（1）修改自己的密码

set password=password('newpass');

password为口令函数，把口令加密存放在mysql数据库的user表的authentication_string列中

 

（2）修改其他用户密码(要有相应权限)

set password for 'username'@'host'= password('newpass');

示例：

set password for tom@192.168.2.1=password('newpass');

set password=password('root');

 

 

4、MySQL重命名用户

使用rename user命令可以给用户名和客户端主机改名

示例：把tom@192.168.2.1 改名为 tim@localhost

rename user tom@192.168.2.1 to tim@localhost;

 

 

 

5、MySQL删除用户

使用drop user语句可以删除用户

示例删除用户tim@localhost

 drop user tim@localhost;

 

 

 

三、MySQL权限

1、权限级别

全局：*.* 

数据库级：<dbname>.*   

表级：<dbname>.<tabname>

存储过程：<dbname>.<procname>

 

2、授权

grant语句可以用来创建用户也可以修改用户的权限

 

grant语法：

grant 权限(colname) on <dbname>.<tabname> to username@host identified by ‘password’;

 

示例，创建用户wang，可以select test库的stu表的sno列：

grant select(sno) on test.stu to wang@localhost identified by 'wang';

测试 (wang)

# mysql -uwang –p

 show databases;

use test;

show tables;

 select * from stu; --报错，只能看sno这列的内容

 select sno from stu;

 

3、显示用户权限

使用show grants语句查看用户拥有的权限

测试（root）

 show grants; --查看自己所拥有的权限

 show grants for wang@localhost; --查看用户wang的权限

 

 

4、撤销用户权限

revoke语句可以用来撤销用户所拥有的权限

 

revoke语法：

revoke 权限(colname) on <dbname>.<tabname> from username@host;

 

示例(root)，收回用户wang的select test库的stu表的sno列权限：

 revoke select(sno) on test.stu from wang@localhost;

 show grants for wang@localhost;

 

 

 

 

四、MySQL授权表

MySQL服务器启动以后会将下列授权表读取到内存中，作为权限控制的依据

对于全局级别权限的变更和用户密码的变更，需要用户退出会话连接重新登录后才能生效 

 

 

五、MySQL用户验证流程

 

 

六、MySQL 查看进程状态

MySQL数据库用show processlist查看服务器进程个用户连接线程的状态

SHOW PROCESSLIST 将生成以下列：

Id:用户连接标识符

User:发出语句的 MySQL 用户

Host:发出语句的客户机的主机名

db:用户选择的数据库，未选为 NULL

Command: 线程正在执行的命令类型

Time:线程处于当前状态的时间（秒）

State:指示线程正在执行的内容的操作、事件或状态

Info:线程正在执行的语句；否则为 NULL

 

 

查看当前用户连接标识符(id)

 select connection_id();

可以用kill命令杀掉用户连接

 kill 3; --当前连接被杀掉会自动重连 

 show processlist;

授予普通用户wang查看当前所有连接用户线程的状态（普通用户仅能看到自己的连接线程状态）

 grant process on *.* to wang@localhost;

 show grants for wang@localhost;

 revoke process on *.* from wang@localhost;

 

 

 

 

第二部分：存储过程_触发器_python连接MySQL

 

一、MySQL存储过程

1、定义

存储过程是(Stored Procedure)一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数来调用执行它（call调用）

 

2、存储过程优点：

（1）增强了sql语言的功能和灵活性

（2）存储过程在第一次编译后再次调用就不需要再编译，比SQL语句执行效率要高（数据库执行动作时都是先编译后执行）

（3）直接调用存储过程可执行已经定义好的SQL语句

（4）存储过程可以避免开发人员重复编写相同的SQL语句

（5）存储过程是在MySQL服务器中存储和执行，可以减少客户端和服务器的数据传输

 

3、语法：

\d是用来修改结束符的，我们语句碰到“；”后就会结束，可是我们不能让存储过程执行到一般的时候结束，所以需要先改变结束符\d $：这样就变成遇到$结束，但是当程序结束我们还需要再改回来，除非你用$很习惯，不然还是再改为；结束，所以最后写上：\d ;

 

（1）调用存储过程

call proc_name()

存储过程属于特定的函数（不返回参数值），调用存储过程就是给过程传参的过程

 

例1：输入班号，班名，老师3个参数，在class表中插入1条记录

然后使用call insert_class(5,’5班’,’老刘’)

 

（2）查看当前有哪些存储过程：show procedure status  [like  ‘proc_name’]

 

（3）显示某一个过程的创建的详细信息：show create procedure proc_name

 

 

二、MySQL自定义函数

1、定义：实现某个特定功能的模块（代码块），函数可以接受参数、执行操作（例如复杂计算）并将操作结果以值的形式返回

存储过程属于特定的函数（不返回参数值）

 

（1）系统内置函数

系统函数分为数值函数、字符串函数、日期时间函数

 

（2）自定义函数

例1：输入生日返回年龄：

例2：输入性别（男|女），体重，身高返回 体态描述

 

 

三、触发器

触发器是满足触发条件执行的一段代码。触发器作用在表上。

触发器自动完成满足特定条件相关的操作

 

1、触发器语法：

 

2、NEW和OLD关键字

 

 

3、示例：创建用户和黑名单表，用户表：用户编号，手机号，余额；黑名单表：用户编号，手机号。如果话费余额不足10元，把用户编号和其手机加入到黑名单表中；如果用户充值后，余额超过10元，把其信息从黑名单表中移除。

用户表：

create table users (id int,phone char(11),balance decimal(9,2));

insert into users values(1,'13501351234',150.32);

insert into users values(2,'18520151234',30.32);

insert into users values(3,'13308764321',85.67);

黑名单表：

create table blacklist as select id,phone from users where 1=2;

 

4、查看触发器

show triggers;

或

select * from information_schema.triggers;

 

5、删除触发器

drop trigger trigger_name;

 

 

四、Python 连接 MySQL

这里就是试一试感受一下后端如何使用python来使用数据库，后面会详细学习的

 

1、首先：

（1）安装pymysql模块

pip install pymysql

 

（2）如果提示没有pip ，输入：

easy_install pip

# 若有权限错误，则在命令前面添加sudo

sudo easy_install pip

 

（3）如果提示版本问题，复制提示信息里的命令，更新以下就好了。

 

连接数据库前，请先确认以下事项：

 

2、注意，pymysql使用之前

你没有删除之前导入到demo’库，细说就是：

（1）您已经创建了数据库 demo（之前哪个学生表）

（2）在demo数据库中您已经创建了stu表

（3）stu表字段为 scn, sname, sex, score等。

（4）你可以使用自己设定或者直接使用root用户名及其密码

（5）在你的机子上已经安装了 Python MySQLdb 模块。

 

3、使用pymysql

使用pycharm来编辑：

结果：