数据查询、更新和删除

SELECT

功能介绍

SELECT 语句用于从表中或者视图中查询数据。语法如下：

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] ]
    [ HAVING condition ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES } ]
    [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
 
where from_item can be one of:
 
    [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
                [ TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] ]
    [ LATERAL ] ( select ) [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ] function_name ( [ argument [, ...] ] )
                [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ] function_name ( [ argument [, ...] ] ) [ AS ] alias ( column_definition [, ...] )
    [ LATERAL ] function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
    [ LATERAL ] ROWS FROM( function_name ( [ argument [, ...] ] ) [ AS ( column_definition [, ...] ) ] [, ...] )
                [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    from_item join_type from_item { ON join_condition | USING ( join_column [, ...] ) [ AS join_using_alias ] }
    from_item NATURAL join_type from_itemfrom_item CROSS JOIN from_itemand grouping_element can be one of:
 
    ( )
    expression
    ( expression [, ...] )
    ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )
    CUBE ( { expression | ( expression [, ...] ) } [, ...] )
    GROUPING SETS ( grouping_element [, ...] )
 
and with_query is:with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( select | values | insert | update | delete )
        [ SEARCH { BREADTH | DEPTH } FIRST BY column_name [, ...] SET search_seq_col_name ]
        [ CYCLE column_name [, ...] SET cycle_mark_col_name [ TO cycle_mark_value DEFAULT cycle_mark_default ] USING cycle_path_col_name ]
 
TABLE [ ONLY ] table_name [ * ]

有关SELECT更多的使用语法，请参考SELECT。

使用场景

使用如下表进行 SELECT 测试

protonbase=> \d t1
                                 数据表 "public.t1"
  栏位  |     类型     | 校对规则 |  可空的  |                 预设
--------+--------------+----------+----------+--------------------------------------
 id     | integer      |          | not null | nextval('t1_id_seq'::text::regclass)
 name   | text         |          |          |
 age    | smallint     |          |          |
 gender | character(1) |          |          |
索引：
    "t1_pkey" PRIMARY KEY, btree (id ASC)
 
protonbase=> SELECT * FROM t1;
 id |    name    | age | gender
----+------------+-----+--------
  1 | updatename |  11 | m
  2 | testname   |     |
  3 | updatename |  11 | f
  4 | updatename |  11 | f
  5 | testname4  |  22 | m
 11 | updatename |  11 | f
(6 行记录)

查询表里满足指定条件数据

protonbase=> SELECT * FROM t1 WHERE gender='m';
 id |    name    | age | gender
----+------------+-----+--------
  1 | updatename |  11 | m
  5 | testname4  |  22 | m
(2 行记录)

只查询部分字段

protonbase=> SELECT id, name FROM t1 WHERE gender='m';
 id |    name
----+------------
  1 | updatename
  5 | testname4
(2 行记录)

SubQuery 查询

子查询或内部查询或嵌套查询是一个 PostgreSQL 查询中的查询，它可以嵌入到 WHERE 子句中。子查询用于返回将在主查询中使用的数据作为进一步限制要检索的数据的条件。子查询可以与 SELECT，INSERT，UPDATE 和 DELETE 语句以及运算符(如 =，<，>，=，<=，IN 等)一起使用。

子查询必须遵循以下规则：

子查询必须括在括号中。
子查询在 SELECT 子句中只能有一列，除非主查询中有多个列用于比较其所选列的子查询。
ORDER BY 不能用于子查询，尽管主查询可以使用 ORDER BY。GROUP BY 可用于执行与子查询中的 ORDER BY 相同的功能。
返回多行的子查询只能与多个值运算符一起使用，例如：IN，EXISTS，NOT IN，ANY / SOME，ALL 运算符。
BETWEEN 运算符不能与子查询一起使用; 但是，BETWEEN 可以在子查询中使用。

子查询最常用于 SELECT 语句。基本语法如下：

SELECT column1, column2, ... colulmnN
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
      (SELECT column1, column2, ... colulmnN
      FROM table1 [, table2 ]
      [WHERE])

注意: 子查询最常用于 SELECT 中，也可用于 INSERT/UPDATE/DELETE

使用示例：

使用如下测试表和数据进行 SubQuery 测试

protonbase=> \d t1
                                 数据表 "public.t1"
  栏位  |     类型     | 校对规则 |  可空的  |                 预设
--------+--------------+----------+----------+--------------------------------------
 id     | integer      |          | not null | nextval('t1_id_seq'::text::regclass)
 name   | text         |          |          |
 age    | smallint     |          |          |
 gender | character(1) |          |          |
索引：
    "t1_pkey" PRIMARY KEY, btree (id ASC)
 
protonbase=> SELECT * FROM t1;
 id |    name    | age | gender
----+------------+-----+--------
  1 | updatename |  11 | m
  2 | aa         |  11 | m
  3 | bb         |  11 | m
  4 | ee         |  11 | f
  5 | ff         |  15 | f
  6 | gg         |  18 | m
(6 行记录)
 
protonbase=> \d t2
              数据表 "public.t2"
 栏位  |   类型   | 校对规则 |  可空的  | 预设
-------+----------+----------+----------+------
 id    | integer  |          | not null |
 score | smallint |          |          |
索引：
    "t2_pkey" PRIMARY KEY, btree (id ASC)
 
protonbase=> SELECT * FROM t2;
 id | score
----+-------
  1 |    80
  2 |    90
  3 |    85
  4 |    88
  5 |    96
  6 |    89
(6 行记录)

查询表 t2 中 score>=90 的所有 id 在 t1 表中的名字

protonbase=> SELECT t1.id, t1.name FROM t1 WHERE id IN (SELECT id FROM t2 WHERE score>=90);
 id | name
----+------
  5 | ff
  2 | aa
(2 行记录)

With 查询

With 查询是 PG 支持的高级 SQL 特性之一，通常称为 CTE(Common Table Expressions)。with 查询在复杂查询中定义一个辅助语句，相当于在查询中定义的临时表。在查询语句比较复杂的时候，它可以将查询 SQL 极大简化，易于阅读。

With 查询语法:

WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
), top_regions AS (
    SELECT region
    FROM regional_sales
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

下面以一个具体的例子来描述 with 查询的用法。测试表定义和数据如下:

protonbase=> \d t1
                                 数据表 "public.t1"
  栏位  |     类型     | 校对规则 |  可空的  |                 预设
--------+--------------+----------+----------+--------------------------------------
 id     | integer      |          | not null | nextval('t1_id_seq'::text::regclass)
 name   | text         |          |          |
 age    | smallint     |          |          |
 gender | character(1) |          |          |
索引：
    "t1_pkey" PRIMARY KEY, btree (id ASC)
 
protonbase=> SELECT * FROM t1;
 id |    name    | age | gender
----+------------+-----+--------
  1 | updatename |  11 | m
  2 | aa         |  11 | m
  3 | bb         |  11 | m
  4 | ee         |  11 | f
  5 | ff         |  15 | f
  6 | gg         |  18 | m
(6 行记录)
 
时间：225.186 ms

SELECT 场景的 WITH 查询

protonbase=> WITH t AS (SELECT * FROM t1 WHERE gender='f') SELECT * FROM t WHERE age > 12;
 id | name | age | gender
----+------+-----+--------
  5 | ff   |  15 | f
(1 行记录)

With 查询好处:

CTE 可以简化 SQL 代码，提高 SQL 代码的可读性
相对于视图来说更加轻量级

DELETE

功能介绍

DELETE 用于从表中删除数据。根据指定的 WHERE 条件可以过滤出特定行进行删除，不指定 WHERE 条件时删除全表数据

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

有关DELETE更多的使用语法，请参考DELETE。

使用示例

对如下表进行删除测试

protonbase=> \d t2
                                  数据表 "public.t2"
  栏位  |     类型     | 校对规则 |  可空的  |                  预设
--------+--------------+----------+----------+-----------------------------------------
 id     | integer      |          | not null | nextval('t1_id_seq'::regclass)::integer
 name   | text         |          |          |
 age    | smallint     |          |          |
 gender | character(1) |          |          |
索引：
    "t2_pkey" PRIMARY KEY, btree (id ASC)
 
protonbase=> select * from t2;
 id |    name    | age | gender
----+------------+-----+--------
 10 | t2testname |  22 | f
 11 | t2testname |  22 | f
 12 | t2testname |  22 | f
(3 行记录)

从表中删除一条数据

-- 删除指定列数据
protonbase=> delete from t2 where id=11;
DELETE 1
protonbase=> select * from t2;
 id |    name    | age | gender
----+------------+-----+--------
 10 | t2testname |  22 | f
 12 | t2testname |  22 | f
(2 行记录)

删除整个表的所有数据

-- 删除全表数据
protonbase=> delete from t2;
DELETE 2
protonbase=> select * from t2;
 id | name | age | gender
----+------+-----+--------
(0 行记录)

使用 DELETE USING 在删除语句中引用其他表，在单一SQL内简化涉及多表的复杂删除操作。

-- 删除product表中所有在stock表里库存为0的产品
DELETE FROM product
USING stock
    WHERE product.id = stock.product_id AND stock.quantity = 0;

注意事项

为了实现多版本并发控制以及 TimeTravel 的功能，MVCC 会在每条记录上增加一个时间戳信息作为版本标记，对 delete 操作并不会立即将老数据从磁盘上物理删除，只有在 MVCC 版本过期后通过异步任务将老数据清理掉，因此 delete 操作执行后会增加存储空间。待异步 compaction 结束后存储才会下降

UPDATE

功能介绍

当更新表中的数据时，需要使用 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_item [, ...] ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

有关UPDATE更多的使用语法，请参考UPDATE。

使用示例

对下面的表进行 update 测试

protonbase=> \d t1
                                 数据表 "public.t1"
  栏位  |     类型     | 校对规则 |  可空的  |                 预设
--------+--------------+----------+----------+--------------------------------------
 id     | integer      |          | not null | nextval('t1_id_seq'::text::regclass)
 name   | text         |          |          |
 age    | smallint     |          |          |
 gender | character(1) |          |          |
索引：
    "t1_pkey" PRIMARY KEY, btree (id ASC)
 
protonbase=> select * from t1;
 id |    name    | age | gender
----+------------+-----+--------
  1 | testname1  |  11 | m
  2 | testname   |     |
  3 | testname3  |  33 | f
  4 | testname4  |  22 | f
  5 | testname4  |  22 | m
 11 | t2testname |  22 | f
(6 行记录)

更新表中的一条数据

protonbase=> update t1 set name='updatename' where id=1;
UPDATE 1
protonbase=> select * from t1 where id=1;
 id |    name    | age | gender
----+------------+-----+--------
  1 | updatename |  11 | m
(1 行记录)

更新满足条件的多条数据

protonbase=> select * from t1 where gender='f';
 id |    name    | age | gender
----+------------+-----+--------
  3 | testname3  |  33 | f
  4 | testname4  |  22 | f
 11 | t2testname |  22 | f
(3 行记录)
 
protonbase=> update t1 set name='updatename',age=11 where gender='f';
UPDATE 3
protonbase=> select * from t1 where gender='f';
 id |    name    | age | gender
----+------------+-----+--------
  3 | updatename |  11 | f
  4 | updatename |  11 | f
 11 | updatename |  11 | f
(3 行记录)

注意事项

为了实现多版本并发控制以及 TimeTravel 的功能，MVCC 会在每条记录上增加一个时间戳信息作为版本标记，对 update 操作并不会立即将老数据从磁盘上物理删除，只有在 MVCC 版本过期后通过异步任务将老数据清理掉，因此 update 操作执行后会增加存储空间。待异步 compaction 结束后存储容量才会下降。

MERGE

功能介绍

可以使用 MERGE INTO 语句来合并记录，它将INSERT、UPDATE、DELETE操作合并为一个单一的语句，根据源表和目标表的匹配条件来执行相应的操作。MERGE INTO 提供了一种高效、简洁、且具备数据一致性的方式来同步两个表之间的数据。

使用场景

数据仓库ETL：将增量数据合并到数据仓库表中。
数据同步：在不同系统或数据库之间同步数据。
数据去重：根据特定条件对表中的数据进行去重和更新。

语法如下：

MERGE INTO target_table_name [[AS] target_alias]
USING source_table_name [[AS] source_alias]
ON ( condition )
WHEN MATCHED THEN
    UPDATE SET column1 = value1 [, column2 = value2 ...]
WHEN NOT MATCHED THEN
    INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...]);

