查询-having

count

select * from emp;
select count(*) from emp;  --返回emp表所有记录的个数
select count(deptno) from emp;  --返回值是14 这说明deptno重复的记录也被当做有效记录
select count(distinct deptno) from emp;  --返回值是3，统计deptno不重复的记录的个数
select count(comm) from emp;  --返回值是4 这说明comm为null的记录不会被当做有效的记录

select max(sal), min(sal)

group by

use scott;

--输出每个部门的编号和该部门的平均工资
select * from emp;
select deptno, avg(sal) "部门平均工资"
  from emp
  group by deptno

--判断下面语句是否正确  error
select deptno, avg(sal) as "部门平均工资", ename
  from emp
  group by deptno

--判断下面语句是否正确  error
select deptno, ename
  from emp
  group by deptno

总结：使用了group by之后 select中只能出现分组后的整体信息，不能出现组内的详细信息

group by a,b的用法
一定要明白下列语句是怎么错误的
--error
select deptno,job,sal
  from emp
  group by deptno,job

--error
select *
  from emp
  group by deptno,job

--ok
select deptno,job,avg(sal)
  from emp
  group by deptno,job
  order by deptno

--ok
select deptno,job,avg(sal) "平均工资",count(*) "部门人数",sum(sal) "部门总工资",min(sal) "部门最低工资"
  from emp
  group by deptno,job
  order by deptno


select comm, count(*)
  from emp
  group by comm

having

--输出部门平均工资大于1500的部门的部门编号 部门的平均工资
select deptno, avg(sal)
  from emp
  group by deptno
  having avg(sal)>2000

--判断下列sql语句是否正确  ok
select deptno, avg(sal) as "平均工资"
  from emp
  group by deptno
  having avg(sal)>2000

--判断下列sql语句是否正确  error
select deptno, avg(sal) as "平均工资"
  from emp
  group by deptno
  having "平均工资">2000

--判断下列sql语句是否正确  ok
select deptno, avg(sal) as "平均工资"
  from emp
  group by deptno
  having deptno>10

--判断下列sql语句是否正确  ok
select deptno, avg(sal) as "平均工资"
  from emp
  group by deptno
  having count(*)>3

--判断下列sql语句是否正确  error
select deptno, avg(sal) as "平均工资"
  from emp
  group by deptno
  having ename like '%A%'

--判断下列sql语句是否正确  error
select ename, sal "工资"
  from emp
  where sal>2000

having和where的异同

--把姓名不包含A的所有员工按部门编号分组
--统计输出部门平均工资大于2000的部门的部门编号 部门的平均工资
select deptno, avg(sal) "平均工资"
  from emp
  where ename not like '%A%'
  group by deptno
  having avg(sal)>2000

--把工资大于2000，
--统计输出部门平均工资大于3000的部门的部门编号 部门的平均工资
select deptno, avg(sal) "平均工资", count(*) "人数", max(sal) "部门最高的工资"
  from emp
  where sal >2000       --where是对原始的记录过滤
  group by deptno
  having avg(sal)>3000  --对分组之后的记录过滤

--判断如下语句是否正确  error 
select deptno, avg(sal) "平均工资", count(*) "人数", max(sal) "部门最高的工资"
  from emp
  group by deptno
  having avg(sal)>3000  --对分组之后的记录过滤
  where sal >2000       --where是对原始的记录过滤

总结：所有select的参数的顺序是不能变化的