SQLite Trigger, visninger og indeks med eksempel

by: Marcus Allen Marcus Allen
Hours oppdatert

I den daglige bruken av SQLite, trenger du noen administrative verktøy over databasen. Du kan også bruke dem til å gjøre spørring i databasen mer effektivt ved å lage indekser, eller mer gjenbrukbare ved å opprette visninger.

SQLite Se

Visninger ligner veldig på tabeller. Men visninger er logiske tabeller; de lagres ikke fysisk som bord. En visning er sammensatt av en utvalgt setning.

Du kan definere en visning for de komplekse spørringene dine, og du kan bruke disse spørringene på nytt når du vil ved å ringe visningen direkte i stedet for å omskrive spørringene på nytt.

CREATE VIEW-uttalelse

For å opprette en visning på en database, kan du bruke CREATE VIEW-setningen etterfulgt av visningsnavnet, og deretter sette søket du ønsker etter det.

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

Trinn 1) Åpne Min datamaskin og naviger til følgende katalog "C:\sqlite" og åpne deretter "sqlite3.exe"

SQLite Se

Trinn 2) Åpne databasen "TutorialsSampleDB.db" med følgende kommando:

SQLite Se

Trinn 3) Følgende er en grunnleggende syntaks for sqlite3-kommandoen for å lage 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;

Det skal ikke være noe utdata fra kommandoen som dette:

SQLite Se

Trinn 4) For å sikre at visningen er opprettet, kan du velge listen over visninger i databasen ved å kjøre følgende kommando:

SELECT name FROM sqlite_master WHERE type = 'view';

Du bør se utsikten "All StudentsView" returneres:

SQLite Se

Trinn 5) Nå er visningen vår opprettet, du kan bruke den som en vanlig tabell noe slikt:

SELECT * FROM AllStudentsView;

Denne kommandoen vil spørre visningen "AllStudents" og velge alle radene fra den som vist i følgende skjermbilde:

SQLite Se

Midlertidige visninger

Midlertidige visninger er midlertidige for den gjeldende databasetilkoblingen som brukes til å opprette den. Så hvis du lukker databasetilkoblingen vil alle de midlertidige visningene bli slettet automatisk. Midlertidige visninger opprettes ved hjelp av en av følgende kommandoer:

  • LAG TEMPVISNING, eller
  • LAG MIDLERTIDIG VISNING.

Midlertidige visninger er nyttige hvis du vil gjøre noen operasjoner for tiden og ikke trenger at det skal være en permanent visning. Så du oppretter bare en midlertidig visning, og gjør deretter behandlingen ved å bruke den visningen. Later når du lukker forbindelsen med databasen, slettes den automatisk.

Eksempel:

I det følgende eksempelet åpner vi en databasetilkobling, og deretter oppretter vi en midlertidig visning.

Etter det vil vi lukke den forbindelsen, og vi vil sjekke om den midlertidige visningen fortsatt eksisterer eller ikke.

Trinn 1) Åpne sqlite3.exe fra katalogen "C:\sqlite" som forklart før.

Trinn 2) Åpne en tilkobling til databasen "TutorialsSampleDB.db" ved å kjøre følgende kommando:

.open TutorialsSampleDB.db

Trinn 3) Skriv følgende kommando som vil lage 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 Se

Trinn 4) Sørg for at tempvisningen "All StudentsTempView" opprettes ved å kjøre følgende kommando:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

SQLite Se

Trinn 5) Lukk sqlite3.exe og åpne den igjen.

Trinn 6) Åpne en tilkobling til databasen "TutorialsSampleDB.db" med følgende kommando:

.open TutorialsSampleDB.db

Trinn 7) Kjør følgende kommando for å få listen over midlertidig visning opprettet på databasen:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

Du bør ikke se noen utdata da den midlertidige visningen vi opprettet slettes når vi lukket databasetilkoblingen i forrige trinn. Ellers, så lenge du holder forbindelsen med databasen åpen, vil du kunne se den midlertidige visningen med data.

SQLite Se

Merknader:

  • Du kan ikke bruke setningene INSERT, DELETE eller UPDATE med visninger, bare du kan bruke kommandoen "velg fra visninger" som vist i trinn 5 i CREATE View-eksemplet.
  • For å slette en VIEW, kan du bruke "DROP VIEW"-setningen:
