first, what is a view.
1, the view is a kind of virtual table;2, the view is based on the
existing table , the view to the establishment of these tables is called the base table ;
3. The statement that provides the data content to the view is a SELECT statement, which can be understood as a stored select statement ;
4. The view provides a different representation of the base table data to the user.
Second, why use the view
1. Provides another level of security for a table (Control data access: Don't want everyone to see it)
2. The complexity of the data is hidden
3. Simplifies the user's SQL command
4. Changes in the structure of the isolated base table
5. Provide data from another angle by renaming the column
6. Avoid repeated access to the same data
iii. Assigning permissions to a view
change to the System account (System/admin)and enter the command in the page:
Grand Create any view to Scott;To
Create the View permissions successfully.
command to create a view (add data to the User_view table):
created successfully
Modifying the data of a view
the command format is the Update table name set column name where ...
When you requery views and tables, you will find that both sides of the data have changed (a relatively abbreviated table =. =)
Description: Changes to the view affect the table, that is, people with different permissions can view not only the data that meets their own permissions, but also the changes, and the primary table data.
Simplify Queries
by adding data to a view through a multi-table connection, this view will have two table-compliant data, allowing you to directly manipulate the chart whenever you add or delete information, rather than using a multi-table connection every time. Makes querying and so on easier, simplifying SQL.
Modify a View
If you need to modify the data for the EMPVIEW2 table, you can use the command:
Create or Replace view empview2.
If you still use CREATE VIEW empview2 you will get an error : The name is used by an existing object.
Masking DML OperationsWhen a
user has only view permissions and no operational database permissions, you can add a command to it:With
read only
to mask the DML operation.
Iv. simple and complex views
difference: There is no use of the grouping function .
Complex view legend:
the query results in data that matches the criteria:
rules for using DML in views
1. DML operations can be performed in a simple view
2. Delete is not available when the following elements appear in the View definition:
---Group functions
---GROUP BY clause
---distinct keywords
---rownum pseudo-column
Five, top-n analysis
1. According to RowNum, the employee ID, salary and name of the top eight employees are queried.
if an intermediate value query is made through rownum, no data is returned
reason: The rownum can only use < or <=, and >= cannot return any data.
So how do you implement a paging-like feature? How do I find data within a specified range?
Analysis: Pseudo-columns cannot be used at this time, but if you make a pseudo-column into a real-world column and then query it, you can draw the data you want.
named RowNum RN, in the outermost SQL statement to query RN (Prevent rownum conflict with this layer), and then add the conditions of Rn can be queried. The results are as follows:
other
show the structure of the view:
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
Oracle (ii)--View details (multi-image HD plus DAO, still afraid to understand?) ）
