ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > 数据库开发技术 > 《PostgreSQL 开发指南》第 22 篇 DML 语句

《PostgreSQL 开发指南》第 22 篇 DML 语句

原创 数据库开发技术 作者：BUAA_dxy 时间：2023-08-18 20:39:43 0 删除 编辑

本篇介绍如何对表中的数据进行修改操作，包括插入数据的INSERT语句、更新数据的UPDATE语句、删除数据的DELETE语句，以及合并数据的INSERT ON CONFLICT语句。

我们首先创建创建两个示例表：

CREATE TABLE dept (
	department_id int NOT NULL,
	department_name varchar(30) NOT NULL,
CONSTRAINT dept_pkey PRIMARY KEY (department_id)
);

CREATE TABLE emp (
	employee_id int NOT NULL,
	first_name varchar(20) NULL,
	last_name varchar(25) NOT NULL,
	hire_date date not null default current_date,
	salary numeric(8,2) NULL,
	manager_id int NULL,
	department_id int NULL,
CONSTRAINT emp_pkey PRIMARY KEY (employee_id),
CONSTRAINT fk_emp_dept FOREIGN KEY (department_id) REFERENCES dept(department_id) ON DELETE CASCADE,
CONSTRAINT fk_emp_manager FOREIGN KEY (manager_id) REFERENCES emp(employee_id)
);

关于创建表的CREATE TABLE语句，可以参考第 06 篇 管理数据表。

插入数据

PostgreSQL 提供了INSERT语句，可以用于插入一行或者多行数据。

插入单行数据

INSERT语句的简单形式如下：

INSERT INTO table_name(column1, column2, ...)
VALUES (value1, value2, ...);

其中，value1 是 column1 的值，value2 是 column2 的值。例如：

INSERT INTO dept(department_id, department_name) VALUES ( 10, 'Administration');

select * from dept;
department_id|department_name|
-------------|---------------|
10|Administration |

如果VALUES列表为所有字段都指定了值，并且按照表的字段顺序出现，可以省略表名后的字段列表。因此，我们也可以使用以下插入语句：

INSERT INTO dept VALUES ( 20, 'Marketing');

select * from dept;
department_id|department_name|
-------------|---------------|
10|Administration |
20|Marketing      |

指定字段的值也可以使用 DEFAULT，表示使用定义字段时的默认值；如果没有指定默认值使用 NULL 值。

插入多行数据

PostgreSQL 中的INSERT语句支持一次插入多行数据，在VALUES之后使用逗号进行分隔。例如：

INSERT INTO emp
VALUES (200, 'Jennifer', 'Whalen', '2020-01-01', 4400.00, NULL, 10),
(201, 'Michael', 'Hartstein', '2020-02-02', 13000.00, NULL, 20),
(202, 'Pat', 'Fay', default, 6000.00, 201, 20);

select * from emp;
employee_id|first_name|last_name|hire_date |salary  |manager_id|department_id|
-----------|----------|---------|----------|--------|----------|-------------|
200|Jennifer  |Whalen   |2020-01-01| 4400.00|          |           10|
201|Michael   |Hartstein|2020-02-02|13000.00|          |           20|
202|Pat       |Fay      |2020-04-14| 6000.00|       201|           20|

以上语句一次增加了 3 名员工信息，日期可以使用字符形式的字面值（‘2020-01-01’），default 表示使用默认的当前日期。

复制数据

INSERT INTO SELECT语句可以将一个查询语句的结果插入表中。例如：

create table emp1 (like emp);

insert into emp1
select * from emp 
where department_id = 20;

select * from emp1;
employee_id|first_name|last_name|hire_date |salary  |manager_id|department_id|
-----------|----------|---------|----------|--------|----------|-------------|
201|Michael   |Hartstein|2020-02-02|13000.00|          |           20|
202|Pat       |Fay      |2020-04-14| 6000.00|       201|           20|

我们首先基于 emp 创建了一个新表 emp1，然后通过查询语句将 emp 中的部分数据复制到 emp1 中。

返回插入的数据

PostgreSQL 对 SQL进行了扩展，可以在INSERT语句之后使用RETURNING返回插入的数据值。例如：

insert into dept
values (30, 'Purchasing')
returning department_id;
department_id|
-------------|
30|

以上语句除了插入一条数据到 dept 表中，同时还返回了该数据的 department_id。

更新数据

单表更新

PostgreSQL 使用UPDATE语句更新表中已有的数据，基本的语法如下：

UPDATE table_name
SET column1 = value1,
       column2 = value2,
...
WHERE conditions;