其中

target_table_name 是要更新或插入数据的目标表
source_table_name 是提供数据的源表
condition 指定目标表和源表之间的匹配条件
WHEN MATCHED THEN 当匹配条件成立时执行UPDATE操作
WHEN NOT MATCHED THEN 当匹配条件不成立时执行INSERT操作

使用示例

MERGE INTO customers c
USING (SELECT *
        FROM new_customer_data) AS nc
ON c.id = nc.id
WHEN MATCHED THEN
    UPDATE
    SET name  = nc.name,
        email = nc.email
WHEN NOT MATCHED THEN
    INSERT (id, name, email)
    VALUES (nc.id, nc.name, nc.email);

有关MERGE更多的使用语法，请参考MERGE。

TRUNCATE

功能介绍

Truncate 用于删除一个表的所有数据，语法如下：

TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ... ]
    [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]

有关TRUNCATE更多的使用语法，请参考TRUNCATE。

使用示例

使用如下表进行 TRUNCATE 测试

数据表 "public.t1"
  栏位  |     类型     | 校对规则 |  可空的  |                 预设
--------+--------------+----------+----------+--------------------------------------
 id     | integer      |          | not null | nextval('t1_id_seq'::text::regclass)
 name   | text         |          |          |
 age    | smallint     |          |          |
 gender | character(1) |          |          |
