MySQL系统和自定义函数、触发器的使用办法是什么
发布时间：2022-01-11 13:55:01 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要讲解了MySQL系统和自定义函数、触发器的使用方法是什么，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习MySQL系统和自定义函数、触发器的使用方法是什么吧！ 一、系统封装函数 MySQL 有很多内
|
这篇文章主要讲解了“MySQL系统和自定义函数、触发器的使用方法是什么”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“MySQL系统和自定义函数、触发器的使用方法是什么”吧！ 一、系统封装函数 MySQL 有很多内置的函数，可以快速解决开发中的一些业务需求，大概包括流程控制函数，数值型函数、字符串型函数、日期时间函数、聚合函数等。以下列出了这些分类中常用的函数。 1、控制流程函数 case…when 根据值判断返回值，类比编程中的IF-ELSE判断。 -- DEMO 01 SELECT CASE DATE_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; -- DEMO 02 SELECT (CASE WHEN 1>0 THEN 'true' ELSE 'false' END) AS result; if(expr1,expr2,expr3) 如果表达式 expr1 是TRUE，则 IF()的返回值为expr2; 否则返回值则为 expr3。 SELECT IF(1>2,'1>2','1<2') AS result ; SELECT IF(1<2,'yes ','no') AS result ; SELECT IF(STRCMP('test','test'),'no','yes'); ifnull(expr1,expr2) 如果表达式 expr1不为NULL，则返回值为expr1;否则返回值为 expr2。 SELECT IFNULL(NULL,'cicada'); SELECT IFNULL(1/1,'no'); 2、常用字符串函数 CHAR_LENGTH() 返回值为字符串的长度 。 SELECT CHAR_LENGTH(' c i c ') ;-- 包含空格 SELECT LENGTH(' S q l ') ; CONCAT(str1…) 拼接串联字符串。 SELECT CONCAT('My', 'S', 'ql'); SELECT CONCAT('My', NULL, 'QL'); -- 包含Null 则返回Null SELECT CONCAT("%", "Java", "%"); -- mybatis中拼接模糊查询 ELT(N,str1,str2,…) 若N = 1，则返回值为 str1 ，若N = 2，则返回值为 str2 ，以此类推，可以用来转换返回页面的状态。 SELECT ELT(1,'提交','审核中','规则通过') ; SELECT ELT(2,'提交','审核中','规则通过') ; FORMAT(X,D) 格式化数字类型。 SELECT FORMAT(3.1455,2) ; -- 四舍五入保留两位 SELECT TRUNCATE(3.1455,2) ; -- 直接截取两位 TRIM(str) 清空字符串空格。 SELECT LTRIM(' hel l o ') ;-- 清空左边 SELECT RTRIM(' hel l o ') ;-- 清空右边 SELECT TRIM(' hel l o ') ; -- 清空两边 SELECT REPLACE('M y S Q L',' ','') ; -- 替换掉全部空格 3、数值函数 FLOOR(X) 返回不大于X的最大整数值 。 SELECT FLOOR(1.23); -- 1 SELECT FLOOR(-1.23); -- -2 MOD(N,M) 模操作。返回N 被 M除后的余数。 SELECT MOD(29,9); -- 2 SELECT 29 MOD 9; -- 2 RAND() RAND(N) 返回一个随机浮点值，范围在0到1之间。若已指定一个整数参数 N ，则它被用作种子值，用来产生重复序列。 SELECT RAND(); -- 0.923 SELECT RAND(20) = RAND(20) ; -- TRUE 4、时间日期函数 ADDDATE(date,INTERVAL expr type) 给指定日期，以指定类型进行运算。 SELECT DATE_ADD('2019-12-29', INTERVAL 3 DAY); -- 2020-01-01 CURDATE() 将当前日期按照’YYYY-MM-DD’ 或YYYYMMDD 格式的值返回，具体格式根据函数用在字符串或是数字语境中而定。 SELECT CURDATE(); -- '2019-12-29' 字符串 SELECT CURDATE() + 0; -- 20180725 数字 DATE(expr) 提取日期或时间日期表达式expr中的日期部分。 SELECT DATE('2019-12-31 01:02:03'); -- '2019-12-31' SELECT DATE('2019-12-31 01:02:03')+0; -- 20191231 DATE_FORMAT(date,format) 根据format 字符串进行 date 值的格式化。 SELECT DATE_FORMAT(NOW(), '%Y-%m-%d'); -- 2019-12-29 SELECT DATE_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、使用方式 create function 函数名([参数列表]) returns 数据类型 begin sql语句; return 值; end; 参数列表的格式是： 变量名 数据类型。 无参案例 CREATE FUNCTION mysum1 () RETURNS INT RETURN (2+3)*2; SELECT mysum1 () ; 有参函数 表结构 CREATE TABLE t01_user ( id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID', user_name varchar(20) DEFAULT NULL COMMENT '用户名称' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '用户表'; 函数用法 create function get_name(p_id INT) returns VARCHAR(20) begin declare userName varchar(20); select user_name from t01_user where id=p_id into userName; return userName; end; SELECT get_name(1) ; 3、函数查看 show create function get_name ; 4、删除函数 drop function get_name ; 5、函数注意事项 函数是事先经过编译，才能在服务器环境调用，所以MySQL集群环境需要同步编译；MySQL是多线程环境，所以要保证函数也是线程安全 。 三、触发器 1、触发器简介 触发器是特殊的存储过程，不同的是存储过程要用CALL来调用，而触发器不需要使用CALL。也不需要手工启动，只要当一个预定义的事件发生的时候，就会被MYSQL自动触发调用。 2、创建触发器 触发器语法 CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt trigger_name：触发器命名 ; trigger_time: 触发动作的时间 ; trigger_event: 激活触发器的语句类型 ; tbl_name: 触发器作用的表明，非临时表 ; trigger_stmt：触发程序执行的语句 ; 表数据同步 当向用户表 t01_user 写入数据时，同时向 t02_back 表写入一份备份数据。 -- 用户备份表 CREATE TABLE t02_back ( id int(11) NOT NULL PRIMARY KEY COMMENT '主键ID', user_name varchar(20) DEFAULT NULL COMMENT '用户名称' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '用户备份'; -- 触发器程序 DROP TRIGGER IF EXISTS user_back_trigger ; CREATE TRIGGER user_back_trigger AFTER INSERT ON t01_user FOR EACH ROW BEGIN INSERT INTO t02_back (id,user_name) VALUES (new.id,new.user_name); END ; -- 测试案例 INSERT INTO t01_user (user_name) VALUES ('smile'),('mysql') ; SELECT * FROM t02_back ; 3、查看触发器 查看触发器是指数据库中已存在的触发器的定义、状态、语法信息等。可以在TRIGGERS表中查看触发器信息。 SELECT * FROM `information_schema`.`TRIGGERS` WHERE `TRIGGER_NAME`='user_back_trigger'; 4、删除触发器 DROP TRIGGER语句可以删除MYSQL中已经定义的触发器，删除触发器的基本语法。 DROP TRIGGER [schema_name.]trigger_name 5、触发器注意事项 触发事件 对于相同的表，相同的事件只能创建一个触发器，比如对表t01_user创建两次AFTER INSERT触发器，就会报错。 执行效率 触发器可以减少应用端和数据库的通信次数和业务逻辑，但是基于行触发的逻辑，如果数据集非常大，效率会降低。 事务问题 触发器执行和原表的执行语句是否在同一个事务中，取决于触发表的存储引擎是否支持事务。 感谢各位的阅读，以上就是“MySQL系统和自定义函数、触发器的使用方法是什么”的内容了，经过本文的学习后，相信大家对MySQL系统和自定义函数、触发器的使用方法是什么这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。 （编辑：昌吉站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