10、SQL--视图（含习题）

 

 

 

一、视图

 

在视图中增、删、改会带动父类表的变化；而普通的增、删、改则不可以

 

1、视图（view）与查询语句的区别：

查询语句（select）查询出来的信息仅仅存在于内存中，断电后文件丢失；view是真正的物理文件是存储起来的，断电开机后依然存在。

select employee_id,last_name,salary

from employees

where department_id=80

 

2、也可以将下边的部分定义为表：

create table empl010

as

select employee_id,last_name,salary

from employees

where department_id=80

 

3、初识视图的概念：

 

一个表中有本公司全部的数据信息，但是一个阶层有一个阶层的权限；一个表中某个数据只能老板看到，而员工看不到。类似这样。

 

 

 

 

二、创建视图：

 

1、创建视图前把创建视图的权限赋给：

创建视图前必须先到Oracle 11g中的”企业管理器”设置系统权限：CREATE ANY VIEW；（本人的必须用system登录才可以设置其他用户的系统权限）附Oracle 11g”企业管理器”网址：https://localhost:1158/em

 

2、创建视图的语法：

create view empview

as

select employee_id,last_name,salary

from employees

where department_id = 80

 

3、查询视图：

select * from empview

查询语句（select）查询出来的信息仅仅存在于内存中，断电后文件丢失；view是真正的物理文件是存储起来的，断电开机后依然存在。

 

4、改视图表中的内容：

update empview

set salary = 2000

where employee_id = 179

 

5、修改视图中的内容，引起了总的数据表的变化：

select employee_id,salary

from employees

where department_id=80

结果中179号员工的工资改为了2000元。

 

6、删除视图中的内容：（此处删除会有约束错误，因为上节设置了约束），没有约束的话语句正常执行。

delete from empview

where employee_id=176

 

7、创建新视图（为表名起别名）：

create view empview01

as

select employee_id id,last_name name,salary money

from employees

where department_id = 80

 

结果：

ID NAME                           MONEY

------- ------------------------- ----------

    165  Lee                            6800.00

 

8、基于多个表创建一个新视图：

create view empview01

as

select employee_id id,last_name name,salary money

from employees

where department_id = 80

 

9、利用”create or replace”创建视图会覆盖之前重名的视图：

（1）、错误的：

create view empview02

as

select employee_id id,last_name name,department_name

from employees e,departments d

where e.department_id = d.department_id

ORA-00955: 名称已由现有对象使用

 

（2）、正确形式：

订正：

create or replace view empview02

as

select employee_id id,last_name name,department_name

from employees e,departments d

where e.department_id = d.department_id

 

 

10、屏蔽 DML 操作（with read only）：

create or replace view empview02

as

select employee_id id,last_name name,department_name

from employees e,departments d

where e.department_id = d.department_id

with read only

 

附：使用屏蔽 DML 操作（with read only）后，用户不可以对视图进行任何操作。不可以增、删、改等等。

 

 

三、简单视图和复杂视图：

 

在复杂视图中不可以使用增、删、改的操作。即DML的操作。

 

 

如果创建中使用了分组函数，那么创建的视图就是复杂视图；反之就是简单视图。

 

1、复杂视图：

create or replace view empview03

as

select department_name,avg(salary) avg  --组函数在这里必须起别名，因为实际不存在组函数这样的列。

from employees e,departments d

where e.department_id = d.department_id

group by department_name

 

2、规定：

在复杂视图中不可以使用增、删、改的操作。即DML的操作。

 

update empview03

set avg=15760

where department_name='IT'

ORA-01732: 此视图的数据操纵操作非法

 

 

3、删除视图：drop view empview03

 

四、Top–N分析：（难点：把结果看成一个新的待查的表）

 

Rownum：伪列

 

1、查询工资在前十名高的员工信息：

 

（1）、错误的代码：查询rownum<=10,rownum自身会有一个默认的排序，与employee_id有关，其实查的是前十号员工的信息。

select rownum,employee_id,last_name,salary

from employees

where rownum <= 10

order by salary desc

 

（2）、订正错误：

把下面的结果当成一个新的表，查询的时候就到新的表中查：

select employee_id,last_name,salary

from employees

order by salary desc

 

（3）、真正的查询工资排名前十的员工信息：

select rownum,employee_id,last_name,salary

from(

       select employee_id,last_name,salary

       from employees

       order by salary desc

)

where rownum <= 10

 

 

2、查询工资在第四十名到五十名的员工信息：

（1）、错误代码：

select rownum,employee_id,last_name,salary

from(

       select employee_id,last_name,salary

       from employees

       order by salary desc

)

where rownum >=40 and rownum <= 50

 

 

因为rownum只能使用 < 或 <= ，解决方法：再像1中那样把某段代码看作一个新的要查的表。

（2）、正确解法：

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 > 40 and rn<=50

正确解法截图：

 

 

五、测验题一：

 

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

select last_name, salary

from (select last_name, salary from employees order by salary desc)

where rownum <= 10

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

          可以标识行号.默认情况下 rownum 按主索引来排序. 若没有主索引则自然排序.

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

 

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

select *

from(

  select rownum rn, temp.*   

--rownum是伪列不能进行>、>=等操作，此处把rownum身份降低了，变成了一个普通的列，这样就可以进行之前不能进行的操作了。别名一定是要的，否则在外边又会把rownum当成是一个新的伪列。 

from (

    select last_name, salary

    from employees e

    order by salary desc

  ) temp

)

where rn > 10 and rn < 21

 

64. 对 oralce 数据库中记录进行分页: 每页显示 10 条记录, 查询第 5 页的数据

select employee_id, last_name, salary

from (

        select rownum rn, employee_id, last_name, salary

        from employees

     ) e

where e.rn <= 50 and e.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

 

六、测验题二：

 

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

create or replace view employee_vu

as

select last_name,employee_id,department_id

from employees

 

2、显示视图的结构

desc employee_vu;

         

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

SELECT * FROM employee_vu;

 

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

create or replace view employee_vu

as

select last_name,employee_id,department_id

from employees

where department_id = 80

 

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

create or replace view employee_vu

as

select last_name,employee_id,department_id

from employees

where department_id = 80

with read only