oracle——笔记——连接查询

转载
mob604756eb17db 2020-02-03 23:43:00
create table emp2 ( empno number(4),
                    ename varchar2(10),
                    job varchar2(9),
                    mgr number(4),
                    hiredate date,
                    sal number(7,2),
                    comm number(7,2),
                    deptno number(2) 
                 );
-----------------------------------------------------------------------------------------------------------

insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800.00, null, 20);

insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600.00, 300.00, 30);

insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250.00, 500.00, 30);

insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975.00, null, 20);

insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250.00, 1400.00, 30);

insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850.00, null, 30);

insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, to_date('09-06-1981', 'dd-mm-yyyy'), 2450.00, null, 10);

insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000.00, null, 20);

insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 5000.00, null, 10);

insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500.00, 0.00, 30);

insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100.00, null, 20);

insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950.00, null, 30);

insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000.00, null, 20);

insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300.00, null, 10);

=============================================================================================================================

create table dept2 ( deptno number(2),
                     dname varchar2(14),
                     loc varchar2(13)
                   );

----------------------------------------------------

insert into dept2 (DEPTNO, DNAME, LOC)
values (10, 'ACCOUNTING', 'NEW YORK');

insert into dept2 (DEPTNO, DNAME, LOC)
values (20, 'RESEARCH', 'DALLAS');

insert into dept2 (DEPTNO, DNAME, LOC)
values (30, 'SALES', 'CHICAGO');

insert into dept2 (DEPTNO, DNAME, LOC)
values (40, 'OPERATIONS', 'BOSTON');

============================================================

create table salgrade2 ( grade number,
                         losal number,
                         hisal number
                       );

--------------------------------------------------

insert into salgrade2 (GRADE, LOSAL, HISAL)
values (1, 700, 1200);

insert into salgrade2 (GRADE, LOSAL, HISAL)
values (2, 1201, 1400);

insert into salgrade2 (GRADE, LOSAL, HISAL)
values (3, 1401, 2000);

insert into salgrade2 (GRADE, LOSAL, HISAL)
values (4, 2001, 3000);

insert into salgrade2 (GRADE, LOSAL, HISAL)
values (5, 3001, 9999);


========================================================

select * from emp2;

select * from dept2;

select * from salgrade2;

=====================================
select * from emp2;        --employee2 员工表
select * from dept2;       --department2 部门表
select * from salgrade2;   -- salary grade2 工资等级表

-----------------------------------------------------
emp2

empno    员工编号
ename    员工姓名
job      工作/工种
mgr      manager上级编号
hiredate 入职日期
sal      salary 工资
comm     奖金/津贴
deptno   部门编号
---------------------

dept2

deptno  部门号
dname   部门名称
loc     所在地
---------------------------
salgrade2

grade                   等级
losal lowest salary     最低工资
hisal high salary       最高工资

=============================================

================================================================================================


多表联合查询/多表连接

 内连接(等值连接、不等值连接)
 外连接(左外、右外、全外)
 自连接
 
等值连接

查询员工姓名和员工所在部门的部门名称:

select *
from emp2,dept2
where emp2.deptno = dept2.deptno; ---连接条件

select ename,dname
from emp2,dept2
where emp2.deptno = dept2.deptno;
   

查询员工编号、入职日期、部门名称

select emp.empno,emp.hiredate,dept.dname
from emp2,dept2
where emp2.deptno = dept2.deptno;
   
select e.empno,e.hiredate,d.dname
from emp2 e,dept2 d
where e.deptno = d.deptno;







查询 SALES 部门(SALES 是部门名称) 的员工信息:

select e.*
from emp2 e,dept2 d
where e.deptno = d.deptno and d.dname = 'SALES';
    




 
查询工作类别是ANALYST 的员工的工资、部门号和部门所在地:

select e.sal,d.deptno,d.loc
from emp2 e,dept2 d
where e.deptno = d.deptno and e.job = 'ANALYST';
 









不等值连接
 
查询每个员工的工资等级:
 
select * from emp2;

select * from salgrade2;
 
select *
from emp2 e,salgrade2 s
where e.sal between s.losal and s.hisal;

 






----------------------------------------------------------------------------








外连接：


作用：查询不满足连接条件的数据

等值查询：两边的表如果都有数值相等，且配对的才会显示出来；

 select * from emp2 e,dept2 d
 where e.deptno = d.deptno;
 
 






右外：  右边的表在左边中如果匹配不到数据，也会显示出来。
 
 select * from dept2;
 
 select * from emp2 e,dept2 d
 where e.deptno(+) = d.deptno;
 






左外： 左边表中的数据如果在右边表中匹配不到数据，也会显示出来；
 
 select * from emp2;

 insert into emp2(empno) values(1122);
 
 select * from emp2 e,dept2 d
 where e.deptno = d.deptno(+); 
 
 delete from emp2 where emp2.empno = 1122;
 
 
 select * from emp2 e,dept2 d
 where e.deptno(+) = d.deptno(+);  ---不存在这种写法 
 





---------------------------------------------------------------------------------------
 






外连接的另外一种写法：


左外：


select * from emp2;

insert into emp2(empno) values(1122);
 
delete from emp2 where emp2.empno = 1122;




select * 
from emp2 e left outer join dept2 d
on e.deptno = d.deptno;                  left    显示左边表不满足条件的数据
                                         outer   可以省略
                                         on      只能写连接条件，其他条件 写到where里
 

                                        
右外：
                                      
select * 
from emp2 e right outer join dept2 d
on e.deptno = d.deptno;   










全外：如果两个表中都有相互匹配不到的数据，则都会显示出来；


select * from emp2;

insert into emp2(empno) values(1122);
 
delete from emp2 where emp2.empno = 1122;


select *    
from emp2 e full outer join dept2 d
on e.deptno = d.deptno;    
   

------------------------------------------------------------------------------------ 
   
查询出没有员工的部门信息：

select d.* 
from emp2 e right outer join dept2 d
on e.deptno = d.deptno
where e.empno is null; 









自连接:

查询员工姓名和他的上级姓名:

select * from emp2;
 
员工的mgr = 上级的empno
 
select *
from emp2 worker,emp2 manager
where worker.mgr = manager.empno;
 
 
  select * from emp2 worker;
  select * from emp2 manager;
  
  






查询出入职比上级早的员工:

select *
from emp2 worker,emp2 manager
where worker.mgr = manager.empno and worker.hiredate < manager.hiredate;