SQLite Nézetek, index és trigger példával

A napi használat során SQLite, szüksége lesz néhány adminisztrációs eszközre az adatbázison keresztül. Segítségükkel hatékonyabbá teheti az adatbázis lekérdezését indexek létrehozásával, vagy újrafelhasználhatóbbá nézetek létrehozásával.

SQLite Kilátás

A nézetek nagyon hasonlítanak a táblázatokhoz. De a nézetek logikai táblázatok; fizikailag nem tárolják őket, mint az asztalokat. A nézet egy select utasításból áll.

Meghatározhat egy nézetet az összetett lekérdezésekhez, és bármikor újra felhasználhatja ezeket a lekérdezéseket úgy, hogy közvetlenül meghívja a nézetet ahelyett, hogy újraírná a lekérdezéseket.

CREATE VIEW utasítás

Nézet létrehozásához egy adatbázisban használhatja a CREATE VIEW utasítást, majd a nézet nevét, majd ezt követően helyezheti el a kívánt lekérdezést.

Példa: A következő példában létrehozunk egy „AllStudentsView” nevű nézetet a „TutorialsSampleDB.db” minta adatbázisban az alábbiak szerint:

Step 1) Nyissa meg a Sajátgép ablakot, keresse meg a következő könyvtárat: „C:\sqlite”, majd nyissa meg az „sqlite3.exe” fájlt:

Step 2) Nyissa meg a „TutorialsSampleDB.db” adatbázist a következő paranccsal:

Step 3) Az alábbiakban bemutatjuk az sqlite3 parancs alapvető szintaxisát a nézet létrehozásához

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;

A parancsnak nem szabad ilyen kimenetet adnia:

Step 4) A nézet létrehozásának biztosításához a következő parancs futtatásával kiválaszthatja a nézetek listáját az adatbázisban:

SELECT name FROM sqlite_master WHERE type = 'view';

A következő nézetet kell látnia, amely visszaadja az „AllStudentsView” értéket:

Step 5) Most létrejött a nézetünk, használhatja normál táblázatként, ilyesmi:

SELECT * FROM AllStudentsView;

Ez a parancs lekérdezi az „AllStudents” nézetet, és kijelöli belőle az összes sort a következő képernyőképen látható módon:

Ideiglenes nézetek

Az ideiglenes nézetek ideiglenesek a létrehozásához használt aktuális adatbázis-kapcsolathoz. Ezután, ha lezárja az adatbázis-kapcsolatot, az összes ideiglenes nézet automatikusan törlődik. Az ideiglenes nézetek a következő parancsok egyikével hozhatók létre:

• TEMP NÉZET LÉTREHOZÁSA, ill
• IDEIGLENES NÉZET LÉTREHOZÁSA.

Az ideiglenes nézetek akkor hasznosak, ha egyelőre szeretne néhány műveletet elvégezni, és nincs szüksége arra, hogy állandó nézet legyen. Tehát csak hozzon létre egy ideiglenes nézetet, majd végezze el a feldolgozást ezzel a nézettel. Later amikor megszakítja a kapcsolatot az adatbázissal, az automatikusan törlődik.

Példa:

A következő példában megnyitunk egy adatbázis-kapcsolatot, majd létrehozunk egy ideiglenes nézetet.

Ezt követően lezárjuk a kapcsolatot, és ellenőrizzük, hogy az ideiglenes nézet még mindig létezik-e vagy sem.

Step 1) Nyissa meg az sqlite3.exe fájlt a „C:\sqlite” könyvtárból a korábban leírtak szerint.

Step 2) Nyisson kapcsolatot a „TutorialsSampleDB.db” adatbázissal a következő parancs futtatásával:

.open TutorialsSampleDB.db

Step 3) Írd meg a következő parancsot, amely létrehoz egy ideiglenes nézetet, az „AllStudentsTempView”-t:

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;

Step 4) Győződjön meg arról, hogy az „AllStudentsTempView” ideiglenes nézet létrejött a következő parancs futtatásával:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

Step 5) Zárja be az sqlite3.exe fájlt, és nyissa meg újra.

Step 6) Nyisson kapcsolatot a „TutorialsSampleDB.db” adatbázissal a következő paranccsal:

.open TutorialsSampleDB.db

Step 7) Futtassa a következő parancsot az adatbázisban létrehozott ideiglenes nézet listájának lekéréséhez:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

Nem kell semmilyen kimenetet látnia, mivel az általunk létrehozott ideiglenes nézet törlődik, amikor az előző lépésben lezártuk az adatbázis-kapcsolatot. Ellenkező esetben, amíg nyitva tartja a kapcsolatot az adatbázissal, láthatja az ideiglenes nézetet adatokkal.

