Postgresql psql工具

朱雀 2024-02-18 12:28 57阅读 0赞

#### 文章目录 ####

*   *  psql介绍
     *  psql 的简单使用
     *  psql 常用命令
     *   *  \\h命令
         *  \\d命令
         *  指定客户端字符集的命令
         *  格式化输出的\\pset命令
         *  \\x命令
         *  执行存储在外部文件中的SQL命令
         *  编辑命令
         *  输出信息的\\echo 命令
         *  其他命令
     *  psql的使用技巧
     *   *  历史命令与补全功能
         *  自动提交技巧
         *  如何得到psql中快捷命令执行的实际SQL

### psql介绍 ###

psql是PostgreSQL中的一个命令行交互式客户端工具，类似Oracle中的命令行工具sqlplus，它允许用户交互地键入SQL语句或命令，然后将其发送给PostgreSQL服务器，再显示SQL语句或命令的结果。另外，所输入内容还可以来自一个文件。此外，它还提供了一些快捷命令和多种类似Shell的特性来实现书写脚本，以及对大量任务的自动化操作。虽然psql的功能与sqlplus差不多，但使用起来远比sqlplus简便，如psq工具可以用上下方向键把上一条和下一条SQL命令翻出来，还有单击Tab键自动补全的强大功能。

### psql 的简单使用 ###

如果已建好数据库，可以直接输入“psq|”进入命令交互输入模式：  
osdba@osdba-laptop：~$ psql  
psql （9.0.3）  
Type “help“ for help。  
osdba=\#  
进入命令交互输入模式后会显示psql版本，然后出现提示符，可以在此提示符下输入标准的SQL命令，也可以输入psq|工具特有的快捷命令，这些快捷命令都是以斜杠“\\” 开头的。  
为什么不需要输入用户名和密码？安装PostgresQL数据库时，会创建一个与初始化数据库时的操作系统用户同名的数据库用户，这个用户是数据库的超级用户，在此OS用户下登录数据库时，因为执行的是操作系统认证，所以是不需要用户名和密码的用户也可以通过修改pg\_hba。conf文件来要求用户输入密码。  
当然，psqI也支持直接使用命令行参数查询信息和执行SQL，这种非交互模式与使用一般的Linux命令没有区别，如使用“psql -”命令可以查看数据库：  
osdba@osdba-work：~$ psql -l  
也可以进入psqI的命令交互输入模式使用“V”命令查看有哪些数据库，与使用上面的“psql-”命令得到的结果是相同的。  
上面的查询结果中有一个叫“postgres”的数据库，这是默认postgreSQL安装完成后就有的一个数据库，还有两个模板数据库：template0和template1。当用户创建数据库时，默认是从模板数据库“template1”克隆来的，所以通常我们可以定制template1数据库中的内容，如向template1中添加一些表后函数，这样后续创建的数据库就会继承 template1中的内容，也会拥有这些表和函数。而templateO是一个最简化的模板库，如果创建数据库时明确指定从此数据库克隆，将创建出一个最简化的数据库。  
下面演示交互模式下的使用方法。  
使用“\\d”命令查看表的示例如下：  
osdba=\# create table t（id int primary key，name varchar（40））；  
还可以使用SQL语句“CREATE DATABASExxx”创建用户数据库，下面是创建 testdb数据库的SQL语句：  
osdba=\# CREATE DATABASE testdb；  
CREATE DATABASE  
然后使用“\\c testdb”命令连接到testdb数据库上：  
osdba=\#\\c testdb；  
You are now connected to database “testdb“ as user “osdba“  
testdb=\#  
下面介绍psql连接数据库的常用的方法，命令格式如下：  
psql -h -p＜端口>［数据库名称］［用户名称］ 示例如下：  
Lpostgres@db1~J$ psql -h 192.168.56.11-p 5432 testdb postgres  
psql （9.0.3）  
Type “help“ for help。  
testdb=\#  
其中-h指定要连接的数据库所在的主机名或IP地址，一p指定连接的数据库端口，最后两个参数分别是数据库名和用户名。  
这些连接参数也可以通过环境变量指定，示例如下：  
export PGDATABASE=testdb  
export PGHOST=192.168.56.11  
export PGPORT=5432  
export PGUSER=postgres  
然后运行psql， 其运行结果与“psql -h 192.168.56.11-p 5432 testdb postgres”的运行结果相同。

### psql 常用命令 ###

#### \\h命令 ####

使用psq|工具需要记住的第一个命令是“\\h”，该命令用于查询SQL语句的语法，如我们不知道如何用SQL语句创建用户，就可以执行“\\h create user”命令来查询。

#### \\d命令 ####

