oracle笔记视图

/*

视图

*/

--创建视图
CREATE VIEW empview
AS
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80
--
SELECT * 
FROM empview
--
SELECT *
FROM employees
--
--
SELECT * 
FROM empview
WHERE employee_id = 176
/*

更新视图中的数据(视图中的数据被修改，视图的基表也会被随之修改) 注意：如果我没记
错的话，在sqlserver中，视图中的数据被修改，基表没有影响，这点和oracle不一样

*/
--修改视图中的数据
UPDATE empview
SET salary = 18000 + 1
WHERE employee_id = 176
--
SELECT *
FROM employees
WHERE employee_id = 176






--
SELECT * 
FROM employees emp
WHERE department_id = 80
--
SELECT emp.employee_id, emp.* 
FROM employees emp
WHERE department_id = 80 
AND manager_id NOT IN (
SELECT emp.employee_id
FROM employees emp
WHERE department_id = 80 
)
--优化前
SELECT emp.employee_id, emp.* 
FROM employees emp
WHERE department_id = 80 
AND manager_id NOT IN (
SELECT emp.employee_id
FROM employees emp
WHERE department_id = 80 
)
AND emp.employee_id NOT IN (
SELECT emp.employee_id
FROM employees emp
WHERE department_id = 80
)
--
SELECT emp.employee_id, emp.* 
FROM employees emp
WHERE department_id = 80 
AND manager_id NOT IN (
SELECT emp.employee_id
FROM employees emp
WHERE department_id = 80 
)
AND emp.employee_id NOT IN (
emp.manager_id
)
--其实下面有点类似于相关子查询，其实就是相关子查询
SELECT emp.employee_id, emp.* 
FROM employees emp
WHERE  
employee_id IN (
SELECT employee_id
FROM employees
WHERE employee_id = 150 OR  employee_id = 156
)
AND last_name = (
emp.last_name
)
--AND last_name = (
--'' || emp.employee_id || ''
--)
--AND employee_id = (
--emp.employee_id
--)
--AND manager_id IN (
--emp.employee_id
--)
--AND employee_id IN (
--emp.employee_id
--)

--优化后
WITH employee_dep_80
AS 
(SELECT employee_id FROM employees WHERE department_id = 80)
SELECT emp.employee_id, emp.*
FROM employees emp
WHERE department_id = 80 
AND manager_id NOT IN (
SELECT employee_id 
FROM employee_dep_80
)
AND emp.employee_id NOT IN (
SELECT employee_id 
FROM employee_dep_80
)
--
SELECT emp.employee_id, emp.* 
FROM employees emp
WHERE manager_id NOT IN (
SELECT emp.employee_id
FROM employees emp
WHERE department_id = 80 
)
--AND department_id = 80 

--
SELECT employee_id, emp.*
FROM employees emp
WHERE manager_id NOT IN (
SELECT employee_id
FROM employees
--WHERE department_id = 80
)
AND department_id = 80

/*
oracle查询中null的问题(in 和 not in)
参考网页https://www.cnblogs.com/sutao/p/7380220.html
参考网页https://blog.csdn.net/longcccvv/article/details/54376015
*/

--可以查出数据
SELECT SYSDATE FROM dual;

--可以查出数据
SELECT SYSDATE FROM dual WHERE 666 = 666;

--查不出数据
SELECT SYSDATE FROM dual WHERE 666 = 888;

/*
相当于WHERE后面的表达式返回false
(注意：null只能和is 和 not is结合使用，不能与=和!=结合使用，null与=和!=结合使用，只会返回false)
*/
--查不出数据
SELECT SYSDATE FROM dual WHERE 666 = NULL; --相当于WHERE后面的表达式返回false

--查不出数据
SELECT SYSDATE FROM dual WHERE 666 != NULL; --相当于WHERE后面的表达式返回false

--查不出数据
SELECT SYSDATE FROM dual WHERE NULL = NULL; --相当于WHERE后面的表达式返回false

