前言
MySQL函数是MySQL数据库提供的内置函数。这些内置函数可以帮助用户更加方便地处理表中的数据。内置函数主要有 数学函数，字符串函数，日期和时间函数，条件判断函数，系统信息函数，加密函数和其他函数。本篇主要介绍一些常用的函数。
目录
3.8 date_add(日期，Interval 数字 时间格式 )
3.9 date_use(日期，Interval 数字 时间格式 )
3.11 datediff(end_date,start_date)函数
3.12 timestampdiff(unit,start_date,end_date)函数
4.3 case when n1 then x1 [when n2 then x2…else xn] end
6.2 CAST(expression AS data_type) 函数
1、数学函数
1.1 abs(x)函数
用于求绝对值，如：3 - 5的绝对值。
1.2 floor(x)函数
返回小于或者等于x的最大的整数，如：求小于或等于5.5 和 - 4.5的最大整数。
1.3 rand()函数
返回0 ~ 1之间的随机数。
与floor函数组合使用，例如：生成1~100之间的随机数
1.4 round()函数
round()函数可以传一个参数，如：round(3.145)，里面一个参数则是数值，它会自动四舍五入；
如果是round(3.1423,3), 两个参数的话，第一个是数值，第二个是小数点保留几位，然后进行四舍五入。
2、字符串函数
2.1 length(str)函数
获取参数值的字节个数
对于utf-8字符集来说，一个英文占1个字节；一个中文占3个字节；
对于gbk字符集来说，一个英文占1个字节；一个中文占2个字节；
演示如下：
2.2 concat(str1,str2,...)函数
将字符串拼接，通过输入的参数str1、str2等，将他们拼接成一个字符串。
2.3 upper(str)、lower(str)函数
upper(str):将字符中的所有字母变为大写；
lower(str)将字符中的所有字母变成小写。
2.4 substr(str,start,len)函数
参数第一个str为输入字符串，从start位置开始截取字符串，len表示要截取的长度； 没有指定len长度：表示从start开始起，截取到字符串末尾。指定了len长度：表示从start开始起，截取len个长度。
2.5 trim(str)函数
去掉字符串前后的空格； 该函数只能去掉字符串前后的空格，不能去掉字符串中间的空格。
2.6 replace( str, x , y)函数
将字符串str中的 x 替换成y
2.7 left(str，x)、right(str，x)
left(str，x)：返回字符串str最左端x个字符；
right(str，x)：返回字符串str最右端x个字符 。
2.8 instr(str,x)函数
返回x第一次出现的索引，如果找不到，返回0； 当查找的x存在于字符串中：返回该子串在字符串中第一次出现的索引。当查找的x不在字符串中返回0。
3、日期函数
3.1 DATE_FORMAT函数
日期的含义：指的是我们常说的年、月、日。
时间的含义：指的是我们常说的时、分、秒。
DATE_FORMAT("20000101", '%Y-%m-%d') -- 2020-01-01
DATE_FORMAT("2000-01-01", '%Y-%m-%d') -- 2020-01-01
DATE_FORMAT('2000-05-07 05:06:07', '%H:%i:%s') -- 05:06:07 (24小时制)
DATE_FORMAT('2000-05-07 05:06:07', '%h:%i:%s') -- 05:06:07 (12小时制)
DATE_FORMAT('2000-05-07 05:06:07', '%Y-%m-%d %H:%i:%s') -- 2000-05-07 05:06:07
3.2 now()函数
返回当前系统的日期和时间
3.3 curdate()函数
只返回系统当前的日期 （年月日）
3.4 curtime()函数
只返回当前的时间（时分秒），不包含日期
3.5 获取日期和时间中的年、月、日、时、分、秒
获取年份：year()
获取月份: month()
获取日: day()
获取小时: hour()
获取分钟: minute()
获取秒数: second()
3.6 weekofyear(当前时间)函数
获取当前时刻所属周数
3.7 quarter(当前日期)函数
获取当前时刻所属的季度
3.8 date_add(日期，Interval 数字 时间格式 )
向前偏移日期和时间，除此之外还有hour（小时），minute（分钟），second（秒），year（年）
3.9 date_use(日期，Interval 数字 时间格式 )
向后偏移日期和时间，除此之外还有hour（小时），minute（分钟），second（秒），year（年）
3.10 last_day(日期)函数
提取某个月最后一天的日期
3.11 datediff(end_date,start_date)函数
计算两个时间相差的天数
3.12 timestampdiff(unit,start_date,end_date)函数
计算两个时间返回的年/月/天数；
unit参数是确定(start_date,end_date)结果的单位，表示为整数，以下是有效单位：year：年份、month：月份、day：天、hour：小时、minute 分钟、second：秒、microsecond：微秒、week：周数、quarter：季度
3.13 获取年月日时分秒周
SELECT now(),dayofweek(now()),YEAR(now()),MONTH(now()),DAY(now()),HOUR(now()),MINUTE(now()),SECOND(now()),WEEK(now());
3.14 常用的日期sql查询
-- 今天
select * from sys_log_login where to_days(createTime) = to_days(now());
-- 查询最近一周的数据
select * from sys_log_login where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(createTime);
-- 查询上一周数据
SELECT * FROM sys_log_login WHERE YEARWEEK(date_format(now() ,'%Y-%m-%d'))= YEARWEEK(now()) -1
-- 近30天
SELECT *FROM sys_log_login where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date( createTime)
-- 查询最近一月的数据
select * from sys_log_login where DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <= date(createTime);
-- 查询本月数据
SELECT * FROM sys_log_login WHERE DATE_FORMAT(createTime, '%Y%m')=DATE_FORMAT(CURDATE() , '%Y%m')
-- 查询上一月数据
SELECT * FROM sys_log_login WHERE PERIOD_DIFF(date_format(now() ,'%Y%m'), date_format(createTime ,'%Y%m'))=1
-- 查询本年数据
select * from `sys_log_login` where YEAR(createTime)=YEAR(NOW());
-- 查询上一年数据
select * from `sys_log_login` where year(createTime)=year(date_sub(now(),interval 1 year));
-- 按日查询
SELECT DATE_FORMAT(createTime, '%Y-%m-%d') AS time,COUNT(*) FROM sys_log_login GROUP BY time
-- 按周查
SELECT DATE_FORMAT(createTime, '%Y-%u') AS time,COUNT(*) FROM sys_log_login GROUP BY time
-- 按月查
SELECT DATE_FORMAT(createTime,'%Y-%m') AS time,COUNT(*) FROM sys_log_login GROUP BY time
-- 按年查
SELECT DATE_FORMAT(createTime,'%Y') AS time,COUNT(*) FROM sys_log_login GROUP BY time
-- 按月分组查询
SELECT
SUM(case MONTH(createTime) WHEN 1 THEN 1 ELSE 0 END) AS 一月份,
SUM(case MONTH(createTime) WHEN 2 THEN 1 ELSE 0 END) AS 二月份,
SUM(case MONTH(createTime) WHEN 3 THEN 1 ELSE 0 END) AS 三月份,
SUM(case MONTH(createTime) WHEN 4 THEN 1 ELSE 0 END) AS 四月份,
SUM(case MONTH(createTime) WHEN 5 THEN 1 ELSE 0 END) AS 五月份,
SUM(case MONTH(createTime) WHEN 6 THEN 1 ELSE 0 END) AS 六月份,
SUM(case MONTH(createTime) WHEN 7 THEN 1 ELSE 0 END) AS 七月份,
SUM(case MONTH(createTime) WHEN 8 THEN 1 ELSE 0 END) AS 八月份,
SUM(case MONTH(createTime) WHEN 9 THEN 1 ELSE 0 END) AS 九月份,
SUM(case MONTH(createTime) WHEN 10 THEN 1 ELSE 0 END) AS 十月份,
SUM(case MONTH(createTime) WHEN 11 THEN 1 ELSE 0 END) AS 十一月份,
SUM(case MONTH(createTime) WHEN 12 THEN 1 ELSE 0 END) AS 十二月份
FROM sys_log_login
WHERE
YEAR(createTime) = YEAR(CURDATE())
4、流程操作函数
4.1 if(expr,v1,v2)函数
实现if-else的效果，如果expr是true，返回v1。如果expr是false，返回v2
4.2 ifnull (v1,v2)
如果v1不为空，则显示v1的值；否则显示v2的值。
4.3 case when n1 then x1 [when n2 then x2…else xn] end
case表示函数开始，end表示函数结束。如果表达式n1成立，则返回x1的值；如果表达式n2成立，则返回x2的值。以此类推，最后遇到else时，返回xn的值。
4.4 DISTINCT 函数
distinct是用于去重，但是只能放在查询字段的开头
5、聚合函数
5.1 SUM()函数
求所有行中单列结果的总和
5.2 AVG()函数
求平均值
5.3 MAX() 函数
求最大值
5.4 MIN() 函数
求最小值
5.5 COUNT() 函数
求总行数
5.6 any_value() 函数
MySQL5.7之后，sql_mode中ONLY_FULL_GROUP_BY模式默认设置为打开状态。
ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，简单的说来，在此模式下，target list中的值要么是来自于聚合函数（sum、avg、max等）的结果，要么是来自于group by list中的表达式的值。
网上有很多通过修改sql_mode的方式来解决此问题。
但除此方法，MySQL也提供了any_value()函数来抑制ONLY_FULL_GROUP_BY值被拒绝。
6、其他函数
6.1 JSON_EXTRACT() 函数
JSON_EXTRACT函数的作用是根据指定的路径，从JSON数据中提取相应的字段值。路径可以是简单的键名，也可以是多层次的嵌套键名，前提您在使用MySQL 5.7及以上版本 。
例如：我们有以下数据
{
"name": "John",
"age": 30,
"address": {
"city": "New York",
"country": "USA"
}
}
如果我们想要获取address.country的值，可以使用以下查询：
SELECT JSON_EXTRACT(person, '$.address.country');
得到值："USA"
6.2 CAST(expression AS data_type) 函数
expression 是被转换的表达式，type是目标数据类型。MySQL支持的数据类型如下：
- 整数类型：TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
- 浮点数类型：FLOAT, DOUBLE, DECIMAL
- 字符串类型：CHAR, VARCHAR
- 日期时间类型：DATE, TIME, DATETIME, TIMESTAMP
-- 将整数转换成字符串
SELECT CAST(123 AS CHAR);-- 将日期转换成字符串
SELECT CAST(NOW() AS CHAR);-- 将字符串转换成日期
SELECT CAST('2022-10-01' AS DATE);
6.3 CONVERT函数
CONVERT函数也用于将一个数据类型转换成另一个数据类型，其语法与CAST函数类似：
CONVERT(expr, type)
其中，expr是被转换的表达式，type是目标数据类型。需要注意的是：
CONVERT函数可以指定字符集和排序规则，这在处理多语言数据时非常有用。例如：
-- 将字符串转换成UTF8编码的字符串
SELECT CONVERT('中国', CHAR CHARACTER SET utf8);-- 将字符串转换成GBK编码的字符串
SELECT CONVERT('中国', CHAR CHARACTER SET gbk);-- 将字符串转换成二进制形式，并指定排序规则
SELECT CONVERT('中国', BINARY) COLLATE utf8_bin;
2695
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
