MySQL基础（六）流程控制、游标、触发器、窗口函数

最新推荐文章于 2024-04-30 16:27:04 发布

独憩

最新推荐文章于 2024-04-30 16:27:04 发布

阅读量199

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_54517101/article/details/131626374

版权

MySQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

定义条件与处理程序

定义条件 是事先定义程序执行过程中可能遇到的问题， 处理程序 定义了在遇到问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力，避免程序异常停止运行。

说明：定义条件和处理程序在存储过程、存储函数中都是支持的。

定义条件

定义条件就是给MySQL中的错误码命名，这有助于存储的程序代码更清晰。它将一个错误名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的 DECLARE HANDLER 语句中。

DECLARE 错误名称 CONDITION FOR 错误码（或错误条件）

错误码的说明： MySQL_error_code 和 sqlstate_value 都可以表示MySQL的错误。 MySQL_error_code是数值类型错误代码。

sqlstate_value是长度为5的字符串类型错误代码。

举例

#使用MySQL_error_code
DECLARE Field_Not_Be_NULL CONDITION FOR 1048;
#使用sqlstate_value
DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE '23000';

定义处理程序

可以为SQL执行过程中发生的某种类型的错误定义特殊的处理程序。定义处理程序时，使用DECLARE语句的语法如下：

DECLARE 处理方式 HANDLER FOR 错误类型 处理语句

处理方式：处理方式有3个取值：CONTINUE、EXIT、UNDO。

CONTINUE ：表示遇到错误不处理，继续执行。

EXIT ：表示遇到错误马上退出。

UNDO ：表示遇到错误后撤回之前的操作。MySQL中暂时不支持这样的操作。

错误类型（即条件）可以有如下取值：

SQLSTATE '字符串错误码' ：表示长度为5的sqlstate_value类型的错误代码；

MySQL_error_code ：匹配数值类型错误代码；

错误名称：表示DECLARE ... CONDITION定义的错误条件名称。

SQLWARNING ：匹配所有以01开头的SQLSTATE错误代码；

NOT FOUND ：匹配所有以02开头的SQLSTATE错误代码；

SQLEXCEPTION ：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码；

处理语句：如果出现上述条件之一，则采用对应的处理方式，并执行指定的处理语句。语句可以是像“ SET 变量 = 值 ”这样的简单语句，也可以是使用 BEGIN ... END 编写的复合语句。

举例

delimiter //
create procedure test_fault()
begin
declare continue handler for 1048 set @y=1;
set @x=1;
update employees set email = null where employee_id=101;
set @x=2;
end //

此时y=1,x=2

流程控制

IF

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

举例

delimiter //
create procedure IF_TEST()
begin
declare age int default 20;
if age>40
	then select '111';
elseif age>18
	then select '222';
else
	select '333';
end if;
end //

分支结构之 CASE

#情况一：类似于switch
CASE 表达式
WHEN 值1 THEN 结果1或语句1(如果是语句，需要加分号)
WHEN 值2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）

#情况二：类似于多重if
CASE
WHEN 条件1 THEN 结果1或语句1(如果是语句，需要加分号)
WHEN 条件2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）

举例

delimiter //
create procedure IF_TEST()
begin
declare a int default 1;
case a
when 0 then select '1111';
when 1 then select '22222';
else select '33333';
end case;
end //

delimiter //
create procedure IF_TEST()
begin
declare a int default 1;
case 
when a = 0 then select '1111';
when a = 1 then select '22222';
else select '33333';
end case;
end //

循环结构之LOOP

LOOP循环语句用来重复执行某些语句。LOOP内的语句一直重复执行直到循环被退出（使用LEAVE子句），跳出循环过程。 LOOP语句的基本格式如下：

[loop_label:] LOOP
循环执行的语句
IF XXX
THEN LEAVE loop_label;
END IF;
END LOOP [loop_label]

loop_label 好像是必须要写，不然无法退出循环

delimiter //
create procedure IF_TEST()
begin
declare a int default 1;
loop_label: LOOP
		set a = a+1;
		if a >= 10
			then leave loop_label;
		end if;
	end LOOP loop_label;
	select a;
end //

delimiter //
create procedure IF_TEST(out num int)
begin
declare a double default 0;
set num = 0;
loop_label: LOOP
	select avg(salary) into a from employees;
	if a >=12000
		then leave loop_label;
	end if;
	update employees set salary = salary*1.1;
	set num = num+1;
	end LOOP loop_label;
end //

循环结构之WHILE

WHILE语句创建一个带条件判断的循环过程。WHILE在执行语句执行时，先对指定的表达式进行判断，如果为真，就执行循环内的语句，否则退出循环。WHILE语句的基本格式如下：

[while_label:] WHILE 循环条件 DO
循环体
END WHILE [while_label];

