PostgreSQL学习总结_pgsql

PostgreSQL

一、概述

1、架构基础

PostgreSQL是一个功能强大的开源对象-关系数据库服务器(ORDBMS)。pgsql使用了客户端/服务器模式。一个pgsql会话包含包含两个进程：
服务器进程。管理数据库文件，接收客户端程序的连接请求，代表客户端执行数据库操作。数据库服务器程序叫做postgres。

客户端应用程序。进行数据库操作。客户端应用程序本质上可以非常多样化，一个客户端可以是一个面向文本的工具，一个图形界面应用程序，一个通过访问数据库来显示网页的web服务器，或者一个专门的数据库维护工具。有些是pgsql自带的，大部分是用户开发的。
客户端和服务器可以在不同的主机上，通过TCP/IP连接通信。记住这一点很重要，因为客户机上的文件再服务器上可能是访问不到的，或者只能换个文件名访问。
pgsql服务器可以处理多并发的连接，它会为每个连接fork一个新的进程。客户端和新的服务器进程的通信与就服务器进程没有关系。主服务器进程会一直保持运行，等待客户端连接。

2、为什么要使用 PostgreSQL

PostgreSQL 具有许多旨在帮助开发人员构建应用程序、管理员保护数据完整性和构建容错环境的功能，并帮助您管理数据，无论数据集有多大。除了免费和开源之外，PostgreSQL 还具有高度的可扩展性。例如，您可以定义自己的数据类型、构建自定义函数，甚至无需重新编译数据库即可使用不同的编程语言编写代码。

3、ORDBMS 术语

• 数据库: 数据库是一些关联表的集合。
• 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
• 列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。
• **行：**一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
• 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
• 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
• **外键：**外键用于关联两个表。
• 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
• **索引：**使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
• 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

4、PostgreSQL 特征

• 函数：通过函数，可以在数据库服务器端执行指令程序。

• 索引：用户可以自定义索引方法，或使用内置的 B 树，哈希表与 GiST 索引。

• 触发器：触发器是由SQL语句查询所触发的事件。如：一个INSERT语句可能触发一个检查数据完整性的触发器。触发器通常由INSERT或UPDATE语句触发。 多版本并发控制：PostgreSQL使用多版本并发控制（MVCC，Multiversion concurrency control）系统进行并发控制，该系统向每个用户提供了一个数据库的"快照"，用户在事务内所作的每个修改，对于其他的用户都不可见，直到该事务成功提交。

• 规则：规则（RULE）允许一个查询能被重写，通常用来实现对视图（VIEW）的操作，如插入（INSERT）、更新（UPDATE）、删除（DELETE）。

• 数据类型：包括文本、任意精度的数值数组、JSON 数据、枚举类型、XML 数据

  等。

• 全文检索：通过 Tsearch2 或 OpenFTS，8.3版本中内嵌 Tsearch2。

• NoSQL：JSON，JSONB，XML，HStore 原生支持，至 NoSQL 数据库的外部数据包装器。

• 数据仓库：能平滑迁移至同属 PostgreSQL 生态的 GreenPlum，DeepGreen，HAWK 等，使用 FDW 进行 ETL。

5、PostgreSQL 功能列表

• 数据类型
  • 基本类型：Integer 整数、Numeric 数字、String 字符串、Boolean 布尔值
  • 结构类型：Date/Time 日期/时间、Array 数组、,Range 范围/多范围、UUID
  • 文档类型：JSON/JSONB、XML、Key-value (Hstore)
  • 几何类型：Point 点、Line 线、Circle 圆、Polygon 多边形
  • 自定义类型：Composite 复合、Custom Types 自定义类型
• 数据的完整性
  • 唯一，非空
  • 主键
  • 外键
  • 排除约束
  • 显式锁、咨询锁
• 并发、性能
  • 索引：B 树、多列、表达式
  • 高级索引：GiST、SP-Gist、KNN Gist、GIN、BRIN、覆盖索引、布隆过滤器
  • 复杂的查询规划器/优化器、仅索引扫描、多列统计
  • 事务、嵌套事务（通过保存点）
  • 多版本并发控制 (MVCC)
  • 并行化读取查询和构建 B 树索引
  • 表分区
  • SQL 标准中定义的所有事务隔离级别，包括 Serializable
  • 表达式的即时 (JIT) 编译
