MySQL View principle explanation and use Daquan

Lin Bingwen Evankaka Original works. Reprint please specify the source Http://blog.csdn.net/evankaka

Absrtact: This paper mainly discusses the definition, principle and how to use, create and delete the view in MySQL.

A View Overview

A view is a virtual table whose contents are defined by a query. As with a real table, a view contains a series of column and row data with names. However, the view does not exist in the database as a stored set of data values. Row and column data is derived from the table referenced by the query that defines the view, and is generated dynamically when the view is referenced.
The view acts like a filter for the underlying table referenced in it. A filter that defines a view can come from one or more tables or other views of the current or other database. There are no restrictions on querying through views, and there are few restrictions on data modification through them.
A view is a SQL statement of queries stored in a database, and it is mainly for two reasons: security reasons, the view can hide some data, such as: Social Insurance Fund table, you can use the view only display name, address, and do not show social security number and wages, etc., another reason is to make complex queries easy to understand and use. This view is like a "window" from which you can see only the columns of data you want to see. This means that you can use SELECT * on this view, and what you see will be the columns of data you give in the view definition:

Since the definition of a view is based on a basic table, why do you define a view? This is because the use of views in a reasonable way can bring many benefits:
1, the view can simplify the user operation
The view mechanism allows users to focus on the data they care about. If the data is not directly from the base table, you can define the view to make the database look simple, clear, and simplify the user's data query operations . For example, a view that defines a number of table joins hides the connection between tables and tables from the user. In other words, the user does just a simple query of a virtual table, and how this virtual table is derived, the user does not need to understand.
2. Views enable users to view the same data in multiple angles
The view mechanism enables different users to view the same data in different ways, and this flexibility is necessary when many different kinds of users share the same database.
3. Views provide a degree of logical independence for refactoring databases
The physical independence of data means that the user's application is not dependent on the physical structure of the database. The logical independence of data is that when a database is re-constructed, such as adding new relationships or adding new fields to existing relationships, the user's application is not affected. Hierarchical database and mesh database generally can support the physical independence of the data, but the logical independence can not be fully supported.
In the lock database, the reconfiguration of the database is often unavoidable. The most common way to refactor a database is to divide a base table "vertically" into multiple base tables. For example: Student student Relations (sno,sname,ssex,sage,sdept),
It is divided into two relationships of SX (Sno,sname,sage) and Sy (sno,ssex,sdept). The original table student is the result of the natural connection of 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;

This way, although the logical structure of the database changes (to SX and SY two tables), the application does not have to be modified because the newly created view is defined as the user's original relationship, leaving the user's external mode intact, and the user's application can still find the data through the view.
Of course, the view can only provide a certain degree of logical independence of the data, for example, because the update of the view is conditional, the statement that modifies the data in the application may still change due to changes in the base table construction.
4, view can provide security protection for confidential data
With the view mechanism, you can define different views of different users when you design a database application, so that confidential data does not show up on a user view that should not see the data now. This way, the view mechanism automatically provides security protection for confidential data. For example, the student table covers the 15 faculties of the school student data, on which 15 views can be defined, each containing only one faculty student data, and only the director of each faculty is allowed to query and modify the primitive student view.
5, the appropriate use of the view can be more clearly expressed in the query
For example, it is often necessary to perform such a query "to find out the course number for each student who has obtained the highest grade". You can define a view to find the highest score for each student:

CREATE VIEW vmgradeasselect Sno,max (Grade) Mgradefrom Scgroup by Sno

Then complete the query with the following query statement:

SELECT SC. Sno,cno from Sc,vmgrade WHERE SC. Sno = Vmgrade. Sno and SC. Grade = Vmgrade. Mgrade;

Second, data preparation

1. Employee table

CREATE TABLE t_employee (        ID INT  PRIMARY KEY  auto_increment,        NAME CHAR () not NULL,        SEX  CHAR ( 2) Not NULL, the age INT is not        null,        DEPARTMENT char (TEN) is not NULL,        SALARY INT is not  null,        HOME CHAR (+), 
   marry char (2) not NULL DEFAULT  ' no ',               HOBBY char (30));

