MYSQL/MARIADB View

This article directory:
1. Create and Modify views
2. View algorithm Merge, temptable
3. Deleting and viewing View information
4. Check for invalid views

A view is a type of table expression, so it is also a virtual table. When you view an operation, the data is temporarily fetched from the table using a statement.

1. Create and Modify views

CREATE[OR REPLACE] [algorithm = {UNDEFINED |MERGE| TempTable}]VIEW[IF  not EXISTS] view_name [(column_list)] asselect_statement [ with[cascaded|LOCAL]CHECK OPTION]ALTER[algorithm = {UNDEFINED |MERGE| TempTable}]VIEWview_name [(column_list)] asselect_statement [ with[cascaded|LOCAL]CHECK OPTION]

When used or replace , this statement is equivalent to alter VIEW if the view exists, equivalent to create view if the view does not exist.

About algorithm, the following article detailed description.

with [local|cascaded] check option: Its object is an updatable view (that is, the merge algorithm's view). For updatable views, a clause can be given WITH CHECK OPTION to prevent the insertion or updating of illegal records unless the WHERE clause in select_statement on the row is "true". Where local means that you can insert or update as long as the filter criteria for this view are met, cascaded indicates that the filter criteria for all views must be met before they can be inserted or updated. The default is with cascaded check option .

For example, the following statement defines 3 views, where the latter two are created with the first view as a base table. When inserting records to VIEW2 and VIEW3, if the field in the record a=10: Because the cascaded option is used by View2 by default, a=10 does not meet view1 conditions, so the insertion fails, and VIEW3 uses the local option, Just satisfy the view3 condition, so a=10 satisfies the condition, that is, it can be inserted successfully.

 Create view view1  as select *  from T where a< /c0> Create view view2  as select *  from View1 where a >5; Create view view3  as select *  from View1 where a>5 with  local check option;

When the view is created in MYSQL/MARIADB, the column is defined as a " cured " state. That is, if an asterisk "*" is used in a SELECT statement in a view definition statement to denote all columns, it is converted to the corresponding column name when the view is created and stored in the View definition statement, so if a new column in the base table is not retrieved by the view's SQL statement.

For example:

create or replace view v_cityasselect * from world.city where id>200;

View definition statements: You can see that the asterisk in the SELECT statement is substituted for the corresponding column name.

Mysql> mysql> Show CREATE View v_city\g***************************1.Row *************************** view:v_city Create view:create algorithm=undefined definer=' Root '@' 192.168.100.% 'SQL SECURITY Definer VIEW' v_city 'As Select' City '.' ID 'As' ID ',' City '.' Name 'As' Name ',' City '.' CountryCode 'As' CountryCode ',' City '.' District 'As' District ',' City '.' Population 'As' Population 'From' City 'Where (' City '.' ID '> $) character_set_client:utf8collation_connection:utf8_general_ci1RowinchSet (0.00Sec

In the Select section of the VIEW definition statement in MYSQL/MARIADB, the from is not a subquery. At this point mysql/mariadb and other types of databases are somewhat different. If, under some conditions, the definition statement from the view exactly requires a subquery, you can define the subquery first as a view and then place the view in the FROM clause. When you update a view, you actually go to the corresponding base table to update it.

2. View algorithm Merge, temptable

algorithm={undefined|merge|temptable}Is the view selection algorithm. The algorithm of the view affects how the view is processed by MYSQL/MARIADB:

1. Merge merges the statements that reference the view with the view definition statements so that some part of the view definition supersedes the corresponding part of the statement. For example, when a view is referenced, the view name is replaced with the Singga table name, the columns involved in the query are replaced with the column names in the Singga table, and so on.
2. TempTable The result of the view into a temporary table, and then executes the corresponding statement operation using the table's data.
3. Undefined is to let mysql/mariadb himself choose Merge or temptable, it is more inclined to merge. This is the default value when algorithm is not specified.

For example, here is a special example of merge that illustrates the merge algorithm:

MariaDB [test]>Create or Replace table t(id int auto_increment, name char) , age int, primary key(ID))                ; MariaDB [test]>INSERT INTO  t(name,age) values(' Chenyi ',+), (' huanger ','), (' zhangsan ','), (' Lisi ') ,(' Wangwu '), (' zhaoliu ',26< /c22>)      ; MariaDB [test]>Select* fromt;+----+----------+------+| ID | name | Age |+----+----------+------+|1| Chenyi | +||2| Huanger | A||3| Zhangsan | at||4| Lisi | -||5| Wangwu | -||6| Zhaoliu | -|+----+----------+------+# Create a id<5 view My_viewMariaDB [test]> Create or replace algorithm=Merge View my_view(vf1,vf2) asselect id,name  from T where Age<24                 ; MariaDB [test]>Select* frommy_view;+-----+----------+| Vf1 | VF2 |+-----+----------+|1| Chenyi | |2| Huanger | |3| Zhangsan |+-----+----------+

The result returned is 3 rows of records.

Because it is a view of the merge algorithm, the items in the view are replaced with the items in the base table T when referencing the view (here is the query operation). Including:

1. The "*" number is replaced with VF1 and VF2, which are replaced with the ID and name in the T table.
2. Replace the My_view in the FROM clause with table T.
3. Plus the WHERE clause in the VIEW definition statement.

Therefore, select * from My_view; at execution time, it is converted to the following query statement:

select id,name from t where age<24;

If the query is My_view, use the following statement:

selectfromwhere vf1<2; +-----+--------+| vf1 | vf2    |+-----+--------+|   1 | chenyi |+-----+--------+

At execution time, the statement is replaced with the following statement:

select id,name from t where id<2 and age<24;

The view is an updatable view only when using the merge algorithm, because the temptable algorithm operates on data that is populated into temporary tables and cannot be updated with the base table.

Because the merge algorithm combines a base table, it has some limitations and the merge algorithm cannot be used when the following conditions occur:

1. Having
2. LIMIT
3. GROUP by
4. DISTINCT
5. UNION
6. UNION All
7. Aggregate functions such as Max (), MIN (), SUM () or COUNT () are used
8. There are subqueries in the select list
9. There is no base table because it is possible to refer to a pure value, such as CREATE View VA as select 2.

The above limitations are due to the fact that after they are used, the structure of the view and the organization of the base table are inconsistent and cannot correspond to the base table one by one and cannot be used as an updatable view.

3. Delete, view view

You can delete multiple views at once.

DROP VIEW [IF EXISTS] view_name [, view_name] ...

The statement does not exist in the mysql/mariadb show view status . You can use the show table status state information of tables and views to show tables display the tables and views in the database.

SHOW TABLE STATUS LIKE  ‘v_city‘;

To view a VIEW definition statement:

show create view view_name;

You can also view related information from the Information_schema.views table, but be aware that the field in the Views table is called table_name instead of view_name. As follows:

select * from information_schema.views where table_name=‘view_name‘;

4. Check for invalid views

When creating the view, it is required that the base table already exists, otherwise it will be an error. However, when the view is created successfully, the base table of the view may be deleted, or the Reference field in the base table will be updated. The view is already an invalid view.

How do I detect these invalid views?

You can find out what views are in Information.schema before using the Check table statement to detect.

For example:

check table my_view,my_view2

The following are invalid view check results:

MariaDB [test]> Check Table my_view\g************************** * 1. Row * **************************Table:test.my_viewOp:checkMsg_type:ErrorMsg_text:Table ' test.t ' doesn ' t exist************************** * 2. Row * **************************Table:test.my_viewOp:checkMsg_type:errormsg_text:view ' Test.my_View ' references invalid table (s) or column (s) or function (s) or definer/invoker of view lack rights to use them************************** * 3. Row * **************************Table:test.my_viewOp:checkMSG_TYPE:ERRORMSG_TEXT:CORRUPT3 rows in Set (0.000 sec)

Back to Linux series article outline: http://www.cnblogs.com/f-ck-need-u/p/7048359.html
Back to Site Architecture series article outline: http://www.cnblogs.com/f-ck-need-u/p/7576137.html
Back to Database series article outline: http://www.cnblogs.com/f-ck-need-u/p/7586194.html
Reprint Please specify source: http://www.cnblogs.com/f-ck-need-u/p/8870908.html

Note: If you think this article is not bad please click on the lower right corner of the recommendation, your support can inspire the author more enthusiasm for writing, thank you very much!

MYSQL/MARIADB View