MySQL笔记-数据库编程相关

变量

系统变量

系统变量分类

MySQL 中的系统变量以 两个“@” 开头，分为全局系统变量（ global ）以及会话系统变量（ session），如果不写，默认会话级别

系统变量	简介
全局系统变量	@@global仅用于标记全局系统变量，针对于所有会话（连接）有效，但不能跨重启
会话系统变量	@@session仅用于标记会话系统变量，仅针对于当前会话（连接）有效。

常用的系统变量

变量	概括
@@error	上一条SQL错误号
@@identity	最后一次插入的标识值
@@rowcount	受上一个SQL语句影响的行数
@@serviceName	该计算机上的SQL服务名称
@@Version	SQLServer的版本信息

全局系统变量（global）

（1）查看所有或部分全局系统变量

# 查看所有全局系统变量
show global variables;
# 查看满足条件的部分全局系统变量
show global variables like '%标识符%';
# 查看指定全局系统变量
select @@global.变量名;

（2）修改全局系统变量的值

# 方式1：修改MySQL 配置文件 ，继而修改MySQL系统变量的值（该方法需要重启MySQL服务）
# 方式2：在MySQL服务运行期间，使用“set”命令重新设置全局系统变量的值
set @@global.变量名=变量值;
set global 变量名=变量值;

会话系统变量（session）

（1）查看所有或部分会话系统变量

# 查看所有会话系统变量
show session variables; 
show variables;
# 查看满足条件的部分会话变量
show session variables like '%标识符%';
# 查看指定会话系统变量
select @@session.变量名;
select @@变量名;

（2）修改会话系统变量的值

# 方式1：修改MySQL 配置文件 ，继而修改MySQL系统变量的值（该方法需要重启MySQL服务）
# 方式2：在MySQL服务运行期间，使用“set”命令重新设置会话系统变量的值
set @@session.变量名=变量值;
set session 变量名=变量值;

MySQL 8.0的新特性—全局变量的持久化

（1）在MySQL数据库中，全局变量可以通过使用set global语句语句来设置，设置的变量值只会临时生效 。数据库重启后，服务器又会从MySQL配置文件中读取变量的默认值。

例如，设置服务器语句超时的限制，可以通过设置系统变量max_execution_time来实现：

set global MAX_EXECUTION_TIME=2000;  

（2）MySQL 8.0版本新增了 set persist 命令，设置的变量值会持久化。MySQL会将该命令的配置保存到数据目录下的 mysqld-auto.cnf 文件中，下次启动时会读取该文件，用其中的配置来覆盖默认的配置文件

例如，设置服务器的最大连接数为1000

set persist global max_connections = 1000;  

用户变量

用户变量分类

MySQL 中的用户变量以 一个“@” 开头，根据作用范围不同，又分为 会话用户变量 和 局部变量 。

用户变量	简介
会话用户变量	作用域和会话变量一样，只对 当前连接 会话有效
局部变量	只在 BEGIN 和 END 语句块中有效，即只能在 存储过程和函数 中使用

会话用户变量

（1）会话用户变量的定义

# 方式1：“=”或“:=”
set @用户变量 = 值;
set @用户变量 := 值;
# 方式2：“:=” 或 into关键字 select 
@用户变量 := 表达式 [from 等子句]; 
select 表达式 into @用户变量 [from 等子句];

（2）查看用户变量的值 （查看、比较、运算等）

select @用户变量

（3）使用案例：声明两个变量，求和并打印

set @m=1;
set @n=1;
set @sum=@m+@n;
select @sum;

局部用户变量（declare）

（1）使用位置：只能放在 BEGIN … END 中，而且只能放在第一句

（2）局部用户变量的定义（如果没有default子句，初始值为null）

declare 变量名 类型 [default 值];

（3）局部用户变量赋值

# 方式1：一般用于赋简单的值
set 变量名 = 值; 
set 变量名 := 值;
# 方式2：一般用于赋表中的字段值
select 局部变量名;

（4）语法结构

begin
	#声明局部变量
	declare 变量名1 变量数据类型 [default 变量默认值];
	declare 变量名2,变量名3,... 变量数据类型 [default 变量默认值];
	#为局部变量赋值
	set 变量名1 = 值;
	select 值 into 变量名2 [FROM 子句];
	#查看局部变量的值
	select 变量1,变量2,变量3;
