Mysql函数汇总-CFTA金融科技基础

MySQL 函数

函数表示对输入参数值返回一个具有特定关系的值, MySQL提供了大量丰富的函数,在进行数据库管理以及数据的查询和操作时将会经常用到各种函数。通过对数据的处理,数据库功能可以变得更加强大,更加灵活地满足不同用户的需求。各类函数从功能方面主要分为以下几类:数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数和加密函数等。

数学函数

数学函数主要用来处理数值数据,主要的数学函数有:绝对值函数、三角函数(包括正弦函数、余弦函数、正切函数、余切函数等)、对数函数、随机数函数等。在有错误产生时,数学函数将会返回空值NULL。

绝对值函数

绝对值函数 ABS(X)返回X的绝对值。

圆周率函数

返回圆周率的函数PI（）返回圆周率π的值。默认的显示小数位数是6位。

平均值函数

avg(X) 返回列X的平均值

获取整数的函数

CEIL(x)、 CEILING(x)和 FLOOR(x)

CEIL(x)和 CEILING(X)意义相同,返回不小于x的最小整数值,返回值转化为一个BIGINT。

FLOOR(x)返回不大于x的最大整数值,返回值转化为一个 BIGINT

获取随机数的函数

RAND()和RAND(x)

RAND(x)返回一个随机浮点值v,范围在0到1之间(即0≤v≤1.0)。若已指定一个整数参数x,则它被用作种子值,用来产生重复序列不带参数的 RANDO每次产生的随机数值是不同的。

当RAND(x)的参数相同时,将产生相同的随机数,不同的x产生的随机数值不同。

截取函数

• 函数 ROUND(x)、 ROUND(x,y)和 TRUNCATE(x,y)

ROUND(x)返回最接近于参数x的整数,对x值进行四舍五入,只保留了各个值的整数部分。

ROUND(x,y)返回最接近于参数x的数,其值保留到小数点后面y位,若y为负值,则将保留x值到小数点左边y位，y值为负数时,保留的小数点左边的相应位数直接保存为0,不进行四舍五入。TRUNCATE(xy)返回被舍去至小数点后y位的数字x.若y的值为0,则结果不带有小数点或不带有小数部分。若y设为负数,则截去(归零)x小数点左起第y位开始后面所有低位的值

字符串函数

字符串函数主要用来处理数据库中的字符串数据, MySQL中字符串函数有:计算字符串长度函数、字符串合并函数、字符串替换函数、字符串比较函数、查找指定字符串位置函数等。

• 计算字符串字符数的函数和字符串长度的函数

LENGTH(str)返回值为字符串sr所包含的字符个数。一个多字节字符算作一个单字符。

• 合并字符串函数 CONCAT(s1,s2,…)、 CONCAT_WS(x,s1,s2,…)

CONCAT(s1,s2,)返回结果为连接参数产生的字符串,或许有一个或多个参数。如有任何一个参数为NULL,则返回值为NULL。

• 获取指定长度的字符串的函数LEFT(s,n)和 RIGHT(sn)

LEFT(s,n)返回字符串s开始的最左边n个字符；RIGHT(s,n)返回字符串中最右边n个字符。

• 删除空格的函数(s)、RTRM(s)和TRM(s)

LTRIM(s)返回字符串s,字符串左侧空格字符被删除； RTRIM(s)返回字符串s,字符串右侧空格字符被删除；TRIM(s)删除字符串s两侧的空格。

• 删除指定字符串的函数TRIM(s1 FROM s)

TRIM(s1 FROM s)删除字符串s中两端所有的子字符串s1，s1为可选项,在未指定情况下, 删除空格。

• 重复生成字符串的函数 REPEAT(s，n)

REPEAT(s,n)返回一个由重复的字符串s组成的字符串, 若等于s<=0 ，则返回一个空字符串，若s或n为NULL,则返回NULL。

• 空格SPACE函数(n)和替换函数 REPLACE(s,s1,s2)

SPACE(n)返回一个由n个空格组成的字符串。

REPLACE(s,s1,s2)使用字符串s2替代字符串s中所有的字符串s1

• 匹配子串开始位置的函数

LOCATE(str1,str)、 POSITION(str1 IN str)和INSTR(str,str1)3个函数作用相同,返回子字符串str1在字符串str中的开始位置。

• 返回子串位置的函数FIND_IN_SET(s1,s2)

