文章目录
- AVG 计算平均值
- SUM 求和
- MAX 获取一组值中的最大值
- MIN 获取一组值中的最小值
- COUNT 计算表中有多少条记录
- GROUP BY 根据一个或多个列对结果集进行分组
- HAVING 为行分组或聚合组指定过滤条件
- ORDER BY 排序
- SQL底层执行原理
- SELECT 语句的完整结构
- SELECT SQL语句的执行过程
AVG 计算平均值
只适用于数值类型的字段(或变量)
SELECT AVG(salary),AVG(salary) * 107 FROM employees;
SUM 求和
只适用于数值类型的字段(或变量)
SELECT SUM(salary) FROM employees;
MAX 获取一组值中的最大值
适用于数值类型、字符串类型、日期时间类型的字段(或变量)
SELECT MAX(salary) FROM employees;
MIN 获取一组值中的最小值
适用于数值类型、字符串类型、日期时间类型的字段(或变量)
SELECT MIN(salary) FROM employees;
COUNT 计算表中有多少条记录
计算指定字段在查询结构中出现的个数(不包含NULL值的)
SELECT COUNT(employee_id),COUNT(salary),COUNT(2 * salary),COUNT(1),COUNT(2),COUNT(*) FROM employees ;#如果计算表中有多少条记录,如何实现?
#方式1:COUNT(*)
#方式2:COUNT(1)
#方式3:COUNT(具体字段) : 不一定对!#② 注意:计算指定字段出现的个数时,是不计算NULL值的。
SELECT COUNT(commission_pct) FROM employees;#③ 公式:AVG = SUM / COUNT (都不计算NULL值)
SELECT AVG(salary),SUM(salary)/COUNT(salary),
AVG(commission_pct),SUM(commission_pct)/COUNT(commission_pct),SUM(commission_pct) / 107
FROM employees;#需求:查询公司中平均奖金率
#错误的!(举例AVG会过滤空值,公司有20个人没有参加抽奖,但是也要算)
SELECT AVG(commission_pct)FROM employees;#正确的:
SELECT SUM(commission_pct) / COUNT(IFNULL(commission_pct,0)),AVG(IFNULL(commission_pct,0))
FROM employees;
count(*) 与 count(具体字段) 效率
如果使用的是MyISAM 存储引擎,则三者效率相同,都是O(1)
如果使用的是InnoDB 存储引擎,则三者效率:COUNT(*) = COUNT(1)> COUNT(字段)
在 MySQL 中统计数据表的行数,可以使用三种方式:SELECT COUNT(*)、SELECT COUNT(1)和SELECT COUNT(具体字段),使用这三者之间的查询效率是怎样的?
在 MvSOL InnoDB 存储引警中,COUNT(*)和 COUNT(1)都是对所有结果进行 COUNT。如果有 WHERE 子句,则是对所有符合筛选条件的数据行进行统计;如果没有 WHERE 子句,则是对数据表的数据行数进行统计。因此 COUNT(*)和 COUNT(1)本质上并没有区别,执行的复杂度都是O(N),也就是采用全表扫描,进行循环+计数的方式进行统计。
如果是 MySQL MyISAM 存储引擎,统计数据表的行数只需要O(1)的复杂度,这是因为每张 MyISAM 的数据表都有一个 meta 信息存储了 row_count 值,而一致性则由表级锁来保证。因为 InnoDB 支持事务,采用行级锁和 MVCC机制,所以无法像 MyISAM 一样,只维护一个row_count 变量,因此需要采用扫描全表,进行循环+计数的方式来完成统计。
需要注意的是,在实际执行中,COUNT(*)和 COUNT(1)的执行时间可能略有差别,不过你还是可以把它俩的执行效率看成是相等的。
另外在 InnoDB 引擎中,如果采用 COUNT(*)和 COUNT(1)来统计数据行数,要尽量采用二级索引。因为主键采用的索引是聚簇索引,聚簇索引包含的信息多,明显会大于二级索引(非聚簇索引)。对于 COUNT(*)和COUNT(1)来说,它们不需要查找具体的行,只是统计行数,系统会自动采用占用空间更小的二级索引来进行统计。
GROUP BY 根据一个或多个列对结果集进行分组
#需求:查询各个部门的平均工资,最高工资
SELECT department_id,AVG(salary),SUM(salary) FROM employees GROUP BY department_id#需求:查询各个department_id,job_id的平均工资
#方式1:
SELECT department_id,job_id,AVG(salary) FROM employees GROUP BY department_id,job_id;
#方式2:
SELECT job_id,department_id,AVG(salary) FROM employees GROUP BY job_id,department_id;#结论1:SELECT中出现的非组函数的字段必须声明在GROUP BY 中。
# 反之,GROUP BY中声明的字段可以不出现在SELECT中。
#结论2:GROUP BY 声明在FROM后面、WHERE后面,ORDER BY 前面、LIMIT前面
#结论3:MySQL中GROUP BY中使用WITH ROLLUP 【计算全体平均工资】
SELECT department_id,AVG(salary) FROM employees GROUP BY department_id WITH ROLLUP;
HAVING 为行分组或聚合组指定过滤条件
#要求1:如果过滤条件中使用了聚合函数,则必须使用HAVING来替换WHERE。否则,报错。
#要求2:HAVING 必须声明在 GROUP BY 的后面。
#要求3:开发中,我们使用HAVING的前提是SQL中使用了GROUP BY
SELECT department_id,MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary) > 10000;#练习:查询部门id为10,20,30,40这4个部门中最高工资比10000高的部门信息
#方式1:推荐,执行效率高于方式2.
SELECT department_id,MAX(salary) FROM employees WHERE department_id IN (10,20,30,40)
GROUP BY department_id HAVING MAX(salary) > 10000;#方式2:
SELECT department_id,MAX(salary) FROM employees GROUP BY department_id
HAVING MAX(salary) > 10000 AND department_id IN (10,20,30,40);#结论:当过滤条件中有聚合函数时,则此过滤条件必须声明在HAVING中。
# 当过滤条件中没有聚合函数时,则此过滤条件声明在WHERE中或HAVING中都可以。但是,建议大家声明在WHERE中。
# WHERE 与 HAVING 的对比
1. 从适用范围上来讲,HAVING的适用范围更广。
2. 如果过滤条件中没有聚合函数:这种情况下,WHERE的执行效率要高于HAVING
ORDER BY 排序
#需求:查询各个部门的平均工资,按照平均工资升序排列
SELECT department_id,AVG(salary) avg_sal FROM employees GROUP BY department_id ORDER BY avg_sal ASC;#说明:当使用ROLLUP时,不能同时使用ORDER BY子句进行结果排序,即ROLLUP和ORDER BY是互相排斥的。
#错误的:
SELECT department_id,AVG(salary) avg_sal FROM employees GROUP BY department_id WITH ROLLUP ORDER BY avg_sal ASC;
SQL底层执行原理
SELECT 语句的完整结构
#sql92语法:
SELECT ....,....,....(存在聚合函数)
FROM ...,....,....
WHERE 多表的连接条件 AND 不包含聚合函数的过滤条件
GROUP BY ...,....
HAVING 包含聚合函数的过滤条件
ORDER BY ....,...(ASC / DESC )
LIMIT ...,....#sql99语法:
SELECT ....,....,....(存在聚合函数)
FROM ...
(LEFT / RIGHT)JOIN ....ON 多表的连接条件
(LEFT / RIGHT)JOIN ... ON ....
WHERE 不包含聚合函数的过滤条件
GROUP BY ...,....
HAVING 包含聚合函数的过滤条件
ORDER BY ....,...(ASC / DESC )
LIMIT ...,....
SELECT SQL语句的执行过程
FROM ...,...-> ON -> (LEFT/RIGNT JOIN) -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT