【MySQL — 数据库基础】深入解析MySQL的聚合查询

      

---

1. 聚合查询 

---

1.1 聚合函数 

---

函数	说明
COUNT ( [DISTINCT] expr)	返回查询到的数据的数量( 行数 )
SUM ( [DISTINCT] expr)	返回查询到的数据的总和，不是数字没有意义
AVG ( [DISTINCT] expr)	返回查询到的数据的平均值，不是数字没有意义
MAX( [DISTINCT] expr)	返回查询到的数据的最大值，不是数字没有意义
MIN ( [DISTINCT] expr)	返回查询到的数据的最小值，不是数字没有意义

注意： null 值在使用聚合函数时，大部分会被忽略掉 ；

---

【MySQL — 数据库增删改查操作】深入解析MySQL的 Retrieve 检索操作

---

---

1.2 COUNT 行数查询 

---

功能：计算行数。可以计算所有行的数量，也可以根据条件计算某列非NULL值的数量。

---

    语法：  

  SELECT COUNT(column_name) FROM table_name WHERE condition;

---

    COUNT  使用案例      

select count(*) from exam; +----------+
| count(*) |
+----------+
|        7 |
+----------+-- count() 用于查询 exam 的行数-- 先执行 select * from exam , 再根据上述的结果, 执行 countselect count(name) from exam; +-------------+
| count(name) |
+-------------+
|           7 |
+-------------+-- * 换成 name 效果相同select count (name) from exam;  
ERROR 1630 (42000)-- count(name) 是连在一起的, 不能加空格-- 如果数据中包含了null , 可能对上述count产生影响insert into exam values(null ,'唐三藏', 67 , 98 , 56);--插入 id 列为 null 的第一列数据select count(*) from exam;  +----------+
| count(*) |
+----------+
|        8 |
+----------+-- 使用 count(*) 不会影响结果select count(id) from exam;+-----------+
| count(id) |
+-----------+
|         7 |
+-----------+-- id  列有空值, 使用 count(列名) 对结果有影响
-- 其他列无空值, 使用 count(列名) 对结果无影响select count(distinct name) from exam; +----------------------+
| count(distinct name) |
+----------------------+
|                    7 |
+----------------------+-- 对 name 进行去重查询

---

1.3 SUM 总和查询

---

功能：计算某列的总和。仅适用于数值类型的列。

---

    语法     

  SELECT SUM(column_name) FROM table_name WHERE condition;

---

    SUM 使用案例       

select sum(English) from exam;
+--------------+
| sum(English) |
+--------------+
|        443.0 |
+--------------+
1 row in set (0.01 sec)-- 确保进行求和的列是数字select sum(name) from exam;
+-----------+
| sum(name) |
+-----------+
|         0 |
+-----------+
1 row in set, 7 warnings (0.00 sec)-- 对 name 求和，虽然不会报错，但是会根据求和列数给出警告show warnings;
+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '唐三藏'    |
| Warning | 1292 | Truncated incorrect DOUBLE value: '孙悟空'    |
| Warning | 1292 | Truncated incorrect DOUBLE value: '猪悟能'    |
| Warning | 1292 | Truncated incorrect DOUBLE value: '曹孟德'    |
| Warning | 1292 | Truncated incorrect DOUBLE value: '刘玄德'    |
| Warning | 1292 | Truncated incorrect DOUBLE value: '孙权'      |
| Warning | 1292 | Truncated incorrect DOUBLE value: '宋公明'    |
+---------+------+-----------------------------------------------+
7 rows in set (0.00 sec)-- 数据库在进行求和时，会把求和的列的值尝试转换成 double-- 如果字符串前半部分是数字，就能把前半部分 Truncated , 并且转换成 double-- 原则上不应该针对字符串进行求和操作，即使字符串有数字，也不一定可以进行算术运算-- null 和任意数值进行计算，结果都为 null ，但是 sum 比较特殊，遇到 null 直接跳过-- 如果有同学缺考，sum 的机制就不会让 null 参与运算，导致总成绩也为 nullselect sum(Chinese + Math + English ) from exam;
+--------------------------------+
| sum(Chinese + Math + English ) |
+--------------------------------+
|                         1548.0 |
+--------------------------------+
1 row in set (0.00 sec)-- sum 可以针对多列中所有的数字进行求和，也可以在求和后面添加筛选条件

---

1.4 AVG 平均数查询 

---

功能：计算某列的平均值。仅适用于数值类型的列。

---

    语法    

  SELECT AVG(column_name) FROM table_name WHERE condition;

---

    AVG 使用案例       

select avg(Chinese) from exam;
+--------------+
| avg(Chinese) |
+--------------+
|     74.85714 |
+--------------+
1 row in set (0.00 sec)

---

1.5 MAX 最大值查询 

---

功能：返回某列的最大值。

---

    语法    

  SELECT MAX(column_name) FROM table_name WHERE condition;

 

---

    SUM 使用案例       

select max(Chinese) from exam;
+--------------+
| max(Chinese) |
+--------------+
|         88.0 |
+--------------+
1 row in set (0.00 sec)

---

1.6 MIN 最小值查询    

---

select min(Chinese) from exam;
+--------------+
| min(Chinese) |
+--------------+
|         55.0 |
+--------------+
1 row in set (0.00 sec)

---

1.7 GROUP BY 分组查询 

---

 group by 是一个更复杂的聚合函数；使用group by指定一个列，就会把列的值相同的行归到一组中，分完组之后，还可以针对每个组，分别进行聚合查询 

---

    语法    

select 分组列 , 聚合函数  from 表名  group by 分组列  having  分组之后的条件

---

    构造数据    

create table emp( id int ,name varchar(20) , role varchar(20) , salary int) ;insert into emp values
(1, '张三', '程序员'  ,  10000  ),
(2, '李四', '程序员'  ,  11000  ), 
(3, '王五', '程序员'  ,  12000  ),
(4, '赵六', '产品经理',  8000   ),
(5, '田七', '产品经理',  9000   ),
(6, '周八', '老板'    ,  100000 );

---

---

---

•     简单分组查询    

select role , count(id) from emp group by role;-- select role   表示根据 emp 的 role 进行分组查询-- select role , count(id) 表示计算相同 role 的行数

---

---

select role , avg(salary), max(salary), min(salary) from emp group by role ;-- 表示根据 role 进行分组，分组后计算相同 role 的平均工资，最大薪资，最小薪资

---

---

•      搭配排序使用分组查询     

select role , avg(salary) from emp group by role order by avg(salary) desc;-- 根据 role 进行分组查询，对各个分组的平均薪资作降序排序

---

---

•      搭配条件筛选进行分组查询     

---

select role , avg(salary) from emp where name != '张三' group by role ;-- 分组前筛选掉名字为张三的记录，然后根据 role 进行分组，分组后求薪资平均值select role , avg(salary) as AvgSalary from emp where name != '张三' group by role ;-- 定义别名

---

---

分组之后的添加筛选条件 

---

select role , avg(salary) from emp where name != '张三' group by role having avg(salary) < 50000;-- 每个岗位 role 查询平均薪资，先筛选张三的记录，然后排除平均薪资高于 5w 的记录

---

---