Sql 简明教程

SQL - UPDATE Query

The SQL UPDATE Statement

SQL UPDATE 语句用来修改表中的现有记录。此语句是数据操作语言 (DML) 的一部分,因为它仅修改表中的数据而不影响表的结构。

The SQL UPDATE Statement is used to modify the existing records in a table. This statement is a part of Data Manipulation Language (DML), as it only modifies the data present in a table without affecting the table’s structure.

要筛选需要修改的记录,可以在 UPDATE 语句中使用 WHERE 子句。通过使用 WHERE 子句,你可以更新单行或多行。

To filter records that needs to be modified, you can use a WHERE clause with UPDATE statement. Using a WHERE clause, you can either update a single row or multiple rows.

SQL UPDATE 语句在修改表中每一行的同时使用对它们的锁,且一旦修改了行,就会释放该锁。因此,它可以使用单个查询来修改单行或多行。

The SQL 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

带 WHERE 子句的 SQL UPDATE 语句的基本语法如下:

The basic syntax of the SQL UPDATE statement with a WHERE clause is as follows −

UPDATE table_name
SET column1 = value1, column2 = value2,..., columnN = valueN
WHERE [condition];

您可以使用 AND 或 OR 运算符组合 N 个条件。

You can combine N number of conditions using the AND or the OR operators.

Example

假设已使用如下所示的 CREATE TABLE 语句创建了一个名为 CUSTOMERS 的表:

Assume we have created a table named CUSTOMERS using the CREATE TABLE statement as shown below −

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)
);

现在,使用如下所示的 INSERT 语句向此表中插入值:

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS 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 );

该表将被创建为:

The table will be created as −

ID

NAME

AGE

ADDRESS

SALARY

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

以下查询将更新表中 ID 号为 6 的客户的 ADDRESS。

The following query will update the ADDRESS for a customer whose ID number is 6 in the table.

UPDATE CUSTOMERS SET ADDRESS = 'Pune' WHERE ID = 6;

Output

该查询生成以下输出:

The query produces the following output −

Query OK, 1 row affected (0.13 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

要验证表的记录是否已修改,请使用下面的 SELECT 查询:

To verify whether the records of the table are modified or not, use the following SELECT query below −

SELECT * FROM CUSTOMERS WHERE ID=6;

现在,CUSTOMERS 表将拥有以下记录:

Now, the CUSTOMERS table would have the following records −

ID

NAME

AGE

ADDRESS

SALARY

6

Komal

22

Pune

4500.00

Update Multiple ROWS and COLUMNS

使用 SQL UPDATE 语句,还可以更新表中的多行和多列。要更新多行,请在 WHERE 子句中指定条件,以使其仅满足必需的行。

Using SQL UPDATE statement, multiple rows and columns in a 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.

然而,要更新多列,请对需要更新的所有列设置新值。在这种情况下,使用 WHERE 子句会限定表的记录,而不用该子句会更改这些列中的所有值。

However, 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 表中的所有 AGE 和 SALARY 列值,则无需使用 WHERE 子句,因为 UPDATE 查询就已足够。以下查询将所有客户的年龄增加 5 岁,并且将 3000 添加到所有 salary 值:

If you want to modify all the AGE and the SALARY column values in the CUSTOMERS table, you do not need to use the WHERE clause as the UPDATE query would be enough. Following query increases the age of all the customers by 5 years and adds 3000 to all the salary values −

UPDATE CUSTOMERS SET AGE = AGE+5, SALARY = SALARY+3000;

Output

该查询生成以下输出:

The query produces the following output −

Query OK, 7 rows affected (0.12 sec)
Rows matched: 7  Changed: 7  Warnings: 0

Verification

要验证表的记录是否已修改,请使用下面的 SELECT 查询:

To verify whether the records of the table are modified or not, use the following SELECT query below −

SELECT * FROM CUSTOMERS;

现在,CUSTOMERS 表将拥有以下记录:

Now, CUSTOMERS table would have the following records −

ID

NAME

AGE

ADDRESS

SALARY

1

Ramesh

37

Ahmedabad

5000.00

2

Khilan

30

Delhi

4500.00

3

Kaushik

28

Kota

5000.00

4

Chaitali

30

Mumbai

9500.00

5

Hardik

32

Bhopal

11500.00

6

Komal

27

Pune

7500.00

7

Muffy

29

Indore

13000.00

Example

但是,如果你想修改 CUSTOMERS 表中所选记录的 ADDRESS 和 SALARY 列,你需要指定一个条件来使用 WHERE 子句筛选待修改记录,如下面的查询所示:

But, if you want to modify the ADDRESS and the SALARY columns of selected records in the CUSTOMERS table, you need to specify a condition to filter the records to be modified, using the WHERE clause, as shown in the following query −

UPDATE CUSTOMERS
SET ADDRESS = 'Pune', SALARY = 1000.00
WHERE NAME = 'Ramesh';

Output

此查询生成以下输出:

This query produces the following output −

Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

要验证表的记录是否已修改,请使用下面的 SELECT 查询:

To verify whether the records of the table are modified or not, use the following SELECT query below −

SELECT * FROM CUSTOMERS WHERE NAME = 'Ramesh';

现在,CUSTOMERS 表将拥有以下记录:

Now, CUSTOMERS table would have the following records −

ID

NAME

AGE

ADDRESS

SALARY

1

Ramesh

37

Pune

1000.00