分组查询以及子查询
一、分组查询
1）group by子句 用于分组查询
【例】查询每个部门员工的平均工资
select deptno,AVG(sal) from emp
group by deptno;注意：
1、如果在select后面有组函数，又有普通列，需要使用group by子句，并且所有的普通列必须都
出现在group by子句中
2、group by子句中的普通列可以不在select后面
2）having 子句用于限定分组查询结果
【例】查询员工表中各部门最高工资大于2900.00的部门号及最高工资
select deptno,MAX(sal) from emp
group by deptno
having MAX(sal) > 2900.00;
注意：where后面不能使用组函数；只要有having子句出现就一定有group by ，group by后面不一定有having子句
总结SQL语句基本结构：
select 列1,… | 查询 |
from 表1,表2,… | 从指定表查询指定列 |
where 条件 and/or 条件 | 限定查询结果 |
group by 列1,列2,… | 分组查询 |
having 条件(组函数) | 限定分组查询结果 |
order by 列1,列2,… (升序/降序) | 排序 |
二、子查询
【例】找出工资高于Jones的雇员
#找出JONES的工资
select sal from emp
where ename = 'JONES'; #得到JONES工资为2950.00
#找出工资高于2975.00
select ename,sal from emp
where sal > 2975.00;#使用子查询
select ename,sal from emp
where sal > (select sal from emp where ename = 'JONES');1) 单行子查询：返回一行记录
使用单行比较运算符 =、>、 >=、 < 、<= 、<>(表示不等于)如: 查询职位与’MARTIN’不同的员工的姓名，工资，职位
select ename,sal,job from emp
where job <> (select job from emp where ename = "MARTIN");
在子查询中使用HAVING子句
【例】查询部门最低工资高于20号部门最低工资的部门及最低工资及最高工资
select deptno,MIN(sal),MAX(sal) from emp
group by deptno
having MIN(sal) > (select MIN(sal) from emp where deptno = 20);2) 多行子查询
子查询返回多行一列使用多行比较运算符
- in(子查询语句) 等于列表中的任何值
- any 比较子查询返回的每一个值，只要其中一个值满足条件就返回true
- <any 指小于最大值
- > any 指大于最小值
- all 比较子查询返回的每一个值，要其中每一个值都要满足条件才返回true
- >all 指大于最大值
- <all 指小于最小值
① in 等于列表中的任何值
如: 查询与’ALLEN’或’SMITH’职位相同的员工的姓名，职位
select ename,job from emp
where job in(select job from emp where ename in("ALLEN","SMITH"));② any 比较子查询返回的每一个值，只要其中一个值满足条件就返回true
如: 查询员工表中工资高于任一部门平均工资的员工的姓名，工资
select ename,sal from emp
where sal > any(select AVG(sal) from emp group by deptno);③ all 比较子查询返回的每一个值，要其中每一个值都要满足条件才返回true
如: 查询入职日期不早于所有部门员工最晚入职的员工的姓名，入职日期
select ename,hiredate from emp
where hiredate >= all(select MAX(hiredate) from emp group by deptno);3) 多列子查询
子查询返回多行多列
多列子查询使用in运算符【练习】查询每个部门工资最高的员工的姓名，工资，部门号
select ename,sal,deptno from emp
where (deptno,sal) in(select deptno,MAX(sal) from emp group by deptno);查询结果
4) 分页子查询
limit 起始行号,返回行数
语法：
sql语句 limit 起始行(第一行的行号为0) 返回行数量如: 查询员工表中工资收入排名前五的员工的姓名，工资
select ename,sal from emp
order by sal desc
limit 0,5;可以把子查询的结果当作一张表使用
如: 查询工资高于自己部门平均工资的员工的姓名，职位，工资，部门号
select e.ename,e.job,e.sal,e.deptno from emp e,(select deptno,AVG(sal) avgsal from emp group by deptno) a
where e.deptno = a.deptno
and e.sal> a.avgsal;总结SQL语句基本结构：
select | 列1,… |
from 表1,表2,… | 从指定表查询指定列 |
where 条件 and/or 条件 | 限定查询结果 |
group by 列1,列2,… | 分组查询 |
having 条件(组函数) | 限定分组查询结果 |
order by 列1,列2,… (升序/降序) | 排序 |
limit 起始行号,返回行数 | 分页子查询 |
