数据库与表管理

学会创建、修改和删除数据库与表，掌握 PostgreSQL 的数据类型和约束。

数据库管理

-- 创建数据库
CREATE DATABASE testdb;
CREATE DATABASE testdb OWNER postgres ENCODING 'UTF8' LC_COLLATE 'zh_CN.UTF-8';

-- 查看数据库列表
\l

-- 查看当前数据库
SELECT current_database();

-- 查看数据库大小
SELECT pg_size_pretty(pg_database_size('testdb'));

-- 切换数据库
\c testdb

-- 修改数据库名称
ALTER DATABASE testdb RENAME TO newdb;

-- 修改数据库配置
ALTER DATABASE testdb SET timezone TO 'Asia/Shanghai';

-- 删除数据库（不能删除当前连接的库）
DROP DATABASE testdb;

Schema（模式）

Schema 是 PostgreSQL 特有的概念，用于组织数据库对象：

-- 查看当前 schema
SELECT current_schema();

-- 查看所有 schema
\dn

-- 创建 schema
CREATE SCHEMA myapp;
CREATE SCHEMA IF NOT EXISTS myapp AUTHORIZATION postgres;

-- 在 schema 中创建表
CREATE TABLE myapp.products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200)
);

-- 设置搜索路径
SET search_path TO myapp, public;

-- 查看搜索路径
SHOW search_path;

-- 删除 schema
DROP SCHEMA myapp CASCADE;  -- CASCADE 会删除所有包含的对象

数据类型概览

类别	类型	说明
整数	SMALLINT / INT / BIGINT	2/4/8 字节
自增	SMALLSERIAL / SERIAL / BIGSERIAL	自动递增
浮点	REAL / DOUBLE PRECISION	不精确
精确小数	NUMERIC(p,s) / DECIMAL(p,s)	推荐金额使用
字符	CHAR(n) / VARCHAR(n) / TEXT	定长/变长/无限制
布尔	BOOLEAN	TRUE / FALSE
日期时间	DATE / TIME / TIMESTAMP / TIMESTAMPTZ	日期、时间、时间戳
二进制	BYTEA	二进制数据
网络	INET / CIDR / MACADDR	IP 地址、MAC 地址
JSON	JSON / JSONB	JSON 数据（JSONB 推荐）
数组	TEXT[] / INT[]	任意类型的数组
枚举	CREATE TYPE	自定义枚举
范围	INT4RANGE / TSRANGE	范围类型
UUID	UUID	通用唯一标识符

表管理

创建表

CREATE TABLE users (
    id SERIAL PRIMARY KEY,                        -- 自增主键
    username VARCHAR(50) NOT NULL,                 -- 非空
    email VARCHAR(200) UNIQUE,                     -- 唯一
    age INT CHECK (age >= 0 AND age < 150),        -- CHECK 约束
    salary NUMERIC(10,2) DEFAULT 0.00,             -- 精确小数
    status VARCHAR(20) DEFAULT 'active',
    tags TEXT[],                                   -- 数组类型
    attributes JSONB DEFAULT '{}',                 -- JSONB
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 使用 GENERATED AS IDENTITY（推荐替代 SERIAL）
CREATE TABLE products (
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price NUMERIC(10,2)
);

查看表结构

-- 查看所有表
\dt

-- 查看表结构
\d users

-- 查看表详细信息
\d+ users

-- 查看建表语句
SELECT pg_get_ddl('users');  -- 或使用 pgAdmin

修改表

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

-- 添加带默认值的列
ALTER TABLE users ADD COLUMN score INT DEFAULT 0;

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

-- 设置默认值
ALTER TABLE users ALTER COLUMN score SET DEFAULT 0;

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

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

-- 重命名列
ALTER TABLE users RENAME COLUMN phone TO mobile;

-- 删除列
ALTER TABLE users DROP COLUMN mobile;

-- 重命名表
ALTER TABLE users RENAME TO members;

删除表

-- 删除表（结构和数据）
DROP TABLE users;
DROP TABLE IF EXISTS users;

-- 清空表数据（保留结构）
TRUNCATE TABLE users;

-- 级联删除（同时删除依赖对象）
DROP TABLE users CASCADE;

约束

-- 主键
id SERIAL PRIMARY KEY
-- 复合主键
PRIMARY KEY (order_id, product_id)

-- 外键
class_id INT REFERENCES classes(id)
FOREIGN KEY (class_id) REFERENCES classes(id)
-- 级联行为
user_id INT REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
-- 其他选项：SET NULL / SET DEFAULT / RESTRICT / NO ACTION

-- 唯一约束
email VARCHAR(200) UNIQUE
UNIQUE (username, email)

-- 非空
username VARCHAR(50) NOT NULL

-- CHECK 约束
age INT CHECK (age >= 0)
CONSTRAINT valid_age CHECK (age >= 0 AND age < 150)

-- 排除约束（PostgreSQL 特有）
EXCLUDE USING gist (period WITH &&)

扩展模块

-- 查看已安装的扩展
\dx

-- 查看可用扩展
SELECT * FROM pg_available_extensions;

-- 常用扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";        -- UUID 生成
CREATE EXTENSION IF NOT EXISTS "pgcrypto";          -- 加密函数
CREATE EXTENSION IF NOT EXISTS "pg_trgm";           -- 模糊搜索
CREATE EXTENSION IF NOT EXISTS "hstore";            -- 键值存储
CREATE EXTENSION IF NOT EXISTS "postgis";           -- 地理空间

练习

-- 1. 创建博客系统的表：
--    categories: id, name, created_at
--    posts: id, title, content (TEXT), category_id (FK), status, created_at
--    comments: id, post_id (FK CASCADE), author, body, created_at

-- 2. 为 posts 的 title 添加 UNIQUE 约束
-- 3. 扩展 pgcrypto 扩展
-- 4. 创建一个 schema 叫 blog，把表移到里面