转psql命令（二）
psql命令大全（2）2008-07-22 16:06命令: 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 可以为空或下面的一种:
NEXT
PRIOR
FIRST
LAST
ABSOLUTE count
RELATIVE count
count
ALL
FORWARD
FORWARD count
FORWARD ALL
BACKWARD
BACKWARD count
BACKWARD 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 DEFAULT
RESET 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 [ 表 [ (列 [, ...] ) ] ]
