Mysql--基础篇--函数（字符串函数，日期函数，数值函数，聚合函数，自定义函数及与存储过程的区别等）

ops/2025/1/12 0:19:36/

MySQL提供了丰富的内置函数，涵盖了字符串处理、数值计算、日期和时间操作、聚合统计、控制流等多种功能。这些函数可以帮助你简化SQL查询，提升开发效率。
除了内置函数，MySQL还支持自定义函数（User-Defined Functions），允许用户根据自己的需求编写复杂的逻辑。

一、MySQL内置函数

1、字符串函数

字符串函数用于处理和操作文本数据。以下是一些常用的字符串函数：

1.1、CONCAT()
  • 作用：连接多个字符串。
  • 语法：CONCAT(str1, str2, …, strN)

示例：

SELECT CONCAT('Hello', ' ', 'World');  -- 输出: Hello World
1.2、CONCAT_WS()
  • 作用：使用指定的分隔符连接多个字符串。
  • 语法：CONCAT_WS(separator, str1, str2, …, strN)

示例：

SELECT CONCAT_WS('-', '2024', '12', '31');  -- 输出: 2024-12-31

运行结果：
在这里插入图片描述

1.3、SUBSTRING()或SUBSTR()
  • 作用：从字符串中提取子字符串。
  • 语法：SUBSTRING(str, pos, len) 或 SUBSTR(str, pos, len)

示例：

SELECT SUBSTRING('Hello World', 7, 5);  -- 从第7个元素开始，截取5个元素。输出: World
1.4、LEFT()和RIGHT()
  • 作用：分别从字符串的左侧或右侧提取指定长度的字符。
  • 语法：LEFT(str, len)和RIGHT(str, len)

示例：

SELECT LEFT('Hello World', 5);  -- 从最左侧开始截取5个元素。输出: Hello
SELECT RIGHT('Hello World', 5); -- 从最右侧开始截取5个元素。输出: World
1.5、LENGTH()和CHAR_LENGTH()
  • 作用：分别返回字符串的字节长度和字符长度。
  • 语法：LENGTH(str)和CHAR_LENGTH(str)

示例：

SELECT LENGTH('Hello'), CHAR_LENGTH('Hello');  -- 输出: 5, 5
SELECT LENGTH('你好'), CHAR_LENGTH('你好');     -- 输出: 6, 2 (中文字符占多个字节)
1.6、LOWER()和UPPER()
  • 作用：将字符串转换为小写或大写。
  • 语法：LOWER(str) 和 UPPER(str)

示例：

SELECT LOWER('HELLO'), UPPER('hello');  -- 输出: hello, HELLO
1.7、TRIM()
  • 作用：去除字符串两端的空格或其他指定字符。
  • 语法：TRIM([BOTH | LEADING | TRAILING] [remstr] FROM str)

示例：

  SELECT TRIM('   Hello World   ');  -- 输出: Hello WorldSELECT TRIM(BOTH '_' FROM '__Hello__');  -- 输出: Hello
1.8、REPLACE()
  • 作用：替换字符串中的某个子字符串。
  • 语法：REPLACE(str, from_str, to_str)

示例：

SELECT REPLACE('Hello World', 'World', 'Universe');  -- 输出: Hello Universe

2、数值函数

数值函数用于处理和计算数值数据。

2.1、ABS()
  • 作用：返回数值的绝对值。
  • 语法：ABS(num)

示例：

SELECT ABS(-10);  -- 输出: 10
2.2、CEIL()和FLOOR()
  • 作用：分别返回大于或等于给定数值的最小整数，以及小于或等于给定数值的最大整数。
  • 语法：CEIL(num)和FLOOR(num)

示例：

SELECT CEIL(3.14), FLOOR(3.14);  -- 输出: 4, 3
2.3、ROUND()
  • 作用：对数值进行四舍五入。
  • 语法：ROUND(num, decimals)

示例：

