mysql 数据操作 单表查询 group by 聚合函数

 

 

强调：

如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义
多条记录之间的某个字段值相同，该字段通常用来作为分组的依据

 

如果按照每个字段都是唯一的进行分组，意味着按照这个表有多少条记录 就分多少组。没有意义

分组一定是 是 好多条记录 能够按照某个字段 只归为几类进行操作

 

 

 

四 聚合函数

max 最大值

min 最小值

avg 平均值

sum 求和

count 总数个数

 

# 需求 每个职位有多少个员工

 

没有where就不用写 对每个组进行 聚合函数的 统计 count

mysql> select count(id) from employee group by post;
+-----------+
| count(id) |
+-----------+
|         5 |
|         5 |
|         6 |
+-----------+
3 rows in set (0.01 sec)

 

 

执行顺序：

1.先找到表 from employee  没有过滤条件 

2.进行分组 对职位分组

3.交给 select count(id) 统计每组的职位 id个数

 

mysql> select post,count(id) as emp_count from employee group by post;
+-----------+-----------+
| post      | emp_count |
+-----------+-----------+
| operation |         5 |
| sale      |         5 |
| teacher   |         6 |
+-----------+-----------+
3 rows in set (0.00 sec)

 

# 取每个职位的最大工资

mysql> select post,max(salary) as emp_max from employee group by post;
+-----------+------------+
| post      | emp_max    |
+-----------+------------+
| operation |   20000.00 |
| sale      |    4000.33 |
| teacher   | 1000000.31 |
+-----------+------------+
3 rows in set (0.13 sec)

 

# 取每个职位的最小工资

 

mysql> select post,min(salary) as emp_min from employee group by post;
+-----------+----------+
| post      | emp_min  |
+-----------+----------+
| operation | 10000.13 |
| sale      |  1000.37 |
| teacher   |  2100.00 |
+-----------+----------+
3 rows in set (0.00 sec)

 

# 取每个职位的平均工资

mysql> select post,avg(salary) as emp_avg from employee group by post;
+-----------+---------------+
| post      | emp_avg       |
+-----------+---------------+
| operation |  16800.026000 |
| sale      |   2600.294000 |
| teacher   | 175766.718333 |
+-----------+---------------+
3 rows in set (0.00 sec)

 

# 取每个职位的年龄的总和

mysql> select post,sum(age) as emp_avg from employee group by post;
+-----------+---------+
| post      | emp_avg |
+-----------+---------+
| operation |     100 |
| sale      |     150 |
| teacher   |     263 |
+-----------+---------+
3 rows in set (0.00 sec)

 

 

#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组

示例：
    SELECT COUNT(*) FROM employee;
    SELECT COUNT(*) FROM employee WHERE depart_id=1;
    SELECT MAX(salary) FROM employee;
    SELECT MIN(salary) FROM employee;
    SELECT AVG(salary) FROM employee;
    SELECT SUM(salary) FROM employee;
    SELECT SUM(salary) FROM employee WHERE depart_id=3;