Oracle培训（三十）——Oracle 11g 第十一章知识点总结——视图

Oracle培训（三十）——Oracle 11g 第十一章知识点总结——视图

知识点预览

复习

视图

复习

--分页 每页显示5条
         --第一页
         select t.*
         from (select rownum sn,ename,job,salfrom emp
                   where rownum <= 1*5) t
                  where t.sn>(1-1)*5;

视图

1.常见的数据库对象

2.为什么使用视图

a)控制数据访问

b)简化查询

c)数据独立性

d)避免重复访问相同的数据

3.简单视图和复杂视图

4.创建视图

a)在CREATE VIEW语句中嵌入子查询

CREATE [OR REPLACE] [FORCE|NOFORCE]VIEW view

[(alias[, alias]...)]

AS subquery

[WITH CHECK OPTION [CONSTRAINT constraint]]

[WITH READ ONLY [CONSTRAINT constraint]];

b)子查询可以是复杂的 SELECT语句

c)创建视图举例

CREATE VIEW empvu80
 AS SELECT employee_id, last_name, salary
    FROM   employees
    WHERE  department_id = 80;

Viewcreated.

d)描述视图结构

DESCRIBE empvu80

e)创建视图时在子查询中给列定义别名

CREATE VIEW          salvu50
 AS SELECT  employee_id ID_NUMBER, last_name NAME,
            salary*12ANN_SALARY
    FROM    employees
    WHERE   department_id = 50;

View created.

f)在选择视图中的列时应使用别名

5.查询视图

SELECT *
FROM        salvu50;

6.修改视图

a)使用CREATE ORREPLACE VIEW 子句修改视图

CREATE OR REPLACE VIEW empvu80
  (id_number, name,sal, department_id)
AS SELECT employee_id, first_name || ' ' || last_name,
           salary,department_id
   FROM    employees
   WHERE   department_id = 80;

View created.

b)CREATE VIEW 子句中各列的别名应和子查询中各列相对应

7.创建复杂视图

创建复杂视图举例

CREATE VIEW   dept_sum_vu
  (name, minsal,maxsal, avgsal)
AS SELECT d.department_name, MIN(e.salary),
             MAX(e.salary),AVG(e.salary)
   FROM      employees e, departments d
   WHERE     e.department_id = d.department_id
   GROUP BY  d.department_name;

View created.

8.视图中使用DML的规定

a)可以在简单视图中执行 DML操作

b)当视图定义中包含以下元素之一时不能使用delete：

i.组函数

ii.GROUP BY子句

iii.DISTINCT关键字

iv.ROWNUM 伪列 DUAL伪表

c)当视图定义中包含以下元素之一时不能使用update：

i.组函数

ii.GROUP BY子句

iii.DISTINCT关键字

iv.ROWNUM 伪列

v.列的定义为表达式

d)当视图定义中包含以下元素之一时不能使用insert:

i.组函数

ii.GROUP BY子句

iii.DISTINCT关键字

iv.ROWNUM 伪列

v.列的定义为表达式

vi.中非空的列在视图定义中未包括

9.WITH CHECK OPTION 子句

a)使用 WITH CHECKOPTION 子句确保DML只能在特定的范围内执行

CREATE OR REPLACE VIEW empvu20
AS SELECT         *
   FROM     employees
   WHERE    department_id = 20
   WITH CHECK OPTIONCONSTRAINT empvu20_ck ;

View created.

b)任何违反WITH CHECKOPTION 约束的请求都会失败

10.屏蔽 DML 操作

a)可以使用 WITH READONLY 选项屏蔽对视图的DML操作

b)任何 DML 操作都会返回一个Oracle server错误

CREATE OR REPLACE VIEW empvu10
   (employee_number, employee_name, job_title)
AS SELECT         employee_id, last_name, job_id
  FROM     employees
  WHERE    department_id = 10
  WITH READ ONLY;

View created.

11.删除视图

删除视图只是删除视图的定义，并不会删除基表的数据

DROP VIEW view;

DROP VIEW empvu80;

View dropped.

12.临时视图

a)临时视图可以是嵌套在 SQL语句中的子查询

b)在FROM 子句中的的子查询是临时视图

c)临时视图不是数据库对象

13.Top-N 分析

a)Top-N 分析查询一个列中最大或最小的 n 个值：

i.销售量最高的十种产品是什么？

ii.销售量最差的十种产品是什么？

b)最大和最小的值的集合是Top-N 分析所关心的

c)查询最大的几个值的 Top-N 分析：

SELECT [column_list], ROWNUM

FROM (SELECT [column_list]

FROM table

ORDER BY Top-N_column)

WHERE ROWNUM <= N;

d)查询工资最高的三名员工