Oracle 表连接查询

/*
CROSS　JOIN
NATURAL JOIN
USING子句
ON子句
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
INNER JOIN

*/
--CROSS JOIN 等同于92语法中的笛卡儿积
SELECT * FROM EMP CROSS JOIN DEPT;
--NATURAL JOIN  相当于是等值连接，但是注意，不需要写连接条件，会从两张表中找到相同的列做连接
--当两张表中不具有相同的列名的时候，会进行笛卡儿积操作,自然连接跟92语法的自连接没有任何关系
SELECT * FROM EMP E NATURAL JOIN DEPT D ;
SELECT * FROM EMP E NATURAL JOIN SALGRADE SG;
--ON子句，可以添加任意的连接条件，
--添加连接条件 相当于92语法中的等值连接
SELECT * FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
--相当于92语法中的非等值连接，
SELECT * FROM EMP E JOIN SALGRADE SG ON E.SAL BETWEEN SG.LOSAL AND SG.HISAL;
--LEFT OUTER JOIN ,会把左表中的全部数据正常显示，右表没有对应的数据直接显示空即可
SELECT * FROM EMP E LEFT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
SELECT * FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO(+);
--RIGHT OUTER JOIN ,会把右表中的全部数据正常显示，左表中没有对应的记录的话显示空即可
SELECT * FROM EMP E RIGHT OUTER JOIN DEPT  D ON E.DEPTNO = D.DEPTNO;
SELECT * FROM EMP E,DEPT D WHERE E.DEPTNO(+) = D.DEPTNO;
--FULL OUTER JOIN ,相当于左外连接和右外连接的合集
SELECT * FROM EMP E FULL OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
--INNER JOIN，两张表的连接查询，只会查询出有匹配记录的数据
SELECT * FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
SELECT * FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
--USING,除了可以使用ON表示连接条件之外，也可以使用USING作为连接条件,此时连接条件的列不再归属于任何一张表
SELECT * FROM EMP E JOIN DEPT D USING(DEPTNO);
SELECT * FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
--总结:两种语法的SQL语句没有任何限制，再公司中可以随意使用，但是建议使用99语法，不要使用92语法，SQL显得清楚明了

--检索雇员名字、所在单位、薪水等级
SELECT E.ENAME, D.LOC, SG.GRADE FROM EMP E  JOIN DEPT D  ON E.DEPTNO = D.DEPTNO JOIN SALGRADE SG ON E.SAL BETWEEN SG.LOSAL AND SG.HISAL;

--子查询： 嵌套再其他SQL语句中的完整SQL语句，可以称之为子查询
--分类：单行子查询/多行子查询
--有哪些人的薪水是在整个雇员的平均薪水之上的
--1、先求平均薪水
SELECT AVG(E.SAL) FROM EMP E;
--2、把所有人的薪水与平均薪水比较
SELECT * FROM EMP E WHERE E.SAL > (SELECT AVG(E.SAL) FROM EMP E); 
--我们要查在雇员中有哪些人是经理人，DISTINCT关键字去重
SELECT * FROM EMP E WHERE E.EMPNO IN (SELECT DISTINCT E.MGR FROM EMP E);
--每个部门平均薪水的等级
--1、先求出部门的平均薪水
SELECT E.DEPTNO,AVG(E.SAL) FROM EMP E GROUP BY E.DEPTNO; 
--2、跟薪水登记表做关联，求出平均薪水的等级
SELECT T.DEPTNO, SG.GRADE FROM SALGRADE SG JOIN (SELECT E.DEPTNO, AVG(E.SAL) VSAL FROM EMP E GROUP BY E.DEPTNO) T ON T.VSAL BETWEEN SG.LOSAL AND SG.HISAL;


--1、求平均薪水最高的部门的部门编号
--求部门的平均薪水
SELECT E.DEPTNO,AVG(E.SAL) FROM EMP E GROUP BY E.DEPTNO;
--求平均薪水最高的部门
SELECT MAX(T.VSAL) FROM (SELECT E.DEPTNO,AVG(E.SAL) VSAL FROM EMP E GROUP BY E.DEPTNO) T
--求部门编号
SELECT DEPTNO FROM (SELECT DEPTNO, AVG(SAL) VSAL FROM EMP GROUP BY DEPTNO) E WHERE E.VSAL=(SELECT MAX(T.VSAL) FROM (SELECT AVG(SAL) VSAL FROM EMP GROUP BY DEPTNO) T)
--求部门信息
SELECT * FROM DEPT D WHERE D.DEPTNO=(SELECT DEPTNO FROM (SELECT DEPTNO, AVG(SAL) VSAL FROM EMP GROUP BY DEPTNO) E WHERE E.VSAL=(SELECT MAX(T.VSAL) FROM (SELECT AVG(SAL) VSAL FROM EMP GROUP BY DEPTNO) T))
--2、求部门平均薪水的等级
--3、求部门平均的薪水等级
--求部门每个人的薪水等级
SELECT E.DEPTNO, SG.GRADE FROM EMP E JOIN SALGRADE SG ON E.SAL BETWEEN SG.LOSAL AND SG.HISAL;
--按照部门求平均等级
SELECT T.DEPTNO, AVG(T.GRADE) FROM (SELECT E.DEPTNO, SG.GRADE FROM EMP E JOIN SALGRADE SG ON E.SAL BETWEEN SG.LOSAL AND SG.HISAL) T GROUP BY T.DEPTNO;
--限制输出，LIMIT，MYSQL中用来做限制输出的，但是ORACLE中不是
--再ORACLE中，如果需要使用限制输出和分页的功能的话，必须要使用ROWNUM，
--但是ROWNUM不能直接使用，需要嵌套使用
--4、求薪水最高的前5名雇员
SELECT * FROM (SELECT * FROM EMP E ORDER BY E.SAL DESC) T1 WHERE ROWNUM <= 5
SELECT * FROM EMP E  WHERE ROWNUM <=5 ORDER BY E.SAL DESC
--5、求薪水最高的第6到10名雇员
SELECT T1.*,ROWNUM FROM (SELECT * FROM EMP E ORDER BY E.SAL DESC) T1 WHERE ROWNUM <= 10
--使用ROWNUM的时候必须要再外层添加嵌套，此时才能将ROWNUM作为其中的一个列，然后再进行限制输出
 SELECT *  FROM (SELECT T1.*, ROWNUM RN FROM (SELECT * FROM EMP E ORDER BY E.SAL DESC) T1 WHERE ROWNUM <= 10) T WHERE T.RN >  5  AND T.RN <= 10;
SELECT * FROM (SELECT T1.*, ROWNUM RN FROM (SELECT * FROM EMP E ORDER BY E.SAL DESC) T1) T  WHERE T.RN > 5  AND T.RN <= 10;