2024年重识 SQLite，简约不简单_sqlitegraph，2024年最新全网独家首发

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上物联网嵌入式知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、电子书籍、讲解视频，并且后续会持续更新

如果你需要这些资料，可以戳这里获取

{ "city": "Berlin", "foundation\_year": 1237, "timezone": "UTC+1" },
{ "city": "Helsinki", "foundation\_year": 1548, "timezone": "UTC+2" },
{ "city": "Monaco", "foundation\_year": 1215, "timezone": "UTC+1" },
{ "city": "Moscow", "foundation\_year": 1147, "timezone": "UTC+3" },
{ "city": "Reykjavik", "foundation\_year": 874, "timezone": "UTC" },
{ "city": "Sarajevo", "foundation\_year": 1461, "timezone": "UTC+1" },
{ "city": "Stockholm", "foundation\_year": 1252, "timezone": "UTC+1" },
{ "city": "Tallinn", "foundation\_year": 1219, "timezone": "UTC+2" },
{ "city": "Zagreb", "foundation\_year": 1094, "timezone": "UTC+1" }

]

如果你喜欢使用 BI 工具而不是控制台，常见的数据探索工具都支持 SQLite，例如 Metabase、Redash 以及 Superset 等。


### 原生 JSON


SQLite 可以非常方便地分析和转换 JSON 数据，用户可以直接从文件中查询数据，也可以将数据导入表中然后进行查询：

select
json_extract(value, ‘$.iso.cod{e}^{\prime })ascode,jso{n}_{e}xtract(value{,}^{\prime }$.iso.number’) as num,
json_extract(value, ‘$.nam{e}^{\prime })asname,jso{n}_{e}xtract(value{,}^{\prime }$.units.major.name’) as unit
from
json_each(readfile(‘currency.sample.json’));
┌──────┬─────┬─────────────────┬──────────┐
│ code │ num │ name │ unit │
├──────┼─────┼─────────────────┼──────────┤
│ ARS │ 032 │ Argentine peso | peso │
│ CHF │ 756 │ Swiss Franc │ franc │
│ EUR │ 978 │ Euro │ euro │
│ GBP │ 826 │ British Pound │ pound │
│ INR │ 356 │ Indian Rupee │ rupee │
│ JPY │ 392 │ Japanese yen │ yen │
│ MAD │ 504 │ Moroccan Dirham │ dirham │
│ RUR │ 643 │ Russian Rouble │ rouble │
│ SOS │ 706 │ Somali Shilling │ shilling │
│ USD │ 840 │ US Dollar │ dollar │
└──────┴─────┴─────────────────┴──────────┘

无论 JSON 对象包含多少层嵌套，SQLite 都可以获取其中的数据：

select
json_extract(value, ‘$.i{d}^{\prime })asid,jso{n}_{e}xtract(value{,}^{\prime }$.name’) as name
from
json_tree(readfile(‘industry.sample.json’))
where
path like ‘$[%].industries’;
┌────────┬──────────────────────┐
│ id │ name │
├────────┼──────────────────────┤
│ 7.538 │ Internet provider │
│ 7.539 │ IT consulting │
│ 7.540 │ Software development │
│ 9.399 │ Mobile communication │
│ 9.400 │ Fixed communication │
│ 9.401 │ Fiber-optics │
│ 43.641 │ Audit │
│ 43.646 │ Insurance │
│ 43.647 │ Bank │
└────────┴──────────────────────┘

### CTE 与集合运算


