MySQL之常用函数

MySQL函数是MySQL数据库提供的内置函数。这些内置函数可以帮助用户更加方便地处理表中的数据。内置函数主要有 数学函数，字符串函数，日期和时间函数，条件判断函数，系统信息函数，加密函数和其他函数。本篇主要介绍一些常用的函数。

1.数学函数

1.1 abs(x)函数

abs(x)函数用于求绝对值。

例如：求2和-2的绝对值

1.2 floor(x)函数

floor(x)函数返回小于或等于x的最大整数。

例如：求小于或等于2.5及-2.5的最大整数

1.3 rand()函数

rand()函数是返回0~1的随机数

例如：利用函数获取两个随机数

与floor函数组合使用：
例如：生成1~100之间的随机数

1.4 PI()函数

PI()函数用于返回圆周率

1.5 truncate(x,y)函数

truncate(x,y)函数返回x保留到小数点后y位的值。

例如：返回圆周率保留两位小数

1.6 round(x)函数和round(x,y)函数

round(x)函数对x四舍五入，round(x,y)返回x保留到y位，截断时进行四舍五入处理。

例如：对圆周率四舍五入和保留三位小数后四舍五入

1.7 sqrt(x)函数

sqrt(x)函数用于求平方根。

例如：求16和100的平方根

2.字符串函数

字符串函数是MySQL中最常用的一类函数，主要用于处理表中的字符串。

2.1 insert(s1,x,len,s2)函数

insert(s1,x,len,s2)函数将字符串s1中x位置开始长度为len的字符串替换为s2

例如：将‘我会吃饭睡觉打游戏‘中的’游戏‘替换成’豆豆‘

2.2 upper(x)函数ucase(x)函数

upper(x)函数和ucase(x)函数将字符串x的所有字母变成大写。

例如：将hello world变成大写

2.3 left(x,n)函数

left(x,n)函数返回字符串x的前n个字符。

例如：把字符串’我叫张三来自中国‘返回前四个字符

2.4 concat(x1,x2,x3…)函数

返回结果为连接参数产生的字符串。如果有任何一个参数为null，则返回值为null。

2.5 rtrim(x)函数

rtrim(x)函数将去掉字符串x结尾处的空格。

例如：

2.6 substring(x,n,len)函数

substring(x,n,len)函数从字符串x的第n个位置开始获取长度为len的字符串。

2.7 reverse(x)函数

reverse(x)函数将字符串x的顺序反过来。

2.8 field(x,x1,x2,x3…)函数

field(x,x1,x2,x3…)函数返回第一个与字符串x匹配的字符串的位置。

2.9 position(x1 in x)函数和instr(x,x1)函数

(1)position(x1 in x)函数表示子字符串x1在字符串x中的开始位置。
(2)instr(x,x1)函数表示子字符串x1在字符串x中的开始位置。

2.10 substring_index()函数

格式：substring_index(str,delim,count)。

str：要处理的字符串。
delim：分隔符。
count：计数。

例如：str=www.wikibt.com
substring_index(str,‘.’,1)结果是：www.wikibt。
也就是说，如果count是正数，那么就是从左往右数，第N个分隔符的左边的全部内容；如果是负数，那么就是从右边数，第N个分隔符右边的所有内容。
例如：想要得到中间的wikibt。
substring_index(substring_index(str,‘.’,2),‘.’,-1)。

例：统计每种性别的人数。

结果：

3.日期和时间函数

日期和时间函数主要用于对表中的日期和时间数据的处理。

3.1 curdate()函数和current_date()函数

curdate()函数和current_date()函数都可以获取当前日期。

3.2 curtime()函数和current_time()函数

curtime()函数和current_time()函数用于获取当前时间。

3.3 now()函数

now()函数可以获取当前日期和时间。

3.4 datediff(t1,t2)函数

datediff(t1,t2)函数用于计算日期t1与t2之间相隔的天数。

3.5 adddate(t,n)函数

adddate(t,n)函数用于返回t日期加上n天后的日期。

3.6 subdate(t,n)函数

subdate(t,n)函数返回t日期减去n天的日期。

4.条件判断函数

条件函数用来在SQL语句中进行条件判断。根据不同的条件，执行不同的SQL语句。

(1)if(n,v1,v2)：如果表达式n成立，则执行v1；否则执行v2。

(2)ifnull(v1,v2)：如果v1不为空，则显示v1的值；否则显示v2的值。

(3)case when n1 then x1 [when n2 then x2…else xn] end
case表示函数开始，end表示函数结束。如果表达式n1成立，则返回x1的值；如果表达式n2成立，则返回x2的值。以此类推，最后遇到else时，返回xn的值。