end;

游标（cursor）

游标概括

（1）游标使用的步骤

①声明游标 —> ②打开游标—>③ 使用游标（从游标中获取数据）—>④ 关闭游标

（2）优缺点和建议

• 优点：游标是 MySQL 的一个重要的功能，为逐条读取 结果集中的数据，提供了完美的解决方案。
  跟在应用层 面实现相同的功能相比，游标可以在存储程序中使用，效率高，程序也更加简洁。
• 缺点：使用游标的过程中，会对数据行进行加锁，这样在业务并发量大的时候，
  不仅会影响业务之间的效率，还会消耗系统资源 ，造成内存不足，这是因为游标是在内存中进行的处理。
• 建议：养成用完之后就关闭的习惯，这样才能提高系统的整体效率。

游标使用

（1）声明游标（适用于 MySQL、SQL Server、DB2、MariaDB）

declare 游标名 cursor for 查询语句;
# 如果是用 Oracle 或者 PostgreSQL
declare 游标名 cursor is 查询语句;

（2）打开游标

open 游标名

（3）使用游标（从游标中获取数据）

# 游标的查询结果集中的字段数，必须跟 INTO 后面的变量数一致，否则在存储过程执行的时候，MySQL 会提示错误
fetch 游标名 into 变量1, 变量2 ...

（4）关闭游标

# 使用完游标后需要关闭掉该游标，因为游标会占用系统资源
close 游标名

游标使用案例

存储过程功能：累加薪资最高的几个员工的薪资值，直到薪资总和达到limit_total_salary参数的值，返回累加的人数给total_count。

声明IN参数 limit_total_salary，DOUBLE类型；

声明OUT参数total_count，INT类型。

# 创建存储过程“get_count_by_limit_total_salary()”
delimiter $
create procedure  get_count_by_limit_total_salary(IN limit_total_salary DOUBLE,OUT total_count INT)
begin
	#声明局部变量
	declare sum_sal DOUBLE DEFAULT 0.0; #记录累加的工资总额
	declare emp_sal DOUBLE; #记录每一个员工的工资
	declare emp_count INT DEFAULT 0;#记录累加的人数
	#1.声明游标（查询员工工资）
	declare emp_cursor cursor for SELECT salary FROM employees ORDER BY salary DESC;
	#2.打开游标
	open emp_cursor;
	# 使用循环结构遍历出每一条数据
	repeat
		#3.使用游标（将每个员工工资赋给变量emp_sal）
		fetch emp_cursor INTO emp_sal;
		# 记录累加工资
		SET sum_sal = sum_sal + emp_sal;
		# 记录累加的人数
		SET emp_count = emp_count + 1;
		# 循环结束条件：达到输入的参数limit_total_salary的值
		until sum_sal >= limit_total_salary
	end repeat;
	# 返回值：累加的人数
	SET total_count = emp_count;
	#4.关闭游标
	close  emp_cursor;
end $
delimiter ;

# 调用存储过程
CALL get_count_by_limit_total_salary(200000,@total_count);
SELECT @total_count;

内置函数

单行函数

MySQL提供了许多单行函数，这些函数可用于对查询结果的每一行数据进行处理，并返回一个单独的值。通常用于对特定列或表达式进行计算、转换或提取

字符串函数

• **CONCAT(str1, str2, …)**：将多个字符串连接在一起。
• **LENGTH(str)**：返回字符串的长度。
• **UPPER(str)**：将字符串转换为大写。
• **LOWER(str)**：将字符串转换为小写。
• **SUBSTRING(str, start, length)**：返回字符串的子串。

数值函数

• **ABS(x)**：返回一个数的绝对值。
• **ROUND(x, d)**：将一个数四舍五入到指定的小数位数。
• **CEIL(x)**：向上取整，返回不小于x的最小整数。
• **FLOOR(x)**：向下取整，返回不大于x的最大整数。
• **MOD(x, y)**：返回x除以y的余数。

时间日期函数

• **NOW()**：返回当前日期和时间。
• **CURDATE()**：返回当前日期。
• **CURTIME()**：返回当前时间。
• **DATE_FORMAT(date, format)**：将日期格式化为指定的格式。
• **DATEDIFF(date1, date2)**：计算两个日期之间的天数差。

条件函数

