MySQL Learning essay--View

View Concepts

A view in a database refers to a virtual table whose contents are defined by the query . Like a real table, a view is made up of rows and columns. The data source of the view is queried by the SQL statement and does not store the data

View Creation Method

Format:

CREATE view name as SQL query

Description

1. CREATE view//creating views
2. View name//The name of the virtual table è table name
3. As followed by query statements, cannot be omitted

Viewing View creation Statements

Format:

1. Show CREATE VIEW name
2. Show CREATE VIEW name \g

Attention:

1. \G indicates that column names are displayed vertically
2. Do not add \g columns to display horizontally
3. \g cannot be appended with semicolons

View the View structure

DESC View Name

Show All views

Format:

SELECT * from Information_schema.views;

Description

All views are stored in the view table of Information_schema

Modify a View

Format:

1. ALTER VIEW name as SQL query
2. Create or replace view name as SQL query//have this view modified, no view created

Updating data in a view

Format:

Update View name set field name = field value where[condition]//Same as normal table update

Attention:

The view modifies the data in the original table, so the base table is not generally modified through the view

Delete a view

Drop View Name

Algorithm of the View

Overview:

Refers to when a view executes, according to what way

Execution mode:

1. Merge Merging algorithm

   The execution of the merge, each time it executes, merges the SQL statements of the view with the SQL statements of the external query view, and finally executes

   Format:

   Create algorithm = merge view name as SQL query statement

2. TempTable Temporal table algorithm

   Query that performs the view before performing other operations

   A temporary table pattern that, whenever queried, generates a temporary table of results for the SELECT statement used by the view, and then queries within the current temporary table

   Format:

   Create algorithm = temptable view name as SQL query

3. Understanding Merge By example, difference of temptable algorithm

Scenarios for views

1. Simplifying SQL statements with views
2. Hide certain fields, protect information

MySQL Learning essay--View