----伪列
--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
--MINUS 差,去掉公共部分
SELECT EMP.EMPLOYEEID,EMP.NAME FROM EMPLOYEE EMP
MINUS
SELECT DEP.DEPTID,DEP.DEPTNAME FROM DEPARTMENT DEP
---sequence创建序列
CREATE SEQUENCE EMPLVL_SEQ
INCREMENT BY 1 --步长
START WITH 1 -- 起始值
NOMAXVALUE --设置没有最大值
NOCYCLE --设置没有循环
CACHE 5; --缓存
---建表
CREATE TABLE EMPLVL
(LVLNO VARCHAR2(2),
LOWSALARY NUMBER,
HIGHSALARY NUMBER);
--EMPLVL_SEQ.NEXTVAL开始为起始值
INSERT INTO EMPLVL VALUES(EMPLVL_SEQ.NEXTVAL, 1000, 2000);
--以后为上一次的值加步长
INSERT INTO EMPLVL VALUES(EMPLVL_SEQ.NEXTVAL, 2001, 3000);
INSERT INTO EMPLVL VALUES(EMPLVL_SEQ.NEXTVAL, 3001, 4000);
---查询当前序列号
SELECT EMPLVL_SEQ.CURRVAL FROM DUAL;
--SELECT *FROM EMPLVL

----视图
---创建视图
CREATE OR REPLACE VIEW SHITU
AS SELECT STU.ID,STU.NAME
FROM STU
--像表一样操作视图
SELECT * FROM SHITU;

---事务控制
--创建表
CREATE TABLE STU(
ID CHAR(10),
NAME VARCHAR2(40) NOT NULL,
SEX CHAR(1) CONSTRAINTS STU_SEX_NN NOT NULL,
BIRTHDAY DATE,
CONSTRAINTS STU_ID_PK PRIMARY KEY (ID)
);
---SELECT * FROM STU
INSERT INTO STU VALUES('0002', 'YAO','1',NULL);
--回滚
ROLLBACK;
--写入磁盘
COMMIT

INSERT INTO STU VALUES('0003', 'YAO','1',NULL);
INSERT INTO STU VALUES('0004', 'YAO','1',NULL);
--设置保存点
SAVEPOINT X;
INSERT INTO STU VALUES('0005', 'YAO','1',NULL);
INSERT INTO STU VALUES('0006', 'YAO','1',NULL);
--回滚到保存点
ROLLBACK TO SAVEPOINT X;
--写入磁盘
COMMIT;