--查不出数据
SELECT SYSDATE FROM dual WHERE NULL != NULL; --相当于WHERE后面的表达式返回false

--可以查出数据
SELECT SYSDATE FROM dual WHERE NULL IS NULL; 

--查不出数据
SELECT SYSDATE FROM dual WHERE NULL IS NOT NULL;

--可以查出数据
SELECT * FROM employees WHERE department_id = 80

--查不出数据
SELECT * FROM employees WHERE department_id = NULL --相当于WHERE后面的表达式返回false

--查不出数据
SELECT * FROM employees WHERE department_id != NULL --相当于WHERE后面的表达式返回false

 
/*
这里使用了or关键字(department_id = 80能查到数据，department_id != NULL为false，这
里的or关键字只要满足其中一个条件就可以，所以可以查出数据)
*/
--可以查出数据
SELECT * FROM employees WHERE department_id = 80 OR department_id != NULL 

--可以查出数据(同上面的理)
SELECT * FROM employees WHERE department_id = 80 OR department_id = NULL


/*
WHERE后面用的是and关键字，department_id = NULL为false，就算department_id = 80可以查出数据，
但是and关键字表示必须2个条件都为true才可以查询出数据，只要一个为false，那整个表达式都为false，所
以查不出数据
*/
--(注意：null只能和is 和 not is结合使用，不能与=和!=结合使用，null与=和!=结合使用，只会返回false)
--查不出数据
SELECT * FROM employees WHERE department_id = 80 AND department_id = NULL 

--查不出数据(同上面的理)
SELECT * FROM employees WHERE department_id = 80 AND department_id != NULL

--可以查出数据
SELECT * FROM employees WHERE department_id = 80 AND department_id IS NOT NULL

--查不出数据
SELECT * FROM employees WHERE department_id = 80 AND department_id IS NULL

--可以查出数据
SELECT * FROM employees WHERE department_id = 80 OR department_id IS NULL

--可以查出数据
SELECT * FROM employees WHERE department_id IS NULL

--可以查出数据
SELECT * FROM employees WHERE department_id IS NOT NULL

--可以查出数据
SELECT * FROM employees WHERE department_id = 80 OR department_id IS NOT NULL

--查不出数据
SELECT employee_id, emp.*
FROM employees emp
WHERE employee_id NOT IN(
SELECT DISTINCT manager_id
FROM employees
--100,123,120,121,147,205,148,149,201,102,101,114,124,145,146,103,122
)
--可以查询出数据
SELECT employee_id, emp.*
FROM employees emp
WHERE employee_id NOT IN(
SELECT DISTINCT manager_id
FROM employees WHERE manager_id IS NOT NULL
--100,123,120,121,147,205,148,149,201,102,101,114,124,145,146,103,122
)

--
SELECT * FROM employees
WHERE manager_id IN (
SELECT employee_id
FROM employees
)


--查询是领导职务的员工
SELECT * FROM employees
WHERE employee_id IN (
SELECT DISTINCT manager_id
FROM employees
)

--查询80号部门中是领导职务的员工
SELECT * FROM employees
WHERE department_id = 80
AND employee_id IN(
SELECT DISTINCT manager_id
FROM employees WHERE manager_id IS NOT NULL
)

--
--查询不是领导职务的员工
SELECT * FROM employees
WHERE employee_id NOT IN (
SELECT DISTINCT manager_id
FROM employees WHERE manager_id IS NOT NULL
)
--查询80号部门中不是领导职务的员工
SELECT * FROM employees
WHERE department_id = 80
AND employee_id NOT IN(
SELECT DISTINCT manager_id
FROM employees WHERE manager_id IS NOT NULL
)

