DQL语句之分组与过滤

我会带着你远行 2022-09-09 00:22 115阅读 0赞

### DQL语句之分组与过滤 ###

*  说明：以下将使用mysql数据库管理系统中的mysql数据库中的emp表作为例子
 *  一、分组函数
 *   *  1.所有的分组函数都是对"某一组"数据进行操作的且输入多行，最终输出的结果为一行
     *  2.多行处理函数自动忽略空(null)
     *  3.分组函数一般都会和group by联合使用，这也是为什么它被称为分组函数的原因。
     *  说明：1.count (\*)和count (具体的某个字段)，他们有什么区别?
     *  小tips：分组函数也能组合起来用
 *  单行处理函数
 *  二、group by(分组查询)与having(过滤)
 *   *  重点：关于SQL语句的执行顺序问题
     *  1.group by 的用法
     *  多字段分组查询
     *  having 与 where
     *   *  使用where 与使用 having 没有区别的。
         *  必须要使用 having的
 *  三、去除重复记录
 *   *  联合去重

--------------------

# 说明：以下将使用mysql数据库管理系统中的mysql数据库中的emp表作为例子 #

本次内容接上次的学习笔记：  
[SQL中的DQL语句二(学习笔记三)][SQL_DQL]

# 一、分组函数 #

分组函数又叫多行处理函数，多行处理函数只有5个;  
avg 求平均  
max 求最大值  
min 求最小值  
count 求一列有几个数  
sum 求和  
有以下几个特点：

## 1.所有的分组函数都是对"某一组"数据进行操作的且输入多行，最终输出的结果为一行 ##

例：求出工资的最大值

select max(sal) from emp;

输出结果：

+----------+
    | max(sal) |
    +----------+
    |  5000.00 |
    +----------+

例：

select ename,max(sal) from emp;

输出结果：

ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'mysql.emp.ENAME'; this is incompatible with sql_mode=only_full_group_by

## 2.多行处理函数自动忽略空(null) ##

**注**：**所有**数据库都是这样规定的，只要有**NULL**参与的运算结果一定是**NULL**

emp的表是这样的：

+-------+--------+-----------+------+------------+---------+---------+--------+
    | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
    +-------+--------+-----------+------+------------+---------+---------+--------+
    |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
    |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
    |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
    |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
    |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
    |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
    |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
    |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
    |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
    |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
    |  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
    |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
    |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
    |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
    +-------+--------+-----------+------+------------+---------+---------+--------+

comm(补贴，福利)中的null是比较多的，我们以comm为例：  
比如说，给每个员工涨10元补贴

select ename,(comm+10) as comm  from emp;

输出结果：

可以看到，所有带NULL的 加上10后都没有变化，这个非常直观的说明了只要有**NULL**参与的运算结果一定是**NULL**

求出comm的平均值；

select avg(comm) from emp;

输出结果

+------------+
    | avg(comm)  |
    +------------+
    | 550.000000 |
    +------------+

这个例子也非常直观的说明了多行处理函数自动忽略空(NULL)

**注**：这里特别说明一下，因为多行处理函数是自动忽略NULL的，所以，一定不要这样写：

select sum(comm) from emp where comm is not null;

在使用多行处理行数时，**不要**在后面加where xxxx is not null  
程序不会报错，但是画蛇添足，可以写但是完全没有必要。

## 3.分组函数一般都会和group by联合使用，这也是为什么它被称为分组函数的原因。 ##

任何一个分组函数(count sum avg max min)都是在group by语句执行结束之后才会执行的。

没有写group by 相当于java的缺省一样，是会存在的，以整个表为一组。group by下面会讲。

## 说明：1.count (\*)和count (具体的某个字段)，他们有什么区别? ##

count(\*) :不是统计某个字段中数据的个数，而是统计总记录条数。(和某个字段无关)  
count(具体的某个字段):表示统计具体的某个字段中不为NULL的数据总数量。

## 小tips：分组函数也能组合起来用 ##

select count(\*) , sum(sal) ,avg(sal) ,max (sal) ,min(sal) from emp;

# 单行处理函数 #

单行处理函数比较多，如果在这里一一赘述就会显得这篇博文太长了，这里只举个例子。  
这里放一个其他人的博文链接：  
[MySQL学习笔记(2)–数据处理函数/单行处理函数][MySQL_2]

单行处理函数的特点与多行处理函数不同，它是**输入一行，输出一行。**  
回顾一下这个：**所有**数据库都是这样规定的，只要有**NULL**参与的运算结果一定是**NULL**

那举个例子：求出所有员工的年薪：  
因为上面这个原因，我们不能直接求出来,所以，这个时候引出我们的新函数 ifnull()空处理函数

ifnull是用法是：  
ifnull(可能为NULL的数据,被当做什么处理)

那我们就可以这样写：

select ename,(sal+ifnull(comm,0))*12 from emp;

输出结果：

