Sql 简明教程

SQL - UPDATE View

SQL UPDATE View Statement

视图是数据库对象，它可以包含现有表中的行（全部或选定的行）。它可以从一个或多个表创建，具体取决于创建视图时提供的 SQL 查询。

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.

与 CREATE VIEW 和 DROP VIEW 不同，没有直接的语句来更新现有视图的记录。我们可以使用 SQL UPDATE 语句来修改表或视图中的现有记录。

Unlike CREATE VIEW and DROP VIEW there is no direct statement to update the records of an existing view. We can use the SQL UPDATE Statement to modify the existing records in a table or a view.

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

您可以使用 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 using the following 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)
);

现在，使用如下所示的 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 );

以下查询基于上述创建的表格创建一个视图 −

Following query creates a view based on the above created table −

CREATE VIEW CUSTOMERS_VIEW AS SELECT * FROM CUSTOMERS;

您可以使用下所示出的 SELECT 查询验证视图的内容：

You can verify the contents of a view using the SELECT query as shown below −

SELECT * FROM CUSTOMERS_VIEW;

将按如下方式显示视图：

The view will be displayed as follows −

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

以下查询将上述创建的 CUSTOMERS_VIEW 中 Ramesh 的年龄更新为 35 岁：

Following query updates the age of Ramesh to 35 in the above created CUSTOMERS_VIEW −

UPDATE CUSTOMERS_VIEW
SET AGE = 35 WHERE name = 'Ramesh';

Verification

您可以使用 SELECT 语句按如下方式验证 CUSTOMERS_VIEW 的内容：

You can verify the contents of the CUSTOMERS_VIEW using the SELECT statement as follows −

SELECT * FROM CUSTOMERS_VIEW WHERE NAME ='Ramesh';

结果视图将有以下记录：

The resultant view would have the following record(s) −

ID

NAME

AGE

ADDRESS

SALARY

1

Ramesh

35

Ahmedabad

2000.00

Example

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

The following query will update the ADDRESS of a customer whose ID is 6 in the CUSTOMERS_VIEW.

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

Output

该查询生成以下输出：

The query produces the following output −

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

Verification

如果使用 SELECT 语句获取 ID 值为 6 的记录，如下所示：

If you retrieve the record with ID value 6 using the SELECT statement as −

SELECT * FROM CUSTOMERS_VIEW WHERE ID=6;

返回的记录为：

The record returned would be −

ID

NAME

AGE

ADDRESS

SALARY

6

Komal

22

Hyderabad

4500.00

Updating Multiple Rows and Columns

还可以使用 UPDATE 语句更新视图/表中的多行和多列。在更新多行时，请在 WHERE 子句中指定条件，以便仅所需的行列满足该条件。

Using UPDATE statement, multiple rows and columns in a view/table can also be updated. While updating multiple rows, specify the condition in a WHERE clause such that only required rows would satisfy it.

Example

以下查询更新了 ID 值为 3 的记录的 CUSTOMERS_VIEW 中的 NAME 和 AGE 列值。

Following query updates the NAME and AGE column values in the CUSTOMERS_VIEW of the record with ID value 3.

UPDATE CUSTOMERS_VIEW
SET NAME = 'Kaushik Ramanujan', AGE = 24
WHERE ID = 3;

Output

该查询生成以下输出：

The query produces the following output −

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

Verification

可以使用以下查询验证记录是否已更新：

You can verify whether the record is updated or not, using the following query −

SELECT * FROM CUSTOMERS_VIEW WHERE ID = 3;

返回的记录为：

The record returned would be −

ID

NAME

AGE

ADDRESS

SALARY

3

Kaushik Ramanujan

24

Kota

2000.00

Example

但是，如果你想修改/更新 CUSTOMERS_VIEW 中所有记录的年龄值，则无需使用 WHERE 子句。

But if you want to modify/update the age values of all the records in the CUSTOMERS_VIEW, there is no need to use the WHERE clause.

UPDATE CUSTOMERS_VIEW SET AGE = AGE+6;

Output

此查询生成以下输出：

This query produces the following output −

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

Verification

要验证 CUSTOMERS_VIEW 的记录是否已修改，请使用以下 SELECT 查询：

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

SELECT * FROM CUSTOMERS_VIEW;

结果 CUSTOMERS_VIEW 将具有以下记录：

The resultant CUSTOMERS_VIEW would have the following records −

ID

NAME

AGE

1

Ramesh

41

2

Khilan

31

3

Kaushik Ramanujan

30

4

Chaitali

31

5

Hardik

33

6

Komal

28

7

Muffy

30