sql：权限管理、存储过程、视图、触发器

1.权限管理

1.1.Grant（授予权限）

GRANT privilege,[privilege],.. ON privilege_level
TO user [IDENTIFIED BY password]
[REQUIRE tsl_option]
[WITH [GRANT_OPTION | resource_option]];

简单解释一下：

• 在GRANT关键字后指定一个或多个权限。如果授予用户多个权限，则每个权限由逗号分隔。
• ON privilege_level 确定权限应用级别。MySQL 支持 global、database、table和列级别。如果使用列权限级别，则必须在每个权限之后指定一个或逗号分隔列的列表。
• user 是要授予权限的用户。如果用户已存在，则GRANT语句将修改其权限。否则将创建一个新用户。
• 可选子句IDENTIFIED BY允许您为用户设置新的密码。
• REQUIRE tsl_option指定用户是否必须通过 SSL，X059 等安全连接连接到数据库服务器。
• 可选 WITH GRANT OPTION 子句允许您授予其他用户或从其他用户中删除您拥有的权限。
• 此外，您可以使用WITH子句分配 MySQL 数据库服务器的资源，例如，设置用户每小时可以使用的连接数或语句数。这在 MySQL 共享托管等共享环境中非常有用。

1.2.Revoke（撤销权限）

REVOKE   privilege_type [(column_list)]
        [, priv_type [(column_list)]]...
ON [object_type] privilege_level
FROM user [, user]...

1.3.查看权限

SHOW GRANTS FOR myuser;

2.存储过程

我们可以把存储过程看成是一些 SQL 语句的集合，中间加了点逻辑控制语句。

存储过程在业务比较复杂的时候是非常实用的且一旦调试完成通过后就能稳定运行，另外，使用存储过程比单纯 SQL 语句执行要快，因为存储过程是预编译过的。

存储过程在互联网公司应用不多，因为存储过程难以调试和扩展，而且没有移植性，还会消耗数据库资源。阿里巴巴 Java 开发手册里要求禁止使用存储过程。

-- 如果存储过程已存在，则删除它
DROP PROCEDURE IF EXISTS `proc_adder`;
DELIMITER ;; -- 改变命令语句结束的分隔符，以便可以在存储过程中使用分号

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_adder`(
    IN a int,      -- 定义一个整型输入参数 a
    IN b int,      -- 定义一个整型输入参数 b
    OUT sum int     -- 定义一个整型输出参数 sum
)
BEGIN
    -- 如果参数 a 为 NULL，则将其设置为 0
    if a is null then set a = 0;
    end if;

    -- 如果参数 b 为 NULL，则将其设置为 0
    if b is null then set b = 0;
    end if;

    -- 计算 a 和 b 的和，并将结果赋值给输出参数 sum
    set sum  = a + b;
END;
-- 结束存储过程的定义
DELIMITER ; -- 恢复命令语句结束的分隔符为分号

使用存储过程

set @b=5;
call proc_adder(2,@b,@s);
select @s as sum;

3.视图

视图是基于 SQL 语句的结果集的可视化的表。视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。

对视图的操作和对普通表的操作一样。

作用：

• 简化复杂的 SQL 操作，比如复杂的联结；
• 通过只给用户访问视图的权限，保证数据的安全性；

创建视图

CREATE VIEW top_10_user_view AS
SELECT id, username
FROM user
WHERE id < 10;

删除视图

DROP VIEW top_10_user_view;

4.触发器

4.1.优缺点

触发器是一种与表操作有关的数据库对象，当触发器所在表上出现指定事件时，将调用该对象。我们可以使用触发器来进行审计跟踪，把修改记录到另外一张表中。

见到审计跟踪这四个字简直ptsd了好么hhh，之前为了接入实习公司的审计系统，问了好多人呜呜呜

使用触发器的优点：

• SQL 触发器提供了另一种检查数据完整性的方法。
• SQL 触发器可以捕获数据库层中业务逻辑中的错误。
• SQL 触发器对于审计表中数据的更改非常有用。

使用触发器的缺点：

• SQL 触发器只能提供扩展验证，并且不能替换所有验证。必须在应用程序层中完成一些简单的验证。
• 从客户端应用程序调用和执行 SQL 触发器是不可见的，因此很难弄清楚数据库层中发生了什么。
• SQL 触发器可能会增加数据库服务器的开销。
• MySQL 不允许在触发器中使用 CALL 语句 ，也就是不能调用存储过程。

注意：MySQL 5.7.2 版之前，每个表定义最多六个触发器。但是，从 MySQL 版本 5.7.2+开始，可以为同一触发事件和操作时间定义多个触发器。

4.2.创建、查看和删除

CREATE TRIGGER trigger_name
trigger_time
trigger_event
ON table_name
FOR EACH ROW
BEGIN
  trigger_statements
END;

说明：

• trigger_name：触发器名
• trigger_time : 触发器的触发时机。取值为 BEFORE 或 AFTER。
• trigger_event : 触发器的监听事件。取值为 INSERT、UPDATE 或 DELETE。
• table_name : 触发器的监听目标。指定在哪张表上建立触发器。
• FOR EACH ROW: 行级监视，Mysql 固定写法，其他 DBMS 不同。
• trigger_statements: 触发器执行动作。是一条或多条 SQL 语句的列表，列表内的每条语句都必须用分号 ; 来结尾。
• 当触发器的触发条件满足时，将会执行 BEGIN 和 END 之间的触发器执行动作。

DELIMITER $
CREATE TRIGGER `trigger_insert_user`
AFTER INSERT 
ON `user`
FOR EACH ROW
BEGIN
    INSERT INTO `user_history`(user_id, operate_type, operate_time)
    VALUES (NEW.id, 'add a user',  now());
END $
DELIMITER ;

-- 查看触发器
SHOW TRIGGERS;
-- 删除触发器
DROP TRIGGER IF EXISTS trigger_insert_user;