11、常用SQL命令_开发指南_数据仓库 DW

帮助文档（华北一、二）

● 命令：ABORT

描述: 终止当前事务

语法:

ABORT [ WORK | TRANSACTION ]

● 命令: ALTER AGGREGATE

描述: 改变一个聚集函数的定义

语法：

ALTER AGGREGATE 名字 ( 类型 ) RENAME TO 新名字

● 命令: ALTER DATABASE

描述: 改变一个数据库

语法:

ALTER DATABASE 名字 SET 参数 { TO | = } { 值 | DEFAULT }

ALTER DATABASE 名字 RESET 参数

ALTER DATABASE 名字 RENAME TO 新名字

ALTER DATABASE 名字 OWNER TO 新属主

● 命令: ALTER FUNCTION

描述: 改变一个函数的定义

语法:

ALTER FUNCTION 名字 ( [ 类型 [, ...] ] ) RENAME TO 新名字

ALTER FUNCTION 名字 ( [ 类型 [, ...] ] ) OWNER TO 新属

● 命令: ALTER GROUP

描述: 改变一个用户组

语法:

ALTER GROUP 组名称 ADD USER 用户名称 [, ... ]

ALTER GROUP 组名称 DROP USER 用户名称 [, ... ]

ALTER GROUP 组名称 RENAME TO 新名称

● 命令: ALTER INDEX

描述: 改变一个索引的定义

语法:

ALTER INDEX name RENAME TO new_name

ALTER INDEX name SET TABLESPACE tablespace_name

ALTER INDEX name SET (storage_parameter = value )

ALTER INDEX name RESET (storage_parameter [, ... ])

● 命令: ALTER SCHEMA

描述: 改变一个模式的定义

语法:

ALTER SCHEMA 名字 RENAME TO 新名字

ALTER SCHEMA 名字 OWNER TO 新属主

● 命令: ALTER SEQUENCE

描述: 改变一个序列生成器的定义

语法:

ALTER SEQUENCE 名字 [ INCREMENT [ BY ] 递增 ] [ MINVALUE 最小值 | NO

MINVALUE ] [ MAXVALUE 最大值 | NO MAXVALUE ] [ RESTART [ WITH ] 开始 ]

[ CACHE 缓存 ] [ [ NO ] CYCLE ] [OWNED BY {表名.列名 | NONE}] ALTER

SEQUENCE name SET SCHEMA new_schema

● 命令: ALTER TYPE

描述: 改变一个类型的定义语法:

ALTER TYPE 名字 OWNER TO 新属主

● 命令: ALTER USER

描述: 改变一个数据库用户

语法:

ALTER USER name RENAME TO newname

ALTER USER name SET parameter { TO | = } { value | DEFAULT }

ALTER USER name RESET parameter

ALTER USER name [ [ WITH ] option [ ... ] ] where option can

be: CREATEDB | NOCREATEDB

● 命令: ANALYZE

描述: 收集关于数据库的统计信息

语法:

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

● 命令: BEGIN

描述: 开始一个事务块

语法:

BEGIN [ WORK | TRANSACTION ] [ 事务模式] [ READ WRITE | READ ONLY ]

事务模式为下面之一:

ISOLATION LEVEL { SERIALIZABLE | READ COMMITTED | READ UNCOMMITTED }

● 命令: CLOSE

描述: 关闭一个游标

语法: CLOSE 名字

● 命令: COMMIT

描述: 提交当前事务

语法: COMMIT [ WORK | TRANSACTION ]

● 命令: COPY

描述: 在一个文件和一个表之间拷贝数据

语法:

