MySQL存储过程及用户权限管理

MySQL存储过程及函数：

创建存储过程：
delimiter //
create procedure 存储过程名称
--begin
--sql语句1
--语句2
.....
--end//
delimiter ;
其中:参数为: [IN|OUT|INOUT] parameter_name type
其中IN表示输入参数，
OUT表示输出参数，
INOUT表示既可以输入也可以输出；
param_name表示参数名称；type表示参数的类型

具体应用：创建无参数存储过程
MariaDB [hellodb]> delimiter //
MariaDB [hellodb]> create procedure dropdatabase()

-> begin
-> drop database testdb;
-> end//

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> delimiter ;

具体应用2：有参数存储过程
MariaDB [testdb]> create procedure text(in name varchar(20))

-> begin
-> insert into testdb.students set name=name;
-> end//

Query OK, 0 rows affected (0.00 sec)

MariaDB [testdb]> delimiter ;
MariaDB [testdb]> call text('jim');
Query OK, 1 row affected (0.01 sec)

2 rows in set (0.00 sec)

查看存储过程的定义：
show create procedure 存储过程名;

调用存储过程：call [存储过程名称];

查看存储过程列表：
show procedure status;

删除存储过程：
drop procedure [存储过程名称];

注意事项：
在哪个库中创建的存储过程就在哪个库中使用，如果
跨库调用需要使用[存储过程所在库].[存储过程名]，
例如hellodb.test这种方式，删除时，同样要这样删除
MySQL触发器：

创建触发器：
创建触发器
CREATE [DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_body
说明：
trigger_name：触发器的名称
trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后触发
trigger_event:：{ INSERT |UPDATE | DELETE }，触发的具体事件
tbl_name：该触发器作用在表名

具体应用：
模拟场景：转账过程！
两张表分别记录jim和tom原有的钱
jim最开始有1万，tom有2千，现在模拟jim给tom转账1
千元.....
MariaDB [textdb]> create trigger text

-> after update
-> on user1 for each row
-> update user2 set money = money+1000;

Query OK, 0 rows affected (0.30 sec)

MariaDB [textdb]> update user1 set money = 9000;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

1 row in set (0.00 sec)

1 row in set (0.00 sec)

注意事项：
before：表示在这个事件之前触发，也就是说，如果
用户执行一个insert动作，在用户还没有执行insert之
前就会被触发器中设定的动作所替换！

查看触发器：
show triggers;
删除触发器：
drop trigger [触发器名称];
MySQL用户及权限管理：

1 创建用户：仅能创建一个受限制的用户，并支持赋予密码！
create user username@hostname identified by '密码'
具体用法：
MariaDB [textdb]> create user 'yl'@'172.20.%.%' identified by '密码';

2 修改用户名：对以登录的用户不起作用
rename user
用法格式：rename user [旧名称] to [新名称]；
具体用法：
mysql> rename user yl@'172.20.%.%' to gw@'172.20.%.%';

3 修改用户密码：
使用格式：
set password for username = password('密码');
具体用法：
set password for root = password('密码');

4 删除用户：
DROP USER username@hostIP --删除用户
在线连接的时候也可以删除用户！
具体用法：
MariaDB [mysql]> drop user gw@'172.20.%.%';

忘记管理员密码后的解决办法：
启动mysqld进程时，为其使用如下选项：
--skip-grant-tables --skip-networking[不允许网络连接]
使用UPDATE命令修改管理员密码
关闭mysqld进程，移除上述两个选项，重启mysqld

编辑/etc/my.cnf
[mysqld]
skip-grant-tables

重启服务后登录mysql直接更改管理员密码即可！
MySQL权限分类：

库级别权限/表级别：
alter ----修改表
alter routing ---修改存储过程和函数
create ---是否可以创建表和库
create routing ---是否可以创建存储过程和函数
create view --是否可以创建视图
drop ---是否可以删除表和库
execute ---是否可以执行存储过程和函数
grant option ---是否可以把自己获取的权限转给他人
index ----是否可以创建和删除索引
show view --是否可以查看视图的创建过程

数据操作权限(表级别)：
delete ---是否可以删除表中的行
insert ---是否可以插入数据
select ---是否可以查询数据
update ---是否可以修改数据

字段级别权限
select(字段1....)
ipdate(字段1....)
insert(字段1....)

管理类权限
create temporary tables ---创建临时表权限
create user ---能否创建用户
file ---在mysql服务器上读取和写入文件
lock tables ---可以手动显式施加表锁
process ---查看线程列表
具体命令：
show processlist
reload ----可以使用flush和resrt
主从复制：
replication client ---向服务器查询所有复制客户端
replication slave ---授权客户端有复制权限
show databases ---是否可以查看表
shutdown ---是否可以关闭服务器
super ----杂项命令

所有权限：all

权限的授予和收回：

grant [权限] on 修饰符，默认是表 to [用户]@[主机IP] identified by '密码'，{多个用户之间逗号隔开} [require ssl]；
object_type:修饰符

TABLE  ---默认是表

| FUNCTION ---存储函数
| PROCEDURE ---存储过程

priv_level:

*  ---所有库，表，函数，过程

| . ---所有库的所有对象
| db_name.* ---某一个库的所有对象
| db_name.tbl_name ---某一个库的一个表
| tbl_name ---某张表
| db_name.routine_name ---指定库的存储过程

with_option:

GRANT OPTION ----把自己的权限授权给它人

| MAX_QUERIES_PER_HOUR count ---每小时允许最大执行查询的次数
| MAX_UPDATES_PER_HOUR count ---每小时允许最大执行修改的次数
| MAX_CONNECTIONS_PER_HOUR count ---每小时允许最大建立连接的次数
| MAX_USER_CONNECTIONS count ---指定使用同一个账号，最多并发连接几次！

grant命令使用时，如果没有该用户，就会自动创建！

具体用法：
grant all on testdb.* to 'yl'@'172.20.%.%' identified by '密码';

回收权利：revoke
具体用法：
revoke insert on testdb.* from 'yl'@'172.20.%.%';
对正在登录的用户不起作用，用户退出再次登录后生效

收回全部权利：
revoke all on [某库或某表] from [用户@主机IP];

查看用户能够使用的权限：
show grants for username@'hostname';
具体用法：
MariaDB [testdb]> show grants for yl@'172.20.%.%';

查看当前用户权限：
show grants for current_user();

几个跟用户授权相关的表：
db：库级别权限
host：主机级别权限，已废弃
tables_priv：表级别权限
colomns_priv：列级别的权限
procs_priv：存储过程和存储函数相关的权限
proxies_priv：代理用户权限