PostgreSQL语法及其所有SQL命令

本文概述

所有PostgreSQL命令
中止命令
ALTER AGGREGATE命令
ALTER CONVERSION命令
ALTER DATABASE命令
ALTER DOMAIN命令
ALTER FUNCTION命令
ALTER GROUP命令
ALTER INDEX命令
ALTER LANGUAGE命令
ALTER OPERATOR命令
ALTER OPERATOR CLASS命令
ALTER SCHEMA命令
ALTER SEQUENCE命令
ALTER TABLE命令
ALTER TABLESPACE命令
ALTER TRIGGER命令
ALTER TYPE命令
ALTER USER命令
命令分析
BEGIN命令
CHECKPOINT命令
关闭命令
CLUSTER命令
COMMIT命令
COPY命令
CREATE AGGREGATE命令
CREATE CAST命令
CREATE CONSTRAINT TRIGGER命令
CREATE CONVERSION命令
CREATE DATABASE命令
CREATE DOMAIN命令
CREATE FUNCTION命令
CREATE GROUP命令
CREATE INDEX命令
CREATE LANGUAGE命令
CREATE OPERATOR命令
CREATE OPERATOR CLASS命令
CREATE RULE命令
CREATE SCHEMA命令
CREATE SEQUENCE命令
创建表
CREATE TABLE AS命令
CREATE TABLESPACE命令
CRFEATE TRIGGER命令
CREATE TYPE命令
CREATE USER命令
CREATE VIEW命令
DEALLOCATE命令
DECLARE命令
删除命令
DROP AGGREGATE命令
DROP CAST命令
DROP CONVERSION命令
DROP DATABASE命令
DROP DOMAIN命令
DROP FUNCTION命令
DROP GROUP命令
DROP INDEX命令
DROP LANGUAGE命令
DROP OPERATOR命令
DROP OPERATOR CLASS命令
DROP RULE命令
DROP SCHEMA命令
DROP SEQUENCE命令
DROP TABLE命令
DROP TABLESPACE命令
DROP TRIGGER命令
DROP TYPE命令
DROP USER命令
DROP VIEW命令
END命令
执行命令
EXPLAIN命令
FETCH命令
INSERT命令
LISTEN命令
LOAD命令
LOCK命令
MOVE命令
NOTIFY命令
PREPARE命令
REINDEX命令
RESET指令
ROLLBACK命令
ROLLBACK TO SAVEPOINT命令
SAVEPOINT命令
SELECT命令
SELECT INTO命令
SET命令
SET CONSTRAINTS命令
SET TRANSACTION命令
显示命令
开始事务
TRUNCATE TABLE命令
UPDATE命令

我们可以使用help命令查看所有postgreSQL命令的语法。请按照以下步骤查看postgreSQL中所有命令的语法。

安装postgreSQL之后, 以以下方式打开psql：程序文件> PostgreSQL 9.2> SQL Shell(psql)
使用以下命令查看特定命令的语法。 postgres-＃\ help & & < 命令名称>

所有PostgreSQL命令在这里, 我们提供了所有postgreSQL命令及其语法的列表：
中止命令句法：

ABORT [ WORK | TRANSACTION ]

ALTER AGGREGATE命令句法：

ALTER AGGREGATE name ( type ) RENAME TO new_name ALTER 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 name ( [ type [, ...] ] ) RENAME TO new_name ALTER FUNCTION name ( [ type [, ...] ] ) OWNER TO new_owner

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 name RENAME TO new_name

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 name RENAME TO new_name ALTER SCHEMA name OWNER TO new_owner

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 [ ONLY ] name [ * ] action [, ... ] ALTER TABLE [ ONLY ] name [ * ] RENAME [ COLUMN ] column TO new_column ALTER TABLE name RENAME TO new_name

ALTER TABLESPACE命令句法：

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

ALTER TRIGGER命令句法：

ALTER TRIGGER name ON table RENAME TO new_name

ALTER TYPE命令句法：

ALTER TYPE name OWNER TO new_owner

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

命令分析句法：

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

BEGIN命令句法：

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

CHECKPOINT命令句法：

CHECKPOINT

关闭命令句法：

CLOSE name

CLUSTER命令句法：

CLUSTER index_name ON table_name CLUSTER table_name CLUSTER

COMMIT命令句法：

COMMIT [ WORK | TRANSACTION ]

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命令句法：

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 CONSTRAINT TRIGGER命令句法：

CREATE CONSTRAINT TRIGGER name AFTER events ON table_name constraint attributes FOR EACH ROW EXECUTE PROCEDURE func_name ( args )

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 [ ... ] ]

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 [ [ 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 ]

CREATE TABLE AS命令句法：

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

CREATE TABLESPACE命令句法：

CREATE TABLESPACE tablespace_name [ OWNER username ] LOCATION 'directory'

CRFEATE TRIGGER命令句法：

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

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 = { internal_length | VARIABLE } ] [ , PASSEDBYVALUE ] [ , ALIGNMENT = alignment ] [ , STORAGE = storage ] [ , DEFAULT = default ] [ , ELEMENT = element ] [ , DELIMITER = delimiter ] )

CREATE USER命令句法：

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

CREATE VIEW命令句法：

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

DEALLOCATE命令句法：

DEALLOCATE [ PREPARE ] plan_name

DECLARE命令句法：

DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FOR query [ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]

删除命令句法：

DELETE FROM [ ONLY ] table [ WHERE condition ]

DROP AGGREGATE命令句法：

DROP AGGREGATE name ( type ) [ CASCADE | RESTRICT ]

DROP CAST命令句法：

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

DROP CONVERSION命令句法：

DROP CONVERSION name [ CASCADE | RESTRICT ]

DROP DATABASE命令句法：

DROP DATABASE name

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 ]

DROP TABLESPACE命令句法：

DROP TABLESPACE tablespace_name

DROP TRIGGER命令句法：

DROP TRIGGER name ON table [ CASCADE | RESTRICT ]

DROP TYPE命令句法：

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

DROP USER命令句法：

DROP USER name

DROP VIEW命令句法：

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

END命令句法：

END [ WORK | TRANSACTION ]

执行命令句法：

EXECUTE plan_name [ (parameter [, ...] ) ]

EXPLAIN命令句法：

EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

FETCH命令句法：

FETCH [ direction { FROM | IN } ] cursor_name

INSERT命令句法：

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

LISTEN命令句法：

LISTEN name

LOAD命令句法：

LOAD 'filename'

LOCK命令句法：

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

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 ]

RESET指令句法：

RESET name RESET ALL

ROLLBACK命令句法：

ROLLBACK [ WORK | TRANSACTION ]

ROLLBACK TO SAVEPOINT命令句法：

ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name

SAVEPOINT命令句法：

SAVEPOINT savepoint_name

SELECT命令句法：

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ] * | expression [ AS output_name ] [, ...] [ 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 table_name [, ...] ] ]

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 table_name [, ...] ] ]

SET命令句法：