Mysql函数汇总列表

2023-06-01 00:00:00 函数列表汇总

聚合函数

AVG(表达式) 返回表达式中所有的平均值。仅用于数字列并自动忽略NULL值。

COUNT(表达式) 返回表达式中非NULL值的数量。可用于数字和字符列。

COUNT(*) 返回表中的行数(包括有NULL值的列)。

MAX(表达式) 返回表达式中的最大值,忽略NULL值。可用于数字、字符和日期时间列。

MIN(表达式) 返回表达式中的最小值,忽略NULL值。可用于数字、字符和日期时间列。

SUM(表达式) 返回表达式中所有的总和,忽略NULL值。仅用于数字列。

转换函数

将一种数据类型转换为另外一种（有CONVERT和CAST两种）

CONVERT convert(value , type)

例:

Select convert(stuno,varchar(10) ) as stuno,stuname from student

CAST cast(value as type)

例:

Select cast(stuno as varchar(10)) as stuno,stuname from student

CAST 和 CONVERT 提供相似的功能，只是语法不同。在时间转化中一般用到convert,因为它比cast多加了一个style,可以转化成不同时间的格式。

条件函数

IF： IF(条件表达式,true返回的值,false返回的值) ，通过条件返回对应的值

CASE：

case 
when 条件表达式 then 值
when 条件表达式 then 值
when 条件表达式 then 值
else 值
end

多个条件，多个返回值可使用该语法判断

日期函数

日期（date）相关

now()：返回当前时间，精确到秒

CURDATE()：返回当前时间，精确到日

CURDATE_DTAE()：返回当前时间，精确到日

CURDATE_DTAE：返回当前时间，精确到日

时间(time)相关

CURTIME()：返回当前时分秒

CURDATE_TIME()：返回当前时分秒

CURDATE_TIME：返回当前时分秒

日期和时间

NOW() 返回当前时间，精确到秒

CURRENT_TIMESTAMP()：返回当前时间，精确到秒

CURRENT_TIMESTAMP：返回当前时间，精确到秒

LOCALTIME()：返回当前时间，精确到秒

LOCALTIME：返回当前时间，精确到秒

LOCALTIMESTAMP()：返回当前时间，精确到秒

LOCALTIMESTAMP：返回当前时间，精确到秒

SYSDATE(int a)：获取当前时间，精确到秒，可以设置秒之后的精度:0~6

NOW()返回执行当前SQL语句的时间；SYSDATE()返回执行当前函数的时间。

日期与时间的比较

无论是date类型的时间，还是字符串类型的时间，我们都可以使用>、=、<来进行字符串的判断。字符串类的日期要符合规范。

日期与时间的运算

时间段的表示方式：interval 数字时间段单位

例如： interval 1 day

时间段单位有：MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR

DATE_ADD(date,INTERVAL expr unit)：为指定时间增加一个时间段

select date_add(now(), interval 1 day) -- 返回现在时间再加上一天

DATE_SUB(date,INTERVAL expr unit)：为指定时间减少一个时间段

select date_sub(now(), interval 1 year) -- 返回现在时间再减去一年

DATEDIFF(date2,date1)：获得两个日期的时间间隔,单位为天数

select DATEDIFF(date1,date2) -- 获得两个日期的时间间隔,单位为天数

TIMEDIFF(date2,date1)：获得两个日期的时间间隔,返回时分秒

select TIMEDIFF(date1,date2) -- 获得两个日期的时间间隔,返回时分秒

日期和时间的格式化

DATE_FORMAT(date,format) 改变日期的格式

用format 格式化date，format 为格式化字符串

常用的部分格式化标识符为：

%Y：年，4位；
%y：年，2位；
%M：月，月名称，如January；
%m：月，2位（01-12）；
%D：日，如1st，2nd；
%d：日，2位（01-31）；
%H：时，2位，24小时制；
%h：时，12小时制；
%i：分，2位（00-59）；
%S或%s：秒，2位（00-59）；
%p：PM 或AM；
%r：HH:mm:ss PM形式时间；
%W：周，周名，如Tuesday；
%w：周，0-6，0为周日；
%j：当前日期所在一年中的天数，3位（001-366）。