• **IFNULL(expr1, expr2)**：如果expr1不为空，则返回expr1；否则返回expr2。
• **NULLIF(expr1, expr2)**：如果expr1等于expr2，则返回NULL；否则返回expr1。

聚合函数

MySQL提供了一组强大的聚合函数，用于对数据进行统计和汇总计算。注意：聚合函数是不能嵌套使用

以下是一些常用的MySQL聚合函数：

• **COUNT()*：计算指定列或表达式的行数。 可以使用COUNT()来计算所有行数。
• **SUM()**：计算指定列或表达式的总和。
• **AVG()**：计算指定列或表达式的平均值。
• **MAX()**：找出指定列或表达式的最大值。
• **MIN()**：找出指定列或表达式的最小值。
• **GROUP_CONCAT()**：将指定列或表达式的值连接为一个字符串，并使用指定的分隔符进行分隔。
• **STDDEV()**：计算指定列或表达式的标准差。
• **VARIANCE()**：计算指定列或表达式的方差。
• **STDDEV_POP()**：计算指定列或表达式的总体标准差。
• **STDDEV_SAMP()**：计算指定列或表达式的样本标准差。
• **VAR_POP()**：计算指定列或表达式的总体方差。
• **VAR_SAMP()**：计算指定列或表达式的样本方差。
• **BIT_XOR()**：计算指定列或表达式的按位异或运算结果。

窗口函数

窗口函数简介

窗口函数也叫分析函数，可以对结果集中的每一行数据进行分组、排序、聚合等操作，并可以在不影响结果集本身的情况下返回额外的计算值

聚合函数与窗口函数区别

聚合函数也可以用于窗口函数，聚合函数是将多条记录聚合为一条；窗口函数是每条记录都会执行，有几条记录执行完还是几条。

窗口函数分类

序号函数

• **ROW_NUMBER()**：给结果集中的每一行分配一个唯一整数标识符，通常用于返回结果集的前几行或者给结果集进行排序。
• **RANK()**：按照指定的列对结果集进行排名，排名相同的行将得到相同的排名值，而空值会被跳过。
• **DENSE_RANK()**：与 RANK() 类似，但是排名相同的行不会得到相同的排名值，排名值之间没有间隔，因此该函数常用于结果集较小的情况下。

分布函数

• **PERCENT_RANK()**：计算结果集中每一行在整个排序结果中所处的百分位，返回值为 0 到 1 之间的一个实数。
• **CUME_DIST()**：计算结果集中每一行的累积分布函数的值，即该行排名的最终百分比。

前后函数

• **LAG()**：获取某一行前面的某一列的值，可以使用 OFFSET 子句来指定前面第几行，默认为 1。
• **LEAD()**：获取某一行后面的某一列的值，也可以使用 OFFSET 子句来指定后面第几行，默认为 1。

头尾函数

• **FIRST_VALUE()**：获取某一列的第一个值。
• **LAST_VALUE()**：获取某一列的最后一个值。

其它函数

• **NTH_VALUE()**：获取某一列的第 N 个值，可以使用 OFFSET 子句来指定第几个值。
• **NTILE()**：将结果集划分成指定数量的桶，每个桶中包含相等数量的行，并返回桶号。

异常处理

定义条件

（1）定义条件就是给MySQL中的错误码命名，这有助于存储的程序代码更清晰

（2）定义条件语法格式

declare 错误名称 condition for 错误码（或错误条件）

定义处理程序

（1）可以为SQL执行过程中发生的某种类型的错误定义特殊的处理程序

（2）定义处理程序语法

declare 处理方式 handler for 错误类型 处理语句  

（3）处理方式：处理方式有3个取值：CONTINUE、EXIT、UNDO。

方式	简介
CONTINUE	表示遇到错误不处理，继续执行
EXIT	表示遇到错误马上退出
UNDO	表示遇到错误后撤回之前的操作。MySQL中暂时不支持这样的操作。

（4）错误类型（即条件）可以有如下取值：

类型	简介
SQLSTATE ‘字符串错误码’	表示长度为5的sqlstate_value类型的错误代码；
MySQL_error_code	匹配数值类型错误代码；
错误名称	表示declare … condition定义的错误条件名称。
SQLWARNING	匹配所有以01开头的SQLSTATE错误代码；
NOT FOUND	匹配所有以02开头的SQLSTATE错误代码；
SQLEXCEPTION	匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码；

