PostgreSQL命令行操作详解
超级会员免费看
PostgreSQL 命令行操作全解析
在数据库操作中，除了使用图形用户界面（GUI），命令行操作也是一项非常重要的技能。本文将详细介绍如何在不同操作系统上设置命令行以使用 PostgreSQL 的 psql 工具，以及如何使用 psql 进行数据库连接、查询、结果格式化等操作。
1. 触发器与函数在数据维护中的应用
在数据库操作中，触发器和函数能极大简化数据维护工作。例如，向 temperature_test 表插入四行数据时， temperature_insert 触发器会为每行数据触发，使得插入的 max_temp 自动在 max_temp_group 列获得相应分类。虽然温度示例和之前的成绩变更审计示例较为基础，但它们展示了触发器和函数在简化数据维护方面的实用性。
为了巩固相关概念，你可以尝试以下练习：
- 创建一个视图，显示纽约市每小时的出租车出行次数。使用相关出租车数据和特定查询。
- 将之前学习的每千分率计算公式转换为 rates_per_thousand() 函数，该函数接受三个参数来计算结果。
- 为 meat_poultry_egg_inspect 表编写一个触发器，每次插入新的食品加工设施时，自动添加一个检查日期，日期设置为当前日期的六个月后。
2. 命令行操作的优势与 psql 工具介绍
在计算机拥有图形用户界面（GUI）之前，命令行是向计算机发出指令的主要方式。命令行（也称为命令行界面、控制台、shell 或终端）是一个基于文本的界面，你可以通过输入程序名称或其他命令来执行任务。使用命令行操作 PostgreSQL 具有以下优势：
- 输入简短命令通常比点击多层菜单项工作速度更快。
- 可以访问一些只有命令行才能提供的功能。
- 当只能通过命令行访问（如连接到远程计算机）时，仍能完成工作。
我们将使用 psql 这个 PostgreSQL 的命令行工具，它允许你运行查询、管理数据库对象，并通过文本命令与计算机操作系统进行交互。
3. 不同操作系统下 psql 的设置
3.1 Windows 系统
- 添加
psql到 PATH 环境变量 ：- 打开 Windows 控制面板，在任务栏搜索框输入“Control Panel”，点击控制面板图标。
- 在控制面板应用中，在右上角搜索框输入“Environment”，点击“Edit the System Environment Variables”，会弹出系统属性对话框。
- 在系统属性对话框的“Advanced”选项卡中，点击“Environment Variables”。在“User variables”部分，如果没有
PATH变量，点击“New”，在“Variable name”框中输入PATH，在“Variable value”框中输入C:\Program Files\PostgreSQL\x.y\bin（x.y是你使用的 PostgreSQL 版本），然后点击“OK”关闭所有对话框；如果已有PATH变量，选中它并点击“Edit”，在变量列表中点击“New”，输入C:\Program Files\PostgreSQL\x.y\bin，完成后点击“OK”关闭所有对话框。
- 启动和配置 Windows 命令提示符 ：
- 启动：选择“Start ▸ Windows System ▸ Command Prompt”。
- 配置：可以将命令提示符固定到任务栏以便快速访问。点击命令提示符窗口栏左侧的图标，选择“Properties”，可自定义字体、颜色等设置。为了更适合查询输出显示，建议将窗口大小（在“Layout”选项卡）设置为宽度 80 和高度 25。
- 常用 Windows 命令 ：
| 命令 | 功能 | 示例 | 操作 |
| ---- | ---- | ---- | ---- |
|cd| 更改目录 |cd C:\my-stuff| 切换到 C 盘的my-stuff目录 |
|copy| 复制文件 |copy C:\my-stuff\song.mp3 C:\Music\song_favorite.mp3| 将my-stuff目录下的song.mp3文件复制到Music目录下，命名为song_favorite.mp3|
|del| 删除文件 |del *.jpg| 删除当前目录下所有扩展名为.jpg的文件 |
|dir| 列出目录内容 |dir /p| 逐屏显示目录内容 |
|findstr| 在文本文件中查找匹配正则表达式的字符串 |findstr "peach" *.txt| 在当前目录下所有扩展名为.txt的文件中搜索包含“peach”的行 |
|mkdir| 创建新目录 |mkdir C:\my-stuff\Salad| 在my-stuff目录下创建一个名为Salad的新目录 |
|move| 移动文件 |move C:\my-stuff\song.mp3 C:\Music\| 将my-stuff目录下的song.mp3文件移动到Music目录 |
3.2 macOS 系统
- 添加
psql到 PATH 环境变量 ：- 显示隐藏文件：
- 导航到“Applications ▸ Utilities ▸ Terminal”启动终端。
- 在命令提示符下，输入
defaults write com.apple.finder AppleShowAllFiles YES并按回车键。 - 退出终端（
⌘-Q），按住“OPTION”键，右键点击 Mac 坞站上的 Finder 图标，选择“Relaunch”。
- 编辑或创建
.bash_profile文件：- 使用 macOS Finder 导航到用户目录，依次点击“Macintosh HD”和“Users”。
- 打开用户目录（通常有一个房子图标），此时应能看到隐藏的文件和目录。
- 检查是否存在
.bash_profile文件。如果存在，右键点击并用喜欢的文本编辑器打开；如果不存在，使用 TextEdit 创建并保存该文件到用户目录。
- 添加 PATH 语句：在
.bash_profile文件中添加export PATH="/Applications/Postgres.app/Contents/Versions/latest/bin:$PATH"，保存并关闭文件。如果终端已打开，关闭并重新启动它。
- 显示隐藏文件：
- 启动和配置 macOS 终端 ：
- 启动：导航到“Applications ▸ Utilities ▸ Terminal”。
- 配置：可以将终端添加到 macOS 坞站以便快速访问。点击“Terminal ▸ Preferences”，可更改字体、颜色等设置。为了更好地显示查询输出，建议将窗口大小（在“Window”选项卡）设置为宽度 80 列和高度 25 行。
- 常用 macOS 终端命令 ：
| 命令 | 功能 | 示例 | 操作 |
| ---- | ---- | ---- | ---- |
|cd| 更改目录 |cd /Users/pparker/my-stuff/| 切换到my-stuff目录 |
|cp| 复制文件 |cp song.mp3 song_backup.mp3| 将当前目录下的song.mp3文件复制为song_backup.mp3|
|grep| 在文本文件中查找匹配正则表达式的字符串 |grep 'us_counties_2010' *.sql| 在所有扩展名为.sql的文件中查找包含“us_counties_2010”的行 |
|ls| 列出目录内容 |ls -al| 以长格式列出所有文件和目录（包括隐藏文件） |
|mkdir| 创建新目录 |mkdir resumes| 在当前工作目录下创建一个名为resumes的目录 |
|mv| 移动文件 |mv song.mp3 /Users/pparker/songs| 将当前目录下的song.mp3文件移动到用户目录下的songs目录 |
|rm| 删除文件 |rm *.jpg| 删除当前目录下所有扩展名为.jpg的文件 |
3.3 Linux 系统
不同 Linux 发行版安装 PostgreSQL 的方法不同，但 psql 是标准 PostgreSQL 安装的一部分，通常会自动添加到 PATH 中。你可以启动终端应用程序（如在 Ubuntu 中，按 CTRL-ALT-T 打开终端），并且 macOS 终端的常用命令在 Linux 系统中同样适用。
4. 使用 psql 进行数据库操作
4.1 启动 psql 并连接到数据库
无论使用哪种操作系统，启动 psql 的方式相同。打开命令行界面（Windows 上的 Command Prompt，macOS 或 Linux 上的 Terminal），在命令提示符下使用以下模式启动 psql ：
psql -d database_name -U user_name
例如，连接本地机器的 analysis 数据库，输入：
psql -d analysis -U postgres
如果要连接到远程服务器上的数据库，可以指定 -h 参数后跟主机名，如：
psql -d analysis -U postgres -h example.com
如果在安装时设置了密码，启动 psql 时会收到密码提示，输入密码并按回车键。成功连接后，会看到类似以下的提示：
psql (10.1)
Type "help" for help.
analysis=#
其中，第一行列出了 psql 和服务器的版本号， analysis=# 是命令输入提示， # 表示以超级用户权限登录。
4.2 获取帮助
在 psql 提示符下，可以使用以下命令获取帮助：
| 命令 | 显示内容 |
| ---- | ---- |
| \? | psql 中可用的命令，如 \dt 列出表 |
| \? options | psql 命令的选项，如 -U 指定用户名 |
| \? variables | psql 可用的变量，如 VERSION 表示当前 psql 版本 |
| \h | SQL 命令列表，添加命令名可查看详细帮助（如 \h INSERT ） |
4.3 更改用户和数据库连接
可以使用以反斜杠开头的元命令向 psql 发出指令。例如，使用 \c 元命令切换到不同的数据库或用户账户。切换到 gis_analysis 数据库：
analysis=# \c gis_analysis
如果要以不同用户登录，可在数据库名后添加用户名，如：
analysis-# \c gis_analysis anthony
4.4 运行 SQL 查询
- 单行查询 ：在
psql提示符下直接输入查询，例如：
analysis=# SELECT geo_name FROM us_counties_2010 LIMIT 3;
按回车键执行查询， psql 会显示查询结果及返回的行数。
- 多行查询 ：可以按回车键换行输入查询，注意直到提供以分号结尾的行时， psql 才会执行查询。例如：
analysis=# SELECT geo_name
analysis-# FROM us_counties_2010
analysis-# LIMIT 3;
当查询超过一行时，数据库名和哈希标记之间的符号会从等号（ = ）变为连字符（ - ）。
4.5 检查未闭合的括号
psql 会提示未闭合的括号。例如创建 wineries 表时：
analysis=# CREATE TABLE wineries (
analysis(# id bigint,
analysis(# winery_name varchar(100)
analysis(# );
CREATE TABLE
输入 CREATE TABLE 语句的第一行和左括号后，提示符会从 analysis=# 变为 analysis(# ，提醒有未闭合的左括号，直到添加右括号。
4.6 编辑查询
如果要修改正在处理的查询，可以使用 \e 或 \edit 元命令。在 Windows 上， psql 默认使用 Notepad 打开最后执行的查询；在 macOS 和 Linux 上，使用 vim 。使用 vim 进行简单编辑的步骤如下：
1. 当 vim 打开查询编辑窗口时，按 I 激活插入模式。
2. 对查询进行编辑。
3. 按 ESC 然后 SHIFT+: 显示冒号命令提示符，输入 wq 并按回车键保存更改。
4. 退出到 psql 提示符后，它将执行修订后的查询，按上箭头键可查看修订后的文本。
4.7 导航和格式化结果
- 设置结果分页 ：对于查询结果较多的情况，可以使用
\pset pager元命令调整分页设置。例如，移除查询中的LIMIT子句后执行查询：
analysis=# SELECT geo_name FROM us_counties_2010;
结果可能会滚动显示，按 Q 可退出滚动结果并返回 psql 提示符。使用 \pset pager 关闭分页后，再次运行查询会直接显示到结果末尾。再次运行 \pset pager 可重新打开分页。
- 格式化结果网格 ：可以使用 \pset 元命令的以下选项格式化结果：
- border int ：指定结果网格的边框，0 表示无边界，1 表示列之间有内部线，2 表示所有单元格周围有线条。例如， \pset border 2 设置所有单元格周围有线条。
- format unaligned ：以分隔符分隔的行显示结果，类似于 CSV 文件。默认分隔符是竖线（ | ），可以使用 fieldsep 命令设置不同的分隔符，如 \pset fieldsep ',' 。使用 \pset format aligned 可恢复列视图，也可以使用 \a 元命令在对齐和未对齐视图之间切换。
- footer ：切换结果页脚（显示结果行数）的开或关。
- null ：设置空值的显示方式，默认显示为空白，可使用 \pset null 'NULL' 将空白替换为大写的 NULL 。
- 查看扩展结果 ：使用 \x 元命令可以将结果以垂直块列表形式显示，适合数据较大或需要逐行查看列值的情况。例如，查询 grades 表：
analysis=# SELECT * FROM grades;
正常显示结果为表格形式，输入 \x 切换到扩展视图后，再次运行查询结果将以垂直块形式显示。再次输入 \x 可恢复列显示，设置 \x auto 可根据输出大小自动选择显示方式。
4.8 数据库信息元命令
可以使用以 \d 开头并附加加号（ + ）的元命令显示数据库中表和其他对象、函数的详细信息，还可以提供可选的模式来过滤输出。例如，输入 \dt+ 列出数据库中所有表及其大小，使用 \dt+ us* 只显示名称以 us 开头的表。
| 命令 | 显示内容 |
| ---- | ---- |
| \d [pattern] | 对象的列、数据类型等信息 |
| \di [pattern] | 索引及其关联的表 |
| \dt [pattern] | 表及其所有者账户 |
| \du [pattern] | 用户账户及其属性 |
| \dv [pattern] | 视图及其所有者账户 |
| \dx [pattern] | 已安装的扩展 |
4.9 导入、导出和使用文件
- 使用
\copy进行导入和导出 ：\copy元命令的工作方式与 SQL COPY 命令类似，但它可以将数据从本地机器路由到远程服务器。例如，删除并重新创建state_regions表并导入数据：
analysis=# DROP TABLE state_regions;
DROP TABLE
analysis=# CREATE TABLE state_regions (
analysis(# st varchar(2) CONSTRAINT st_key PRIMARY KEY,
analysis(# region varchar(20) NOT NULL
analysis(# );
CREATE TABLE
analysis=# \copy state_regions FROM 'C:\YourDirectory\state_regions.csv' WITH (FORMAT CSV, HEADER);
COPY 56
如果连接到远程服务器，使用相同的 \copy 语法即可将本地文件路由到远程服务器进行导入。也可以使用 \copy TO 进行导出。
- 将查询输出保存到文件 ：使用 \o 元命令可以将查询结果和消息保存到文件。例如，将查询输出设置为未对齐、逗号分隔且无页脚的格式，然后将结果保存到文件：
analysis=# \a \f , \pset footer
Output format is unaligned.
Field separator is ",".
Default footer is off.
analysis=# SELECT * FROM grades;
student_id,course_id,course,grade
1,2,English 11B,D
1,3,World History 11B,C
1,4,Trig 2,B
1,1,Biology 2,C
analysis=# \o 'C:/YourDirectory/query_output.csv'
analysis=# SELECT * FROM grades;
analysis=#
此后每次运行查询，输出都会追加到指定的文件中。要停止保存输出到该文件，可以指定新文件或输入 \o 无文件名以恢复将结果输出到屏幕。
通过以上步骤，你可以在不同操作系统上设置并使用 psql 进行数据库操作，包括连接数据库、运行查询、格式化结果、获取数据库信息以及导入导出数据等。掌握这些技能将大大提高你在数据库管理和操作方面的效率。
PostgreSQL 命令行操作全解析
5. 常见问题与解决方案
在使用 psql 进行数据库操作的过程中，可能会遇到一些常见问题，以下为你提供相应的解决方案。
5.1 密码问题
- 问题描述 ：在启动
psql时，如果忘记密码或者密码输入错误，将无法成功连接到数据库。 - 解决方案 ：如果你忘记了密码，可以通过 PostgreSQL 的相关工具重置密码。在 Linux 或 macOS 系统中，可以使用
psql以超级用户身份登录，然后使用以下命令修改密码：
ALTER USER postgres PASSWORD 'new_password';
在 Windows 系统中，也可以通过类似的方式在 psql 中执行上述命令来修改密码。
5.2 连接问题
- 问题描述 ：当尝试连接到远程服务器时，可能会遇到连接失败的情况，这可能是由于网络问题、服务器配置问题或权限问题导致的。
- 解决方案 ：
- 检查网络连接是否正常，可以尝试使用
ping命令测试与服务器的网络连通性。 - 检查服务器的 PostgreSQL 配置文件
pg_hba.conf和postgresql.conf，确保允许远程连接，并且配置了正确的认证方式。 - 检查用户权限，确保使用的用户账户具有连接到指定数据库的权限。
- 检查网络连接是否正常，可以尝试使用
5.3 文件路径问题
- 问题描述 ：在使用
\copy或\o命令时，如果文件路径指定错误，会导致数据导入导出或保存查询输出失败。 - 解决方案 ：
- 在 Windows 系统中，文件路径需要使用双反斜杠（
\\）或正斜杠（/），例如C:/YourDirectory/query_output.csv或C:\\YourDirectory\\query_output.csv。 - 在 Linux 或 macOS 系统中，使用正斜杠作为路径分隔符，确保路径正确指向目标文件或目录。
- 在 Windows 系统中，文件路径需要使用双反斜杠（
6. 高级应用场景
除了基本的数据库操作， psql 还可以应用于一些高级场景，帮助你更高效地管理和分析数据。
6.1 自动化脚本
可以编写脚本批量执行 psql 命令，实现自动化的数据处理和管理任务。例如，创建一个 shell 脚本，在每天凌晨自动备份数据库：
#!/bin/bash
# 定义数据库连接信息
DB_NAME="analysis"
DB_USER="postgres"
DB_PASSWORD="your_password"
BACKUP_DIR="/path/to/backup"
# 创建备份目录（如果不存在）
mkdir -p $BACKUP_DIR
# 生成备份文件名
BACKUP_FILE="$BACKUP_DIR/backup_$(date +%Y%m%d).sql"
# 执行备份命令
PGPASSWORD=$DB_PASSWORD psql -d $DB_NAME -U $DB_USER -c "COPY (SELECT * FROM your_table) TO '$BACKUP_FILE' WITH CSV HEADER;"
# 检查备份是否成功
if [ $? -eq 0 ]; then
echo "Backup completed successfully."
else
echo "Backup failed."
fi
将上述脚本保存为一个 .sh 文件，例如 backup_script.sh ，然后赋予执行权限：
chmod +x backup_script.sh
最后，可以使用 cron 任务调度器在每天凌晨执行该脚本：
0 0 * * * /path/to/backup_script.sh
6.2 数据迁移
在不同的 PostgreSQL 数据库之间进行数据迁移时， psql 可以发挥重要作用。可以使用 \copy 命令将数据从一个数据库导出，然后导入到另一个数据库。以下是一个简单的数据迁移示例：
- 导出数据 ：在源数据库中执行以下命令将数据导出到本地文件：
\copy your_table TO '/path/to/exported_data.csv' WITH CSV HEADER;
- 导入数据 ：在目标数据库中执行以下命令将数据从本地文件导入：
\copy your_table FROM '/path/to/exported_data.csv' WITH CSV HEADER;
7. 总结与展望
通过本文的介绍，你已经了解了如何在不同操作系统上设置 psql 工具，以及如何使用它进行数据库连接、查询、结果格式化、获取数据库信息和导入导出数据等操作。掌握 psql 的使用技巧，将使你在数据库管理和操作方面更加得心应手。
随着数据库技术的不断发展， psql 也在不断更新和完善，未来可能会提供更多强大的功能和工具。例如，可能会进一步优化命令行操作的交互性，提供更丰富的元命令和选项，以满足不同用户的需求。同时，结合其他数据库管理工具和技术， psql 可以更好地应用于大数据分析、数据挖掘等领域，为数据处理和决策提供更有力的支持。
希望你在实际应用中不断探索和实践，充分发挥 psql 的优势，提高数据库操作的效率和质量。如果你在使用过程中遇到任何问题或有新的发现，欢迎在评论区分享交流。
8. 参考资源
为了帮助你进一步深入学习和掌握 psql 的使用，以下为你提供一些参考资源。
8.1 官方文档
PostgreSQL 的官方文档是学习和使用 psql 的权威资料，其中包含了详细的命令说明、示例和使用指南。你可以访问 PostgreSQL 官方文档 获取最新的信息。
8.2 在线教程和博客
互联网上有许多关于 PostgreSQL 和 psql 的在线教程和博客文章，这些资源可以提供不同的视角和实践经验。例如， Stack Overflow 上有大量关于 psql 的问题和解答，可以帮助你解决遇到的实际问题。
8.3 书籍
有一些优秀的数据库相关书籍也会涉及到 psql 的使用，例如《PostgreSQL: Up and Running》《Learning PostgreSQL》等，这些书籍可以帮助你系统地学习 PostgreSQL 数据库和 psql 工具。
通过利用这些参考资源，你可以不断拓展自己的知识和技能，更好地应用 psql 进行数据库操作和管理。
扫一扫
1999
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
