Mysql view function-mysql tutorial

No details. no views of mysql have never been used in normal work, so I have not paid attention to it, recently, when configuring sphinx full-text search, because it does not support a single index to index multiple tables, it needs to index through views, so a mysql View function is specified. mysql supports views from mysql 5.0, but from the official documentation

No details. no views of mysql have never been used in normal work, so I have not paid attention to it, recently, when configuring sphinx full-text search, because it does not support a single index to index multiple tables, it needs to index through views, so a mysql View function is specified. mysql supports views from mysql 5.0, but from the official documentation

<无详细内容> <无>

I have never used the mysql view function in my daily work, so I have never paid attention to it. recently, when configuring sphinx full-text search, it does not support a single index to index multiple tables, this requires the view to be indexed, so a specific mysql View function is viewed. mysql supports views starting from mysql 5.0. However, the earlier version is not very complete from the official documentation. mysql 5.1 officially provides views. take a look at the view in the mysql Manual Chapter 22nd: View directory 22.1. alter view syntax 22.2. create view syntax 22.3. drop view syntax 22.4. the show create view syntax provides the VIEW function (including updatable views) in MySQL 5.1 ). This chapter discusses the following topics: use create view or alter view to CREATE or change a VIEW. · Use drop view to destroy a VIEW. · Use show create view to display VIEW metadata. For restrictions on using views, see Appendix I: feature restrictions. If you have never supported upgrading an earlier version of the view to MySQL 5.1, you should upgrade the authorization table to include View-related permissions to use the view. See section 2.10.2 "upgrade authorization table ". 22.1. alter view syntax ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] check option] This statement is used to change the definition of an existing view. Its syntax is similar to create view. See section 22.2 "create view syntax ". This statement requires the create view and DROP permissions for the VIEW, and some permissions for each column referenced in the SELECT statement. 22.2. create view syntax CREATE [or replace] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] check option] This statement can create a new view. if the or replace clause is specified, this statement can REPLACE the existing view. Select_statement is a SELECT statement that defines a view. This statement can be selected from the base table or other views. This statement requires the create view permission for the VIEW and some permissions for each column selected by the SELECT statement. Columns used elsewhere in the SELECT statement must have the SELECT permission. If there is an or replace clause, you must have the DROP permission on The View. View is a database. By default, a new view is created for the current database. To explicitly create a view in a given database, you must specify the name db_name.view_name when creating the view. Mysql> create view test. v as select * FROM t; tables and views share the same namespace in the database. Therefore, the database cannot contain tables and views with the same name. A view must have a unique column name and must not have duplicates, just like a base table. By default, the column names retrieved by the SELECT statement are used as View column names. To define a clear name for a view column, use the optional column_list clause to list 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 columns retrieved by the SELECT statement can be simple references to the table columns. You can also use expressions such as functions, constant values, and operators. Unqualified tables or views in the SELECT statement are interpreted according to the default database. By specifying a table or View name with an appropriate database name, a view can reference a view in a table or other database. Multiple SELECT statements can be used to create views. A view can reference a base table or other views. It can use UNION, UNION, and subquery. SELECT does not even need to reference any table. In the following example, we define the view for selecting two columns from the other TABLE, and provide the expressions calculated based on these columns: mysql> create table t (qty INT, price INT); mysql> insert into t VALUES (3, 50); mysql> create view v as select qty, price, qty * price AS value FROM t; mysql> SELECT * FROM v; + ------ + ------- + | qty | price | value | + ------ + ------- + | 3 | 50 | 150 | + ------ + ------- + View definitions are subject to the following restrictions:: · The SELECT statement cannot contain subqueries in the FROM clause. · SELECT statements cannot reference system or user variables. · The SELECT statement cannot reference preprocessing statement parameters. · Within the stored subroutine, the definition cannot reference subroutine parameters or local variables. · The table or view referenced in the definition must exist. However, after creating a view, you can discard the referenced table or view. To CHECK whether view definitions have such problems, you can 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 the trigger program with the view. 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. For other options or clauses in the definition, they will be added to the options or clauses of the statements that reference the view, but the effect is not defined. For example, if a view definition contains a LIMIT clause and is selected from a specific view and the View uses a statement with its own LIMIT clause, no LIMIT clause is defined. The same principle applies to other options, such as ALL, DISTINCT, or SQL _SMALL_RESULT after the SELECT keyword, and applies to other clauses, such as INTO, FOR UPDATE, LOCK IN SHARE MODE, and PROCEDURE. If a VIEW is created and the query processing environment is changed by changing the system variables, the results obtained from the VIEW are affected: mysql> create view v as select charset (CHAR (65 )), COLLATION (CHAR (65); Query OK, 0 rows affected (0.00 sec) mysql> set names 'latin1'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM v; + ------------------- + --------------------- + | CHARSET (CHAR (65) | COLLATION (CHAR (65 )) | + ------------------- + | latin1 | latin1_swedish_ci | + ----------- -------- + --------------------- + 1 row in set (0.00 sec) mysql> set names 'utf8'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM v; + ------------------- + | CHARSET (CHAR (65) | COLLATION (CHAR (65 )) | + ------------------- + limit + | utf8 | utf8_general_ci | + --------------------- + ------------------- + 1 row in set (0.00 sec) optional ALGORITHM clause is an extension of standard SQL MySQL. ALGORITHM can have three values: MERGE, TEMPTABLE, or UNDEFINED. Without the ALGORITHM clause, the default ALGORITHM is UNDEFINED (UNDEFINED ). The algorithm affects the way MySQL processes the view. For MERGE, the text of the statements that reference the view is combined with the view definition, so that a part of the view definition replaces the corresponding part of the statement. For TEMPTABLE, The View result is placed in a temporary table and then executed using it. For 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. One reason to explicitly select TEMPTABLE is that the lock on the base table can be released after the temporary table is created and before the statement processing is completed. Compared with the MERGE algorithm, the lock release speed is faster, so that other clients using the view will not be blocked for a long time. The view algorithm can be UNDEFINED. There are three methods: · There is no ALGORITHM clause in the create view statement. · The create view statement has one explicit ALGORITHM = UNDEFINED clause. · Specify ALGORITHM = MERGE for views that can only be processed by temporary tables. In this case, MySQL generates an alarm and sets the algorithm to UNDEFINED. As described earlier, MERGE is processed by combining the corresponding part of the view definition into statements that reference the view. The following example briefly describes how MERGE works. In this example, assume that there is a VIEW v_merge: create algorithm = merge view v_merge (vc1, vc2) ASSELECT c1, c2 FROM t WHERE c3> 100; example 1: assume that the following statement is issued: SELECT * FROM v_merge; MySQL: · v_merge becomes t · * to vc1, vc2, corresponding to c1 and c2. The statement that will be executed by adding the View WHERE clause is: SELECT c1, c2 FROM t WHERE c3> 100. Example 2: assume that the following statement is issued: SELECT * FROM v_merge WHERE vc1 <100; the statement is handled in a similar way as described earlier, but vc1 <100 changes to c1 <100, use the AND connector to add the WHERE clause of the view to the WHERE clause of the statement (parentheses are added to ensure correct priority Executed Clause ). The resulting statement becomes: SELECT c1, c2 FROM t WHERE (c3> 100) AND (c1 <100); in fact, the statement to be executed is a WHERE clause in the following form: the WHERE (select WHERE) AND (view WHERE) MERGE algorithm requires that the rows in the view AND the rows in the base table have a one-to-one relationship. If the link does not exist. You must replace it with a temporary table. If a view contains any of the following structures, the one-to-one relationship will be lost: · aggregate functions (SUM (), MIN (), MAX (), COUNT (), and so on ). · DISTINCT · group by · HAVING · UNION or union all · references only text values (in this case, there is no basic table ). Some views are updatable. That is to say, you can use them in statements such as UPDATE, DELETE, or INSERT to UPDATE the content of the base table. For updatable views, the rows in the view and the rows in the base table must have a one-to-one relationship. There are some other specific structures that make the views unupdatable. More specifically, if a view contains any of the following structures, it cannot be updated: · aggregate functions (SUM (), MIN (), MAX (), COUNT ). · DISTINCT · group by · HAVING · UNION or union all · subqueries in the selection list · Join · non-updatable view in the FROM clause · subqueries in the WHERE clause, reference the table in the FROM clause. · Reference only text values (in this case, there is no basic table to be updated ). · ALGORITHM = TEMPTABLE (using a temporary table will always make the view unupdatable ). With regard to insertability (available INSERT statement updates), if it also meets the following additional requirements on The View column, the updatable view can also be inserted: · duplicate View column names are not allowed. · The View must contain all columns in the base table without default values. · View columns must be simple column references rather than export columns. The export column is not a simple column reference, but exported from the expression. The following provides some examples of export columns: · 3.14159 · col1 + 3 · UPPER (col2) · col3/col4 · (subquery) hybrid views of simple column references and export columns cannot be inserted, however, if you only update non-export columns, the view can be updated. Consider the following VIEW: create view v as select col1, 1 AS col2 FROM t; this VIEW cannot be inserted because col2 is exported FROM the expression. However, if col2 is not updated during the update, it can be updated. This type of UPDATE is allowed: UPDATE v SET col1 = 0; the following UPDATE is not allowed because it attempts to UPDATE the export column: UPDATE v SET col2 = 0; in some cases, the ability to update a multi-table view is assumed that it can be processed using the MERGE algorithm. For this reason, the view must use an internal UNION (instead of an external UNION or UNION ). In addition, only a single table in the view definition can be updated. Therefore, the SET clause must only name the columns of a table in the view. Theoretically, the view of union all is not allowed, because temporary tables are used to process them in the implementation. If a multi-table updatable view is inserted into a single table, INSERT can work. DELETE is not supported. For updatable views, you can specify the with check option clause to prevent inserting or updating rows, unless the WHERE clause in the select_statement on the row is "true ". In the with check option clause about updatable views, when the view is defined based on another view, the LOCAL and CASCADED keywords determine the scope of the CHECK test. The LOCAL keyword restricts the check option so that it only applies to the defined View. CASCADED checks the base table to be evaluated. If no keyword is specified, the default value is CASCADED. Consider the following TABLE and VIEW set definitions: mysql> create table t1 (a INT); mysql> create view v1 as select * FROM t1 WHERE a <2-> with check option; mysql> create view v2 as select * FROM v1 WHERE a> 0-> with local check option; mysql> create view v3 as select * FROM v1 WHERE a> 0-> with cascaded check option; here, VIEW v2 and v3 are defined based on another VIEW v1. V2 has the local check option. Therefore, only the v2 check is performed to test the inserted items. V3 has the CASCADED check option. Therefore, it not only tests the inserted items for its own check, but also tests the inserted items for the basic view check. These differences are described in the following statement: ql> insert into v2 VALUES (2); Query OK, 1 row affected (0.00 sec) mysql> insert into v3 VALUES (2); ERROR 1369 (HY000): check option failed 'test. the updatability of the v3 view may be affected by the value of the system variable updatable_views_with_limit. See section 5.3.3 "server system variables ". INFORMATION_SCHEMA contains one VIEWS table from which information about view objects can be obtained. See section 23.1.15 "INFORMATION_SCHEMA VIEWS Table ". 22.3. drop view syntax drop view [if exists] view_name [, view_name]... [RESTRICT | CASCADE] drop view can delete one or more views. You must have the DROP permission on each view. You can use the keyword if exists to prevent errors due to nonexistent views. If this clause is specified, a NOTE is generated for each view that does not exist. See section 13.5.4.22 "show warnings syntax ". If RESTRICT and CASCADE are given, they are parsed and ignored. 22.4. show create view syntax show create view view_name This statement provides a create view statement for creating a given VIEW. Mysql> show create view v; + ------ + ---------------------------------------------------- + | View | Create View | + ------ + Preview + | v | create view 'test '. 'V' AS select 1 AS 'A', 2 AS 'B' | + ------ + -------------------------------------------------------- +