PostgreSQL 入门教程

   当谈到关系型数据库管理系统（RDBMS），PostgreSQL（简称为Postgres）是一个备受赞誉的开源选择。它具有强大的功能和灵活性，被广泛用于各种规模的应用程序和企业级解决方案。本文将为您提供有关PostgreSQL的全面介绍，包括其历史背景、概念、特点、用法、安装步骤、简单的示例和总结。

1. 历史背景

  PostgreSQL起源于20世纪80年代，最初由加州大学伯克利分校的计算机科学家开发。它基于Ingres项目，并在许多领域进行了改进。PostgreSQL于1995年发布，成为第一个真正的开源关系型数据库。自那时以来，它在全球范围内发展迅速，成为非常受欢迎和广泛使用的数据库。

发展历程

• 1986年：PostgreSQL的前身是由加州大学伯克利分校（University of California, Berkeley）开发的一个称为Ingres的项目。但是，由于许可证问题，该项目无法继续开发。
• 1989年：由于Ingres项目的中止，Michael Stonebraker教授和他的研究团队开始开发一个名为Postgres的新项目，目标是构建一个可扩展的关系型数据库系统。
• 1994年：Postgres项目发布了第一个开源版本，成为了PostgreSQL的起点。PostgreSQL作为一种开源项目，受到了全球开发者社区的广泛关注和贡献。
• 1996年：PostgreSQL 6.0发布，这是第一个稳定版本，具备许多现代数据库系统所具备的功能，如事务支持、外键约束等。
• 2000年：PostgreSQL 7.0发布，引入了许多重要的改进，如支持SQL-92标准、触发器、存储过程等。
• 2005年：PostgreSQL 8.0发布，引入了许多改进，如可扩展性的提升、并发性能的改进、表空间管理等。
• 2010年：PostgreSQL 9.0发布，引入了诸多新特性，如可复制性、并行查询、外部表、JSON支持等。

  随着时间的推移，PostgreSQL持续发展和改进，成为了一种功能丰富、可靠稳定的开源关系型数据库系统。它在数据完整性、事务支持、并发性能、扩展性等方面具有很高的声誉，并被广泛应用于各种规模和类型的项目和企业中。

2. 概念

---

  PostgreSQL是一个对象关系型数据库管理系统（ORDBMS），它建立在关系数据库的基础上，并扩展为支持复杂数据类型、事务处理、并发控制和扩展性等功能。它采用SQL作为查询语言，并且支持存储过程、触发器、视图和索引等高级数据库概念。

3. 特点

---

1. 可靠性和稳定性：PostgreSQL被广泛认为是一种非常可靠和稳定的数据库系统。它采用了多版本并发控制（MVCC）技术，可以提供高度的数据完整性和并发性能。
2. 扩展性：PostgreSQL支持水平和垂直两种扩展方式。通过水平扩展，可以在多个服务器上分布数据。通过垂直扩展，可以增加单个服务器的处理能力。
3. 具备高级功能：PostgreSQL提供了许多高级功能，如复杂查询、窗口函数、递归查询、全文搜索、地理空间数据处理等。它还支持多种数据类型，如JSON、XML、数组、范围类型等。
4. 完整的事务支持：PostgreSQL支持ACID（原子性、一致性、隔离性、持久性）事务属性，可以确保数据的一致性和完整性。它还支持保存点和回滚操作，使得事务处理更加灵活。
5. 强大的安全性：PostgreSQL提供了多层次的安全性控制，包括用户和角色管理、访问权限控制、SSL/TLS加密连接等。它还支持加密存储和传输数据，以保护敏感信息的安全。
6. 可扩展的架构：PostgreSQL的架构设计灵活，可以轻松地集成其他工具和扩展。它支持自定义函数、存储过程和触发器，可以根据需要进行定制和扩展。
7. 大型社区支持：PostgreSQL拥有活跃的全球开发者社区，提供了广泛的文档、教程和支持资源。这使得用户可以获得快速响应和问题解决方案。
8. 跨平台支持：PostgreSQL可以运行在多种操作系统上，包括Windows、Linux、macOS等。这使得它适用于各种开发和部署环境。

4. 用法

4.1 数据库连接

