Oracle: 四、Oracle连接查询，子查询(相关子查询，嵌套子查询)（下）

内容简介

 　　1，Demo连接查询。2，Demo 子查询（相关子查询，嵌套子查询）。

技术与环境

操作系统:	windows	语言类别:	SQL之PL/SQL
thankyou:	sunshine, 谢谢你的默默付出	数据库:	Oracle
学习软件:	Oracle 10g
课程总策划:	yuanbo	English name:	sunshine
个人主页:	http://www.cnblogs.com/ylbtech/
科研团队:	ylbtech	教研团队:	ylbtech

1，Demo连接查询

--========================================================
--ylb：Oracle
--17:13 2011-12-30
--1，链接查询（传统链接、内链接）
--========================================================
 
--总结：什么时候使用连接查询？
--当需要显示的数据来源于多个表时，使用连接查询。
--一，连接查询--分传统连接与内连接
/*
1,查询员工姓名和所在部门的名称(2种 ）
--传统连接  --依赖的是    ,和where
*/
select * from emp,dept where emp.deptno=dept.deptno
select ename,dname,emp.deptno from emp ,dept where emp.deptno=dept.deptno;
select * from emp a ,dept b where a.deptno=b.deptno;
select a.ename,b.dname from emp a,dept b where a.deptno=b.deptno
select a.ename,b.dname,a.deptno from emp a,dept b where a.deptno=b.deptno
--内联接    --依赖的是     inner join   on 
select * from emp a inner join dept b on a.deptno=b.deptno
 
2,查询员工姓名和所在部门的名称，要求部门编号为30(2种 ）
select a.ename,b.dname from emp a inner join dept b on a.deptno=b.deptno where a.deptno=30
select a.ename,b.dname from emp a,dept b where a.deptno=b.deptno and a.deptno=30
3,查询员工姓名和部门名称,要求没有员工的部门的名称也要查询出来
--传统连接 ：使用”（＋）“
select * from emp a ,dept b where a.deptno(+)=b.deptno;
select * from emp a ,dept b where b.deptno=a.deptno(+);
--使用join连接 ：   
右外连接
select * from emp a right outer join dept b on a.deptno=b.deptno

左外连接  
select * from emp a left outer join dept b on a.deptno=b.deptno;
     
全外连接: 
--只能用标准sql来书写
 select * from emp a full outer join dept b on a.deptno=b.deptno;
 
4,查询员工姓名和其直接上级的姓名,要求没有经理的员工也查询出来
--自连接
员工 SMITH 的上级是 FORD
select '员工'||a.ename||'的上级是'||b.ename from emp a left outer join emp b on a.mgr=b.empno;
select '员工'||a.ename||'的上级是'||b.ename from emp a,emp b where a.mgr=b.empno(+)

select a.ename,b.ename from emp a,emp b where a.mgr=b.empno(+)

2，Demo 子查询（相关子查询，嵌套子查询）

--========================================================
--ylb：Oracle
--17:13 2011-12-30
--1，子查询（嵌套子查询、相关子查询）
--========================================================
/*** 
连接与子查询的区别：
1，当需要多个表的数据时用连接，子查询只能返回单表数据。
2，连接快，子查询慢。
3，子查询功能强大。
4，子查询-两种（嵌套子查询，关联子查询）
 嵌套简单，关联复杂，面试关联查询
**/


--一， 子查询第一种 : 嵌套子查询:简单--子查询可以独立运行，自内而外
--1，查询工资高于SMITH工资的所有员工
select * from emp where sal>(select sal from emp where enAme='SMITH')
go
--2，查询工资高于公司平均工资的所有员工?
select * from emp where sal>(select avg(sal) from emp)
--附加题，
--> >= < <= = != <> ^= 后面只能跟一个值，
--如果有多个值，>all--大于最大值    >any--大于最小值
--查询工资高于所有部门的平均工资的员工
select * from emp where sal>all(select avg(sal) from emp group by deptno)
--查询工资高于任何部门的平均工资的员工

select * from emp where sal>any(select avg(sal) from emp group by deptno)
    
    
go
/*********************************************************************************/                
--二， 子查询第二种 : 关联子查询 ,思考：自外而内
--3，查询工资高于本部门平均工资的所有员工?
select * from emp a where a.sal>(select avg(sal) from emp where deptno=a.deptno)
--4，查询本部门最高工资的员工?(三种方法)
--方法一，使用嵌套子查询（非关联子查询）
select * from emp a where (a.deptno,a.sal) in (select deptno,max(sal) from emp group by deptno)
--方法二，使用关联子查询/*9-******************
select * from emp a where a.sal=(select max(sal) from emp where deptno=a.deptno)
--方法三，使用关联子查询的名次问题，名次=人数+1 
sal=800
deptno=20
select * from emp a 
where (
select count(*) from emp 
where deptno=a.deptno and sal>a.sal)=1
/*********************************************************************************/   


go
--补充题：
--查询本部门第二高工资的员工?(一种方法)
--5，查询本部门最低工资的员工 ?
select * from emp a where (select count(*) from emp where deptno=a.deptno and sal<a.sal)=0                                   

------------------------------------------------------三，select 语句做表达式
--6，统计每个部门的信息和人数?
select a.*,(select count(*) from emp where deptno=a.deptno) 人数 from dept a
select a.* from dept a
   
select a.deptno,b.dname,b.loc,count(*) from emp a,dept b where a.deptno=b.deptno group by a.deptno,b.dname,b.loc
               
--7，统计每个部门工资在(500-1000)/(1000-3500)/(3500-7000) 的人数?
select a.*,
(select count(*) from emp where deptno=a.deptno and sal>500 and sal<=1000) "500-1000",
(select count(*) from emp where deptno=a.deptno and sal>1000 and sal<=3500),
(select count(*) from emp where deptno=a.deptno and sal>3500 and sal<=7000) 
from dept a 

 

作者：ylbtech 出处：http://ylbtech.cnblogs.com/ 本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。