如何在 PostgreSQL 中插入和删除数据

介绍

添加和删除表格中的记录是数据库执行的最常见操作之一。添加数据需要指定要添加值的表格和列名称，以及要输入到每个字段中的值。删除记录需要识别正确的行或多行，并将其从表格中删除。

在本指南中，我们将介绍如何使用 SQL INSERT 和 DELETE 命令与 PostgreSQL 一起使用。这包括基本语法、如何返回有关已处理数据的 data 信息，以及如何在一个语句中添加或删除多行。

回顾表格结构

在使用 INSERT 命令之前，你必须了解表格结构，以便你可以满足表格的列、数据类型和约束所要求的条件。根据你的数据库客户端，有几种不同的方法可以做到这一点。

如果你使用的是 psql 命令行客户端，最简单的方法是使用该工具内置的 \d+ 元命令。

例如，要查找名为 employee 的表格结构，可以键入以下内容

\d+ employee

                                                                Table "public.employee"
   Column    | Type                        | Collation | Nullable | Default                                       | Storage  | Stats target | Description
-------------+-----------------------------+-----------+----------+-----------------------------------------------+----------+--------------+-------------
 employee_id | integer                     |           | not null | nextval('employee_employee_id_seq'::regclass) | plain    |              |
 first_name  | character varying(45)       |           | not null |                                               | extended |              |
 last_name   | character varying(45)       |           | not null |                                               | extended |              |
 last_update | timestamp without time zone |           | not null | now()                                         | plain    |              |
Indexes:
    "employee_pkey" PRIMARY KEY, btree (employee_id)
    "idx_employee_last_name" btree (last_name)
Triggers:
    last_updated BEFORE UPDATE ON employee FOR EACH ROW EXECUTE FUNCTION last_updated()
Access method: heap

输出显示表格的列名、数据类型和默认值等。

\d+ 元命令仅在使用 psql 客户端时可用，因此如果你使用的是其他客户端，可能需要直接查询表格信息。可以使用以下查询获取大多数相关信息

SELECT column_name, data_type, column_default, is_nullable, character_maximum_length
FROM information_schema.columns WHERE table_name ='employee';

 column_name | data_type                   | column_default                                | is_nullable | character_maximum_length
-------------+-----------------------------+-----------------------------------------------+-------------+--------------------------
 employee_id | integer                     | nextval('employee_employee_id_seq'::regclass) | NO          |
 first_name  | character varying           |                                               | NO          | 45
 last_name   | character varying           |                                               | NO          | 45
 last_update | timestamp without time zone | now()                                         | NO          |
(4 rows)

这些应该让你很好地了解表格结构，以便你可以正确插入值。

使用 INSERT 将新记录添加到表格

SQL INSERT 命令用于将数据行添加到现有表格。了解表格结构后，就可以构建一个命令，该命令将表格的列与要为新记录插入的相应值匹配。

该命令的基本语法如下所示

INSERT INTO my_table(column1, column2)
VALUES ('value1', 'value2');

列列表中的列与值列表中提供的值直接对应。

默认情况下，INSERT 命令返回对象 ID（通常为 0）和成功插入的行数

INSERT 0 1

例如，要将新员工插入到上面列出的 employee 表格中，可以键入以下内容

INSERT INTO employee(first_name, last_name)
VALUES ('Bob', 'Smith');

INSERT 0 1

在这里，我们为 first_name 和 last_name 列提供了值，而将其他列留空，以便由它们的默认值填充。如果查询表格，你会看到已添加新记录

SELECT * FROM employee;

 employee_id | first_name | last_name |        last_update
-------------+------------+-----------+----------------------------
           1 | Bob        | Smith     | 2020-08-19 21:07:00.952454
(1 row)

从 INSERT 语句中返回数据

如果你想要有关添加到表格的数据的更多信息，可以在语句末尾包含 RETURNING 子句。 RETURNING 子句指定要显示的刚刚插入的记录的列。

例如，要显示刚刚插入的记录的所有列，可以键入以下内容

INSERT INTO my_table(column_name, column_name_2)
VALUES ('value', 'value2')
RETURNING *;

 column_name | column_name_2
-------------+---------------
 value       | value2
(1 row)

INSERT 0 1

使用 employee 表格，这将看起来像这样

INSERT INTO employee(first_name, last_name)
VALUES ('Sue', 'Berns')
RETURNING *;

 employee_id | first_name | last_name |       last_update
-------------+------------+-----------+--------------------------
           2 | Sue        | Berns     | 2020-08-19 21:15:01.7622
(1 row)

INSERT 0 1

你也可以选择仅从插入中返回特定的列。例如，在这里，我们只对新员工的 ID 感兴趣

