MySQL GROUP BY和HAVING的使用 2022/09/09

🔥group by应用场景🔥

我们已经掌握使用select语句结合where查询条件获取需要的数据，但在实际的应用中，还会遇到下面这类需求，又该如何解决？

1、公司想知道每个部门有多少员工

2、班主任想统计各科成绩的第一名

3、某门店想掌握男、女性会员的人数和平均年龄

🔥group by的使用🔥

 从字面上理解，group by表示根据某种规则对数据进行分组，它必须配合聚合函数进行使用，对数据进行分组后可以进行count、sum、avg、max和min等运算。

group by语法

SELECT column_name,aggregate_function(column_name) FROM table_name GROUP BY column_name

说明：

1、aggregate_function 表示聚合函数

2、group by 可以对一列或多列进行分组

mysql> alter table employee add column dept varchar(20) comment '部门名称';
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> desc employee;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int         | NO   | PRI | NULL    | auto_increment |
| name   | varchar(30) | YES  |     | NULL    |                |
| sex    | varchar(1)  | YES  |     | NULL    |                |
| salary | int         | YES  |     | NULL    |                |
| dept   | varchar(20) | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
 
mysql> update employee set dept='部门A' where id='1';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> update employee set dept='部门B' where id='2';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> update employee set dept='部门B' where id='3';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select * from employee;
+----+--------+------+--------+-------+
| id | name   | sex  | salary | dept  |
+----+--------+------+--------+-------+
|  1 | 张三   | 男   |   5500 | 部门A |
|  2 | 李四   | 男   |   4500 | 部门B |
|  3 | 张小妹 | 女   |   4500 | 部门B |
+----+--------+------+--------+-------+
3 rows in set (0.00 sec)

select sex,count(*) from employee group by sex;
+------+----------+
| sex  | count(*) |
+------+----------+
| 男   |        2 |
| 女   |        1 |
+------+----------+
2 rows in set (0.00 sec)

 select dept,count(*) from employee group by dept;
+-------+----------+
| dept  | count(*) |
+-------+----------+
| 部门A |        1 |
| 部门B |        2 |
+-------+----------+
2 rows in set (0.00 sec)

select dept,sum(salary) from employee group by dept;
+-------+-------------+
| dept  | sum(salary) |
+-------+-------------+
| 部门A |        5500 |
| 部门B |        9000 |
+-------+-------------+
2 rows in set (0.00 sec)

select dept,sum(salary) from employee group by dept;
+-------+-------------+
| dept  | sum(salary) |
+-------+-------------+
| 部门A |        5500 |
| 部门B |        9000 |
+-------+-------------+
2 rows in set (0.00 sec)

 select dept,min(salary) from employee group by dept;
+-------+-------------+
| dept  | min(salary) |
+-------+-------------+
| 部门A |        5500 |
| 部门B |        4500 |
+-------+-------------+
2 rows in set (0.00 sec)

🔥 having的使用 🔥

在SQL中增加HAVING子句原因是，WHERE 关键字无法与聚合函数一起使用。HAVING子句可以对分组后的各数据进行筛选。

having语法

SELECT column_name，aggregate_function(column_name)
 FROM table_name WHERE column_name operator value 
GROUP BY column_name HAVING aggregate_function(column_name) operator value

mysql> select dept, count(*) from employee group by dept;
+-------+----------+
| dept  | count(*) |
+-------+----------+
| 部门A |        1 |
| 部门B |        2 |
+-------+----------+
2 rows in set (0.00 sec)
 
mysql> select dept, count(*) from employee group by dept having count(*)<2;
+-------+----------+
| dept  | count(*) |
+-------+----------+
| 部门A |        1 |
+-------+----------+
1 row in set (0.00 sec)
 
mysql> select dept, count(*) from employee group by dept having count(*)<3;
+-------+----------+
| dept  | count(*) |
+-------+----------+
| 部门A |        1 |
| 部门B |        2 |
+-------+----------+
2 rows in set (0.00 sec)

mysql> select dept, max(salary) from employee group by dept;
+-------+-------------+
| dept  | max(salary) |
+-------+-------------+
| 部门A |        5500 |
| 部门B |        4500 |
+-------+-------------+
2 rows in set (0.00 sec)
 
mysql> select dept,max(salary) from employee group by dept having max(salary)>=5000;
+-------+-------------+
| dept  | max(salary) |
+-------+-------------+
| 部门A |        5500 |
+-------+-------------+
1 row in set (0.00 sec)