Principles and usage of the MySql View _ MySQL

Original works of Lin Bingwen Evankaka. Reprinted please indicate the source http: blogcsdnnetevankaka Abstract: This article focuses on the definition, principle, and usage, creation, and deletion of visual charts in MySql.

1. View overview

A view is a virtual table whose content is defined by the query. Like a real table, a view contains a series of columns and row data with names. However, a view does not exist in the database as a stored data value set. Rows and columns are used to define tables referenced by View queries and dynamically generate tables when views are referenced.
For the referenced basic table, the View function is similar to filtering. The filtering of the definition view can be from one or more tables of the current or other databases, or other views. There are no restrictions on query through views, and there are few restrictions on data modification through them.
A view is an SQL statement stored in a database. it is mainly for two reasons: security reasons. a view can hide some data, such as the social insurance fund table, you can use the view to display only the name and address, but not the social insurance number and wage number. Another reason is that complex queries are easy to understand and use. This view is like a "window" where you can only see the data columns you want to view. This means that you can use SELECT * In this view, and you will see the data columns you have given in the view definition:

Since the View definition is based on the basic table, why should we define the view? This is because the rational use of views can bring many benefits:
1. View can simplify user operations
The view mechanism allows users to focus on the data of interest. 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. For example, the views that define several table connections hide the join operation between tables. In other words, the user only performs simple queries on a virtual table, but does not need to know how the virtual table is obtained.
2. Views allow users to view the same data from multiple perspectives
The view mechanism allows different users to view the same data in different ways. this flexibility is necessary when many users of different types share the same database.
3. Views provide a certain degree of logical independence for restructured databases
The physical independence of data means that your applications do not depend on the physical structure of the database. The logical independence of data means that when the database is re-constructed, your applications will not be affected if a new link or a new field is added to the original link. Hierarchical databases and network databases generally support physical independence of data, while logical independence cannot be fully supported.
In the case of relational databases, the re-construction of databases is often inevitable. The most common way to reconstruct a database is to vertically divide a basic table into multiple basic tables. For example, Student (Sno, Sname, Ssex, Sage, Sdept ),
There are two relationships: SX (Sno, Sname, Sage) and SY (Sno, Ssex, Sdept. The Student of the original table is the result of the natural connection between the SX table and the SY table. If you create 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；

In this way, although the logical structure of the database has changed (to SX and SY tables), the application does not need to be modified because the newly created view is defined as the user's original relationship, keep the user's external mode unchanged, and the user's application can still search for data through the view.
Of course, a view can only provide logical independence of data to a certain extent. for example, a view update is conditional, therefore, the statement for modifying data in the application may still change due to the change in the basic table structure.
4. View provides security protection for confidential data
With the view mechanism, you can define different views for different users when designing a database application system, so that confidential data does not appear in the user view that should not be seen. In this way, the view mechanism automatically provides security protection for confidential data. For example, if the Student table involves the Student data of 15 departments in the school, 15 views can be defined on it. each view only contains the Student data of one department, only the directors of each department can query and modify the student view of the original department.
5. Use a view to clearly express the query
For example, you often need to execute this query to "find the course number for each student for which he has obtained the highest score ". You can first define a view to obtain the highest score of each student:

CREATE VIEW VMGRADEASSELECT Sno，MAX(Grade) MgradeFROM SCGROUP BY Sno

Then, use the following query statement to complete the query:

SELECT SC.Sno，Cno FROM SC，VMGRADE WHERE SC.Sno = VMGRADE.Sno AND SC.Grade = VMGRADE.Mgrade；

II. Data Preparation

1. employee table

Create table t_employee (id int primary key AUTO_INCREMENT, name char (30) not null, sex char (2) not null, age int not null, department char (10) not null, salary int not null, home char (30), marry char (2) not null default 'no', Hoby CHAR (30 ));

Insert data:

Insert into learning. t_employee (ID, NAME, SEX, AGE, DEPARTMENT, SALARY, HOME, MARRY, Hober) VALUES (NULL, 'red', 'female, 20, 'HR ', '123', 'Guangdong ', 'no', 'Tennis'); insert into learning. t_employee (ID, NAME, SEX, AGE, DEPARTMENT, SALARY, HOME, MARRY, Hober) VALUES (NULL, 'Tomorrow ', 'female', 21, 'personnel DEPARTMENT ', '123', 'Beijing', 'no', 'Tennis '); insert into learning. t_employee (ID, NAME, SEX, AGE, DEPARTMENT, SALARY, HOME, MARRY, Hober) VALUES (NULL, 'dayday', 'male', 22, 'R & d department ', '123', 'Shanghai', 'no', 'Music'); insert into learning. t_employee (ID, NAME, SEX, AGE, DEPARTMENT, SALARY, HOME, MARRY, Hober) VALUES (NULL, 'big data', 'female ', 23, 'R & d department ', '123', 'Chongqing ', 'no', 'none'); insert into learning. t_employee (ID, NAME, SEX, AGE, DEPARTMENT, SALARY, HOME, MARRY, Hober) VALUES (NULL, '王', 'female, 24, 'R & d department ', '123', 'Sichuan ', 'yes', 'soccer'); insert into learning. t_employee (ID, NAME, SEX, AGE, DEPARTMENT, SALARY, HOME, MARRY, Hober) VALUES (NULL, 'unname', 'male', 25, 'sales DEPARTMENT ', '123', 'Fujian ', 'no', 'game'); insert into learning. t_employee (ID, NAME, SEX, AGE, DEPARTMENT, SALARY, HOME, MARRY, Hober) VALUES (NULL, 'Unknown ', 'female', 26, 'sales DEPARTMENT ', '123', 'Shanxi ', 'no', 'basketball ');

Insert result:

Then define an employee information table:

create TABLE t_employee_detail(ID INT PRIMARY KEY,POS CHAR(10) NOT NULL,EXPERENCE CHAR(10) NOT NULL,CONSTRAINT `FK_ID` FOREIGN KEY(ID) REFERENCES t_employee(ID))

Insert as follows:

Insert into t_employee_detail (ID, POS, EXPERENCE) VALUES (1, 'personnel management', 'working for two year'); insert into t_employee_detail (ID, POS, EXPERENCE) VALUES (2, 'recruiting personnel ', 'working two year'); insert into t_employee_detail (ID, POS, EXPERENCE) VALUES (3, 'Junior engineer', 'working year '); insert into t_employee_detail (ID, POS, EXPERENCE) VALUES (4, 'intermediate engineer', 'working for two year'); insert into t_employee_detail (ID, POS, EXPERENCE) VALUES (5, 'senior engineer', 'three years' working year'); insert into t_employee_detail (ID, POS, EXPERENCE) VALUES (6, 'sales representative ', 'two years' working year '); insert into t_employee_detail (ID, POS, EXPERENCE) VALUES (7, 'Salesman', 'working year ');

Content:

III. Use cases

1. Syntax

CREATE [or replace] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW [db_name.] view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] check option]

