Hello World

目标

通过 psql 命令行和 Python 驱动完成 PostgreSQL 数据库创建、表设计、CRUD 操作，体验 PostgreSQL 独有的 JSONB 和窗口函数特性。

环境准备

# 确保 PostgreSQL 已运行
psql -U postgres -h localhost

第一步：psql 命令行操作

-- 创建数据库
CREATE DATABASE hello_pg;

-- 连接（命令行：\c hello_pg）

-- 创建图书表
CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author VARCHAR(100),
    price DECIMAL(6,2),
    tags JSONB DEFAULT '[]',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 插入数据（含 JSONB）
INSERT INTO books (title, author, price, tags) VALUES
    ('深入浅出PostgreSQL', '张三', 79.00, '["数据库","SQL","入门"]'),
    ('高性能PostgreSQL', '李四', 99.00, '["数据库","性能优化","进阶"]'),
    ('PostgreSQL实战', '王五', 69.00, '["数据库","实践","SQL"]');

-- JSONB 查询：查找 tags 包含"进阶"的书
SELECT title, author FROM books
WHERE tags @> '["进阶"]'::jsonb;

-- 窗口函数：按价格排名
SELECT title, price,
    RANK() OVER (ORDER BY price DESC) AS rank
FROM books;

第二步：Python + psycopg2

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    dbname="hello_pg",
    user="postgres",
    password="pg123"
)
cur = conn.cursor()

# 创建表
cur.execute("""
    CREATE TABLE IF NOT EXISTS students (
        id SERIAL PRIMARY KEY,
        name VARCHAR(50) NOT NULL,
        score INT,
        grade CHAR(1) GENERATED ALWAYS AS (
            CASE
                WHEN score >= 90 THEN 'A'
                WHEN score >= 80 THEN 'B'
                WHEN score >= 70 THEN 'C'
                ELSE 'F'
            END
        ) STORED
    )
""")

# 插入
cur.execute(
    "INSERT INTO students (name, score) VALUES (%s, %s), (%s, %s)",
    ("小明", 92, "小红", 85)
)
conn.commit()

# 查询
cur.execute("SELECT * FROM students ORDER BY score DESC")
for row in cur.fetchall():
    print(f"{row[1]}: {row[2]}分 → {row[3]}")

cur.close()
conn.close()

预期输出

# psql JSONB 查询
 title       | author
-------------+-------
 高性能PostgreSQL | 李四

# 窗口函数
 title              | price | rank
--------------------+-------+------
 高性能PostgreSQL   | 99.00 |    1
 深入浅出PostgreSQL | 79.00 |    2
 PostgreSQL实战     | 69.00 |    3

# Python 生成列
小明: 92分 → A
小红: 85分 → B