目录
定义
创建语法
测试
方法一 多表关联查询
方法二 创建中间表来使查询简化
方法三 创建视图
创建视图
视图修改
with read only子句
with check option子句
基表不能通过视图增、删、改的情况
组函数
group by子句
distinct关键字
字段表达式
没有null null列
删除视图
定义
视图中不存放数据，他只是一个定义(物化视图除外)
踏实经过制定的方式来显示一个或多个表的数据
视图所依据的表被称为基表
他可以提供另一种级别的表安全性，不想被看到的内容不会显示
可以使复杂的数据隐藏起来
简化多表查询的sql
通过对列的重命名，进行另一个角度的数据提供
创建语法
| create [or replace] [force] view VIEW_NAME [(alias[, alias]…)] as SELECT_STATEMENT [witch check option] [with read only]; |
测试
查询emp表中每位员工的部门位置以及工资等级情况
方法一 多表关联查询
| SQL> select e.ename,d.dname,s.grade from emp e,dept d,salgrade s where e.deptno=d.deptno and e.sal between s.losal and s.hisal order by grade;
ENAME DNAME GRADE
--------- ---------- ----------
JAMES SALES 1
ADAMS RESEARCH 1
SMITH RESEARCH 1
MARTIN SALES 2
WARD SALES 2
MILLER ACCOUNTING 2
ALLEN SALES 3
TURNER SALES 3
BLAKE SALES 4
JONES RESEARCH 4
SCOTT RESEARCH 4
FORD RESEARCH 4
CLARK ACCOUNTING 4
KING ACCOUNTING 5
14 rows selected. |
sql太长了，如果想简化sql，可以将查询的数据插入到一个新的表中
方法二 创建中间表来使查询简化
| SQL> create table tb_view01 as select e.ename,d.dname,s.grade from emp e,dept d,salgrade s where e.deptno=d.deptno and e.sal between s.losal and s.hisal order by grade;
Table created.
SQL> select * from tb_view01;
ENAME DNAME GRADE
--------- ---------- ----------
JAMES SALES 1
ADAMS RESEARCH 1
SMITH RESEARCH 1
MARTIN SALES 2
WARD SALES 2
MILLER ACCOUNTING 2
ALLEN SALES 3
TURNER SALES 3
BLAKE SALES 4
JONES RESEARCH 4
SCOTT RESEARCH 4
FORD RESEARCH 4
CLARK ACCOUNTING 4
KING ACCOUNTING 5
14 rows selected. |
这样虽然可以select *，但是tb_view01不能随着基表变化
方法三 创建视图
| SQL> create view view_01 as select e.ename,d.dname,s.grade from emp e,dept d,salgrade s where e.deptno=d.deptno and e.sal between s.losal and s.hisal order by grade;
View created.
SQL> set line 50
SQL> desc view_01;
Name Null? Type
----------------------- -------- ----------------
ENAME VARCHAR2(10)
DNAME VARCHAR2(14)
GRADE NUMBER
SQL> select * from view_01;
ENAME DNAME GRADE
--------- ---------- ----------
JAMES SALES 1
ADAMS RESEARCH 1
SMITH RESEARCH 1
MARTIN SALES 2
WARD SALES 2
MILLER ACCOUNTING 2
ALLEN SALES 3
TURNER SALES 3
BLAKE SALES 4
JONES RESEARCH 4
SCOTT RESEARCH 4
FORD RESEARCH 4
CLARK ACCOUNTING 4
KING ACCOUNTING 5
14 rows selected.
SQL> select * from tab where tname='VIEW_01';
TNAME TABTYPE CLUSTERID
---------- --------------------- ----------
VIEW_01 VIEW |
创建视图
创建视图需要有create view权限
如果没有则会报错
| SQL> conn scott/tiger;
Connected.
SQL> create view view_02 as select ename from emp;
create view view_02 as select ename from emp
*
ERROR at line 1:
ORA-01031: insufficient privileges |
授权方式很简单
| SQL> conn / as sysdba
Connected.
SQL> grant create view to scott;
Grant succeeded.
SQL> conn scott/tiger
Connected.
SQL> create view view_02 as select ename from emp;
View created. |
使用revoke即可回收
| SQL> conn / as sysdba
Connected.
SQL> revoke create view from scott;
Revoke succeeded. |
可以通过视图对基表进行dml操作，前提是视图中必须有几表关键字段
| SQL> update view_02 set ename='HAHA' where ename='SMITH';
1 row updated.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- --------- ---------- ----- ------------------- ----- ----- ------
7369 HAHA CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
SQL> rollback;
Rollback complete. |
另外如果基表主键中的定义在视图中没有，那么也无法对基表进行赋值
| SQL> insert into view_02 values('AAA');
insert into view_02 values('AAA')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."EMP"."EMPNO") |
视图修改
with read only子句
将view_02修改为with read only 属性
| SQL> create or replace view view_02 as select ename,sal from emp with read only;
View created.
SQL> select * from view_02;
ENAME SAL
--------- -----
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
14 rows selected. |
将视图改为只读以后增、删、改等DML都不允许了
| SQL> insert into view_02 values('HAHA',9999);
insert into view_02 values('HAHA',9999)
*
ERROR at line 1:
ORA-42399: cannot perform a DML operation on a read-only view
SQL> delete view_02 where ename='KING';
delete view_02 where ename='KING'
*
ERROR at line 1:
ORA-42399: cannot perform a DML operation on a read-only view
SQL> update view_02 set ename='HAHA' where ename='SMITH';
update view_02 set ename='HAHA' where ename='SMITH'
*
ERROR at line 1:
ORA-42399: cannot perform a DML operation on a read-only view |
with check option子句
满足条件的数据才能通过视图而看到，其他的不可以
修改view_02并添加with check option子句
| SQL> create or replace view view_02 as select * from emp where deptno=10 with check option;
View created.
SQL> select * from view_02;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- --------- ---------- ----- ------------------- ----- ----- ------
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 |
测试通过视图修改使用with check option限定字段deptno的值，包括外键限定的值
| SQL> update view_02 set deptno=40;
update view_02 set deptno=40
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
SQL> update view_02 set deptno=99;
update view_02 set deptno=99
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not found |
不能通过视图修改不属于deptno=10的数据
| SQL> update view_02 set ename='HAHA' where deptno=20;
0 rows updated.
SQL> update view_02 set ename='HAHA' where empno=7902;
0 rows updated.
SQL> update view_02 set ename='HAHA' where deptno=10;
3 rows updated.
SQL> rollback;
Rollback complete. |
基表不能通过视图增、删、改的情况
组函数
当视图中包含组函数的时候，不可以增、删、改
创建测试视图
| SQL> create view view_03 as select sum(sal),avg(sal) from emp;
create view view_03 as select sum(sal),avg(sal) from emp
*
ERROR at line 1:
ORA-00998: must name this expression with a column alias |
创建包含组函数的视图时必须要给列起别名
| SQL> create view view_03 as select sum(sal) s,avg(sal) a from emp;
View created.
SQL> desc view_03
Name Null? Type
----------------------- -------- ----------------
S NUMBER
A NUMBER
SQL> select * from view_03;
S A
---------- ----------
29025 2073.21429 |
增删改测试
| SQL> insert into view_03 values(100,100);
insert into view_03 values(100,100)
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal
on this view
SQL> delete view_03;
delete view_03
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal
on this view
SQL> update view_03 set a=200 where s=29025;
update view_03 set a=200 where s=29025
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal
on this view |
group by子句
当视图中包含group by子句时，不可以增、删、改
创建测试视图
| SQL> create view view_04 as select deptno,sum(sal) s from emp group by deptno;
View created.
SQL> desc view_04;
Name Null? Type
----------------------- -------- ----------------
DEPTNO NUMBER(2)
S NUMBER
SQL> select * from view_04;
DEPTNO S
------ ----------
30 9400
20 10875
10 8750 |
增删改测试
| SQL> insert into view_04 values(40,9999);
insert into view_04 values(40,9999)
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
SQL> delete view_04;
delete view_04
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal
on this view
SQL> update view_04 set s=9999 where deptno=10;
update view_04 set s=9999 where deptno=10
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal
on this view |
distinct关键字
当视图中包含distinct关键字的时候，不可以增、删、改
创建测试视图
| SQL> create view view_05 as select distinct job from emp;
View created.
SQL> desc view_05;
Name Null? Type
----------------------- -------- ----------------
JOB VARCHAR2(9)
SQL> select * from view_05;
JOB
----------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST |
增删改测试
| SQL> insert into view_05 values('HAHA');
insert into view_05 values('HAHA')
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal
on this view
SQL> delete view_05;
delete view_05
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal
on this view
SQL> update view_05 set job='HAHA' where job='CLERK';
update view_05 set job='HAHA' where job='CLERK'
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal
on this view |
字段表达式
当视图中包含功过基表中的字段表达式而得来的信息时，不可以增、改
|
SQL> desc view_06;
Name Null? Type
----------------------- -------- ----------------
EMPNO NOT NULL NUMBER(4)
YEAR NUMBER
SQL> select * from view_06;
EMPNO YEAR
----- ----------
7369 9600
7499 19500
7521 15500
7566 35700
7654 16400
7698 34200
7782 29400
7788 36000
7839 60000
7844 18000
7876 13200
7900 11400
7902 36000
7934 15600
14 rows selected. |
增删改测试
| SQL> insert into view_06 values(9999,99999);
insert into view_06 values(9999,99999)
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
SQL> update view_06 set year=99999 where empno=7788;
update view_06 set year=99999 where empno=7788
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
SQL> delete from view_06 where empno=7788;
1 row deleted.
SQL> rollback;
Rollback complete. |
可以对基表进行删除
没有null null列
当视图中不包含基表中被定义为null null的列时，不可以增
创建测试表
| SQL> create view view_07 as select ename,job from emp;
View created.
SQL> desc emp;
Name Null? Type
----------------------- -------- ----------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> desc view_07;
Name Null? Type
----------------------- -------- ----------------
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
SQL> select * from view_07;
ENAME JOB
--------- ----------
SMITH CLERK
ALLEN SALESMAN
WARD SALESMAN
JONES MANAGER
MARTIN SALESMAN
BLAKE MANAGER
CLARK MANAGER
SCOTT ANALYST
KING PRESIDENT
TURNER SALESMAN
ADAMS CLERK
JAMES CLERK
FORD ANALYST
MILLER CLERK
14 rows selected. |
增删改测试
| SQL> insert into view_07 values('HAHA','HEHE');
insert into view_07 values('HAHA','HEHE')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into
("SCOTT"."EMP"."EMPNO")
SQL> delete view_07 where ename='SMITH';
1 row deleted.
SQL> update view_07 set JOB='HAHA' where ename='KING';
1 row updated. |
删除视图
| SQL> drop view view_01;
View dropped. |
因为视图本身没有数据，因此不存在空间释放的问题
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
