Mysql 简明教程
MySQL - Update View
MySQL UPDATE 语句用于各种数据库对象以更新其中的现有数据。这是一条 DML（数据操作语言）命令。
The MySQL UPDATE statement is used on various database objects to update the existing data in them. This is a DML (Data Manipulation language) command.
使用 UPDATE 语句时，我们需要小心，因为它可以修改对象中的所有记录，除非事先进行选择。为了避免丢失或重新插入正确的数据，我们使用子句来筛选需要更新的记录。这样，我们可以选择性地更新一个或多个行。
We need to be careful while using the UPDATE statement as it can modify all the records in an object, if not selected beforehand. To avoid losing or re-inserting correct data, we use clauses to filter the records that need to be updated. This way, we can update either a single row or multiple rows selectively.
MySQL UPDATE View Statement
在 MySQL 中，视图是一个数据库对象，它可以包含来自现有表（全部或选定的行）。一个视图可以由一个或多个表创建，它取决于所提供的 SQL 查询来创建视图。
In MySQL, a view is a database object that can contain rows (all or selected) from an existing table. It can be created from one or many tables which depends on the provided SQL query to create a view.
没有直接的语句可以更新 MySQL 视图。我们使用 UPDATE 语句修改视图中的所有记录或选定记录。结果也会反应回原始表。
There is no direct statement to update a MySQL view. We use the UPDATE statement to modify all or selective records in a view. The results are reflected back in the original table as well.
Syntax
带有 WHERE 子句的 UPDATE 查询的基本语法如下：
The basic syntax of the UPDATE query with a WHERE clause is as follows −
UPDATE view_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];Note: 我们可以使用 AND 或 OR 运算符组合任意数量的条件。
Note: We can combine N number of conditions using the AND or the OR operators.
Example
首先，让我们使用以下查询创建一个名为 CUSTOMERS 的表 -
First of all, let us create a table with the name CUSTOMERS using the following query −
CREATE TABLE CUSTOMERS(
ID int NOT NULL,
NAME varchar(20) NOT NULL,
AGE int NOT NULL,
ADDRESS varchar(25),
SALARY decimal(18, 2),
PRIMARY KEY (ID)
);现在，我们使用如下 INSERT 语句向此表插入一些记录：
Now, we are inserting some records into this table using the INSERT statement as follows −
INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', '32', 'Ahmedabad', 2000),
(2, 'Khilan', '25', 'Delhi', 1500),
(3, 'Kaushik', '23', 'Kota', 2500),
(4, 'Chaitali', '26', 'Mumbai', 6500),
(5, 'Hardik','27', 'Bhopal', 8500),
(6, 'Komal', '22', 'MP', 9000),
(7, 'Muffy', '24', 'Indore', 5500);Creating a view −
Creating a view −
以下查询基于上述创建的表格创建一个视图 −
Following query creates a view based on the above created table −
CREATE VIEW CUSTOMERS_VIEW AS SELECT * FROM CUSTOMERS;使用以下查询，我们可以验证视图的内容：
Using the following query, we can verify the contents of a view −
SELECT * FROM CUSTOMERS_VIEW;将按如下方式显示视图：
The view will be displayed as follows −
Updating this view −
Updating this view −
我们现在尝试通过创建的视图，使用以下查询将原始的 CUSTOMERS 表中 Ramesh 的年龄更新为 35：
Now, through the view we created, we are trying to update the age of Ramesh to 35 in the original CUSTOMERS table, using the following query −
UPDATE CUSTOMERS_VIEW SET AGE = 35 WHERE name = 'Ramesh';这最终将更新基本表 CUSTOMERS，而该表本身也会反映在视图中。
This will ultimately update the base table CUSTOMERS and the same would reflect in the view itself.
Updating Multiple Rows and Columns
在 MySQL 中，我们可以使用 UPDATE 语句更新表的多个行和列。若要修改多行，请在 WHERE 子句中指定条件，以便只有必需的行满足条件。
In MySQL, we can update multiple rows and columns of a table using the UPDATE statement. To update multiple rows, specify the condition in a WHERE clause such that only the required rows would satisfy it.
若要修改多列，请针对需要更新的所有列设置新值。在这种情况下，使用 WHERE 子句将缩小表的记录范围，而不使用该子句将改变所有这些列中的值。
To update multiple columns, set the new values to all the columns that need to be updated. In this case, using the WHERE clause would narrow down the records of the table and not using the clause would change all the values in these columns.
Syntax
以下是如何更新多行和多列的语法：
Following is the syntax to update multiple rows and columns −
UPDATE table_name
SET column_name1 = new_value, column_name2 = new_value...
WHERE condition(s)Example
在以下查询中，我们尝试修改 CUSTOMERS 表中 WHERE ID = 3 的 NAME 和 AGE 列的值：
In the following query, we are trying to modify the NAME and AGE column values in the CUSTOMERS table for WHERE ID = 3:
UPDATE CUSTOMERS_VIEW
SET NAME = 'Kaushik', AGE = 24
WHERE ID = 3;Verification
使用 SELECT 查询，我们可以检索包含以下记录的 CUSTOMERS 表：
Using the SELECT query, we can retrieve the CUSTOMERS table with following records −
Example
如果我们要修改 CUSTOMERS 表中 AGE 列的所有记录，则可以使用以下查询：
If we want to modify all the records of AGE column in the CUSTOMERS table, we can use the following query −
UPDATE CUSTOMERS_VIEW SET AGE = 24;