Oracle培训（二十九）——Oracle 11g 第六章知识点总结——子查询

公孙怡然

Oracle培训（二十九）——Oracle 11g 第六章知识点总结——子查询

　　知识点预览
　　
　　子查询
　　

　　子查询
　　
　　1.使用子查询解决问题
　　
　　谁的工资比 Abel 高?
　　

　　
　　2.子查询语法
　　a)子查询 (内查询) 在主查询之前一次执行完成。
　　b)子查询的结果被主查询使用 (外查询)。
　　
　　SELECT select_list
　　FROM table
　　WHERE expr operator
　　  (SELECT select_list
　　  FROM
table);
　　
　　3.子查询
　　
　　

SELECT last_name
FROM   employees
WHERE  salary >
(SELECT salary
FROM   employees
WHERE  last_name = 'Abel');


　　
　　4.注意事项
　　a)子查询要包含在括号内。
　　b)将子查询放在比较条件的右侧。
　　c)除非进行Top-N 分析，否则不要在子查询中使用ORDERBY 子句。
　　d)单行操作符对应单行子查询，多行操作符对应多行子查询。
　　

　　5.子查询类型
　　

　　
　　
　　6.单行子查询
　　a)只返回一行。
　　b)使用单行比较操作符。
　　
　　


　　

　　
　　7.执行单行子查询
　　
　　

SELECTlast_name, job_id, salary
FROM   employees
WHERE  job_id =
(SELECT job_id
FROM   employees
WHERE  employee_id = 141)
AND    salary >
(SELECT salary
FROM   employees
WHERE  employee_id = 143);


　　
　　8.在子查询中使用组函数
　　
　　

SELECTlast_name, job_id, salary
FROM   employees
WHERE  salary =
(SELECT MIN(salary)
FROM   employees);


　　
　　9.子查询中的 HAVING 子句
　　a)首先执行子查询。
　　b)向主查询中的HAVING 子句返回结果。
　　
　　

SELECT   department_id,MIN(salary)
FROM     employees
GROUP BY department_id
HAVING   MIN(salary) >
(SELECTMIN(salary)
FROM   employees
WHERE  department_id = 50);


　　
　　10.非法使用子查询
　　
　　

SELECT employee_id,last_name
FROM   employees
WHERE  salary =
(SELECT   MIN(salary)
FROM     employees
GROUP BY department_id);


　　
　　

　　

　　
　　
　　11.子查询中的空值问题
　　
　　

SELECTlast_name, job_id
FROM   employees
WHERE  job_id =
(SELECT job_id
FROM   employees
WHERE  last_name = 'Haas');
　　
　　







　　12.多行子查询
　　a)返回多行。
　　b)使用多行比较操作符。
　　

　　

　　
　　

　　13.在多行子查询中使用 ANY 操作符
　　
　　

SELECTemployee_id, last_name, job_id, salary
FROM   employees
WHERE  salary < ANY
(SELECT salary
FROM   employees
WHERE  job_id = 'IT_PROG')
AND    job_id <> 'IT_PROG';


　　
　　14.在多行子查询中使用 ALL 操作符
　　
　　

SELECTemployee_id, last_name, job_id, salary
FROM   employees
WHERE  salary < ALL
(SELECT salary
FROM   employees
WHERE  job_id = 'IT_PROG')
AND    job_id <> 'IT_PROG';


　　
　　15.子查询中的空值问题
　　
　　

SELECTemp.last_name
FROM   employees emp
WHERE  emp.employee_id NOT IN/EXISTS
(SELECTmgr.manager_id
FROM   employees mgr);


　　
　　
　　no rows selected
　　

　　16.示例
　　
　　

--查询出比MARTIN工资高的所有人
--分析：
--a.查询出MARTIN的工资
selectsal from emp where ename = 'MARTIN';
--b.查询出比他高的所有人
selectename,sal
fromemp
wheresal >
--子查询要包含在括号内
(
select sal
from emp
where ename = 'MARTIN'
);
--<ALL --小于最小的
--<ANY --小于最大的
-->ALL --大于最大的
-->ANY --大于最小的
--=ANY --等价于in
--=ALL --报错
--多行子查询
<any小于最大的 =any /  in
<all  小于最小的
1.       列出至少有一个员工的所有部门
select count(*) ,sum(sal),deptno
from emp
having count(*)>=1
group by deptno
--2.列出薪金比"SMITH"高的所有员工
--4.列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称。
select e.empno,e.ename,d.dname
from
emp e, emp m,
(
selectdeptno,dname
fromdept
) d
where e.hiredate < m.hiredate
and e.mgr = m.empno
andd.deptno = e.deptno;
--5.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
select d.dname,e.ename,e.deptno
from emp e right outer join dept d
on e.deptno = d.deptno;
--6.列出所有"CLERK"（办事员）的姓名及其部门名称，部门的人数
selectename,e.deptno,d.dname,t.num
fromemp e,dept d , (
selectdeptno,count(*) num
fromemp
wherejob='CLERK'
groupby deptno
)t
where job='CLERK' and e.deptno=d.deptno andd.deptno=t.deptno;
selecte.ename,d.dname,t.num
fromemp e, dept d,
(
select deptno,count(*) num
from emp
where job = 'CLERK'
group by deptno
)t
where e.deptno = d.deptno and job = 'CLERK'and t.deptno = e.deptno;
--7.列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数
select job, count(*)
from emp
having min(sal) > 1500
group by job;
--8.列出在部门"SALES"（销售部）工作的员工的姓名，假定不知道销售部门的部门编号。
select deptno from dept where dname ='SALES';
select e.ename from emp;
select e.ename,e.deptno
from emp e
where e.deptno =
(
selectdeptno
fromdept where
dname= 'SALES'
);
--9.列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，--工资等级。
selecte.ename,e.sal,d.dname,m.ename
fromemp e,dept d,emp m
wheree.sal>(
selectavg(sal) from emp
)and e.deptno=d.deptno and e.mgr=m.empno;

