当前位置：极客教程 > PostgreSQL > PostgreSQL 精品教程 > PostgreSQL 语法

PostgreSQL 语法

PostgreSQL 语法

本章提供了PostgreSQL SQL命令的列表，紧接着是每个命令的准确语法规则。这组命令来自于psql命令行工具。既然你已经安装了Postgres，打开psql，如下所示 −

程序文件 → PostgreSQL 9.2 → SQL Shell(psql)。

使用psql，你可以使用\help命令生成一整个命令列表。要获取特定命令的语法，请使用以下命令−

postgres-# \help <command_name>
SQL

SQL语句

SQL语句由标记组成，每个标记代表关键字、标识符、带引号的标识符、常量或特殊字符符号。下表使用一个简单的SELECT语句来说明一个基本但完整的SQL语句及其组成部分。

SELECT  id, name    FROM    states
SQL
Token Type Keyword Identifiers Keyword Identifier
Description Command Id and name columns Clause Table name

PostgreSQL SQL命令

ABORT

终止当前事务。

ABORT [ WORK | TRANSACTION ]
SQL

ALTER AGGREGATE

ALTER AGGREGATE的定义。

ALTER AGGREGATE name ( type ) RENAME TO new_name
ALTER AGGREGATE name ( type ) OWNER TO new_owner
SQL

ALTER CONVERSION

ALTER CONVERSION的定义。

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

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
SQL

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
SQL

ALTER FUNCTION

更改一个函数的定义。

ALTER FUNCTION name ( [ type [, ...] ] ) RENAME TO new_name
ALTER FUNCTION name ( [ type [, ...] ] ) OWNER TO new_owner
SQL

ALTER GROUP

更改用户组。

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

ALTER INDEX

更改索引的定义。

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

ALTER LANGUAGE

更改一种过程化语言的定义。

ALTER LANGUAGE name RENAME TO new_name
SQL

ALTER OPERATOR

更改操作符的定义。

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

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
SQL

ALTER SCHEMA

更改模式的定义。

ALTER SCHEMA name RENAME TO new_name
ALTER SCHEMA name OWNER TO new_owner
SQL

ALTER SEQUENCE

更改序列生成器的定义。

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

ALTER TABLE

修改表的定义。

ALTER TABLE [ ONLY ] name [ * ]
action [, ... ]
ALTER TABLE [ ONLY ] name [ * ]
RENAME [ COLUMN ] column TO new_column
ALTER TABLE name
RENAME TO new_name
SQL

下面的英文翻译成中文，不解释，保留HTML格式：

其中，action是以下几种情况之一:

ADD [ COLUMN ] column_type [ column_constraint [ ... ] ]
DROP [ COLUMN ] column [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] column TYPE type [ USING expression ]
ALTER [ COLUMN ] column SET DEFAULT expression
ALTER [ COLUMN ] column DROP DEFAULT
ALTER [ COLUMN ] column { SET | DROP } NOT NULL
ALTER [ COLUMN ] column SET STATISTICS integer
ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
ADD table_constraint
DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
CLUSTER ON index_name
SET WITHOUT CLUSTER
SET WITHOUT OIDS
OWNER TO new_owner
SET TABLESPACE tablespace_name
SQL

ALTER TABLESPACE

更改表空间的定义。

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

ALTER TRIGGER

更改触发器的定义。

ALTER TRIGGER name ON table RENAME TO new_name
SQL

ALTER TYPE

更改类型的定义。

ALTER TYPE name OWNER TO new_owner
SQL

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
SQL

哪里选项可以是−

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

ANALYZE

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

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

BEGIN

启动一个事务块。

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

其中transaction_mode是−中的一种

ISOLATION LEVEL { 
   SERIALIZABLE | REPEATABLE READ | READ COMMITTED
   | READ UNCOMMITTED
}
READ WRITE | READ ONLY
SQL

CHECKPOINT

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

CHECKPOINT
SQL

CLOSE

关闭一个游标。

CLOSE name
SQL

CLUSTER

根据索引对表进行集群化。

CLUSTER index_name ON table_name
CLUSTER table_name
CLUSTER
SQL

COMMENT

定义或更改对象的注释。