流程控制

顺序结构

begin
	语句或语句块
end

分支结构

分支结构之 IF

（1）语法结构

if 表达式1 
	then 操作1
elseif 表达式2 
	then 操作2
	……
else 
	操作N
end if;

（2）举例

delimiter $
create function test_if()
begin
	declare age INT default 20;
	if  age > 60
		then select '中老年';
	elseif  age > 18
		then select '青壮年';
	else
		SELECT '未成年';
	end if;
end $
delimiter;

分支结构之 CASE

（1）语法结构

case [表达式]
	when 条件1 then 结果1或语句1(如果是语句，需要加分号)
	when 条件2 then 结果2或语句2(如果是语句，需要加分号)    
	……
	[ else 结果n或语句n(如果是语句，需要加分号)]
end [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）

（2）案例1：查询user表中 参数选手名称 与 获得的奖牌（根据分数判断）

select 
uname,
case
    when ucredit>=200 then '金牌选手'
    when ucredit>=100 then '银牌选手'
    else '铜牌选手'
end
from users limit 5;

（3）案例2：判断val值等于1、等于2，或者两者都不等。

delimiter $
create procedure test_case(IN var int)
begin
	case var
		when 1 then select 'var = 1';
		when 2 then select 'var = 2';
		when 3 then select 'var = 3';
		else select '其他数字';
	end case;
end $
delimiter;

循环结构

循环结构之 LOOP

LOOP循环语句用来重复执行某些语句，直到循环被退出（使用LEAVE子 句），跳出循环过程。

（1）语法结构（loop_label表示LOOP语句的标注名称）

[loop_label:] loop 
	循环执行的语句 
end loop [loop_label]

（2）案例

delimiter $
create procedure test_loop()
begin
	# 声明局部变量num
	declare num INT default 1;
	# 使用循环结构loop
	loop_label:loop
		# 对num重新赋值
		SET num = num + 1;
		# 如果num大于10则跳出循环
		if num >= 10 
			then leave loop_label;
		end if;
	end loop loop_label;
	#查看num
	SELECT num;
end $
delimiter ;

循环结构之 WHILE

WHILE循环是带条件判断的循环过程。对指定的表达式进行判断，如果为真，就执行循环内的语句，否则退出循环

（1）语法结构（while_label为WHILE语句的标注名称）

[while_label:] while 循环条件 do
	循环体
end while [while_label];

（2）案例：当 i 值小于等于10时，将重复执行循环过程

delimiter $
create procedure test_while()
begin	
	# 声明局部变量num
	declare num int default 1;
	#循环条件
	while num <= 10 do
		#循环体（略）
	#迭代条件
	set num = num + 1;
	end while;
	#查询
	select num;
end $
delimiter ;

循环结构之 REPEAT

REPEAT语句创建一个带条件判断的循环过程。与WHILE循环不同的是，REPEAT 循环首先会执行一次循环，然后在 UNTIL 中进行表达式的判断，如果满足条件就退出，即 END REPEAT；如果条件不满足，则会就继续执行循环，直到满足退出条件为止。

（1）语法结构（repeat_label为REPEAT语句的标注名称）

[repeat_label:] repeat
	循环体的语句
until 结束循环的条件表达式
end repeat [repeat_label]

（2）案例：不满足 i 值大于或等于10时，将重复执行循环体

delimiter $
create procedure test_repeat()
begin	
	# 声明局部变量num
	declare num int default 1;
	# 使用循环结构REPEAT
	repeat
		# 循环体的语句
		SET num = num + 1;
		# 不满足就继续执行循环体
		until num >= 10
	end repeat;
	#查看
	SELECT num;
end $
delimiter ;

跳转语句

跳转语句之 LEAVE语句

LEAVE语句：可以用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。（类似其他语言的break）

（1）语法结构

leave 标记名

（2）案例：创建存储过程 “leave_begin()”，声明INT类型的IN参数num。给BEGIN…END加标记名，并在 BEGIN…END中使用IF语句判断num参数的值。

# 如果num<=0，则使用LEAVE语句退出BEGIN...END；
# 如果num=1，则查询“employees”表的平均薪资；
# 如果num=2，则查询“employees”表的最低薪资；
# 如果num>2，则查询“employees”表的最高薪资。
# IF语句结束后查询“employees”表的总人数。
delimiter $
create procedure leave_begin(IN num INT)
begin_label:begin
	if num <= 0
		then leave begin_label;
	elseif num = 1
		then SELECT AVG(salary) FROM employees;
	elseif num = 2
		then SELECT MIN(salary) FROM employees;
	else 
		SELECT MAX(salary) FROM employees;
	end if;
	#查询总人数
	SELECT COUNT(*) FROM employees;
end $
delimiter ;

跳转语句之 ITERATE语句

ITERATE语句：只能用在循环语句（LOOP、REPEAT和WHILE语句）内，表示重新开始循环，将执行顺序 转到语句段开头处，意思为“再次循环”。（类似其他语言的continue）

（1）语法结构（ITERATE语句必须跟在循环标志前面。label参数表示循环的标志）

iterate label

（2）案例：定义局部变量num，初始值为0。循环结构中执行num + 1操作。

# 如果num < 10，则继续执行循环； 
# 如果num > 15，则退出循环结构；
delimiter $
create procedure  test_iterate()
begin
	declare num INT default 0;
	loop_label:loop
		#赋值
		set num = num + 1;
		# 如果小于10，则继续执行循环； 
		if num  < 10
			then iterate loop_label;
		# 如果大于15，则退出循环结构；
		elsef num > 15
			then leave loop_label;
		end if;
		# 当num为0-9时都会重新开始循环（iterate），当num为10-15时会执行到此语句，直到num大于15时，结束循环（leave）
		SELECT '此句会执行6次';
	end loop;
end $
delimiter ;

事件（event）

查看事件调度器是否开启

# 方式一
show variables like 'event_scheduler'
# 方式二
select @@event_scheduler;
# 方式三
show processlist;

开启事件调度器

（1）方式一：使用set global命令可以开启或关闭事件

set global event_scheduler=1;
set global event_scheduler=ON;

（2）方式二：通过更改配置文件（始终开启事件）

需要在MySQL的配置文件my.ini（Windows系统）/my.cnf（Linux系统）中添加event_scheduler=on。（添加后需要重启服务）

# 事件调度器启动状态
event_scheduler = on

创建事件

语法结构

create
	[ definer ={user | current_user }]
	event [if not exists] 事件名
	on schedule schedule
	[on completion [not] preserve]
	[ enable | disable | disable on slave ]
	[ comment '注释内容']
	do 执行的代码;

字段说明

字段	简介
definer	可选，用于定义事件执行时检查权限的用户
on schedule schedule	必选，用于定义执行的时间和时间间隔
on completion [not] preserve	可选，用于定义事件是否循环执行，即是一次执行还是永久执行，默认为一次执行，即 NOT PRESERVE
enable | disable | disable on slave	可选项，用于指定事件的一种属性。默认活动的（enable）。 enable：表示该事件是活动的，也就是调度器检查事件是否必选调用； disable：表示该事件是关闭的，也就是事件的声明存储到目录中，但是调度器不会检查它是否应该调用； disable on slave：表示事件在从机中是关闭的。
comment ‘注释内容’	可选，用于定义事件的注释
do 执行的代码;	必选，用于指定事件启动时所要执行的代码。 可以是任何有效的SQL语句、存储过程或者一个计划执行的事件。 如果包含多条语句，可以使用BEGIN…END复合结构

在on schedule 子句中，参数schedule值为一个AS子句，用于指定事件在某个时刻发生，语法格式如下

# 方式一
AT timestamp [+ INTERVAL interval] …
# 方式二
EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]

