重识 SQLite,简约不简单

sqlite

🍺业精于勤,荒于嬉;行成于思,毁于随。——韩愈

大家好!我是只谈技术不剪发的 Tony 老师。

如果问你哪个数据库产品是世界上使用最多的数据库,你认为是 Oracle、MySQL 还是 Microsoft SQL Server?

以上都不是,世界上安装使用最多的数据库是 SQLite。没错,就是这个小巧的嵌入式数据库引擎。所有的手机、电脑、浏览器以及无数的应用程序都内置了 SQLite 数据库,PHP 和 Python 语言也内置的 SQLite 支持,预计正在使用的 SQLite 数据库达到了一万亿(1012)以上。

无论对于开发/测试人员、数据分析师/科学家、IT 运维人员还是产品经理,SQLite 都是一个非常有用的工具。本文就带大家回顾一下 SQLite 提供的一些实用功能。如果觉得文章有用,欢迎评论📝、点赞👍、推荐🎁

命令行工具

SQLite 提供了一个非常方便的数据库控制台,也就是 Windows 系统上的 sqlite3.exe 或者 Linux / macOS 系统上的 sqlite3。对于数据分析师来说,它比 Excel 强大,但是比 Python pandas 简单。用户可以通过命令导入 CSV 文件,导入时会自动创建相应的数据表:

> .import --csv city.csv city
> select count(*) from city;
1117

SQLite 命令行工具支持各种 SQL 语句,同时以 ASCII 风格显示查询结果:

select
  century || ' century' as dates,
  count(*) as city_count
from history
group by century
order by century desc;
┌────────────┬────────────┐
│   dates    │ city_count │
├────────────┼────────────┤
│ 21 century │ 1          │
│ 20 century │ 263        │
│ 19 century │ 189        │
│ 18 century │ 191        │
│ 17 century │ 137        │
│ ......        │
└────────────┴────────────┘

查询结果可以被导出到各种 SQL 命令、CSV、JSON、Markdown 以及 HTML 格式的文件。例如:

.mode json
.output city.json
select city, foundation_year, timezone from city limit 10;
.shell cat city.json
[
    { "city": "Amsterdam", "foundation_year": 1300, "timezone": "UTC+1" },
    { "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.code') as code,
  json_extract(value, '$.iso.number') as num,
  json_extract(value, '$.name') as name,
  json_extract(value, '$.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, '$.id') as id,
  json_extract(value, '$.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.400Fixed communication  │
│ 9.401  │ Fiber-optics         │
│ 43.641 │ Audit                │
│ 43.646 │ Insurance            │
│ 43.647 │ Bank                 │
└────────┴──────────────────────┘

CTE 与集合运算

SQLite 支持通用表表达式(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;

基于其他字段的生成列也不在话下:

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  │
├────────────┼──────┼────────┼──────┼─────┼─────┼──────┤
│ 14833492952566408171199 │
└────────────┴──────┴────────┴──────┴─────┴─────┴──────┘

SQLite 比其他数据库管理系统提供的函数更少一些,不过可以通过扩展插件的方式获取额外的支持。这个项目按照不同的领域编译了一些常用的插件。

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

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               │
├──────┼────────────┼────────────────────────────────┤
│ 0116*********                      │
│ 1254********************           │
│ 2376****************************** │
│ 3285**********************         │
│ 4184**************                 │
│ 590*******                        │
│ 654****                           │
│ 741***                            │
│ 831**                             │
│ 915*                              │
│ 1011*                              │
│ 1112*                              │
│ 122*                              │
└──────┴────────────┴────────────────────────────────┘

性能

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 作为文档数据库使用:

create table currency(
  body text,
  code text as (json_extract(body, '$.code')),
  name text as (json_extract(body, '$.name'))
);

create index currency_code_idx on currency(code);

insert into currency
select value
from json_each(readfile('currency.sample.json'));
explain query plan
select name from currency where code = 'EUR';

QUERY PLAN
`--SEARCH TABLE currency USING INDEX currency_code_idx (code=?)

有了 WITH RECURSIVE 查询,SQLite 也可以作为一个图形数据库使用,或者使用这个 simple-graph(Python 模块)。

SQLite 提供了内置的全文搜索功能:

create virtual table books_fts
using fts5(title, author, publisher);

insert into books_fts
select title, author, publisher from books;

select
  author,
  substr(title, 1, 30) as title,
  substr(publisher, 1, 10) as publisher
from books_fts
where books_fts match 'ann'
limit 5;
┌─────────────────────┬────────────────────────────────┬────────────┐
│       author        │             title              │ publisher  │
├─────────────────────┼────────────────────────────────┼────────────┤
│ Ruby Ann Boxcar     │ Ruby Ann's Down Home Trailer P │ Citadel    │
│ Ruby Ann Boxcar     │ Ruby Ann's Down Home Trailer P │ Citadel    │
│ Lynne Ann DeSpelder │ The Last Dance: Encountering D │ McGraw-Hil │
│ Daniel Defoe        │ Robinson Crusoe                │ Ann Arbor  │
│ Ann Thwaite         │ Waiting for the Party: The Lif │ David R. G │
└─────────────────────┴────────────────────────────────┴────────────┘

如果想要一个内存数据库作为中间计算模块,只需要一行 Python 代码就可以搞定:

db = sqlite3.connect(":memory:")

甚至可以支持多个连接访问:

db = sqlite3.connect("file::memory:?cache=shared")

更多功能

SQLite 还提供了许多其他的高级功能,例如窗口函数、UPSERT 语句、UPDATE FROM、generate_series() 函数、R-树索引、正则表达式、模糊查找以及 GEO 等。

如果你在寻找 SQLite 管理开发工具,推荐两款免费开源的工具:DBeaverDB Browser for SQLite

不剪发的Tony老师 CSDN认证博客专家 数据库架构师
毕业于北京航空航天大学,十多年数据库管理与开发经验,CSDN学院签约讲师以及GitChat专栏作者。目前在一家全球性的金融公司从事数据库架构设计。
相关推荐
DirectX修复工具(DirectX Repair)是一款系统级工具软件简便易用。本程序为绿色版无需安装可直接运行。 本程序的主要功能是检测当前系统的DirectX状态如果发现异常则进行修复。程序主要针对0xc000007b问题设计可以完美修复该问题。本程序中包含了最新版的DirectX redist(Jun2010)并且全部DX文件都有Microsoft的数字签名安全放心。 本程序为了应对一般电脑用户的使用采用了易用的一键式设计只要点击主界面上的“检测并修复”按钮程序就会自动完成校验、检测、下载、修复以及注册的全部功能无需用户的介入大大降低了使用难度。在常规修复过程中程序还会自动检测DirectX加速状态在异常时给予用户相应提示。 本程序适用于多个操作系统如Windows XP(需先安装.NET 2.0详情请参阅“致Windows XP用户.txt”文件)、Windows Vista、Windows 7、Windows 8、Windows 8.1、Windows 8.1 Update、Windows 10同时兼容32位操作系统和64位操作系统。本程序会根据系统的自动调整任务模式无需用户进行设置。 本程序的V4.0版分为标准版、增强版以及在线修复版。所有版本都支持修复DirectX的功能而增强版则额外支持修复c++的功能。在线修复版功能与标准版相同但其所需的数据包需要在修复时自动下载。各个版本之间主程序完全相同只是其配套使用的数据包同。因此标准版和在线修复版可以通过补全扩展包的形式成为增强版。本程序自V3.5版起自带扩展功能。只要在主界面的“工具”菜单下打开“选项”对话框找到“扩展”标签点击其中的“开始扩展”按钮即可。扩展过程需要Internet连接扩展成功后新的数据包可自动生效。扩展用时根据网络速度同而最快仅需数秒最慢需要数分钟烦请耐心等待。如扩展失败可点击“扩展”界面左上角小锁图标切换为加密连接即可很大程度上避免因防火墙或其他原因导致的连接失败。 本程序自V2.0版起采用全新的底层程序架构使用了异步多线程编程技术使得检测、下载、修复单独进行干扰快速如飞。新程序更改了自我校验方式因此使用新版本的程序时会再出现自我校验失败的错误;但并非取消自我校验因此程序安全性与之前版本相同并未降低。 程序有更新系统c++功能。由于绝大多数软件运行时需要c++的支持并且c++的异常也会导致0xc000007b错误因此程序在检测修复的同时也会根据需要更新系统中的c++组件。自V3.2版本开始使用了全新的c++扩展包可以大幅提高工业软件修复成功的概率。修复c++的功能仅限于增强版标准版及在线修复版在系统c++异常时(非丢失时)会提示用户使用增强版进行修复。除常规修复外新版程序还支持C++强力修复功能。当常规修复无效时可以到本程序的选项界面内开启强力修复功能可大幅提高修复成功率。请注意请仅在常规修复无效时再使用此功能。 程序有两种窗口样式。正常模式即默认样式适合绝大多数用户使用。另有一种简约模式此时窗口将只显示最基本的内容修复会自动进行修复完成10秒钟后会自动退出。该窗口样式可以使修复工作变得更加简单快速同时方便其他软件、游戏将本程序内嵌即可进行无需人工参与的快速修复。开启简约模式的方法是:打开程序所在目录下的“Settings.ini”文件(如果没有可以自己创建)将其中的“FormStyle”一项的值改为“Simple”并保存即可。 新版程序支持命令行运行模式。在命令行中调用本程序可以在路径后直接添加命令进行相应的设置。常见的命令有7类分别是设置语言的命令、设置窗口模式的命令设置安全级别的命令、开启强力修复的命令、设置c++修复模式的命令、控制Direct加速的命令、显示版权信息的命令。具体命令名称可以通过“/help”或“/?”进行查询。 程序有高级筛选功能开启该功能后用户可以自主选择要修复的文件避免了其他必要的修复工作。同时也支持通过文件进行辅助筛选只要在程序目录下建立“Filter.dat”文件其中的每一行写一个需要修复文件的序号即可。该功能仅针对高级用户使用并且必须在正常窗口模式下才有效(简约模式时无效)。 本程序有自动记录日志功能可以记录每一次检测修复结果方便在出现问题时及时分析和查找原因以便找到解决办法。 程序的“选项”对话框中包含了7项高级功能。点击"常规”选项卡可以调整程序的基本运行情况包括日志记录、安全级别控制、调试模式开启等。只有开启调试模式后才能在C
©️2020 CSDN 皮肤主题: 酷酷鲨 设计师:CSDN官方博客 返回首页
实付 69.90元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值