Mysql_Note6

1.游标

1.1 定义

够对结果集中的每一条记录进行定位,并对指向的记录中的数据进行操作的数据结构

1.2 游标的使用步骤

创建存储函数

create function 函数名称 (参数) returns 数据类型 程序体
1

存储函数与存储过程的区别

游标使用语法

# 定义游标
declare 游标名 cursor for 查询语句
# 打开游标
open 游标名
# 从游标结果集中读取数据
fetch 游标名 into 变量名
# 关闭游标
close 游标名;
1
2
3
4
5
6
7
8

示例

1.在现有库存数量的基础上,加上本次进货的数量

2.根据本次进货的价格,数量,现有商品的平均进价和库存,计算新的平均进价;(本次进货价格 * 本次进货数量 + 现有商品平均进价 * 现有商品库存)/(本次进货数量 + 现有库存数量）

delimiter //
create procedure demo.mytest(mylistnumber int)
begin
declare mystockid int;
declare myitemnumber int ;
declare myquantity deciaml(10,3);
DECLARE myprice DECIMAL(10,2); 

-- 用来控制循环结束
DECLARE done INT DEFAULT FALSE; 

 -- 定义游标
DECLARE cursor_importdata CURSOR FOR
SELECT b.stockid,a.itemnumber,a.quantity,a.importprice FROM demo.importdetails AS a
JOIN demo.importhead AS b ON (a.listnumber=b.listnumber)

-- 条件处理语句
declare continue handler for not found set done = TRUE;

-- 打开游标
open cursor_importdata;
fetch cursor_importdate into mystockid,myitemnumber,myquantity,myprice

-- 循环
repeat

-- 更新进价
UPDATE demo.goodsmaster AS a,demo.inventory AS b
SET a.avgimportprice = (a.avgimportprice*b.invquantity+myprice*myquantity)/(b.invquantity+myquantity)
WHERE a.itemnumber=b.itemnumber AND b.stockid=mystockid AND a.itemnumber=myitemnumber;

-- 更新库存
UPDATE demo.inventory 
SET invquantity = invquantity + myquantity
WHERE stockid = mystockid AND it
emnumber=myitemnumber;

-- 获取下一条记录
FETCH cursor_importdata INTO mystockid,myitemnumber,myquantity,myprice;

-- 结束循环
UNTIL done END REPEAT;
CLOSE cursor_importdata;
END
//
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

注意

1. DECLARE done INT DEFAULT FALSE MySQL没有boolean类型 , 把true处理为1,把false处理为0
2. 游标 适用于需要对集合中的行进行单独处理的场景

1.3 补充说明

1.3.1 条件处理语句

DECLARE 处理方式 HANDLER FOR 问题 操作；
-- 当游标读到结果集的最后，没有记录了，设置操作完成标识为真，然后继续运行程序
declare continue handler for not found set done =true;
1
2
3

注意

1. NOT FOUND 游标走到结果集的最后,没有记录了
2. done是我定义的用来标识数据集中的数据是否已经处理完成的一个标记
3. CONTINUE和EXIT , 表示遇到问题,执行了语法结构中的“操作”之后,是选择继续运行程序,还是选择退出，结束程序

1.3.2流程控制语句

跳转语句

iterate: 只能用在循环语句内,表示重新开始循环

leave: 可以用在循环语句内,或者以 BEGIN 和 END 包裹起来的程序体内,表示跳出循环或者跳出程序体的操作

循环语句

标签：LOOP
操作
END LOOP 标签
1
2
3

LOOP 循环不能自己结束，需要用跳转语句 ITERATE 或者 LEAVE 来进行控制

WHILE 条件 DO
操作
END WHILE;
1
2
3

WHILE 循环是先判断条件,再执行循环体中的操作

REPEAT
操作
UNTIL 条件 END REPEAT；
1
2
3

REPEAT循环是先执行操作,后判断条件

条件判断语句

IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2]……
[ELSE 操作N]
END IF
1
2
3
4

IF 语句的特点是,不同的表达式对应不同的操作

CASE 表达式
WHEN 值1 THEN 操作1
[WHEN 值2 THEN 操作2]……
[ELSE 操作N]
END CASE;
1
2
3
4
5

CASE 语句的特点是,表达式不同的值对应不同的操作