SELECT ROUND(3.14159, 2);  -- 输出: 3.14
SELECT ROUND(3.14159);  -- 输出: 3
2.4、MOD()
  • 作用：返回两个数相除后的余数。
  • 语法：MOD(num1, num2)或num1 % num2

示例：

 SELECT MOD(10, 3);  -- 输出: 1
2.5、POW()和POWER()
  • 作用：返回第一个参数的幂次方。
  • 语法：POW(base, exponent) 或 POWER(base, exponent)

示例：

 SELECT POW(2, 3);  -- 输出: 8
2.6、SQRT()
  • 作用：返回数值的平方根。
  • 语法：SQRT(num)

示例：

SELECT SQRT(16);  -- 输出: 4
2.7、RAND()
  • 作用：返回一个0到1之间的随机浮点数。
  • 语法：RAND()

示例：

  SELECT RAND();  -- 输出: 随机浮点数

运行结果：
在这里插入图片描述

3、日期和时间函数

日期和时间函数用于处理和操作日期、时间数据。

3.1、NOW()和CURRENT_TIMESTAMP()
  • 作用：返回当前的日期和时间。
  • 语法：NOW() 和 CURRENT_TIMESTAMP()

示例：

  SELECT NOW();  -- 输出: 当前日期和时间

运行结果：
在这里插入图片描述

3.2、CURDATE()和CURTIME()
  • 作用：分别返回当前的日期和时间。
  • 语法：CURDATE()和CURTIME()

示例：

SELECT CURDATE(), CURTIME();  -- 输出: 当前日期, 当前时间

在这里插入图片描述

3.3、DATE()和TIME()
  • 作用：分别提取日期和时间部分。
  • 语法：DATE(datetime) 和 TIME(datetime)

示例：

 SELECT DATE('2024-12-31 14:30:00'), TIME('2024-12-31 14:30:00');  -- 输出: 2024-12-31, 14:30:00
3.4、YEAR()、MONTH()、DAY()等
  • 作用：提取日期中的年、月、日等部分。
  • 语法：YEAR(date)、MONTH(date)、DAY(date) 等

示例：

  SELECT YEAR('2024-12-31'), MONTH('2024-12-31'), DAY('2024-12-31');  -- 输出: 2024, 12, 31
3.5、DATEDIFF()
  • 作用：计算两个日期之间的天数差。
  • 语法：DATEDIFF(date1, date2)

示例：

 SELECT DATEDIFF('2024-12-31', '2024-01-01');  -- 输出: 365
3.6、TIMESTAMPDIFF()
  • 作用：计算两个日期或时间之间的差异，支持多种单位（如年、月、日、小时等）。
  • 语法：TIMESTAMPDIFF(unit, datetime1, datetime2)

示例：

  SELECT TIMESTAMPDIFF(YEAR, '1990-01-01', '2024-12-31');  -- 输出: 34
3.7、DATE_ADD()和DATE_SUB()
  • 作用：在日期上添加或减去指定的时间间隔。
  • 语法：DATE_ADD(date, INTERVAL expr unit) 和 DATE_SUB(date, INTERVAL expr unit)

示例：

  SELECT DATE_ADD('2024-12-31', INTERVAL 1 DAY);  -- 增加1个月。输出: 2025-01-01SELECT DATE_SUB('2024-12-31', INTERVAL 1 MONTH);  -- 减去1个月。输出: 2024-11-30

4、聚合函数

聚合函数用于对一组数据进行汇总计算。以下是一些常用的聚合函数：

4.1、COUNT()
  • 作用：计算行数。
  • 语法：COUNT()或COUNT(column)

示例：

 SELECT COUNT() FROM employees;  -- 计算表中所有行数SELECT COUNT(salary) FROM employees;  -- 计算非空 salary 的行数
4.2、SUM()
  • 作用：计算数值列的总和。
  • 语法：SUM(column)

示例：

 SELECT SUM(salary) FROM employees;  -- 计算所有员工的工资总和
4.3、AVG()
  • 作用：计算数值列的平均值。
  • 语法：AVG(column)

示例：

 SELECT AVG(salary) FROM employees;  -- 计算所有员工的平均工资
