postgresql基本命令操作

前端之家收集整理的这篇文章主要介绍了postgresql基本命令操作前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

postgresql基本命令操作:

登陆数据库

[postgres@localhost ~]$ psql -Utestwjw -h 127.0.0.1 -dpostgres -p 36985

Password for user testwjw:

psql.bin (9.5.9)

Type "help" for help.


postgres=>

切换数据库

postgres=> \c testdb1

You are now connected to database "testdb1" as user "testwjw".

查看所有的数据库

testdb1=> \l

testdb1=> \list

List of databases

Name | Owner | Encoding | Collate | Ctype | Access privileges

-----------+----------+----------+-------------+-------------+-----------------------

postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +

| | | | | postgres=CTc/postgres

template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +

| | | | | postgres=CTc/postgres

testdb1 | testwjw | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/testwjw +

| | | | | testwjw=CTc/testwjw

testdb2 | testwjw | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

(5 rows)


查看所有的表:

testdb1=> \dt

List of relations

Schema | Name | Type | Owner

--------+-------+-------+---------

public | t | table | testwjw

public | t1 | table | testwjw

public | tlb01 | table | testwjw

(3 rows)


testdb1=>


创建数据库

[postgres@localhost ~]$ psql -p 36985

psql.bin (9.5.9)

Type "help" for help.


postgres=# create database testdb3 with encoding='utf8' owner=testwjw;

CREATE DATABASE


[postgres@localhost ~]$ createdb testdb5 -p 36985

[postgres@localhost ~]$ createdb testdb6 -p 36985


查看创建的数据库

[postgres@localhost ~]$ psql -p 36985 -c '\list'|egrep "testdb4|testdb5"

testdb4 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

testdb5 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |


删除创建的数据库

#以testwjw的身份连接服务器,删除testdb1数据库

[postgres@localhost ~]$ dropdb -Utestwjw -p 36985 -e testdb1

DROP DATABASE testdb1;

[postgres@localhost ~]$ psql -p 36985 -c '\list'|grep "testdb1"

通过查看系统表验证该数据库是否已经被删除:

[postgres@localhost ~]$ psql -p 36985 -c "SELECT count(*) FROM pg_database WHERE datname ='testdb1'"

count

-------

0

(1 row)

证明此数据库确实被删除


查看数据库中所有的表以及单表结构:

testdb2=# \dt

List of relations

Schema | Name | Type | Owner

--------+------+-------+---------

public | tlb2 | table | testwjw

(1 row)


testdb2=# \d tlb2

Table "public.tlb2"

Column | Type | Modifiers

--------+-----------------------+-----------

id | integer |

pay | character varying(20) |

name | character varying(6) |

Indexes:

"uniq" UNIQUE CONSTRAINT,btree (id)


testdb2=#

查看索引详细信息:

testdb2=# \d uniq;

Index "public.uniq"

Column | Type | Definition

--------+---------+------------

id | integer | id

unique,btree,for table "public.tlb2"


\d+ 命令:将会显示比\d命令更详细的信息,除了前面介绍的那些,它还会显示任何与表列相关的注释,以及表中出现的OID。

testdb2=# \d+

List of relations

Schema | Name | Type | Owner | Size | Description

--------+------+-------+---------+---------+-------------

public | tlb2 | table | testwjw | 0 bytes |

(1 row)


testdb2=# \d

List of relations

Schema | Name | Type | Owner

--------+------+-------+---------

public | tlb2 | table | testwjw

(1 row)


testdb2=#


列出所有的schemas:


testdb2=# \dn

List of schemas

Name | Owner

--------+----------

public | postgres

(1 row)


创建schema:

testdb2=# create schema sa;

CREATE SCHEMA


testdb2=# \dn

List of schemas

Name | Owner

--------+----------

public | postgres

sa | postgres

(2 rows)


testdb2=#


显示sql执行的时间,可以使用\timing参数:

testdb2=# \timing

Timing is on.

testdb2=# select * from tlb2;

id | pay | name

----+-----+------

(0 rows)


Time: 0.177 ms

testdb2=#


如果想列出数据库中所有的角色或者用户,可以使用\du \dg,这两个命令等价,因为postgressql用户和角色不区分:

testdb2=# \du

List of roles

Role name | Attributes | Member of

-----------+------------------------------------------------------------+-----------

postgres | Superuser,Create role,Create DB,Replication,Bypass RLS | {}

testwjw | | {}


testdb2=# \dg

List of roles

Role name | Attributes | Member of

-----------+------------------------------------------------------------+-----------

postgres | Superuser,Bypass RLS | {}

testwjw | | {}


testdb2=#


查看表字段:

testdb2=# SELECT a.attname from pg_class c,pg_attribute a,pg_type t where c.relname='tlb2' and a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oid;

attname

---------

id

pay

name

(3 rows)

Time: 0.586 ms

testdb2=# \dnp+

List of schemas

Name | Owner | Access privileges | Description

--------+----------+----------------------+------------------------

public | postgres | postgres=UC/postgres+| standard public schema

| | =UC/postgres |

sa | postgres | |

(2 rows)


testdb2=# \dn

List of schemas

Name | Owner

--------+----------

public | postgres

sa | postgres

(2 rows)

testdb2=#

创建表:

testdb2=# \dt

List of relations

Schema | Name | Type | Owner

--------+------+-------+---------

public | tlb2 | table | testwjw

(1 row)

建表:

testdb2=# CREATE TABLE products (

product_no integer,

name text,

price numeric

);

CREATE TABLE

查看表:

testdb2=# \dt

List of relations

Schema | Name | Type | Owner

--------+----------+-------+----------

public | products | table | postgres

public | tlb2 | table | testwjw

(2 rows)

删除表:

testdb2=# drop table products;

DROP TABLE

testdb2=# \dt

List of relations

Schema | Name | Type | Owner

--------+------+-------+---------

public | tlb2 | table | testwjw

(1 row)

testdb2=#

列出所有的表空间:

postgres=# \db

List of tablespaces

Name | Owner | Location

---------------+----------+--------------------------

my_tablespace | postgres | /data/postgresql/mydata

pg_default | postgres |

pg_global | postgres |

tbspace01 | postgres | /data/postgresql/tbspace

(4 rows)

猜你在找的Postgre SQL相关文章