• 可靠性、灾难恢复
  • 预写日志 (WAL)
  • 复制：异步、同步、逻辑
  • 时间点恢复 (PITR)，活动备用
  • 表空间
• 安全
  • 身份验证：GSSAPI、SSPI、LDAP、SCRAM-SHA-256、证书等
  • 强大的访问控制系统
  • 列级和行级安全
  • 使用证书和其他方法进行多因素身份验证
• 可扩展性
  • 存储函数和过程
  • 过程语言：PL/PGSQL、Perl、Python（等等）
  • SQL/JSON 路径表达式
  • 外部数据包装器：使用标准 SQL 接口连接到其他数据库或流
  • 可定制的表格存储界面
  • 许多提供附加功能的扩展，包括 PostGIS
• 国际化，文本搜索
  • 支持国际字符集，例如通过 ICU 排序规则
  • 不区分大小写和不区分重音的排序规则
  • 全文检索

二、PostgreSQL 语法

1、基础SQL 语句

一个 SQL 语句通常包含了关键字、标识符（字段）、常量、特殊符号等，下面是一个简单的 SQL 语句：

SELECT id, name FROM runoob
1

2、PostgreSQL 常用命令

三、PostgreSQL 数据类型

PostgreSQL提 供了丰富的数据类型。用户可以使用 CREATE TYPE 命令在数据库中创建新的数据类型。

包括：数值类型、货币类型、字符类型、日期/时间类型、布尔类型、枚举类型、几何类型、网络地址类型。以下列出几种：

1、数值类型

数值类型由 2 字节、4 字节或 8 字节的整数以及 4 字节或 8 字节的浮点数和可选精度的十进制数组成。

下表列出了可用的数值类型。

2、货币类型

money 类型存储带有固定小数精度的货币金额。

numeric、int 和 bigint 类型的值可以转换为 money，不建议使用浮点数来处理处理货币类型，因为存在舍入错误的可能性。

3、字符类型

四、PostgreSQL 运算符&关键字&字句

1、AND & OR 运算符

在 PostgreSQL 中，AND 和 OR 也叫连接运算符，在查询数据时用于缩小查询范围，我们可以用 AND 或者 OR 指定一个或多个查询条件。

AND

AND 运算符表示一个或者多个条件必须同时成立。

在 WHERE 子句中，AND 的使用语法如下：

SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];
1
2
3

OR

OR 运算符表示多个条件中只需满足其中任意一个即可。

在 WHERE 子句中，OR 的使用语法如下：

SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]
1
2
3

2、DISTINCT 关键字

在 PostgreSQL 中，DISTINCT 关键字与 SELECT 语句一起使用，用于去除重复记录，只获取唯一的记录。

我们平时在操作数据时，有可能出现一种情况，在一个表中有多个重复的记录，当提取这样的记录时，DISTINCT 关键字就显得特别有意义，它只获取唯一一次记录，而不是获取重复记录。

语法

用于去除重复记录的 DISTINCT 关键字的基本语法如下：

SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]
1
2
3

3、子句

（1）WHERE 子句

在 PostgreSQL 中，当我们需要根据指定条件从单张表或者多张表中查询数据时，就可以在 SELECT 语句中添加 WHERE 子句，从而过滤掉我们不需要数据。

WHERE 子句不仅可以用于 SELECT 语句中，同时也可以用于 UPDATE，DELETE 等等语句中。

语法

以下是 SELECT 语句中使用 WHERE 子句从数据库中读取数据的通用语法：

SELECT column1, column2, columnN
FROM table_name
WHERE [condition1]
1
2
3

我们可以在 WHERE 子句中使用比较运算符或逻辑运算符，例如 >, <, =, LIKE, NOT 等等。

（2） LIKE 子句

在 PostgreSQL 数据库中，我们如果要获取包含某些字符的数据，可以使用 LIKE 子句。

在 LIKE 子句中，通常与通配符结合使用，通配符表示任意字符，在 PostgreSQL 中，主要有以下两种通配符：

• 百分号 %
• 下划线 _

如果没有使用以上两种通配符，LIKE 子句和等号 = 得到的结果是一样的。

语法

以下是使用 LIKE 子句搭配百分号 % 和下划线 _ 从数据库中获取数据的通用语法：

