介绍
添加和删除表中的记录是数据库执行的最常见操作之一。添加数据涉及指定要添加值的表和列名称,以及要输入到每个字段中的值。删除记录涉及识别正确的行或多行并将其从表中删除。
在本指南中,我们将介绍如何在PostgreSQL中使用SQL INSERT
和DELETE
命令。这包括基本语法、如何返回有关已处理数据的信息,以及如何在一个语句中添加或删除多行。
审查表结构
在使用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_lengthFROM 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 | 45last_name | character varying | | NO | 45last_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)
您还可以使用Prisma Client通过发出创建查询来向表中添加数据。
从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_tableWHERE <condition>;
例如,要删除我们employee
表中所有first_name
设置为Abigail
的行,我们可以键入以下内容
DELETE FROM employeeWHERE first_name = 'Abigail';
DELETE 1
此处的返回值表示DELETE
命令已处理,并删除了单行。
要使用Prisma Client从表中删除数据,请使用删除查询。
从DELETE
语句返回数据
与INSERT
命令一样,您可以通过添加RETURNING
子句来返回受影响的行或已删除行中的特定列
DELETE FROM my_tableWHERE <condition>RETURNING *;
例如,我们可以通过返回此处已删除employee
的所有列来验证是否删除了正确的记录
DELETE FROM employeeWHERE 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 employeeWHERE employee_id in (3,4)RETURNING *;
employee_id | first_name | last_name | last_update-------------+------------+-----------+----------------------------3 | Delores | Muniz | 2020-08-19 21:17:06.9436084 | Simone | Kohler | 2020-08-19 21:19:19.298833(2 rows)DELETE 2
您甚至可以省略WHERE
子句来删除给定表中的所有行
DELETE FROM employeeRETURNING *;
employee_id | first_name | last_name | last_update-------------+------------+-----------+----------------------------2 | Sue | Berns | 2020-08-19 21:15:01.76226 | Tamal | Wayne | 2020-08-19 22:11:53.4085317 | Katie | Singh | 2020-08-19 22:11:53.4085318 | Filipe | Espinosa | 2020-08-19 22:11:53.408531(4 rows)DELETE 4
但请注意,使用DELETE
清空表中的数据不如TRUNCATE
命令高效,后者无需扫描表即可删除数据。
Prisma Client使用一个名为deleteMany的独立查询,一次性删除多行数据。
结论
在本文中,我们介绍了一些最重要的命令,用于控制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
语句来检查两行是否具有相同的值,然后删除重复项。
此外,您可以使用子查询删除重复项,或者通过使用临时表并按照以下步骤操作:
- 创建一个新表,其结构与应删除重复行的表相同。
- 将源表中的不同行插入到临时表中。
- 删除源表。
- 将临时表重命名为源表的名称。
您可以使用包含EXISTS
的WHERE
子句的DELETE
语句来删除PostgreSQL中存在的记录。EXISTS
子句需要一个子查询。
基本语法如下
DELETE FROM table_nameWHERE EXISTS ( subquery );
PostgreSQL只允许在其SELECT
语句中使用LIMIT
子句。因此,要在DELETE
语句中使用它,您必须包含一个SELECT
。
语法可能如下所示
DELETE FROM table_nameWHERE field_name IN (SELECT field_name FROM table_name LIMIT 1);