Mysql 简明教程
MySQL - Update Query
The MySQL UPDATE Statement
MySQL UPDATE 查询用于修改表中的现有记录。此语句是 SQL 中数据操作语言的一部分，因为它只修改表中存在的数据，而不影响表的结构。
The MySQL UPDATE Query is used to modify the existing records in a table. This statement is a part of Data Manipulation Language in SQL, as it only modifies the data present in a table without affecting the table’s structure.
因为它只与表的数据进行交互，所以必须谨慎使用 UPDATE 语句。如果没有预先选择要修改的行，则表中的所有行都将受到影响，而正确的表数据将丢失或需要重新插入。
Since it only interacts with the data of a table, the UPDATE statement needs to used cautiously. If the rows to be modified aren’t selected beforehand, all the rows in the table will be affected and the correct table data is either lost or needs to be reinserted.
因此，为了筛选需要修改的记录，MySQL 始终提供一个 WHERE 子句。使用 WHERE 子句，您可以更新一行或多行。
Therefore, to filter records that needs to be modified, MySQL always provides a WHERE clause. Using a WHERE clause, you can either update a single row or multiple rows.
UPDATE 语句在修改表中每一行时都会使用锁，一旦行被修改，锁就会被释放。因此，它可以用单个查询一次更改一行或多行。
The UPDATE statement makes use of locks on each row while modifying them in a table, and once the row is modified, the lock is released. Therefore, it can either make changes to a single row or multiple rows with a single query.
Syntax
以下是用于修改 MySQL 表中数据的 UPDATE 命令的 SQL 语法：
Following is the SQL syntax of the UPDATE command to modify the data in the MySQL table −
UPDATE table_name SET field1 = new-value1, field2 = new-value2
[WHERE Clause]-
You can update one or more field altogether.
-
You can specify any condition using the WHERE clause.
-
You can update the values in a single table at a time.
当您想要更新表中的选定行时，WHERE 子句非常有用。
The WHERE clause is very useful when you want to update the selected rows in a table.
Updating Data from the Command Prompt
这将使用带有 WHERE 子句的 SQL UPDATE 命令更新 MySQL 表中选定的数据。
This will use the SQL UPDATE command with the WHERE clause to update the selected data in an MySQL table.
Example
首先，让我们使用以下 CREATE 查询创建一个名为 CUSTOMERS 的表：
First of all, let us create a table named CUSTOMERS using the following CREATE query −
CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);下面的查询将 7 条记录插入到上述已创建的表中：
The below query inserts 7 records in to the above created table −
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );执行以下查询以检索 CUSTOMERS 表中存在的所有记录 −
Execute the following query to retrieve all the records present in the CUSTOMERS table −
Select * from CUSTOMERS;以下为 CUSTOMERS 表 −
Following is the CUSTOMERS table −
在这里，我们使用 SQL UPDATE 查询更新 CUSTOMERS 表中的 NAME 字段。它将 ID 等于 6 的行的名称设置为“Nikhilesh”。
Here, we are using the SQL UPDATE query to update the NAME field in the CUSTOMERS table. It sets the name to 'Nikhilesh' for the row where the 'ID' is equal to 6.
UPDATE CUSTOMERS
SET NAME = 'Nikhilesh'
WHERE ID = 6;Updating Multiple Records from the Command Prompt
使用 UPDATE 语句，还可以更新 MySQL 表中的多行和多列。要更新多行，请在 WHERE 子句中指定条件，以便仅必需行满足该条件。因此，仅更新那些记录中的值。
Using UPDATE statement, multiple rows and columns in a MySQL table can also be updated. To update multiple rows, specify the condition in a WHERE clause such that only the required rows would satisfy it. Thus, only updating the values in those records.
Example
现在，让我们使用以下查询更新先前创建的 CUSTOMERS 表中的多条记录：
Now, let us update multiple records in the previously created CUSTOMERS table using the following query −
UPDATE CUSTOMERS
SET ADDRESS = 'Vishakapatnam'
WHERE ID = 6 OR ID = 3;Updating a table Using a Client Program
除了使用 MySQL 查询更新表中的记录外，我们还可以使用客户端程序对表执行 UPDATE 操作。
In addition to update records in a table using the MySQL query, we can also perform the UPDATE operation on a table using a client program.