我正在创建一个视图来向用户显示他/她的数据，但我也希望用户能够在这些视图中的某些字段中进行更改。 视图中所做的更改是否也反映在基表中？
此外，我能够更新由多个基表组成的视图吗？
I am creating a view to show the user his/her data, but I also want the user to be able to make changes in some of the fields in those views. Are the changes made in a view reflected in the base table as well?
Also, would I be able to update a view that is made up of more than one base table?
最满意答案
如可更新和可插入视图中所述 ：
一些视图是可更新的。 也就是说，您可以在UPDATE ， DELETE或INSERT等语句中使用它们来更新基础表的内容。 要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。 还有一些其他构造使视图不可更新。 更具体地说，如果视图包含以下任何内容，则视图不可更新：
聚合函数（ SUM() ， MIN() ， MAX() ， COUNT()等）
DISTINCT
GROUP BY
HAVING
UNION或UNION ALL
选择列表中的子查询
某些连接（请参阅本节后面的其他连接讨论）
FROM子句中的不可更新视图
WHERE子句中的子查询，它引用FROM子句中的表
仅指文字值（在这种情况下，没有要更新的基础表）
使用ALGORITHM = TEMPTABLE （使用临时表总是使视图不可更新）
对基表的任何列的多个引用。
[ deletia ]
假设可以使用MERGE算法处理多表视图，有时可以更新多表视图。 为此，视图必须使用内部联接（不是外部联接或UNION ）。 此外，只能更新视图定义中的单个表，因此SET子句必须只列出视图中其中一个表的列。 使用UNION ALL视图是不允许的，即使它们理论上可以更新，因为实现使用临时表来处理它们。
As documented under Updatable and Insertable 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 underlying table. For a view to be updatable, there must be a one-to-one relationship between the rows in the view and the rows in the underlying table. There are also certain other constructs that make a view nonupdatable. To be more specific, a view is not updatable if it contains any of the following:
Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)
DISTINCT
GROUP BY
HAVING
UNION or UNION ALL
Subquery in the select list
Certain joins (see additional join discussion later in this section)
Nonupdatable view in the FROM clause
A subquery in the WHERE clause that refers to a table in the FROM clause
Refers only to literal values (in this case, there is no underlying table to update)
Uses ALGORITHM = TEMPTABLE (use of a temporary table always makes a view nonupdatable)
Multiple references to any column of a base table.
[ deletia ]
It is sometimes possible for a multiple-table view to be updatable, assuming that it can be processed with the MERGE algorithm. For this to work, the view must use an inner join (not an outer join or a UNION). Also, only a single table in the view definition can be updated, so the SET clause must name only columns from one of the tables in the view. Views that use UNION ALL are not permitted even though they might be theoretically updatable, because the implementation uses temporary tables to process them.
更多推荐
发布评论