1.认识子查询
子查询就是嵌入在其他数据库语句(包括where,having,from,select)中的select语句,在执行sql语*句时先执行子句。**
子查询注意事项
1)查询要包还在括号内
2) 将查询放在较条件的右侧增强可读性 ( 查询可以出现在较运算符的两侧)
3)单操作符对应单查询,多操作符对应多查询
2.在where子句中使用子查询
例:
在where子句中嵌套select语句,求出工资大于公司平均的员工。
select ename,sal
from emp
where sal>(select avg(sal) from emp);
ENAME SAL
---------- ----------
JONES 2975
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
FORD 3000
3.单行子查询
查询出于CLARK同岗位的且工资大于两千的员工的名字,岗位和工资
SQL> select ename,job,sal
2 from emp
3 where job = (select job from emp
4 where ename=‘CLARK’)
5 and sal>2000;
ENAME JOB SAL
---------- --------- ----------
JONES MANAGER 2975
BLAKE MANAGER 2850
CLARK MANAGER 2450
4.having语句中使用子查询
例:
查询出
SQL> select deptno,min(sal)
2 from emp
3 group by deptno
4 having min(sal)>(select min(sal) from emp
5 where deptno=20);
DEPTNO MIN(SAL)
---------- ----------
30 950
10 1300
当单列子查询返回多于一行时,子查询会出现常见错误
5.多行查询
1.返回多行
2.使用多行比较操作符
操作符 | 含义 |
---|---|
IN | 等于列表中的某一个值 |
ANY | 与列表中的任意值比较 |
ALL | 与列表中所有的值相比较 |
例:
查询某部门中工资最低的员工姓名
SELECT last_name, salary, department_id
FROM employees
WHERE salary IN (SELECT MIN(salary)
FROM employees
GROUP BY department_id);
6.一对多的比较关系用ANY和ALL
>ANY于最值的意思
<ANY表示于最值
=ANY等于IN
>ALL表示大于最大值
><ALL表示小于最小值
7.在from子句中使用子句查询
SQL> select a.ename , a.sal , a.deptno , b.salavg
2 from emp a , (select deptno,avg(sal) salavg from emp group by deptno ) b
3 where a.deptno = b.deptno
4 and a.sal > b.salavg;
ENAME SAL DEPTNO SALAVG
---------- ---------- ---------- ----------
ALLEN 1600 30 1566.66667
JONES 2975 20 2175
BLAKE 2850 30 1566.66667
SCOTT 3000 20 2175
KING 5000 10 2916.66667
FORD 3000 20 2175
6 rows selected.
8.结果集分⻚
要求列出 emp 表第 5-第 10 名员⼯(按 sal ⼤–⼩排序)的信息
使 rownum 筛选时必须显示或隐示的包含第⼀⾏,否则不会返回任何⾏
select * from
(select t1.,rownum rn
from (select * from emp order by sal desc) t1
where rownum<=10) where rn>=5;
select * from (select t1., rownum rn
from (select * from emp order by sal desc) t1)
where rn between 5 and 10;