视图（view）

来源：互联网发布：重庆网络大学教育学院编辑：程序博客网时间：2024/05/22 06:54

视图是从一个表或多个表中导出的虚表,它与常规表不同之处在于它并不存储数据-它只是在一个存储的查询中定义.视图同真实表一样,也包含一系列带有名称的列和数据。视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句. 视图向用户提供基表数据的另一种表现形式。

视图分为简单视图还有复杂视图

所谓简单的视图是只引用一个表,并且它只从基础表中检索数据,不允许修改其中的数据；

复杂的视图是引用多个表,视图越复杂、视图内再查询的表达式就越多越复杂.

为什么使用视图?

控制数据访问:提供了一种安全机制,通过视图显示数据时,屏蔽用户不应该知道的数据.

简化查询:select语句构造了一个复杂的查询语句,每次使用必须都得调用它,根据复杂查询的语句创建一个试图,来简化查询。

补充：查询视图

SQL> select * from usersview;

检索视图的定义

SQL> select * from user_views;

一、创建视图

创建视图的语法：

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

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

AS subquery

[WITH CHECK OPTION [CONSTRAINT constraint]]

[WITH READ ONLY [CONSTRAINT constraint]];

SQL> create or replace view empview as select * from emp;

create or replace view empview as select * from emp

ORA-01031: 权限不足

注意：在创建视图时应先赋予创建视图的权限，create view或create any view权限

切换到管理员然后赋予权限

SQL> grant create any view to redarmy;

SQL> grant create view to redarmy;

简单视图：用简单的select语句创建

SQL> create or replace view empview as select * from emp;

View created

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

SQL> create view usersview as select username name,password pass from users;

可以创建带有函数和group by 的语句

SQL> create or replace view empview2 as select deptno,count(*) from emp group by deptno;

create or replace view empview2 as select deptno,count(*) from emp group by deptno

ORA-00998: 必须使用列别名命名此表达式

备注：使用函数的时候，要添加上别名

SQL> create or replace view empview2 as select deptno,count(*) con from emp group by deptno;

View created

创建连接视图带有函数 group by子句 order by 子句

SQL> create view gdeo as select e.deptno,count(*) cou from emp e inner join dept d on e.deptno=d.deptno group by e.deptno order by e.deptno;

View created

在上面的基础上带有having子句

SQL> create view gdeoh as select e.deptno,count(*) cou from emp e inner join dept d on e.deptno=d.deptno group by e.deptno having count(*)>3 order by e.deptno;

View created

复杂的视图：用复杂的select语句创建

创建emp_view视图，

SQL> create or replace view emp_view as select * from emp;

View created

SQL> desc emp;

Name Type Nullable Default Comments

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

EMPNO NUMBER(4)

ENAME VARCHAR2(10) Y

JOB VARCHAR2(9) Y

MGR NUMBER(4) Y

HIREDATE DATE Y

SAL NUMBER(7,2) Y

COMM NUMBER(7,2) Y

DEPTNO NUMBER(2)

SQL> desc emp_view;

Name Type Nullable Default Comments

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

EMPNO NUMBER(4)

ENAME VARCHAR2(10) Y

JOB VARCHAR2(9) Y

MGR NUMBER(4) Y

HIREDATE DATE Y

SAL NUMBER(7,2) Y

COMM NUMBER(7,2) Y

DEPTNO NUMBER(2)

二、修改视图

在视图中可以像表一样进行增、删、改、查

SQL> insert into emp_view values(7978,'guo','SALESMAN','7689',to_date('1981-2-20','yyyy-MM-dd'),'1000','100','30');

1 row inserted

SQL> select * from emp_view;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980-12-17 800.00 20

7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30

7566 JONES MANAGER 7839 1981-4-2 2975.00 20

7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30

7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30

7782 CLARK MANAGER 7839 1981-6-9 2450.00 10

7788 SCOTT ANALYST 7566 1982-12-9 3000.00 20

7839 KING PRESIDENT 1981-11-17 5000.00 10

7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30

7876 ADAMS CLERK 7788 1983-1-12 1100.00 20

7900 JAMES CLERK 7698 1981-12-3 950.00 30

7902 FORD ANALYST 7566 1981-12-3 3000.00 20

7934 MILLER CLERK 7782 1982-1-23 1300.00 10

7978 guo SALESMAN 7689 1981-2-20 1000.00 100.00 30

15 rows selected

SQL> update emp_view set comm=200 where empno=7978;

1 row updated

SQL> select * from emp_view;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980-12-17 800.00 20

7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30

7566 JONES MANAGER 7839 1981-4-2 2975.00 20

7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30

7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30

7782 CLARK MANAGER 7839 1981-6-9 2450.00 10

7788 SCOTT ANALYST 7566 1982-12-9 3000.00 20

7839 KING PRESIDENT 1981-11-17 5000.00 10

7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30

7876 ADAMS CLERK 7788 1983-1-12 1100.00 20

7900 JAMES CLERK 7698 1981-12-3 950.00 30

7902 FORD ANALYST 7566 1981-12-3 3000.00 20

7934 MILLER CLERK 7782 1982-1-23 1300.00 10

7978 guo SALESMAN 7689 1981-2-20 1000.00 200.00 30

15 rows selected

SQL> delete emp_view where empno=7978;

1 row deleted

SQL> select * from emp_view;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980-12-17 800.00 20

7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30

7566 JONES MANAGER 7839 1981-4-2 2975.00 20

7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30

7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30

7782 CLARK MANAGER 7839 1981-6-9 2450.00 10

7788 SCOTT ANALYST 7566 1982-12-9 3000.00 20

7839 KING PRESIDENT 1981-11-17 5000.00 10

7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30

7876 ADAMS CLERK 7788 1983-1-12 1100.00 20

7900 JAMES CLERK 7698 1981-12-3 950.00 30

7902 FORD ANALYST 7566 1981-12-3 3000.00 20

7934 MILLER CLERK 7782 1982-1-23 1300.00 10

14 rows selected

什么是数据字典？？？

从数据库字典中检索视图的结构（用于创建view）

SQL> desc user_views;

Name Type Nullable Default Comments

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

VIEW_NAME VARCHAR2(30) Name of the view

TEXT_LENGTH NUMBER Y Length of the view text

TEXT LONG Y View text

TYPE_TEXT_LENGTH NUMBER Y Length of the type clause of the object view

TYPE_TEXT VARCHAR2(4000) Y Type clause of the object view

OID_TEXT_LENGTH NUMBER Y Length of the WITH OBJECT OID clause of the object view

OID_TEXT VARCHAR2(4000) Y WITH OBJECT OID clause of the object view

VIEW_TYPE_OWNER VARCHAR2(30) Y Owner of the type of the view if the view is a object view

VIEW_TYPE VARCHAR2(30) Y Type of the view if the view is a object view

SUPERVIEW_NAME VARCHAR2(30) Y Name of the superview, if view is a subview

查看某个视图的创建的select语句

SQL> select view_name,text from user_views where view_name='EMP_VIEW';

VIEW_NAME TEXT

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

EMP_VIEW select "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" from emp

注意：where后面的视图名要大写

视图的相关性：

//当基表被修改后，视图会失效

查询所有对象的名字和状态

SQL> select object_name,status from user_objects where object_name='EMP_VIEW';

OBJECT_NAME STATUS

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

EMP_VIEW VALID