PostgreSQL 基础入门教程 – wiki基地

作者： /

PostgreSQL 基础入门教程：从零开始掌握关系型数据库利器

欢迎来到 PostgreSQL 的世界！如果你正在寻找一个强大、可靠、功能丰富且完全开源的关系型数据库系统，那么你来对地方了。PostgreSQL，通常被亲切地称为 Postgres，是全球最受欢迎的开源数据库之一，以其卓越的稳定性、先进的功能集和对 SQL 标准的高度遵循而闻名。

本教程旨在为完全没有 PostgreSQL 经验，或者只有少量数据库基础的读者提供一个全面的入门指南。我们将从数据库的基本概念讲起，逐步深入到安装、连接、创建数据库和表，以及最核心的 SQL 语言基础（数据的增、删、改、查）。通过本教程的学习，你将获得使用 PostgreSQL 进行基本数据管理的能力。

第一章：初识 PostgreSQL – 为什么选择它？

在开始技术细节之前，让我们先了解一下 PostgreSQL 是什么，以及为什么它如此受到开发者和企业的青睐。

1.1 什么是 PostgreSQL？

PostgreSQL 是一个对象-关系型数据库管理系统 (ORDBMS)。这意味着它不仅支持传统的关系型数据库特性（如表、行、列、主键、外键等），还融入了面向对象数据库的一些概念（如自定义类型、继承等），尽管在实际使用中，我们更多地将其视为一个强大的关系型数据库。

它是自由和开源软件，在宽松的 PostgreSQL 许可证下发布。这意味着你可以免费使用、修改和分发它，这为个人学习、小型项目到大型企业应用提供了极大的灵活性和成本优势。

1.2 PostgreSQL 的主要特点和优势

  • 稳定性与可靠性： Postgres 在数据完整性和可靠性方面有着卓越的声誉。它支持事务处理（ACID 属性：原子性、一致性、隔离性、持久性），确保数据在任何情况下都不会丢失或损坏。
  • 功能丰富： 除了标准的 SQL 功能外，Postgres 还提供了大量高级特性，如复杂查询、存储过程、触发器、视图、外键引用、事务隔离级别、窗口函数、公用表表达式 (CTE) 等等。
  • ** extensibility（可扩展性）：** 这是 Postgres 的一大亮点。你可以定义新的数据类型、函数、操作符、聚合函数，甚至可以通过外部数据包装器 (Foreign Data Wrappers, FDW) 连接到其他数据库或数据源。
  • 对 SQL 标准的高度遵循： PostgreSQL 严格遵循 SQL 标准，这意味着你在 Postgres 中学习的 SQL 知识在很大程度上可以直接应用于其他符合标准的数据库系统，反之亦然。
  • 强大的社区支持： 作为一个成熟的开源项目，Postgres 拥有一个庞大而活跃的全球社区。遇到问题时，可以轻松找到文档、论坛、邮件列表等资源寻求帮助。
  • 多种数据类型支持： 除了常见的文本、数字、日期类型外，Postgres 还原生支持数组、JSON/JSONB、几何数据类型、网络地址类型等等，这使得它能够很好地处理各种复杂的数据结构。
  • 并发控制： 使用多版本并发控制 (MVCC) 机制，允许多个用户同时读写数据而不会相互阻塞，提高了系统的并发性能。

1.3 谁在使用 PostgreSQL？

从初创公司到大型企业，从网站后端到科学计算，PostgreSQL 的应用范围极其广泛。许多知名的技术公司和服务都在后端使用 PostgreSQL，例如 Instagram, Reddit, Spotify 等等。这证明了其在处理大规模数据和高并发访问方面的能力。

总而言之，学习 PostgreSQL 将为你打开通往强大数据库管理和应用开发的大门。

第二章：安装与连接 PostgreSQL

要开始使用 PostgreSQL，首先需要将其安装到你的计算机上。安装过程因操作系统的不同而略有差异。

2.1 安装 PostgreSQL

2.1.1 Windows

