前面介绍了 PostgreSQL 基础概念及安装部署、目录结构与配置文件 postgresql.conf、常用管理命令、访问控制与认证 相关的知识点,今天我将详细的为大家介绍 PostgreSQL 语法 相关知识,希望大家能够从中收获多多!如有帮助,请点在看、转发支持一波!!!
默认情况下 PostgreSQL 安装完成后,自带了一个命令行工具SQL Shell(psql)
。
Linux 系统可以直接切换到 postgres 用户来开启命令行工具:
# sudo -i -u postgres
Windows系统一般在它的安装目录下:
Program Files → PostgreSQL 11.3 → SQL Shell(psql)
Mac OS我们直接搜索就可以了找到:
进入命令行工具,我们可以使用 \help 来查看各个命令的语法:
postgres-# \help <command_name>
例如,我们查看下 select 语句的语法:
postgres=# \help SELECT
Command: SELECT
Description: retrieve rows from a table or view
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
[ * | expression [ [ AS ] output_name ] [, ...] ]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition [, ...] ]
[ WINDOW window_name AS ( window_definition ) [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
[ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
#from_item 可以是以下选项之一
[ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
更多关于大数据 PostgreSQL 系列的学习文章,请参阅:PostgreSQL 数据库,本系列持续更新中。
SQL 语句
一个 SQL 语句通常包含了关键字、标识符(字段)、常量、特殊符号等,下面是一个简单的 SQL 语句:
SELECT id, name FROM runoob
PostgreSQL 命令
ABORT
ABORT 用于退出当前事务。
ABORT [ WORK | TRANSACTION ]
ALTER AGGREGATE
修改一个聚集函数的定义 。
ALTER AGGREGATE _name_ ( _argtype_ [ , ... ] ) RENAME TO _new_name_
ALTER AGGREGATE _name_ ( _argtype_ [ , ... ] ) OWNER TO _new_owner_
ALTER AGGREGATE _name_ ( _argtype_ [ , ... ] ) SET SCHEMA _new_schema_
ALTER COLLATION
修改一个排序规则定义 。
ALTER COLLATION _name_ RENAME TO _new_name_
ALTER COLLATION _name_ OWNER TO _new_owner_
ALTER COLLATION _name_ SET SCHEMA _new_schema_
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 DEFAULT PRIVILEGES
定义默认的访问权限。
ALTER DEFAULT PRIVILEGES
[ FOR { ROLE | USER } target_role [, ...] ]
[ IN SCHEMA schema_name [, ...] ]
abbreviated_grant_or_revoke
where abbreviated_grant_or_revoke is one of:
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON TABLES
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
...
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