-- 基本语法
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL,
    age INTEGER DEFAULT 0,
    balance REAL DEFAULT 0.0,
    is_active INTEGER DEFAULT 1,
    created_at TEXT DEFAULT (datetime('now', 'localtime')),
    updated_at TEXT DEFAULT (datetime('now', 'localtime'))
);

-- CREATE TABLE IF NOT EXISTS（幂等操作）
CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL CHECK(price >= 0),
    stock INTEGER DEFAULT 0
);

-- 从查询结果创建表
CREATE TABLE active_users AS
SELECT * FROM users WHERE is_active = 1;

-- 仅复制结构（无数据）
CREATE TABLE users_backup AS SELECT * FROM users WHERE 0;

CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    order_no TEXT NOT NULL UNIQUE,
    total_amount REAL NOT NULL DEFAULT 0,
    status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'paid', 'shipped', 'completed', 'cancelled')),
    note TEXT,
    created_at TEXT DEFAULT (datetime('now', 'localtime')),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE order_items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL CHECK(quantity > 0),
    unit_price REAL NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

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

-- 添加列（SQLite 3.35.0+）
ALTER TABLE users ADD COLUMN phone TEXT;
ALTER TABLE users ADD COLUMN avatar_url TEXT DEFAULT '/image/default.png';

-- 重命名列（SQLite 3.25.0+）
ALTER TABLE users RENAME COLUMN username TO name;

-- 删除表
DROP TABLE users;
DROP TABLE IF EXISTS users;  -- 幂等操作

-- 删除视图
DROP VIEW IF EXISTS active_user_view;

-- 单列索引
CREATE INDEX idx_users_email ON users(email);

-- 复合索引
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- 唯一索引
CREATE INDEX idx_users_username ON users(username);

-- 条件索引（部分索引）
CREATE INDEX idx_active_users ON users(email) WHERE is_active = 1;

-- 删除索引
DROP INDEX IF EXISTS idx_users_email;

-- 基本插入
INSERT INTO users (username, email, age)
VALUES ('张三', '[email protected]', 25);

-- 批量插入
INSERT INTO users (username, email, age) VALUES
    ('李四', '[email protected]', 30),
    ('王五', '[email protected]', 28),
    ('赵六', '[email protected]', 35),
    ('孙七', '[email protected]', 22);

-- INSERT OR IGNORE（忽略冲突）
INSERT OR IGNORE INTO users (username, email, age)
VALUES ('张三', '[email protected]', 25);

-- INSERT OR REPLACE（冲突时替换整行）
INSERT OR REPLACE INTO users (id, username, email, age)
VALUES (1, '张三', '[email protected]', 26);

-- INSERT ... ON CONFLICT（SQLite 3.24.0+）
INSERT INTO users (username, email, age)
VALUES ('张三', '[email protected]', 25)
ON CONFLICT(username) DO UPDATE SET
    email = excluded.email,
    age = excluded.age;

INSERT INTO users (username, email, age)
VALUES ('新用户', '[email protected]', 20)
ON CONFLICT DO NOTHING;

-- 从查询结果插入
INSERT INTO active_users (id, username, email)
SELECT id, username, email FROM users WHERE is_active = 1;

-- 基本更新
UPDATE users SET age = 26 WHERE id = 1;

-- 多列更新
UPDATE users SET
    age = age + 1,
    updated_at = datetime('now', 'localtime')
WHERE is_active = 1;

-- 带条件的更新
UPDATE orders SET status = 'completed'
WHERE status = 'shipped' AND created_at < '2025-01-01';

-- 使用子查询更新
UPDATE users SET balance = (
    SELECT COALESCE(SUM(total_amount), 0)
    FROM orders
    WHERE orders.user_id = users.id AND orders.status = 'completed'
);

-- 限制更新行数（通过子查询）
UPDATE users SET is_active = 0
WHERE id IN (
    SELECT id FROM users ORDER BY id LIMIT 10
);

-- 基本删除
DELETE FROM users WHERE id = 5;

-- 带条件删除
DELETE FROM orders WHERE status = 'cancelled' AND created_at < '2024-01-01';

-- 删除所有数据（比 DELETE 快）
DELETE FROM users;
-- 或
-- DELETE FROM users WHERE 1=1;

-- 使用子查询删除
DELETE FROM order_items WHERE order_id IN (
    SELECT id FROM orders WHERE status = 'cancelled'
);

-- 限制删除行数（通过 rowid）
DELETE FROM users WHERE rowid IN (
    SELECT rowid FROM users ORDER BY rowid LIMIT 100
);

-- 查询所有列
SELECT * FROM users;

-- 查询指定列
SELECT id, username, email FROM users;

-- 别名
SELECT id AS 用户ID, username AS 用户名 FROM users;

-- 去重
SELECT DISTINCT age FROM users;

-- 限制行数
SELECT * FROM users LIMIT 10;

-- 分页
SELECT * FROM users LIMIT 10 OFFSET 20;
-- 或使用 WHERE + 游标
SELECT * FROM users WHERE id > 100 LIMIT 10;

-- 比较运算符
SELECT * FROM users WHERE age = 25;
SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE age BETWEEN 18 AND 30;
SELECT * FROM users WHERE age IN (18, 25, 30);

-- 字符串匹配
SELECT * FROM users WHERE username LIKE '张%';       -- 以张开头
SELECT * FROM users WHERE username LIKE '%三%';       -- 包含三
SELECT * FROM users WHERE email LIKE '%@gmail.com';   -- 以 @gmail.com 结尾

-- SQLite 支持的 LIKE 通配符：
-- % 匹配任意字符序列
-- _ 匹配单个字符

-- NULL 检查
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

