编辑“︁PostgreSQL基础”︁（章节）

= PostgreSQL基础 =

PostgreSQL是一个功能强大的开源关系型数据库管理系统（RDBMS），以其可靠性、功能丰富性和可扩展性而闻名。它支持SQL标准，并提供了许多高级功能，如复杂查询、外键、触发器、视图、事务完整性、多版本并发控制（MVCC）等。

== 简介 ==
PostgreSQL最初由加州大学伯克利分校开发，名为Postgres，后来演变为PostgreSQL。它适用于各种规模的应用程序，从小型单机应用到大型互联网应用。PostgreSQL支持多种数据类型，包括原生JSON支持，使其成为现代Web开发的理想选择。

== 安装与设置 ==
=== 安装 ===
在Linux系统上，可以使用包管理器安装PostgreSQL：

<syntaxhighlight lang="bash">
# Ubuntu/Debian
sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

# CentOS/RHEL
sudo yum install postgresql-server postgresql-contrib
</syntaxhighlight>

=== 基本配置 ===
安装完成后，需要初始化数据库集群并启动服务：

<syntaxhighlight lang="bash">
# 初始化数据库
sudo postgresql-setup initdb

# 启动服务
sudo systemctl start postgresql

# 设置开机自启
sudo systemctl enable postgresql
</syntaxhighlight>

== 基本操作 ==
=== 连接到数据库 ===
使用psql命令行工具连接到PostgreSQL：

<syntaxhighlight lang="bash">
sudo -u postgres psql
</syntaxhighlight>

=== 创建数据库 ===
在psql中创建新数据库：

<syntaxhighlight lang="sql">
CREATE DATABASE mydatabase;
</syntaxhighlight>

=== 创建表 ===
创建一个简单的用户表：

<syntaxhighlight lang="sql">
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
</syntaxhighlight>

=== 插入数据 ===
向表中插入数据：

<syntaxhighlight lang="sql">
INSERT INTO users (username, email) VALUES ('johndoe', 'john@example.com');
</syntaxhighlight>

=== 查询数据 ===
查询表中的数据：

<syntaxhighlight lang="sql">
SELECT * FROM users;
</syntaxhighlight>

输出示例：
<pre>
 id | username |      email       |          created_at          
----+----------+------------------+-------------------------------
  1 | johndoe  | john@example.com | 2023-05-15 14:32:19.123456+00
</pre>

== 高级特性 ==
=== 事务 ===
PostgreSQL支持ACID事务：

<syntaxhighlight lang="sql">
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
COMMIT;
</syntaxhighlight>

=== JSON支持 ===
PostgreSQL原生支持JSON数据类型：

<syntaxhighlight lang="sql">
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    attributes JSONB
);

INSERT INTO products (name, attributes) 
VALUES ('Laptop', '{"color": "black", "memory": "16GB", "storage": "512GB"}');
</syntaxhighlight>

查询JSON字段：

<syntaxhighlight lang="sql">
SELECT name, attributes->>'color' AS color FROM products;
</syntaxhighlight>

=== 索引 ===
创建索引提高查询性能：

<syntaxhighlight lang="sql">
CREATE INDEX idx_users_username ON users (username);
</syntaxhighlight>

== 实际应用案例 ==
=== 电子商务平台 ===
PostgreSQL可用于存储产品目录、用户信息和订单数据：

<mermaid>
erDiagram
    CUSTOMER ||--o{ ORDER : places
    ORDER ||--|{ ORDER_ITEM : contains
    PRODUCT ||--o{ ORDER_ITEM : includes
    CUSTOMER {
        int id PK
        string name
        string email
    }
    ORDER {
        int id PK
        int customer_id FK
        date order_date
    }
    ORDER_ITEM {
        int id PK
        int order_id FK
        int product_id FK
        int quantity
    }
    PRODUCT {
        int id PK
        string name
        decimal price
    }
</mermaid>

=== 地理信息系统 ===
PostgreSQL的PostGIS扩展使其成为GIS应用的理想选择：

<syntaxhighlight lang="sql">
-- 创建包含地理数据的表
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    geom GEOMETRY(POINT, 4326)
);

-- 插入地理数据
INSERT INTO locations (name, geom)
VALUES ('Central Park', ST_GeomFromText('POINT(-73.968285 40.785091)', 4326));

-- 查询附近的地点
SELECT name FROM locations 
WHERE ST_DWithin(
    geom,
    ST_GeomFromText('POINT(-73.968285 40.785091)', 4326),
    0.01
);
</syntaxhighlight>

== 性能优化 ==
=== 查询分析 ===
使用EXPLAIN分析查询计划：

<syntaxhighlight lang="sql">
EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'johndoe';
</syntaxhighlight>

=== 分区表 ===
对大表进行分区提高性能：

<syntaxhighlight lang="sql">
CREATE TABLE measurement (
    city_id int NOT NULL,
    logdate date NOT NULL,
    peaktemp int,
    unitsales int
) PARTITION BY RANGE (logdate);

-- 创建分区
CREATE TABLE measurement_y2023m01 PARTITION OF measurement
    FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');
</syntaxhighlight>

== 数学公式示例 ==
PostgreSQL支持多种数学函数和运算符。例如，计算欧几里得距离：

<math>
d = \sqrt{(x_2 - x_1)^2 + (y_2 - y_1)^2}
</math>

在PostgreSQL中实现：

<syntaxhighlight lang="sql">
SELECT SQRT(POW(x2 - x1, 2) + POW(y2 - y1, 2)) AS distance
FROM points;
</syntaxhighlight>

== 总结 ==
PostgreSQL是一个功能全面、可扩展的关系型数据库系统，适合各种规模的应用。它提供了丰富的功能集，包括高级SQL支持、JSON处理、地理空间数据处理等。通过合理的设计和优化，PostgreSQL可以满足从简单应用到复杂企业级系统的各种需求。

对于初学者，建议从基本SQL操作开始，逐步探索PostgreSQL的高级特性。对于有经验的开发者，可以深入研究其扩展性、性能调优和特定领域的扩展功能。

[[Category:计算机科学]]
[[Category:数据库与信息系统]]
[[Category:数据库管理系统]]