【PostgreSQL】数据操作

PostgreSQL数据操作

插入数据

创建表时，它不包含任何数据。在数据库派上用场之前，首先要做的就是插入数据。数据一次插入一行。您也可以在单个命令中插入多行，但不能插入非完整行的内容。即使您只知道一些列值，也必须创建一个完整的行。

要创建新行，请使用 INSERT 命令。该命令需要表名和列值。例如产品表：

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric
);

插入行的示例命令如下：

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

数据值按列在表中的显示顺序列出，以逗号分隔。通常，数据值将是文本（常量），但也允许使用标量表达式。

上面的语法有一个缺点，即您需要知道表中列的顺序。为避免这种情况，还可以显式列出列。例如，以下两个命令的效果与上述命令相同：

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

许多用户认为始终列出列名是一种很好的做法。

如果没有所有列的值，则可以省略其中的一些列。在这种情况下，将使用其默认值填充列。例如：

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

第二种形式是 PostgreSQL 扩展。它用给定的值填充左侧的列，其余的值将默认为默认值。

为清楚起见，还可以显式请求单个列或整行的默认值：

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

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

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

也可以插入查询结果（可能是无行、一行或多行）：

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

有时候我们为了模拟批量数据我们需要使用到generate_series函数，假设我们在商标中插入5000🀄?测试产品，价格随机

INSERT INTO products VALUES (generate_series(1,5000), 'fruit_'+generate_series(1,5000),(random() * (1000 - 1) + 1)::numeric(10, 2));

更新数据

对数据库中已有数据的修改称为更新。您可以更新单个行、表中的所有行或所有行的子集。每列都可以单独更新;其他列不受影响。

要更新现有行，请使用 UPDATE 命令。这需要三条信息：

• 要更新的表和列的名称

• 列的新值

• 要更新的行

SQL 通常不会为行提供唯一标识符。因此，并不总是可以直接指定要更新的行。相反，您可以指定行必须满足哪些条件才能更新。只有当表中有一个主键（与是否声明该主键无关）时，才能通过选择与主键匹配的条件来可靠地对各个行进行寻址。图形数据库访问工具依赖于这一事实来允许您单独更新行。
例如，以下命令将价格为 5 的所有产品更新为价格为 10：

UPDATE products SET price = 10 WHERE price = 5;

这可能会导致更新零行、一行或多行。尝试与任何行不匹配的更新都不是错误。

让我们详细看一下该命令。首先是关键字，后跟表名。像往常一样，表名可以是架构限定的，否则会在路径中查找它。接下来是关键字，后跟列名、等号和新列值。新列值可以是任何标量表达式，而不仅仅是常量。例如，如果您想将所有产品的价格提高 10%，您可以使用：UPDATESET

UPDATE products SET price = price * 1.10;

如您所见，新值的表达式可以引用行中的现有值。我们还省略了该条款。如果省略WHERE，则表示表中的所有行都已更新。如果存在，则仅更新与条件匹配的行。请注意，子句中的等号是赋值，而子句中的等号是比较，但这不会产生任何歧义。当然，WHERE条件不一定是相等性测试。还有许多其他运算符可用。但是表达式需要计算为布尔值结果。

您可以通过在子句中列出多个赋值来更新命令中的多个列。例如：UPDATESET

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

删除数据

到目前为止，我们已经解释了如何向表添加数据以及如何更改数据。剩下的就是讨论如何删除不再需要的数据。正如只能在整行中添加数据一样，您只能从表中删除整行。在上一节中，我们解释了 SQL 不提供直接寻址单个行的方法。因此，只能通过指定要删除的行必须匹配的条件来删除行。如果表中有主键，则可以指定确切的行。但是，您也可以删除与条件匹配的行组，也可以一次删除表中的所有行。

使用 DELETE 命令删除行;语法与 UPDATE 命令非常相似。例如，要从产品表中删除价格为 10 的所有行，请使用：

DELETE FROM products WHERE price = 10;

如果你只是写：

DELETE FROM products;

然后，表中的所有行都将被删除！等同于

TRUNCATE TABLE your_table;

从修改的行返回数据

有时，在操作已修改的行时从这些行中获取数据很有用。INSERT、 UPDATE和 DELETE命令都有一个可选子句来支持这一点。使用RETURNING可以避免执行额外的数据库查询来收集数据，并且在难以可靠地识别修改后的行时特别有价值。

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

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

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

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

该子句对 也非常有用。RETURNINGINSERT … SELECT

在 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的另一个常见用例。