SQL语法概要

终止

终止当前事务

  1. ABORT [WORK | TRANSACTION]

更多信息参阅 ABORT

修改聚集函数

改变聚集函数的定义

  1. ALTER AGGREGATE name ( type [ , ... ] ) RENAME TO new_name
  2. ALTER AGGREGATE name ( type [ , ... ] ) OWNER TO new_owner
  3. ALTER AGGREGATE name ( type [ , ... ] ) SET SCHEMA new_schema

更多信息参阅 ALTER AGGREGATE

修改转换

修改转换的定义。

  1. ALTER CONVERSION name RENAME TO newname
  2. ALTER CONVERSION name OWNER TO newowner

更多信息参阅 ALTER CONVERSION

修改数据库

修改数据库属性

  1. ALTER DATABASE name [ WITH CONNECTION LIMIT connlimit ]
  2. ALTER DATABASE name SET parameter { TO | = } { value | DEFAULT }
  3. ALTER DATABASE name RESET parameter
  4. ALTER DATABASE name RENAME TO newname
  5. ALTER DATABASE name OWNER TO new_owner

更多信息参阅 ALTER DATABASE

修改域

改变域的定义

  1. ALTER DOMAIN name { SET DEFAULT expression | DROP DEFAULT }
  2. ALTER DOMAIN name { SET | DROP } NOT NULL
  3. ALTER DOMAIN name ADD domain_constraint
  4. ALTER DOMAIN name DROP CONSTRAINT constraint_name [RESTRICT | CASCADE]
  5. ALTER DOMAIN name OWNER TO new_owner
  6. ALTER DOMAIN name SET SCHEMA new_schema

更多信息请参阅 ALTER DOMAIN

修改扩展

改变在Greenplum数据库中注册的扩展的定义。

  1. ALTER EXTENSION name UPDATE [ TO new_version ]
  2. ALTER EXTENSION name SET SCHEMA new_schema
  3. ALTER EXTENSION name ADD member_object
  4. ALTER EXTENSION name DROP member_object
  5. 其中 member_object 是:
  6. ACCESS METHOD object_name |
  7. AGGREGATE aggregate_name ( aggregate_signature ) |
  8. CAST (source_type AS target_type) |
  9. COLLATION object_name |
  10. CONVERSION object_name |
  11. DOMAIN object_name |
  12. EVENT TRIGGER object_name |
  13. FOREIGN DATA WRAPPER object_name |
  14. FOREIGN TABLE object_name |
  15. FUNCTION function_name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) |
  16. MATERIALIZED VIEW object_name |
  17. OPERATOR operator_name (left_type, right_type) |
  18. OPERATOR CLASS object_name USING index_method |
  19. OPERATOR FAMILY object_name USING index_method |
  20. [ PROCEDURAL ] LANGUAGE object_name |
  21. SCHEMA object_name |
  22. SEQUENCE object_name |
  23. SERVER object_name |
  24. TABLE object_name |
  25. TEXT SEARCH CONFIGURATION object_name |
  26. TEXT SEARCH DICTIONARY object_name |
  27. TEXT SEARCH PARSER object_name |
  28. TEXT SEARCH TEMPLATE object_name |
  29. TRANSFORM FOR type_name LANGUAGE lang_name |
  30. TYPE object_name |
  31. VIEW object_name
  32. aggregate_signature 是:
  33. * | [ argmode ] [ argname ] argtype [ , ... ] |
  34. [ [ argmode ] [ argname ] argtype [ , ... ] ]
  35. ORDER BY [ argmode ] [ argname ] argtype [ , ... ]

更多信息参阅 ALTER EXTENSION

修改外部表

改变外部表的定义。

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

更多信息参阅 ALTER EXTERNAL TABLE

修改文件空间

改变文件空间的定义

  1. ALTER FILESPACE name RENAME TO newname
  2. ALTER FILESPACE name OWNER TO newowner

更多信息参阅 ALTER FILESPACE

修改函数

改变函数的定义

  1. ALTER FUNCTION name ( [ [argmode] [argname] argtype [, ...] ] )
  2. action [, ... ] [RESTRICT]
  3. ALTER FUNCTION name ( [ [argmode] [argname] argtype [, ...] ] )
  4. RENAME TO new_name
  5. ALTER FUNCTION name ( [ [argmode] [argname] argtype [, ...] ] )
  6. OWNER TO new_owner
  7. ALTER FUNCTION name ( [ [argmode] [argname] argtype [, ...] ] )
  8. SET SCHEMA new_schema

更多信息参阅 ALTER FUNCTION

修改组

改变角色名字或者成员信息

  1. ALTER GROUP groupname ADD USER username [, ... ]
  2. ALTER GROUP groupname DROP USER username [, ... ]
  3. ALTER GROUP groupname RENAME TO newname

更多信息参阅 ALTER GROUP

修改索引

改变索引的定义

  1. ALTER INDEX name RENAME TO new_name
  2. ALTER INDEX name SET TABLESPACE tablespace_name
  3. ALTER INDEX name SET ( FILLFACTOR = value )
  4. ALTER INDEX name RESET ( FILLFACTOR )

更多信息参阅 ALTER INDEX

修改语言

改变程序语言的名字

  1. ALTER LANGUAGE name RENAME TO newname
  2. ALTER LANGUAGE name OWNER TO new_owner

