PostgreSQL 语法

2024年8月29日 | 阅读 10 分钟

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

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

所有 PostgreSQL 命令

在这里，我们提供所有 postgreSQL 命令及其语法的列表

ABORT 命令

语法

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

ANALYSE 命令

语法

BEGIN 命令

语法

CHECKPOINT 命令

语法

CLOSE 命令

语法

CLUSTER 命令

语法

CLUSTER index_name ON table_name
CLUSTER table_name
CLUSTER

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 命令

语法

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

CREATE TABLE AS 命令

语法

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

CREATE TABLESPACE 命令

语法

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 VIEW 命令

语法

DEALLOCATE 命令

语法

DECLARE 命令

语法

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

DELETE 命令

语法

DROP AGGREGATE 命令

语法

DROP CAST 命令

语法

DROP CONVERSION 命令

语法

DROP DATABASE 命令

语法

DROP DOMAIN 命令

语法

DROP FUNCTION 命令

语法

DROP GROUP 命令

语法

DROP INDEX 命令

语法

DROP LANGUAGE 命令

语法

DROP OPERATOR 命令

语法

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

DROP OPERATOR CLASS 命令

语法

DROP RULE 命令

语法

DROP SCHEMA 命令

语法

DROP SEQUENCE 命令

语法

DROP TABLE 命令

语法

DROP TABLESPACE 命令

语法

DROP TRIGGER 命令

语法

DROP TYPE 命令

语法

DROP USER 命令

语法

DROP VIEW 命令

语法

END 命令

语法

EXECUTE 命令

语法

EXPLAIN 命令

语法

FETCH 命令

语法

INSERT 命令

语法

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

LISTEN 命令

语法

LOAD 命令

语法

LOCK 命令

语法

MOVE 命令

语法

NOTIFY 命令

语法

PREPARE 命令

语法

REINDEX 命令

语法

RESET 命令

语法

RESET name
RESET ALL

ROLLBACK 命令

语法

ROLLBACK TO SAVEPOINT 命令

语法

SAVEPOINT 命令

语法

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 命令

语法

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

SET CONSTRAINTS 命令

语法

SET TRANSACTION 命令

语法

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

SHOW 命令

语法

SHOW name
SHOW ALL

START TRANSACTION

语法

TRUNCATE TABLE 命令

语法

UPDATE 命令

语法

UPDATE [ ONLY ] table SET column = { expression | DEFAULT } [, ...]
[ FROM from_list ]
[ WHERE condition ]

下一个主题安装 PostgreSQL

我们提供所有技术（如 Java 教程、Android、Java 框架）的教程和面试问题

联系信息

G-13, 2nd Floor, Sec-3, Noida, UP, 201301, India

hr@tpointtech.com

+91-9599086977

关注我们

Python

Java

.Net Framework

AI, ML and Data Science

Cloud Technology

B.Tech and MCA

Web Technology

PHP

Software Testing

Technical Interview

Java Interview

Python

Web Interview

Database Interview

B.Tech / MCA

Important Interview

Software Testing Interview

Company Interviews

Online Compilers

Multiple Choice Questions

PostgreSQL Join

测验

Interview

差异

PostgreSQL Advance

数据库约束

PostgreSQL 索引

PostgreSQL 触发器

PostgreSQL 教程

PostgreSQL 视图

PostgreSQL 条件

PostgreSQL 语句

PostgreSQL 查询

PostgreSQL 模式

数据类型

PostgreSQL 表

PostgreSQL 数据库