/*
这里涉及到and 和 or 的执行顺序，执行顺序不同，查询出来的结果也会不同
and和or的执行顺序(and优先级 > or优先级),所以1 and 2 or 3 and 4 等同于 (1 and 2) or (3 and 4)
oracle中where中有and和or同时存在，and优先级高于or
*/
--查询非80号部门中不是领导职务的员工
SELECT * FROM employees
WHERE department_id != 80 --注意:department_id != 80这个条件，不会把department_id等于空的算进去
AND employee_id NOT IN(
SELECT DISTINCT manager_id
FROM employees WHERE manager_id IS NOT NULL
) ORDER BY employee_id DESC
--and 和 or 的执行顺序，执行顺序不同，查询出来的结果也会不同
--如下的查询语句，查询出来的记录是符合非80号部门中不是领导职务的员工的要求的
SELECT * FROM employees
WHERE (department_id != 80 OR department_id IS NULL)
AND employee_id NOT IN(
SELECT DISTINCT manager_id
FROM employees WHERE manager_id IS NOT NULL
) ORDER BY employee_id DESC
--and 和 or 的执行顺序，执行顺序不同，查询出来的结果也会不同
--下面的这条sql语句的执行顺序是，先执行and再执行or
SELECT * FROM employees
WHERE department_id != 80 OR department_id IS NULL
AND employee_id NOT IN(
SELECT DISTINCT manager_id
FROM employees WHERE manager_id IS NOT NULL
) ORDER BY employee_id DESC
--下面的这条sql语句和上面的那条sql语句是等价的
SELECT * FROM employees
WHERE department_id IS NULL
AND employee_id NOT IN(
SELECT DISTINCT manager_id
FROM employees WHERE manager_id IS NOT NULL
) 
OR department_id != 80  
ORDER BY employee_id DESC
--
--and 和 or 的执行顺序，执行顺序不同，查询出来的结果也会不同
SELECT * FROM employees
WHERE department_id != 80 
AND employee_id NOT IN(
SELECT DISTINCT manager_id
FROM employees WHERE manager_id IS NOT NULL
) OR department_id IS NULL
ORDER BY employee_id DESC



--查询视图
SELECT * FROM empview;
--查询员工表
SELECT * FROM employees WHERE department_id = 80;
--删除视图中的数据，视图的基表中的数据也会被随之删除
DELETE FROM empview WHERE employee_id = 166;--166号员工不是领导，所以可以删除(employees表中的employee_id字段和manager_id字段有主外键关系)
DELETE FROM empview WHERE employee_id = 172; --172号员工不是领导，所以可以删除
DELETE FROM empview WHERE employee_id = 177; --177号员工不是领导，所以可以删除
DELETE FROM empview WHERE employee_id = 150; --150号员工不是领导，所以可以删除



--and和or的执行顺序(and优先级 > or优先级),所以1 and 2 or 3 and 4 等同于 (1 and 2) or (3 and 4)
--return true
SELECT SYSDATE FROM DUAL WHERE 1 = 2 AND 1 = 2 OR 1 = 1;
--return false
SELECT SYSDATE FROM dual WHERE 1 = 2 AND (1 = 2 OR 1 = 1);  
--return true
SELECT SYSDATE FROM dual WHERE 666 = 666
--这里的NOT可以理解成取反
SELECT SYSDATE FROM dual WHERE NOT 666 = 666
--
SELECT * FROM employees WHERE department_id IN (80)
--没把department_id等于空的记录算进去
SELECT * FROM employees WHERE department_id NOT IN (80)
--
SELECT * FROM employees WHERE department_id NOT IN (80) OR department_id IS NULL


--注意：oracle排序中默认null最大
--排序(降序)(注意：oracle排序中默认null最大)
SELECT * FROM employees 
ORDER BY department_id DESC 


--排序(升序)
SELECT * FROM employees 
ORDER BY department_id ASC 
 

--查询80号部门的员工
SELECT * FROM employees WHERE department_id = 80


