MySQL常用函数

---

一、数学函数

函数	说明
abs(v)	返回v的绝对值
floor(v)	返回不大于v的最大整数(去掉小数)
ceil(v)	返回不小于v的最小整数(有效数进1)
truncate(v,n)	对v保留n位小数精度
round(v)	保留整数，四舍五入
round(v,n)	保留v的n位小数，四舍五入
rand()	返回0~1的随机数
sign(v)	正数返回1，负数返回-1，0返回0
pi()	返回圆周率的值，保留7位小数
pow(x,y)	返回x的y次乘方值
sqrt(x)	返回非负数x的二次方根
exp(n)	返回e的n次方
mod(n,m)	返回n除m的余数
log(v)	返回v的自然对数(v相对于基数2的对数)
log10(v)	返回v的基数为10的对数
redians(v)	角度转弧度
degrees(v)	弧度转角度

二、字符串函数

函数	说明
char_length(v)	返回字符串的字符长度
length(v)	返回字符串的字节长度(一个汉字三个字节)
concat(s1,s2,…)	字符串拼接(任意一个为NULL则返回值为NULL)
concat_ws(x,s1,s2,…)	间隔符x拼接字符串
group_concat(FIELD_NAME)	行转列(将字段名对应列的值以逗号拼接成一个字符串对象)
insert(s1,x,len,s2)	从s1的x位置开始，将长度为len的字符串替换成s2(删除/插入/替换)
lower(v)	字符串中字母全部转换为小写形式
upper(v)	字符串中字母全部转换为大写形式
left(v,len)	从v的左边开始截取长度为len的字符串
right(v,len)	从v的右边开始截取长度为len的字符串
mid(v,x,len)	从v的x位置开始截取长度为len的字符串
lpad(s1,len,s2)	从s1的左侧用s2填充至len字符长度(若s1长度大于len则返回值缩短至len字符)
rpad(s1,len,s2)	从s1的右侧用s2填充至len字符长度(若s1长度大于len则返回值缩短至len字符)
ltrim(v)	清空字符串v左侧所有空格
rtrim(v)	清空字符串v右侧所有空格
trim(v)	清空字符串v两端所有空格
trim(s from v)	清空字符串v两端所有字符串s
repeat(v,n)	返回将v重复n次组成的字符串
space(n)	返回一个由n个空格组成的字符串
repalce(v,s1,s2)	将s中所有的s1替换为s2
strcmp(s1,s2)	s1<s2返回-1，s1=s2返回0，s1>s2返回1
substring(s,x,len)	等同于mid(s,x,len)
substring_index(v,x,n)	以x作为分隔符，从v左侧截取第n个分隔符之前的字符串
locate(s1,s2)	返回s1在s2中的开始位置
reverse(v)	返回将v翻转之后的字符串
elt(n,s1,s2,…)	返回第n个字符串
field(v,s1,s2,…)	返回第一个与v匹配的字符串的位置
find_in_set(v,‘s1,s2,…’)	返回包含多个字符串的对象中与v匹配的字符串位置
make_set(x,s1,s2,…)	按x的二进制数从s1,s2,…中选取字符串

三、日期函数