Insert data:

INSERT into Learning.t_employee (ID, NAME, SEX, Age,department, SALARY, HOME, MARRY, HOBBY) VALUES (NULL, ' Little Red ', ' female ', 20, ' HR ') , ' 4000 ', ' Guangdong ', ' no ', ' tennis '); INSERT into Learning.t_employee (ID, NAME, SEX, Age,department, SALARY, HOME, MARRY, HOBBY) VALUES (NULL, ' tomorrow ', ' female ', 21, ' personnel ', ' 9000 ', ' Beijing ', ' no ', ' tennis '); INSERT into Learning.t_employee (ID, NAME, SEX, Age,department, SALARY, HOME, MARRY, HOBBY) VALUES (NULL, ' everyday ', ' male ', 22, ' research and development ', ' 8000 ', ' Shanghai ', ' no ', ' music '); INSERT into Learning.t_employee (ID, NAME, SEX , Age,department, SALARY, HOME, MARRY, HOBBY) VALUES (NULL, ' big ', ' female ', 23, ' research and development ', ' 9000 ', ' Chongqing ', ' no ', ' none '); INSERT into Learning.t_employee (ID, NAME, SEX, Age,department, SALARY, HOME, MARRY, HOBBY) VALUES (NULL, ' under Wang ', ' female ', 24, ' research and development ', ' 9000 ', ' Sichuan ', ' is ', ' Football '); INSERT into Learning.t_employee (ID, NAME, SEX, Age,department, SALARY, HOME, MARRY, HOBBY) VALUES (NULL, ' nameless ', ' Male ', 25, ' Sales department ', ' 6000 ', ' Fujian ', ' no ', ' game '); INSERT into Learning.t_employee (ID, NAME, SEX, Age,department, SALARY, HOME, MARRY, HOBBY) VALUES (NULL, ' Don't know ', ' female ', 26, ' Sales ', ' 5000 ', ' Shanxi ', ' no ', ' basketball '); 

Result of insert:

Then define an employee information table:

Create TABLE t_employee_detail (ID INT PRIMARY Key,pos char (Ten) not null,experence char (TEN) not Null,constraint ' fk_id ' for Eign KEY (ID) REFERENCES T_employee (ID))

Insert the following:

Insert into T_employee_detail (id,pos,experence) VALUES (1, ' Personnel Management ', ' working two years '), insert into T_employee_detail (Id,pos, Experence) VALUES (2, ' recruiting ', ' working two years '), insert into T_employee_detail (id,pos,experence) VALUES (3, ' Junior engineer ', ' working one year '); insert Into T_employee_detail (id,pos,experence) VALUES (4, ' intermediate engineer ', ' working two years '); INSERT into T_employee_detail (id,pos,experence) VALUES (5, ' Senior Engineer ', ' working three years '); insert into T_employee_detail (id,pos,experence) VALUES (6, ' Sales rep ', ' working two years '); INSERT INTO T_ Employee_detail (id,pos,experence) VALUES (7, ' Salesperson ', ' working one year ');

Content:

Third, use case

1. Syntax

CREATE [OR REPLACE] [algorithm = {UNDEFINED | MERGE | TempTable}] VIEW [db_name.] view_name [(column_list)] as select_statement [with [cascaded | LOCAL] CHECK OPTION]

This statement allows you to create a view that, given [OR REPLACE], overwrites the original view when it already has a view with the same name. Select_statement is a query statement that can be queried from a table or other view. The view belongs to the database, so you need to specify the name of the database and, if unspecified, create a new view in the current database.
Tables and databases share the same namespace in the database, so the database cannot contain tables and views of the same name, and the column names of the views cannot be duplicated.

Before you create a view, you should see if you have permissions:

SELECT Select_priv,create_view_priv from Mysql.user WHERE user= ' root '

Y indicates a permission to create

2. Create a view on a single table

In the employee table is the CREATE view

CREATE VIEW v_view1 (ID, name, sex, age,department) as SELECT ID, name, sex, age,department from Learning.t_employee;

Then the display content:

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;

Show results

SELECT * from V_view2

4. View 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 view

In MySQL, updating views refers to inserting (insert), Updating (update), and deleting (delete) tables by view. Because a view is a virtual table with no data in it, it is updated by converting to the base table when the view is updated.
When you update a view, only the data within the permission range is updated. is out of range and cannot be updated.

Before update:

After the update:

UPDATE v_view2 SET pos= ' senior Engineer ' WHERE name= ' every day '

The data on the corresponding real table also changed.

SELECT * from Learning.t_employee_detail WHERE t_employee_detail.id=3

Non-updatable Views:
Some views are updatable. That is, you can use them in statements such as update, delete, or insert to update the contents of the base table. For updatable views, you must have a one-to-one relationship between the rows in the view and the rows in the base table. There are also specific other structures that make the view non-updatable. More specifically, if the view contains any of the following structures, it is not updatable:

· Aggregate Functions (SUM (), MIN (), MAX (), COUNT (), and so on).
· DISTINCT
· GROUP by
· Having
· Union or UNION ALL
· Subqueries located in the select list
· Join
· Non-updatable view in FROM clause
· A subquery in the WHERE clause that refers to the table in the FROM clause.
· Only literal values are referenced (in that case, there is no base table to update).
· Algorithm = temptable (using temporary tables always makes the view non-updatable).

Attention

Although the data can be updated in the view, there are a number of limitations. In general, it's a good idea to use a view as a virtual table for querying data instead of updating the data through a view. Because when you use a view to update data, you may fail to update data if you do not fully consider the limitations of updating data in the view.

Can cascaded and local decide if the view can be updated?
with[cascaded| LOCAL] CHECK option can you decide whether the view can be updated? The basic definitions of these two parameters are as follows:
The local parameter indicates that the view is updated as long as the conditions defined by the view itself are met.
The cascaded parameter indicates that all related views and table conditions need to be met when the view is updated. When not specified, the parameter is the default value.

Use with CHECK option:
(with CHECK option does not work for views without a Where condition)

CREATE VIEW v_view3 (ID, name,sex,age,department,salary, HOME, MARRY, HOBBY) as SELECT ID, NAME, sex,age,department,salary , home,marry,hobby from Learning.t_employee WHERE department= ' personnel department ' with LOCAL CHECK OPTION;

Indicates that only those who are inserted into the Department of personnel are qualified.

And then insert a line:

INSERT into learning. V_VIEW3 (ID, NAME, SEX, Age,department, SALARY, HOME, MARRY, HOBBY) VALUES (NULL, ' will be ', ' female ', 20, ' hr ', ' 4500 ', ' Guangdong ', ' no ', ' tennis ‘);

Look at the results:

SELECT * from learning. V_view3

Also look at the data in the real table:

Then insert one:

INSERT into learning. V_VIEW3 (ID, NAME, SEX, Age,department, SALARY, HOME, MARRY, HOBBY) VALUES (NULL, ' Qqqqvasvas ', ' female ', 20, ' research and development ', ' 4500 ', ' Shanghai ', ' No ', ' tennis ');

Results show Insert failed
For the WITH CHECK option usage, the following is summarized:
By manipulating the base table with a view with CHECK option (just facing a single table, the view that joins multiple tables is looking for answers), the following conclusions are drawn: the inserted data can be queried through the view to conform with CHECK option otherwise it will not conform;
First, the view only operates the data it can query, and for the data it cannot query, even if the base table has, it can not be manipulated by the view.
1. For update, with CHECK option, to ensure that the data is queried by the view after the update
2. For delete, there is no same as check option
4. For INSERT, with CHECK option, to ensure that the data is queried by the view after insert
For views without a WHERE clause, using WITH CHECK option is redundant

7. Delete View

DROP View IF EXISTS view name

Copyright NOTICE: This article for Bo Master Lin Bingwen Evankaka original article, without Bo Master permission not reproduced.

MySQL View principle explanation and use Daquan