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

一、流程控制语句

1.条件语句

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

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

2.循环语句

用于函数和存储过程中

1. while 循环
   语法：

while 条件 do
循环体;
 end while;

2. repeat循环
   语法：

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

3. repeat循环
   语法：

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

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

-- while 实现从1+2+...+100
 DROP PROCEDURE if EXISTS proc_getsum1;
 create PROCEDURE proc_getsum1()
 BEGIN
 DECLARE i int default 1; -- 1.....100
 DECLARE sum int default 0;
 WHILE i<=100 DO
 set sum=sum+i;
 set i=i+1;
 end WHILE;
 SELECT sum as whilesum;
 end;
 CALL proc_getsum1();-- REPEAT 实现从1+2+...+100
 DROP PROCEDURE if EXISTS proc_getsum2;
 create PROCEDURE proc_getsum2()
 BEGIN
 DECLARE i int default 1; -- 1.....100
 DECLARE sum int default 0;
 REPEAT
 SET sum=sum+i;
 set i=i+1;
 UNTIL i>100  -- 注意：这里没有; 号
END REPEAT;
 SELECT sum as repeatsum;
 end;
 CALL proc_getsum2();-- loop 实现从1+2+...+100
 DROP PROCEDURE if EXISTS proc_getsum3;
 create PROCEDURE proc_getsum3()
 BEGIN
 DECLARE i int default 1; -- 1.....100
 DECLARE 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();

流程控制语句练习：

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

DROP PROCEDURE IF EXISTS checkSeatAvailability;
CREATE PROCEDURE checkSeatAvailability(fid VARCHAR(20), level VARCHAR(20))
BEGIN
    SELECT *,
    CASE 
		WHEN availableSeats < 5 THEN '舱位较少' 
    ELSE '舱位充足' 
		END result
    FROM cabin 
    WHERE flightid = fid
    AND 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()
BEGIN
 DECLARE 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 THEN
		set 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 ROW
 BEGIN -- 当只有一条执行语句时，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 = seats
	where flightid =old.flightid and grade = old.grade;
end;

DELETE from ticketsell where ordersid = 2;
SELECT * from mylogs;
SELECT * from cabin