当前位置：首页 > article >正文

数据库的DQL(3)

article 2025/1/21 17:31:55

数据库的DQL(3)

分组查询

在MySQL中，group by关键字可以根据一个或多个字段对查询结果进行分组

group by 字段名

1.分组函数

有时也叫聚合函数

count(): 查询表中的记录数量
avg(): 求平均值
sum(): 求和
max():求最大值
min():求最小值

案例1：


mysql> select count(empno),sum(sal),avg(sal) from emp;
+--------------+----------+-------------+
| count(empno) | sum(sal) | avg(sal)    |
+--------------+----------+-------------+
|           14 | 29025.00 | 2073.214286 |
+--------------+----------+-------------+
1 row in set (0.00 sec)

mysql> select  max(sal),min(sal) from emp;
+----------+----------+
| max(sal) | min(sal) |
+----------+----------+
|  5000.00 |   800.00 |
+----------+----------+
1 row in set (0.00 sec)

count函数主要功能是进行数据统计，但是在进行数据统计时，若果一张表中没有统计记录，那么coun()也会返回数据，只是这个数据是0

案例2：


mysql> select count(grade) from salgrade;
+--------------+
| count(grade) |
+--------------+
|            5 |
+--------------+
1 row in set (0.00 sec)

mysql> create table tb_emp1(id int(11),name varchar(25),deptid int(11),salary float);
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> select count(*) from tb_emp1;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select sum(id) from tb_emp1;
+---------+
| sum(id) |
+---------+
|    NULL |
+---------+
1 row in set (0.00 sec)

若果使用其他函数，则有可能返回null值，但是count()永远都会返回一个具体的数字

2.分组统计

当数据重复时分组才有意义，一个也可以单独分一组，但是没有意义。

案例1：


mysql> select deptno,count(empno),avg(sal) from emp group by deptno;
+--------+--------------+-------------+
| deptno | count(empno) | avg(sal)    |
+--------+--------------+-------------+
|     10 |            3 | 2916.666667 |
|     20 |            5 | 2175.000000 |
|     30 |            6 | 1566.666667 |
+--------+--------------+-------------+
3 rows in set (0.00 sec)

案例2：


mysql> select job,max(sal),min(sal) from emp group by job;
+-----------+----------+----------+
| job       | max(sal) | min(sal) |
+-----------+----------+----------+
| CLERK     |  1300.00 |   800.00 |
| SALESMAN  |  1600.00 |  1250.00 |
| MANAGER   |  2975.00 |  2450.00 |
| ANALYST   |  3000.00 |  3000.00 |
| PRESIDENT |  5000.00 |  5000.00 |
+-----------+----------+----------+
5 rows in set (0.00 sec)

1.分组函数可以在没有分组的时候单独使用，可是不能出现其他的查询字段：

案例3：

#正确用法

mysql> select count(empno) from emp;
+--------------+
| count(empno) |
+--------------+
|           14 |
+--------------+
1 row in set (0.00 sec)

##错误用法
mysql> select empno count(empno) from emp;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'count(empno) from emp' at line 1

2.若果要进行分组，则select子句后，只能出现分组的字段和统计函数，其他字段不能出现：

案例4：

#正确用法
  
mysql> select job ,count(empno),avg(sal) from emp group by job;
+-----------+--------------+-------------+
| job       | count(empno) | avg(sal)    |
+-----------+--------------+-------------+
| CLERK     |            4 | 1037.500000 |
| SALESMAN  |            4 | 1400.000000 |
| MANAGER   |            3 | 2758.333333 |
| ANALYST   |            2 | 3000.000000 |
| PRESIDENT |            1 | 5000.000000 |
+-----------+--------------+-------------+
5 rows in set (0.00 sec)

##错误用法
mysql> select empno job ,count(empno),avg(sal) from emp group by job;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.emp.empno' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

3.分组函数允许嵌套，只不过需要通过字查询的方式实验：

案例5：

#按照职位分组，统计平均工资最高的工资
#先统计出各个职位的平均工资

mysql> select job ,avg(sal) from emp group by job;
+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| CLERK     | 1037.500000 |
| SALESMAN  | 1400.000000 |
| MANAGER   | 2758.333333 |
| ANALYST   | 3000.000000 |
| PRESIDENT | 5000.000000 |
+-----------+-------------+
5 rows in set (0.00 sec)

#查询平均工资最高的工资

mysql> select max(avgtb.avgsal ) from (select job,avg(sal) as avgsal from emp group by job) as avgtb;
+--------------------+
| max(avgtb.avgsal ) |
+--------------------+
|        5000.000000 |
+--------------------+
1 row in set (0.00 sec)

3.分组过滤

在MySQL中，可以使用having关键字对分组后的数据进行过滤

having 查询条件

HAVING 关键字和 WHERE 关键字都可以用来过滤数据，且 HAVING 支持 WHERE 关键字中所有的操作符和语法。

但是 WHERE 和 HAVING 关键字也存在以下几点差异：

一般情况下，WHERE 用于过滤数据行，而 HAVING 用于过滤分组。

WHERE 查询条件中不可以使用聚合函数，而 HAVING 查询条件中可以使用聚合函数。

WHERE 在数据分组前进行过滤，而 HAVING 在数据分组后进行过滤。
WHERE 针对数据库文件进行过滤，而 HAVING 针对查询结果进行过滤。也就是说，WHERE 根据数据表中的字段直接进行过滤，而 HAVING 是根据前面已经查询出的字段进行过滤。
WHERE 查询条件中不可以使用字段别名，而 HAVING 查询条件中可以使用字段别名。

案例1：


mysql> select ename,sal from emp having sal>1500;
+-------+---------+
| ename | sal     |
+-------+---------+
| ALLEN | 1600.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+
7 rows in set (0.00 sec)

案例2：



mysql> select job,max(sal) from emp group by job;
+-----------+----------+
| job       | max(sal) |
+-----------+----------+
| CLERK     |  1300.00 |
| SALESMAN  |  1600.00 |
| MANAGER   |  2975.00 |
| ANALYST   |  3000.00 |
| PRESIDENT |  5000.00 |
+-----------+----------+
5 rows in set (0.00 sec)

mysql> select job,max(sal) from emp group by job having job='manager';
+---------+----------+
| job     | max(sal) |
+---------+----------+
| MANAGER |  2975.00 |
+---------+----------+
1 row in set (0.00 sec)