Definition view of the database

A view is a table that is exported from one or more basic tables (or views). Unlike the base table, the database only holds the definition of the view, and the data in the view is stored in the original base table. A view is a mapping of a base table, and if a base table changes, the data that is queried from the view changes accordingly. The operations of the view mainly include the following:

1. Define the View

The SQL statement creates a view with the CREATE VIEW command in the general format:

CREATE View <>[(< column name >[,< column name;] ...)

As < sub-query >

[with CHECK option];

Where the subroutine can be any complex SELECT statement, but usually does not include the ORDER BY clause and the distinct phrase. With CHECK option indicates that the update, INSERT, delete operation is guaranteed to be updated, inserted, deleted, and the row satisfies the predicate condition in the view definition (that is, the conditional expression in the subquery).

The attribute column names that make up the view are either omitted or all specified, and there is no third option. If the individual property column names of the view are omitted, the property column name of the implied view is made up of the fields of the target column names in the SELECT statement. However, there are several situations where you must specify all the column names for the view:

(1) A target is not a simple attribute name, but a clustered or column expression

(2) A number of fields with the same column name as the view are selected when the multi-table connection

(3) You need to enable a new, more appropriate name for a column in the view

For example, create a view that sets up students for "is":

CREATE VIEW Is_student

As

Select Sno,sname,sage

From student

Where sdept= ' is ';

In this instance, the Is_student column name is omitted, which implies that the three column names in the SELECT clause in the subquery are composed (that is, only sno,sname,sage). At this time we can query the information of the IS students through the is_student view, such as: SELECT * from is_student;//(only sno,sname,sage three information is displayed, because the column name is omitted when creating the view. So the default field is the field in the SELECT statement).

If you want to create a view of an information system student, it is necessary to ensure that the view is available only to students of the information system when modifying and inserting it:

CREATE VIEW Is_student

As

Select Sno,sname,sage

From student

Where sdept= ' is '

With check option;//guarantees that the view is modified, the DBMS automatically adds the sdept= "is" condition.

For example, create a view that reflects the year the student was born:

Create View birth_student (Sno,sname,sbirth)

As

Select Sno,sname,2017-sage

from student;

In the SELECT clause, you can also define a view by using a query with the aggregate function GROUP BY clause, which is called a grouped view. For example, create a view of the student's school number and his average score:

Create View Sn_av (Sno,gave)

As

Select Sno,avg (Grade)

From SC GROUP by Sno;

Because the average result of the SELECT statement in the AS clause is obtained through the aggregate function, the individual property column names of the view Sn_av must be defined in the Create VEIW statement, not omitted.

2. Delete View

The statement to delete the view is: Drop View < view name >[cascade]; The statement removes the definition of the view from the data dictionary. If the view also exports other views, the Cascade Cascade DELETE statement can be used to remove the view along with all views exported by the view.

3. Query view

A query for a view is the same as a query on a base table, for example, you can query all the information in the Birth_student view:

SELECT * from Birth_student;

For example, in Query Sn_av view, the average value is greater than or equal to 90 student number and average score:

SELECT * FROM Sn_av where gave>90;

4. Update view

The action to update the view is ultimately implemented on a specific table, and in order to prevent users from updating the data through the view by intentionally or unintentionally modifying basic table data that is not part of the view scope, you can add a WITH CHECK OPTION clause when defining the view.

4.1 INSERT statement

For example, change the name of student number 20021215 in the Is_student view to "Zhang Fei":

Update Is_student

Set Sname= "Zhang Fei"

where sno= "20021215";

Add a new student record in the Is_student view, as follows:

Insert

Into Is_student

VALUES ("20021218", "Zhao Zilong", 20);

The statement that the above statement is converted to an update table is:

Insert

into student (sno,sname,sage,sdept)

VALUES ("20021218", "Zhao Zilong", "is");

4.2 DELETE statement

For example, delete the record in the Is_student table with the number 20021215:

Delete

From Is_student

where sno= "20021215";

4.3 UPDATE statement

For example, change the student number 20021215 in the Is_student table to the name "Guan Yu":

Update Is_student

Set sname= "Guan Yu"

where sno= "20021215";

The main functions of the view are as follows:

1) View provides some logical independence for refactoring database

The physical independence of the data is that the user's application should not be dependent on the physical structure of the database; Logical independence means that the user's application should not be affected when the database is re-constructed, such as adding new relationships or adding new fields to the original relationship. In a relational database, the most common way to refactor a database is to "vertically" divide a basic table into several basic tables, such as the student relationship:

Student (sno,sname,ssex,sage,sdept)

Divided into SX (sno,sname,ssex) and Sy (sno,sage,sdept) Two relationships, this is the result of the student table is a natural connection between SY and SX two tables. If you create a student view:

CREATE VIEW student (sno,sname,ssex,sage,sdept)

As

Select SX. Sno,sx. Sname,sx. Ssex,sy.sage,sy. Sdept

From Sx,sy

where SX. Sno=sy. Sno;

This way, although the logical structure of the database changes (to SX and SY two tables), the application does not have to change.

2) view to provide security for confidential data

With the view mechanism, when designing a database application, you can define different views for different users so that confidential data does not appear on a user view that should not be seen.

Definition view of the database