4.4、MAX()和MIN()
  • 作用：分别返回数值列中的最大值和最小值。
  • 语法：MAX(column)和MIN(column)

示例：

  SELECT MAX(salary), MIN(salary) FROM employees;  -- 返回最高和最低工资
4.5、GROUP_CONCAT()
  • 作用：将多行数据合并为一个字符串。
  • 语法：GROUP_CONCAT(column [SEPARATOR separator])

示例：

  SELECT department, GROUP_CONCAT(name SEPARATOR ', ') AS employeesFROM employeesGROUP BY department;

运行结果：
在这里插入图片描述

5、控制流函数

控制流函数用于在查询中实现条件逻辑。以下是一些常用的控制流函数：

5.1、IF()
  • 作用：根据条件返回不同的值。
  • 语法：IF(condition, true_value, false_value)

示例：

  SELECT name, IF(salary > 50000, 'High', 'Low') AS salary_levelFROM employees;

运行结果：
在这里插入图片描述

5.2、CASE表达式
  • 作用：实现多条件判断。
  • 语法：
  CASEWHEN condition1 THEN result1WHEN condition2 THEN result2...ELSE default_resultEND

示例：

  SELECT name,CASEWHEN salary > 80000 THEN 'Senior'WHEN salary > 50000 THEN 'Mid-level'ELSE 'Junior'END AS levelFROM employees;

运行结果：
在这里插入图片描述

5.3、COALESCE()
  • 作用：返回第一个非空的表达式。
  • 语法：COALESCE(expr1, expr2, …, exprN)

示例：

  SELECT COALESCE(phone, email, 'No contact info') AS contact_infoFROM employees;

运行结果：
在这里插入图片描述
说明：
COALESCE（参数1，参数2…）,方法可以包含多个参数，方法返回第一个不为空的参数值。

5.4、NULLIF()
  • 作用：如果两个表达式相等，则返回NULL，否则返回第一个表达式。
  • 语法：NULLIF(expr1, expr2)

示例：

  SELECT NULLIF(manager_id, 0) AS managerIdFROM employees;

运行结果：
manger_id和null（本例的0）作比较，如果不相等返回第一个表达式的值，如果相等则返回null
在这里插入图片描述
manger_id和manger_id自身相比，肯定都是相同的，都返回了null
在这里插入图片描述

6、加密和哈希函数

加密和哈希函数用于生成加密字符串或哈希值。以下是一些常用的加密和哈希函数：

6.1、MD5()
  • 作用：生成MD5哈希值。
  • 语法：MD5(str)

示例：

 SELECT MD5('password');  -- 输出: 5f4dcc3b5aa765d61d8327deb882cf99

运行结果：
在这里插入图片描述

6.2、SHA1()和SHA2()
  • 作用：生成SHA-1或SHA-2哈希值。
  • 语法：SHA1(str)和SHA2(str, bit_length)

示例：

 SELECT SHA1('password');  -- 输出: 5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8SELECT SHA2('password', 256);  -- 输出: 5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8

运行结果：
在这里插入图片描述

6.3、AES_ENCRYPT()和AES_DECRYPT()
  • 作用：使用AES算法进行加密和解密。
  • 语法：AES_ENCRYPT(str, key)和AES_DECRYPT(encrypted_str, key)

示例：

 SELECT AES_ENCRYPT('secret', 'mykey');SELECT AES_DECRYPT(AES_ENCRYPT('secret', 'mykey'), 'mykey');

运行结果：
在这里插入图片描述

7、其他函数

除了上述分类，MySQL还提供了一些其他类型的函数。

7.1、UUID()
  • 作用：生成一个唯一的UUID（通用唯一标识符）。
  • 语法：UUID()

示例：
SELECT UUID();
运行结果：
在这里插入图片描述

7.2、INET_ATON()和INET_NTOA()
  • 作用：将IP地址转换为整数，或将整数转换为IP地址。
  • 语法：INET_ATON(ip_address) 和 INET_NTOA(integer)

