mysql--View

1. Concept of views

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.

1. How to create a view

1 --format: CREATE view name as SQL query;2 3 --Description:4 5 --1) Create view keyword6 7 --2) View name: Is the virtual table name8 9 --3) as keywordTen  One --4) SQL query, that is: Select query statement through the view can have a selection of display fields. A Create ViewV_age as Select *  fromStudentwhereAge< -;

3. View the View creation statement

1 Create  View View name \g

4 Viewing the View structure

1 Desc View name

5. Show All views

1 Select *  from Information_schema.views \g

6. Modify View 1) method one:

1  Alter View  View name as    select query;

2) Method Two:

1  Create or Replace View  as

7. Update the data in the view

First, update the data in the original data table, and in view the data in the view

After updating the original table, view (the view cannot find the data that the original table was modified, so there will be one less data )

Note: Changes to the contents of the original data table are modified by the view. The data in this view is synchronized with the original table data.

Recommendation: Data is generally not modified through views.

8. Delete a view

1 Drop  View Name

9. View algorithm

How to use Navicat:

Overview: Refers to when a view is executed and according to which way it is executed

1) Merge merging

The execution of the merge, the SQL statements of the view are merged with the SQL statements of the external query view, and executed at the time of execution;

1  Create Algorithm= Merge  view as SQL query;

2) temptable temporary table

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. (i.e. execute the view first)

1 Create Algorithm= temptable  view as SQL query;

3) Merge, difference of temptable algorithm:

Example: Statistics: Tb_phone, the highest price of similar mobile phone products?

10. View Scenario 1) Hide certain fields to protect the data

2) Use complex SQL statements for simplified statement input

mysql--View