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
相关新闻>>
- 发表评论
-
- 最新评论 更多>>