greenplum基本操作及管理命令要点

第1章系统管理1.1 GP服务启停su - gpadmingpstart #正常启动gpstop #正常关闭gpstop -M fast #快速关闭gpstop –r #重启gpstop –u #重新加载配置文件1.2 登陆psql gpdbpsql -d gpdb -h gphostm -p 5432 -U gpadmin 1.3 查看segment配置select * from gp_segment_configuration;1.4 文件系统select * from pg_filespace_entry;1.5 列出所有数据库psql –l1.6 行表库最大尺寸一个数据库最大尺寸？无限制（已存在有32TB 的数据库）一个表的最大尺寸？32 TB一行记录的最大尺寸？1.6 TB一个字段的最大尺寸? 1 GB一个表里最大行数？无限制一个表里最大列数？250-1600 （与列类型有关）一个表里的最大索引数量？无限制当然，实际上没有真正的无限制，还是要受可用磁盘空间、可用内存/交换区的制约。

事实上，当这些数值变得异常地大时，系统性能也会受很大影响。

表的最大尺寸32 TB 不需要操作系统对大文件的支持。

大表用多个1 GB 的文件存储，因此文件系统尺寸的限制是不重要的。

如果缺省的块大小增长到32K ，最大的表尺寸和最大列数还可以增加到四倍1.7 存储空间一个Postgres 数据库（存储一个文本文件）所占用的空间最多可能需要相当于这个文本文件自身大小5倍的磁盘空间。

第2章Psql操作2.1 创建/删除用户创建用户：createuser [-a] [-A] [-d] [-D] [-e] [-P] [-h 主机名] [-p port] 用户名参数说明：[-a]：允许创建其他用户，相当于创建一个超级用户；[-A]：不允许此用户创建其他用户；[-d]：允许此用户创建数据库；[-D]：不允许此用户创建数据库；[-e]：将执行过程显示到Shell上；[-P]：创建用户时，同时设置密码；[-h 主机名]：为某个主机上的Postgres创建用户；[-p port]：与-h参数一同使用，指定主机的端口。

createuser -h 172.28.18.51 -p 5000 -D -A -e testuser创建超级用户：createuser -P -d -a -e testuser删除用户：命令：dropuser [-i] [-h] [-p] [-e] 用户名参数说明：[ -i]：删除用户前，要求确认；[-h 主机名]：删除某个主机上的Postgres用户；[-p port]：与-h参数一同使用，指定主机的端口；[-e]：将执行过程显示到Shell上。

