写在前面：内容部分摘抄于冰河的MySQL运维大全
MySQL函数简介
方便开发人员更好的进行数据分析与统计，提高开发效率；内置的函数从功能的角度可以分为数学函数、字符串函数、日期函数和时间函数、流程处理函数、加密与解密函数、聚合函数、获取MySQL信息函数等
一、数学函数
1.1 绝对值函数
函数ABS(X)获取X的绝对值
SELECT ABS(1), ABS(-1), ABS(0), ABS(3.14), ABS(-3.14);
1.2 圆周率函数
select pi()
1.3 获取整数的函数
1.3.1 CEIL(X)函数与CEILING(X)函数
获取大于或等于某个值的最小整数，示例：
SELECT CEIL(1), CEIL(-1), CEILING(3.14), CEILING(-3.14);
1.3.2 FLOOR(X)函数
获取小于或等于某个值的最大整数，示例：
SELECT FLOOR(1), FLOOR(-1), FLOOR(3.14), FLOOR(-3.14);
1.4 返回列表中最值函数
1.4.1 LEAST(e1,e2,e3...)函数
LEAST(e1,e2,e3...)函数用于获取列表中的最小值，列表中的数据可以由数字组成，也可以由字符串组成。
SELECT LEAST(2,3),LEAST(3.15, 2.16), LEAST('hello', 'world'), LEAST('a', 1);
1.4.2 GREATEST(e1,e2,e3...)函数
GREATEST(e1,e2,e3...)函数用于获取列表中的最大值，列表中的数据可以由数字组成，也可以由字符串组成
SELECT GREATEST(2,3), GREATEST(3.15, 2.16), GREATEST('hello', 'world'), GREATEST('a', 1)
1.5 三角函数
1.5.1 SIN(X)函数
SIN(X)函数返回X的正弦值，其中，参数X为弧度值
SELECT SIN(1), SIN(0), SIN(-1), SIN(PI());
1.5.2 ASIN(X)函数
ASIN(X)函数返回X的反正弦值，即获取正弦为X的值，如果X的值不在-1到1之间，返回NULL
SELECT ASIN(0.8414709848078965), ASIN(0), ASIN(-0.8414709848078965);
1.5.3 COS(X)函数
COS(X)函数返回X的余弦值，其中，参数X为弧度值
SELECT COS(1), COS(0), COS(PI());
PS：三角函数相关的函数MySQL几乎都有，大家自行查阅资料吧
1.6 乘方与开方函数
1.6.1 POW(X,Y)函数
POW(X,Y)函数返回X的Y次方，使用示例如下：
select pow(2,4)
1.6.2 POWER(X,Y)函数，同POW(X,Y)函数
1.6.3 EXP(X)函数
EXP(X)函数返回e的X次方，其中e是一个常数
select exp(2),exp(5)
1.6.4 SQRT(X)函数
SQRT(X)函数返回X的平方根，当X的值为负数时，返回NULL
select sqrt(16), sqrt(0), sqrt(-16)
1.7 对数函数
1.7.1 LN(X)函数
LN(X)函数返回以e为底的X的对数，当X的值小于或者等于0时，返回的结果为NULL
select ln(100), ln(1), ln(0), ln(-1)
LOG(X)函数与LN(X)函数相同
1.7.2 LOG10(X)函数
以10为底的X的对数
1.7.3 LOG2(X)函数
以2为底的X的对数
1.8 随机函数
1.8.1 RAND()函数
返回0-1之间的随机数
select rand(),rand(),rand()
1.8.2 RAND(X)函数
RAND(X)函数返回0-1之间的随机数，其中X作为种子值，相同的X值会产生重复的随机数
select rand(10), rand(10), rand(0), rand(-10)
1.9 四舍五入与数字截取
1.9.1 ROUND(X)函数
返回X值四舍五入之后的整数值
select round(3.4), round(3.5)
1.9.2 ROUND(X,Y)函数
返回一个对X的值进行四舍五入后最接近X的值，并保留小数点后Y位，如果Y为负数，保留小数点到左边Y位
SELECT ROUND(3.145,2), ROUND(3.145, 0), ROUND(1308.789, -2);
1.9.3 TRUNCATE(X,Y)函数
对X的值进行截断，保留到小数点后Y位，如果Y为0则保留整数部分，如果Y为负数，保留到小数点左边Y位
SELECT TRUNCATE(156.1516, 3), TRUNCATE(156.1516, 0), TRUNCATE(156.1516, -2);
1.10 符号函数
SIGN(X)函数，X为整数返回1，X为0返回0，X为负数返回-1
1.11 数学运算函数
1.11.1 DIV函数
M DIV N，返回M除以N的整数结果值，N为0的时候返回NULL
SELECT 16 DIV 5, 16 DIV -2, 16 DIV 0;
1.11.2 MOD(X,Y)函数
返回X除以Y后的余数，当Y为0时，返回NULL
SELECT MOD(6, 4), MOD(6, 3), MOD(6, 0);
二、字符串函数
2.1 ASCII(S)函数
返回第一个字符的ASCII码值，示例：
SELECT ASCII('abc'), ASCII('pain');
2.2 CHAR_LENGTH(S)
返回字符串S的长度，示例：
SELECT CHAR_LENGTH('hello'), CHAR_LENGTH('你好'), CHAR_LENGTH(' ');
2.3 LENGTH(S)函数
返回字符串S的字节数，示例：
SELECT LENGTH('hello'), LENGTH('你好'), LENGTH(' ');
注意：不同编码占用字节数可能不一样
2.4 CONCAT(S1,S2,...Sn)
将字符串S1,S2,...Sn合并成一个字符串，示例：
select concat('hello', ' ', 'world')
注意：函数中字符串有任意一个为NULL，则返回NULL
2.5 CONCAT_WS(X,S1,S2,...Sn)
函数将字符串S1,S2,...Sn拼接成一个以X分隔的字符串，X可以是一个字符串，也可以是其他合法的参数
select concat_ws(',','a','b')
注意：如果分隔符X为NULL, 则函数返回NULL
如果字符串S1,S2,...Sn中有任意的字符串为NULL，函数会忽略NULL的字符串
2.6 INSERT(oldstr,x,y,replacestr)函数
将字符串oldstr从第x位置开始的y个字符长度的子字符串替换成replacestr
select insert('hello world', 1, 5, 'hi')
2.6 LOWER(S)函数
将字符串转为小写
select lower('HELLO WORLD'), lower('Hello World')
2.7 UPPER(S)函数
将字符串转为大写
select upper('hello world'), upper('Hello World')
2.8 LEFT(str,x)函数
返回字符串str最左边的x个字符串组成的字符串，如果x的值为null，返回null
select left('hello world', 5), left('hello world', null)
RIGHT(str,x)函数同理
2.9 LPAD(str,n,pstr)函数
使用字符串pstr对字符串最左边进行填充，直到str字符串的长度达到n为止
select lpad('world', 11, 'hello ')
RPAD(str,n,pstr)函数同理
2.10 TRIM(S)函数
LTRIM(S)：去掉字符串S左边空格
RTRIM(S)：去掉字符串S右边空格
TRIM(S)：去掉字符串S两空格
TRIM(substr FROM str)：删除字符串str首尾的子字符串substr
其他函数
REPLACE(S,A,B)：替换字符串，同String类
SUBSTR(S,X,Y)：截取字符串,同String类
SPACE(X)：返回由X个空格组成的字符串
LOCATE(substr,str)：substr在str的位置，返回substr的最后字符在str中的索引
ELT(M,S1,S2,...Sn)：返回指定位置的字符串，M=n，返回Sn
FIELD(S,S1,S2,...,Sn)：返回S在字符串S1,S2,...Sn第一次出现的位置
FIND_IN_SET(S1,S2)：返回S1在S2出现的位置
REVERSE(S)：反转字符串
NULLIF(v1,v2)：比较字符串，如果v1和v2相等，则返回null，否则返回v1
三、日期和时间函数
CURDATE()：返回当前日期年、月、日部分，格式为YYYY-MM-DD
CURTIME()：返回当前时间时、分、秒部分，格式为HH:MM:SS
NOW()：返回当前日期和时间，包含年、月、日、时、分、秒，格式为YYYY-MM-DD HH:MM:SS
UNIX_TIMESTAMP(date)：将date转化为UNIX时间戳
FROM_UNIXTIME(timestamp)：将UNIX时间戳转化为时间，格式为YYYY-MM-DD HH:MM:SS，和UNIX_TIMESTAMP(date)互为反函数
UTC_DATE(): 返回UTC时间
UTC_TIME()
YEAR(date)
MONTH(date)
MONTHNAME(date)
DAY(date)
DAYNAME(date)
DAYOFWEEK(date)
WEEKDAY(date)
WEEK(date)
WEEKOFYEAR(date)
DAYOFYEAR(date)
DAYOFMONTH(date)
QUARTER(date)：返回对应的季节，范围为1~4
四、流程处理函数
五、加密与解密函数
六、聚合函数
七、获取MySQL信息函数
八、加锁与解锁函数
九、JSON函数
十、窗口函数
准备工作
创建表，t_goods
CREATE IF EXISTS TABLE t_goods(
id INT PRIMARY KEY,
t_category_id INT,
t_category VARCHAR(30),
t_name VARCHAR(50),
t_price DECIMAL(10,2),
t_stock INT,
t_upper_time DATETIME
);
插入数据
INSERT INTO t_goods (id,t_category_id, t_category, t_name, t_price, t_stock, t_upper_time) VALUES
(1,1, '女装/女士精品', 'T恤', 39.90, 1000, '2020-11-10 00:00:00'),
(2,1, '女装/女士精品', '连衣裙', 79.90, 2500, '2020-11-10 00:00:00'),
(3,1, '女装/女士精品', '卫衣', 89.90, 1500, '2020-11-10 00:00:00'),
(4,1, '女装/女士精品', '牛仔裤', 89.90, 3500, '2020-11-10 00:00:00'),
(5,1, '女装/女士精品', '百褶裙', 29.90, 500, '2020-11-10 00:00:00'),
(6,1, '女装/女士精品', '呢绒外套', 399.90, 1200, '2020-11-10 00:00:00'),
(7,2, '户外运动', '自行车', 399.90, 1000, '2020-11-10 00:00:00'),
(8,2, '户外运动', '山地自行车', 1399.90, 2500, '2020-11-10 00:00:00'),
(9,2, '户外运动', '登山杖', 59.90, 1500, '2020-11-10 00:00:00'),
(10,2, '户外运动', '骑行装备', 399.90, 3500, '2020-11-10 00:00:00'),
(11,2, '户外运动', '运动外套', 799.90, 500, '2020-11-10 00:00:00'),
(12,2, '户外运动', '滑板', 499.90, 1200, '2020-11-10 00:00:00');
10.1 序号函数
10.1.1 ROW-NUMBER()函数
案例：查询t_goods数据表中每个商品分类下价格最高的3种商品信息
select * from
(select row_number() over(partition by t_category_id order by t_price desc) as row_num,
id,
t_category_id,
t_category,
t_name,
t_price,
t_stock
from t_goods ) t
where t_category_id = 1 and row_num <= 3;
10.1.2 RANK()函数
案例：对序号进行并列排序，跳过重复的序号， 比如序号为1、1、3
select * from
(select rank() over(partition by t_category_id order by t_price desc) as row_num,
id,
t_category_id,
t_category,
t_name,
t_price,
t_stock
from t_goods ) t
where t_category_id = 1 and row_num <= 3;
10.1.3 DENSE_RANK()函数
案例：获取t_goods表中类别为"女装/女士精品"的价格最高的4款商品信息
select * from (select dense_rank() over(partition by t_category_id order by t_price desc) as row_num,
id,
t_category_id,
t_category,
t_name,
t_price,
t_stock
from t_goods ) t
where t_category_id = 1 and row_num <= 3;
10.2 分布函数
10.2.1 PERCENT_RANK()函数
PERCENT_RANK()函数是等级值百分比函数，按照如下方式进行计算
（rank-1） / (rows - 1)
其中，rank的值为使用RANK()函数产生的序号，rows的值为当前窗口的总记录数
案例：计算t_goods表中名称为"女装/女士精品"的类别下的商品的PERCENT_RANK()值
select
rank() over w as r,
percent_rank() over w as pr,
id,
t_category_id,
t_category,
t_name,
t_price,
t_stock
from
t_goods
where
t_category_id = 1 window w as (partition by t_category_id ORDER by t_price desc)
10.2.2 CUME_DIST()函数
CUME_DIST()函数主要用于查询小于或等于某个值的比例, 计算方式：当前rank值 / 分组内总行数
案例：查询t_goods表中小于或等于当前价格的比例
select
cume_dist() over(partition by t_category_id order by t_price desc) as cd,
rank() over w as rk,
id,
t_category,
t_name,
t_price
from
t_goods
window w as (partition by t_category_id ORDER by t_price desc);
10.3 前后函数
10.3.1 LAG(expr,n)
返回当前行的前n行的expr的值
案例：查询t_goods表中前一个商品价格与当前商品价格的差值
select
id,
t_category,
t_name,
t_price,
pre_price,
t_price - pre_price as diff_price
from
(select
id,
t_category,
t_name,
t_price,
LAG(t_price, 1) over w as pre_price
from t_goods window w as (partition by t_category_id order by t_price)) t;
10.3.2 LEAD(expr,n)函数
返回当前行的后n行的expr的值
案例：查询t_goods数据表中后一个商品价格与当前商品价格的差值
select
id,
t_category,
t_name,
t_price,
behind_price,
behind_price - t_price as diff_price
from
(select
id,
t_category,
t_name,
t_price,
LEAD(t_price, 1) over w as behind_price
from t_goods window w as (partition by t_category_id order by t_price)) t;
10.4 首尾函数
10.4.1 FIRST_VALUE(expr)函数
返回第一个expr的值
案例：按照价格排序，查询第1个商品的价格信息
select
id,
t_category,
t_name,
t_price,
t_stock,
first_value(t_price) over w as first_price
from t_goods
window w as (partition by t_category_id order by t_price)
10.4.2 LAST_VALUE(expr)函数
返回最后一个expr的值
案例：根据价格拍续，查询最后一个商品的价格信息
select
id,
t_category,
t_name,
t_price,
t_stock,
last_value(t_price) over w as last_price
from t_goods
window w as (partition by t_category_id order by t_price);
10.5 其他函数
10.5.1 NTH_VALUE(expr,n)函数
返回第n个expr的值
案例：查询t_goods表中排名第2和第3的价格信息
SELECT
id,
t_category,
t_name,
t_price,
NTH_VALUE(t_price,2) OVER w AS second_price,
NTH_VALUE(t_price,3) OVER w AS third_price
FROM
t_goods WINDOW w AS (PARTITION BY t_category_id ORDER BY t_price);
10.5.2 NTILE(n)函数
NTILE(n)函数将分区中的有序数据分为n个桶，记录桶编号
案例：将t_goods表中的商品按照价格分为3组
select
ntile(3) over w as nt,
id,
t_category,
t_name,
t_price
from t_goods
window w as (partition by t_category_id order by t_price);
十一、MySQL的其他函数
以下这些函数无法具体分累，但是也十分常用
11.1 FORMAT(value,n)
FORMAT(value,n)函数返回对数字value进行格式化后的结果数据，其中n表示四舍五入后保留到小数点后n位。如果n的值小于或者等于0，则只保留整数部分。
select format(123.123,2),format(123.123,0),format(123.123,-2);
11.2 CONV(value,from,to)
CONV(value,from,to)函数将value的值进行不同进制之间的转换，value是一个整数，如果任意一个参数为null，则结果返回null
select conv(16,10,2),conv(8888,10,16),conv(null,10,2);
11.3 INET_ATON(value)
INET_ATON(value)函数将以点分隔的IP地址转化为一个数字表示，其中，value为以点表示的IP地址
select inet_aton('192.168.1.100')
11.4 INET_NTOA(value)
INET_NTOA(value)函数将数字形式的IP地址转化为以点分隔的IP地址
select inet_ntoa(3232235876)
可以看到INET_ATON(value)函数与INET_NTOA(value)函数互为反函数
BENCHMARK(n,expr)函数
BENCHMARK(n,expr)函数将表达式expr重复执行n次，主要用于测试MySQL处理expr表达式所耗费的时间
select benchmark(100,MD5('mysql'))
注意：据我测试benchmark(n,expr)函数并不准确
11.5 CAST(value AS type)
CAST(value AS type)函数将value转换为type类型的值，其中type的取值如下所示
示例如下：
select cast('123' as signed)
注意：CONVERT(value,type)函数的作用与CASE(value AS type)函数相同
11.6 CONVERT(value USING char_code)
将value所使用的字符编码修改为char_code
select charset('mysql'),charset(convert('mysql' using 'utf8'));
注意：utf8是utf8mb3的简称，这种字符集只支持码位0-65535之间BMP字符，而一些生僻字、emoji表情等这些补充字符都不在支持范围内，存储这样的字符会报错。因为utfmb3存在这样的缺陷，所以MySQL在5.5.3之后增加了utf8mb4的编码，这是一个可以支持补充字符的字符集。所以，建议创建MySQL表的时候，为了考虑到兼容性，使用utf8mb4，mysql默认的字符编码为utf8mb4