SELECT FROM table_name WHERE column LIKE 'XXXX%';
或者
SELECT FROM table_name WHERE column LIKE '%XXXX%';
或者
SELECT FROM table_name WHERE column LIKE 'XXXX_';
或者
SELECT FROM table_name WHERE column LIKE '_XXXX';
或者
SELECT FROM table_name WHERE column LIKE '_XXXX_';
1
2
3
4
5
6
7
8
9

你可以在 WHERE 子句中指定任何条件。

你可以使用 AND 或者 OR 指定一个或多个条件。

XXXX 可以是任何数字或者字符。

实例

下面是 LIKE 语句中演示了 % 和 _ 的一些差别:

在 PostgreSQL 中，LIKE 子句是只能用于对字符进行比较，因此在上面例子中，我们要将整型数据类型转化为字符串数据类型。

（3）LIMIT 子句

PostgreSQL 中的 limit 子句用于限制 SELECT 语句中查询的数据的数量。

语法

带有 LIMIT 子句的 SELECT 语句的基本语法如下：

SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]
1
2
3

下面是 LIMIT 子句与 OFFSET 子句一起使用时的语法：

SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows] OFFSET [row num]
1
2
3

（4）ORDER BY 语句

在 PostgreSQL 中，ORDER BY 用于对一列或者多列数据进行升序（ASC）或者降序（DESC）排列。

语法

ORDER BY 子句的基础语法如下：

SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
1
2
3
4

您可以在 ORDER BY 中使用一列或者多列，但是必须保证要排序的列必须存在。

ASC 表示升序，DESC 表示降序。

（5）GROUP BY 语句

在 PostgreSQL 中，GROUP BY 语句和 SELECT 语句一起使用，用来对相同的数据进行分组。

GROUP BY 在一个 SELECT 语句中，放在 WHRER 子句的后面，ORDER BY 子句的前面。

语法

下面给出了 GROUP BY 子句的基本语法:

SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN
1
2
3
4
5

GROUP BY 子句必须放在 WHERE 子句中的条件之后，必须放在 ORDER BY 子句之前。

在 GROUP BY 子句中，你可以对一列或者多列进行分组，但是被分组的列必须存在于列清单中。

（6）WITH 子句

在 PostgreSQL 中，WITH 子句提供了一种编写辅助语句的方法，以便在更大的查询中使用。

WITH 子句有助于将复杂的大型查询分解为更简单的表单，便于阅读。这些语句通常称为通用表表达式（Common Table Express， CTE），也可以当做一个为查询而存在的临时表。

WITH 子句是在多次执行子查询时特别有用，允许我们在查询中通过它的名称(可能是多次)引用它。

WITH 子句在使用前必须先定义。

语法

WITH 查询的基础语法如下：

WITH
   name_for_summary_data AS (
      SELECT Statement)
   SELECT columns
   FROM name_for_summary_data
   WHERE conditions <=> (
      SELECT column
      FROM name_for_summary_data)
   [ORDER BY columns]
1
2
3
4
5
6
7
8
9

name_for_summary_data 是 WITH 子句的名称，name_for_summary_data 可以与现有的表名相同，并且具有优先级。

可以在 WITH 中使用数据 INSERT, UPDATE 或 DELETE 语句，允许您在同一个查询中执行多个不同的操作。

WITH 递归

在 WITH 子句中可以使用自身输出的数据。

公用表表达式 (CTE) 具有一个重要的优点，那就是能够引用其自身，从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。

（7）HAVING 子句

HAVING 子句可以让我们筛选分组后的各组数据。

WHERE 子句在所选列上设置条件，而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。

语法

下面是 HAVING 子句在 SELECT 查询中的位置：

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
1
2
3
4
5
6

HAVING 子句必须放置于 GROUP BY 子句后面，ORDER BY 子句前面，下面是 HAVING 子句在 SELECT 语句中基础语法：

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2
1
2
3
4
5
6

五、PostgreSQL 模式（SCHEMA）

PostgreSQL 模式（SCHEMA）可以看着是一个表的集合。

一个模式可以包含视图、索引、数据类型、函数和操作符等。

相同的对象名称可以被用于不同的模式中而不会出现冲突，例如 schema1 和 myschema 都可以包含名为 mytable 的表。

使用模式的优势：

• 允许多个用户使用一个数据库并且不会互相干扰。
• 将数据库对象组织成逻辑组以便更容易管理。
• 第三方应用的对象可以放在独立的模式中，这样它们就不会与其他对象的名称发生冲突。

