Oracle--多表查询及子查询

多表查询


1、找涉及中的表
2、找表中的关联条件
3、要查询的东西查询出来 
ps:n张表，至少要找n-1个关联条件，而且n-1关联条件之间是 and


内连接
查询出雇员的姓名，工作以及这个部门所在的位置


select e.ename,e.job,d.loc
from scott.emp e,scott.dept d
where e.deptno=d.deptno;
等值连接


查询出雇员的姓名，工作，以及雇员的直接上级领导的姓名


select e.ename 雇员的姓名,e.job,m.ename 领导的姓名
from scott.emp e,scott.emp m
where  e.mgr=m.empno;


自连接


查询出雇员的姓名，工作，以及雇员的直接上级领导的姓名，以及雇员部门的名称


select e.ename 雇员的姓名,e.job,m.ename 领导的姓名,d.dname
from scott.emp e,scott.emp m,scott.dept d
where e.mgr=m.empno and e.deptno=d.deptno;


非等值连接:


查询出雇员的姓名，工资，工资在公司的等级
select e.ename,e.sal,g.grade
from scott.emp e,scott.salgrade g
where  e.sal between g.losal and g.hisal;


查询出雇员的姓名，工资，工资在公司的等级，雇员的上级领导的姓名和工资
select e.ename 雇员的姓名,e.sal 雇员的工资,g.grade,m.ename 领导的姓名,m.sal 领导的工资
from scott.emp e,scott.salgrade g,scott.emp m
where e.sal between g.losal and g.hisal and e.mgr=m.empno;


查询出雇员的姓名，工资，工资在公司的等级，雇员的上级领导的姓名和工资,以及领导工资在公司的工资等级


select e.ename 雇员的姓名,e.sal 雇员的工资,g.grade 雇员的工资等级,m.ename 领导的姓名,m.sal 领导的工资,mg.grade 领导的工资等级
from scott.emp e,scott.salgrade g,scott.emp m,scott.salgrade mg
where e.sal between g.losal and g.hisal and e.mgr=m.empno and
m.sal between mg.losal and mg.hisal;


进一步思考：要求按照以下的样式显示工资的等级
1：第五等级
2：第四等级
3：第三等级
4：第二等级
5：第一等级
decode(g.grade,1,'第五等级',2,'第四等级',3,'第三等级',4,'第二等级','第一等级')


case mg.grade
  when   1  then '第五等级'
  when    2 then '第四等级'
  when 3  then '第三等级'
  when 4 then '第二等级'
   else   '第一等级'
end
select e.ename 雇员的姓名,e.sal 雇员的工资,decode(g.grade,1,'第五等级',2,'第四等级',3,'第三等级',4,'第二等级','第一等级') 雇员的工资等级,m.ename 领导的姓名,m.sal 领导的工资,case mg.grade
  when   1  then '第五等级'
  when    2 then '第四等级'
  when 3  then '第三等级'
  when 4 then '第二等级'
   else   '第一等级'
end 领导的工资等级
from scott.emp e,scott.salgrade g,scott.emp m,scott.salgrade mg
where e.sal between g.losal and g.hisal and e.mgr=m.empno and
m.sal between mg.losal and mg.hisal;


内连接
外连接
select e.ename 雇员的姓名,e.job,m.ename 领导的姓名
from scott.emp m,scott.emp e
where  m.empno(+)=e.mgr;
左外连接：左连接 (+)在=右边
右外连接：(+)在=左边


SQL1999的语法：
select   *
from table1 [cross join] table2--交叉连接：会产生冗余的数据
                    [natural join] table2 --自然连接，自动的进行关联自动的匹配                 [join  table 2 using (column)]--直接关联字段
                    [join table2 on(table1.column=table2.column)]
                    [left [outer] join table2 on
(table1.column=table2.column)]-- 左外连接
                   [right [outer] join table2 on
(table1.column=table2.column)]--右外连接


（１）显示姓名中含有字母A的雇员的姓名、部门名称
select e.ename,d.dname
from scott.emp e,scott.dept d
where e.deptno=d.deptno and e.ename like '%A%';


