MySQL Learning notes-view

MySQL Learning notes-view

A view is a way to view the underlying table data, and its role is

-Simplify your development and use views to perform multiple table operations
-Data security, developers can not directly to the table operation, can not be deleted, modify the operation
-Data refactoring, in a limited table, to generate the desired view at different angles, simplifying the business

CREATE View Syntax

CREATE View Syntax:

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]    VIEW view_name [(column_list)]    AS select_statement    [WITH [CASCADED | LOCAL] CHECK OPTION]

The statement can create a new view, and if given an OR replace clause, the statement can also replace the existing view. Select_statement is a SELECT statement that gives a definition of the view. The statement can be selected from a base table or other view.

This statement requires the CREATE VIEW permission for the view and some permissions on each column selected by the SELECT statement. You must have SELECT permission for columns that are used elsewhere in the SELECT statement. If there is an OR replace clause, you must have drop permission on the view.

The view belongs to the database. By default, a new view is created in the current database. To explicitly create a view in a given database, when you create it, you should specify the name as

db_name.view_name。

Cases:

CREATE VIEW TEST.V as SELECT * from t;

Tables and views share the same namespace in the database, so the database cannot contain tables and views with the same name. The view is actually a virtual table.

The view must have a unique column name and no duplication, just like a base table. By default, the column names retrieved by the SELECT statement are used as the view column name. To define an explicit name for a view column, use the optional column_list clause to list the IDs separated by commas. The number of names in column_list must be equal to the number of columns retrieved by the SELECT statement.

The column retrieved by the SELECT statement can be a simple reference to a table column. It can also be an expression that uses functions, constant values, operators, and so on.

For unqualified tables or views in a SELECT statement, they are interpreted according to the default database. A view can refer to a view in a table or other database by qualifying the table or view name with the appropriate database name.

The

can create views using a variety of SELECT statements. A view can reference a base table or other view. It can use Union, union, and subquery. Select does not even need to reference any tables. In the following example, a view is defined that selects two columns from another table and gives the expression computed from these columns:

 CR Eate  table  t (seg1 int , seg2 int );  insert  INTO  t Span class= "Hljs-keyword" >values  (3 , 50 ) ; create  VIEW  V Span class= "Hljs-keyword" >as  select  seg1, SEG2, seg1*seg2 as  value  from  t;  select  * FROM  v;  

Show Results:

The view definition is subject to the following limitations:

- The SELECT statement cannot contain subqueries from the FROM clause.

-The SELECT statement cannot reference a system or user variable.

-The SELECT statement cannot reference a preprocessing statement parameter.

-Within a stored subroutine, the definition cannot refer to subroutine parameters or local variables.

-The table or view referenced in the definition must exist. However, after you create a view, you can discard the table or view that defines the reference. To check whether the view definition has such problems, use the Check table statement.

-The temporary table cannot be referenced in the definition, and the temporary view cannot be created.

-The table named in the view definition must already exist.

- You cannot associate a trigger with a view.

The order by is allowed in the view definition, but it is ignored if a selection is made from a specific view that uses a statement with its own order by.

If you create a view that references a system variable and changes the query processing environment by changing the system variables, it affects the results obtained from the view, and the example creates a view that contains the system character set and the proofing method information

create view v as select charset(char(65)) , collation(char(65));

CHARSET (str) returns the character set for a string argument.
>collation (str) returns how string parameters are sorted

View results:

To change the character set again query results:

set names ‘utf8‘;select * from v;

Will get a different result:

CHARSET (CHAR (+))	COLLATION (CHAR (+))
Utf8	Utf8_general_ci

(My computer has no way to show the correct results, so the data directly posted out-_-)

The optional algorithm clause is a MySQL extension to standard SQL. Algorithm three values are desirable:
-MERGE
-TempTable
-UNDEFINED

If there is no algorithm clause, the default algorithm is undefined (undefined). The algorithm affects how MySQL handles views.

• For merge, the text of the statement referencing the view is merged with the view definition so that some part of the view definition supersedes the corresponding part of the statement.

• For temptable, the result of the view is placed in the staging table and then used to execute the statement.

• For Undefined,mysql, you will select the algorithm that you want to use. If possible, it tends to merge rather than temptable, because the merge is usually more efficient, and if a temporary table is used, the view is not updatable.

One benefit of choosing temptable is that you can release locks on the base table after the temporary table is created and before the statement processing is complete. Locking is released faster than the merge algorithm, so that other clients using the view are not masked for long periods of time.

There are three scenarios in which the view algorithm is undefined:

· There is no algorithm clause in the CREATE VIEW statement.

· The CREATE view statement has 1 explicit algorithm = undefined clauses.

