View of MySQL

1. What is a view?

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

2. When should I use the view?

If a query result occurs very frequently, that is, you should always take this query result to make subqueries

3. Creation syntax for Views:

CREATE VIEW name as SELECT statement;

4. What are the benefits of using a view?

① Simplifying Query statements

For example: There is a commodity table, we often have to check the average price of the goods under each column

Select Cat_id,avg (shop_price) from goods gropy by cat_id;

At this point we can create a single view:

CREATE View Avgprice as Select Cat_id,avg (shop_price) from goods gropy by cat_id;

After the creation, we have to check the average price of each column, as long as this writing

SELECT * from Avgprice; it's ready.

② can be used to control permissions

Closed the permissions of the table, but open the corresponding view permissions, only some data columns are open in the view

For example, our goods product list, we do not want to let others see our sales price, this time we can view the list of permissions closed, create a view

CREATE View Showgoods as select goods_id,goods_name from goods;

No SALES price list is allowed.

③ can be used when big data is divided into tables.

For example, if the table row data is more than 2 million rows, the speed slows down

You can split a table of data into 4 tables to store

News list

NewSID 1,2,3,4 ...

NEWS1,NEWS2,NEWS3,NEWS4 table

Scatter the data of a table into 4 tables, there are many scattered methods,

The most common is the ID modulo to calculate

id%4+1=[1,2,3,4]

...

You can also use the view to form a single view of four sheets

CREATE VIEW news as SELECT * FROM NEWS1 Union SELECT * FROM NEWS2 Union ...

5. Changes to the view

Alter VIEW view name as SELECT statement;

6. Relationship of views to tables

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

7. Does changing the view affect the table?

The change of ① view will also affect the table;

② but views are not always able to be added and censored;

The data of the view can be modified when it corresponds to the table data one by one;

The insert for the view should also be aware that the view must contain a column in the table that does not have a default value.

8. Algorithm of Views (arithmetic rules)

Algorithm = merge/temptable/undefined

Merge: When referencing views, statements that refer to views are merged with statements that define views

means that the view is just a rule, a statement rule, when the query view, the query view of the statement

For example: where ... Those associated with the creation of the statement where clause are merged, parsed, and formed into a SELECT statement.

As an example:

Let's create a view to query all items with prices greater than 3000

Create VIEW G2 As select Goods_id,goods_name,shop_price from goods where Shop_price > 3000;

Then we query the view again, plus a Where condition <5000

SELECT * FROM G2 where Shop_price < 5000

At this point, it will combine two statements to form such a SELECT statement

Select Goods_id,goods_name,shop_price from goods where Shop_price > Shop_price < 5000;

TempTable: is to create a temporary table based on the creation of the statement, and then query the view of the statement from the temporary table to check the data

There is a significant difference between merge and Temptalbe:

The merge eventually went to the goods table, and TempTable went to the virtual table.

For example: We want to get the most expensive items under each column.

First we create a view to find out which items in each column are sorted by price descending

CREATE View LMJ as select Cat_id,goods_id,goods_name,shop_price from goods order by cat_id,shop_price Desc;

When we query this view and then group the cat_id, is it possible to get the results we want?

SELECT * from LMJ Group by cat_id;

The answer is no, because it combines the statements of our creation view and the statements of the query view into

Select Cat_id,goods_id,goods_name,shop_price from goods group by cat_id ORDER by cat_id,shop_price Desc;

And if we specify it when creating the view, the operation rules are: temptable

Create algorithm=temptable View LMJ as select Cat_id,goods_id,goods_name,shop_price from goods order by Cat_id,shop_price Desc

Then we query the view: SELECT * frm lmj GROUP by cat_id; we'll get the results we want.

It will first select cat_id,goods_id,goods_name,shop_price from goods order by cat_id,shop_price Desc; The result of this SQL statement is placed in a temporary table, Then we'll look at this temporary form and we'll get the results we want, not the merger.

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

View of MySQL