-
视图相关知识的汇总
重点大纲
- 描述视图
- 创建，改变视图的定义，删除视图
- 通过视图重新找回数据
- 通过视图插入，更新和删除数据
- 创建和使用inline视图
- 执行Top-N 分析
什么是视图？
视图是基于一张表或者另一张视图的逻辑表。 视图本身不包含数据。视图被存储在数据字典中。
为什么使用视图？
- 限制数据访问
- 使复杂查询更容易
- 提供数据独立性
- 相同的数据表示为不同的视图
创建视图
- 在create view 语句中可以嵌入子查询
- 子查询可以包含复杂的SELECT语法。
授权：
- SQL>
- SQL> conn / as sysdba
- Connected.
- SQL>
- SQL> grant create view to scott;
- grant create view to scott
- *
- ERROR at line 1:
- ORA-01917: user or role 'SCOTT' does not exist
- SQL> show con_name;
- CON_NAME
- ------------------------------
- CDB$ROOT
- SQL> alter session set container=PDB1;
- Session altered.
- SQL> grant create view to scott;
- Grant succeeded.
- SQL> conn scott/tiger
- ERROR:
- ORA-01017: invalid username/password; logon denied
- Warning: You are no longer connected to ORACLE.
- SQL> conn scott/tiger@PDB1;
- Connected.
- SQL>
- SQL> select * from session_privs;
- PRIVILEGE
- ----------------------------------------
- CREATE SESSION
- UNLIMITED TABLESPACE
- CREATE TABLE
- CREATE CLUSTER
- CREATE VIEW
- CREATE SEQUENCE
- CREATE PROCEDURE
- CREATE TRIGGER
- CREATE TYPE
- CREATE OPERATOR
- CREATE INDEXTYPE
- SET CONTAINER
- 12 rows selected.
- SQL>
创建视图
- SQL> show con_name;
- CON_NAME
- ------------------------------
- CDB$ROOT
- SQL> alter session set container=PDB1;
- Session altered.
- SQL> conn scott/tiger@PDB1;
- Connected.
- SQL>
- SQL>
- SQL> select * from session_privs;
- PRIVILEGE
- ----------------------------------------
- CREATE SESSION
- UNLIMITED TABLESPACE
- CREATE TABLE
- CREATE CLUSTER
- CREATE VIEW
- CREATE SEQUENCE
- CREATE PROCEDURE
- CREATE TRIGGER
- CREATE TYPE
- CREATE OPERATOR
- CREATE INDEXTYPE
- PRIVILEGE
- ----------------------------------------
- SET CONTAINER
- 12 rows selected.
- SQL> create view vu10
- 2 as
- 3 select empno,ename,sal,deptno from emp where deptno = 10;
- View created.
- SQL> set pagesize 200
- SQL> set linesize 200
- SQL>
视图中增加一个字段，
- SQL> select * from vu10;
- EMPNO ENAME SAL DEPTNO
- ---------- ---------- ---------- ----------
- 7782 CLARK 2450 10
- 7839 KING 5000 10
- 7934 MILLER 1300 10
- SQL> create or replace view vu10
- 2 as
- 3 select empno,ename,hiredate,sal,deptno from emp where deptno = 10;
- View created.
- SQL> select * from vu10;
- EMPNO ENAME HIREDATE SAL DEPTNO
- ---------- ---------- --------- ---------- ----------
- 7782 CLARK 09-JUN-81 2450 10
- 7839 KING 17-NOV-81 5000 10
- 7934 MILLER 23-JAN-82 1300 10
- SQL>
- SQL>
- SQL> create or replace view vu10(employee_id,first_name,hire_date,salary,department_id)
- 2 as
- 3 select empno,ename,hiredate,sal,deptno from emp where deptno = 10;
- View created.
- SQL> select * from vu10;
- EMPLOYEE_ID FIRST_NAME HIRE_DATE SALARY DEPARTMENT_ID
- ----------- ---------- --------- ---------- -------------
- 7782 CLARK 09-JUN-81 2450 10
- 7839 KING 17-NOV-81 5000 10
- 7934 MILLER 23-JAN-82 1300 10
- SQL>
强制建视图
- SQL>
- SQL> create or replace view vu20
- 2 as
- 3 select empno,ename,hiredate,sal,deptno from e05 where deptno = 20;
- select empno,ename,hiredate,sal,deptno from e05 where deptno = 20
- *
- ERROR at line 3:
- ORA-00942: table or view does not exist
- SQL> desc vu20
- ERROR:
- ORA-04043: object vu20 does not exist
- SQL> create or replace force view vu20
- 2 as
- 3 select empno,ename,hiredate,sal,deptno from e05 where deptno = 20;
- Warning: View created with compilation errors.
- SQL>
- SQL> desc vu20
- ERROR:
- ORA-24372: invalid object for describe
- SQL> create table e05 as select * from emp;
- Table created.
- SQL> desc vu20;
- Name Null? Type
- ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
- EMPNO NUMBER(4)
- ENAME VARCHAR2(10)
- HIREDATE DATE
- SAL NUMBER(7,2)
- DEPTNO NUMBER(2)
- SQL>
- SQL> select * from vu20;
- EMPNO ENAME HIREDATE SAL DEPTNO
- ---------- ---------- --------- ---------- ----------
- 7369 SMITH 17-DEC-80 800 20
- 7566 JONES 02-APR-81 2975 20
- 7788 SCOTT 24-JAN-87 3000 20
- 7876 ADAMS 02-APR-87 1100 20
- 7902 FORD 03-DEC-81 3000 20
- SQL>
- SQL> select * from tab;
- TNAME TABTYPE CLUSTERID
- -------------------------------------------------------------------------------------------------------------------------------- ------------- ----------
- DEPT TABLE
- EMP TABLE
- BONUS TABLE
- SALGRADE TABLE
- T01 TABLE
- E01 TABLE
- E02 TABLE
- DETAIL_DEPT TABLE
- T03 TABLE
- VU10 VIEW
- VU20 VIEW
- E05 TABLE
- 12 rows selected.
- SQL> select text from user_views where view_name='VU20';
- TEXT
- --------------------------------------------------------------------------------
- select empno,ename,hiredate,sal,deptno from e05 where deptno = 20
- SQL> select * from (select empno,ename,hiredate,sal,deptno from e05 where deptno = 20);
- EMPNO ENAME HIREDATE SAL DEPTNO
- ---------- ---------- --------- ---------- ----------
- 7369 SMITH 17-DEC-80 800 20
- 7566 JONES 02-APR-81 2975 20
- 7788 SCOTT 24-JAN-87 3000 20
- 7876 ADAMS 02-APR-87 1100 20
- 7902 FORD 03-DEC-81 3000 20
- SQL>
with check option:
- SQL> insert into vu20 values(1,'tom',sysdate,1200,10);
- 1 row created.
- SQL> select * from vu20;
- EMPNO ENAME HIREDATE SAL DEPTNO
- ---------- ---------- --------- ---------- ----------
- 7369 SMITH 17-DEC-80 800 20
- 7566 JONES 02-APR-81 2975 20
- 7788 SCOTT 24-JAN-87 3000 20
- 7876 ADAMS 02-APR-87 1100 20
- 7902 FORD 03-DEC-81 3000 20
- SQL> select * from vu10;
- EMPLOYEE_ID FIRST_NAME HIRE_DATE SALARY DEPARTMENT_ID
- ----------- ---------- --------- ---------- -------------
- 7782 CLARK 09-JUN-81 2450 10
- 7839 KING 17-NOV-81 5000 10
- 7934 MILLER 23-JAN-82 1300 10
- SQL> select * from e05;
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
- 7369 SMITH CLERK 7902 17-DEC-80 800 20
- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
- 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
- 7566 JONES MANAGER 7839 02-APR-81 2975 20
- 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
- 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
- 7782 CLARK MANAGER 7839 09-JUN-81 2450 10
- 7788 SCOTT ANALYST 7566 24-JAN-87 3000 20
- 7839 KING PRESIDENT 17-NOV-81 5000 10
- 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
- 7876 ADAMS CLERK 7788 02-APR-87 1100 20
- 7900 JAMES CLERK 7698 03-DEC-81 950 30
- 7902 FORD ANALYST 7566 03-DEC-81 3000 20
- 7934 MILLER CLERK 7782 23-JAN-82 1300 10
- 1 tom 700
- 1 tom 17-NOV-22 1200 10
- 16 rows selected.
- SQL> roll
- Rollback complete.
- SQL> select * from e05;
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
- 7369 SMITH CLERK 7902 17-DEC-80 800 20
- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
- 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
- 7566 JONES MANAGER 7839 02-APR-81 2975 20
- 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
- 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
- 7782 CLARK MANAGER 7839 09-JUN-81 2450 10
- 7788 SCOTT ANALYST 7566 24-JAN-87 3000 20
- 7839 KING PRESIDENT 17-NOV-81 5000 10
- 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
- 7876 ADAMS CLERK 7788 02-APR-87 1100 20
- 7900 JAMES CLERK 7698 03-DEC-81 950 30
- 7902 FORD ANALYST 7566 03-DEC-81 3000 20
- 7934 MILLER CLERK 7782 23-JAN-82 1300 10
- 1 tom 700
- 15 rows selected.
- SQL> create or replace force view vu20
- 2 as
- 3 select empno,ename,hiredate,sal,deptno from e05 where deptno = 20
- 4 with check option;
- View created.
- SQL> insert into vu20 values(1,'tom',sysdate,1200,10);
- insert into vu20 values(1,'tom',sysdate,1200,10)
- *
- ERROR at line 1:
- ORA-01402: view WITH CHECK OPTION where-clause violation
- SQL> insert into vu20 values(1,'tom',sysdate,1200,20);
- 1 row created.
- SQL>
对视图执行DML操作的规则
如果一个视图包含下面这些，不能通过该视图增加数据：
- 组函数
- GROUP BY 子句
- DISTINCT 关键字
- 伪列ROWNUM 关键字
- 被表达式定义的列
- 没有被视图选择，数据库表中的NOT NULL列。
删除视图
DROP VIEW VIEW_NAME;
- SQL> drop view vu20;
- View dropped.
- SQL>
内联视图
- 内联视图是对你在SQL语句中使用的别名（或相关名称）的子查询
- 主查询的FROM 子句中指定的子查询是内联视图的样例
- 内联视图不是schema对象
建立一个新用户 pstest
- SQL> alter session set container=PDB1;
- Session altered.
- SQL> grant connect,resource to pstest identified by pstest;
- Grant succeeded.
- SQL> conn pstest/pstest@PDB1;
- Connected.
- SQL>
- SQL>
另一个用户下授予pstest 访问某个视图的权限
- SQL>
- SQL> grant select on vu10 to pstest;
- Grant succeeded.
- SQL>
- SQL> select * from scott.vu10;
- EMPLOYEE_ID FIRST_NAME HIRE_DATE SALARY DEPARTMENT_ID
- ----------- ---------- --------- ---------- -------------
- 7782 CLARK 09-JUN-81 2450 10
- 7839 KING 17-NOV-81 5000 10
- 7934 MILLER 23-JAN-82 1300 10
- SQL> show user;
- USER is "PSTEST"
- SQL>
创建视图
- SQL>
- SQL>
- SQL> select * from (select ename,sal from emp order by sal desc);
- ENAME SAL
- ---------- ----------
- KING 5000
- FORD 3000
- SCOTT 3000
- JONES 2975
- BLAKE 2850
- CLARK 2450
- ALLEN 1600
- TURNER 1500
- MILLER 1300
- WARD 1250
- MARTIN 1250
- ADAMS 1100
- JAMES 950
- SMITH 800
- tom 700
- 15 rows selected.
- SQL> create or replace view vutest as select ename,sal from emp order by sal desc;
- View created.
- SQL> select * from vutest;
- ENAME SAL
- ---------- ----------
- KING 5000
- FORD 3000
- SCOTT 3000
- JONES 2975
- BLAKE 2850
- CLARK 2450
- ALLEN 1600
- TURNER 1500
- MILLER 1300
- WARD 1250
- MARTIN 1250
- ADAMS 1100
- JAMES 950
- SMITH 800
- tom 700
- 15 rows selected.
- SQL>
Top-n 分析
- Top-n查询查找一列中n个最大或者最小的数值
- 最大和最小值都被认为是Top-n查询
-
相关阅读:
OpenCV(四十):图像分割—漫水填充
服务CPU异常飙高问题分析和解决
【Linux】《Linux命令行与shell脚本编程大全 (第4版) 》笔记-Chapter14-处理用户输入
新浪微博一键删除所有内容
【YOLOv5】环境搭建：Win11 + mx450
Spring.NET使用Oracle.DataAccess.Client访问数据库
JUC并发编程与源码分析笔记06-Java内存模型之JMM
Linux应用开发 - 读写锁
动态规划：总结
图神经网络 | Pytorch图神经网络ST-GNN
- 原文地址：https://blog.csdn.net/u011868279/article/details/127896713
