SQLite Trigger, visninger og indeks med eksempel

Af: Marcus Allen Marcus Allen
Hours Opdateret

I den daglige brug af SQLite, skal du bruge nogle administrative værktøjer over din database. Du kan også bruge dem til at gøre forespørgsler i databasen mere effektivt ved at oprette indekser, eller mere genbrugelige ved at oprette visninger.

SQLite Specifikation

Visninger minder meget om tabeller. Men visninger er logiske tabeller; de opbevares ikke fysisk som borde. En visning er sammensat af et udvalgt udsagn.

Du kan definere en visning for dine komplekse forespørgsler, og du kan genbruge disse forespørgsler, når du vil, ved at kalde visningen direkte i stedet for at omskrive forespørgslerne igen.

CREATE VIEW-erklæring

For at oprette en visning på en database kan du bruge CREATE VIEW-sætningen efterfulgt af visningsnavnet og derefter sætte den ønskede forespørgsel efter det.

Eksempel: I det følgende eksempel vil vi oprette en visning med navnet "All StudentsView" i eksempeldatabasen "TutorialsSampleDB.db" som følgende:

Trin 1) Åbn Denne computer og naviger til følgende mappe "C:\sqlite"Og derefter åbne"sqlite3.exe"

SQLite Specifikation

Trin 2) Åbn databasen "TutorialsSampleDB.db” ved følgende kommando:

SQLite Specifikation

Trin 3) Følgende er en grundlæggende syntaks for sqlite3-kommandoen til at oprette View

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;

Der bør ikke være noget output fra kommandoen som denne:

SQLite Specifikation

Trin 4) For at sikre, at visningen oprettes, kan du vælge listen over visninger i databasen ved at køre følgende kommando:

SELECT name FROM sqlite_master WHERE type = 'view';

Du skal se udsigten "All StudentsView" returneres:

SQLite Specifikation

Trin 5) Nu er vores visning oprettet, du kan bruge det som en normal tabel noget som dette:

SELECT * FROM AllStudentsView;

Denne kommando vil forespørge på visningen "AllStudents" og vælge alle rækkerne fra den som vist på følgende skærmbillede:

SQLite Specifikation

Midlertidige visninger

Midlertidige visninger er midlertidige for den aktuelle databaseforbindelse, der bruges til at oprette den. Så hvis du lukker databaseforbindelsen vil alle de midlertidige visninger blive slettet automatisk. Midlertidige visninger oprettes ved hjælp af en af ​​følgende kommandoer:

  • OPRET TEMP VISNING, eller
  • OPRET MIDLERTIDIG UDSIGT.

Midlertidige visninger er nyttige, hvis du vil udføre nogle handlinger foreløbig og ikke har brug for, at det er en permanent visning. Så du opretter bare en midlertidig visning og laver derefter din behandling ved hjælp af den visning. Later når du lukker forbindelsen med databasen, slettes den automatisk.

Eksempel:

I det følgende eksempel vil vi åbne en databaseforbindelse og derefter oprette en midlertidig visning.

Derefter lukker vi den forbindelse, og vi vil kontrollere, om den midlertidige visning stadig eksisterer eller ej.

Trin 1) Åbn sqlite3.exe fra mappen "C:\sqlite" som forklaret før.

Trin 2) Åbn en forbindelse til databasen "TutorialsSampleDB.db" ved at køre følgende kommando:

.open TutorialsSampleDB.db

Trin 3) Skriv følgende kommando, der vil oprette en midlertidig visning "All StudentsTempView":

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 Specifikation

Trin 4) Sørg for, at tempvisningen "All StudentsTempView" oprettes ved at køre følgende kommando:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

SQLite Specifikation

Trin 5) Luk sqlite3.exe og åbn den igen.

Trin 6) Åbn en forbindelse til databasen "TutorialsSampleDB.db” ved følgende kommando:

.open TutorialsSampleDB.db

Trin 7) Kør følgende kommando for at få listen over midlertidige visninger oprettet på databasen:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

Du bør ikke se noget output, da den midlertidige visning, vi oprettede, slettes, da vi lukkede databaseforbindelsen i det forrige trin. Ellers, så længe du holder forbindelsen med databasen åben, vil du kunne se den midlertidige visning med data.

SQLite Specifikation