COPY table [(column [, ...])] FROM {'file' | STDIN} [ [WITH] [OIDS]

[HEADER] [DELIMITER [ AS ] 'delimiter'] [NULL [ AS ] 'null string'] [ESCAPE

[ AS ] 'escape' | 'OFF'] [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF'] [CSV [QUOTE

[ AS ] 'quote'] [FORCE NOT NULL column [, ...]] [FILL MISSING FIELDS] [[LOG

ERRORS [INTO error_table] [KEEP] SEGMENT REJECT LIMIT count [ROWS |

PERCENT] ] COPY {table [(column [, ...])] | (query)} TO {'file' | STDOUT}

[ [WITH] [OIDS] [HEADER] [DELIMITER [ AS ] 'delimiter'] [NULL [ AS ] 'null

string'] [ESCAPE [ AS ] 'escape' | 'OFF'] [CSV [QUOTE [ AS ] 'quote']

[FORCE QUOTE column [, ...]] ] [IGNORE EXTERNAL PARTITIONS ]

● 命令: CREATE AGGREGATE

描述: 定义一个新的聚集函数

语法:

CREATE [ORDERED] AGGREGATE name (input_data_type [ , ... ]) ( SFUNC =

sfunc, STYPE = state_data_type [, PREFUNC = prefunc] [, FINALFUNC = ffunc]

[, INITCOND = initial_condition] [, SORTOP = sort_operator] )

● 命令: CREATE CAST

描述: 定义一个新的类型转换

语法:

CREATE CAST (源类型 AS 目标类型) WITH FUNCTION 函数名 (参数类型) [ AS

ASSIGNMENT | AS IMPLICIT ] CREATE CAST (源类型 AS 目标类型) WITHOUT

FUNCTION [ AS ASSIGNMENT | AS IMPLICIT ]

● 命令: CREATE DATABASE

描述: 创建一个新的数据库

语法:

CREATE DATABASE 数据库名称 [ [ WITH ] [ OWNER [=] 数据库属

主 ] [ TEMPLATE [=] 模板 ] [ ENCODING [=] 编码 ] [ TABLESPACE [=] 表

空间 ] ]

● 命令: CREATE FUNCTION

描述: 定义一个新的函数

语法:

CREATE [ OR REPLACE ] FUNCTION 名字 ( [ [ 参数名字 ] 参数类型

[, ...] ] ) RETURNS 返回类型 { LANGUAGE 语言名称 | IMMUTABLE | STABLE |

VOLATILE | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT |

STRICT | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY

DEFINER | AS 'definition' | AS 'obj_file', 'link_symbol' } … [ WITH

( attribute [, ...] ) ]

● 命令: CREATE GROUP

描述: 定义一个新的用户组

语法:

CREATE GROUP 组名 [ [ WITH ] option [ ... ] ] option 可以为: SUPERUSER |

[ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'timestamp' |

IN ROLE rolename [, ...] | IN GROUP rolename [, ...] | ROLE rolename

[, ...] | ADMIN rolename [, ...] | USER rolename [, ...] | SYSID uid

● 命令: CREATE INDEX

描述: 定义一个新的索引

语法:

CREATE [ OR REPLACE ] RULE 名字 AS ON 事件 TO 表 [ WHERE 条件 ] DO

[ ALSO | INSTEAD ] { NOTHING | 命令 | ( 命令 ; 命令 ... ) }

● 命令: CREATE SCHEMA

描述: 定义一个新的模式

语法:

CREATE SCHEMA 模式名称 [ AUTHORIZATION 用户名称 ] [ 模式元素 [ ... ] ]

CREATE SCHEMA AUTHORIZATION 用户名称 [ 模式元素 [ ... ] ]

● 命令: CREATE SEQUENCE

描述: 定义一个新的序列生成器

语法:

CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT

[ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE

maxvalue | NO MAXVALUE ] [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ]

CYCLE ] [OWNED BY { table.column | NONE }]

● 命令: CREATE TABLE

描述: 定义一个新的表

语法:

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name

( { column_name data_type [ DEFAULT default_expr ] [ column_constraint

[ ... ] ] | table_constraint | LIKE parent_table [ { INCLUDING |

EXCLUDING } DEFAULTS ] } [, ... ] ) [ INHERITS ( parent_table

[, ... ] ) ] [ WITH OIDS | WITHOUT OIDS ] [ ON COMMIT { PRESERVE ROWS |

DELETE ROWS | DROP } ] [ TABLESPACE tablespace ] where column_constraint

is: [ CONSTRAINT constraint_name ] { NOT NULL | NULL | UNIQUE [ USING

INDEX TABLESPACE tablespace ] | PRIMARY KEY [ USING INDEX TABLESPACE tablespace

] | CHECK (expression) | REFERENCES reftable [ ( refcolumn ) ]

[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON

UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED |

INITIALLY IMMEDIATE ] and table_constraint is: [ CONSTRAINT

constraint_name ] { UNIQUE ( column_name [, ... ] ) [ USING INDEX

TABLESPACE tablespace ] | PRIMARY KEY ( column_name [, ... ] ) [ USING

INDEX TABLESPACE tablespace ] | CHECK ( expression ) | FOREIGN KEY

( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn

[, ... ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE

action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY

DEFERRED | INITIALLY IMMEDIATE ]

● 命令: CREATE TABLE AS

描述: 以一个查询的结果定义一个新的表

语法:

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE 表名字 [ (字段名字

[, ...] ) ] [ [ WITH | WITHOUT ] OIDS ] AS query

● 命令: CREATE TYPE

描述: 定义一个新的数据类型

语法:

CREATE TYPE name AS ( attribute_name data_type [, ... ] ) CREATE TYPE name

( INPUT = input_function, OUTPUT = output_function [, RECEIVE =

receive_function] [, SEND = send_function] [, INTERNALLENGTH =

{internallength | VARIABLE}] [, PASSEDBYVALUE] [, ALIGNMENT = alignment] [,

STORAGE = storage] [, DEFAULT = default] [, ELEMENT = element] [, DELIMITER

= delimiter] ) CREATE TYPE name

● 命令: CREATE USER

描述: 定义一个新的数据库用户帐户

语法:

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

NOLOGIN | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL

'timestamp' | IN ROLE rolename [, ...] | IN GROUP rolename [, ...] | ROLE

rolename [, ...] | ADMIN rolename [, ...] | USER rolename [, ...] | SYSID

uid | RESOURCE QUEUE queue_name

命令: CREATE VIEW 描述: 定义一个新的视图语法:

CREATE [OR REPLACE] [TEMP | TEMPORARY] VIEW name [ ( column_name

[, ...] ) ] AS query

● 命令: DECLARE

描述: 定义一个游标

语法:

DECLARE name [BINARY] [INSENSITIVE] [NO SCROLL] CURSOR [{WITH | WITHOUT}

HOLD] FOR query [FOR READ ONLY]

● 命令: DELETE

描述: 删除一个表的记录

语法:

DELETE FROM [ ONLY ] 表 [ WHERE 条件]

● 命令: DROP AGGREGATE

描述: 删除一个聚集函数

语法:

DROP AGGREGATE 名字 ( 类型 ) [ CASCADE | RESTRICT ]

● 命令: DROP CAST

描述: 删除一个类型转换

语法:

最新活动

产品

解决方案

政务云

工业互联网

云市场

支持与服务

合作伙伴