oracle视图分页查询,视图以及oracle分页，练习

--创建视图

SQL>create or replace view emp_view as

select employee_id id,last_name name,email,department_name,city

from employees e,departments d,locations l

where e.department_id=d.department_id and d.location_id=l.location_id

--视图的记录被删，原表的记录也被删了

SQL> delete from emp_view where id=197;

1 row deleted

SQL> select * from employees;

--屏蔽DML操作  视图的记录不能被删

SQL>create or replace view emp_view

as

select employee_id id,last_name name,email,department_name,city

from employees e,departments d,locations l

where e.department_id=d.department_id and d.location_id=l.location_id

with read only

--使用rownum进行top-n分析

--查询员工表中 salary 前 10 的员工信息.

--没有按rownum排序

select rownum,e.*

from employees e

where rownum<=10

order by salary desc

--按rownum排序

select rownum,e.*

from(

select last_name,salary

from employees

order by salary desc

) e

where rownum <=10

说明: rownum "伪列" ---- 数据表本身并没有这样的列, 是 oracle 数据库为每个数据表 "加上的"  列. 可以标识行号.

默认情况下 rownum 按主索引来排序. 若没有主索引则自然排序.

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

--查询员工表中 salary 10 - 20 的员工信息.

select *

from(

select rownum rn,e.*

from(

select employee_id,last_name,salary

from employees

order by salary desc

) e

)

where rn>=11 and rn<=20

select employee_id,last_name,salary

from(

select rownum rn,e.*

from(

select employee_id,last_name,salary

from employees

order by salary desc

) e

)

where rn>=11 and rn<=20

--使用rownum进行分页，每页显示6条记录，显示第1页的内容

--oracle

select *

from(

select rownum rn,e.*

from employees e

)

where rn>=1 and rn<7

--使用rownum进行分页，每页显示6条记录，显示第5页的内容

--mysql

select *

from employees

limit (5-4)*6,6

--oracle

select *

from(

select rownum rn,e.*

from employees e

)

where rn>=1+(5-1)*6 and rn

where rn>=1+(pageNum-1)*pageSize and rn

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

--练习

1.使用表employees创建视图employee_vu，其中包括姓名(EMPLOYEE)，员工号(EMPLOYEE_ID)，部门号(DEPARTMENT_ID).

create view employee_vu

as select last_name,employee_id,department_id

from employees;

2.显示视图的结构

describe employee_vu;

3.查询视图中的全部内容

select * from employee_vu;

4.将视图中的数据限定在部门号是80的范围内

create view employee_vu

as select last_name,employee_id,department_id

from employees

where department_id=80;

5.将视图改变成只读视图

create view employee_vu as select last_name,employee_id,department_id from employees where department_id=80 with read only;