自查询
select e.employees_id,m.employees_id||':'||m.last_name from employees e,employees m where e.employees_id=m.manager_id
子查询
select m.last_name,m.salary,round(e.c,2) avgsalary,d.department_name/*姓名，工资，平均工资，部门*/
from employees m,/*基本表*/
(select department_id,avg(salary) c from employees group by department_id) e,/*部门工资平均值*/
departments d/*部门表*/
where e.department_id=m.department_id(+) /*基本表和平均值表关联 */
and m.department_id=d.department_id(+)/*部门表和基本表关联*/
and m.salary>e.c /*人员工资大于部门平均工资*/
rownum
in 包含在里边意思
not in
any或者关键字意思 where 字段<any （selecy XXX from ..where..）；
all and关键字意思where 字段<any （selecy XXX from ..where..）；同时小于
> < >= <= <>
select * from XX where exists(子查询) exists只需要判断是否有子查询里的数据
in 和not in当条件返回值为空时，not in语句将报错 in正常
而当exists和not exists时不需处理null
多表连接查询效率低于子查询
set autotrace on
//查看任务计划可查看其语句消耗占用
rownum只能用<= //当有排序时，会先执行rownum再排序，因此排序将不生效，所以需要用子查询嵌套进行排序
select * from (select last_name, salary from employees order by salary desc) where rownum<=3
分页查询：
SQL> select * from
(select * from
(select * from
(select last_name, salary from employees order by salary desc)
where rownum<=6)
order by salary)
where rownum<=3
order by salary desc;
SQL> select last_name, salary
from (select rownum row_num, v1.*
from
(select last_name, salary from employees order by salary desc) v1
) v2
where row_num between 4 and 6;
select last_name, salary
from (select rownum row_num, v1.*
from
(select last_name, salary from employees order by salary desc) v1
where rownum<=6
) v2
where row_num >= 4;