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

1. 聚合查询 

1.1 聚合函数 

注意： 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 , 再根据上述的结果, 执行 count

 select 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 参与运算，导致总成绩也为 null


select 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 的记录