MySQL 常用函数

我真真的是小白

于 2024-04-14 19:48:59 发布

阅读量1.1k

点赞数 19

分类专栏： MySQL 文章标签： mysql oracle 数据库

本文链接：https://blog.csdn.net/m0_53067943/article/details/137752559

版权

MySQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

MySQL 常用函数

在这里插入图片描述

MySQL 函数是 MySQL 数据库提供的内部函数，这些内部函数可以帮助用户更加方便地处理表中的数据。函数就像预定的公式一样存放在数据库里，每个用户都可以调用已经存在的函数来完成某些功能。

同时 MySQL 数据库还支持用户自己建立函数，以适应实际的业务操作。正确使用函数会让读者在编写 SQL 语句时起到事半功倍的效果。

一、MySQL函数分类

1. 单行函数

字符串函数

主要用于处理字符串。其中包括字符串连接函数、字符串比较函数、将字符串的字母都变成小写或大写字母的函数和获取子串的函数等。

大多数方法与 Java 中的 String 类的方法名类似

数学函数

主要用于处理数字。这类函数包括绝对值函数、正弦函数、余弦函数和获得随机数的函数等。

日期函数

主要用于处理日期和时间。其中包括获取当前时间的函数、获取当前日期的函数、返回年份的函数和返回日期的函数等。

条件函数

主要用于在 SQL 语句中控制条件选择。其中包括 IF 语句、CASE 语句和 WHERE 语句

系统信息函数

主要用于获取 MySQL 数据库的系统信息。其中包括获取数据库名的函数、获取当前用户的函数和获取数据库版本的函数等。

其他函数

主要包括格式化函数和锁函数等。

2. 聚合函数

AVG（平均值）函数：返回指定组的平均值，空值会被忽略。
COUNT（统计）函数：返回指定组中项目的总数量。
MAX（最大值）函数：返回指定数据的最大值。
MIN（最小值）函数：返回指定数据的最小值。
SUM（求和）函数：返回指定数据的和，只能用于数字列，空值会被忽略。

二、函数详解

1. 字符函数

1）length(str)

获取参数值的字节个数

对于 UTF-8 字符集来说，一个英文占 1 个字节；一个中文占 3 个字节；

对于 GBK 字符集来说，一个英文占 1 个字节；一个中文占 2 个字节；

select length('我真的是小白') as length;

2）concat(str1,str2,…)

将字符串拼接，通过输入的参数str1、str2等，将他们拼接成一个字符串。

select concat('我真的','是','小白') as name;

concat() 函数可以在 mapper.xml 文件中模糊查询时使用，具体示例如下：