示例：
SELECT INET_ATON(‘192.168.1.1’); – 输出: 3232235777
SELECT INET_NTOA(3232235777); – 输出: 192.168.1.1
运行结果：
在这里插入图片描述

8、内置函数总结

MySQL提供了丰富多样的内置函数，涵盖了字符串处理、数值计算、日期和时间操作、聚合统计、控制流、加密和哈希操作等多个方面。合理使用这些函数可以大大简化SQL查询，提高开发效率并增强数据处理能力。

二、MySQL自定义函数

除了内置函数，MySQL还支持自定义函数（User-Defined Functions, UDF），允许用户根据自己的需求编写复杂的逻辑。

自定义函数可以分为两类：

  • 存储函数（Stored Functions）：使用SQL语法自定义编写函数，可以在数据库中存储并调用。
  • C语言编写的UDF（User-Defined Functions）：通过C或C++编写并编译为动态链接库（.so 文件），然后在MySQL中加载和使用。

1、存储函数（Stored Functions）

存储函数是使用SQL和PL/SQL编写的函数，类似于存储过程，但它们返回一个值。存储函数可以在SELECT、INSERT、UPDATE等SQL语句中直接调用。

1.1、创建存储函数

使用CREATE FUNCTION语句来创建存储函数。

创建存储函数的基本语法：

DELIMITER //
CREATE FUNCTION function_name (param1 type, param2 type, ...)
RETURNS return_type
DETERMINISTIC | NOT DETERMINISTIC
LANGUAGE SQL
BEGIN-- 函数体RETURN result;
END //
DELIMITER ;

解释：

  • function_name：函数的名称。
  • param1, param2, …：函数的参数列表，每个参数包括名称和类型。
  • RETURNS return_type：指定函数的返回值类型。
  • DETERMINISTIC | NOT DETERMINISTIC：指定函数是否是确定性的。如果函数对于相同的输入总是返回相同的结果，则为DETERMINISTIC；否则为NOT DETERMINISTIC。
  • LANGUAGE SQL：指定函数使用的语言，通常是SQL。
  • BEGIN … END：函数体，包含执行的逻辑。
  • RETURN result：返回结果。
1.2、示例1：简单的数学计算函数

假设我们想创建一个函数add_numbers，它接受两个整数作为参数，并返回它们的和。

示例：

DELIMITER //CREATE FUNCTION add_numbers(a INT, b INT)
RETURNS INT
DETERMINISTIC
BEGINDECLARE sum INT;SET sum = a + b;RETURN sum;
END //DELIMITER ;

运行结果：
在这里插入图片描述
调用示例：

SELECT add_numbers(5, 10);  -- 输出: 15

运行结果：
在这里插入图片描述

1.3、示例2：带有条件判断的函数

我们可以创建一个函数get_employee_level，它根据员工的工资返回其级别（如"Junior"、“Mid-level"或"Senior”）。

示例：

DELIMITER //CREATE FUNCTION get_employee_level(salary DECIMAL(10, 2))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGINDECLARE level VARCHAR(20);IF salary > 80000 THENSET level = 'Senior';ELSEIF salary > 50000 THENSET level = 'Mid-level';ELSESET level = 'Junior';END IF;RETURN level;
END //DELIMITER ;

运行结果：
在这里插入图片描述

调用示例：

SELECT name, salary, get_employee_level(salary) AS level
FROM employees;

运行结果：
在这里插入图片描述

1.4、示例3：带有游标的函数

假设我们有一个表orders，我们想创建一个函数get_total_orders，它接受客户ID作为参数，并返回该客户的所有订单总额。

创建表的sql：

CREATE TABLE orders (id INT AUTO_INCREMENT PRIMARY KEY,          -- 订单的唯一标识符customer_id INT NOT NULL,                   -- 客户的唯一标识符order_date DATE NOT NULL,                   -- 订单的日期amount DECIMAL(10, 2) NOT NULL             -- 订单的金额
);

示例数据：
在这里插入图片描述
创建函数示例：

