The role of views in the database

Source: Internet
Author: User

Recently doing a function is working with other project groups.

Background: Now to do a user-managed system, we need to provide services to achieve compatibility with the original database, but now our database and site database stored in the content or the field is somewhat different, but we still want to be able to reduce the changes to a minimum, of course, this is just my initial understanding, Now the specific situation is not well understood, for this aspect of the application, the Internet to understand the relevant operation of the database, to understand that "view" is a more advantageous weapon to solve the problem.

A

View is a table that is exported from one or several basic tables (or views). Unlike a base table, it is a virtual table. The database stores only the definition of the view, not the data for the view, which is still stored in the original base table. So the data in the base table changes, and the data that is queried from the view changes. In this sense, a view is like a window through which you can see the data of interest in the database and its changes. The
view is used as a table. The main function is not to allow all people to see the entire table. For example, there is a staff information table, there are contact, salary, etc., may only hope that HR can see the salary, others can only see the contact, then you can use the view to achieve.
Since the definition of a view is based on a basic table, why do you define a view? This is due to the many benefits of using views reasonably:
1, single source table view
   View data can be taken from only a subset of the rows and columns of one base table, which corresponds to the column of the base table. Such defined views can typically be queried and changed data operations.
2, multi-source table View
   View data can come from more than one table, so the defined view is typically used only for queries and not for modifying data.
3, new view defined on existing view
   view can be re-established on view, the view as a data source must be established.
4, view with Expression
   in order to reduce redundant data in a database when defining a base table, only the basic data is stored in the table, and data derived from the basic data through various calculations is generally not stored. However, because the data in the view is not actually stored, you can set some derived property columns as needed when defining the view, saving the computed value in these derived property columns. These derived properties are also referred to as virtual columns because they do not actually exist in the base table. A view that contains a virtual column is also called a view with an expression.
5, view with grouping statistics
   view with grouping statistics refers to a query statement that defines a view that contains a GROUP BY clause, so that the view can only be used for queries and cannot be used to modify data.

The physical independence of the

data means that the user's application is not dependent on the physical structure of the database. The logical independence of data is that when a database is re-constructed, such as adding new relationships or adding new fields to existing relationships, the user's application is not affected. Hierarchical database and mesh database generally can support the physical independence of the data, but the logical independence can not be fully supported.
in the related database, the reconfiguration of the database is often unavoidable. The most common way to refactor a database is to divide a base table "vertically" into multiple base tables. For example: The student relationship student (sno,sname,ssex,sage,sdept), is divided into SX (sno,sname,sage) and Sy (sno,ssex,sdept) two relationships. The original table student is the result of the natural connection of the SX table and the SY table. If you set up a view Student:
Create View Student (sno,sname,ssex,sage,sdept)
as
SELECT SX. Sno,sx. Sname,sy. Ssex,sx. Sage,sy. Sdept
from Sx,sy
WHERE SX. Sno=sy. Sno;
this way, although the logical structure of the database has changed (into SX and SY two tables), the application does not have to be modified because the newly created view is defined as the user's original relationship, leaving the user's external mode intact, and the user's application can still find the data through the view.
Of course, the view can only provide a certain degree of logical independence of the data, for example, because the update of the view is conditional, the statement that modifies the data in the application may still change due to changes in the base table construction.

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.