PostgreSQL常用命令有哪些

查看版本信息

mydb=#SELECTversion();version-------------------------------------------------------------------------------------------------------PostgreSQL9.3.6oni686-pc-linux-gnu,compiledbygcc(GCC)4.4.620110731(RedHat4.4.6-3),32-bit(1row)

创建数据库

mydb=#CREATEDATABASEtest;CREATEDATABASE

显示所有数据库

mydb-#\lListofdatabasesName|Owner|Encoding|Collate|Ctype|Accessprivileges-----------+---------+----------+-------------+-------------+---------------------mydb|postgre|UTF8|zh_CN.UTF-8|zh_CN.UTF-8|postgres|postgre|UTF8|zh_CN.UTF-8|zh_CN.UTF-8|template0|postgre|UTF8|zh_CN.UTF-8|zh_CN.UTF-8|=c/postgre+|||||postgre=CTc/postgretemplate1|postgre|UTF8|zh_CN.UTF-8|zh_CN.UTF-8|=c/postgre+|||||postgre=CTc/postgretest|postgre|UTF8|zh_CN.UTF-8|zh_CN.UTF-8|(5rows)

切换当前数据库

mydb-#\ctestPasswordforuserpostgre:Youarenowconnectedtodatabase"test"asuser"postgre".

删除数据库

#不能删除当前连接的数据库test=#DROPDATABASEtest;ERROR:cannotdropthecurrentlyopendatabasetest=#DROPDATABASEmydb;DROPDATABASE

列举当前数据库表

test=#createtabletasselect*frompg_tablespace;SELECT2test=#\dtListofrelationsSchema|Name|Type|Owner--------+------+-------+---------public|t|table|postgre(1row)

查看表结构

test=#\dtTable"public.t"Column|Type|Modifiers------------+-----------+-----------spcname|name|spcowner|oid|spcacl|aclitem[]|spcoptions|text[]|

重命名表

test=#altertabletrenametot_t;ALTERTABLEtest=#\dtListofrelationsSchema|Name|Type|Owner--------+------+-------+---------public|t_t|table|postgre(1row)

查看表索引

#创建索引test=#createindexidx_nameont(spcname);CREATEINDEX#显示索引test=#\di;ListofrelationsSchema|Name|Type|Owner|Table--------+----------+-------+---------+-------public|idx_name|index|postgre|tpublic|idx_t|index|postgre|t_t(2rows)

删除表

test=#droptablet_t;DROPTABLE

创建用户

test=#CREATEUSERsvoid;CREATEROLE#创建sovid指定密码test=#CREATEUSERsvoidWITHPASSWORD'123qwe';CREATEROLE

查看系统用户信息

test=#SELECTusenameFROMpg_user;usename---------postgresvoid(2rows)test=#\du;ListofrolesRolename|Attributes|Memberof-----------+------------------------------------------------+-----------postgre|Superuser,Createrole,CreateDB,Replication|{}svoid||{}

删除用户

test=#DROPUSERsvoid;DROPROLE

查看schema

test=#\dn;ListofschemasName|Owner--------+---------public|postgre(1row)

创建schema

test=#CREATESCHEMAmyschema;CREATESCHEMA

删除schema

test=#DROPSCHEMAmyschema;DROPSCHEMA

postgresql