字段说明

字段	简介
timestamp	表示一个具体的时间点，后面加上一个时间间隔，表示在这个时间间隔后事件发生。
EVERY子句	用于表示事件在指定时间区间内每隔多长时间发生一次，其中 SELECT子句用于指定开始时间；ENDS子句用于指定结束时间
interval	表示一个从现在开始的时间，其值由一个数值和单位构成。例如，使用“4 WEEK”表示4周；

interval参数值的语法格式如下

quantity {
	YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND |YEAR_MONTH |
	DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND
}

一些常用的时间间隔设置

语法	简介
ON SCHEDULE EVERY 5 SECOND	每隔5秒钟执行
ON SCHEDULE EVERY 1 MINUTE	每隔1分钟执行
ON SCHEDULE EVERY 1 WEEK	每隔1周执行
ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD( DATE_ADD( CURDATE(), INTERVAL 1 DAY ), INTERVAL 1 HOUR )	每天凌晨1点执行
ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD( DATE_ADD( DATE_SUB( CURDATE(),INTERVAL DAY(CURDATE())-1 DAY ),INTERVAL 1 MONTH ),INTERVAL 1 HOUR )	每个月的第一天凌晨1点执行
ON SCHEDULE EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + 1 WEEK	每 3 个月，从现在起一周后开始
ON SCHEDULE EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK

查询事件

select * from information_schema.events;

启动事件

alter event 事件名 enable; 

关闭事件

alter event 事件名 disable; 

删除事件

drop event if exists 事件名;

存储过程（procedure）

改变mysql结束符

MySQL默认的语句结束符号为分号; 为了避免与存储过程中SQL语句结束符相冲突，需要使用 DELIMITER改变存储过程的结束符，使用后需要指定回分号，否则后续的查询结束符都为新设置的

# 常见的新的结束符为$ 或 //
DELIMITER 新的结束标记  

案例：定义以$为结束符号

delimiter $
create procedure 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
begin
	sql语句1; 
	sql语句2;
end $
delimiter ;

创建存储过程

# 存储过程含义：就是一组经过 预先编译 的 SQL 语句 的封装
# 与存储函数区别：存储过程是没有返回值的。
create procedure 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
begin
	函数体
end;

参数概括

参数	简介
IN	当前参数为输入参数，也就是表示入参； 存储过程只是读取这个参数的值。如果没有定义参数种类， 默认就是 IN ，表示输入参数。
OUT	当前参数为输出参数，也就是表示出参； 执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。
INOUT	当前参数既可以为输入参数，也可以为输出参数。
形参类型	形参类型可以是 MySQL数据库中的任意类型
characteristics	表示创建存储过程时指定的对存储过程的约束条件

characteristics 表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下：

LANGUAGE SQL  | [NOT] DETERMINISTIC  | { CONTAINS SQL | NO  SQL | READS SQL DATA | MODIFIES SQL DATA }  | SQL SECURITY {  DEFINER | INVOKER }  | COMMENT 'string'  

参数	简介
LANGUAGE SQL	说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL
[NOT] DETERMINISTIC	指明存储过程执行的结果是否确定。 DETERMINISTIC表示结果是确定 的。每次执行存储过程时，相同的输入会得到相同的输出。 NOT DETERMINISTIC表示结果是不确定 的，相同的输入可能得到不同的输出。 如果没有指定任意一个值，默认为NOT DETERMINISTIC。
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }	指明子程序使 用SQL语句的限制。 CONTAINS SQL：当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句； NO SQL：当前存储过程的子程序中不包含任何SQL语句； READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句； MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句。 默认情况下，系统会指定为CONTAINS SQL
SQL SECURITY { DEFINER | INVOKER }	执行当前存储过程的权限，即指明哪些用户能够执 行当前存储过程。 DEFINER 表示只有当前存储过程的创建者或者定义者才能执行当前存储过程； INVOKER 表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。 如果没有设置相关的值，则MySQL默认指定值为DEFINER。
COMMENT ‘string’	注释信息，可以用来描述存储过程

调用存储过程

# 存储过程和数据库相关，如果要执行其他数据库中的存储过程，需要指定数据库名称
call 存储过程名(实参列表)

案例1：调用 in 模式的参数

call 存储过程名('值');

案例2：调用 out 模式的参数

set @name; 
call 存储过程名(@name); 
select @name;

案例3：调用 inout 模式的参数

set @name=值;
call 存储过程名(@name); 
select @name;

查看存储函数或存储过程

（1）查看存储过程和函数的创建信息

# 存储函数function 存储过程procedure
show create {procedure|function} 存储过程或函数名;

（2）查看存储过程和函数的状态信息

# 存储函数function 存储过程procedure
show {procedure|function}  status [like '存储过程或函数名']

删除存储函数或存储过程

# IF EXISTS：如果程序或函数不存储，它可以防止发生错误，产生一个用SHOW WARNINGS查看的警告
drop {procedure|function} [if exists] 存储过程或函数的名

存储函数（function）

创建存储函数

create function 函数名( 形参名 形参类型, ... )
	returns 返回值类型
