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

描述: 删除一个类型转换

语法:

DROP CAST (源类型 AS 目标类型) [ CASCADE | RESTRICT ]

● 命令: DROP DATABASE

描述: 删除一个数据库

语法:

DROP DATABASE 名字

● 命令: DROP FUNCTION

描述: 删除一个函数

语法:

DROP FUNCTION 名字 ( [ 类型 [, ...] ] ) [ CASCADE | RESTRICT ]

● 命令: DROP GROUP

描述: 删除一个用户组

语法:

DROP GROUP 名字

● 命令: DROP INDEX

描述: 删除一个索引

语法:

DROP INDEX 名字 [, ...] [ CASCADE | RESTRICT ]

● 命令: DROP RULE

描述: 删除一个重写规则

语法:

DROP RULE 名字 ON 关系 [ CASCADE | RESTRICT ]

● 命令: DROP SCHEMA

描述: 删除一个模式

语法:

DROP SCHEMA 名字 [, ...] [ CASCADE | RESTRICT ]

● 命令: DROP SEQUENCE

描述: 删除一个序列

语法:

DROP SEQUENCE 名字 [, ...] [ CASCADE | RESTRICT ]

● 命令: DROP TABLE

描述: 删除一个表

语法:

DROP TABLE 名字 [, ...] [ CASCADE | RESTRICT ]

● 命令: DROP TYPE

描述: 删除一个数据类型

语法:

DROP TYPE 名字 [, ...] [ CASCADE | RESTRICT ]

● 命令: DROP USER

描述: 删除一个数据库用户帐户

语法:

DROP USER 名字

● 命令: DROP VIEW

描述: 删除一个视图

语法:

DROP VIEW 名字 [, ...] [ CASCADE | RESTRICT ]

● 命令: END

描述: 提交当前事务

语法:

END [ WORK | TRANSACTION ]

● 命令: EXECUTE

描述: 执行一个准备好的语句

语法:

EXECUTE 规划名称 [ (参数 [, ...] ) ]

● 命令: EXPLAIN

描述: 显示语句的执行规划

语法:

EXPLAIN [ ANALYZE ] [ VERBOSE ] 语句

● 命令: FETCH

描述: 恢复来自一个使用游标查询的行

语法:

FETCH [ direction { FROM | IN } ] cursorname direction 可以为空或下面的一

FORWARD | FORWARD count | FORWARD ALL

● 命令: GRANT

描述: 定义访问权限

语法:

IGRAN { {SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER |

TRUNCATE } [,...] | ALL [PRIVILEGES] } ON [TABLE] tablename [, ...] TO

{rolename | PUBLIC} [, ...] [WITH GRANT OPTION] GRANT { {USAGE | SELECT |

UPDATE} [,...] | ALL [PRIVILEGES] } ON SEQUENCE sequencename [, ...] TO

{ rolename | PUBLIC } [, ...] [WITH GRANT OPTION] GRANT { {CREATE | CONNECT

| TEMPORARY | TEMP} [,...] | ALL [PRIVILEGES] } ON DATABASE dbname [, ...]

TO {rolename | PUBLIC} [, ...] [WITH GRANT OPTION] GRANT { EXECUTE | ALL

[PRIVILEGES] } ON FUNCTION funcname ( [ [argmode] [argname] argtype

[, ...] ] ) [, ...] TO {rolename | PUBLIC} [, ...] [WITH GRANT OPTION]

GRANT { USAGE | ALL [PRIVILEGES] } ON LANGUAGE langname [, ...] TO

{rolename | PUBLIC} [, ...] [WITH GRANT OPTION] GRANT { {CREATE | USAGE}

[,...] | ALL [PRIVILEGES] } ON SCHEMA schemaname [, ...] TO {rolename |

PUBLIC} [, ...] [WITH GRANT OPTION] GRANT { CREATE | ALL [PRIVILEGES] } ON

TABLESPACE tablespacename [, ...] TO {rolename | PUBLIC} [, ...] [WITH

GRANT OPTION] GRANT parent_role [, ...] TO member_role [, ...] [WITH ADMIN

OPTION] GRANT { SELECT | INSERT | ALL [PRIVILEGES] } ON PROTOCOL

protocolname TO username

● 命令: INSERT

描述: 在一个表中插入记录

语法:

INSERT INTO 表名 [ ( 字段 [, ...] ) ] { DEFAULT VALUES | VALUES ( { 表达式 |

DEFAULT } [, ...] ) | 子查询 }

● 命令: LOCK

描述: 锁定一个表

语法:

LOCK [ TABLE ] 名字 [, ...] [ IN lockmode MODE ] [ NOWAIT ]

lockmode 可以是下面的一种:

SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE

● 命令: PREPARE

描述: 为执行准备一条语句

语法:

PREPARE 规划名称 [ (数据类型 [, ...] ) ] AS 语句

● 命令: REINDEX

描述: 重建索引

语法:

REINDEX { DATABASE | TABLE | INDEX } 名字 [ FORCE ]

● 命令: RELEASE SAVEPOINT

描述: 删除一个以前定义的 savepoint

语法:

RELEASE [ SAVEPOINT ] savepoint_name

● 命令: RESET

描述: 恢复运行时参数值为默认值

语法:

RESET 名字

RESET ALL

命令: REVOKE 描述: 删除访问权限语法:

REVOKE [GRANT OPTION FOR] { {SELECT | INSERT | UPDATE | DELETE | REFERENCES

| TRIGGER | TRUNCATE } [,...] | ALL [PRIVILEGES] } ON [TABLE] tablename

[, ...] FROM {rolename | PUBLIC} [, ...] [CASCADE | RESTRICT] REVOKE [GRANT

OPTION FOR] { {USAGE | SELECT | UPDATE} [,...] | ALL [PRIVILEGES] } ON

SEQUENCE sequencename [, ...] FROM { rolename | PUBLIC } [, ...] [CASCADE |

RESTRICT] REVOKE [GRANT OPTION FOR] { {CREATE | CONNECT | TEMPORARY | TEMP}

[,...] | ALL [PRIVILEGES] } ON DATABASE dbname [, ...] FROM {rolename |

PUBLIC} [, ...] [CASCADE | RESTRICT] REVOKE [GRANT OPTION FOR] {EXECUTE |

ALL [PRIVILEGES]} ON FUNCTION funcname ( [[argmode] [argname] argtype

[, ...]] ) [, ...] FROM {rolename | PUBLIC} [, ...] [CASCADE | RESTRICT]

REVOKE [GRANT OPTION FOR] {USAGE | ALL [PRIVILEGES]} ON LANGUAGE langname

[, ...] FROM {rolename | PUBLIC} [, ...] [ CASCADE | RESTRICT ] REVOKE

[GRANT OPTION FOR] { {CREATE | USAGE} [,...] | ALL [PRIVILEGES] } ON SCHEMA

schemaname [, ...] FROM {rolename | PUBLIC} [, ...] [CASCADE | RESTRICT]

REVOKE [GRANT OPTION FOR] { CREATE | ALL [PRIVILEGES] } ON TABLESPACE

tablespacename [, ...] FROM { rolename | PUBLIC } [, ...] [CASCADE |

RESTRICT] REVOKE [ADMIN OPTION FOR] parent_role [, ...] FROM member_role

[, ...] [CASCADE | RESTRICT]

● 命令: ROLLBACK

描述: 终止当前事务

语法:

ROLLBACK [ WORK | TRANSACTION ]

● 命令: ROLLBACK TO SAVEPOINT

描述: 回滚到一个 savepoint

语法:

ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name

● 命令: SAVEPOINT

描述: 在当前事物中定义一个新的 savepoint

语法:

SAVEPOINT savepoint_name

● 命令: SELECT

描述: 从表或视图中查询记录

语法:

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] [, ...]] [LIMIT {count | ALL}]

[OFFSET start] [FOR {UPDATE | SHARE} [OF table_name [, ...]] [NOWAIT]

[...]] where grouping_element can be one of: () expression ROLLUP

(expression [,...]) CUBE (expression [,...]) GROUPING SETS

((grouping_element [, ...])) where window_specification can be:

[window_name] [PARTITION BY expression [, ...]] [ORDER BY expression [ASC |

DESC | USING operator] [, ...] [{RANGE | ROWS} { UNBOUNDED PRECEDING |

expression PRECEDING | CURRENT ROW | BETWEEN window_frame_bound AND

window_frame_bound }]] where window_frame_bound can be one of: UNBOUNDED

PRECEDING expression PRECEDING CURRENT ROW expression FOLLOWING UNBOUNDED

FOLLOWING where from_item can be one of: [ONLY] table_name [[AS] alias

[( column_alias [, ...] )]] (select) [AS] alias [( column_alias [, ...] )]

function_name ( [argument [, ...]] ) [AS] alias [( column_alias [, ...] |

column_definition [, ...] )] function_name ( [argument [, ...]] ) AS

( column_definition [, ...] ) from_item [NATURAL] join_type from_item [ON

join_condition | USING ( join_column [, ...] )]

● 命令: SELECT INTO

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

语法:

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] [, ...]] [LIMIT {count | ALL}]

[OFFSET start] [FOR {UPDATE | SHARE} [OF table_name [, ...]] [NOWAIT]

[...]]

● 命令: SET

描述: 改变一个运行时参数

语法:

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

DEFAULT}

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

最新活动

产品

解决方案

政务云

工业互联网

云市场

支持与服务

合作伙伴