Teilcodes (Teilindizes)
Beim Erstellen eines Index können Sie eine Bedingung für eine Zeile angeben, die in den Index aufgenommen werden soll. Beispielsweise ist eine der Spalten nicht leer, die andere entspricht dem angegebenen Wert.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 indexIndizes für den Ausdruck (Indizes für Ausdrücke)
Wenn ein Ausdruck häufig in Abfragen für eine Tabelle verwendet wird, können Sie einen Index darauf erstellen. Es sollte jedoch berücksichtigt werden, dass, obwohl der Optimierer nicht sehr flexibel ist, die Permutation von Spalten im Ausdruck dazu führt, dass die Verwendung des Index abgelehnt wird.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 tableBerechnete Spalte (generierte Spalten)
Wenn die Spaltendaten das Ergebnis der Auswertung eines Ausdrucks in anderen Spalten sind, können Sie eine virtuelle Spalte erstellen. Es gibt zwei Typen: VIRTUAL (berechnet jedes Mal, wenn die Tabelle gelesen wird und keinen Speicherplatz beansprucht) und STORED (berechnet, wenn Daten in die Tabelle geschrieben werden und Speicherplatz beanspruchen). Natürlich können Sie keine Daten direkt in solche Spalten schreiben.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
);R-Tree-Index
Der Index ist für die schnelle Suche in einem Wertebereich / Verschachtelung von Objekten vorgesehen, d.h. Aufgaben, die für Geosysteme typisch sind, wenn rechteckige Objekte durch ihre Position und Größe angegeben werden und alle Objekte gefunden werden müssen, die sich mit dem aktuellen Objekt überschneiden. Dieser Index ist als virtuelle Tabelle implementiert (siehe unten) und dies ist nur im Wesentlichen ein Index. Für die Unterstützung des R-Tree-Index muss SQLite mit einem Flag erstellt werdenSQLITE_ENABLE_RTREE(standardmäßig nicht aktiviert).
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;
Spalte umbenennen
SQLite unterstützt Änderungen in der Struktur von Tabellen nur unzureichend. Daher können Sie nach dem Erstellen einer Tabelle keine Einschränkung ändern oder eine Spalte löschen. Seit Version 3.25.0 können Sie eine Spalte umbenennen, aber ihren Typ nicht ändern.alter table tbl1 rename column a to b;Für andere Operationen wird auch alles vorgeschlagen, um eine Tabelle mit der gewünschten Struktur zu erstellen, Daten dort zu übertragen, die alte zu löschen und die neue umzubenennen.
Zeile hinzufügen , sonst aktualisieren (Upsert)
Mit deron conflictOperatorklasse insertkönnen Sie eine neue Zeile hinzufügen. Wenn Sie bereits eine Zeile mit demselben Wert pro Schlüssel haben, aktualisieren Sie diese.
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;Update von Anweisung
Wenn eine Zeile basierend auf Daten aus einer anderen Tabelle aktualisiert werden muss, mussten Sie zuvor für jede Spalte oder eine Unterabfrage verwendenwith. Seit Version 3.33.0 wurde der Operator updateum ein Schlüsselwort erweitert, fromund jetzt können Sie dies tun
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;CTE-Abfragen, Klasse mit (Common Table Expression)
Die Klassewithkann als temporäre Darstellung für eine Anforderung verwendet werden. In Version 3.34.0 wurde die Möglichkeit der Verwendung von withInnen erklärt with.
with tab2 as (select * from tab1 where a > 10),
tab3 as (select * from tab2 inner join ...)
select * from tab3;
Mit dem Zusatz von einem Schlüsselwort
recursive, withkann es für Abfragen verwendet werden , wenn Sie auf die zugehörigen Daten bedienen möchten.
--
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;Fensterfunktion (Fensterfunktionen)
Seit Version 3.25.0 sind in SQLite Fensterfunktionen, manchmal auch als Analysefunktionen bezeichnet, verfügbar, mit denen Sie Berechnungen für ein Datenelement (Fenster) durchführen können.--
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;
SQLite-Dienstprogramme
Neben der sqlite3- CLI stehen zwei weitere Dienstprogramme zur Verfügung. Mit dem ersten - sqldiff können Sie Datenbanken (oder eine separate Tabelle) nicht nur nach Struktur, sondern auch nach Daten vergleichen. Der zweite, sqlite3_analizer, wird verwendet, um Informationen darüber anzuzeigen, wie Speicherplatz von Tabellen und Indizes in der Datenbankdatei effektiv genutzt wird. Ähnliche Informationen können aus der virtuellen Tabelle dbstat abgerufen werden (erfordertSQLITE_ENABLE_DBSTAT_VTABbeim Kompilieren von SQLite ein Flag ).
Seit Version 3.22.0 enthält die CLI sqlite3 einen (experimentellen) .expert-Befehl, mit dem Sie feststellen können, welcher Index für die eingegebene Abfrage hinzugefügt werden soll.
Erstellen Sie ein Vakuum in Backup
Seit Version 3.27.0 wurde der Befehlvacuumum ein Schlüsselwort erweitert into, mit dem Sie eine Kopie der Datenbank erstellen können, ohne sie direkt aus SQL zu stoppen. Es ist eine einfache Alternative zur Backup-API .
vacuum into 'D:/backup/' || strftime('%Y-%M-%d', 'now') || '.sqlite';Printf-Funktion
Die Funktion ist analog zur C-Funktion. In diesem Fall werden dieNULL-Werte als leere Zeichenfolge für %sund 0für den Zahlenplatzhalter interpretiert .
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
Zeit und Datum
In SQLite DateundTime . Obwohl es möglich ist, eine Tabelle mit Spalten dieser Typen zu erstellen, entspricht dies der Erstellung von Spalten ohne Angabe eines Typs, sodass die Daten in solchen Spalten als Text gespeichert werden. Dies ist praktisch beim Anzeigen von Daten, hat jedoch eine Reihe von Nachteilen: Ineffektive Suche: Wenn kein Index vorhanden ist, nehmen die Daten viel Platz ein und es gibt keine Zeitzone. Um dies zu vermeiden, können Sie die Daten als Unix-Zeit speichern , d. H. Anzahl der Sekunden seit Mitternacht 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
Seit Version 3.9.0 können Sie in SQLite mit json arbeiten (entweder ist ein Flag zurSQLITE_ENABLE_JSON1Kompilierungszeit oder eine geladene Erweiterung erforderlich ). Json - Daten werden als Text gespeichert. Das Ergebnis der Funktionen ist auch Text.
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] ()Volltextsuche
Wie bei json muss bei der VolltextsucheSQLITE_ENABLE_FTS5beim Kompilieren oder Laden einer Erweiterung ein Flag gesetzt werden . Um mit der Suche zu arbeiten, wird zunächst eine virtuelle Tabelle mit indizierten Feldern erstellt und anschließend die Daten mit der üblichen Tabelle dort geladen insert. Es ist zu beachten, dass die Erweiterung für ihren Betrieb zusätzliche Tabellen erstellt und die erstellte virtuelle Tabelle ihre Daten verwendet.
create virtual table emails using fts5(sender, body);
SELECT * FROM emails WHERE emails = 'fts5'; -- sender body fts5
Erweiterungen
SQLite-Funktionen können über ladbare Module hinzugefügt werden. Einige von ihnen wurden bereits oben erwähnt - json1 und die fts .Erweiterungen können verwendet werden, um benutzerdefinierte Funktionen (nicht nur skalar, wie zum Beispiel
crc32, sondern auch aggregierend oder sogar mit Fenstern ) und virtuelle Tabellen hinzuzufügen . Virtuelle Tabellen sind Tabellen, die in der Datenbank vorhanden sind, deren Daten jedoch von der Erweiterung verarbeitet werden , während einige von ihnen je nach Implementierung erstellt werden müssen
create virtual table temp.tab1 using csv(filename='thefile.csv');
select * from tab1;Andere, sogenannte Tabellenwerte , können sofort verwendet werden.
select value from generate_series(5, 100, 5);Einige der virtuellen Tabellen sind hier aufgelistet .
Eine Erweiterung kann sowohl Funktionen als auch virtuelle Tabellen implementieren. Zum Beispiel enthält json1 13 Skalar- und 2 Aggregatfunktionen sowie zwei virtuelle Tabellen
json_eachund json_tree. Um Ihre eigene Funktion zu schreiben, müssen Sie lediglich Grundkenntnisse in C haben und den Erweiterungscode aus dem SQLite-Repository analysieren . Das Implementieren eigener virtueller Tabellen ist etwas komplizierter (anscheinend gibt es deshalb nur wenige davon). Hier können wir das etwas veraltete Buch Using SQLite von Jay A. Kreibich , den Artikel von Michael Owens , die Vorlage aus dem Repository und den Code generate_series als tabellenwertige Funktionen empfehlen .
Darüber hinaus können Erweiterungen betriebssystemspezifische Elemente wie das Dateisystem implementieren, um Portabilität zu gewährleisten. Details finden Sie hier .
Verschiedenes
- Verwenden Sie
'(einfaches Anführungszeichen) für Zeichenfolgenkonstanten und"(doppeltes Anführungszeichen) für Spalten- und Tabellennamen. - Um Informationen zu Tabelle tab1 zu erhalten, können Sie verwenden
-- main select * from pragma_table_info('tab1'); -- temp (attach) select * from pragma_table_info('tab1') where schema = 'temp' - SQLite hat ein eigenes offizielles Forum , in dem der Schöpfer von SQLite - Richard Hipp - teilnimmt und in dem Sie einen Fehlerbericht veröffentlichen können.
- SQLite-Editoren: SQLite Studio , DB Browser für SQLite und ( ads !) Sqlite-gui (nur Windows).