---

  在使用PostgreSQL之前，首先需要安装并配置数据库服务器。然后，您可以使用支持多种编程语言的客户端驱动程序来连接到数据库，并执行操作。

psql -h 主机名/服务器IP -p 端口号 -U 用户名 -d 数据库名
# 使用 postgres 用户登录到 127.0.0.1 这个而主机上的端口为 5432 的 pgsql 数据库
psql -h 127.0.0.1 -p 5432 -U postgres
1
2
3

查询所有数据库

select datname from pg_database;
1

其他常用命令:

# 1. 退出登录
postgres=# \q

# 2. 获取帮助信息
postgres=# \h
postgres=# \h create table

# 3. 查看\相关命令
postgres=# \?

# 4. 数据库版本
postgres=# select version();
postgres=# show server_version;

# 5. 查看有哪些数据库
postgres=# \l
postgres=# \l+
postgres=# select oid,datname from pg_database;

# 6. 查看当前登录的数据库
postgres=# select current_database();

# 7. 切换数据库
postgres=# \c test

# 8. 切换用户
postgres=# \c - zhangsan

# 9. 查看当前用户
postgres=# select user;
postgres=# select current_user;

# 10. 查看当前数据库下所有表
postgres=# \d
postgres=# select tableowner,schemaname,tablename,tablespace from pg_tables where tableowner='postgres';

# 11. 查看登录信息
postgres=# \conninfo

# 12. 查看当前连接信息
postgres=# select * from pg_stat_activity;

# 13. 查看表结构
postgres=# \d demo;
postgres=# \d+ demo;
postgres=# select table_schema,table_name,column_name,data_type,character_maximum_length from information_schema.columns where table_name='demo';

# 14. 查看角色信息
postgres=# select rolname,rolsuper,rolcreatedb from pg_roles;

# 15. 查看用户角色
postgres=# \dg
postgres=# \du

# 16. 查看用户表权限
postgres=# select * from information_schema.table_privileges where grantee = 'cjc';
postgres=# \dp

# 16. 查看表空间
postgres=# \db

# 17. 格式化输出\pset
postgres=# select * from demo;
 id | age
----+-----
  1 |  50
  2 |  18
(2 行记录)
postgres=# \pset border 2;
边缘风格是 2.
postgres=# select * from demo;
+----+-----+
| id | age |
+----+-----+
|  1 |  50 |
|  2 |  18 |
+----+-----+
(2 行记录)
# 18. 取消边框
postgres=# \pset border 0;
边缘风格是 0.
postgres=# select * from demo;
id age
-- ---
 1  50
 2  18

# 19. 显示信息
postgres=# \echo hahaha
hahaha

# 20. 执行SQL脚本
cat t1.sql  
psql -h 127.0.0.1 -p 5432 -U postgres -W cjcdb -f t1.sql 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94

4.2 数据库创建

---

  使用CREATE DATABASE语句在服务器上创建新数据库。

CREATE DATABASE mydatabase;
1

4.3 表创建

  使用CREATE TABLE语句创建新表，并指定列名和数据类型。

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    age INTEGER
);
1
2
3
4
5

4.4 数据插入

  使用INSERT INTO语句将数据插入表中。

INSERT INTO users (name, age) VALUES ('John Doe', 30);
1

4.5 数据查询

  使用SELECT语句从表中检索数据。

SELECT * FROM users;
1

4.6 数据更新

  使用UPDATE语句更新表中的数据。

UPDATE users SET age = 40 WHERE id = 1;
1

4.7 数据删除

---

  使用DELETE语句从表中删除数据。

DELETE FROM users WHERE id = 1;
1

5. 安装步骤

---

这里介绍再 windows 环境下安装 PostgreSQL （版本：16.1）

1. 进入 PostgreSQL 官网，下载压缩包，官网地址：PostgreSQL 官网下载

   

