1.游标
1.1 定义
够对结果集中的每一条记录进行定位,并对指向的记录中的数据进行操作的数据结构
1.2 游标的使用步骤
创建存储函数
create function 函数名称 (参数) returns 数据类型 程序体
存储函数与存储过程的区别
特性 | 存储函数 | 存储过程 |
---|---|---|
必须返回一个值 | 必须返回 | 可以不返回 |
是否能查询函数中使用 | 能 | 不能 |
是否通过call调用 | 不能 | 能 |
能否进行表操作和事务操作 | 不能 | 能 |
游标使用语法
# 定义游标
declare 游标名 cursor for 查询语句
# 打开游标
open 游标名
# 从游标结果集中读取数据
fetch 游标名 into 变量名
# 关闭游标
close 游标名;
示例
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
//
注意
DECLARE done INT DEFAULT FALSE
MySQL没有boolean类型 , 把true处理为1,把false处理为0- 游标 适用于需要对集合中的行进行单独处理的场景
1.3 补充说明
1.3.1 条件处理语句
DECLARE 处理方式 HANDLER FOR 问题 操作;
-- 当游标读到结果集的最后,没有记录了,设置操作完成标识为真,然后继续运行程序
declare continue handler for not found set done =true;
注意
- NOT FOUND 游标走到结果集的最后,没有记录了
- done是我定义的用来标识数据集中的数据是否已经处理完成的一个标记
- CONTINUE和EXIT , 表示遇到问题,执行了语法结构中的“操作”之后,是选择继续运行程序,还是选择退出,结束程序
1.3.2流程控制语句
跳转语句 | 循环语句 | 条件判断语句 |
---|---|---|
iterate | loop | if |
leave | while | case |
repeat |
跳转语句
iterate: 只能用在循环语句内,表示重新开始循环
leave: 可以用在循环语句内,或者以 BEGIN 和 END 包裹起来的程序体内,表示跳出循环或者跳出程序体的操作
循环语句
标签:LOOP
操作
END LOOP 标签
LOOP 循环不能自己结束,需要用跳转语句 ITERATE 或者 LEAVE 来进行控制
WHILE 条件 DO
操作
END WHILE;
WHILE 循环是先判断条件,再执行循环体中的操作
REPEAT
操作
UNTIL 条件 END REPEAT;
REPEAT循环是先执行操作,后判断条件
条件判断语句
IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2]……
[ELSE 操作N]
END IF
IF 语句的特点是,不同的表达式对应不同的操作
CASE 表达式
WHEN 值1 THEN 操作1
[WHEN 值2 THEN 操作2]……
[ELSE 操作N]
END CASE;
CASE 语句的特点是,表达式不同的值对应不同的操作
1.4 思考题
1.假设有张 demo.test 表如下
id | myquant |
---|---|
1 | 100 |
2 | 101 |
3 | 102 |
4 | 103 |
编写简单的存储过程,用游标来逐一处理一个数据表中的数据,
要求:编号为偶数的记录,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 ;
2.触发器
1.触发器的基本操作
创建触发器
create trigger 触发器 {before | after } { insert| update |delete}
on 表名 FOR each ROW 表达式
查看触发器
show triggers\G;
删除触发器
drop trigge 触发器名称;
示例
--假如在 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 ;
注意
通过 select row_count函数来捕获上一条sql是否执行成功且影响的数据条数,-1表示执行失败
2.触发器的优缺点
1 | 优点 | 缺点 |
---|---|---|
可以确保数据的完整性 | 触发器最大的一个问题就是可读性差 | |
触发器可以帮助我们记录操作日志 | ||
触发器还可以用在操作数据前,对数据进行合法性检查 |
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 ;
3.权限管理
1.角色的基本操作
创建角色
create role 角色
--示例
create role 'manager'@'localhost';
如果不写主机名,MySQL 默认是通配符“%”,意思是这个账号可以从任何一台主机上登录数据库
给角色赋权
GRANT 权限 ON 表名 TO 角色名;
-- 示例
grant select,insert,delete,update on demo.invcount to 'stocker'
查看角色权限
show grants for 'manager'
删除角色
drop role 角色名称
撤销角色权限
revoke 权限 on 表名 from 角色名
revole 角色ming from 用户名
激活角色权限
set global activate_all_roles_on_login=ON
MySQL 中创建了角色之后,默认都是没有被激活的
查看角色是否被激活
CURRENT_ROLE()函数
2.用户的基本操作
1.创建用户
create user 用户名 [identified by 密码]
“[ ]”表示可选,也就是说,可以指定用户登录时需要密码验证,也可以不指定密码验证,这样用户可以直接登录
2.给用户授权
grant 角色名称 to 用户名称
3.直接给用户授权
grant 权限 on 表名 to 用户名称
4.查看用户权限
show grants for 用户名
5.删除用户
drop user 用户;
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'