INSERT INTO employee(first_name, last_name)
VALUES ('Delores', 'Muniz')
RETURNING employee_id;

 employee_id
-------------
           3
(1 row)

INSERT 0 1

与往常一样，你也可以使用列别名来更改输出中的列名

INSERT INTO employee(first_name, last_name)
VALUES ('Simone', 'Kohler')
RETURNING employee_id AS "Employee ID";

 Employee ID
-------------
           4
(1 row)

INSERT 0 1

使用 INSERT 一次添加多行

与一次插入一条记录相比，一次性插入多行记录更加省时高效。PostgreSQL 允许您指定要添加到同一表的多个行。每个新行都封装在括号中，每个括号组之间用逗号隔开。

多记录插入的基本语法如下所示

INSERT INTO my_table(column_name, column_name_2)
VALUES
    ('value', 'value2'),
    ('value3', 'value4'),
    ('value5', 'value6');

对于我们一直在引用的 employee 表，您可以通过键入以下命令在一个语句中添加四个新员工

INSERT INTO employee(first_name, last_name)
VALUES
    ('Abigail', 'Spencer'),
    ('Tamal', 'Wayne'),
    ('Katie', 'Singh'),
    ('Felipe', 'Espinosa');

INSERT 0 4

使用 DELETE 从表中删除行

SQL DELETE 命令用于从表中删除行，作为 INSERT 的补充操作。为了从表中删除行，您必须在 WHERE 子句中提供匹配条件，以识别您要定位的行。

基本语法如下所示

DELETE FROM my_table
WHERE <condition>;

例如，要删除 employee 表中所有 first_name 设置为 Abigail 的行，您可以键入以下内容

DELETE FROM employee
WHERE first_name = 'Abigail';

DELETE 1

此处的返回值表明 DELETE 命令已处理，并删除了一行。

从 DELETE 语句中返回数据

与 INSERT 命令一样，您可以通过添加 RETURNING 子句来返回受影响的行或从已删除的行中返回特定的列

DELETE FROM my_table
WHERE <condition>
RETURNING *;

例如，我们可以通过返回已删除的 employee 的所有列来验证是否删除了正确的记录

DELETE FROM employee
WHERE last_name = 'Smith'
RETURNING *;

 employee_id | first_name | last_name |        last_update
-------------+------------+-----------+----------------------------
           1 | Bob        | Smith     | 2020-08-19 21:07:00.952454
(1 row)

DELETE 1

使用 DELETE 一次删除多行

您可以通过操作 WHERE 子句中指定的选取条件，使用 DELETE 一次删除多个项目。

例如，要根据 ID 删除多行，您可以键入类似以下内容

DELETE FROM employee
WHERE employee_id in (3,4)
RETURNING *;

 employee_id | first_name | last_name |        last_update
-------------+------------+-----------+----------------------------
           3 | Delores    | Muniz     | 2020-08-19 21:17:06.943608
           4 | Simone     | Kohler    | 2020-08-19 21:19:19.298833
(2 rows)

DELETE 2

您甚至可以省略 WHERE 子句以从给定表中删除所有行

DELETE FROM employee
RETURNING *;

 employee_id | first_name | last_name |        last_update
-------------+------------+-----------+----------------------------
           2 | Sue        | Berns     | 2020-08-19 21:15:01.7622
           6 | Tamal      | Wayne     | 2020-08-19 22:11:53.408531
           7 | Katie      | Singh     | 2020-08-19 22:11:53.408531
           8 | Filipe     | Espinosa  | 2020-08-19 22:11:53.408531
(4 rows)

DELETE 4

但是，请注意，使用 DELETE 清空表中的数据 不如 TRUNCATE 命令高效，后者可以在不扫描表的情况下删除数据。

结论

在本文中，我们介绍了一些用于控制 PostgreSQL 表中数据的最重要的命令。 INSERT 命令可用于向表中添加新数据，而 DELETE 命令指定要删除哪些行。这两个命令都能够返回它们影响的行，并且可以同时操作多行。

这两个命令是管理增加或减少表中包含的记录数量的主要机制。了解它们的语法以及它们与其他子句组合的方式，将使您能够根据需要填充和清理您的表。

常见问题解答

INSERT INTO my_table(column_name, column_name_2)
VALUES
    ('value', 'value2'),
    ('value3', 'value4'),
    ('value5', 'value6');

INSERT INTO employee(first_name, last_name)
VALUES
    ('Abigail', 'Spencer'),
    ('Tamal', 'Wayne'),
    ('Katie', 'Singh'),
    ('Felipe', 'Espinosa');

WHERE EXISTS ( subquery );

DELETE FROM table_name
    WHERE EXISTS ( subquery );

DELETE FROM table_name
WHERE field_name IN (
    SELECT field_name FROM table_name LIMIT 1);