postgresql 如何使用 psql 命令列出、创建、使用和检查数据库？

How to use the psql command to list, create, use and examine databases?

提问by zabouti

I'm a postgreSQL newbie and I can't find any usable introduction to using the psql command. At least I think that's the command I want to use.

我是 postgreSQL 新手，我找不到任何关于使用 psql 命令的有用介绍。至少我认为这是我想要使用的命令。

Is it possible in postgreSQL to simply connect to the server and then list, create, use and examine databases?

在 postgreSQL 中是否可以简单地连接到服务器，然后列出、创建、使用和检查数据库？

I'd like to be able to use psql to do something like this with MySQL (I've deleted many extra lines):

我希望能够使用 psql 用 MySQL 做这样的事情（我已经删除了很多额外的行）：

Connect without specifying a database - I can't seem to do that with psql:

连接而不指定数据库 - 我似乎无法用 psql 做到这一点：

$ mysql -u root -prootpassword
Welcome to the MySQL monitor.  Commands end with ; or \g.
Server version: 5.5.28 MySQL Community Server (GPL)

I can list databases with mysql but the posgreSQL command SHOW doesn't seem to do it.

我可以用 mysql 列出数据库，但 posgreSQL 命令 SHOW 似乎没有这样做。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| ocdp               |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.04 sec)

I can switch databases.

我可以切换数据库。

mysql> use ocdp;
Database changed

I can't figure out this command in psql:

我无法在 psql 中找出这个命令：

mysql> show tables;
+---------------------------------+
| Tables_in_ocdp                  |
+---------------------------------+
| OCAddresses                     |
| OCStreets                       |
+---------------------------------+
2 rows in set (0.00 sec)

I think I can do this in psql with 'createdb' and 'dropdb' commands:

我想我可以在 psql 中使用“createdb”和“dropdb”命令来做到这一点：

mysql> create database foo;
Query OK, 1 row affected (0.00 sec)

mysql> drop database foo;
Query OK, 0 rows affected (0.03 sec)

I use \quit

我使用 \quit

mysql> quit
Bye

The answer to these questions should take only a moment for someone who knows postgreSQL but I just can't find documentation anywhere that shows how to do these simple operations. Maybe I shouldn't even be using psql at all for this?

对于了解 postgreSQL 的人来说，这些问题的答案应该只需要一点时间，但我在任何地方都找不到说明如何执行这些简单操作的文档。也许我根本不应该为此使用 psql ？

回答by SeanPlusPlus

connect to server:

连接到服务器：

$ mysql -u root -prootpassword

$ su - postgres
$ psql

list databases:

列出数据库：

mysql> show databases;

postgres=# \l

switch databases:

切换数据库：

mysql> use ocdp;

postgres=# \c ocdp

show tables:

显示表：

mysql> show tables;

postgres=# \dt

create database:

创建数据库：

mysql> create database foo;

postgres=# create database foo;

drop database:

删除数据库：

mysql> drop database foo;

postgres=# drop database foo;

quit:

退出：

mysql> quit

postgres=# \q

回答by msmukesh4

Try Using this command if you are using Mac-OS-X and for postgres 9.4

如果您使用的是 Mac-OS-X 和 postgres 9.4，请尝试使用此命令

psql --list

psql --list

回答by a_horse_with_no_name

Connect without specifying a database - I can't seem to do that with psql:

连接而不指定数据库 - 我似乎无法用 psql 做到这一点：

Quote from the manual

从手册中引用

The default user name is your Unix user name, as is the default database name

默认用户名是您的 Unix 用户名，默认数据库名称也是如此

---

---

I can switch databases

我可以切换数据库

Quote from the manual

从手册中引用

\connect [ dbname [ username ] [ host ] [ port ] ] Establishes a new connection to a PostgreSQL server

\connect [ dbname [ username ] [ host ] [ port ] ] 建立到 PostgreSQL 服务器的新连接

---

---

show tables

显示表格

Quote from the manual

从手册中引用

\d[S+] [ pattern ] For each relation (table, view, index, sequence, or foreign table) or composite type matching the pattern, show all columns

\d[S+] [ 模式 ] 对于匹配模式的每个关系（表、视图、索引、序列或外部表）或复合类型，显示所有列

---

---

create database foo;

创建数据库 foo;

This is the same statement in PostgreSQL, which is documented in the manual

这与 PostgreSQL 中的语句相同，手册中记录了该语句

---

---

quit

退出

Quote from the manual

从手册中引用

\q or \quit Quits the psql program.

\q 或 \quit 退出 psql 程序。

回答by Dinesh Pallapa

Postgresql with terminal

带有终端的 Postgresql

enter into the postgresql

进入postgresql

   sudo -u postgres psql

find list Of databases in postgresql by

通过以下方式查找 postgresql 中的数据库列表

   $ \l

connect to database by following command

通过以下命令连接到数据库

   $ \c databasename

view models in database by

查看数据库中的模型

   $ \dt

Now list of table present in database are displayed and perform operations on the tables like

现在显示数据库中存在的表列表并对表执行操作，例如

   $ select * from table;