Oracle - 查询语句 - 多表关联查询

****
    /*    
        SQL语句的历史版本
            SQL89
            比较多  
                SQL92
                SQL99
        多表关联查询
            笛卡尔积
            等值关联查询
            非等值关联查询
            左外连接
            右外连接
            全外连接
            自连接
    */
    ----------------------------------92语法
    --查询部门名称和员工名称(两张表的关联没有任何的约束条件)
    SELECT * FROM EMP;
    SELECT * FROM DEPT;
    SELECT * FROM SALGRADE;
    SELECT * FROM EMP,DEPT;
    SELECT * FROM EMP,DEPT,SALGRADE;
    --等值关联查询
    SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;
    SELECT DEPT.DNAME,EMP.ENAME FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;
    --查询sott所在的部门名称
    SELECT DEPT.DNAME FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.ENAME = 'SCOTT';
    --非等值关联查询
    SELECT EMP.ENAME,EMP.SAL,SALGRADE.GRADE FROM EMP,SALGRADE WHERE EMP.SAL BETWEEN SALGRADE.LOSAL AND SALGRADE.HISAL;
    --给表设置一个别名，这个别名只有当前查询有效
    SELECT E.ENAME,E.SAL,S.GRADE FROM EMP E,SALGRADE S WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;
    --查询部门编号，部门名称，员工姓名，员工薪资，员工薪资等级
    SELECT D.DEPTNO,D.DNAME,E.ENAME,E.SAL,S.GRADE FROM EMP E,DEPT D,SALGRADE S
        WHERE E.DEPTNO = D.DEPTNO
            AND E.SAL BETWEEN S.LOSAL AND S.HISAL;
    --外连接:就是为了找回不符合条件的数据
    SELECT DEPTNO,COUNT(*) FROM EMP GROUP BY DEPTNO;
    SELECT D.DEPTNO,D.DNAME,E.ENAME FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO;
    --右外连接
    SELECT D.DEPTNO,D.DNAME,E.ENAME FROM EMP E,DEPT D WHERE E.DEPTNO(+) = D.DEPTNO;
    --左外连接
    SELECT D.DEPTNO,D.DNAME,E.ENAME FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO(+);
    --修改数据
    UPDATE EMP SET DEPTNO = NULL WHERE EMPNO = 7934;
    COMMIT;
    --自连接
    SELECT * FROM EMP;
    --查询当前员工和经理的名字
    SELECT E.ENAME ,E.MGR ,M.ENAME,M.EMPNO FROM EMP E,EMP M
        WHERE E.MGR = M.EMPNO(+);
        
    ----------------------------------99语法
    --笛卡尔积
    SELECT * FROM EMP E,DEPT D;
    SELECT * FROM EMP E CROSS JOIN DEPT D;
    --等值连接(就是自动匹配名字，类型相同的列)
    SELECT * FROM EMP E NATURAL JOIN DEPT D;
    SELECT * FROM EMP E JOIN DEPT D USING(DEPTNO);
    SELECT * FROM EMP E JOIN DEPT D ON(E.DEPTNO = D.DEPTNO);
    --非等值连接
    SELECT * FROM EMP E JOIN SALGRADE S ON(E.SAL BETWEEN S.LOSAL AND S.HISAL);
    --外连接
    SELECT * FROM EMP E LEFT JOIN DEPT D USING(DEPTNO);
    SELECT * FROM EMP E RIGHT JOIN DEPT D USING(DEPTNO);
    SELECT * FROM EMP E FULL JOIN DEPT D USING(DEPTNO);
    --自连接
    SELECT E.ENAME,M.ENAME FROM EMP E JOIN EMP M ON(E.MGR = M.EMPNO);
    SELECT E.ENAME,M.ENAME FROM EMP E LEFT JOIN EMP M ON(E.MGR = M.EMPNO);
    --子查询(作为查询条件)
    SELECT * FROM EMP WHERE SAL = (SELECT MAX(SAL) FROM EMP);
    SELECT * FROM EMP WHERE SAL > (SELECT AVG(SAL) FROM EMP);
    --子查询(作为一张虚拟的表)
    SELECT * FROM EMP E ,(SELECT AVG(SAL) ASL,DEPTNO FROM EMP GROUP BY DEPTNO) T
        WHERE E.SAL>T.ASL
            AND E.DEPTNO = T.DEPTNO;
    --单行子查询
    SELECT * FROM EMP WHERE SAL = (SELECT MAX(SAL) FROM EMP);
    --多行子查询
    SELECT * FROM EMP E WHERE E.SAL IN(SELECT MAX(SAL) FROM EMP WHERE DEPTNO IS NOT NULL GROUP BY DEPTNO);
    /*
        IN:取其中之一相等就Ok
        ALL:必须所有得都满足条件
        SOME:满足一部分即可
    */
    文章转载出处：https://www.cnblogs.com/chenglc/p/6922998.html
****