DROP VIEW AllStudentsView;

For å sikre at visningen blir slettet, kan du kjøre følgende kommando som gir deg listen over visninger i databasen:

SELECT name FROM sqlite_master WHERE type = 'view';

Du vil ikke finne noen visninger returnert da visningen ble slettet, som følger:

SQLite Se

RELATERTE ARTIKLER

SQLite Index

Hvis du har en bok, og du vil søke etter et nøkkelord i den boken. Du vil søke etter det søkeordet i indeksen til boken. Deretter vil du navigere til sidenummeret for det søkeordet for å lese mer informasjon om det søkeordet.

Men hvis det ikke er noen indeks på den boken eller sidetall, vil du skanne hele boken fra begynnelsen til slutten til du finner nøkkelordet du søker etter. Og dette er veldig vanskelig, spesielt når du har en indeks og veldig langsom prosess for å søke etter et nøkkelord.

Indekser i SQLite (og det samme konseptet gjelder for andre databasesystemer fungerer på samme måte som indeksene som finnes bak i bøkene.

Når du søker etter noen rader i en SQLite tabell med søkekriterier, SQLite vil søke på alle radene i tabellen til den finner radene du leter etter som samsvarer med søkekriteriene. Og den prosessen blir veldig treg når du har større bord.

Indekser vil øke hastigheten på søk etter data og vil bidra til å utføre datahenting fra tabeller. Indekser er definert i tabellkolonnene.

Forbedre ytelsen med indekser:

Indekser kan forbedre ytelsen til å søke data på en tabell. Når du oppretter en indeks på en kolonne, SQLite vil lage en datastruktur for den indeksen der hver feltverdi har en peker til hele raden der verdien hører hjemme.

Deretter, hvis du kjører et søk med en søkebetingelse på en kolonne som er en del av en indeks, SQLite vil først slå opp etter verdien på indeksen. SQLite vil ikke skanne hele tabellen for det. Deretter vil den lese plasseringen der verdien peker for tabellraden. SQLite vil finne raden på den plasseringen og hente den.

Men hvis kolonnen du søker etter, ikke er en del av en indeks, SQLite vil utføre en skanning etter kolonneverdiene for å finne dataene du leter etter. Det vil vanligvis være en langsommere prosess hvis det ikke er noen indeks.

Se for deg en bok uten indeks og du må søke etter et spesifikt ord. Du vil skanne hele boken fra første side til siste side på jakt etter det ordet. Men hvis du har en indeks på den boken, vil du først se etter ordet på den. Få sidenummeret der det er plassert, og naviger deretter til det. Noe som vil være mye raskere enn å skanne hele boken fra perm til perm.

SQLite OPPRETT INDEKS

For å lage en indeks på en kolonne, bør du bruke kommandoen CREATE INDEX. Og du bør definere det som følger:

  • Du må spesifisere navnet på indeksen etter CREATE INDEX-kommandoen.
  • Etter navnet på indeksen må du sette nøkkelordet "PÅ", etterfulgt av tabellnavnet der indeksen skal opprettes.
  • Deretter listen over kolonnenavn som brukes til indeksen.
  • Du kan bruke ett av følgende nøkkelord "ASC" eller "DESC" etter et kolonnenavn for å spesifisere en sorteringsrekkefølge som brukes til å bestille indeksdata.

Eksempel:

I følgende eksempel vil vi lage en indeks "StudentNameIndex” på elevbordet i ”Studenter" database som følger:

Trinn 1) Naviger til mappen "C:\sqlite" som forklart før.

Trinn 2) Åpne sqlite3.exe.

Trinn 3) Åpne databasen "TutorialsSampleDB.db" med følgende kommando:

.open TutorialsSampleDB.db

Trinn 4) Opprett en ny indeks "Studentnavnindeks" ved å bruke følgende kommando:

CREATE INDEX StudentNameIndex ON Students(StudentName);

Du skal ikke se noen utdata for dette:

SQLite Index

Trinn 5) For å sikre at indeksen ble opprettet kan du kjøre følgende spørring, som gir deg listen over indekser som er opprettet i tabellen Studenter:

PRAGMA index_list(Students);

Du bør se indeksen vi nettopp opprettet returnerte:

SQLite Index

Merknader:

  • Indekser kan opprettes ikke bare basert på kolonner, men også uttrykk. Noe sånt som dette:
CREATE INDEX OrderTotalIndex ON OrderItems(OrderId, Quantity*Price);

"OrderTotalIndex" vil være basert på OrderId-kolonnen og også på multiplikasjonen av Quantity-kolonneverdien og Pris-kolonnen. Så alle spørringer for "OrderId" og "Quantity*Price" vil være effektive ettersom spørringen vil bruke indeksen.

  • Hvis du spesifiserte en WHERE-klausul i CREATE INDEX-setningen, vil indeksen være en delvis indeks. I dette tilfellet vil det være oppføringer i indeksen for bare radene som samsvarer med betingelsene i WHERE-leddet. For eksempel i følgende indeks: 
    CREATE INDEX OrderTotalIndexForLargeQuantities ON OrderItems(OrderId, Quantity*Price)
WHERE Quantity > 10000;

    ( I eksemplet ovenfor vil indeksen være en delvis indeks ettersom det er spesifisert en WHERE-klausul. I dette tilfellet vil indeksen bare brukes på de bestillingene som har en kvantitetsverdi større enn 10000 XNUMX. Merk at denne indeksen kalles en delvis indeks på grunn av WHERE-klausulen, ikke uttrykket som brukes på den. Du kan imidlertid bruke uttrykkene med normale indekser.)

  • Du kan bruke CREATE UNIQUE INDEX-setningen i stedet for CREATE INDEX for å forhindre dupliserte oppføringer for kolonnene og dermed vil alle verdiene for den indekserte kolonnen være unike.
  • For å slette en indeks, bruk DROP INDEX-kommandoen etterfulgt av indeksnavnet for å slette.

SQLite Avtrekker

Introduksjon til SQLite Avtrekker

Utløsere er automatiske forhåndsdefinerte operasjoner som utføres når en spesifikk handling skjer på en databasetabell. En utløser kan defineres til å utløses når en av følgende handlinger skjer på en tabell:

  • SETTE INN i en tabell.
  • SLETT rader fra en tabell.
  • OPPDATERT en av tabellkolonnene.

SQLite støtter FOR HVER ROW-utløser slik at de forhåndsdefinerte operasjonene i utløseren vil bli utført for alle radene som er involvert i handlingene som skjedde på tabellen (enten det er sett inn, slett eller oppdatering).

SQLite SKAP TRIGGER

