【赵渝强老师】使用PostgreSQL客户端工具

在PostgreSQL数据库服务器端安装配置完成以后，便可以使用客户端工具连接到服务器端进行数据库操作了。PostgreSQL支持多种客户端的连接，这里重点介绍psql和pgAdmin这两款PostgreSQL客户端工具的使用方法。

视频讲解如下：
https://www.bilibili.com/video/BV1tryhYHErY/?aid=113339724210...

一、命令行客户端psql

psql是PostgreSQL自带的命令行客户端工具。它能够交互式地键入查询，把它们发送给PostgreSQL，并且查看查询结果；或者输入可以来自于一个文件或者命令行参数。此外，psql还提供一些元命令和多种类似shell的特性来编写脚本和实现自动化任务提供便利。因此从功能上看，psql等同于Oracle中的sqlplus。执行该命令连接数据库时, 默认的用户和数据库是postgres。psql的命令格式如下：

psql -h <hostname or ip> -p <端口> [数据库名称] [用户名称]

通过下面的命令可以查看psql的帮助信息：

[postgres@mydb pgsql]$ bin/psql --help

# 输出的信息如下：
psql is the PostgreSQL interactive terminal.
Usage:
  psql [OPTION]... [DBNAME [USERNAME]]
....
Connection options:
  -h, --host=HOSTNAME      database server host or socket directory 
(default: "local socket")
  -p, --port=PORT           database server port (default: "5432")
  -U, --username=USERNAME  database user name (default: "postgres")
  -w, --no-password         never prompt for password
  -W, --password             force password prompt 
(should happen automatically)
...

psql的两种操作模式：交互式模式和非交互式模式，下面分别进行介绍。

1.1 psql的交互式模式

直接在操作系统的命令行界面上键入bin/psql并回车，从操作系统提示符切换到psql提示符后就表示已经进入了psql 的交互模式界面，就可以执行命令了。在psql的交互式模式下，输入的命令以分号作为命令结束标记。

视频讲解如下：
https://www.bilibili.com/video/BV1ZuyVYJEps/?aid=113344723820...

下表说明了psql提供的常用命令。

下面创建一个测试用的数据库scott，该数据库中包含两张表，即：员工表（emp）和部门表（dept）。
（1）创建scott数据库。

mydemodb=# create database scott;
mydemodb=# \c scott 

（2）创建员工表和部门表。

scott=# create table dept
     (deptno int primary key,
     dname varchar(10),
     loc varchar(10)
     );

scott=# create table emp 
      (empno int primary key,
      ename varchar(10),
      job varchar(10),
      mgr int,
      hiredate varchar(10),
      sal int,
      comm int,
      deptno int,
      foreign key(deptno) references dept(deptno));

（3）在PostgreSQL的命令提示符下往部门表和员工表中插入数据。

-- 插入部门表数据
scott=# insert into dept values(10,'ACCOUNTING','NEW YORK');
scott=# insert into dept values(20,'RESEARCH','DALLAS');
scott=# insert into dept values(30,'SALES','CHICAGO');
scott=# insert into dept values(40,'OPERATIONS','BOSTON');

-- 插入员工表数据
scott=# insert into emp values (7369,'SMITH','CLERK',7902,'1980/12/17',800,null,20);
scott=# insert into emp values (7499,'ALLEN','SALESMAN',7698,'1981/2/20',1600,300,30);
scott=# insert into emp values (7521,'WARD','SALESMAN',7698,'1981/2/22',1250,500,30);
scott=# insert into emp values (7566,'JONES','MANAGER',7839,'1981/4/2',2975,null,20);
scott=# insert into emp values (7654,'MARTIN','SALESMAN',7698,'1981/9/28',1250,1400,30);
scott=# insert into emp values (7698,'BLAKE','MANAGER',7839,'1981/5/1',2850,null,30);
scott=# insert into emp values (7782,'CLARK','MANAGER',7839,'1981/6/9',2450,null,10);
scott=# insert into emp values (7788,'SCOTT','ANALYST',7566,'1987/4/19',3000,null,20);
scott=# insert into emp values (7839,'KING','PRESIDENT',-1,'1981/11/17',5000,null,10);
scott=# insert into emp values (7844,'TURNER','SALESMAN',7698,'1981/9/8',1500,null,30);
scott=# insert into emp values (7876,'ADAMS','CLERK',7788,'1987/5/23',1100,null,20);
scott=# insert into emp values (7900,'JAMES','CLERK',7698,'1981/12/3',950,null,30);
scott=# insert into emp values (7902,'FORD','ANALYST',7566,'1981/12/3',3000,null,20);
scott=# insert into emp values (7934,'MILLER','CLERK',7782,'1982/1/23',1300,null,10);

