SQLite表操作 #

-- SQLite 表的特点：
-- 1. 每个表存储在 B-Tree 结构中
-- 2. 支持 ROWID（隐式主键）
-- 3. 支持 WITHOUT ROWID 表
-- 4. 支持临时表
-- 5. 支持虚拟表

-- 表类型：
-- 1. 普通表 - 持久存储
-- 2. 临时表 - 会话期间存在
-- 3. 虚拟表 - 由代码实现

-- 推荐命名规范
-- 1. 使用小写字母
-- 2. 使用下划线分隔
-- 3. 使用复数形式
-- 4. 避免使用保留字

-- 好的命名
users
user_orders
product_categories
order_items

-- 不好的命名
Users          -- 大写
userOrders     -- 驼峰
user-orders    -- 连字符
order          -- 单数

-- CREATE TABLE 基本语法
CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
    [table_constraints]
);

-- 示例：创建用户表
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    age INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 完整的列定义语法
-- column_name datatype [constraints]

CREATE TABLE products (
    -- 列名 + 数据类型
    id INTEGER,
    
    -- 带约束的列
    name TEXT NOT NULL,
    
    -- 带默认值
    status INTEGER DEFAULT 1,
    
    -- 带多个约束
    email TEXT NOT NULL UNIQUE,
    
    -- 带检查约束
    price REAL CHECK(price >= 0),
    
    -- 带排序规则
    code TEXT COLLATE NOCASE
);

-- 单列主键
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT
);

-- 自增主键
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
);

-- 复合主键
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);

-- PRIMARY KEY 与 ROWID
-- INTEGER PRIMARY KEY 是 ROWID 的别名
-- 其他类型的主键会创建独立的索引
CREATE TABLE users (
    id TEXT PRIMARY KEY,  -- 不是 ROWID 的别名
    name TEXT
);

-- 启用外键约束
PRAGMA foreign_keys = ON;

-- 创建带外键的表
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    total REAL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 外键选项
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE      -- 删除用户时删除订单
        ON UPDATE CASCADE      -- 更新用户ID时更新订单
);

-- 外键动作
-- ON DELETE/UPDATE:
--   CASCADE  - 级联操作
--   SET NULL - 设置为NULL
--   SET DEFAULT - 设置为默认值
--   RESTRICT - 限制（默认）
--   NO ACTION - 无动作

-- 自引用外键
CREATE TABLE categories (
    id INTEGER PRIMARY KEY,
    name TEXT,
    parent_id INTEGER,
    FOREIGN KEY (parent_id) REFERENCES categories(id)
);

-- 单列唯一约束
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE
);

-- 多列唯一约束
CREATE TABLE user_roles (
    user_id INTEGER,
    role_id INTEGER,
    UNIQUE (user_id, role_id)
);

-- 命名唯一约束
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT,
    CONSTRAINT uk_users_email UNIQUE (email)
);

-- 单列检查约束
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    price REAL CHECK(price >= 0),
    quantity INTEGER CHECK(quantity >= 0)
);

-- 多列检查约束
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    start_date DATE,
    end_date DATE,
    CHECK (end_date > start_date)
);

-- 复杂检查约束
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    age INTEGER,
    email TEXT,
    CHECK (age >= 0 AND age <= 150),
    CHECK (email LIKE '%@%.%')
);

-- 命名检查约束
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    price REAL,
    CONSTRAINT chk_price_positive CHECK (price > 0)
);

-- 使用常量默认值
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    status INTEGER DEFAULT 1,
    role TEXT DEFAULT 'user'
);

-- 使用函数默认值
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    date DATE DEFAULT (date('now'))
);

-- 使用表达式默认值
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    price REAL,
    discount REAL DEFAULT 0,
    final_price REAL DEFAULT (price * (1 - discount))
);

-- NOT NULL 约束
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    phone TEXT  -- 可以为NULL
);

-- NOT NULL 与 DEFAULT 结合
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL DEFAULT 'Anonymous'
);

-- TEMPORARY 或 TEMP 关键字
-- 临时表在连接关闭时自动删除

CREATE TEMP TABLE temp_results (
    id INTEGER PRIMARY KEY,
    value TEXT
);

-- 或
CREATE TEMPORARY TABLE temp_results (
    id INTEGER PRIMARY KEY,
    value TEXT
);

-- 临时表只在当前连接可见
-- 存储在 temp 数据库中

