SQLite 也可以做复杂的查询，我不信--SQLite 五脏俱全系列 (4)

sqlite> CREATE TABLE test (id INT, val INT);  

insert into test (id,val) values ('c','a');


CREATE TABLE test1 (id INT, val INT) strict;  

insert into test1 (id,val) values ('c','a');
insert into test1 (id,val) values (1,2);sqlite> sqlite> sqlite> sqlite> sqlite> sqlite> sqlite> Runtime error: cannot store TEXT value in INT column test1.id (19)

sqlite> 
sqlite> select * from test;
c|a
sqlite> select * from test1;
1|2
sqlite> 

sqlite> INSERT INTO users (username, age, balance) VALUES ('zhangsan', 25, 1000.50);
sqlite> INSERT INTO users (username, age, balance) VALUES ('rich_man', 45, 9999999.99);
sqlite> INSERT INTO users (username, age, balance) VALUES ('baby_lee', 0, 50.0);
sqlite> INSERT INTO users (username, age) VALUES ('minimalist', 30);
sqlite> select * from users;
1|zhangsan|25|1000.5|2026-03-03 03:40:52
2|rich_man|45|9999999.99|2026-03-03 03:41:06
3|baby_lee|0|50.0|2026-03-03 03:41:19
4|minimalist|30|0.0|2026-03-03 03:41:28


sqlite> INSERT INTO users (username, age, balance) 
VALUES ('zhangsan', 26, 1500.0)
ON CONFLICT(username) DO UPDATE SET 
    balance = excluded.balance,
    age = excluded.age;

sqlite> SELECT 
    CASE 
        WHEN age < 18 THEN '未成年'
        WHEN age BETWEEN 18 AND 35 THEN '青年'
        WHEN age BETWEEN 36 AND 60 THEN '中年'
        ELSE '老年'
    END AS age_group,
    COUNT(*) AS member_count,
    ROUND(AVG(balance), 2) AS avg_balance,
    SUM(balance) AS total_assets
FROM users
GROUP BY age_group
ORDER BY total_assets DESC;  
中年|1|9999999.99|9999999.99
青年|2|750.0|1500.0
未成年|1|50.0|50.0
Run Time: real 0.001 user 0.000172 sys 0.000114
sqlite> 
sqlite> 
sqlite> 
sqlite> 
sqlite> SELECT 
    username, 
    balance,
    -- 计算余额在全局的排名（1为最高）
    RANK() OVER (ORDER BY balance DESC) as balance_rank,
    -- 计算余额占总额的百分比
    ROUND(balance * 100.0 / SUM(balance) OVER(), 2) || '%' as wealth    _percent
FROM users;  
rich_man|9999999.99|1|99.98%
zhangsan|1500.0|2|0.01%
baby_lee|50.0|3|0.0%
minimalist|0.0|4|0.0%
Run Time: real 0.001 user 0.000523 sys 0.000000
sqlite> 
sqlite> 
sqlite> 
sqlite> SELECT user_id, username, age, balance
FROM users
WHERE username LIKE '%an%'  -- 包含 "an" 的用户名
  AND balance > 0
ORDER BY balance DESC, created_at ASC
LIMIT 2 OFFSET 0;      
2|rich_man|45|9999999.99
1|zhangsan|26|1500.0
Run Time: real 0.001 user 0.000204 sys 0.000000

sqlite> 
sqlite> 
sqlite> 
sqlite> SELECT 
    a.username AS user_a, 
    b.username AS user_b, 
    ABS(a.balance - b.balance) AS gap
FROM users a
JOIN users b ON a.user_id != b.user_id
ORDER BY gap ASC
LIMIT 1;   
baby_lee|minimalist|50.0
Run Time: real 0.000 user 0.000074 sys 0.000000
sqlite> 
sqlite> 
sqlite> 
sqlite> INSERT INTO users (username, age, balance)
WITH RECURSIVE cnt(x) AS (
    SELECT 1 
    UNION ALL 
    SELECT x + 1 FROM cnt WHERE x < 1000）
    SELECT 
    'robot_' || x, 
    (ABS(RANDOM()) % 80) + 1, -- 随机 1-80 岁
    ROUND(ABS(RANDOM()) % 10000 / 7.0, 2) -- 随机余额
FROM cnt;   
Run Time: real 0.003 user 0.000000 sys 0.002220
sqlite> 
sqlite> 
sqlite>