文章详情

短信预约信息系统项目管理师 报名、考试、查分时间动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL基础篇(03):系统和自定义函数总结,触发器使用详解

2016-07-21 13:20

关注

MySQL基础篇(03):系统和自定义函数总结,触发器使用详解

本文源码:GitHub·点这里 || GitEE·点这里

MySQL 有很多内置的函数,可以快速解决开发中的一些业务需求,大概包括流程控制函数,数值型函数、字符串型函数、日期时间函数、聚合函数等。以下列出了这些分类中常用的函数。

1、控制流程函数

根据值判断返回值,类比编程中的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;

如果表达式 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');

如果表达式 expr1不为NULL,则返回值为expr1;否则返回值为 expr2。

SELECT IFNULL(NULL,'cicada');
SELECT IFNULL(1/1,'no');

2、常用字符串函数

返回值为字符串的长度 。

SELECT CHAR_LENGTH(' c i c ') ;-- 包含空格
SELECT LENGTH(' S q l ') ;

拼接串联字符串。

SELECT CONCAT('My', 'S', 'ql');
SELECT CONCAT('My', NULL, 'QL'); -- 包含Null 则返回Null
SELECT CONCAT("%", "Java", "%"); -- mybatis中拼接模糊查询

若N = 1,则返回值为 str1 ,若N = 2,则返回值为 str2 ,以此类推,可以用来转换返回页面的状态。

SELECT ELT(1,'提交','审核中','规则通过') ;
SELECT ELT(2,'提交','审核中','规则通过') ;

格式化数字类型。

SELECT FORMAT(3.1455,2) ; -- 四舍五入保留两位
SELECT TRUNCATE(3.1455,2) ; -- 直接截取两位

清空字符串空格。

SELECT LTRIM('  hel l o ') ;-- 清空左边
SELECT RTRIM('  hel l o ') ;-- 清空右边
SELECT TRIM('  hel l o ') ; -- 清空两边
SELECT REPLACE('M y S Q L',' ','') ; -- 替换掉全部空格

3、数值函数

返回不大于X的最大整数值 。

SELECT FLOOR(1.23); -- 1
SELECT FLOOR(-1.23); -- -2

模操作。返回N 被 M除后的余数。

SELECT MOD(29,9); -- 2
SELECT 29 MOD 9; -- 2

返回一个随机浮点值,范围在0到1之间。若已指定一个整数参数 N ,则它被用作种子值,用来产生重复序列。

SELECT RAND(); -- 0.923
SELECT RAND(20) = RAND(20) ; -- TRUE

4、时间日期函数

给指定日期,以指定类型进行运算。

SELECT DATE_ADD('2019-12-29', INTERVAL 3 DAY); -- 2020-01-01

将当前日期按照"YYYY-MM-DD" 或YYYYMMDD 格式的值返回,具体格式根据函数用在字符串或是数字语境中而定。

SELECT CURDATE(); -- '2019-12-29' 字符串
SELECT CURDATE() + 0; -- 20180725 数字

提取日期或时间日期表达式expr中的日期部分。

SELECT DATE('2019-12-31 01:02:03'); -- '2019-12-31'
SELECT DATE('2019-12-31 01:02:03')+0; -- 20191231

根据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

表数据同步

当向用户表 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触发器,就会报错。

触发器可以减少应用端和数据库的通信次数和业务逻辑,但是基于行触发的逻辑,如果数据集非常大,效率会降低。

触发器执行和原表的执行语句是否在同一个事务中,取决于触发表的存储引擎是否支持事务。

GitHub·地址
https://github.com/cicadasmile/mysql-data-base
GitEE·地址
https://gitee.com/cicadasmile/mysql-data-base

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