Shulou(Shulou.com)06/01 Report--

What is a view [View]

(1) View is a virtual table.

(2) the view is based on the existing tables, and the tables on which the view is built are called base tables.

(3) the statement that provides data content to the view is the SELECT statement, and the view can be understood as a stored SELECT statement.

(4) the view provides the user with another form of base table data.

(5) the view does not store the real data, and the real data is still stored in the base table.

(6) although the programmer operates the view, the final view will be converted to the operation base table.

(7) A base table can have 0 or more views.

When will the view be used?

(1) if you do not want the user to see all the data (fields, records), but only want the user to see some data, you can use the view at this time.

(2) when you need to reduce the writing of SQL query statements, you can use views, but do not improve query efficiency

View application field

(1) Banking, telecommunications, metals, securities and military projects that are inconvenient for users to know all the data.

The role of views

(1) restrict data access

(2) simplify complex queries

(3) provide mutual independence of data.

(4) the same data can be displayed in different ways.

Create a view emp_view_1 based on all the columns of the emp table

Create view view name asselect query for one or more base tables create view emp_view_1asselect * from emp

By default, ordinary users do not have the right to create views, so you have to ask sysdba to assign you creare view permissions.

As sysdba, authorize the scott user create view permissions

Grant create view to scott

Revoke the create view rights of the scott user as sysdba

Revoke create view from scott

Specify columns based on the emp table to create a view emp_view_2 that contains the number / name / salary / annual salary / annual income (column aliases are used in the query)

Create view emp_view_2asselect empno "number", ename "name", sal "salary", sal*12 "Annual salary", sal*12+NVL (comm,0) "Annual income" from emp

Specify the columns based on the emp table, and create a view emp_view_3 (a _.

Create view emp_view_3 (aformabpcececedje) asselect empno "number", ename "name", sal "salary", sal*12 "annual salary", sal*12+NVL (comm,0) "annual income" from emp

Query the structure of the view created by emp_view_3

Desc emp_view_3

Modify the emp_view_3 (id,name,salary,annual,income) view

Create or replace view view name as subquery create or replace view emp_view_3 (id,name,salary,annual,income) asselect empno "number", ename "name", sal "salary", sal*12 "annual salary", sal*12+NVL (comm,0) "annual income" from emp

Query the emp table to find out the minimum wage, maximum wage and average wage of each department

Select min (sal), max (sal), round (avg (sal), 0), deptnofrom empgroup by deptno

Create a view emp_view_4, which contains the minimum wage, maximum wage, and average wage of each department

Create or replace view emp_view_4asselect deptno "Department number", min (sal) "minimum wage", max (sal) "maximum wage", round (avg (sal), 0) "average wage" from empgroup by deptno

Create a view emp_view_5 that contains employee number, name, salary, department name, and salary grade

Create or replace view emp_view_5asselect e.empno "No.", e.ename "name", e.sal "salary", d.dname "Department name", s.grade "salary scale" from emp eForce dept dforce and (e.sal between s.losal and s.hisal)

Delete the record of employee 7788 in the view emp_view_1 and use the delete operation, will it affect the base table?

Delete from emp_view_1 where empno=7788

If written correctly, it will affect the base table.

Is it OK to change emp_view_1 to read-only view [with read only] and then perform the above delete operation?

Create or replace view emp_view_1asselect * from empwith read only

Unable to perform delete operation

Will deleting the [entire] view affect the table?

Will not affect the base table

Delete the view, will you enter the Recycle Bin?

Will not enter the Recycle Bin.

Does deleting the base table affect the view?

Will affect the view

Does the view have an impact after flashing back to the base table?

The view is working properly again.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.