MySQL中常用的聚合函数

前言：SQL函数简介

SQL函数有很多种，像数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MySQL信息函数、聚合函数等，但总体可以分为两大类：单行函数和聚合函数。其相关特点如下图

一.五大常见的聚合函数（经常使用的）

• “平均值”AVG() 与“求和”SUM()函数的使用（注：只适用于数值类型的字段或变量，且不会统计指定字段值为NULL的数据）

AVG()与SUM()函数是单行函数，即把多条数据计算后得到一条数据

# 表中所用用户年龄的平均值与工资的总和
SELECT AVG(age),SUM(salary) FROM user_demo

•  “最大值”MAX() 与  “最小值”MIN()的使用（注：适用于数值类型、字符串类型、时间日期类型的字段或变量）

# 查表中年龄最小的和年龄最大的值,最大的姓名，最小的时间
SELECT MIN(age),MAX(age),MAX(user_name),MIN(cteat_time) FROM user_demo

在比较字符取最大或最小是根据默认的规则进行比较的，具体就是数据库设置的比较规则，时间取大小就是时间的早晚，越早的时间越小，越晚的时间越大

• “获取个数函数”COUNT() ：即 获取表中指定字段出现的个数函数 

# 获取表中数据个数
SELECT COUNT(*) FROM user_demo

注意：获取表中数据可以有以下几种方式

1. COUNT(*)
2. COUNT(1)
3. COUNT(表中具体的一个字段名)   ：此方式不会统计指定字段值为NULL的数据个数

恒成立表达式：AVG(指定字段1) = SUM(指定字段1) / COUNT(指定字段1)

 

二、字符操作的函数

注意：MySQL中，字符串的位置是从1开始的。

具体如下表格

函数	函数作用（描述）	简单使用示例
ASCII(Str)	返回字符串Str中的第一个字符的ASCII码值	SELECT ASCII('ABDD') ；会返回A对应的ASII值
CHAR_LENGTH(Str)	返回字符串Str的字符个数。CHARACTER_LENGTH(Str)与其作用相同	SELECT CHAR_LENGTH('a热爱学习'); 会得到输入的字符长度：5
CONCAT(s1,s2,......,sn)	拼接s1,s2,......,sn成为一个字符	SELECT CONCAT('你好','世界')； 会显示“你好世界”
CONCAT_WS(X,s1,s2,......,sn)	同CONCAT(s1,s2,...)函数作用一致，但会在每个字符串之间会加上X	SELECT CONCAT_WS('+','1','2','3')；  会显示“1+2+3”
INSERT(str, idx, len,replacestr)	将字符串str从第idx位置开始，将len个字符长的子串替换为字符串replacestr	SELECT INSERT('ABCDE',2,3,'O');  会显示 “AOE”
LENGTH(s)	返回字符串s的字节数，和字符集有关	SELECT LENGTH('热爱学习')； 使用utf8字符集会得到12字节
LEFT(str,n)	从左边开始返回字符串str左边的n个字符	SELECT LEFT('你是me',3);  会得到“你是m”
RIGHT(str,n)	从右边开始返回字符串str右边的n个字符	SELECT RIGHT('你是me',3);  会得到“是me”
REPLACE(str, a, b)	用字符串b替换字符串str中所有出现的字符串a	SELECT REPLACE('你是好好好学生','好好好','好');  会得到“你是好学生”
UPPER(s) 或 UCASE(s)	将字符串s的所有字母转成大写字母	SELECT UPPER('你是me')； 会得到“你是ME”
LOWER(s) 或LCASE(s)	将字符串s的所有字母转成小写字母	SELECT LOWER('你是Me')； 会得到“你是me”
LPAD(str, len, pad)	用字符串pad对str最左边进行填充，直到str的长度为len个字符	SELECT LPAD('拜拜',5,'L')  会得到“LLL拜拜”
RPAD(str ,len, pad)	用字符串pad对str最右边进行填充，直到str的长度为len个字符	SELECT RPAD('拜拜',5,'L')  会得到“拜拜LLL”
LTRIM(s)	去掉字符串s左侧的空格	SELECT LTRIM('    你好    ');  会得到“你好    ”
RTRIM(s)	去掉字符串s右侧的空格	SELECT RTRIM('    你好    ');  会得到“    你好”
TRIM(s)	去掉字符串s开始与结尾的空格	SELECT TRIM('    你 好    '); 会得到“你 好”
TRIM(s1 FROM str)	去掉字符串str中开始与结尾的s1	SELECT TRIM('w' FROM 'qw你好w') ; 得到“qw你好”,区分大小写
TRIM(LEADING s1 FROM str)	去掉字符串str中开始处的s1	同上
TRIM(TRAILING s1 FROM str)	去掉字符串s中结尾处的s1	同上
REPEAT(str, n)	返回str重复n次的结果	SELECT REPEAT('你是', 3) ； 会得到“你是你是你是”
SPACE(n)	返回n个空格	SELECT SPACE(2)； 会的到“  ”，即两个空格
STRCMP(s1,s2)	比较字符串s1,s2的ASCII码值的大小	SELECT STRCMP('RT','FD');  会得到“1”
SUBSTR(s,index,len)	返回从字符串s的index位置往后的len个字符，作用与SUBSTRING(s,n,len)、MID(s,n,len)相同	SELECT SUBSTR('世界很美好',2,3)；  得到“界很美” SELECT SUBSTR('世界很美好',2)； 得到“界很美好”
ELT(m,s1,s2,…,sn)	返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如 果m=n，则返回sn	SELECT ELT(2,'大大','小小','多多','少少')； 得到“小小”
REVERSE(s)	返回s反转后的字符串	SELECT REVERSE('热爱学习')；  得到“习学爱热”
NULLIF(value1,value2)	比较两个字符串，如果value1与value2相等，则返回NULL，否则返回 value1	SELECT NULLIF('大大','小小')； 得到“大大”

 