例如：对tb_book1表中的书本价格进行评论

5.系统信息函数

系统信息函数用来查询MySQL数据库的系统信息。例如：查询数据库的版本，查询数据库的当前用户等。
(1)select version()：获取数据库的版本号。
(2)select connection_id()：获取服务器的连接数。
(3)select database()：获取当前数据库名。
(4)select user()：获取当前用户。

6.加密函数

加密函数是MySQL中用来对数据进行加密的函数。因为数据库中有些很敏感的信息不希望被其他人看到，所以就可以通过加密的方式来使这些数据变成看似乱码的数据。例如，用户密码就应该进行加密。

6.1 加密函数password(str)

password(str)函数可以对字符串str加密，主要是用来给用户的密码加密。

6.2 加密函数md5(str)

md5(str)函数可以对字符串str进行加密，主要对普通的数据加密。

例如：对字符串123加密

7.窗口函数

窗口函数可以对分组统计结果中的每一条记录进行计算。

格式：函数 over ([partition by 字段名 asc[desc]])

partition by：指定窗口函数按哪些字段进行分组，分组后，窗口函数就可以在每个分组中分别执行。
order by：指窗口函数按哪些字段进行排序，执行排序操作使窗口函数按照排序后的数据记录的顺序进行编号。

7.1 row_number()函数

row_number()函数功能：对数据中的序号进行顺序显示。

当前信息表：

例：查找每个大学的所有学生信息按gpa升序排列

select *,
row_number() over (partition by university order by gpa) as '排名' 
from user_profile;

输出结果：

例：查找每个学校gpa最低的人

select device_id,university,gpa 
from(
	select *,
	row_number() over (partition by university order by gpa) 
	as rn from user_profile) as m 
where rn=1;

输出结果：

7.2 rank()函数

rank()函数功能：对序号进行并列排序，指定字段数值相同则会产生相同的序号记录，且产生序号间隙(比如1,1,3,4而不是1,2,3,4)。
使用rank函数必须用order by参数。

例：查找每个大学所有的学生信息按gpa降序排列

select *,
rank() over (partition by university order by gpa desc) as '排名' 
from user_profile;

输出结果：

7.3 dense_rank()函数

dense_rank()函数功能：对序号进行并列排序，指定字段数值相同则会产生相同的序号记录，且产生序号间隙(比如1,1,2,3而不是1,2,3,4)。

例：查找每个大学所有的学生信息按gpa降序排列

select *,
dense_rank() over (partition by university order by gpa desc) as '排名' 
from user_profile;

输出结果：

7.4 lag()函数

lag(expr,n)函数的功能：返回当前的前n行的expr值。如果n为0，则lag函数计算的是当前行；如果省略n值则默认n为1，n必须为非负整数。

例：查询每个学生前一位的gpa，按学校分组gpa降序排列

select *,
lag(gpa,1) over (partition by university order by gpa desc) 
as '前一位gpa' 
from user_profile;

输出结果：

7.5 lead()函数

lead(expr,n)函数功能：返回当前行的后n行的expr值。

例：查询当前行gpa与后一行gpa的差

select device_id,university,gpa,hou_gpa,gpa-hou_gpa as diff_gpa 
from (
	select *,
	lead(gpa,1) over (partition by university order by gpa desc) 
	as hou_gpa from user_profile) 
as t;

输出结果：

7.6 first_value()函数

first_value(expr)函数功能：取分组排序之后，该组expr值都等于第一个。

代码：

select *,
first_value(gpa) over (partition by university order by gpa desc) 
as '首位gpa' from user_profile;

输出结果：

分组之后gpa都为第一位的值。

7.7 last_value()函数

last_value(expr)函数与first_value()函数相反，它是等于最后一位的值。

7.8 nth_value()函数

nth_value(expr,n)函数与first_value(),last_value()函数不同，它是返回第n个值。

8.其他函数

MySQL除了上述的内置函数外，还包括很对函数。
(1)format(x,n)：将数字x进行格式化，将x保留到小数点后n位。这个过程会进行四舍五入的操作。
(2)bin(x)：返回x的二进制编码。
(3)hex(x)：返回x的十六进制编码。
(4)oct(x)：返回x的八进制编码。
(5)inet_ntoa(n)：将数字n转换成IP地址的形式。
(6)inet_aton(IP)：将IP地址转换为数字表示。
(7)cast(x as type)：将x变为type类型，这个函数只binary,char,date,
datetime,time,signed integer,unsigned integer这些类型起作用。但只是改变了输出值的数据类型，并没有改变表中字段的类型。