PostgreSQL 基础配置与SQL操作（完整版）

---

课程目标

[ ] PostgreSQL 安装（源码/RPM/二进制）

[ ] psql 命令行工具使用技巧

[ ] SQL 基础：DCL角色管理

[ ] SQL 基础：DDL（创建数据库、表）

[ ] SQL 基础：DML（插入、更新、删除数据）

[ ] SQL 基础：DQL（单表查询、条件过滤、排序）

---

第一部分：基本概述

---

数据库排行榜：https://db-engines.com/en/ranking

一、发展历史

PostgreSQL 的发展历史可以追溯到上世纪80年代：

时间	事件
上世纪80年代	加州大学伯克利分校的 Michael Stonebraker 教授牵头搞了个 Postgres 项目，主要是为了解决当时数据库的一些痛点，比如支持复杂数据类型和数据关系
1994年	有两个研究生给它加了 SQL 解释器，改名叫 Postgres95
1996年	正式更名为 PostgreSQL

名称由来： Postgres SQL = PostgreSQL

Logo 由来： Postgres 最初在征求 Logo 时，有社区成员推荐大象（因为大象记忆力很强，对应数据库稳定、可靠），最终用大象的头像作为 Logo。

发展历程：

在之后的十几年里，PG 数据库一直发展得不温不火

但在 MySQL 被 Oracle 收购之后，引起了人们的重视

2015年之后，随着云计算、大数据的爆发，PG 因开源生态、高可扩展性的优势，受到越来越多的公司欢迎

由于其宽松的许可协议，任何人都可以免费使用、修改和分发 PostgreSQL，无论用于个人、商业还是学术用途

目前由于 AI 行业的兴起，部分 AI 开发使用 PostgreSQL 存储数据，主要是向量数据

二、版本选型

官网地址：https://www.postgresql.org

版本说明：

PostgreSQL 14 将于 2026 年 11 月 12 日停止接收修复

如果在生产环境中运行 PostgreSQL 14，建议计划升级到更新的、受支持的 PostgreSQL 版本

本次选择 PostgreSQL 18.4 进行学习

三、知识储备（与 MySQL 对比）

为衔接之前学的 MySQL，提前梳理一些核心差异，避免混淆：

对比项	MySQL	PostgreSQL
端口	默认 3306	默认 5432
超级用户	root	postgres
存储引擎	InnoDB、MyISAM 等	PostgreSQL（唯一核心引擎，无需手动切换）
数据库层次	库 → 表 → 行 → 列	表空间 → 库 → schema → 表 → 行 → 列
登录命令	`mysql` 命令	`psql` 命令

四、面试题

问题：PG 和 MySQL 在用起来有啥区别？说三个？

1. PG 事务更严格，适合金融/复杂业务；MySQL 更轻量，适合互联网快速开发 2. PG 完全支持 SQL 标准，复杂查询强；MySQL 语法灵活但标准支持弱 3. PG 功能多像企业级数据库；MySQL 简单高效，适合高并发简单业务 4. PG 开源的社区活跃；MySQL 是有大厂背书支持会更好一些

---

第二部分：PostgreSQL 安装

---

一、获取安装仓库

下载地址：https://www.postgresql.org/download

中文手册：http://www.postgres.cn/docs/current

1.1 查看系统信息

安装之前，先确认服务器的系统版本、内核版本及 CPU 架构：

# 查看系统版本
cat /etc/os-release

命令说明：

用途：执行 cat /etc/os-release。

重点看：命令输出中的状态、关键数值或报错信息。

NAME="CentOS Stream"
VERSION="9"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="9"
PLATFORM_ID="platform:el9"
PRETTY_NAME="CentOS Stream 9"
ANSI_COLOR="0;31"
LOGO="fedora-logo-icon"
CPE_NAME="cpe:/o:centos:centos:9"
HOME_URL="https://centos.org/"
BUG_REPORT_URL="https://issues.redhat.com/"
REDHAT_SUPPORT_PRODUCT="Red Hat Enterprise Linux 9"
REDHAT_SUPPORT_PRODUCT_VERSION="CentOS Stream"

# 查看内核版本和CPU架构
uname -a

