MySQL View update

Yesterday in writing the 2019 autumn strokes of the group of questions about the question of whether the view can be updated, suddenly feel that the issue of review before the focus of attention, but do not want to complete. Today, specially searched some information summarizes. This article mainly describes the update limitations of the view, for more knowledge of the view, refer to: MySQL View principle explained and use Daquan

1. Introduction to Views
   1. View is not a table, can not directly store data, is a virtual table, view and database table has a corresponding relationship, in some cases, we can use the view to manipulate the database table, so as to simplify the query operation (in general, the view is used for querying), Under certain conditions, you can use the view to update the data in the database tables, and later refer to the update of the view.
   2. In general, when creating a conditional view, add the "with CHECK OPTION" command

2. Update issues with views
   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. If the view contains any of the following structures, it is not updatable:

   1. Aggregate Functions (SUM (), MIN (), MAX (), COUNT (), and so on).
   2. DISTINCT
   3. GROUP by
   4. Having
   5. Union or UNION ALL
   6. Subqueries located in the select list
   7. Join
   8. Non-updatable view in FROM clause
   9. A subquery in the WHERE clause that refers to the table in the FROM clause.
   10. Only literal values are referenced (in that case, there is no base table to update).
   11. Algorithm = temptable (using temporary tables always makes the view non-updatable).

Attention:
Although the data can be updated in the view, there are a number of limitations. In general, it's a good idea to use a view as a virtual table for querying data instead of updating the data through a view. Because when you use a view to update data, you may fail to update data if you do not fully consider the limitations of updating data in the view.

with[cascaded| LOCAL] CHECK option can you decide whether the view can be updated?

1. The basic definitions of these two parameters are as follows:
   The local parameter indicates that the view is updated as long as the conditions defined by the view itself are met.
   The cascaded parameter indicates that all related views and table conditions need to be met when the view is updated. When not specified, the parameter is the default value.

2. For the WITH CHECK option usage, the following is summarized:
   1. The view only operates data that it can query, and the data that it cannot query, even if the base table has, can not be manipulated by the view.
   2. For update, with CHECK option, to ensure that the data is queried by the view after the update
   3. For delete, there is no same as check option
   4. For INSERT, with CHECK option, to ensure that the data is queried by the view after insert
   5. For views without a WHERE clause, using WITH CHECK option is redundant

This article refers to: MySQL view principle explained with use Daquan and previous view learning notes
If you want to reprint please specify the Source: https://www.cnblogs.com/zhuchenglin/p/9602569.html

MySQL View update