|
目录
15,分组函数（多行处理函数）
15,1分组函数在使用的时候需要注意那些？
16,分组查询（非常重要）
16.1什么是分组查询？
16.2将之前的关键字全部组合在一起，来看一下他们的执行顺序
16.3找出每个工作岗位的工资和？
16.4找出每个部门的最高薪资？
16.5找出每个部门中不同工作岗位的最高薪资？
16.6使用having可以对分完组之后的数据进一步过滤
16.7where完成不了，需要having解决的
20,大总结（单表的查询学完了）
20,1关键字顺序
20,2执行顺序：
20,3流程：
15,分组函数（多行处理函数）
多行处理函数的特点特点：输入多行，最终输出一行
5个函数：
????????count 计数
????????sum 求和
????????avg 平均值
????????max 最大值
????????min 最小值
注意：
分组函数在使用的时候必须先进行分组，然后才能用
如果你没有对数据进行分组，整张表默认为一组
例：找出最高工资？
mysql> select max(sal) from emp;
+---------------+
| max(sal)? ? |
+---------------+
| ?5000.00? ? |
+---------------+
例：找出最低工资？
mysql> select min(sal) from emp;
+--------------+
| min(sal)? ? ?|
+---------------+
| ??800.00? ? ?|
+---------------+
例：计算工资和？
mysql> select sum(sal) from emp;
+---------------+
| sum(sal)? ? |
+--------------+
| 29025.00? ?|
+--------------+
例：计算平均工资？
mysql> select avg(sal) from emp;
+------------------+
| avg(sal)? ? ? ? ? |
+-------------------+
| 2073.214286? |
+-------------------+
14个工资全部加起来，然后除以14
例：计算员工数量？
mysql> select count(ename) from emp;
+-----------------------+
| count(ename)? ? ?|
+----------------------+
| ??????????14? ? ? ? ? ? |
+----------------------+
15,1分组函数在使用的时候需要注意那些？
第一点：分组函数自动忽略NULL，你不需要提前对NULL进行处理
第二点：分组函数中count(*)和count(具体字段)有什么区别？
count(具体字段)：统计该字段下所有不为NULL的元素的总数
count(*)：统计表当中的总行数。（只要有一行数据count++）
因为每一行记录不可能都为NULL，一行数据中有一列不为NULL，则这行 数据就是有效的
第三点：分组函数不能够直接使用在where子句中
例：找出比最低工资高的员工信息
?mysql> select ename,sal from emp where sal > min(sal);
表面上没问题，但是运行会报错
?这个后面分组查询会说！
第四点：所有的分组函数可以组合起来一起用
mysql> select sum(sal),min(sal),max(sal),avg(sal),count(sal) from emp;
+-------------+----------------+---------------+--------------------+--------------+
| sum(sal)? ?| min(sal)? ? ? | max(sal)? ? | avg(sal)? ? ? ? ? ? | count(sal) |
+-------------+---------------+----------------+--------------------+---------------+
| 29025.00? ?| ??800.00? ?| ?5000.00? ? ?| 2073.214286? ? | ???14? ? ? ? ? ?|
+--------------+--------------+---------------+----------------------+---------------+
16,分组查询（非常重要）
16.1什么是分组查询？
在实际的应用中，可能有这样的需求，需要先进行分组，然后对每一组的数据进行 操作。这个时候我们需要使用分组查询，怎么进行分组查询呢？
select
????????...
from
????????...
group by
????????...
例：计算每个部门的工资和？
????????计算每个工作岗位的平均薪资？
????????找出每个工作岗位的最高薪资？
16.2将之前的关键字全部组合在一起，来看一下他们的执行顺序
select
????????...
from
????????...
where
????????...
group by
????????...
order by
????????...
以上关键字的顺序不能颠倒，需要记忆
执行顺序是什么:
????????1，from
????????2，where
????????3，group by
????????4，select
????????5，order by
这里我们就可以回答前面提出的问题了
为什么分组函数不能直接使用在where后面？
mysql> select ename,sal from emp where sal > min(sal);//报错
因为分组函数在使用的时候必须先分组之后才能使用
从上面的执行顺序可以看出，先执行的where，后执行的group by
where在执行的时候，还没有分组。所以where后面不能出现分组函数
mysql> select sum(sal)?from emp;
这个没有分组，为啥sum()函数可以用呢？
从上面的执行顺序可以看出，select在group by之后执行
语法格式上已经执行过了group by，虽然没有出现group by，但是默认分成了 一组
所以在执行select之前已经分好组了，所以可以这样用
16.3找出每个工作岗位的工资和？
实现思路：按照工作岗位分组，然后对工资求和
mysql> select job,sum(sal) from emp group by job;
+-----------------+--------------+
| job? ? ? ? ? ? ? ? | sum(sal) ?|
+-----------------+--------------+
| CLERK? ? ? ? ?| ?4150.00 |
| SALESMAN | ?5600.00 |
| MANAGER? ?| ?8275.00 |
| ANALYST? ? ?| ?6000.00 |
| PRESIDENT | ?5000.00 |
+----------------+---------------+
mysql> select ename,job,sum(sal) from emp group by job;//报错
因为是对job分组，和ename没有关系
比如说job有四种，ename有14种，这样是不能在一起分组的
重点结论：
在一条select语句当中，如果有group by语句的话
select后面只能跟：参加分组的字段，以及分组函数
其他的一律不能跟
16.4找出每个部门的最高薪资？
实现思路是什么：按照部门分组，然后对工资求最大值
mysql> select deptno,max(sal) from emp group by deptno;
+-------------+-------------+
| deptno? ? ? | max(sal) |
+-------------+-------------+
| ????20? ? ? ? | ?3000.00 |
| ????30? ? ? ? | ?2850.00 |
| ????10? ? ? ??| ?5000.00 |
+-------------+--------------+
16.5找出每个部门中不同工作岗位的最高薪资？
前面我们都是单独对一个字段进行分组，这里我们要对两个字段进行分组
技巧：两个字段联合成一个字段看
mysql> select deptno,job,max(sal) from emp group by deptno,job;
+-----------+------------------+--------------+
| deptno? ?| job? ? ? ? ? ? ? ? ?| max(sal) ?|
+-----------+------------------+-------------+
| ????20? ? ? | CLERK? ? ? ? ? | ?1100.00 |
| ????30? ? ? | SALESMAN ?| ?1600.00 |
| ????20? ? ? | MANAGER? ?| ?2975.00 |
| ????30? ? ? | MANAGER? ?| ?2850.00 |
| ????10? ? ?| MANAGER? ? | ?2450.00 |
| ????20? ? ? | ANALYST? ? ?| ?3000.00 |
| ????10? ? ?| PRESIDENT ?| ?5000.00 |
| ????30? ? ?| CLERK? ? ? ? ? ?| ??950.00 |
| ????10? ? ? | CLERK? ? ? ? ? | ?1300.00 |
+------------+-----------------+---------------+
16.6使用having可以对分完组之后的数据进一步过滤
注意：
having不能不能单独使用，having不能代替where，having必须和group by联合使用
例：找出每个部门的最高薪资，要求显示最高薪资大于3000的？
mysql> select deptno,max(sal) from emp group by deptno having max(sal)>3000;
+-----------+---------------+
| deptno? ?| max(sal) ?|
+-----------+---------------+
| ????10? ? ? ?| ?5000.00 |
+------------+--------------+
思考一个问题：以上的sql语句执行效率是不是低？
比较低，实际上可以这样考虑：先将大于3000的都找出来，然后再分组
先利用where将大于3000的找出来，在利用group by分组
mysql> select deptno,max(sal) from emp where sal>3000 group by deptno;
+------------+-------------+
| deptno? ?| max(sal) |
+-----------+-------------+
| ????10? ? ? ?| ?5000.00 |
+------------+--------------+
优化策略：where和having，优先选择where，where实在完成不了，再选择having
16.7where完成不了，需要having解决的
例：找出每个部门平均薪资，要求显示平均薪资高于2500的？
这个例子就能用where优化了，因为where不能筛选平均工资
所以这里只能用having优化
mysql> select deptno,avg(sal) from emp group by deptno having avg(sal)>2500;
+-----------+--------------------+
| deptno? ?| avg(sal)? ? ? ? ? |
+-----------+--------------------+
| ????10? ? ? | 2916.666667? |
+------------+-------------------+
20,大总结（单表的查询学完了）
20,1关键字顺序
select
????????...
where
????????...
group by
????????...
having
????????...
order by
????????...
以上关键字只能按照下面这个顺序来，不能颠倒！
20,2执行顺序：
- from
- where
- group by
- having
- select
- order by
20,3流程：
从某张表中查询数据
先经过where条件筛选选出有价值的数据
对这些有价值的数据进行分组
分组之后可以使用having继续筛选
select查询出来
最后排序输出
例：找出每个岗位的平均薪资，要求显示平均薪资大于1500的，除MANAGER之外
要求按照平均薪资降序输出
mysql> select job,avg(sal) from emp where job!="MANAGER" group by job having avg(sal)>1500 order by avg(sal) desc;
+------------------+------------------+
| job? ? ? ? ? ?? ? ? | avg(sal)? ? ? ? ?|
+------------------+------------------+
| PRESIDENT | 5000.000000 |
| ANALYST? ? ?| 3000.000000 |
+-----------------+------------------+
|