数据库学习2024/3/16

聚合函数

1.常见的聚合函数

1.1 AVG / SUM：只适用于数值类型的字段（或变量）,对字符串和日期是无意义的

SELECT AVG(salary),SUM(salary), AVG(salary) *107

FROM employees;

1.2 MAX / MIN：适用数值类型、字符串类型、时间日期类型的字段（或变量）

SELECT MAX(salary),MIN(salary),MAX(last_name),MIN(last_name),MAX(hire_date),MIN(hire_date)

FROM employees;

1.3COUNT:

①作用：计算指定字段在查询结构中出现的个数(不包含NULL)

SELECT COUNT(employee_id),COUNT(1),COUNT(*)

FROM employees;

案例：如果要计算表中有多少条记录，如何实现？

方式1：count(*) 方式2：count(常数)

方式1：count(具体字段)：不一定对的！！！

SELECT COUNT(employee_id),COUNT(1),COUNT(*)

FROM employees;

②注意：计算指定字段出现的个数时，是不计算NULL值的

SELECT COUNT(commission_pct),COUNT(department_id)

FROM employees;

③ AVG = SUM / COUNT(自动过滤NULL)

SELECT AVG(commission_pct),SUM(commission_pct)/COUNT(commission_pct),

SUM(commission_pct)/ 107

FROM employees;

案例：查询公司中平均奖金率

错误的

SELECT AVG(commission_pct) FROM employees;

正确的

SELECT SUM(commission_pct)/COUNT(IFNULL(commission_pct,0)),AVG(IFNULL(commission_pct,0)) FROM employees;

案例：如果要计算表中有多少条记录那个效率高？

如果是MyISAM的存储引擎，则三者效率相同

如果是InnoDB的存储引擎，则count(*)=count(数值)>count(具体字段)

其他：方差、标准差、中位数

2.GROUP BY的使用

单组

案例：查询各个部门的平均工资：

SELECT department_id,AVG(salary)

FROM employees

GROUP BY department_id;

多组

案例：查询各个部门和job_id的平均工资：

SELECT department_id,job_id,AVG(salary)

FROM employees

GROUP BY department_id,job_id;

#或

SELECT job_id,department_id,AVG(salary)

FROM employees

GROUP BY job_id,department_id;

#错误的！！

SELECT job_id,department_id,AVG(salary)

FROM employees

GROUP BY job_id;

结论1：SELECT中出现的非组函数的字段必须出现在GROUP BY中。

 反之，GROUP BY中声明的字段可以不出现在SELECT中。

结论2：GROUP BY 声明在FROM和WHERE后面，在ORDER BY 和LIMIT前面

结论3：当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。

SELECT job_id,department_id,AVG(salary)

FROM employees

GROUP BY job_id,department_id WITH ROLLUP

ORDER BY AVG(salary);

3.HAVING的使用(过滤数据)

结论1：如果过滤条件中使用了聚合函数，则必须使用HAVING来替换WHERE,否则会报错

结论2：HAVING声明必须在GROUP BY后面

结论3：开发中我们使用HAVING的全前提是使用了GROUP BY

结论4：当过滤条件中有聚合函数，则必须使用HAVING

 当过滤条件中没有聚合函数，则可以使用HAVING或WHERE,
 建议使用WHERE,因为效率高

HAVING与WHERE的对比：

1.从适用范围：HAVING比WHERE适用范围更广

2.如果过滤条件中没有聚合函数，WHERE比HAVING效率更高

4.SQL的底层执行原理

SQL的基本语法

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 ...,...

SQL语句的执行过程()中是可能有的

FROM...,...-> (ON) ->(LEFT/RIGHT JOIN) -> WHERE ->GROUP BY ->HAVING ->SELECT ->DISTINCT(去重)->ORDER BY ->LIMIT

数据库学习笔记