-- 逻辑运算符
SELECT * FROM users WHERE age > 18 AND is_active = 1;
SELECT * FROM users WHERE age < 18 OR age > 60;
SELECT * FROM users WHERE NOT is_active = 1;
SELECT * FROM users WHERE age NOT IN (18, 25);

-- GLOB（区分大小写的 shell 通配符匹配）
SELECT * FROM users WHERE email GLOB '*@gmail.com';

-- 单列排序
SELECT * FROM users ORDER BY age ASC;       -- 升序（默认）
SELECT * FROM users ORDER BY age DESC;      -- 降序

-- 多列排序
SELECT * FROM users ORDER BY age DESC, username ASC;

-- NULL 排序行为
-- NULL 在 SQLite 中被视为比其他值更小
SELECT * FROM users ORDER BY phone;         -- NULL 排在最前面
SELECT * FROM users ORDER BY phone DESC;    -- NULL 排在最后面

-- 使用表达式排序
SELECT * FROM users ORDER BY length(username);

-- 使用 CASE 排序
SELECT * FROM users ORDER BY
    CASE status
        WHEN 'active' THEN 1
        WHEN 'inactive' THEN 2
        ELSE 3
    END;

-- 常用聚合函数
SELECT
    COUNT(*) AS total,
    COUNT(DISTINCT age) AS distinct_ages,
    AVG(age) AS avg_age,
    SUM(balance) AS total_balance,
    MIN(age) AS youngest,
    MAX(age) AS oldest
FROM users;

-- GROUP BY
SELECT age, COUNT(*) AS count
FROM users
GROUP BY age;

-- 带 HAVING 过滤
SELECT age, COUNT(*) AS count
FROM users
GROUP BY age
HAVING count > 1
ORDER BY count DESC;

-- 多列分组
SELECT age, is_active, COUNT(*) AS count
FROM users
GROUP BY age, is_active;

-- INNER JOIN（内连接，只返回匹配的行）
SELECT u.username, o.order_no, o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN（左连接，返回左表所有行）
SELECT u.username, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- 自连接
SELECT a.username AS user1, b.username AS user2
FROM users a
INNER JOIN users b ON a.age = b.age AND a.id < b.id;

-- 多表连接
SELECT u.username, o.order_no, p.name AS product_name, oi.quantity
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

-- 创建表演示类型亲和性
CREATE TABLE type_demo (
    a TEXT,          -- TEXT 亲和性
    b NUMERIC,       -- NUMERIC 亲和性
    c INTEGER,       -- INTEGER 亲和性
    d REAL,          -- REAL 亲和性
    e                -- BLOB 亲和性
);

-- 插入不同类型的数据
INSERT INTO type_demo VALUES (42, 42, 42, 42, 42);
INSERT INTO type_demo VALUES ('3.14', '3.14', '3.14', '3.14', '3.14');
INSERT INTO type_demo VALUES ('hello', 'hello', 'hello', 'hello', 'hello');

-- 查询实际存储的类型
SELECT typeof(a), typeof(b), typeof(c), typeof(d), typeof(e)
FROM type_demo;
-- text|integer|integer|real|integer
-- text|real|integer|real|text
-- text|text|text|text|text

CREATE TABLE demo (
    id INTEGER PRIMARY KEY,
    val TEXT
);
INSERT INTO demo VALUES (1, '123');

-- typeof(val) 返回 'text'
-- 但如果使用 INSERT OR REPLACE:
INSERT OR REPLACE INTO demo VALUES (1, 456);
-- typeof(val) 返回 'integer'！

-- 算术运算符
SELECT 10 + 3;    -- 13
SELECT 10 - 3;    -- 7
SELECT 10 * 3;    -- 30
SELECT 10 / 3;    -- 3（整数除法）
SELECT 10.0 / 3;  -- 3.33333333333333
SELECT 10 % 3;    -- 1（取模）

-- 字符串运算
SELECT 'Hello' || ' ' || 'World';  -- Hello World
SELECT length('Hello');              -- 5
SELECT upper('hello');               -- HELLO
SELECT lower('HELLO');               -- hello
SELECT substr('Hello World', 1, 5); -- Hello
SELECT replace('Hello', 'l', 'L');  -- HeLLo
SELECT trim('  Hello  ');            -- Hello
SELECT ltrim('  Hello');             -- Hello
SELECT rtrim('Hello  ');             -- Hello
SELECT instr('Hello World', 'World');-- 7（位置）
SELECT printf('%s is %d years old', '张三', 25); -- 张三 is 25 years old

-- 条件表达式
SELECT
    CASE
        WHEN age < 18 THEN '未成年'
        WHEN age < 60 THEN '成年'
        ELSE '老年'
    END AS age_group
FROM users;

-- COALESCE（返回第一个非 NULL 值）
SELECT COALESCE(phone, email, '无联系方式') AS contact FROM users;

-- IFNULL
SELECT IFNULL(phone, '未填写') AS phone FROM users;

-- NULLIF（相等时返回 NULL）
SELECT NULLIF(1, 1);  -- NULL
SELECT NULLIF(1, 2);  -- 1

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    created_at TEXT DEFAULT (datetime('now', 'localtime'))
);

-- 注册（防止重复）
INSERT INTO users (username, email, password_hash)
VALUES ('zhangsan', '[email protected]', 'hashed_password_here');

-- 创建订单
INSERT INTO orders (user_id, order_no, total_amount)
VALUES (1, 'ORD20250101001', 299.99);

-- 添加商品
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (last_insert_rowid(), 1, 2, 149.99);

-- 更新状态
UPDATE orders SET status = 'paid' WHERE order_no = 'ORD20250101001';