MySQL VIEW learning notes

A view is a stored SQL SELECT statement, or a virtual table (in MySQL, the view is used in front of the table ). The data can be data from one or several basic tables (or views. It can also be user-defined data. In fact, the view does not store data. The data is stored in the basic table. When the data in the basic table changes, the data in the view changes accordingly.

View function:

■ View can make the query clear:

If the data you are looking for is stored in three Relational Tables, you need to write a joint Query when you view the data. In another way, I put the data of the joint query in the view. Is it more convenient to query the data?

■ Protect important data in the database and show different data to different people:

If you want someone to help you develop a system, but you want to expose the real table, is the view the best choice?

View type:

Mysql has three types of views: MERGE, TEMPTABLE, and UNDEFINED. Without the ALGORITHM clause, the default ALGORITHM is UNDEFINED (UNDEFINED ). The algorithm affects the way MySQL processes the view.
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. MySQL selects the algorithm to be used. If possible, it tends to be MERGE rather than TEMPTABLE, because MERGE is generally more effective, and if a temporary table is used, the view cannot be updated.

Ii. Create and delete views:

Basic View creation Syntax:

The Code is as follows:	Copy code
CREATE VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] check option]

As follows:

The Code is as follows:

Copy code

Mysql> create view SC AS SELECT s. Name, c. Cname FROM students AS s RIGHT JOIN courses AS c ON s. CID1 = c. CID; Query OK, 0 rows affected (0.07 sec) Mysql> show tables; + ------------------ + | Tables_in_jiaowu | + ------------------ + | Courses | | SC | | Scores | | Students | | Tutors | + ------------------ + 5 rows in set (0.01 sec) Mysql> SELECT * FROM SC; + -------------- + ------------------ + | Name | Cname | + -------------- + ------------------ + | GuoJing | TaiJiquan | | YangGuo | TaiJiquan | | DingDian | Qishangquan | | HuFei | Wanliduxing | | HuangRong | Qianzhuwandushou | | YueLingshang | Wanliduxing | | ZhangWuji | Hamagong | | Xuzhu | TaiJiquan | | NULL | Yiyangzhi | | NULL | Jinshejianfa | | NULL | Qiankundanuoyi | | NULL | Pixiejianfa | | NULL | Jiuyinbaiguzhua | + -------------- + ------------------ + 13 rows in set (0.02 sec)

Basic View deletion Syntax:

The Code is as follows:	Copy code
Drop view [if exists] View_name [, view_name]... [RESTRICT | CASCADE]

As follows:

The Code is as follows:	Copy code
Mysql> drop view if exists SC; Query OK, 0 rows affected (0.01 sec)

View the view creation process:

The Code is as follows:

Copy code

Mysql> show create view scG * *************************** 1. row *************************** View: SC Create View: create algorithm = undefined definer = 'root' @ 'localhost' SQL security definer view 'SC' AS select s '. 'name' AS 'name', 'c '. 'cname' AS 'cname' from ('courses ''c' left join 'students' s' on ('S '. 'ci1' = 'C '. 'cid '))) Character_set_client: utf8 Collation_connection: utf8_general_ci 1 row in set (0.00 sec)

Iii. Considerations for using views in MySQL:

(1) To run the statement for creating a view, you must have the permission to create a view (crate view). If [or replace] is added, you must also have the permission to delete the view (drop view) permissions;
(2) The select statement cannot contain subqueries in the from clause;
(3) select statements cannot reference system or user variables;
(4) select statements cannot reference preprocessing statement parameters;
(5) Within the stored subroutine, the definition cannot reference the subroutine parameters or local variables;
(6) The table or view referenced in the definition must exist. However, after creating a MySQL view, you can discard the referenced table or view. To check whether view definitions have such problems, you can use the check table statement;
(7) The temporary table cannot be referenced in the definition, and the temporary view cannot be created;
(8) The table named in the view definition must already exist;
(9) The trigger program and view cannot be associated;
(10) order by can be used in the view definition. However, if you select from a specific view and the view uses a statement with its own order by, it will be ignored.
(11) The MySQL view does not support atomization (that is, the view is maintained as the actual data), nor can it create indexes, so the efficiency is low!