Oracle (II) -- & gt; detailed view (why can't I understand multiple high-definition images ?), Oracle multi-graph

Source: Internet
Author: User

Oracle (II) --> View Details (I am afraid I can't understand the multi-graph in high definition ?), Oracle multi-graph
1. What is a view.1. A view is a virtual table;2. A view is built on an existing table. The tables created by a view are called base tables;3. statements that provide data to the view are SELECT statements, which can be understood as stored SELECT statements;4. A view provides users with another form of representation of the base table data.
Ii. Why View1. provide another level of table Security (Control Data Access: do not want everyone to see it)2. Hiding data complexity3. Simplified SQL commands4. Change the structure of the isolated base table5. Provide data from another perspective by renaming Columns6. Avoid repeated access to the same data
3. Grant view PermissionsChange to the system account (system/admin) and enter the following command on the page:Grand create any view to scott;The view permission is successfully created.
Command for creating a view (adding data to the user_view table ):
Created successfully
Modify View data
Command Format: update table name set column name where...
When you re-query the view and table, you will find that the data on both sides has been changed (relatively simple table =. =)
Note: Changes to views affect tables. users with different permissions can view and modify data that meets their own permissions, and the primary table data is also changed.
Simplified Query
Adding data to a view through a multi-Table connection, the view will have two tables that meet the conditions, so that you can directly view the chart each time you add, delete, modify, and query information, instead of using multi-table join each time. Makes queries and so on simple and simplifies SQL.
Modify ViewTo modify the data in the empview2 table, run the following command:Create or replace view empview2.If you still use create view empview2, an error is returned: The name is used by an existing object.
Shield DML operationsIf you have only the permission to view and operate databases, you can add a command for it:With read only
To block DML operations.
Iv. Simple view and complex view
Difference: whether grouping functions are used.
Complex legend:
Query the Qualified Data:
DML usage in views1. DML operations can be performed in a simple view.2. When the following elements appear in the view definition, delete cannot be used:--- Group Function--- Group by clause
--- Distinct keyword--- Rownum pseudo Column
5. TOP-N Analysis1. query the id, salary, and name of the first eight employees based on rownum.
If you use rownum for median query, no data is returned.
Cause: Only <or <= can be used for rownum, while =,>,> = cannot return any data.How can we implement a paging-like function? How do I find data within a specified range?Analysis: pseudo Columns cannot be used at this time. However, if you convert a pseudo column into a column that actually exists, then you can query the expected data.
Name rownum as rn. query rn in the SQL statement at the outermost layer (to prevent rownum conflict with the rownum at the current layer), and then add the rn condition to query. The result is as follows:
OthersView Structure:






Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.