MySQL
中提供了丰富的函数支持,包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等,通过这些函数,一方面可以简化业务的代码量,另一方面还能更好的实现各类特殊业务需求,下来一起来聊聊MySQL
支持的函数。
1.1、数学函数
数学函数是MySQL
中最常用的一类函数,主要用来处理所有数值类型的字段值,一起来看看。
• abs(X)
:返回X
的绝对值,如传进-1
,则返回1
。
• ln(X)
:返回X
的自然相对数。
• log(X,Y)
:返回以X
的以Y
为底的对数。
• log10(X)
:返回以X
基数为10
的对数。
• bin(X)
:返回X
的二进制值。
• oct(X)
:返回X
的八进制值。
• hex(X)
:返回X
的十六进制值。
• mod(X,Y)
:返回X
除以Y
的余数。
• ceil(X) | ceiling(X)
:返回不小于X
的最小整数,如传入1.23
,则返回2
。
• round(X)
:返回X
四舍五入的整数。
• floor(X)
:返回X
向下取整后的值,如传入2.34
,会返回2
。
• greatest(X1,X2....,Xn)
:返回集合中的最大整数值。
• least(X1,X2....,Xn)
:返回集合中的最小整数值。
• rand(N)
:返回一个0~N``0~1
之间的随机小数(不传参默认返回0~1
之间的随机小数)。
• sign(X)
:传入正数,返回1
;传入负数,返回-1
;传入0
,返回0
。
• pow(X,Y) | power(X,Y)
:返回X
的Y
次方值。
• pi()
:返回四舍五入后的圆周率,3.141593
。
• sin(X)
:返回X
的正弦值。
• asin(X)
:返回X
的反正弦值。
• cos(X)
:返回X
的余弦值。
• acos(X)
:返回X
的反余弦值。
• tan(X)
:返回X
的正切值。
• atan(X)
:返回X
的反正切值。
• cot(X)
:返回X
的余切值。
• radians(x)
:返回x
由角度转化为弧度的值。
• degrees(x)
:返回x
由弧度转化为角度的值。
• sqrt(X)
:返回X
的平方根。
• exp(e,X)
:返回e
的x
乘方的值。
• truncate(X,N)
:返回小数X
保留N
位精准度的小数。
• format(x,y)
:将x
格式化位以逗号隔开的数字列表,y
是结果的小数位数。
• inet_aton(ip)
:将IP
地址以数字的形式展现。
• inet_ntoa(number)
:显示数字代表的IP
地址。
1.2、字符串函数
• ascii(C)
:返回字符C
的ASCII
码。
• length(S)
:返回字符串的占位空间,传入“竹子爱熊猫”,返回15
,一个汉字占位3
字节。
• bit_length(S)
:返回字符串的比特长度。
• concat(S1,S2,...)
:合并传入的多个字符串。
• concat_wa(sep,S1,S2...)
:合并传入的多个字符串,每个字符串之间用sep
间隔。
• position(str,s) | locate(str,s)
:返回s
在str
中第一次出现的位置,没有则返回0
。
• find_in_set(S,list)
:返回字符串S
在list
列表中的位置。
• insert(S1,start,end,S2)
:使用S2
字符串替换掉S1
字符串中start~end
的内容。
• lcase(S) | lower(S)
:将传入的字符串中所有大写字母转换为小写。
• ucase(S) | upper(S)
:将传入的字符串中所有小写字母转换为大写。
• left(S,index)
:从左侧开始截取字符串S
的index
个字符。
• right(S,index)
:从右侧开始截取字符串S
的index
个字符。
• trim(S)
:删除字符S
左右两侧的空格。
• rtrim(S)
:删除字符S
右侧的空格。
• replace(S,old,new)
:使用new
新字符替换掉S
字符串中的old
字符。
• repeat(str,count)
:将str
字符串重复count
次后返回。
• substring(S,index,N)
:截取S
字符串,从index
位置开始,返回长度为N
的字符串。
• reverse(S)
:将传入的字符串反转,即传入Java
,返回avaJ
。
• quote(str)
:用反斜杠转移str
中的英文单引号。
• strcmp(S1,S2)
:比较两个字符是否相同。
• lpad(str,len,s)
:对str
字符串左边填充len
个s
字符。
• rpad(str,len,s)
:对str
字符串右边填充len
个s
字符。
1.3、日期和时间函数
• curdate() | current_date()
:返回当前系统的日期,如2022-10-21
。
• curtime() | current_time()
:返回当前系统的时间,如17:30:52
。
• now() | sysdate()
:返回当前系统的日期时间,如2022-10-21 17:30:59
。
• unix_timestamp()
:获取一个数值类型的unix
时间戳,如1666348711
。
• from_unixtime()
:将unix_timestamp()
获取的数值时间戳,格式化成日期格式。
• month(date)
:获取date
中的月份。
• year(date)
:获取date
中的年份。
• hour(date)
:获取date
中的小时。
• minute(date)
:获取date
中的分钟。
• second(date)
:获取date
中的秒数。
• monthname(date)
:返回date
中月份的英文名称。
• dayname(date)
:获取日期date
是星期几,如Friday
。
• dayofweek(date)
:获取date
位于一周的索引位置,周日是1
、周一是2
...周六是7
。
• week(date)
:获取date
是本年的第多少周。
• quarter(date)
:获取date
位于一年中的哪个季度(1~4
)。
• dayofyear(date)
:获取date
是本年的第多少天。
• dayofmonth(date)
:获取date
是本月的第多少天。
• time_to_sec(time)
:将传入的时间time
转换为秒数,比如"01:00:00" = 3600s
。
• date_add(date,interval 时间 单位) | adddate(...)
:将date
与给定的时间按单位相加。
• date_sub(date,interval 时间 单位) | subdate(...)
:将date
与给定的时间按单位相减。
• addtime(date,time)
:将date
加上指定的时间,如addtime(now(),"01:01:01")
。
• subtime(date,time)
:将date
减去指定的时间。
• datediff(date1,date2)
:计算两个日期之间的间隔天数。
• last_day(date)
:获取date
日期这个月的最后一天。
• date_format(date,format)
:将一个日期格式化成指定格式,format
可选项如下:
• %a
:工作日的英文缩写(Sun~Sat
)。
• %b
:月份的英文缩写(Jan~Dec
)。
• %c
:月份的数字格式(1~12
)。
• %M
:月份的英文全称(January~December
)。
• %D
:带有英文后缀的数字月份(1th、2st、3nd....
)。
• %d
:一个月内的天数,双数形式(01、02、03....31
)。
• %e
:一个月内的天数,单数形式(1、2、3、4....31
)。
• %f
:微妙(000000~999999
)。
• %H
:一天内的小时,24
小时的周期(00、01、02...23
)。
• %h | %I
:一天内的小时,12
小时的周期(01、02、03...12
)。
• %i
:一小时内的分钟(00~59
)。
• %j
:一年中的天数(001~366
)。
• %k
:以24
小时制显示时间(00~23
)。
• %l
:以12
小时制显示时间(01~12
)。
• %m
:月份的数字形式,双数形式(01~12
)。
• %p
:一天内的时间段(上午AM
、下午PM
)。
• %r
:12
小时制的时间(12:01:09 AM
)。
• %S | %s
:秒数,双数形式(00~59
)。
• %T
:24
小时制的时间(23:18:22
)。
• %U
:一年内的周(00~53
)。
• time_format(time,format)
:将一个时间格式化成指定格式。
• str_to_date(str,format)
:将日期字符串,格式化成指定格式。
• timestampdiff(unit,start,end)
:计算两个日期之间间隔的具体时间,unit
是单位:
• year
:年。
• quarter
:季度。
• month
:月。
• week
:周。
• day
:天。
• hour
:小时。
• minute
:分钟。
• second
:秒数。
• microsecond
:微妙。
• weekday(date)
:返回date
位于一周内的索引(0
是周一...6
是周日)。
1.4、聚合函数
聚合函数一般是会结合select、group by having
筛选数据使用。
• max(字段名)
:查询指定字段值中的最大值。
• min(字段名)
:查询指定字段值中的最小值。
• count(字段名)
:统计查询结果中的行数。
• sum(字段名)
:求和指定字段的所有值。
• avg(字段名)
:对指定字段的所有值,求出平均值。
• group_concat(字段名)
:返回指定字段所有值组合成的结果,如下:
• distinct(字段名)
:对于查询结果中的指定的字段去重。
这里稍微介绍一个日常业务中碰到次数较多的需求:
select *from zz_users;
+---------+-----------+----------+----------+---------------------+
| user_id | user_name | user_sex | password | register_time |
+---------+-----------+----------+----------+---------------------+
|1|熊猫|女|6666|2022-08-1415:22:01|
|2|竹子|男|1234|2022-09-1416:17:44|
|3|子竹|男|4321|2022-09-1607:42:21|
|4|黑熊|男|8888|2022-09-1723:48:29|
|8|猫熊|女|8888|2022-09-2717:22:29|
|9|棕熊|男|0369|2022-10-1723:48:29|
+---------+-----------+----------+----------+---------------------+
-- 基于性别字段分组,然后显示各组中的所有ID
select
convert(
group_concat(user_id orderby user_id asc separator ",")
using utf8)as"分组统计"
from`zz_users`groupby user_sex;
+-------------+
|分组统计|
+-------------+
|1,8|
|2,3,4,9|
+-------------+
上述利用了group_concat()、group by
实现了按照一个字段分组后,显示对应分组的所有ID
。
1.5、控制流程函数
• if(expr,r1,r2)
:expr
是表达式,如果成立返回r1
,否则返回r2
。
• ifnull(v,r)
:如果v
不为null
则返回v
,否则返回r
。
• nullif(v1,v2)
:如果v1 == v2
,则返回null
,如果不相等则返回V1
。
-- if的用例
selectif(user_id >3,"√","×")from zz_users;
-- ifnull的用例
select ifnull(user_id,"×")from zz_users;
-- case语法1:
case<表达式>
when<值1>then<操作>
when<值2>then<操作>
...
else<操作>
end;
-- 用例:判断当前时间是星期几
selectcase weekday(now())
when0then'星期一'
when1then'星期二'
when2then'星期三'
when3then'星期四'
when4then'星期五'
when5then'星期六'
else'星期天'
endas"今天是星期几?";
-- case语法2:
case
when<条件1>then<命令>
when<条件2>then<命令>
...
else commands
end;
-- 用例:判断今天是星期几
selectcase
when weekday(now())=0then'星期一'
when weekday(now())=1then'星期二'
when weekday(now())=2then'星期三'
when weekday(now())=3then'星期四'
when weekday(now())=4then'星期五'
when weekday(now())=5then'星期六'
else'星期天'
endas"今天是星期几?";
简单聊一下CASE
语法,第一种语法就类似于Java
中的switch
,而第二种语法就类似于多重if
,通过CASE
语法能够让SQL
更加灵活,完成类似于存储过程的工作。
1.6、加密函数
• password(str)
:将str
字符串以数据库密码的形式加密,一般用在设置DB
用户密码上。
• md5(str)
:对str
字符串以MD5
不可逆算法模式加密。
• encode(str,key)
:通过key
密钥对str
字符串进行加密(对称加密算法)。
• decode(str,key)
:通过key
密钥对str
字符串进行解密。
• aes_encrypt(str,key)
:通过key
密钥对str
字符串,以AES
算法进行加密。
• aes_decrypt(str,key)
:通过key
密钥对str
字符串,以AES
算法进行解密。
• sha(str)
:计算str
字符串的散列算法校验值。
• encrypt(str,salt)
:使用salt
盐值对str
字符串进行加密。
• decrypt(str,salt)
:使用salt
盐值对str
字符串进行解密。
1.7、系统函数
• version()
:查询当前数据库的版本。
• connection_id()
:返回当前数据库连接的ID
。
• database() | schema()
:返回当前连接位于哪个数据库,即use
进入的库。
• user()
:查询当前的登录的所有用户信息。
• system_user()
:返回当前登录的所有系统用户信息。
• session_user()
:查询所有连接的用户信息。
• current_user()
:查询当前连接的用户信息。
• charset(str)
:返回当前数据库的编码格式。
• collation(str)
:返回当前数据库的字符排序规则。
• benchmark(count,expr)
:将expr
表达式重复运行count
次。
• found_rows()
:返回最后一个select
查询语句检索的数据总行数。
• cast(v as 类型)
:将v
转换为指定的数据类型。