--查询非80号部门的员工(注意：department_id为空的记录不会算进去)
SELECT * FROM employees WHERE department_id != 80
--
SELECT * FROM employees WHERE department_id != 80 OR department_id IS NULL
--
SELECT * FROM employees WHERE department_id = 80 AND department_id IS NULL
--
SELECT * FROM employees WHERE department_id = 80 AND department_id IS NOT NULL
--
SELECT * FROM employees WHERE department_id != 80 AND department_id IS NOT NULL
--
SELECT * FROM employees WHERE  department_id IS NULL
--
SELECT * FROM employees
SELECT * FROM employees WHERE  department_id IS NULL
/*
Oracle中的!=(不等于号)与Null的情况
参考网页https://www.cnblogs.com/nick-huang/p/3921605.html
*/
--查不出数据 --查询非80号部门的员工(注意：department_id为空的记录不会算进去,department_id为空的记录不满足department_id != 80的条件)
SELECT * FROM employees WHERE  department_id != 80 AND department_id IS NULL


  
/*
oracle 子查询中null的问题(in 和 not in)
参考网页https://www.cnblogs.com/sutao/p/7380220.html
参考网页https://blog.csdn.net/longcccvv/article/details/54376015
*/
--注意：department_id != 80 只会查找department_id不为null并且department_id不等于80的数据
SELECT * FROM employees
WHERE department_id != 80
--如下这样才会把department_id不等于80的数据全部找出来(包括department_id为空的数据)
SELECT * FROM employees
WHERE department_id != 80 OR department_id IS NULL
--可以查询出数据
SELECT employee_id, emp.*
FROM employees emp
WHERE employee_id IN(
188, 101, NULL
)
--可以查询出数据
SELECT employee_id, emp.*
FROM employees emp
WHERE employee_id NOT IN(
188, 101
)
--查不出数据，一条数据都没有
SELECT employee_id, emp.*
FROM employees emp
WHERE employee_id NOT IN(
188, 101, NULL
)
--查不出数据，一条数据都没有
SELECT employee_id, emp.*
FROM employees emp
WHERE employee_id IS NOT NULL AND employee_id NOT IN(
188, 101, NULL
)
--查不出数据，一条数据都没有
SELECT employee_id, emp.*
FROM employees emp
WHERE employee_id IS NOT NULL AND employee_id NOT IN(
NULL
)
--查不出数据，一条数据都没有
SELECT employee_id, emp.*
FROM employees emp
WHERE employee_id NOT IN(
NULL
)
--查不出数据，一条数据都没有
SELECT employee_id, emp.*
FROM employees emp
WHERE department_id IN(
NULL
)
--可以查询出数据
SELECT employee_id, emp.*
FROM employees emp
WHERE department_id IN(
80, NULL
)
--可以查询出数据
SELECT employee_id, emp.*
FROM employees emp
WHERE department_id IS NULL


--
SELECT * FROM employees
WHERE department_id = 80

--
SELECT * FROM employees WHERE manager_id = 149
--
SELECT *
FROM employees 
WHERE department_id = 80 
--
SELECT *
FROM empview 

--
DELETE FROM empview
WHERE employee_id = 149

--
SELECT * FROM employees




--
--创建视图或者覆盖/替换视图(即修改视图)
CREATE OR REPLACE VIEW view_emp
AS 
SELECT * 
FROM  employees;
--
SELECT * 
FROM view_emp

--创建视图之后，对基表的操作(比如在基表中插入了数据)，会影响到视图吗? 会
CREATE OR REPLACE VIEW view_dep
AS 
SELECT * 
FROM departments;
--
SELECT * 
FROM view_dep

--不能对视图中的数据进行增删改的操作(即屏蔽DML操作)
CREATE OR REPLACE VIEW view_dep
AS 
SELECT * 
FROM departments
WITH READ ONLY; -- 屏蔽DML操作

--向视图中添加数据，会影响基表
INSERT INTO view_dep
VALUES (117, '测试', NULL, 2400)
--
SELECT * 
FROM departments;
--修改基表中的数据，会影响视图
UPDATE departments
SET department_name = '测试666' WHERE department_id = 666
--向基表中添加一条数据，会影响视图
INSERT INTO departments
VALUES (888, '财务', NULL, 1700)



