Inilah mengapa SQLite sangat cocok untuk pekerjaan Anda sehari-hari. Tidak masalah jika Anda adalah pengembang, analis, penguji, admin, atau manajer produk.
Sebagai permulaan, beberapa fakta yang diketahui:
SQLite adalah sistem manajemen basis data yang paling banyak digunakan di dunia dan termasuk dalam semua sistem operasi populer.
Bekerja tanpa server.
Untuk pengembang, itu tertanam langsung ke dalam aplikasi.
Untuk orang lain - konsol yang nyaman (REPL) dalam satu file (sqlite3.exe di Windows, sqlite3 di Linux / macOS).
Konsol, impor dan ekspor
Konsol adalah fitur pembunuh SQLite: alat analisis data yang lebih kuat daripada Excel, dan jauh lebih sederhana daripada panda mana pun. Data dari CSV dimuat dengan satu perintah, tabel dibuat secara otomatis:
> .import --csv city.csv city
> select count(*) from city;
1117
Fitur SQL dasar didukung, dan konsol menunjukkan hasilnya dalam tabel yang bagus. Ada juga fitur SQL lanjutan, tetapi lebih banyak tentang mereka nanti.
select
century || '- ' as dates,
count(*) as city_count
from history
group by century
order by century desc;
┌──────────┬────────────┐
│ dates │ city_count │
├──────────┼────────────┤
│ 21- │ 1 │
│ 20- │ 263 │
│ 19- │ 189 │
│ 18- │ 191 │
│ 17- │ 137 │
│ 16- │ 79 │
│ 15- │ 39 │
│ 14- │ 38 │
│ 13- │ 27 │
│ 12- │ 44 │
│ 11- │ 8 │
│ 10- │ 6 │
│ 9- │ 4 │
│ 5- │ 1 │
│ 3- │ 1 │
└──────────┴────────────┘
Banyak format ekspor data: sql, csv, json, bahkan penurunan harga dan html. Semuanya dilakukan dengan beberapa perintah:
.mode json
.output city.json
select city, foundation_year, timezone from city limit 10;
.shell cat city.json
[{"city":"","foundation_year":1969,"timezone":"UTC+3"},
{"city":"","foundation_year":1857,"timezone":"UTC+3"},
{"city":"-","foundation_year":1830,"timezone":"UTC+7"},
{"city":"","foundation_year":1913,"timezone":"UTC+7"},
{"city":"","foundation_year":1730,"timezone":"UTC+7"},
{"city":"","foundation_year":1846,"timezone":"UTC+7"},
{"city":"","foundation_year":1709,"timezone":"UTC+7"},
{"city":"","foundation_year":1942,"timezone":"UTC+7"},
{"city":"","foundation_year":1748,"timezone":"UTC+7"},
{"city":"","foundation_year":1736,"timezone":"UTC+7"}]
Bekerja dengan JSON secara native
Tidak ada yang lebih nyaman daripada SQLite untuk mengurai dan mengubah JSON. Anda dapat memilih data langsung dari file, seolah-olah itu adalah tabel biasa. Atau muat ke dalam tabel dan pilih dari sana - sesuai keinginan Anda.
select
json_extract(value, '$.code') as code,
json_extract(value, '$.name') as name,
json_extract(value, '$.rate') as rate,
json_extract(value, '$.default') as "default"
from
json_each(readfile('currency.sample.json'))
;
┌──────┬───────────────────┬────────────┬─────────┐ │ code │ name │ rate │ default │ ├──────┼───────────────────┼────────────┼─────────┤ │ AZN │ │ 0.023107 │ 0 │ │ BYR │ │ 0.034966 │ 0 │ │ EUR │ │ 0.011138 │ 0 │ │ GEL │ │ 0.0344 │ 0 │ │ KGS │ │ 1.131738 │ 0 │ │ KZT │ │ 5.699857 │ 0 │ │ RUR │ │ 1.0 │ 1 │ │ UAH │ │ 0.380539 │ 0 │ │ USD │ │ 0.013601 │ 0 │ │ UZS │ │ 142.441417 │ 0 │ └──────┴───────────────────┴────────────┴─────────┘
Tidak peduli seberapa luas JSON tersebut, Anda dapat memilih atribut dari setiap penumpukan:
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 │ - │ │ 7.539 │ - │ │ 7.540 │ │ │ 9.399 │ │ │ 9.400 │ │ │ 9.401 │ │ │ 43.641 │ │ │ 43.646 │ │ │ 43.647 │ │ └────────┴──────────────────────┘
CTE dan setel operasi
, Common Table Expressions ( WITH
) , . ( parent_id
) — WITH
. «» .
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 │ ├──────┼─────────────────────────────────────┼───────┤ │ 113 │ │ 1 │ │ 1 │ , │ 2 │ │ 1586 │ , │ 2 │ │ 1588 │ , , │ 3 │ │ 78 │ , , │ 3 │ │ 212 │ , , │ 3 │ │ ... │ ... │ ... │ └──────┴─────────────────────────────────────┴───────┘
? : 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.13)
else
salary_from
end
);
, :
select
substr(name, 1, 40) as name,
salary_net
from vacancy
where
salary_currency = 'RUR'
and salary_net is not null
limit 10;
┌──────────────────────────────────────────┬────────────┐
│ name │ salary_net │
├──────────────────────────────────────────┼────────────┤
│ - (Delphi) │ 40000 │
│ Scala / Java ( Senio │ 60000 │
│ Java / Kotlin Developer │ 150000 │
│ 1 │ 150000 │
│ C# │ 53097 │
│ 1 │ 80000 │
│ Java - (Middle, Senior) │ 100000 │
│ C#/ .NET │ 70796 │
│ / QA engineer ( │ 45000 │
│ │ 17699 │
└──────────────────────────────────────────┴────────────┘
.
? : , , , . , . , ( ).
.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 - . , — . .
, . , :
. . , :
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 │ * │
│ 13 │ 5 │ * │
│ 14 │ 3 │ * │
│ 15 │ 1 │ * │
│ 17 │ 1 │ * │
│ 18 │ 2 │ * │
└──────┴────────────┴────────────────────────────────┘
SQLite ( — ). INSERT
240 . CSV ( ) — 2 .
.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 , . . write-ahead log ( ) . — , .
SQLite . , sqlite.org SQLite , (~200 ). 700 , 95% .
,
, «» . .
SQLite : json json_extract()
:
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 = 'RUR';
QUERY PLAN
`--SEARCH TABLE currency USING INDEX currency_code_idx (code=?)
. WITH RECURSIVE
, :
:
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 │ └─────────────────────┴────────────────────────────────┴────────────┘
in-memory ? :
db = sqlite3.connect(":memory:")
:
db = sqlite3.connect("file::memory:?cache=shared")
( PostgreSQL). UPSERT
, UPDATE FROM
generate_series()
. R-Tree . , fuzzy- . SQLite «» .
, SQLite . , !
, SQLite — @sqliter