PostgreSQL入门教程 - 从零开始学习PostgreSQL数据库

1. PostgreSQL简介

PostgreSQL是一个功能强大的开源对象关系型数据库管理系统（ORDBMS），它支持大部分SQL标准，并提供了许多现代特性。

核心特点：

✅ 开源免费：完全开源，无许可证费用
✅ 标准兼容：高度符合SQL标准
✅ 功能丰富：支持复杂查询、触发器、视图、存储过程等
✅ 扩展性强：支持自定义数据类型、函数、操作符
✅ ACID特性：完全支持事务的ACID特性
✅ 跨平台：支持Windows、Linux、macOS等操作系统
✅ 高并发：支持多版本并发控制（MVCC）
✅ 数据类型丰富：支持JSON、数组、范围类型等

特性	PostgreSQL	MySQL	Oracle
开源	✅ 完全开源	✅ 开源	❌ 商业
性能	优秀	优秀	优秀
功能	非常丰富	丰富	非常丰富
标准兼容	高度兼容	部分兼容	高度兼容
学习曲线	中等	简单	陡峭
适用场景	企业应用、复杂查询	Web应用	大型企业

Web应用：作为Web应用的后端数据库
企业应用：复杂的企业级应用系统
数据分析：数据仓库和数据分析
地理信息系统：PostGIS扩展支持地理数据
科学研究：科研数据处理和分析

2. 环境搭建与安装

下载PostgreSQL

访问PostgreSQL官网：https://www.postgresql.org/download/windows/
下载Windows安装程序（推荐使用EnterpriseDB提供的安装包）
运行安装程序

安装步骤

选择安装目录：默认 C:\Program Files\PostgreSQL\16
选择组件：
- PostgreSQL Server（必需）
- pgAdmin 4（图形化管理工具，推荐）
- Stack Builder（可选）
- Command Line Tools（推荐）
设置数据目录：默认 C:\Program Files\PostgreSQL\16\data
设置超级用户密码：设置postgres用户的密码（请记住此密码）
设置端口：默认5432
选择区域设置：选择默认或自定义
完成安装

验证安装

打开命令提示符（CMD）或PowerShell：

psql --version
# 应该显示：psql (PostgreSQL) 16.x

# 连接到数据库
psql -U postgres
# 输入密码后进入PostgreSQL命令行

2.2 macOS系统安装

使用Homebrew安装（推荐）

# 安装PostgreSQL
brew install postgresql@16

# 启动PostgreSQL服务
brew services start postgresql@16

# 创建数据库集群（首次安装）
initdb /usr/local/var/postgresql@16

# 连接到数据库
psql -U postgres

使用Postgres.app安装

访问：https://postgresapp.com/
下载并安装Postgres.app
启动应用，点击"Initialize"初始化数据库
双击数据库名称即可连接

Ubuntu/Debian

# 更新包列表
sudo apt update

# 安装PostgreSQL
sudo apt install postgresql postgresql-contrib

# 启动服务
sudo systemctl start postgresql
sudo systemctl enable postgresql

# 切换到postgres用户
sudo -u postgres psql

CentOS/RHEL

# 安装PostgreSQL仓库
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# 安装PostgreSQL
sudo yum install -y postgresql16-server

# 初始化数据库
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb

# 启动服务
sudo systemctl start postgresql-16
sudo systemctl enable postgresql-16

# 拉取PostgreSQL镜像
docker pull postgres:16

# 运行PostgreSQL容器
docker run --name postgres-demo \
  -e POSTGRES_PASSWORD=postgres \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_DB=mydb \
  -p 5432:5432 \
  -d postgres:16

# 连接到容器中的PostgreSQL
docker exec -it postgres-demo psql -U postgres -d mydb

pgAdmin是PostgreSQL的官方图形化管理工具，提供了友好的用户界面。

安装pgAdmin

Windows：安装PostgreSQL时已包含
macOS：brew install pgadmin4
Linux：sudo apt install pgadmin4

使用pgAdmin

启动pgAdmin
右键"Servers" → "Create" → "Server"
填写连接信息：
- Name: 任意名称
- Host: localhost
- Port: 5432
- Username: postgres
- Password: 安装时设置的密码
点击"Save"保存