Bemærkninger:

  • Du kan ikke bruge sætningerne INSERT, DELETE eller UPDATE med visninger, kun du kan bruge kommandoen "select from views" som vist i trin 5 i CREATE View-eksemplet.
  • For at slette en VIEW kan du bruge "DROP VIEW"-sætningen:
DROP VIEW AllStudentsView;

For at sikre, at visningen slettes, kan du køre følgende kommando, som giver dig listen over visninger i databasen:

SELECT name FROM sqlite_master WHERE type = 'view';

Du vil ikke finde nogen visninger returneret, da visningen blev slettet, som følger:

SQLite Specifikation

RELATEREDE ARTIKLER

SQLite Indeks

Hvis du har en bog, og du vil søge efter et nøgleord på den bog. Du vil søge efter dette søgeord i bogens indeks. Derefter vil du navigere til sidenummeret for det pågældende søgeord for at læse mere om det pågældende søgeord.

Men hvis der ikke er indeks på den bog eller sidetal, scanner du hele bogen fra begyndelsen til slutningen, indtil du finder det søgeord, du søger efter. Og dette er meget svært, især når du har et indeks og meget langsom proces til at søge efter et søgeord.

Indekser i SQLite (og det samme koncept gælder for andre databasestyringssystemer også) fungerer på samme måde som indekserne, der findes bagerst i bøgerne.

Når du søger efter nogle rækker i en SQLite tabel med søgekriterier, SQLite vil søge på alle rækkerne i tabellen, indtil den finder de rækker, du leder efter, som matcher søgekriterierne. Og den proces bliver meget langsom, når man har større borde.

Indekser vil fremskynde søgeforespørgsler efter data og vil hjælpe med at udføre datahentning fra tabeller. Indekser er defineret i tabelkolonnerne.

Forbedring af ydeevne med indekser:

Indekser kan forbedre ydeevnen for at søge data på en tabel. Når du opretter et indeks på en kolonne, SQLite vil oprette en datastruktur for det indeks, hvor hver feltværdi har en pegepind til hele rækken, hvor værdien hører hjemme.

Hvis du derefter kører en forespørgsel med en søgebetingelse på en kolonne, der er en del af et indeks, SQLite vil først slå op efter værdien på indekset. SQLite vil ikke scanne hele tabellen for det. Så vil den læse det sted, hvor værdien peger på tabelrækken. SQLite vil finde rækken på denne placering og hente den.

Men hvis den kolonne, du søger efter, ikke er en del af et indeks, SQLite vil udføre en scanning for kolonneværdierne for at finde de data, du leder efter. Det vil normalt være en langsommere proces, hvis der ikke er noget indeks.

Forestil dig en bog uden indeks, og du skal søge efter et bestemt ord. Du scanner hele bogen fra første side til sidste side og leder efter det ord. Men hvis du har et indeks over den bog, vil du først lede efter ordet på den. Få sidenummeret, hvor det er placeret, og naviger derefter til det. Hvilket vil være meget hurtigere end at scanne hele bogen fra ende til anden.

SQLite Opret INDEX

For at oprette et indeks på en kolonne, skal du bruge kommandoen CREATE INDEX. Og du bør definere det som følger:

  • Du skal angive navnet på indekset efter CREATE INDEX-kommandoen.
  • Efter navnet på indekset skal du sætte nøgleordet "ON", efterfulgt af tabelnavnet, hvor indekset vil blive oprettet.
  • Derefter listen over kolonnenavne, der bruges til indekset.
  • Du kan bruge et af følgende nøgleord "ASC" eller "DESC" efter et hvilket som helst kolonnenavn for at angive en sorteringsrækkefølge, der bruges til at sortere indeksdataene.

Eksempel:

I det følgende eksempel vil vi oprette et indeks "StudentNameIndex” på elevernes bord i ”Studerende" database som følger:

Trin 1) Naviger til mappen "C:\sqlite" som forklaret før.

Trin 2) Åbn sqlite3.exe.

Trin 3) Åbn databasen "TutorialsSampleDB.db” ved følgende kommando:

.open TutorialsSampleDB.db

Trin 4) Opret et nyt indeks "StudentNameIndex" ved hjælp af følgende kommando:

CREATE INDEX StudentNameIndex ON Students(StudentName);

Du burde ikke se noget output for dette:

SQLite Indeks

