一.存储过程
1.存储过程是什么
存储过程是为了完成特定功能的SQL语句集，编写完成后存储在数据库当中，用户根据存储过程的名字和参数来执行并获得相对应的结果
简单来说其实就是c++和java中的方法
2.特点
1.封装性：将业务逻辑封装在了数据库内部，降低了应用程序的复杂性
2.可维护性：集中管理数据库操作，便于维护和更新
3.可复用性：可以被多次调用，提高代码的复用性
3.优缺点
优点：
1.性能优化：存储过程执行的速度比单个SQL语句快
这是因为在存储过程第一次创建时，MySQL就会先预编译并存储在数据库中；而单条SQL语句在执行过程中，还要进行解析
2.安全性：用户只能够间接访问到数据库
3.代码复用：可以被重复调用，减少了重复代码，提高了可维护性
4.事务管理：在存储过程中可以实现复杂的事务逻辑
5.降低耦合：底层表结构发生变化时，只需要去修改存储过程，应用程序改动小
缺点：
1.可移植性差：存储过程不能跨数据库移植，更换数据库时需要重新编写
2.调试困难：支持存储过程调试的数据库比较少，开发和维护困难
3.不适合高并发场景：在高并发场景中，存储过程可能会增加数据库压力
（数据库平时进行增，删，改的业务量就已经很高了，如果在高并发场景还要去执行存储过程，数据库压力会非常大，所以在平常会将需要存储过程处理的业务程序给移动到应用程序当中）
4.语法
1> 创建
delimiter //（将结束符改为 //）
create procedure存储过程名（[参数列表]）
begin
SQL语句;
end//
delimiter ; （将结束符改为 ;）
2> 调用
call存储过程名(参数);
3> 查看
---- 查看存储过程名
select*fromimformation_schema.routineswhereroutines_schema = '数据库名';
---- 查看创建存储过程的SQL语句
show create procedure存储过程名;
4> 删除
drop procedure[ifexists] 存储过程名;
在创建过程中为什么要将结束符进行修改呢？
这是因为在命令行中执行创建存储过程时是以" ; "结束的，所以在创建的过程中就会结束，造成语法错误；虽然在可视化工具中会自动对这种情况进行优化，就算不修改结束符也可以正常编译，但是在编写完脚本之后，这些脚本上传到服务器是统一在命令行环境中执行的，所以还是推荐在创建存储过程中都进行修改。
5.使用
二.变量
1.系统变量
系统变量是MySQL服务器的配置变量，控制服务器的性能和行为，分为全局变量(GLOBAL)和会话变量(SESSION)
例子：在微信中我的昵称叫张三，在微信群中我可以将我的群昵称改为李四，那么张三就是一个全局变量，李四就是一个会话变量。
全局变量就是在启动服务器时会读取的值，会话变量可以基于全局变量去针对每个会话进行修改。比如在开启会话时，autocommit默认是ON，我们就可以针对会话将其修改成OFF
查看系统变量
--查看所有的系统变量
show[global| session] variables;
-- 查看指定的系统变量
show[global| session] variables 'xxx';
-- 查看指定的系统变量，可以用%进行模糊查询
show[global| session] variables '%xxx%';
-- 用select查看指定的系统变量
select@@[global| session].xxx;
设置系统变量
set[global| session] 系统变量名 = 值;
set@@[global| session].系统变量 = 值;
注意：
1>.如果没有指定global或者session默认为session
2>.会话关闭时，设置的会话变量会失效，新建会话时读取全局变量为初始值
3>.MySQL重庆时，设置的全局变量会失效，要永久生效需要修改配置文件
2.用户自定义变量
用户自定义变量是在SQL会话定义的变量，不需要提前声明，作用域为当前会话
赋值
-- 方式一
SET@var_name = expr [,@var_name]...;
-- 方式二【推荐】
SET@var_name := expr [,@var_name]...;
-- 方式三 在SELECT语句中
SELECT@var_name := expr [,@var_name]...;
-- 方式四 查询结果赋值给⾃定义变量
SELECT列名 INTO@var_name FROM 表名 WHERE ...;
推荐使用方法二和方法四，因为在MySQL中“ = ”有赋值和判等的作用，所以用“ := ”来明确赋值操作
3.局部变量
局部变量是定义在存储过程，函数和触发器中的，需要提前声明，作用域在声明的BEGIN.....END块中
语法
声明
declarevar_name 类型 [default 默认值];
赋值
-- 方式一
set变量名 := 值;
-- 方式二
select列名intovar_namefrom表名where...... ;
4.注意事项
1>.变量名不区分大小写
2>.在存储过程和函数中，局部变量必须在使用前声明（在触发器中，自定义的局部变量也是要提前声明的；只不过在触发器有专属的new和old变量，这些时MySQL的内置变量不需要我们声明）
3>.用户自定义变量在会话结束后生效，局部变量在存储过程和函数结束时失效
4>.避免使用保留字作为变量名（比如select这种已经定义好的有特殊意义的字段就叫作保留字）
三.SQL编程
1.条件判断 —— if语句
语法
if 条件1then
.......
[elseif 条件2then
......
else
......]
endif;
示例
2.参数
分类
1>.IN 输入类型，调用存储过程时要传入的值（值类型）
2>. OUT 输出类型，作为存储过程中的返回值
3>.INOUT 输入输出类型，既可以作为传入的值也可以作为返回值（引用类型）
注：
值类型：在方法中对变量进行的修改，只在方法中生效，在外部不生效
引用类似；在方法中对变量进行的修改，外部可以访问到修改后的值
语法
DELIMITER //
-- 创建存储过程
CREATEPROCEDURE存储过程名 ([IN/OUT/INOUT 参数名 参数类型] [,...])
BEGIN
-- SQL 语句
END //
DELIMITER ;
示例
3.case语句
语法
语法一
----- 只有当case_value 等于对应的when_value时才会执行对应的statement_list
casecase_value
whenwhen_valuethenstatement_list;
[whenwhen_valuethenstatement_list;].......
[elsestatement_list;]
endcase;
语法二
-- 只有当search_condition满足时才会执行对应的statement_list
case
whensearch_conditionthenstatement_list;
[whensearch_conditionthenstatement_list;].......
[elsestatement_list;]
endcase;
示例
传入⼀个状态码，输出该状态码表示的含义
根据传入的月份，输出该月份属于哪个季度
注意事项
1.多分支或者多判断语句可以用case语句代替if语句，case为if的简化版本
2.SQL中的CASE不具备向下穿透的特性，在Java中的CASE语句中如果不写break会向下穿透
3.statement_list由一条或者多条SQL语句构成且不能为空，不过可以用begin.....end块来进行占位
4.若case_value和when_value都不匹配或者search_condition都不满足，并且不包含ELSE子句就会报错
4.循环
1> where
语法
WHILEsearch_conditionDO
statement_list
ENDWHILE;
示例
传入一数n，计算从1累加到n的值
2> repeat
类似do....whiel循环
语法
REPEATstatement_list
UNTILsearch_condition
ENDREPEAT;
进入循环会先执行一次stateme_list，直到满足search_condition后就不再执行，退出循环
示例
3> loop
loop也可以实现简单的循环，但是不像前面两个循环可以达到某个条件自动退出循环。使用loop时，当达到某个条件想要终止当前循环或者是退出整个循环要配合：
LEVELlabel：退出整个循环，类似JAVA中的break；
ITERATElabel：终止当前循环，类似JAVA中的continue；
语法
[begin_label]LOOP
statement_list；
ENDLOOP[end_label];
begin_label和end_label相当于给这个LOOP循环起个名字，不是必须写，但是如果都写的话名字要一样
示例
5.游标
是一个数据库对象，允许在存储过程和函数中对查询到的结果进行检索
如下图：对已经查询出来的结果集，游标就可以向下依次遍历每条数据（刚开始游标不会指向任何一条记录）
注意：MySQL的游标是只读的，不能进行更新操作
语法
1>.要在使用前声明，open，fetch，close分别是打开游标，获取游标记录和关闭游标
2>.游标必须声明在条件处理程序之前，并且局部变量必须声明在条件处理程序和游标之前
-- 声明
declare游标名cursor for查询语句; （此处只是定义游标）
-- 打开游标
open游标名; （真正的执行查询语句，并把查询语句和游标进行关联）
-- 获取游标记录
fetch游标名 into 变量[,变量......];（此处变量是提前定义好的，用于保存记录中的值）
-- 关闭游标
close游标名;
示例
-- 传入班级编号，查询学生表中属于该班级的学生信息，并将符合条件的学生信息写如到新表中
delimiter //
DROP PROCEDURE IF EXISTSp10;
CREATE PROCEDUREp10(INclass_id INT)
BEGIN
-- 1.首先声明变量
-- 学生变量
DECLAREstudent_nameVARCHAR(20);
DECLAREclass_nameVARCHAR(20);
-- 声明游标
DECLAREs_cursorCURSOR FORSELECTs.name , c.name FROM student s , class c
WHEREs.class_id = c.idANDc.id = class_id;
-- 2.创建新表
CREATE TABLEt_student_class(
idBIGINT PRIMARY KEYAUTO_INCREMENT,
student_nameVARCHAR(20),
class_nameVARCHAR(20)
);
-- 3.开启游标
OPENs_cursor;
-- 4.遍历结果集
WHILE TRUE DO(这边还不知道以什么条件结束，暂时先这样写，到后面条件处理程序会解决)
-- 5.获取游标记录
FETCHs_cursorINTOstudent_name,class_name;
-- 6.将获取到的数据插入到新表
INSERT INTOt_student_classVALUES(NULL,student_name,class_name);
END WHILE;
-- 7.关闭游标
CLOSEs_cursor;
END//
delimiter;CALLp10(1);
可以看到因为while被我们写成了一个死循环所以游标会一直向下遍历直到没有记录，所以会报错，在接下来的条件处理程序能够解决
6.条件处理程序
定义条件：事先定义程序在执行过程中可能会遇到的问题
处理程序：定义了在遇到问题时应该采取什么处理方式
其实就跟JAVA中的异常类似，遇到异常进行捕获和处理
语法
-- 声明条件处理程序
declare handler_actionHANDLER
FORcondition_value[,condition_value].....
statement;
handler_action:{
continue-- 继续执行当前程序
exit-- 终止执行当前程序
}
condition_value:{
mysql_error_code-- MYSQL错误码
|SQLSTATE[VALUE] sqlstate_value-- 状态码
|SQLWARNING-- 所有以01开头的SQLSTATE代码
|NOTFOUND-- 所有以02开头的SQLSTATE代码
|SQLEXCEPTION-- 所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码
}
condition_value就类似JAVA中要捕获哪个类型的异常
示例
现在已经了解了条件处理程序了，那就可以来解决刚才使用游标时遇到的问题了
-- 传入班级编号，查询学生表中属于该班级的学生信息，并将符合条件的学生信息写如到新表中
delimiter //
DROP PROCEDURE IF EXISTSp10;
CREATE PROCEDUREp10(INclass_id INT)
BEGIN
-- 1.首先声明变量
-- 学生变量
DECLAREstudent_nameVARCHAR(20);
DECLAREclass_nameVARCHAR(20);-- 定义游标结束标识符
DECLAREis_done boolDEFAULT TRUE;
-- 声明游标
DECLAREs_cursorCURSOR FORSELECTs.name , c.name FROM student s , class c
WHEREs.class_id = c.idANDc.id = class_id;-- 声明条件处理程序
DECLARE CONTINUE HANDLER FOR NOTfound set is_done :=FALSE;
-- 2.创建新表
CREATE TABLEt_student_class(
idBIGINT PRIMARY KEYAUTO_INCREMENT,
student_nameVARCHAR(20),
class_nameVARCHAR(20)
);
-- 3.开启游标
OPENs_cursor;
-- 4.遍历结果集
WHILEis_doneDO
-- 5.获取游标记录
FETCHs_cursorINTOstudent_name,class_name;
-- 6.将获取到的数据插入到新表
INSERT INTOt_student_classVALUES(NULL,student_name,class_name);
END WHILE;SELECT*FROMt_student_class;
-- 7.关闭游标
CLOSEs_cursor;
END//
delimiter;CALLp10(1);
那我们发现这样修改完之后虽然不报错，但是新表中却有重复的数据。
这是因为在游标向下遍历到空的记录行时，条件处理程序就会接管将is_done设置成false，然后程序需要继续向下进行，那么条件处理程序就将游标重置为了它所获取到的上一条记录的位置，也就是遍历到空记录行时又被重置为了沙悟净这条记录
那其实在is_done变为false时就应该退出循环，而不是继续进行循环，这样才不会出现错误。所以使用LOOP即可
-- 4.遍历结果集
read_label:LOOP
-- 5.获取游标记录
FETCHs_cursorINTOstudent_name,class_name;
IF NOTis_doneTHEN
LEAVEread_label;
END IF;
-- 6.将获取到的数据插入到新表
INSERT INTOt_student_classVALUES(NULL,student_name,class_name);
END LOOPread_label;
SELECT * FROMt_student_class;
这边还有一个可能会犯的错误：
如果我们把判断退出的语句放在了获取游标记录的代码之前，还是会造成插入重复数据的情况。
这是因为如果是先判断再获取记录的话，如果游标已经到了空记录行，此时条件处理程序虽然已经将is_done设置为false，可是因为判断语句在前，无法进行判断，所以此时游标就会被重置为上一条记录继续进行插入
所以应该是先获取游标记录看是否已经为空，如果为空就直接退出，就算条件处理程序重置了游标也无法进行插入，就不会出现重复数据的情况了
7.存储函数
MySQL中的存储函数相当于有返回值的存储过程，参数只能是IN类型，所以在写参数的时候IN可以省略。存储过程和存储函数的主要区别就在于存储函数一定有返回值，而存储过程不一定
语法
那么在MySQL8.0中，binlog开启时，必须指定characteristic特征，不然会报错
CREATE FUNCTION存储函数名称 ([参数列表])
RETURNStype [characteristic ...]
BEGIN
-- SQL语句
RETURN...;
END;
characteristic:
[NOT]DETERMINISTIC--表示相同的输入参数总是产生[不同]相同的结果
|NO SQL--不包含SQL语句
|READS SQLDATA--包含读取数据的语句，如select
|MODIFIES SQLDATA-- 包含写入数据的语句，如update，delete
-- 使用存储函数
select存储函数名称 ([参数列表]);
示例
四.触发器
1.什么叫触发器
是一个与表关联的对象，在进行insert，update，delete操作时，执行并触发指定触发器定义的SQL语句
可在表操作之前或之后触发，这被称为触发时间
MySQL支持三种触发器：insert触发器，update触发器，delete触发器。可以使用NEW和OLD关键字来引用触发器执行中发生变化的记录内容
行级触发器：
当进行表操作时，每影响了多少行数据，触发器就触发多少次。比如，进行了update操作影响了10行，那么触发器就会对这10条数据都触发一次。行级触发器可以获得更新前和更新后的值
语句级触发器：
当进行表操作时，无论影响了多少行数据，语句级触发器都只会在操作前或后触发一次。主要用于一些全局性的操作
注意：MySQL中只支持行级触发器，不支持语句级触发器
2.语法
-- 创建
CREATE TRIGGER[IFNOT EXISTS] trigger_name
trigger_time trigger_event
ONtbl_nameFOR EACHROW
BEGIN
trigger_stmt;
END;
trigger_time: { BEFORE | AFTER }
trigger_event: {INSERT|UPDATE|DELETE}
-- 查看
SHOWTRIGGERS;
-- 删除, 如果没有指定schema_name，默认为当前数据库
DROP TRIGGER[IFEXISTS] [schema_name.]trigger_name;
3.示例
通过触发器记录学生表的变更日志，将变更日志写入日志表student_log中，包含增加，修改和删 除操作
创建日志表
插入触发器
当我们插入数据时，插入触发器就会触发并执行定义的SQL语句
更新触发器
由于是进行更新操作，所以可以引用更新前的记录和更新后的记录
当进行多条记录的更新，每影响多少行，更新触发器就触发多少次
删除触发器
当删除记录时
总结
本文介绍了什么是存储结构，变量有哪几种，SQL编码的各个部分以及触发器。其实大部分的内容其实和JAVA的编程是类似的，只是有一些细微的差别，比如存储过程其实就算JAVA中的方法，还有在SQL编码部分提到的条件处理程序跟JAVA中的异常思路是一样的。在变量部分，关注各个变量的作用域，需不需要提前声明，最主要的和常用的其实就是局部变量。那触发器其实就可以理解是进行表操作的一个子程序，并不影响表操作这个主要程序。
