Kode parsial (Indeks Parsial)
Saat membuat indeks, Anda dapat menentukan kondisi untuk baris yang akan dimasukkan dalam indeks, misalnya, salah satu kolom tidak kosong, tetapi kolom lainnya sama dengan nilai yang ditentukan.create index idx_partial on tab1(a, b) where a is not null and b = 5;
select * from tab1 where a is not null and b = 5; --> search table tab1 using indexIndeks pada ekspresi (Indeks Pada Ekspresi)
Jika ekspresi sering digunakan dalam kueri ke tabel, Anda bisa membuat indeks di atasnya. Namun, harus diingat bahwa meskipun pengoptimal tidak terlalu fleksibel, permutasi kolom dalam ekspresi akan mengarah pada penolakan penggunaan indeks.create index idx_expression on tab1(a + b);
select * from tab1 where a + b > 10; --> search table tab1 using index ...
select * from tab1 where b + a > 10; --> scan tableKolom terhitung (Kolom yang Dihasilkan)
Jika data kolom adalah hasil evaluasi ekspresi pada kolom lain, Anda dapat membuat kolom virtual. Ada dua jenis: VIRTUAL (dihitung setiap kali tabel dibaca dan tidak memakan tempat) dan STORED (dihitung saat menulis data ke tabel dan menggunakan tempat). Tentu saja, Anda tidak bisa langsung menulis data ke kolom seperti itu.create table tab1 (
a integer primary key,
b int,
c text,
d int generated always as (a * abs(b)) virtual,
e text generated always as (substr(c, b, b + 1)) stored
);Indeks R-Tree
Indeks ditujukan untuk pencarian cepat dalam berbagai nilai / penumpukan objek, mis. tugas khas untuk sistem geografis, ketika objek persegi panjang ditentukan oleh posisi dan ukurannya dan diperlukan untuk menemukan semua objek yang berpotongan dengan yang sekarang. Indeks ini diimplementasikan sebagai tabel virtual (lihat di bawah) dan ini hanya indeks pada intinya. Dukungan indeks R-Tree membutuhkan pembuatan SQLite dengan sebuah flagSQLITE_ENABLE_RTREE(tidak dicentang secara default).
create virtual table idx_rtree using rtree (
id, --
minx, maxx, -- c x
miny, maxy, -- c y
data --
);
insert into idx_rtree values (1, -80.7749, -80.7747, 35.3776, 35.3778);
insert into idx_rtree values (2, -81.0, -79.6, 35.0, 36.2);
select id from idx_rtree
where minx >= -81.08 and maxx <= -80.58 and miny >= 35.00 and maxy <= 35.44;
Mengganti nama kolom
SQLite kurang mendukung perubahan dalam struktur tabel, jadi, setelah membuat tabel, Anda tidak dapat mengubah batasan atau menjatuhkan kolom. Sejak versi 3.25.0, Anda dapat mengganti nama kolom, tetapi tidak dapat mengubah tipenya.alter table tbl1 rename column a to b;Untuk operasi lain, semuanya juga diusulkan untuk membuat tabel dengan struktur yang diinginkan, mentransfer data di sana, menghapus yang lama dan mengganti nama yang baru.
Tambahkan baris, jika tidak perbarui (Upsert)
Dengan menggunakan kelason conflictoperator insert, Anda dapat menambahkan baris baru, dan jika Anda sudah memiliki baris dengan nilai yang sama dengan kunci, perbarui.
create table vocabulary (word text primary key, count int default 1);
insert into vocabulary (word) values ('jovial')
on conflict (word) do update set count = count + 1;Pembaruan dari pernyataan
Jika baris perlu diperbarui berdasarkan data dari tabel lain, maka sebelumnya Anda harus menggunakan subkueri untuk setiap kolom atauwith. Sejak versi 3.33.0, operator telah updatediperpanjang dengan kata kunci fromdan sekarang Anda dapat melakukannya
update inventory
set quantity = quantity - daily.amt
from (select sum(quantity) as amt, itemid from sales group by 2) as daily
where inventory.itemid = daily.itemid;Kueri CTE, kelas dengan (Ekspresi Tabel Umum)
Kelaswithdapat digunakan sebagai representasi sementara untuk permintaan. Di versi 3.34.0, kemungkinan untuk menggunakannya withdi dalam diumumkan with.
with tab2 as (select * from tab1 where a > 10),
tab3 as (select * from tab2 inner join ...)
select * from tab3;
Dengan tambahan kata kunci
recursive, withini dapat digunakan untuk kueri di mana Anda ingin mengoperasikan data terkait.
--
with recursive cnt(x) as (
values(1) union all select x + 1 from cnt where x < 1000
)
select x from cnt;
--
create table tab1 (id, parent_id);
insert into tab1 values
(1, null), (10, 1), (11, 1), (12, 10), (13, 10),
(2, null), (20, 2), (21, 2), (22, 20), (23, 21);
--
with recursive tc (id) as (
select id from tab1 where id = 10
union
select tab1.id from tab1, tc where tab1.parent_id = tc.id
)
--
with recursive tc (id, parent_id) as (
select id, parent_id from tab1 where id in (12, 21)
union
select tc.parent_id, tab1.parent_id
from tab1, tc where tab1.id = tc.parent_id
)
select distinct id from tc where parent_id is null order by 1;
-- , .
create table org(name text primary key, boss text references org);
insert into org values ('Alice', null),
('Bob', 'Alice'), ('Cindy', 'Alice'), ('Dave', 'Bob'),
('Emma', 'Bob'), ('Fred', 'Cindy'), ('Gail', 'Cindy');
with recursive
under_alice (name, level) as (
values('Alice', 0)
union all
select org.name, under_alice.level + 1
from org join under_alice on org.boss = under_alice.name
order by 2
)
select substr('..........', 1, level * 3) || name from under_alice;Fungsi jendela (Fungsi Jendela)
Sejak versi 3.25.0, fungsi jendela, terkadang juga disebut fungsi analitik, tersedia di SQLite, memungkinkan Anda melakukan penghitungan pada sepotong data (jendela).--
create table tab1 (x integer primary key, y text);
insert into tab1 values (1, 'aaa'), (2, 'ccc'), (3, 'bbb');
select x, y, row_number() over (order by y) as row_number from tab1 order by x;
--
create table tab1 (a integer primary key, b, c);
insert into tab1 values (1, 'A', 'one'),
(2, 'B', 'two'), (3, 'C', 'three'), (4, 'D', 'one'),
(5, 'E', 'two'), (6, 'F', 'three'), (7, 'G', 'one');
--
select a, b, group_concat(b, '.') over (order by a rows between 1 preceding and 1 following) as prev_curr_next from tab1;
-- (, c)
select c, a, b, group_concat(b, '.') over (partition by c order by a range between current row and unbounded following) as curr_end from tab1 order by c, a;
--
select c, a, b, group_concat(b, '.') filter (where c <> 'two') over (order by a) as exceptTwo from t1 order by a;
Utilitas SQLite
Selain CLI sqlite3 , tersedia dua lagi utilitas. Yang pertama - sqldiff , memungkinkan Anda untuk membandingkan database (atau tabel terpisah) tidak hanya berdasarkan struktur, tetapi juga dengan data. Kedua, sqlite3_analizer, digunakan untuk menampilkan informasi tentang bagaimana ruang secara efektif digunakan oleh tabel dan indeks dalam file database. Informasi serupa dapat diperoleh dari tabel virtual dbstat (memerlukan tandaSQLITE_ENABLE_DBSTAT_VTABsaat mengompilasi SQLite).
Sejak versi 3.22.0 CLI sqlite3 berisi perintah (eksperimental) .expert yang dapat memberi tahu Anda indeks mana yang harus ditambahkan untuk kueri yang dimasukkan.
Buat Vakum Ke cadangan
Sejak versi 3.27.0, perintah telahvacuumdiperpanjang dengan kata kunci intoyang memungkinkan Anda membuat salinan database tanpa menghentikannya langsung dari SQL. Ini adalah alternatif sederhana untuk API Cadangan .
vacuum into 'D:/backup/' || strftime('%Y-%M-%d', 'now') || '.sqlite';Fungsi printf
Fungsinya dianalogikan dengan fungsi C. Dalam kasus ini,NULL-values diinterpretasikan sebagai string kosong untuk %sdan 0untuk placeholder angka.
select 'a' || ' 123 ' || null; --> null
select printf('%s %i %s', 'a', 123, null); --> 123 a
select printf('%s %i %i', 'a', 123, null); --> 123 a 0
Waktu dan tanggal
Di SQLite DatedanTime . Meskipun dimungkinkan untuk membuat tabel dengan kolom jenis ini, akan sama seperti membuat kolom tanpa menentukan jenis, sehingga data dalam kolom tersebut disimpan sebagai teks. Ini nyaman saat melihat data, tetapi memiliki sejumlah kelemahan: pencarian tidak efektif, jika tidak ada indeks, data memakan banyak ruang, dan tidak ada zona waktu. Untuk menghindari hal ini, Anda dapat menyimpan data sebagai waktu unix , mis. jumlah detik sejak tengah malam 01/01/1970.
select strftime('%Y-%M-%d %H:%m', 'now'); --> UTC
select strftime('%Y-%M-%d %H:%m', 'now', 'localtime'); -->
select strftime('%s', 'now'); -- Unix-
select strftime('%s', 'now', '+2 day'); --> unix-
-- unix- - 21-11-2020 15:25:14
select strftime('%d-%m-%Y %H:%M:%S', 1605961514, 'unixepoch', 'localtime')Json
Sejak versi 3.9.0, Anda dapat bekerja dengan json di SQLite (baik benderaSQLITE_ENABLE_JSON1waktu kompilasi atau ekstensi yang dimuat diperlukan ). Data Json disimpan sebagai teks. Hasil dari fungsinya juga berupa teks.
select json_array(1, 2, 3); --> [1,2,3] ()
select json_array_length(json_array(1, 2, 3)); --> 3
select json_array_length('[1,2,3]'); --> 3
select json_object('a', json_array(2, 5), 'b', 10); --> {"a":[2,5],"b":10} ()
select json_extract('{"a":[2,5],"b":10}', '$.a[0]'); --> 2
select json_insert('{"a":[2,5]}', '$.c', 10); --> {"a":[2,5],"c":10} ()
select value from json_each(json_array(2, 5)); --> 2 2, 5
select json_group_array(value) from json_each(json_array(2, 5)); --> [2,5] ()Pencarian teks lengkap
Seperti json, pencarian teks lengkap membutuhkan sebuah benderaSQLITE_ENABLE_FTS5untuk disetel saat menyusun atau memuat ekstensi. Untuk bekerja dengan penelusuran, pertama-tama dibuat tabel virtual dengan bidang yang diindeks, lalu data dimuat di sana menggunakan yang biasa insert. Perlu diingat bahwa untuk operasinya ekstensi membuat tabel tambahan dan tabel virtual yang dibuat menggunakan datanya.
create virtual table emails using fts5(sender, body);
SELECT * FROM emails WHERE emails = 'fts5'; -- sender body fts5
Ekstensi
Kemampuan SQLite dapat ditambahkan melalui modul yang dapat dimuat. Beberapa di antaranya telah disebutkan di atas - json1 dan fts .Ekstensi dapat digunakan baik untuk menambahkan fungsi yang ditentukan pengguna (tidak hanya fungsi skalar, seperti, misalnya
crc32, tetapi juga menggabungkan atau bahkan berjendela ), dan tabel virtual. Tabel virtual adalah tabel yang ada di database, tetapi datanya diproses oleh ekstensi, sementara, bergantung pada implementasinya, beberapa di antaranya memerlukan pembuatan
create virtual table temp.tab1 using csv(filename='thefile.csv');
select * from tab1;Lainnya, yang disebut nilai tabel , dapat digunakan dengan segera.
select value from generate_series(5, 100, 5);Beberapa tabel virtual tercantum di sini .
Satu ekstensi dapat mengimplementasikan fungsi dan tabel virtual. Misalnya, json1 berisi 13 skalar dan 2 fungsi agregat dan dua tabel virtual
json_eachdan json_tree. Untuk menulis fungsi Anda sendiri, Anda hanya perlu memiliki pengetahuan dasar tentang C dan mengurai kode ekstensi dari repositori SQLite . Menerapkan tabel virtual Anda sendiri sedikit lebih rumit (tampaknya itulah sebabnya jumlahnya sedikit). Di sini kami dapat merekomendasikan buku yang agak ketinggalan jaman Menggunakan SQLite oleh Jay A. Kreibich , artikel oleh Michael Owens , template dari repositori, dan kode generate_series sebagai fungsi nilai tabel.
Selain itu, ekstensi dapat menerapkan hal-hal khusus sistem operasi, seperti sistem file, untuk memberikan portabilitas. Detailnya dapat ditemukan di sini .
bermacam-macam
- Gunakan
'(petik tunggal) untuk konstanta string dan"(petik ganda) untuk nama kolom dan tabel. - Untuk mendapatkan informasi tentang tabel tab1 dapat Anda gunakan
-- main select * from pragma_table_info('tab1'); -- temp (attach) select * from pragma_table_info('tab1') where schema = 'temp' - SQLite memiliki forum resminya sendiri , tempat pencipta SQLite - Richard Hipp berpartisipasi, dan tempat Anda dapat memposting laporan bug.
- Editor SQLite: SQLite Studio , DB Browser untuk SQLite dan (ads!) Sqlite-gui (khusus Windows).