“\\d”命令的格式如下：  
\\d ［pattern］  
\\d［pattern ］+  
该命令将显示每个匹配“pattern” （表、视图、索引、序列）的信息，包括对象中所有的列、各列的数据类型、表空间（如果不是默认的）和所有特殊属性（诸如“NOT NULL”或默认值等）等。唯一约束相关的索引、规则、约束、触发器也同样会显示出来。如果关系是一个视图，还会显示视图的定义（“匹配模式”将在下面定义）。下面来看看该命令的具体用法。  
1）如果“\\d”命令后什么都不带，将列出当前数据库中的所有表，示例如下：  
osdba=\#\\d  
2）“\\d”命令后面跟一个表名，表示显示这个表的结构定义，示例如下：  
osdba=\#\\dt  
3）“\\d”命令也可以用于显示索引信息，示例如下：  
osdba=\#\\d t\_pkey  
4）“\\d”命令后面的表名或索引名中也可以使用通配符，如“\*”或“？”等，示例如下：  
osdba=\#\\d x？  
5）使用“\\d+”命令可以显示比“\\d”命令的执行结果更详细的信息，除了前面介绍的信息，还会显示所有与表的列关联的注释，以及表中出现的OID。示例如下：  
osdba=\#\\d+t  
6） 匹配不同对象类型的“\\d”命令如下：  
•如果只想显示匹配的表，可以使用“\\dt”命令。  
•如果只想显示索引，可以使用“\\d”命令。  
•如果只想显示序列，可以使用“\\ds”命令。  
•如果只想显示视图，可以使用“\\dv”命令。  
•如果想显示函数，可以使用“\\df”命令。  
7） 如果想显示执行SQL语句的时间，可以用“\\timing”命令，示例如下：  
osdba=\#\\timing on  
8）要想列出所有的schema，可以使用“\\dn”命令，示例如下：  
osdba=\#\\dn  
9）要想显示所有的表空间，可以用“\\db”命令，示例如下：  
osdba=\#\\db  
实际上，PostgreSQL中的表空间对应一个目录，放在这个表空间中的表，就是把表的数据文件放到该表空间下。  
10）要想列出数据库中的所有角色或用户，可以使用“\\du”或“ldg”命令，示例如下：  
osdba=\#\\dg  
“\\du”和“\\dg”命令等价。原因是，在PostgreSQL数据库中，用户和角色是不分的。  
11）“\\dp”或“\\z”命令用于显示表的权限分配情况，示例如下：  
osdba=\#\\dpt

#### 指定客户端字符集的命令 ####

当客户端的字符编码与服务器不一致时，可能会出现乱码，可以使用“\\encoding” 命令指定客户端的字符编码，如使用“\\encoding gbk；”命令设置客户端的字符编码为 “gbk”；使用“\\encoding utf8；”命令设置客户端的字符编码为“utf8” 。

#### 格式化输出的\\pset命令 ####

“\\pset”命令的语法如下：  
pset ［option ［value］］  
根据命令后面“option”和“value”的不同可以设置很多种不同的输出格式，这里只介绍一些常用的用法。  
默认情况下，psql中执行SQL语句后输出的内容是只有内边框的表格：  
osdba=\# select \* from class；  
如果要像MySQL中一样输出带有内外边框的表格内容，可以用命令“\\pset boder 2” 来实现，示例如下：  
osdba=\#\\pset border 2  
当然也可以用“\\pset boder O”命令输出不带任何边框的内容，示例如下：  
osdba=\#\\pset border 0  
综上所述， “pset“命令设置边框的用法如下。  
•\\pset border 0：表示输出内容无边框。  
•\\pset border 1：表示输出内容只有内边框。  
•\\pset border 2：表示输出内容内外都有边框。  
psq|中默认的输出格式是“\\pset border 1’。  
不管输出的内容加不加边框，内容本身都是对齐的，是为增强数据的可读性而专门格式化过的，而有时我们需要把命令的结果输出为其他程序可以读取的文件，如以逗号分隔或以Tab分隔的文本文件，这时就需要用到“\\pset format unaligned”命令了，示例如下：  
osdba=\#\\pset format unaligned  
默认分隔符是“Y”，我们可以用命令“\\pset fieldsep”来设置分隔符，如改成Tab分隔符的方法如下：  
osdba=\#\\pset fieldsep“\\t  
实际使用时，我们需要把SQL命令输出到一个文件中，而不是屏幕上，这时可以用 “\\o”命令指定一个文件，然后再执行上面的SQL命令，执行结果就会输出到这个文件中， 示例如下：  
osdba=\#\\pset format unaligned

#### \\x命令 ####

使用“\\x”命令可以把按行展示的数据变成按列展示，示例如下：  
osdba=\#\\×  
如果数据行太长出现折行，就可以使用这里介绍的“\\x”命令将其拆分为多行显示。  
这与MySQL中命令后加“\\G”的功能类似。