Trin 5) For at sikre, at indekset blev oprettet, kan du køre følgende forespørgsel, som giver dig listen over indekser, der er oprettet i tabellen Elever:

PRAGMA index_list(Students);

Du bør se det indeks, vi lige har oprettet, returnerede:

SQLite Indeks

Bemærkninger:

  • Indekser kan oprettes ikke kun baseret på kolonner, men også udtryk. Noget som dette:
CREATE INDEX OrderTotalIndex ON OrderItems(OrderId, Quantity*Price);

"OrderTotalIndex" vil være baseret på OrderId-kolonnen og også på multiplikationen af ​​Quantity-kolonnens værdi og Price-kolonnens værdi. Så enhver forespørgsel efter "OrderId" og "Quantity*Price" vil være effektiv, da forespørgslen vil bruge indekset.

  • Hvis du har angivet et WHERE-udtryk i CREATE INDEX-sætningen, vil indekset være et delvist indeks. I dette tilfælde vil der kun være indgange i indekset for de rækker, der matcher betingelserne i WHERE-sætningen. For eksempel i følgende indeks: 
    CREATE INDEX OrderTotalIndexForLargeQuantities ON OrderItems(OrderId, Quantity*Price)
WHERE Quantity > 10000;

    ( I ovenstående eksempel vil indekset være et delvist indeks, da der er angivet en WHERE-klausul. I dette tilfælde vil indekset kun blive anvendt på de ordrer, der har en mængdeværdi større end 10000. Bemærk, at dette indeks kaldes et delvist indeks. indeks på grund af WHERE-sætningen, ikke det udtryk, der bruges på det. Du kan dog bruge udtrykkene med normale indekser.)

  • Du kan bruge CREATE UNIQUE INDEX-sætningen i stedet for CREATE INDEX for at forhindre duplikerede poster for kolonnerne, og dermed vil alle værdierne for den indekserede kolonne være unikke.
  • For at slette et indeks skal du bruge kommandoen DROP INDEX efterfulgt af indeksnavnet for at slette.

SQLite Udløser

Introduktion til SQLite Udløser

Triggere er automatiske foruddefinerede operationer, der udføres, når en specifik handling finder sted på en databasetabel. En trigger kan defineres til at blive udløst, når en af ​​følgende handlinger sker på en tabel:

  • INDSÆT i en tabel.
  • SLET rækker fra en tabel.
  • OPDATERE en af ​​tabelkolonnerne.

SQLite understøtter FOR EACH ROW trigger, så de foruddefinerede operationer i triggeren vil blive udført for alle de rækker, der er involveret i handlingerne på bordet (uanset om det er indsæt, slet eller opdatering).

SQLite OPRET TRIGGER

