Shulou(Shulou.com)06/03 Report--

This article is about how views are used in MYSQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

The view is a kind of virtual table defined by the query statement of the real data table in the database. The view itself does not store the data but stores the definition of the query data.

What are the advantages of views?

First of all, it is clear that the query of the view itself is virtually no different from the efficiency of the sql join table query directly as a definition. So don't expect to use views to improve your efficiency.

Only three visible advantages have been found for the time being.

(1)。 For example, when your program sends a query to the database server, the number of bytes of the statement transmitted by the view is less.

(2)。 The code on the program is written less, and it is easier to read the fields needed for the view query, without having to consider the details of the multi-table query.

(3)。 When multiple tables update fields or reduce redundancy, you don't have to modify the program, you just need to update the view.

What can really improve the query efficiency is the reasonable planning of table structure and the establishment of appropriate query index. If you are interested in understanding, you can take a look at this query optimization article.

Use of views

[1] create a view

Createviewview_nameas your complex query statement

Extension: we can use the specified algorithm to create the view

CreateALGORITHM=TEMPTABLEviewview_nameas your complex query statement

CreateALGORITHM=MERGEviewview_nameas your complex query statement

TEMPTABLE temporary table schema can only be queried but not updated

When the MERGE merge replacement mode updates the data in the view, it also updates the data in the real table (if not specified, the system will use it by default).

[2] Delete view

Dropviewview_name

A small case of using a view

Learning code is always the fastest in a case.

Copy the code

# creation of users table

CREATETABLE`users` (

`u _ id`int (4) NOTNULLauto_increment

`username`varchar (20) defaultNULL

`age`int (4) defaultNULL

`roomy`varchar (20) defaultNULL

PRIMARYKEY (`u_ id`)

) ENGINE=InnoDBAUTO_INCREMENT=6DEFAULTCHARSET=utf8

# creation of course table

CREATETABLE`room` (

`cid`int (4) NOTNULLauto_increment

`Coursename`varchar (20) defaultNULL

`substitution`varchar (255) defaultNULL

PRIMARYKEY (`c_ id`)

) ENGINE=InnoDBAUTO_INCREMENT=5DEFAULTCHARSET=utf8

Thank you for reading! This is the end of this article on "how to use views in MYSQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.