Shulou(Shulou.com)05/31 Report--

This article mainly explains the concept of MySQL view. Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let Xiaobian take you to learn the concept of MySQL view!

1. Basic concepts

The view itself is a virtual table that holds no data. When using SQL statements to access views, the data retrieved is generated by MySQL from other tables, and the views and tables are in the same namespace. View query data is relatively safe, view can hide some data and structure, only let users see the data within the permission, so that complex queries are easy to understand and use.

2. View Usage

Basic usage of the view is now demonstrated based on user and order management.

Base table structure

basic syntax

CREATEORREPLACEVIEWview_name

ASselect_statement

Note: Tables and views share the same namespace in the database, so a database cannot contain tables and views with the same name.

User Order View

CREATEORREPLACE

VIEWuser_order_viewASSELECT

t1.id,t1.user_name,t2.order_no,t2.good_id,

t2.good_name,t2.num,t2.total_price

FROMv01_usert1

LEFTJOINv02_ordert2ONt2.user_id=t1.id;

view call

Here and MySQL table query is basically the same, you can use a variety of query conditions.

View implementation

temporary list algorithm

The server will save the SQL data of the view query in a temporary table. The structure of the temporary table is consistent with the structure of the view fields. This is the most taboo operation in SQL query optimization. If the data volume is slightly too large, it will seriously affect performance. Temporary tables are created if the view cannot create a one-to-one mapping with the original table, which shows that the view is not very simple or even very complex.

merging algorithm

The server executes queries based on the tables used in the view and merges the query structure back to the client.

Method of distinction

Execute the following query statement to analyze the performance parameters of the execution.

EXPLAINSELECT*FROMuser_order_view;

Observe the select_type field in the query result. If it is DERIVED, it indicates that a temporary table is used. Here SQL performs analysis of the syntax behind the optimization part of the detailed explanation.

At this point, I believe that everyone has a deeper understanding of the concept of MySQL view, so let's actually operate it! Here is the website, more related content can enter the relevant channels for inquiry, pay attention to us, continue to learn!

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.