For at oprette en ny TRIGGER kan du bruge CREATE TRIGGER-sætningen som følger:

  • Efter CREATE TRIGGER skal du angive et triggernavn.
  • Efter triggernavnet skal du angive, hvornår udløsernavnet præcist skal udføres. Du har tre muligheder:
  • FØR – triggeren vil blive udført før den specificerede INSERT, UPDATE eller delete-sætning.
  • Efter – udløseren vil blive udført efter den specificerede INSERT, UPDATE eller delete-sætning.
  • I STEDET FOR – Det vil erstatte den handling, der skete, der udløste triggeren med den sætning, der er angivet i TRIGGER. I STEDET FOR trigger er ikke anvendelig med tabeller, kun med visninger.
  • Derefter skal du angive typen af ​​handling, udløseren udløses, når det sker. Enten SLET, INDSÆT eller OPDATERE.
  • Du kan vælge et valgfrit kolonnenavn, så udløseren ikke udløses, medmindre handlingen skete på den kolonne.
  • Derefter skal du angive det tabelnavn, som triggeren skal oprettes i.
  • Inde i udløserens brødtekst skal du angive den sætning, der skal udføres for hver række, når udløseren udløses.

    • Triggere vil kun blive aktiveret (udløst) afhængigt af typen af ​​sætningen specificeret på create trigger-kommandoen. For eksempel:

    • BEFORE INSERT-udløseren vil blive aktiveret (udløst) før enhver insert-sætning.
    • AFTER UPDATE-triggeren vil blive aktiveret (udløst) efter enhver opdateringserklæring, ... og så videre.

    Inde i triggeren kan du henvise til de nyligt indsatte værdier ved at bruge nøgleordet "nyt". Du kan også henvise til de slettede eller opdaterede værdier ved at bruge det gamle nøgleord. Som følgende:

    • Inde i INSERT triggere – nyt nøgleord kan bruges.
    • Inde i UPDATE-triggere – nye og gamle søgeord kan bruges.
    • Inde i DELETE-triggere – gamle søgeord kan bruges.

    Eksempel

    I det følgende vil vi oprette en trigger, der udløses, før vi indsætter en ny elev i "Studerende" bord.

    Det vil logge den nyligt indsatte elev ind i tabellen "Studenterlog” med et automatisk tidsstempel for den aktuelle dato, klokkeslæt, hvor insert-sætningen skete. Som følgende:

    Trin 1) Naviger til mappen "C:\sqlite” og kør sqlite3.exe.

    Trin 2) Åbn databasen "TutorialsSampleDB.db" ved at køre følgende kommando:

    .open TutorialsSampleDB.db

    Trin 3) opret triggeren "IndsætIntoStudentTrigger" Ved at køre følgende kommando:

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

    funktionen "dato tid()" vil give dig den aktuelle dato, klokkeslæt, da indsættelseserklæringen skete. Så vi kan logge indsætningstransaktionen med automatiske tidsstempler tilføjet til hver transaktion.

    Kommandoen skal køre med succes, og du får ingen output:

    SQLite Udløser

    Udløseren "IndsætIntoStudentTrigger” udløses hver gang du indsætter en ny elev i elevtabellen. Det "ny” nøgleord refererer til de værdier, der vil blive indsat. F.eks.ny.StudentId” vil være det elev-id, der vil blive indsat.

    Nu vil vi teste, hvordan triggeren opfører sig, når vi indsætter en ny elev.

    Trin 4) Skriv følgende kommando, der vil indsætte en ny elev i elevtabellen:

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

    Trin 5) Skriv følgende kommando, som vil vælge alle rækkerne fra "Studenterlog" bord:

    SELECT * FROM StudentsLog;

    Du skulle se en ny række returneret for den nye elev, vi lige har indsat:

    SQLite Udløser

    Denne række blev indsat af udløseren, før den nye elev med id 11 blev indsat.

    I dette eksempel brugte vi triggeren " IndsætIntoStudentTrigger ” vi oprettede, for at logge eventuelle indsæt transaktioner i tabellen ”Studenterlog” automatisk. På samme måde kan du logge enhver opdatering eller slette udsagn.

    Forebyggelse af utilsigtede opdateringer med triggere:

    Ved at bruge BEFORE UPDATE-udløsere på en tabel kan du forhindre opdateringssætningerne på en kolonne baseret på et udtryk.

    Eksempel

    I det følgende eksempel forhindrer vi enhver opdateringserklæring i at opdatere kolonnen "studentnavn" i tabellen Elever:

    Trin 1) Naviger til mappen "C:\sqlite” og kør sqlite3.exe.

    Trin 2) Åbn databasen "TutorialsSampleDB.db" ved at køre følgende kommando:

    .open TutorialsSampleDB.db

    Trin 3) Opret en ny trigger "preventUpdateStudentName" på bordet "Studerende” ved at køre følgende kommando

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

    Den "HÆVE" kommando vil rejse en fejl med en fejlmeddelelse " Du kan ikke opdatere elevnavn ", og så forhindrer den opdateringssætningen i at køre.

    Nu vil vi verificere, at udløseren fungerer godt, og den forhindrer enhver opdatering af elevnavn-kolonnen.

    Trin 4) Kør følgende opdateringskommando, som vil opdatere elevens navn "Jack" at være "Jack1".

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

    Du skulle få den fejlmeddelelse, vi specificerede på udløseren, og siger, at "Du kan ikke opdatere elevnavn" som følgende:

    SQLite Udløser

    Trin 5) Kør følgende kommando, som vil vælge listen over elevers navne fra elevtabellen.

    SELECT StudentName FROM Students;

    Du skal se, at elevnavnet "Jack" stadig er det samme, og det ændrer sig ikke:

    SQLite Udløser

    Resumé

    Visninger, indekser og triggere er meget kraftfulde værktøjer til at administrere en SQLite database. Du kan spore dataændringshandlingerne, når de sker på en tabel. Du kan også optimere databasens datahentningsoperation ved at oprette indekser.