Concept:
View: The so-called view is to extract one or more tables of data to generate a map, management view can also achieve the effect of the original table, convenient data management and security operations.
A view is actually a query SQL statement that displays related data in one or more tables or other views. The view uses the results of a query as a table, so the view can be thought of as a virtual table that stores the results of the query. Views originate from tables, and all modifications to view data are eventually reflected in the base table of the views, which must be subject to the integrity constraints of the base table.
Storage of views
Unlike tables, views do not require allocation of storage space, nor does the view contain actual data. A view simply defines a query, and the data in the view is fetched from the base table, which is generated dynamically when the view is referenced. Because views are based on other objects in the database, a view requires only the space in the data dictionary to hold its definition, without additional storage space.
Benefits of the View:
1. Information hiding
For example , there are wages in the S_emp table, you can create views and hide payroll information. (You can work with permissions so that a user can view only the view and cannot view the table.) )
2. make complex queries simple.
3. Data Independence
4. different representations of the same data.
Categories of views:
1. Simple View
2. Complex views
Comparison:
Simple View Complex view
Number of tables involved 1 1 or more
The Include function does not contain a
Contains group data does not contain
use the view by using the DML can not be
Creation of the View:
CREATE [OR REPLACE] [force| Noforce] VIEW view_name
[(alias[, alias] ...)]
As
Select ....
[With CHECK OPTION [CONSTRAINT CONSTRAINT]]
[With READ only]
Note
1.or Replace: represents a modified view
2.force| Noforce: Build This view even if the base table does not exist | The base table does not exist and does not establish this view, the default value.
3.alias: The name of the column in the View ( equivalent to the alias of the result column for the subquery )
4. subqueries can contain complex query syntax, which is hidden from the user.
5. the sub-query cannot contain an order by clause.
6.WITH CHECK OPTION The data row that is inserted or modified must satisfy the constraints of the view definition, in other words, when the keyword indicates a DML operation on view Only the contents of the Where condition limit in the select statement can be manipulated
7.WITH Read Only: This view is read-only and cannot be performed on any DML operations on this view .
8. View structure: desc view_name;
For example:
Create or Replace view MyView
As
Select Id,last_name,start_date
From S_emp
where ID <= 4;
At this point you can use:
1. View all data information in the view
SELECT * from MyView;
2. perform the insert:
INSERT into MyView values (111, ' haha ', ' 03-5 month -16 '); Insert Success!
3. once again, the data you just inserted cannot be found because this data does not meet id<=4, but it is s_emp to view the original table .
If:
Create or Replace view MyView
(id,name,s_date)
As
Select Id,last_name,start_date
From S_emp
Where ID <= 4
with CHECK option;
at this point you can use:
1. View all data information in the view
SELECT * from MyView;
2. perform the insert:
INSERT into MyView values (121, ' haha ', ' 03-5 month -16 '); Insert failed! Because the view constraint is id<=4, now the ID value inserted is 121, so the failure!
Create or Replace view MyView
(id,name,s_date)
As
Select Id,last_name,start_date
From S_emp;
Or
Create or Replace view MyView
As
Select Id,last_name,start_date s_date
From S_emp;
the names of the columns in MyView are id,name,s_date.
To create a complex view:
Complex views may contain groupings, group functions, multi-table joins, and so on.
For example:
CREATE or replace VIEW MyView
(Name, Minsal, Maxsal, Avgsal)
As SELECT D.name, MIN (e.salary),
MAX (E.salary), AVG (e.salary)
From S_emp E, s_dept D
WHERE e.dept_id = d.id
GROUP by D.name;
Viewing View information
Data dictionary user_views can be used ;
To delete a View object:
ROP VIEW view_name;
Oracle (CREATE VIEW)