[characteristics ...]
begin
	函数体  #函数体中肯定有 RETURN 语句
end;

参数概括

参数	简介
参数列表	包含两部分参数名和参数类型 指定参数为IN、OUT或INOUT只对PROCEDURE是合法的，FUNCTION中总是默认为IN参数。
returns type	表示函数返回数据的类型； RETURNS子句只能对FUNCTION做指定，对函数而言这是 强制的。 它用来指定函数的返回类型，而且函 数体必须包含一个 RETURN value 语句。
characteristic	创建函数时指定的对函数的约束，取值与创建存储过程时相同
函数体	函数体必须有return语句 且每个sql语句后要以;结尾 所以需要使用delimiter来重新设置结束标记 可以用BEGIN…END来表示SQL代码的开始和结束。如果函数体只有一条语句，也可以省略 BEGIN…END

调用存储函数

# 存储函数的使用方法与MySQL内部函数的使用方法是一样
# 区别：存储函数是 用户自己定义 的，而内部函数是MySQL 开发者 定义
select 函数名(实参列表)

查看存储函数或存储过程

（1）查看存储过程和函数的创建信息

# 存储函数function 存储过程procedure
show create {procedure|function} 存储过程或函数名;

（2）查看存储过程和函数的状态信息

# 存储函数function 存储过程procedure
show {procedure|function}  status [like '存储过程或函数名']

修改存储函数或存储过程

修改存储过程或函数，不影响存储过程或函数功能，只是修改相关特性

# 存储函数function 存储过程procedure
alter {procedure|function} 存储过程或函数名 [characteristic ...]

characteristic指定存储过程或函数的特性，其取值信息与创建存储过程、函数时的取值信息略有不同。

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT ‘string’

字段概括

字段	简介
CONTAINS SQL	表示子程序包含SQL语句，但不包含读或写数据的语句。
NO SQL	表示子程序中不包含SQL语句。
READS SQL DATA	表示子程序中包含读数据的语句。
MODIFIES SQL DATA	表示子程序中包含写数据的语句。
SQL SECURITY { DEFINER | INVOKER } ，	指明谁有权限来执行 DEFINER ，表示只有定义者自己才能够执行。 INVOKER ，表示调用者可以执行
COMMENT ‘string’	表示注释信息

删除存储函数或存储过程

# IF EXISTS：如果程序或函数不存储，它可以防止发生错误，产生一个用SHOW WARNINGS查看的警告
drop {procedure|function} [if exists] 存储过程或函数的名

触发器（trigger）

创建触发器

create trigger 触发器名称 
{ before|after } { insert | update | delete } on 表名 
for each row
触发器执行的语句块;

字段说明

字段	简介
表名	表示触发器监控的对象
{ before|after }	表示触发的时间。 before 表示在事件之前触发；after 表示在事件之后触发
{ insert | update | delete }	表示触发的事件 insert 表示插入记录时触发；update 表示更新记录时触发；delete 表示删除记录时触发。
触发器执行的语句块	可以是单条SQL语句，也可以是由begin…**end;**结构组成的复合语句块

查看触发器

（1）查看当前数据库的所有触发器的定义

show triggers;

（2）查看当前数据库中某个触发器的定义

show create trigger 触发器名

（3）从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息。

select * from information_schema.TRIGGERS;

删除触发器

drop trigger if exists 触发器名称;

触发器案例

（1）创建测试表test_trigger

create table test_trigger (
    id int primary key AUTO_INCREMENT, # 主键自增
    t_note varchar(30)
);

（2）创建日志表test_trigger_log ，存放测试表test_trigger的日志

create table test_trigger_log (
	id int primary key AUTO_INCREMENT, # 主键自增
	t_log varchar(30)
);

（3）创建名称为before_insert_test_tri的触发器，向test_trigger数据表插入数据之前，向test_trigger_log数据表中插入日志信息（固定内容）。

delimiter $
create trigger before_insert_test_tri
before insert on test_trigger
for each row
begin
	insert into test_trigger_log(t_log) values('before insert...');
end $
delimiter ;

（4）测试向test_trigger表插入数据

insert into test_trigger(t_note) values('Tom...');

（5）此时test_trigger表会有插入的Tom...数据，但插入之前会先向test_trigger_log表插入的日志before insert...