3. 数据库基本操作

使用psql命令行工具

# 连接到默认数据库（postgres）
psql -U postgres

# 连接到指定数据库
psql -U postgres -d mydb

# 指定主机和端口
psql -h localhost -p 5432 -U postgres -d mydb

基本psql命令

-- 查看所有数据库
\l

-- 切换数据库
\c database_name

-- 查看当前数据库的所有表
\dt

-- 查看表结构
\d table_name

-- 查看所有用户
\du

-- 退出psql
\q

-- 查看帮助
\?

-- 创建数据库
CREATE DATABASE mydb;

-- 创建数据库并指定所有者
CREATE DATABASE mydb OWNER username;

-- 创建数据库并指定编码
CREATE DATABASE mydb 
    ENCODING 'UTF8' 
    LC_COLLATE='en_US.UTF-8' 
    LC_CTYPE='en_US.UTF-8';

-- 查看所有数据库
SELECT datname FROM pg_database;

-- 删除数据库（需要先断开所有连接）
DROP DATABASE mydb;

-- 强制删除数据库
DROP DATABASE mydb WITH (FORCE);

-- 重命名数据库
ALTER DATABASE old_name RENAME TO new_name;

-- 修改数据库所有者
ALTER DATABASE mydb OWNER TO new_owner;

-- 修改数据库连接限制
ALTER DATABASE mydb WITH CONNECTION LIMIT 50;

4. 数据类型

整数类型

-- SMALLINT: 2字节，范围 -32768 到 32767
CREATE TABLE test1 (
    id SMALLINT
);

-- INTEGER/INT: 4字节，范围 -2147483648 到 2147483647
CREATE TABLE test2 (
    id INTEGER,
    age INT
);

-- BIGINT: 8字节，范围很大
CREATE TABLE test3 (
    id BIGINT
);

-- SERIAL: 自动递增整数（1到2147483647）
CREATE TABLE test4 (
    id SERIAL PRIMARY KEY
);

-- BIGSERIAL: 自动递增大整数
CREATE TABLE test5 (
    id BIGSERIAL PRIMARY KEY
);

浮点数类型

-- REAL: 单精度浮点数，4字节
CREATE TABLE test6 (
    price REAL
);

-- DOUBLE PRECISION: 双精度浮点数，8字节
CREATE TABLE test7 (
    price DOUBLE PRECISION
);

-- NUMERIC/DECIMAL: 精确数值类型
CREATE TABLE test8 (
    -- 总共10位，小数点后2位
    amount NUMERIC(10, 2),
    -- 不指定精度，任意精度
    value DECIMAL
);

-- CHAR(n): 固定长度字符串
CREATE TABLE test9 (
    code CHAR(10)  -- 固定10个字符
);

-- VARCHAR(n): 可变长度字符串
CREATE TABLE test10 (
    name VARCHAR(100)  -- 最多100个字符
);

-- TEXT: 无长度限制的字符串
CREATE TABLE test11 (
    description TEXT
);

CREATE TABLE test12 (
    -- DATE: 日期（年月日）
    birth_date DATE,
    
    -- TIME: 时间（时分秒）
    start_time TIME,
    
    -- TIMESTAMP: 日期和时间
    created_at TIMESTAMP,
    
    -- TIMESTAMPTZ: 带时区的时间戳
    updated_at TIMESTAMPTZ,
    
    -- INTERVAL: 时间间隔
    duration INTERVAL
);

-- 插入日期时间数据
INSERT INTO test12 VALUES (
    '2024-01-15',
    '14:30:00',
    '2024-01-15 14:30:00',
    '2024-01-15 14:30:00+08',
    INTERVAL '1 day 2 hours'
);

CREATE TABLE test13 (
    is_active BOOLEAN,
    is_deleted BOOL  -- BOOL是BOOLEAN的别名
);

-- 插入布尔值
INSERT INTO test13 VALUES (TRUE, FALSE);
INSERT INTO test13 VALUES ('t', 'f');  -- 也可以使用't'/'f'
INSERT INTO test13 VALUES ('yes', 'no');  -- 或'yes'/'no'
INSERT INTO test13 VALUES ('1', '0');  -- 或'1'/'0'

