MySQL内置函数（常用函数+聚合函数）
2022/04/05
MySQL
> 视频教程：[尚硅谷_MySQL](http://www.atguigu.com/download_detail.shtml?v=349) 官方文档：[Chapter 12 Functions and Operators](https://dev.mysql.com/doc/refman/8.0/en/functions.html) # 一般函数 ## 数值函数 ### 基本函数 函数 | 用法 | 示例 | 返回值 --- | --- | --- | --- ABS(x) | 返回x的绝对值 | ABS(-123) | 123 SIGN(x) | 返回x的符号。正数返回1、负数返回-1、0返回0 | SIGN(-23) | -1 PI() | 返回圆周率的值 | PI() | 3.141593 CEIL(x) , CEILING(x) | 返回大于或等于某个值的最小整数 | CEIL(32.32) | 33 FLOOR(x) | 返回小于或等于某个值的最大整数 | FLOOR(32.32) | 32 LEAST(e1,e2,e3…) | 返回列表中的最小值 | LEAST(3,1,5) | 1 GREATEST(e1,e2,e3…) | 返回列表中的最大值 | GREATEST(3,1,5) | 5 MOD(x,y) | 返回X除以Y后的余数，同 `x MOD y` 或 `x % y` | MOD(12,5) | 2 RAND() | 返回0~1的随机值 | RAND() | 0.40111328046872585 RAND(x) | 返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机数 | RAND(10) | 0.6570515219653505 ROUND(x) | 返回一个对x的值进行四舍五入后，最接近于X的整数 | ROUND(123.556) | 124 ROUND(x,y) | 返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位 | ROUND(123.456,1) | 123.5 TRUNCATE(x,y) | 返回数字x截断为y位小数的结果 | TRUNCATE(123.496,1) | 123.5 SQRT(x) | 返回x的平方根。当X的值为负数时，返回NULL | SQRT(9) | 3 ### 角度与弧度函数 函数 | 用法 | 示例 | 返回值 --- | --- | --- | --- RADIANS(x) | 将角度转化为弧度，其中，参数 x 为角度值 | RADIANS(180) | 3.141592653589793 DEGREES(x) | 将弧度转化为角度，其中，参数 x 为弧度值 | DEGREES(PI()) | 180 ### 三角函数 函数 | 用法 | 示例 | 返回值 --- | --- | --- | --- SIN(x) | 返回x的正弦值，其中，参数x为弧度值 | SELECT SIN(RADIANS(90)) | 1 ASIN(x) | 返回x的反正弦值，即获取正弦为x的值。如果x的值不在-1到1之间，则返回NULL | SELECT DEGREES(ASIN(1)) | 90 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为弧度值 ### 指数与对数函数 函数 | 用法 | 示例 | 返回值 --- | --- | --- | --- POW(x,y)，POWER(X,Y) | 返回x的y次方 | POW(2,2) | 4 EXP(X) | 返回e的X次方，其中e是一个常数，2.718281828459045 | SELECT EXP(1) | 2.718281828459045 LN(X)，LOG(X) | 返回以e为底的X的对数，当X <= 0 时，返回的结果为NULL | SELECT LN(EXP(1)) | 1 LOG10(X) | 返回以10为底的X的对数，当X <= 0 时，返回的结果为NULL | SELECT LOG10(100) | 2 LOG2(X) | 返回以2为底的X的对数，当X <= 0 时，返回NULL | LOG2(4) | 2 ### 进制转换函数 函数 | 用法 | 示例 | 返回值 --- | --- | --- | --- BIN(x) | 返回x的二进制编码 | BIN(10) | 1010 HEX(x) | 返回x的十六进制编码 | HEX(10)| A OCT(x) | 返回x的八进制编码 | OCT(10) | 12 CONV(x,f1,f2) | 返回f1进制数变成f2进制数 | CONV(10,10,8) | 12 ## 字符串函数 函数 | 用法 | 示例 | 返回值 --- | --- | --- | --- ASCII(S) | 返回字符串S中的**第一个**字符的ASCII码值 | ASCII('Abcdfsf') | 65 CHAR_LENGTH(s) | 返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同 | CHAR_LENGTH('hello') | 5 LENGTH(s) | 返回字符串s的字节数，和字符集有关 | LENGTH('hello') | 5 CONCAT(s1,s2,......,sn) | 连接s1,s2,......,sn为一个字符串 | CONCAT('hello','world') | helloworld CONCAT_WS(x,s1,s2,......,sn) | 同CONCAT(s1,s2,...)函数，但是每个字符串之间要加上x | CONCAT_WS('-','hello','world') | hello-world INSERT(str, idx, len,replacestr) | 将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr | INSERT('helloworld',2,3,'aaaaa') | haaaaaoworld REPLACE(str, a, b) | 用字符串b替换字符串str中所有出现的字符串a | REPLACE('hello','ll','mmm') | hemmmo UPPER(s) , UCASE(s) | 将字符串s的所有字母转成大写字母 | UPPER('HelLo') | HELLO LOWER(s) , LCASE(s) | 将字符串s的所有字母转成小写字母 | UPPER('HelLo') | hello LEFT(str,n) | 返回字符串str最左边的n个字符 | LEFT('hello',2) | he RIGHT(str,n) | 返回字符串str最右边的n个字符 | RIGHT('hello',3) | llo LPAD(str, len, pad) | 用字符串pad对str最左边进行填充，直到str的长度为len个字符 | SELECT LPAD('123',5,'0') | 00123 RPAD(str ,len, pad) | 用字符串pad对str最右边进行填充，直到str的长度为len个字符 | SELECT RPAD('123',5,'0') | 12300 LTRIM(s) | 去掉字符串s左侧的空格 | LTRIM(' h el lo') | h el lo RTRIM(s) | 去掉字符串s右侧的空格 | RTRIM('h el lo ') | h el lo TRIM(s) | 去掉字符串s开始与结尾的空格 | SELECT TRIM(' hello ') | hello TRIM(s1 FROM s) | 去掉字符串s开始与结尾的s1 | TRIM('00' FROM '00hello00') | TRIM('00' FROM '00hello00') TRIM(LEADING s1 FROM s) | 去掉字符串s开始处的s1 | SELECT TRIM(LEADING '00' FROM '00hello00') | hello00 TRIM(TRAILING s1 FROM s) | 去掉字符串s结尾处的s1 | SELECT TRIM(TRAILING '00' FROM '00hello00') | 00hello REPEAT(str, n) | 返回str重复n次的结果 | REPEAT('hello',4) | hellohellohellohello SPACE(n) | 返回n个空格 | LENGTH(SPACE(5) | 5 STRCMP(s1,s2) | 比较字符串s1,s2的ASCII码值的大小 | STRCMP('c','b') | 1 SUBSTR(s,index,len) | 返回从字符串s的index位置其len个字符<br>作用与SUBSTRING(s,n,len)、MID(s,n,len)相同 | SUBSTR('hello',2,2) | el LOCATE(substr,str) | 返回字符串substr在字符串str中首次出现的位置，未找到返回0<br>作用与POSITION(substr IN str)、INSTR(str,substr)相同。 | LOCATE('el','hello') | 2 ELT(m,s1,s2,…,sn) | 返回指定位置的字符串<br>如果m=1，则返回s1，如果m=n，则返回sn | SELECT ELT(2,'a','b','c','d') | b FIELD(s,s1,s2,…,sn) | 返回字符串s在字符串列表中第一次出现的位置 | FIELD('mm','gg','jj','mm','dd','mm') | 3 FIND_IN_SET(s1,s2) | 返回字符串s1在字符串s2中出现的位置<br>其中，字符串s2是一个以逗号分隔的字符串 | FIND_IN_SET('mm','gg,mm,dd,mm') | 2 REVERSE(s) | 返回s反转后的字符串 | REVERSE('abc') | cba NULLIF(value1,value2) | 比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1 | NULLIF('a','b') | a ## 日期和时间函数 ### 获取日期、时间 函数 | 用法 | 示例 | 返回值 --- | --- | --- | --- CURDATE() , CURRENT_DATE() | 返回当前日期，只包含年、月、日 | CURDATE() | 2022-04-05 CURTIME() , CURRENT_TIME() | 返回当前时间，只包含时、分、秒 | CURTIME() | 16:48:37 NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP() | 返回当前系统日期和时间 | NOW() | 2022-04-05 16:48:37 UTC_DATE() | 返回UTC（世界标准时间）日期 | UTC_DATE() | 2022-04-05 UTC_TIME() | 返回UTC（世界标准时间）时间 | UTC_TIME() | 08:48:37 ### 日期与时间戳的转换 函数 | 用法 | 示例 | 返回值 --- | --- | --- | --- UNIX_TIMESTAMP() | 以UNIX时间戳的形式返回当前时间。 | UNIX_TIMESTAMP() | 1649148947 UNIX_TIMESTAMP(date) | 将时间date以UNIX时间戳的形式返回。 | UNIX_TIMESTAMP('2022-01-01 12:12:12') | 1641010332 FROM_UNIXTIME(timestamp) | 将UNIX时间戳的时间转换为普通格式的时间 | FROM_UNIXTIME(1641010332) | 2022-01-01 12:12:12 ### 获取月份、星期、星期数、天数等函数 函数 | 用法 | 示例 | 返回值 --- | --- | --- | --- YEAR(date) / MONTH(date) / DAY(date) | 返回具体的日期值 | SELECT YEAR(CURDATE()) | 2022 HOUR(time) / MINUTE(time) / SECOND(time) | 返回具体的时间值 | HOUR(CURTIME()) | 16 MONTHNAME(date) | 返回月份：January，... | MONTHNAME('2021-10-26') | October DAYNAME(date) | 返回星期几：MONDAY，TUESDAY...SUNDAY | DAYNAME('2021-10-26') | Tuesday WEEKDAY(date) | 返回周几，周1是0，周2是1，...周日是6 | WEEKDAY('2021-10-26') | 1 QUARTER(date) | 返回日期对应的季度，范围为1～4 | QUARTER(CURDATE()) |2 WEEK(date) , WEEKOFYEAR(date) | 返回一年中的第几周 | WEEK(CURDATE()) | 14 DAYOFYEAR(date) | 返回日期是一年中的第几天 | DAYOFYEAR(NOW()) | 95 DAYOFMONTH(date) | 返回日期位于所在月份的第几天 | DAYOFMONTH(NOW()) | 5 DAYOFWEEK(date) | 返回周几，周日是1，周一是2，...周六是7 | DAYOFWEEK(NOW()) | 3 ### 日期的操作函数 函数 | 用法 | 示例 | 返回值 --- | --- | --- | --- EXTRACT(type FROM date) | 返回指定日期中特定的部分，type指定返回的值 | EXTRACT(DAY FROM NOW()) | 5 `EXTRACT(type FROM date)` 函数中 `type` 的取值与含义： type取值 | 含义 --- | --- MICROSECOND | 当前毫秒 SECOND | 当前秒 0-59 MINUTE | 当前分 0-59 HOUR | 当前时 0-23 DAY | 当前日（即几号）1-31 WEEK | 当前周（即今年的第几周） MONTH | 当前月 1-12 QUARTER | 当前季度 1-4 YEAR | 当前年份 SECOND_MICROSECOND | 当前秒和毫秒（ssSSSSSS） MINUTE_MICROSECOND | 当前分、秒和毫秒（mmssSSSSSS） HOUR_MICROSECOND | 当前时、分、秒和毫秒（hhmmssSSSSSS） DAY_MICROSECOND | 当前天、时、分、秒和毫秒 MINUTE_SECOND | 当前分和秒（mmss） HOUR_SECOND | 当前时、分和秒（hhmmss） DAY_SECOND | 当前天、时、分和秒 HOUR_MINUTE | 当前时和分（hhmm） DAY_MINUTE | 返回天、时和分 DAY_HOUR | 返回天和小时 YEAR_MONTH | 当前年和月（yyyymm） ### 时间和秒钟转换的函数 函数 | 用法 | 示例 | 返回值 --- | --- | --- | --- TIME_TO_SEC(time) | 将 time 转化为秒并返回结果值。转化的公式为： 小时*3600+分钟*60+秒 | TIME_TO_SEC(CURTIME()) | 62828 SEC_TO_TIME(seconds) | 将 seconds 描述转化为包含小时、分钟和秒的时间 | SEC_TO_TIME(62828) | 17:27:08 ### 计算日期和时间的函数 > 第1组： 函数 | 用法 | 示例 | 返回值 --- | --- | --- | --- DATE_ADD(datetime, INTERVAL expr type)<br>ADDDATE(date,INTERVAL expr type) | 返回与datetime相差INTERVAL时间段的日期时间 | DATE_ADD(NOW(),INTERVAL 1 YEAR) | 2023-04-05 17:40:11 DATE_SUB(date,INTERVAL expr type)<br>SUBDATE(date,INTERVAL expr type) | 返回与date相差INTERVAL时间间隔的日期 | DATE_SUB(NOW(),INTERVAL 1 YEAR) | 2023-04-05 17:40:11 上述函数中 `type`的取值： 间隔类型 | 含义 --- | --- HOUR | 小时 MINUTE | 分钟 SECOND | 秒 YEAR | 年 MONTH | 月 DAY | 日 YEAR_MONTH | 年和月 DAY_HOUR | 日和小时 DAY_MINUTE | 日和分钟 DAY_SECOND | 日和秒 HOUR_MINUTE | 小时和分钟 HOUR_SECOND | 小时和秒 MINUTE_SECOND | 分钟和秒 > 第2组： 函数 | 用法 | 示例 | 返回值 --- | --- | --- | --- ADDTIME(time1,time2) | 返回time1加上time2的时间。当time2为一个数字时，代表的是秒，可以为负数 | ADDTIME(NOW(),20) | 2022-04-05 17:47:07 SUBTIME(time1,time2) | 返回time1减去time2后的时间。当time2为一个数字时，代表的是秒，可以为负数 | SUBTIME(NOW(),30) | 2022-04-05 17:46:17 DATEDIFF(date1,date2) | 返回date1 - date2的日期间隔天数 | DATEDIFF(NOW(),'2022-01-01') | 94 TIMEDIFF(time1, time2) | 返回time1 - time2的时间间隔 | TIMEDIFF(NOW(),'2022-01-01 22:10:10') | 838:59:59 FROM_DAYS(N) | 返回从0000年1月1日起，N天以后的日期 | FROM_DAYS(366) | 0001-01-01 TO_DAYS(date) | 返回日期date距离0000年1月1日的天数 | TO_DAYS('0000-12-25') | 359 LAST_DAY(date) | 返回date所在月份的最后一天的日期 | LAST_DAY(NOW()) | 2022-04-30 MAKEDATE(year,n) | 针对给定年份与所在年份中的天数返回一个日期 | MAKEDATE(2022,32) | 2022-02-01 MAKETIME(hour,minute,second) | 将给定的小时、分钟和秒组合成时间并返回 | MAKETIME(10,21,23) | 10:21:23 PERIOD_ADD(time,n) | 返回time加上n后的时间 | PERIOD_ADD(20200101010101,10) | 20200101010111 ### 日期的格式化与解析 函数 | 用法 | 示例 | 返回值 --- | --- | --- | --- DATE_FORMAT(date,fmt) | 按照字符串fmt格式化日期date值 | DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s') | 2022-04-05 18:12:48 TIME_FORMAT(time,fmt) | 按照字符串fmt格式化时间time值 | TIME_FORMAT(CURTIME(),'%h:%i:%S') | 06:12:48 GET_FORMAT(date_type,format_type) | 返回日期字符串的显示格式 | SELECT GET_FORMAT(DATE,'USA') | %m.%d.%Y STR_TO_DATE(str, fmt) | 按照字符串fmt对str进行解析，解析为一个日期 | STR_TO_DATE('2021-03-01 11:37:30','%Y-%m-%d %h:%i:%S') | 2021-03-01 11:37:30 上述函数中 `fmt` 参数常用的格式符： 格式符 | 说明 --- | --- %Y | 4位数字表示年份 %y | 表示两位数字表示年份 %M | 月名表示月份（January,....） %m | 两位数字表示月份（01,02,03。。。） %b | 缩写的月名（Jan.，Feb.，....） %c | 数字表示月份（1,2,3,...） %D | 英文后缀表示月中的天数（1st,2nd,3rd,...） %d | 两位数字表示月中的天数(01,02...) %e | 数字形式表示月中的天数（1,2,3,4,5.....） %H | 两位数字表示小数，24小时制（01,02..） %h 和 %I | 两位数字表示小时，12小时制（01,02..） %k | 数字形式的小时，24小时制(1,2,3) %l | 数字形式表示小时，12小时制（1,2,3,4....） %i | 两位数字表示分钟（00,01,02） %S 和 %s | 两位数字表示秒(00,01,02...) %W | 一周中的星期名称（Sunday...） %a | 一周中的星期缩写（Sun.，Mon.,Tues.，..） %w | 以数字表示周中的天数(0=Sunday,1=Monday....) %j | 以3位数字表示年中的天数(001,002...) %U | 以数字表示年中的第几周，（1,2,3。。）其中Sunday为周中第一天 %u | 以数字表示年中的第几周，（1,2,3。。）其中Monday为周中第一天 %T | 24小时制 %r | 12小时制 %p | AM或PM %% | 表示% `GET_FORMAT` 函数中 `date_type` 和 `format_type` 参数取值如下： 日期类型 | 格式化类型 | 返回的格式化字符串 --- | --- | --- DATE | USA | %m.%d.%Y DATE | JIS | %Y-%m-%d DATE | IsO | %Y-%m-%d DATE | EUR | %d.%m.%Y DATE | INTERNAL | %Y%m%d TIME | USA | %h:%i:%s %p TIME | JIS | %H:%i:%s TIME | ISO | %H:%i:%s TIME | EUR | %H.%i.%s TIME | INTERNAL | %H%i%s DATETIME | USA | %Y-%m-%d %H.%i.%s DATETIME | JIS | %Y-%m-%d %H:%i:%s DATETIME | ISO | %Y-%m-%d %H:%i:%s DATETIME | EUR | %Y-%m-%d %H.%i.%s DATETIME | INTERNAL | %Y%m%d%H%i%s ## 流程控制函数 函数 | 用法 --- | --- 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... ``` mysql> SELECT last_name,salary,IF(salary >= 6000,'高工资','低工资') "details" FROM employees; +-------------+----------+-----------+ | last_name | salary | details | +-------------+----------+-----------+ | King | 24000.00 | 高工资 | | Ernst | 6000.00 | 高工资 | | Austin | 4800.00 | 低工资 | +-------------+----------+-----------+ mysql> SELECT commission_pct,IFNULL(commission_pct,0) "details" FROM employees; +----------------+---------+ | commission_pct | details | +----------------+---------+ | NULL | 0.00 | | 0.40 | 0.40 | | 0.30 | 0.30 | +----------------+---------+ mysql> SELECT salary,CASE WHEN salary >= 15000 THEN 'A' -> WHEN salary >= 10000 THEN 'B' -> WHEN salary >= 8000 THEN 'C' -> ELSE 'D' END "details" -> FROM employees; +----------+---------+ | salary | details | +----------+---------+ | 24000.00 | A | | 9000.00 | C | | 6000.00 | D | | 12000.00 | B | +----------+---------+ mysql> ``` ## 加密解密函数 函数 | 用法 | 示例 | 返回值 --- | --- | --- | --- PASSWORD(str) | 返回字符串str的加密版本，41位长的字符串<br>加密结果不可逆，常用于用户的密码加密 | MySQL8.x弃用 MD5(str) | 返回字符串str的md5值<br>若参数为NULL，则会返回NULL | MD5('mysql') | 81c3b080dad537de7e10e0987a4bf52e SHA(str) | 返回字符串str的sha1值<br>当参数为NULL时，返回NULL<br>SHA加密算法比MD5更加安全。 | SHA('mysql') | f460c882a18c1304d88854e902e11b85d71e7e1b ENCODE(value,password_seed) | 返回使用password_seed作为加密密码加密value | MySQL8.x弃用 DECODE(value,password_seed) | 返回使用password_seed作为加密密码解密value | MySQL8.x弃用 ## MySQL信息函数 函数 | 用法 | 示例 | 返回值 --- | --- | --- | --- VERSION() | 返回当前MySQL的版本号 | VERSION() | 8.0.28 CONNECTION_ID() | 返回当前连接ID | CONNECTION_ID() | 31 DATABASE()，SCHEMA() | 返回MySQL命令行当前所在的数据库 | DATABASE() | test USER()，CURRENT_USER()、SYSTEM_USER()，SESSION_USER() | 返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名” | USER() | root@localhost CHARSET(value) | 返回字符串value自变量的字符集 | CHARSET('test') | utf8mb4 COLLATION(value) | 返回字符串value的比较规则 | COLLATION('test') | utf8mb4_0900_ai_ci ## 其他函数 函数 | 用法 | 示例 | 返回值 --- | --- | --- | --- FORMAT(value,n) | 返回对数字value进行格式化后的结果数据。n表示四舍五入后保留到小数点后n位 | FORMAT(123.125,2) | 123.13 INET_ATON(ipvalue) | 将以点分隔的IP地址转化为一个数字 | INET_ATON('192.168.1.100') | 3232235876 INET_NTOA(value) | 将数字形式的IP地址转化为以点分隔的IP地址 | INET_NTOA(3232235876) | 192.168.1.100 BENCHMARK(n,expr) | 将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费的时间 | BENCHMARK(100000,MD5('mysql')) CONVERT(value USING char_code) | 将value所使用的字符编码修改为char_code | CHARSET(CONVERT('atguigu' USING 'gbk')) | gbk # 聚合函数（分组函数） ## 聚合函数 ### 简介 聚合函数作用于一组数据，并对一组数据返回一个值。 > 聚合函数类型： - AVG() - SUM() - MAX() - MIN() - COUNT() > 聚合函数语法： ``` SELECT [column,] group function(column), ... FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column]; ``` 注：聚合函数不能嵌套调用。比如不能出现类似 `AVG(SUM(字段名称))` 形式的调用。 ### `AVG` 平均值 `SUM` 总和值 可以对 **数值型数据** 使用 `AVG` 和 `SUM` 函数 ``` mysql> SELECT AVG(salary),SUM(salary) FROM employees; +-------------+-------------+ | AVG(salary) | SUM(salary) | +-------------+-------------+ | 6461.682243 | 691400.00 | +-------------+-------------+ ``` ### `MIN` 最小值 `MAX` 最大值 可以对 **任意数据类型** 的数据使用 `MIN` 和 `MAX` 函数。 ``` mysql> SELECT MAX(salary),MIN(salary) FROM employees; +-------------+-------------+ | MAX(salary) | MIN(salary) | +-------------+-------------+ | 24000.00 | 2100.00 | +-------------+-------------+ ``` ### `COUNT` 总数值 - `COUNT(*)`：返回表中记录总数，适用于任意数据类型。 - `COUNT(x)`：返回 `x` 不为空的记录总数。 ``` mysql> SELECT COUNT(employee_id),COUNT(salary),COUNT(*) FROM employees ; +--------------------+---------------+----------+ | COUNT(employee_id) | COUNT(salary) | COUNT(*) | +--------------------+---------------+----------+ | 107 | 107 | 107 | +--------------------+---------------+----------+ ``` > 使用 COUNT(*)、COUNT(1)、COUNT(具体字段) 哪个效率更高？ - 如果使用的是 `MyISAM` 存储引擎，则三者效率相同，都是 `O(1)` - 如果使用的是 `InnoDB` 存储引擎，则三者效率：`COUNT(*)` = `COUNT(1)` > `COUNT(字段)` ## GROUP BY 分组 ### 简介 1. 可以使用 `GROUP BY` 子句将表中的数据分成若干组 2. `GROUP BY` 声明在 `FROM` 、 `WHERE` 后面，`ORDER BY` 、 `LIMIT` 前面 3. 在 `SELECT` 列表中所有未包含在组函数中的列都应该包含在 `GROUP BY` 子句中 ### 使用 > 单列 ``` mysql> SELECT department_id,AVG(salary),SUM(salary) -> FROM employees -> GROUP BY department_id; +---------------+--------------+-------------+ | department_id | AVG(salary) | SUM(salary) | +---------------+--------------+-------------+ | NULL | 7000.000000 | 7000.00 | | 10 | 4400.000000 | 4400.00 | | 20 | 9500.000000 | 19000.00 | | 30 | 4150.000000 | 24900.00 | +---------------+--------------+-------------+ ``` > 多列 ``` mysql> SELECT department_id,job_id,AVG(salary) -> FROM employees -> GROUP BY department_id,job_id; +---------------+------------+--------------+ | department_id | job_id | AVG(salary) | +---------------+------------+--------------+ | 90 | AD_PRES | 24000.000000 | | 90 | AD_VP | 17000.000000 | | 100 | FI_MGR | 12000.000000 | | 100 | FI_ACCOUNT | 7920.000000 | | 50 | ST_CLERK | 2785.000000 | | 80 | SA_MAN | 12200.000000 | | 80 | SA_REP | 8396.551724 | | NULL | SA_REP | 7000.000000 | +---------------+------------+--------------+ ``` ### `GROUP BY` 中使用 `WITH ROLLUP` 使用 `WITH ROLLUP` 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。当使用 `ROLLUP` 时，不能同时使用 `ORDER BY` 子句进行结果排序，即 `ROLLUP` 和 `ORDER BY` 是互相排斥的。 ``` mysql> SELECT department_id,AVG(salary) -> FROM employees -> GROUP BY department_id WITH ROLLUP; +---------------+--------------+ | department_id | AVG(salary) | +---------------+--------------+ | NULL | 7000.000000 | | 10 | 4400.000000 | | 20 | 9500.000000 | | 30 | 4150.000000 | | 40 | 6500.000000 | | 50 | 3475.555556 | | 60 | 5760.000000 | | 70 | 10000.000000 | | 80 | 8955.882353 | | 90 | 19333.333333 | | 100 | 8600.000000 | | 110 | 10150.000000 | | NULL | 6461.682243 | +---------------+--------------+ ``` 其中最后一行的`| NULL | 6461.682243 |`是统计的数据 ## HAVING 分组过滤 ### 简介 1. 行已经被分组。 2. 使用了聚合函数。 3. 满足 `HAVING` 子句中条件的分组将被显示。 4. `HAVING` 不能单独使用，必须要跟 `GROUP BY` 一起使用。 ### 使用 ``` mysql> SELECT department_id,MAX(salary) -> FROM employees -> GROUP BY department_id -> HAVING MAX(salary) > 10000; +---------------+-------------+ | department_id | MAX(salary) | +---------------+-------------+ | 20 | 13000.00 | | 30 | 11000.00 | | 80 | 14000.00 | | 90 | 24000.00 | | 100 | 12000.00 | | 110 | 12000.00 | +---------------+-------------+ ``` ### `WHERE` 和 `HAVING` 的对比 - 区别1：`WHERE` 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；`HAVING` 必须要与 `GROUP BY` 配合使用，可以把分组计算的函数和分组字段作为筛选条件。这决定了，在需要对数据进行分组统计的时候，`HAVING` 可以完成 `WHERE` 不能完成的任务。这是因为，在查询语法结构中，`WHERE` 在 `GROUP BY` 之前，所以无法对分组结果进行筛选。`HAVING` 在 `GROUP BY` 之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是 `WHERE` 无法完成的。另外，`WHERE` 排除的记录不再包括在分组中。 - 区别2：如果需要通过连接从关联表中获取需要的数据，`WHERE` 是先筛选后连接，而 `HAVING` 是先连接后筛选。这一点，就决定了在关联查询中，`WHERE` 比 `HAVING` 更高效。因为 `WHERE` 可以先筛选，用一个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高。`HAVING` 则需要先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用的资源就比较多，执行效率也较低。 > 总结 关键字 | 优点 | 缺点 --- | --- | --- WHERE | 先筛选数据再关联，执行效率高 | 不能使用分组中的计算函数进行筛选 HAVING | 可以使用分组中的计算函数 | 在最后的结果集中进行筛选，执行效率较低 > 开发中的选择： `WHERE` 和 `HAVING` 也不是互相排斥的，我们可以在一个查询里面同时使用 `WHERE` 和 `HAVING`。包含分组统计函数的条件用 `HAVING`，普通条件用 `WHERE`。这样，我们就既利用了 `WHERE` 条件的高效快速，又发挥了 `HAVING` 可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候，运行效率会有很大的差别。
6