SELECT DATE_FORMAT(SYSDATE(),"%Y-%m-%d %H:%i:%s");-- 2022-11-10 18:29:05
SELECT DATE_FORMAT(SYSDATE(),"%Y-%m-%d"); -- 14:42:31

字符串长度

CHAR_LENGTH(str)函数计算字符串str所包含的字符个数。一个多字节字符算作一个单字符。

LENGTH(str)返回值为字符串的字节长度，使用utf8编码字符集时，一个汉字是三字节，一个数字或者字母算1字节；

concat(s1,s2,...)返回值为字符串的字节长度，使用utf8编码字符集时，一个汉字是三字节，一个数字或者字母算1字节；

insert(s1,x,len,s2)返回字符串s1，其子字符串起始于x位置和被字符串s2取代的len字符。如果x超过字符串长度，则返回值为原始字符串。假如len的长度大于其他字符串的长度，则从位置x开始替换。若任何一个参数为null，则返回值为null。

lower(str)或者lcase(str)可以将字符串str中的字母字符全部转换成小写字母。

upper(str)或者ucase(str)可以将字符串str中的字母字符全部转换成大写字母。

ltrim(s)返回字符串s，字符串左侧空格字符被删除；

SUBSTRING_INDEX(str,str,int)截取字符串

substring(s,n,len)带有len参数的格式，从字符串s返回一个长度与len字符相同的子字符串，起始于位置n。也可能对n使用一个负值。假如这样，则子字符串的位置起始于字符串结尾的n字符，即倒数第n个字符，而不是字符串的开头位置。

窗口函数(mysql8.0开始)

窗口函数基本概念

MySQL从8.0开始支持窗口函数，这个功能在大多数据库中早已支持，有的也叫分析函数。窗口的概念非常重要，它可以理解为记录集合，窗口函数也就是在满足某种条件的记录集合上执行的特殊函数对于每条记录都要在此窗口内执行函数。简单的说窗口函数就是对于查询的每一行，都使用与该行相关的行进行计算。

窗口函数也叫OLAP函数(Online Analytical Processing,联机分析处理)，可以对数据进行实时分析处理。窗口函数和group by有类似之处，其区别在于窗口会对每个分组之后的数据进行分别操作，而group by一般对分组之后的函数使用聚合函数汇总。

窗口函数和普通聚合函数的区别

聚合函数是将多条记录聚合为一条；窗口函数是每条记录都会执行，有几条记录执行完还是几条。

聚合函数也可以用于窗口函数。

常见的窗口函数

名称参数描述
ROW_NUMBER()否当前行在其分组内的序号.不管其排序结果中是否出现重复值.其排序结果都为:1,2.3.4.5.
DENSE_RANK()否不间断的组内排序.使用这个函数时,可以出现1.1.2.2这种形式的分组。
RAN()否间断的组内排序。其排序结果可能出现如下结果:1.1.3.4.4.6
PERCENT_RANK()否累计百分比。该函数的计算结果为:小于该条记录值的所有记录的行数/该分组的总行数-1.所以该记录的返回值为[0,1]。
CUME_DIST()否累计分布值。即分组值小于等于当前值的行数与分组总行数的比值。取值范围为(0.1]。
LAG()lag(expr,[N,[detaut]])从当前行开始往前取第N行，如果N缺失，默认为1。如果不存在前一行.则默认返回default。default默认值为NULL。
LEAD()lead(expr,[N,[defaut]])从当前行开始往后取第N行。函数功能与lag()相反.其余与lag()相同。
FIRST_VALUE()frst_value(expr)返回分组内截止当前行的第一个值。
LAST_VALUE()last_value(expr)返回分组内截止当前行的最后一个值。
NTH_VALUE()nth_value(expr.N)返回分组内截止当前行的第N行。与first_value\last_value\nth_ value函数功能相似，只是返回分组内截止当前行的不同行号的数据。
NTILE()ntile(N)返回当前行在分组内的分桶号。在计算时要先将该分组内的所有数据划分成N个桶。之后返回每个记录所在的分桶号。返回范围从1到N。
窗口函数的使用

window_function(expr)
OVER( partition by ……
order by ……
frame_clause ……
)

实例

SELECT device_id,university,gpa FROM
(SELECT device_id,university,gpa,
RANK() over (PARTITION BY university ORDER BY gpa) rk FROM user_profile) a
WHERE a.rk=1;

相关文章