· Specify algorithm = MERGE for views that can only be processed with temporary tables. In this case, MySQL generates an alarm and sets the algorithm to undefined.

As described earlier, the merge is processed by merging the corresponding parts of the view definition into the statements that refer to the view.

In the following example, a brief description of how merge works. In this example, assume that there are 1 views v_merge that have the following definitions:

CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS       SELECT c1, c2 FROM t WHERE c3 > 100;

Example 1: Assume that the following statement was issued:

SELECT * FROM v_merge;

MySQL processes the statement in the following way:

1·         v_merge成为t2·         *成为vc1、vc2，与c1、c2对应3·         增加视图WHERE子句

The resulting statement will be executed as follows:

SELECT c1, c2 FROM t WHERE c3 > 100;

Example 2: Assume that the following statement was issued:

SELECT * FROM v_merge WHERE vc1 < 100;

The statement is handled in a similar manner as previously described, but VC1 < 100 becomes C1 < 100, and the WHERE clause of the view is added to the WHERE clause of the statement using the and join word (the parentheses are added to ensure that the clause is executed in the correct order of precedence). The resulting statement that will be executed becomes:

SELECT c1, c2 FROM t WHERE (c3 > 100) AND (c1 < 100);

In fact, the statement to be executed is a WHERE clause with the following form:

WHERE (select WHERE) AND (view WHERE)

The merge algorithm requires a one-to-one relationship between the rows in the view and the rows in the base table. If you do not have the relationship. You must use a temporary table instead. If the view contains any of the following structures, it loses a one-to-one relationship:

· Aggregate Functions (SUM (), MIN (), MAX (), COUNT (), and so on).

· DISTINCT

· GROUP by

· Having

· Union or UNION ALL

· Only literal values are referenced (in this case, there is no base table). If CREATE view v as select 1 as Num;

Some views are updatable. That is, you can use them in statements such as update, delete, or insert to update the contents of the base table. For updatable views, you must have a one-to-one relationship between the rows in the view and the rows in the base table. That meets the requirements of the merge algorithm.

If the view contains any of the following structures, it is not updatable:

· Aggregate Functions (SUM (), MIN (), MAX (), COUNT (), and so on).

· DISTINCT

· GROUP by

· Having

· Union or UNION ALL

· Subqueries located in the select list

· Join

· Non-updatable view in FROM clause

· A subquery in the WHERE clause that refers to the table in the FROM clause.

· Only literal values are referenced (in that case, there is no base table to update).

· Algorithm = temptable (using temporary tables always makes the view non-updatable).

For example, I have a t_user table in my database, and I build a one-to-one view of it:

You can see that when I change the data in the view, the base table corresponding to the view changes accordingly:

If I create a non-one-to-one view of T_user, I can see the immutable view:

For updatable views, a with CHECK option clause is given to prevent inserting or updating rows unless the WHERE clause in select_statement on the row is true. That is, the inserted data conforms to the where requirement in select_statement to be inserted.

In the WITH CHECK OPTION clause on updatable views, when the view is defined according to another view, the local and cascaded keywords determine the scope of the inspection test.
The Local keyword restricts check option so that it only acts on the defined view.
Cascaded will check the base table that will be evaluated. If either keyword is not given, the default value is cascaded. The meaning of cascaded is cascading.

V1,v2,v3 Three views are created below:

  CREATE TABLE T1 (a INT);  CREATE VIEW v1  as SELECT *  from t1 WHERE a < 2W ITH CHECK OPTION;     #创建t表的一对一视图 CREATE VIEW v2  as SELECT *  from v1 WHERE a > 
     
      0with
       
      LOCAL 
      CHECK 
      OPTION;     
       CREATE VIEW v3  as SELECT *  from v1 WHERE a > 0W ITH cascaded CHECK OPTION;     

Here, Views v2 and V3 are defined v1 according to another view.
V2 has the local check option, so the insert item is only tested against the V2 check, that is, only the limitations of the V2 where statement are considered.
V3 has the cascaded check option, so inserting items are tested against the check of the basic view, not only for its own inspection.
As you can see from the results below, the same data is inserted, but the results are different:

This is because when inserting v2, only WHERE a > 0 is queried
Inserting v3 will also query where a > 0 and v1 where a < 2, so the number 2 cannot be inserted.

DROP View Syntax

DROP View Syntax

DROP VIEW [IF EXISTS]    ...    [RESTRICT | CASCADE]

Drop view can delete 1 or more views. You must have drop permissions on each view.

You can use the keyword if exists to prevent errors due to non-existent views.

SHOW CREATE View Syntax

SHOW CREATE View Syntax

SHOW CREATE VIEW view_name

This statement gives the 1 CREATE VIEW statement that creates a given view.

For example, query the V3 view:

MySQL Learning notes-view