Oracle -- 子查询，连接运算

一、子查询

-- 1.单行子查询
--查询与员工SMITH属于同一部门的员工信息
  select * from emp where deptno =(select deptno from emp where ename='SMITH');

--2.多行子查询
-- 查询与部门10员工工作岗位相同的员工信息
  select * from emp where job in(select job from emp where deptno=10);
  
-- 2.2 查询与部门10员工工作岗位不相同的员工信息
  select * from emp where job not in(select job from emp where deptno=10);
 
-- 3.多列子查询
--查询与SMITH的部门和岗位都相同的员工信息
  select * from emp where (deptno,job) =(select deptno,job from emp where ename='SMITH');
  
--子查询中all关键字的使用:
--4.显示工资比部门号30的所有员工的工资都要高的员工的，姓名，工资，部门号
-- 求出30号部门所有员工的工资
-- select sal from emp where deptno = 30;
  select ename,sal,deptno from emp where sal > all (select sal from emp where deptno = 30);
 --第二种方案
  select ename,sal,deptno from emp where sal > (select max(sal) from emp where deptno = 30);  
 
--子查询中any关键字的使用
--5.显示工资比部门号30的任意员工的工资都要低的员工的，姓名，工资，部门号
--select sal from emp where deptno = 30;
  select ename,sal,deptno from emp where sal > any (select sal from emp where deptno = 30);
 --第二种方案
  select ename,sal,deptno from emp where sal > (select min(sal) from emp where deptno = 30);
    
--.内联视图子查询（分页查询）
--8.查询30号部门中工资排序前5名的员工信息。
 /*
    rownum是Oracle对查询结果集加的一个伪列 (强调：先要有结果集)。
    简单的说 rownum 是对符合条件结果的序列号。它的值是从1开始的
    但是他却不受到sql中order by排序的影响
 */
  select rownum,e.* from  ( select * from emp where deptno = 30 order by sal desc ) e where rownum <=5 
--9.查询所有员工中工资排序在6到10名之间的员工信息。
/*
   页数1，每页显示行数: 5
   结束范围: <= 页数* 显示行数
   开始范围: > (页数-1) * 显示行数
*/
  select * from ( select rownum rn,e.* from ( select * from emp order by sal desc ) e where rownum <= 2 * 5 ) rn where rn > (2 - 1) * 5 

 /*笛卡尔积
   笛卡尔积――是关系的乘法，
   它将分别来自两个数据集合中的行
   以所有可能的方式进行组合。
   A  = {1, 2, 3}
   B =  {4, 5, 6}
   A * B = {
      (1,4), (1, 5), (1, 6)
      (2, 4), (2, 5), (2, 6)
      (3, 4), (3, 5), (3, 6)
   }
   3 * 3 = 9
 */
--1.显示10部门下所有员工的部门名称，
--员工姓名，工资(emp,dept)
--select * from dept;
--select * from emp;
-- dept * emp = 4 * 14 = 56
select d.dname,e.ename,e.sal
 from emp e,dept d  -- 得到emp,dept数据的笛卡尔积
where e.deptno = d.deptno -- 通过where条件消除笛卡尔积
 and e.deptno = 10;
--2.查询工资高于自己部门平均工资的员工的姓名，工资信息
-- 先求每个部门的平均工资
select deptno,avg(sal) dept_sal_avg from emp
group by deptno
-- 拿员工工资和员工所在部门的平均工资做比较
select e.ename,e.sal,dsa.dept_sal_avg from emp e,(
  select deptno,avg(sal) dept_sal_avg from emp
  group by deptno
) dsa where  e.deptno = dsa.deptno and sal > dsa.dept_sal_avg  


连接运算:
  连接运算是由一个笛卡尔积运算和一个选取运算构成的。
  首先用笛卡尔积完成对两个数据集合的乘运算，
  然后对生成的结果集合进行选取运算，
  确保只把分别来自两个数据集合并且具有重叠部分(符合选取运算)的行合并在一起。

left[outer] join(左连接) 
  返回包括左表(集合)中的所有记录和右表(集合)中联结字段相等的记录
right[outer] join(右连接) 
  返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 
  只返回两个表中联结字段相等的行
-- 外连接：
-- 学生信息表
create table t_student(
       f_id int,
       f_name varchar2(30)
);

-- 创建一个考试成绩表(字段:成绩编号，科目，分数，学生编号)
create table t_exam(
       f_id int,
       f_subject varchar2(30),
       f_score  number(3,1),
       f_student_id int
);

select * from t_exam;
select * from t_student;
insert into t_student values(1,'张三');
insert into t_student values(2,'李四');
insert into t_student values(3,'王五');
insert into t_student values(4,'赵六');

insert into t_exam values(1,'Java',99,1);
insert into t_exam values(2,'C++',99,1);
insert into t_exam values(3,'Java',98,2);
insert into t_exam values(4,'Java',59,3);
insert into t_exam values(5,'Java',49,5);

delete from t_exam;

--3.左外联接
select s.f_name,e.f_subject,e.f_score from t_student s
left join t_exam e 
on
s.f_id = e.f_student_id;

--4.右外联接
select s.f_name,e.f_subject,e.f_score from t_student s
right join t_exam e 
on
s.f_id = e.f_student_id;

--5.内(等值)连接
select s.f_name,e.f_subject,e.f_score from t_student s
inner join t_exam e 
on
s.f_id = e.f_student_id;

/*
       提示:
       select * from emp e inner join dept d on e.deptno = d.deptno;
       相当于:
       select * from emp e,dept d where e.deptno = d.deptno;
*/
--6.自连接
--查询员工‘SMITH’ 的上级领导的姓名
select p.ename from emp e,emp p
    where e.ename='SMITH' and e.mgr = p.empno;

--7.查询每一个学生的姓名、考试的科目数、平均成绩

/*
       nvl(value1, replace_with)
　　  功能：如果value1为NULL，则nvl函数返回replace_with的值，否则返回value1的值。 
*/
select f_name,( select count(*) from t_exam where f_student_id = ts.f_id) subject_nums,
(  select nvl(avg(f_score), 0) from t_exam where f_student_id = ts.f_id) avg_score
from t_student ts