首页 > 代码库 > Oracle - 查询语句 - 多表关联查询
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:满足一部分即可*/
Oracle - 查询语句 - 多表关联查询
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。