-- 临时表特点：
-- 1. 只在当前连接可见
-- 2. 连接关闭时自动删除
-- 3. 存储在临时数据库或内存中
-- 4. 可以创建索引和触发器

-- 使用场景：
-- 1. 复杂查询的中间结果
-- 2. 批量处理的临时存储
-- 3. 会话级别的缓存

-- WITHOUT ROWID 表不使用 ROWID
-- 数据按主键存储在 B-Tree 中

CREATE TABLE users (
    id TEXT PRIMARY KEY,
    name TEXT
) WITHOUT ROWID;

-- 适用场景：
-- 1. 主键是非整数类型
-- 2. 主键较大
-- 3. 需要按主键快速查找

-- WITH ROWID（默认）
CREATE TABLE with_rowid (
    id TEXT PRIMARY KEY,
    name TEXT
);
-- 存储结构：ROWID -> 数据
-- 主键索引：id -> ROWID

-- WITHOUT ROWID
CREATE TABLE without_rowid (
    id TEXT PRIMARY KEY,
    name TEXT
) WITHOUT ROWID;
-- 存储结构：id -> 数据
-- 无额外的主键索引

-- 选择建议：
-- 使用 WITHOUT ROWID 当：
-- 1. 单行数据较小
-- 2. 主键是非整数
-- 3. 主要按主键查询

-- 使用 WITH ROWID 当：
-- 1. 需要使用 rowid 列
-- 2. 单行数据较大
-- 3. 有多个索引

-- SQLite 的 ALTER TABLE 功能有限
-- 支持的操作：
-- 1. 重命名表
-- 2. 添加列
-- 3. 重命名列（3.25.0+）
-- 4. 删除列（3.35.5+）

-- 不支持的操作：
-- 1. 修改列类型
-- 2. 添加/删除约束
-- 3. 修改列默认值

-- RENAME TO
ALTER TABLE users RENAME TO members;

-- 重命名后，相关对象会自动更新：
-- 1. 索引
-- 2. 触发器
-- 3. 视图

-- ADD COLUMN
ALTER TABLE users ADD COLUMN age INTEGER;
ALTER TABLE users ADD COLUMN phone TEXT DEFAULT '';

-- 添加列的限制：
-- 1. 不能添加 PRIMARY KEY
-- 2. 不能添加 UNIQUE（除非是 WITHOUT ROWID 表）
-- 3. 不能添加 NOT NULL 且无默认值
-- 4. 不能添加外键约束

-- 可以添加：
ALTER TABLE users ADD COLUMN nickname TEXT;
ALTER TABLE users ADD COLUMN status INTEGER DEFAULT 1;
ALTER TABLE users ADD COLUMN bio TEXT CHECK(length(bio) <= 500);

-- SQLite 3.25.0+ 支持
ALTER TABLE users RENAME COLUMN name TO full_name;
ALTER TABLE users RENAME COLUMN created_at TO created_date;

-- SQLite 3.35.5+ 支持
ALTER TABLE users DROP COLUMN age;
ALTER TABLE users DROP COLUMN phone;

-- 不能删除的列：
-- 1. 主键列
-- 2. 被外键引用的列
-- 3. 索引中的列（需要先删除索引）
-- 4. UNIQUE 约束中的列
-- 5. CHECK 约束中的列

-- 对于不支持的修改，使用重建表的方法

-- 步骤：
-- 1. 创建新表
-- 2. 复制数据
-- 3. 删除旧表
-- 4. 重命名新表

-- 示例：修改列类型
BEGIN TRANSACTION;

-- 1. 创建新表
CREATE TABLE users_new (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age TEXT,  -- 从 INTEGER 改为 TEXT
    email TEXT UNIQUE
);

-- 2. 复制数据
INSERT INTO users_new (id, name, age, email)
SELECT id, name, CAST(age AS TEXT), email FROM users;

-- 3. 删除旧表
DROP TABLE users;

-- 4. 重命名新表
ALTER TABLE users_new RENAME TO users;

-- 5. 重建索引
CREATE INDEX idx_users_email ON users(email);

COMMIT;

-- 基本语法
DROP TABLE table_name;

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

-- 删除临时表
DROP TABLE temp.temp_results;

-- 注意：
-- 1. 删除表会同时删除相关索引和触发器
-- 2. 不能删除被外键引用的表（除非先删除外键）
-- 3. 删除后数据无法恢复

