openGauss高级特性:SQL语法（4）

CREATE USER

创建一个用户。

CREATE USER user_name [ [ WITH ] option [ ... ] ] [ ENCRYPTED | UNENCRYPTED ] { PASSWORD | IDENTIFIED BY } { 'password' [ EXPIRED ] | DISABLE };

where option can be:
{SYSADMIN | NOSYSADMIN}
    | {MONADMIN | NOMONADMIN}
    | {OPRADMIN | NOOPRADMIN}
    | {POLADMIN | NOPOLADMIN}
    | {AUDITADMIN | NOAUDITADMIN}
    | {CREATEDB | NOCREATEDB}
    | {USEFT | NOUSEFT}
    | {CREATEROLE | NOCREATEROLE}
    | {INHERIT | NOINHERIT}
    | {LOGIN | NOLOGIN}
    | {REPLICATION | NOREPLICATION}
    | {INDEPENDENT | NOINDEPENDENT}
    | {VCADMIN | NOVCADMIN}
    | {PERSISTENCE | NOPERSISTENCE}
    | CONNECTION LIMIT connlimit
    | VALID BEGIN 'timestamp'
    | VALID UNTIL 'timestamp'
    | RESOURCE POOL 'respool'
    | USER GROUP 'groupuser'
    | PERM SPACE 'spacelimit'
    | TEMP SPACE 'tmpspacelimit'
    | SPILL SPACE 'spillspacelimit'
    | NODE GROUP logic_cluster_name
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid
    | DEFAULT TABLESPACE tablespace_name
    | PROFILE DEFAULT
    | PROFILE profile_name
    | PGUSER

CREATE VIEW

创建一个视图。

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW view_name [ ( column_name [, ...] ) ]
    [ WITH ( {view_option_name [= view_option_value]} [, ... ] ) ]
    AS query;

CREATE WEAK PASSWORD DICTIONARY

向gs_global_config表中插入一个或者多个弱口令。

CREATE WEAK PASSWORD DICTIONARY
     [WITH VALUES] ( {'weak_password'} [, ...] );

CURSOR

定义一个游标，用于在一个大的查询里面检索少数几行数据。