#### 执行存储在外部文件中的SQL命令 ####

命令“\\i<文件名>”用于执行存储在外部文件中的SQL语句或命令。示例如下：  
osdba=\#\\×  
Expanded display （expanded） is on。  
osdba=\# \\i getrunsql  
当然也可以在psq|命令行中加上“\_f”来执行SQL脚本文件中的命令，示例如下：  
osdba@osdba-work：~$ psql -x -f getrunsql  
其中命令行参数“ ”的作用相当于在psql交互模式下运行“\\x”命令。

#### 编辑命令 ####

编辑命令“\\e”可以用于编辑文件，也可用于编辑系统中已存在的函数或视图定义， 下面来举例说明此命令的使用方法。  
输入“\\e”命令后会调用一个编辑器，在Linux下通常是Vi，当“\\e”命令不带任何参数时则是生成一个临时文件，前面执行的最后一条命令会出现在临时文件中，当编辑完成后退出编辑器并回到psql中时会立即执行该命令：  
osdba=\#\\e  
这里输入“\\e”后，会进入Vi编辑器，退出Vi编辑器后就会执行Vi中编辑的内容，然后下面就显示出执行的内容  
在上面的操作中，我们在Vi中输入的内容为“‘select \* from class where no=1；” ’ 当退出Vi编辑器后，就会执行SQL语句“select \* from class where no=1；”，这条SQL 语句的内容在psql中是看不到的。  
“\\e后面也可以指定一个文件名，但要求这个文件必须存在，否则会报错：  
osdba=\# e 1.sgl  
1.sq ： No such file or directory  
可以用“\\ef”命令编辑一个函数的定义，如果“\\ef”后面不跟任何参数，则会出现一个编辑函数的模板  
如果“\\ef”后面跟一个函数名，则函数定义的内容会出现在Vi编辑器中，当编辑完成后按“wq：”保存并退出，再输入“，”就会执行所创建函数的SQL语句。  
同样输入“\\ev”且后面不跟任何参数时，在Vi中会出现一个创建视图的模板  
然后用户就可以在Vi中编辑这个创建视图的SQL语句，编辑完成后，保存并退出， 再输入分号“；”，就会执行所创建视图的SQL语句。  
也可以编辑已存在的视图的定义，只需在“\\ev”命令后面跟视图的名称即可。  
“\\ef”和“\\ev”命令可以用于查看函数或视图的定义，当然用户需要注意，退出V后， 要在psq|中输入“\\reset”来清除psq的命令缓冲区，防止误执行创建函数和视图的SQL 语句，示例如下：  
postgres=\#\\ev vm\_class 在这里进入Vi后，在Vi中用“：q“退出  
No changes  
postgres-\#\\reset 在这里不要忘了输入“\\reset”“命令清除psql缓冲区

#### 输出信息的\\echo 命令 ####

“\\echo”命令用于输出一行信息，示例如下：  
osdba=\#\\echo hello word  
hello word  
此命令通常用于在使用。sq|脚本的文件中输出提示信息。

#### 其他命令 ####

更多其他的命令可以用“\\？”命令来显示，示例如下：  
osdba=\#\\？

### psql的使用技巧 ###

#### 历史命令与补全功能 ####

可以使用上下方向键把以前使用过的命令或SQL语句调出来，连续单击两次Tab键表示把命令补全或给出输入提示。

#### 自动提交技巧 ####

需要特别注意的是，在psq|中事务是自动提交的，比如，执行完一条DELETE或 UPDATE语句后，事务就会自动提交，如果不想让事务自动提交，方法有两种。

方法一：运行“begin；”命令，然后执行DML语句，最后再执行commit或rollback语句，示例如下。  
osdba=\# begin；  
osdba=\# update x1 set name=‘xxxxx’ where id=1;  
UPDATE 1  
osdba=\# select \* from x1;  
osdba=\# rollback;  
ROLLBACK  
osdba=\# select \* from x1;  
方法二：直接使用psq|中的命令关闭自动提交功能。  
\\set AUTOCOMMIT off  
这个命令中的“AUTOCOMMIT”是大写的，不能使用小写，如果使用小写，虽不会报错，但会导致关闭自动提交的操作无效。

#### 如何得到psql中快捷命令执行的实际SQL ####

在启动psq的命令行中加上“-E”参数，就可以把psal中各种以“\\”开头的命令执行的实际SQL语句打印出来，示例如下：  
osdba@osdba-laptop：~$ psql -E postgres  
如果在已运行的psgl中显示了某个命令实际执行的SQL语句后又想关闭此功能，该怎么办？这时可以使用“\\set ECHO\_HIDDEN onloff”命令，示例如下：  
osdba@osdba-laptop：~$ psql postgres