目录
存储过程：
存储过程在实际开发中，存储过程还是很少使用的。只有在系统遇到性能瓶颈，在进行优化的时候，对于大数量的应用来说，可以考虑使用一些。
优点：
速度快：降低了应用服务器和数据库服务器之间网络通讯的开销。尤其是在数据量庞大的情况下，效果更显著。
缺点：
移植性差；编写难度大；维护性差
存储过程的创建
create produce p1()
begin
select empno,ename,from emp;
end;调用存储过程
call p1();查看存储过程
方法一：查看创建存储过程的语句
show create procedure p1;
方法二：查看information_schema.routines
讲解：(替换成自己的）
select * from information_schema.routines where routine_name = 'p1';通过系统表information_schema.routines可以查看存储过程的在状态信息。
在mysql中只要创建了一个存储过程对象，在information_schema.routines系统表中就会增加一条记录，这条记录是专门描述存储过程对象的状态
information_schema.routines这个系统表当中存储的不仅包括存储过程的状态信息，也包括函数对象，触发器对象等的状态信息。
关键列：information_schema。ROUTINES表中的一些重要的列包括（不仅限以下）：
SPECIFIC_NAME：存储过程的具体名称，包括该存储过程的名字，参数列表。
ROUTINE_SCHEMA：存储过程所在的数据库名称。
ROUTINE_NAME：存储过程的名称。
ROUTINE_TYPE：PROCEDURE表示是一个存储过程，FUNCTION表示是一个函数。
ROUTINE_DEFINITION：存储过程的定义语句。
CREATED：存储过程的创建时间。
LAST_ALTERED：存储过程的最后修改时间。
DATA_TYPE：存储过程的返回值类型、参数类型等。
删除存储过程：
drop procedure if exists p1;delimiter命令
delimiter命令用于改变MySQL解释句的定界符。mySQL默认使用“；”作为语句定界符。而使用delimiter命令可以将“；”更改为其他字符。（以下例子中将以“//”为定界符。
delimiter //
CREATE PROCEDURE my_proc ()
BEGIN
SELECT * FROM my_table;
INSERT INTO my_table (col1, col2) VALUES ('value1', 'value2');
END //MySQL变量
系统变量
指在MySQL服务器运行时控制其行为的参数。这些变量可以被设置为特定的值来改变服务器的默认设置，来满足不同的需求。
系统变量可以分为：全局（globa）或会话（session）作用域：
全局作用域：指对所有连接和所有数据库都适用。
会话作用域：指只对当前连接和当前数据库适用。
查看系统变量：
show [global|session] variables;
show [global|session] variables like '';
select @@[global|session.]系统变量名;注意：没有指定session或global时，默认时session
设置系统变量：
set [global | session] 系统变量名 = 值;
set @@[global | session.]系统变量名 = 值;注意：无论是全局设置还是会话设置，mysql服务重启之后，之前配置都会失效。可以通过修改MySQL根目录下的my.ini配置文件达到永久修改的效果。但是不建议采用修改my.ini这种方式。
在my.ini修改
[mysqld]
autocommit=0用户变量
用户变量用户自定义的变量。只在当前会话有效。所有的用户变量‘@’开始。
给用户变量赋值
set @name = 'jackson';
set @age := 30;
set @gender := '男', @addr := '黄海';
select @email := 'jackson@123.com';
select sal into @sal from emp where ename ='SMITH';读取用户变量的值
select @name, @age, @gender, @addr, @email, @sal;注意：mysql中变量不需要声明。直接赋值就行。如果没有声明变量，直接读取该变量，返回null
局部变量
在存储过程中可以使用局部变量。使用declare声明。在begin和end之间有效。
变量的声明
declare 变量名 数据类型 [default ...];
变量的赋值
set 变量名 = 值;
set 变量名 := 值;
select 字段名 into 变量名 from 表名 ...;if语句
语法格式
if 条件 then
......
elseif 条件 then
......
elseif 条件 then
......
else
......
end if;案例：员工月薪sal，超过10000属于“高收入”，5000到10000属于“中收入”，少于5000的属于“低收入”。
create procedure p3()
begin
declare sal int default 5000;
declare grade varchar(20);
if sal > 10000 then
set grade := '高收入';
elseif sal >= 6000 then
set grade := '中收入';
else
set grade := '低收入';
end if;
select grade;
end;
call p3();参数
存储过程的参数包括三种形式：
in：入参（未指定时，默认时in）
out：出参
inout：既是入参，又是出参
案例：员工月薪sal，超过10000属于“高收入”，5000到10000属于“中收入”，少于5000的属于“低收入”。
create procedure p4(in sal int, out grade varchar(20))
begin
if sal > 10000 then
set grade := '高收入';
elseif sal >= 6000 then
set grade := '中收入';
else
set grade := '低收入';
end if;
end;
call p4(5000, @grade);
select @grade;
case语句
语法格式
格式一：
case 值
when 值1 then
......
when 值2 then
......
when 值3 then
......
else
......
end case;格式二：
case
when 条件1 then
......
when 条件2 then
......
when 条件3 then
......
else
......
end case;案例：根据不同月份，输出不同的季节。
方法一：
create procedure mypro(in month int, out result varchar(100))
begin
case month
when 3 then set result := '春季';
when 4 then set result := '春季';
when 5 then set result := '春季';
when 6 then set result := '夏季';
when 7 then set result := '夏季';
when 8 then set result := '夏季';
when 9 then set result := '秋季';
when 10 then set result := '秋季';
when 11 then set result := '秋季';
when 12 then set result := '冬季';
when 1 then set result := '冬季';
when 2 then set result := '冬季';
else set result := '非法月份';
end case;
end;
call mypro(9, @season);
select @season;方法二：
create procedure mypro(in month int, out result varchar(100))
begin
case
when month = 3 or month = 4 or month = 5 then
set result := '春季';
when month = 6 or month = 7 or month = 8 then
set result := '夏季';
when month = 9 or month = 10 or month = 11 then
set result := '秋季';
when month = 12 or month = 1 or month = 2 then
set result := '冬季';
else
set result := '非法月份';
end case;
end;
call mypro(9, @season);
select @season;循环
while循环
语法格式
while 条件 do
循环体;
end while;案例：传入一个数字n，计算1~n中所有偶数的和
create procedure mypro(in n int)
begin
declare sum int default 0;
while n > 0 do
if n % 2 = 0 then
set sum := sum + n;
end if;
set n := n - 1;
end while;
select sum;
end;
call mypro(10);
repeat循环
语法格式
repeat
循环体;
until 条件
end repeat;注意：条件成立时，结束循环。
案例：传入一个数n，计算1~n中所有偶数的和
create procedure mypro(in n int, out sum int)
begin
set sum := 0;
repeat
if n % 2 = 0 then
set sum := sum + n;
end if;
set n := n - 1;
until n <= 0
end repeat;
end;
call mypro(10, @sum);
select @sum;loop循环
语法格式：
[begin_label:] LOOP
-- 循环体
[IF condition THEN
LEAVE [loop_label];
END IF;]
END LOOP [loop_label;]
LEAVE label; -- 退出指定标记的循环体
ITERATE label; -- 直接进入下一次循环案例：（输出结果为1~9）
create procedure mypro()
begin
declare i int default 0;
mylp:loop
set i := i + 1;
if i = 5 then
iterate mylp;
end if;
if i = 10 then
leave mylp;
end if;
select i;
end loop;
end;游标
概述：只想结果集中某条记录的指针，允许程序注意访问结果集中的每条记录，并对其进行逐行操作和处理。
使用步骤
声明游标语法：declare 游标名称 cursor for 查询语句；
打开游标语法：open 游标名称；
通过游标获取数据的语法：fetch 游标名称 into 变量,变量,变量
关闭游标的语法：close 游标名称；
案例：从dept表中查询部门编号和部门名，创建一张新表dept2，将查询结果插入到新表中。
create procedure p7()
begin
declare no int;
declare name varchar(100);
declare dept_cursor cursor for select deptno,dname from dept;
drop table if exists dept2;
create table dept2(
no int primary key,
name varchar(100)
);
open dept_cursor;
while true do
fetch dept_cursor into no, name;
insert into dept2(no,name) values(no,name);
end while;
close dept_cursor;
end;
call p7();
注意：声明局部变量和声明游标有顺序要求，局部变量的声明需要在游标声明之前完成。
捕捉异常并处理
语法格式
DECLARE ... HANDLER
DECLARE handler_action HANDLER FOR condition_value statement;DECLARE handler_action：声明一个处理程序。
condition_value： 是异常的条件值。
statement：是在捕捉到异常时要执行的SQL语句。
案例：给上面的游标添加异常处理机制
create procedure P7()
begin
declare no int;
declare name varchar(100);
declare dept_cursor cursor for select deptno,dname from dept;
declare exit handler for not found close dept_cursor;
create table dept2(
no int primary key,
name varchar(100)
);
open dept_cursor;
while true do
fetch dept_cursor into no, name;
insert into dept2(no,name) values(no,name);
end while;
close dept_cursor;
end;
call p7();
存储函数
存储函数：带返回值的存储过程。参数只允许是in（但不能写显示的写in）。没有out，也没有inout。
语法格式：
CREATE FUNCTION 存储函数名称(参数列表) RETURNS 数据类型 [特征]
BEGIN
--函数体
RETURN ...;
END;特征的可取重要值：
deterministic：用该特征标记该函数为确定性函数
no sql：用该特征标记该函数执行过程中不会查询数据库，如果确实没有查询语句建议使用。
reads sql data：用该特征标记该函数会进行查询操作，告诉 MySQL 优化器这个函数需要查询数据库的数据，可以使用查询缓存来缓存结果，从而提高查询性能；同时 MySQL 还会针对该函数的查询进行优化器缓存处理。
案例：计算1~n的所有偶数之和
-- 删除函数
drop function if exists sum_fun;
-- 创建函数
create function sum_fun(n int)
returns int deterministic
begin
declare result int default 0;
while n > 0 do
if n % 2 = 0 then
set result := result + n;
end if;
set n := n - 1;
end while;
return result;
end;
-- 调用函数
set @result = sum_fun(100);
select @result;触发器
触发器：一种数据库对象，可以在特定的数据操作中如插入（insert），更新（update），或删除（delete）触发时自动执行。
作用
1、强制实施业务规则：触发器可以帮助确保数据表中的业务规则得到强制执行，例如检查插入或更新的数据是否符合某些规则。
2、数据审计：触发器可以声明在执行数据修改时自动记日志或审计数据变化的操作，使数据对数据库管理员和 SQL 审计人员更易于追踪和审计。
3、执行特定业务操作：触发器可以自动执行特定的业务操作，例如计算数据行的总数、计算平均值或总和等。
语法格式
CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name FOR EACH ROW
BEGIN
-- 触发器执行的 SQL 语句
END;trigger_name：触发器的名称
BEFORE/AFTER：触发器的类型，可以是 BEFORE 或者 AFTER
INSERT/UPDATE/DELETE：触发器所监控的 DML 调用类型
table_name：触发器所绑定的表名
FOR EACH ROW：表示触发器在每行受到 DML 的影响之后都会执行
触发器执行SQL语句：该语句会在触发器被触发时执行
触发器的NEW和OLD的关键字：
NEW：在INSERT和UPDATE触发器中引用新插入或更新的行。
OLD：在UPDATE和DELETE触发器中引用原始被更新或删除的行。
触发器的优点
数据的完整性：可以用于强制实施业务规则，确保数据的完整性。
自动化任务：可以用于自动化常见任务，如日志记录、审计等。
提高性能：可以在数据库层面上执行逻辑，减少了从应用程序到数据库之间的通信开销。
案例：用于在order表中插入新纪录时更新order_count表中的订单数
CREATE TRIGGER update_order_count
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
UPDATE order_count
SET count = count + 1;
END;查看触发器
show triggers;删除触发器
DROP TRIGGER IF EXISTS trigger_name;
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
