暑假实训-4_Oracle数据库-4_多表连接

-- 2018/07/12 8:30-11:30 实训第3天上午

SELECT * FROM emp;  -- 员工表
SELECT deptno 部门编号, dname 部门名称, loc 工作地点 FROM dept;  -- 部门表
SELECT grade 工资等级, losal 最低工资, hisal 最高工资 FROM salgrade;  -- 工资等级表

-- 多表连接

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;

-- 笛卡尔积
-- 第一个表的所有行和第二个表中的所有行都发生连接
SELECT emp.empno, emp.ename, emp.deptno, dept.deptno, dept.loc
FROM emp, dept;

-- 用等值连接检索数据
SELECT emp.empno, emp.ename, emp.deptno, dept.deptno, dept.loc
FROM emp, dept
WHERE emp.deptno = dept.deptno;

-- 使用AND运算符增加其他查询条件
SELECT emp.empno, emp.ename, emp.deptno, dept.deptno, dept.loc
FROM emp, dept
WHERE emp.deptno = dept.deptno AND loc = 'NEW YORK';

-- 限制歧义列名 使用表名作前缀 别名
SELECT e.empno, e.ename, e.deptno, e.deptno, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno;

-- 1-1 写一个查询，显示所有员工姓名，部门编号，部门名称
SELECT ename 员工姓名, emp.deptno 部门编号, dept.dname 部门名称
FROM emp, dept
WHERE emp.deptno = dept.deptno;

-- 1-2 写一个查询，显示所有工作在CHICAGO并且奖金不为空的员工姓名，工作地点，奖金
SELECT ename 员工姓名, dept.loc 工作地点, sal 奖金
FROM emp, dept
WHERE emp.deptno = dept.deptno AND comm IS NOT NULL AND loc = 'CHICAGO';

-- 1-3 写一个查询，显示所有姓名中含有A字符的员工姓名，工作地点
SELECT ename 员工姓名, loc 工作地点
FROM emp, dept
WHERE emp.deptno = dept.deptno AND INSTR (ename, 'A') > 0;

-- 多于两个表的连接
-- 非等值连接的数据检索
-- 查询每个员工的姓名，工资，工资等级
SELECT e.ename 员工姓名, e.sal 工资, s.grade 工资等级
FROM emp e,   salgrade s
WHERE e.sal >= s.losal AND e.sal <= s.hisal;
   -- e.sal BETWEEN s.losal AND s.hisal;

-- 多表连接的写法
-- 1 分析表的来源构成FROM子句
-- 2 分析表间关系，如果两表通过第三张表关联，FROM子句中也要包含关联表 FROM emp, (SELECT ... ) dd
-- 3 WHERE子句中补充表间关系，N个表对应N-1个关系
-- 4 分析是否还有其它限制条件，补充到WHERE子句的表关联关系之后，作为限制条件
-- 5 根据用户想要显示的信息，补充SELECT子句
-- 6 分析是否有排序要求，如果排序要求中还涉及到其它表，则也要进行第2步补充排序字段所在的表，并且添加表之间的关联关系

-- 2-1 查询每个员工的编号，姓名，工资，工资等级，所在工作城市，按照工资等级进行升序排序
SELECT empno 员工编号, ename 员工姓名, sal 工资, s.grade 工资等级, loc 工作地点
FROM emp e, salgrade s, dept d
WHERE e.deptno = d.deptno AND (sal BETWEEN s.losal AND s.hisal)
ORDER BY s.grade;

-- 外部连接
SELECT  table1.column, table2.column
FROM    table1, table2
WHERE   table1.column(+) = table2.column;

SELECT  table1.column, table2.column
FROM    table1, table2
WHERE   table1.column = table2.column(+);

SELECT  e.ename, d.deptno, d.dname
FROM    emp e,   dept d
WHERE   e.deptno(+) = d.deptno  -- 新表包含所有d中的deptno
ORDER BY    e.deptno;

SELECT  e.ename, d.deptno, d.dname
FROM    emp e,   dept d
WHERE   e.deptno = d.deptno(+)  -- 新表包含所有e中的deptno
ORDER BY    e.deptno;

-- 自身连接
SELECT worker.ename 员工姓名, manager.ename 经理姓名 
FROM emp worker, emp manager
WHERE worker.mgr = manager.empno(+);

-- 3-1 查询所有工作在NEW YORK和CHICAGO的员工姓名，员工编号，以及他们的经理姓名，经理编号
SELECT w.ename 员工姓名, w.empno 员工编号, m.ename 经理姓名, m.empno 经理编号
FROM emp w, emp m, dept d
WHERE w.mgr = m.empno(+) AND w.deptno = d.deptno AND d.loc IN ('NEW YORK', 'CHICAGO');