更多信息参阅 ALTER LANGUAGE

修改操作符

改变操作符的定义

  1. ALTER OPERATOR name ( {lefttype | NONE} , {righttype | NONE} )
  2. OWNER TO newowner

更多信息参阅 ALTER OPERATOR

修改操作符类

改变操作符类的定义

  1. ALTER OPERATOR CLASS name USING index_method RENAME TO newname
  2. ALTER OPERATOR CLASS name USING index_method OWNER TO newowner

更多信息参阅 ALTER OPERATOR CLASS

修改操作符族

修改操作符族的定义

  1. ALTER OPERATOR FAMILY name USING index_method ADD
  2. { OPERATOR strategy_number operator_name ( op_type, op_type ) [ RECHECK ]
  3. | FUNCTION support_number [ ( op_type [ , op_type ] ) ] funcname ( argument_type [, ...] )
  4. } [, ... ]
  5. ALTER OPERATOR FAMILY name USING index_method DROP
  6. { OPERATOR strategy_number ( op_type, op_type )
  7. | FUNCTION support_number [ ( op_type [ , op_type ] )
  8. } [, ... ]
  9. ALTER OPERATOR FAMILY name USING index_method RENAME TO newname
  10. ALTER OPERATOR FAMILY name USING index_method OWNER TO newowner

更多信息参阅 ALTER OPERATOR FAMILY

修改协议

修改协议的定义

  1. ALTER PROTOCOL name RENAME TO newname
  2. ALTER PROTOCOL name OWNER TO newowner

更多信息参阅 ALTER PROTOCOL

修改资源队列

修改资源队列的限制

  1. ALTER RESOURCE QUEUE name WITH ( queue_attribute=value [, ... ] )

更新信息参阅 ALTER RESOURCE QUEUE

修改角色

修改数据库角色(用户或组)。

  1. ALTER ROLE name RENAME TO newname
  2. ALTER ROLE name SET config_parameter {TO | =} {value | DEFAULT}
  3. ALTER ROLE name RESET config_parameter
  4. ALTER ROLE name RESOURCE QUEUE {queue_name | NONE}
  5. ALTER ROLE name [ [WITH] option [ ... ] ]

更多信息参阅 ALTER ROLE

修改模式

改变模式的定义

  1. ALTER SCHEMA name RENAME TO newname
  2. ALTER SCHEMA name OWNER TO newowner

更多信息参阅 ALTER SCHEMA

修改序列

改变序列生成器的定义

  1. ALTER SEQUENCE name [INCREMENT [ BY ] increment]
  2. [MINVALUE minvalue | NO MINVALUE]
  3. [MAXVALUE maxvalue | NO MAXVALUE]
  4. [RESTART [ WITH ] start]
  5. [CACHE cache] [[ NO ] CYCLE]
  6. [OWNED BY {table.column | NONE}]
  7. ALTER SEQUENCE name RENAME TO new_name
  8. ALTER SEQUENCE name SET SCHEMA new_schema

更多信息参阅 ALTER SEQUENCE

修改表

改变的表的定义

  1. ALTER TABLE [ONLY] name RENAME [COLUMN] column TO new_column
  2. ALTER TABLE name RENAME TO new_name
  3. ALTER TABLE name SET SCHEMA new_schema
  4. ALTER TABLE [ONLY] name SET
  5. DISTRIBUTED BY (column, [ ... ] )
  6. | DISTRIBUTED RANDOMLY
  7. | WITH (REORGANIZE=true|false)
  8. ALTER TABLE [ONLY] name action [, ... ]
  9. ALTER TABLE name
  10. [ ALTER PARTITION { partition_name | FOR (RANK(number))
  11. | FOR (value) } partition_action [...] ]
  12. partition_action

更多信息参阅 ALTER TABLE

修改表空间

改变表空间的定义。

  1. ALTER TABLESPACE name RENAME TO newname
  2. ALTER TABLESPACE name OWNER TO newowner

更多信息参阅 ALTER TABLESPACE

修改类型

改变数据类型的定义

  1. ALTER TYPE name
  2. OWNER TO new_owner | SET SCHEMA new_schema

更多信息参阅 ALTER TYPE

修改用户

修改数据库角色(用户)的定义。

  1. ALTER USER name RENAME TO newname
  2. ALTER USER name SET config_parameter {TO | =} {value | DEFAULT}
  3. ALTER USER name RESET config_parameter
  4. ALTER USER name [ [WITH] option [ ... ] ]

更新信息参阅 ALTER USER

修改视图

改变视图的定义

  1. ALTER VIEW name RENAME TO newname

更新信息参阅 ALTER VIEW

分析

收集关于数据库的数据

  1. ANALYZE [VERBOSE] [ROOTPARTITION [ALL] ]
  2. [table [ (column [, ...] ) ]]

更新信息参阅 ANALYZE

开始

启动事务块

  1. BEGIN [WORK | TRANSACTION] [transaction_mode]
  2. [READ ONLY | READ WRITE]

更多信息参阅 BEGIN

检查点

强制事务记录检查点

  1. CHECKPOINT

更多信息参阅 CHECKPOINT

关闭

关闭游标

  1. CLOSE cursor_name

更多信息参阅 CLOSE

集簇

根据索引对磁盘上的堆存储表进行物理重新排序。不是Greenplum数据库的推荐操作。

  1. CLUSTER indexname ON tablename
  2. CLUSTER tablename
  3. CLUSTER

更多信息参阅 CLUSTER

注释

定义或者修改对一个对象的注释。

  1. COMMENT ON
  2. { TABLE object_name |
  3. COLUMN table_name.column_name |
  4. AGGREGATE agg_name (agg_type [, ...]) |
  5. CAST (sourcetype AS targettype) |
  6. CONSTRAINT constraint_name ON table_name |
  7. CONVERSION object_name |
  8. DATABASE object_name |
  9. DOMAIN object_name |
  10. FILESPACE object_name |
  11. FUNCTION func_name ([[argmode] [argname] argtype [, ...]]) |
  12. INDEX object_name |
  13. LARGE OBJECT large_object_oid |
  14. OPERATOR op (leftoperand_type, rightoperand_type) |
  15. OPERATOR CLASS object_name USING index_method |
  16. [PROCEDURAL] LANGUAGE object_name |
  17. RESOURCE QUEUE object_name |
  18. ROLE object_name |
  19. RULE rule_name ON table_name |
  20. SCHEMA object_name |
  21. SEQUENCE object_name |
  22. TABLESPACE object_name |
  23. TRIGGER trigger_name ON table_name |
  24. TYPE object_name |
  25. VIEW object_name }
  26. IS 'text'

更多信息参阅 COMMENT

提交

提交当前事务

  1. COMMIT [WORK | TRANSACTION]

更多信息参阅 COMMIT

复制

在文件和表之间拷贝数据。

  1. COPY table [(column [, ...])] FROM {'file' | STDIN}
  2. [ [WITH]
  3. [BINARY]
  4. [OIDS]
  5. [HEADER]
  6. [DELIMITER [ AS ] 'delimiter']
  7. [NULL [ AS ] 'null string']
  8. [ESCAPE [ AS ] 'escape' | 'OFF']
  9. [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
  10. [CSV [QUOTE [ AS ] 'quote']
  11. [FORCE NOT NULL column [, ...]]
  12. [FILL MISSING FIELDS]
  13. [[LOG ERRORS]
  14. SEGMENT REJECT LIMIT count [ROWS | PERCENT] ]
  15. COPY {table [(column [, ...])] | (query)} TO {'file' | STDOUT}
  16. [ [WITH]
  17. [ON SEGMENT]
  18. [BINARY]
  19. [OIDS]
  20. [HEADER]
  21. [DELIMITER [ AS ] 'delimiter']
  22. [NULL [ AS ] 'null string']
  23. [ESCAPE [ AS ] 'escape' | 'OFF']
  24. [CSV [QUOTE [ AS ] 'quote']
  25. [FORCE QUOTE column [, ...]] ]
  26. [IGNORE EXTERNAL PARTITIONS ]

更多信息参阅 COPY

创建聚集函数

定义一个新的聚集函数

  1. CREATE [ORDERED] AGGREGATE name (input_data_type [ , ... ])
  2. ( SFUNC = sfunc,
  3. STYPE = state_data_type
  4. [, PREFUNC = prefunc]
  5. [, FINALFUNC = ffunc]
  6. [, INITCOND = initial_condition]
  7. [, SORTOP = sort_operator] )

更多信息参阅 CREATE AGGREGATE

创建投影

定义一个新的投影。

  1. CREATE CAST (sourcetype AS targettype)
  2. WITH FUNCTION funcname (argtypes)
  3. [AS ASSIGNMENT | AS IMPLICIT]
  4. CREATE CAST (sourcetype AS targettype) WITHOUT FUNCTION
  5. [AS ASSIGNMENT | AS IMPLICIT]

更多信息参阅 CREATE CAST

创建转换

定义一个新的编码转换。

  1. CREATE [DEFAULT] CONVERSION name FOR source_encoding TO
  2. dest_encoding FROM funcname

更多信息参阅 CREATE CONVERSION

创建数据库

创建一个信息的数据库。

  1. CREATE DATABASE name [ [WITH] [OWNER [=] dbowner]
  2. [TEMPLATE [=] template]
  3. [ENCODING [=] encoding]
  4. [TABLESPACE [=] tablespace]
  5. [CONNECTION LIMIT [=] connlimit ] ]

更多信息参阅 CREATE DATABASE

创建域

定义一个新的域。

  1. CREATE DOMAIN name [AS] data_type [DEFAULT expression]
  2. [CONSTRAINT constraint_name
  3. | NOT NULL | NULL
  4. | CHECK (expression) [...]]

更多信息参阅 CREATE DOMAIN

创建扩展

在Greenplum数据库中注册一个扩展。

  1. CREATE EXTENSION [ IF NOT EXISTS ] extension_name
  2. [ WITH ] [ SCHEMA schema_name ]
  3. [ VERSION version ]
  4. [ FROM old_version ]
  5. [ CASCADE ]

更多信息参阅 CREATE EXTENSION

创建外部表

定义一张外部表

  1. CREATE [READABLE] EXTERNAL TABLE table_name
  2. ( column_name data_type [, ...] | LIKE other_table )
  3. LOCATION ('file://seghost[:port]/path/file' [, ...])
  4. | ('gpfdist://filehost[:port]/file_pattern[#transform=trans_name]'
  5. [, ...]
  6. | ('gpfdists://filehost[:port]/file_pattern[#transform=trans_name]'
  7. [, ...])
  8. | ('gphdfs://hdfs_host[:port]/path/file')
  9. | ('s3://S3_endpoint[:port]/bucket_name/[S3_prefix]
  10. [region=S3-region]
  11. [config=config_file]')
  12. [ON MASTER]
  13. FORMAT 'TEXT'
  14. [( [HEADER]
  15. [DELIMITER [AS] 'delimiter' | 'OFF']
  16. [NULL [AS] 'null string']
  17. [ESCAPE [AS] 'escape' | 'OFF']
  18. [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
  19. [FILL MISSING FIELDS] )]
  20. | 'CSV'
  21. [( [HEADER]
  22. [QUOTE [AS] 'quote']
  23. [DELIMITER [AS] 'delimiter']
  24. [NULL [AS] 'null string']
  25. [FORCE NOT NULL column [, ...]]
  26. [ESCAPE [AS] 'escape']
  27. [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
  28. [FILL MISSING FIELDS] )]
  29. | 'AVRO'
  30. | 'PARQUET'
  31. | 'CUSTOM' (Formatter=<formatter_specifications>)
  32. [ ENCODING 'encoding' ]
  33. [ [LOG ERRORS] SEGMENT REJECT LIMIT count
  34. [ROWS | PERCENT] ]
  35. CREATE [READABLE] EXTERNAL WEB TABLE table_name
  36. ( column_name data_type [, ...] | LIKE other_table )
  37. LOCATION ('http://webhost[:port]/path/file' [, ...])
  38. | EXECUTE 'command' [ON ALL
  39. | MASTER
  40. | number_of_segments
  41. | HOST ['segment_hostname']
  42. | SEGMENT segment_id ]
  43. FORMAT 'TEXT'
  44. [( [HEADER]
  45. [DELIMITER [AS] 'delimiter' | 'OFF']
  46. [NULL [AS] 'null string']
  47. [ESCAPE [AS] 'escape' | 'OFF']
  48. [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
  49. [FILL MISSING FIELDS] )]
  50. | 'CSV'
  51. [( [HEADER]
  52. [QUOTE [AS] 'quote']
  53. [DELIMITER [AS] 'delimiter']
  54. [NULL [AS] 'null string']
  55. [FORCE NOT NULL column [, ...]]
  56. [ESCAPE [AS] 'escape']
  57. [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
  58. [FILL MISSING FIELDS] )]
  59. | 'CUSTOM' (Formatter=<formatter specifications>)
  60. [ ENCODING 'encoding' ]
  61. [ [LOG ERRORS] SEGMENT REJECT LIMIT count
  62. [ROWS | PERCENT] ]
  63. CREATE WRITABLE EXTERNAL TABLE table_name
  64. ( column_name data_type [, ...] | LIKE other_table )
  65. LOCATION('gpfdist://outputhost[:port]/filename[#transform=trans_name]'
  66. [, ...])
  67. | ('gpfdists://outputhost[:port]/file_pattern[#transform=trans_name]'
  68. [, ...])
  69. | ('gphdfs://hdfs_host[:port]/path')
  70. FORMAT 'TEXT'
  71. [( [DELIMITER [AS] 'delimiter']
  72. [NULL [AS] 'null string']
  73. [ESCAPE [AS] 'escape' | 'OFF'] )]
  74. | 'CSV'
  75. [([QUOTE [AS] 'quote']
  76. [DELIMITER [AS] 'delimiter']
  77. [NULL [AS] 'null string']
  78. [FORCE QUOTE column [, ...]] ]
  79. [ESCAPE [AS] 'escape'] )]
  80. | 'AVRO'
  81. | 'PARQUET'
  82. | 'CUSTOM' (Formatter=<formatter specifications>)
  83. [ ENCODING 'write_encoding' ]
  84. [ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ]
  85. CREATE WRITABLE EXTERNAL TABLE table_name
  86. ( column_name data_type [, ...] | LIKE other_table )
  87. LOCATION('s3://S3_endpoint[:port]/bucket_name/[S3_prefix]
  88. [region=S3-region]
  89. [config=config_file]')
  90. [ON MASTER]
  91. FORMAT 'TEXT'
  92. [( [DELIMITER [AS] 'delimiter']
  93. [NULL [AS] 'null string']
  94. [ESCAPE [AS] 'escape' | 'OFF'] )]
  95. | 'CSV'
  96. [([QUOTE [AS] 'quote']
  97. [DELIMITER [AS] 'delimiter']
  98. [NULL [AS] 'null string']
  99. [FORCE QUOTE column [, ...]] ]
  100. [ESCAPE [AS] 'escape'] )]
  101. CREATE WRITABLE EXTERNAL WEB TABLE table_name
  102. ( column_name data_type [, ...] | LIKE other_table )
  103. EXECUTE 'command' [ON ALL]
  104. FORMAT 'TEXT'
  105. [( [DELIMITER [AS] 'delimiter']
  106. [NULL [AS] 'null string']
  107. [ESCAPE [AS] 'escape' | 'OFF'] )]
  108. | 'CSV'
  109. [([QUOTE [AS] 'quote']
  110. [DELIMITER [AS] 'delimiter']
  111. [NULL [AS] 'null string']
  112. [FORCE QUOTE column [, ...]] ]
  113. [ESCAPE [AS] 'escape'] )]
  114. | 'CUSTOM' (Formatter=<formatter specifications>)
  115. [ ENCODING 'write_encoding' ]
  116. [ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ]

更多信息参阅 CREATE EXTERNAL TABLE

创建函数

定义一个新的函数

  1. CREATE [OR REPLACE] FUNCTION name
  2. ( [ [argmode] [argname] argtype [ { DEFAULT | = } defexpr ] [, ...] ] )
  3. [ RETURNS { [ SETOF ] rettype
  4. | TABLE ([{ argname argtype | LIKE other table }
  5. [, ...]])
  6. } ]
  7. { LANGUAGE langname
  8. | IMMUTABLE | STABLE | VOLATILE
  9. | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
  10. | [EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINE
  11. | COST execution_cost
  12. | SET configuration_parameter { TO value | = value | FROM CURRENT }
  13. | AS 'definition'
  14. | AS 'obj_file', 'link_symbol' } ...
  15. [ WITH ({ DESCRIBE = describe_function
  16. } [, ...] ) ]

更多信息参阅 CREATE FUNCTION

创建组

定义一个新的数据库角色。

  1. CREATE GROUP name [ [WITH] option [ ... ] ]

更多信息参阅 CREATE GROUP

创建索引

定义一个新的索引。

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

更多信息参阅 CREATE INDEX

创建语言

定义一个新的程序语言。

  1. CREATE [PROCEDURAL] LANGUAGE name
  2. CREATE [TRUSTED] [PROCEDURAL] LANGUAGE name
  3. HANDLER call_handler [ INLINE inline_handler ] [VALIDATOR valfunction]

更多信息参阅 CREATE LANGUAGE

创建操作符

定义一个新的操作符。

  1. CREATE OPERATOR name (
  2. PROCEDURE = funcname
  3. [, LEFTARG = lefttype] [, RIGHTARG = righttype]
  4. [, COMMUTATOR = com_op] [, NEGATOR = neg_op]
  5. [, RESTRICT = res_proc] [, JOIN = join_proc]
  6. [, HASHES] [, MERGES]
  7. [, SORT1 = left_sort_op] [, SORT2 = right_sort_op]
  8. [, LTCMP = less_than_op] [, GTCMP = greater_than_op] )

更多信息参阅 CREATE OPERATOR

创建操作符类

定义一个新的操作符类

  1. CREATE OPERATOR CLASS name [DEFAULT] FOR TYPE data_type
  2. USING index_method AS
  3. {
  4. OPERATOR strategy_number op_name [(op_type, op_type)] [RECHECK]
  5. | FUNCTION support_number funcname (argument_type [, ...] )
  6. | STORAGE storage_type
  7. } [, ... ]

更多信息参阅 CREATE OPERATOR CLASS

创建操作符族

定义一个新的操作符族

  1. CREATE OPERATOR FAMILY name USING index_method

更多信息参阅 CREATE OPERATOR FAMILY

创建协议

注册自定义数据访问协议,当定义Greenplum数据库外部表时可以指定。

  1. CREATE [TRUSTED] PROTOCOL name (
  2. [readfunc='read_call_handler'] [, writefunc='write_call_handler']
  3. [, validatorfunc='validate_handler' ])

更多信息参阅 CREATE PROTOCOL

创建资源队列

定义一个新的资源队列。

  1. CREATE RESOURCE QUEUE name WITH (queue_attribute=value [, ... ])

更多信息参阅 CREATE RESOURCE QUEUE

创建角色

定义一个新的数据库角色(用户或组)。

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

更多信息参阅 CREATE ROLE

创建规则

定义一个新的重写规则

  1. CREATE [OR REPLACE] RULE name AS ON event
  2. TO table [WHERE condition]
  3. DO [ALSO | INSTEAD] { NOTHING | command | (command; command
  4. ...) }

更多信息参阅 CREATE RULE

创建模式

定义一个新的模式。

  1. CREATE SCHEMA schema_name [AUTHORIZATION username]
  2. [schema_element [ ... ]]
  3. CREATE SCHEMA AUTHORIZATION rolename [schema_element [ ... ]]

更多信息参阅 CREATE SCHEMA

创建序列

定义一个新的序列生成器。

  1. CREATE [TEMPORARY | TEMP] SEQUENCE name
  2. [INCREMENT [BY] value]
  3. [MINVALUE minvalue | NO MINVALUE]
  4. [MAXVALUE maxvalue | NO MAXVALUE]
  5. [START [ WITH ] start]
  6. [CACHE cache]
  7. [[NO] CYCLE]
  8. [OWNED BY { table.column | NONE }]

更多信息参阅 CREATE SEQUENCE

创建表

定义一个新的表。

  1. CREATE [[GLOBAL | LOCAL] {TEMPORARY | TEMP}] TABLE table_name (
  2. [ { column_name data_type [ DEFAULT default_expr ]
  3. [column_constraint [ ... ]
  4. [ ENCODING ( storage_directive [,...] ) ]
  5. ]
  6. | table_constraint
  7. | LIKE other_table [{INCLUDING | EXCLUDING}
  8. {DEFAULTS | CONSTRAINTS}] ...}
  9. [, ... ] ]
  10. )
  11. [ INHERITS ( parent_table [, ... ] ) ]
  12. [ WITH ( storage_parameter=value [, ... ] )
  13. [ ON COMMIT {PRESERVE ROWS | DELETE ROWS | DROP} ]
  14. [ TABLESPACE tablespace ]
  15. [ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ]
  16. [ PARTITION BY partition_type (column)
  17. [ SUBPARTITION BY partition_type (column) ]
  18. [ SUBPARTITION TEMPLATE ( template_spec ) ]
  19. [...]
  20. ( partition_spec )
  21. | [ SUBPARTITION BY partition_type (column) ]
  22. [...]
  23. ( partition_spec
  24. [ ( subpartition_spec
  25. [(...)]
  26. ) ]
  27. )

更多信息参阅 CREATE TABLE

创建表如(AS)

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

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

更多信息参阅 CREATE TABLE AS

创建表空间

定义一个新的表空间。

  1. CREATE TABLESPACE tablespace_name [OWNER username]
  2. FILESPACE filespace_name

更多信息参阅 CREATE TABLESPACE

创建类型

定义一个新的类型。

  1. CREATE TYPE name AS ( attribute_name data_type [, ... ] )
  2. CREATE TYPE name AS ENUM ( 'label' [, ... ] )
  3. CREATE TYPE name (
  4. INPUT = input_function,
  5. OUTPUT = output_function
  6. [, RECEIVE = receive_function]
  7. [, SEND = send_function]
  8. [, TYPMOD_IN = type_modifier_input_function ]
  9. [, TYPMOD_OUT = type_modifier_output_function ]
  10. [, INTERNALLENGTH = {internallength | VARIABLE}]
  11. [, PASSEDBYVALUE]
  12. [, ALIGNMENT = alignment]
  13. [, STORAGE = storage]
  14. [, DEFAULT = default]
  15. [, ELEMENT = element]
  16. [, DELIMITER = delimiter] )
  17. CREATE TYPE name

更多信息参阅 CREATE TYPE

创建用户

定义一个默认带有 LOGIN 权限的数据库角色。

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

更多信息参阅 CREATE USER

创建视图

定义一个新的视图

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

更多信息参阅 CREATE VIEW

取消分配

取消分配一个已经准备(预编译)的语句

  1. DEALLOCATE [PREPARE] name

更多信息参阅 DEALLOCATE

声明

定义一个游标。

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

更多信息参阅 DECLARE

函数

从表中删除行。

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

更多信息参阅 DELETE

丢弃

丢弃会话的状态。

  1. DISCARD { ALL | PLANS | TEMPORARY | TEMP }

更多信息参阅 DISCARD

删除聚集函数

删除聚集函数。

  1. DROP AGGREGATE [IF EXISTS] name ( type [, ...] ) [CASCADE | RESTRICT]

更多信息参阅 DROP AGGREGATE

(做)DO

执行匿名代码块作为暂时匿名函数。

  1. DO [ LANGUAGE lang_name ] code

更多信息参阅 DO

删除投影

删除一个投影。

  1. DROP CAST [IF EXISTS] (sourcetype AS targettype) [CASCADE | RESTRICT]

更多信息参阅 DROP CAST

删除转换

删除一个转换。

  1. DROP CONVERSION [IF EXISTS] name [CASCADE | RESTRICT]

更多信息参阅 DROP CONVERSION

删除数据库

删除一个数据库。

  1. DROP DATABASE [IF EXISTS] name

更多信息参阅 DROP DATABASE

删除域

删除一个域。

  1. DROP DOMAIN [IF EXISTS] name [, ...] [CASCADE | RESTRICT]

更多信息参阅 DROP DOMAIN

删除扩展

从Greenplum数据库中删除一个扩展。

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

更多信息参阅 DROP EXTENSION

删除外部表

删除一个外部表定义。

  1. DROP EXTERNAL [WEB] TABLE [IF EXISTS] name [CASCADE | RESTRICT]

更多信息参阅 DROP EXTERNAL TABLE

删除文件空间

删除一个文件空间。

  1. DROP FILESPACE [IF EXISTS] filespacename

更多信息参阅 DROP FILESPACE

删除函数

删除一个函数。

  1. DROP FUNCTION [IF EXISTS] name ( [ [argmode] [argname] argtype
  2. [, ...] ] ) [CASCADE | RESTRICT]

更多信息参阅 DROP FUNCTION

删除组

删除一个数据库角色。

  1. DROP GROUP [IF EXISTS] name [, ...]

更多信息参阅 DROP GROUP

删除索引

删除一个索引。

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

更多信息参阅 DROP INDEX

删除语言

删除一个程序语言。

  1. DROP [PROCEDURAL] LANGUAGE [IF EXISTS] name [CASCADE | RESTRICT]

更多信息参阅 DROP LANGUAGE

删除操作符

删除一个操作符。

  1. DROP OPERATOR [IF EXISTS] name ( {lefttype | NONE} ,
  2. {righttype | NONE} ) [CASCADE | RESTRICT]

更多信息参阅 DROP OPERATOR

删除操作符类

删除一个操作符类。

  1. DROP OPERATOR CLASS [IF EXISTS] name USING index_method [CASCADE | RESTRICT]

更多信息参阅 DROP OPERATOR CLASS

删除操作符族

删除一个操作符族。

  1. DROP OPERATOR FAMILY [IF EXISTS] name USING index_method [CASCADE | RESTRICT]

更多信息参阅 DROP OPERATOR FAMILY

删除拥有(owned)

珊瑚数据库角色所拥有的数据库对象。

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

更多信息参阅 DROP OWNED

删除协议

从数据库中删除外部表的访问协议。

  1. DROP PROTOCOL [IF EXISTS] name

更多信息参阅 DROP PROTOCOL

删除资源队列

删除一个资源队列。

  1. DROP RESOURCE QUEUE queue_name

更多信息参阅 DROP RESOURCE QUEUE

删除角色

删除一个数据库角色。

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

更多信息参阅 DROP ROLE

删除规则

删除一个重写规则。

  1. DROP RULE [IF EXISTS] name ON relation [CASCADE | RESTRICT]

更多信息参阅 DROP RULE

删除模式

删除一个模式。

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

更多信息参阅 DROP SCHEMA

删除序列

删除一个序列

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

更多信息参阅 DROP SEQUENCE

删除表

删除一个表。

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

更多信息参阅 DROP TABLE

删除表空间

删除一个表空间

  1. DROP TABLESPACE [IF EXISTS] tablespacename

更多信息参阅 DROP TABLESPACE

删除类型

删除一个数据类型

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

更多信息参阅 DROP TYPE

删除用户

删除一个数据库角色

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

更多信息参阅 DROP USER

删除视图

删除一个视图

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

更多信息参阅 DROP VIEW

结束(END)

提交当前事务

  1. END [WORK | TRANSACTION]

更多信息参阅 END

执行

执行一个已经准备好的SQL语句。

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

更多信息参阅 EXECUTE

解释

展示语句的查询计划。

  1. EXPLAIN [ANALYZE] [VERBOSE] statement

更多信息参阅 EXPLAIN

提取

使用游标获取查询结果的行。

  1. FETCH [ forward_direction { FROM | IN } ] cursorname

更多信息参阅 FETCH

授权

定义一个访问权限

  1. GRANT { {SELECT | INSERT | UPDATE | DELETE | REFERENCES |
  2. TRIGGER | TRUNCATE } [,...] | ALL [PRIVILEGES] }
  3. ON [TABLE] tablename [, ...]
  4. TO {rolename | PUBLIC} [, ...] [WITH GRANT OPTION]
  5. GRANT { {USAGE | SELECT | UPDATE} [,...] | ALL [PRIVILEGES] }
  6. ON SEQUENCE sequencename [, ...]
  7. TO { rolename | PUBLIC } [, ...] [WITH GRANT OPTION]
  8. GRANT { {CREATE | CONNECT | TEMPORARY | TEMP} [,...] | ALL
  9. [PRIVILEGES] }
  10. ON DATABASE dbname [, ...]
  11. TO {rolename | PUBLIC} [, ...] [WITH GRANT OPTION]
  12. GRANT { EXECUTE | ALL [PRIVILEGES] }
  13. ON FUNCTION funcname ( [ [argmode] [argname] argtype [, ...]
  14. ] ) [, ...]
  15. TO {rolename | PUBLIC} [, ...] [WITH GRANT OPTION]
  16. GRANT { USAGE | ALL [PRIVILEGES] }
  17. ON LANGUAGE langname [, ...]
  18. TO {rolename | PUBLIC} [, ...] [WITH GRANT OPTION]
  19. GRANT { {CREATE | USAGE} [,...] | ALL [PRIVILEGES] }
  20. ON SCHEMA schemaname [, ...]
  21. TO {rolename | PUBLIC} [, ...] [WITH GRANT OPTION]
  22. GRANT { CREATE | ALL [PRIVILEGES] }
  23. ON TABLESPACE tablespacename [, ...]
  24. TO {rolename | PUBLIC} [, ...] [WITH GRANT OPTION]
  25. GRANT parent_role [, ...]
  26. TO member_role [, ...] [WITH ADMIN OPTION]
  27. GRANT { SELECT | INSERT | ALL [PRIVILEGES] }
  28. ON PROTOCOL protocolname
  29. TO username

更多信息参阅 GRANT

插入

在表中创建新的行

  1. INSERT INTO table [( column [, ...] )]
  2. {DEFAULT VALUES | VALUES ( {expression | DEFAULT} [, ...] )
  3. [, ...] | query}

更多信息参阅 INSERT

加载

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

  1. LOAD 'filename'

更多信息参阅 LOAD

锁住一张表

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

更多信息参阅 LOCK

移动

放置一个游标

  1. MOVE [ forward_direction {FROM | IN} ] cursorname

更多信息参阅 MOVE

准备

准备一个执行的语句

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

更多信息参阅 PREPARE

重新分配拥有

改变数据库角色所拥有的数据库对象的所有权。

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

更多信息参阅 REASSIGN OWNED

重新索引

重新构建索引

  1. REINDEX {INDEX | TABLE | DATABASE | SYSTEM} name

更多信息参阅 REINDEX

释放 SAVEPOINT

销毁一个之前定义过的savepoint。

  1. RELEASE [SAVEPOINT] savepoint_name

更多信息参阅 RELEASE SAVEPOINT

重置

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

  1. RESET configuration_parameter
  2. RESET ALL

更多信息参阅 RESET

撤销

撤销访问权限

  1. REVOKE [GRANT OPTION FOR] { {SELECT | INSERT | UPDATE | DELETE
  2. | REFERENCES | TRIGGER | TRUNCATE } [,...] | ALL [PRIVILEGES] }
  3. ON [TABLE] tablename [, ...]
  4. FROM {rolename | PUBLIC} [, ...]
  5. [CASCADE | RESTRICT]
  6. REVOKE [GRANT OPTION FOR] { {USAGE | SELECT | UPDATE} [,...]
  7. | ALL [PRIVILEGES] }
  8. ON SEQUENCE sequencename [, ...]
  9. FROM { rolename | PUBLIC } [, ...]
  10. [CASCADE | RESTRICT]
  11. REVOKE [GRANT OPTION FOR] { {CREATE | CONNECT
  12. | TEMPORARY | TEMP} [,...] | ALL [PRIVILEGES] }
  13. ON DATABASE dbname [, ...]
  14. FROM {rolename | PUBLIC} [, ...]
  15. [CASCADE | RESTRICT]
  16. REVOKE [GRANT OPTION FOR] {EXECUTE | ALL [PRIVILEGES]}
  17. ON FUNCTION funcname ( [[argmode] [argname] argtype
  18. [, ...]] ) [, ...]
  19. FROM {rolename | PUBLIC} [, ...]
  20. [CASCADE | RESTRICT]
  21. REVOKE [GRANT OPTION FOR] {USAGE | ALL [PRIVILEGES]}
  22. ON LANGUAGE langname [, ...]
  23. FROM {rolename | PUBLIC} [, ...]
  24. [ CASCADE | RESTRICT ]
  25. REVOKE [GRANT OPTION FOR] { {CREATE | USAGE} [,...]
  26. | ALL [PRIVILEGES] }
  27. ON SCHEMA schemaname [, ...]
  28. FROM {rolename | PUBLIC} [, ...]
  29. [CASCADE | RESTRICT]
  30. REVOKE [GRANT OPTION FOR] { CREATE | ALL [PRIVILEGES] }
  31. ON TABLESPACE tablespacename [, ...]
  32. FROM { rolename | PUBLIC } [, ...]
  33. [CASCADE | RESTRICT]
  34. REVOKE [ADMIN OPTION FOR] parent_role [, ...]
  35. FROM member_role [, ...]
  36. [CASCADE | RESTRICT]

更多信息参阅 REVOKE

回滚

中止当前事务

  1. ROLLBACK [WORK | TRANSACTION]

更多信息参阅 ROLLBACK

回滚到 SAVEPOINT

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

  1. ROLLBACK [WORK | TRANSACTION] TO [SAVEPOINT] savepoint_name

更多信息参阅 ROLLBACK TO SAVEPOINT

SAVEPOINT

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

  1. SAVEPOINT savepoint_name

更多信息参阅 SAVEPOINT

选择(SELECT)

从表或者视图中检索行。

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

更多信息参阅 SELECT

选择到(SELECT INTO)

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

  1. [ WITH with_query [, ...] ]
  2. SELECT [ALL | DISTINCT [ON ( expression [, ...] )]]
  3. * | expression [AS output_name] [, ...]
  4. INTO [TEMPORARY | TEMP] [TABLE] new_table
  5. [FROM from_item [, ...]]
  6. [WHERE condition]
  7. [GROUP BY expression [, ...]]
  8. [HAVING condition [, ...]]
  9. [{UNION | INTERSECT | EXCEPT} [ALL] select]
  10. [ORDER BY expression [ASC | DESC | USING operator] [NULLS {FIRST | LAST}] [, ...]]
  11. [LIMIT {count | ALL}]
  12. [OFFSET start]
  13. [FOR {UPDATE | SHARE} [OF table_name [, ...]] [NOWAIT]
  14. [...]]

更多信息参阅 SELECT INTO

设置

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

  1. SET [SESSION | LOCAL] configuration_parameter {TO | =} value |
  2. 'value' | DEFAULT}
  3. SET [SESSION | LOCAL] TIME ZONE {timezone | LOCAL | DEFAULT}

更多信息参阅 SET

设置角色

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

  1. SET [SESSION | LOCAL] ROLE rolename
  2. SET [SESSION | LOCAL] ROLE NONE
  3. RESET ROLE

更多信息参阅 SET ROLE

设置会话授权

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

  1. SET [SESSION | LOCAL] SESSION AUTHORIZATION rolename
  2. SET [SESSION | LOCAL] SESSION AUTHORIZATION DEFAULT
  3. RESET SESSION AUTHORIZATION

更多信息参阅 SET SESSION AUTHORIZATION

设置事务(SET TRANSACTION)

设置当前事务的特征。

  1. SET TRANSACTION [transaction_mode] [READ ONLY | READ WRITE]
  2. SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode
  3. [READ ONLY | READ WRITE]

更多信息参阅 SET TRANSACTION

显示(SHOW)

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

  1. SHOW configuration_parameter
  2. SHOW ALL

更多信息参阅 SHOW

开始事务

开始一个事务块。

  1. START TRANSACTION [SERIALIZABLE | READ COMMITTED | READ UNCOMMITTED]
  2. [READ WRITE | READ ONLY]

更多信息参阅 START TRANSACTION

截断(TRUNCATE)

清空表的所有行。

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

更多信息参阅 TRUNCATE

更新

更新表的行。

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

更多信息参阅 UPDATE

清理

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

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

更多信息参阅 VACUUM

计算一组行。

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

更多信息参阅 VALUES

上级话题: SQL命令参考