MYSQL--日期函数、字符串操作、自定义函数、聚合函数、功能性函数、数字函数、窗口函数-CSDN博客

本文链接：https://blog.csdn.net/weixin_43776126/article/details/132846664

一、MYSQL日期函数

1、在时分秒加操作
SELECT ADDTIME('2007-12-30 21:50:50', '1:1:1')
SELECT ADDTIME(NOW(),'1:1:1')

2、获取当前日期
SELECT CURRENT_DATE()

3、获取当前时分秒
SELECT CURRENT_TIME()

4、获取当前时间
SELECT CURRENT_TIMESTAMP()

SELECT NOW()

5、获取当前时间的日期部分
SELECT DATE(NOW())

6、当前时间减一天

SELECT DATE_ADD(now() ,INTERVAL -1 DAY)

7、计算两个日期时间差（前面的时间-后面的时间的差值）
SELECT DATEDIFF('2016-6-8','2016-6-10')

8、获取时间的年、月、日

SELECT YEAR|MONTH|DAY(DATETIME )-- 错误操作
SELECT YEAR(NOW())-- 单独获取年
SELECT MONTH(NOW())-- 单独获取月
SELECT DAY(NOW())-- 单独获取日

二、对字符串的操作

1、当前的编码格式
SELECT CHARSET('hello')

2、合并字符串
SELECT CONCAT('he','lo')

3、获取子字符串的下标开始位置（子字符串有多个字符，返回第一个字符的下标）
SELECT INSTR('hello','e') 当在原字符串中找不到子字符串时,会返回0

4、获取字符串左边N个字符
SELECT LEFT('hello',2)

5、获取字符串右边N个字符

SELECT RIGHT('hello',2)

6、替换字符串
SELECT REPLACE('hello','h','wo')

7、比较两个字符串前面大返回1 后面大返回-1 相等返回0
SELECT STRCMP('hello','heloo')

8、去除左边的空格
SELECT LTRIM(' hello')

9、去除右边的空格
SELECT RTRIM('hello ')

10、返回null
SELECT MOD(34,0)

三、自定义函数

1、先执行删除自定义函数从操作

drop FUNCTION if EXISTS CalculateTotalPrice

2、定义函数

delimiter // 定义函数结束的分隔符，区别于数据库符号的都可

CREATE FUNCTION CalculateTotalPrice(status INT, visit_count int)
RETURNS int
BEGIN
DECLARE total DECIMAL(10, 2);
SET total = status * visit_count;
RETURN total;
END;
//

3、调用函数
select CalculateTotalPrice(status,num) as total
from buliding_detail
where scene_type='xxx'

四、聚合函数

聚合函数，是作用在一列数据上的，对一列的数据进行运算的函数。包含有: max、min、sum、count、avg等常见的函数。

max(): 计算指定列数据的最大值
min(): 计算指定列数据的最小值
count(): 计算指定列不为NULL的数据的数量
sum(): 计算指定列的数值的和，如果计算的列的类型不是数值类型，计算结果为0
avg(): 计算指定列的数值的平均值，如果计算的列的类型不是数值类型，计算的结果为0

举例：

select max(sal) from te 求te表中sal的最大值，其他聚合函数如此

注意： count(*)、count(1)用来统计行记录，只要有这一行就会统计，即便这一行的所有的字段值都是NULL，依然算是一个有效的行

count(某一字段)统计字段值不为空的数量

五、功能性函数

1、IFNULL(field,value)

逻辑: 如果第一个参数field是NULL，则返回第二个参数value的值，否则就返回field本身的值。

举例：

select ifnull(null, 10);		-- 查询结果是10
select ifnull(10, 20);			-- 查询结果是10
select ifnull(`id`, 0);			-- 如果id字段的值是NULL，就返回0，否则就返回id字段的值

2、IF(condition,value1,value2)

逻辑: 如果第一个参数condition条件成立，就返回value1的值，否则就返回value2的值。

举例：

select if(10 > 20, 10, 20);		-- 因为10 > 20不成立，因此返回20
select if(10 > 5, 10, 5);		-- 因为10 > 5成立，因此返回10
select if(`chinese` > `math`, `chinese`, `math`);	-- 如果chinese字段的值大于math字段的值，就返回chinese字段的值，否则就返回math字段的值

六、数学函数

函数	描述
ABS (number2 )	绝对值
BIN (decimal_number )	十进制转二进制
CEILING (number2 )	向上取整
CONV(number2,from_base,to_base)	进制转换
FLOOR (number2 )	向下取整
FORMAT (number,decimal_places )	保留小数位数
HEX (DecimalNumber )	转十六进制
LEAST (number , number2 [...])	求最小值
MOD (numerator ,denominator )	求余
RAND([seed])	RAND([seed])

七、窗口函数

窗口函数又名开窗函数，属于分析函数的一种。是一种用于解决复杂报表统计需求的函数；
常用于计算基于组的某种值，与聚合函数的不同之处在于：对于每个组返回多行，而聚合函数对于每个组只返回一行。简单来说，窗口函数对每条详细记录开一个窗口，进行聚合统计的查询；
窗口函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能随着行的变化而变化；
窗口函数一般不单独使用；
窗口函数内也可以分组和排序。

使用over()函数，为查询到的每一行数据开一个数据窗口。窗口内的数据为本次查询到的所有的数据。一般是对窗口内的数据进行聚合统计的操作。

统计某字段值的和