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
地址。 -
......
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
字符。 -
......
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
是周日)。
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
。
5、控制流程函数
-
if(expr,r1,r2)
:expr
是表达式,如果成立返回r1
,否则返回r2
。 -
ifnull(v,r)
:如果v
不为null
则返回v
,否则返回r
。 -
nullif(v1,v2)
:如果v1 == v2
,则返回null
,如果不相等则返回V1
。
-- if的用例
select if(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;
-- 用例:判断当前时间是星期几
select case weekday(now())
when 0 then '星期一'
when 1 then '星期二'
when 2 then '星期三'
when 3 then '星期四'
when 4 then '星期五'
when 5 then '星期六'
else '星期天'
end as "今天是星期几?";
-- case语法2:
case
when <条件1> then <命令>
when <条件2> then <命令>
...
else commands
end;
-- 用例:判断今天是星期几
selectcase
when weekday (now()) = 0 then '星期一'
when weekday (now()) = 1 then '星期二'
when weekday (now()) = 2 then '星期三'
when weekday (now()) = 3 then '星期四'
when weekday (now()) = 4 then '星期五'
when weekday (now()) = 5 then '星期六'
else '星期天'
end as "今天是星期几?";
简单聊一下CASE
语法,第一种语法就类似于Java
中的switch
,而第二种语法就类似于多重if
,通过CASE
语法能够让SQL
更加灵活,完成类似于存储过程的工作。
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
字符串进行解密。
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
转换为指定的数据类型。
除开上述这些函数之外,其实在MySQL
还有很多很多的函数,但目前几乎已经将所有常用的函数全部列出来了,因此对于其他偏冷门一些的函数就不再介绍。当然,就算你需要的某个功能在MySQL
中没有提供函数支持,你也可以通过create function
的方式自定义存储函数,其逻辑与上篇讲到的《MySQL存储过程》大致相同。