三、数值函数

1.基本函数

函数	函数的作用（功能描述）	简单使用示例
ABS(num)	返回num的绝对值	SELECT ABS(-25);  得到“25”
SIGN(num)	返回num的符号。正数返回1，负数返回-1，0返回0	SELECT SIGN(-25);  得到“-1”
PI()	返回圆周率的值	SELECT PI()； 得到“3.141593”
CEIL(num) 与 CEILING(num)	返回大于或等于num的最小整数	SELECT CEIL(-3.21);  得到“-3” SELECT CEIL(3.21);  得到“4”
FLOOR(num)	返回小于或等于num的最大整数	SELECT FLOOR(-3.21);  得到“-4” SELECT FLOOR(3.21);  得到“3”
LEAST(e1,e2,e3…)	返回列表中的最小值	SELECT LEAST(1,8,2,6) ;   得到“1”
GREATEST(e1,e2,e3…)	返回列表中的最大值	SELECT GREATEST(1,8,2,6) ;   得到“8”
MOD(x,y)	返回X除以Y后的余数	SELECT MOD(5,3)； 得到“2”
RAND()	返回0~1的随机值	SELECT RAND()； 得到一个0~1范围的随机数（每次执行得到的结果都不一样）
RAND(x)	返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机数	SELECT RAND(6000);  得到一个固定的值，只要输入的值不变得到的结果就不变
ROUND(x)	返回一个对x的值进行四舍五入后，最接近于X的整数	SELECT ROUND(-1.45)； 得到“-1”
ROUND(x,y)	返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位	SELECT ROUND(-1.45,1)； 得到“-1.5”
TRUNCATE(x,y)	返回数字x截断为y位小数的结果	SELECT TRUNCATE(20,1)； 得到“20” SELECT TRUNCATE(20.123,1)； 得到“20.1”
SQRT(x)	返回x的平方根。当X的值为负数时，返回NULL	SELECT SQRT(4)； 得到“2”
RADIANS(x)	将角度转化为弧度，其中，参数x为角度值	SELECT RADIANS(30)；得到“0.5235987755982988”
DEGREES(x)	将弧度转化为角度，其中，参数x为弧度值	SELECT DEGREES(0.5)； 得到“28.64788975654116”

 

2.三角函数

函数	函数的作用（功能描述）	简单使用示例
SIN(x)	返回x的正弦值，其中，参数x为弧度值
ASIN(x)	返回x的反正弦值，即获取正弦为x的值。如果x的值不在-1到1之间，则返回NULL
COS(x)	返回x的余弦值，其中，参数x为弧度值
ACOS(x)	返回x的反余弦值，即获取余弦为x的值。如果x的值不在-1到1之间，则返回NULL
TAN(x)	返回x的正切值，其中，参数x为弧度值
ATAN(x)	返回x的反正切值，即返回正切值为x的值
ATAN2(m,n)	返回两个参数的反正切值
COT(x)	返回x的余切值，其中，X为弧度值

 