-- 检查表是否存在
SELECT name FROM sqlite_master WHERE type='table' AND name='users';

-- 条件删除
DROP TABLE IF EXISTS users;

-- 检查外键依赖
SELECT 
    m.name AS table_name,
    p.'from' AS column_name,
    p.'table' AS referenced_table
FROM sqlite_master m
JOIN pragma_foreign_key_list(m.name) p
WHERE p.'table' = 'users';

-- 命令行方式
.schema users

-- SQL方式
SELECT sql FROM sqlite_master WHERE type='table' AND name='users';

-- 使用 PRAGMA
PRAGMA table_info(users);

-- 输出：
-- cid | name | type | notnull | dflt_value | pk
-- ----|------|------|---------|------------|---
-- 0   | id   | INTEGER | 0    | NULL       | 1
-- 1   | name | TEXT | 0       | NULL       | 0

-- 查看所有表
SELECT name FROM sqlite_master WHERE type='table';

-- 查看所有表和视图
SELECT name, type FROM sqlite_master WHERE type IN ('table', 'view');

-- 查看表数量
SELECT COUNT(*) FROM sqlite_master WHERE type='table';

-- 查看特定前缀的表
SELECT name FROM sqlite_master 
WHERE type='table' AND name LIKE 'order_%';

-- PRAGMA table_info
PRAGMA table_info(users);

-- 输出列：
-- cid: 列ID
-- name: 列名
-- type: 数据类型
-- notnull: 是否非空
-- dflt_value: 默认值
-- pk: 是否主键

-- 查看外键
PRAGMA foreign_key_list(users);

-- 查看索引
PRAGMA index_list(users);

-- 查看索引信息
PRAGMA index_info(idx_users_email);

-- 命名约束便于管理
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT,
    name TEXT,
    
    -- 命名唯一约束
    CONSTRAINT uk_users_email UNIQUE (email),
    
    -- 命名检查约束
    CONSTRAINT chk_users_name CHECK (length(name) >= 2),
    
    -- 命名主键
    CONSTRAINT pk_users PRIMARY KEY (id)
);

-- 查看约束
SELECT sql FROM sqlite_master WHERE type='table' AND name='users';

-- ON CONFLICT 子句
-- 用于处理约束冲突

-- INSERT OR REPLACE
INSERT OR REPLACE INTO users (id, name) VALUES (1, 'Alice');

-- INSERT OR IGNORE
INSERT OR IGNORE INTO users (id, name) VALUES (1, 'Alice');

-- INSERT OR ABORT（默认）
INSERT OR ABORT INTO users (id, name) VALUES (1, 'Alice');

-- INSERT OR FAIL
INSERT OR FAIL INTO users (id, name) VALUES (1, 'Alice');

-- INSERT OR ROLLBACK
INSERT OR ROLLBACK INTO users (id, name) VALUES (1, 'Alice');

-- 在表定义中指定冲突处理
CREATE TABLE users (
    id INTEGER PRIMARY KEY ON CONFLICT REPLACE,
    email TEXT UNIQUE ON CONFLICT IGNORE
);

-- 推荐：使用 INTEGER PRIMARY KEY
CREATE TABLE users (
    id INTEGER PRIMARY KEY,  -- 自增，高效
    name TEXT
);

-- 或明确使用 AUTOINCREMENT
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,  -- 保证ID不重用
    name TEXT
);

-- 不推荐：使用 TEXT 或复合主键作为主键
-- 除非有特殊需求
CREATE TABLE users (
    uuid TEXT PRIMARY KEY,  -- 较大，效率较低
    name TEXT
);

-- 为常用查询条件创建索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_status ON users(status);

-- 复合索引顺序
-- 将最常用的列放在前面
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- 覆盖索引
-- 包含查询所需的所有列
CREATE INDEX idx_users_active ON users(status, name, email);

-- 整数类型
id INTEGER PRIMARY KEY
age INTEGER
count INTEGER

-- 文本类型
name TEXT
email TEXT
description TEXT

-- 浮点数
price REAL
rate REAL

-- 布尔值
is_active INTEGER DEFAULT 1  -- 0=false, 1=true

-- 日期时间
created_at TEXT DEFAULT CURRENT_TIMESTAMP

CREATE TABLE [IF NOT EXISTS] [TEMP] table_name (
    column_definition,
    ...,
    table_constraints
) [WITHOUT ROWID];