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;