模式类似于操作系统层的目录，但是模式不能嵌套。

语法

我们可以使用 CREATE SCHEMA 语句来创建模式，语法格式如下：

CREATE SCHEMA myschema.mytable (
...
);
1
2
3

六、PostgreSQL 约束

PostgreSQL 约束用于规定表中的数据规则。

如果存在违反约束的数据行为，行为会被约束终止。

约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

约束确保了数据库中数据的准确性和可靠性。

约束可以是列级或表级。列级约束仅适用于列，表级约束被应用到整个表。

以下是在 PostgreSQL 中常用的约束。

• NOT NULL：指示某列不能存储 NULL 值。
• UNIQUE：确保某列的值都是唯一的。
• PRIMARY Key：NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。。
• FOREIGN Key： 保证一个表中的数据匹配另一个表中的值的参照完整性。
• CHECK： 保证列中的值符合指定的条件。
• EXCLUSION ：排他约束，保证如果将任何两行的指定列或表达式使用指定操作符进行比较，至少其中一个操作符比较将会返回 false 或空值。

七、PostgreSQL 索引

索引是加速搜索引擎检索数据的一种特殊表查询。使用 CREATE INDEX 语句创建索引，它允许命名索引，指定表及要索引的一列或多列，并指示索引是升序排列还是降序排列。

索引也可以是唯一的，与 UNIQUE 约束类似，在列上或列组合上防止重复条目。

CREATE INDEX 命令

CREATE INDEX （创建索引）的语法如下：

CREATE INDEX index_name ON table_name;
1

索引类型

• 单列索引：单列索引是一个只基于表的一个列上创建的索引。
• 组合索引：组合索引是基于表的多列上创建的索引。
• 唯一索引: 使用唯一索引不仅是为了性能，同时也为了数据的完整性。唯一索引不允许任何重复的值插入到表中。
• **局部索引: ** 局部索引 是在表的子集上构建的索引；子集由一个条件表达式上定义。索引只包含满足条件的行。
• 隐式索引: 隐式索引 是在创建对象时，由数据库服务器自动创建的索引。索引自动创建为主键约束和唯一约束。

DROP INDEX （删除索引）

一个索引可以使用 PostgreSQL 的 DROP 命令删除。

DROP INDEX index_name;
1

什么情况下要避免使用索引？

虽然索引的目的在于提高数据库的性能，但这里有几个情况需要避免使用索引。

使用索引时，需要考虑下列准则：

• 索引不应该使用在较小的表上。
• 索引不应该使用在有频繁的大批量的更新或插入操作的表上。
• 索引不应该使用在含有大量的 NULL 值的列上。
• 索引不应该使用在频繁操作的列上。

八、PostgreSQL View（视图）

View（视图）是一张假表，只不过是通过相关的名称存储在数据库中的一个 PostgreSQL 语句。

View（视图）实际上是一个以预定义的 PostgreSQL 查询形式存在的表的组合。

View（视图）可以包含一个表的所有行或从一个或多个表选定行。

View（视图）可以从一个或多个表创建，这取决于要创建视图的 PostgreSQL 查询。

View（视图）是一种虚拟表，允许用户实现以下几点：

• 用户或用户组认为更自然或直观查找结构数据的方式。
• 限制数据访问，用户只能看到有限的数据，而不是完整的表。
• 汇总各种表中的数据，用于生成报告。

PostgreSQL 视图是只读的，因此可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句。但是可以在视图上创建一个触发器，当尝试 DELETE、INSERT 或 UPDATE 视图时触发，需要做的动作在触发器内容中定义。

CREATE VIEW（创建视图）

在 PostgreSQL 用 CREATE VIEW 语句创建视图，视图创建可以从一张表，多张表或者其他视图。

CREATE VIEW 基础语法如下：

CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];
1
2
3
4

DROP VIEW （删除视图）

要删除视图，只需使用带有 view_name 的 DROP VIEW 语句。DROP VIEW 的基本语法如下：

runoobdb=# DROP VIEW view_name;
1

九、PostgreSQL TRANSACTION（事务）

TRANSACTION（事务）是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。

数据库事务通常包含了一个序列的对数据库的读/写操作。包含有以下两个目的：