select e.ename,d.dname from scott.emp e join scott.dept d on(e.deptno=d.deptno) where e.ename like '%A%';




select e.ename,d.dname from scott.emp e natural join scott.dept  d where e.ename like '%A%';


select e.ename,d.dname from scott.emp e join scott.dept d using(deptno) where e.ename like '%A%';




（２）显示雇员的姓名、编号以及其上级管理员的姓名和编号
select e.ename 雇员的姓名,e.empno 雇员的编号,m.ename 领导的姓名,m.empno 领导的编号
from scott.emp e,scott.emp m
where e.mgr=m.empno(+);


select e.ename 雇员的姓名,e.empno 雇员的编号,m.ename 领导的姓名,m.empno 领导的编号
from scott.emp e,scott.emp m
where e.mgr=m.empno;


select e.ename 雇员的姓名,e.empno 雇员的编号,m.ename 领导的姓名,m.empno 领导的编号
from scott.emp e  join scott.emp m on(e.mgr=m.empno);


（３）在(2)的基础上，显示所有雇员（即使是没有上级领导）。


select e.ename 雇员的姓名,e.empno 雇员的编号,m.ename 领导的姓名,m.empno 领导的编号
from scott.emp e left join scott.emp m on(e.mgr=m.empno);






思考练习题
１．编写一个查询显示每个雇员的姓名、部门编号及部门名称。


select e.ename,d.deptno,d.dname
from scott.emp e,scott.dept d
where e.deptno(+)=d.deptno;


２．显示30号部门的雇员所从事的不重复的工作名称，并显示30部门的所在地。
select  distinct e.job,d.loc
from scott.emp e,scott.dept d
where e.deptno=d.deptno and e.deptno=30;
３．编写一个查询显示能获得奖金的雇员的姓名、部门名称、部门所在地。
select e.ename,d.dname,d.loc
from scott.emp e,scott.dept d
where e.deptno=d.deptno and e.comm is not null;


４．编写一个查询显示在DALLAS工作的雇员的姓名、工作、部门编号及部门名称。
select e.ename,e.deptno,d.dname
from scott.emp e,scott.dept d
where e.deptno=d.deptno and d.loc='DALLAS';


５．创建一个查询显示雇员的姓名、工作、部门名称、工资以及工资等级。
select e.ename,e.job,d.dname,e.sal,s.grade
from  scott.emp e,scott.dept d,scott.salgrade s
where  e.deptno=d.deptno and e.sal between s.losal and s.hisal;






６．显示比其上级管理员更早进入公司的雇员的姓名、受雇日期以及其上级领导的姓名及受雇日期。


select e.ename  雇员的姓名,e.hiredate 雇员的雇佣日期,m.ename 领导的姓名,m.hiredate 领导的雇佣日期
from scott.emp e,scott.emp m
where e.mgr=m.empno and e.hiredate<m.hiredate;




组函数以及分组统计
组函数
count():
max()
min()
avg()
sum()


求出emp表中总记录数
求出emp表中员工的最低工资
求出emp表中部门为20的最高工资
求出职位是SALESMAN的平均工资
分组统计(group by)
select
from
where
group by 字段,字段
order by


查询出每个部门的员工数
select count(1)
from scott.emp group by deptno;


查询出每个工种的最低工资和最高工资


select min(sal),max(sal) from scott.emp group by job


ps:1、在使用分组统计的时候，查询的结果只能是分组统计的条件和组函数
     2、如果不使用分组统计，查询的结果只能是组函数
     3、如果不使用分组统计，查询的结果中组函数是不可以嵌套
    4、 如果使用了分组统计，查询的结果只能是嵌套的函数


按照部门分组，并显示部门的姓名，以及每个部门的员工数
select d.dname,count(1),d.deptno
from scott.dept d,scott.emp e
where  d.deptno=e.deptno
group by  d.dname,d.deptno;


having:如果条件当中出现组函数的，只能用having




select
from
where
group by 字段,字段
having
order by




