PostgreSQL基本语法

本章提供了PostgreSQL SQL 命令的列表，以及每个命令的精确语法规则，这套命令取自psql命令行工具，现在您已经安装了Postgres，打开psql为-

Program Files→PostgreSQL 9.2→SQL Shell(psql)。

使用psql，可以使用\help命令生成命令的完整列表。对于特定命令的语法，请使用以下命令-

 postgres-#\help <command_name>

ABORT

中止当前事务。

ABORT [ WORK | TRANSACTION ]

ALERT AGGREGATE

更改聚合函数的定义。

ALERT AGGREGATE name ( type ) RENAME TO new_name
ALERT AGGREGATE name ( type ) OWNER TO new_owner

ALTER CONVERSION

更改转换的定义。

ALTER CONVERSION name RENAME TO new_name
ALTER CONVERSION name OWNER TO new_owner

ALTER DATABASE

更改数据库特定的参数。

ALTER DATABASE name SET parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name RESET parameter
ALTER DATABASE name RENAME TO new_name
ALTER DATABASE name OWNER TO new_owner

ALTER DOMAIN

更改域特定参数的定义。

ALTER DOMAIN name { SET DEFAULT expression | DROP DEFAULT }
ALTER DOMAIN name { SET | DROP } NOT NULL
ALTER DOMAIN name ADD domain_constraint
ALTER DOMAIN name DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
ALTER DOMAIN name OWNER TO new_owner

ALTER FUNCTION

更改函数的定义。

ALTER FUNCTION

ALTER GROUP

ALTER GROUP groupname ADD USER username [, ... ]
ALTER GROUP groupname DROP USER username [, ... ]
ALTER GROUP groupname RENAME TO new_name

ALTER INDEX

更改索引的定义。

ALTER INDEX name OWNER TO new_owner
ALTER INDEX name SET TABLESPACE indexspace_name
ALTER INDEX name RENAME TO new_name

ALTER LANGUAGE

更改过程语言的定义。

ALTER LANGUAGE

ALTER OPERATOR

更改运算符的定义。

ALTER OPERATOR name ( { lefttype | NONE }, { righttype | NONE } )
OWNER TO new_owner

ALTER OPERATOR CLASS

更改操作类别的定义。

ALTER OPERATOR CLASS name USING index_method RENAME TO new_name
ALTER OPERATOR CLASS name USING index_method OWNER TO new_owner

ALTER SCHEMA

更改架构的定义。

ALTER SCHEMA

ALTER SEQUENCE

更改序列生成器的定义。

ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ]
[ MAXVALUE maxvalue | NO MAXVALUE ]
[ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]

ALTER TABLE

更改表的定义。

ALTER TABLE

其中动作是以下几行之一-

CLUSTER

ALTER TABLESPACE

更改表空间的定义。

ALTER TABLESPACE name RENAME TO new_name
ALTER TABLESPACE name OWNER TO new_owner

ALTER TRIGGER

更改触发器的定义。

ALTER TRIGGER

ALTER TYPE

更改类型的定义。

ALTER TYPE

ALTER USER

更改数据库用户帐户。

ALTER USER name [ [ WITH ] option [ ... ] ]
ALTER USER name RENAME TO new_name
ALTER USER name SET parameter { TO | = } { value | DEFAULT }
ALTER USER name RESET parameter

其中选项可以是-

[ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| CREATEDB | NOCREATEDB
| CREATEUSER | NOCREATEUSER
| VALID UNTIL 'abstime'

ANALYZE

收集有关数据库的统计信息。

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

BEGIN

启动事务块。

BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]

其中 transaction_mode 是以下之一:

COMMITTED

CHECKPOINT

强制执行事务日志检查点。

CHECKPOINT

CLOSE

关闭游标。

CLOSE name

CLUSTER

根据索引对表进行聚类。

CLUSTER

COMMENT

定义或更改对象的注释。

COMMENT

COMMIT

提交当前交易。

COMMIT

COPY

在文件和表之间复制数据。

