Concepts and Applications of views in MySql

The basic concept view of a view is a table exported from one or more basic tables (or views. Unlike the basic table, it is a virtual table. The database only stores view definitions, but does not Store View data. The data is still stored in the original basic table. Therefore, the data in the basic table changes, and the data queried from the view changes accordingly. View

The basic concept view of a view is a table exported from one or more basic tables (or views. Unlike the basic table, it is a virtual table. The database only stores view definitions, but does not Store View data. The data is still stored in the original basic table. Therefore, the data in the basic table changes, and the data queried from the view changes accordingly. View

Basic concepts of views

A view is a table exported from one or more basic tables (or views. Unlike the basic table, it is a virtual table.

The database only stores view definitions, but does not Store View data. The data is still stored in the original basic table. Therefore, the data in the basic table changes, and the data queried from the view changes accordingly.

As defined, it can be queried and deleted like a basic table. You can also define a new view on top of a view, but there are some restrictions on the update (add, delete, and modify) operations of the view.

Advantages of a view

A view has the following advantages over a basic table:

1. view can simplify user operations

View mechanism users can focus on the data they care about. If the data is not directly from the basic table, you can define the view to make the database look simple and clear, and simplify the user's data query operations.

2. A view allows users to view the same data from different perspectives.

For fixed basic tables, we can create different views for different users so that different users can see the information they need.

3. Views provide a certain degree of logic for restructuring databases.

For example, if the original table A is divided into Table B and Table C, we can still build A view A on the basis of Table B and Table C, while the program using this data table can remain unchanged.

4. view security protection for confidential data

For example, the scores of each course constitute a basic table, but each student can only view the scores of his or her own course. Therefore, you can create a view for each student, hide the data of other students and only show their own data.

5. Use a view to clearly express the query.

Sometimes using an existing view for query can greatly reduce the complexity of the query statement.

Note: The following data tables are used as examples: student, course, and SC. For details, see EXISTS and NOT EXISTS in the Mysql database.

Create View

SQL view creation command

CREATE VIEW <视图名> [( <列名> [, <列名> ]…)]
AS <子查询>
[With check option];

The subquery can be any complex SELECT statement, but generally it cannot contain the order by clause and DISTINCT phrase (because the subquery is an intermediate result ).
The attribute column name that makes up the view can beAll omitted or all specifiedIf omitted, fields in the SELECT target column in The subquery are implicitly composed;

However, you must specify all the column names for the view in the following cases:
(1) A target column is not a simple attribute name, but a clustering function or column expression, or a target column *
(2) several fields with the same column as the view are selected for multi-table join.
(3) You need to enable a new and more appropriate name for a column in the view.

When RDBMS executes the create view statement, it only saves the VIEW definition to the data dictionary and does not execute the SELECT statement. When querying a view, data is retrieved from the basic table according to the view definition.

The created view can be divided into the following three types:

Row and column subset View

If a view is exported from a single basic table, and only some rows and columns of the basic table are removed, but the primary code is retained, this type of view is called a row-column subset view.

Example 1.1

Create a view for students in the Information System

SQL statement:

CREATE VIEW IS_StudentAS SELECT Sno, Sname, Sage FROM Student WHERE  Sdept= 'IS'

In this example, the column name of the view IS_Student is omitted, which is implicitly composed of three columns in the SELECT clause in the subquery.

With check option: Make sure that the updated, inserted, or deleted rows meet the predicate conditions in the view definition when performing UPDATE, INSERT, and DELETE operations on the view (that is, the conditional expressions in the subquery)

Example 1.2

Create a view for students in the information system and require that the update operations performed through this view only involve students in the information system.

SQL statement:

CREATE VIEW IS_StudentAS SELECT Sno, Sname, Sage      FROM  Student      WHERE  Sdept= 'IS'WITH CHECK OPTION;

Since the with check option clause IS added when the IS_Student view IS defined, When you insert, modify, and delete the view in the future, the RDBMS automatically adds the Sdept = 'is condition:

• Insert operation: DBMS automatically checks whether the Sdept attribute value IS 'is'
  • If not, the insert operation is rejected.
  • If the Sdept attribute value IS not provided, the Sdept value IS defined as 'is'
• Modification Operation: DBMS automatically adds the Sdept = 'is Condition
• Delete operation: DBMS automatically adds the Sdept = 'is Condition

A view can be created not only on a single basic table, but also on multiple basic tables or on a basic table and view.

Example 1.3 set up the Student View of the No. 1 course in the Information Department

CREATE VIEW IS_S1(Sno, Sname, Grade)AS SELECT Student.Sno, Sname, Grade      FROM  Student, SC      WHERE Sdept= 'IS' AND                     Student.Sno=SC.Sno AND                     SC.Cno= '1';

Example 1.4 create a view for students whose information department has taken course 1 and whose score is higher than 90

CREATE VIEW IS_S2AS SELECT Sno, Sname, Grade      FROM IS_S1      WHERE Grade>=90;

View with Expression

When defining a basic table, in order to reduce redundant data in the database, the table only stores basic data, and the data derived from basic data after various computations is generally not stored.

However, because the data in the view is not actually stored, you can set some derived attribute columns based on application requirements when defining the view.

Because these derived attribute columns do not actually exist in the basic tag, they are also called virtual columns. A view with virtual columns is also called a view with expressions.

Example 2.1 define a view that reflects the birth year of a student

CREATE  VIEW BT_S(Sno, Sname, Sbirth)AS SELECT Sno, Sname, 2000-SageFROM  Student;

Group view

You can also use queries with Aggregate functions and group by clauses to define a view. This view is called a GROUP view.

Example 3.1 define a student's student ID and average score as a view

CREATE VIEW S_G(Sno, Gavg)AS SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno;

Because the average score of the target column of the SELECT statement in the AS sub-statement is obtained through the function clustering function, each attribute column name that makes up the S_G VIEW must be clearly defined in create view, s_G is a group view.

Example 3.2 define all girl records in the Student table as a view

Create view F_Student1 (stdnum, name, sex, age, dept) as select * FROM Student WHERE Ssex = 'femal ';

The F_Student1 view is created by the subquery "SELECT *". You need to clearly define each attribute column name that makes up the F_Student1 view.

Query View

After a view is defined, You can query the view just like a basic table.

From the user's perspective, the query view is the same as the query basic table.

However, all View queries are interpreted as basic data tables by the database management software, because the views are abstracted based on the basic tables.

Example 4.1 find students under the age of 20 in the View of Information System students

SELECT Sno, SageFROM IS_StudentWHERE Sage<20;

IS_Student view definition (view definition Example 1 ):

CREATE VIEW IS_StudentAS SELECT Sno, Sname, Sage      FROM Student      WHERE Sdept= 'IS';

The query statement after DBMS conversion is:

SELECT Sno, SageFROM StudentWHERE Sdept='IS' AND Sage<20;

Update View

Updating a view refers to inserting, deleting, and modifying data through the view.

Because the view is a virtual table that does not actually store data, the update of the view must be converted to the update of the basic table.

From the user's perspective, the update view is the same as the update basic table; RDBMS converts it to the update operation on the basic table.

To prevent users from performing operations on basic table data that is not within the view range intentionally or unintentionally when updating data through the view, you can add the with check option clause when defining the view.

In this way, when adding or deleting data in a view, RDBMS checks the conditions in the view definition. If the conditions are not met, the operation is denied.

Example 5.1 change the Student name in student view IS_Student student ID 95002 to "Liu Chen"

UPDATE IS_StudentSET Sname = 'Liu Chen 'WHERE Sno = '000000 ';

Converted statement:

UPDATEStudentSET Sname = 'Liu Chen 'WHERE Sno = '000000' AND Sdept = 'is ';

In relational databases, not all views are updatable, because some view updates cannot be converted to the corresponding basic table updates in a meaningful manner.

Generally, the row-column subset view can be updated. In addition, some views can be updated theoretically, but their exact features are still subject to research, some views cannot be updated theoretically.

Delete View

Statement format for deleting a VIEW: DROP VIEW <视图名> ;

Deleting a view has no effect on the basic table that generates the view.

When deleting a base table, all VIEW definitions exported from the base table are not deleted, but cannot be used. You must use drop view to explicitly delete a base table.

Example 6.1

Delete view BT_S:

DROP VIEW BT_S;

Delete view IS_S1:

DROP VIEW IS_S1;