oracle--3

/**
表连接：数据来自于多张表 -->92语法
1、笛卡尔积 :对乘
select 字段列表 from 表1，表2....
2、等值连接: 在笛卡尔积的基础上 取 有关系的列 值相等的记录
select 字段列表 from 表1，表2 where 表1.字段 =表2.字段
3、非等值连接:在笛卡尔积的基础上 取 有关系的列 值不等的记录
  != > <  >= <= between and
4、自连接:自己与自己关联 同一张表的关联
5、外连接:确保主表的记录都存在(至少出现一次)，从表如果匹配直接匹配，不匹配补充 null
  1)、分清主次
  2)、从表基础上加入null   +
  根据主表的位置 分为 左外  右外
  左外:主表在左边
  右外:主表在右边
 
*/
--查看 所有雇员的雇员名称、所在部门名称

/**
1、笛卡尔积 :对乘  ,列名相同，必须使用表名|别名区分
*/
select ename, d.deptno, sal
  from (select ename, deptno, sal from emp where deptno = 20) e,
       (select deptno, dname from dept) d;
/**
2、等值连接: 在笛卡尔积的基础上 取 有关系的列 值相等的记录
*/
select ename, d.deptno, sal
  from (select ename, deptno, sal from emp where deptno = 20) e,
       (select deptno, dname from dept) d
 where e.deptno = d.deptno;
 
 /**
3、非等值连接: 在笛卡尔积的基础上 取 有关系的列 值不等的记录
*/
select ename, d.deptno, sal
  from (select ename, deptno, sal from emp where deptno = 20) e,
       (select deptno, dname from dept) d
 where e.deptno != d.deptno;
 
 
 
 
 --查看员工的 工资等级
 select * from salgrade s;
 select ename,sal from emp e;
 select ename,sal,grade from salgrade s, emp e where e.sal between losal and hisal;
/**
4、自连接:在等值连接 基础上，自己与自己关联 同一张表的关联,以不同角度看待
*/
--查看员工的上级名称
--表中只存在上级编号 mgr
--下级表
select empno,ename,mgr from emp e ;
--上级表
select empno,ename from emp m;
--自连接
select e.ename en,m.ename mn from emp e,emp m where e.mgr=m.empno;

--查看 所有部门的员工数、部门名称
--emp 按部门统计记录数
select deptno,count(*) c from emp group by deptno;
--dept 所有部门
select deptno,dname from dept;
--求出 所有部门的员工数、部门名称
--左外:主表在 左边
select dname, nvl(c,0) c
  from dept d, (select deptno, count(*) c from emp group by deptno) e
 where d.deptno = e.deptno(+);
 select dname, nvl(c,0) c
  from dept d, (select deptno, count(*) c from emp group by deptno) e
 where e.deptno(+)=d.deptno;
--右外:主表在 右边
select dname, nvl(c, 0) c
  from (select deptno, count(*) c from emp group by deptno) e, dept d
 where d.deptno = e.deptno(+);
select dname, nvl(c, 0) c
  from (select deptno, count(*) c from emp group by deptno) e, dept d
 where  e.deptno(+)=d.deptno ;




练习题：


--3.找"分析员"员工的员工编号、员工名称、上级名称、部门地址
--员工编号、员工名称 -->emp e 下级
--上级编号  mgr  :上级名称 emp m 上级
--部门地址 dept
--找"分析员" 条件
select e.empno, e.ename, m.ename mname, loc
  from emp e, emp m, dept
 where e.mgr = m.empno
   and e.deptno = dept.deptno
   and e.job = 'ANALYST';



--4.列出所有雇员的姓名及其上级的姓名。
--雇员的姓名 emp e 下级表
--上级的姓名 emp m 上级表
--所有的雇员: 有些雇员没有上级 ，确保所有的记录至少出现一次  外连接
select e.ename, m.ename mname  from emp e ,emp m where e.mgr =m.empno(+);

--5.列出入职日期早于其直接上级的所有雇员。
--入职日期 emp e 下级表
--直接上级的入职日期 emp m 上级表
--早于 <
select e.ename, m.ename mname, e.hiredate eh, m.hiredate mh
  from emp e, emp m
 where e.mgr = m.empno
   and e.hiredate < m.hiredate;




--6.列出所有部门名称及雇员
--部门名称 dept
--雇员 emp
--所有的部门  外连接
select dname,ename,sal,job from dept d,emp e
where d.deptno=e.deptno(+);
--7.列出所有“CLERK”（办事员）的姓名及其部门名称。
--姓名 emp
--部门名称 dept
--所有“CLERK”（办事员） 过滤条件
select ename, dname
  from emp e, dept d
 where e.deptno = d.deptno
   and job = 'CLERK';
--8.列出从事“SALES”（销售）工作的雇员的姓名，假定不知道销售部的部门编号。  
--“SALES” 部门名称 找出部门编号
--子查询
select ename from emp where deptno =(select deptno from dept where dname='SALES');
--表连接
select ename
  from emp e, dept d
 where e.deptno = d.deptno
   and d.dname = 'SALES';
--9.列出在每个部门工作的雇员的数量以及其他信息。
--雇员的数量 emp
--部门信息 dept
--雇员数量 按部门编号统计数量
select deptno,count(*) c from emp group by deptno;
--考虑确保每个部门都存在
select d.*, c
  from (select deptno, count(*) c from emp group by deptno) e, dept d
 where e.deptno(+) = d.deptno;

--10.列出所有雇员的雇员名称、部门名称和薪金。
--雇员名称 薪金  emp
--部门名称 dept
select ename,sal,dname from emp e,dept d
where e.deptno =d.deptno(+);
--11.求出部门编号为20的雇员名、部门名、薪水等级
--雇员名 emp
--部门名 dept
--薪水等级 salgrade
--部门编号为20 过滤条件
select ename, dname, grade
  from emp e, dept d, salgrade s
 where e.deptno = d.deptno
   and e.sal >= s.losal
   and e.sal <= s.hisal
   and e.deptno = 20;


--92 弊端: 概念不清 、连接条件与过滤条件 混为一谈