COMMENT ON {
   TABLE object_name |
   COLUMN table_name.column_name |
   AGGREGATE agg_name (agg_type) |
   CAST (source_type AS target_type) |
   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 (left_operand_type, right_operand_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'
SQL

COMMIT

提交当前事务。

COMMIT [ WORK | TRANSACTION ]
SQL

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

CREATE AGGREGATE

定义一个新的聚合函数。

CREATE AGGREGATE name (
   BASETYPE = input_data_type,
   SFUNC = sfunc,
   STYPE = state_data_type
   [, FINALFUNC = ffunc ]
   [, INITCOND = initial_condition ]
)
SQL

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

CREATE CONSTRAINT

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

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

CREATE CONVERSION

定义一个新的转换。

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

CREATE DATABASE

创建一个新的数据库。

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

CREATE DOMAIN

定义一个新的域。

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

约束在哪里是 –

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

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

CREATE GROUP

定义一个新的用户群组。

CREATE GROUP name [ [ WITH ] option [ ... ] ]
Where option can be:
SYSID gid
| USER username [, ...]
SQL

CREATE INDEX

定义一个新的索引。

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

CREATE LANGUAGE

定义一个新的过程语言。

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

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

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

CREATE RULE

定义一个新的重写规则。

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

CREATE SCHEMA

定义一个新的模式。

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

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

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

其中 column_constraint 是-

[ CONSTRAINT constraint_name ] { 
   NOT NULL |
   NULL |
   UNIQUE [ USING INDEX TABLESPACE tablespace ] |
   PRIMARY KEY [ USING INDEX TABLESPACE tablespace ] |
   CHECK (expression) |
   REFERENCES ref_table [ ( ref_column ) ]
   [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
   [ ON DELETE action ] [ ON UPDATE action ] 
}
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
SQL

而是：

[ 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 ref_table [ ( ref_column [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
SQL

CREATE TABLE

根据查询结果定义一个新表格。

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

CREATE TABLESPACE

定义一个新的表空间。

CREATE TABLESPACE tablespace_name [ OWNER username ] LOCATION 'directory'
SQL

CREATE TRIGGER

定义一个新的触发器。

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

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

CREATE USER

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

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

在这里，option可以是：

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

CREATE VIEW

定义一个新视图。

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

DEALLOCATE

释放一个已经准备好的语句。

DEALLOCATE [ PREPARE ] plan_name
SQL

DECLARE

定义一个游标。

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

DELETE

删除表中的行。

DELETE FROM [ ONLY ] table [ WHERE condition ]
SQL

DROP AGGREGATE

删除一个聚合函数。

DROP AGGREGATE name ( type ) [ CASCADE | RESTRICT ]
SQL

DROP CAST

移除一个转换。

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

DROP CONVERSION

移除一个转换。

DROP CONVERSION name [ CASCADE | RESTRICT ]
SQL

DROP DATABASE

删除一个数据库。

DROP DATABASE name
SQL

DROP DOMAIN

删除一个域名。

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

DROP FUNCTION

删除一个函数。

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

DROP GROUP

删除用户组。

DROP GROUP name
SQL

DROP INDEX

移除一个索引。

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

DROP LANGUAGE

删除一种过程化语言。

DROP [ PROCEDURAL ] LANGUAGE name [ CASCADE | RESTRICT ]
SQL

DROP OPERATOR

删除一个操作符。

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

DROP OPERATOR CLASS

移除一个操作符类

DROP OPERATOR CLASS name USING index_method [ CASCADE | RESTRICT ]
SQL

DROP RULE

删除重写规则。

DROP RULE name ON relation [ CASCADE | RESTRICT ]
SQL

DROP SCHEMA

删除一个架构。

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

DROP SEQUENCE

删除一个序列。

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

DROP TABLE

删除一个表。

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

DROP TABLESPACE

移除一个表空间。

DROP TABLESPACE tablespace_name
SQL

DROP TRIGGER

移除一个触发器。

DROP TRIGGER name ON table [ CASCADE | RESTRICT ]
SQL

DROP TYPE

删除数据类型。

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

DROP USER

移除数据库用户账户。

DROP USER name
SQL

DROP VIEW

删除一个视图。

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

END

提交当前事务。

END [ WORK | TRANSACTION ]
SQL

EXECUTE

执行预编译语句。

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

EXPLAIN

显示一条语句的执行计划。

EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
SQL

FETCH

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

FETCH [ direction { FROM | IN } ] cursor_name
SQL

方向可以为空或其中之一−

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

GRANT

定义访问权限。

GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] table_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE db_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON FUNCTION func_name ([type, ...]) [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
SQL

INSERT

在表格中创建新的行。

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

LISTEN

监听通知。

LISTEN name
SQL

LOAD

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

LOAD 'filename'
SQL

LOCK

锁定一个表。

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

其中 lock_mode 是 − 中的一个

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

MOVE

定位一个光标。

MOVE [ direction { FROM | IN } ] cursor_name
SQL

NOTIFY

生成一个通知。

NOTIFY name
SQL

PREPARE

为执行准备一个声明。

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

REINDEX

重建索引。

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

RELEASE SAVEPOINT

销毁先前定义的保存点。

RELEASE [ SAVEPOINT ] savepoint_name
SQL

RESET

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

RESET name
RESET ALL
SQL

REVOKE

移除访问权限。

REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] table_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE db_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
ON FUNCTION func_name ([type, ...]) [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
SQL

ROLLBACK

终止当前事务。

ROLLBACK [ WORK | TRANSACTION ]
SQL

ROLLBACK SAVEPOINT

回滚到一个保存点。

ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name
SQL

SAVEPOINT

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

SAVEPOINT savepoint_name
SQL

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

SELECT ON

从查询结果中定义一个新表格。

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

SET

更改运行时参数。

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

SET CONSTRAINTS

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

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

SET SESSION AUTHORIZATION

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

SET [ SESSION | LOCAL ] SESSION AUTHORIZATION username
SET [ SESSION | LOCAL ] SESSION AUTHORIZATION DEFAULT
RESET SESSION AUTHORIZATION
SQL

SET TRANSACTION

设置当前事务的特性。

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

其中transaction_mode为以下之一−

ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED
| READ UNCOMMITTED }
READ WRITE | READ ONLY
SQL

SHOW

显示运行时参数的值。

SHOW name
SHOW ALL
SQL

START TRANSACTION

开始一个事务块。

START TRANSACTION [ transaction_mode [, ...] ]
SQL

其中transaction_mode是−中的一个

ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED
| READ UNCOMMITTED }
READ WRITE | READ ONLY
SQL

TRUNCATE

清空表格。

TRUNCATE [ TABLE ] name
SQL

UNLISTEN

停止监听通知。

UNLISTEN { name | * }
SQL

UPDATE

更新表格中的行。

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

FULL

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

VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]
SQL

Python教程

Python 教程

Python 教程
Tkinter 教程

Tkinter 教程
Pandas 教程

Pandas 教程
NumPy 教程

NumPy 教程
Flask 教程

Flask 教程
Django 教程

Django 教程
PySpark 教程

PySpark 教程
wxPython 教程

wxPython 教程
SymPy 教程

SymPy 教程
Seaborn 教程

Seaborn 教程
SciPy 教程

SciPy 教程
RxPY 教程

RxPY 教程
Pycharm 教程

Pycharm 教程
Pygame 教程

Pygame 教程
PyGTK 教程

PyGTK 教程
PyQt 教程

PyQt 教程
PyQt5 教程

PyQt5 教程
PyTorch 教程

PyTorch 教程
Matplotlib 教程

Matplotlib 教程
Web2py 教程

Web2py 教程
BeautifulSoup 教程

BeautifulSoup 教程

Java教程

Java 教程

Java 教程

Web教程

HTML 教程

HTML 教程
CSS 教程

CSS 教程
CSS3 教程

CSS3 教程
jQuery 教程

jQuery 教程
Ajax 教程

Ajax 教程
AngularJS 教程

AngularJS 教程
TypeScript 教程

TypeScript 教程
WordPress 教程

WordPress 教程
Laravel 教程

Laravel 教程
Next.js 教程

Next.js 教程
PhantomJS 教程

PhantomJS 教程
Three.js 教程

Three.js 教程
Underscore.JS 教程

Underscore.JS 教程
WebGL 教程

WebGL 教程
WebRTC 教程

WebRTC 教程
VueJS 教程

VueJS 教程

数据库教程

SQL 教程

SQL 教程
MySQL 教程

MySQL 教程
MongoDB 教程

MongoDB 教程
PostgreSQL 教程

PostgreSQL 教程
SQLite 教程

SQLite 教程
Redis 教程

Redis 教程
MariaDB 教程

MariaDB 教程

图形图像教程

Vulkan 教程

Vulkan 教程
OpenCV 教程

OpenCV 教程

大数据教程

R语言 教程

R语言 教程

开发工具教程

Git 教程

Git 教程
VSCode 教程

VSCode 教程
Docker 教程

Docker 教程
Gerrit 教程

Gerrit 教程
Excel 教程

Excel 教程

计算机教程

Go语言 教程

Go语言 教程
C++ 教程

C++ 教程

PostgreSQL 精品教程

登录

找回密码？

注册