+--------+-------------------------+
    | ename  | (sal+ifnull(comm,0))*12 |
    +--------+-------------------------+
    | SMITH  |                 9600.00 |
    | ALLEN  |                22800.00 |
    | WARD   |                21000.00 |
    | JONES  |                35700.00 |
    | MARTIN |                31800.00 |
    | BLAKE  |                34200.00 |
    | CLARK  |                29400.00 |
    | SCOTT  |                36000.00 |
    | KING   |                60000.00 |
    | TURNER |                18000.00 |
    | ADAMS  |                13200.00 |
    | JAMES  |                11400.00 |
    | FORD   |                36000.00 |
    | MILLER |                15600.00 |
    +--------+-------------------------+

# 二、group by(分组查询)与having(过滤) #

group by：按照某个字段或者某些字段进行分组。  
having：having是对分组之后的数据进行再次过滤

## 重点：关于SQL语句的执行顺序问题 ##

select    5
    ...
    from      1
    ...
    where     2
    ....
    group by  3 
    ....
    having    4
    ....
    order by  6
    ...
    
    
    
    注：分组函数是在group by之后执行
    没有写group by 时，实际上相当于默认存在，以整个表作为一个组

这个时候，就会出现一些问题，比如说这样：  
选出大于平均工资的员工：

select ename,sal from emp where sal > avg(sal);
    
    就会报错：
    ERROR 1111 (HY000): Invalid use of group function

正确的写法应该是：

select ename,sal from emp where sal > (select avg(sal) from emp);
    
    或者，求出平均工资后，再将平均工资代入where sal > xxx中。

注：注意这个书写顺序与执行顺序，很容易搞错。  
还有除了select from 是必须写的外的，其他应该根据需求进行组合。

## 1.group by 的用法 ##

group by：按照某个字段或者某些字段进行分组。  
**注意**：分组函数一般都会和group by联合使用，这也是为什么它被称为分组函数的原因。  
并且任何一个分组函数(count sum avg max min)都是在group by语句执行结束之后才会执行的。

案例:找出每个工作岗位的最高薪资。

select max(sal),job from emp group by job;
    
    
    输出结果：
    +----------+-----------+
    | max(sal) | job       |
    +----------+-----------+
    |  1300.00 | CLERK     |
    |  1600.00 | SALESMAN  |
    |  2975.00 | MANAGER   |
    |  3000.00 | ANALYST   |
    |  5000.00 | PRESIDENT |
    +----------+-----------+

那能不能找出这些工资对应的人呢？

select ename，max(sal),job from emp group by job;
    
    ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mysql.emp.ENAME' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
    
    是不行的

注意：当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段。

## 多字段分组查询 ##

这是一个比较重要的点，一般来说，把需求拆分，理清思路，并不是很难.  
举例：  
找出每个部门不同工作岗位的最高薪资。

在这里我们就要将需求拆解，理清思路。

首先，我们还是要参照一下，前面的执行顺序。在这里我在放过来。

select    5
    ...
    from      1
    ...
    where     2
    ....
    group by  3 
    ....
    having    4
    ....
    order by  6
    
    注：分组函数是在group by之后执行

先where 进行筛选，然后是group by 进行分组，其次可以对过滤分组后的表再次进行筛选，最后进行排序。

那么，这个东西就好写了。

找出每个部门不同工作岗位的最高薪资。

首先，我们可以先分组，这里需要对两种数据进行分组，第一个是部分，第二个是工作岗位。

select job,deptno from emp group by deptno,job;

然后再找出每个部门不同工作岗位的最高薪资。

select job,deptno,max(sal) from emp group by deptno,job order by deptno;

+-----------+--------+----------+
    | job       | deptno | max(sal) |
    +-----------+--------+----------+
    | CLERK     |     10 |  1300.00 |
    | MANAGER   |     10 |  2450.00 |
    | PRESIDENT |     10 |  5000.00 |
    | ANALYST   |     20 |  3000.00 |
    | CLERK     |     20 |  1100.00 |
    | MANAGER   |     20 |  2975.00 |
    | CLERK     |     30 |   950.00 |
    | MANAGER   |     30 |  2850.00 |
    | SALESMAN  |     30 |  1600.00 |
    +-----------+--------+----------+

## having 与 where ##

having 和 where 两者的作用都是对表进行筛选，两者的区别在于 where 在group by 之前， having 在group by 之后。也就是说，  
当需要在分组前进行筛选时，使用where；  
当需要在分组后筛选时，选择having；  
当无论分组前筛选，还是分组后筛选没有区别时，选择 having 与 选择 where 的最终结果是没有区别的。

这里建议此时选择使用 where ，因为当你筛选过后，group by 进行分组需要操作的量就小了。

这里来几个例子：

### 使用where 与使用 having 没有区别的。 ###

找出每个部门的最高薪资，要求显示薪资大于 2900 的数据。  
这里就可以有两种写法。

select max(sal),deptno from emp group by deptno having max(sal) > 2900;
     
    select max(sal),deptno from emp where sal > 2900 group by deptno;

### 必须要使用 having的 ###

例：  
找出每个部门的平均薪资，要求显示薪资大于2000的数据。

select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;

# 三、去除重复记录 #

去重需要用到另外一个关键字 distinct

例：筛选出不同的工作岗位。

select distinct job from emp;