查询出平均工资大于2000的部门的编号和平均工资
select avg(sal),deptno
from scott.emp
group by deptno
having avg(sal)>2000;


显示非销售人员工作的名称以及从事同一工作的雇员月工资的总和
并且要满足同一工作的雇员的月工资总和大于4000，输出结果按照月工资总和的升序排序
job<>'SALESMAN'
group by job
sum(sal)>4000


select job,sum(sal) su from scott.emp where job<>'SALESMAN' group by job  having sum(sal)>4000 order by su;


实验步骤
（１）显示所有雇员工资的最高（Maximum）、最低（Minimum）、合计（Sum）和平均值（Average），并对各值按四舍五入进行处理。
select  max(sal) Maximum,min(sal) Minimum,sum(sal) Sum,round(avg(sal)) Average from scott.emp


（２）按照工作种类进行分组，显示各个组内工资的最高（Maximum）、最低（Minimum）、合计（Sum）和平均值（Average）。
select  max(sal) Maximum,min(sal) Minimum,sum(sal) Sum,round(avg(sal)) Average from scott.emp group by job




（３）编写一查询显示各个部门的名称（DNAME）、位置（LOC）、部门内的雇员数（Number of People）、部门内的平均工资（Salary）、平均工资四舍五入到小数点后两位。


select count(1) "Number of People",round(avg(sal)) Salary,d.dname,d.loc
from scott.emp e,scott.dept d
where e.deptno=d.deptno
group by d.dname,d.loc;




思考练习题
１．组函数对多个数据行进行处理，每个组产生一个结果，该语句对否，请判断。
对
２．组函数计算过程中可以对空值进行处理，该语句对否，请判断。


对
３．WHERE字句对数据行的限制优先于分组计算，该语句对否，请判断。
对
４．编写一个查询显示从事同一工作的人数。


select count(1)
from  scott.emp
group by job;
５．求出管理员的人数（Number of Managers），而不显示管理员。
 select count(1)
from scott.emp
where job='MANAGER';




６．编写一查询显示雇员最高工资和最低工资的差值（DIFFERENCE）。


select  max(sal)-min(sal) DIFFERENCE
from scott.emp


７．显示管理员编号（管理员编号明确），以及被该管理员所管理的雇员的最低工资（最低工资大于1000美元）。输出结果按最低工资降序排列。
select min(e.sal) min,m.empno
from scott.emp e,scott.emp m
where e.mgr=m.empno and m.job='MANAGER'
group by m.empno
having min(e.sal)>1000
order by min desc;


８．创建一查询显示所有雇员的数量以及分别在1980、1981、1982、1983年加入公司的雇员数量，设置恰当的列标题。


select count(1) 总人数,count(
decode(to_char(hiredate,'yyyy'),'1980',empno)) "1980 人数",
count(decode(to_char(hiredate,'yyyy'),'1981',empno)) "1981人数",
count(decode(to_char(hiredate,'yyyy'),'1982',empno)) "1982 人数",
count(decode(to_char(hiredate,'yyyy'),'1983',empno)) "1983 人数"
from scott.emp;


子查询：
一个查询的内部还包含另外一个查询




select
from
where
group by 
having
order by


where


查询出工资比SMITH要高的雇员信息
select sal from scott.emp where ename='SMITH';
select * from scott.emp where sal>(select sal from scott.emp where ename='SMITH');


查询出工作和7788一样的雇员的信息
select * from scott.emp where job=(select job from scott.emp where empno=7788);


查询出部门名称，部门的员工数，部门的平均工资，部门的最低收入雇员的姓名




select deptno,count(1),round(avg(sal)),min(sal)
from scott.emp
group by deptno;




select  d.dname,temp.cou,temp.avg,e.ename
from (select deptno,count(1) cou,round(avg(sal)) avg,min(sal) min
from scott.emp
group by deptno) temp,scott.dept d,scott.emp e
where d.deptno=temp.deptno and  e.sal=temp.min;




要求查询出工作是CLERK的姓名以及部门的名称，和部门的总收入