PostgreSql增删改（与mysql差异）

灰太狼 2022-11-07 00:57 182阅读 0赞

## INSERT ##

当一个表被创建后，它不包含数据。在数据库可以有点用之前要做的第一件事就是向里面插入数据。数据在概念上是以每次一行地方式被插入的。你当然可以每次插入多行，但是却没有办法一次插入少于一行的数据。即使你只知道几个列的值，那么你也必须创建一个完整的行。

要创建一个新行，使用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';

**重点**  
与mysql无异

--------------------

## UPDATE ##

UPDATE — 更新一个表的行  
大纲

[ WITH [ RECURSIVE ] with_query [, ...] ]
    UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
        SET { column_name = { expression | DEFAULT } |
              ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
              ( column_name [, ...] ) = ( sub-SELECT )
            } [, ...]
        [ FROM from_list ]
        [ WHERE condition | WHERE CURRENT OF cursor_name ]
        [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

描述

UPDATE更改满足条件的所有行中指定列 的值。只有要被修改的列需要在SET子句中提及， 没有被显式修改的列保持它们之前的值。

有两种方法使用包含在数据库其他表中的信息来修改一个表：使用子选择 或者在FROM子句中指定额外的表。这种技术只适合 特定的环境。

可选的RETURNING子句导致UPDATE 基于实际被更新的每一行计算并且返回值。任何使用该表的列以及 FROM中提到的其他表的列的表达式都能被计算。 计算时会使用该表的列的新（更新后）值。RETURNING 列表的语法和SELECT的输出列表相同。

你必须拥有该表上的UPDATE特权，或者至少拥有 要被更新的列上的该特权。如果任何一列的值需要被 expressions或者 condition读取， 你还必须拥有该列上的SELECT特权。  
参数

with\_query

WITH子句允许你指定一个或者更多个在 UPDATE中可用其名称引用的子查询。详见 第 7.8 节和SELECT。

table\_name

要更新的表的名称（可以是模式限定的）。如果在表名前指定了 ONLY，只会更新所提及表中的匹配行。如果没有指定 ONLY，任何从所提及表继承得到的表中的匹配行也会 被更新。可选地，在表名之后指定*可以显式地指示要 把后代表也包括在内。

alias

目标表的一个替代名称。在提供了一个别名时，它会完全隐藏表的真实 名称。例如，给定UPDATE foo AS f， UPDATE语句的剩余部分必须用 f而不是foo来引用该表。

column\_name

table_name 所指定的表的一列的名称。如果需要，该列名可以用一个子域名称或者 数组下标限定。不要在目标列的说明中包括表的名称 — 例如 UPDATE table_name SET table_name.col = 1是非法的。

expression

要被赋值给该列的一个表达式。该表达式可以使用该表中这一列或者 其他列的旧值。

DEFAULT

将该列设置为它的默认值（如果没有为它指定默认值表达式，默认值 将会为 NULL）。

sub-SELECT

一个SELECT子查询，它产生和在它之前的圆括号中列列表中 一样多的输出列。被执行时，该子查询必须得到不超过一行。如果它得到 一行，其列值会被赋予给目标列。如果它得不到行，NULL 值将被赋予给 目标列。该子查询可以引用被更新表中当前行的旧值。

from\_list

表表达式的列表，允许来自其他表的列出现在WHERE 条件和更新表达式中。这类似于可以在 SELECT语句的FROM 子句中指定的表列表。注意目标表不能出现在 from_list中，除非你想做自连接（这种情况下它必须 以别名出现在from_list中）。

condition

一个返回boolean类型值的表达式。让这个 表达式返回true的行将会被更新。

cursor\_name

要在WHERE CURRENT OF条件中使用的游标名。 要被更新的是从这个游标中最近取出的行。该游标必须是一个 在UPDATE目标表上的非分组查询。注意 WHERE CURRENT OF不能和一个布尔条件一起 指定。有关对游标使用WHERE CURRENT OF的 更多信息请见DECLARE。

output\_expression

在每一行被更新后，要被UPDATE命令计算并且返回 的表达式。该表达式可以使用 table_name指定 的表或者FROM列出的表中的任何列名。写* 可以返回所有列。

output\_name

用于一个被返回列的名称。

输出

成功完成时，一个UPDATE命令返回形如

UPDATE count

的命令标签。 count是被更新的行数， 包括值没有更改的匹配行。注意，当更新被一个BEFORE UPDATE 触发器抑制时，这个数量可能比匹配 condition的行数少。如果 count为零，没有行被该查 询更新（这不是一个错误）。

如果UPDATE命令包含一个RETURNING 子句，其结果将类似于一个包含RETURNING列表中定义的 列和值的SELECT语句（在被该命令更新的行上计算） 的结果。  
注解

当存在FROM子句时，实际发生的是：目标表被连接到 from\_list中的表，并且该连接的每一 个输出行表示对目标表的一个更新操作。在使用FROM 时，你应该确保该连接对每一个要修改的行产生至多一个输出行。换 句话说，一个目标行不应该连接到来自其他表的多于一行上。如果发 生这种情况，则只有一个连接行将被用于更新目标行，但是将使用哪 一行是很难预测的。

由于这种不确定性，只在一个子选择中引用其他表更安全，不过这种 语句通常很难写并且也比使用连接慢。

在分区表的情况下，更新一行有可能导致它不再满足其所在分区的分区约束。此时，如果这个行满足分区树中某个其他分区的分区约束，那么这个行会被移动到那个分区。 如果没有这样的分区，则会发生错误。在后台，行的移动实际上是一次DELETE操作和一次INSERT操作。

在移动的行上的并发UPDATE或DELETE可能会收到序列化失败错误。 假设会话 1 正在分区键上执行UPDATE，同时，对可访问该行的并发会话 2 在此行上执行UPDATE或DELETE操作。 在这种情况下，会话 2 的UPDATE 或 DELETE将检测行移动并引发序列化失败错误（该错误始终返回 SQLSTATE 代码"40001"）。 如果发生这种情况，应用程序可能希望重试事务。 在通常情况下，表没有分区或没有行移动，会话 2 将标识新更新的行，并执行UPDATE/DELETE在此新行版本中。

请注意，虽然行可以从本地分区移动到外表分区（如果外数据包装器支持元组路由），但它们不能从外表分区移动到另一个分区。  
示例

把表films的列kind 中的单词Drama改成Dramatic：

UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';

在表weather的一行中调整温度项并且 把沉淀物重置为它的默认值：

UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
      WHERE city = 'San Francisco' AND date = '2003-07-03';

执行相同的操作并且返回更新后的项：

UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
      WHERE city = 'San Francisco' AND date = '2003-07-03'
      RETURNING temp_lo, temp_hi, prcp;

使用另一种列列表语法来做同样的更新：

UPDATE weather SET (temp_lo, temp_hi, prcp) = (temp_lo+1, temp_lo+15, DEFAULT)
      WHERE city = 'San Francisco' AND date = '2003-07-03';

为管理Acme Corporation账户的销售人员增加销售量，使用 FROM子句语法：

UPDATE employees SET sales_count = sales_count + 1 FROM accounts
      WHERE accounts.name = 'Acme Corporation'
      AND employees.id = accounts.sales_person;

执行相同的操作，在 WHERE子句中使用子选择：

UPDATE employees SET sales_count = sales_count + 1 WHERE id =
      (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation');

更新 accounts 表中的联系人姓名以匹配当前被分配的销售员：

UPDATE accounts SET (contact_first_name, contact_last_name) =
        (SELECT first_name, last_name FROM salesmen
         WHERE salesmen.id = accounts.sales_id);

可以用连接完成类似的结果：

UPDATE accounts SET contact_first_name = first_name,
                        contact_last_name = last_name
      FROM salesmen WHERE salesmen.id = accounts.sales_id;

不过，如果salesmen.id不是一个唯一键， 第二个查询可能会给出令人意外的结果，然而如果有多个id匹配， 第一个查询保证会发生错误。还有，如果对于一个特定的 accounts.sales\_id项没有匹配，第一个查询将 把相应的姓名域设置为 NULL，而第二个查询完全不会更新该行。

更新一个统计表中的统计数据以匹配当前数据：

UPDATE summary s SET (sum_x, sum_y, avg_x, avg_y) =
        (SELECT sum(x), sum(y), avg(x), avg(y) FROM data d
         WHERE d.group_id = s.group_id);

尝试插入一个新库存项及其库存量。如果该项已经存在，则转而更新 已有项的库存量。要这样做并且不让整个事务失败，可以使用保存点：

BEGIN;
    -- 其他操作
    SAVEPOINT sp1;
    INSERT INTO wines VALUES('Chateau Lafite 2003', '24');
    -- 假定上述语句由于未被唯一键失败，
    -- 那么现在我们发出这些命令：
    ROLLBACK TO sp1;
    UPDATE wines SET stock = stock + 24 WHERE winename = 'Chateau Lafite 2003';
    -- 继续其他操作，并且最终
    COMMIT;

更改表films中由游标c\_films 定位的行的kind列：

UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films;

**重点**  
与mysql差异  
例：创建两个表

create  table  tm_ztest1 (id int , name varchar(50) );
    insert  into tm_ztest1 (id,name ) values(1,'a'),(2,'b'),(3,'c'),(4,'d');
     select   * from tm_ztest1 ;
     
    
    create  table  tm_ztest2 (id int , name varchar(50) );
    insert  into tm_ztest2 (id,name ) values(1,'a'),(2,'a'),(3,'a'),(4,'a'),(5,'a');
     select   * from tm_ztest2 ;

![在这里插入图片描述][2021031016031923.png]![在这里插入图片描述][20210310160337545.png]

如果我们期望以tm\_ztest1为准根据id关联修改tm\_ztest2的name列  
在mysql上可能是这样的

UPDATE tm_ztest2 a LEFT JOIN tm_ztest1 b  ON a.id=b.id SET a.name=b.name;
    
     select   * from tm_ztest2 ;

![在这里插入图片描述][20210310161347722.png]  
假设这是我们期望的结果  
那postgre上如何实现

UPDATE tm_ztest2 a    SET  name=b.name  FROM   tm_ztest1 b  where  a.id=b.id 
    
     select   * from tm_ztest2 ;

![在这里插入图片描述][20210310161815981.png]  
可见结果与mysql并不一致  
那我们该如何实现与mysql同样的结果、postgre中update不支持join但为我们提供了可选词with

with z as (select  a.id,b."name"  from  tm_ztest2 a left join tm_ztest1 b on a.id=b.id     )
    
     UPDATE tm_ztest2 a    SET  name=z.name  FROM   z  where  a.id=z.id ;

![在这里插入图片描述][20210310162348682.png]  
此时与mysql结果一致  
在执行update语句时、 除非我们明确逻辑、 不然不推荐使用left join关联 这可能使你修改的数据比期望的多。

--------------------

## DELETE ##

DELETE — 删除一个表的行

[ WITH [ RECURSIVE ] with_query [, ...] ]
    DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
        [ USING using_list ]
        [ WHERE condition | WHERE CURRENT OF cursor_name ]
        [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

描述

DELETE从指定表中删除满足 WHERE子句的行。如果WHERE 子句没有出现，效果将会是删除表中的所有行。结果是一个合法的空表。

有两种方式可以使用数据库中其他表中包含的信息来删除一个表的行： 使用子选择或者在USING子句中指定额外的表。 哪种技术更合适取决于特定的环境。

可选的RETURNING子句导致DELETE 基于实际被删除的每一行计算并且返回值。任何使用被删除表列或者 USING中提到的其他表的列的表达式都可以被计算。 RETURNING列表的语法和SELECT的 输出列表语法相同。

要从表中删除行，你必须具有其上的DELETE特权， 以及USING子句中任何表以及其值在condition中被读取的表上的 SELECT特权。  
参数

with\_query

WITH子句允许你指定一个或者多个子查询，在 DELETE查询中可以用子查询的名字来引用它们。 详见第 7.8 节和SELECT。

table\_name

要从其中删除行的表名（可以是模式限定的）。如果在表名前指定 ONLY，只会从提到的表中删除匹配的行。如果没有指定 ONLY，还会删除该表的任何继承表中的匹配行。可选地， 可以在表名后面指定*来显式指定要包括继承表。

alias

目标表的一个别名。提供别名时，它会完全隐藏该表的真实名称。例如， 对于DELETE FROM foo AS f， DELETE语句的剩余部分都会用 f而不是 foo来引用该表。

using\_list

一个表表达式的列表，它允许在WHERE条件中出现 来自其他表的列。这和SELECT语句 的FROM 子句中指定 的表列表相似。例如，可以指定表的别名。除非想要进行自连接， 否则不要在using_list 再写上目标表。

condition

一个返回boolean类型值的表达式。只有让这个 表达式返回true的行才将被删除。

cursor\_name

要在WHERE CURRENT OF情况中使用的游标 的名称。最近一次从这个游标中取出的行将被删除。该游标 必须是DELETE的目标表上的非分组查询。 注意不能在使用WHERE CURRENT OF的同时 指定一个布尔条件。有关将游标用于 WHERE CURRENT OF的更多信息请见 DECLARE。

output\_expression

在每一行被删除后，会被DELETE计算并且返回的表达式。 该表达式可以使用table_name 以及USING中的表的任何列。写成*可以返回所有列。

output\_name

被返回列的名称。

输出

在成功完成时，一个DELETE命令会返回以下形式 的命令标签：

DELETE count

count是被删除行的数目。 注意如果有一个BEFORE DELETE触发器抑制删除，那么该数目 可能小于匹配condition 的行数。如果count为 0， 表示查询没有删除行（这并非一种错误）。

如果DELETE命令包含RETURNING子句， 则结果会与包含有RETURNING列表中定义的列和值的 SELECT语句结果相似，这些结果是在被该命令删除的 行上计算得来。  
注解

通过在USING子句中指定其他的表， PostgreSQL允许在 WHERE条件中引用其他表的列。例如，要 删除有一个给定制片人制作的所有电影，可以这样做：

DELETE FROM films USING producers
      WHERE producer_id = producers.id AND producers.name = 'foo';

这里实际发生的事情是在films和 producers之间进行连接，然后删除 所有成功连接的films行。这种语法不 属于标准。更标准的方式是：

DELETE FROM films
      WHERE producer_id IN (SELECT id FROM producers WHERE name = 'foo');

在一些情况下，连接形式比子查询形式更容易书写或者执行更快。  
示例

删除所有电影，但音乐剧除外：

DELETE FROM films WHERE kind <> 'Musical';

清空表films：

DELETE FROM films;

删除已完成的任务，返回被删除行的明细：

DELETE FROM tasks WHERE status = 'DONE' RETURNING *;

删除tasks中游标c\_tasks 当前位于其上的行：

DELETE FROM tasks WHERE CURRENT OF c_tasks;

**重点**  
与mysql差异  
同样的、postgre中delete也提供了可选词with、如果涉及类似mysql多表连接作为删除的筛选条件时、 可考虑用using实现关联或为保证执行结果准确、 用with筛选出需要删除的id再进行删除。

--------------------

**update 与 delete语法均有差异、 为保证结果准确、 可使用with确定要操作的数据后再进行操作**

[2021031016031923.png]: https://img-blog.csdnimg.cn/2021031016031923.png
[20210310160337545.png]: /images/20221023/eb2f974b543d41069c24bfbcd7c35468.png
[20210310161347722.png]: /images/20221023/0b1c3ef0091e4ac9aa27c533e8236344.png
[20210310161815981.png]: /images/20221023/b85cf3249a6b41329105316960216738.png
[20210310162348682.png]: /images/20221023/ef1160e1469d4693b8a3f247a3463bed.png