MySQL系统和自定义函数、触发器的使用方法是什么
这篇文章主要讲解了“MySQL系统和自定义函数、触发器的使用方法是什么”,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习“MySQL系统和自定义函数、触发器的使用方法是什么”吧!
一、系统封装函数MySQL 有很多内置的函数,可以快速解决开发中的一些业务需求,大概包括流程控制函数,数值型函数、字符串型函数、日期时间函数、聚合函数等。以下列出了这些分类中常用的函数。
1、控制流程函数case…when
根据值判断返回值,类比编程中的IF-ELSE判断。
--DEMO01SELECTCASEDATE_FORMAT(NOW(),'%Y-%m-%d')WHEN'2019-12-29'THEN'today'WHEN'2019-12-28'THEN'yesterday'WHEN'2019-12-30'THEN'tommor'ELSE'Unknow'END;--DEMO02SELECT(CASEWHEN1>0THEN'true'ELSE'false'END)ASresult;
if(expr1,expr2,expr3)
如果表达式 expr1 是TRUE,则 IF()的返回值为expr2; 否则返回值则为 expr3。
SELECTIF(1>2,'1>2','1<2')ASresult;SELECTIF(1<2,'yes','no')ASresult;SELECTIF(STRCMP('test','test'),'no','yes');
ifnull(expr1,expr2)
如果表达式 expr1不为NULL,则返回值为expr1;否则返回值为 expr2。
SELECTIFNULL(NULL,'cicada');SELECTIFNULL(1/1,'no');2、常用字符串函数
CHAR_LENGTH()
返回值为字符串的长度 。
SELECTCHAR_LENGTH('cic');--包含空格SELECTLENGTH('Sql');
CONCAT(str1…)
拼接串联字符串。
SELECTCONCAT('My','S','ql');SELECTCONCAT('My',NULL,'QL');--包含Null则返回NullSELECTCONCAT("%","Java","%");--mybatis中拼接模糊查询
ELT(N,str1,str2,…)
若N = 1,则返回值为 str1 ,若N = 2,则返回值为 str2 ,以此类推,可以用来转换返回页面的状态。
SELECTELT(1,'提交','审核中','规则通过');SELECTELT(2,'提交','审核中','规则通过');
FORMAT(X,D)
格式化数字类型。
SELECTFORMAT(3.1455,2);--四舍五入保留两位SELECTTRUNCATE(3.1455,2);--直接截取两位
TRIM(str)
清空字符串空格。
SELECTLTRIM('hello');--清空左边SELECTRTRIM('hello');--清空右边SELECTTRIM('hello');--清空两边SELECTREPLACE('MySQL','','');--替换掉全部空格3、数值函数
FLOOR(X)
返回不大于X的最大整数值 。
SELECTFLOOR(1.23);--1SELECTFLOOR(-1.23);---2
MOD(N,M)
模操作。返回N 被 M除后的余数。
SELECTMOD(29,9);--2SELECT29MOD9;--2
RAND() RAND(N)
返回一个随机浮点值,范围在0到1之间。若已指定一个整数参数 N ,则它被用作种子值,用来产生重复序列。
SELECTRAND();--0.923SELECTRAND(20)=RAND(20);--TRUE4、时间日期函数
ADDDATE(date,INTERVAL expr type)
给指定日期,以指定类型进行运算。
SELECTDATE_ADD('2019-12-29',INTERVAL3DAY);--2020-01-01
CURDATE()
将当前日期按照’YYYY-MM-DD’ 或YYYYMMDD 格式的值返回,具体格式根据函数用在字符串或是数字语境中而定。
SELECTCURDATE();--'2019-12-29'字符串SELECTCURDATE()+0;--20180725数字
DATE(expr)
提取日期或时间日期表达式expr中的日期部分。
SELECTDATE('2019-12-3101:02:03');--'2019-12-31'SELECTDATE('2019-12-3101:02:03')+0;--20191231
DATE_FORMAT(date,format)
根据format 字符串进行 date 值的格式化。
SELECTDATE_FORMAT(NOW(),'%Y-%m-%d');--2019-12-29SELECTDATE_FORMAT(NOW(),'%Y年%m月%d日');--2019年12月29日5、聚合函数
AVG([distinct]expr)求平均值COUNT({*|[distinct]}expr)统计行的数量MAX([distinct]expr)求最大值MIN([distinct]expr)求最小值SUM([distinct]expr)求累加和二、自定义函数1、概念简介
函数存储着一系列sql语句,调用函数就是一次性执行这些语句。所以函数可以降低语句重复。函数注重返回值,而触发器注重执行过程,所以一些语句无法执行。所以函数并不是单纯的sql语句集合。
2、使用方式createfunction函数名([参数列表])returns数据类型beginsql语句;return值;end;
参数列表的格式是: 变量名 数据类型。
无参案例
CREATEFUNCTIONmysum1()RETURNSINTRETURN(2+3)*2;SELECTmysum1();
有参函数
表结构
CREATETABLEt01_user(idint(11)NOTNULLAUTO_INCREMENTPRIMARYKEYCOMMENT'主键ID',user_namevarchar(20)DEFAULTNULLCOMMENT'用户名称')ENGINE=InnoDBDEFAULTCHARSET=utf8COMMENT'用户表';
函数用法
createfunctionget_name(p_idINT)returnsVARCHAR(20)begindeclareuserNamevarchar(20);selectuser_namefromt01_userwhereid=p_idintouserName;returnuserName;end;SELECTget_name(1);3、函数查看
showcreatefunctionget_name;4、删除函数
dropfunctionget_name;5、函数注意事项
函数是事先经过编译,才能在服务器环境调用,所以MySQL集群环境需要同步编译;MySQL是多线程环境,所以要保证函数也是线程安全 。
三、触发器1、触发器简介触发器是特殊的存储过程,不同的是存储过程要用CALL来调用,而触发器不需要使用CALL。也不需要手工启动,只要当一个预定义的事件发生的时候,就会被MYSQL自动触发调用。
2、创建触发器触发器语法
CREATETRIGGERtrigger_nametrigger_timetrigger_eventONtbl_nameFOREACHROWtrigger_stmt
trigger_name:触发器命名 ;
trigger_time: 触发动作的时间 ;
trigger_event: 激活触发器的语句类型 ;
tbl_name: 触发器作用的表明,非临时表 ;
trigger_stmt:触发程序执行的语句 ;
表数据同步
当向用户表 t01_user
写入数据时,同时向 t02_back
表写入一份备份数据。
--用户备份表CREATETABLEt02_back(idint(11)NOTNULLPRIMARYKEYCOMMENT'主键ID',user_namevarchar(20)DEFAULTNULLCOMMENT'用户名称')ENGINE=InnoDBDEFAULTCHARSET=utf8COMMENT'用户备份';--触发器程序DROPTRIGGERIFEXISTSuser_back_trigger;CREATETRIGGERuser_back_triggerAFTERINSERTONt01_userFOREACHROWBEGININSERTINTOt02_back(id,user_name)VALUES(new.id,new.user_name);END;--测试案例INSERTINTOt01_user(user_name)VALUES('smile'),('mysql');SELECT*FROMt02_back;3、查看触发器
查看触发器是指数据库中已存在的触发器的定义、状态、语法信息等。可以在TRIGGERS表中查看触发器信息。
SELECT*FROM`information_schema`.`TRIGGERS`WHERE`TRIGGER_NAME`='user_back_trigger';4、删除触发器
DROP TRIGGER语句可以删除MYSQL中已经定义的触发器,删除触发器的基本语法。
DROPTRIGGER[schema_name.]trigger_name5、触发器注意事项
触发事件
对于相同的表,相同的事件只能创建一个触发器,比如对表t01_user创建两次AFTER INSERT触发器,就会报错。
执行效率
触发器可以减少应用端和数据库的通信次数和业务逻辑,但是基于行触发的逻辑,如果数据集非常大,效率会降低。
事务问题
触发器执行和原表的执行语句是否在同一个事务中,取决于触发表的存储引擎是否支持事务。
感谢各位的阅读,以上就是“MySQL系统和自定义函数、触发器的使用方法是什么”的内容了,经过本文的学习后,相信大家对MySQL系统和自定义函数、触发器的使用方法是什么这一问题有了更深刻的体会,具体使用情况还需要大家实践验证。这里是亿速云,小编将为大家推送更多相关知识点的文章,欢迎关注!
声明:本站所有文章资源内容,如无特殊说明或标注,均为采集网络资源。如若本站内容侵犯了原著者的合法权益,可联系本站删除。