DELIMITER //CREATE FUNCTION get_total_orders(customer_id_p INT)
RETURNS DECIMAL(10, 2)
DETERMINISTIC
BEGINDECLARE total DECIMAL(10, 2) DEFAULT 0;DECLARE order_amount DECIMAL(10, 2);DECLARE done INT DEFAULT 0;DECLARE cur CURSOR FORSELECT amount FROM orders WHERE customer_id = customer_id_p;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;OPEN cur;read_loop: LOOPFETCH cur INTO order_amount;IF done THENLEAVE read_loop;END IF;SET total = total + order_amount;END LOOP;CLOSE cur;RETURN total;
END //DELIMITER ;

解释：

  • DELIMITER //：更改分隔符为//，以便在函数体中使用;作为语句结束符，而不会提前终止函数定义。最后再将分隔符恢复为;。

  • CREATE FUNCTION get_total_orders(customer_id INT)：创建一个名为get_total_orders的存储函数，接受一个INT类型的参数customer_id_p（注意入参不要和表字段相同），表示要查询的客户ID。

  • RETURNS DECIMAL(10, 2)：指定函数返回一个DECIMAL(10, 2)类型的值，即订单总额，最多10位数字，其中2位是小数部分。

  • DETERMINISTIC：表示该函数对于相同的输入总是返回相同的结果。这意味着MySQL可以对其进行优化，因为它不会依赖外部状态或产生随机结果。

  • DECLARE total DECIMAL(10, 2) DEFAULT 0; ：定义total，用于累加所有订单的金额，初始值为0。

  • DECLARE order_amount DECIMAL(10, 2); ：定义order_amount，用于存储从游标中获取的每个订单的金额。

  • DECLARE done INT DEFAULT 0;：定义done，用于标记游标的遍历是否完成，初始值为0。当游标遍历到末尾时，done将被设置为1。

  • DECLARE cur CURSOR FOR：声明一个游标cur，用于遍历orders表中属于指定customer_id_p的所有订单的amount列。

  • SELECT amount FROM orders WHERE customer_id = customer_id_p; :这里的customer_id_p是函数的参数，表示我们要查询的客户ID。

  • DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; ：当游标遍历到末尾（即没有更多行可以读取）时，MySQL会触发NOT FOUND异常。我们使用CONTINUE HANDLER来捕获这个异常，并将done设置为1，表示遍历结束。

  • OPEN cur：打开游标，准备从orders表中读取数据。

  • read_loop: LOOP：开始一个循环，逐行读取游标中的数据。

  • FETCH cur INTO order_amount：从游标中获取当前行的amount值，并将其存储在 order_amount变量中。

  • IF done THEN LEAVE read_loop：如果done被设置为1，表示游标已经遍历到末尾，则退出循环。

  • SET total = total + order_amount：将当前订单的金额累加到total中。

  • CLOSE cur：关闭游标，释放资源。

  • RETURN total：返回累加后的订单总额。

创建函数结果：
在这里插入图片描述
调用示例：

SELECT get_total_orders(2001);  -- 返回客户ID为2001的所有订单总额

运行结果：
在这里插入图片描述

2、存储函数与存储过程的区别

在这里插入图片描述

3、存储函数的最佳实践

  • 保持函数简单：存储函数应该尽量简洁，避免过于复杂的逻辑。如果需要处理复杂的业务逻辑，考虑使用存储过程。
  • 使用DETERMINISTIC标记：如果函数对于相同的输入总是返回相同的结果，标记为DETERMINISTIC可以提高查询优化器的效率。
  • 避免副作用：存储函数不应该修改数据库中的数据，因为这可能会导致不可预测的行为。
  • 合理使用变量：在函数体内使用局部变量（DECLARE）来存储中间结果，避免重复计算。
  • 错误处理：使用SIGNAL语句抛出自定义错误，确保函数在遇到异常情况时能够正确处理。

4、自定义函数总结

MySQL的自定义函数功能非常强大，尤其是存储函数，允许你在数据库中封装复杂的逻辑并返回结果。通过合理使用存储函数，你可以简化SQL查询，提升代码的可读性和可维护性。

