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;
上一篇:HDU1556:Color the ball(简单的线段树区域更新)


下一篇:mysql 数据操作 单表查询 通过四则运算查询