0%

MySQL进阶学习笔记

8 min read   |   2.2k words Posted on Edited on In
继续学习MySQL的触发器，存储过程，权限管理，感觉对MySQL的认知似乎又深入了一些

之前只是简单得学习了MySQL的增删查改等，在项目中深感知识不够，于是学习了一下MySQL触发器，存储过程，权限管理等知识。

触发器

是什么

触发器就是对有关系的数据表进行操作的一种功能，当满足触发条件，则执行指定的事件。比如商品的库存表和用户的订单表之间，若用户下单，用户订单表会相应增加，而库存表则会相应减少，触发器就可以完成这样的操作。
如果不实用触发器，则需要在事件之后，对有关联的数据表手动update。如上订单表与库存表，用户下单后，需要手动更新库存表。

组成

触发器的组成包括触发对象(谁)，触发事件(做了什么事)，触发事件(时间)，触发之后执行的事件(因前面的做了什么事，需要做什么)

语法

1
2
3
4
5
6
create trigger <触发器名称>  --触发器`必须`有名字，最多64个字符
{ before | after }  --触发事件，决定满足条件之后的执行语句，在该条件之前还是之后执行
{ insert | update | delete }  --指定触发事件。
on <表名称>  --触发器是属于某一个表的:当在这个表上执行插入、 更新或删除操作的时候就导致触发器的激活. 我们不能给同一张表的同一个事件安排两个触发器。
for each row  --触发器的执行间隔：FOR EACH ROW子句通知触发器 每隔一行执行一次动作，而不是对整个表执行一次。
<满足条件之后，需要执行的SQL语句>  --触发器包含所要触发的SQL语句，可以是复合语句。

例如

1
2
3
4
5
6
7
8
9
10
delimiter $ --修改结束符，以免和SQL语句结束符混淆
create trigger t1
before
update
on orde
for each row
begin
update goods set num = num+old.much-new.much where gid=old.gid;
end$
delimiter ;

注意：不能同时在一个表上建立2个相同类型的触发器

new & old

触发器语句中会涉及到参数传递
例如触发条件是delete，则在语句之后之后，会有一条语句被删除，因此若在语句之后执行触发事件，并且需要引用之前的数据，可以采用old这个关键词。
又如触发条件是insert，则在语句执行之后，会有一条语句被添加，因此若在语句之后执行触发事件，并且需要引用之前的数据，可以采用new这个关键词。
对于update而言，一般new 与old都同时存在。
注意触发事件before与after的选择：不能在触发之后对之前的数据进行更新

实例

因为触发器是需要指定表的，因此需要指定数据库。以库存表与用户订单表为例

  1. 新建数据库与表
    新建表并且随便指定一部分数据 goods为库存表(gid库存标号,name名称,num数量)，orde为订单表(oid订单编号,gid库存编号,much购买商品数目)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    create database learntrigger;

    CREATE TABLE `goods` (
      `gid` int(11) DEFAULT NULL,
      `name` varchar(20) DEFAULT NULL,
      `num` smallint(6) DEFAULT NULL
    ) ;

    CREATE TABLE `orde` (
      `oid` int(11) DEFAULT NULL,
      `gid` int(11) DEFAULT NULL,
      `much` smallint(6) DEFAULT NULL
    );

  2. 新建一个触发器
    触发器完成如下操作：用户下单之后，库存相应减少.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    delimiter $ 
    create trigger t1
    before
    insert
    on orde
    for each row
    begin
    update goods set num:=num-new.much where gid=new.gid;
    end$

完善上述触发器：若用户下单数目，超过库存，则将用户下单的数量自动设置为库存数，以免出现库存为负数的情形

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
delimiter $ 
create trigger t1
before
insert
on orde
for each row
begin
declare
temp_num int;
select num into temp_num from goods where gid = new.gid;
if new.much > temp_num then
set new.much=temp_num;
end if;
update goods set num:=num-new.much where gid=new.gid;
end$

这里必须选择before，若为update因为insert之后数据已经生成，这个时候确再去修改insert的值，存在逻辑错误。

其他

  1. 查看触发器 

    1
    show triggers [FROM database_name];

    若有先使用use database_name;则可以不添加[FROM database_name]
    添加\G 参数可以以列的形式显示，如show triggers \G;

  2. 删除触发器
    1
    drop trigger [IF EXISTS] [database_name.]trigger_name

存储过程与游标

与触发器比较