Megjegyzések:

• Nem használhatja az INSERT, DELETE vagy UPDATE utasításokat nézeteknél, csak a „select from view” parancsot használhatja, amint az a nézet LÉTREHOZÁSA példa 5. lépésében látható.
• A VIEW törléséhez használja a „DROP VIEW” utasítást:

DROP VIEW AllStudentsView;

A nézet törlésének biztosításához futtassa a következő parancsot, amely megadja az adatbázisban lévő nézetek listáját:

SELECT name FROM sqlite_master WHERE type = 'view';

A nézet törlésével visszaadott nézet nem jelenik meg, az alábbiak szerint:

Az összetett lekérdezések nézetekkel való újrafelhasználása mellett SQLite indexeken keresztül gyorsabban lekéri az egyező sorokat.

SQLite index

Ha van egy könyve, és keresni szeretne egy kulcsszót a könyvhöz. Erre a kulcsszóra keresni fog a könyv indexében. Ezután a kulcsszóhoz tartozó oldalszámra navigál, ahol további információkat olvashat a kulcsszóról.

Ha azonban nincs index a könyvön vagy oldalszámok, akkor az egész könyvet az elejétől a végéig beolvassa, amíg meg nem találja a keresett kulcsszót. És ez nagyon nehéz, különösen akkor, ha van egy index és nagyon lassú a kulcsszó keresési folyamata.

Indexek be SQLite (és ugyanez a fogalom másra is érvényes adatbázis-kezelő rendszerek is) ugyanúgy működik, mint a könyvek hátulján található indexek.

Amikor néhány sort keres egy SQLite táblázat keresési feltételekkel, SQLite a táblázat összes sorában keresni fog, amíg meg nem találja azokat a sorokat, amelyek megfelelnek a keresési feltételeknek. És ez a folyamat nagyon lelassul, ha nagyobb asztalok vannak.

Az indexek felgyorsítják az adatok keresését, és segítik az adatok táblákból történő lekérését. Az indexek a táblázat oszlopaiban vannak meghatározva.

A teljesítmény javítása indexekkel:

Az indexek javíthatják az adatok táblán történő keresésének teljesítményét. Amikor indexet hoz létre egy oszlopon, SQLite létrehoz egy adatstruktúrát az indexhez, ahol minden mezőértéknek van egy mutatója arra a teljes sorra, amelyhez az érték tartozik.

Ezután, ha olyan lekérdezést futtat le keresési feltétellel egy oszlopban, amely egy index része, SQLite először megkeresi az értéket az indexen. SQLite nem fogja átvizsgálni az egész táblázatot. Ezután beolvassa azt a helyet, ahol a táblázat sorának értéke mutat. SQLite megkeresi a sort az adott helyen, és visszakeresi.

Ha azonban a keresett oszlop nem része egy indexnek, SQLite elvégzi az oszlopértékek beolvasását, hogy megtalálja a keresett adatokat. Általában lassabb folyamat lesz, ha nincs index.

Képzeljen el egy könyvet, amelyen nincs index, és egy adott szóra kell keresnie. Az egész könyvet az első oldaltól az utolsó oldalig átvizsgálja, keresve a szót. Ha azonban van indexe a könyvön, akkor először keresse meg a szót rajta. Keresse meg az oldalszámot, ahol található, majd navigáljon hozzá. Ami sokkal gyorsabb lesz, mint az egész könyvet a borítótól a borítóig beszkennelni.

SQLite INDEX létrehozása

Ha indexet szeretne létrehozni egy oszlopon, használja a CREATE INDEX parancsot. És ezt a következőképpen kell meghatároznia:

• A CREATE INDEX parancs után meg kell adni az index nevét.
• Az index neve után az „ON” kulcsszót kell beírni, majd a táblázat nevét, amelyben az index létrejön.
• Ezután az indexhez használt oszlopnevek listája.
• Bármely oszlopnév után használhatja az alábbi „ASC” vagy „DESC” kulcsszavak egyikét az indexadatok rendezéséhez használt rendezési sorrend meghatározásához.

Példa:

A következő példában létrehozunk egy „StudentNameIndex” indexet a „Students” adatbázisban található students táblán az alábbiak szerint:

Step 1) Navigáljon a „C:\sqlite” mappába a korábban leírtak szerint.

Step 2) Nyissa meg az sqlite3.exe fájlt.

Step 3) Nyissa meg a „TutorialsSampleDB.db” adatbázist a következő paranccsal:

.open TutorialsSampleDB.db

Step 4) Hozz létre egy új indexet, a „StudentNameIndex”-et a következő paranccsal:

CREATE INDEX StudentNameIndex ON Students(StudentName);

