建表与数据类型

-- 整数类型
SMALLINT      -- 2字节，范围 -32768 ~ 32767
INTEGER       -- 4字节，范围 -21亿 ~ 21亿
BIGINT        -- 8字节，范围超大

-- 精确小数
NUMERIC(10,2)  -- 总共10位，小数点后2位，精确计算
DECIMAL(10,2) -- NUMERIC 的别名

-- 浮点类型
REAL          -- 4字节，6位十进制精度
DOUBLE PRECISION -- 8字节，15位十进制精度

-- 自增类型
SMALLSERIAL   -- 2字节自增
SERIAL        -- 4字节自增（常用）
BIGSERIAL     -- 8字节自增

CHAR(n)       -- 定长字符串，不足补空格
VARCHAR(n)    -- 变长字符串，有长度限制（最常用）
TEXT          -- 不限长度字符串（无性能损失）

DATE          -- 日期（年月日）
TIME          -- 时间（时分秒，可带时区）
TIMESTAMP     -- 日期+时间（不带时区）
TIMESTAMPTZ   -- 日期+时间（带时区）
INTERVAL      -- 时间间隔（1 day '3 hours'）

BOOLEAN        -- TRUE / FALSE / NULL
-- 输入时也支持：'yes'/'no', '1'/'0', 't'/'f'

JSON          -- 文本格式的 JSON（保留格式，含空格/缩进）
JSONB         -- 二进制格式（去除空格，可建索引，处理更快）

-- 创建 schema（命名空间）
CREATE SCHEMA IF NOT EXISTS school;

-- 创建一张完整的用户表
CREATE TABLE school.students (
    id SERIAL PRIMARY KEY,
    student_no VARCHAR(20) UNIQUE NOT NULL,  -- 学号，唯一约束
    name VARCHAR(50) NOT NULL,
    gender CHAR(1) CHECK (gender IN ('M', 'F', 'O')),  -- 检查约束
    birth_date DATE,
    email VARCHAR(200) UNIQUE,
    phone VARCHAR(20),
    address TEXT,
    enrollment_date DATE DEFAULT CURRENT_DATE,
    status VARCHAR(10) DEFAULT 'active' CHECK (status IN ('active', 'graduated', 'suspended')),
    gpa NUMERIC(3,2) DEFAULT 0.00 CHECK (gpa >= 0.00 AND gpa <= 4.00),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 查看表结构
\d school.students

-- 1. NOT NULL — 不能为空
name VARCHAR(100) NOT NULL

-- 2. UNIQUE — 唯一
email VARCHAR(200) UNIQUE

-- 3. PRIMARY KEY — 主键（NOT NULL + UNIQUE）
id SERIAL PRIMARY KEY

-- 4. CHECK — 条件检查
price NUMERIC(10,2) CHECK (price > 0)
age INTEGER CHECK (age >= 0 AND age <= 150)

-- 5. DEFAULT — 默认值
created_at TIMESTAMPTZ DEFAULT NOW()

CREATE TABLE products (
    product_id SERIAL,
    product_name VARCHAR(100) NOT NULL,
    price NUMERIC(10,2),
    category VARCHAR(50),
    stock INTEGER DEFAULT 0,

    CONSTRAINT pk_products PRIMARY KEY (product_id),
    CONSTRAINT uq_products_name UNIQUE (product_name),
    CONSTRAINT ck_products_price CHECK (price > 0),
    CONSTRAINT ck_products_stock CHECK (stock >= 0)
);

-- 添加列
ALTER TABLE students ADD COLUMN wechat VARCHAR(50);

-- 删除列
ALTER TABLE students DROP COLUMN wechat;

-- 修改列类型
ALTER TABLE students ALTER COLUMN grade TYPE VARCHAR(10);

-- 设置默认值
ALTER TABLE students ALTER COLUMN grade SET DEFAULT 'freshman';

-- 添加约束
ALTER TABLE students ADD CONSTRAINT ck_students_age CHECK (age >= 0);

-- 删除约束
ALTER TABLE students DROP CONSTRAINT ck_students_age;

-- 重命名列
ALTER TABLE students RENAME COLUMN grade TO class_level;

-- 重命名表
ALTER TABLE students RENAME TO scholar;

-- 创建 schema（类似命名空间/目录）
CREATE SCHEMA sales;
CREATE SCHEMA hr;

-- 在 schema 中创建表
CREATE TABLE sales.orders (id SERIAL PRIMARY KEY, ...);
CREATE TABLE hr.employees (id SERIAL PRIMARY KEY, ...);

-- 查看所有 schema
\dn

-- 设置搜索路径（简化访问）
SET search_path TO sales, hr, public;

-- 创建使用数据库
CREATE DATABASE ecommerce;
\c ecommerce

-- 用户表
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(200) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    phone VARCHAR(20),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 商品分类表
CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id INTEGER REFERENCES categories(category_id),
    description TEXT
);

-- 商品表
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    category_id INTEGER REFERENCES categories(category_id),
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price NUMERIC(10,2) NOT NULL CHECK (price > 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 订单表（先建，供订单项引用）
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(user_id),
    status VARCHAR(20) DEFAULT 'pending' 
        CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')),
    total_amount NUMERIC(12,2) NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 订单项表（多对多关联表）
CREATE TABLE order_items (
    order_id INTEGER REFERENCES orders(order_id),
    product_id INTEGER REFERENCES products(product_id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);