MySQL view and mysql View

MySQL view and mysql View

Part 1: Basic concepts and applications

1. View Overview

The MySQL view and the Oracle view are a concept, that is, a virtual table with no data, and the data is stored in the base table;

2. Add a view
1: syntax
CREATE
[Or replace]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = {user | CURRENT_USER}]
[SQL SECURITY {DEFINER | INVOKER}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] check option]
2: Example (create view)
Mysql> use ehrt;
Database changed
Mysql> create algorithm = MERGE view t1 as select * from user;

3. Modify the view
1: syntax
ALTER
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = {user | CURRENT_USER}]
[SQL SECURITY {DEFINER | INVOKER}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] check option]
2: Example
Mysql> alter view t1 as select * from user where name = 'jacson ';

4. view the view

1. view Data

Mysql> use ehrt;
Mysql> select * from t1;
+ ---- + ------ + ----- +
| Id | name | sex |
+ ---- + ------ + ----- +
| 1 | Jacson | 0 |
| 2 | Bai | 0 |
+ ---- + ------ + ----- +

2 rows in set (0.00 sec)

2. view the View Structure

Mysql> show create view t1G;
 
5. delete a view
Mysql> drop view t1;

Query OK, 0 rows affected (0.00 sec)

Part 2: Advanced Concepts

I. view type
Mysql has three types of views: MERGE, TEMPTABLE, and UNDEFINED. If the ALGORITHM clause is not specified, the default ALGORITHM is UNDEFINED. This method affects the speed of MySQL processing views.
1. MERGE combines the text of the statements that reference the view with the view definition, so that a part of the view definition replaces the corresponding part of the statement.
2. The results of the TEMPTABLE view will be placed in the temporary table and then executed using it.
3. UNDEFINED: Let MySQL select the algorithm to be used.

It is recommended that you select MERGE when the physical host memory is small or the program can be transplanted.

-- To be continued

Part 3: Advanced Applications

1. Is the monitoring view invalid?

-- To be continued

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.