oracle--4

1、自连接：

列出所有雇员的姓名及其上级的姓名。
select e.ename, m.ename mname  from emp e ,emp m where e.mgr =m.empno(+);

列出入职日期早于其直接上级的所有雇员。
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;



2、列出从事“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';


3、求出部门编号为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;


4、行转列

   1）、如果course='语文'，其显示内容为score，否则为null
    select name,
      decode(course, '语文', score) "语文"
      from tb_student;

   2）、查询  课程名称 获取
      select distinct course from tb_student;
    decode +分组
      select * from tb_student;

   3）、行转列
    select name,
           min(decode(course, '语文', score)) "语文",
           min(decode(course, '英语', score)) "英语",
           min(decode(course, '数学', score)) "数学"
      from tb_student
     group by name;


 

5、

99语法:概念明确 、连接条件与过滤条件 分开
注意：自然连接、using 连接 相同列名 共同所有 不能 使用表的别名访问
1、交叉连接  cross join                    -->笛卡尔积
2、自然连接(主外键|列名相同)  natural join  -->等值连接
   相同列名 共同所有 不能 使用表的别名访问
3、using 连接(列名相同)                    -->等值连接
    相同列名 共同所有 不能 使用表的别名访问
4、on  连接  解决一切       -->等值连接 非等值 自连接
   相同的列名必须区分
5、外连接 :确保主表的记录至少出现一次，从表不匹配 补充null
    left join  on|using
    right join on|using   
6、全连接: 确保 所有表的记录 至少出现一次，匹配，直接使用等值 ，不匹配相互补充null
   full join  on|using

--1、交叉连接
select * from emp cross join dept;
--2、自然连接(主外键|列名相同)  natural join  -->等值连接
select * from emp natural join dept;
--3、using连接(主外键|列名相同)    -->等值连接
select * from emp join dept using(deptno);
--4、on 连接 -->等值连接   相同的列名必须区分
select emp.deptno,empno,ename,dname from emp join dept on  emp.deptno =dept.deptno;
--4、on连接  -->非等值  员工的工资等级(一定范围内)
select ename, grade
  from emp e
  join salgrade s
    on e.sal between s.losal and s.hisal;
--4、on连接    -->自连接   员工的上级名称
select e.ename,m.ename mname  from emp e join emp m  on e.mgr =m.empno;
--5、所有部门的员工数
--员工数
select count(*),deptno from emp group by deptno;
--左外
select dname ,c from dept d left join (select count(*) c, deptno from emp group by deptno) e
 using (deptno);
select dname ,c from dept d left join (select count(*) c, deptno from emp group by deptno) e
 on d.deptno=e.deptno;
 --右外
 select dname, c
   from (select count(*) c, deptno from emp group by deptno) e
  right join dept d
  using (deptno);

----11.求出部门编号为20的雇员名、部门名、薪水等级
--雇员名 emp
--部门名 dept
--薪水等级 salgrade
--部门编号为20 过滤条件
select ename, dname, grade
  from emp e natural
  join dept d
  join salgrade s
    on e.sal >= s.losal
   and e.sal <= s.hisal
 where deptno = 20;

select ename, dname, grade
  from emp e
  join dept d using(deptno)
  join salgrade s
    on e.sal >= s.losal
   and e.sal <= s.hisal
 where deptno = 20;

select ename, dname, grade
  from emp e
  join dept d
    on e.deptno = d.deptno
  join salgrade s
    on e.sal >= s.losal
   and e.sal <= s.hisal
 where e.deptno = 20;
 
--创建表 了解 全连接
create table tb_test1(
     id number(1),
     name varchar2(2)
)
insert into tb_test1 values(1,'a');
insert into tb_test1 values(2,'b');
insert into tb_test1 values(4,'f');
commit;
create table tb_test2(
     id number(1),
     name varchar2(2)
)
insert into tb_test2 values(1,'c');
insert into tb_test2 values(3,'d');
commit;
 

select * from tb_test1;
select * from tb_test2;
--左链
select id,t1.name,t2.name from tb_test1 t1 left join tb_test2 t2 using (id);  
--全连接
select id,t1.name,t2.name from tb_test1 t1 full join tb_test2 t2 using (id);
 
--92如何实现 全连接？