select * from t_student where name like concat('%',#{name},'%')

3）upper(str)/lower(str)

upper(str)：将字符中的所有字母变为大写
lower(str)：将字符中的所有字母变成小写

select upper('Hello') as 大写;
select lower('Hello') as 小写;

4）substr(str,start,len)

str 为输入字符串，从 start 位置（包含）开始截取字符串，len 表示要截取的长度；

如果没有指定 len 长度，则表示从 start 开始起，截取到字符串末尾。

select substr('你好，我是MySQL',2,6) as result; -- 结果为：好，我是My

5）trim(str)

去掉字符串前后的空格；该函数只能去掉字符串前后的空格，不能去掉字符串中间的空格。

select trim(' 你好，我 是 MySQL ') as result;

2. 数学函数

1）round(x[, y])

四舍五入，y 表示保留小数的位数。

当对正数进行四舍五入：按照正常的计算方式，四舍五入即可。

当对负数进行四舍五入：先把符号丢到一边，对去掉负号后的正数进行四舍五入，完成以后，再把这个负号，补上即可。

select round(1.5472) as result;
-- 结果为：2
select round(1.5472, 2) as result;
-- 结果为：1.55
select round(-1.5472, 2) as result;
-- 结果为：-1.55

2）ceil(x)

向上取整，返回 大于等于 该参数的最小整数。

select ceil(1.1) as result;
select ceil(1.9) as result;
-- 结果皆为：2

3）floor(x)

向下取整，返回 小于等于 该参数的最大整数。

select floor(1.1) as result;
select floor(1.9) as result;
-- 结果皆为：1

4）mod(x,y)

取 x 除以 y 的余数。当 x 为正数，结果就是正数。当 x 为负数，结果就是负数。

select mod(12,5) as result;

5）pow(x,y)

此函数是用于计算指数函数，x 为底，y 为指数。

select pow(5,3) as result;

6）pi()

返回圆周率 π。

select pi();
-- 结果为：3.141593

3. 日期函数

日期的含义：指的是我们常说的年、月、日。

时间的含义：指的是我们常说的时、分、秒。

补充时间格式符含义表

序号	格式符	含义
1	%Y	四位的年份
2	%y	两位的年份
3	%m	月份（01,02,…,11,12）
4	%c	月份（1,2,…,11,12）
5	%d	日（01,02,…）
6	%H	小时（24小时制）
7	%h	小时（12小时制）
8	%i	分钟（00,01,…,59）
9	%s	秒（00,01,…,59）

1）now()

返回当前系统的日期和时间

select now(); -- 结果为：2024-03-29 10:10:40

2）curdate()/curtime()

前者只返回系统当前的日期，不包含时间；后者只返回当前的时间，不包含日期。

select curdate(); -- 结果：2024-03-29
select curtime(); -- 结果：10:10:40

3）year()/month()/day()

获取日期和时间中的年、月、日、时、分、秒

year()：获取年份
month()：获取月份
day()：获取日
hour()：获取小时
minute()：获取分钟
second()：获取秒数

select year(now()) as '年份'; -- 结果为：2024
select month(curdate()) as '月份'; -- 结果为：3
select day('2024-03-29') as '天数'; -- 结果为：29

select hour(now()) as "小时"; -- 结果为：10
select minute(curtime()) as "分钟"; -- 结果为：30
select second('10:10:40') as "秒数"; -- 结果为：30

4）date_format()

用于格式化日期和时间值，并将其转换为字符串形式

select date_format(now(),'%Y年%m月%d日') as date; -- 结果为：2024年03月29日
-- 也可以将表中的某个字段进行时间格式
select date_format(createTime '%Y-%m-%d %H:%i:%s') as formatted_date from t_user;

5）quarter()/weekofyear()

前者返回当前时刻所属的季度；后者返回当前时刻所属周数。

select quarter(curdate()) as '当前季度'; -- 结果为：1
-- 也可以获取某个字段所属季度
select quarter(create_time) as '当前季度' from t_user;
select weekofyear(curdate()) as '当前周数'; -- 结果为：13
select weekofyear(create_time) as '当前周数' from t_user;

6）last_day()

返回某个月的最后一天

select last_day(now()) as '最后一天'; -- 结果为：2024-03-31

7）datediff()

用法：datediff(end_date, start_date)

用于计算两个时间相差的天数

select concat('距离2025年还有 ', datediff('2025-01-01',curdate()), ' 天！') as title;
-- 结果为：距离2025年还有 278 天！

4. 条件函数

1）if(expr,v1,v2)

实现 if-else 的效果，如果 expr 是 true，返回 v1。如果 expr 是 false，返回 v2

-- 示例1
select student_name, score, 
       if(score >= 90, 'A', 'B') as grade
from t_student;
-- 示例2
select name, age, 
	if(age >= 18, IF(gender = 'M', 'Adult Male', 'Adult Female'), 'Minor') as status 
from t_user;

2）ifnull(expr1,expr2)

用来处理可能的 null 值

当提供的第一个参数为 null 时，返回第二个参数的值；如果第一个参数非 null，则直接返回第一个参数的值。

select ifnull(name, '未知') as name from t_user;

3）case…when

MySQL 中的 case 语句是一个条件表达式，提供了灵活的方式来根据不同的条件执行不同的计算或返回不同的值。

case 语句有两种主要用法：简单的 case 表达式和搜索型 case 表达式。

简单CASE表达式：

CASE column_name
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_value
END

在这种形式中，column_name 是要测试的列，而 value1, value2 等是与之比较的值。如果 column_name 的值等于 value1，则返回 result1，如果等于 value2，则返回 result2，依此类推。如果没有匹配项，则返回 ELSE 子句中的 default_value。如果省略了 ELSE 子句并且没有匹配项，则返回 NULL。示例如下：

SELECT student_id, score,
       CASE score
           WHEN 90 THEN '优秀'
           WHEN 80 THEN '良好'
           WHEN 70 THEN '中等'
           ELSE '及格'
       END AS grade
FROM t_student;

搜索型CASE表达式：

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_value
END

在这个形式中，condition1, condition2 等是布尔表达式，当它们为真时，对应的 result1, result2 等将被返回。最后的 ELSE 子句同样用于提供一个默认值，当所有条件都不满足时返回该值。示例如下：

SELECT name,
       CASE
           WHEN name LIKE 'Mr.%' THEN 'Male'
           WHEN name LIKE 'Ms.%' THEN 'Female'
           ELSE 'Unknown'
       END AS gender_guess
FROM t_student;

5. 其他函数

1）version()

查看MySQL系统版本信息号

select version(); -- 结果为：8.0.26

2）processlist

查看用户的连接信息

show processlist;

6. 聚合函数

avg()：返回指定组的平均值，忽略 null 值
count()：返回指定组中项目的总数量。count(*) 不忽略 null 值，count(字段) 忽略 null 值
max()：返回指定数据的最大值。忽略 null 值（不影响结果）
min()：返回指定数据的最小值。忽略 null 值（不影响结果）
sum()：返回指定数据的和，只能用于数字列。忽略 null 值（不影响结果）

注意：

sum() 函数和 avg() 函数，传入整型/小数类型才有意义；忽略空值行。对于字符串类型、日期/时间类型的计算都没有太大意义。
max() 函数和 min() 函数，传入整型/小数类型、日期/时间类型意义较大
count() 函数可以传入任何数据类型，但是碰到 null 要注意，空值跳过，不计数。
sum()/count() 方法计算平均值时，结果和 avg() 函数可能会不一样。

三、count() 常见场景

count() 函数用于对表中的记录进行统计，通常有以下几种用法：

count(*)：最常用的计数方法，包含 null 值
count(1)：与 count(*) 效果相同，效率也相似，包含 null 值
count(主键字段)：主键通常是唯一且不允许为空的，所以效果与上面两个一致，不包含 null 值；（如果主键列确实有 null 值，会忽略这些 null 值，但是这违反了主键约束）
count(字段)：统计该列中非 null 值的行数，即不包含 null 值

那么，四者的效率如何呢？即哪种性能最好，答案如下：

count(*) = count(1) > count(主键字段) > count(字段)

count() 是一个聚合函数，函数的参数不仅可以是字段名，也可以是其他任意表达式，该函数作用是统计符合查询条件的记录中，函数指定的参数不为 null 的记录有多少个。

在通过 count() 函数统计有多少个记录时，MySQL 的 server 层会维护一个名叫 count 的变量。

server 层会循环向 InnoDB 读取一条记录，如果 count 函数指定的参数不为 null，那么就会将变量 count 加 1，直到符合查询的全部记录被读完，就退出循环。最后将 count 变量的值发送给客户端。

count(1)、 count(*)、 count(主键字段) 在执行的时候，如果表里存在二级索引，优化器就会选择二级索引进行扫描。

所以，如果要执行 count(1)、 count(*)、 count(主键字段) 时，尽量在数据表上建立二级索引，这样优化器会自动采用 key_len 最小的二级索引进行扫描，相比于扫描主键索引效率会高一些。

关于四种常用 count() 的原理，可以参考这篇文章，原文链接如下：MySQL 中 count(*) 和 count(1) 有什么区别？哪个性能最好？