Linux node1 5.14.0-697.el9.x86_64 #1 SMP PREEMPT_DYNAMIC Wed Apr 22 09:06:04 UTC 2026 x86_64 x86_64 x86_64 GNU/Linux

二、安装 PostgreSQL

本次在 CentOS 9 系统中安装 PostgreSQL。

2.1 检查网络连通性

# 注：安装之前，最好先确认一下服务器能否正常上网
ping -c1 www.baidu.com
# 如果ping不通，则不需要继续了，先解决上网问题

2.2 安装 PG 仓库

# 安装pgsql仓库
dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

命令作用：

使用 dnf 包管理器执行 install 操作，对软件包进行安装、更新、删除或查询。

执行结果：

成功后软件包会按指定动作完成安装、更新、删除或查询；失败时检查仓库、网络和权限。

关键参数：

注意事项：


**注意事项：**



---

注意事项：


**示例操作：**

pg_ctl:没有服务器进程正在运行

完成服务器进程已经启动

服务器进程已经关闭

文件名	含义
postgresql-Sun.log	星期日的日志
postgresql-Mon.log	星期一的日志
postgresql-Tue.log	星期二的日志
postgresql-Wed.log	星期三的日志
postgresql-Thu.log	星期四的日志
postgresql-Fri.log	星期五的日志
postgresql-Sat.log	星期六的日志

配置文件	说明
`/var/lib/pgsql/18/data/postgresql.conf`	主配置文件
`/var/lib/pgsql/18/data/pg_hba.conf`	访问控制（类比 MySQL 的 user 表权限控制）


**注意事项：**

注意事项：


再输入一遍：

第二步：远程登录


**注意事项：**

postgres=#


**注意事项：**



**连接步骤：**


**连接失败排查：**


---


---



\c

\conninfo

----------------------+----------------- 数据库 | postgres 选项 |

\q

\encoding

\l

                                                         数据库列表
   名称    |  拥有者  | 字元编码 | Locale Provider |  校对规则   |    Ctype    | Locale | ICU Rules |       存取权限
-----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------
           |          |          |                 |             |             |        |           | postgres=CTc/postgres
           |          |          |                 |             |             |        |           | postgres=CTc/postgres


**注意事项：**



\d

\d+

\dt

\di

\dv

\df


**注意事项：**



\du




| 分类 | 全称 | 说明 | 命令 |
|---|---|---|---|
| **DQL** | Data Query Language | 数据查询语句，主要用于数据查询 | SELECT |
| **DML** | Data Manipulation Language | 数据操纵语言，主要用于插入、更新、删除数据 | INSERT、UPDATE、DELETE |
| **DDL** | Data Definition Language | 数据定义语言，主要用于创建、删除、修改表或索引等数据库对象 | CREATE、DROP、ALTER |
| **DCL** | Data Control Language | 数据库控制语言，主要用于授予或撤销用户权限 | GRANT、REVOKE |


参考文档：http://www.postgres.cn/docs/current/datatype.html

| 分类名称 | 说明 |
|---|---|
| **数值类型** | 整数类型有2字节的 **smallint**、4字节的 **int**、8字节的 **bigint**，也可以用 **int2**、**int4**、**int8** 来表示；精确类型的小数有 **numeric** 和 **decimal**，`numeric(m,n)` 也可以用 `decimal(m,n)` 表示；非精确类型的浮点小数有单精度的 **real** 和双精度的 **double precision**；还有8字节的货币类型 (**money**)；还有2字节的自增整数 **smallserial**、4字节的自增整数 **serial**、8字节的自增整数 **bigserial** |
| **字符类型** | 有 **varchar(n)**、**char(n)**、**text** 3种类型，`varchar(n)` 也可以用 `char varying(n)` 来表示，`varchar(n)` 最大可以存储 **1GB** |
| **日期和时间类型** | 有 **date**、**time**、**timestamp**，而 **time** 和 **timestamp** 又根据是否包含时区分为两种类型，可以精确到秒以下，如毫秒 |



**语法：**

选项	说明

示例操作：

                       角色列表
 角色名称 |                    属性
----------+--------------------------------------------

第一步：修改本地认证方法