3.进制间的转换

函数	函数的作用（功能描述）	简单使用示例
BIN(x)	返回x的二进制编码
HEX(x)	返回x的十六进制编码
OCT(x)	返回x的八进制编码
CONV(x,f1,f2)	返回f1进制数变成f2进制数，x代表f1进制编码，得到的是对应的f2数

 

4.其他函数

函数	函数的作用（功能描述）	简单使用示例
POW(x,y) 或 POWER(X,Y)	返回x的y次方	SELECT POW(2,3)； 得到“8”
EXP(X)	返回e的X次方，其中e是一个常数，2.718281828459045	SELECT EXP(2)； 得到“7.38905609893065”
LN(X) 或 LOG(X)	返回以e为底的X的对数，当X <= 0 时，返回的结果为NULL
LOG10(X)	返回以10为底的X的对数，当X <= 0 时，返回的结果为NULL
LOG2(X)	返回以2为底的X的对数，当X <= 0 时，返回NULL

 

四、日期时间函数

1.获取时间、日期的函数

函数	函数的作用（功能描述）	简单使用示例
CURDATE() CURRENT_DATE()	返回当前日期，只包含年、月、日	SELECT CURDATE()； 得到“2022-04-06”
CURTIME()  CURRENT_TIME()	返回当前时间，只包含时、分、秒	SELECT CURTIME()； 得到“23:46:54”
NOW() / SYSDATE()  CURRENT_TIMESTAMP()  LOCALTIME()  LOCALTIMESTAMP()	返回当前系统日期和时间（包含年月日时分秒）	SELECT NOW()；   得到“2022-04-06 23:47:46”
UTC_DATE()	返回UTC（世界标准时间）日期	SELECT UTC_DATE()； 得到“2022-04-06”
UTC_TIME()	返回UTC（世界标准时间）时间	SELECT UTC_TIME()；  得到“15:49:19”

 

2.日期与时间戳转换函数

函数	函数的作用（功能描述）	简单使用示例
UNIX_TIMESTAMP()	以UNIX时间戳的形式返回当前时间	SELECT UNIX_TIMESTAMP()； 得到“1649260405”
UNIX_TIMESTAMP(date)	将时间date以UNIX时间戳的形式返回	SELECT UNIX_TIMESTAMP('2022-04-06 12:23:54') 得到“1649219034”
FROM_UNIXTIME(timestamp)	将UNIX时间戳的时间转换为普通格式的时间	SELECT FROM_UNIXTIME('1649219034')； 得到“2022-04-06 12:23:54.000000” SELECT FROM_UNIXTIME(1649219034) 得到“2022-04-06 12:23:54”

 

3.获取月份、星期、星期数、天数等函数 

函数	函数的作用（功能描述）	简单使用示例
YEAR(date) / MONTH(date) / DAY(date)	返回具体的日期值，分别是年/月/日	SELECT YEAR(NOW()) ；得到“2022” SELECT YEAR('2022-04-06 12:15:36')；得到“2022”
HOUR(time) / MINUTE(time) / SECOND(time)	返回具体的时间值 ，分别是时/分/秒	同上
MONTHNAME(date)	返回月份：January，...	SELECT MONTHNAME('2022-04-16 22:01:30')  得到：“April”
DAYNAME(date)	返回星期几：MONDAY，TUESDAY.....SUNDAY	SELECT DAYNAME('2022-04-16 22:01:30')  得到“Saturday”
WEEKDAY(date)	返回周几，注意，周1是0，周2是1，。。。周日是6	SELECT WEEKDAY('2022-04-16 22:01:30')   得到：“5”
QUARTER(date)	返回日期对应的季度，范围为1～4，	SELECT QUARTER('2022-04-16 22:01:30')  得到“2”季度
WEEK(date) ， WEEKOFYEAR(date)	返回一年中的第几周	SELECT WEEK('2022-04-16 22:01:30')  得到“15”周
DAYOFYEAR(date)	返回日期是一年中的第几天	SELECT DAYOFYEAR('2022-04-16 22:01:30')  得到“106” 天
DAYOFMONTH(date)	返回日期位于所在月份的第几天	SELECT DAYOFMONTH('2022-04-16 22:01:30')  得到“16”天
DAYOFWEEK(date)	返回周几，注意：周日是1，周一是2，。。。周六是7	SELECT DAYOFWEEK('2022-04-16 22:01:30')  得到“7”，代表周六
EXTRACT(type FROM date)	返回指定日期中特定的部分，type指定返回的值	SELECT EXTRACT(second from '2022-04-16 22:01:30')  得到“30”秒
EXTRACT(type FROM date)函数中type的取值与含义：

 

