1. 聚合函数
聚合函数 ( Aggregate Function ) : 是在数据库中进行数据处理和计算的常用函数 .
它们可以对一组数据进行求和 , 计数 , 平均值 , 最大值 , 最小值等操作 , 从而得到汇总结果 . 常见的聚合函数有以下几种 :
SUM : 用于计算某一列的数值总和 , 可以用于整数 , 小数或者日期类型的列 .
AVG : 用于计算某一列的数值平均值 , 通常用于整数或者小数类型的列 .
MAX : 用于找出某一列的最大值 , 可以对任意数据类型的数据使用 .
MIN : 用于找出某一列的最小值 , 同样可以对任意数据类型的数据使用 .
COUNT : 用于计算表中记录的总数或者某一列中非空值的数量 . COUNT ( * ) 会返回表中记录的总数 , 适用于任意数据类型 . 注意事项 :
* 1. 聚合函数通常都会忽略空值 ( COUNT比较特后续详细说明 ) . 例如 , 如果一列中有 5 个数值和 3 个空值 , SUM函数只会对那 5 个数值进行求和 , 而忽略那 3 个空值 . 同样地 , AVG函数也只会根据那 5 个数值来计算平均值 . * 2. 聚合函数经常与SELECT语句的GROUP BY子句一同使用 , 以便按照某个或多个列对结果进行分组 . 此外 , 还可以使用HAVING子句对聚合结果进行过滤 . 例如 , 可以找出平均薪水超过某个值的部门 . * 3. 聚合函数不能嵌套调用 . 比如不能出现类似 'AVG(SUM(字段名称))' 形式的调用 . * 4. 聚合函数通常不能与普通字段 ( 即非聚合字段 ) 直接一起出现在SELECT语句的列列表中 . ( 除非这些普通字段也包含在GROUP BY子句中 , 否则数据库会报错 . ) 聚合函数与GROUP BY子句一同使用时作用于一组数据 , 并对每一组数据返回一个值 . 聚合函数单独使用时作用于一列数据 , 并返回一个值 . 如果在SELECT语句中同时包含聚合函数和普通字段 , 但没有使用GROUP BY子句 , 数据库不知道如何将普通字段的值与聚合函数的结果对应起来 .
1.1 求数值总数
基本语法 : SUM ( column_name ) ; 用于计算指定列的总和 .
例 : SELECT SUM ( salary ) AS total_salary FROM employees ;
mysql> SELECT SUM ( salary) FROM employees;
+
| SUM ( salary) |
+
| 691400.00 |
+
1 row in set ( 0.00 sec)
1.2 仅全分组模式
ONLY_FULL_GROUP_BY : 是MySQL中的一种SQL模式 , 其作用是约束SQL语句的执行 .
当启用这个模式时 , MySQL要求SELECT语句中的GROUP BY子句中的列必须是聚合函数 ( 如SUM、COUNT等 ) 或在GROUP BY子句中出现的列 .
换句话说 , 如果在SELECT语句中存在既不在GROUP BY子句中也不作为聚合函数的列 , MySQL将会抛出错误 . 这种限制的目的是为了确保查询结果的准确性 .
因为在GROUP BY语句中 , 查询结果是按照分组列进行聚合的 .
如果SELECT语句中的列既包含了聚合函数又包含了其他非分组列 , 那么就会出现模糊性 , 无法确切地确定每个分组中的非聚合列的值 .
如果想要显示员工id , 姓名等字段信息 , 代码则为 : SELECT employee_id , first_name , SUM ( salary ) FROM employees ;
在MySQL 5.7 .5 之前的版本中代码是可以运行的 , ROUP BY的行比较宽松 ,
允许在SELECT列表中选择未在GROUP BY子句中明确提及的列 , 并且没有使用聚合函数 .
然而 , 从MySQL 5.7 .5 起 , 由于默认的sql_mode包含了ONLY_FULL_GROUP_BY , MySQL对GROUP BY的执行更加严格 . ONLY_FULL_GROUP_BY模式要求 , 对于SELECT列表 , HAVING条件或ORDER BY列表中的非聚合列 ,
它们必须明确地出现在 GROUP BY 子句中 .
如果没有 , MySQL 就会抛出一个错误 . 临时关闭ONLY_FULL_GROUP_BY模式 : SET SESSION sql_mode = ( SELECT REPLACE ( @ @ sql_mode , 'ONLY_FULL_GROUP_BY' , '' ) ) ;
mysql> SET SESSION sql_mode= ( SELECT REPLACE ( @@sql_mode , 'ONLY_FULL_GROUP_BY' , '' ) ) ;
Query OK, 0 rows affected ( 0.00 sec)
mysql> SELECT employee_id, first_name, SUM ( salary) FROM employees;
+
| employee_id | first_name | SUM ( salary) |
+
| 100 | Steven | 691400.00 |
+
1 row in set ( 0.00 sec)
如果employees表中有多行数据 , 且没有使用 GROUP BY子句 , 那么employee_id和first_name的值是从表中随机选择的单行数据 ,
而SUM ( salary ) 则是计算了表中所有行的薪水总和 .
因此 , employee_id和first_name的值在这种情况下确实是没有意义的 , 因为它们并不代表与薪水总和相对应的具体员工 .
1.3 求平均值
基本语法 : AVG ( column_name ) ; 用于计算指定列的平均值 .
例 : SELECT AVG ( salary ) AS average_salary FROM employees ;
mysql> SELECT AVG ( salary) FROM employees;
+
| AVG ( salary) |
+
| 6461.682243 |
+
1 row in set ( 0.00 sec)
mysql> SELECT SUM ( salary) / 107 FROM employees;
+
| SUM ( salary) / 107 |
+
| 6461.682243 |
+
1 row in set ( 0.00 sec)
1.4 求最大/小值
基本语法 :
MIN ( column_name ) ; 用于找出指定列的最小值 . 例 : SELECT MIN ( salary ) AS minimum_salary FROM employees ;
MAX ( column_name ) ; 用于找出指定列的最大值 . 例 : SELECT MAX ( salary ) AS maximum_salary FROM employees ;
-- 查询最高工资:
mysql> SELECT MAX(salary) FROM employees;
+-------------+
| MAX(salary) |
+-------------+
| 24000.00 |
+-------------+
1 row in set (0.00 sec)
mysql> SELECT MIN ( salary) FROM employees;
+
| MIN ( salary) |
+
| 2100.00 |
+
1 row in set ( 0.00 sec)
mysql> SELECT MIN ( hire_date) , MAX ( hire_date) FROM employees;
+
| MIN ( hire_date) | MAX ( hire_date) |
+
| 1987 - 06 - 17 | 2000 - 04 - 21 |
+
1 row in set ( 0.00 sec)
1.5 统计
基本语法 :
COUNT ( * ) : 计算表中所有记录的数量 . 例 : SELECT COUNT ( * ) AS total_employees FROM employees ;
COUNT ( 常数 ) : 计算表中所有记录的数量 . 例 : SELECT COUNT ( 1 ) AS total_employees FROM employees ;
COUNT ( 字段 ) : 计算指定列中非NULL值的数量 . 例 : SELECT COUNT ( salary ) AS salary_count FROM employees ; COUNT函数在处理NULL值时的行为取决于其使用的具体形式 .
使用COUNT ( 1 ) 或COUNT ( 2 ) 或COUNT ( * ) 时 , 它们的功能实际上是相同的 .
( 这里的常数仅仅是一个占位符 , 用于表示对每一行进行计数 , 可以被任何非NULL的常量表达式替换 . )
它们都会计算表中的行数 , 不论列中的值是否为NULL .
count ( * ) 是SQL92定义的标准统计行数的语法 , 跟数据库无关 , 跟NULL和非NULL无关 . 当使用COUNT ( 字段 ) 时 , 它会统计该字段在表中出现的次数 , 但会忽略字段值为NULL的情况 .
也就是说 , 它不会将null值计入总数 .
mysql> SELECT COUNT ( * ) , COUNT ( 1 ) , COUNT ( 2 ) FROM employees;
+
| COUNT ( * ) | COUNT ( 1 ) | COUNT ( 2 ) |
+
| 107 | 107 | 107 |
+
1 row in set ( 0.00 sec)
mysql> SELECT COUNT ( employee_id) , COUNT ( commission_pct) FROM employees;
+
| COUNT ( employee_id) | COUNT ( commission_pct) |
+
| 107 | 35 |
+
1 row in set ( 0.00 sec)
mysql> SELECT SUM ( salary) / COUNT ( * ) FROM employees;
+
| SUM ( salary) / COUNT ( * ) |
+
| 6461.682243 |
+
1 row in set ( 0.00 sec)
mysql> SELECT AVG ( commission_pct) , SUM ( commission_pct) / COUNT ( commission_pct) FROM employees;
+
| AVG ( commission_pct) | SUM ( commission_pct) / COUNT ( commission_pct) |
+
| 0.222857 | 0.222857 |
+
1 row in set ( 0.00 sec)
mysql> SELECT AVG ( IFNULL( commission_pct, 0 ) ) ,
SUM ( commission_pct) / COUNT ( IFNULL( commission_pct, 0 ) )
FROM employees;
+
| AVG ( IFNULL( commission_pct, 0 ) ) | SUM ( commission_pct) / COUNT ( IFNULL( commission_pct, 0 ) ) |
+
| 0.072897 | 0.072897 |
+
1 row in set ( 0.00 sec)
问题 : 统计表中的记录数 , 使用COUNT ( * ) , COUNT ( 1 ) , COUNT ( 具体字段 ) 哪个效率更高呢?
答 : 如果使用的是MyISAM存储引擎 , 则三者效率相同 , 都是O ( 1 ) . 如果使用的是InnoDB 存储引擎 , 则三者效率 : COUNT ( * ) = COUNT ( 1 ) > COUNT ( 字段 ) .
2. GROUP BY 分组
2.1 基本语法
GROUP BY是SQL中的一个子句 , 用于对查询结果进行分组 .
在SELECT语句中使用GROUP BY子句时 , 数据库会根据指定的列或表达式的值对查询结果进行分组 .
所有具有相同值的行会被放到同一个分组中 , 如果分组列中具有NULL值 , 则NULL将作为一个分组返回 .
然后 , 你可以使用聚合函数 ( 如SUM , AVG , COUNT等 ) 对每个分组进行计算 , 得到每个分组的摘要信息 . 基本语法如下 :
SELECT column_name , aggregate_function ( column_name )
FROM table_name
WHERE column_name operator value
GROUP BY column_name ;
其中 , column_name是你要根据其进行分组的列的名称 , aggregate_function是你要应用的聚合函数 .
GROUP BY声明在FROM后面 , WHERE后面 , ORDER BY前面 , LIMIT前面 . 注意事项 :
* 1. 除聚集计算语句外 , SELECT语句中的每个列都必须在GROUP BY子句中给出 , 否则报错 .
* 2. GROUP BY子句可以包含任意数目的列 , 这使得能对分组进行嵌套 , 为数据分组提供更细致的控制 . 例如 , 你可以同时按部门和职位对员工进行分组 , 以获取更详细的数据 .
* 3. GROUP_CONCAT ( ) 函数是MySQL中的一个特殊函数 , 它主要用于将同一个分组中的值连接起来 , 并返回一个字符串结果 . 这个函数在配合GROUP BY子句使用时非常有用 , 因为它可以在分组后 , 将指定字段的所有值连接成一个单独的字符串 .
* 4. 使用GROUP BY子句对多个列进行分组时 , 列的顺序并不会影响分组的结果或聚合函数的结果 . 这是因为GROUP BY子句是基于所有列的组合来定义分组的 , 而不是单独基于每个列 .
2.2 基本使用
mysql> SELECT department_id FROM employees GROUP BY department_id;
+
| department_id |
+
| NULL |
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
| 60 |
| 70 |
| 80 |
| 90 |
| 100 |
| 110 |
+
12 rows in set ( 0.00 sec)
SELECT department_id, GROUP_CONCAT( salary) FROM employees GROUP BY department_id;
+
| department_id | GROUP_CONCAT( salary) | | NULL | 7000.00 |
| 10 | 4400.00 | | 20 | 13000.00 , 6000.00 |
| 30 | 11000.00 , 3100.00 , 2900.00 , 2800.00 , 2600.00 , 2500.00 |
| 40 | 6500.00 |
| . . . | . . . |
| 100 | 12000.00 , 9000.00 , 8200.00 , 7700.00 , 7800.00 , 6900.00 |
| 110 | 12000.00 , 8300.00 | +
mysql> SELECT department_id, salary FROM employees GROUP BY department_id;
ERROR 1055 ( 42000 ) : Expression
this is incompatible with sql_mode= only_full_group_by
2.3 搭配聚合函数
使用GROUP BY子句与聚合函数搭配是非常常见的做法 ,
主要用于将多行数据按照一个或多个列进行分组 , 并对每个分组应用聚合函数来计算汇总值 . 以下是一些常用的聚合函数及其与GROUP BY子句搭配使用的示例 :
* 1. 计算每个分组的某列的总和 : SELECT department_id , SUM ( salary ) AS total_salary FROM employees GROUP BY department_id ;
* 2. 计算每个分组的某列的平均值 : SELECT job_id , AVG ( salary ) AS average_salary FROM employees GROUP BY job_id ;
* 3. 找出每个分组的某列的最大值 : SELECT department_id , MAX ( salary ) AS max_salary FROM employees GROUP BY department_id ;
* 4. 找出每个分组的某列的最小值 : SELECT job_id , MIN ( hire_date ) AS earliest_hire_date FROM employees GROUP BY job_id ;
* 5. 计算每个分组的行数 : SELECT department_id , COUNT ( * ) AS num_employees FROM employees GROUP BY department_id ;
mysql> SELECT department_id, AVG ( salary) FROM employees GROUP BY department_id;
+
| 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 |
+
12 rows in set ( 0.00 sec)
mysql> SELECT department_id, MAX ( salary) FROM employees GROUP BY department_id;
+
| department_id | MAX ( salary) |
+
| NULL | 7000.00 |
| 10 | 4400.00 |
| 20 | 13000.00 |
| 30 | 11000.00 |
| 40 | 6500.00 |
| 50 | 8200.00 |
| 60 | 9000.00 |
| 70 | 10000.00 |
| 80 | 14000.00 |
| 90 | 24000.00 |
| 100 | 12000.00 |
| 110 | 12000.00 |
+
12 rows in set ( 0.00 sec)
mysql> SELECT job_id, AVG ( salary) FROM employees GROUP BY job_id;
+
| job_id | AVG ( salary) |
+
| AC_ACCOUNT | 8300.000000 |
| AC_MGR | 12000.000000 |
| AD_ASST | 4400.000000 |
| . . . | . . . |
| SA_REP | 8350.000000 |
| SH_CLERK | 3215.000000 |
| ST_CLERK | 2785.000000 |
| ST_MAN | 7280.000000 |
+
19 rows in set ( 0.00 sec)
2.4 多列分组
GROUP BY子句对多个列进行分组时 , 列的顺序不会影响分组的结果 .
但SELECT子句中列的顺序可能会影响结果的显示顺序 .
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 |
| 60 | IT_PROG | 5760.000000 |
| . . . | . . . | . . . |
| 110 | AC_MGR | 12000.000000 |
| 110 | AC_ACCOUNT | 8300.000000 |
+
20 rows in set ( 0.00 sec)
mysql> SELECT job_id, department_id, AVG ( salary) FROM employees GROUP BY job_id, department_id;
+
| job_id | department_id | AVG ( salary) |
+
| AD_PRES | 90 | 24000.000000 |
| AD_VP | 90 | 17000.000000 |
| . . . | . . . | . . . |
| AC_MGR | 110 | 12000.000000 |
| AC_ACCOUNT | 110 | 8300.000000 |
+
20 rows in set ( 0.00 sec)
2.5 分组后排序
在SQL中 , 分组后排序通常涉及两个关键步骤 :
* 1. 首先使用GROUP BY子句对数据进行分组 .
* 2. 然后使用ORDER BY子句对分组后的结果进行排序 . 基本语法 :
SELECT column1 , column2 , aggregate_function ( column3 )
FROM your_table
GROUP BY column1 , column2
ORDER BY column1 , column2 ;
mysql> SELECT department_id, AVG ( salary) AS ` avg_sal` FROM employees GROUP BY department_id ORDER BY avg_sal;
+
| department_id | avg_sal |
+
| 50 | 3475.555556 |
| 30 | 4150.000000 |
| 10 | 4400.000000 |
| 60 | 5760.000000 |
| 40 | 6500.000000 |
| NULL | 7000.000000 |
| 100 | 8600.000000 |
| 80 | 8955.882353 |
| 20 | 9500.000000 |
| 70 | 10000.000000 |
| 110 | 10150.000000 |
| 90 | 19333.333333 |
+
12 rows in set ( 0.00 sec)
2.6 WITH ROLLUP 子句
WITH ROLLUP是SQL中的一个子句 , 主要用于与GROUP BY子句结合使用 , 以在查询结果中生成小计和总计 .
当在查询中使用GROUP BY对数据进行分组后 , WITH ROLLUP会在结果集的末尾添加额外的行 , 这些行显示了基于当前分组层次结构的汇总信息 . 语法 : SELECT department_id , AVG ( salary ) FROM employees GROUP BY department_id WITH ROLLUP ; 注意事项 :
* 1. 汇总值通常是基于所选的聚合函数来计算的 ( 如汇总AVG ( ) 函数的平均值 , 那么汇总的结果也就是平均值 ) .
* 2. 当使用WITH ROLLUP进行分组时 , 如果在汇总时列的某个值为NULL , 那么该列在汇总行中的值也会被设置为NULL .
* 3. 使用多列进行分组时 , WITH ROLLUP会按照你指定的列的顺序生成不同级别的汇总 . 例如 , 如果按照列A和列B进行分组 , 那么WITH ROLLUP会首先为每一组 ( A , B ) 生成汇总行 , 然后为每一个唯一的A值生成汇总行 ( 此时B列的值为NULL ) , 最后在结果集的末尾生成一个包含所有行汇总的单一行 ( 此时A和B列的值都为NULL ) .
* 4. 老版本MySQL使用WITH 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 |
+
13 rows in set ( 0.00 sec)
mysql> SELECT department_id, SUM ( salary) FROM employees GROUP BY department_id WITH ROLLUP ;
+
| department_id | SUM ( salary) |
+
| NULL | 7000.00 |
| 10 | 4400.00 |
| 20 | 19000.00 |
| 30 | 24900.00 |
| 40 | 6500.00 |
| 50 | 156400.00 |
| 60 | 28800.00 |
| 70 | 10000.00 |
| 80 | 304500.00 |
| 90 | 58000.00 |
| 100 | 51600.00 |
| 110 | 20300.00 |
| NULL | 691400.00 |
+
13 rows in set ( 0.00 sec)
mysql> SELECT department_id, job_id, AVG ( salary) FROM employees GROUP BY department_id, job_id WITH ROLLUP ;
+
| department_id | job_id | AVG ( salary) |
+
| NULL | SA_REP | 7000.000000 |
| NULL | NULL | 7000.000000 |
| 10 | AD_ASST | 4400.000000 |
| 10 | NULL | 4400.000000 |
| 20 | MK_MAN | 13000.000000 |
| 20 | MK_REP | 6000.000000 |
| 20 | NULL | 9500.000000 |
| 30 | PU_CLERK | 2780.000000 |
| 30 | PU_MAN | 11000.000000 |
| 30 | NULL | 4150.000000 |
| . . . | . . . | . . . |
| NULL | NULL | 6461.682243 |
+
33 rows in set ( 0.00 sec)
mysql> SELECT department_id, AVG ( salary) AS ` avg_sal` FROM employees
GROUP BY department_id WITH ROLLUP ORDER BY avg_sal ASC ;
+
| department_id | avg_sal |
+
| 50 | 3475.555556 |
| 30 | 4150.000000 |
| 10 | 4400.000000 |
| 60 | 5760.000000 |
| NULL | 6461.682243 |
| 40 | 6500.000000 |
| NULL | 7000.000000 |
| 100 | 8600.000000 |
| 80 | 8955.882353 |
| 20 | 9500.000000 |
| 70 | 10000.000000 |
| 110 | 10150.000000 |
| 90 | 19333.333333 |
+
13 rows in set ( 0.00 sec)
mysql> SELECT department_id, AVG ( salary) AS ` avg_sal` FROM employees
GROUP BY department_id WITH ROLLUP ORDER BY avg_sal ASC ;
ERROR 1221 ( HY000) : Incorrect usage of CUBE/ ROLLUP and ORDER BY
3. HAVING过滤
3.1 基本语法
在MySQL中 , HAVING关键字主要用于对分组后的数据进行过滤 . HAVING子句的基本语法如下 :
HAVING < 查询条件 >
其中 , < 查询条件 > 指的是指定的过滤条件 , 可以包含聚合函数 . 例如 , 如果你想要查询各部门最高工资大于 10000 的部门信息 , 可以使用以下查询 :
SELECT department , MAX ( salary ) as max_salary
FROM employees
GROUP BY department
HAVING max_salary > 10000 ;
在这个查询中 , 首先使用GROUP BY子句按部门对员工进行分组 , 然后使用MAX ( salary ) 聚合函数计算每个部门的最高工资 .
最后 , 使用HAVING子句过滤出最高工资大于 10000 的部门 . 注意事项 :
* 1. 当过滤条件中使用了聚合函数时 , 必须使用HAVING而不是WHERE . 这是因为WHERE子句在数据分组前进行过滤 , 并且不支持聚合函数 , 而HAVING子句是在数据分组后进行过滤 , 并支持使用聚合函数 .
* 2. HAVING子句通常与GROUP BY子句一起使用 , 且声明在GROUP BY子句的后面 . 另外 , 虽然HAVING可以单独使用 , 但当过滤条件中没有聚合函数时 , 推荐使用WHERE子句 , 因为它的执行效率更高 .
mysql> SELECT department_id, MAX ( salary) AS ` 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 |
+
6 rows in set ( 0.00 sec)
3.2 基本使用
mysql> SELECT department_id, MAX ( salary) AS ` max_salary` FROM employees
WHERE department_id IN ( 10 , 20 , 30 , 40 )
GROUP BY department_id HAVING max_salary > 10000 ;
+
| department_id | max_salary |
+
| 20 | 13000.00 |
| 30 | 11000.00 |
+
2 rows in set ( 0.00 sec)
mysql> SELECT department_id, MAX ( salary) AS ` max_salary` FROM employees
GROUP BY department_id HAVING max_salary > 10000 AND department_id IN ( 10 , 20 , 30 , 40 ) ;
+
| department_id | max_salary |
+
| 20 | 13000.00 |
| 30 | 11000.00 |
+
2 rows in set ( 0.00 sec)
3.3 WHERE和HAVING的对比
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可以使用包含分组统计函数的查询条件的优点 .
当数据量特别大的时候 , 运行效率会有很大的差别 .
4. SQL底层执行原理
4.1 查询的结构
关键字顺序 : SELECT . . . FROM . . . JOIN . . . ON . . . WHERE . . . GROUP BY . . . HAVING . . . ORDER BY . . . LIMIT . . .
SELECT DISTINCT column1, column2, aggregate_function( column3) AS alias_name
FROM table1
JOIN table2
ON table1. id = table2. table1_id
WHERE table1. column4 = 'some_value' AND table2. column5 > 100
GROUP BY column1, column2
HAVING aggregate_function( column3) > some_value
ORDER BY column1 ASC , aggregate_function( column3) DESC
LIMIT offset , number_of_rows;
SELECT DISTINCT column1, column2, aggregate_function( column3) AS alias_name
FROM table1
( LEFT / RIGHT ) JOIN table2
ON table1. id = table2. table1_id
WHERE table1. column4 = 'some_value' AND table2. column5 > 100
GROUP BY column1, column2
HAVING aggregate_function( column3) > some_value
ORDER BY column1 ASC , alias_name DESC
LIMIT offset , number_of_rows;
4.2 SELECT执行顺序
SQL语句的各个部分会按照以下顺序进行处理 :
* 1. FROM . . . JOIN . . . ON 首先 , 数据库会根据FROM子句确定要查询的表 . 然后 , 如果存在JOIN操作 ( 如INNER JOIN , LEFT JOIN , RIGHT JOIN等 ) , 数据库会根据ON子句中指定的条件将这些表连接起来 . * 2. WHERE 在连接了所有必要的表之后 , WHERE子句会过滤出满足条件的记录 . 这一步会大大减少需要处理的记录数 . * 3. GROUP BY 接下来 , GROUP BY子句会将结果集按照指定的列进行分组 . 这通常与聚合函数 ( 如SUM ( ) , COUNT ( ) , AVG ( ) 等 ) 一起使用 , 以便对每个组进行计算 . * 4. HAVING HAVING子句用于过滤分组后的结果 . 它类似于WHERE子句 , 但作用于分组后的数据 , 而不是原始的记录 . * 5. SELECT 在此阶段 , 数据库会选择要显示的列 . 这包括直接选择的列和通过聚合函数计算得到的列 . * 6. DISTINCT 如果在SELECT子句中使用了DISTINCT关键字 , 数据库会去除结果集中的重复行 . * 7. ORDER BY ORDER BY子句会对结果集进行排序 . 可以按照一列或多列进行排序 , 并指定升序 ( ASC ) 或降序 ( DESC ) . * 8. LIMIT 最后 , LIMIT子句用于限制返回的记录数 . 这对于分页查询特别有用 , 因为它允许你只获取结果集的一个子集 . 需要注意的是 , 虽然上述顺序是SQL语句的逻辑执行顺序 , 但实际的物理执行计划可能会因数据库优化器的决策而有所不同 .
优化器会根据表的大小 , 索引的存在与否 , 统计信息等因素来决定如何最有效地执行查询 .
执行顺序 : FROM . . . JOIN . . . ON - > WHERE - > GROUP BY - > HAVING - > SELECT 的字段 - > DISTINCT - > ORDER BY - > LIMIT
SELECT DISTINCT player_id, player_name, count ( * ) as num
FROM player JOIN team ON player. team_id = team. team_id
WHERE height > 1.80
GROUP BY player. team_id
HAVING num > 2
ORDER BY num DESC
LIMIT 2
在SELECT语句执行这些步骤的时候 , 每个步骤都会产生一个虚拟表 , 然后将这个虚拟表传入下一个步骤中作为输入 .
需要注意的是 , 这些步骤隐含在SQL的执行过程中 , 对于我们来说是不可见的 .
4.3 SQL 的执行原理
SELECT是先执行FROM这一步的 .
在这个阶段 , 如果是多张表联查 , 还会经历下面的几个步骤 :
* 1. 首先先通过CROSS JOIN求笛卡尔积 , 相当于得到虚拟表 vt ( virtual table ) 1 - 1 ;
* 2. 通过ON进行筛选 , 在虚拟表vt1- 1 的基础上进行筛选 , 得到虚拟表 vt1- 2 ;
* 3. 添加外部行 . 如果我们使用的是左连接 , 右链接或者全连接 , 就会涉及到外部行 , 也就是在虚拟表 vt1- 2 的基础上增加外部行 , 得到虚拟表 vt1- 3. 当然如果我们操作的是两张以上的表 , 还会重复上面的步骤 , 直到所有表都被处理完为止 .
这个过程得到是我们的原始数据 . 当我们拿到了查询数据表的原始数据 , 也就是最终的虚拟表vt1 , 就可以在此基础上再进行WHERE阶段 .
在这个阶段中 , 会根据vt1表的结果进行筛选过滤 , 得到虚拟表 vt2 . 然后进入第三步和第四步 , 也就是GROUP BY和HAVING阶段 .
在这个阶段中 , 实际上是在虚拟表vt2的基础上进行分组和分组过滤 , 得到中间的虚拟表vt3和vt4 . 当我们完成了条件筛选部分之后 , 就可以筛选表中提取的字段 , 也就是进入到SELECT和DISTINCT阶段 .
首先在SELECT阶段会提取想要的字段 , 然后在DISTINCT阶段过滤掉重复的行 , 分别得到中间的虚拟表vt5- 1 和vt5- 2. 当我们提取了想要的字段数据之后 , 就可以按照指定的字段进行排序 , 也就是ORDER BY阶段 , 得到虚拟表vt6 . 最后在vt6的基础上 , 取出指定行的记录 , 也就是LIMIT阶段 , 得到最终的结果 , 对应的是虚拟表vt7 . 当然我们在写SELECT语句的时候 , 不一定存在所有的关键字 , 相应的阶段就会省略 .
4.4 别名问题
在SQL中 , 字段别名主要在SELECT子句中使用 , 以便为查询结果的列提供更具描述性或更简洁的名称 .
然而 , 别名在查询的其他部分 ( 如WHERE和GROUP BY子句 ) 中的使用是有限制的 , 这主要是出于查询逻辑和语法解析的考虑 . 为什么WHERE和GROUP BY不能使用别名?
逻辑顺序 : SQL查询的执行顺序并不是按照你写的顺序来的 .
虽然书写顺序是 : FROM - > WHERE - > GROUP BY - > HAVING - > SELECT , 但实际执行时 , WHERE和GROUP BY会在SELECT之前被处理 .
因此 , 在WHERE或GROUP BY中使用别名时 , 该别名可能尚未被定义或识别 . 解析顺序 : SQL查询解析器在处理查询时 , 需要按照特定的顺序来解析各个子句 .
如果允许在WHERE或GROUP BY中使用别名 , 那么解析器就需要进行更复杂的处理 , 以确保别名在正确的上下文中被解析 . 为什么HAVING可以使用别名?
标准的SQL标准是不允许在having中使用select子句中的别名 , 但是MySQL对这个地方进行了扩展 .
官网中明确表示 : 可以在group by , having子句中使用别名 , 不可以在where中使用别名。
地址 : https : / / dev . mysql . com / doc / refman / 8.0 /en/problems-with-alias.html .
地址 : https : / / dev . mysql . com / doc / refman / 8.0 /en/group-by-handling.html .
5. 练习
* 1. where子句可否使用组函数进行过滤?
答 : where不支持组函数过滤 , 组函数过滤需要使用having子句 .
mysql> SELECT MAX ( salary) , MIN ( salary) , AVG ( salary) , SUM ( salary) FROM employees;
+
| MAX ( salary) | MIN ( salary) | AVG ( salary) | SUM ( salary) |
+
| 24000.00 | 2100.00 | 6461.682243 | 691400.00 |
+
1 row in set ( 0.00 sec)
mysql> SELECT MAX ( salary) , MIN ( salary) , MAX ( salary) - MIN ( salary) AS ` difference` FROM employees;
+
| MAX ( salary) | MIN ( salary) | difference |
+
| 24000.00 | 2100.00 | 21900.00 |
+
1 row in set ( 0.00 sec)
mysql> SELECT job_id, MAX ( salary) , MIN ( salary) , AVG ( salary) , SUM ( salary) FROM employees GROUP BY job_id;
+
| job_id | MAX ( salary) | MIN ( salary) | AVG ( salary) | SUM ( salary) |
+
| AC_ACCOUNT | 8300.00 | 8300.00 | 8300.000000 | 8300.00 |
| AC_MGR | 12000.00 | 12000.00 | 12000.000000 | 12000.00 |
| AD_ASST | 4400.00 | 4400.00 | 4400.000000 | 4400.00 |
| AD_PRES | 24000.00 | 24000.00 | 24000.000000 | 24000.00 |
| AD_VP | 17000.00 | 17000.00 | 17000.000000 | 34000.00 |
| FI_ACCOUNT | 9000.00 | 6900.00 | 7920.000000 | 39600.00 |
| FI_MGR | 12000.00 | 12000.00 | 12000.000000 | 12000.00 |
| HR_REP | 6500.00 | 6500.00 | 6500.000000 | 6500.00 |
| IT_PROG | 9000.00 | 4200.00 | 5760.000000 | 28800.00 |
| MK_MAN | 13000.00 | 13000.00 | 13000.000000 | 13000.00 |
| MK_REP | 6000.00 | 6000.00 | 6000.000000 | 6000.00 |
| PR_REP | 10000.00 | 10000.00 | 10000.000000 | 10000.00 |
| PU_CLERK | 3100.00 | 2500.00 | 2780.000000 | 13900.00 |
| PU_MAN | 11000.00 | 11000.00 | 11000.000000 | 11000.00 |
| SA_MAN | 14000.00 | 10500.00 | 12200.000000 | 61000.00 |
| SA_REP | 11500.00 | 6100.00 | 8350.000000 | 250500.00 |
| SH_CLERK | 4200.00 | 2500.00 | 3215.000000 | 64300.00 |
| ST_CLERK | 3600.00 | 2100.00 | 2785.000000 | 55700.00 |
| ST_MAN | 8200.00 | 5800.00 | 7280.000000 | 36400.00 |
+
19 rows in set ( 0.00 sec)
mysql> SELECT job_id, COUNT ( * ) FROM employees GROUP BY job_id;
+
| job_id | COUNT ( * ) |
+
| AC_ACCOUNT | 1 |
| AC_MGR | 1 |
| AD_ASST | 1 |
| AD_PRES | 1 |
| AD_VP | 2 |
| FI_ACCOUNT | 5 |
| FI_MGR | 1 |
| HR_REP | 1 |
| IT_PROG | 5 |
| MK_MAN | 1 |
| MK_REP | 1 |
| PR_REP | 1 |
| PU_CLERK | 5 |
| PU_MAN | 1 |
| SA_MAN | 5 |
| SA_REP | 30 |
| SH_CLERK | 20 |
| ST_CLERK | 20 |
| ST_MAN | 5 |
+
19 rows in set ( 0.00 sec)
mysql> SELECT manager_id, MIN ( salary) AS ` min_salary`
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING min_salary > 6000 ;
+
| manager_id | min_salary |
+
| 102 | 9000.00 |
| 108 | 6900.00 |
| 145 | 7000.00 |
| 146 | 7000.00 |
| 147 | 6200.00 |
| 148 | 6100.00 |
| 149 | 6200.00 |
| 205 | 8300.00 |
+
8 rows in set ( 0.00 sec)
SELECT department_name, location_id, COUNT ( * ) , AVG ( salary) AS ` avg_salary`
FROM employees AS ` emp`
RIGHT JOIN departments AS ` dep`
ON emp. department_id = dep. department_id
GROUP BY department_name, location_id
ORDER BY avg_salary DESC ;
+
| department_name | location_id | COUNT ( * ) | avg_salary |
+
| Executive | 1700 | 3 | 19333.333333 |
| Accounting | 1700 | 2 | 10150.000000 |
| Public Relations | 2700 | 1 | 10000.000000 |
| Marketing | 1800 | 2 | 9500.000000 |
| Sales | 2500 | 34 | 8955.882353 |
| Finance | 1700 | 6 | 8600.000000 |
| Human Resources | 2400 | 1 | 6500.000000 |
| IT | 1400 | 5 | 5760.000000 |
| Administration | 1700 | 1 | 4400.000000 |
| Purchasing | 1700 | 6 | 4150.000000 |
| Shipping | 1500 | 45 | 3475.555556 |
| . . . | . . . | . . . | . . . |
| Retail Sales | 1700 | 1 | NULL |
| Recruiting | 1700 | 1 | NULL |
| Payroll | 1700 | 1 | NULL |
+
27 rows in set ( 0.00 sec)
SELECT department_name, job_id, MIN ( salary) AS ` min_salary`
FROM employees AS ` emp`
RIGHT JOIN departments AS ` dep`
ON emp. department_id = dep. department_id
GROUP BY department_name, job_id;
+
| department_name | job_id | min_salary |
+
| Administration | AD_ASST | 4400.00 |
| Marketing | MK_MAN | 13000.00 |
| Marketing | MK_REP | 6000.00 |
| Purchasing | PU_MAN | 11000.00 |
| Purchasing | PU_CLERK | 2500.00 |
| Human Resources | HR_REP | 6500.00 |
| Shipping | ST_MAN | 5800.00 |
| . . . | . . . | . . . |
| IT Support | NULL | NULL |
| NOC | NULL | NULL |
| IT Helpdesk | NULL | NULL |
| Government Sales | NULL | NULL |
| Retail Sales | NULL | NULL |
| Recruiting | NULL | NULL |
| Payroll | NULL | NULL |
+
35 rows in set ( 0.00 sec)