COPY table_name [ ( column [, ...] ) ]
FROM { 'filename' | STDIN }
[ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE NOT NULL column [, ...] ]
COPY table_name [ ( column [, ...] ) ]
TO { 'filename' | 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

定义一个新的CAST表。

CREATE CAST (source_type AS target_type)
WITH FUNCTION func_name (arg_types)
[ AS ASSIGNMENT | AS IMPLICIT ]
CREATE CAST (source_type AS target_type)
WITHOUT FUNCTION
[ AS ASSIGNMENT | AS IMPLICIT ]

CREATE TRIGGER

定义一个新的约束触发器。

EXECUTE

CREATE CONVERSION

定义新的转化。

CREATE [DEFAULT] CONVERSION name
FOR source_encoding TO dest_encoding FROM func_name

CREATE DATABASE

创建一个新的数据库。

CREATE DATABASE name
[ [ WITH ] [ OWNER [=] db_owner ]
   [ TEMPLATE [=] template ]
   [ ENCODING [=] encoding ]
   [ TABLESPACE [=] tablespace ] 
]

CREATE DOMAIN

定义一个新域。

CREATE DOMAIN name [AS] data_type
[ DEFAULT expression ]
[ constraint [ ... ] ]

其中约束是-

[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | CHECK (expression) }

CREATE FUNCTION

定义一个新功能。

CREATE [ OR REPLACE ] FUNCTION name ( [ [ arg_name ] arg_type [, ...] ] )
RETURNS ret_type
{ LANGUAGE lang_name
   | 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 name [ [ WITH ] option [ ... ] ]
Where option can be:
SYSID gid
| USER username [, ...]

CREATE INDEX

定义一个新索引。

CREATE [ UNIQUE ] INDEX name ON table [ USING method ]
( { column | ( expression ) } [ opclass ] [, ...] )
[ TABLESPACE tablespace ]
[ WHERE predicate ]

CREATE LANGUAGE

定义一种新的程序语言。

CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE name
HANDLER call_handler [ VALIDATOR val_function ]

CREATE OPERATOR

定义一个新的运算符。

CREATE OPERATOR name (
   PROCEDURE = func_name
   [, LEFTARG = left_type ] [, RIGHTARG = right_type ]
   [, 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 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 func_name ( argument_type [, ...] )
   | STORAGE storage_type
} [, ... ]

CREATE RULE

定义一个新的重写规则。

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

CREATE SCHEMA

定义一个新的架构。

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

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

定义一个新表。

COMMIT

其中 column_constraint 是-

DELETE

并且 table_constraint 是-

DELETE

CREATE TABLE AS

根据查询输出定义一个新表。

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name
[ (column_name [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ]
AS query

CREATE TABLESPACE

定义一个新的表空间。

CREATE TABLESPACE

CREATE TRIGGER

定义一个新触发器。

CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
执行 PROCEDURE func_name ( arguments )

CREATE TYPE

定义一个新的数据类型。

ANALYZE

CREATE USER

定义一个新的数据库用户帐户。

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

其中选项可以是-

SYSID uid
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| CREATEDB | NOCREATEDB
| CREATEUSER | NOCREATEUSER
| IN GROUP group_name [, ...]
| VALID UNTIL 'abs_time'

CREATE VIEW

定义一个新视图。

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

DEALLOCATE

取消分配准备好的语句。

DEALLOCATE [PREPARE ] plan_name

DECLARE

定义一个游标。

UPDATE

DELETE

删除表中的行。

DELETE

DROP AGGREGATE

删除聚合函数。

DROP AGGREGATE

DROP CAST

删除别名。

DROP CAST (source_type AS target_type) [ CASCADE | RESTRICT ]

DROP CONVERSION

删除转换。

DROP CONVERSION name [ CASCADE | RESTRICT ]

DROP DATABASE

删除数据库。

DROP DATABASE

DROP DOMAIN

删除域。

DROP DOMAIN name [, ...] [ CASCADE | RESTRICT ]

DROP FUNCTION

删除功能。

DROP FUNCTION name ( [ type [, ...] ] ) [ CASCADE | RESTRICT ]

DROP GROUP

删除用户组。

DROP GROUP name

DROP INDEX

删除索引。

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

DROP LANGUAGE

删除程序语言。

DROP [ PROCEDURAL ] LANGUAGE name [ CASCADE | RESTRICT ]

DROP OPERATOR

删除运算符。

DROP OPERATOR name ( { left_type | NONE }, { right_type | NONE } )
[ CASCADE | RESTRICT ]

DROP OPERATOR CLASS

删除运算类。

DROP OPERATOR CLASS name USING index_method [ CASCADE | RESTRICT ]

DROP RULE

删除重写规则。

DROP RULE name ON relation [ CASCADE | RESTRICT ]

DROP SCHEMA

删除架构。

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

DROP SEQUENCE

删除序列。

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

DROP TABLE

删除表。

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

DRP TABLESPACE

删除表空间。

DROP TABLESPACE

DROP TRIGGER

删除触发器。

DROP TRIGGER name ON table [ CASCADE | RESTRICT ]

DROP TYPE

删除数据类型。

DROP TYPE 

DROP USER

删除数据库用户帐户。

DROP USER 

DROP VIEW

删除视图。

DROP VIEW 

END

提交当前交易。

END [ WORK | TRANSACTION ]

EXECUTE

执行准备好的语句。

EXECUTE

EXPLAIN

显示语句的执行计划。

ANALYZE

FETCH

使用游标从查询中检索行。

FETCH [ direction { FROM | IN } ] cursor_name

其中 direction 可以为空或-

NEXT
PRIOR
FIRST
LAST
ABSOLUTE count
RELATIVE count
count
ALL
FORWARD
FORWARD count
FORWARD ALL
BACKWARD
BACKWARD count
BACKWARD ALL

GRANT

定义访问权限。

SELECT

INSERT

在表中创建新行。

INSERT

LISTEN

收听通知。

LISTEN

LOAD

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

LOAD 'filename'

LOCK

锁定表。

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

其中 lock_mode 是-之一

ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE
| SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE

MOVE

定位光标。

MOVE [ direction { FROM | IN } ] cursor_name

NOTIFY

生成通知。

NOTIFY name

PREPARE

准备要执行的语句。

PREPARE plan_name [ (data_type [, ...] ) ] AS statement

REINDEX

重建索引。

REINDEX { DATABASE | TABLE | INDEX } name [ FORCE ]

REPEASE SAVEPOINT

销毁先前定义的保存点。

SAVEPOINT

RESET

将运行时参数的值恢复为默认值。

RESET name
RESET ALL

REVOKE

删除访问权限。

REVOKE

ROLLBACK

中止当前交易。

ROLLBACK

ROLLBACK TO SAVEPOINT

回滚到保存点。

ROLLBACK

SAVEPOINT

在当前事务中定义一个新的保存点。

SAVEPOINT

SELECT

从表或视图中检索行。

SELECT

[ 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

根据查询输出定义一个新表。

SELCT

SET

更改运行时参数。

SET [ SESSION | LOCAL ] name { TO | = } { value | 'value' | DEFAULT }
SET [ SESSION | LOCAL ] TIME ZONE { time_zone | LOCAL | DEFAULT }

SET CONSTRAINTS

设置当前事务的约束检查模式。

SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }

SET SESSION AUTHORIZATION

设置会话用户标识符和当前会话的当前用户标识符。

 SESSION AUTHORIZATION

SET TRANSACTION

设置当前交易的特征。

SET TRANSACTION transaction_mode [, ...]
SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [, ...]

其中 transaction_mode 是以下之一:

COMMIT

SHOW

显示运行时参数的值。

SHOW name
SHOW ALL

START TRANSACTION

启动事务块。

START TRANSACTION [ transaction_mode [, ...] ]

其中 transaction_mode 是以下之一:

COMMIT

TRUNCATE

清空表。

TRUNCATE[ TABLE ] name

UNLISTEN

停止收听通知。

UNLISTEN { name | * }

UPDATE

更新表的行。

UPDATE

VACUUM

垃圾收集并可选地分析数据库。

VACUUM