• 为数据库操作序列提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。
• 当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。

当事务被提交给了数据库管理系统（DBMS），则 DBMS 需要确保该事务中的所有操作都成功完成且其结果被永久保存在数据库中，如果事务中有的操作没有成功完成，则事务中的所有操作都需要回滚，回到事务执行前的状态；同时，该事务对数据库或者其他事务的执行无影响，所有的事务都好像在独立的运行。

1、事务的属性

事务具有以下四个标准属性，通常根据首字母缩写为 ACID：

• 原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
• 一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
• 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
• 持久性（Durability）：已被提交的事务对数据库的修改应该永久保存在数据库中。

例子

某人要在商店使用电子货币购买100元的东西，当中至少包括两个操作：

• 该人账户减少 100 元。
• 商店账户增加100元。

支持事务的数据库管理系统就是要确保以上两个操作（整个"事务"）都能完成，或一起取消，否则就会出现 100 元平白消失或出现的情况。

2、事务控制

使用下面的命令来控制事务：

**BEGIN TRANSACTION：**开始一个事务。COMMIT：事务确认，或者可以使用 END TRANSACTION 命令。ROLLBACK：事务回滚。

事务控制命令只与 INSERT、UPDATE 和 DELETE 一起使用。他们不能在创建表或删除表时使用，因为这些操作在数据库中是自动提交的。

BEGIN TRANSACTION 命令

事务可以使用 BEGIN TRANSACTION 命令或简单的 BEGIN 命令来启动。此类事务通常会持续执行下去，直到遇到下一个 COMMIT 或 ROLLBACK 命令。不过在数据库关闭或发生错误时，事务处理也会回滚。以下是启动一个事务的简单语法：

BEGIN;

或者

BEGIN TRANSACTION;
1
2
3
4
5

COMMIT 命令

COMMIT 命令是用于把事务调用的更改保存到数据库中的事务命令，即确认事务。

COMMIT 命令的语法如下：

COMMIT;

或者

END TRANSACTION;
1
2
3
4
5

ROLLBACK 命令

ROLLBACK 命令是用于撤消尚未保存到数据库的事务命令，即回滚事务。

ROLLBACK 命令的语法如下：

ROLLBACK;
1

十、PostgreSQL LOCK（锁）

锁主要是为了保持数据库数据的一致性，可以阻止用户修改一行或整个表，一般用在并发较高的数据库中。

在多个用户访问数据库的时候若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。

数据库中有两种基本的锁：排它锁（Exclusive Locks）和共享锁（Share Locks）。

如果数据对象加上排它锁，则其他的事务不能对它读取和修改。

如果加上共享锁，则该数据库对象可以被其他事务读取，但不能修改。

LOCK 命令语法

LOCK 命令基础语法如下：

LOCK [ TABLE ]
name
 IN
lock_mode
1
2
3
4

• name：要锁定的现有表的名称（可选模式限定）。如果只在表名之前指定，则只锁定该表。如果未指定，则锁定该表及其所有子表（如果有）。
• lock_mode：锁定模式指定该锁与哪个锁冲突。如果没有指定锁定模式，则使用限制最大的访问独占模式。可能的值是：ACCESS SHARE，ROW SHARE， ROW EXCLUSIVE， SHARE UPDATE EXCLUSIVE， SHARE，SHARE ROW EXCLUSIVE，EXCLUSIVE，ACCESS EXCLUSIVE。

一旦获得了锁，锁将在当前事务的其余时间保持。没有解锁表命令；锁总是在事务结束时释放。

死锁

当两个事务彼此等待对方完成其操作时，可能会发生死锁。尽管 PostgreSQL 可以检测它们并以回滚结束它们，但死锁仍然很不方便。为了防止应用程序遇到这个问题，请确保将应用程序设计为以相同的顺序锁定对象。

咨询锁

PostgreSQL 提供了创建具有应用程序定义含义的锁的方法。这些被称为咨询锁。由于系统不强制使用它们，所以正确使用它们取决于应用程序。咨询锁对于不适合 MVCC 模型的锁定策略非常有用。

例如，咨询锁的一个常见用途是模拟所谓"平面文件"数据管理系统中典型的悲观锁定策略。虽然存储在表中的标志可以用于相同的目的，但是通知锁更快，避免了表膨胀，并且在会话结束时由服务器自动清理。

–官网地址