索引：
    "t1_pkey" PRIMARY KEY, btree (id ASC)
 
protonbase=> SELECT * FROM t1;
 id |    name    | age | gender
----+------------+-----+--------
  1 | updatename |  11 | m
  2 | testname   |     |
  3 | updatename |  11 | f
  4 | updatename |  11 | f
  5 | testname4  |  22 | m
 11 | updatename |  11 | f
(6 行记录)

对表进行 truncate 操作

protonbase=> TRUNCATE TABLE t1;
TRUNCATE TABLE
protonbase=> SELECT * FROM t1;
 id | name | age | gender
----+------+-----+--------
(0 行记录)

注意事项

TRUNCATE TABLE 与 DELETE 具有相同的效果，但是由于它实际上并不扫描表，所以速度更快。

VIEW

功能介绍

视图(View)本质上是一个存储在数据库中的查询语句。视图本身不包含数据,也被称为虚拟表。我们在创建视图时给它指定了一个名称，然后可以像表一样对其进行查询。视图物理上实际不存在的，在该视图每次被引用的时候都会运行一次 query。好处是不保存任何数据，只是保存查询语句，减少经常调用 SQL 的频繁书写问题。

语法如下:

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name< [, ...] ) ]
    [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
    AS query
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

有关VIEW更多的使用语法，请参考CREATE VIEW，ALTER VIEW，DROP VIEW。

使用示例

使用如下表进行视图相关测试

protonbase=> \d t1
 id     | integer      |           | not null | nextval('t1_id_seq'::text::regclass)
 name   | text         |           |          |
 age    | smallint     |           |          |
 gender | character(1) |           |          |
 
protonbase=> SELECT * FROM t1;
  1 | updatename |  11 | m
  2 | aa         |  11 | m
  3 | bb         |  11 | m
  4 | ee         |  11 | f
  5 | ff         |  15 | f
  6 | gg         |  18 | m

创建视图

可以在 SELECT 语句中包含多个表，这与在 SQL SELECT 查询中使用的方式非常相似

protonbase=> CREATE VIEW testview AS SELECT id, name, age, gender FROM t1 WHERE gender='m';
CREATE VIEW
protonbase=> SELECT * FROM testview;
  1 | updatename |  11 | m
  2 | aa         |  11 | m
  3 | bb         |  11 | m
  6 | gg         |  18 | m

修改视图

protonbase=> \dv
 public | testview | view | pbadmin
 
protonbase=> ALTER VIEW testview RENAME TO testview1;
ALTER VIEW
 
protonbase=> \dv
 public | testview1 | view | pbadmin

删除视图

通过 drop view 对视图进行删除，示例如下:

protonbase=> DROP VIEW testview;
DROP VIEW

查询所有视图

通过 information_schema.tables 或者 \dv 可以找到所有用户定义的视图信息

protonbase=> SELECT * FROM information_schema.tables WHERE table_type = 'VIEW';
 protonbase    | public       | testview   | VIEW       | NO                 |       1
 
 protonbase=> \dv
 public | testview | view | pbadmin