1.2 psql的非交互式模式

要在非交互模式下使用psql，需要从操作系统的命令提示符执行psql命令，并给其传送一个脚本文件。psql的非交互模式是指在调用psql时直接以选项的形式指定要执行的脚本，脚本中可以含有任意数量的SQL和psql语句，然后psql会自动执行此脚本的内容，期间无需与用户进行交互，这就是非交互模式的本意。要在非交互模式下执行脚本文件，使用-f选项即可。下面通过一个示例来说明psql的非交互式模式的使用方法。

视频讲解如下：
https://www.bilibili.com/video/BV1i2ynYMEQQ/?aid=113348649754...

（1）编辑脚本文件myscript，并输入要执行的SQL和psql语句，如下：

\l
\db
select datname from pg_database;

（2）执行脚本

bin/psql -f myscript

# 输出的结果如下：
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |...
-----------+----------+----------+-------------+-------------+...
 mydemodb  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |...
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |...
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |...
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |...
(4 rows)

                         List of tablespaces
    Name    |  Owner   |                   Location                   
------------+----------+-------------------------------------------
 mydemotbs  | postgres | /home/postgres/training/pgsql/data/mydemotbs
 pg_default | postgres | 
 pg_global  | postgres | 
(3 rows)

  datname  
-----------
 postgres
 mydemodb
 template1
 template0
(4 rows)

二、图形化客户端pgAdmin

pgAdmin 4是一款为PostgreSQL设计的可靠和全面的数据库设计和管理软件，它允许用户连接到特定的数据库，创建表和运行各种从简单到复杂的SQL语句。它支持的操作系统包括Linux，Windows，Mac OS X。下图展示了pgAdmin的下载界面。

这里以Windows版本为例进行介绍。由于pgAdmin 4安装比较简单，这里就不在进行介绍了。pgAdmin 4安装完成后直接启动即可。第一次运行pgAdmin 4的时候，需要设置管理员的密码。如下图所示。

要使用pgAdmin 4连接PostgreSQL数据库服务器，需要对PostgreSQL数据库服务器端进行相应的配置。下面的步骤展示了整个配置的过程。
（1）修改postgresql.conf文件将参数listen_addresses设置为接收所有客户机地址。

listen_addresses = '*'

# 该参数的默认值是localhost，即：只接受当前主机的客户端请求。

（2）修改配置文件pg_hba.conf，增加下面的参数配置。

host all all 0.0.0.0/0 md5

# 这里设置的“host all all 0.0.0.0/0 md5”表示
# 任何主机均可以以md5加密认证的方式访问任何数据库。

如果不配置该参数会出现下面的错误信息，如下图所示。

（3）重启PostgreSQL数据库服务器。

bin/pg_ctl -D data/ -l logfile restart

（4）由于在安装PostgreSQL数据库服务器时，没有为用户postgres设置密码。因此这里需要重置一下该用户的密码。使用psql命令行客户端登录PostgreSQL数据库服务器，执行下面的命令设置用户postgres用户的密码。

postgres=# alter user postgres with password 'Welcome_1';

（5）在pgAdmin 4图形工具上，右键点击“Servers”，并选择“Register->Server...”，如下图所示。

（6）在弹出对话框的“General”选项卡中输入“Name”，如下图所示。

（7）在“Connection”选项卡中输入“Host name/address”和“Password”，点击“Save”。如下图所示。

（8）此时便可以成功登录PostgreSQL数据库服务器，如下图所示。