2. 开始安装

   1. 解压 PostgreSQL 压缩包

      

   2. 在安装目录下创建一个 data 文件夹

      

   3. 初始化数据库，进入bin 目录下并执行初始化命令

      # 初始化数据库
      initdb.exe -D D:\Settings\PostgerSQL\data -E UTF-8 --locale=chs -U postgres -W
      
      # 参数详解
      -U postgres -W 注：
      -D ：指定数据库簇的存储目录 D:\Settings\PostgerSQL\data
      -E ：指定DB的超级用户的用户名postgres –locale：关于区域设置（chinese-simplified-china）
      -U ：默认编码格式chs
      -W ：为超级用户指定密码的提示
      1
      2
      3
      4
      5
      6
      7
      8
      9

   4. 输入新的用户口令，输出成功

      

   5. 启动 PostgreSQL 数据库

      pg_ctl -D D:\Settings\PostgreSQL\data -l logfile start
      1

      启动成功
      

3. 启动数据库

   1. 启动 PostgreSQL 数据库，进入安装目录下 D:\Settings\PostgreSQL\pgAdmin 4\runtime，选择 pgAdmin4.exe 文件运行

      

   2. 使用 PostgreSQL

      1. 创建分组 - 点击左上角的Object->Create -> Server Group…

         

      2. 创建数据库

         
         输入名称
         
         输出host、端口、数据源等
         

      3. 创建表-右键Tables -> create table

         
         至此，PostgreSQL windows 版就安装完成了

6. 简单示例

---

  以下是一个简单的示例，演示了如何创建和查询数据表：

-- 创建新数据库
CREATE DATABASE mydatabase;

-- 使用新数据库
\c mydatabase

-- 创建新表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    age INTEGER
);

-- 插入数据
INSERT INTO users (name, age) VALUES ('John Doe', 30);

-- 查询数据
SELECT * FROM users;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

7. 扩展

7.1 数据类型

---

  除了常见的整数、浮点数和字符串等基本数据类型外，PostgreSQL还提供了许多其他复杂的数据类型，如数组、日期/时间、JSON、UUID和几何类型等。这些数据类型使您能够更好地适应各种数据需求并提高数据存储的效率。

7.2 查询优化

---

  PostgreSQL具有强大的查询优化功能，自动选择最佳执行计划以提高查询性能。了解如何编写有效的查询语句，以及如何使用索引、分区表和查询优化器提示等技术，可以显著提高查询速度和响应时间。

7.3 并发控制

---

  对于高并发应用程序，PostgreSQL提供了多种并发控制机制，如事务隔离级别、悲观锁和乐观锁等。深入了解这些机制，可以确保数据的一致性和并发性，并避免不可预料的数据冲突。

7.4 数据备份和恢复

---

  数据备份是保护数据安全和灾难恢复的关键。PostgreSQL提供了多种备份和恢复策略，如物理备份、逻辑备份和增量备份等。了解如何创建和恢复备份，以及如何设置自动备份任务，可以帮助您保护数据库的完整性和可用性。

7.5 扩展性和高可用性

---

  对于需要处理大规模数据和高并发访问的应用程序，PostgreSQL提供了各种扩展性和高可用性解决方案。这包括主从复制、流复制、分布式架构和连接池等技术。学习如何配置和管理这些特性，可以确保您的应用程序具有良好的可扩展性和高可靠性。

7.6 安全性加固

除了基本的身份验证和访问控制外，PostgreSQL还提供了更高级的安全功能，如SSL/TLS加密、行级安全、列级权限和审计跟踪等。了解如何使用这些功能，可以增强数据库的安全性，并确保数据的保密性和完整性。

7.7 社区和生态系统

---

  PostgreSQL有一个活跃的社区，提供了丰富的文档、论坛和博客等资源。参与社区活动可以与其他开发者交流经验、解决问题，并获取最新的更新和发展。此外，还有许多第三方工具和插件可以扩展PostgreSQL的功能，使其更适应不同的用例和需求。

8. 总结

---

  PostgreSQL是一个功能强大且灵活的开源关系型数据库管理系统。它具有许多优点，如可靠性、可扩展性、安全性和可定制性。本文提供了有关PostgreSQL的历史背景、概念、特点、用法、安装步骤和简单示例的详细介绍。通过深入了解PostgreSQL，您可以更好地利用它的优势来构建高性能和可靠的应用程序。当您熟悉了PostgreSQL的基本概念和用法后，还有许多更深入的特性和功能可以探索。