持久化数据库与 DML

7.1 持久化数据库文件

DuckDB 支持将数据库持久化到单个 .duckdb 文件中（类似 SQLite 的 .sqlite 文件）。DuckDB 的文件格式与 SQLite 不同，是自研的列式存储格式，不能互相使用。

SQL-- CLI：打开或创建持久化数据库
duckdb analytics.duckdb

-- 连接到数据库后，所有操作自动持久化
CREATE TABLE users (id INTEGER PRIMARY KEY, name VARCHAR);
INSERT INTO users VALUES (1, 'Alice');
-- 关闭后重新打开，数据仍然存在

-- 查看数据库文件版本信息
SELECT * FROM duckdb_databases();
SELECT * FROM duckdb_tables();

7.2 DDL：创建表与数据类型

SQLCREATE TABLE orders (
    order_id    BIGINT PRIMARY KEY,
    user_id     INTEGER NOT NULL,
    product     VARCHAR(200) NOT NULL,
    amount      DECIMAL(10, 2) CHECK (amount > 0),
    status      VARCHAR DEFAULT 'pending',
    tags        VARCHAR[],               -- 数组列
    metadata    JSON,                    -- JSON 列
    created_at  TIMESTAMPTZ DEFAULT now()
);

-- CREATE TABLE AS（从查询结果创建表）
CREATE TABLE orders_2024 AS
    SELECT * FROM orders WHERE year(created_at) = 2024;

-- 从文件直接创建表
CREATE TABLE raw_sales AS SELECT * FROM 'sales.parquet';

-- 修改表结构
ALTER TABLE orders ADD COLUMN region VARCHAR;
ALTER TABLE orders DROP COLUMN tags;
ALTER TABLE orders RENAME COLUMN product TO product_name;

7.3 DML：增删改查

SQL-- INSERT 单行
INSERT INTO orders (order_id, user_id, product, amount)
VALUES (1001, 42, 'MacBook Pro', 15999.00);

-- INSERT 批量（性能更好）
INSERT INTO orders (order_id, user_id, product, amount)
VALUES
    (1002, 43, 'iPhone 16', 8999),
    (1003, 44, 'AirPods Pro', 1999);

-- INSERT FROM SELECT（从查询结果插入）
INSERT INTO orders_archive
SELECT * FROM orders WHERE year(created_at) < 2023;

-- INSERT OR REPLACE（冲突时替换）
INSERT OR REPLACE INTO orders VALUES (1001, 42, 'MacBook Air', 12999);

-- UPDATE
UPDATE orders
SET status = 'shipped', region = '华东'
WHERE order_id = 1001;

-- DELETE
DELETE FROM orders
WHERE status = 'cancelled'
  AND created_at < current_date() - INTERVAL '30 days';

-- UPSERT（INSERT ON CONFLICT DO UPDATE）
INSERT INTO user_stats (user_id, total_orders)
VALUES (42, 1)
ON CONFLICT (user_id) DO UPDATE
    SET total_orders = total_orders + EXCLUDED.total_orders;

7.4 事务支持

SQL-- 显式事务
BEGIN;

UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;

COMMIT;   -- 提交事务
-- 或
ROLLBACK; -- 回滚事务

-- Savepoint（部分回滚）
BEGIN;
INSERT INTO orders VALUES (...);
SAVEPOINT sp1;
UPDATE inventory SET stock = stock - 1;
ROLLBACK TO sp1;  -- 只回滚到 sp1，INSERT 保留
COMMIT;

7.5 序列与自增

SQL-- 创建序列
CREATE SEQUENCE order_seq START 1000 INCREMENT 1;

-- 使用序列
INSERT INTO orders (order_id, product)
VALUES (nextval('order_seq'), 'iPhone');

-- SEQUENCE 自增类型（简写）
CREATE TABLE products (
    id          INTEGER DEFAULT nextval('seq_products_id'),
    name        VARCHAR
);

-- DuckDB 1.0+ 支持 BIGINT AUTO_INCREMENT
CREATE TABLE events (
    id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name   VARCHAR
);

7.6 视图（VIEW）

SQL-- 创建视图
CREATE VIEW active_orders AS
    SELECT * FROM orders WHERE status NOT IN ('cancelled', 'refunded');

-- 查询视图（和表一样）
SELECT COUNT(*) FROM active_orders;

-- 删除视图
DROP VIEW active_orders;

-- 视图直接包装文件（无需先建表！）
CREATE VIEW raw_logs AS
    SELECT * FROM read_csv_auto('logs/*.csv');

-- 这样就可以用表名方式查询文件
SELECT date_trunc('hour', timestamp), COUNT(*)
FROM raw_logs
GROUP BY 1;

7.7 宏 MACRO（存储过程替代品）

DuckDB 使用 MACRO 实现可复用的 SQL 代码，比存储过程更简洁，分为标量宏和表宏：

SQL-- 标量宏：接受参数返回标量值
CREATE MACRO clamp(x, lo, hi) AS
    CASE
        WHEN x < lo THEN lo
        WHEN x > hi THEN hi
        ELSE x
    END;

SELECT clamp(150, 0, 100);   -- 返回 100
SELECT clamp(-5, 0, 100);   -- 返回 0

-- 表宏：接受参数返回表结果
CREATE MACRO get_top_products(n := 10) AS TABLE
    SELECT product, SUM(amount) AS revenue
    FROM orders
    GROUP BY product
    ORDER BY revenue DESC
    LIMIT n;

-- 使用表宏
SELECT * FROM get_top_products();       -- 默认 Top 10
SELECT * FROM get_top_products(n := 5); -- 自定义 Top 5

7.8 ATTACH 多数据库

DuckDB 支持同时连接多个数据库文件，甚至可以跨库 JOIN！

SQL-- 附加其他 DuckDB 文件
ATTACH 'sales_2023.duckdb' AS db2023;
ATTACH 'sales_2024.duckdb' AS db2024 (READ_ONLY);

-- 跨库查询（db名.表名）
SELECT * FROM db2023.orders
UNION ALL
SELECT * FROM db2024.orders;

-- 查看已附加的数据库
SHOW DATABASES;

-- 切换默认数据库
USE db2024;

-- 分离数据库
DETACH db2023;

-- 附加 SQLite 数据库（跨库 JOIN！）
INSTALL sqlite; LOAD sqlite;
ATTACH 'legacy.sqlite' AS sqlite_db (TYPE SQLITE);
SELECT d.*, s.legacy_data
FROM duckdb_table d
JOIN sqlite_db.old_table s ON d.id = s.id;