PgSQL——学习笔记六：UPDATE 语句：更新表中数据 & DELETE 语句：删除表中数据_pgsql update

PostgreSQL UPDATE 语句：更新表中数据

如果我们要更新在 PostgreSQL 数据库中的数据，我们可以用 UPDATE 来操作。

//UPDATE 语句修改数据的通用 SQL 语法：
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
1
2
3
4

1、我们可以同时更新一个或者多个字段。2、还可以在 WHERE 子句中指定任何条件。
实例：

mydb=# select * from COMPANY;
 id | name  | age |                      address                       | salary | join_date
----+-------+-----+----------------------------------------------------+--------+------------
  1 | paul  |  32 | California                                         |   2000 | 2001-07-03
  2 | Allen |  24 | Texas                                              |        | 2007-12-13
  3 | Teddy |  23 | Norway                                             |  20000 |
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2007-12-13
  5 | David |  27 | Texas                                              |  85000 | 2007-12-13
(5 行记录)

//将更新 COMPANY 表中 id 为 3 的 salary 字段值：
mydb=# UPDATE COMPANY SET SALARY = 15000 WHERE ID = 3;
UPDATE 1

mydb=# select * from COMPANY;
 id | name  | age |                      address                       | salary | join_date
----+-------+-----+----------------------------------------------------+--------+------------
  1 | paul  |  32 | California                                         |   2000 | 2001-07-03
  2 | Allen |  24 | Texas                                              |        | 2007-12-13
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2007-12-13
  5 | David |  27 | Texas                                              |  85000 | 2007-12-13
  3 | Teddy |  23 | Norway                                             |  15000 |
(5 行记录)

//将同时更新 salary 字段和 address 字段的值：
mydb=# UPDATE COMPANY SET ADDRESS = 'Texas', SALARY=20000;
UPDATE 5

mydb=# select * from COMPANY;
 id | name  | age |                      address                       | salary | join_date
----+-------+-----+----------------------------------------------------+--------+------------
  1 | paul  |  32 | Texas                                              |  20000 | 2001-07-03
  2 | Allen |  24 | Texas                                              |  20000 | 2007-12-13
  4 | Mark  |  25 | Texas                                              |  20000 | 2007-12-13
  5 | David |  27 | Texas                                              |  20000 | 2007-12-13
  3 | Teddy |  23 | Texas                                              |  20000 |
(5 行记录)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

PostgreSQL DELETE 语句：删除表中数据

你可以使用 DELETE 语句来删除 PostgreSQL 表中的数据。

 DELETE 语句删除数据的通用语法：
DELETE FROM table_name WHERE [condition];
1
2

如果没有指定 WHERE 子句，PostgreSQL 表中的所有记录将被删除。
一般我们需要在 WHERE 子句中指定条件来删除对应的记录，条件语句可以使用 AND 或 OR 运算符来指定一个或多个。
实例：

//将删除 ID 为 2 的数据：
mydb=# DELETE FROM COMPANY WHERE ID = 2;
DELETE 1
mydb=# select * from COMPANY;
 id | name  | age |                      address                       | salary | join_date
----+-------+-----+----------------------------------------------------+--------+------------
  1 | paul  |  32 | Texas                                              |  20000 | 2001-07-03
  4 | Mark  |  25 | Texas                                              |  20000 | 2007-12-13
  5 | David |  27 | Texas                                              |  20000 | 2007-12-13
  3 | Teddy |  23 | Texas                                              |  20000 |
(4 行记录)

//将删除整张 COMPANY 表：
mydb=# DELETE FROM COMPANY;
DELETE 4
mydb=# select * from COMPANY;
 id | name | age | address | salary | join_date
----+------+-----+---------+--------+-----------
(0 行记录)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19