招商加盟网站开发,物联网专业就业方向,曲靖公司网站建设,wordpress返回html原理1. 聚合函数
聚合函数(Aggregate Function): 是在数据库中进行数据处理和计算的常用函数.
它们可以对一组数据进行求和, 计数, 平均值, 最大值, 最小值等操作, 从而得到汇总结果.常见的聚合函数有以下几种:
SUM: 用于计算某一列的数值总和, 可以用于整数, 小数或者日期类型的列…
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,));-- 临时关闭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 | -- 员工表中有107条数据.
------------------------------
1 row in set (0.00 sec)-- COUNT(字段)时会忽略字段值为NULL的情况:
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)-- 计算有佣金的员工的佣金平均值(忽略值为NULL的情况):
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)-- 使用GROUP_CONCAT()函数可以在分组后, 将指定字段的所有值连接成一个单独的字符串.
-- 按部门分组后, 将每个组的成员薪资连接成一个字符串:
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 | -------------------------------------------------------------------------------
-- 除聚集计算语句外, SELECT语句中的每个列都必须在GROUP BY子句中给出, 否则报错.
-- salary没有在集合函数中, 也没有在GROUP BY分组中, 就不能出现在SELECT中.
mysql SELECT department_id, salary FROM employees GROUP BY department_id;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column atguigudb.employees.salary which is not functionally dependent on columns in GROUP BY clause;
this is incompatible with sql_modeonly_full_group_by-- 错误1055(42000): SELECT 列表中的表达式#2不在GROUP BY子句中,
-- 并且包含非聚合列atguigudb.employees.salary, 该列不是GROUP BY子句中列的函数依赖项;
-- 这与sql_modeonly_full_group_by 不兼容.
-- 如果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;-- 计算每个部门的平均工资(1.先按部门id分组. 2.使用聚合函数对分组后的字段进行计算):
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子句中列的顺序可能会影响结果的显示顺序.-- 查询每个部门与岗位的平均工资.
-- 方式1:
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 | -- 先按部门id分组, 在这个基础上再按岗位id分组.
| 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)-- 方式2:
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 | -- GROUP BY子句对多个列进行分组时, 列的顺序不会影响分组的结果...
| AD_VP | 90 | 17000.000000 | -- SELECT子句中列的顺序可能会影响结果的显示顺序.
| ... | ... | ... | -- 省略
| 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)-- 多列分组使用WITH ROLLUP:
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 | -- 计算department_id和job_id的所有组合平均值.
| 10 | AD_ASST | 4400.000000 |
| 10 | NULL | 4400.000000 | -- 计算department_id和job_id的所有组合平均值.
| 20 | MK_MAN | 13000.000000 |
| 20 | MK_REP | 6000.000000 |
| 20 | NULL | 9500.000000 | -- 计算department_id和job_id的所有组合平均值.
| 30 | PU_CLERK | 2780.000000 |
| 30 | PU_MAN | 11000.000000 |
| 30 | NULL | 4150.000000 | -- 计算department_id和job_id的所有组合平均值.
| ... | ... | ... |
| NULL | NULL | 6461.682243 | -- 总汇为所有department_id和job_id组合的平均值.
-----------------------------------------
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)-- 5.7版本:
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 BY3. 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子句, 因为它的执行效率更高.-- 查询各部门最高工资大于10000的部门信息:
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 基本使用
-- 查询部门id为10, 20, 30, 40这4个部门中最高工资大于10000高的部门信息:
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)-- 低效的方式:
-- 当过滤条件中没有聚合函数时, 则此过滤条件声明在WHERE中或HAVING中都可以. 但是声明在WHERE中效率更高.
-- 先分组后过滤: 如果分组的数据量特别大, 那么首先进行分组可能会消耗较多的计算资源.
-- 先过滤后分组: 滤掉不需要的数据, 然后再进行分组, 这可以减少分组操作的数据量, 从而提高查询效率.
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...-- sql92语法
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;-- sql99语法
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. HAVINGHAVING子句用于过滤分组后的结果.它类似于WHERE子句, 但作用于分组后的数据, 而不是原始的记录.* 5. SELECT在此阶段, 数据库会选择要显示的列. 这包括直接选择的列和通过聚合函数计算得到的列.* 6. DISTINCT如果在SELECT子句中使用了DISTINCT关键字, 数据库会去除结果集中的重复行.* 7. ORDER BYORDER BY子句会对结果集进行排序.可以按照一列或多列进行排序, 并指定升序(ASC)或降序(DESC).* 8. LIMIT最后, LIMIT子句用于限制返回的记录数. 这对于分页查询特别有用, 因为它允许你只获取结果集的一个子集.需要注意的是, 虽然上述顺序是SQL语句的逻辑执行顺序, 但实际的物理执行计划可能会因数据库优化器的决策而有所不同.
优化器会根据表的大小, 索引的存在与否, 统计信息等因素来决定如何最有效地执行查询.执行顺序: FROM ... JOIN ... ON - WHERE - GROUP BY - HAVING - SELECT 的字段 - DISTINCT - ORDER BY - LIMITSELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
FROM player JOIN team ON player.team_id team.team_id # 顺序 1
WHERE height 1.80 # 顺序 2
GROUP BY player.team_id # 顺序 3
HAVING num 2 # 顺序 4
ORDER BY num DESC # 顺序 6
LIMIT 2 # 顺序 7在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子句.-- 2. 查询公司员工工资的最大值, 最小值, 平均值, 总和:
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)-- 3. 查询员工最高工资和最低工资的差距(别名difference):
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)-- 4. 查询各岗位(job_id)的员工工资的最大值, 最小值, 平均值, 总和:
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)-- 5. 统计各个岗位(job_id)的员工人数:
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)-- 6.查询各个管理者手下员工的最低工资, 其中最低工资不能低于6000, 没有管理者的员工不计算在内:
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)-- 7.查询所有部门的名字, 地址id(location_id), 员工数量和平均工资, 并按平均工资降序:
-- 两个表拼接需要查询所有部门的名字, 地址id(location_id), 这两个字段在第二张表, 那肯定是右连接(左表中可能存在NULL).
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)-- 8. 查询每个部名称呼, 岗位名称和最低工资:
-- 查询每个部名称呼先就使用右连接(左表中可能存在NULL).
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)
