PostgreSQL常用命令有哪些

这篇文章主要介绍PostgreSQL常用命令有哪些，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！

查看版本信息

mydb=# SELECT version();                                                version                                                ------------------------------------------------------------------------------------------------------- PostgreSQL 9.3.6 on i686-pc-linux-gnu, compiled by gcc (GCC) 4.4.6 20110731 (Red Hat 4.4.6-3), 32-bit(1 row)

创建数据库

mydb=# CREATE DATABASE test;CREATE DATABASE

显示所有数据库

mydb-# \l                                List of databases   Name    |  Owner  | Encoding |   Collate   |    Ctype    |  Access privileges  -----------+---------+----------+-------------+-------------+--------------------- 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/postgre template1 | postgre | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgre         +           |         |          |             |             | postgre=CTc/postgre test      | postgre | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | (5 rows)

切换当前数据库

mydb-# \c testPassword for user postgre: You are now connected to database "test" as user "postgre".

删除数据库

# 不能删除当前连接的数据库test=# DROP DATABASE test;            ERROR:  cannot drop the currently open databasetest=# DROP DATABASE mydb;DROP DATABASE

列举当前数据库表

test=# create table t as select * from pg_tablespace;SELECT 2test=# \dt        List of relations Schema | Name | Type  |  Owner  --------+------+-------+--------- public | t    | table | postgre(1 row)

查看表结构

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

重命名表

test=# alter table t rename to t_t;ALTER TABLEtest=# \dt        List of relations Schema | Name | Type  |  Owner  --------+------+-------+--------- public | t_t  | table | postgre(1 row)

查看表索引

# 创建索引test=# create index idx_name on t (spcname);CREATE INDEX# 显示索引test=# \di ;              List of relations Schema |   Name   | Type  |  Owner  | Table --------+----------+-------+---------+------- public | idx_name | index | postgre | t public | idx_t    | index | postgre | t_t(2 rows)

删除表

test=# drop table t_t;DROP TABLE

创建用户

test=# CREATE USER svoid;CREATE ROLE# 创建sovid 指定密码test=# CREATE USER svoid WITH PASSWORD '123qwe';CREATE ROLE

查看系统用户信息

test=# SELECT usename FROM pg_user; usename --------- postgre svoid(2 rows)test=# \du;                             List of roles Role name |                   Attributes                   | Member of -----------+------------------------------------------------+----------- postgre   | Superuser, Create role, Create DB, Replication | {} svoid     |                                                | {}

删除用户

test=# DROP USER svoid;DROP ROLE

查看schema

test=# \dn; List of schemas  Name  |  Owner  --------+--------- public | postgre(1 row)

创建schema

test=# CREATE SCHEMA myschema;CREATE SCHEMA

删除schema

test=# DROP SCHEMA myschema;DROP SCHEMA

以上是"PostgreSQL常用命令有哪些"这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注行业资讯频道！