For å opprette en ny TRIGGER, kan du bruke CREATE TRIGGER-setningen som følger:

  • Etter CREATE TRIGGER, bør du spesifisere et utløsernavn.
  • Etter utløsernavnet må du spesifisere når nøyaktig utløsernavnet skal utføres. Du har tre alternativer:
  • FØR – utløseren vil bli utført før INSERT-, UPDATE- eller delete-setningen som er spesifisert.
  • Etter – utløseren vil bli utført etter INSERT-, UPDATE- eller delete-setningen som er spesifisert.
  • I STEDET FOR – Det vil erstatte handlingen som skjedde som utløste utløseren med setningen spesifisert i TRIGGEREN. I STEDET FOR trigger er ikke aktuelt med tabeller, kun med visninger.
  • Deretter må du spesifisere typen handling, utløseren utløses når det skjer. Enten SLETT, INSERT eller OPPDATER.
  • Du kan velge et valgfritt kolonnenavn slik at utløseren ikke utløses med mindre handlingen skjedde på den kolonnen.
  • Deretter må du spesifisere tabellnavnet der utløseren skal opprettes.
  • Inne i kroppen til utløseren bør du spesifisere setningen som skal utføres for hver rad når utløseren utløses.

    • Triggere vil bare aktiveres (utløses) avhengig av typen setning som er spesifisert på create trigger-kommandoen. For eksempel:

    • BEFORE INSERT-utløseren vil bli aktivert (utløst) før enhver insert-setning.
    • ETTER UPDATE-utløseren vil bli aktivert (utløst) etter enhver oppdateringssetning, ... og så videre.

    Inne i utløseren kan du referere til de nylig innsatte verdiene ved å bruke nøkkelordet "nye". Du kan også referere til de slettede eller oppdaterte verdiene ved å bruke det gamle nøkkelordet. Som følgende:

    • Inne i INSERT-utløsere – nytt nøkkelord kan brukes.
    • Inne i UPDATE-utløsere – nye og gamle søkeord kan brukes.
    • Inne i DELETE-utløsere – gamle søkeord kan brukes.

    Eksempel

    I det følgende vil vi lage en utløser som utløses før vi setter inn en ny student i "Studenter”Bord.

    Den vil logge den nylig innsatte studenten inn i tabellen "Studentlogg” med et automatisk tidsstempel for gjeldende dato og klokkeslett da insert-setningen skjedde. Som følgende:

    Trinn 1) Naviger til katalogen "C:\sqlite” og kjør sqlite3.exe.

    Trinn 2) Åpne databasen "TutorialsSampleDB.db" ved å kjøre følgende kommando:

    .open TutorialsSampleDB.db

    Trinn 3) lag utløseren "InsertIntoStudentTrigger" Ved å kjøre følgende kommando:

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

    Funksjonen "datetime()" vil gi deg gjeldende dato og klokkeslett da insert-setningen skjedde. Slik at vi kan logge innsettingstransaksjonen med automatiske tidsstempler lagt til hver transaksjon.

    Kommandoen skal kjøre vellykket, og du får ingen utdata:

    SQLite Avtrekker

    Utløseren "InsertIntoStudentTrigger” vil avfyres hver gang du setter inn en ny elev i elevtabellen. «nytt" nøkkelord refererer til verdiene som vil bli satt inn. For eksempel, "new.StudentId” vil være student-ID-en som skal settes inn.

    Nå skal vi teste hvordan triggeren oppfører seg når vi setter inn en ny student.

    Trinn 4) Skriv følgende kommando som vil sette inn en ny elev i elevtabellen:

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

    Trinn 5) Skriv følgende kommando som vil velge alle radene fra "Studentlogg" tabell:

    SELECT * FROM StudentsLog;

    Du skal se en ny rad returnert for den nye studenten vi nettopp la inn:

    SQLite Avtrekker

    Denne raden ble satt inn av utløseren før den nye studenten med id 11 ble satt inn.

    I dette eksemplet brukte vi triggeren " InsertIntoStudentTrigger " vi opprettet, for å logge inn eventuelle transaksjoner i tabellen "Studentlogg" automatisk. På samme måte kan du logge enhver oppdatering, eller slette uttalelser.

    Forhindrer utilsiktede oppdateringer med triggere:

    Ved å bruke FØR UPDATE-utløsere på en tabell, kan du forhindre oppdateringssetningene i en kolonne basert på et uttrykk.

    Eksempel

    I det følgende eksempelet vil vi forhindre at enhver oppdateringssetning oppdaterer "studentnavn"-kolonnen i Studenter-tabellen:

    Trinn 1) Naviger til katalogen "C:\sqlite” og kjør sqlite3.exe.

    Trinn 2) Åpne databasen "TutorialsSampleDB.db" ved å kjøre følgende kommando:

    .open TutorialsSampleDB.db

    Trinn 3) Opprett en ny trigger "preventUpdateStudentName"på bordet"Studenter" ved å kjø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ØY" kommandoen vil gi en feilmelding med en feilmelding " Du kan ikke oppdatere studentnavn ", og da vil det forhindre at oppdateringssetningen kjøres.

    Nå vil vi bekrefte at utløseren fungerer bra, og den forhindrer enhver oppdatering for elevnavn-kolonnen.

    Trinn 4) Kjør følgende oppdateringskommando, som vil oppdatere elevnavnet "jekk"å være"Jack1".

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

    Du bør få feilmeldingen vi spesifiserte på utløseren, som sier at "Du kan ikke oppdatere studentnavn" som følgende:

    SQLite Avtrekker

    Trinn 5) Kjør følgende kommando, som vil velge listen over elevenes navn fra elevtabellen.

    SELECT StudentName FROM Students;

    Du bør se at elevnavnet "Jack" fortsatt er det samme og at det ikke endres:

    SQLite Avtrekker

    Sammendrag

    Visninger, indekser og utløsere er veldig kraftige verktøy for å administrere en SQLite database. Du kan spore dataendringsoperasjonene når de skjer på en tabell. Du kan også optimalisere datainnhentingsoperasjonen ved å lage indekser.