4.时间和秒钟转换的函数

函数	函数的作用（功能描述）	简单使用示例
TIME_TO_SEC(time)	将 time 转化为秒并返回结果值。只对时分秒有效， 转化的公式为： 小时*3600+分钟*60+秒	SELECT TIME_TO_SEC('2022-04-06 12:15:36') SELECT TIME_TO_SEC('12:15:36') ; 得到结果一样“44136”
SEC_TO_TIME(seconds)	将 seconds 描述转化为包含小时、分钟和秒的时间	SELECT SEC_TO_TIME('44136') ； 得到“12:15:36.000000” SELECT SEC_TO_TIME(44136) ； 得到“12:15:36”

 

5.计算时间和日期的函数

函数	函数的作用（功能描述）	简单使用示例
DATE_ADD(datetime, INTERVAL expr type) ADDDATE(date,INTERVAL expr type)	返回与给定日期时间（datetime）相差INTERVAL时间段的日期时间 可以为负数，（本质上是加操作）	SELECT DATE_ADD('2022-04-16 22:01:30',INTERVAL -5 SECOND)  得到“2022-04-16 22:01:25” SELECT DATE_ADD('2022-04-16 22:01:30',INTERVAL 5 SECOND)  得到“2022-04-16 22:01:35”
DATE_SUB(date,INTERVAL expr type) SUBDATE(date,INTERVAL expr type)	返回与date相差INTERVAL时间间隔的日期，当是负数时是加操作 （本质上是减操作）	SELECT DATE_SUB('2022-04-16 22:01:30',INTERVAL -5 SECOND)  得到“2022-04-16 22:01:35” SELECT DATE_SUB('2022-04-16 22:01:30',INTERVAL 5 SECOND)  得到“2022-04-16 22:01:25”
上述函数中type的取值：
ADDTIME(time1,time2)	返回time1加上time2的时间。当time2为一个数字时，代表的是 秒 ，可以为负数 ，time2不可以带年月日，只支持时分秒	SELECT ADDTIME('2022-04-16 22:01:30','01:02:30')  得到“2022-04-16 23:04:00” SELECT ADDTIME('2022-04-16 22:01:30',-30)  得到“2022-04-16 22:01:00”
SUBTIME(time1,time2)	返回time1减去time2后的时间。当time2为一个数字时，代表的是 秒 ，可以为负数	同上，结果相反
DATEDIFF(date1,date2)	返回date1 - date2的日期间隔天数 ，跟时分秒无关	SELECT DATEDIFF('2022-04-16 22:01:30','2022-04-18 22:01:30')  得到“-2” SELECT DATEDIFF('2022-04-16 22:01:30','2022-04-13 22:01:30')  得到“3”
TIMEDIFF(time1, time2)	返回time1 - time2的时间间隔 ，	SELECT TIMEDIFF('2022-04-16 22:01:30','2022-04-13 22:01:30')  得到“74:00:30”
FROM_DAYS(N)	返回从0000年1月1日起，N天以后的日期
TO_DAYS(date)	返回日期date距离0000年1月1日的天数
LAST_DAY(date)	返回date所在月份的最后一天的日期
MAKEDATE(year,n)	针对给定年份与所在年份中的天数返回一个日期
MAKETIME(hour,minute,second)	将给定的小时、分钟和秒组合成时间并返回
PERIOD_ADD(time,n)	返回time加上n后的时间

 

6.日期的格式化与解析

函数	函数的作用（功能描述）	简单使用示例
DATE_FORMAT(date,fmt)	按照字符串fmt格式化日期date值
TIME_FORMAT(time,fmt)	按照字符串fmt格式化时间time值
GET_FORMAT(date_type,format_type)	返回日期字符串的显示格式
STR_TO_DATE(str, fmt)	按照字符串fmt对str进行解析，解析为一个日期

