本篇为MySQL高级SQL编程完整学习笔记主要讲解存储过程、存储函数、游标、条件处理程序以及触发器。从基础概念、语法示例到踩坑点结合可执行SQL案例配套16道高频面试题方便复习巩固可直接用于期末备考与面试复习。1 学习目标1. 了解存储过程的特点和优缺点2. 掌握创建、调用、查看和删除存储过程的语法3. 掌握MySQL中的变量类型4. 掌握系统变量、用户自定义变量和局部变量的使用方法5. 掌握SQL编程中涉及到的语法规则包括条件判断、参数分类、选择分支、循环6. 掌握游标的定义与使用7. 掌握条件处理程序的定义与使用8. 掌握存储函数的定义与使用9. 可以根据实际业务编写存储过程10. 掌握触发器的使用场景与分类11. 掌握触发器的创建、查看和删除方法12. 可以根据实际业务定义触发器2 面试问题1. 存储过程的作用是什么2. 如何创建一个存储过程3. MySQL中的变量都有哪几种4. 如何定义一个变量5. MySQL中使用变量是否需要提前声明6. MySQL中的参数分为哪几种7. 用过游标吗游标的作用是什么8. 了解条件处理程序吗介绍一下如何使用9. 存储函数与存储过程的区别是什么10. 如何查看数据库中创建的存储过程11. 什么是触发器12. MySQL中触发器分为几种类型13. 行级触发器与语句级触发器的区别是什么14. 说一下了解的触发器使用场景都有哪些15. 如果对一表中的数据进行更新要在日志表中记录该条记录更新前与更新后的值如何实现16. 如何查看数据库中创建的触发器3 存储过程3.1 存储过程是什么存储过程是一组为了完成特定功能的SQL语句集经编译后存储在数据库中用户通过指定存储过程的名字和参数来执行并获取相应的结果。3.2 特点1. 封装性将业务逻辑封装在数据库内部减少应用程序的复杂性。2. 可维护性集中管理数据库操作便于维护和更新。3. 可重用性可以被多次调用提高代码的重用性。3.3 优缺点优点1. 性能优化存储过程在创建时编译并存储在数据库中执行速度比单个SQL语句快。2. 代码重用存储过程可以重复调用减少重复代码提高代码的可维护性。3. 安全性可以限制用户直接访问数据库通过存储过程间接访问从而保证系统安全性。4. 事务管理可以在存储过程中实现复杂的事务逻辑。5. 降低耦合当表结构发生变化时只需要修改相应的存储过程应用程序的改动较小。缺点1. 可移植性差存储过程不能跨数据库移植更换数据库时需要重新编写。2. 调试困难只有少数数据库管理系统支持存储过程的调试开发和维护困难。3. 不适合高并发场景在高并发场景下存储过程可能会增加数据库的压力难以维护。3.4 语法3.4.1 创建创建存储过程前要修改SQL语句结束标识符DELIMITER //因为存储过程内部SQL用;会提前终止创建语句。-- 修改SQL语句结束标识符为 // DELIMITER // -- 创建存储过程 CREATE PROCEDURE 存储过程名 (参数列表) BEGIN -- SQL语句 END // -- 修改SQL语句结束标识符为 ; DELIMITER ;3.4.2 调用CALL 存储过程名 (参数列表);3.4.3 查看-- 查看指定数据库中创建的存储过程 SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA 数据库名; -- 查看存储过程的定义 SHOW CREATE PROCEDURE 存储过程名;3.4.4 删除DROP PROCEDURE [IF EXISTS] 存储过程名;3.5 示例-- 创建存储过程 CREATE PROCEDURE p_calAvg() BEGIN select name, chinese math english as total from exam; END; -- 调用存储过程 CALL p_calAvg();4 变量MySQL变量分为三类系统变量、用户自定义变量、局部变量。4.1 系统变量系统变量是MySQL服务器的配置变量控制服务器行为和性能。分为全局变量GLOBAL、会话变量SESSION。4.1.1 查看系统变量-- 查看所有系统变量 SHOW [GLOBAL|SESSION] VARIABLES; -- 查看指定的系统变量模糊查询 SHOW [GLOBAL|SESSION] VARIABLES LIKE xxx; -- 使用SELECT查看指定系统变量 SELECT [GLOBAL|SESSION].系统变量名; --示例查看以auto开头的全局系统变量 SHOW GLOBAL VARIABLES LIKE auto%; --示例查看以char开头的会话系统变量 SHOW SESSION VARIABLES LIKE char%; --示例查看事务自动提交全局系统变量 SELECT GLOBAL.autocommit;4.1.2 设置系统变量SET [GLOBAL|SESSION] 系统变量名 值; SET [SESSION].系统变量名 值; --示例设置事务自动提交会话变量为关闭/开启 SET SESSION.autocommit 0; SET autocommit 1;注意1. 不写GLOBAL/SESSION默认设置会话SESSION变量2. 会话关闭SESSION变量失效MySQL重启GLOBAL变量失效永久生效需要改配置文件。4.2 用户自定义变量用户自定义变量在SQL会话中定义不需要提前声明作用域当前会话。4.2.1 赋值--方式1 SET SET var_name expr [, var_name] ...; --方式2 推荐区分赋值和等于比较 SET var_name : expr [, var_name] ...; --方式3 select SELECT var_name : expr [, var_name] ...; --方式4 查询结果赋值 SELECT 列名 INTO var_name FROM 表名 WHERE ...;示例-- 方式1给用户变量age赋值为18 SET age : 18; -- 查看变量的值输出18 SELECT age; -- 查询student表id1的sno字段把查询出来的结果存入变量sno SELECT sno INTO sno FROM student WHERE id 1; -- 打印sno的值就是id1学生的sno SELECT sno; -- 统计student表总记录行数存入变量count SELECT count(*) INTO count FROM student; -- 输出学生总条数 SELECT count; -- count(*)一定有返回结果表空返回0所以count一定会被赋值。 --未赋值变量返回NULL SELECT var;注意SQL中等号既是比较运算符又是赋值自定义变量赋值推荐使用 :4.3 局部变量局部变量只在存储过程/函数/触发器内有效必须用DECLARE声明作用域BEGIN ... END块内部。4.3.1 声明DECLARE 变量名 变量类型 [DEFAULT 默认值] ...;4.3.2 赋值SET var_name 值; SET var_name : 值; SELECT 列名 INTO var_name FROM 表名 WHERE ...;示例CREATE PROCEDURE p1() BEGIN --定义局部变量指定默认值 DECLARE stu_count INT DEFAULT 0; --把查询结果赋值给局部变量 select count(*) into stu_count from student; --使用局部变量 select stu_count; END; call p1();4.4 注意事项1. 变量名不区分大小写。2. 存储过程/函数中局部变量必须先声明后使用。3. 用户自定义变量会话结束失效局部变量存储过程执行结束失效。4. 不要使用MySQL保留字作为变量名。5 SQL编程存储过程内部逻辑5.1 IF条件判断5.1.1 语法IF 条件1 THEN ... [ELSEIF 条件2 THEN ...] [ELSE ...] END IF;练习示例DELIMITER // CREATE PROCEDURE p2() BEGIN --定义初始分数变量 DECLARE score INT DEFAULT 86; --定义等级结果变量 DECLARE result VARCHAR(10); IF score 90 THEN SET result : 优秀; ELSEIF score 80 AND score 90 THEN SET result : 良好; ELSEIF score 60 AND score 80 THEN SET result : 及格; ELSE SET result : 不及格; END IF; SELECT result; END // DELIMITER ; CALL p2();5.2 参数5.2.1 参数分类类型说明IN输入类型调用存储过程传入值默认参数类型OUT输出类型可以作为存储过程返回值INOUT输入输出类型既做输入也可以输出5.2.2 语法DELIMITER // CREATE PROCEDURE 存储过程名 ([IN/OUT/INOUT 参数名 参数类型], ...) BEGIN --SQL语句 END // DELIMITER ;示例分数等级传入分数输出等级DELIMITER // CREATE PROCEDURE p3(IN score INT, OUT result VARCHAR(10)) BEGIN IF score 90 THEN SET result : 优秀; ELSEIF score 80 AND score 90 THEN SET result : 良好; ELSEIF score 60 AND score 80 THEN SET result : 及格; ELSE SET result : 不及格; END IF; END // DELIMITER ; --调用 CALL p3(98,res); SELECT res;INOUT示例分数加10分DELIMITER // CREATE PROCEDURE p4(INOUT score INT) BEGIN SET score : score 10; END // DELIMITER ; SET score :98; CALL p4(score); SELECT score;5.3 CASE分支语法一等值匹配CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] ... [ELSE statement_list] END CASEcase_value 和when_value做等于比较匹配到就执行THEN都不匹配执行ELSE。没有ELSE会报错。语法二条件表达式匹配CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list] ... [ELSE statement_list] END CASE每个WHEN写布尔条件哪个条件为真执行对应THEN。示例1状态码含义DELIMITER // CREATE PROCEDURE p5(IN code INT, OUT result varchar(50)) BEGIN CASE code WHEN 0 THEN SET result : 成功; WHEN 10001 THEN SET result : 用户名或密码错误; WHEN 10002 THEN SET result : 您没有对应的权限请联系管理员; WHEN 20001 THEN SET result : 你传入的参数有误; WHEN 20002 THEN SET result : 没有找到相应的结果; ELSE SET result : 服务器错误请联系管理员; END CASE; END // DELIMITER ; CALL p5(10001,result); SELECT result;示例2月份判断季度DELIMITER // CREATE PROCEDURE p6(IN month INT, OUT result varchar(50)) BEGIN CASE WHEN month 1 AND month 3 THEN SET result : 第一季度; WHEN month 4 AND month 6 THEN SET result : 第二季度; WHEN month 7 AND month 9 THEN SET result : 第三季度; WHEN month 10 AND month 12 THEN SET result : 第四季度; ELSE SET result : 非法参数; END CASE; END // DELIMITER ; CALL p6(6,result); SELECT result;注意statement_list可以多条SQL不能空可以用BEGIN...END块没有ELSE匹配不到会报错。5.4 循环5.4.1 WHILE循环先判断条件条件成立执行循环体。WHILE search_condition DO statement_list END WHILE;示例1累加到nDELIMITER // CREATE PROCEDURE p7(IN n INT) BEGIN DECLARE total INT DEFAULT 0; WHILE n 0 DO SET total : total n; SET n : n - 1; END WHILE; SELECT total; END // DELIMITER ; CALL p7(100);5.4.2 REPEAT循环先执行一次循环体再判断条件条件成立UNTIL退出循环类似do‑while。REPEAT statement_list UNTIL search_condition END REPEAT;示例累加1~nDELIMITER // CREATE PROCEDURE p8(IN n INT) BEGIN DECLARE total int DEFAULT 0; REPEAT SET total : total n; SET n : n - 1; UNTIL n 0 END REPEAT; SELECT total; END // DELIMITER ; CALL p8(100);5.4.3 LOOP循环无限循环依靠LEAVE退出breakITERATE继续下一轮continue需要标签label。• LEAVE label退出整个循环• ITERATE label结束本次循环直接下一轮语法[begin_label:] LOOP statement_list END LOOP [end_label]示例11累加到nDELIMITER // CREATE PROCEDURE p9(IN n INT) BEGIN DECLARE total int DEFAULT 0; sum_label: LOOP IF n 0 THEN LEAVE sum_label; END IF; SET total : total n; SET n : n - 1; END LOOP sum_label; SELECT total; END // DELIMITER ; CALL p9(100);示例2累加1‑n之间偶数DELIMITER // CREATE PROCEDURE p10(IN n INT) BEGIN DECLARE total int DEFAULT 0; sum_label: LOOP IF n 0 THEN LEAVE sum_label; END IF; --奇数直接跳过本次循环 IF n % 2 1 THEN SET n : n -1; ITERATE sum_label; END IF; SET total : total n; SET n : n - 1; END LOOP sum_label; SELECT total; END // DELIMITER ; CALL p10(100);5.5 游标cursorMySQL游标存储过程/函数中对select查询结果集逐行读取只读不能更新数据。声明顺序变量 → 游标 → 条件处理程序handler完整流程DECLARE声明游标 → OPEN打开游标 → FETCH INTO读取一行 → CLOSE关闭游标语法--声明游标 DECLARE 游标名 CURSOR FOR 查询语句; --打开游标 OPEN 游标名; --读取一行数据到变量 FETCH 游标名 INTO 变量1[,变量2...]; --关闭游标 CLOSE 游标名;坑游标读完所有行继续FETCH会抛出 NOT FOUND 1329错误需要搭配条件处理程序handler捕获。错误版本死循环报错DELIMITER // CREATE PROCEDURE p11(IN class_id INT) BEGIN DECLARE student_name VARCHAR(20); DECLARE class_name VARCHAR(20); DECLARE s_cursor CURSOR FOR select s.name student_name, c.name class_name from student s, class c where s.class_id c.id and s.class_id class_id; DROP TABLE IF EXISTS t_student_class; CREATE TABLE IF NOT EXISTS t_student_class ( id BIGINT PRIMARY KEY AUTO_INCREMENT, student_name VARCHAR(20), class_name VARCHAR(20) ); OPEN s_cursor; WHILE TRUE DO FETCH s_cursor INTO student_name, class_name; INSERT INTO t_student_class VALUES (NULL, student_name, class_name); END WHILE; CLOSE s_cursor; END // DELIMITER ; --调用报错 ERROR 1329 No data - zero rows fetched5.6 条件处理程序handler遇到警告/错误的时候定义处理逻辑避免存储过程异常崩溃。DECLARE handler_action HANDLER FOR condition_value [, condition_value] ... statement;• handler_actionCONTINUE继续往下执行EXIT终止当前BEGIN‑END块• condition_valuemysql_error_code错误码SQLSTATE 状态码SQLWARNING所有01开头警告NOT FOUND所有02开头游标读取完毕就是NOT FOUNDSQLEXCEPTION没有被上面捕获的错误游标修复完整版搭配handler-- 修改语句结束符为 //避免存储过程内部 ; 提前结束整个存储过程定义 DELIMITER // -- 创建存储过程 p12传入输入参数 class_id 整型代表班级编号 CREATE PROCEDURE p12(IN class_id INT) BEGIN -- 定义局部变量保存游标读取出来的学生姓名 DECLARE student_name VARCHAR(20); -- 定义局部变量保存游标读取出来的班级名称 DECLARE class_name VARCHAR(20); --定义结束标记变量布尔类型默认falsefalse代表游标还有数据true代表游标数据读完 DECLARE is_done bool DEFAULT FALSE; --1.声明游标游标相当于结果集指针 --查询指定班级下学生姓名、班级名称把查询结果交给游标s_cursor管理 DECLARE s_cursor CURSOR FOR select s.name student_name, c.name class_name from student s, class c where s.class_id c.id and s.class_id class_id; --2.条件处理程序游标FETCH取不到数据(遍历完毕触发NOT FOUND) --设置is_done true并且CONTINUE表示不报错继续往下执行代码 DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_done : TRUE; -- 如果表t_student_class存在就删除 DROP TABLE IF EXISTS t_student_class; -- 创建存放结果的表id自增主键存学生名、班级名 CREATE TABLE IF NOT EXISTS t_student_class ( id BIGINT PRIMARY KEY AUTO_INCREMENT, student_name VARCHAR(20), class_name VARCHAR(20) ); OPEN s_cursor; -- 打开游标执行游标绑定的select查询拿到结果集 read_loop: LOOP -- 开启循环循环标签 read_loop方便跳出循环 -- 从游标当前行取出数据赋值给局部变量 student_name, class_name FETCH s_cursor INTO student_name, class_name; -- 判断标记如果is_done为true说明游标全部读取完毕跳出循环 IF is_done THEN LEAVE read_loop; END IF; -- 把拿到的学生、班级名字插入结果表id传NULL使用自增 INSERT INTO t_student_class VALUES (NULL, student_name,class_name); END LOOP read_loop; -- 循环结束回到LOOP开头继续下一轮 CLOSE s_cursor; --关闭游标释放游标资源 END // DELIMITER ; -- 把结束符改回分号; --调用存储过程传入班级id1执行全部逻辑 CALL p12(1); SELECT * FROM t_student_class;5.7 存储函数 function存储函数必须有返回值参数只能IN类似内置函数用SELECT 函数()调用。binlog开启的时候必须指定characteristic属性。语法CREATE FUNCTION 函数名称 ([参数列表]) RETURNS type [characteristic ...] BEGIN --SQL语句 RETURN ...; END;characteristic属性1. DETERMINISTIC相同输入总是返回相同结果2. NO SQL不包含SQL3. READS SQL DATA包含查询select4. MODIFIES SQL DATA包含写操作update delete示例1累加到nDELIMITER // CREATE FUNCTION fun1(n INT) RETURNS INT DETERMINISTIC BEGIN DECLARE total int DEFAULT 0; WHILE n 0 DO SET total : total n; SET n : n - 1; END WHILE; RETURN total; END // DELIMITER ; SELECT fun1(100);存储过程 vs 存储函数区别1. 存储过程用CALL调用参数支持IN/OUT/INOUT可以没有返回值2. 存储函数SELECT调用只能IN参数必须RETURN返回一个值。6 触发器 trigger触发器和表绑定当表发生INSERT / UPDATE / DELETE自动执行预设SQL。触发时间BEFORE操作之前 / AFTER操作之后MySQL只支持行级触发器 FOR EACH ROW每一行变化触发一次没有语句级触发器。关键字• NEW新增/修改后的新数据INSERT、UPDATE可以读取NEW• OLD修改前、被删除的旧数据UPDATE、DELETE可以读取OLD触发器类型OLDNEWINSERT无NEW代表新增行UPDATEOLD修改前数据NEW修改之后数据DELETEOLD被删除的数据无6.1 语法--创建 CREATE TRIGGER [IF NOT EXISTS] trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW BEGIN trigger_stmt; END; --查看 SHOW TRIGGERS; --删除 DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;trigger_timeBEFORE | AFTERtrigger_eventINSERT | UPDATE | DELETE6.2 练习记录学生表操作日志--日志表 create table student_log ( id bigint primary key auto_increment, operation_type varchar(10) not null comment 操作类型:insert/update/delete, operation_time datetime not null comment 操作时间, operation_id bigint not null comment 操作的记录ID, operation_data varchar(500) comment 操作数据 );6.3 INSERT触发器-- 修改语句结束符为//防止触发器内部分号;提前结束定义 DELIMITER // -- 创建触发器 trg_student_insert -- AFTER INSERT在student表**插入数据完成之后**触发 -- FOR EACH ROW行级触发器每插入1行触发器就执行1次MySQL只有行级触发器 create trigger trg_student_insert after insert on student for each row begin -- 向日志表student_log写入一条操作记录 insert into student_log (operation_type,operation_time,operation_id,operation_data) values ( insert, -- 操作类型新增 now(), -- 操作发生时间当前系统时间 new.id, -- 新插入学生记录的idNEW代表刚插入的那一行数据 -- concat拼接新行全部字段把多个字段拼成一个字符串存入日志 concat(new.id,,,new.name,,,new.sno,,,new.age,,,new.gender,,,new.enroll_date,,,new.class_id) ); end // DELIMITER ; -- 恢复结束符为分号; --测试向student插入一条学生数据 insert into student values (null , 曹操, 300001, 28, 1, 2024‑09‑01,3); -- 查询日志表查看触发器自动生成的日志记录 select * from student_log;6.4 UPDATE触发器DELIMITER // create trigger trg_student_update after update on student for each row begin insert into student_log (operation_type,operation_time,operation_id,operation_data) values ( update, now(), new.id, concat(old.id,,,old.name,,,old.sno,,,old.age,,,old.gender,,,old.enroll_date,,,old.class_id, ||, new.id,,,new.name,,,new.sno,,,new.age,,,new.gender,,,new.enroll_date,,,new.class_id) ); end // DELIMITER ; --测试 update student set age 20, class_id2 where name曹操; select * from student_log;6.5 DELETE触发器DELIMITER // create trigger trg_student_delete after delete on student for each row begin insert into student_log (operation_type,operation_time,operation_id,operation_data) values ( delete, now(), old.id, concat(old.id,,,old.name,,,old.sno,,,old.age,,,old.gender,,,old.enroll_date,,,old.class_id) ); end // DELIMITER ; --测试 delete from student where name曹操; select * from student_log;7 面试问题解答1. 存储过程的作用是什么把一组业务SQL封装编译存储在数据库可以重复调用减少重复代码封装业务逻辑提升复用性。2. 如何创建存储过程修改结束符DELIMITERCREATE PROCEDURE 名(参数) BEGIN ... END改回分号结束符CALL调用。3. MySQL中的变量都有哪几种系统变量(GLOBAL全局、SESSION会话)、用户自定义变量var、局部变量DECLARE存储过程内部。4. 如何定义一个变量系统变量SET xxx用户变量SET a : 10局部变量DECLARE a INT DEFAULT 0只能在BEGIN‑END内部。5. MySQL中使用变量是否需要提前声明局部变量DECLARE必须声明用户自定义变量不需要声明直接赋值使用。6. MySQL中的参数分为哪几种IN输入OUT输出INOUT输入输出。7. 用过游标吗游标的作用是什么游标用来逐行读取select查询的结果集注意读完会NOT FOUND报错要搭配DECLARE HANDLER捕获异常。8. 了解条件处理程序吗介绍一下如何使用DECLARE ... HANDLER FOR NOT FOUND捕获存储过程内部异常选择CONTINUE继续/EXIT退出块用来处理游标读完、SQL报错。9. 存储函数与存储过程的区别是什么存储过程CALL调用支持IN/OUT/INOUT存储函数SELECT调用只能IN必须RETURN返回值。10. 如何查看数据库中创建的存储过程SHOW CREATE PROCEDURE xxx查询information_schema.ROUTINES系统表。11. 什么是触发器绑定表当发生insert/update/delete自动执行SQL用于日志记录、数据校验。12. MySQL中触发器分为几种类型INSERT触发器、UPDATE触发器、DELETE触发器分为BEFORE / AFTER触发时机。13. 行级触发器与语句级触发器的区别是什么行级FOR EACH ROW每受影响一行触发一次语句级整条SQL只触发一次。MySQL只支持行级触发器。14. 触发器使用场景数据变更日志记录、数据校验、数据同步、备份变更数据。15. 更新一条记录日志表记录更新前后的值怎么实现AFTER UPDATE触发器OLD拿修改前NEW拿修改后把concat拼接新旧字段插入日志表。16. 如何查看触发器SHOW TRIGGERS;