1.4 思考题

1.假设有张 demo.test 表如下

编写简单的存储过程,用游标来逐一处理一个数据表中的数据,

要求：编号为偶数的记录，myquant=myquant+1；编号是奇数的记录，myquant=myquant+2

delimiter //
create procedure test_fuc()
begin

declare my_id int;
declare my_myquant int;
declare done int default false;

declare cursor_test cursor for
select  id,myquant from where demo.test;
declare continue handler for not found set done =True; 

open cursor_test;
fetch cursor_test into my_id,my_myquant;

while done
do
if  (my_id mod 2) =1 then

update demo.test set myquant =myquant +2 where id =my_id

else
update demo.test  set myquant =myquant +1 where id =my_id

end if
fetch cursor_test into  my_id,my_myquant;-- 获取下一条信息
end while
close cursor_test;-- 关闭游标
end
//
delimiter ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

2.触发器

1.触发器的基本操作

创建触发器

create trigger 触发器 {before | after } { insert| update |delete}
on 表名 FOR each ROW 表达式
1
2

查看触发器

show triggers\G;
1

删除触发器

drop trigge 触发器名称;
1

示例

--假如在 2020 年 12 月 20 日这一天，会员编号是 2 的会员李四，到超市的某家连锁店购买了一条烟，消费了 150 元。现在，我们用之前学过的 SQL 语句，把这个会员储值余额的变动情况记录到会员储值历史表中


delimiter //
create trigger demo.upd_membermaster before update on demo.membermaster  --在更新前触发
for each row

begin
if (new.meberdeposit <> old.meberdeposit) --
then

insert into demo.deposithist(meberid,transdate,oldvalue,newvalue,changedvalue)

select new.memberid,now(),old.meberdeposit,new.memberdeposit,new.meberdeposit-old.memberdeposit;

end if;
end
//

delimiter ;


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

注意

通过 select row_count函数来捕获上一条sql是否执行成功且影响的数据条数,-1表示执行失败

2.触发器的优缺点

3.思考题

1.使用触发器实现每当在进货单明细表中插入或修改数据的时候,都要更新进货单头表中的总计数量和总计金额

delimiter //
create trigger demo.update_importdetails after  update on demo.importdetails
for each row
begin

update demo.importhead set quantity=ifnull(quantity,0)-old.quantity+new.quantity,
importvalue=ifnull(importvalue,0)-old.importvalue+new.importvalue where listnumber=new.listnumber



end
delimiter ;

1
2
3
4
5
6
7
8
9
10
11
12
13

3.权限管理

1.角色的基本操作

创建角色

create role 角色
--示例
create role 'manager'@'localhost';
1
2
3

如果不写主机名,MySQL 默认是通配符“%”,意思是这个账号可以从任何一台主机上登录数据库

给角色赋权

GRANT 权限 ON 表名 TO 角色名;
-- 示例
grant select,insert,delete,update on demo.invcount to 'stocker'
1
2
3

查看角色权限

show grants for 'manager'
1

删除角色

drop role 角色名称
1

撤销角色权限

revoke 权限 on 表名 from 角色名
revole 角色ming from 用户名
1
2

激活角色权限

set global activate_all_roles_on_login=ON
1

MySQL 中创建了角色之后,默认都是没有被激活的

查看角色是否被激活

CURRENT_ROLE()函数
1

2.用户的基本操作

1.创建用户

create user 用户名 [identified by 密码]
1

“[ ]”表示可选，也就是说，可以指定用户登录时需要密码验证，也可以不指定密码验证，这样用户可以直接登录

2.给用户授权

grant 角色名称 to 用户名称
1

3.直接给用户授权

grant 权限 on 表名 to 用户名称
1

4.查看用户权限

show grants for 用户名
1

5.删除用户

drop user 用户;
1

3.思考题

1.创建一个财务角色, 使李四通过财务的角色获得对应付账款表增删改查的权限,和对商品信息表,盘点表有只读的权限

create role 'caiwu' 
grant select,insert,delete,update on demo.settlement to 'caiwu' 
grant select on demo.goodsmaste to demo.goodsmaster

create user 'lisi' identified by 'mima'
grant 'caiwu' to 'lisi'
1
2
3
4
5
6