MySQL数据库函数

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存储过程》大致相同。