Oracle下SQL基本操作(三)(8)

----伪列
--ROWID用它来唯一标识一行数据（与磁盘地址对应）,默认按ROWID排序
SELECT EMP.EMPLOYEEID,ROWID FROM EMPLOYEE EMP;
---ROWNUM对查询结果集依次编号 （默认从1开始）
SELECT EMP.EMPLOYEEID,ROWID,ROWNUM FROM EMPLOYEE EMP;
---编号打乱了，因为一开始它会默认按ROWID或SELECT后的第一关键字排序后，
---就把ROWNUM编好号,如果再按其他关键字排序，则编号就打乱了。
SELECT  EMP.NAME,EMP.NAME,ROWID,ROWNUM,EMP.EMPLOYEEID
 FROM EMPLOYEE EMP
 ORDER BY EMP.NAME
---TOP_N查询
--1先排序后作为一个子结果，2再查询
SELECT  SUB.EMPLOYEEID,SUB.SALARY,ROWNUM
    FROM (SELECT EMP.EMPLOYEEID,EMP.SALARY
             FROM EMPLOYEE EMP
             ORDER BY SALARY) SUB
   WHERE ROWNUM <= 3;
  
---ROWNUME要想使用>或>=操作它，只能先为它起别名才行
---翻页查询
  SELECT  SUBB.EMPLOYEEID,SUBB.SALARY,SUBB.RN
  FROM
  ( SELECT  SUB.EMPLOYEEID,SUB.SALARY,ROWNUM  RN
    FROM (SELECT EMP.EMPLOYEEID,EMP.SALARY
             FROM EMPLOYEE EMP
             ORDER BY SALARY) SUB)  SUBB
   WHERE RN <= 5 AND RN >=2;
 
 
---相关的子查询
---一个部门中，工资最高的员工ID
 SELECT OUTEMP.EMPLOYEEID
     FROM EMPLOYEE OUTEMP
     WHERE OUTEMP.SALARY = (
      SELECT MAX(INEMP.SALARY)
         FROM EMPLOYEE INEMP
         WHERE OUTEMP.DEPTID = INEMP.DEPTID)
        
---EXISTS
---如果一个部门中有成员，则输出这个部门的ID
SELECT DEPT.DEPTID
  FROM DEPARTMENT DEPT
  WHERE EXISTS (
  SELECT 'AA'
   FROM EMPLOYEE EMP
     WHERE DEPT.DEPTID = EMP.DEPTID)
 

-----集合操作
SELECT * FROM EMPLOYEE FOR UPDATE;
SELECT * FROM DEPARTMENT;
---0918  02 市场部  一行为重复
--UNION 并( 去重)
SELECT  EMP.EMPLOYEEID,EMP.NAME   FROM EMPLOYEE EMP
UNION
SELECT  DEP.DEPTID,DEP.DEPTNAME   FROM DEPARTMENT DEP
----UNION 并( 不去重)
SELECT  EMP.EMPLOYEEID,EMP.NAME   FROM EMPLOYEE EMP
UNION ALL
SELECT  DEP.DEPTID,DEP.DEPTNAME   FROM DEPARTMENT DEP
--INTERSECT交
SELECT  EMP.EMPLOYEEID,EMP.NAME   FROM EMPLOYEE EMP
INTERSECT
SELECT  DEP.DEPTID,DEP.DEPTNAME   FROM DEPARTMENT DEP