CURSOR cursor_name
    [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
    FOR query ;

DEALLOCATE

用于删除前面编写的预备语句。如果用户没有明确删除一个预备语句，那么它将在会话结束的时候被删除。

DEALLOCATE [ PREPARE ] { name | ALL };

DECLARE

用于删除前面编写的预备语句。如果用户没有明确删除一个预备语句，那么它将在会话结束的时候被删除。

1. declare a cursor:
DECLARE cursor_name [ BINARY ] [ NO SCROLL ]
    CURSOR [ { WITH | WITHOUT } HOLD ] FOR query ;

2. start an anonymous block:
[DECLARE [declare_statements]]
BEGIN
execution_statements
END;
/

DELETE

从指定的表里删除满足WHERE子句的行。如果WHERE子句不存在，将删除表中所有行，结果只保留表结构。

[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE [/*+ plan_hint */] FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    [ USING using_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ] [ LIMIT row_count ]
    [ RETURNING { * | { output_expr [ [ AS ] output_name ] } [, ...] } ];

DO

执行匿名代码块。

DO [ LANGUAGE lang_name ] code;

DROP AUDIT POLICY

删除一个审计策略。

DROP AUDIT POLICY [IF EXISTS] policy_name;

DROP CLIENT MASTER KEY

删除一个客户端加密主密钥(CMK)。

DROP CLIENT MASTER KEY [ IF EXISTS ] client_master_key_name [, ...];

DROP COLUMN ENCRYPTION KEY

删除一个列加密密钥(cek)。

DROP COLUMN ENCRYPTION KEY [ IF EXISTS ] client_column_key_name [, ...];

DROP DATA SOURCE

删除一个Data Source对象。

DROP DATA SOURCE [IF EXISTS] src_name [CASCADE | RESTRICT];

DROP DATABASE

删除一个数据库。

DROP DATABASE [ IF EXISTS ] database_name;

DROP DIRECTORY

删除指定的directory对象。

DROP DIRECTORY [ IF EXISTS ] directory_name;

DROP EVENT TRIGGER

删除一个事件触发器。

DROP EVENT TRIGGER [ IF EXISTS ] name [ CASCADE | RESTRICT ];

DROP EXTENSION

删除一个扩展。

DROP EXTENSION [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ];

DROP FOREIGN TABLE

删除指定的外表。

DROP FOREIGN TABLE [ IF EXISTS ]
table_name [, ...] [ CASCADE | RESTRICT ];

DROP FUNCTION

删除一个已存在的函数。

DROP FUNCTION [ IF EXISTS ] function_name [ ( [ {[ argmode ] [ argname ] argtype} [, ...] ] ) [ CASCADE | RESTRICT ] ];

DROP GROUP

删除用户组。

DROP GROUP [ IF EXISTS ] group_name [, ...];

DROP INDEX

删除索引。

DROP INDEX [ IF EXISTS ]
index_name [, ...] [ CASCADE | RESTRICT ];

DROP MASKING POLICY

删除脱敏策略。

DROP MASKING POLICY [IF EXISTS] policy_name;

DROP MATERIALIZED VIEW

强制删除数据库中已有的物化视图。

DROP MATERIALIZED VIEW [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

DROP MODEL

删除一个已训练完成保存的模型对象。

DROP MODEL model_name;

DROP OPERATOR

openGauss暂不支持。

DROP OPERATOR [ IF EXISTS ] name ( { left_type | NONE } , { right_type | NONE } ) [ CASCADE | RESTRICT ]

DROP OWNED

删除一个数据库角色所拥有的数据库对象。

DROP OWNED BY name [, ...] [ CASCADE | RESTRICT ];

DROP PACKAGE

删除已存在的PACKAGE或者PACKAGE BODY。

DROP PACKAGE [ IF EXISTS ] package_name;

DROP PROCEDURE

删除已存在的存储过程。

DROP PROCEDURE [ IF EXISTS ] procedure_name;

DROP RESOURCE LABEL

删除资源标签。

DROP RESOURCE LABEL [ IF EXISTS ] policy_name[, ... ];

DROP RESOURCE POOL

删除一个资源池。

DROP RESOURCE POOL [ IF EXISTS ] pool_name;

DROP ROLE

删除指定的角色。

DROP ROLE [ IF EXISTS ] role_name [, ...];

DROP ROW LEVEL SECURITY POLICY

删除表上某个行访问控制策略。

DROP [ ROW LEVEL SECURITY ] POLICY [ IF EXISTS ] policy_name ON table_name [ CASCADE | RESTRICT ]

DROP SCHEMA

从数据库中删除模式。

DROP SCHEMA [ IF EXISTS ] schema_name [, ...] [ CASCADE | RESTRICT ];

DROP SEQUENCE

从当前数据库里删除序列。

DROP SEQUENCE [ IF EXISTS ] {[schema.]sequence_name} [, ...] [ CASCADE | RESTRICT ];

DROP SERVER

删除现有的一个数据服务器。

DROP SERVER [ IF EXISTS ] server_name [ { CASCADE | RESTRICT } ] ;

DROP SYNONYM

删除指定的SYNONYM对象。

DROP SYNONYM [ IF EXISTS ] synonym_name [ CASCADE | RESTRICT ];

DROP TABLE

删除指定的表。

DROP TABLE [ IF EXISTS ]
{[schema.]table_name} [, ...] [ CASCADE | RESTRICT ];

DROP TABLESPACE

删除一个表空间。

DROP TABLESPACE [ IF EXISTS ] tablespace_name;

DROP TEXT SEARCH CONFIGURATION

删除已有文本搜索配置。

DROP TEXT SEARCH CONFIGURATION [ IF EXISTS ] name [ CASCADE | RESTRICT ]

DROP TEXT SEARCH DICTIONARY

删除全文检索词典。

DROP TEXT SEARCH DICTIONARY [ IF EXISTS ] name [ CASCADE | RESTRICT ];

DROP TRIGGER

删除触发器。

DROP TRIGGER [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ]

DROP TYPE

删除一个用户定义的数据类型。

DROP TYPE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

DROP USER

删除用户，同时会删除同名的schema。

DROP USER [ IF EXISTS ] user_name [, ...] [ CASCADE | RESTRICT ];

DROP VIEW

数据库中强制删除已有的视图。

DROP VIEW [ IF EXISTS ] view_name [, ...] [ CASCADE | RESTRICT ];

DROP WEAK PASSWORD DICTIONARY

清空gs_global_config中的所有弱口令。

DROP WEAK PASSWORD DICTIONARY;

END

END可完成提交事务的功能，即提交事务的所有操作。

END [ WORK | TRANSACTION ]

EXECUTE

执行一个前面准备好的预备语句。因为一个预备语句只在会话的生命期里存在，那么预备语句必须是在当前会话的前些时候用PREPARE语句创建的。

EXECUTE name [ ( parameter [, ...] ) ];

EXECUTE DIRECT

在指定的节点上执行SQL语句。一般情况下，SQL语句的执行是由集群负载自动分配到合适的节点上，execute direct主要用于数据库维护和测试。

EXPLAIN [ (  option  [, ...] )  ] statement;
EXPLAIN  { [  { ANALYZE  | ANALYSE  }  ] [ VERBOSE  ]  | PERFORMANCE  } statement;

where option can be:
ANALYZE [ boolean ] |
    ANALYSE [ boolean ] |
    VERBOSE [ boolean ] |
    COSTS [ boolean ] |
    CPU [ boolean ] |
    DETAIL [ boolean ] |
    NODES [ boolean ] |
    NUM_NODES [ boolean ] |
    BUFFERS [ boolean ] |
    TIMING [ boolean ] |
    PLAN [ boolean ] |
    FORMAT { TEXT | XML | JSON | YAML }

openGauss=# \h EXECUTE DIRECT
Command:     EXECUTE DIRECT
Description: launch queries directly to dedicated nodes
Syntax:
EXECUTE DIRECT ON ( nodename [, ... ] ) query;
EXECUTE DIRECT ON { COORDINATORS | DATANODES | ALL } query;

EXPLAIN

显示SQL语句的执行计划。

EXPLAIN [ (  option  [, ...] )  ] statement;
EXPLAIN  { [  { ANALYZE  | ANALYSE  }  ] [ VERBOSE  ]  | PERFORMANCE  } statement;

where option can be:
ANALYZE [ boolean ] |
    ANALYSE [ boolean ] |
    VERBOSE [ boolean ] |
    COSTS [ boolean ] |
    CPU [ boolean ] |
    DETAIL [ boolean ] |
    NODES [ boolean ] |
    NUM_NODES [ boolean ] |
    BUFFERS [ boolean ] |
    TIMING [ boolean ] |
    PLAN [ boolean ] |
    FORMAT { TEXT | XML | JSON | YAML }

FETCH

FETCH通过已创建的游标来检索数据。

FETCH [ direction { FROM | IN } ] cursor_name;

where direction can be:
NEXT
   | PRIOR
   | FIRST
   | LAST
   | ABSOLUTE count
   | RELATIVE count
   | count
   | ALL
   | FORWARD
   | FORWARD count
   | FORWARD ALL
   | BACKWARD
   | BACKWARD count
   | BACKWARD ALL

GRANT

对角色和用户进行授权操作。

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES |
    ALTER | DROP | COMMENT | INDEX | VACUUM } [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ];
GRANT { {{ SELECT | INSERT | UPDATE | REFERENCES | COMMENT } ( column_name [, ...] )}
    [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
    ON [ TABLE ] table_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ];
GRANT { { SELECT | UPDATE | USAGE | ALTER | DROP | COMMENT } [, ...]
    | ALL [ PRIVILEGES ] }
    ON { [ SEQUENCE ] sequence_name [, ...]
    | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ];
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP | ALTER | DROP | COMMENT }
    [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ];
GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON DOMAIN domain_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ];
GRANT { { USAGE | DROP } [, ...] | ALL [ PRIVILEGES ] }
    ON CLIENT_MASTER_KEY client_master_key
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ];
GRANT { { USAGE | DROP } [, ...] | ALL [ PRIVILEGES ] }
    ON COLUMN_ENCRYPTION_KEY column_encryption_key
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ];
GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN DATA WRAPPER fdw_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ];
GRANT { { USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
    ON FOREIGN SERVER server_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ];
GRANT { { EXECUTE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
    ON { FUNCTION {function_name ( [ {[ argmode ] [ arg_name ] arg_type} [, ...] ] )} [, ...]
         | ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ];
GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON LANGUAGE lang_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ];
GRANT { { CREATE | USAGE | COMPUTE | ALTER | DROP } [, ...] | ALL [ PRIVILEGES ] }
    ON NODE GROUP group_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ];
GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
    ON LARGE OBJECT loid [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ];
GRANT { { CREATE | USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schema_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ];
GRANT { { CREATE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
    ON TABLESPACE tablespace_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ];
GRANT { { USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
    ON TYPE type_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ];
GRANT { USAGE | ALL [PRIVILEGES] }
    ON DATA SOURCE src_name [, ...]
    TO { [GROUP] role_name | PUBLIC } [, ...]
    [WITH GRANT OPTION];
GRANT { { READ | WRITE } [, ...] | ALL [PRIVILEGES] }
    ON DIRECTORY directory_name [, ...]
    TO { [GROUP] role_name | PUBLIC } [, ...]
    [WITH GRANT OPTION];
GRANT { { EXECUTE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
    ON PACKAGE package_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ];
GRANT role_name [, ...]
   TO role_name [, ...]
   [ WITH ADMIN OPTION ];
GRANT ALL { PRIVILEGES | PRIVILEGE }
   TO role_name;