Oracle进阶（五）常用查询

【摘要】 1、常用SQL语法
点击链接了解SQL基础语法
1.1 进阶查询
–1 ANY –查询工资比部门编号为30的任意员工工资高的员工SELECT ENAME, SAL, DEPTNO FROM EMP WHERE SAL> ANY (SELECT SAL FROM EMP WHERE DEPTNO=’30’);– 等价SELECT ENAME, SAL, DEPTNO…

1、常用SQL语法

点击链接了解SQL基础语法

1.1 进阶查询

      --1 ANY 

      --查询工资比部门编号为30的任意员工工资高的员工

      SELECT ENAME, SAL, DEPTNO FROM EMP WHERE SAL> ANY (SELECT SAL FROM EMP WHERE DEPTNO='30');

      -- 等价

      SELECT ENAME, SAL, DEPTNO FROM EMP WHERE SAL > (SELECT MIN(SAL) FROM EMP WHERE DEPTNO='30');

      --2 查询与SMITH的部门编号和岗位相等的所有员工信息

      SELECT * FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME='SMITH') AND JOB=(SELECT JOB FROM EMP WHERE ENAME='SMITH');

      -- 等价

      SELECT * FROM EMP WHERE (DEPTNO, JOB) = (SELECT DEPTNO, JOB FROM EMP WHERE ENAME='SMITH');

      --3 查找每个部门工资最高的员工

      SELECT E.ENAME, E.SAL, T.DEPTNO FROM EMP E, (SELECT MAX(SAL) MAXSAL, DEPTNO FROM EMP GROUP BY DEPTNO) T WHERE E.SAL >=T.MAXSAL AND E.DEPTNO=T.DEPTNO;

      -- 等价

      SELECT * FROM EMP WHERE SAL IN(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);

      --4 根据查询结果创建新表

      CREATE TABLE TEMP (EMP_ID, EMP_NAME) AS SELECT EMPNO,ENAME FROM EMP;

      --5 自我复制数据

      INSERT INTO TEMP (EMP_ID, EMP_NAME) SELECT EMP_ID,EMP_NAME FROM TEMP;

      --6 UNION并集

      -- 查询工资大于2500或工作等于MANAGER的员工

      SELECT ENAME, SAL, JOB FROM EMP WHERE SAL>2500

      UNION

      SELECT ENAME, SAL, JOB FROM EMP WHERE JOB='MANAGER';

      -- 等价

      SELECT ENAME, SAL, JOB FROM EMP WHERE SAL>2500 OR JOB='MANAGER';

      --7 INTERSECT交集

      -- 查询工资大于2500且工作等于MANAGER的员工

      SELECT ENAME, SAL, JOB FROM EMP WHERE SAL>2500

      INTERSECT

      SELECT ENAME, SAL, JOB FROM EMP WHERE JOB='MANAGER';

      -- 等价

      SELECT ENAME, SAL, JOB FROM EMP WHERE SAL>2500 AND JOB='MANAGER';

      --8 MINUS差集

      -- 查询工资大于2500且工作不等于MANAGER的员工

      SELECT ENAME, SAL, JOB FROM EMP WHERE SAL>2500

      MINUS

      SELECT ENAME, SAL, JOB FROM EMP WHERE JOB='MANAGER';

      --等价

      SELECT ENAME, SAL, JOB FROM EMP WHERE SAL>2500 AND JOB<>'MANAGER';

      --*************9 层次查询*****************************

      --9.1、START WITH ...... CONNECT BY

      --注：START WITH: 指定起始节点条件

      --CONNECT BY: 指定父子行的条件关系

      --找到职位PRESIDENT的下级以及下级的下级

      SELECT EMPNO, ENAME, JOB, MGR, DEPTNO, LEVEL FROM EMP

      START WITH JOB='PRESIDENT'

      CONNECT BY PRIOR EMPNO= MGR;

      SELECT EMPNO, CAST(LPAD(' ', LEVEL*2-1,' ')||ENAME AS VARCHAR2(20)) ENAME, JOB, MGR, DEPTNO, LEVEL

      FROM EMP

      START WITH JOB='PRESIDENT'

      CONNECT BY PRIOR EMPNO= MGR;

      --找到职位PRESIDENT的上级及上级的上级

      SELECT EMPNO, ENAME, JOB, MGR, DEPTNO, LEVEL FROM EMP

      START WITH JOB='PRESIDENT'

      CONNECT BY  MGR = PRIOR EMPNO;

      --9.2、SYS_CONNECT_BY_PATH ( COLUMN , CHAR )

      --从雇员KING开始，查出所有下级雇员相关信息以及雇员名路径用’/'连接）

      SELECT EMPNO,

      CAST(LPAD(' ', LEVEL * 2 - 1, ' ') || ENAME AS VARCHAR2(20)) ENAME,

       JOB,

       MGR,

       DEPTNO,

      LEVEL,

      CAST(SYS_CONNECT_BY_PATH(ENAME, '/') AS VARCHAR2(40)) NAMEPATH

       FROM EMP

      START WITH ENAME = 'KING'

      CONNECT BY PRIOR EMPNO = MGR;

      --9.3、CONNECT_BY_ROOT 查找根节点

      SELECT EMPNO,

      CAST(LPAD(' ', LEVEL * 2 - 1, ' ') || ENAME AS VARCHAR2(20)) ENAME,

       JOB,

       MGR,

       DEPTNO,

      LEVEL,

      CAST(SYS_CONNECT_BY_PATH(ENAME, '/') AS VARCHAR2(40)) NAMEPATH,

      CONNECT_BY_ROOT(EMPNO) ROOTNO

       FROM EMP

      START WITH ENAME IN ('JONES','BLAKE','CLARK')

      CONNECT BY PRIOR EMPNO = MGR;

      --9.4、NOCYCLE、CONNECT_BY_ISCYCLE 让语句不循环 是否循环

      --注：CONNECT_BY_ISCYCLE 为1表示是循环的点

      SELECT EMPNO, ENAME, JOB, MGR, DEPTNO, LEVEL,CONNECT_BY_ISCYCLE ISCYCLE

      FROM EMP

      START WITH JOB = 'PRESIDENT'

      CONNECT BY NOCYCLE PRIOR EMPNO = MGR;

      --9.5、CONNECT_BY_ISLEAF 是否叶子节点,1为是，0为否；叶子节点可以理解为最后一层

      SELECT EMPNO,

      CAST(LPAD(' ', LEVEL * 2 - 1, ' ') || ENAME AS VARCHAR2(20)) ENAME,

       JOB,

       MGR,

       DEPTNO,

      LEVEL,

      CAST(SYS_CONNECT_BY_PATH(ENAME, '/') AS VARCHAR2(40)) NAMEPATH,

      CONNECT_BY_ISLEAF ISLEAF

       FROM EMP

      START WITH ENAME = 'KING'

      CONNECT BY PRIOR EMPNO = MGR;

      --9.6 层级关联

      -- 根据员工的上级编号进行层级关联

      SELECT LEVEL,EMPNO,ENAME,MGR

      FROM EMP

      CONNECT BY PRIOR EMPNO = MGR

      START WITH MGR IS NULL

      ORDER BY LEVEL;

      --****************************************

      --10 正则替换

      SELECT REGEXP_REPLACE('ADSFAAD','[^A]','0') FROM DUAL;

      --11 字符串分列

      SELECT

       REGEXP_SUBSTR('192.168.0','[^.]+',1,1) A1

       ,REGEXP_SUBSTR('192.168.0','[^.]+',1,2) A2

       ,REGEXP_SUBSTR('192.168.0','[^.]+',1,3) A3

      FROM DUAL;

      --12 计算字符出现数量

      SELECT REGEXP_COUNT('ADSFAAD','A') FROM DUAL;

      SELECT LENGTH(REGEXP_REPLACE('ADSFAAD','[^A]')) FROM DUAL;

      SELECT TRANSLATE('ADSFAAD','A'||'ADSFAAD','A') FROM DUAL;

      --13 删除不需要字符

      SELECT REPLACE(TRANSLATE(JOB,'MAN','AAA'),'AAA','') FROM EMP;

      SELECT REGEXP_REPLACE(JOB,'[MAN]','') FROM EMP;

      --14 字符数字分离

      SELECT

       REGEXP_REPLACE('SDFSF04','[0-9]','') 字符

       ,REGEXP_REPLACE('SDFSF04','[^0-9]','') 数字

      FROM DUAL;

      SELECT

      TRANSLATE('SDFSF04','A0123456789','A') 字符

       ,TRANSLATE('SDFSF04','0123456789'||'SDFSF04','0123456789') 字符

      FROM DUAL;

      --15 界定符

      -- 带单引号字符

      SELECT 'ADSF''ASFADS' FROM DUAL;

      --SELECT Q'{'}' FROM DUAL;

      --16 LISTAGG() WITHIN GROUP

      --把一列字符组合在一起

      SELECT

        E.DEPTNO

        ,LISTAGG(E.ENAME,',') WITHIN GROUP (ORDER BY E.EMPNO) NAMES

      FROM EMP E

      GROUP BY E.DEPTNO;

      --累计

      SELECT

        E.*

        ,SUM(SAL) OVER(PARTITION BY E.DEPTNO ORDER BY HIREDATE)

        ,(SELECT LISTAGG(SAL,'+') WITHIN GROUP (ORDER BY E2.HIREDATE)

      FROM EMP E2

      WHERE E2.DEPTNO = 30

      AND E2.HIREDATE <= E.HIREDATE

       )-- E.HIREDATE的数据迭代进入计算，迭代出

      FROM EMP E

      WHERE E.DEPTNO =30;

      --17 OVER

      --员工信息按各部门薪资降序排列

      SELECT

         EMPNO

         ,ENAME

         ,SAL

         ,DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) SAL_RANK

      FROM EMP;

      --18 KEEP

      -- 显示各部门最高工资的员工信息

      SELECT

         DEPTNO

         ,MIN(SAL) KEEP (DENSE_RANK FIRST ORDER BY SAL DESC) TOP_SAL

      FROM

         EMP

      GROUP BY DEPTNO;

      --19 LEVEL

      SELECT LEVEL FROM EMP CONNECT BY LEVEL<=1;

      SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 4;

      --20 GROUP BY ROLLUP（A，B）

      --注：分组三次 1 A，B条件分组 2 A条件分组 3 无条件分组

      SELECT DEPTNO, JOB, SUM(SAL) FROM EMP GROUP BY ROLLUP(DEPTNO, JOB);

      --等价

      SELECT DEPTNO, JOB, SUM(SAL) FROM EMP GROUP BY DEPTNO, JOB

      UNION ALL

      SELECT DEPTNO,TO_CHAR(''), SUM(SAL) FROM EMP GROUP BY DEPTNO

      UNION ALL

      SELECT TO_NUMBER(''),TO_CHAR(''),SUM(SAL) FROM EMP

      --21 WITH

      --求每个部门的平均工资,以及剔除薪资低于1000的实习人员之后的平均工资

      WITH TMP1 AS

      (SELECT E1.DEPTNO, ROUND(AVG(NVL(E1.SAL, 0)), 2) AVG_SAL

      FROM EMP E1

      GROUP BY E1.DEPTNO),

      TMP2 AS

      (SELECT E1.DEPTNO, ROUND(AVG(NVL(E1.SAL, 0)), 2) AVG_SAL

      FROM EMP E1

      WHERE E1.SAL > 1000

      GROUP BY E1.DEPTNO)

      SELECT D.DEPTNO, TMP1.AVG_SAL AVG_SAL1, TMP2.AVG_SAL AVG_SAL2

      FROM DEPT D

      LEFT JOIN TMP1

      ON D.DEPTNO = TMP1.DEPTNO

      LEFT JOIN TMP2

      ON D.DEPTNO = TMP2.DEPTNO;

  


                    
        
© 版权声明
文章版权归作者所有，未经允许请勿转载。
THE END
数据库