数据库 SQL DCL 权限控制 存储过程 游标 触发器

权限控制

  • GRANT 和 REVOKE 可在几个层次上控制访问权限：
    • 整个服务器，使用 GRANT ALL 和 REVOKE ALL；
    • 整个数据库，使用 ON database.*；
    • 特定的表，使用 ON database.table；
    • 特定的列；
    • 特定的存储过程。
  • 新创建的账户没有任何权限。
  • 账户用 username@host 的形式定义，username@% 使用的是默认主机名。

  • MySQL 的账户信息保存在 MySQL 这个数据库中。

    1. USE mysql;
    2. SELECT user FROM user;
    3. 复制代码

    创建账户

    1. CREATE USER myuser IDENTIFIED BY 'mypassword';

    修改账户名

    1. UPDATE user SET user='newuser' WHERE user='myuser';
    2. FLUSH PRIVILEGES;

    删除账户

    1. DROP USER myuser;

    查看权限

    1. SHOW GRANTS FOR myuser;

    授予权限

    1. GRANT SELECT, INSERT ON *.* TO myuser;

    删除权限

    1. REVOKE SELECT, INSERT ON *.* FROM myuser;

    更改密码

    1. SET PASSWORD FOR myuser = 'mypass';

    存储过程

  • 存储过程可以看成是对一系列 SQL 操作的批处理；

  • 使用存储过程的好处
    • 代码封装，保证了一定的安全性；
    • 代码复用；
    • 由于是预先编译，因此具有很高的性能。

  • 创建存储过程

    • 命令行中创建存储过程需要自定义分隔符，因为命令行是以 ; 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。
    • 包含 in、out 和 inout 三种参数。
    • 给变量赋值都需要用 select into 语句。
    • 每次只能给一个变量赋值，不支持集合的操作。

      创建存储过程

      1. DROP PROCEDURE IF EXISTS `proc_adder`;
      2. DELIMITER ;;
      3. CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_adder`(IN a int, IN b int, OUT sum int)
      4. BEGIN
      5. DECLARE c int;
      6. if a is null then set a = 0;
      7. end if;
      8. if b is null then set b = 0;
      9. end if;
      10. set sum  = a + b;
      11. END
      12. ;;
      13. DELIMITER ;

      使用存储过程

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

      游标

  • 游标（cursor）是一个存储在 DBMS 服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。

  • 在存储过程中使用游标可以对一个结果集进行移动遍历。
  • 游标主要用于交互式应用，其中用户需要对数据集中的任意行进行浏览和修改。

  • 使用游标的四个步骤：

    • 声明游标，这个过程没有实际检索出数据；
    • 打开游标；
    • 取出数据；
    • 关闭游标；
      1. DELIMITER $
      2. CREATE  PROCEDURE getTotal()
      3. BEGIN
      4. DECLARE total INT;
      5. -- 创建接收游标数据的变量
      6. DECLARE sid INT;
      7. DECLARE sname VARCHAR(10);
      8. -- 创建总数变量
      9. DECLARE sage INT;
      10. -- 创建结束标志变量
      11. DECLARE done INT DEFAULT false;
      12. -- 创建游标
      13. DECLARE cur CURSOR FOR SELECT id,name,age from cursor_table where age>30;
      14. -- 指定游标循环结束时的返回值
      15. DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
      16. SET total = 0;
      17. OPEN cur;
      18. FETCH cur INTO sid, sname, sage;
      19. WHILE(NOT done)
      20. DO
      21.    SET total = total + 1;
      22.    FETCH cur INTO sid, sname, sage;
      23. END WHILE;
      24. CLOSE cur;
      25. SELECT total;
      26. END $
      27. DELIMITER ;
      28. -- 调用存储过程
      29. call getTotal();

      触发器

      触发器是一种与表操作有关的数据库对象，当触发器所在表上出现指定事件时，将调用该对象，即表的操作事件触发表上的触发器的执行。
      可以使用触发器来进行审计跟踪，把修改记录到另外一张表中。
      MySQL 不允许在触发器中使用 CALL 语句 ，也就是不能调用存储过程。
      BEGIN 和 END
      当触发器的触发条件满足时，将会执行 BEGIN 和 END 之间的触发器执行动作。 :::danger 🔔 注意：在 MySQL 中，分号 ; 是语句结束的标识符，遇到分号表示该段语句已经结束，MySQL 可以开始执行了。因此，解释器遇到触发器执行动作中的分号后就开始执行，然后会报错，因为没有找到和 BEGIN 匹配的 END。
      这时就会用到 DELIMITER 命令（DELIMITER 是定界符，分隔符的意思）。它是一条命令，不需要语句结束标识，语法为：DELIMITER new_delemiter。new_delemiter 可以设为 1 个或多个长度的符号，默认的是分号 ;，可以把它修改为其他符号，如 $ - DELIMITER $ 。在这之后的语句，以分号结束，解释器不会有什么反应，只有遇到了 $，才认为是语句结束。注意，使用完之后，还应该记得把它给修改回来。 ::: NEW 和 OLD

  • MySQL 中定义了 NEW 和 OLD 关键字，用来表示触发器的所在表中，触发了触发器的那一行数据。

  • 在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
  • 在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；
  • 在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；

  • 使用方法： NEW.columnName （columnName 为相应数据表某一列名）

    创建触发器

    CREATE TRIGGER 指令用于创建触发器。
    语法：

    1. CREATE TRIGGER trigger_name
    2. trigger_time
    3. trigger_event
    4. ON table_name
    5. FOR EACH ROW
    6. BEGIN
    7. trigger_statements
    8. END;

    说明：

  • trigger_name：触发器名

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

示例：

  1. DELIMITER $
  2. CREATE TRIGGER `trigger_insert_user`
  3. AFTER INSERT ON `user`
  4. FOR EACH ROW
  5. BEGIN
  6.     INSERT INTO `user_history`(user_id, operate_type, operate_time)
  7.     VALUES (NEW.id, 'add a user',  now());
  8. END $
  9. DELIMITER ;

查看触发器

  1. SHOW TRIGGERS;

删除触发器

  1. DROP TRIGGER IF EXISTS trigger_insert_user;