1. PostgreSQL 简介与基础

PostgreSQL 简介与基础：代码实践与详解

PostgreSQL，也被称为 Postgres，是一个强大、开源的对象关系型数据库管理系统 (ORDBMS)。它以其可靠性、数据完整性、丰富的功能集和可扩展性而闻名。PostgreSQL 符合 ACID 标准，并支持 SQL 标准的许多高级特性，使其成为各种规模应用程序的理想选择。

1. PostgreSQL 简介

PostgreSQL 起源于 1986 年的加州大学伯克利分校的 Ingres 项目。它经过多年的发展，已经成为一个功能齐全、高度可定制的数据库系统。

PostgreSQL 的主要特点：

• 开源与免费： PostgreSQL 采用 PostgreSQL 许可证，允许用户自由使用、修改和分发。

• 兼容 ACID： 保证事务的原子性、一致性、隔离性和持久性，确保数据的可靠性。

• SQL 标准支持： 支持 SQL 标准的许多特性，包括复杂查询、子查询、视图、触发器和存储过程。

• 高级数据类型： 除了标准数据类型外，还支持数组、JSON、XML、几何数据类型和用户自定义类型。

• 可扩展性： 支持通过扩展来增强功能，例如 PostGIS (地理空间数据支持) 和 TimescaleDB (时间序列数据支持)。

• 并发控制： 使用多版本并发控制 (MVCC) 来实现高效的并发访问，避免读写冲突。

• 可靠性与稳定性： 以其稳定性和可靠性而闻名，适用于关键业务应用。

• 跨平台支持： 支持各种操作系统，包括 Linux、Windows、macOS 等。

2. PostgreSQL 基础

2.1 安装与配置

PostgreSQL 可以从官方网站 (www.postgresql.org) 下载并安装。安装过程因操作系统而异，通常包含以下步骤：

1. 下载适用于您操作系统的安装包。

2. 运行安装程序，并按照提示进行操作。

3. 设置 PostgreSQL 的管理员密码 (postgres 用户)。

4. 配置 PostgreSQL 的监听地址和端口 (默认为 5432)。

5. 启动 PostgreSQL 服务。

2.2 连接到 PostgreSQL

安装完成后，可以使用 psql 命令行工具或图形化工具 (如 pgAdmin) 连接到 PostgreSQL 服务器。

使用 psql 连接：

psql -U postgres -d postgres -h localhost
​

• -U postgres: 指定用户名为 postgres (管理员用户)。

• -d postgres: 指定数据库为 postgres (默认数据库)。

• -h localhost: 指定服务器地址为 localhost (本地服务器)。

连接成功后，您将看到 postgres=# 提示符，表示可以执行 SQL 命令。

2.3 数据库操作

2.3.1 创建数据库

使用 CREATE DATABASE 命令创建新的数据库。

CREATE DATABASE mydatabase;
​

2.3.2 连接到数据库

使用 \c 命令连接到指定的数据库。

\c mydatabase
​

2.3.3 删除数据库

使用 DROP DATABASE 命令删除数据库。

DROP DATABASE mydatabase;
​

2.4 表操作

2.4.1 创建表

使用 CREATE TABLE 命令创建新的表。

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
​

• SERIAL: 自动递增的整数类型，常用于主键。

• PRIMARY KEY: 指定列为主键，唯一标识表中的每一行。

• UNIQUE: 保证列中的值唯一。

• NOT NULL: 保证列中的值不能为空。

• VARCHAR(n): 可变长度字符串类型，最大长度为 n。

• TIMESTAMP WITH TIME ZONE: 带时区的时间戳类型。

• DEFAULT NOW(): 设置默认值为当前时间。

2.4.2 查看表结构

使用 \d 命令查看表的结构。

\d users
​

2.4.3 修改表结构

使用 ALTER TABLE 命令修改表的结构。

-- 添加新的列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- 修改列的数据类型
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(200);
-- 删除列
ALTER TABLE users DROP COLUMN phone;
-- 添加约束
ALTER TABLE users ADD CONSTRAINT check_email CHECK (email LIKE '%@%.%');
​

