当前位置：首页 > ops >正文

MySQL高级进阶（流程控制、循环语句、触发器）

ops 2025/9/6 8:32:59

一、流程控制语句

1.条件语句

IF语句：在函数或存储过程中使用
语法：

IF 条件 THEN
语句;[ELSEIF 条件2 THEN
语句2;ELSE 语句n;]END IF；

2.循环语句

用于函数和存储过程中

while 循环
语法：

while 条件 do
循环体;end while;

repeat循环
语法：

repeat 
循环体;until 条件  -- 循环退出条件，与while条件相反
end repeat;

repeat循环
语法：

标识名:loop
循环体;leave 标识名
end loop;

循环体验：使用循环完成从1累加到100

-- while 实现从1+2+...+100DROP PROCEDURE if EXISTS proc_getsum1;create PROCEDURE proc_getsum1()BEGINDECLARE i int default 1; -- 1.....100DECLARE sum int default 0;WHILE i<=100 DOset sum=sum+i;set i=i+1;end WHILE;SELECT sum as whilesum;end;CALL proc_getsum1();-- REPEAT 实现从1+2+...+100DROP PROCEDURE if EXISTS proc_getsum2;create PROCEDURE proc_getsum2()BEGINDECLARE i int default 1; -- 1.....100DECLARE sum int default 0;REPEATSET sum=sum+i;set i=i+1;UNTIL i>100  -- 注意：这里没有; 号
END REPEAT;SELECT sum as repeatsum;end;CALL proc_getsum2();-- loop 实现从1+2+...+100DROP PROCEDURE if EXISTS proc_getsum3;create PROCEDURE proc_getsum3()BEGINDECLARE i int default 1; -- 1.....100DECLARE sum int default 0;
myloop:LOOP  -- 声明标识名
SET sum=sum+i;set i=i+1;IF i>100 THEN  -- 判断循环变量是否到100以上
Leave myloop;  -- 离开循环
END if;END LOOP;SELECT sum as loopsum;end;CALL proc_getsum3();

流程控制语句练习：

创建存储过程，用于查询指定航班，指定舱位等级的剩余座位，如果少于5则显示‘舱位较少’，否则显示‘舱位充足’，如：查询MU294，头等舱的座位信息

sql代码：

DROP PROCEDURE IF EXISTS checkSeatAvailability;
CREATE PROCEDURE checkSeatAvailability(fid VARCHAR(20), level VARCHAR(20))
BEGINSELECT *,CASE WHEN availableSeats < 5 THEN '舱位较少' ELSE '舱位充足' END resultFROM cabin WHERE flightid = fidAND grade = level;
end;
call checkSeatAvailability('MU294','头等舱');

在这里插入图片描述
2. 暑假座舱涨价，需更新价格，头等舱统一上浮10%，商务舱统一上浮8%，经济舱统一上浮5%
sql代码：

UPDATE cabin set fullPrice = 
case grade 
WHEN '头等舱' then fullPrice * 1.1
WHEN '商务舱' then fullPrice * 1.08
when  '经济舱' THEN fullPrice * 1.05
ELSE fullPrice
end;SELECT * FROM cabin;

在这里插入图片描述
3. 创建存储过程，使用循环计算从2000到3000年一共有多少个闰年

sql代码：

drop PROCEDURE if EXISTS proc_cal;
CREATE PROCEDURE proc_cal()
BEGINDECLARE year int DEFAULT 2000; -- 年份DECLARE sum int DEFAULT 0; -- 统计数量WHILE YEAR <= 3000 DO if year % 4 = 0 and year%100!=0 or year % 400 = 0 THENset sum=sum+1;end if;set year = year +1;
end WHILE;SELECT sum '闰年数量';
end;

在这里插入图片描述

二、触发器

1、触发器概念

触发器是一种特殊的存储过程，它在试图更改触发器所保护的数据时自动执行。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作；
注意：

在MySQL中，只有执行insert,delete,update操作时才能触发触发器的执行；
使用别名OLD和NEW来引用触发器中发生变化的记录内容。只支持行级触发，不支持语句级触发；

触发器的特性：

什么条件会触发：执行 Insert、Delete、Update语句时
什么时候触发:在增删改前before或者后after
触发频率：针对每一行执行
触发器定义在表上，附着在表上

语法：

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件 ON 表名 FOR EACH ROWBEGIN -- 当只有一条执行语句时，begin..end可以省略-- 执行语句;END

触发器练习题：

1、编写触发器，当销售一个航班的座位后，cabin表的可用座位就减少一个
如：原cabin中MU294 经济舱可用座位数：
在这里插入图片描述
在ticketSell表中插入MU294经济舱的销售数据后，经济舱可用座位数：

在这里插入图片描述
分析：要实现的功能是当向ticketsell表插入数据后，更新cabin表的数据。所以触发器应绑定在ticketsell
表的insert事件后（after），触发器需要做的事情是更新cabin表的availableseats字段。
sql代码：

cabin表的availableseats字段。
drop table if EXISTS mylogs;
create table mylogs (id int PRIMARY KEY AUTO_INCREMENT,ordersid INT,identityid VARCHAR(50),flightid VARCHAR(10),flightdate datetime,logDate datetime DEFAULT CURRENT_TIMESTAMP
);

2、编写触发器，当删除销售数据后，为防止误操作，将删除的数据备份在日志表中（先创建日志表，参考案例）如：删除订单ordersid36的数据后，在日志表中保存原数据中的订单号，身份证号，航班，飞行日期及删除时间：
sql 代码：

drop TRIGGER if EXISTS t1;
CREATE TRIGGER t1 AFTER DELETE
on ticketsell for EACH ROW 
BEGIN INSERT mylogs VALUE(null,old.ordersID,old.identityID,
old.flightid,old.flightdate,DEFAULT);
end;DELETE from ticketsell where ordersid = 1;
SELECT * from mylogs;

在这里插入图片描述
3、更改第二题的触发器，删除订单后，除了要备份数据至日志表外，原cabin表中可用座位要还原如：删除MU294 经济舱的销售数据后原cabin中MU294 经济舱可用座位数
sql 代码：

drop TRIGGER if EXISTS t1;
CREATE TRIGGER t1 AFTER DELETE
on ticketsell for EACH ROW 
BEGIN INSERT mylogs VALUE(null,old.ordersID,old.identityID,
old.flightid,old.flightdate,DEFAULT);UPDATE cabin set availableSeats = seatswhere flightid =old.flightid and grade = old.grade;
end;DELETE from ticketsell where ordersid = 2;
SELECT * from mylogs;
SELECT * from cabin