本文为您介绍Dataphin的计算引擎为AnalyticDB for PostgreSQL时,系统支持的SQL命令及语法。

CREATE DATABASE

创建数据库。
CREATE DATABASE name [ [WITH] [OWNER [=] dbowner]
                     [TEMPLATE [=] template]
                     [ENCODING [=] encoding]
                     [CONNECTION LIMIT [=] connlimit ] ]

CREATE INDEX

定义索引。

CREATE [UNIQUE] INDEX name ON table
       [USING btree|bitmap|gist]
       ( {column | (expression)} [opclass] [, ...] )
       [ WITH ( FILLFACTOR = value ) ]
       [TABLESPACE tablespace]
       [WHERE predicate]

更多信息请参见CREATE INDEX

CREATE ROLE

定义数据库角色(用户或组)。

CREATE ROLE name [[WITH] option [ ... ]]

更多信息请参见CREATE ROLE

CREATE SEQUENCE

定义序列生成器。

CREATE [TEMPORARY | TEMP] SEQUENCE name
       [INCREMENT [BY] value] 
       [MINVALUE minvalue | NO MINVALUE] 
       [MAXVALUE maxvalue | NO MAXVALUE] 
       [START [ WITH ] start] 
       [CACHE cache] 
       [[NO] CYCLE] 
       [OWNED BY { table.column | NONE }]

更多信息请参见CREATE SEQUENCE

CREATE USER

定义默认具有LOGIN权限的数据库角色。

CREATE USER name [ [WITH] option [ ... ] ]

更多信息请参见CREATE USER

CREATE VIEW

定义视图。

CREATE [OR REPLACE] [TEMP | TEMPORARY] VIEW name
       [ ( column_name [, ...] ) ]
       AS query

更多信息请参见CREATE VIEW

CREATE SCHEMA

定义SCHEMA。

CREATE SCHEMA schema_name [AUTHORIZATION username] 
   [schema_element [ ... ]]

CREATE SCHEMA AUTHORIZATION rolename [schema_element [ ... ]]

更多信息请参见CREATE SCHEMA

CREATE TABLE

定义表。

