MySQL base view

Original: MySQL base view

In queries , We often look at query results as temporary tables .

What is View ? View can look at a virtual table . is a projection of the table from some sort of operation. .

Since the view is just a projection of a query for a table , the main step is to query the table . The result of the query is named view. .

Relationship of views to tables

A view is a query result of a table , and the data of the natural table changes , affecting the results of the view .

How does the view change ?

0: View additions and deletions will also affect the table

1: However , The view is always able to be added and censored .

View in some cases , It can be modified .

Requirement : The data of the view corresponds to the data of the table . just like the mapping of functions .

Table --roll out the data for the view

Views --roll out the data for the table

Definition of the view :

A view is a virtual table formed by the results of a query . is a SQL Query Results

The creation syntax for the view :

Create View name as select statement ;

Delete syntax for the view :

Drop View name

Changes to the View

Alter View view name as select xxxxxx

Why do you want a view ?

Answer : 1: can simplify the query

2: permission control is possible

Closed the permissions of the table , but open the corresponding view permissions , the view only open part of the data

3: can be used when big data is divided into tables

For example , when the number of rows in a table exceeds tens of thousands of lines , it slows down.

You can split a table's data into 4 tables to store it .

algorithm of the View

Algorithm = merge/temptable/undefined

Merge: When referencing a view , statements referencing the view are merged with statements that define the view .

TempTable: When referencing a view , create a temporary table based on the view's creation statement

Undefined: undefined , automatic , let the system help you choose .

Merge, which means that the view is just a rule , a statement rule , when querying the View ,

Merge the Query view statements ( such as where ) with the statement WHERE clause at the time of Creation , analysis .

Form a Select statement .

Example : Create a statement for a view :

Mysql> CREATE VIEW G2 as select Goods_id,cat_id,goods_name,shop_price from goods where shop_price>2000

Statement for Query view :

SELECT * FROM G2 where Shop_price < 3000;

The final statement executed :

Select Goods_id,cat_id,goods_name,shop_price from goods where Shop_price > Shop_price < 3000

Instead, temptable creates a temporary table based on the creation statement ,

The statement from the query view then checks the data from the temporary table .

Create algorethm=temptable View G2 as select Goods_id,cat_id,goods_name,shop_price from goods where Shop_price > 2000

Statement for Query view :

SELECT * FROM G2 where Shop_price < 3000;

The final 2 words: take the data and put it on the temporary table , then go to the temporary table .

Select goods_id,cat_id,goods_name,shop_price from goods where Shop_price > 2000;

========> temptable

Check the temp table again.

Select * from temptable where shop_price< 3000

MySQL base view