SQLite Trigger, megtekintések és indexelés példával

By: Marcus Allen Marcus Allen
Hours korszerűsített

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 nézetet "AllStudentsView" a minta adatbázisban "TutorialsSampleDB.db” a következőképpen:

Step 1) Nyissa meg a Sajátgépet, és keresse meg a következő könyvtárat:C:\sqlite", majd nyissa meg a "sqlite3.exe"

SQLite Kilátás

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

SQLite Kilátás

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:

SQLite Kilátás

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';

Látnod kell a kilátást"AllStudentsView” visszakerül:

SQLite Kilátás

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:

SQLite Kilátás

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 " könyvtárbólC:\sqlite” ahogy korábban elmagyaráztuk.

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

.open TutorialsSampleDB.db

Step 3) Írja be a következő parancsot, amely ideiglenes nézetet hoz létre "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;

SQLite Kilátás

Step 4) Győződjön meg arról, hogy a hőmérséklet nézet "AllStudentsTempView” a következő parancs futtatásával jön létre:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

SQLite Kilátás

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

Step 6) Nyisson meg egy kapcsolatot az adatbázissal "TutorialsSampleDB.db” 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.

SQLite Kilátás

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:

SQLite Kilátás

KAPCSOLÓDÓ CIKKEK

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 indexet „StudentNameIndex" a diákasztalon a "Diákok” adatbázis az alábbiak szerint:

Step 1) Keresse meg a mappát "C:\sqlite” ahogy korábban elmagyaráztuk.

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

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

.open TutorialsSampleDB.db

Step 4) Hozzon létre egy új indexet "StudentNameIndex” a következő paranccsal:

CREATE INDEX StudentNameIndex ON Students(StudentName);

Ehhez nem kell kimenetet látnia:

SQLite index

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:

SQLite index

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.

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 aktiválási szabályt, amely aktiválódik, mielőtt új tanulót illeszt be a „Diákok" asztal.

    Az újonnan beillesztett tanulót a táblázatba naplózzaStudentsLog” egy automatikus időbélyegzővel az aktuális dátumhoz, és az insert utasításhoz. A következőképpen:

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

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

    .open TutorialsSampleDB.db

    Step 3) hozza létre a triggert "InsertIntoStudentTrigger” 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 funkció "dátum idő()" megadja az aktuális dátum időbélyegzőjét, amikor az insert utasítás megtörtént. Hogy a beszúrt tranzakciót minden tranzakcióhoz hozzáadott automatikus időbélyeggel tudjuk naplózni.

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

    SQLite ravasz

    A ravasz "InsertIntoStudentTrigger” minden alkalommal aktiválódik, amikor új tanulót szúr be a tanulók táblázatába. A "új” kulcsszó a beszúrandó értékekre vonatkozik. Például a „új.StudentId” lesz a beszúrandó diákigazolvány.

    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) Írja be a következő parancsot, amely kijelöli az összes sort a "StudentsLog" asztal:

    SELECT * FROM StudentsLog;

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

    SQLite ravasz

    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 a triggert használtuk InsertIntoStudentTrigger " hoztuk létre, hogy naplózza a tranzakciókat a táblázatba "StudentsLog” automatikusan. Ugyanígy naplózhat bármilyen frissítést vagy törölhet kijelentéseket.

    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áljon a „könyvtárba”C:\sqlite” és futtassa az sqlite3.exe fájlt.

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

    .open TutorialsSampleDB.db

    Step 3) Hozzon létre egy új triggert "preventUpdateStudentName" az asztalon "Diákok” 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;

    AzEMEL" parancs hibát jelez egy hibaüzenettel " A tanuló nevét nem frissítheti ", és akkor 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 frissíti a tanuló nevét "csatlakozó" lenni "Jack1".

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

    Meg kell kapnia a triggerben megadott hibaüzenetet, amely szerint "A tanuló nevét nem frissítheti” a következőképpen:

    SQLite ravasz

    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:

    SQLite ravasz

    Összegzésként

    A nézetek, indexek és triggerek nagyon hatékony eszközök egy SQLite adatbázis. Nyomon követheti az adatmódosítási műveleteket, amikor azok egy táblán történnek. Az adatbázis adatlekérési műveletét indexek létrehozásával is optimalizálhatja.