PostgreSQL教程

2.1 数据库操作

创建数据库

在PostgreSQL中，可以使用CREATE DATABASE语句创建新的数据库：

-- 创建数据库
CREATE DATABASE mydb;

-- 创建数据库并指定编码和所有者
CREATE DATABASE mydb 
    WITH OWNER = postgres 
    ENCODING = 'UTF8' 
    LC_COLLATE = 'zh_CN.UTF-8' 
    LC_CTYPE = 'zh_CN.UTF-8' 
    TEMPLATE = template0;

查看数据库

可以使用以下命令查看数据库列表：

-- 使用psql命令行工具
\l

-- 或者使用SQL查询
SELECT datname FROM pg_database;

切换数据库

在psql中切换到指定数据库：

\c mydb

删除数据库

使用DROP DATABASE语句删除数据库：

-- 删除数据库
DROP DATABASE mydb;

-- 如果数据库正在使用，可以强制删除（PostgreSQL 13+）
DROP DATABASE IF EXISTS mydb WITH (FORCE);

2.2 数据表操作

创建数据表

使用CREATE TABLE语句创建数据表：

-- 创建简单表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    age INTEGER CHECK (age >= 0 AND age <= 150),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建带外键的表
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    product_name VARCHAR(255) NOT NULL,
    quantity INTEGER DEFAULT 1,
    price DECIMAL(10,2),
    order_date DATE DEFAULT CURRENT_DATE
);

常用数据类型

字段约束

PostgreSQL支持多种字段约束：

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,           -- 非空约束
    sku VARCHAR(50) UNIQUE,               -- 唯一约束
    price DECIMAL(10,2) CHECK (price > 0), -- 检查约束
    category_id INTEGER REFERENCES categories(id), -- 外键约束
    description TEXT DEFAULT '暂无描述',    -- 默认值约束
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2.3 表结构修改

添加字段

-- 添加单个字段
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 添加多个字段
ALTER TABLE users 
    ADD COLUMN address TEXT,
    ADD COLUMN gender CHAR(1);

修改字段

-- 修改字段类型
ALTER TABLE users ALTER COLUMN phone TYPE VARCHAR(30);

-- 设置默认值
ALTER TABLE users ALTER COLUMN created_at SET DEFAULT CURRENT_TIMESTAMP;

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

重命名字段

ALTER TABLE users RENAME COLUMN phone TO telephone;

删除字段

ALTER TABLE users DROP COLUMN address;

重命名表

ALTER TABLE users RENAME TO customers;

2.4 查看表信息

查看表结构

-- 使用psql命令
\d users

-- 查看表定义
\d+ users

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

查看表约束

SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'users';

2.5 删除数据表

删除表

-- 删除单个表
DROP TABLE users;

-- 删除多个表
DROP TABLE users, orders;

-- 如果表存在则删除
DROP TABLE IF EXISTS users;

级联删除

-- 删除表及其依赖对象
DROP TABLE users CASCADE;

2.6 实际应用示例

-- 创建数据库
CREATE DATABASE ecommerce;

-- 切换到新数据库
\c ecommerce;

-- 创建用户表
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,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建商品分类表
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建商品表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    category_id INTEGER REFERENCES categories(id),
    stock_quantity INTEGER DEFAULT 0 CHECK (stock_quantity >= 0),
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建订单表
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    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),
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    price DECIMAL(10,2) NOT NULL
);