Ehhez nem kell kimenetet látnia:

Step 5) Az index létrehozásának biztosításához futtathatja a következő lekérdezést, amely megadja a Diákok táblázatban létrehozott indexek listáját:

PRAGMA index_list(Students);

Látnia kell az imént létrehozott indexet:

Megjegyzések:

• Indexek nem csak oszlopok, hanem kifejezések alapján is létrehozhatók. Valami ilyesmi:

CREATE INDEX OrderTotalIndex ON OrderItems(OrderId, Quantity*Price);

Az „OrderTotalIndex” az OrderId oszlopon, valamint a Mennyiség és az Ár oszlop értékének szorzatán alapul. Tehát a „Rendelésazonosító” és a „Mennyiség*Ár” lekérdezése hatékony lesz, mivel a lekérdezés az indexet használja.

• Ha a CREATE INDEX utasításban WHERE záradékot adott meg, az index részleges index lesz. Ebben az esetben csak a WHERE záradék feltételeinek megfelelő sorokhoz lesznek bejegyzések az indexben. Például a következő indexben:

  CREATE INDEX OrderTotalIndexForLargeQuantities ON OrderItems(OrderId, Quantity*Price)
  WHERE Quantity > 10000;

  (A fenti példában az index egy részindex, mivel WHERE záradék van megadva. Ebben az esetben az index csak azokra a rendelésekre vonatkozik, amelyek mennyiségi értéke nagyobb, mint 10000. Vegye figyelembe, hogy ezt az indexet részlegesnek nevezik. index a WHERE záradék miatt, nem a rajta használt kifejezés. Azonban használhatja a normál indexekkel rendelkező kifejezéseket.)

• Használhatja a CREATE UNIQUE INDEX utasítást a CREATE INDEX helyett, hogy megakadályozza az ismétlődő bejegyzéseket az oszlopokban, és így az indexelt oszlop minden értéke egyedi lesz.
• Egy index törléséhez használja a DROP INDEX parancsot, majd írja be a törölni kívánt index nevét.

Míg az indexek felgyorsítják az olvasást, a triggerek lehetővé teszik SQLite automatikusan reagál, amikor az adatok megváltoznak.

SQLite ravasz

Bevezetés a SQLite ravasz

A triggerek automatikus előre definiált műveletek, amelyek akkor futnak le, amikor egy adott művelet történik egy adatbázistáblán. Meghatározható, hogy az eseményindító aktiválódjon, ha a következő műveletek valamelyike ​​történik egy táblán:

• INSERT egy táblázatba.
• Sorok törlése a táblázatból.
• FRISSÍTÉSE a táblázat egyik oszlopát.

SQLite támogatja az MINDEN SOR triggert, így a triggerben előre meghatározott műveletek végrehajtásra kerülnek a táblán végrehajtott műveletekben érintett összes sorra (legyen szó beszúrásról, törlésről vagy frissítésről).

SQLite TRIGGER LÉTREHOZÁSA

Új TRIGGER létrehozásához használhatja a CREATE TRIGGER utasítást a következők szerint:

• A CREATE TRIGGER után meg kell adnia a trigger nevét.
• A trigger neve után meg kell adni, hogy pontosan mikor kell végrehajtani a trigger nevét. Három lehetőség közül választhat:
  • BEFORE – a trigger a megadott INSERT, UPDATE vagy delete utasítás előtt kerül végrehajtásra.
  • After – a trigger a megadott INSERT, UPDATE vagy delete utasítás után kerül végrehajtásra.
  • HELYETT – A triggert elindító megtörtént műveletet a TRIGGER-ben megadott utasításra cseréli. Az INSTEAD OF trigger nem alkalmazható táblázatoknál, csak nézetek esetén.
• Ezután meg kell adnia a művelet típusát, a trigger aktiválódik, amikor megtörténik. A DELETE, INSERT vagy UPDATE lehet.
• Kiválaszthat egy opcionális oszlopnevet, így az eseményindító csak akkor indul el, ha a művelet az oszlopon történt.
• Ezután meg kell adnia a tábla nevét, amelyben a trigger létrejön.
• Az eseményindító törzsén belül meg kell adnia azt az utasítást, amelyet az eseményindító indításakor minden sorhoz végre kell hajtani.

A triggerek csak a trigger létrehozása parancsban megadott utasítás típusától függően lesznek aktiválva (égetve). Például:

• A BEFORE INSERT trigger minden insert utasítás előtt aktiválódik (kiütődik).
• Az AFTER UPDATE trigger minden frissítési utasítás után aktiválódik (kilövellődik), … és így tovább.