-- 创建包含数组的表
CREATE TABLE test14 (
    id SERIAL PRIMARY KEY,
    tags TEXT[],  -- 文本数组
    scores INTEGER[],  -- 整数数组
    matrix INTEGER[][]  -- 二维数组
);

-- 插入数组数据
INSERT INTO test14 (tags, scores) VALUES 
    (ARRAY['java', 'python', 'sql'], ARRAY[90, 85, 92]);

-- 查询数组
SELECT tags[1] FROM test14;  -- 获取第一个元素
SELECT array_length(tags, 1) FROM test14;  -- 获取数组长度

-- 创建包含JSON的表
CREATE TABLE test15 (
    id SERIAL PRIMARY KEY,
    data JSON,  -- JSON类型
    data_jsonb JSONB  -- JSONB类型（二进制存储，支持索引）
);

-- 插入JSON数据
INSERT INTO test15 (data, data_jsonb) VALUES 
    ('{"name": "Alice", "age": 30}', '{"name": "Bob", "age": 25}');

-- 查询JSON数据
SELECT data->>'name' FROM test15;  -- 获取name字段（文本）
SELECT data->'age' FROM test15;  -- 获取age字段（JSON）
SELECT data_jsonb @> '{"age": 25}' FROM test15;  -- 检查是否包含

-- 启用UUID扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 创建包含UUID的表
CREATE TABLE test16 (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(100)
);

-- 插入数据（UUID会自动生成）
INSERT INTO test16 (name) VALUES ('Alice');

5. 表操作

基本语法

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    ...
);

完整示例

-- 创建用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    age INTEGER CHECK (age >= 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建订单表
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    order_no VARCHAR(50) UNIQUE NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL CHECK (total_amount > 0),
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

PRIMARY KEY（主键）

CREATE TABLE test17 (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100)
);

-- 或者
CREATE TABLE test18 (
    id INTEGER,
    name VARCHAR(100),
    PRIMARY KEY (id)
);

-- 复合主键
CREATE TABLE test19 (
    user_id INTEGER,
    role_id INTEGER,
    PRIMARY KEY (user_id, role_id)
);

FOREIGN KEY（外键）

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 带级联操作
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) 
        ON DELETE CASCADE  -- 删除订单时自动删除订单项
        ON UPDATE CASCADE  -- 更新订单ID时自动更新
);

NOT NULL（非空）

CREATE TABLE test20 (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL
);

UNIQUE（唯一）

CREATE TABLE test21 (
    id INTEGER PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    username VARCHAR(50) UNIQUE NOT NULL
);

-- 复合唯一约束
CREATE TABLE test22 (
    user_id INTEGER,
    role_id INTEGER,
    UNIQUE (user_id, role_id)
);

CHECK（检查约束）

CREATE TABLE test23 (
    id INTEGER PRIMARY KEY,
    age INTEGER CHECK (age >= 0 AND age <= 150),
    price DECIMAL(10, 2) CHECK (price > 0),
    status VARCHAR(20) CHECK (status IN ('active', 'inactive', 'pending'))
);

DEFAULT（默认值）

CREATE TABLE test24 (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'active',
    count INTEGER DEFAULT 0
);

添加列

-- 添加单列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 添加多列
ALTER TABLE users 
    ADD COLUMN phone VARCHAR(20),
    ADD COLUMN address TEXT;

删除列

ALTER TABLE users DROP COLUMN phone;

-- 如果列有依赖，使用CASCADE
ALTER TABLE users DROP COLUMN phone CASCADE;

修改列

-- 修改列类型
ALTER TABLE users ALTER COLUMN age TYPE SMALLINT;

-- 修改列名
ALTER TABLE users RENAME COLUMN age TO user_age;

-- 设置默认值
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';

-- 删除默认值
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;

-- 设置NOT NULL
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- 删除NOT NULL
ALTER TABLE users ALTER COLUMN email DROP NOT NULL;

添加约束

-- 添加主键
ALTER TABLE test25 ADD PRIMARY KEY (id);

-- 添加外键
ALTER TABLE orders 
    ADD CONSTRAINT fk_user 
    FOREIGN KEY (user_id) REFERENCES users(id);

-- 添加唯一约束
ALTER TABLE users ADD CONSTRAINT uk_email UNIQUE (email);

