建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網營銷解決方案
PostgreSQL語法
本章提供了PostgreSQL的SQL命令，這些命令的準確語法規(guī)則。這組命令是從psql的命令行工具。上一節(jié)我們已經安裝Postgres，直接打開psql：
專注于為中小企業(yè)提供網站設計制作、成都網站設計服務,電腦端+手機端+微信端的三站合一,更高效的管理,為中小企業(yè)大連免費做網站提供優(yōu)質的服務。我們立足成都，凝聚了一批互聯(lián)網行業(yè)人才，有力地推動了數(shù)千家企業(yè)的穩(wěn)健成長，幫助中小企業(yè)通過網站建設實現(xiàn)規(guī)模擴充和轉變。
Program Files > PostgreSQL 9.2 > SQL Shell(psql).
使用psql的時候，可以生成完整的命令列表使用的\ help命令。對于一個特定的命令的語法使用下面的命令：
- postgres-# \help
SQL語句
一個SQL語句，包括令牌，每個令牌可以代表一個關鍵字，標識符，帶引號的標識符，常量，或特殊的字符符號。以下表格使用簡單的SELECT語句來說明一個基本的，完整的SQL語句及其組件。
| SELECT | id, name | FROM | states | |
|---|---|---|---|---|
| Token Type | Keyword | Identifiers | Keyword | Identifier |
| Description | Command | Id and name columns | Clause | Table name |
PostgreSQL的SQL命令
ABORT
中止當前事務。
- ABORT [ WORK | TRANSACTION ]
ALTER AGGREGATE
更改聚合函數(shù)的定義。
- 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
更改數(shù)據(jù)庫的具體參數(shù)。
- 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
變更的定義域的具體參數(shù)。
- 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
改變它的定義了一個函數(shù)。
- 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
更改序列發(fā)生器的定義.
- 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
動作是以下行之一:
- 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
ALTER TABLESPACE
更改一個表空間的定義.
- ALTER TABLESPACE name RENAME TO new_name ALTER TABLESPACE name OWNER TO new_owner
ALTER TRIGGER
改變一個觸發(fā)器的定義.
- ALTER TRIGGER name ON table RENAME TO new_name
ALTER TYPE
Change the definition of a type.
- ALTER TYPE name OWNER TO new_owner
ALTER USER
更改數(shù)據(jù)庫用戶帳戶
- 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
收集數(shù)據(jù)庫的統(tǒng)計信息.
- ANALYZE [ VERBOSE ] [ table [ (column [, ...] ) ] ]
BEGIN
Start a transaction block.
- BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]
transaction_mode是其中一個:
- ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED } READ WRITE | READ ONLY
CHECKPOINT
強制事務處理日志檢查點.
- CHECKPOINT
CLOSE
關閉游標.
- CLOSE name
CLUSTER
根據(jù)索引集群表.
CLUSTER index_name ON table_name CLUSTER table_name CLUSTER
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'COMMIT
提交當前事務.
COMMIT [ WORK | TRANSACTION ]
COPY
Copy data between a file and a table.
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
Define a new aggregate function.
CREATE AGGREGATE name ( BASETYPE = input_data_type, SFUNC = sfunc, STYPE = state_data_type [ , FINALFUNC = ffunc ] [ , INITCOND = initial_condition ] )
CREATE CAST
Define a new 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
定義一個新的約束觸發(fā)器
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
創(chuàng)建一個新的數(shù)據(jù)庫.
CREATE DATABASE name [ [ WITH ] [ OWNER [=] db_owner ] [ TEMPLATE [=] template ] [ ENCODING [=] encoding ] [ TABLESPACE [=] tablespace ] ]
CREATE DOMAIN
定義一個新的域.
CREATE DOMAIN name [AS] data_type [ DEFAULT expression ] [ constraint [ ... ] ]
Where constraint is:
[ CONSTRAINT constraint_name ] { NOT NULL | NULL | CHECK (expression) }CREATE FUNCTION
定義一個新的函數(shù).
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
定義一個新的重寫規(guī)則.
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
定義一個新的序列發(fā)生器.
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 http://fisionsoft.com.cn/article/dhjhgie.html
