mySQL的函数

zou_xi

已于 2023-04-03 11:54:17 修改

阅读量228

点赞数 1

分类专栏： My SQL数据库文章标签： mysql

于 2022-07-31 15:40:15 首次发布

本文链接：https://blog.csdn.net/zouzxxi/article/details/126071508

版权

My SQL数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

在MySQL中，为了提高代码重用性和隐藏实现细节，MySQL提供了很多函数。函数可以理解为别人封装好的模板代码。

mysql的函数一共有6种：

1.聚合函数

在MySQL中，聚合函数主要由：聚合查询里的count(),sum(),min(),max(),avg()。

除了这些还有一个函数:group_concat(），该函数用户实现行的合并。

group_concat()函数首先根据group by指定的列进行分组，并且用分隔符分隔，将同一个分组中的值连接起来，返回一个字符串结果。

格式：

group_concat([distinct] 字段名 [order by 排序字段 asc/desc] [separator '分隔符']);

说明：

（1）使用distinct可以排除重复值；

（2）如果需要对结果中的值进行排序，可以使用order by子句；

（3）separator是一个字符串值，默认为逗号。

2.数学函数

函数名	描述	实列
abs(x)	返回x的绝对值	select abs(-1); 返回1
ceil(x)	放回小于或等于x的最小整数，向上取整	select ceil(1.5); 返回2
floor(x)	返回小于或等于x的最大整数，向下取整	select floor(1.5); 返回1
greatest(expr1,expr2,...)	返回列表中的最大值	select greatest(3, 12, 34, 8, 25); 返回34
least(expr1,expr2,...)	返回列表中的最小值	select least(3, 12, 34, 8, 25); 返回3
max(expression)	返回字段 expression 中的最大值	返回数据表 Products 中字段 Price 的最大值： select max(Price) from Products;
min(expression)	返回字段 expression 中的最小值	返回数据表 Products 中字段 Price 的最小值： select min(Price) from Products;
mod(x,y)	返回x除以y以后的余数	select mod(5,2); 返回1
pi()	返回圆周率（3.141593）正确的圆周率：3.1415926	select pi(); 返回3.141593
pow(x,y)	返回x的y次方	select pow(2,3); 返回8
rand()	返回0到1的随机数	select rand(); 返回0.0380730256792838
round(x)	返回离x最近的整数(遵循四舍五入）	select round(1.5); 返回2
round(x,y)	返回指定位数的小数(遵循四舍五入)	select round(1.23456,3); 返回1.235
truncate(x,y)	返回数值x保留到小数点后y位的值(不会进行四舍五入)	select truncate(1.23456,3); 返回1.234

3.字符串函数

函数名	描述	实列
char_length(s)	返回字符串 s 的字符数(是按照字符来统计的)	select char_length("RUNOOB");
character_length(s)	返回字符串 s 的字符数(是按照字节来统计的)	select character_length("RUNOOB");
concat(s1,s2,...)	字符串s1,s1等多个字符串合并为一个字符串	select concat("SQL ", "Runoob ", "Gooogle ", "Facebook");
concat_ws(x,s1,s2...)	同 concat(s1,s2,...) 函数，但是每个字符串之间要加上 x，x 可以是分隔符	select concat_ws("-", "SQL", "Tutorial", "is", "fun!");
fleld(s,s1,s2...)	返回第一个字符串，s在字符串列表（s1,s2...)中的位置	select fleld("c", "a", "b", "c", "d", "e");
ltrlm(s)	去掉字符串s开始处的空格	select ltrlm(" RUNOOB");
mid(s,n,len)	从字符串s的n位置截取长度为len的子字符串，同substring(s,n,len)	select mid("RUNOOB", 2, 3);
position(s1 in s)	从字符串s中获取s1的开始位置	select position('b' in 'abc'); 返回2
replace(s,s1,s2)	将字符串s2替代字符串s中的字符串s1	select replace('abc','a','x'); 返回xbc
reverse(s)	将字符串s的顺序反过来	select reverse('abc'); 返回cba
right(s,n)	返回字符串 s 的后 n 个字符	select right('runoob',2); 返回ob
rtrim(s)	去掉字符串 s 结尾处的空格	select rtrim("RUNOOB ");
strcmp(s1,s2)	比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1	select strcmp("runoob", "runoob"); 返回0
substr(s,start,length)	从字符串 s 的 start 位置截取长度为 length 的子字符串	select substr(''RUNOOB",2,3)
substring(s, start, length)	从字符串 s 的 start 位置截取长度为 length 的子字符串	select substring("RUNOOB", 2, 3)
trim(s)	去掉字符串 s 开始和结尾处的空格	select trim(' RUNOOB ');
ucase(s)	将字符串转换为大写	select ucase("runoob"); 返回RUNOOB
upper(s)	将字符串转换为大写	select upper("runoob"); 返回RUNOOB
lcase(s)	将字符串 s 的所有字母变成小写字母	select lcase('RUNOOB'); 返回runoob
lower(s)	将字符串 s 的所有字母变成小写字母	select lower('RUNOOB'); 返回runoob

4.日期函数

格式	解释	案列
UNIX_TIMESTAMP()	返回从1970-01-01 00:00:00到当前毫秒值	select UNIX_TIMESTAMP() -> 1632729059
UNIX_TIMESTAMP(DATE_STRING)	将制定日期转为毫秒值时间戳	SELECT UNIX_TIMESTAMP('2011-12-07 13:01:03');
FROM_UNIXTIME(BIGINT UNIXTIME[, STRING FORMAT])	将毫秒值时间戳转为指定格式日期	SELECT FROM_UNIXTIME(1598079966,'%Y-%m-%d %H:%i:%s'); (1598079966,'%Y-%m-%d %H:%i:%s'); -> 2020-08-22 15-06-06
CURDATE()	返回当前日期	SELECT CURDATE(); -> 2018-09-19
CURRENT_DATE()	返回当前日期	SELECT CURRENT_DATE(); -> 2018-09-19
CURRENT_TIME	返回当前时间	SELECT CURRENT_TIME(); -> 19:59:02
CURRENT_TIMESTAMP()	返回当前日期和时间	SELECT CURRENT_TIMESTAMP() -> 2018-09-19 20:57:43
DATE()	从日期或日期时间表达式中提取日期值	SELECT DATE("2017-06-15"); -> 2017-06-15
DATEDIFF(d1,d2)	计算日期 d1->d2 之间相隔的天数	SELECT DATEDIFF('2001-01-01','2001-02-02') -> -32
TIMEDIFF(time1, time2)	计算时间差值	SELECT TIMEDIFF("13:10:11", "13:10:10"); -> 00:00:01
DATE_FORMAT(d,f)	按表达式 f的要求显示日期 d	SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r') -> 2011-11-11 11:11:11 AM
STR_TO_DATE(string, format_mask)	将字符串转变为日期	SELECT STR_TO_DATE("August 10 2017", "%M %d %Y"); -> 2017-08-10
DATE_SUB(date,INTERVAL expr type)	函数从日期减去指定的时间间隔。	Orders 表中 OrderDate 字段减去 2 天： SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate FROM Orders
LAST_DAY(d)	返回给给定日期的那一月份的最后一天	SELECT LAST_DAY("2017-06-20"); -> 2017-06-30
MAKEDATE(year, day-of-year)	基于给定参数年份 year 和所在年中的天数序号 day-of-year 返回一个日期	SELECT MAKEDATE(2017, 3); -> 2017-01-03
NOW()	返回当前日期和时间	SELECT NOW() -> 2018-09-19 20:57:43
YEARWEEK(date, mode)	返回年份及第几周（0到53），mode 中 0 表示周天，1表示周一，以此类推	SELECT YEARWEEK("2017-06-15"); -> 201724
WEEKOFYEAR(d)	计算日期 d 是本年的第几个星期，范围是 0 到 53	SELECT WEEKOFYEAR('2011-11-11 11:11:11') -> 45
WEEKDAY(d)	日期 d 是星期几，0 表示星期一，1 表示星期二	SELECT WEEKDAY("2017-06-15"); -> 3
WEEK(d)	计算日期 d 是本年的第几个星期，范围是 0 到 53	SELECT WEEK('2011-11-11 11:11:11') -> 45
DAYOFYEAR(d)	计算日期 d 是本年的第几天	SELECT DAYOFYEAR('2011-11-11 11:11:11') ->315
DAYOFWEEK(d)	日期 d 今天是星期几，1 星期日，2 星期一，以此类推	SELECT DAYOFWEEK('2011-11-11 11:11:11') ->6
DAYOFMONTH(d)	计算日期 d 是本月的第几天	SELECT DAYOFMONTH('2011-11-11 11:11:11') ->11
MONTH(d)	返回日期d中的月份值，1 到 12	SELECT MONTH('2011-11-11 11:11:11') ->11
MONTHNAME(d)	返回日期当中的月份名称，如 November	SELECT MONTHNAME('2011-11-11 11:11:11') -> November

5.控制流程函数

if逻辑判断语句

格式	解释	案列
IF(expr,v1,v2)	如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。	SELECT IF(1 > 0,'正确','错误') ->正确
IFNULL(v1,v2)	如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。	SELECT IFNULL(null,'Hello Word') ->Hello Word
ISNULL(expression)	判断表达式是否为 NULL	SELECT ISNULL(NULL); ->1
NULLIF(expr1, expr2)	比较两个字符串，如果字符串 expr1 与 expr2 相等返回 NULL，否则返回 expr1	SELECT NULLIF(25, 25); ->

case when语句

格式	解释	操作
CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... WHEN conditionN THEN resultN ELSE result END	CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。	select case 100 when 50 then 'tom' when 100 then 'mary'else 'tim' end ; select case when 1=2 then 'tom' when 2=2 then 'mary' else'tim' end ;

格式

解释

操作

CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... WHEN conditionN THEN resultN ELSE result END

CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。

select case 100 when 50 then 'tom' when 100 then 'mary'else 'tim' end ;

select case when 1=2 then 'tom' when 2=2 then 'mary' else'tim' end ;

6.窗口函数

MySQL 8.0 新增窗口函数,窗口函数又被称为开窗函数，与Oracle 窗口函数类似，属于MySQL的一大特点. 非聚合窗口函数是相对于聚函数来说的。聚合函数是对一组数据计算后返回单个值（即分组），非聚合函数一次只会处理一行数据。窗口聚合函数在行记录上计算某个字段的结果时，可将窗口范围内的数据输入到聚合函数中，并不改变行数。

语法结构：

window_function ( expr ) OVER ( 
  PARTITION BY ... 
  ORDER BY ... 
  frame_clause 
)

其中，window_function 是窗口函数的名称；expr 是参数，有些函数不需要参数；OVER子句包含三个选项：

分区（PARTITION BY）

PARTITION BY选项用于将数据行拆分成多个分区（组），它的作用类似于GROUP BY分组。如果省略了

PARTITION BY，所有的数据作为一个组进行计算排序（ORDER BY） OVER 子句中的ORDER BY选项用于指定分区内的排序方式，与 ORDER BY 子句的作用类似以及窗口大小（frame_clause）。 frame_clause选项用于在当前分区内指定一个计算窗口，也就是一个与当前行相关的数据子集。