存储过程与触发器类似，但是存储过程可以主动调用。参数传递更加灵活，功能增加强大。

组成

1
2
3
4
create procedure  procedure_name([paraname paratype])
begin
<sql语句>
end

调用
call procedure_name([paraname paratype]);
paraname 为用户指定的参数名称，paratype可以从in out inout选择
in表示输入型参数，out表示输出型参数，inout表示可进可出

实例

以下创建一个输入矩形的宽高，输出矩阵面积，并且判断矩形的宽与高的关系

1
2
3
4
5
6
7
8
9
10
11
12
delimiter $
create procedure p100(width int,height int)
begin
select concat('面积',width*height) as area;
if width>height then
select "宽大于高";
elseif width < height then
select "高大于宽";
else 
select "正方形";
end if;
end$

调用

1
call p1(100,200)$

权限管理

参考文献：

  • 英文参考文献
  • 中文参考文献
    MySQL提供的权限管理，分为连接的权利与执行的权利.

    连接的权利

    连接的权利指的是：是否有权限连接MySQL,该部分又MYSQL数据库下面的mysql数据库中的user表段控制。
    1
    2
    3
    4
    5
    mysql -uroot -p
    show databases; /*此处可以看到有一个mysql数据库*/
    use mysql;
    select host ,user,password from user;/*查看可以有效连接的用户信息*/
    /*host从哪里来、用户名username、密码possword*/

从上面可以看到对权限的管理主要从user,password,host三个参数来实现控制

执行的权利

连接上数据库之后，执行SQL语句时还需要进行权限检查。
包括执行权限(增删查改)，授予的权限(全局层级,数据库层级,表层级,列层级,子程序层级)的检查

新建具有特定权限的用户

grand [权限1,选项2,权限3……] on . to username@’set_host’ identified by ‘set_password’；/ 授予权限/
recoke [权限1,选项2,权限3……] on . to username@’set_host’ identified by ‘set_password’；/ 取消权限/
其中*.*对应上面授予的权限层级的不同有不同的写法。

  • 全局层级
    全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。GRANT ALL ON .和REVOKE ALL ON .只授予和撤销全局权限。
  • 数据库层级
    数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db和mysql.host表中。GRANT ALL ON db_name.和REVOKE ALL ON db_name.只授予和撤销数据库权限。
  • 表层级
    表权限适用于一个给定表中的所有列。这些权限存储在mysql.talbes_priv表中。GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤销表权限。
  • 列层级
    列权限适用于一个给定表中的单一列。这些权限存储在mysql.columns_priv表中。当使用REVOKE时，您必须指定与被授权列相同的列。
  • 子程序层级
    CREATE ROUTINE, ALTER ROUTINE, EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了CREATE ROUTINE外，这些权限可以被授予为子程序层级，并存储在mysql.procs_priv表中。
权限 意义
ALL [PRIVILEGES] 设置除GRANT OPTION之外的所有简单权限
ALTER 允许使用ALTER TABLE
ALTER ROUTINE 更改或取消已存储的子程序
CREATE 允许使用CREATE TABLE
CREATE ROUTINE 创建已存储的子程序
CREATE TEMPORARY TABLES 允许使用CREATE TEMPORARY TABLE
CREATE USER 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW 允许使用CREATE VIEW
DELETE 允许使用DELETE
DROP 允许使用DROP TABLE
EXECUTE 允许用户运行已存储的子程序
FILE 允许使用SELECT…INTO OUTFILE和LOAD DATA INFILE
INDEX 允许使用CREATE INDEX和DROP INDEX
INSERT 允许使用INSERT
LOCK TABLES 允许对您拥有SELECT权限的表使用LOCK TABLES
PROCESS 允许使用SHOW FULL PROCESSLIST
REFERENCES 未被实施
RELOAD 允许使用FLUSH
REPLICATION CLIENT 允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE 用于复制型从属服务器（从主服务器中读取二进制日志事件）
SELECT 允许使用SELECT
SHOW DATABASES SHOW DATABASES显示所有数据库
SHOW VIEW 允许使用SHOW CREATE VIEW
SHUTDOWN 允许使用mysqladmin shutdown
SUPER 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。
UPDATE 允许使用UPDATE
USAGE “无权限”的同义词
GRANT OPTION 允许授予权限

注意设置之后需要冲刷权限flush privileges;

坚持原创技术分享，您的支持将鼓励我继续创作！