-- 添加检查约束
ALTER TABLE users 
    ADD CONSTRAINT chk_age 
    CHECK (age >= 0);

删除约束

-- 删除约束（需要知道约束名）
ALTER TABLE users DROP CONSTRAINT chk_age;

-- 删除主键
ALTER TABLE test25 DROP CONSTRAINT test25_pkey;

-- 删除外键
ALTER TABLE orders DROP CONSTRAINT fk_user;

-- 重命名表
ALTER TABLE old_table_name RENAME TO new_table_name;

-- 删除表
DROP TABLE table_name;

-- 如果表有依赖，使用CASCADE
DROP TABLE table_name CASCADE;

-- 如果表存在才删除
DROP TABLE IF EXISTS table_name;

-- 使用psql命令
\d table_name  -- 查看表结构
\d+ table_name  -- 查看详细信息

-- 使用SQL查询
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'users';

6. 数据操作（DML）

基本插入

-- 插入单行数据
INSERT INTO users (username, email, password, age) 
VALUES ('alice', 'alice@example.com', 'password123', 25);

-- 插入多行数据
INSERT INTO users (username, email, password, age) VALUES
    ('bob', 'bob@example.com', 'password123', 30),
    ('charlie', 'charlie@example.com', 'password123', 28),
    ('david', 'david@example.com', 'password123', 35);

