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 表示聚合函數

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的使用 🔥

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 valuemysql> 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)