SQL函数与自定义函数

SQL强大的一个原因就是函数丰富，MYSQL为例，函数有以下几种：

数学函数
字符串函数
日期和时间函数
条件判断函数
系统信息函数
加密函数
格式化函数

同时，MYSQL也支持自定义函数，这篇文章分为两个部分，限于篇幅，第一个部分介绍常用函数，第二部分讲解如何自定义函数。

原生函数

1. 数学函数：

 ABS(x)，绝对值
 CEIL(x)，FLOOR(x)，大于等于的整数，小于等于的整数
 SIGN(x)，返回x的符号，x是负数、0、正数分别返回-1、0和1　　
 TRUNCATE(x,y),返回数值x保留到小数点后y位的值
 ROUND(x,y),保留x小数点后y位的值，但截断时要进行四舍五入　
 POW(x,y),SQRT(x),EXP(x) 指数相关
 MOD(x,y)，返回x除以y以后的余数　　
 LOG(x)，LOG10(x)，对数　

2. 字符串函数

 CHAR_LENGTH(s)，返回字符串s的字符数
 CONCAT(s1,s2,...) ，将字符串s1,s2等多个字符串合并为一个字符串
 CONCAT_WS(x,s1,s2,...)，同上，每个字符串直接要加上x
 UPPER(s)，LOWER(s)，大小写
 LEFT(s,n)，RIGHT(s,n)，返回字符串s的前，后n个字符
 LTRIM(s)，RTRIM(s) ，TRIM(s)     去掉字符串s开始，结尾处的空格
 STRCMP(s1,s2)  比较字符串s1和s2
 SUBSTRING(s,n,len)     获取s中第n个位置开始长度为len的字符串

3. 日期和时间函数

CURDATE()，CURTIME()，NOW() 返回日期，时间2017-10-12 10:12:22
UNIX_TIMESTAMP()，UNIX_TIMESTAMP(d)，FROM_UNIXTIME(d)
UTC_DATE() ，UTC_TIME() UTC日期时间
MONTH(d)， 返回日期d中的月份值，1->12
MONTHNAME(d)，返回日期当中的月份名称，如Janyary
DAYNAME(d) ，返回日期d是星期几，如Monday,Tuesday
WEEK(d)，计算日期d是本年的第几个星期，范围是0->53
DAYOFYEAR(d) ，计算日期d是本年的第几天
DAYOFMONTH(d) ，计算日期d是本月的第几天
HOUR(t)，MINUTE(t)，SECOND(t) 取出时分秒
TIME_TO_SEC(t)，SEC_TO_TIME(s) 时间和秒转换
TO_DAYS(d) ，计算日期d距离0000年1月1日的天数
DATEDIFF(d1,d2) ，计算日期d1->d2之间相隔的天数
ADDDATE(d,n) ，计算日期d加上n天的日期
SUBDATE(d,n) ，日期d减去n天后的日期
ADDDATE(d，INTERVAL expr type) 计算起始日期d加上一个时间段后的日期
SUBDATE(d,INTERVAL expr type) ，日期d减去一个时间段后的日期
ADDTIME(t,n)，SUBTIME(t,n)   时间t加/减上n秒的时间

type有：
MICROSECOND，SECOND,MINUTE，HOUR，DAY
WEEK，MONTH，QUARTER，YEAR
SECOND_MICROSECOND，MINUTE_MICROSECOND，MINUTE_SECOND
HOUR_MICROSECOND，HOUR_SECOND，HOUR_MINUTE
DAY_MICROSECOND，DAY_SECOND，DAY_MINUTE，DAY_HOUR
YEAR_MONTH

4. 条件判断函数

IF(expr,v1,v2)， if(sex=1,"男","女")
IFNULL(v1,v2)

CASE expr 
　　WHEN e1 THEN v1
　　WHEN e1 THEN v1
　　...
　　ELSE vn
END

5.其他函数

格式化函数FORMAT(x,n)，将数字x进行格式化，将x保留到小数点后n位

 ASCII(s) 返回字符串s的第一个字符的ASCII码；
 BIN（x） 返回x的二进制编码；
 HEX(x) 返回x的十六进制编码；
 OCT(x) 返回x的八进制编码；
 CONV(x,f1,f2) 返回f1进制数变成f2进制数；

转换数据类型

CAST(x AS type)
CONVERT(x,type)
eg, CAST('3' AS UNSIGNED INTEGER) 

类型有BINARY、CHAR、DATE、DATETIME、TIME、SIGNED INTEGER、UNSIGNED INTEGER

自定义函数

MYSQL支持自定义函数

• 创建UDF:

CREATE [AGGREGATE] FUNCTION function_name(parameter_name type,[parameter_name type,...])
RETURNS {STRING|INTEGER|REAL}
runtime_body

• 删除UDF:

DROP FUNCTION function_name

• 调用函数:

SELECT function_name(parameter_value,...)

UDF可以没有参数,但UDF必须有且只有一个返回值
在函数体中,如果包含多条语句,我们需要把多条语句放到BEGIN…END语句块中

• 定义局部变量:

DECLARE var_name[,varname]...date_type [DEFAULT VALUE];

定义局部变量语句必须在BEGIN…END的第一行定义

• 变量赋值:

SET parameter_name = value[,parameter_name = value...]

用户变量定义语法:(可以理解成全局变量)

SET @param_name = value

• LOOP语句

使某些特定的语句重复执行，LOOP语句本身没有停止循环的语句，必须是遇到LEAVE语句等才能停止循环。begin_label参数和end_label参数分别表示循环开始和结束的标志
LOOP语句的语法：

[begin_label:] LOOP 
statement_list 
END LOOP [end_label] 

• LEAVE语句，ITERATE语句

LEAVE语句，ITERATE语句主要用于跳出循环控制。其语法形式如下：

LEAVE label 
ITERATE label 

其中LEAVE相当于break，ITERATE相当于continue

• REPEAT语句

REPEAT语句是有条件控制的循环语句。相当于do while，基本语法形式如下：

[begin_label:] REPEAT 
statement_list 
UNTIL search_condition 
END REPEAT [end_label] 

• WHILE语句

基本语法形式如下：

[begin_label:] WHILE search_condition DO 
statement_list 
END WHILE [end_label] 

最后举个例子，leetcode的一道题，
https://leetcode.com/problems/nth-highest-salary/description/

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
      set N=N-1;
  RETURN (
      # Write your MySQL query statement below.
        select max(Salary) from 
        (
            select Salary from 
            (
                select distinct Salary from Employee A 
                    UNION ALL 
                (select distinct Salary from Employee B order by Salary DESC limit N)
            )D GROUP BY Salary HAVING COUNT(Salary) = 1 
        )E

  );
END

或者利用limit

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M=N-1;
  RETURN (
    # Write your MySQL query statement below.
    SELECT IFNULL((SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT M ,1), NULL)
  );
END

这里注意下limit的用法

  LIMIT 2 OFFSET 1; 2条数据，从第1条开始读取
  LIMIT 2,1; 从第2条开始读，读取1条