注意：上述 非GET_FORMAT 函数中fmt参数常用的格式符： 

格式符	说明	格式符	说明	格式符	说明
%Y	4位数字表示年份,如 “2022”年	%M	月名表示月份（January,....）	%D	英文后缀表示月中的天数（1st,2nd,3rd,...）
%y	表示两位数字表示年份 如：“22”年	%m	两位数字表示月份（01,02,03。。。）	%d	两位数字表示月中的天数(01,02...)
%b	缩写的月名（Jan.，Feb.，....）	%c	数字表示月份（1,2,3,...）	%e	数字形式表示月中的天数（1,2,3,4,5.....）
%H	两位数字表示小数，24小时制（01,02..）	%k	数字形式的小时，24小时制(1,2,3)	%l	数字形式表示小时，12小时制（1,2,3,4....）
%h和%I	两位数字表示小时，12小时制（01,02..）	%i	两位数字表示分钟（00,01,02）	%S和%s	两位数字表示秒(00,01,02...)
%W	一周中的星期名称（Sunday...）	%a	一周中的星期缩写（Sun.，Mon.,Tues.，..）	%j	以3位数字表示年中的天数(001,002...)
%w	以数字表示周中的天数(0=Sunday,1=Monday....)	%U	以数字表示年中的第几周，（1,2,3。。）其中Sunday为周中第一天	%T	24小时制
%r	12小时制	%p	AM或PM	%%	表示%

 

五、流程控制函数

函数	描述	使用方式
IF(value,value1,value2)	如果value的值为true，返回value1，否则返回value2
IFNULL(value1, value2)	如果value1不为NULL，返回value1，否则返回value2
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 .... [ELSE resultn] END	相当于Java的if...else if...else...
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 .... [ELSE 值n] END	相当于Java的switch...case...

 

六、 加密与解密函数

函数	说明	使用
PASSWORD(str)	返回字符串str的加密版本，41位长的字符串。加密结果 不可逆 ，常用于用户的密码加密
MD5(str)	返回字符串str的md5加密后的值，也是一种加密方式。若参数为NULL，则会返回NULL
SHA(str)	从原明文密码str计算并返回加密后的密码字符串，当参数为 NULL时，返回NULL。 SHA加密算法比MD5更加安全 。
ENCODE(value,password_seed)	返回使用password_seed作为加密密码加密value	SELECT ENCODE('mysql123', 'password');  得到“�>?�͉�4”
DECODE(value,password_seed)	返回使用password_seed作为加密密码解密value	DECODE(ENCODE('mysql123', 'password'),'password');   得到：“mysql123”
可以看到，ENCODE(value,password_seed)函数与DECODE(value,password_seed)函数互为反函数。

 

七、MySQL信息函数 

函数	说明	使用
VERSION()	返回当前MySQL的版本号	SELECT VERSION() ；得到版本号：5.7.25-log
CONNECTION_ID()	返回当前MySQL服务器的连接数	SELECT CONNECTION_ID()
DATABASE()，SCHEMA()	返回MySQL命令行当前所在的数据库	SELECT DATABASE()
USER()，CURRENT_USER()、SYSTEM_USER()， SESSION_USER()	返回当前连接MySQL的用户名，返回结果格式为 “主机名@用户名”	SELECT USER()
CHARSET(value)	返回字符串value自变量的字符集	SELECT CHARSET('ABC');  得到“utf8mb4”
COLLATION(value)	返回字符串value的比较规则	SELECT COLLATION('ABC');  得到“utf8mb4_general_ci”
其他函数
FORMAT(value,n)	返回对数字value进行格式化后的结果数据。n表示 四舍五入 后保留到小数点后n位（ 如果n的值小于或者等于0，则只保留整数部分）
CONV(value,from,to)	将value的值进行不同进制之间的转换
INET_ATON(ipvalue)	将以点分隔的IP地址转化为一个数字， 转换方式：以“192.168.1.100”为例，计算方式为192乘以256的3次方，加上168乘以256的2次方，加上1乘以256，再加上100。
INET_NTOA(value)	将数字形式的IP地址转化为以点分隔的IP地址
BENCHMARK(n,expr)	将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费的时间
CONVERT(value USING char_code)	将value所使用的字符编码修改为char_code