---------------------可以理解成相关子查询
SELECT * 
from employees e
		where department_id = (
      			select department_id
      			from employees e
      			where employee_id = 108
		) and salary = (
      			select min(salary)
      			from employees
      			where department_id = e.department_id
		)

--------------------- 
SELECT * 
from employees e
		where department_id IN (
      			select department_id
      			from employees e
      			where employee_id = 108
		) and salary = (
      			select min(salary)
      			from employees
      			where department_id = e.department_id
		)

--------------------- 
SELECT * 
from employees e
		where department_id = (
      			select department_id
      			from employees
      			where employee_id = 108
		) and salary = (
      			select min(salary)
      			from employees
      			where department_id IN (e.department_id)
		)

--------------------- 
SELECT * 
from employees e
		where department_id IN (
      			select department_id
      			from employees
      			where employee_id = 108
		) and salary = (
      			select min(salary)
      			from employees
      			where department_id IN (e.department_id)
		)

--创建视图(给视图中的字段取别名)
CREATE VIEW view_emp2
AS
SELECT employee_id id, last_name name, salary --给视图中的字段取别名
FROM employees
WHERE department_id = 80

--查询视图
SELECT * 
FROM view_emp2
--修改视图中的数据
UPDATE view_emp2
SET salary = 8888, name = 'Johnson ABCDE' --使用视图中的别名
WHERE ID = 179

--查询基表
SELECT *
FROM employees
WHERE department_id = 80

--视图可以简化查询
CREATE VIEW view_emp3
AS
SELECT employee_id ID, last_name NAME, emp.department_id dep_id, salary, department_name dep_name
FROM employees emp, departments dep
WHERE emp.department_id = dep.department_id

--查询视图
SELECT * FROM view_emp3

--修改视图
CREATE OR REPLACE VIEW view_emp3
AS
SELECT employee_id ID, last_name NAME, emp.department_id dep_id, salary, job_id, department_name dep_name
FROM employees emp, departments dep
WHERE emp.department_id = dep.department_id 

--查询视图
SELECT * FROM view_emp3

/*
简单视图和复杂视图

  特性         简单视图	        复杂视图
表的数量        一个	         一个或多个
函数            没有	            有
分组            没有	            有
DML 操作        可以	         有时可以


*/

--复杂视图(如果视图中出现了组函数，一般我们就可以称之为复杂视图了)，如下：
CREATE OR REPLACE VIEW view_emp4
AS
--组函数一定要取别名，因为实际当中不存在AVG(salary)这样一个列，AVG(salary)这一列是查询出来的，所
--以一定要给组函数取一个别名才不会报错
SELECT department_name, AVG(salary) avg_salary 
FROM employees emp, departments dep
WHERE emp.department_id = dep.department_id
GROUP BY dep.department_name

/*

视图中使用DML的规定
可以在简单视图中执行 DML 操作
当视图定义中包含以下元素之一时不能使用delete:
组函数
GROUP BY 子句
DISTINCT 关键字
ROWNUM 伪列


当视图定义中包含以下元素之一时不能使用update:
组函数
GROUP BY子句
DISTINCT 关键字
ROWNUM 伪列
列的定义为表达式


当视图定义中包含以下元素之一时不能使insert:
组函数
GROUP BY 子句
DISTINCT 关键字
ROWNUM 伪列
列的定义为表达式
表中非空的列在视图定义中未包括


*/

--修改复杂视图中的数据(非法操作，行不通)
UPDATE view_emp4
SET avg_salary = 12000
WHERE department_name = 'IT'



--
SELECT * FROM view_emp4;

--创建视图(取别名)
CREATE OR REPLACE VIEW  view_emp5
(myid, myName, dep_id, sal) --取别名
AS 
SELECT employee_id, first_name || ' ' || last_name, department_id, salary
FROM employees
WHERE department_id = 80;

