数据查询、更新和删除

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 行记录)

实际示例

以下是一些常见的 SELECT 查询示例，帮助您更好地理解和使用 ProtonBase：

1. 基本查询示例

-- 查询所有员工信息
SELECT * FROM employees;
 
-- 查询特定字段
SELECT employee_id, first_name, last_name, department FROM employees;
 
-- 带条件查询
SELECT employee_id, first_name, last_name 
FROM employees 
WHERE department = 'Engineering';
 
-- 排序查询
SELECT employee_id, first_name, last_name, salary 
FROM employees 
ORDER BY salary DESC;
 
-- 限制结果数量
SELECT employee_id, first_name, last_name 
FROM employees 
ORDER BY hire_date DESC 
LIMIT 10;

2. 聚合查询示例

-- 统计各部门员工数量
SELECT department, COUNT(*) as employee_count 
FROM employees 
GROUP BY department;
 
-- 计算各部门平均薪资
SELECT department, AVG(salary) as avg_salary 
FROM employees 
GROUP BY department;
 
-- 查找最高薪资员工
SELECT employee_id, first_name, last_name, salary 
FROM employees 
WHERE salary = (SELECT MAX(salary) FROM employees);

3. 连接查询示例

-- 内连接查询员工及其部门信息
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
 
-- 左连接查询所有员工及其部门信息（包括没有分配部门的员工）
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

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;

实际示例

以下是一些常见的 DELETE 操作示例，帮助您更好地理解和使用 ProtonBase：

1. 基本删除操作

-- 删除特定员工记录
DELETE FROM employees WHERE employee_id = 1001;
 
-- 删除离职员工记录
DELETE FROM employees WHERE status = 'terminated';
 
-- 删除特定部门的所有员工（谨慎操作）
DELETE FROM employees WHERE department_id = 5;

2. 条件删除操作

-- 删除超过30天的日志记录
DELETE FROM system_logs WHERE log_date < CURRENT_DATE - INTERVAL '30 days';
 
-- 删除测试数据
DELETE FROM user_data WHERE user_id IN (SELECT user_id FROM test_users);
 
-- 删除重复记录，保留最小ID的记录
DELETE FROM user_contacts 
WHERE user_id IN (
    SELECT user_id 
    FROM user_contacts 
    GROUP BY user_id 
    HAVING COUNT(*) > 1
) AND ctid NOT IN (
    SELECT MIN(ctid) 
    FROM user_contacts 
    GROUP BY user_id 
    HAVING COUNT(*) > 1
);

注意事项

为了实现多版本并发控制以及 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 行记录)

实际示例

以下是一些常见的 UPDATE 操作示例，帮助您更好地理解和使用 ProtonBase：

1. 基本更新操作

-- 更新员工薪资
UPDATE employees 
SET salary = 75000 
WHERE employee_id = 1001;
 
-- 更新员工部门和职位
UPDATE employees 
SET department_id = 3, job_title = 'Senior Developer' 
WHERE employee_id = 1002;
 
-- 批量更新部门名称
UPDATE departments 
SET department_name = 'Engineering Division' 
WHERE department_id = 1;

2. 使用表达式更新

-- 给所有员工涨薪10%
UPDATE employees 
SET salary = salary * 1.1;
 
-- 更新员工年龄（根据生日计算）
UPDATE employees 
SET age = EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date));
 
-- 更新最后修改时间和用户
UPDATE user_profiles 
SET last_modified = CURRENT_TIMESTAMP, 
    modified_by = 'admin' 
WHERE user_id = 12345;

3. 多表关联更新

-- 根据部门表更新员工的部门名称
UPDATE employees 
SET department_name = d.department_name
FROM departments d
WHERE employees.department_id = d.department_id;
 
-- 更新订单状态为已发货
UPDATE orders 
SET status = 'shipped', 
    shipped_date = CURRENT_DATE
FROM shipments s
WHERE orders.order_id = s.order_id 
AND s.ship_date IS NOT NULL;

注意事项

为了实现多版本并发控制以及 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 操作示例，帮助您更好地理解和使用 ProtonBase：

1. 数据同步示例

