MySQL：函数

基本介绍

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

        在MySQL中，函数非常多，主要可以分为五类：聚合函数、数学函数、字符串函数、日期函数、控制流函数、窗口函数（MsSQL8.0版本及以上才有）

聚合函数

        在MySQL中，聚合函数主要由: count，sum，min，max，avg，group_ concat() 。

        这里主要讲group_ concat()，该函数用来用户实现行的合并

group_ concat()

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

格式：

数学函数

函数名	描述
ABS(x)	返回x的绝对值
CEIL(x)	返回大于或等于x的最小整数
FLOOR(x)	返回小于或等于x的最大整数
GREATEST(expr1, expr2, expr3, ...)	返回列表中的最大值
LEAST(expr1, expr2, expr3, ...	返回列表中的最小值
MAX(expression)	返回字段expression中的最大值
MIN(expression)	返回字段expression中的最小值
MOD(x,y)	返回x除以y以后的余数（取模）
PI()	返回圆周率(3.141593)
POW(x,y)	返回x的y次方
RAND()	返回0到1的随机数
ROUND(x)	返回离x最近的整数(遵循四舍五入)
ROUND(x,y)	将x保留y位小数并返回结果(遵循四舍五入)
TRUNCATE(x,y)	返回数值x保留到小数点后y位的值(与ROUND最大的区别是不会进行四舍五入)

字符串函数

函数名	描述
LENGTH(s)	返回字符串s的字节数
CHAR_ LENGTH(s)	返回字符串s的字符数
CHARACTER_ LENGTH(s)	返回字符串s的字符数
CONCAT(s1,s2...sn)	字符串s1,s2等多个字符串合并为一个字符串
CONCAT_WS(x, s1,2...sn)	同CONCAT(51,2...)函数，但是每个字符串之间要加上x，x可以是分隔符
FlELD(s,s1,2...)	返回第一个字符串s在字符串列表(s1,s2,...)中的位置
LTRIM(s)	去掉字符串s开始处的空格
RTRIM(s)	去掉字符串s后边的空格
TRIM(s)	去掉字符串s两边的空格
MID(s,n,len)	从字符串s的n位置截取长度为len的子字符串，同SUBSTRING(s,n,len)
POSITION(s1 IN s)	从字符串s中获取s1的开始位置
REPLACE(s,s1,s2)	将字符串s2替代字符串S中的字符串s1
REVERSE(s)	将字符串s的顺序反过来
RIGHT(s,n)	返回字符串s的后n个字符
STRCMP(s1,s2)	比较字符串s1和s2，如果s1与s2相等返回0，如果s1>s2返回1,如果s1<s2返回-1
SUBSTR(s, start, length)	从字符串s的start位置截取长度为length的子字符串
SUBSTRING(s, start, length)	从字符串s的start位置截取长度为length的子字符串
UCASE(s)	将字符串转换为大写
UPPER(s)	将字符串转换为大写
LCASE(s)	将字符串s的所有字母变成小写字母
LOWER(s)	将字符串S的所有字母变成小写字母

日期函数

函数

函数名	描述
UNIX_TIMESTAMP()	返回从1970-01-01 00:00:00到当前毫秒值
UNIX_TIMESTAMP(DATE_STRING)	将制定日期转为毫秒值时间戳
FROM_ UNIXTIME(BIGINT UNIXTIME[ STRINGFORMAT])	将毫秒值时间戳转为指定格式日期
CURDATE()	返回当前日期(年月日)
CURRENT_DATE()	返回当前日期(年月日)
CURRENT_TIME	返回当前时间(时分秒)
CURTIME()	返回当前时间(时分秒)
CURRENT_ TIMESTAMP()	返回当前日期和时间
DATE()	从日期或日期时间表达式中提取日期值
DATEDIFF(d1,d2)	计算日期d1->d2之间相隔的天数
TIMEDIFF(time1, time2)	计算时间差值（秒）
DATE_FORMAT(d,f)	按表达式f的要求显示日期d
STR_TO_DATE(string, format mask)	将字符串转变为日期
DATE_SUB(date,INTERVAL expr type)	函数从日期减去或加上指定的时间间隔。type 值可以是:MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR、DAY_MINUTE、DAY_HOUR、YEAR_MONTH、SECOND_MICROSECOND、MINUTE_ MICROSECOND、MINUTE_SECOND、HOUR_MICROSECOND、HOUR_SECOND、HOUR_MINUTE、DAY_MICROSECOND、DAY_SECOND、DAY_MINUTE、DAY_HOUR、YEAR_MONTH
ADDDATE/DATE ADD/DATE_ADD(date,INTERVAL expr type)
EXTRACT(type FROM d)	从日期d中获取指定的值，type 指定返回的值。type 可取值为:MICROSECOND...
LAST_DAY(d)	返回给给定日期的那一月份的最后一天
MAKEDATE(year, day- of-year)	基于给定参数年份year和所在年中的天数序号day-of-year 返回一个日期
YEAR(d)	返回年份
MONTH(d)	返回日期d中的月份值，1到12
DAY(d)	返回日期值d的日期部分
HOUR(t)	返回t中的小时值
MINUTE(t)	返回t中的分钟值
SECOND(t)	返回t中的秒钟值
QUARTER(d)	返回日期d是第几季节，返回1到4
MONTHNAME(d)	返回日期当中的月份名称，如November
MONTH(d)	返回日期d中的月份值，1 到12
DAYNAME(d)	返回日期d是星期几，如Monday, Tuesday
DAYOFMONTH(d)	计算日期d是本月的第几天
DAYOFWEEK(d)	日期d今天是星期几，1星期日，2星期一，以此类推
DAYOFYEAR(d)	计算日期d是本年的第几天
WEEK(d)	计算日期d是本年的第几个星期，范围是0到53
WEEKDAY(d)	日期d是星期几，0表示星期一，1表示星期二
WEEKOFYEAR(d)	计算日期d是本年的第几个星期，范围是0到53
YEARWEEK(date, mode)	返回年份及第几周(0到53) ，mode 中0表示周天，1表示周一，以此类推
NOW()	返回当前日期和时间

日期格式

日期格式

格式	描述
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时(00-23)
%h	小时(01-12)
%I	小时(01-12)
%i	分钟，数值(00-59)
%j	年的天(001-366)
%k	小时(0-23)
%l	小时(1-12)
%M	月名
%m	月，数值(00-12)
%p	AM或PM
%r	时间，12-小时(hh:mm:ss AM或PM)
%S	秒(00-59)
%s	秒(00-59)
%T	时间，24-小时(hh:mn:ss)
%U	周(00-53)星期日是一周的第一天
%u	周(00-53)星期一是一周的第一天
%V	周(01-53)星期日是一周的第一天， 与%X使用
%v	周(01-53) 星期一是一周的第一天，与%X使用
%W	星期名
%w	周的天(0=星期日, 6=星期六)
%X	年，其中的星期日是周的第一天，4位，与%V使用
%x	年，其中的星期一是周的第一天，4位，与%V使用
%Y	年，4位
%y	年，2位

控制流函数

if逻辑判断函数

格式	解释
IF(expr,v1,v2)	如果表达式expr成立，返回结果v1;否则，返回结果v2。
IFNULL(v1,v2)	如果v1的值不为NULL,则返回v1,否则返回v2。
ISNULL(expression)	判断表达式是否为NULL
NULLIF(expr1, expr2)	比较两个字符串，如果字符串expr1与expr2相等返回NULL,否则返回expr1

CASE_WHEN语句

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

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

窗口函数

介绍

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

分类

另外还有开窗聚合函数: SUM,AVG,MIN,MAX

语法结构

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

        1.分区(PARTITION BY)

        PARTITION BY选项用于将数据行拆分成多个分区(组)，它的作用类似于GROUP BY分组。如果省略了PARTITION BY，所有的数据作为一个组进行计算

        2.排序(ORDER BY)

        OVER子句中的ORDER BY选项用于指定分区内的排序方式，与ORDER BY子句的作用类似

        3.窗口大小(frame_clause) 

        frame_clause选项用于在当前分区内指定一个计算窗口，也就是一个与当前行相关的数据子集。

序号函数

        序号函数有三个：ROW_NUMBER()、 RANK()、 DENSE_RANK(), 可以用来实现分组排序,并添加序号。

格式

开窗聚合函数

概念

        在窗口中每条记录动态地应用聚合函数(SUM()、 AVG()、 MAX()、 MIN()、 COUNT()) ，可以动态计算在指定的窗口内的各种聚合函数值。

案例

 order by ... 后还可以加关键字rows

        rows between unbounded preceding and current row     --从首行加到当前行(默认就是这样)

        rows between n preceding and current row  --从前n行加到当前行(是n+1项的和，没有不加)

        rows between n preceding and k following  --从前n行加到后k行(包括当前行)

分布函数

CUME_DIST函数

介绍  

        用途：分组内小于、等于当前rank值的行数/分组内总行数

        应用场景：查询小于等于当前薪资(salary) 的比例

案例

PERCENT_RANK函数

介绍 

        用途：每行按照公式(rank-1) / (rows 1)进行计算。其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数

        应用场景：不常用

案例

前后函数

介绍

        分类：LAG、LEAD

        用途：返回位于当前行的前n行(LAG(expr,n)) 或后n行(LEAD(expr,n)) 的expr的值

        应用场景：查询前1名同学的成绩和当前同学成绩的差值

案例

头尾函数

介绍

        分类： FIRST_VALUE、LAST_VALUE

        用途：返回第一个(FIRST_VALUE(expr))或最后一个(LAST_VALUE(expr)) expr的值

        应用场景：截止到当前，按照日期排序查询第1个入职和最后1个入职员工的薪资

案例

        注意：如果不指 定ORDER BY，则进行排序混乱，会出现错误的结果

其它函数

NTH_ VALUE(expr, n)

介绍 

        用途：返回窗口中第n个expr的值。expr可以是表达式，也可以是列名

        应用场景：截止到当前薪资，显示每个员工的薪资中排名第2或者第3的薪资

案例

 NTILE(n)

介绍

        用途：将分区中的有序数据分为n个等级，记录等级数

        应用场景：将每个部门员工按照入职日期分成3组

案例