delimiter //
create procedure IF_TEST()
begin
declare a int default 0;
while a<=10 do
    set a=a+1;
end while;
select a;
end //

循环结构之REPEAT

REPEAT语句创建一个带条件判断的循环过程。与WHILE循环不同的是，REPEAT 循环首先会执行一次循环，然后在 UNTIL 中进行表达式的判断，如果满足条件就退出，即 END REPEAT；如果条件不满足，则会就继续执行循环，直到满足退出条件为止。

REPEAT语句的基本格式如下：

[repeat_label:] REPEAT
循环体的语句
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label]

注意 until xxx后面不需要加；

delimiter //
create procedure IF_TEST()
begin
	declare a int default 0;
	repeat
		set a=a+1;
		until a>=10
		end repeat;
	select a;
end //

对比三种循环结构：

1、这三种循环都可以省略名称，但如果循环中添加了循环控制语句（LEAVE或ITERATE）则必须添加名称。

2、 LOOP：一般用于实现简单的"死"循环 WHILE：先判断后执行 REPEAT：先执行后判

断，无条件至少执行一次

跳转语句leave和iterate

leave可以理解为break

LEAVE 标记名

leave不仅仅可以用在循环体中，也可以用在普通的 begin_end中

DELIMITER //
CREATE PROCEDURE leave_begin(IN num INT)
begin_label: BEGIN
    IF num<=0
        THEN LEAVE begin_label;
    ELSEIF num=1
        THEN SELECT AVG(salary) FROM employees;
    ELSEIF num=2
        THEN SELECT MIN(salary) FROM employees;
    ELSE
        SELECT MAX(salary) FROM employees;
    END IF;
SELECT COUNT(*) FROM employees;
END //

ITERATE语句：只能用在循环语句（LOOP、REPEAT和WHILE语句）内，表示重新开始循环，将执行顺序转到语句段开头处。如果你有面向过程的编程语言的使用经验，你可以把 ITERATE 理解为 continue，意思为“再次循环”。

delimiter //
create procedure IF_TEST()
begin
	declare a int default 0;
	my_loop:LOOP
	SET a = a+1;
		if a<10
			then iterate my_loop;
		elseif a>15
			then leave my_loop;
		end if;
		
	end loop;
	select a;
end //

游标

虽然我们也可以通过筛选条件 WHERE 和 HAVING，或者是限定返回记录的关键字 LIMIT 返回一条记录，但是，却无法在结果集中像指针一样，向前定位一条记录、向后定位一条记录，或者是随意定位到某一条记录，并对记录的数据进行处理。

这个时候，就可以用到游标。游标，提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构。游标让 SQL 这种面向集合的语言有了面向过程开发的能力。

打个比方，如果说我的需求是，工资大于10000的人涨薪1000，其余的人涨薪2000。那么我们必须一个一个定位到每一个员工，这样的操作可以使用游标实现

使用游标步骤

1、声明游标

DECLARE cursor_name CURSOR FOR select_statement;

2、打开游标

OPEN cursor_name

3、使用游标（从游标中取得数据）

FETCH cursor_name INTO var_name [, var_name] ...

4、关闭游标

CLOSE cursor_name

举个例子

实现循环查看员工的工资，大于10000的涨薪1000，其余的涨薪2000

delimiter //
create procedure TEST()
begin
	declare emp_sal double(8,2) default 0;
	declare flag int default 0;
	declare sal_cursor cursor for select salary from employees;
	
	declare continue handler for NOT FOUND set flag=1;
	open sal_cursor;
	loop_label:LOOP
		if flag>=1
			then leave loop_label;
		end if;
		fetch sal_cursor into emp_sal;
		if emp_sal>10000
			THEN UPDATE employees set salary = salary+1000 where salary = emp_sal;
		else
			UPDATE employees set salary = salary+2000 where salary = emp_sal;
		end if;
	end loop;
	close sal_cursor;
end //

declare continue handler for NOT FOUND set flag=1;

这句话保证了循环完所有数据后能退出循环

游标是 MySQL 的一个重要的功能，为逐条读取结果集中的数据，提供了完美的解决方案。跟在应用层面实现相同的功能相比，游标可以在存储程序中使用，效率高，程序也更加简洁。

但同时也会带来一些性能问题，比如在使用游标的过程中，会对数据行进行加锁，这样在业务并发量大的时候，不仅会影响业务之间的效率，还会消耗系统资源，造成内存不足，这是因为游标是在内存中进行的处理。

建议：养成用完之后就关闭的习惯，这样才能提高系统的整体效率。

全局变量的持久化

之前在设置全局变量的时候，如果重启了sql就会丢失这样的设置

使用SET GLOBAL语句设置的变量值只会临时生效。数据库重启后，服务器又会从MySQL配置文件中读取变量的默认值。 MySQL 8.0版本新增了 SET PERSIST 命令。例如，设置服务器的最大连接数为1000：