2.4.4 删除表

使用 DROP TABLE 命令删除表。

DROP TABLE users;
​

2.5 数据操作

2.5.1 插入数据

使用 INSERT INTO 命令插入新的数据。

INSERT INTO users (username, email) VALUES ('john_doe', 'john.doe@example.com');
INSERT INTO users (username, email) VALUES ('jane_doe', 'jane.doe@example.com');
​

2.5.2 查询数据

使用 SELECT 命令查询数据。

-- 查询所有数据
SELECT * FROM users;
-- 查询指定列
SELECT id, username FROM users;
-- 使用 WHERE 子句进行过滤
SELECT * FROM users WHERE username = 'john_doe';
-- 使用 ORDER BY 子句进行排序
SELECT * FROM users ORDER BY created_at DESC;
-- 使用 LIMIT 子句限制结果数量
SELECT * FROM users LIMIT 10;
-- 使用 LIKE 子句进行模糊查询
SELECT * FROM users WHERE username LIKE 'john%';
​

2.5.3 更新数据

使用 UPDATE 命令更新数据。

UPDATE users SET email = 'john.new@example.com' WHERE username = 'john_doe';
​

2.5.4 删除数据

使用 DELETE FROM 命令删除数据。

DELETE FROM users WHERE username = 'john_doe';
​

3. 代码实践示例

以下是一个使用 Python 和 psycopg2 库连接 PostgreSQL 并执行基本数据库操作的示例：

import psycopg2
# 数据库连接信息
db_host = "localhost"
db_name = "mydatabase"
db_user = "postgres"
db_password = "your_password" # 替换为你的密码
try:
    # 连接到 PostgreSQL
    conn = psycopg2.connect(host=db_host, database=db_name, user=db_user, password=db_password)
    cur = conn.cursor()
    # 执行 SQL 查询
    cur.execute("SELECT * FROM users;")
    rows = cur.fetchall()
    # 打印查询结果
    for row in rows:
        print(row)
    # 插入新的数据
    cur.execute("INSERT INTO users (username, email) VALUES (%s, %s)", ('new_user', 'new.user@example.com'))
    conn.commit() # 提交事务
    # 关闭游标和连接
    cur.close()
    conn.close()
except psycopg2.Error as e:
    print("Error connecting to PostgreSQL:", e)
​

代码解释：

1. 导入 psycopg2 库： 用于连接和操作 PostgreSQL 数据库。

2. 定义数据库连接信息： 包括主机、数据库名称、用户名和密码。

3. 连接到 PostgreSQL： 使用 psycopg2.connect() 函数建立连接。

4. 创建游标： 使用 conn.cursor() 创建游标对象，用于执行 SQL 命令。

5. 执行 SQL 查询： 使用 cur.execute() 函数执行 SQL 查询，并使用 cur.fetchall() 获取所有结果。

6. 打印查询结果： 遍历结果集并打印每一行数据。

7. 插入新的数据： 使用 cur.execute() 函数执行 INSERT 语句，并使用参数化查询，防止 SQL 注入。

8. 提交事务： 使用 conn.commit() 函数提交事务，将更改保存到数据库。

9. 关闭游标和连接： 使用 cur.close() 和 conn.close() 函数关闭游标和连接，释放资源。

10. 异常处理： 使用 try...except 块捕获连接错误，并打印错误信息。

4. 总结

本文介绍了 PostgreSQL 的简介与基础，包括其主要特点、安装配置、数据库操作、表操作、数据操作以及一个使用 Python 和 psycopg2 库进行数据库操作的示例。通过学习这些基础知识，您可以开始使用 PostgreSQL 构建强大的应用程序。 PostgreSQL 拥有庞大的社区和丰富的资源，可以帮助您解决遇到的问题。 不断学习和实践，您将能够充分利用 PostgreSQL 的强大功能。 进一步学习可以关注索引优化、性能调优、备份恢复等方面，以提升数据库的效率和可靠性。