SQLite 支持[通用表表达式](https://bbs.csdn.net/topics/618679757)（Common Table Expression）和连接查询。对于具有层级关系的数据（例如组织结构等），可以通过 WITH RECURSIVE 很方便地进行遍历。

with recursive tmp(id, name, level) as (
select id, name, 1 as level
from area
where parent_id is null
union all
select
area.id,
tmp.name || ', ’ || area.name as name,
tmp.level + 1 as level
from area
join tmp on area.parent_id = tmp.id
)
select * from tmp;
┌──────┬──────────────────────────┬───────┐
│ id │ name │ level │
├──────┼──────────────────────────┼───────┤
│ 93 │ US │ 1 │
│ 768 │ US, Washington DC │ 2 │
│ 1833 │ US, Washington │ 2 │
│ 2987 │ US, Washington, Bellevue │ 3 │
│ 3021 │ US, Washington, Everett │ 3 │
│ 3039 │ US, Washington, Kent │ 3 │
│ … │ … │ … │
└──────┴──────────────────────────┴───────┘

SQLite 还提供了 UNION、INTERSECT 以及 EXCEPT 集合运算符：

select employer_id
from employer_area
where area_id = 1
except
select employer_id
from employer_area
where area_id = 2;

基于其他字段的[生成列](https://bbs.csdn.net/topics/618679757)也不在话下：

alter table vacancy
add column salary_net integer as (
case when salary_gross = true then
round(salary_from/1.04)
else
salary_from
end
);

生成列可以像其他普通字段一样查询：

select
substr(name, 1, 40) as name,
salary_net
from vacancy
where
salary_currency = ‘JPY’
and salary_net is not null
limit 10;

### 统计函数


通过加载 stats 插件，SQLite 支持以下描述性统计：均值、中位数、百分位、标准差等。

.load sqlite3-stats

select
count(*) as book_count,
cast(avg(num_pages) as integer) as mean,
cast(median(num_pages) as integer) as median,
mode(num_pages) as mode,
percentile_90(num_pages) as p90,
percentile_95(num_pages) as p95,
percentile_99(num_pages) as p99
from books;
┌────────────┬──────┬────────┬──────┬─────┬─────┬──────┐
│ book_count │ mean │ median │ mode │ p90 │ p95 │ p99 │
├────────────┼──────┼────────┼──────┼─────┼─────┼──────┤
│ 1483 │ 349 │ 295 │ 256 │ 640 │ 817 │ 1199 │
└────────────┴──────┴────────┴──────┴─────┴─────┴──────┘

SQLite 比其他数据库管理系统提供的函数更少一些，不过可以通过扩展插件的方式获取额外的支持。[这个项目](https://bbs.csdn.net/topics/618679757)按照不同的领域编译了一些常用的插件。


以下示例在控制台中描绘了一个数据分布图：

with slots as (
select
num_pages/100 as slot,
count(*) as book_count
from books
group by slot
),
max as (
select max(book_count) as value
from slots
)
select
slot,
book_count,
printf(‘%.’ || (book_count * 30 / max.value) || ‘c’, ‘*’) as bar
from slots, max
order by slot;
┌──────┬────────────┬────────────────────────────────┐
│ slot │ book_count │ bar │
├──────┼────────────┼────────────────────────────────┤
│ 0 │ 116 │ ********* │
│ 1 │ 254 │ ******************** │
│ 2 │ 376 │ ****************************** │
│ 3 │ 285 │ ********************** │
│ 4 │ 184 │ ************** │
│ 5 │ 90 │ ******* │
│ 6 │ 54 │ **** │
│ 7 │ 41 │ *** │
│ 8 │ 31 │ ** │
│ 9 │ 15 │ * │
│ 10 │ 11 │ * │
│ 11 │ 12 │ * │
│ 12 │ 2 │ * │
└──────┴────────────┴────────────────────────────────┘

### 性能


SQLite 可以支持数以亿计的数据行，在个人电脑上的普通 INSERT 语句也可以达到 10 万条/秒以上。如果使用虚拟表连接 CSV 文件，插入性能会更好：

.load sqlite3-vsv

create virtual table temp.blocks_csv using vsv(
filename=“ipblocks.csv”,
schema=“create table x(network text, geoname_id integer, registered_country_geoname_id integer, represented_country_geoname_id integer, is_anonymous_proxy integer, is_satellite_provider integer, postal_code text, latitude real, longitude real, accuracy_radius integer)”,
columns=10,
header=on,
nulls=on
);
.timer on
insert into blocks
select * from blocks_csv;

Run Time: real 5.176 user 4.716420 sys 0.403866
select count(*) from blocks;
3386629

Run Time: real 0.095 user 0.021972 sys 0.063716

很多人认为 SQLite 不适合作为 Web 应用后台数据库，因为它不支持并发访问。实际上这是一个谣传，在write-ahead log 模式下，SQLite 提供了并发读取。虽然只能单个进程写入，但是很多情况下已经足够了。


SQLite 非常适合小型网站和应用程序。sqlite.org 就是使用 SQLite 作为数据库，在不需要进行优化的情况下（每个页面大概包含 200 个查询请求），它可以处理每个月 70 万的访问量，同时性能超过 95% 的网站。


### 文档、图形以及全文搜索


SQLite 支持部分索引和表达式索引（函数索引），我们可以基于计算列创建索引，甚至[将 SQLite 作为文档数据库](https://bbs.csdn.net/topics/618679757)使用：



![img](https://img-blog.csdnimg.cn/img_convert/d3d4bc485a642746dbad0c3735a14274.png)
![img](https://img-blog.csdnimg.cn/img_convert/fa245cb38b3981dc8d7a3c53f1ca3553.png)

**既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上物联网嵌入式知识点，真正体系化！**

**由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、电子书籍、讲解视频，并且后续会持续更新**

**[如果你需要这些资料，可以戳这里获取](https://bbs.csdn.net/topics/618679757)**

.(img-5APnlv7t-1715672610777)]
[外链图片转存中...(img-fSgAbHve-1715672610779)]

**既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上物联网嵌入式知识点，真正体系化！**

**由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、电子书籍、讲解视频，并且后续会持续更新**

**[如果你需要这些资料，可以戳这里获取](https://bbs.csdn.net/topics/618679757)**