Database--View

1. Definition

A

view is a table that is exported from one or more base tables (or views) and is a virtual table.

2. Concept

View is a way to view data in a database table;

The view provides the ability to store pre-defined query statements as objects in the database for later use;

The view is only a logical object, not a physical object, because the view does not occupy the physical storage space;

? The table that is queried in the view is called the base table of the view;

The contents of the view include: a subset of the columns of the base table or a subset of the rows, two or more base table unions, two or more base table connections, a statistical summary of the base table, a subset of the view, and a mixture of the view and the base table.

3. View function (Advantages)

Centralized user-use data;

The complexity of the mask database, the view separates the complexity of the database design from the user's shielding;

Simplify the management of user rights;

? reorganize data for output to other applications.

4. Scope of use of the view

? Focus on specific data: Views enable users to focus on the specific data they are interested in and the specific tasks they are responsible for. Unnecessary data or sensitive data can not be present in the view.

? Simplifying data manipulation: Views Simplify how users work with data. You can define common connections, projections, union queries, and select queries as views, and users do not have to specify all conditions and conditional qualifications each time they perform additional operations on the data.

Custom data: Views allow users to view data in different ways, even when they are using the same query at the same time.

5. View definition

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

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

as< Sub-query >

[with CHECK OPTION];

A subquery can be any complex SELECT statement, but it is generally not allowed to contain an order by child statement and a distinct phrase.

? The WITH CHECK OPTION phrase indicates that the update, insert, and delete operations are made to ensure that the updated, inserted, or deleted rows satisfy 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, There is no third option. If the individual property column names in the view are omitted, the view is suppressed by the fields in the target column of the SELECT clause in the subquery. You must specify all the column names that comprise the view, as follows:

A. A target column is not a purely property name, but rather a clustered or column expression. B. A multiple table join is a field that selects several columns with the same name as the view. C. You need to use a new, more appropriate name for a column in the view.

Example 1: Building a view of a computer department student

CREATE VIEW Cs_computer

As

SELECT Sno,sname,sage

From student

WHERE sdept= ' CS ';

Example 2: Building a view of a computer department student and requiring that the view be modified and inserted should still be the only student in the computer department.

CREATE VIEW Cs_computer

As

SELECT Sno,sname,sage

From student

WHERE sdept= ' CS '

With CHECK OPTION;

Example 3: Establish a student view of a computer system with a score of 90 or more.

CREATE VIEW cs_s1 (Sno,sname,cgrade)

As

SELECT Student.sno,sname,cgrade

From STUDENT,SC

WHERE sdept= ' CS ' and Student.sno=sc.sno and cgrade>=90;

Database--View