其中，WHERE决定了需要更新的数据行，只有满足条件的数据才会更新；如果省略WHERE条件，将会更新表中的所有数据，需要谨慎使用。

以下语句将编号为 200 的员工从原部门调动到 Marketing，并且涨薪 1000：

update emp 
set salary = salary + 1000,
    department_id = 20
where employee_id = 200;

跨表更新

除了以上形式的更新语句之外，PostgreSQL 还支持通过关联其他表中的数据进行更新。以下语句利用 emp 中的数据更新 emp1 表：

update emp1
set salary = emp.salary,
    department_id = emp.department_id,
    manager_id = emp.manager_id 
from emp
where emp1.employee_id = emp.employee_id;

我们使用FROM子句访问 emp 中的数据，并且在WHERE子句中指定了两个表的关联条件。这种语句与多表连接查询（join）类似，有时候也称为多表连接更新（UPDATE JOIN）。

返回更新后的数据

PostgreSQL 同样对UPDATE语句进行了扩展，支持使用RETURNING返回更新后的数据值。例如：

update emp 
set salary = salary + 1000,
    department_id = 20
where employee_id = 200
returning first_name, last_name, salary;
first_name|last_name|salary |
----------|---------|-------|
Jennifer  |Whalen   |6400.00|

我们再次更新编号为 200 的员工的信息，并且返回了更新之后的记录。

删除数据

删除数据可以使用DELETE语句：

DELETE FROM table_name
WHERE conditions;

同样，只有满足WHERE条件的数据才会被删除；如果省略，将会删除表中所有的数据。

单表删除

以下语句用于删除 emp1 中员工编号为 201 的数据：

delete
from emp1
where employee_id = 201;

如果没有编号为 201 的数据，不会删除任何数据。

跨表删除

PostgreSQL 同样支持通过关联其他表进行数据删除。以下语句利用 emp 表删除 emp1 表中的数据：

delete
from emp1
using emp
where emp1.employee_id = emp.employee_id;

注意，跨表删除使用USING关键字引用其他的表，而不是JOIN。以上语句删除了 emp1 中员工编号存在于 emp 表中的数据，等价于以下子查询实现：

delete
from emp1
where emp1.employee_id in (select employee_id from emp);

返回被删除的数据

PostgreSQL 中的DELETE语句也可以使用RETURNING返回被删除的数据。例如：

-- 先插入一些数据
insert into emp1
select * from emp 
where department_id = 20;

delete
from emp1
returning *;
employee_id|first_name|last_name|hire_date |salary  |manager_id|department_id|
-----------|----------|---------|----------|--------|----------|-------------|
201|Michael   |Hartstein|2020-02-02|13000.00|          |           20|
202|Pat       |Fay      |2020-04-14| 6000.00|       201|           20|
200|Jennifer  |Whalen   |2020-01-01| 6400.00|          |           20|

我们先从 emp 复制了一些数据到 emp1 中，然后删除所有数据并且返回这些记录。

合并数据

MERGE语句

SQL 标准中定义了一个用于合并数据的 MERGE 语句，可以基于指定条件执行插入、更新或者删除操作。PostgreSQL 15 实现了 MERGE 语句，基本语法如下：

MERGE INTO target 
USING source 
ON join_condition
{ WHEN MATCHED THEN { UPDATE| DELETE | DO NOTHING } |
WHEN NOT MATCHED THEN { INSERT | DO NOTHING } };

其中 target 是合并操作的目标表；source 是合并数据的来源，可以是表名或者查询语句；ON 子句是判断源数据在目标表中是否存在的条件；WHEN MATCHED THEN 分支指定了数据匹配（已经存在）时执行的操作；WHEN NOT MATCHED THEN 分支指定了数据不存在时的操作。

我们创建一个示例表account：

CREATE TABLE account (
    id INTEGER PRIMARY KEY,
    balance NUMERIC NOT NULL,
status VARCHAR(1) NOT NULL CHECK (status IN ('Y', 'N'))
);

使用以下语句为 account 表新增一条记录：

MERGE INTO account a
USING (VALUES(1, 0, 'Y')) s(id, balance, status)
ON a.id = s.id
WHEN MATCHED THEN
UPDATE SET balance = s.balance, status = s.status
WHEN NOT MATCHED THEN 
INSERT (id, balance, status)
VALUES (s.id, s.balance, s.status);

SELECT * FROM account;
id|balance|status|
--|-------|------|
1|      0|Y     |

由于 id 等于 1 的记录不存在，以上语句将会执行 WHEN NOT MATCHED THEN 分支，插入一条新的记录。

接下来我们将插入源数据中的 balance 修改为 100，再次执行 MERGE 语句：

