SQLite-index, trigger en weergave met voorbeeld

Bij het dagelijkse gebruik van SQLite heb je een aantal administratieve tools nodig over je database. U kunt ze ook gebruiken om het doorzoeken van de database efficiënter te maken door indexen te maken, of meer herbruikbaar door weergaven te maken.

In deze tutorial leer je -

SQLite-weergave

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

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

CREATE VIEW-instructie

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

Voorbeeld:

In het volgende voorbeeld maken we een View aan met de naam ' AlleStudentenBekijken ' in de voorbeelddatabase ' ZelfstudiesSampleDB.db ' als volgt:

Stap 1) Open Mijn Computer en navigeer naar de volgende map ' C:sqlite ' en dan openen ' sqlite3.exe ':

Stap 2) Open de databank ' ZelfstudiesSampleDB.db ' door het volgende commando:

Stap 3) Hieronder volgt een basissyntaxis van de opdracht sqlite3 om de weergave |__+_| . te maken

Er mag geen uitvoer van de opdracht zijn zoals deze:

Stap 4) Om ervoor te zorgen dat de weergave wordt gemaakt, kunt u de lijst met weergaven in de database selecteren door de volgende opdracht uit te voeren: |_+_|

Je zou het uitzicht moeten zien ' AlleStudentenBekijken ' wordt geretourneerd:

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

Deze opdracht zal de weergave 'AllStudents' opvragen en alle rijen eruit selecteren, 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 TEMP VIEW, of
  • MAAK TIJDELIJK UITZICHT.

Tijdelijke weergaven zijn handig als u op dat moment enkele bewerkingen wilt uitvoeren en het niet nodig hebt om een ​​permanente weergave te zijn. U maakt dus gewoon een tijdelijke weergave en voert vervolgens uw verwerking uit met die weergave. Wanneer u later de verbinding met de database verbreekt, wordt deze automatisch verwijderd.

Voorbeeld:

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

Daarna sluiten we die verbinding, 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) Open een verbinding met de database ' ZelfstudiesSampleDB.db ' door het volgende commando uit te voeren: |__+_|

Stap 3) Schrijf de volgende opdracht om een ​​tijdelijke weergave te maken ' AllStudentsTempView ':

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;

Stap 4) Zorg ervoor dat de tijdelijke weergave ' AllStudentsTempView ' wordt gemaakt door het volgende commando uit te voeren: |_+_|

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

Stap 6) Open een verbinding met de database ' ZelfstudiesSampleDB.db ' door het volgende commando: |__+_|

Stap 7) Voer de volgende opdracht uit om de lijst met tijdelijke weergaven te krijgen die in de database zijn gemaakt: |_+_|

U zou geen uitvoer moeten zien, omdat de tijdelijke weergave 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 geopend houdt, de tijdelijke weergave met gegevens 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 getoond in stap 5 in het CREATE View-voorbeeld.
  • Om een ​​VIEW te verwijderen, kunt u het 'DROP VIEW'-statement gebruiken:
SELECT name FROM sqlite_master WHERE type = 'view';

Om ervoor te zorgen dat de weergave wordt verwijderd, kunt u de volgende opdracht uitvoeren die u de lijst met weergaven in de database geeft: |__+_|

U zult zien dat er geen weergaven zijn geretourneerd omdat de weergave is verwijderd, als volgt:

SQLite-index

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

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

Indexen in SQLite (en hetzelfde concept geldt ook voor andere databasebeheersystemen) werken op dezelfde manier als de indexen achter in de boeken.

Wanneer u zoekt naar enkele rijen in een SQLite-tabel met zoekcriteria, zoekt SQLite in alle rijen van de tabel totdat het de rijen vindt die u zoekt die overeenkomen met de zoekcriteria. 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 op 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, maakt SQLite een gegevensstructuur voor die index waarbij elke veldwaarde een verwijzing heeft naar de hele rij waar de waarde thuishoort.