最简单的方式是从 PostgreSQL 官方网站 (https://www.postgresql.org/download/windows/) 下载交互式安装程序。安装程序会引导你完成安装过程，包括选择安装组件（通常建议包含 pgAdmin，这是一个非常方便的图形化管理工具）、设置数据目录、设置管理员用户（默认是 postgres）的密码以及设置监听端口（默认是 5432）。请务必记住管理员密码，后续连接数据库需要使用。

2.1.2 macOS

  • 官方下载： 也可以从官方网站下载 macOS 安装程序 (https://www.postgresql.org/download/macosx/)。
  • Homebrew： 许多 macOS 用户喜欢使用 Homebrew 包管理器进行安装。打开终端，运行 brew install postgresql。Homebrew 会自动处理依赖关系并安装最新版本的 PostgreSQL。安装完成后，它会提示你如何启动和停止 PostgreSQL 服务。

2.1.3 Linux

大多数 Linux 发行版都将 PostgreSQL 包含在其官方软件仓库中。使用你发行版对应的包管理器进行安装：

  • Debian/Ubuntu: sudo apt update && sudo apt install postgresql postgresql-contrib (postgresql-contrib 包含一些有用的附加模块)。
  • Fedora/CentOS/RHEL: sudo dnf install postgresql-server postgresql-contrib 或 sudo yum install postgresql-server postgresql-contrib。

安装完成后，通常需要初始化数据库并启动服务：

  • Debian/Ubuntu: sudo systemctl enable postgresql && sudo systemctl start postgresql (或者在较旧的版本上使用 sudo service postgresql start)。
  • Fedora/CentOS/RHEL: sudo postgresql-setup --initdb 然后 sudo systemctl enable postgresql && sudo systemctl start postgresql (或者在较旧的版本上使用 sudo service postgresql start)。

请注意，在 Linux 上安装后，默认会创建一个名为 postgres 的系统用户，它是 PostgreSQL 的管理员用户。你需要切换到这个用户才能使用命令行工具管理数据库，或者为其设置一个密码以允许远程连接。

重要提示: 详细的安装步骤请务必参考 PostgreSQL 官方文档，它会提供针对不同操作系统和版本的最准确指导。

2.2 连接 PostgreSQL

安装完成后，你可以通过多种方式连接到 PostgreSQL 服务器：

2.2.1 使用命令行工具：psql

psql 是 PostgreSQL 官方提供的交互式命令行客户端。它功能强大，适合执行 SQL 命令、管理数据库以及进行一些系统级别的操作。

  • 连接本地默认数据库（通常是同名的用户数据库）：
    • 如果你当前系统用户和数据库用户同名，直接输入 psql。
    • 如果以 postgres 用户连接，输入 psql -U postgres，然后根据提示输入密码。
  • 连接到指定的数据库和用户： psql -U your_username -d your_database_name -h your_host -p your_port
    • -U: 指定用户
    • -d: 指定数据库
    • -h: 指定主机名或 IP 地址 (本地连接通常省略或使用 localhost)
    • -p: 指定端口号 (默认 5432，通常省略)

成功连接后，你会看到类似于 psql (版本号) Type "help" for help. 的提示，并且命令行提示符会变为 your_database_name=# 或 your_database_name=>。

psql 的常用命令 (以反斜杠 \ 开头):

  • \l: 列出所有数据库。
  • \dt: 列出当前数据库中的所有表。
  • \d table_name: 描述表的结构 (列、类型、约束等)。
  • \dn: 列出所有 schema。
  • \q: 退出 psql。
  • \?: 获取 psql 命令帮助。

2.2.2 使用图形化工具：pgAdmin

pgAdmin 是一个流行的 PostgreSQL GUI 管理工具。它提供了一个友好的界面来管理数据库、创建和修改表、执行查询、监控服务器状态等。如果你在安装 PostgreSQL 时包含了 pgAdmin，可以直接启动它。

启动 pgAdmin 后，你需要添加一个新的服务器连接。点击 “Add New Server”，填写连接信息：

  • General (通用):
    • Name (名称): 给这个连接起一个名字，方便识别 (例如: 本地Postgres)。
  • Connection (连接):
    • Host name/address (主机名/地址): localhost (如果在本机) 或服务器的 IP/域名。
    • Port (端口): 5432 (默认)。
    • Maintenance database (维护数据库): postgres (默认)。
    • Username (用户名): postgres (或其他你有权限的用户)。
    • Password (密码): 输入该用户的密码。

点击 “Save”，如果连接信息正确，你就可以在左侧的浏览器面板中看到并管理你的 PostgreSQL 服务器了。你可以通过右键点击数据库、表等对象来执行各种操作，或者打开 Query Tool (查询工具) 来执行 SQL 命令。

第三章：数据库基本概念与 SQL 入门

在使用 PostgreSQL 之前，理解一些核心的数据库概念至关重要。同时，SQL (Structured Query Language) 是与数据库交互的标准语言，我们将学习其最基础的语法。

3.1 数据库基本概念

  • 数据库 (Database): 是相关数据的集合，它包含了组织、管理和访问这些数据所需的结构（如表、索引等）。一个 PostgreSQL 服务器可以管理多个数据库。
  • 表 (Table): 是数据库中最基本的数据存储单元。它由行和列组成，类似于电子表格。
  • 行 (Row / Record / Tuple): 表中的一条记录，代表一个独立的数据项。
  • 列 (Column / Field / Attribute): 表中的一个字段，代表数据的一种特定属性。每一列都有一个名称和定义的数据类型。
  • 数据类型 (Data Type): 定义了列中可以存储的数据种类，例如整数、文本、日期、布尔值等。正确选择数据类型对于数据存储效率和数据完整性至关重要。
  • 主键 (Primary Key, PK): 一列或一组列，其值能唯一标识表中的每一行。主键值不能重复，也不能为 NULL。它用于确保数据的唯一性，并作为与其他表建立关系的基础。
  • 外键 (Foreign Key, FK): 表中的一列或一组列，它引用了另一个表（或同一个表）中的主键。外键用于建立表之间的关系，并维护参照完整性（Referential Integrity），即确保引用的数据在被引用表中确实存在。
  • Schema (模式): 是数据库对象（如表、视图、索引、函数等）的集合的命名空间。它帮助组织和隔离数据库对象，避免命名冲突。默认情况下，每个数据库都有一个名为 public 的 schema。

3.2 SQL 简介

SQL 是用于管理关系型数据库的标准语言。它主要分为几个子集：

  • DDL (Data Definition Language): 数据定义语言，用于定义数据库结构，如 CREATE, ALTER, DROP。
  • DML (Data Manipulation Language): 数据操作语言，用于管理数据库中的数据，如 SELECT, INSERT, UPDATE, DELETE。
  • DCL (Data Control Language): 数据控制语言，用于管理用户权限，如 GRANT, REVOKE。
  • TCL (Transaction Control Language): 事务控制语言，用于管理事务，如 BEGIN, COMMIT, ROLLBACK。

在本入门教程中，我们将重点关注 DDL 和 DML 的基础部分。

第四章：使用 SQL 创建数据库和表 (DDL)

现在我们已经理解了基本概念，是时候动手创建我们自己的数据库和表了。

4.1 创建数据库 (CREATE DATABASE)

假设我们要创建一个用于存储图书信息的数据库。

sql
-- 创建一个名为 library 的数据库
CREATE DATABASE library;

在 psql 或 pgAdmin 的 Query Tool 中执行此命令。成功后，你就拥有了一个新的数据库。接下来，你需要连接到这个新的数据库才能在其中创建表。

在 psql 中，可以使用 \c library 命令切换到 library 数据库。
在 pgAdmin 中，可以在左侧的浏览器面板中展开 “Databases”，找到 library 并双击连接。然后打开 Query Tool，确保它连接的是 library 数据库。

4.2 创建表 (CREATE TABLE)

现在在 library 数据库中，我们创建一个名为 books 的表来存储图书信息。

“`sql
— 连接到 library 数据库 (如果在 psql 中，使用 \c library)
— 如果在 pgAdmin Query Tool 中，确保已连接到 library

— 创建 books 表
CREATE TABLE books (
— 定义列
book_id SERIAL PRIMARY KEY, — 图书ID, SERIAL 类型会自动生成唯一的整数序列, 并设为主键
title VARCHAR(255) NOT NULL, — 书名, VARCHAR(255) 表示最大长度255的字符串, NOT NULL 表示不能为空
author VARCHAR(255) NOT NULL, — 作者名, VARCHAR(255), NOT NULL
publication_year INTEGER, — 出版年份, INTEGER 表示整数
isbn VARCHAR(13) UNIQUE, — ISBN号, VARCHAR(13), UNIQUE 表示值必须唯一
genre VARCHAR(100), — 类型, VARCHAR(100)
stock_count INTEGER DEFAULT 0 — 库存数量, INTEGER, DEFAULT 0 表示如果插入时未指定值则默认为0
);

— 成功执行后，你会看到 CREATE TABLE 的提示
“`

解释上述 SQL 语法：

  • CREATE TABLE books: 创建一个名为 books 的新表。
  • 括号 () 内定义了表的列。
  • 每行定义一列，格式为 列名 数据类型 [约束]。
  • SERIAL: PostgreSQL 特有的伪类型，它会自动创建一个序列（Sequence），并在每次插入新行时从序列获取下一个值，通常用于创建自增的主键 ID。它实际上是 INTEGER NOT NULL 加上一个序列，并且列的默认值被设置为序列的下一个值。
  • PRIMARY KEY: 将 book_id 列设为主键。主键隐含了 NOT NULL 和 UNIQUE 约束。
  • VARCHAR(n): 用于存储可变长度的字符串，最大长度为 n。
  • NOT NULL: 约束，指定该列的值不能为空。
  • INTEGER: 用于存储整数。
  • UNIQUE: 约束，指定该列的值在整个表中必须是唯一的。
  • DEFAULT value: 为列指定默认值。如果在 INSERT 语句中没有为该列提供值，将使用此默认值。

我们再创建一个 authors 表，并与 books 表建立关系。

“`sql
— 创建 authors 表
CREATE TABLE authors (
author_id SERIAL PRIMARY KEY, — 作者ID, 主键
author_name VARCHAR(255) NOT NULL, — 作者姓名
country VARCHAR(100) — 作者国籍
);

— 修改 books 表，添加外键关联 authors 表
ALTER TABLE books
ADD COLUMN author_id INTEGER, — 添加一个 author_id 列
ADD CONSTRAINT fk_author — 定义一个外键约束
FOREIGN KEY (author_id) — 指定当前表的外键列
REFERENCES authors (author_id) — 指定引用哪个表及哪个列 (被引用表的主键)
ON DELETE SET NULL; — 当被引用的作者被删除时，将 books 表中对应的 author_id 设为 NULL
— 其他 ON DELETE 选项包括: CASCADE (级联删除), RESTRICT (阻止删除), NO ACTION (同 RESTRICT, 默认)
“`

这个例子展示了：
* 如何创建另一个表。
* 如何使用 ALTER TABLE 语句修改现有表，这里是添加一个新列。
* 如何使用 ADD CONSTRAINT 定义一个外键约束 (FOREIGN KEY)，使其引用 authors 表的 author_id 列。

3.3 查看表结构 (\d)

在 psql 中，可以使用 \d table_name 查看表的详细结构：

bash
\d books

这会显示表的列、类型、约束、索引等信息。在 pgAdmin 中，可以在左侧浏览器面板中选中表，然后在右侧的面板中查看其属性、列、约束等信息。

第五章：使用 SQL 操作数据 (DML)

DDL 帮我们搭建了数据库结构，现在是时候使用 DML 来填充和管理数据了。

5.1 插入数据 (INSERT INTO)

向表中添加新行。

“`sql
— 向 authors 表插入数据
INSERT INTO authors (author_name, country) VALUES
(‘J.K. Rowling’, ‘UK’),
(‘George Orwell’, ‘UK’),
(‘Jane Austen’, ‘UK’);

— 向 books 表插入数据 (引用 authors 表的 author_id)
— 需要先知道作者的 author_id
— 假设 J.K. Rowling 的 author_id 是 1, George Orwell 是 2, Jane Austen 是 3 (SERIAL 会自动生成)

INSERT INTO books (title, author_id, publication_year, isbn, genre, stock_count) VALUES
(‘Harry Potter and the Sorcerer”s Stone’, 1, 1997, ‘978-0590353403’, ‘Fantasy’, 50),
(‘1984’, 2, 1949, ‘978-0451524935’, ‘Dystopian’, 25),
(‘Pride and Prejudice’, 3, 1813, ‘978-0141439518’, ‘Romance’, 30),
(‘Animal Farm’, 2, 1945, ‘978-0451526342’, ‘Satire’, 20),
(‘Harry Potter and the Chamber of Secrets’, 1, 1998, ‘978-0439064866’, ‘Fantasy’, 45);

— 插入一条不指定 stock_count 的图书记录，会使用默认值 0
INSERT INTO books (title, author_id, publication_year, isbn, genre) VALUES
(‘Sense and Sensibility’, 3, 1811, ‘978-0141439519’, ‘Romance’);

— 注意：插入字符串时使用单引号 ‘
— 如果字符串中包含单引号，需要使用两个单引号 ” 进行转义
“`

语法解释：

  • INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
  • 你也可以省略列名列表，但前提是 VALUES 中的值必须按照表中列的定义顺序提供，并且数量要和列的数量一致。这通常不推荐，因为它使得 SQL 语句的可读性变差，并且在表结构改变时容易出错。
  • 可以使用单个 INSERT INTO 语句插入多行，通过在 VALUES 后面使用逗号分隔多个值组。

5.2 查询数据 (SELECT)

SELECT 语句是 SQL 中最常用也是最强大的语句，用于从数据库中检索数据。

5.2.1 基本查询

“`sql
— 查询 books 表中所有列和所有行
SELECT * FROM books;

— 查询 books 表中指定的列
SELECT title, author, publication_year FROM books;

— 查询 authors 表中的所有作者名
SELECT author_name FROM authors;
“`

语法解释：

  • SELECT column1, column2, ... FROM table_name;
  • * 表示选择所有列。

5.2.2 使用 WHERE 子句过滤数据

WHERE 子句用于指定查询条件，只返回符合条件的行。

“`sql
— 查询出版年份在2000年之后的图书
SELECT title, publication_year FROM books WHERE publication_year > 2000;

— 查询作者是 ‘George Orwell’ 的图书
SELECT title FROM books WHERE author_id = 2; — 这里使用 author_id 进行过滤，更精确

— 查询库存数量小于 30 的图书
SELECT title, stock_count FROM books WHERE stock_count < 30;

— 查询类型为 ‘Fantasy’ 且库存大于 40 的图书
SELECT title, genre, stock_count FROM books WHERE genre = ‘Fantasy’ AND stock_count > 40;

— 查询作者是 ‘Jane Austen’ 或 ‘George Orwell’ 的图书 (使用 OR)
SELECT title FROM books WHERE author_id = 3 OR author_id = 2;

— 使用 IN 子句查询作者是 ‘Jane Austen’ 或 ‘George Orwell’ 的图书 (更简洁)
SELECT title FROM books WHERE author_id IN (3, 2);

— 查询书名包含 ‘Harry Potter’ 的图书 (使用 LIKE 和通配符 %)
SELECT title FROM books WHERE title LIKE ‘%Harry Potter%’; — % 匹配任意长度的字符

— 查询 ISBN 号以 ‘978’ 开头的图书
SELECT title, isbn FROM books WHERE isbn LIKE ‘978%’;

— 查询 ISBN 号已知 (非 NULL) 的图书
SELECT title, isbn FROM books WHERE isbn IS NOT NULL;

— 查询 ISBN 号未知 (为 NULL) 的图书
SELECT title, isbn FROM books WHERE isbn IS NULL;

— 查询出版年份在 1945 到 1950 之间的图书 (使用 BETWEEN)
SELECT title, publication_year FROM books WHERE publication_year BETWEEN 1945 AND 1950;
“`

常用 WHERE 条件操作符：

  • 比较运算符: =, != (或 <>), >, <, >=, <=
  • 逻辑运算符: AND, OR, NOT
  • LIKE, ILIKE (大小写不敏感的 LIKE), NOT LIKE
  • IN, NOT IN
  • BETWEEN value1 AND value2
  • IS NULL, IS NOT NULL

5.2.3 使用 ORDER BY 子句排序结果

ORDER BY 子句用于对查询结果进行排序。

“`sql
— 按出版年份升序排列图书
SELECT title, publication_year FROM books ORDER BY publication_year ASC; — ASC 表示升序 (默认)

— 按库存数量降序排列图书
SELECT title, stock_count FROM books ORDER BY stock_count DESC; — DESC 表示降序

— 先按作者ID升序，再按出版年份降序排列图书
SELECT title, author_id, publication_year FROM books ORDER BY author_id ASC, publication_year DESC;
“`

语法解释：

  • ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...

5.2.4 使用 LIMIT 和 OFFSET 子句限制结果数量

LIMIT 限制返回的最大行数，OFFSET 跳过指定数量的行（常用于分页）。

“`sql
— 只返回库存数量最多的前 3 本书
SELECT title, stock_count FROM books ORDER BY stock_count DESC LIMIT 3;

— 跳过前 3 行，然后返回接下来的 2 行 (例如用于获取第 4 和第 5 条记录)
SELECT title FROM books ORDER BY title LIMIT 2 OFFSET 3;
“`

语法解释：

  • LIMIT count
  • OFFSET count

5.2.5 使用聚合函数

聚合函数用于对一组行的值进行计算，返回单个值。

“`sql
— 计算 books 表的总行数
SELECT COUNT(*) FROM books;

— 计算库存数量大于 20 的图书数量
SELECT COUNT(*) FROM books WHERE stock_count > 20;

— 计算所有图书的库存总和
SELECT SUM(stock_count) FROM books;

— 计算图书的平均出版年份
SELECT AVG(publication_year) FROM books;

— 找到库存数量的最小值和最大值
SELECT MIN(stock_count), MAX(stock_count) FROM books;
“`

常用聚合函数：

  • COUNT(): 计算行数。
  • SUM(column): 计算指定列的总和（适用于数值类型）。
  • AVG(column): 计算指定列的平均值（适用于数值类型）。
  • MIN(column): 找到指定列的最小值。
  • MAX(column): 找到指定列的最大值。

5.2.6 使用 GROUP BY 子句

GROUP BY 子句用于将具有相同值的行分组，然后对每个组应用聚合函数。

“`sql
— 按作者ID分组，计算每个作者的图书数量
SELECT author_id, COUNT(*) AS book_count FROM books GROUP BY author_id;

— 按出版年份分组，计算每年的出版图书数量
SELECT publication_year, COUNT(*) AS book_count FROM books GROUP BY publication_year ORDER BY publication_year;

— 按类型分组，计算每种类型的平均库存
SELECT genre, AVG(stock_count) AS average_stock FROM books GROUP BY genre;
“`

语法解释：

  • GROUP BY column1, column2, ...
  • 在使用 GROUP BY 时，SELECT 列表通常只能包含 GROUP BY 列和聚合函数的表达式。

5.2.7 使用 HAVING 子句

HAVING 子句用于过滤 GROUP BY 分组后的结果。它类似于 WHERE，但应用于组而不是单个行。

“`sql
— 按作者ID分组，计算每个作者的图书数量，但只显示图书数量大于 1 的作者
SELECT author_id, COUNT() AS book_count
FROM books
GROUP BY author_id
HAVING COUNT() > 1;

— 按类型分组，计算每种类型的平均库存，但只显示平均库存大于 30 的类型
SELECT genre, AVG(stock_count) AS average_stock
FROM books
GROUP BY genre
HAVING AVG(stock_count) > 30;
“`

语法解释：

  • HAVING condition，这里的 condition 通常包含聚合函数。

重要区别： WHERE 在分组之前过滤行，HAVING 在分组之后过滤组。

5.2.8 表连接 (JOIN)

表连接是 SQL 中用于合并来自两个或多个表的数据的关键技术，通常基于它们之间的相关列（如主键和外键）。

INNER JOIN (内连接): 返回两个表中都存在匹配行的结果。

sql
-- 连接 books 表和 authors 表，根据 author_id 匹配
-- 查询图书信息及其对应的作者姓名
SELECT
b.title,
b.publication_year,
a.author_name,
a.country
FROM
books b -- 给 books 表起别名 b
INNER JOIN
authors a ON b.author_id = a.author_id; -- 给 authors 表起别名 a, 指定连接条件

LEFT JOIN (左连接 / LEFT OUTER JOIN): 返回左表（FROM 后面的表）中的所有行，以及右表（JOIN 后面的表）中匹配的行。如果左表中的行在右表中没有匹配，则右表中的列会显示为 NULL。

sql
-- 查询所有作者，以及他们各自的图书（如果作者没有图书，图书信息显示为 NULL）
SELECT
a.author_name,
b.title
FROM
authors a
LEFT JOIN
books b ON a.author_id = b.author_id;

RIGHT JOIN (右连接 / RIGHT OUTER JOIN): 返回右表中的所有行，以及左表中匹配的行。如果右表中的行在左表中没有匹配，则左表中的列会显示为 NULL。

sql
-- 查询所有图书，以及它们各自的作者姓名（如果图书没有作者ID或作者ID无效，作者信息显示为 NULL）
-- 在实际应用中，LEFT JOIN 更常用，RIGHT JOIN 的效果通常可以通过交换 FROM 和 JOIN 后面的表并使用 LEFT JOIN 来实现
SELECT
b.title,
a.author_name
FROM
books b
RIGHT JOIN -- 这里的 RIGHT JOIN 效果与上面 LEFT JOIN 交换表位置类似
authors a ON b.author_id = a.author_id;

FULL OUTER JOIN (全外连接): 返回左表和右表中所有的行。如果某行在另一个表中没有匹配，则另一个表中的列显示为 NULL。

sql
-- 返回所有作者和所有图书，无论它们是否匹配
SELECT
a.author_name,
b.title
FROM
authors a
FULL OUTER JOIN
books b ON a.author_id = b.author_id;

在入门阶段，掌握 INNER JOIN 和 LEFT JOIN 就已经非常实用了。

5.3 更新数据 (UPDATE)

UPDATE 语句用于修改表中现有的数据。

“`sql
— 更新某本书的库存数量
UPDATE books
SET stock_count = 55
WHERE title = ‘Harry Potter and the Sorcerer”s Stone’; — !!! 务必带 WHERE 子句，否则会更新所有行

— 更新 George Orwell 的国籍
UPDATE authors
SET country = ‘United Kingdom’ — 将 UK 更新为 United Kingdom
WHERE author_name = ‘George Orwell’;

— 更新出版年份在 2000 年之前的图书，将 genre 设为 ‘Classic’ (如果 genre 为空)
UPDATE books
SET genre = ‘Classic’
WHERE publication_year < 2000 AND genre IS NULL;
“`

语法解释：

  • UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
  • 警告： 如果省略 WHERE 子句，UPDATE 将会修改表中的所有行！

5.4 删除数据 (DELETE)

DELETE 语句用于删除表中的行。

“`sql
— 删除某个作者的图书 (假设 author_id 是 2，George Orwell)
DELETE FROM books
WHERE author_id = 2; — 删除 author_id 为 2 的所有图书

— 删除所有库存为 0 的图书
DELETE FROM books
WHERE stock_count = 0;

— 删除 Jane Austen (假设 author_id 是 3)
DELETE FROM authors
WHERE author_id = 3; — 根据我们之前定义的 ON DELETE SET NULL 约束，这将把 books 表中引用 author_id 3 的行的 author_id 设为 NULL
“`

语法解释：

  • DELETE FROM table_name WHERE condition;
  • 警告： 如果省略 WHERE 子句，DELETE 将会删除表中的所有行！

清空表 (TRUNCATE TABLE):

如果你想删除表中的所有数据，但保留表的结构，可以使用 TRUNCATE TABLE。它比不带 WHERE 子句的 DELETE 更快，因为它通常不记录每行删除的操作（取决于事务设置），并且会重置序列（如 SERIAL 列）。

sql
-- 清空 books 表 (删除所有行，并重置 SERIAL 序列)
TRUNCATE TABLE books;

警告： TRUNCATE TABLE 操作通常不可回滚（如果你没有在事务中执行），请谨慎使用。

第六章：事务简介

事务是一组作为单个逻辑工作单元执行的 SQL 语句。事务要么全部成功提交 (COMMIT)，要么全部失败回滚 (ROLLBACK)。这保证了数据库的原子性 (Atomicity) 和一致性 (Consistency)。PostgreSQL 默认是自动提交模式，即每条 SQL 语句都作为一个独立的事务执行。但在执行多个相关的操作时，通常需要显式地开启事务。

“`sql
— 开启一个事务
BEGIN; — 或 START TRANSACTION;

— 执行一系列操作
UPDATE accounts SET balance = balance – 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
— 如果在转账过程中发生错误 (例如 user_id 不存在)

— 如果所有操作都成功，提交事务
COMMIT;

— 如果发生错误，回滚事务，撤销所有操作
— ROLLBACK;
“`

在执行一系列相互依赖的修改操作时，使用事务可以确保数据的完整性。

第七章：进一步学习方向和资源

恭喜你！你已经掌握了 PostgreSQL 的基础知识，包括安装、连接以及最核心的 DDL 和 DML 操作。这为你进一步深入学习打下了坚实的基础。

接下来你可以学习的方向：

  • 更高级的 SQL 特性： 子查询 (Subqueries)、公用表表达式 (CTE)、窗口函数 (Window Functions)、视图 (Views)、索引 (Indexes) 的类型和使用。
  • 数据类型： 深入了解 PostgreSQL 丰富的原生数据类型，特别是 JSONB 的使用。
  • 函数与过程： 学习如何编写存储函数和存储过程 (PL/pgSQL)。
  • 触发器 (Triggers): 学习如何在特定事件发生时自动执行一些操作。
  • 用户与权限管理： 如何创建用户、角色，并分配不同的权限。
  • 备份与恢复： 掌握使用 pg_dump 和 pg_restore 等工具进行数据备份和恢复。
  • 性能调优： 学习如何分析查询计划 (EXPLAIN)，创建合适的索引，优化数据库配置。
  • 复制与高可用性： 了解 PostgreSQL 的复制机制，构建高可用的数据库集群。

推荐资源：

  • PostgreSQL 官方文档： 这是最权威、最完整的资源。虽然刚开始可能觉得有点深，但遇到具体问题时，查阅官方文档是最佳实践。(https://www.postgresql.org/docs/)
  • pgAdmin 文档： 学习如何高效地使用 pgAdmin 管理你的数据库。
  • SQL 教程网站： W3Schools, Codecademy 等网站提供了很多互动式的 SQL 基础教程。
  • 在线课程： Coursera, Udemy, B站等平台有很多关于 SQL 和 PostgreSQL 的课程。
  • 技术博客和社区论坛： 搜索 PostgreSQL 相关的技术博客，参与 Stack Overflow 或其他技术社区的讨论。

总结

本教程带你从零开始了 PostgreSQL 的学习之旅。我们了解了 PostgreSQL 的优势和基本概念，学习了如何在不同操作系统上安装和连接，并详细讲解了使用 SQL 进行数据库和表的创建 (CREATE)，以及数据的增 (INSERT)、删 (DELETE)、改 (UPDATE)、查 (SELECT) 操作。特别是 SELECT 语句的各种子句（WHERE, ORDER BY, LIMIT, GROUP BY, HAVING, JOIN）是数据库查询的核心，务必多加练习。最后，我们简要介绍了事务的概念，并指明了进一步学习的方向和资源。

数据库是软件开发中不可或缺的基础。掌握 PostgreSQL 这样强大的数据库系统，将极大地提升你的数据处理能力和应用开发能力。理论学习只是第一步，大量的实践是掌握数据库技能的关键。创建你自己的数据库，设计表结构，插入各种数据，然后尝试编写各种复杂的查询来解决实际问题。

祝你在 PostgreSQL 的学习道路上一切顺利！不断探索，不断实践，你将能够驾驭这个强大的数据库利器。

发表评论

您的邮箱地址不会被公开。 必填项已用 * 标注

滚动至顶部