A triggerben az „új” kulcsszó használatával hivatkozhat az újonnan beillesztett értékekre. A törölt vagy frissített értékekre is hivatkozhat a régi kulcsszó használatával. A következőképpen:

• Az INSERT triggerek belsejében – új kulcsszó használható.
• Az UPDATE triggereken belül – új és régi kulcsszavak is használhatók.
• A DELETE triggerek belsejében – régi kulcsszó használható.

Példa

A következőkben létrehozunk egy triggert, amely egy új diák „Diákok” táblázatba való beszúrása előtt aktiválódik.

A rendszer automatikusan bejelentkezik az újonnan beillesztett diákkal a „StudentsLog” táblába, a beszúrási utasítás aktuális dátumának és időpontjának megfelelő időbélyeggel. Az alábbiak szerint:

Step 1) Navigálj a „C:\sqlite” könyvtárba, és futtasd az sqlite3.exe fájlt.

Step 2) Nyissa meg a „TutorialsSampleDB.db” adatbázist a következő parancs futtatásával:

.open TutorialsSampleDB.db

Step 3) Hozza létre az „InsertIntoStudentTrigger” triggert a következő parancs futtatásával:

CREATE TRIGGER InsertIntoStudentTrigger 
       BEFORE INSERT ON Students
BEGIN
  INSERT INTO StudentsLog VALUES(new.StudentId, datetime(), 'Insert');
END;

A „datetime()” függvény megadja az aktuális dátum-időbélyeget, amikor a beszúrási utasítás történt. Így naplózhatjuk a beszúrási tranzakciót, minden tranzakcióhoz automatikus időbélyeggel ellátva.

A parancsnak sikeresen le kell futnia, és nem kap kimenetet:

Az „InsertIntoStudentTrigger” trigger minden alkalommal aktiválódik, amikor új diákot szúrunk be a diákok táblázatba. A „new” kulcsszó a beszúrandó értékekre utal. Például a „new.StudentId” lesz a beszúrandó diákazonosító.

Most teszteljük, hogyan viselkedik a trigger, amikor új tanulót szúrunk be.

Step 4) Írja be a következő parancsot, amely új tanulót szúr be a tanulók táblázatába:

INSERT INTO Students VALUES(11, 'guru11', 1, '1999-10-12');

Step 5) Írd meg a következő parancsot, amely kijelöli a „StudentsLog” tábla összes sorát:

SELECT * FROM StudentsLog;

Egy új sort kell látnia az imént beszúrt új tanulóhoz:

Ezt a sort az eseményindító szúrta be a 11-es azonosítójú új tanuló beszúrása előtt.

Ebben a példában az általunk létrehozott „InsertIntoStudentTrigger” triggert használtuk a „StudentsLog” táblába beszúrt tranzakciók automatikus naplózására. Ugyanígy naplózhatja a frissítési vagy törlési utasításokat is.

A nem kívánt frissítések megelőzése triggerekkel:

A BEFORE UPDATE eseményindítók használatával egy táblán megakadályozhatja, hogy egy oszlopban a frissítési utasítások egy kifejezésen alapuljanak.

Példa

A következő példában megakadályozzuk, hogy minden frissítési utasítás frissítse a Diákok táblázat „tanulónév” oszlopát:

Step 1) Navigálj a „C:\sqlite” könyvtárba, és futtasd az sqlite3.exe fájlt.

Step 2) Nyissa meg a „TutorialsSampleDB.db” adatbázist a következő parancs futtatásával:

.open TutorialsSampleDB.db

Step 3) Hozz létre egy új, „preventUpdateStudentName” nevű triggert a „Students” táblán a következő parancs futtatásával:

CREATE TRIGGER preventUpdateStudentName
BEFORE UPDATE OF StudentName ON Students
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, 'You cannot update studentname');
END;

A „RAISE” parancs hibát jelez „You cannot update studentname” hibaüzenettel, majd megakadályozza a frissítési utasítás végrehajtását.

Most ellenőrizni fogjuk, hogy a trigger jól működik-e, és megakadályozza a diáknév oszlop frissítését.

Step 4) Futtassa a következő frissítési parancsot, amely a „Jack” tanuló nevét „Jack1”-re frissíti.

UPDATE Students SET StudentName = 'Jack1' WHERE StudentName = 'Jack';

A triggeren megadott hibaüzenetet kell kapnod, amely szerint a „You cannot update studentname” (A diáknév nem frissíthető) a következőképpen jelenik meg:

Step 5) Futtassa a következő parancsot, amely kiválasztja a tanulók nevének listáját a tanulók táblázatából.

SELECT StudentName FROM Students;

Látnia kell, hogy a „Jack” tanuló neve továbbra is ugyanaz, és nem változik:

GYIK