--创建视图(取别名)
CREATE OR REPLACE VIEW  view_emp666
(myid, myName, 部门编号, 薪水) --取别名
AS 
SELECT employee_id ID, first_name || ' ' || last_name NAME, department_id d_id, salary mysalary
FROM employees
WHERE department_id = 80;

--
SELECT * FROM view_emp666;

--查询视图
SELECT * FROM view_emp5 

--创建视图(取别名)
CREATE OR REPLACE VIEW  view_emp6
AS 
SELECT employee_id id, first_name || ' ' || last_name NAME, department_id dep_id, salary sal --取别名
FROM employees
WHERE department_id = 80;

--查询视图
SELECT * FROM view_emp6 

--创建视图(取别名)
CREATE OR REPLACE VIEW  view_emp7
AS 
SELECT employee_id AS id, first_name || ' ' || last_name AS NAME, department_id AS dep_id, salary AS sal --取别名
FROM employees
WHERE department_id = 80;

--查询视图
SELECT * FROM view_emp7 

--查询视图
SELECT ID, NAME, dep_id, sal FROM view_emp7 

--删除视图
DROP VIEW view_emp7

--正确
SELECT employee_id, salary FROM employees;

--正确(取别名)
SELECT employee_id / 2 column1, salary / 2 column2 FROM employees;

--注意：同级中，除排序，不能直接引用别名
--错误，会报错(取别名,不能直接引用别名)
SELECT employee_id, salary, (employee_id + salary) / 2 column1, column1 / 2 FROM employees;
--正确
SELECT employee_id, salary, (employee_id + salary) / 3 column1, (employee_id + salary) / 3 / employee_id FROM employees;
--正确(排序时，使用别名)
SELECT employee_id, salary sal FROM employees ORDER BY sal DESC;

--
SELECT emp.last_name || ' work hard in ' || dep.department_name || '哈哈哈!', emp.employee_id, 
emp.last_name, dep.department_id, dep.department_name
FROM employees emp, departments dep
WHERE emp.department_id = dep.department_id

/*

top-N 分析
类似于数据分页

*/

SELECT emp.employee_id, emp.last_name, emp.salary sal
FROM employees emp
ORDER BY sal DESC;

--如果想查询薪水排前10的人?
--如下这样写是有问题的(ROWNUM可以理解成一个伪列)
SELECT ROWNUM, emp.employee_id, emp.last_name, emp.salary sal
FROM employees emp
WHERE ROWNUM <= 10
ORDER BY sal DESC;

--如下这种写法才是正确的
SELECT ROWNUM, employee_id, name, sal
FROM 
(SELECT emp.employee_id, emp.last_name name, emp.salary sal
FROM employees emp
ORDER BY sal DESC
)
WHERE ROWNUM <= 10;


/*
Oracle中的rownum不能使用大于>的问题

可以参考网页https://www.cnblogs.com/java0819/archive/2011/08/03/2146205.html

因为rownum总是从1开始的，第一条不满足去掉的话，第二条的rownum 又成了1。依此类推，所以永远没有满足条件的记录。

可以这样理解：rownum是一个序列，是Oracle数据库从数据文件或缓冲区中读取数据的顺序。

它取得第一条记录则rownum值为1，第二条为2。依次类推。

当使用“>、>=、=、between...and”这些条件时，从缓冲区或数据文件中得到的第一条记录的rownum为1，不符合sql语句的条件，会被删除，接着取下条。

下条的rownum还会是1，又被删除，依次类推，便没有了数据。

*/
--
--注意:  对 ROWNUM 只能使用 < 或 <=, 而用 =, >, >= 都将不能返回任何数据
--薪水排40到50名之间的人(类似于数据分页)
--如下写法是错误的(ROWNUM可以理解成一个伪列,如果把伪列变成一个真实的列就可以使用>、=、>=了)
SELECT ROWNUM, employee_id, name, sal
FROM 
(SELECT emp.employee_id, emp.last_name name, emp.salary sal
FROM employees emp
ORDER BY sal DESC
)
--WHERE ROWNUM >= 1
--WHERE ROWNUM = 1
--WHERE ROWNUM = 2
--没有查询到任何记录(因为rownum总是从1开始的，第一条不满足去掉的话，第二条的rownum 又成了1。依此类推，所以永远没有满足条件的记录)
WHERE ROWNUM <= 50
AND ROWNUM > 40; 



