强调:
如果我们用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;