SQLite数据库操作 #

-- SQLite 数据库特点：
-- 1. 单文件存储 - 整个数据库存储在一个文件中
-- 2. 无服务器 - 不需要单独的服务器进程
-- 3. 零配置 - 无需安装和配置
-- 4. 动态类型 - 列类型灵活

-- 数据库文件扩展名：
-- .db     - 最常用
-- .sqlite - 常用
-- .sqlite3- 常用
-- .db3    - 较少使用

数据库文件结构：
┌────────────────────────────────┐
│         文件头 (100字节)         │
├────────────────────────────────┤
│         页面 1 (B-Tree页)       │
├────────────────────────────────┤
│         页面 2 (B-Tree页)       │
├────────────────────────────────┤
│         页面 3 (溢出页)         │
├────────────────────────────────┤
│              ...               │
└────────────────────────────────┘

-- 页面大小：512 到 65536 字节（默认 4096）

# 方式1：直接创建
sqlite3 myapp.db

# 方式2：创建并执行SQL
sqlite3 myapp.db "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);"

# 方式3：创建内存数据库
sqlite3 :memory:

# 方式4：创建临时文件数据库
sqlite3 ''

-- 打开或创建数据库
.open myapp.db

-- 检查当前数据库
.databases

-- 输出示例：
-- main: /path/to/myapp.db

# Python
import sqlite3

# 创建/打开数据库文件
conn = sqlite3.connect('myapp.db')

# 创建内存数据库
conn = sqlite3.connect(':memory:')

# 创建临时文件数据库
conn = sqlite3.connect('')

// Node.js (better-sqlite3)
const Database = require('better-sqlite3');

// 创建/打开数据库文件
const db = new Database('myapp.db');

// 创建内存数据库
const db = new Database(':memory:');

// Java
import java.sql.Connection;
import java.sql.DriverManager;

// 创建/打开数据库文件
Connection conn = DriverManager.getConnection("jdbc:sqlite:myapp.db");

// 创建内存数据库
Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:");

-- 内存数据库：数据存储在内存中，关闭后数据丢失

-- 命令行方式
sqlite3 :memory:

-- 或在SQLite命令行中
.open :memory:

-- 创建表并插入数据
CREATE TABLE temp_data (id INTEGER PRIMARY KEY, value TEXT);
INSERT INTO temp_data (value) VALUES ('temporary');

-- 查询数据
SELECT * FROM temp_data;

-- 优点：
-- 1. 速度极快 - 无磁盘I/O
-- 2. 安全 - 关闭后数据消失
-- 3. 无需清理 - 无临时文件

-- 缺点：
-- 1. 数据不持久
-- 2. 受内存大小限制
-- 3. 进程结束数据丢失

-- 适用场景：
-- 1. 临时数据处理
-- 2. 测试环境
-- 3. 缓存数据
-- 4. 数据分析中间结果

-- 将内存数据库保存到文件

-- 方法1：使用 .backup 命令
.backup 'backup.db'

-- 方法2：使用 ATTACH 复制
ATTACH DATABASE 'backup.db' AS backup;
CREATE TABLE backup.users AS SELECT * FROM main.users;
DETACH DATABASE backup;

-- SQLite 支持同时操作多个数据库
-- 使用 ATTACH 附加其他数据库

-- 基本语法
ATTACH DATABASE 'filename' AS database_name;

-- 示例
ATTACH DATABASE 'users.db' AS users_db;
ATTACH DATABASE 'orders.db' AS orders_db;
ATTACH DATABASE 'logs.db' AS logs_db;

-- 查看附加的数据库
.databases

-- 输出示例：
-- main: /path/to/main.db
-- users_db: /path/to/users.db
-- orders_db: /path/to/orders.db
-- logs_db: /path/to/logs.db

-- 使用 database_name.table_name 访问表

-- 主数据库（main）
SELECT * FROM main.users;

-- 附加的数据库
SELECT * FROM users_db.users;
SELECT * FROM orders_db.orders;

-- 跨数据库查询
SELECT 
    u.name,
    o.order_date,
    o.total
FROM main.users u
JOIN orders_db.orders o ON u.id = o.user_id;

-- 跨数据库插入
INSERT INTO logs_db.logs (message)
SELECT 'Order created: ' || id FROM orders_db.orders;

-- 创建跨数据库视图
CREATE VIEW order_summary AS
SELECT 
    u.name,
    o.id AS order_id,
    o.total
FROM main.users u
JOIN orders_db.orders o ON u.id = o.user_id;

-- 跨数据库复制表
CREATE TABLE users_db.users_backup AS
SELECT * FROM main.users;

-- 跨数据库事务
BEGIN TRANSACTION;
    INSERT INTO main.users (name) VALUES ('Alice');
    INSERT INTO orders_db.orders (user_id, total) VALUES (last_insert_rowid(), 100);
COMMIT;

-- 使用 DETACH 分离数据库
DETACH DATABASE database_name;

-- 示例
DETACH DATABASE users_db;
DETACH DATABASE orders_db;

-- 注意：main 数据库不能分离
DETACH DATABASE main;  -- 错误！

-- 命令行方式
.databases

-- SQL方式
SELECT 
    name,
    file
FROM pragma_database_list;

-- 输出示例：
-- name    | file
-- --------|------------------
-- main    | /path/to/main.db
-- temp    | 
-- users_db| /path/to/users.db

-- 数据库页面大小
PRAGMA page_size;

-- 数据库页数
PRAGMA page_count;

-- 数据库大小（字节）
SELECT page_size * page_count AS size_bytes FROM pragma_page_count;

-- 数据库版本
PRAGMA schema_version;

-- 用户版本（可用于版本控制）
PRAGMA user_version;

