SQLite Visninger, indeks og trigger med eksempel

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 Se

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 opretter vi en visning med navnet "AllStudentsView" i eksempeldatabasen "TutorialsSampleDB.db" som følger:

Trin 1) Åbn Denne computer, og naviger til følgende mappe “C:\sqlite”, og åbn derefter “sqlite3.exe”:

Trin 2) Åbn databasen “TutorialsSampleDB.db” med følgende kommando:

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:

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 burde se, at visningen “AllStudentsView” returneres:

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:

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 tidligere.

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 opretter en midlertidig visning med navnet "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;

Trin 4) Sørg for at den midlertidige visning "AllStudentsTempView" er oprettet ved at køre følgende kommando:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

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

Trin 6) Åbn en forbindelse til databasen “TutorialsSampleDB.db” med 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.

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:

Udover at genbruge komplekse forespørgsler med visninger, SQLite henter også matchende rækker hurtigere via indekser.

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 opretter vi et indeks "StudentNameIndex" på elevtabellen i "Students"-databasen som følger:

Trin 1) Naviger til mappen “C:\sqlite” som forklaret tidligere.

Trin 2) Åbn sqlite3.exe.

Trin 3) Åbn databasen “TutorialsSampleDB.db” med 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:

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:

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.

Mens indekser gør læsninger hurtigere, tillader udløsere SQLite reagerer automatisk, når data ændres.

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 opretter vi en trigger, der aktiveres, før en ny elev indsættes i tabellen "Elever".

Den vil logge den nyligt indsatte elev i tabellen "StudentsLog" med et automatisk tidsstempel for den aktuelle dato og tidspunkt, hvor insert-sætningen fandt sted. Som følger:

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 “InsertIntoStudentTrigger” 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 "datetime()" giver dig det aktuelle dato- og tidsstempel, hvor insert-sætningen fandt sted. Så vi kan logge insert-transaktionen med automatiske tidsstempler tilføjet til hver transaktion.

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

Triggeren "InsertIntoStudentTrigger" aktiveres hver gang du indsætter en ny elev i elevtabellen. Nøgleordet "new" refererer til de værdier, der indsættes. For eksempel vil "new.StudentId" være det elev-id, der indsættes.

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ækker fra tabellen "StudentsLog":

SELECT * FROM StudentsLog;

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

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

I dette eksempel brugte vi triggeren "InsertIntoStudentTrigger", som vi oprettede, til automatisk at logge alle indsættelsestransaktioner i tabellen "StudentsLog". På samme måde kan du logge alle opdateringer eller slettede sætninger.

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" i tabellen "Students" 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;

Kommandoen "RAISE" vil generere en fejl med fejlmeddelelsen "Du kan ikke opdatere studentnavn", og derefter vil den forhindre opdateringssætningen i at udføres.

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 elevnavnet "Jack" til "Jack1".

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

Du burde få den fejlmeddelelse, vi angav på triggeren, der siger "Du kan ikke opdatere elevnavnet", som følger:

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:

Ofte Stillede Spørgsmål