乘风破浪会有时，直挂云帆济沧海！！！


http://www.ppmy.cn/ops/149292.html

相关文章

el-upload on-preview 扩大预览事件点击范围

el-upload on-preview 扩大预览事件点击范围

1.首先说明开发环境&#xff0c;vue2项目&#xff0c;采用列表模式的el-upload组件时&#xff0c;加入附件预览功能 element官网给出的示范代码是以下写法 <el-uploadclass"upload-demo"action"https://jsonplaceholder.typicode.com/posts/":on-previ…
阅读更多...
【Linux】Linux开发：GDB调试器与Git版本控制工具指南

【Linux】Linux开发：GDB调试器与Git版本控制工具指南

Linux相关知识点可以通过点击以下链接进行学习一起加油&#xff01;初识指令指令进阶权限管理yum包管理与vim编辑器GCC/G编译器make与Makefile自动化构建 在 Linux 开发中&#xff0c;GDB 调试器和 Git 版本控制工具是开发者必备的利器。GDB 帮助快速定位代码问题&#xff0c;G…
阅读更多...
三天学完微服务其二

三天学完微服务其二

Nacos注册中心 启动Nacos 配置更新步骤 是修改nacos中的配置后&#xff0c;微服务中无需重启即可让配置生效&#xff0c;也就是**配置热更新**。 方式一 在Value注入的变量所在类上添加注解RefreshScope 方式二 使用ConfigurationProperties注解代替Value注解。 Nacos快速…
阅读更多...
GO通过SMTP协议发送邮件

GO通过SMTP协议发送邮件

什么是SMTP协议 SMTP&#xff08;Simple Mail Transfer Protocol&#xff0c;简单邮件传输协议&#xff09;是用于发送邮件的协议。当一个邮件服务器需要发送邮件给另一个邮件服务器时&#xff0c;它会使用SMTP协议与目标服务器建立连接&#xff0c;并传输邮件内容。SMTP协议的…
阅读更多...
Scala语言的面向对象编程

Scala语言的面向对象编程

Scala语言的面向对象编程 面向对象编程&#xff08;Object-Oriented Programming&#xff0c;OOP&#xff09;是一种编程范式&#xff0c;它使用“对象”来组织代码&#xff0c;这些对象能够包含数据&#xff08;属性&#xff09;以及功能&#xff08;方法&#xff09;。Scala…
阅读更多...
spring boot 多数据源集成mysql、postgresql、phoenix、doris等

spring boot 多数据源集成mysql、postgresql、phoenix、doris等

如何搭建多数据源项目只要以下简单几步; 一. 创建核心在config.datasource文件夹里 二. 引入相对应的jar包 三. 创建数据库连接配置 四. 写逻辑代码进行验证 1.DataSource package com.irootech.config.datasource;import java.lang.annotation.*;Target({ElementType.MET…
阅读更多...
基于Spring Boot的宠物健康顾问系统的设计与实现（LW+源码+讲解）

基于Spring Boot的宠物健康顾问系统的设计与实现（LW+源码+讲解）

专注于大学生项目实战开发,讲解,毕业答疑辅导&#xff0c;欢迎高校老师/同行前辈交流合作✌。 技术范围&#xff1a;SpringBoot、Vue、SSM、HLMT、小程序、Jsp、PHP、Nodejs、Python、爬虫、数据可视化、安卓app、大数据、物联网、机器学习等设计与开发。 主要内容&#xff1a;…
阅读更多...
【Uniapp-Vue3】uniapp创建组件

【Uniapp-Vue3】uniapp创建组件

项目右键-->新建-->目录-->取名components 注意&#xff1a;名字一定要是components&#xff0c;小写 右键components文件-->新建组件-->输入组件名称-->勾选创建同名目录-->创建 注意&#xff1a;命名要么使用驼峰命名法&#xff0c;如userHeader&…
阅读更多...
最新文章

Copyright @ 2022~2023