select e.ename,temp.dname,m.ename as "上级领导"
from
emp e, emp m,(
selectdeptno,dname
from
dept
) temp
where e.mgr = m.empno
and temp.deptno = e.deptno
and e.sal >
(select avg(sal)
from emp);
--10.列出与"SCOTT"从事相同工作的所有员工及部门名称
select e.ename,temp.dname
from emp e,
(
selectdeptno,dname
from
dept
)temp
where e.job =
(
selectjob
fromemp
whereename = 'SCOTT'
) and e.deptno = temp.deptno
and e.ename != 'SCOTT';
--11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金
select e.ename, e.sal
from emp e
where
e.sal = any
(
selectsal
fromemp
wheredeptno = 30
);
--12.列出薪金高于在部门30工作的所有员工的薪金的员工的姓名和薪金、部门名称。
select e.ename, e.sal, temp.dname
from emp e,
(
selectdeptno,dname
from
dept
) temp
where
e.sal > all
(
selectsal
fromemp
wheredeptno = 30
) and temp.deptno = e.deptno;
--13.列出在每个部门工作的员工数量、平均工资和平均服务期限。
select count(*),avg(sal),avg(round((sysdate -hiredate) / 365, 0)),deptno
from emp
group by deptno;
--14.列出所有员工的姓名、部门名称和工资
select e.ename,temp.dname,e.sal
from emp e,
(
selectdeptno,dname
from
dept
) temp
where temp.deptno = e.deptno;
--15.列出所有部门的详细信息和部门人数
selectd.*,t.c
fromdept d,(
selectdeptno,count(*) c
fromemp
groupby deptno
)t
whered.deptno=t.deptno;

select d.*,temp.c
from dept d,
(
select count(*) asc,deptno
from emp
group by deptno
)temp
where d.deptno = temp.deptno;
--优化
select dept.dname,tem.num
from dept
left join
(select deptno, count(*) num
from emp
group by(deptno)
)tem
on tem.deptno = dept.deptno;
--16.列出各种工作的最低工资及从事此工作(从事该职位且工资最低)的雇员姓名。
--1列出各种工作的最低工资
selecte.ename,e.sal,t.s,t.job
fromemp e,(
selectmin(sal) s,job
fromemp
groupby job
)t
wheree.sal=t.s;
--17.出各个部门的MANAGER(经理)的最低薪金。
selectmin(sal)
fromemp
wherejob = 'MANAGER'
group by deptno;
--18.列出所有员工的年工资，按年薪从低到高排序。
selectsal*12 s from emp order by s;
selectename,sal * 12 as "年薪"
fromemp
orderby sal;
selectename,sal * 12 as "年薪"
fromemp
orderby sal * 12;
--19.查处某个员工的上级主管，并要求出这些主管的薪水超过3000
selecte.ename,e.mgr,m.empno,m.ename
fromemp e, emp m
wheree.mgr = m.empno
andm.sal > 3000;
--20.求出部门名称中，带‘S’字符的部门员工的工资合计、部门人数
selectd1.dname,t.*
fromdept d1,(
selectcount(*),sum(e.sal),e.deptno
fromemp e,dept d
whered.dname like '%S%' and e.deptno=d.deptno
groupby e.deptno
)t
whered1.deptno=t.deptno;
--第一行：ORA-00937: 不是单组分组函数
select sum(e.sal), temp.c
from emp e,
(
selectcount(*) c
fromdept
groupby deptno
)temp
where e.deptno
in
(
selectdeptno
fromdept
wheredname like '%S%'
);
select sum(sal), count(*)
from emp
group by deptno
having deptno
in
(
selectdeptno
fromdept
wheredname like '%S%'
);
--21.给任职日期超过10年的人加薪10%


　　
　　
　　第9题：
　　

　　第15题：
　　

　　第16题：
　　

　　第20题：