-- 3-2 第上一题的基础上，添加没有经理的员工King，并按照员工编号排序
SELECT w.ename 员工姓名, w.empno 员工编号, m.ename 经理姓名, m.empno 经理编号
FROM emp w, emp m, dept d  -- worker manager
WHERE w.mgr = m.empno(+) AND w.deptno = d.deptno AND d.loc IN ('NEW YORK', 'CHICAGO')
ORDER BY w.empno;

-- 3-3 查询所有员工编号，姓名，部门名称，包括没有部门的员工也要显示出来
SELECT empno, ename, dname
FROM emp e, dept d
WHERE e.deptno = d.deptno(+);

-- 交叉连接 会产生交叉乘积，和笛卡尔积是一样的
SELECT  emp.empno,emp.ename,emp.sal,emp.deptno,dept.loc 
FROM    emp 
CROSS  JOIN dept; 

-- 自然连接 对两个表之间相同名字和数据类型的列进行等值连接 如果同名不同类型将会产生错误
SELECT  empno,ename,sal,deptno,loc 
FROM    emp 
NATURAL JOIN    dept; 

-- USING子句 指定用某个或某几个相同名字和数据类型的列作为连接条件
-- NATURAL JOIN子句和USING子句是相互排斥的，不能同时使用
SELECT  e.ename,e.ename,e.sal,deptno,d.loc 
FROM    emp e JOIN dept d USING (deptno) 
WHERE   deptno = 20 ;

-- ON子句 用ON将连接条件和其它检索条件分隔开，其它检索条件写在WHERE子句
-- ON子句可以提高代码的可读性
SELECT  e.empno, e.ename, e.deptno, d.deptno, d.loc 
FROM    emp e 
JOIN    dept d 
ON      (e.deptno = d.deptno); 

SELECT  e.empno 员工编号, e.ename 员工姓名, d.loc 工作地点, m.ename 经理姓名
FROM        emp e 
JOIN        dept d 
ON          e.deptno = d.deptno 
JOIN        emp m 
ON          e.mgr = m.empno; 

-- 左外连接
SELECT  e.ename,e.deptno,d.loc 
FROM    emp e 
LEFT OUTER JOIN dept d 
ON      (e.deptno = d.deptno); 

-- 右外连接
SELECT  e.ename,e.deptno,d.loc 
FROM    emp e 
LEFT OUTER JOIN dept d 
ON      (e.deptno = d.deptno); 

-- 全外连接
SELECT  e.ename,e.deptno,d.loc 
FROM        emp e 
FULL   OUTER JOIN dept d 
ON          (e.deptno = d.deptno); 

-- 使用SQL-99写法，完成如下练习
-- 4-1 创建一个员工表和部门表的交叉连接
SELECT *
FROM emp
CROSS JOIN dept;

-- 4-2 使用自然连接，显示入职日期在80年5月1日之后的员工姓名，部门名称，入职日期
SELECT ename, dname, hiredate
FROM emp
NATURAL JOIN dept;

-- 4-3 使用USING子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点
SELECT ename, dname, loc, grade
FROM emp e JOIN dept d USING (deptno), salgrade s
WHERE sal BETWEEN s.losal AND s.hisal;

-- 4-4 使用ON子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点，薪资等级
SELECT ename, dname, loc, grade
FROM emp e JOIN dept d ON e.deptno = d.deptno, salgrade s
WHERE (sal BETWEEN s.losal AND s.hisal) AND loc = 'CHICAGO';

-- 4-5 使用左连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来
SELECT w.ename 员工姓名, m.ename 经理姓名
FROM emp w
LEFT OUTER JOIN emp m
ON (w.mgr = m.empno);

-- 4-6 使用右连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来
SELECT w.ename 员工姓名, m.ename 经理姓名
FROM emp m
RIGHT OUTER JOIN emp w
ON (w.mgr = m.empno);

-- 课后作业
-- 1 显示员工SMITH的姓名，部门名称，直接上级名称
SELECT w.ename 员工名字, dname 部门名称, m.ename 直接上级名称
FROM emp w ,emp m, dept d
WHERE w.ename = 'SMITH'
AND w.deptno = d.deptno(+)
AND w.mgr = m.empno(+);

-- 2 显示员工姓名，部门名称，工资，工资级别，要求工资级别大于4级
SELECT ename 员工姓名, dname, sal, grade
FROM emp e, dept d, salgrade s
WHERE e.deptno = d.deptno(+)
AND (sal BETWEEN s.losal AND s.hisal)
AND grade > 4;

-- 3 显示员工KING和FORD管理的员工姓名及其经理姓名
SELECT w.ename 员工姓名, m.ename 经理姓名
FROM emp w, emp m
WHERE w.mgr = m.empno(+)
AND m.ename IN ('KING', 'FORD');

-- 4 显示员工姓名，参加工作时间，经理名，参加工作时间，要求参加时间比经理早
SELECT w.ename 员工姓名, w.hiredate 入职时间, m.ename 经理姓名, m.hiredate 入职时间
FROM emp w, emp m
WHERE w.mgr = m.empno(+)
AND w.hiredate < m.hiredate;