Als u vervolgens een query uitvoert met een zoekvoorwaarde op een kolom die deel uitmaakt van een index, zoekt SQLite eerst naar de waarde in de index. SQLite zal er niet de hele tabel op scannen. Dan leest het de locatie waar de waarde voor de tabelrij staat. SQLite zal de rij op die locatie lokaliseren en ophalen.

Als de kolom die u zoekt echter geen deel uitmaakt van een index, zal SQLite een scan uitvoeren op de kolomwaarden om de gegevens te vinden die u zoekt. Het zal meestal een langzamer proces zijn als er geen index is.

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

SQLite MAAK INDEX

Om een ​​index op een kolom te maken, moet u het commando CREATE INDEX gebruiken. En je zou het als volgt moeten 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.
  • Dan de lijst met kolomnamen die voor de index worden gebruikt.
  • U kunt een van de volgende trefwoorden 'ASC' of 'DESC' achter elke kolomnaam gebruiken om een ​​sorteervolgorde op te geven die wordt gebruikt om de indexgegevens te ordenen.

Voorbeeld:

In het volgende voorbeeld zullen we een index maken 'StudentNaamIndex' ' op de studententafel in de ' studenten ' database als volgt:

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

Stap 2) Open sqlite3.exe.

Stap 3) Open de databank ' ZelfstudiesSampleDB.db ' door het volgende commando: |__+_|

Stap 4) Maak een nieuwe index ' StudentNaamIndex ' met het volgende commando: |__+_|

U zou hiervoor geen uitvoer moeten zien:

Stap 5) Om ervoor te zorgen dat de index is gemaakt, kunt u de volgende query uitvoeren, die u de lijst met indexen geeft die in de tabel Studenten zijn gemaakt: |__+_|

Je zou moeten zien dat de index die we zojuist hebben gemaakt, is geretourneerd:

Opmerkingen:

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

De 'OrderTotalIndex' wordt gebaseerd op de OrderId-kolom en ook op de vermenigvuldiging van de kolomwaarde Hoeveelheid en de kolomwaarde Prijs. Dus elke query voor 'OrderId' en 'Quantity*Price' zal efficiënt zijn omdat de query de index zal gebruiken.

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

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

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

SQLite-trigger

Inleiding tot 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 op een tafel plaatsvindt:

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

SQLite ondersteunt VOOR ELKE RIJ-trigger, zodat de vooraf gedefinieerde bewerkingen in de trigger worden uitgevoerd voor alle rijen die betrokken zijn bij de acties die in de tabel hebben plaatsgevonden (of het nu gaat om invoegen, verwijderen of bijwerken).

SQLite TRIGGER MAKEN

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

  • Na de CREATE TRIGGER moet u een triggernaam opgeven.
  • Na de triggernaam moet u specificeren wanneer de triggernaam precies moet worden uitgevoerd. Je hebt drie opties:
    • BEFORE – de trigger wordt uitgevoerd vóór de opgegeven INSERT-, UPDATE- of delete-instructie.
    • After – de trigger wordt uitgevoerd na de INSERT-, UPDATE- of de opgegeven delete-opdracht.
    • IN PLAATS VAN - Het vervangt de actie die is gebeurd die de trigger heeft geactiveerd door de verklaring die is opgegeven in de TRIGGER. INSTEAD OF trigger is niet van toepassing bij tabellen, alleen bij views.
  • Vervolgens moet u het type actie specificeren, de trigger wordt geactiveerd wanneer het gebeurt. Ofwel VERWIJDEREN, INVOEREN 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 geactiveerd (afgevuurd) vóór een insert-instructie.
  • De AFTER UPDATE-trigger wordt geactiveerd (afgevuurd) na een update-instructie, ... enzovoort.

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

  • Binnen INSERT-triggers - nieuw trefwoord kan worden gebruikt.
  • Binnen UPDATE-triggers - nieuwe en oude zoekwoorden kunnen worden gebruikt.
  • Binnen DELETE-triggers - oud trefwoord kan worden gebruikt.

