大家好!我是只談技術不剪發的?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?>?selectcount(?*)?fromcity?;?1117

SQLite?命令行工具支持各種?SQL?語句，同時以?ASCII?風格顯示查詢結果：

select?century?||?'?century'asdates,?count(*)?ascity_count?fromhistory?groupbycentury?orderbycentury?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?selectcity,?foundation_year,?timezone?fromcity?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')?ascode,?json_extract(?value,?'$.iso.number')?asnum,?json_extract(?value,?'$.name')?asname,?json_extract(?value,?'$.units.major.name')?asunit?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')?asid,?json_extract(?value,?'$.name')?asname?from?json_tree(readfile(?'industry.sample.json'))?where?pathlike'$[%].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?支持通用表表達式(Common?Table?Expression)和連接查詢。對于具有層級關系的數據(例如組織結構等)，可以通過?WITH?RECURSIVE?很方便地進行遍歷。

withrecursivetmp(?id,?name,?level)?as(?selectid,?name,?1aslevel?fromarea?whereparent_id?isnull?unionall?select?area.id,?tmp.name?||?',?'||?area.name?asname,?tmp.level?+?1aslevel?fromarea?jointmp?onarea.parent_id?=?tmp.id?)?select*?fromtmp;?┌──────┬──────────────────────────┬───────┐?│?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?集合運算符：

selectemployer_id?fromemployer_area?wherearea_id?=?1?except?selectemployer_id?fromemployer_area?wherearea_id?=?2;

基于其他字段的生成列也不在話下：

altertablevacancy?addcolumnsalary_net?integeras(?casewhensalary_gross?=?truethen?round(salary_from/?1.04)?else?salary_from?end?);

生成列可以像其他普通字段一樣查詢：

select?substr(?name,?1,?40)?asname,?salary_net?fromvacancy?where?salary_currency?=?'JPY'?andsalary_net?isnotnull?limit10;

統計函數

通過加載?stats?插件，SQLite?支持以下描述性統計：均值、中位數、百分位、標準差等。

.load?sqlite3-stats

selectcount(*)?asbook_count,?cast(?avg(num_pages)?asinteger)?asmean,?cast(?median(num_pages)?asinteger)?asmedian,?mode(num_pages)?asmode,?percentile_90(num_pages)?asp90,?percentile_95(num_pages)?asp95,?percentile_99(num_pages)?asp99?frombooks;?┌────────────┬──────┬────────┬──────┬─────┬─────┬──────┐│?book_count?│?mean?│?median?│?mode?│?p90?│?p95?│?p99?│├────────────┼──────┼────────┼──────┼─────┼─────┼──────┤│?1483?│?349?│?295?│?256?│?640?│?817?│?1199?│└────────────┴──────┴────────┴──────┴─────┴─────┴──────┘

SQLite?比其他數據庫管理系統提供的函數更少一些，不過可以通過擴展插件的方式獲取額外的支持。這個項目按照不同的領域編譯了一些常用的插件。

以下示例在控制臺中描繪了一個數據分布圖：

with?slots?as?(selectnum_pages/100?as?slot,count(*)?as?book_countfrom?booksgroup?by?slot),max?as?(select?max(book_count)?as?valuefrom?slots)selectslot,book_count,printf('%.'?||?(book_count?*?30?/?max.value)?||?'c',?'*')?as?bar?from?slots,?maxorder?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

createvirtualtabletemp.blocks_csv?usingvsv(?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?oninsertintoblocks?select*?fromblocks_csv;

Run?Time:?real?5.176?user?4.716420?sys?0.403866selectcount(*)?fromblocks;?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?作為文檔數據庫使用：

createtablecurrency(?bodytext,?code?textas(json_extract(?body,?'$.code')),?nametextas(json_extract(?body,?'$.name'))?);

createindexcurrency_code_idx?oncurrency(code);

insertintocurrency?selectvaluefromjson_each(readfile(?'currency.sample.json'));?explainqueryplan?selectnamefromcurrency?wherecode?=?'EUR';?QUERY?PLAN`?--SEARCH?TABLE?currency?USING?INDEX?currency_code_idx?(code=?)

有了?WITH?RECURSIVE?查詢，SQLite?也可以作為一個圖形數據庫使用，或者使用這個?simple-graph(Python?模塊)。

SQLite?提供了內置的全文搜索功能：

createvirtualtablebooks_fts?usingfts5(title,?author,?publisher);

insertintobooks_fts?selecttitle,?author,?publisher?frombooks;

selectauthor,substr(title,?1,?30)?astitle,?substr(publisher,?1,?10)?aspublisher?frombooks_fts?wherebooks_fts?match'ann'limit5;?┌─────────────────────┬────────────────────────────────┬────────────┐│?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?管理開發工具，推薦兩款免費開源的工具：DBeaver?和?DB?Browser?for?SQLite。

最后，小編給您推薦，金山毒霸“文檔修復大師”，支持.doc/.ppt/.xls等格式的文檔的修復，修復后再也不用為文檔的數據丟失而煩惱。