-- 设置用户版本
PRAGMA user_version = 1;

-- 表统计
SELECT 
    name AS table_name,
    (SELECT COUNT(*) FROM pragma_table_info(m.name)) AS column_count
FROM sqlite_master 
WHERE type = 'table';

-- 索引统计
SELECT COUNT(*) AS index_count 
FROM sqlite_master 
WHERE type = 'index' AND sql IS NOT NULL;

-- 触发器统计
SELECT COUNT(*) AS trigger_count 
FROM sqlite_master 
WHERE type = 'trigger';

-- VACUUM：重建数据库文件，回收空间

-- 基本用法
VACUUM;

-- 指定数据库
VACUUM main;

-- VACUUM 的作用：
-- 1. 回收已删除数据占用的空间
-- 2. 重建数据库文件
-- 3. 整理碎片
-- 4. 优化性能

-- 注意：
-- 1. 执行期间会锁定数据库
-- 2. 需要足够的磁盘空间（临时需要2倍大小）
-- 3. 大数据库执行时间较长

-- 自动 VACUUM 模式
PRAGMA auto_vacuum = NONE;     -- 默认，不自动回收
PRAGMA auto_vacuum = FULL;     -- 完全自动回收
PRAGMA auto_vacuum = INCREMENTAL; -- 增量回收

-- 增量 VACUUM
PRAGMA incremental_vacuum(100); -- 回收100页

-- 检查数据库完整性
PRAGMA integrity_check;

-- 完整检查（较慢）
PRAGMA integrity_check(1000000);

-- 快速检查
PRAGMA quick_check;

-- 检查特定表
PRAGMA integrity_check('users');

-- 分析统计信息
ANALYZE;

-- 分析特定表
ANALYZE users;

-- 分析特定索引
ANALYZE idx_users_email;

-- 更新统计信息后，查询优化器可以更好地选择索引

-- 常用 PRAGMA 设置

-- 页面大小（创建数据库时设置）
PRAGMA page_size = 4096;

-- 缓存大小（页数）
PRAGMA cache_size = -2000;  -- 负数表示KB

-- 日志模式
PRAGMA journal_mode = DELETE;   -- 默认
PRAGMA journal_mode = WAL;      -- Write-Ahead Logging（推荐）
PRAGMA journal_mode = MEMORY;   -- 内存日志
PRAGMA journal_mode = OFF;      -- 无日志

-- 同步模式
PRAGMA synchronous = OFF;       -- 不同步（最快，不安全）
PRAGMA synchronous = NORMAL;    -- 正常同步
PRAGMA synchronous = FULL;      -- 完全同步（最安全）

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

-- 临时文件存储
PRAGMA temp_store = MEMORY;     -- 内存
PRAGMA temp_store = FILE;       -- 文件

-- 锁定模式
PRAGMA locking_mode = NORMAL;   -- 正常
PRAGMA locking_mode = EXCLUSIVE; -- 独占

-- 查看各种设置
PRAGMA page_size;
PRAGMA cache_size;
PRAGMA journal_mode;
PRAGMA synchronous;
PRAGMA foreign_keys;
PRAGMA temp_store;
PRAGMA locking_mode;

-- 查看所有编译选项
PRAGMA compile_options;

-- 备份数据库
.backup 'backup.db'

-- 备份到指定数据库
.backup main 'backup.db'

-- 备份附加的数据库
.backup users_db 'users_backup.db'

-- 导出SQL脚本
.dump

-- 导出到文件
.output backup.sql
.dump
.output stdout

-- 导出特定表
.dump users

-- 导出多个表
.dump users orders products

# 从SQL脚本恢复
sqlite3 new.db < backup.sql

# 或在SQLite命令行中
sqlite3 new.db
.read backup.sql

# Python 在线备份
import sqlite3

def backup_database(source_db, dest_db):
    source = sqlite3.connect(source_db)
    dest = sqlite3.connect(dest_db)
    
    # 执行备份
    source.backup(dest)
    
    dest.close()
    source.close()

backup_database('main.db', 'backup.db')

# 设置数据库文件权限
chmod 600 myapp.db    # 只有所有者可读写

# 设置目录权限
chmod 700 /path/to/database/directory

-- SQLite 本身不支持加密
-- 需要使用扩展：
-- 1. SQLCipher - 商业加密扩展
-- 2. SEE - SQLite Encryption Extension
-- 3. wxSQLite3 - 开源加密扩展

-- SQLCipher 示例
PRAGMA key = 'your-encryption-key';
-- 或
PRAGMA key = "x'your-hex-key'";

-- 重新加密
PRAGMA rekey = 'new-encryption-key';

-- 以只读模式打开数据库
-- 命令行
sqlite3 file:myapp.db?mode=ro

-- URI 格式
.open file:myapp.db?mode=ro

-- Python
conn = sqlite3.connect('file:myapp.db?mode=ro', uri=True)

-- 错误：database is locked

-- 解决方案：
-- 1. 使用 WAL 模式
PRAGMA journal_mode = WAL;

-- 2. 增加超时时间
PRAGMA busy_timeout = 5000;  -- 5秒

-- 3. 减少事务时间
BEGIN IMMEDIATE;
-- 快速操作
COMMIT;

-- 检查损坏
PRAGMA integrity_check;

-- 如果损坏，尝试恢复
-- 1. 导出数据
.dump

-- 2. 创建新数据库
-- 3. 导入数据
.read backup.sql

-- 检查数据库大小
SELECT page_size * page_count / 1024 / 1024 AS size_mb 
FROM pragma_page_count;

-- 回收空间
VACUUM;

-- 删除不需要的数据后执行VACUUM
DELETE FROM logs WHERE created_at < date('now', '-30 days');
VACUUM;