MySQL数据库基础 === 函数

前端_贾公子
78 阅读6分钟

MySQL数据库基础 === 函数

前言

在 SQL 世界中，函数是处理数据的强大工具，它们能够帮助我们轻松地对数据进行各种操作和转换。无论是对字符串进行拼接、截取、大小写转换，还是对数值进行四舍五入、取整、求余，亦或是对日期进行格式化、计算日期差，SQL 函数都能提供简洁而高效的解决方案。此外，流程函数则赋予了 SQL 更强的逻辑处理能力，使我们能够根据不同的条件来决定查询结果的走向。熟练掌握这些函数，对于提升 SQL 编程能力、优化数据处理流程具有重要意义。

函数

  • 字符串函数
  • 数值函数
  • 日期函数
  • 流程函数

字符串函数

常用函数：

函数功能
CONCAT(s1, s2, …, sn)字符串拼接，将s1, s2, …, sn拼接成一个字符串
LOWER(str)将字符串全部转为小写
UPPER(str)将字符串全部转为大写
LPAD(str, n, pad)左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
RPAD(str, n, pad)右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
TRIM(str)去掉字符串头部和尾部的空格
SUBSTRING(str, start, len)返回从字符串str从start位置起的len个长度的字符串
REPLACE(column, source, replace)替换字符串
-- 拼接
SELECT CONCAT('hello','mysql')
-- 小写
SELECT LOWER('Hello')
-- 大写
SELECT UPPER("hello")
-- 左填充
SELECT LPAD('01', 5, '-')
-- 右填充
SELECT RPAD('01', 5, '-');
-- 去除空格
SELECT TRIM(' Hello World ');
-- 切片（起始索引为1）
SELECT SUBSTRING('Hello World', 1, 5);
--  工号补零
UPDATE emp SET workno = LPAD(workno, 5, '0');

数值函数

常见函数：

函数功能
CEIL(x)向上取整
FLOOR(x)向下取整
MOD(x, y)返回x/y的模
RAND()返回0~1内的随机数
ROUND(x, y)求参数x的四舍五入值，保留y位小数
--  向上取整
SELECT CEIL(1.1)
-- 向下取整
SELECT FLOOR(1.9)
-- 取余 
SELECT MOD(4,2)
-- 随机数 0-1
SELECT RAND()
-- 四舍五入
SELECT ROUND(2.325,2) 
-- 六位验证码
SELECT  LPAD(ROUND(RAND() * 1000000,0),6,"0")

常用函数：

函数功能
CURDATE()返回当前日期
CURTIME()返回当前时间
NOW()返回当前日期和时间
YEAR(date)获取指定date的年份
MONTH(date)获取指定date的月份
DAY(date)获取指定date的日期
DATE_ADD(date, INTERVAL expr type)返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1, date2)返回起始时间date1和结束时间date2之间的天数
 
-- 返回当前日期
SELECT CURDATE()
-- 返回当前时间
SELECT CURTIME()
-- 返回当前日期和时间
SELECT NOW()
-- 获取指定date的年份
SELECT YEAR(NOW())
-- 	获取指定date的月份
SELECT MONTH(NOW())
-- 获取指定date的日期
SELECT DAY(NOW())
-- 加70年 INTERVAL 为固定值
SELECT DATE_ADD(NOW(),INTERVAL 70 YEAR)
-- 第一个时间 - 第二个时间
SELECT DATEDIFF('2021-01-01','2022-01-01')
-- 入职时间排序
SELECT name,DATEDIFF(CURDATE(),entrydate) AS 'entrydays' FROM emp ORDER BY  entrydays DESC;

流程函数

常用函数：

函数功能
IF(value, t, f)如果value为true，则返回t，否则返回f
IFNULL(value1, value2)如果value1不为空，返回value1，否则返回value2
CASE WHEN [ val1 ] THEN [ res1 ] … ELSE [ default ] END如果val1为true，返回res1，… 否则返回default默认值
CASE [ expr ] WHEN [ val1 ] THEN [ res1 ] … ELSE [ default ] END如果expr的值等于val1，返回res1，… 否则返回default默认值
-- 如果value为true，则返回t，否则返回f
SELECT IF(1 = 1, 't', 'f');
 