2.2 创建数据库createdb -p 5432 -e -U gpadmin mydb2.3 运行建库脚本psql -p 5432 -U gpadmin -d mydb -f ./mysql.sql 2.4 表描述/d+ <tablename>2.5 执行sql文件psql gpdbname –f yoursqlfile.sql或者psql登陆后执行\i yoursqlfile.sq2.6 返回查询头几行/随机几行SELECT colsFROM tabORDER BY random()LIMIT 1 ;2.7 更改字段类型ALTER TABLE ALTER COLUMN TYPE第3章数据导入导出3.1 GPload工具编写load.ymlverSION: 1.0.0.1USER: gpadminPORT: 5432GPLOAD:INPUT:- SOURCE:FILE:- /opt/gpadmin/zhonghong/testload.txt- COLUMNS:- msisdn: text- blank1: text- blank2: text- uaText: text- FORMAT: text- DELIMITER: '|'OUTPUT:- TABLE: ua_in- MODE: INSERTSQL:执行命令：gpload -f load.yml -U gpadmin -d uainfo3.2 外部表CREATE EXTERNAL TABLE ext_expenses ( name text,date date, amount float4, category text, desc text ) LOCATION (‘gpfdist://etlhost:8081/*’,‘gpfdist://etlhost1:8081/*’)FORMAT ‘TEXT’ (DELIMITER ‘,’);装载数据：1-insert方式INSERT INTO expenses_travelSELECT * from ext_expenses where category=’travel’;2-create table…as …方式CREATE TABLE expenses AS SELECT * from ext_expenses;3.3 Copy命令Copy命令装载数据并不是并行装载，而且所装载的数据文件必须放在master服务器上，例如：COPY country FROM ‘/data/gpdb/country_data’WITH DELIMITER ‘|’ LOG ERRORS INTO err_countrySEGMENT REJECT LIMIT 10 ROWS;第4章附录——命令大全输入: /copyright 获取发布信息/h 获取SQL 命令的帮助/? 获取psql 命令的帮助/g 或者以分号作为执行查询的结尾/q 退出4.1 一般选项/c[onnect] [数据库名|- [用户名称]]联接到新的数据库(当前为"test")/cd [目录名] 改变当前的工作目录/copyright 显示PostgreSQL 用法和发布信息/encoding [编码]显示或设置客户端编码/h [名字] SQL 命令的语法帮助, 用* 可以看所有命令的帮助/q 退出psql/set [名字[值]]设置内部变量, 如果没有参数就列出所有/timing 查询计时开关切换(目前是关闭)/unset 名字取消(删除)内部变量/! [命令] 在shell 里执行命令或者开始一个交互的shell4.2 查询缓冲区选项/e [文件名] 用一个外部编辑器编辑当前查询缓冲区或者文件/g [文件名] 向服务器发送SQL 命令(并且把结果写到文件或者|管道) /p 显示当前查询缓冲区的内容/r 重置(清理) 查询缓冲区/s [文件名] 打印历史或者将其保存到文件/w [文件名] 将查询缓冲区写出到文件4.3 输入/输出选项/echo [字串] 向标准输出写出文本/i 文件名执行来自文件的命令/o [文件名] 向文件或者|管道发送所有查询结果/qecho [字串]向查询输出流写出文本(见/o)4.4 信息选项/d [名字] 描述表, 索引, 序列, 或者视图/d{t|i|s|v|S} [模式] (加"+" 获取更多信息)列出表/索引/序列/视图/系统表/da [模式] 列出聚集函数/db [模式] 列出表空间(加"+" 获取更多的信息)/dc [模式] 列出编码转换/dC 列出类型转换/dd [模式] 显示目标的注释/dD [模式] 列出域/df [模式] 列出函数(加"+" 获取更多的信息)/dg [模式] 列出组/dn [模式] 列出模式(加"+" 获取更多的信息)/do [名字] 列出操作符/dl 列出大对象, 和/lo_list 一样/dp [模式] 列出表, 视图, 序列的访问权限/dT [模式] 列出数据类型(加"+" 获取更多的信息)/du [模式] 列出用户/l 列出所有数据库(加"+" 获取更多的信息)/z [模式] 列出表, 视图, 序列的访问权限(和/dp 一样)4.5 格式选项/a 在非对齐和对齐的输出模式之间切换/C [字串] 设置表标题, 如果参数空则取消标题/f [字串] 为非对齐查询输出显示或设置域分隔符/H 在HTML 输出模式之间切换(当前是关闭)/pset 变量[值]设置表的输出选项(变量:= {foramt|border|expanded|fieldsep|null|recordsep|tuples_only|title|tableattr|pager})/t 只显示行(当前是关闭)/T [字串] 设置HTML <表> 标记属性, 如果没有参数就取消设置/x 在扩展输出之间切换(目前是关闭)4.6 拷贝, 大对象选项/copy ... 执行SQL COPY, 数据流指向客户端主机/lo_export LOBOID FILE/lo_import FILE [COMMENT]/lo_list/lo_unlink LOBOID 大对象操作4.7 SQL帮助ABORT CREATE INDEX DROP TYPEALTER AGGREGATE CREATE LANGUAGE DROP USERALTER CONVERSION CREATE OPERATOR CLASS DROP VIEW ALTER DATABASE CREATE OPERATOR ENDALTER DOMAIN CREATE RULE EXECUTEALTER FUNCTION CREATE SCHEMA EXPLAINALTER GROUP CREATE SEQUENCE FETCHALTER INDEX CREATE TABLE GRANTALTER LANGUAGE CREATE TABLE AS INSERTALTER OPERATOR CLASS CREATE TABLESPACE LISTENALTER OPERATOR CREATE TRIGGER LOADALTER SCHEMA CREATE TYPE LOCKALTER SEQUENCE CREATE USER MOVEALTER TABLE CREATE VIEW NOTIFYALTER TABLESPACE DEALLOCATE PREPAREALTER TRIGGER DECLARE REINDEXALTER TYPE DELETE RELEASE SAVEPOINTALTER USER DROP AGGREGATE RESETANALYZE DROP CAST REVOKEBEGIN DROP CONVERSION ROLLBACKCHECKPOINT DROP DATABASE ROLLBACK TO SAVEPOINTCLOSE DROP DOMAIN SAVEPOINTCLUSTER DROP FUNCTION SELECTCOMMENT DROP GROUP SELECT INTOCOMMIT DROP INDEX SETCOPY DROP LANGUAGE SET CONSTRAINTSCREATE AGGREGATE DROP OPERATOR CLASS SET SESSION AUTHORIZATIONCREATE CAST DROP OPERATOR SET TRANSACTIONCREATE CONSTRAINT TRIGGER DROP RULE SHOWCREATE CONVERSION DROP SCHEMA START TRANSACTIONCREATE DATABASE DROP SEQUENCE TRUNCATECREATE DOMAIN DROP TABLE UNLISTENCREATE FUNCTION DROP TABLESPACE UPDATECREATE GROUP DROP TRIGGER VACUUM命令: ABORT描述: 终止当前事务语法:ABORT [ WORK | TRANSACTION ]命令: ALTER AGGREGATE描述: 改变一个聚集函数的定义语法:ALTER AGGREGATE 名字( 类型) RENAME TO 新名字ALTER AGGREGATE 名字( 类型) OWNER TO 新属主命令: ALTER CONVERSION描述: 改变一个转换的定义语法:ALTER CONVERSION 名字RENAME TO 新名字ALTER CONVERSION 名字OWNER TO 新属主命令: ALTER DATABASE描述: 改变一个数据库语法:ALTER DATABASE 名字SET 参数{ TO | = } { 值| DEFAULT } ALTER DATABASE 名字RESET 参数ALTER DATABASE 名字RENAME TO 新名字ALTER DATABASE 名字OWNER TO 新属主命令: ALTER DOMAIN描述: 改变一个域的定义语法:ALTER DOMAIN 名字{ SET DEFAULT 说明| DROP DEFAULT }ALTER DOMAIN 名字{ SET | DROP } NOT NULLALTER DOMAIN 名字ADD 域约束ALTER DOMAIN 名字DROP CONSTRAINT 约束名字[ RESTRICT | CASCADE ] ALTER DOMAIN 名字OWNER TO 新宿主命令: ALTER DOMAIN描述: 改变一个域的定义语法:ALTER DOMAIN 名字{ SET DEFAULT 说明| DROP DEFAULT }ALTER DOMAIN 名字{ SET | DROP } NOT NULLALTER DOMAIN 名字ADD 域约束ALTER DOMAIN 名字DROP CONSTRAINT 约束名字[ RESTRICT | CASCADE ] ALTER DOMAIN 名字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 索引名称动作[, ... ]ALTER INDEX 索引旧名称RENAME TO 索引新名称动作为以下之一:OWNER TO 新属主SET TABLESPACE indexspace_name命令: ALTER LANGUAGE描述: 改变一个过程语言的定义语法:ALTER LANGUAGE 名字RENAME TO 新名字命令: ALTER OPERATOR CLASS描述: 改变一个操作符表的定义语法:ALTER OPERATOR CLASS 名字USING 索引方法RENAME TO 新名字ALTER OPERATOR CLASS 名字USING 索引方法OWNER TO 新属主命令: ALTER OPERATOR CLASS描述: 改变一个操作符表的定义语法:ALTER OPERATOR CLASS 名字USING 索引方法RENAME TO 新名字ALTER OPERATOR CLASS 名字USING 索引方法OWNER TO 新属主命令: ALTER OPERATOR描述: 改变一个操作符的定义语法:ALTER OPERATOR 名字( { 左边类型| NONE } , { 右边类型| NONE } ) OWNER TO 新属主命令: 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 ]命令: ALTER TABLE描述: 改变一个表的定义语法:ALTER TABLE [ ONLY ] 表名[ * ]action [, ... ]ALTER TABLE [ ONLY ] 表名[ * ]RENAME [ COLUMN ] 字段名TO 新字段名ALTER TABLE 表名RENAME TO 新表名action 为下面的一种:ADD [ COLUMN ] 字段名类型[ 字段约束[ ... ] ]DROP [ COLUMN ] 字段名[ RESTRICT | CASCADE ]ALTER [ COLUMN ] 字段名TYPE 类型[ USING 表达式]ALTER [ COLUMN ] 字段名SET DEFAULT 表达式ALTER [ COLUMN ] 字段名DROP DEFAULTALTER [ COLUMN ] 字段名{ SET | DROP } NOT NULLALTER [ COLUMN ] 字段名SET STATISTICS integerALTER [ COLUMN ] 字段名SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }ADD 表约束DROP CONSTRAINT 约束名字[ RESTRICT | CASCADE ]CLUSTER ON 索引名称SET WITHOUT CLUSTERSET WITHOUT OIDSOWNER TO 新属主SET TABLESPACE 表空间名字命令: ALTER TABLESPACE描述: 改变一个表空间的定义语法:ALTER TABLESPACE 名字RENAME TO 新名字ALTER TABLESPACE 名字OWNER TO 新属主命令: ALTER TABLESPACE描述: 改变一个表空间的定义语法:ALTER TABLESPACE 名字RENAME TO 新名字ALTER TABLESPACE 名字OWNER TO 新属主命令: ALTER TRIGGER描述: 改变一个触发器的定义语法:ALTER TRIGGER 名字ON 表RENAME TO 新名字命令: ALTER TYPE描述: 改变一个类型的定义语法:ALTER TYPE 名字OWNER TO 新属主命令: ALTER USER描述: 改变一个数据库用户语法:ALTER USER name [ [ WITH ] option [ ... ] ]where option can be:CREATEDB | NOCREATEDB| CREATEUSER | NOCREATEUSER| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'| VALID UNTIL 'abstime'ALTER USER name RENAME TO newnameALTER USER name SET parameter { TO | = } { value | DEFAULT }ALTER USER name RESET parameter命令: ANALYZE描述: 收集关于数据库的统计数字语法:ANALYZE [ VERBOSE ] [ 表[ (字段[, ...] ) ] ]命令: BEGIN描述: 开始一个事务块语法:BEGIN [ WORK | TRANSACTION ] [ 事物模式[, ...] ]事物模式为下面之一:ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }READ WRITE | READ ONLY命令: CHECKPOINT描述: 强行交易日志检查语法:CHECKPOINT命令: CLOSE描述: 关闭一个游标语法:CLOSE 名字命令: CLUSTER描述: 根据一个索引建表簇语法:CLUSTER 索引名字ON 表名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 |FUNCTION func_name (arg1_type, arg2_type, ...) | 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 |RULE rule_name ON table_name |SCHEMA object_name |SEQUENCE object_name |TRIGGER trigger_name ON table_name |TYPE object_name |VIEW object_name} IS 'text'命令: COMMIT描述: 提交当前事务语法:COMMIT [ WORK | TRANSACTION ]命令: COPY描述: 在一个文件和一个表之间拷贝数据语法:COPY 表名[ ( 字段[, ...] ) ]FROM { '文件名' | STDIN }[ [ WITH ][ BINARY ][ OIDS ][ DELIMITER [ AS ] 'delimiter' ][ NULL [ AS ] 'null string' ][ CSV [ QUOTE [ AS ] 'quote' ] [ ESCAPE [ AS ] 'escape' ][ FORCE NOT NULL column [, ...] ]COPY 表名[ ( 字段[, ...] ) ]TO { '文件名' | STDOUT }[ [ WITH ][ BINARY ][ OIDS ][ DELIMITER [ AS ] 'delimiter' ] [ NULL [ AS ] 'null string' ][ CSV [ QUOTE [ AS ] 'quote' ] [ ESCAPE [ AS ] 'escape' ][ FORCE QUOTE column [, ...] ]命令: CREATE AGGREGATE描述: 定义一个新的聚集函数语法:CREATE AGGREGATE name ( BASETYPE = input_data_type, SFUNC = sfunc,STYPE = state_data_type[ , FINALFUNC = ffunc ][ , INITCOND = initial_condition ] )命令: CREATE CAST描述: 定义一个新的类型转换语法:CREATE CAST (源类型AS 目标类型)WITH FUNCTION 函数名(参数类型)[ AS ASSIGNMENT | AS IMPLICIT ]CREATE CAST (源类型AS 目标类型)WITHOUT FUNCTION[ AS ASSIGNMENT | AS IMPLICIT ]命令: CREATE CONSTRAINT TRIGGER描述: 定义一个新的约束触发器语法:CREATE CONSTRAINT TRIGGER nameAFTER events ONtablename constraint attributesFOR EACH ROW EXECUTE PROCEDURE funcname ( args )命令: CREATE CONVERSION描述: define a new encoding conversion语法:CREATE [DEFAULT] CONVERSION 名字FOR 源编码TO 目标编码FROM 函数名命令: CREATE DATABASE描述: 创建一个新的数据库语法:CREATE DATABASE 数据库名称[ [ WITH ] [ OWNER [=] 数据库属主][ TEMPLATE [=] 模板][ ENCODING [=] 编码][ TABLESPACE [=] 表空间] ]命令: CREATE DOMAIN描述: 定义一个新的域语法:CREATE DOMAIN name [AS] data_type[ DEFAULT expression ][ constraint [ ... ] ]constraint 是:[ CONSTRAINT constraint_name ]{ NOT NULL | NULL | CHECK (expression) }命令: 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 可以为:SYSID gid| USER username [, ...]命令: CREATE INDEX描述: 定义一个新的索引语法:CREATE [ UNIQUE ] INDEX 索引名称ON 表名[ USING method ]( { column | ( expression ) } [ opclass ] [, ...] )[ TABLESPACE tablespace ][ WHERE predicate ]命令: CREATE LANGUAGE描述: 定义一个新的过程语言语法:CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE nameHANDLER call_handler [ VALIDATOR valfunction ]命令: CREATE OPERATOR CLASS描述: 定义一个新的操作符类语法:CREATE OPERATOR CLASS name [ DEFAULT ] FOR TYPE data_type USING index_method AS{ OPERATOR strategy_number operator_name [ ( op_type, op_type ) ] [ RECHECK ] | FUNCTION support_number funcname ( argument_type [, ...] )| STORAGE storage_type} [, ... ]命令: CREATE OPERATOR CLASS描述: 定义一个新的操作符类语法:CREATE OPERATOR CLASS name [ DEFAULT ] FOR TYPE data_type USING index_method AS{ OPERATOR strategy_number operator_name [ ( op_type, op_type ) ] [ RECHECK ] | FUNCTION support_number funcname ( argument_type [, ...] )| STORAGE storage_type} [, ... ]命令: CREATE OPERATOR描述: 定义一个新的操作符语法:CREATE OPERATOR name (PROCEDURE = funcname[, LEFTARG = lefttype ] [, RIGHTARG = righttype ][, COMMUTATOR = com_op ] [, NEGATOR = neg_op ][, RESTRICT = res_proc ] [, JOIN = join_proc ][, HASHES ] [, MERGES ][, SORT1 = left_sort_op ] [, SORT2 = right_sort_op ][, LTCMP = less_than_op ] [, GTCMP = greater_than_op ])命令: CREATE RULE描述: 定义一个新的重写规则语法:CREATE [ OR REPLACE ] RULE 名字AS ON 事件TO 表[ WHERE 条件]DO [ ALSO | INSTEAD ] { NOTHING | 命令| ( 命令; 命令 ... ) }命令: CREATE RULE描述: 定义一个新的重写规则语法:CREATE [ OR REPLACE ] RULE 名字AS ON 事件TO 表[ WHERE 条件]DO [ ALSO | INSTEAD ] { NOTHING | 命令| ( 命令; 命令 ... ) }test=# /h CREATE SCHEMA命令: 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 ]命令: 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 TABLESPACE描述: 定义一个新的表空间语法:CREATE TABLESPACE 表空间名称[ OWNER 用户名称] LOCATION '目录'命令: CREATE TRIGGER描述: 定义一个新的触发器语法:CREATE TRIGGER 名字{ BEFORE | AFTER } { 事件[ OR ... ] }ON 表[ FOR [ EACH ] { ROW | STATEMENT } ]EXECUTE PROCEDURE 函数名( 参数)命令: 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 ][ , ANALYZE = analyze_function ][ , INTERNALLENGTH = { internallength | VARIABLE } ] [ , PASSEDBYVALUE ][ , ALIGNMENT = alignment ][ , STORAGE = storage ][ , DEFAULT = default ][ , ELEMENT = element ][ , DELIMITER = delimiter ])命令: CREATE USER描述: 定义一个新的数据库用户帐户语法:CREATE USER name [ [ WITH ] option [ ... ] ]where option can be:SYSID uid| CREATEDB | NOCREATEDB| CREATEUSER | NOCREATEUSER| IN GROUP groupname [, ...]| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'abstime'命令: CREATE VIEW描述: 定义一个新的视图语法:CREATE [ OR REPLACE ] VIEW 名字[ ( 字段名[, ...] ) ] AS query命令: DEALLOCATE描述: 解除一个准备好的语句语法:DEALLOCATE [ PREPARE ] 规划名称命令: DECLARE描述: 定义一个游标语法:DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FOR query[ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]命令: DELETE描述: 删除一个表的记录语法:DELETE FROM [ ONLY ] 表[ WHERE 条件]命令: DROP AGGREGATE描述: 删除一个聚集函数语法:DROP AGGREGATE 名字( 类型) [ CASCADE | RESTRICT ]命令: DROP CAST描述: 删除一个类型转换语法:DROP CAST (源类型AS 目标类型) [ CASCADE | RESTRICT ]命令: DROP CONVERSION描述: 删除一个转换语法:DROP CONVERSION 名字[ CASCADE | RESTRICT ]命令: DROP DATABASE描述: 删除一个数据库语法:DROP DATABASE 名字命令: DROP DOMAIN描述: 删除一个域语法:DROP DOMAIN 名字[, ...] [ CASCADE | RESTRICT ]命令: DROP FUNCTION描述: 删除一个函数语法:DROP FUNCTION 名字( [ 类型[, ...] ] ) [ CASCADE | RESTRICT ]命令: DROP GROUP描述: 删除一个用户组语法:DROP GROUP 名字命令: DROP INDEX描述: 删除一个索引语法:DROP INDEX 名字[, ...] [ CASCADE | RESTRICT ]命令: DROP LANGUAGE描述: 删除一个过程语言语法:DROP [ PROCEDURAL ] LANGUAGE 名字[ CASCADE | RESTRICT命令: DROP OPERATOR CLASS描述: 删除一个操作符类语法:DROP OPERATOR CLASS name USING index_method [ CASCADE | RESTRICT ]描述: 删除一个操作符语法:DROP OPERATOR 名字( { 左边类型| NONE } , { 右边类型| NONE } ) [ 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 TABLESPACE描述: 删除一个表空间语法:DROP TABLESPACE 表空间名字命令: DROP TRIGGER描述: 删除一个触发器语法:DROP TRIGGER 名字ON 表[ 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 可以为空或下面的一种:NEXTPRIORFIRSTLASTABSOLUTE countRELATIVE countcountALLFORWARDFORWARD countFORWARD ALLBACKWARDBACKWARD countBACKWARD ALL命令: GRANT描述: 定义访问权限语法:GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER[,...] | ALL [ PRIVILEGES ] }ON [ TABLE ] 表名称[, ...]TO { 用户名称| GROUP 组名称| PUBLIC } [, ...] [ WITH GRANT OPTION ]GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }ON DATABASE 数据库名称[, ...]TO { 用户名称| GROUP 组名称| PUBLIC } [, ...] [ WITH GRANT OPTION ]GRANT { EXECUTE | ALL [ PRIVILEGES ] }ON FUNCTION 函数名称([类型, ...]) [, ...]TO { 用户名称| GROUP 组名称| PUBLIC } [, ...] [ WITH GRANT OPTION ]GRANT { USAGE | ALL [ PRIVILEGES ] }ON LANGUAGE 语言名称[, ...]TO { 用户名称| GROUP 组名称| PUBLIC } [, ...] [ WITH GRANT OPTION ]GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }ON SCHEMA 模式名称[, ...]TO { 用户名称| GROUP 组名称| PUBLIC } [, ...] [ WITH GRANT OPTION ]GRANT { CREATE | ALL [ PRIVILEGES ] }ON TABLESPACE 表空间名称[, ...]TO { 用户名称| GROUP 组名称| PUBLIC } [, ...] [ WITH GRANT OPTION ]命令: INSERT描述: 在一个表中创建新行语法:INSERT INTO 表名[ ( 字段[, ...] ) ]{ DEFAULT VALUES | VALUES ( { 表达式| DEFAULT } [, ...] ) | 子查询}命令: LISTEN描述: 监听一个通知语法:LISTEN 名字命令: LOAD描述: 提取或重载一个共享库文件语法:LOAD '文件名'命令: LOCK描述: 锁定一个表语法:LOCK [ TABLE ] 名字[, ...] [ IN lockmode MODE ] [ NOWAIT ]lockmode 可以是下面的一种:ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE| SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE命令: MOVE描述: 定位一个游标语法:MOVE [ direction { FROM | IN } ] cursorname命令: NOTIFY描述: 生成一个通知语法:NOTIFY 名字命令: 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 | RULE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] }ON [ TABLE ] 表名称[, ...]FROM { 用户名称| GROUP 组名称| PUBLIC } [, ...][ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ]{ { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }ON DATABASE 数据库名称[, ...]FROM { 用户名称| GROUP 组名称| PUBLIC } [, ...][ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ]{ EXECUTE | ALL [ PRIVILEGES ] }ON FUNCTION 函数名称([类型, ...]) [, ...]FROM { 用户名称| GROUP 组名称| PUBLIC } [, ...][ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ]{ USAGE | ALL [ PRIVILEGES ] }ON LANGUAGE 语言名称[, ...]FROM { 用户名称| GROUP 组名称| PUBLIC } [, ...][ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ]{ { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }ON SCHEMA 模式名称[, ...]FROM { 用户名称| GROUP 组名称| PUBLIC } [, ...][ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ]{ CREATE | ALL [ PRIVILEGES ] }ON TABLESPACE 表空间名称[, ...]FROM { 用户名称| GROUP 组名称| PUBLIC } [, ...][ 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 ( 表达式[, ...] ) ] ]* | 表达式[ AS output_name ] [, ...][ FROM from_item [, ...] ][ WHERE 条件][ GROUP BY 表达式[, ...] ][ HAVING 条件[, ...] ][ { UNION | INTERSECT | EXCEPT } [ ALL ] select ][ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ][ LIMIT { count | ALL } ][ OFFSET start ][ FOR UPDATE [ OF 表名[, ...] ] ]from_item 可以是下面的一种:[ 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 [ OF tablename [, ...] ] ]命令: SET描述: 改变一个运行时参数语法:SET [ SESSION | LOCAL ] 名字{ TO | = } { 值| '值' | DEFAULT }SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT }命令: SET CONSTRAINTS描述: 设置当前交易的约束检查模式语法:SET CONSTRAINTS { ALL | 名字[, ...] } { DEFERRED | IMMEDIATE }命令: SET SESSION AUTHORIZATION描述: 设置当前会话的会话用户确认和当前用户确认语法:SET [ SESSION | LOCAL ] SESSION AUTHORIZATION 用户名称SET [ SESSION | LOCAL ] SESSION AUTHORIZATION DEFAULTRESET SESSION AUTHORIZATION命令: SET TRANSACTION描述: 设置当前交易的属性语法:SET TRANSACTION 事物模式[, ...]SET SESSION CHARACTERISTICS AS TRANSACTION 事物模式[, ...]事物模式为下面之一:ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }READ WRITE | READ ONLY命令: SHOW描述: 显示运行时参数值语法:SHOW 名字SHOW ALL命令: START TRANSACTION描述: 开始一个事务块语法:START TRANSACTION [ 事物模式[, ...] ]事物模式为下面之一:ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }READ WRITE | READ ONLY命令: TRUNCATE描述: 清空一个表语法:TRUNCATE [ TABLE ] 名字命令: UNLISTEN描述: 停止监听通知语法:UNLISTEN { 名字| * }命令: UPDATE描述: 更新一个表的记录语法:UPDATE [ ONLY ] 表名SET 字段= { 表达式| DEFAULT } [, ...][ FROM fromlist ][ WHERE 条件]命令: VACUUM描述: 垃圾回收和可选择分析一个数据库语法:VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ 表]VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ 表[ (列[, ...] ) ] ]。