-- 插入所有列（按表结构顺序）
INSERT INTO users VALUES 
    (DEFAULT, 'eve', 'eve@example.com', 'password123', 22, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

从查询结果插入

-- 从另一个表插入数据
INSERT INTO users_backup (username, email, password, age)
SELECT username, email, password, age
FROM users
WHERE age > 25;

插入时处理冲突

-- ON CONFLICT处理（PostgreSQL特有）
INSERT INTO users (username, email, password, age)
VALUES ('alice', 'alice@example.com', 'password123', 25)
ON CONFLICT (username) 
DO UPDATE SET 
    email = EXCLUDED.email,
    updated_at = CURRENT_TIMESTAMP;

-- 如果冲突则忽略
INSERT INTO users (username, email, password, age)
VALUES ('alice', 'alice@example.com', 'password123', 25)
ON CONFLICT (username) DO NOTHING;

基本更新

-- 更新单行
UPDATE users 
SET email = 'newemail@example.com'
WHERE username = 'alice';

-- 更新多列
UPDATE users 
SET 
    email = 'newemail@example.com',
    age = 26,
    updated_at = CURRENT_TIMESTAMP
WHERE username = 'alice';

-- 更新所有行（谨慎使用！）
UPDATE users SET status = 'active';

使用子查询更新

-- 基于子查询更新
UPDATE orders 
SET status = 'completed'
WHERE user_id IN (
    SELECT id FROM users WHERE age > 30
);

使用JOIN更新

-- PostgreSQL支持JOIN更新
UPDATE orders o
SET status = 'completed'
FROM users u
WHERE o.user_id = u.id AND u.age > 30;

基本删除

-- 删除指定行
DELETE FROM users WHERE username = 'alice';

-- 删除所有行（谨慎使用！）
DELETE FROM users;

-- 使用子查询删除
DELETE FROM orders 
WHERE user_id IN (
    SELECT id FROM users WHERE age < 18
);

TRUNCATE（快速清空表）

-- 清空表（比DELETE快，但无法回滚）
TRUNCATE TABLE users;

-- 清空多个表
TRUNCATE TABLE users, orders;

-- 重置自增序列
TRUNCATE TABLE users RESTART IDENTITY;

7. 查询操作（SELECT）

SELECT基本语法

-- 查询所有列
SELECT * FROM users;

-- 查询指定列
SELECT id, username, email FROM users;

-- 使用别名
SELECT 
    id AS user_id,
    username AS name,
    email AS email_address
FROM users;

-- 使用表达式
SELECT 
    username,
    age,
    age + 10 AS age_in_10_years
FROM users;

比较运算符

-- 等于
SELECT * FROM users WHERE age = 25;

-- 不等于
SELECT * FROM users WHERE age != 25;
SELECT * FROM users WHERE age <> 25;

-- 大于、小于
SELECT * FROM users WHERE age > 25;
SELECT * FROM users WHERE age < 30;
SELECT * FROM users WHERE age >= 25;
SELECT * FROM users WHERE age <= 30;

-- BETWEEN
SELECT * FROM users WHERE age BETWEEN 25 AND 30;

-- IN
SELECT * FROM users WHERE age IN (25, 30, 35);

-- LIKE（模糊匹配）
SELECT * FROM users WHERE username LIKE 'a%';  -- 以a开头
SELECT * FROM users WHERE username LIKE '%e';  -- 以e结尾
SELECT * FROM users WHERE username LIKE '%li%';  -- 包含li

-- ILIKE（不区分大小写，PostgreSQL特有）
SELECT * FROM users WHERE username ILIKE 'ALICE';

-- IS NULL / IS NOT NULL
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

逻辑运算符

-- AND
SELECT * FROM users WHERE age > 25 AND age < 35;

-- OR
SELECT * FROM users WHERE age < 20 OR age > 60;

-- NOT
SELECT * FROM users WHERE NOT age = 25;

-- 组合使用
SELECT * FROM users 
WHERE (age > 25 AND age < 35) 
   OR (username LIKE 'a%');

-- 升序排序（默认）
SELECT * FROM users ORDER BY age;

-- 降序排序
SELECT * FROM users ORDER BY age DESC;

-- 多列排序
SELECT * FROM users ORDER BY age DESC, username ASC;

-- 使用表达式排序
SELECT * FROM users ORDER BY LENGTH(username);

-- 限制返回行数
SELECT * FROM users LIMIT 10;

-- 跳过前N行
SELECT * FROM users OFFSET 5;

-- 分页查询
SELECT * FROM users 
ORDER BY id 
LIMIT 10 OFFSET 0;  -- 第1页
SELECT * FROM users 
ORDER BY id 
LIMIT 10 OFFSET 10;  -- 第2页

-- 去除重复行
SELECT DISTINCT age FROM users;

-- 多列去重
SELECT DISTINCT age, status FROM users;

-- COUNT：计数
SELECT COUNT(*) FROM users;
SELECT COUNT(email) FROM users;  -- 不统计NULL

-- SUM：求和
SELECT SUM(total_amount) FROM orders;

-- AVG：平均值
SELECT AVG(age) FROM users;

-- MAX/MIN：最大值/最小值
SELECT MAX(age) FROM users;
SELECT MIN(age) FROM users;

-- 组合使用
SELECT 
    COUNT(*) AS total_users,
    AVG(age) AS avg_age,
    MAX(age) AS max_age,
    MIN(age) AS min_age
FROM users;

-- 按单列分组
SELECT status, COUNT(*) 
FROM orders 
GROUP BY status;

-- 按多列分组
SELECT user_id, status, COUNT(*) 
FROM orders 
GROUP BY user_id, status;

-- 使用HAVING过滤分组
SELECT status, COUNT(*) 
FROM orders 
GROUP BY status 
HAVING COUNT(*) > 10;

INNER JOIN（内连接）

-- 基本语法
SELECT u.username, o.order_no, o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 简写（INNER可省略）
SELECT u.username, o.order_no, o.total_amount
FROM users u
JOIN orders o ON u.id = o.user_id;

LEFT JOIN（左连接）

-- 左连接：返回左表所有记录，右表没有匹配则NULL
SELECT u.username, o.order_no
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

RIGHT JOIN（右连接）

-- 右连接：返回右表所有记录，左表没有匹配则NULL
SELECT u.username, o.order_no
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

FULL OUTER JOIN（全外连接）

-- 全外连接：返回两表所有记录
SELECT u.username, o.order_no
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

多表连接

SELECT 
    u.username,
    o.order_no,
    oi.product_name,
    oi.quantity
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id;

标量子查询

-- 返回单个值的子查询
SELECT username, age,
    (SELECT AVG(age) FROM users) AS avg_age
FROM users;

多行子查询

-- IN子查询
SELECT * FROM users 
WHERE id IN (
    SELECT user_id FROM orders WHERE total_amount > 1000
);

-- EXISTS子查询
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id AND o.total_amount > 1000
);

字符串函数

