分享到

介绍

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

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

审查表结构

在使用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_namelast_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');

在PostgreSQL中插入前检查记录是否存在的一种方法是使用EXISTS子查询表达式

EXISTS条件与子查询结合使用,用于检查您要查找的数据。如果子查询返回至少一行,则认为条件满足。如果没有返回行,则记录尚不存在。

基本语法如下

WHERE EXISTS ( subquery );

PostgreSQL中有几种删除重复行的方法。您可以使用DELETE USING语句来检查两行是否具有相同的值,然后删除重复项。

此外,您可以使用子查询删除重复项,或者通过使用临时表并按照以下步骤操作:

  1. 创建一个新表,其结构与应删除重复行的表相同。
  2. 将源表中的不同行插入到临时表中。
  3. 删除源表。
  4. 将临时表重命名为源表的名称。

您可以使用包含EXISTSWHERE子句的DELETE语句来删除PostgreSQL中存在的记录。EXISTS子句需要一个子查询。

基本语法如下

DELETE FROM table_name
WHERE EXISTS ( subquery );

PostgreSQL只允许在其SELECT语句中使用LIMIT子句。因此,要在DELETE语句中使用它,您必须包含一个SELECT

语法可能如下所示

DELETE FROM table_name
WHERE field_name IN (
SELECT field_name FROM table_name LIMIT 1);
关于作者
Justin Ellingwood

Justin Ellingwood

Justin自2013年以来一直撰写有关数据库、Linux、基础设施和开发者工具的文章。他目前与妻子和两只兔子住在柏林。他通常不需要以第三人称写作,这对所有相关方来说都是一种解脱。
© . All rights reserved.