MySQL函数、存储过程和触发器

MySQL函数和存储过程

1 存储过程

1.1 定义
存储过程是(Stored Procedure)一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数来调用执行它（call调用）
1.2 存储过程优点
1.增强了sql语言的功能和灵活性
2.存储过程在第一次编译后再次调用就不需要再编译，比SQL语句执行效率要高（数据库执行动作时都是先编译后执行）
3.直接调用存储过程可执行已经定义好的SQL语句
4.存储过程可以避免开发人员重复编写相同的SQL语句
5.存储过程是在MySQL服务器中存储和执行，可以减少客户端和服务器的数据传输
1.3 语法
\d 标识符
CREATE PROCEDURE proc_name()
BEGIN
…
…
END 标识符
\d ;

调用存储过程call proc_name()
1.4 查看当前有哪些存储过程
show procedure status [like ‘proc_name’]
1.5 显示某一个过程的创建的详细信息：
show create procedure proc_name
1.6 存储过程示例
\d$$createprocedurechaxun()beginselects.name,c.classfromstudentssjoinclassescons.classid=c.classid;end$$
\d ;

2 MySQL函数

2.1 定义
实现某个特定功能的模块（代码块），函数可以接受参数、执行操作（例如复杂计算）并将操作结果以值的形式返回
函数在数学中是一种对应关系， 简单地说，因变量y随着变量x的变化而变化，y就是x的函数

存储过程属于特定的函数（不返回参数值）
2.2 函数分类
2.2.1 系统内置函数
系统函数分为数值函数、字符串函数、日期时间函数

数值函数
很多数字处理和数值方面的运算，则可以使用数值函数，如下
ABS() --返回数值的绝对值 （案例的数值例如x）
CEIL() —返回不小于x的最小整数
FLOOR() —返回不大于 x 的最大整数(与CEIL的用法刚好相反)
MOD(x,y) ----返回数字x除以y后的余数：x mod y 相当于%取模
ROUND(X[,Y]) ---- 将数字X四舍五入到指定的小数位数Y
FLOOR(i + RAND() * (j – i)) —获得一个从i到j之间的随机整数
例如：mysql> select floor(1+rand()*5); 产生1到5的随机数

字符串函数

日期函数
YEAR(date) 获得年份
MONTH(date) 获得月份
DAY(date) 获得天
HOUR(date) 获得小时
MINUTE(date) 获得分钟
SECOND(date) 获得秒
NOW() 当前日期和时间
ADDDATE(date,x) 获得x天后的日期
SUBDATE(date,x) 获得x天前的日期
DATEDIFF(date1,date2) 获得2个日期之间的天数
2.2.2 自定义函数
函数定义语法：
\d 标识符
CREATE FUNCTION function_name(参数列表)
RETURNS 返回值类型
BEGIN
routine_body
END 标识符
2.3 函数特点
辅助查询
用途单一简单工作可以写成函数，查询语句就可以使用
可以和SQL结合使用，放到sql语句中就可以使用
查询工作
有返回值,必须要有返回值
一般返回值，只能是1行1列
2.4 函数示例
例1：输入生日返回年龄：
\d $$createfunctionge{t}_{a}ge(i{n}_{b}irthdate)returnsdecimal(8,2)beginreturn(selectround(datediff(now(),i{n}_{b}irth)/365,2));end$$
\d;
select sname,birth,get_age(birth) age from stu;
select sname,birth,get_age(birth) age from stu where get_age(birth)>25;

例2：输入班号返回班名
\d $$createfunctionge{t}_{c}name1(i{n}_{c}noint)returnsvarchar(50)begindeclare{v}_{c}namevarchar(50);selectcnameinto{v}_{c}namefromclasswherecno=i{n}_{c}no;if{v}_{c}nameisnotnullthenreturn{v}_{c}name;elseretur{n}^{\prime }无{班}^{\prime };endif;end$$
\d;

select sname,cno,get_cname1(cno) from stu;

例3：输入性别（男|女），体重，身高返回 体态描述
\d $$createfunctionge{t}_{b}mi(i{n}_{s}exvarchar(10),i{n}_{w}eightdecimal(4,1),i{n}_{h}eightdecimal(4,1))returnsvarchar(20)beginreturn(selectbnamefrombmiwherei{n}_{w}eight/(i{n}_{h}eight/100\ast i{n}_{h}eight/100)betweenlvalandhvalandsex=i{n}_{s}ex);end$$
\d;

select sname,sex,height,weight,get_bmi(sex,weight,height) bname from stu;
例4
\d $$createfunctionge{t}_{c}las{s}_{n}ame(i{n}_{s}tuidint)returnsvarchar(30)beginreturn(selectc.classfromstudentssjoinclassescons.classid=c.classidwheres.stuid=i{n}_{s}tuid);end$$
\d;

3 触发器

3.1 定义
触发器(trigger),也叫触发程序，是与表有关的命名数据库对象，触发器是一种特殊的存储过程，但是触发器不需要语句调用，也不需要手动启动，它由事件触发，事件包括INSERT,UPDATE,和DELETE语句，当指定表发生指定动作，将激活触发器。
3.2 触发器语法
CREATE
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
trigger_body

trigger_time: { BEFORE | AFTER }
before 在触发事件之前执行触发器中的代码
after 在触发事件之后执行触发器中的代码
trigger_event: { INSERT | UPDATE | DELETE }
MySQL支持的触发事件是：增删改DML语句
3.3 NEW和OLD关键字
关键字 insert delete update
new.column_name 引用插入行中的某个列的数值 无 引用更新行中某个列原来的值（更新前的值）
old.column_name 无 引用删除行中某个列的数值 引用更新行中某个列新的值（更新后的值）

3.4 触发器其他相关命令
（1）查看触发器
show triggers;
或
select * from information_schema.triggers\G;
（2）删除触发器
drop trigger trigger_name;
（3）修改MySQL的语法结束符。
DELIMITER || 将语法分割符修改为||

3.5 示例
创建用户和黑名单表，
用户表：用户编号，手机号，余额；
黑名单表：用户编号，手机号。
如果话费余额不足10元，把用户编号和其手机加入到黑名单表中；如果用户充值后，余额超过10元，把其信息从黑名单表中移除。

\d $$
create trigger t_oper_blacklist before update on users for each row
begin
if new.balance<10 then
insert into blacklist values(old.id,old.phone);
end if;

if new.balance>=10 then
delete from blacklist where id=old.id;
end if;
end$$
\d;

update users set balance=balance-25 where phone=‘18520151234’;
select * from users;
select * from blacklist;

update users set balance=balance+100 where phone=‘18520151234’;
select * from users;
select * from blacklist;