-- 连接字符串
SELECT CONCAT('Hello', ' ', 'World');
SELECT 'Hello' || ' ' || 'World';

-- 长度
SELECT LENGTH('Hello');

-- 大小写转换
SELECT UPPER('hello');  -- HELLO
SELECT LOWER('HELLO');  -- hello

-- 截取
SELECT SUBSTRING('Hello World', 1, 5);  -- Hello

-- 替换
SELECT REPLACE('Hello World', 'World', 'PostgreSQL');

-- 去除空格
SELECT TRIM('  Hello  ');  -- Hello
SELECT LTRIM('  Hello');  -- Hello
SELECT RTRIM('Hello  ');  -- Hello

数值函数

-- 绝对值
SELECT ABS(-10);  -- 10

-- 四舍五入
SELECT ROUND(3.14159, 2);  -- 3.14

-- 向上取整
SELECT CEIL(3.14);  -- 4

-- 向下取整
SELECT FLOOR(3.14);  -- 3

-- 随机数
SELECT RANDOM();  -- 0到1之间的随机数

日期函数

-- 当前日期时间
SELECT CURRENT_DATE;  -- 当前日期
SELECT CURRENT_TIME;  -- 当前时间
SELECT CURRENT_TIMESTAMP;  -- 当前时间戳
SELECT NOW();  -- 当前时间戳

-- 提取日期部分
SELECT EXTRACT(YEAR FROM CURRENT_TIMESTAMP);
SELECT EXTRACT(MONTH FROM CURRENT_TIMESTAMP);
SELECT EXTRACT(DAY FROM CURRENT_TIMESTAMP);

-- 日期运算
SELECT CURRENT_DATE + INTERVAL '1 day';  -- 明天
SELECT CURRENT_DATE - INTERVAL '1 month';  -- 上个月
SELECT AGE(CURRENT_DATE, '2000-01-01');  -- 年龄

8. 索引与优化

索引是数据库中用于快速查找数据的数据结构，类似于书籍的目录。

8.2 创建索引

基本索引

-- 创建单列索引
CREATE INDEX idx_username ON users(username);

-- 创建多列索引（复合索引）
CREATE INDEX idx_user_age ON users(username, age);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 创建部分索引（只索引满足条件的行）
CREATE INDEX idx_active_users ON users(username) 
WHERE status = 'active';

索引类型

-- B-tree索引（默认，最常用）
CREATE INDEX idx_name ON users(username);

-- Hash索引（只支持等值查询）
CREATE INDEX idx_name ON users USING HASH(username);

-- GIN索引（用于数组、JSON等）
CREATE INDEX idx_tags ON products USING GIN(tags);

-- GiST索引（用于全文搜索、几何数据）
CREATE INDEX idx_location ON places USING GIST(location);

-- 查看表的所有索引
SELECT indexname, indexdef 
FROM pg_indexes 
WHERE tablename = 'users';

-- 使用psql命令
\d users

-- 删除索引
DROP INDEX idx_username;

-- 如果不存在则忽略错误
DROP INDEX IF EXISTS idx_username;

为经常查询的列创建索引
为外键创建索引
为WHERE、JOIN、ORDER BY中的列创建索引
避免在小表上创建过多索引
定期分析表以更新统计信息

-- 分析表（更新统计信息）
ANALYZE users;

-- 查看查询计划
EXPLAIN SELECT * FROM users WHERE username = 'alice';

-- 查看详细执行计划
EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'alice';

9. 事务处理

事务是一组SQL语句的集合，要么全部执行成功，要么全部回滚。

原子性（Atomicity）：事务中的所有操作要么全部成功，要么全部失败
一致性（Consistency）：事务执行前后数据库保持一致状态
隔离性（Isolation）：并发事务之间相互隔离
持久性（Durability）：事务提交后数据永久保存

开始和提交事务

-- 开始事务
BEGIN;

-- 执行SQL语句
INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;

-- 提交事务
COMMIT;

回滚事务

BEGIN;

INSERT INTO users (username, email) VALUES ('bob', 'bob@example.com');
-- 如果发生错误，回滚
ROLLBACK;

保存点（Savepoint）

BEGIN;

INSERT INTO users (username, email) VALUES ('charlie', 'charlie@example.com');
SAVEPOINT sp1;

UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
-- 如果出错，可以回滚到保存点
ROLLBACK TO SAVEPOINT sp1;

COMMIT;

PostgreSQL支持以下隔离级别：

-- 设置隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 查看当前隔离级别
SHOW transaction_isolation;

隔离级别说明：

READ UNCOMMITTED：读取未提交数据（PostgreSQL实际不支持，会提升为READ COMMITTED）
READ COMMITTED：读取已提交数据（默认）
REPEATABLE READ：可重复读
SERIALIZABLE：串行化（最高隔离级别）

10. 视图和存储过程

创建视图

-- 创建简单视图
CREATE VIEW active_users AS
SELECT id, username, email, age
FROM users
WHERE status = 'active';

-- 使用视图
SELECT * FROM active_users;

-- 创建带选项的视图
CREATE OR REPLACE VIEW user_orders AS
SELECT 
    u.username,
    o.order_no,
    o.total_amount,
    o.created_at
FROM users u
JOIN orders o ON u.id = o.user_id;

修改视图

-- 修改视图定义
CREATE OR REPLACE VIEW active_users AS
SELECT id, username, email, age, created_at
FROM users
WHERE status = 'active';

删除视图

DROP VIEW active_users;
DROP VIEW IF EXISTS active_users;

创建存储过程

-- 创建简单存储过程
CREATE OR REPLACE PROCEDURE update_user_age(
    p_user_id INTEGER,
    p_new_age INTEGER
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE users 
    SET age = p_new_age, updated_at = CURRENT_TIMESTAMP
    WHERE id = p_user_id;
END;
$$;

-- 调用存储过程
CALL update_user_age(1, 30);

带返回值的存储过程

CREATE OR REPLACE PROCEDURE get_user_count(
    OUT total_count INTEGER
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT COUNT(*) INTO total_count FROM users;
END;
$$;

-- 调用
DO $$
DECLARE
    count INTEGER;
BEGIN
    CALL get_user_count(count);
    RAISE NOTICE 'Total users: %', count;
END;
$$;

创建函数

-- 创建简单函数
CREATE OR REPLACE FUNCTION get_user_age(p_username VARCHAR)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
    user_age INTEGER;
BEGIN
    SELECT age INTO user_age FROM users WHERE username = p_username;
    RETURN user_age;
END;
$$;

-- 使用函数
SELECT get_user_age('alice');

返回表的函数

CREATE OR REPLACE FUNCTION get_users_by_age(min_age INTEGER)
RETURNS TABLE (
    id INTEGER,
    username VARCHAR,
    age INTEGER
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT u.id, u.username, u.age
    FROM users u
    WHERE u.age >= min_age;
END;
$$;

-- 使用函数
SELECT * FROM get_users_by_age(25);

11. 用户权限管理

-- 创建用户
CREATE USER newuser WITH PASSWORD 'password123';

-- 创建用户并指定权限
CREATE USER admin_user WITH 
    PASSWORD 'admin123'
    CREATEDB
    CREATEROLE
    SUPERUSER;

-- 修改密码
ALTER USER username WITH PASSWORD 'newpassword';

-- 修改用户属性
ALTER USER username WITH CREATEDB;
ALTER USER username WITH SUPERUSER;

-- 删除用户
DROP USER username;

-- 如果用户有对象，使用CASCADE
DROP USER username CASCADE;

授予权限

-- 授予表的所有权限
GRANT ALL PRIVILEGES ON TABLE users TO username;

-- 授予特定权限
GRANT SELECT, INSERT, UPDATE ON TABLE users TO username;

-- 授予数据库权限
GRANT CONNECT ON DATABASE mydb TO username;

-- 授予模式权限
GRANT USAGE ON SCHEMA public TO username;

撤销权限

-- 撤销权限
REVOKE SELECT ON TABLE users FROM username;

-- 撤销所有权限
REVOKE ALL PRIVILEGES ON TABLE users FROM username;

查看权限

-- 查看表权限
SELECT grantee, privilege_type 
FROM information_schema.role_table_grants 
WHERE table_name = 'users';

12. 备份与恢复

备份单个数据库

# 备份数据库
pg_dump -U postgres -d mydb > backup.sql

# 备份为自定义格式（压缩）
pg_dump -U postgres -d mydb -Fc > backup.dump

# 备份为tar格式
pg_dump -U postgres -d mydb -Ft > backup.tar

备份所有数据库

# 备份所有数据库
pg_dumpall -U postgres > all_databases.sql

备份特定表

# 备份特定表
pg_dump -U postgres -d mydb -t users > users_backup.sql

# 从SQL文件恢复
psql -U postgres -d mydb < backup.sql

# 从自定义格式恢复
pg_restore -U postgres -d mydb backup.dump

# 从tar格式恢复
pg_restore -U postgres -d mydb backup.tar

#!/bin/bash
# backup.sh

BACKUP_DIR="/backup/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="mydb"

mkdir -p $BACKUP_DIR

pg_dump -U postgres -d $DB_NAME -Fc > $BACKUP_DIR/${DB_NAME}_${DATE}.dump

# 删除7天前的备份
find $BACKUP_DIR -name "*.dump" -mtime +7 -delete

13. 性能优化

使用EXPLAIN分析查询

-- 查看查询计划
EXPLAIN SELECT * FROM users WHERE username = 'alice';

-- 查看详细执行计划
EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'alice';

-- 查看缓冲区使用情况
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE username = 'alice';

优化建议

使用索引：为经常查询的列创建索引
**避免SELECT ***：只查询需要的列
使用LIMIT：限制返回结果数量
优化JOIN：确保JOIN条件有索引
使用EXISTS代替IN：对于大表，EXISTS通常更快

-- 创建部分索引（只索引需要的行）
CREATE INDEX idx_active_users ON users(username) 
WHERE status = 'active';

-- 创建表达式索引
CREATE INDEX idx_lower_username ON users(LOWER(username));

-- 定期重建索引
REINDEX TABLE users;
REINDEX DATABASE mydb;

修改postgresql.conf

# 内存设置
shared_buffers = 256MB          # 共享缓冲区
effective_cache_size = 1GB      # 有效缓存大小
work_mem = 16MB                  # 工作内存

# 连接设置
max_connections = 100            # 最大连接数

# 日志设置
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'

-- 查看当前活动连接
SELECT * FROM pg_stat_activity;

-- 查看表大小
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- 查看索引使用情况
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

14. 实际应用案例

-- 创建用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建用户配置表
CREATE TABLE user_profiles (
    user_id INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INTEGER,
    bio TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建索引
CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_email ON users(email);

-- 插入测试数据
INSERT INTO users (username, email, password_hash) VALUES
    ('alice', 'alice@example.com', 'hash1'),
    ('bob', 'bob@example.com', 'hash2');

INSERT INTO user_profiles (user_id, first_name, last_name, age) VALUES
    (1, 'Alice', 'Smith', 25),
    (2, 'Bob', 'Jones', 30);

-- 查询用户信息
SELECT 
    u.id,
    u.username,
    u.email,
    p.first_name,
    p.last_name,
    p.age
FROM users u
LEFT JOIN user_profiles p ON u.id = p.user_id
WHERE u.username = 'alice';

-- 创建商品表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建订单表
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    order_no VARCHAR(50) UNIQUE NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建订单项表
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    price DECIMAL(10, 2) NOT NULL,
    subtotal DECIMAL(10, 2) GENERATED ALWAYS AS (quantity * price) STORED
);

-- 创建订单视图
CREATE VIEW order_details AS
SELECT 
    o.id AS order_id,
    o.order_no,
    u.username,
    o.total_amount,
    o.status,
    o.created_at,
    COUNT(oi.id) AS item_count
FROM orders o
JOIN users u ON o.user_id = u.id
LEFT JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.id, o.order_no, u.username, o.total_amount, o.status, o.created_at;

-- 查询订单详情
SELECT * FROM order_details WHERE order_no = 'ORD001';

-- 按月统计订单数量和金额
SELECT 
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_revenue,
    AVG(total_amount) AS avg_order_value
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month DESC;

-- 用户购买排行榜
SELECT 
    u.username,
    COUNT(o.id) AS order_count,
    SUM(o.total_amount) AS total_spent,
    AVG(o.total_amount) AS avg_order_value
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username
ORDER BY total_spent DESC
LIMIT 10;