SQLite Weergaven, index en trigger met voorbeeld

Bij het dagelijks gebruik van SQLite, heeft u enkele administratieve hulpmiddelen voor uw database nodig. U kunt ze ook gebruiken om het uitvoeren van query's in de database efficiënter te maken door indexen te maken, of om deze beter herbruikbaar te maken door weergaven te maken.

SQLite Bekijk

Weergaven lijken sterk op tabellen. Maar Views zijn logische tabellen; ze worden niet fysiek opgeslagen zoals tabellen. Een weergave bestaat uit een select-instructie.

U kunt een weergave definiëren voor uw complexe query's en u kunt deze query's opnieuw gebruiken wanneer u maar wilt door de weergave rechtstreeks aan te roepen in plaats van de query's opnieuw te schrijven.

CREATE VIEW-instructie

Om een ​​weergave voor een database te maken, kunt u de instructie CREATE VIEW gebruiken, gevolgd door de weergavenaam, en daarna de gewenste query plaatsen.

Voorbeeld: In het volgende voorbeeld maken we een weergave met de naam "AllStudentsView" aan in de voorbeelddatabase "TutorialsSampleDB.db" zoals hieronder weergegeven:

Stap 1) Open Deze computer en navigeer naar de volgende map: “C:\sqlite” en open vervolgens “sqlite3.exe”:

Stap 2) Open de database “TutorialsSampleDB.db” met het volgende commando:

Stap 3) Hieronder volgt een basis-syntaxis van de sqlite3-opdracht om de weergave te maken

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;

Er mag geen uitvoer van de opdracht als volgt zijn:

Stap 4) Om er zeker van te zijn dat de weergave wordt gemaakt, kunt u de lijst met weergaven in de database selecteren door de volgende opdracht uit te voeren:

SELECT name FROM sqlite_master WHERE type = 'view';

Als het goed is, wordt de weergave "AllStudentsView" geretourneerd:

Stap 5) Nu onze weergave is gemaakt, kunt u deze als een normale tabel gebruiken, ongeveer als volgt:

SELECT * FROM AllStudentsView;

Met deze opdracht wordt de weergave 'AllStudents' opgevraagd en worden alle rijen geselecteerd, zoals weergegeven in de volgende schermafbeelding:

Tijdelijke weergaven

Tijdelijke weergaven zijn tijdelijk voor de huidige databaseverbinding die is gebruikt om deze te maken. Als u vervolgens de databaseverbinding sluit, worden alle tijdelijke weergaven automatisch verwijderd. Tijdelijke weergaven worden gemaakt met een van de volgende opdrachten:

• MAAK TEMPWEERGAVE, of
• CREËER TIJDELIJK UITZICHT.

Tijdelijke weergaven zijn handig als u tijdelijk wat bewerkingen wilt uitvoeren en het geen permanente weergave hoeft te zijn. U maakt dus gewoon een tijdelijke weergave en voert vervolgens uw verwerking uit met behulp van die weergave. Later wanneer u de verbinding met de database verbreekt, wordt deze automatisch verwijderd.

Voorbeeld:

In het volgende voorbeeld openen we een databaseverbinding en maken we vervolgens een tijdelijke weergave.

Daarna sluiten we die verbinding af en kijken we of de tijdelijke weergave nog bestaat of niet.

Stap 1) Open sqlite3.exe vanuit de map “C:\sqlite”, zoals eerder uitgelegd.

Stap 2) Maak verbinding met de database “TutorialsSampleDB.db” door de volgende opdracht uit te voeren:

.open TutorialsSampleDB.db

Stap 3) Schrijf de volgende opdracht om een ​​tijdelijke weergave met de naam "AllStudentsTempView" aan te maken:

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;

Stap 4) Zorg ervoor dat de tijdelijke weergave "AllStudentsTempView" wordt aangemaakt door de volgende opdracht uit te voeren:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

Stap 5) Sluit sqlite3.exe en open het opnieuw.

Stap 6) Maak verbinding met de database “TutorialsSampleDB.db” met behulp van de volgende opdracht:

.open TutorialsSampleDB.db

Stap 7) Voer de volgende opdracht uit om de lijst met tijdelijke weergaven op te halen die in de database zijn gemaakt:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

U zou geen output moeten zien, omdat de temp view die we hebben gemaakt, is verwijderd toen we de databaseverbinding in de vorige stap sloten. Anders zou u, zolang u de verbinding met de database open houdt, de temporary view met data kunnen zien.

Opmerkingen:

• U kunt de instructies INSERT, DELETE of UPDATE niet gebruiken met views, u kunt alleen de opdracht “select from views” gebruiken, zoals weergegeven in stap 5 in het CREATE View-voorbeeld.
• Om een ​​VIEW te verwijderen, kunt u de instructie “DROP VIEW” gebruiken:

DROP VIEW AllStudentsView;

Om er zeker van te zijn dat de weergave wordt verwijderd, kunt u de volgende opdracht uitvoeren. Deze geeft u een lijst met weergaven in de database:

SELECT name FROM sqlite_master WHERE type = 'view';

Er worden geen weergaven geretourneerd omdat de weergave is verwijderd, zoals hieronder:

Naast het hergebruiken van complexe query's met weergaven, SQLite Het ophalen van overeenkomende rijen gaat ook sneller dankzij indexen.

SQLite Index

Als u een boek heeft en u wilt zoeken op een trefwoord voor dat boek. U zoekt op dat trefwoord in de index van het boek. Vervolgens navigeert u naar het paginanummer voor dat trefwoord om meer informatie over dat trefwoord te lezen.

Als er echter geen index of paginanummers op dat boek staan, scant u het hele boek van begin tot eind totdat u het trefwoord vindt waarnaar u op zoek bent. En dit is erg moeilijk, vooral als u een index hebt en een erg langzaam proces om naar een trefwoord te zoeken.

Indexen binnen SQLite (en hetzelfde concept geldt voor andere databasebeheersystemen ook) werkt op dezelfde manier als de indexen achterin de boeken.

Wanneer u zoekt naar enkele rijen in een SQLite tabel met zoekcriteria, SQLite zal in alle rijen van de tabel zoeken totdat het de rijen vindt waarnaar u zoekt die aan de zoekcriteria voldoen. En dat proces wordt erg traag als je grotere tafels hebt.

Indexen versnellen zoekopdrachten naar gegevens en helpen bij het ophalen van gegevens uit tabellen. Indexen worden gedefinieerd in de tabelkolommen.

Prestaties verbeteren met indexen:

Indexen kunnen de prestaties van het zoeken naar gegevens in een tabel verbeteren. Wanneer u een index op een kolom maakt, SQLite zal een gegevensstructuur voor die index creëren waarbij elke veldwaarde een verwijzing heeft naar de hele rij waar de waarde thuishoort.

Als u vervolgens een query met een zoekvoorwaarde uitvoert op een kolom die deel uitmaakt van een index, SQLite zal eerst de waarde op de index opzoeken. SQLite zal er niet de hele tabel op scannen. Vervolgens wordt de locatie gelezen waar de waarde voor de tabelrij verwijst. SQLite zal de rij op die locatie lokaliseren en ophalen.

Als de kolom waarnaar u zoekt echter geen deel uitmaakt van een index, SQLite voert een scan uit voor de kolomwaarden om de gegevens te vinden waarnaar u op zoek bent. Als er geen index is, zal dit doorgaans een langzamer proces zijn.

Stel je een boek voor zonder index en je moet naar een specifiek woord zoeken. U scant het hele boek, van de eerste tot de laatste pagina, op zoek naar dat woord. Als u echter een index van dat boek heeft, zoekt u eerst naar het woord erop. Zoek het paginanummer op waar het zich bevindt en navigeer er vervolgens naartoe. Dat zal veel sneller zijn dan het hele boek van kaft tot kaft scannen.

SQLite CREËER INDEX

Om een ​​index op een kolom te maken, gebruikt u het commando CREATE INDEX. En je moet het als volgt definiëren:

• U moet de naam van de index opgeven na de opdracht CREATE INDEX.
• Na de naam van de index moet u het trefwoord “ON” plaatsen, gevolgd door de tabelnaam waarin de index zal worden aangemaakt.
• Vervolgens de lijst met kolomnamen die voor de index worden gebruikt.
• U kunt een van de volgende trefwoorden "ASC" of "DESC" gebruiken na een kolomnaam om een ​​sorteervolgorde op te geven die wordt gebruikt om de indexgegevens te ordenen.

Voorbeeld:

In het volgende voorbeeld maken we een index met de naam "StudentNameIndex" aan op de tabel "students" in de database "Students", zoals hieronder weergegeven:

Stap 1) Navigeer naar de map "C:\sqlite" zoals eerder uitgelegd.

Stap 2) Open sqlite3.exe.

Stap 3) Open de database “TutorialsSampleDB.db” met het volgende commando:

.open TutorialsSampleDB.db

Stap 4) Maak een nieuwe index met de naam "StudentNameIndex" aan met behulp van de volgende opdracht:

CREATE INDEX StudentNameIndex ON Students(StudentName);

U zou hiervoor geen uitvoer moeten zien:

Stap 5) Om er zeker van te zijn dat de index is gemaakt, kunt u de volgende query uitvoeren. Deze geeft u een lijst met indexen die zijn gemaakt in de tabel Studenten:

PRAGMA index_list(Students);

Je zou de index moeten zien die we zojuist hebben gemaakt:

Opmerkingen:

• Indexen kunnen niet alleen op basis van kolommen worden gemaakt, maar ook op basis van expressies. Iets zoals dit:

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

De “OrderTotalIndex” zal gebaseerd zijn op de OrderId-kolom en ook op de vermenigvuldiging van de kolomwaarde Hoeveelheid en de kolomwaarde Prijs. Elke zoekopdracht naar “OrderId” en “Quantity*Price” zal dus efficiënt zijn, omdat de zoekopdracht de index gebruikt.

• Als u een WHERE-component in de CREATE INDEX-instructie hebt opgegeven, is de index een gedeeltelijke index. In dit geval staan ​​er alleen vermeldingen in de index voor de rijen die voldoen aan de voorwaarden in de WHERE-component. Bijvoorbeeld in de volgende index:

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

  (In het bovenstaande voorbeeld zal de index een gedeeltelijke index zijn omdat er een WHERE-clausule is opgegeven. In dit geval wordt de index alleen toegepast op bestellingen met een hoeveelheidswaarde groter dan 10000. Houd er rekening mee dat deze index een gedeeltelijke index wordt genoemd. index vanwege de WHERE-clausule, niet de expressie die erop wordt gebruikt. U kunt de expressies echter wel gebruiken met normale indexen.)

• U kunt de instructie CREATE UNIQUE INDEX gebruiken in plaats van de CREATE INDEX om dubbele vermeldingen voor de kolommen te voorkomen, waardoor alle waarden voor de geïndexeerde kolom uniek zullen zijn.
• Om een ​​index te verwijderen, gebruikt u de opdracht DROP INDEX gevolgd door de indexnaam die u wilt verwijderen.

Hoewel indexen het lezen versnellen, laten triggers SQLite Reageer automatisch wanneer de gegevens wijzigen.

SQLite Trigger

Proef de SQLite Trigger

Triggers zijn automatische vooraf gedefinieerde bewerkingen die worden uitgevoerd wanneer een specifieke actie plaatsvindt op een databasetabel. Een trigger kan worden gedefinieerd om te worden geactiveerd wanneer een van de volgende acties plaatsvindt op een tabel:

• INSERT in een tabel.
• VERWIJDER rijen uit een tabel.
• UPDATE een van de tabelkolommen.

SQLite ondersteunt de trigger FOR EACH ROW, zodat de vooraf gedefinieerde bewerkingen in de trigger worden uitgevoerd voor alle rijen die betrokken zijn bij de acties die in de tabel zijn uitgevoerd (ongeacht of het invoegen, verwijderen of bijwerken betreft).

SQLite MAAK TRIGGER

Om een ​​nieuwe TRIGGER te maken, kunt u de instructie CREATE TRIGGER als volgt gebruiken:

• Na CREATE TRIGGER moet u een triggernaam opgeven.
• Na de triggernaam moet u opgeven wanneer de triggernaam precies moet worden uitgevoerd. Je hebt drie opties:
  • BEFORE – de trigger wordt uitgevoerd vóór de opgegeven INSERT-, UPDATE- of verwijderinstructie.
  • Na – de trigger wordt uitgevoerd na de opgegeven INSERT-, UPDATE- of verwijderinstructie.
  • INSTEAD OF – Het vervangt de actie die heeft plaatsgevonden en die de trigger heeft geactiveerd, met de instructie die is opgegeven in de TRIGGER. INSTEAD OF trigger is niet van toepassing op tabellen, alleen op weergaven.
• Vervolgens moet u het type actie opgeven; de trigger wordt geactiveerd wanneer deze plaatsvindt. Ofwel VERWIJDEREN, INVOEGEN of BIJWERKEN.
• U kunt een optionele kolomnaam kiezen, zodat de trigger niet wordt geactiveerd tenzij de actie op die kolom heeft plaatsgevonden.
• Vervolgens moet u de tabelnaam opgeven waarin de trigger wordt gemaakt.
• In de hoofdtekst van de trigger moet u de instructie opgeven die voor elke rij moet worden uitgevoerd wanneer de trigger wordt geactiveerd.

Triggers worden alleen geactiveerd (afgevuurd), afhankelijk van het type instructie dat is opgegeven bij de opdracht create trigger. Bijvoorbeeld:

• De BEFORE INSERT-trigger wordt vóór elke insert-instructie geactiveerd (afgevuurd).
• De AFTER UPDATE-trigger wordt geactiveerd (geactiveerd) na elke update-instructie, ... enzovoort.

Binnen de trigger kunt u verwijzen naar de nieuw ingevoegde waarden met behulp van het trefwoord "new". U kunt ook verwijzen naar de verwijderde of bijgewerkte waarden met behulp van het trefwoord old. Als volgt:

• Binnen INSERT-triggers – er kan een nieuw trefwoord worden gebruikt.
• Binnen UPDATE-triggers kunnen nieuwe en oude trefwoorden worden gebruikt.
• Binnen DELETE-triggers kan een oud trefwoord worden gebruikt.

Voorbeeld

Hieronder maken we een trigger aan die wordt geactiveerd voordat een nieuwe student wordt toegevoegd aan de tabel "Studenten".

De nieuw ingevoegde student wordt geregistreerd in de tabel "StudentsLog" met een automatische tijdstempel voor de huidige datum en tijd waarop de invoegopdracht is uitgevoerd. Zoals hieronder weergegeven:

Stap 1) Navigeer naar de map “C:\sqlite” en voer sqlite3.exe uit.

Stap 2) Open de database “TutorialsSampleDB.db” door het volgende commando uit te voeren:

.open TutorialsSampleDB.db

Stap 3) Maak de trigger "InsertIntoStudentTrigger" aan door de volgende opdracht uit te voeren:

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

De functie "datetime()" geeft de huidige datum en tijdstempel weer van het moment waarop de INSERT-instructie plaatsvond. Zo kunnen we de INSERT-transactie vastleggen met automatisch toegevoegde tijdstempels.

De opdracht zou met succes moeten worden uitgevoerd en u krijgt geen uitvoer:

De trigger "InsertIntoStudentTrigger" wordt geactiveerd telkens wanneer u een nieuwe student in de tabel 'students' invoegt. Het sleutelwoord "new" verwijst naar de waarden die worden ingevoegd. "new.StudentId" is bijvoorbeeld het student-ID dat wordt ingevoegd.

Nu gaan we testen hoe de trigger zich gedraagt ​​als we een nieuwe leerling invoegen.

Stap 4) Schrijf de volgende opdracht om een ​​nieuwe student in de tabel 'studenten' in te voegen:

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

Stap 5) Schrijf de volgende opdracht om alle rijen uit de tabel "StudentsLog" te selecteren:

SELECT * FROM StudentsLog;

Er zou een nieuwe rij moeten verschijnen voor de nieuwe leerling die we zojuist hebben ingevoegd:

Deze rij werd door de trigger ingevoegd voordat de nieuwe leerling met id 11 werd ingevoegd.

In dit voorbeeld hebben we de trigger "InsertIntoStudentTrigger" die we hebben gemaakt gebruikt om alle invoegtransacties in de tabel "StudentsLog" automatisch te registreren. Op dezelfde manier kunt u alle update- of delete-instructies registreren.

Onbedoelde updates voorkomen met triggers:

Met behulp van BEFORE UPDATE-triggers voor een tabel kunt u de update-instructies voor een kolom op basis van een expressie voorkomen.

Voorbeeld

In het volgende voorbeeld voorkomen we dat een update-instructie de kolom ‘studentname’ in de tabel Students bijwerkt:

Stap 1) Navigeer naar de map “C:\sqlite” en voer sqlite3.exe uit.

Stap 2) Open de database “TutorialsSampleDB.db” door het volgende commando uit te voeren:

.open TutorialsSampleDB.db

Stap 3) Maak een nieuwe trigger "preventUpdateStudentName" aan op de tabel "Students" door de volgende opdracht uit te voeren.

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

Het commando "RAISE" genereert een foutmelding met de tekst "U kunt studentnaam niet bijwerken" en voorkomt vervolgens dat de update-instructie wordt uitgevoerd.

Nu zullen we verifiëren dat de trigger goed werkt en dat deze elke update voor de kolom studentnaam verhindert.

Stap 4) Voer de volgende updateopdracht uit. Hiermee wordt de studentnaam "Jack" gewijzigd in "Jack1".

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

Je zou de foutmelding moeten krijgen die we in de trigger hebben gespecificeerd, namelijk "Je kunt de studentnaam niet bijwerken", zoals hieronder weergegeven:

Stap 5) Voer de volgende opdracht uit om de lijst met studentennamen uit de tabel 'studenten' te selecteren.

SELECT StudentName FROM Students;

Je zou moeten zien dat de studentennaam “Jack” nog steeds hetzelfde is en niet verandert:

Veelgestelde vragen