You can use this statement to create a view. if [or replace] is specified, the view with the same name will overwrite the original view. Select_statement is a query statement that can be queried from tables or other views. The view belongs to the database, so you need to specify the name of the database. if it is not specified, it indicates creating a new view in the current database.
Tables and databases share the same namespace in the database. Therefore, the database cannot contain tables and views with the same name, and the column names of views cannot be repeated.

Before creating a View, check whether you have the permission:

SELECT SELECT_priv,create_view_priv from mysql.user WHERE user='root'

Y indicates that you have created permissions.

2. create a view on a single table

Create view in employee table

CREATE VIEW V_VIEW1(ID, NAME, SEX, AGE,DEPARTMENT) AS SELECT ID, NAME, SEX, AGE,DEPARTMENT FROM learning.t_employee;

Then the content is displayed:

SELECT * FROM V_VIEW1

3. create a view on multiple tables

CREATE VIEW V_VIEW2(ID, NAME, SEX, AGE,DEPARTMENT,POS,EXPERENCE) AS SELECT a.ID, a.NAME, a.SEX, a.AGE,a.DEPARTMENT,b.POS,b.EXPERENCE FROM learning.t_employee a,learning.t_employee_detail b WHERE a.ID=b.ID;

Display result

SELECT * FROM V_VIEW2

4. View The View

(1) DESCRIBE command

DESCRIBE V_VIEW2

(2) SHOW TABLE STATUS