SET PERSIST global max_connections = 1000;

触发器

触发器是由 事件来触发 某个操作，这些事件包括 INSERT 、 UPDATE 、 DELETE 事件。所谓事件就是指用户的动作或者触发某项行为。如果定义了触发程序，当数据库执行这些语句时候，就相当于事件发生了，就会自动激发触发器执行相应的操作。

当对数据表中的数据执行插入、更新和删除操作，需要自动执行一些数据库逻辑时，可以使用触发器来实现。

说到底就是一个监听器，并作出回应。

触发器的使用

CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW
触发器执行的语句块;

案例1，在test1表插入数据之前，在test2表中插入1

create table test1(
id int primary key auto_increment,
a int
)

CREATE TABLE test2(
id INT PRIMARY KEY AUTO_INCREMENT,
b INT
)

DELIMITER //
create trigger insert_tri
before insert on test1
for each row
begin
	insert into test2(b) values(1);
end //

insert into test1(a) values(222)

select * from test2

案例2，在员工表插入前检查工资是否大于老板的工资，如果是，报错

DELIMITER //
create trigger insert_tri2
before insert on employees
for each row
begin
	declare sal double;
	
	select salary into sal from employees where employee_id = NEW.manager_id;
	if NEW.salary > sal then
		SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪资高于领导薪资错误';
		end if;
	
end //

这里使用了 SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪资高于领导薪资错误'; 手动触发报错，且使用NEW.获取新加入的数据

查看触发器

查看触发器是查看数据库中已经存在的触发器的定义、状态和语法信息等。

方式1：查看当前数据库的所有触发器的定义

SHOW TRIGGERS

方式2：查看当前数据库中某个触发器的定义

SHOW CREATE TRIGGER 触发器名

方式3：从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息。

SELECT * FROM information_schema.TRIGGERS

删除触发器

DROP TRIGGER IF EXISTS 触发器名称

新特性1、窗口函数

对于数据分组处理，我们之前学过了 group by，但是这样会造成一个类别的所有数据都被压缩到一条数据，而无法处理分类别的单个数据处理

举个例子，对于不同种类的商品，我想统计每个商品与同类商品中最贵的商品的差价，那么可能需要根据不同的类别对数据进行单独处理，窗口函数就可以实现。

都是分类，窗口函数分类完，数据条数不变

窗口函数分类

窗口函数可以分为 静态窗口函数 和 动态窗口函数。

静态窗口函数的窗口大小是固定的，不会因为记录的不同而不同；

动态窗口函数的窗口大小会随着记录的不同而变化。

语法结构

函数 OVER（[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）
or
函数 OVER 窗口名 … WINDOW 窗口名 AS （[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）

下面的案例针对这个表进行

序号函数

ROW_NUMBER()函数

ROW_NUMBER()函数能够对数据中的序号进行顺序显示。

举例：查询 goods 数据表中每个商品分类下价格降序排列的各个商品信息。

SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS
row_num,id, category_id, category, NAME, price, stock
FROM goods;

这里主要生成了一个row_num列，用于记录每个类别下的排序序号

RANK()函数

SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS
row_num,id, category_id, category, NAME, price, stock
FROM goods;

跟上面那个的主要区别是，当遇到price一样的商品，rank函数给予的序号是一样的

DENSE_RANK()函数

SELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS
row_num,id, category_id, category, NAME, price, stock
FROM goods;

主要区别是，两个89.9元的都序号是2，后面那个序号是3而不是4

分布函数

PERCENT_RANK()函数

PERCENT_RANK()函数是等级值百分比函数。按照如下方式进行计算。

其中，rank的值为使用RANK()函数产生的序号，rows的值为当前窗口的总记录数。

SELECT PERCENT_RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS pr,
id, category_id, category, NAME, price, stock
FROM goods

其实就是统计排序在同组中的百分比

CUME_DIST()函数

查询goods数据表中小于或等于当前价格的比例。

SELECT CUME_DIST() OVER(PARTITION BY category_id ORDER BY price ASC) AS cd,
id, category, NAME, price
FROM goods;

grant all privileges on *.* to root@"%" identified by '123456' with grant option;

独憩

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL基础（六）流程控制、游标、触发器、窗口函数

定义条件就是给MySQL中的错误码命名，这有助于存储的程序代码更清晰。它将一个错误名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的 DECLARE HANDLER 语句中。DECLARE 错误名称 CONDITION FOR 错误码（或错误条件）错误码的说明： MySQL_error_code 和 sqlstate_value 都可以表示MySQL的错误。MySQL_error_code是数值类型错误代码。sqlstate_value是长度为5的字符串类型错误代码。举例。
复制链接

扫一扫