FIND IN SET(s1,s2)返回字符串s1在字符串列表s2中出现的位置,字符串列表是一个由多个逗号,分开的字符串组成的列表。如果s1不在s2或s2为空字符串,则返回值为0。如果任意一个参数为NULL,则返回值为NULL。

这个函数在第一个参数包含一个逗号‘，’时将无法正常运行。

• upper('abcdefg') 将字符串转换大写

• lower ('abcdefg') 将字符串转换小写

日期和时间函数

日期和时间函数主要用来处理日期和时间值,一般的日期函数除了使用DATE类型的参数外,也可以使用 DATETIME或者 TIMESTAMP类型的参数,但会忽略这些值的时间部分相同的,以TIME类型值为参数的函数,可以接受TIMESTAMP类型的参数,但会忽略日期部分,许多日期函数可以同时接受数和字符串类型的两种参数,本节将介绍各种日期和时间函数的功能和用法。

• 获取当前日期的函数和获取当前时间的函数

CURDATE（）和 CURTIME（）函数作用相同,将当前日期按照‘YYYY-MM-DD’或YYYYMMDD格式的值返回,具体格式根据函数在字符串或是数字语境中而定。

重点：注意3个日期相关函数区别：

在mysql客户端中分别执行如下sql语句：

select CURDATE()

select CURTIME()

select NOW()

输出：

第一行：2021-12-17

第二行：01:17:07

第三行：2021-12-17 01:17:17

• UNIX时间戳函数

UNIX_TIMESTAMP(date)若无参数调用,则返回一个Unix时间戳(‘1970-01-01 00:00:00’ GMT之后的秒数)作为无符号整数。其中,GMT (Greenwich mean time)为格林尼治标准时间)。若用date来调用 UNIX TIMESTAMPO,它会将参数值以’1970-01-01 00:00:00’ GMT 后的秒数的形式返回，date可以是一个DATE字符串、 DATETIME字符串、 TIMESTAMP或一个当地时间的 YYMMDD或 YYYYMMDD格式的数字。

• 计算字符串字符数的函数和字符串长度的函数

LENGTH(str)返回值为字符串sr所包含的字符个数。一个多字节字符算作一个单字符。

• 获取星期的函数 DAYNAME(d). DAYOFWEEK(d) WEEKDAY(d)

DAYNAME(d)函数返回d对应的工作日的英文名称,例如 Sunday、 Monday等。

DAYOFWEEK(d)函数返回d对应的一周中的索引(位置)。1表示周日,2表示周一 ,…,7表示周六)

• 获取星期数的函数WEEK(d)和 WEEKOFYEAR(d)

WEEK(d)计算日期d是一年中的第几周。

• 获取天数的函数 DAYOFYEAR(d)和 DAYOFMONTH(d)

DAYOFYEAR(d)函数返回d是一年中的第几天,范围是从1-366.

• 获取年份、季度、小时、分钟和秒钟的函数

YEAR(date)返回date对应的年份,范围是1970-2069.

QUARTER(date)返回date对应的一年中的季度值,范围是从1-4

MINUTE(time)返回time对应的分钟数,范围是从0-59

SECOND(time)返回time对应的秒数,范围是从0-59

• 将日期和时间格式化的函数

DATE_FORMAT(date,format)根据format指定的格式显示date值。主要format格式如下：

%Y 年，4 位

%M 月名

%m 月，数值(00-12)

%d 天，数值(00-31)

%H 小时 (00-23)

%h 小时 (01-12)

%i 分钟，数值(00-59)

%S或者%s 秒(00-59)

条件判断函数

条件判断函数也称为控制流程函数,根据满足的条件的不同,执行相应的流程。MySQL 中进行条件判断的函数有IF、 IFNUL和CASE。

• IF(epxr,v1,v2)函数

IF(expr,v,v2),如果表达式expr是TRUE(expr<> and expr <> NULL),则IF()的返回值为v1;否则返回值为v2。IF()的返回值为数字值或字符串值,具体情况视其所在语境而定。

如果v1或v2中只有一个明确是NULL，则IF()函数的结果类型为非NULL表达式的结果类型。

• IFNULL(v1,v2)函数

IFNULL(v1,v2)假如v1不为NULL,则 IFNULLO的返回值为v1;否则其返回值为v2

IFNULLO的返回值是数字或是字符串,具体情况取决于其所在的语境。

• CASE函数 条件函数语句，CASE WHEN 条件 THEN 成立时执行 ELSE 不成立时执行 END