show TABLE status LIKE 'V_VIEW2'

(3) show create view command

show CREATE view V_VIEW2

5. modify the View

(1) create or replace command

CREATE OR REPLACE VIEW V_VIEW1(ID, NAME, SEX) AS SELECT ID, NAME, SEX  FROM learning.t_employee;

(2) ALTER command

ALTER VIEW  V_VIEW1(ID, NAME) AS SELECT ID, NAME  FROM learning.t_employee;SELECT * FROM learning.v_view1

6. Update The View

In MySQL, an UPDATE view is used to INSERT, UPDATE, and DELETE data in tables. Because the view is a virtual table with no data in it, when the view is updated, it is converted to the basic table for update.
When updating a view, only data within the permission range can be updated. If the update is out of the range, it cannot be updated.

Before update:

After update:

UPDATE V_VIEW2 set pos = 'senior engineer 'where name = 'Everyday'

The corresponding real table data has also changed

SELECT * FROM learning.t_employee_detail WHERE t_employee_detail.ID=3

Views that cannot be updated:
Some views are updatable. That is to say, you can use them in statements such as UPDATE, DELETE, or INSERT to UPDATE the content of the base table. For updatable views, the rows in the view and the rows in the base table must have a one-to-one relationship. There are some other specific structures that make the views unupdatable. More specifically, if a view contains any of the following structures, it cannot be updated:

· Aggregate functions (SUM (), MIN (), MAX (), COUNT ).
· DISTINCT
· GROUP
· HAVING
· UNION or UNION ALL
· Subquery in the selection list
· Join
· Non-updatable view in the FROM clause
· The subquery in the WHERE clause references the table in the FROM clause.
· Reference only text values (in this case, there is no basic table to be updated ).
· ALGORITHM = TEMPTABLE (using a temporary table will always make the view unupdatable ).

Note:

Although data can be updated in a view, there are many restrictions. Generally, it is better to use a view as a virtual table for Data Query instead of updating data through the view. Because, when you use a view to update data, if you do not fully consider the restrictions of updating data in the view, data update may fail.

Can CASCADED and LOCAL determine whether the view can be updated?
WITH [CASCADED | LOCAL] can check option determine whether the view can be updated? The basic definitions of these two parameters are as follows:
The LOCAL parameter indicates that when updating a view, only the conditions defined by the view can be met.
The CASCADED parameter indicates that all views and tables must be updated. If not specified, this parameter is the default value.

With check option usage:
(With check option does not work for views without where conditions)

Create view V_VIEW3 (ID, NAME, SEX, AGE, DEPARTMENT, SALARY, HOME, MARRY, holobby) as select id, NAME, SEX, AGE, DEPARTMENT, SALARY, HOME, MARRY, holobby FROM learning. t_employee where department = 'personnel authorization' with local check option;

Indicates that only the person with the Department specified as the personnel department is allowed to be inserted.

Insert:

Insert into learning. v_VIEW3 (ID, NAME, SEX, AGE, DEPARTMENT, SALARY, HOME, MARRY, Hober) VALUES (NULL, 'will be', 'female ', 20, 'HR ', '123', 'Guangdong ', 'no', 'Tennis ');

Check the result:

SELECT * FROM learning.V_VIEW3

View the data in the real table at the same time:

Insert another entry:

Insert into learning. v_VIEW3 (ID, NAME, SEX, AGE, DEPARTMENT, SALARY, HOME, MARRY, Hober) VALUES (NULL, 'qqqqvasvas ', 'female', 20, 'R & d department ', '123', 'Shanghai', 'no', 'Tennis ');

Insertion failed
The usage of with check option is summarized as follows:
You can use a view with the check option to operate the base table (only for a single table, the view connecting multiple tables is looking for an answer). the following conclusions are available, if the view can be queried, it will match with check option; otherwise, it will not;
First, the view only operates on the data that can be queried. for the data that cannot be queried by the view, even if the base table exists, it cannot be operated through the view.
1. for update, with check option is available. make sure that the data is queried by the view after update.
2. for delete, all with check options are the same
4. with check option for insert, make sure that data is queried by the view after insert.
For views without where clauses, using with check option is redundant.

7. delete a View

Drop view if exists view Name