"Introduction to Database System"--3.2. View

Concept

A view is a table that is exported from one or several basic tables (or views)

View is a virtual table

The database stores only the definition of the view and does not store the data for the view

Once the view is defined, it can be queried, deleted, but the update operation has a certain limit

Defining views

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

As < sub-query >

[with CHECK OPTION];

The description subquery can be any SELECT statement, whether it can contain an ORDER BY clause and a distinct phrase, depending on the specific system;

The definition of a view can be based on another view

after adding with the CHECK option phrase constraint view to update, insert, and delete data, you should still satisfy the criteria in the subquery

note that column names are either all specified or not specified, and when not specified, use the column names in the subquery

The following conditions must be specified:

A target is listed as a clustered function or column expression

When multiple tables are connected, the same column name

You need to name a column

category View of a subset of rows: A view is derived from a single base table, and only some rows are removed, the view is called the column subset view

view with Expression : A property in a view is a property of a base table that is obtained by an arithmetic/logical/comparison operation, and the view is called a view with an expression

grouped view : A query with a clustered function and a GROUP BY clause to define a view

An example of building a view of students in information systems

CREATE VIEW is_student

As

SELECT sno,sname,sage from Student WHERE sdept= ' is ';

Create a view of the students in the information system, and constrain the View only information students ( subset views ) when making modifications and inserts

CREATE VIEW Is_student_view

As

SELECT sno,sname,sage from Student Where sdept= ' is '

With CHECK OPTION;

A view of students who have enrolled in 1 good courses (including student number, name, score)

Cretae VIEW is_s1 (Sno,sname,grade)

As

SELECT SC. Sno,sname,grade

From STUDENT,SC

WHERE STUDENT.SNO=SC. Sno and sdept= ' is ' and SC. cno= ' 1 ';

A view of students who have enrolled in the 1th course with a score of 90 or more in the information department

CREATE VIEW Is_s2

As

SELECT Sno,sname,grade

From IS_S1

WHERE grade>=90;

Define a view that reflects the year that the student was born (a view with an expression )

CREATE VIEW bt_s (Sno,sname,sbirth)

As

SELECT Sno,sname,2017-sage

From Student;

Define student number and average scores as a single view ( Group view )

CREATE VIEW S_g (SNO,GAVG)

As

SELECT Sno,avg (Grade)

From SC

GROUP by Sno;

Define all girls in the student table as a single view

CREATE VIEW g_student

As

SELECT *

From Student

WHERE ssex= ' woman ';

Delete a view

format DROP View < view name > [CASCADE];

After the view is deleted, the view definition is removed from the data dictionary

If other views are also exported on the basis of the view, the Cascade cascade Delete is used, otherwise the execution is denied

Example  DROP VIEW bt_s; /* Successful execution */

DROP VIEW is_s1; /* Deny execution */

DROP VIEW Is_si CASCADE; /* Successfully executed while deleting the is_s2*/

Query view

Concept View digestion : When the query is executed, the validity check is performed first, the tables and views involved in the query are checked, if present, the definition of the view is taken from the data dictionary, the subquery in the definition is combined with the user's query, and the query is converted to the basic table. After the query is executed after the correction

Note Aggregate functions in the WHERE clause cannot be conditional expressions, but most Rdbs are optimized for queries and can be converted correctly

Update view

Concept update view, which refers to inserting, deleting, modifying

Note that views are virtual tables that do not store the actual data, so updates to the view are eventually converted to updates to the base table

not all views can be updated, such as the average score view

View update operations for each RDBMS should refer to the specific implementations of each RDBMS

An example of an information student trying to is_student a student with a secondary school number of 201215122 to Chen Liu--update of the view

UPDATE is_student

SET sname= ' Chen Liu '

WHERE sno= ' 201215122 ';

Insert a new student record into the student View is_student (' 201215129 ', ' Zhao Xin ')--the insert of the view

INSERT into Is_student

VALUES (' 201215129 ', ' Zhao Xin ', 20);

Delete Information students view Is_student student record number 201215129-- Delete of view

DELETE from Is_student

WHERE sno= ' 201215129 ';

The role of the view

Simplifying user actions

View the same data in multiple ways

Provides some logical independence for refactoring the database (new base tables for the database, or for modifying the base tables) (without affecting the user's application)

Provide security for confidential data (e.g. all students in the whole school are in one table, but create their own views for each college, each student can only see their own college data)

Use views appropriately to make query statements clearer

"Introduction to Database System"--3.2. View