SQLite Spouštěč, zobrazení a index s příkladem
Marcus Allen
Při každodenním používání SQLite, budete potřebovat nějaké nástroje pro správu databáze. Můžete je také použít k efektivnějšímu dotazování databáze vytvořením indexů nebo k lepšímu opětovnému použití vytvořením pohledů.
SQLite Zobrazit
Pohledy jsou velmi podobné tabulkám. Ale pohledy jsou logické tabulky; nejsou fyzicky uloženy jako tabulky. Pohled se skládá z příkazu select.
Pro své složité dotazy můžete definovat zobrazení a tyto dotazy můžete kdykoli znovu použít, a to přímým voláním zobrazení namísto opětovného přepisování dotazů.
Příkaz CREATE VIEW
Chcete-li vytvořit pohled na databázi, můžete použít příkaz CREATE VIEW následovaný názvem pohledu a poté vložit požadovaný dotaz.
Příklad: V následujícím příkladu vytvoříme pohled s názvem „AllStudentsView“ ve vzorové databázi “NávodySampleDB.db“ takto:
Krok 1) Otevřete Tento počítač a přejděte do následujícího adresáře “C:\sqlite“ a poté otevřete „sqlite3.exe"
Krok 2) Otevřít databázi"NávodySampleDB.db“ pomocí následujícího příkazu:
Krok 3) Následuje základní syntaxe příkazu sqlite3 pro vytvoření pohledu
CREATE VIEW AllStudentsView
AS
SELECT
s.StudentId,
s.StudentName,
s.DateOfBirth,
d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
Z příkazu by neměl být žádný výstup, jako je tento:
Krok 4) Chcete-li zajistit, aby byl pohled vytvořen, můžete vybrat seznam pohledů v databázi spuštěním následujícího příkazu:
SELECT name FROM sqlite_master WHERE type = 'view';
Měli byste vidět výhled"AllStudentsView“ se vrací:
Krok 5) Nyní je náš pohled vytvořen, můžete jej použít jako normální tabulku, například takto:
SELECT * FROM AllStudentsView;
Tento příkaz se dotáže zobrazení „AllStudents“ a vybere z něj všechny řádky, jak je znázorněno na následujícím snímku obrazovky:
Dočasné pohledy
Dočasné pohledy jsou dočasné pro aktuální připojení k databázi použité k jeho vytvoření. Poté, pokud zavřete připojení k databázi, budou všechny dočasné pohledy automaticky odstraněny. Dočasné pohledy se vytvářejí pomocí jednoho z následujících příkazů:
- VYTVOŘIT ZOBRAZENÍ TEPLOTY, popř
- VYTVOŘIT DOČASNÝ POHLED.
Dočasné pohledy jsou užitečné, pokud chcete prozatím provádět nějaké operace a nepotřebujete, aby to byl trvalý pohled. Stačí tedy vytvořit dočasný pohled a poté provést zpracování pomocí tohoto pohledu. Later když uzavřete spojení s databází, bude automaticky smazána.
Příklad:
V následujícím příkladu otevřeme připojení k databázi a poté vytvoříme dočasné zobrazení.
Poté toto připojení uzavřeme a zkontrolujeme, zda dočasné zobrazení stále existuje nebo ne.
Krok 1) Otevřete sqlite3.exe z adresáře “C:\sqlite“ jak bylo vysvětleno dříve.
Krok 2) Otevřít připojení k databázi “NávodySampleDB.db“ spuštěním následujícího příkazu:
.open TutorialsSampleDB.db
Krok 3) Napište následující příkaz, který vytvoří dočasné zobrazení “AllStudentsTempView"
CREATE TEMP VIEW AllStudentsTempView
AS
SELECT
s.StudentId,
s.StudentName,
s.DateOfBirth,
d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
Krok 4) Ujistěte se, že zobrazení temp "AllStudentsTempView“ se vytvoří spuštěním následujícího příkazu:
SELECT name FROM sqlite_temp_master WHERE type = 'view';
Krok 5) Zavřete sqlite3.exe a znovu jej otevřete.
Krok 6) Otevřít připojení k databázi “NávodySampleDB.db“ pomocí následujícího příkazu:
.open TutorialsSampleDB.db
Krok 7) Spuštěním následujícího příkazu získáte seznam dočasných pohledů vytvořených v databázi:
SELECT name FROM sqlite_temp_master WHERE type = 'view';
Neměli byste vidět žádný výstup, protože dočasný pohled, který jsme vytvořili, je odstraněn, když jsme v předchozím kroku zavřeli připojení k databázi. Jinak, pokud ponecháte spojení s databází otevřené, budete moci vidět dočasný pohled s daty.
Poznámky:
- S pohledy nemůžete použít příkazy INSERT, DELETE nebo UPDATE, můžete použít pouze příkaz „vybrat z pohledů“, jak je znázorněno v kroku 5 v příkladu CREATE View.
- Chcete-li odstranit VIEW, můžete použít příkaz „DROP VIEW“:
DROP VIEW AllStudentsView;
Chcete-li zajistit odstranění pohledu, můžete spustit následující příkaz, který vám poskytne seznam pohledů v databázi:
SELECT name FROM sqlite_master WHERE type = 'view';
Nenajdete žádná zobrazení vrácená po odstranění zobrazení, a to následovně:
SQLite index
Pokud máte knihu a chcete pro ni vyhledat klíčové slovo. Toto klíčové slovo budete hledat v rejstříku knihy. Poté přejdete na číslo stránky daného klíčového slova a přečtete si o něm více informací.
Pokud však kniha neobsahuje žádný rejstřík ani čísla stránek, budete skenovat celou knihu od začátku do konce, dokud nenajdete klíčové slovo, které hledáte. A to je velmi obtížné, zvláště když máte index a velmi pomalý proces hledání klíčového slova.
Indexy v SQLite (a stejný koncept platí pro ostatní systémy pro správu databází funguje stejně jako rejstříky na zadní straně knih.
Když hledáte nějaké řádky v SQLite tabulka s vyhledávacími kritérii, SQLite bude hledat ve všech řádcích tabulky, dokud nenajde řádky, které hledáte, které odpovídají kritériím vyhledávání. A tento proces je velmi pomalý, když máte větší stoly.
Indexy urychlí vyhledávací dotazy na data a pomohou při načítání dat z tabulek. Indexy jsou definovány ve sloupcích tabulky.
Zlepšení výkonu pomocí indexů:
Indexy mohou zlepšit výkon vyhledávání dat v tabulce. Když vytvoříte index ve sloupci, SQLite vytvoří datovou strukturu pro tento index, kde každá hodnota pole má ukazatel na celý řádek, kam hodnota patří.
Pokud pak spustíte dotaz s vyhledávací podmínkou na sloupec, který je součástí indexu, SQLite nejprve vyhledá hodnotu na indexu. SQLite nenaskenuje celou tabulku. Poté přečte místo, kde hodnota ukazuje na řádek tabulky. SQLite vyhledá řádek v tomto umístění a načte jej.
Pokud však sloupec, který hledáte, není součástí indexu, SQLite provede skenování hodnot sloupců, aby našel data, která hledáte. Pokud neexistuje žádný index, bude to obvykle pomalejší proces.
Představte si knihu bez rejstříku a vy potřebujete vyhledat konkrétní slovo. Naskenujete celou knihu od první do poslední stránky a budete hledat dané slovo. Pokud však na té knize máte rejstřík, budete hledat slovo na ní jako první. Získejte číslo stránky, kde se nachází, a poté na ni přejděte. Což bude mnohem rychlejší než skenování celé knihy od obálky k přebalu.
SQLite VYTVOŘIT INDEX
Chcete-li vytvořit index na sloupci, měli byste použít příkaz CREATE INDEX. A měli byste to definovat takto:
- Po příkazu CREATE INDEX musíte zadat název indexu.
- Za názvem indexu musíte zadat klíčové slovo „ON“ a za ním název tabulky, ve které bude index vytvořen.
- Poté seznam názvů sloupců, které se používají pro index.
- Můžete použít jedno z následujících klíčových slov „ASC“ nebo „DESC“ za kterýmkoli názvem sloupce k určení pořadí řazení používaného k řazení indexových dat.
Příklad:
V následujícím příkladu vytvoříme index „StudentNameIndex“ na studentském stole v „Studenti“ databáze takto:
Krok 1) Přejděte do složky „C:\sqlite“ jak bylo vysvětleno dříve.
Krok 2) Otevřete sqlite3.exe.
Krok 3) Otevřít databázi"NávodySampleDB.db“ pomocí následujícího příkazu:
.open TutorialsSampleDB.db
Krok 4) Vytvořit nový index “StudentNameIndex“ pomocí následujícího příkazu:
CREATE INDEX StudentNameIndex ON Students(StudentName);
Neměli byste vidět žádný výstup pro toto:
Krok 5) Chcete-li zajistit, aby byl index vytvořen, můžete spustit následující dotaz, který vám poskytne seznam indexů vytvořených v tabulce Studenti:
PRAGMA index_list(Students);
Měli byste vidět, že index, který jsme právě vytvořili, vrátil:
Poznámky:
- Indexy lze vytvářet nejen na základě sloupců, ale také výrazů. Něco takového:
CREATE INDEX OrderTotalIndex ON OrderItems(OrderId, Quantity*Price);
„OrderTotalIndex“ bude založen na sloupci OrderId a také na vynásobení hodnoty sloupce Množství a hodnoty sloupce Cena. Takže jakýkoli dotaz na „OrderId“ a „Quantity*Price“ bude efektivní, protože dotaz bude používat index.
- Pokud jste v příkazu CREATE INDEX zadali klauzuli WHERE, bude index částečným indexem. V tomto případě budou v indexu položky pouze pro řádky, které odpovídají podmínkám v klauzuli WHERE. Například v následujícím indexu:
CREATE INDEX OrderTotalIndexForLargeQuantities ON OrderItems(OrderId, Quantity*Price) WHERE Quantity > 10000;
(Ve výše uvedeném příkladu bude index částečným indexem, protože je specifikována klauzule WHERE. V tomto případě bude index aplikován pouze na ty objednávky, které mají hodnotu množství větší než 10000 XNUMX. Všimněte si, že tento index se nazývá částečný index kvůli klauzuli WHERE, nikoli výrazu, který je v ní použit. Můžete však použít výrazy s normálními indexy.)
- Místo příkazu CREATE INDEX můžete použít příkaz CREATE UNIQUE INDEX, abyste zabránili duplicitním záznamům ve sloupcích, a proto budou všechny hodnoty pro indexovaný sloupec jedinečné.
- Chcete-li odstranit index, použijte příkaz DROP INDEX následovaný názvem indexu, který chcete odstranit.
SQLite spoušť
Úvod do SQLite spoušť
Spouštěče jsou automatické předdefinované operace, které se provádějí, když v databázové tabulce dojde k určité akci. Spouštěč lze definovat tak, aby se spustil vždy, když v tabulce dojde k jedné z následujících akcí:
- INSERT do tabulky.
- DELETE řádky z tabulky.
- AKTUALIZUJTE jeden ze sloupců tabulky.
SQLite podporuje spouštěč FOR EACH ROW, takže předdefinované operace ve spouštěči budou provedeny pro všechny řádky zapojené do akcí, ke kterým došlo v tabulce (ať už jde o vložení, odstranění nebo aktualizaci).
SQLite VYTVOŘIT SPUŠTĚNÍ
Chcete-li vytvořit nový TRIGGER, můžete použít příkaz CREATE TRIGGER takto:
- Po CREATE TRIGGER byste měli zadat název spouštěče.
- Za názvem spouštěče musíte určit, kdy přesně se má název spouštěče spustit. Máte tři možnosti:
- BEFORE – trigger bude proveden před zadaným příkazem INSERT, UPDATE nebo delete.
- After – trigger bude proveden po zadaném příkazu INSERT, UPDATE nebo delete.
- MÍSTO – Nahradí akci, která spustila spouštěč, příkazem uvedeným v TRIGGER. Spouštěč INSTEAD OF nelze použít u tabulek, ale pouze u pohledů.
Spouštěče budou aktivovány (spouštěny) pouze v závislosti na typu příkazu zadaného v příkazu create trigger. Například:
- Aktivační událost BEFORE INSERT bude aktivována (spuštěna) před jakýmkoli příkazem insert.
- Spouštěč PO AKTUALIZACI se aktivuje (spustí) po každém příkazu aktualizace, … a tak dále.
Uvnitř spouštěče se můžete na nově vložené hodnoty odkazovat pomocí klíčového slova „new“. Také můžete odkazovat na odstraněné nebo aktualizované hodnoty pomocí starého klíčového slova. Takto:
- Uvnitř spouštěčů INSERT – lze použít nové klíčové slovo.
- Uvnitř spouštěčů UPDATE – lze použít nová i stará klíčová slova.
- Uvnitř spouštěčů DELETE – lze použít staré klíčové slovo.
Příklad
V následujícím textu vytvoříme spoušť, která se spustí před vložením nového studenta do „Studenti" stůl.
Přihlásí nově vloženého studenta do tabulky “StudentsLog” s automatickým časovým razítkem pro aktuální datum a čas, kdy došlo k příkazu insert. Takto:
Krok 1) Přejděte do adresáře "C:\sqlite“ a spusťte sqlite3.exe.
Krok 2) Otevřít databázi"NávodySampleDB.db“ spuštěním následujícího příkazu:
.open TutorialsSampleDB.db
Krok 3) vytvořit spoušť“InsertIntoStudentTrigger“ Spuštěním následujícího příkazu:
CREATE TRIGGER InsertIntoStudentTrigger
BEFORE INSERT ON Students
BEGIN
INSERT INTO StudentsLog VALUES(new.StudentId, datetime(), 'Insert');
END;
Funkce "čas schůzky()" vám poskytne aktuální datum a čas, kdy došlo k vložení příkazu. Abychom mohli zaznamenat vloženou transakci s automatickými časovými razítky přidanými ke každé transakci.
Příkaz by měl běžet úspěšně a nedostanete žádný výstup:
Spoušť "InsertIntoStudentTrigger“ se spustí pokaždé, když vložíte nového studenta do tabulky studentů. "nový” klíčové slovo odkazuje na hodnoty, které budou vloženy. Například „nové.StudentId” bude ID studenta, které bude vloženo.
Nyní vyzkoušíme, jak se trigger chová, když vložíme nového studenta.
Krok 4) Napište následující příkaz, který vloží nového studenta do tabulky studentů:
INSERT INTO Students VALUES(11, 'guru11', 1, '1999-10-12');
Krok 5) Napište následující příkaz, který vybere všechny řádky z „StudentsLog" stůl:
SELECT * FROM StudentsLog;
Měli byste vidět nový řádek vrácený pro nového studenta, kterého jsme právě vložili:
Tento řádek byl vložen triggerem před vložením nového studenta s ID 11.
V tomto příkladu jsme použili spoušť „ InsertIntoStudentTrigger "vytvořili jsme, abychom zaznamenali všechny transakce vložení do tabulky"StudentsLog“ automaticky. Stejným způsobem můžete protokolovat jakoukoli aktualizaci nebo mazat výpisy.
Prevence nechtěných aktualizací pomocí spouštěčů:
Pomocí spouštěčů BEFORE UPDATE na tabulce můžete zabránit aktualizačním příkazům ve sloupci na základě výrazu.
Příklad
V následujícím příkladu zabráníme jakémukoli aktualizačnímu příkazu aktualizovat sloupec „jméno studenta“ v tabulce Studenti:
Krok 1) Přejděte do adresáře "C:\sqlite“ a spusťte sqlite3.exe.
Krok 2) Otevřít databázi"NávodySampleDB.db“ spuštěním následujícího příkazu:
.open TutorialsSampleDB.db
Krok 3) Vytvořit nový spouštěč “preventUpdateStudentName“na stole”Studenti” spuštěním následujícího příkazu
CREATE TRIGGER preventUpdateStudentName
BEFORE UPDATE OF StudentName ON Students
FOR EACH ROW
BEGIN
SELECT RAISE(ABORT, 'You cannot update studentname');
END;
"VYZDVIHNOUT"příkaz vyvolá chybu s chybovou zprávou" Jméno studenta nelze aktualizovat “, a pak zabrání provedení příkazu aktualizace.
Nyní ověříme, že spouštěč funguje dobře a zabraňuje jakékoli aktualizaci sloupce studentname.
Krok 4) Spusťte následující příkaz aktualizace, který aktualizuje jméno studenta “zvedák" být "Jack1".
UPDATE Students SET StudentName = 'Jack1' WHERE StudentName = 'Jack';
Měli byste dostat chybovou zprávu, kterou jsme specifikovali na spouštěči, říkající, že „Jméno studenta nelze aktualizovat“ takto:
Krok 5) Spusťte následující příkaz, který vybere seznam jmen studentů z tabulky studentů.
SELECT StudentName FROM Students;
Měli byste vidět, že jméno studenta „Jack“ je stále stejné a nemění se:
Shrnutí
Zobrazení, indexy a spouštěče jsou velmi výkonné nástroje pro správu SQLite databáze. Můžete sledovat operace úpravy dat, když k nim dojde v tabulce. Operaci načítání databázových dat můžete také optimalizovat vytvořením indexů.
