oracle 5表关联查询,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;

--查询scott所在的部门名称

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:满足一部分即可

*/