Voorbeeld

In het volgende zullen we een trigger maken die wordt geactiveerd voordat een nieuwe student wordt ingevoegd in de ' studenten ' tafel.

Het zal de nieuw ingevoegde student in de tabel loggen ' StudentenLog ' met een automatisch tijdstempel voor de huidige datum en tijd waarop de insert-instructie plaatsvond. Als volgt:

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

Stap 2) Open de databank ' ZelfstudiesSampleDB.db ' door het volgende commando uit te voeren: |__+_|

Stap 3) maak de trigger ' InvoegenInStudentTrigger ' Door het volgende commando uit te voeren: |__+_|

De functie 'datum Tijd ()' geeft u de huidige datum en tijdstempel wanneer de insert-instructie plaatsvond. Zodat we de invoegtransactie kunnen loggen met automatische tijdstempels toegevoegd aan elke transactie.

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

De trekker ' InvoegenInStudentTrigger ' wordt geactiveerd telkens wanneer u een nieuwe leerling in de leerlingentabel plaatst. De ' nieuwe ' trefwoord verwijst naar de waarden die worden ingevoegd. Bijvoorbeeld de ' new.StudentId ' is de student-ID die wordt ingevoegd.

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

Stap 4) Schrijf het volgende commando om een ​​nieuwe leerling in de leerlingentabel in te voegen: |_+_|

Stap 5) Schrijf de volgende opdracht die alle rijen uit de ' StudentenLog ' tabel: |_+_|

Je zou een nieuwe rij moeten zien geretourneerd voor de nieuwe student die we zojuist hebben ingevoegd:

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

In dit voorbeeld gebruikten we de trigger ' InvoegenInStudentTrigger ' we hebben gemaakt om eventuele invoegtransacties in de tabel te loggen ' StudentenLog ' automatisch. Op dezelfde manier kunt u elke update loggen of verklaringen verwijderen.

Onbedoelde updates voorkomen met triggers:

Door BEFORE UPDATE-triggers voor een tabel te gebruiken, kunt u de update-instructies voor een kolom op basis van een expressie voorkomen.

Voorbeeld

In het volgende voorbeeld voorkomen we dat een update-statement de kolom 'studentnaam' in de tabel Studenten bijwerkt:

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

Stap 2) Open de databank ' ZelfstudiesSampleDB.db ' door het volgende commando uit te voeren: |__+_|

Stap 3) Maak een nieuwe trigger ' voorkomenUpdateStudentNaam ' op de tafel ' studenten ' door het volgende commando uit te voeren |__+_|

De ' SALARISVERHOGING ' commando zal een foutmelding geven met een foutmelding ' Je kunt studentnaam niet bijwerken ', en dan zal het voorkomen dat de update-instructie wordt uitgevoerd.

Nu gaan we controleren of de trigger goed werkt, en het voorkomt elke update voor de kolom studentnaam.

Stap 4) Voer de volgende update-opdracht uit, waarmee de studentnaam wordt bijgewerkt ' Jack ' zijn ' Jack1 '.

.open TutorialsSampleDB.db

U zou de foutmelding moeten krijgen die we op de trigger hebben opgegeven, met de melding dat ' Je kunt studentnaam niet bijwerken ' als volgt:

Stap 5) Voer de volgende opdracht uit, waarmee de lijst met namen van studenten uit de tabel met studenten wordt geselecteerd. |__+_|

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

Samenvatting:

Weergaven, indexen en triggers zijn zeer krachtige hulpmiddelen voor het beheren van een SQLite-database. U kunt de bewerkingen voor het wijzigen van gegevens volgen wanneer ze op een tabel plaatsvinden. U kunt het ophalen van databasegegevens ook optimaliseren door indexen te maken.