MERGE INTO account a
USING (VALUES(1, 100, 'Y')) s(id, balance, status)
ON a.id = s.id
WHEN MATCHED THEN
UPDATE SET balance = s.balance, status = s.status
WHEN NOT MATCHED THEN 
INSERT (id, balance, status)
VALUES (s.id, s.balance, s.status);

SELECT * FROM account;
id|balance|status|
--|-------|------|
1|    100|Y     |

以上语句将会执行 WHEN MATCHED THEN分 支，更新 account 表中 id 等于 1 的记录。

最后，我们在 MERGE 语句中增加一个分支，用于删除数据：

MERGE INTO account a
USING (VALUES(1, 100, 'N')) s(id, balance, status)
ON a.id = s.id
WHEN MATCHED AND s.status = 'N' THEN
DELETE 
WHEN MATCHED THEN
UPDATE SET balance = s.balance, status = s.status
WHEN NOT MATCHED THEN 
INSERT (id, balance, status)
VALUES (s.id, s.balance, s.status);

SELECT * FROM account;
id|balance|status|
--|-------|------|

语句中的 WHEN MATCHED AND s.status = ‘N’ THEN 表示如果源数据存在，并且源数据中的状态为 N，则删除目标表中的对应记录。因此，最后的查询语句没有返回结果。

INSERT ON CONFLICT语句

对于 PostgreSQL 14 以及更早版本，可以通过 INSERT INTO … ON CONFLICT… 实现数据合并的功能。

INSERT INTO table_name(column1, column2, ...)
{VALUES (value1, value2, ...) | SELECT ...}
ON CONFLICT conflict_target conflict_action;

其中，conflict_target 是判断数据是否已经存在的条件：

  • ( { index_column_name | ( index_expression ) } )，基于某个具有索引的字段或者表达式进行判断；

  • ON CONSTRAINT constraint_name，基于某个唯一约束进行判断。

conflict_action 表示冲突时采取的操作：

  • DO NOTHING，如果数据已经存在，不做任何操作；

  • DO UPDATE SET，如果数据已经存在，更新该数据；可以使用 WHERE 子句进一步限制需要更新的数据。

这种语句通过为 INSERT 语句增加 ON CONFLICT 选项，组合了 INSERT 和 UPDATE 语句的功能，因此也被称为 UPSERT 语句。

emp 表中已经存在编号为 200 的员工，如果我们再次插入该编号将会提示主键冲突：

INSERT INTO emp
values (200, 'Jennifer', 'Whalen', '2020-01-01', 4400.00, NULL, 10)
SQL Error [23505]: ERROR: duplicate key value violates unique constraint "emp_pkey"
  Detail: Key (employee_id)=(200) already exists.

此时，我们可以增加冲突处理，从而避免语句出错：

INSERT INTO emp
values (200, 'Jennifer', 'Whalen', '2020-01-01', 4400.00, NULL, 10)
on conflict (employee_id)
do nothing;

以上语句基于 employee_id 字段是否重复进行判断，冲突时不做任何处理。

另一种处理冲突的方式就是进行数据更新：

SELECT department_id 
FROM emp
WHERE employee_id = 200;
department_id|
-------------|
20|

INSERT INTO emp
values (200, 'Jennifer', 'Whalen', '2020-01-01', 4400.00, NULL, 10)
on conflict on constraint emp_pkey
do update
set first_name = EXCLUDED.first_name,
    last_name = EXCLUDED.last_name,
    hire_date = EXCLUDED.hire_date,
    salary = EXCLUDED.salary,
    manager_id =EXCLUDED.manager_id,
    department_id = EXCLUDED.department_id;

SELECT *
FROM emp
WHERE employee_id = 200;
employee_id|first_name|last_name|hire_date|salary|manager_id|department_id|
-----------|----------|---------|----------|-------|--------|-------------|
200|Jennifer  |Whalen   |2020-01-01|4400.00|        |           10|

该员工的部门编号在前面被修改为 20；我们通过主键约束 emp_pkey 进行重复数据的判断，然后更新该员工的数据；EXCLUDED 是一个特殊的表，代表了原本应该插入的数据行；最终该员工的部门编号被更新为 10。

原文作者：SQL编程思想；如有侵权，请联系管理员删除。

0
0
上一篇： 《PostgreSQL 开发指南》第 23 篇 事务与并发控制
下一篇： SQL案例分析：微信群红包
请登录后发表评论 登录
全部评论
BUAA_dxy
专家姓名：董旭阳
《SQL编程思想》作者，专注于数据库领域知识的分享。

注册时间：2021-08-26

  • 博文量
    443
  • 访问量
    546942

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593