如何在PostgreSQL中插入和刪除資料

介紹

新增和刪除表中的記錄是資料庫執行的最常見操作之一。新增資料涉及指定要新增值的表和列名稱，以及要輸入到每個欄位中的值。刪除記錄涉及識別正確的行或多行並將其從表中刪除。

在本指南中，我們將介紹如何在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_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);