操作的表
mysql> select * from 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 |
group by
group by : 按照某个字段或者某些字段进行分组。
having : having是对分组之后的数据进行再次过滤。
案例:找出每个工作岗位的最高薪资。
(找的是每一组的最高薪资,所以需要先进行分组)
select job,max(sal) from emp group by job;
+-----------+----------+
| job | max(sal) |
+-----------+----------+
| ANALYST | 3000.00 |
| CLERK | 1300.00 |
| MANAGER | 2975.00 |
| PRESIDENT | 5000.00 |
| SALESMAN | 1600.00 |
+-----------+----------+
先from,再group by,最后求max
分组函数总是在group函数结束之后再执行
注意:分组函数一般都会和group by联合使用,这也是为什么它被称为分组函数的原因。
并且任何一个分组函数(count sum avg max min)都是在group by语句执行结束之后才会执行的
当一条sql语句没有group by的话,整张表的数据会自成一组。
多字段分组查询
找出每个工作岗位的最高薪资
select ename,job,max(sal) from emp group by job;
这条语句能不能执行,Oracle不能,MySQL可以,但没意义
+-------+-----------+----------+
| ename | job | max(sal) |
+-------+-----------+----------+
| SCOTT | ANALYST | 3000.00 |
| SMITH | CLERK | 1300.00 |
| JONES | MANAGER | 2975.00 |
| KING | PRESIDENT | 5000.00 |
| ALLEN | SALESMAN | 1600.00 |
+-------+-----------+----------+
以上在mysql当中,查询结果是有的,但是结果没有意义,在Oracle数据库当中会报错。语法错误。
Oracle的语法规则比MySQL语法规则严谨。
记住一个规则:当一条语句中有group by的话,select后面只能跟分组函数和参与分组的字段。
每个工作岗位的平均薪资?
select job,avg(sal) from emp group by job;
+-----------+-------------+
| job | avg(sal) |
+-----------+-------------+
| ANALYST | 3000.000000 |
| CLERK | 1037.500000 |
| MANAGER | 2758.333333 |
| PRESIDENT | 5000.000000 |
| SALESMAN | 1400.000000 |
+-----------+-------------+
多个字段能不能联合起来一块分组?
案例:找出每个部门不同工作岗位的最高薪资。
先查看数据:select deptno,job,sal from emp order by deptno;
+--------+-----------+---------+
| deptno | job | sal |
+--------+-----------+---------+
| 10 | MANAGER | 2450.00 |
| 10 | PRESIDENT | 5000.00 |
| 10 | CLERK | 1300.00 |
| 20 | CLERK | 800.00 |
| 20 | MANAGER | 2975.00 |
| 20 | ANALYST | 3000.00 |
| 20 | CLERK | 1100.00 |
| 20 | ANALYST | 3000.00 |
| 30 | SALESMAN | 1600.00 |
| 30 | SALESMAN | 1250.00 |
| 30 | SALESMAN | 1250.00 |
| 30 | MANAGER | 2850.00 |
| 30 | SALESMAN | 1500.00 |
| 30 | CLERK | 950.00 |
+--------+-----------+---------+
select
deptno,job,max(sal)
from
emp
group by
deptno,job;
where和having的选择
找出每个部门的最高薪资,要求显示薪资大于2900的数据。
写SQL语句的时候,有时别幻想着一步就能写出来,有时候读一句,写一句。要分步骤来写。
第一步:找出每个部门的最高薪资
select max(sal),deptno from emp group by deptno;
+----------+--------+
| max(sal) | deptno |
+----------+--------+
| 5000.00 | 10 |
| 3000.00 | 20 |
| 2850.00 | 30 |
+----------+--------+
第二步:找出薪资大于2900
select max(sal),deptno from emp group by deptno having max(sal) > 2900; // 这种方式效率低。因为第一次结果就已经查出来了,还要计算分组函数
+----------+--------+
| max(sal) | deptno |
+----------+--------+
| 5000.00 | 10 |
| 3000.00 | 20 |
+----------+--------+
select max(sal),deptno from emp where sal > 2900 group by deptno; // 效率较高,建议能够使用where过滤的尽量使用where。
where已经提前过滤了。
where搞不定的我们就用having
找出每个部门的平均薪资,要求显示薪资大于2000的数据。
第一步:找出每个部门的平均薪资
select deptno,avg(sal) from emp group by deptno;
第二步:要求显示薪资大于2000的数据
select avg(sal),deptno from emp group by deptno having avg(sal)>2000;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
+--------+-------------+
where后面不能使用分组函数:
select deptno,avg(sal) from emp where avg(sal) > 2000 group by deptno; // 错误了。
这种情况只能使用having过滤。
having是groupby的搭档,只有groupby出现了,having才有意义
总结一个完整的DQL语句怎么写?
select 5
..
from 1
..
where 2
..
group by 3
..
having 4
..
order by 6
排序是最后的,排序最后输出。
去除重复记录
1、关于查询结果集的去重?
select DISTINCT job from emp; // distinct关键字去除重复记录。
+-----------+
| job |
+-----------+
| CLERK |
| SALESMAN |
| MANAGER |
| ANALYST |
| PRESIDENT |
+-----------+
mysql> select ename,distinct job from emp;
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 ‘distinct job from emp’ at line 1
以上的sql语句是错误的。
记住:distinct只能出现在所有字段的最前面。
select distinct deptno,job from emp;
+--------+-----------+
| deptno | job |
+--------+-----------+
| 20 | CLERK |
| 30 | SALESMAN |
| 20 | MANAGER |
| 30 | MANAGER |
| 10 | MANAGER |
| 20 | ANALYST |
| 10 | PRESIDENT |
| 30 | CLERK |
| 10 | CLERK |
+--------+-----------+
distinct放到最前面表示后面所有字段去除重复记录。这里表名去除deptno和job同时重复的去掉
案例:统计岗位的数量?
select count(distinct job) from emp;
去重之后统计数量
+---------------------+
| count(distinct job) |
+---------------------+
| 5 |
+---------------------+