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;