PostgreSQL 04 - DML

发表于 2024-08-06 分类于 DB ， PostgreSQL 评论数：阅读次数：

PostgreSQL: The World's Most Advanced Open Source Relational Database.

DML (Data Manipulation Language) 是 SQL (Structured Query Language) 的一部分，用于对数据库中的数据进行操作。DML 主要包括插入，修改，删除三种操作。

插入数据

向表中插入数据，可以一次插入一行，也可以一次插入多行，但不能插入不完整的一行。

插入数据时需要知道表名，假定存在以下表：

CREATE TABLE products (
    id integer,
    name varchar,
    price numeric
);

使用INSERT命令插入一行数据：

INSERT INTO products VALUES (1, 'Cheese', 9.99);

数据值按列在表中出现的顺序列出，并用逗号分隔。数据值可以是常量，也可以是表达式。上述语法省略了列名，但通常情况下建议指明列名列表，例如：

INSERT INTO products (id, name, price) VALUES (1, 'Cheese', 9.99);
INSERT INTO products (name, price, id) VALUES ('Cheese', 9.99, 1);

可以只列出部分列名，剩余的列将被填充为默认值：

INSERT INTO products (id, name) VALUES (1, 'Cheese');
INSERT INTO products VALUES (1, 'Cheese');

第二种形式是 PostgreSQL 扩展：它从左侧填充列，其余列将被填充为默认值。

还可以显式将某一列甚至一整行填充为默认值：

INSERT INTO products (id, name, price) VALUES (1, 'Cheese', DEFAULT);
INSERT INTO products DEFAULT VALUES;

可以在单个命令中插入多行

INSERT INTO products (id, name, price) VALUES
    (1, 'Cheese', 9.99),
    (2, 'Bread', 1.99),
    (3, 'Milk', 2.99);

还可以插入查询结果 (可能是 0 行，1 行或者多行)：

INSERT INTO products (id, name, price)
  SELECT id, name, price FROM new_products
    WHERE release_date = 'today';

更新数据

可以使用UPDATE命令更新表中的单个行、所有行或所有行的子集。可以单独更新某一列，而其他列不受影响。要更新现有行，需要三条信息：要更新的表和列的名称，列的新值和要更新的行。

SQL 通常不为行提供唯一标识符 (如行号)，因此无法直接指定要更新的行，需要用户指定某些条件来选中行。例如：将价格为 5 的所有产品的价格更新为 10：

UPDATE products SET price = 10 WHERE price = 5;

这可能会导致 0 行、1 行或多行被更新，更新 0 行的操作不会出错。

使用 SET 子句指定列的新值，值可以是常量，也可以是表达式：

UPDATE products SET price = price * 1.10;

如果省略 WHERE 子句，则表示表中的所有行都将被更新。否则，将仅更新满足 WHERE 条件的行。

可以在 SET 子句中同时更新多个列：

UPDATE mytable SET a = 5, b = 3, c = 1 WHERE a > 0;

删除数据

从数据表中删除数据时只能删除一整行，和 UPDATE 命令一样，需要用户指定删除条件，例如：

DELETE FROM products WHERE price = 10;

要删除数据表中所有行，使用：

DELETE FROM products;

返回数据

INSERT、UPDATE 和 DELETE 命令都具有一个可选的RETURNING子句来返回修改行中的数据。使用 RETURNING 可以避免执行额外的数据库查询来收集数据，并且在难以可靠地识别修改行时尤其有价值。

RETURNING 子句的内容与 SELECT 命令的输出列表相同。它可以包含命令目标表的列名，或使用这些列的值表达式。一种常见的简写是RETURNING *，它按顺序选择目标表的所有列。

在 INSERT 中，RETURNING 返回的数据是插入时的行。这在简单的插入中不太有用，因为它只会重复客户端提供的数据。但当列依赖于计算的默认值时，它会非常方便。例如，当使用 serial 列提供唯一标识符时，RETURNING 可以返回分配给新行的 ID：

CREATE TABLE users (firstname text, lastname text, id serial primary key);

INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id;

在 UPDATE 中，RETURNING 返回的数据是修改行后的新内容，例如：

UPDATE products SET price = price * 1.10
  WHERE price <= 99.99
  RETURNING name, price AS new_price;

在 DELETE 中，RETURNING 返回的数据是被删除行的内容，例如：

DELETE FROM products
  WHERE obsoletion_date = 'today'
  RETURNING *;

如果目标表上有触发器，RETURNING 返回的数据是触发器修改后的行。因此，检查由触发器计算的列是 RETURNING 的另一个常见用例。