MySQL之函数

函数

1. 概述

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

2. 分类

   在MySQL中，函数非常多，主要可以分为以下几类：

   1. 聚合函数

      1. 介绍

         在MySQL中，聚合函数主要有：count，sum，min，max，avg，group_concat()，group_concat()该函数用于实现行的合并。

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

      2. 格式

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

         说明：

         使用distinct可以排除重复值；

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

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

   2. 数学函数

      函数名	描述
      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)	返回指定位数的小数（遵循四舍五入）
      TRUNCATE(x,y)	返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）

   3. 字符串函数

      函数	描述
      LENGTH(s)	返回字符串 s 的字节数
      CHAR_LENGTH(s)	返回字符串 s 的字符数
      CHARACTER_LENGTH(s)	返回字符串 s 的字符数
      CONCAT(s1,s2...sn)	字符串 s1,s2 等多个字符串合并为一个字符串
      CONCAT_WS(x, s1,s2...sn)	同 CONCAT(s1,s2,...) 函数，但是每个字符串之间要加上 x，x 可以是分隔符
      FIELD(s,s1,s2...)	返回第一个字符串 s 在字符串列表(s1,s2...)中的位置
      TRIM(s)	去掉字符串 s 开始和结尾处的空格
      LTRIM(s)	去掉字符串 s 开始处的空格
      RTRIM(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 的所有字母变成小写字母

3. 日期函数

   函数名	描述
   UNIX_TIMESTAMP()	返回从1970-01-01 00:00:00到当前毫秒值
   UNIX_TIMESTAMP(DATE_STRING)	将制定日期转为毫秒值时间戳
   FROM_UNIXTIME(BIGINT UNIXTIME[, STRING FORMAT])	将毫秒值时间戳转为指定格式日期
   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)	函数从日期减去指定的时间间隔。
   ADDDATE/DATE_ADD(d，INTERVAL expr type)	计算起始日期 d 加上一个时间段后的日期，type 值可以是： ·MICROSECOND ·SECOND ·MINUTE ·HOUR ·DAY ·WEEK ·MONTH ·QUARTER ·YEAR ·DAY_MINUTE ·DAY_HOUR ·YEAR_MONTH
   DATE_ADD(d，INTERVAL expr type)	计算起始日期 d 加上一个时间段后的日期，type 值可以是： ·SECOND_MICROSECOND ·MINUTE_MICROSECOND ·MINUTE_SECOND ·HOUR_MICROSECOND ·HOUR_SECOND ·HOUR_MINUTE ·DAY_MICROSECOND ·DAY_SECOND ·DAY_MINUTE ·DAY_HOUR ·YEAR_MONTH
   EXTRACT(type FROM d)	从日期 d 中获取指定的值，type 指定返回的值。 type可取值为： ·MICROSECOND ·SECOND ·MINUTE ·HOUR …..
   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()	返回当前日期和时间

   1. 控制流函数

      1. 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

      2. case when语句

         1. 格式

            CASE expression

            WHEN condition1 THEN result1

            WHEN condition2 THEN result2

            ...

            WHEN conditionN THEN resultN

            ELSE result

            END

         2. 解释

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

   2. 窗口函数

      1. 介绍

         • MySQL 8.0 新增窗口函数,窗口函数又被称为开窗函数，与Oracle 窗口函数类似，属于MySQL的一大特点.

         • 非聚合窗口函数是相对于聚函数来说的。聚合函数是对一组数据计算后返回单个值（即分组），非聚合函数一次只会处理一行数据。窗口聚合函数在行记录上计算某个字段的结果时，可将窗口范围内的数据输入到聚合函数中，并不改变行数。

      2. 语法结构

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

         其中，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选项用于在当前分区内指定一个计算窗口，也就是一个与当前行相关的数据子集。

      3. 分类

         1. 序号函数

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

            ROW_NUMBER()：按顺序排名；

            RANK()：相同排序后有间隔；

            DENSE_RANK()：相同排序后无间隔。

            格式：

            row_number()|rank()|dense_rank() over ( 
              partition by ... 
              order by ... 
            ) 

         2. 开窗聚合函数 - SUM(), AVG(), MIN(), MAX()

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

            SUM()|AVG()|MAX()|MIN()|COUNT() over ( 
              partition by ... 
              order by ... 
              rows between unbounded preceding and current row
            ) -- 如果没有order  by排序语句  默认把分组内的所有数据进行sum操作

            注释：

            unbounded preceding表示从头开始；

            unbounded following表示到末尾；

            current row表示当前行；

            数字 preceding表示向前多少行开始；

            数字 following表示向后多少行结束。

         3. 分布函数 - CUME_DIST()和PERCENT_RANK()

            1. CUME_DIST()

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

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

               select  
                dname,
                ename,
                salary,
                cume_dist() over(partition by dept order by salary) as rn2 
               from employee;

            2. PERCENT_RANK()

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

               应用场景：不常用

               select 
                dname,
                ename,
                salary,
                rank() over(partition by dname order by salary desc ) as rn,
                percent_rank() over(partition by dname order by salary desc ) as rn2
               from employee;

         4. 前后函数 - LAG()和LEAD()

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

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

            -- lag的用法
            select 
             dname,
             ename,
             hiredate,
             salary,
             lag(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) as last_1_time,
             lag(hiredate,2) over(partition by dname order by hiredate) as last_2_time 
            from employee;
            -- lead的用法
            select 
             dname,
             ename,
             hiredate,
             salary,
             lead(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) as last_1_time,
             lead(hiredate,2) over(partition by dname order by hiredate) as last_2_time 
            from employee;

         5. 头尾函数 - FIRST_VALUE和LAST_VALUE

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

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

            -- 注意,  如果不指定ORDER BY，则进行排序混乱，会出现错误的结果
            select
              dname,
              ename,
              hiredate,
              salary,
              first_value(salary) over(partition by dname order by hiredate) as first,
              last_value(salary) over(partition by dname order by  hiredate) as last 
            from  employee;

         6. 其他函数 - NTH_VALUE(expr, n)和NTILE(n)

            1. NTH_VALUE(expr, n)

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

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

               -- 查询每个部门截止目前薪资排在第二和第三的员工信息
               select 
                 dname,
                 ename,
                 hiredate,
                 salary,
                 nth_value(salary,2) over(partition by dname order by hiredate) as second_score,
                 nth_value(salary,3) over(partition by dname order by hiredate) as third_score
               from employee

            2. NTILE(n)

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

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

               -- 根据入职日期将每个部门的员工分成3组
               select 
                 dname,
                 ename,
                 hiredate,
                 salary,
               ntile(3) over(partition by dname order by  hiredate  ) as rn 
               from employee;
               -- 取出每个部门的第一组员工
               select
               *
               from
               (
                   SELECT 
                       dname,
                       ename,
                       hiredate,
                       salary,
                   NTILE(3) OVER(PARTITION BY dname ORDER BY  hiredate  ) AS rn 
                   FROM employee
               )t
               where t.rn = 1;