CREATE [[GLOBAL | LOCAL] {TEMPORARY | TEMP}] TABLE table_name ( 
[ { column_name data_type [ DEFAULT default_expr ] 
   [column_constraint [ ... ]
[ ENCODING ( storage_directive [,...] ) ]
] 
   | table_constraint
   | LIKE other_table [{INCLUDING | EXCLUDING} 
                      {DEFAULTS | CONSTRAINTS}] ...}
   [, ... ] ]
   )
   [ INHERITS ( parent_table [, ... ] ) ]
   [ WITH ( storage_parameter=value [, ... ] )
   [ ON COMMIT {PRESERVE ROWS | DELETE ROWS | DROP} ]
   [ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ]
   [ PARTITION BY partition_type (column)
       [ SUBPARTITION BY partition_type (column) ] 
          [ SUBPARTITION TEMPLATE ( template_spec ) ]
       [...]
    ( partition_spec ) 
        | [ SUBPARTITION BY partition_type (column) ]
          [...]
    ( partition_spec
      [ ( subpartition_spec
           [(...)] 
         ) ] 
    )

更多信息请参见CREATE TABLE

CREATE TABLE AS

从查询的结果中定义新的表。

CREATE [ [GLOBAL | LOCAL] {TEMPORARY | TEMP} ] TABLE table_name
   [(column_name [, ...] )]
   [ WITH ( storage_parameter=value [, ... ] ) ]
   [ON COMMIT {PRESERVE ROWS | DELETE ROWS | DROP}]
   [TABLESPACE tablespace]
   AS query
   [DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY]

更多信息请参见CREATE TABLE AS

SELECT

从表或视图中检索行。

[ WITH with_query [, ...] ]
SELECT [ALL | DISTINCT [ON (expression [, ...])]]
  * | expression [[AS] output_name] [, ...]
  [FROM from_item [, ...]]
  [WHERE condition]
  [GROUP BY grouping_element [, ...]]
  [HAVING condition [, ...]]
  [WINDOW window_name AS (window_specification)]
  [{UNION | INTERSECT | EXCEPT} [ALL] select]
  [ORDER BY expression [ASC | DESC | USING operator] [NULLS {FIRST | LAST}] [, ...]]
  [LIMIT {count | ALL}]
  [OFFSET start]
  [FOR {UPDATE | SHARE} [OF table_name [, ...]] [NOWAIT] [...]]

更多信息请参见SELECT

ALTER DATABASE

修改数据库属性。

ALTER DATABASE name [ WITH CONNECTION LIMIT connlimit ]
ALTER DATABASE name SET parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name RESET parameter
ALTER DATABASE name RENAME TO newname
ALTER DATABASE name OWNER TO new_owner

更多信息请参见ALTER DATABASE

ALTER EXTERNAL TABLE

改变外部表的定义。

ALTER EXTERNAL TABLE name RENAME [COLUMN] column TO new_column
ALTER EXTERNAL TABLE name RENAME TO new_name
ALTER EXTERNAL TABLE name SET SCHEMA new_schema
ALTER EXTERNAL TABLE name action [, ... ]

更多信息请参见ALTER EXTERNAL TABLE

ALTER INDEX

改变索引的定义。

ALTER INDEX name RENAME TO new_name
ALTER INDEX name SET TABLESPACE tablespace_name
ALTER INDEX name SET ( FILLFACTOR = value )
ALTER INDEX name RESET ( FILLFACTOR )

更多信息请参见ALTER INDEX

ALTER SCHEMA

改变模式的定义。

ALTER SCHEMA name RENAME TO newname

ALTER SCHEMA name OWNER TO newowner

更多信息请参见ALTER SCHEMA

ALTER TABLE

改变的表的定义。

ALTER TABLE [ONLY] name RENAME [COLUMN] column TO new_column

ALTER TABLE name RENAME TO new_name

ALTER TABLE name SET SCHEMA new_schema

ALTER TABLE [ONLY] name SET 
     DISTRIBUTED BY (column, [ ... ] ) 
   | DISTRIBUTED RANDOMLY 
   | WITH (REORGANIZE=true|false)

ALTER TABLE [ONLY] name action [, ... ]

ALTER TABLE name
   [ ALTER PARTITION { partition_name | FOR (RANK(number)) 
   | FOR (value) } partition_action [...] ] 
   partition_action

更多信息请参见ALTER TABLE

DROP DATABASE

删除数据库。

DROP DATABASE [IF EXISTS] name

更多信息请参见DROP DATABASE

DROP INDEX

删除索引。

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

更多信息请参见DROP INDEX

DROP SCHEMA

删除SCHEMA。

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

更多信息请参见DROP SCHEMA

DROP ROLE

删除数据库角色。

DROP ROLE [IF EXISTS] name [, ...]

更多信息请参见DROP ROLE

DROP SEQUENCE

删除序列。

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

更多信息请参见DROP SEQUENCE

DROP USER

删除数据库角色。

DROP USER [IF EXISTS] name [, ...]

更多信息请参见DROP USER

DROP VIEW

删除视图。

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

更多信息请参见DROP VIEW

DROP TABLE

删除表。

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

更多信息请参见DROP TABLE

BEGIN

启动事务块。

BEGIN [WORK | TRANSACTION] [transaction_mode]
      [READ ONLY | READ WRITE]

更多信息请参见BEGIN

CHECKPOINT

强制事务记录检查点。

CHECKPOINT

更多信息请参见CHECKPOINT

CLOSE

关闭游标。

CLOSE cursor_name

更多信息请参见CLOSE

CLUSTER

根据索引对磁盘上的堆存储表,重新排序。

CLUSTER indexname ON tablename

CLUSTER tablename

CLUSTER

更多信息请参见CLUSTER

COMMENT

定义或修改对象的注释。

COMMENT ON
{ TABLE object_name |
  COLUMN table_name.column_name |
  AGGREGATE agg_name (agg_type [, ...]) |
  CAST (sourcetype AS targettype) |
  CONSTRAINT constraint_name ON table_name |
  CONVERSION object_name |
  DATABASE object_name |
  DOMAIN object_name |
  FILESPACE object_name |
  FUNCTION func_name ([[argmode] [argname] argtype [, ...]]) |
  INDEX object_name |
  LARGE OBJECT large_object_oid |
  OPERATOR op (leftoperand_type, rightoperand_type) |
  OPERATOR CLASS object_name USING index_method |
  [PROCEDURAL] LANGUAGE object_name |
  RESOURCE QUEUE object_name |
  ROLE object_name |
  RULE rule_name ON table_name |
  SCHEMA object_name |
  SEQUENCE object_name |
  TABLESPACE object_name |
  TRIGGER trigger_name ON table_name |
  TYPE object_name |
  VIEW object_name } 
IS 'text'

更多信息请参见COMMENT

COMMIT

提交当前事务。

COMMIT [WORK | TRANSACTION]

更多信息请参见COMMIT

DEALLOCATE

取消分配预编译的语句。

DEALLOCATE [PREPARE] name

更多信息请参见DEALLOCATE

DECLARE

定义游标。

DECLARE name [BINARY] [INSENSITIVE] [NO SCROLL] CURSOR 
     [{WITH | WITHOUT} HOLD] 
     FOR query [FOR READ ONLY]

更多信息请参见DECLARE

DELETE

从表中删除行。

DELETE FROM [ONLY] table [[AS] alias]
      [USING usinglist]
      [WHERE condition | WHERE CURRENT OF cursor_name ]

更多信息请参见DELETE

END

提交当前事务。

END [WORK | TRANSACTION]

更多信息请参见END

EXPLAIN

展示语句的查询计划。

EXPLAIN [ANALYZE] [VERBOSE] statement

更多信息请参见EXPLAIN

FETCH

通过游标获取查询结果的行。

FETCH [ forward_direction { FROM | IN } ] cursorname

更多信息请参见FETCH

LOAD

加载或重新加载共享库文件。

LOAD 'filename'

更多信息请参见 LOAD

LOCK

锁住一张表。

LOCK [TABLE] name [, ...] [IN lockmode MODE] [NOWAIT]

更多信息请参见LOCK

MOVE

放置游标。

MOVE [ forward_direction {FROM | IN} ] cursorname

更多信息请参见MOVE

PREPARE

准备一个执行的语句。

PREPARE name [ (datatype [, ...] ) ] AS statement

更多信息请参见PREPARE

REASSIGN OWNED

修改数据库角色。

REASSIGN OWNED BY old_role [, ...] TO new_role

更多信息请参见REASSIGN OWNED

REINDEX

重新构建索引。

REINDEX {INDEX | TABLE | DATABASE | SYSTEM} name

更多信息请参见REINDEX

RELEASE SAVEPOINT

销毁定义过的SAVEPOINT。

RELEASE [SAVEPOINT] savepoint_name

更多信息请参见RELEASE SAVEPOINT

RESET

恢复系统配置参数的值为默认值。

RESET configuration_parameter

RESET ALL

更多信息请参见RESET

ROLLBACK

中止当前事务。

ROLLBACK [WORK | TRANSACTION]

更多信息请参见ROLLBACK

ROLLBACK TO SAVEPOINT

将当前事务回滚到某个SAVEPOINT。

ROLLBACK [WORK | TRANSACTION] TO [SAVEPOINT] savepoint_name

更多信息请参见ROLLBACK TO SAVEPOINT

SAVEPOINT

在当前事务定义一个新的SAVEPOINT。

SAVEPOINT savepoint_name

更多信息请参见SAVEPOINT

SELECT INTO

从查询结果中定义一个新的表。

[ WITH with_query [, ...] ]
SELECT [ALL | DISTINCT [ON ( expression [, ...] )]]
    * | expression [AS output_name] [, ...]
    INTO [TEMPORARY | TEMP] [TABLE] new_table
    [FROM from_item [, ...]]
    [WHERE condition]
    [GROUP BY expression [, ...]]
    [HAVING condition [, ...]]
    [{UNION | INTERSECT | EXCEPT} [ALL] select]
    [ORDER BY expression [ASC | DESC | USING operator] [NULLS {FIRST | LAST}] [, ...]]
    [LIMIT {count | ALL}]
    [OFFSET start]
    [FOR {UPDATE | SHARE} [OF table_name [, ...]] [NOWAIT] 
    [...]]

更多信息请参见SELECT INTO

SET

改变数据库配置参数的值。

SET [SESSION | LOCAL] configuration_parameter {TO | =} value | 
    'value' | DEFAULT}

SET [SESSION | LOCAL] TIME ZONE {timezone | LOCAL | DEFAULT}

更多信息请参见SET

SET ROLE

设置当前会话当前角色的标识符。

SET [SESSION | LOCAL] ROLE rolename

SET [SESSION | LOCAL] ROLE NONE

RESET ROLE

更多信息请参见SET ROLE

SET SESSION AUTHORIZATION

设置会话角色标识符和当前会话当前角色的标识符。

SET [SESSION | LOCAL] SESSION AUTHORIZATION rolename

SET [SESSION | LOCAL] SESSION AUTHORIZATION DEFAULT

RESET SESSION AUTHORIZATION

更多信息请参见SET SESSION AUTHORIZATION

SET TRANSACTION

设置当前事务的特征。

SET TRANSACTION [transaction_mode] [READ ONLY | READ WRITE]

SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode 
     [READ ONLY | READ WRITE]

更多信息请参见SET TRANSACTION

SHOW

显示当前系统配置参数的值。

SHOW configuration_parameter

SHOW ALL

更多信息请参见SHOW

START TRANSACTION

开始事务块。

START TRANSACTION [SERIALIZABLE | READ COMMITTED | READ UNCOMMITTED]
                  [READ WRITE | READ ONLY]

更多信息请参见START TRANSACTION

TRUNCATE

清空表的所有行。

TRUNCATE [TABLE] name [, ...] [CASCADE | RESTRICT]

更多信息请参见TRUNCATE

UPDATE

更新表的行。

UPDATE [ONLY] table [[AS] alias]
   SET {column = {expression | DEFAULT} |
   (column [, ...]) = ({expression | DEFAULT} [, ...])} [, ...]
   [FROM fromlist]
   [WHERE condition | WHERE CURRENT OF cursor_name ]

更多信息请参见UPDATE

VACUUM

垃圾收集和选择性分析数据库。

VACUUM [FULL] [FREEZE] [VERBOSE] [table]

VACUUM [FULL] [FREEZE] [VERBOSE] ANALYZE
              [table [(column [, ...] )]]

更多信息请参见VACUUM

VALUES

计算一组行。

VALUES ( expression [, ...] ) [, ...]
   [ORDER BY sort_expression [ASC | DESC | USING operator] [, ...]]
   [LIMIT {count | ALL}] [OFFSET start]

更多信息请参见VALUES