-- 同步产品数据
MERGE INTO products p
USING staging_products sp
ON p.product_id = sp.product_id
WHEN MATCHED THEN
    UPDATE SET 
        product_name = sp.product_name,
        price = sp.price,
        last_updated = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
    INSERT (product_id, product_name, price, created_date)
    VALUES (sp.product_id, sp.product_name, sp.price, CURRENT_TIMESTAMP);

2. 用户数据更新示例

-- 更新用户信息
MERGE INTO user_profiles up
USING updated_user_data uud
ON up.user_id = uud.user_id
WHEN MATCHED THEN
    UPDATE SET 
        email = uud.email,
        phone = uud.phone,
        last_login = uud.last_login
WHEN NOT MATCHED THEN
    INSERT (user_id, email, phone, registration_date)
    VALUES (uud.user_id, uud.email, uud.phone, CURRENT_TIMESTAMP);

有关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 操作示例，帮助您更好地理解和使用 ProtonBase：

1. 基本清空操作

-- 清空日志表
TRUNCATE TABLE system_logs;
 
-- 清空临时数据表
TRUNCATE TABLE temp_data;
 
-- 清空多个表
TRUNCATE TABLE session_data, user_sessions, login_attempts;

2. 重置自增序列

-- 清空表并重置自增ID
TRUNCATE TABLE users RESTART IDENTITY;
 
-- 清空多个表并重置自增ID
TRUNCATE TABLE orders, order_items RESTART IDENTITY;

注意事项

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

实际示例

以下是一些常见的视图操作示例，帮助您更好地理解和使用 ProtonBase：

1. 创建常用查询视图

-- 创建活跃员工视图
CREATE VIEW active_employees AS
SELECT employee_id, first_name, last_name, department, salary, hire_date
FROM employees
WHERE status = 'active';
 
-- 创建销售报告视图
CREATE VIEW sales_report AS
SELECT 
    p.product_name,
    c.customer_name,
    o.order_date,
    oi.quantity,
    oi.unit_price,
    (oi.quantity * oi.unit_price) AS total_amount
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days';

2. 创建聚合视图

-- 创建部门统计视图
CREATE VIEW department_stats AS
SELECT 
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary
FROM employees
WHERE status = 'active'
GROUP BY department;

3. 创建安全视图

-- 创建用户信息视图（不包含敏感信息）
CREATE VIEW user_public_info AS
SELECT user_id, username, first_name, last_name, registration_date
FROM users
WHERE status = 'active';

最佳实践

查询优化建议

使用索引：
- 为经常用于 WHERE、JOIN 和 ORDER BY 的列创建索引
- 避免在大表上对非索引列进行全表扫描
优化 WHERE 条件：
- 将选择性高的条件放在 WHERE 子句的前面
- 避免在 WHERE 子句中使用函数，这会导致索引失效
合理使用 LIMIT：
- 对于只需要部分结果的查询，使用 LIMIT 减少数据传输
- 结合 ORDER BY 使用 LIMIT 实现分页功能
**避免 SELECT ***：
- 只查询需要的列，减少网络传输和内存使用
- 明确指定列名可以提高查询的可读性和可维护性

更新和删除操作建议

使用事务：
- 对于重要的更新和删除操作，使用事务确保数据一致性
- 在执行批量操作前，先在测试环境中验证
备份重要数据：
- 在执行大规模更新或删除操作前，先备份相关数据
- 使用 SELECT 查询验证 WHERE 条件的正确性
分批处理：
- 对于大量数据的操作，考虑分批处理以减少锁竞争
- 使用 LIMIT 和 OFFSET 实现分批处理

视图使用建议

简化复杂查询：
- 使用视图封装复杂的 JOIN 和聚合查询
- 为常用的业务查询创建视图，提高开发效率
数据安全：
- 使用视图限制用户访问敏感数据
- 通过视图提供数据的只读访问
性能考虑：
- 避免在视图中使用复杂的计算和函数
- 定期检查视图查询的性能，必要时进行优化

通过遵循这些最佳实践，您可以更高效地使用 ProtonBase 的数据操作功能，提高查询性能并确保数据安全。