--如下写法是正确的(类似于数据分页)
SELECT rn, employee_id, name, sal --此时rn就不是伪列了，而是一个真实的列了
FROM
(
SELECT ROWNUM rn, employee_id, name, sal --把ROWNUM伪列变成一个真实的列就可以使用>、=、>=了
FROM 
(SELECT emp.employee_id, emp.last_name name, emp.salary sal
FROM employees emp
ORDER BY sal DESC
)
)
WHERE rn > 40 AND rn <= 50 --此时rn就不是伪列了，而是一个真实的列了，真实的列就可以使用>、=、>=了


/*

视图练习

*/

--查询员工表中 salary 前 10 的员工信息
SELECT employee_id, last_name, salary
FROM (
SELECT employee_id, last_name, salary 
FROM employees
ORDER BY salary DESC
)
WHERE ROWNUM <= 10

/*
说明: rownum "伪列" ---- 数据表本身并没有这样的列, 是 oracle 数据库为每个数据表 "加上的"  列.
 
可以标识行号.默认情况下 rownum 按主索引来排序. 若没有主索引则自然排序.

注意: 对 ROWNUM 只能使用 < 或 <=, 而是用 =, >, >= 都将不能返回任何数据.
*/

--查询员工表中 salary 10 - 20 的员工信息
SELECT rn, employee_id, last_name, salary
FROM(
SELECT ROWNUM rn, employee_id, last_name, salary
FROM (
SELECT employee_id, last_name, salary 
FROM employees
ORDER BY salary DESC
)
)
WHERE rn > 10 AND rn <= 20

--
select *
from(
  select rownum rn, temp.*
  from (
    select last_name, salary
    from employees e
    order by salary desc
  ) temp
)
where rn > 10 and rn < 21

--对 oralce 数据库中记录进行分页: 每页显示 10 条记录, 查询第 5 页的数据 
--注意: 对 oracle 分页必须使用 rownum "伪列"!

select rn, employee_id, last_name, salary
from (
        select rownum rn, employee_id, last_name, salary
        from employees
     ) emp
where emp.rn <= 50 and emp.rn > 40


--注意: 对 oracle 分页必须使用 rownum "伪列"!

/*

select employee_id, last_name, salary
from (
        select rownum rn, employee_id, last_name, salary
        from employees
     ) e
where e.rn <= pageNo * pageSize and e.rn > (pageNo - 1) * pageSize

*/

/*
使用表employees创建视图employee_vu，其中包括姓名（LAST_NAME），员工号（EMPLOYEE_ID），部门号(DEPARTMENT_ID)
*/

CREATE OR REPLACE VIEW employee_vu
AS
SELECT emp.last_name, emp.employee_id, emp.department_id
FROM employees emp

--查询视图中的全部内容
SELECT * FROM employee_vu


--显示视图的结构
DESC employee_vu;

--查询视图中的全部内容
SELECT * FROM employee_vu

--将视图中的数据限定在部门号是80的范围内
CREATE OR REPLACE VIEW employee_vu
AS
SELECT emp.last_name, emp.employee_id, emp.department_id
FROM employees emp
WHERE emp.department_id = 80

--查询视图中的全部内容
SELECT * FROM employee_vu

--将视图改变成只读视图

CREATE OR REPLACE VIEW employee_vu
AS
SELECT emp.last_name, emp.employee_id, emp.department_id
FROM employees emp
WHERE emp.department_id = 80
WITH READ ONLY -- 只读视图(即屏蔽DML操作)