-- 如果value1不为空，返回value1，否则返回value2
SELECT  IFNULL("ok","err")
SELECT IFNULL(NULL,"err")
 
-- 如果val1为true，返回res1，… 否则返回default默认值
select name,（case workaddress 
	when '北京市' then '一线城市'
	when '广州市' then '一线城市'
	else '二线城市' end) as '工作地址'
from emp;
 
-- 如果expr的值等于val1，返回res1，… 否则返回default默认值
select name,(case 
when age > 30 then '中年'
else '青年' end)
from emp;
简单 CASE 表达式

简单 CASE 表达式用于基于一个表达式的值来选择结果。其基本语法如下：

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END

  • expression ：这是要评估的表达式。

  • WHEN value THEN result ：如果 expression 等于 value ，则返回 result 。

  • ELSE default_result ：如果 expression 不等于任何 WHEN 子句中的 value ，则返回 default_result 。

搜索 CASE 表达式

搜索 CASE 表达式用于基于多个条件来选择结果。其基本语法如下：

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

  • WHEN condition THEN result ：如果 condition 为真，则返回 result 。

  • ELSE default_result ：如果没有任何 WHEN 子句的条件为真，则返回 default_result 。

对比总结

  • 适用场景 ：

  • 简单 CASE ：适用于基于一个表达式的值进行判断的情况。例如，根据某个字段的值来选择结果。

  • 搜索 CASE ：适用于基于多个条件进行判断的情况。例如，根据多个不同的条件来选择结果。

  • 灵活性 ：

  • 简单 CASE ：只能基于一个表达式的值进行判断，适用于简单的值匹配。

  • 搜索 CASE ：可以基于复杂的逻辑条件进行判断，适用于更复杂的逻辑判断。

根据具体的需求和场景选择合适的 CASE 表达式形式，可以使 SQL 查询更加清晰和高效。

SQL 函数与表达式总结

字符串函数

  • CONCAT(s1, s2, ..., sn) ：拼接多个字符串。

  • LOWER(str) ：将字符串转为小写。

  • UPPER(str) ：将字符串转为大写。

  • LPAD(str, n, pad) ：左填充字符串至指定长度。

  • RPAD(str, n, pad) ：右填充字符串至指定长度。

  • TRIM(str) ：去除字符串首尾空格。

  • SUBSTRING(str, start, len) ：截取指定位置和长度的子字符串。

  • REPLACE(column, source, replace) ：替换字符串中的字符。

数值函数

  • CEIL(x) ：向上取整。

  • FLOOR(x) ：向下取整。

  • MOD(x, y) ：计算 x 除以 y 的余数。

  • RAND() ：生成 0 到 1 之间的随机数。

  • ROUND(x, y) ：将 x 四舍五入到小数点后 y 位。

日期函数

  • CURDATE() ：返回当前日期。

  • CURTIME() ：返回当前时间。

  • NOW() ：返回当前日期和时间。

  • YEAR(date) ：获取日期的年份部分。

  • MONTH(date) ：获取日期的月份部分。

  • DAY(date) ：获取日期的日部分。

  • DATE_ADD(date, INTERVAL expr type) ：将时间间隔加到日期上。

  • DATEDIFF(date1, date2) ：计算两个日期之间的天数差。

流程函数

  • IF(value, t, f) ：条件为真返回 t，否则返回 f。

  • IFNULL(value1, value2) ：value1 不为空返回 value1，否则返回 value2。

  • CASE WHEN [val1] THEN [res1] ... ELSE [default] END ：根据条件选择结果。

  • CASE [expr] WHEN [val1] THEN [res1] ... ELSE [default] END ：根据表达式值选择结果。

CASE 表达式

  • 简单 CASE ：基于表达式值选择结果，适用于值匹配。

  • 搜索 CASE ：基于多个条件选择结果，适用于复杂逻辑判断。

avatar
文章
阅读
粉丝