函数	说明
curdate()	返回当前日期的年月日(yyyy-MM-dd)
curtime()	返回当前日期的时分秒(HH:mm:ss)
now()	返回当前日期的年月日时分秒(yyyy-MM-dd HH:mm:ss)
unix_timestamp()	获取当前时间的毫秒数
unix_timestamp(date)	获取指定日期的毫秒数
from_unix(date)	将毫秒数转化为时间格式
utc_date()	当前UTC(世界标准时间)的日期值(格林尼治时间，中国地区为UTC+8)
utc_time()	当前UTC(世界标准时间)的时间值(格林尼治时间，中国地区为UTC+8)
year(date)	返回日期的年份值
month(date)	返回日期的月份值
day(date)	返回日期的日值
hour(date)	返回日期的小时值
minute(date)	返回日期的分钟值
second(date)	返回日期的秒钟值
milliseconds(date)	返回日期的毫秒值
weekofyear(date)	本年第几周(1~53)
weekday(date)	本周第几天(周日为第一天)
dayofweek(date)	本周第几天(周一为第一天)
dayofyear(date)	本年第几天
quarter(date)	本年第几季度(1~4)
datediff(d1,d2)	d1至d2之间的天数
adddate(d,n) subdate(d,n)	日期d加上n天的日期 日期d减去n天的日期
adddate(date,INTERVAL expr TYPE) subdate(date,INTERVAL expr TYPE)	对日期增加指定类型的指定时间间隔并返回新的日期 对日期减少指定类型的指定时间间隔并返回新的日期
date_format(date,format)	按照format的指定格式显示日期

四、系统级函数

函数	说明
if(expr,v1,v2)	如果表达式expr成立(true)，则返回v1，否则返回v2
ifnull(v1,v2)	如果v1不为空，则返回v1，否则返回v2
case when expr1 then v1 [when expr2 then v2][else vn] end	case表示函数开始，end表示函数结束，如果表达式expr1成立，则返回v1， 如果表达式expr2成立，则返回v2，以此类推，最后else返回vn
case expr when e1 then v1 [when e2 then v2][else vn] end	case表示函数开始，end表示函数结束，如果表达式expr取值e1，则返回v1， 如果表达式expr取值e2，则返回v2，以此类推，最后else返回vn
database()	获取当前数据库名
password(str)	对字符串str进行不可逆加密
md5(str)	对字符串str进行md5加密
encode(src_str,pass_str)	用字符串pass_str加密字符串src_str，加密后的结果是一个二进制数
decode(crpyt_str,pass_str)	用字符串pass_str解密字符串crpyt_str
conv(N,from_base,to_base)	将数值N由from_base进制转换为to_base进制，返回字符串类型

五、聚合函数

函数	说明
min(FIELD_NAME)	取字段名对应列中的最小值
max(FIELD_NAME)	取字段名对应列中的最大值
sum(FIELD_NAME)	对字段名对应列的数据求和
avg(FIELD_NAME)	对字段名对应列的数据求平均值
count(1/[distinct] FIELD_NAME)	对字段名对应列的数据进行[去重]计数

---

六、个别函数解析

1.group_concat

group_concat([distinct] FIELD_NAME1 [order by FIELD_NAME2 ASC/DESC][separator 'sep'])

下面表中一个老师对应多个科目，导致老师名字出现多次，这时可以使用group_concat将老师名字去重，同时还能显示每个老师对应的教学科目

代码如下：

mysql> select
    -> C.cid,T.tname,C.cname
    -> from course C
    -> inner join teacher T on T.tid=C.teacher_id
    -> order by C.cid;

+ -------- + -------- +
|  tname |  cname |
+ -------- + -------- +
|   张三   |   生物   |
|   张三   |   体育   |
|   李四   |   物理   |
|   王五   |   化学   |
|   李杰   |   美术   |
|   李四   |   音乐   |
|   王五   |   语文   |
|   李杰   |   数学   |
|   李四   |   地理   |
|   张三   |   研究   |
+ -------- + -------- +

mysql> select
    -> distinct T.tname,group_concat(C.cname)
    -> from course C
    -> inner join teacher T on T.tid=C.teacher_id
    -> group by T.tname;

+ -------- + -------------------------------- +
|  tname |  group_concat(C.cname) |
+ -------- + -------------------------------- +
|   张三   |  生物,体育,研究                |
|   李四   |  物理,音乐,地理                |
|   李杰   |  美术,数学                        |
|   王五   |  化学,语文                        |
+ -------- + -------------------------------- +

---

2.substring_index

substring_index(VERCHAR,SEPARATOR,NUM)

select substring_index('aa,bb,cc,dd',',',2);

+ -------- +
|  aa,bb  |
+ -------- +