SQLite Visningar, index och trigger med exempel

I den dagliga användningen av SQLite, behöver du några administrativa verktyg över din databas. Du kan också använda dem för att göra sökningar i databasen mer effektivt genom att skapa index, eller mer återanvändbara genom att skapa vyer.

SQLite Visa

Vyerna påminner mycket om tabeller. Men vyer är logiska tabeller; de lagras inte fysiskt som bord. En vy består av ett urvalsuttryck.

Du kan definiera en vy för dina komplexa frågor, och du kan återanvända dessa frågor när du vill genom att anropa vyn direkt istället för att skriva om frågorna igen.

CREATE VIEW uttalande

För att skapa en vy på en databas kan du använda CREATE VIEW-satsen följt av vynamnet och sedan sätta den fråga du vill ha efter det.

Exempel: I följande exempel skapar vi en vy med namnet "AllStudentsView" i exempeldatabasen "TutorialsSampleDB.db" enligt följande:

Steg 1) Öppna Den här datorn och navigera till följande katalog "C:\sqlite" och öppna sedan "sqlite3.exe":

Steg 2) Öppna databasen "TutorialsSampleDB.db" med följande kommando:

Steg 3) Följande är en grundläggande syntax för kommandot sqlite3 för att skapa vyn

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 ska inte finnas någon utdata från kommandot så här:

Steg 4) För att säkerställa att vyn skapas kan du välja listan med vyer i databasen genom att köra följande kommando:

SELECT name FROM sqlite_master WHERE type = 'view';

Du bör se att vyn "AllStudentsView" returneras:

Steg 5) Nu är vår vy skapad, du kan använda den som en vanlig tabell ungefär så här:

SELECT * FROM AllStudentsView;

Detta kommando kommer att fråga vyn "AllStudents" och välja alla rader från den som visas i följande skärmdump:

Tillfälliga vyer

Tillfälliga vyer är tillfälliga för den aktuella databasanslutningen som används för att skapa den. Om du sedan stänger databasanslutningen kommer alla tillfälliga vyer att raderas automatiskt. Tillfälliga vyer skapas med något av följande kommandon:

• SKAPA TEMP VISNING, eller
• SKAPA TILLFÄLLIG VISNING.

Tillfälliga vyer är användbara om du vill göra vissa operationer för tillfället och inte behöver vara en permanent vy. Så du skapar bara en tillfällig vy och gör sedan din bearbetning med den vyn. Later när du stänger anslutningen till databasen kommer den att raderas automatiskt.

Exempel:

I följande exempel kommer vi att öppna en databasanslutning och sedan skapa en tillfällig vy.

Efter det kommer vi att stänga den anslutningen, och vi kommer att kontrollera om den tillfälliga vyn fortfarande finns eller inte.

Steg 1) Öppna sqlite3.exe från katalogen "C:\sqlite" som förklarats tidigare.

Steg 2) Öppna en anslutning till databasen "TutorialsSampleDB.db" genom att köra följande kommando:

.open TutorialsSampleDB.db

Steg 3) Skriv följande kommando som skapar en temporär vy med namnet "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;

Steg 4) Se till att den temporära vyn "AllStudentsTempView" skapas genom att köra följande kommando:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

Steg 5) Stäng sqlite3.exe och öppna den igen.

Steg 6) Öppna en anslutning till databasen "TutorialsSampleDB.db" med följande kommando:

.open TutorialsSampleDB.db

Steg 7) Kör följande kommando för att få listan över tillfälliga vyer som skapats i databasen:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

Du bör inte se någon utdata eftersom den tillfälliga vyn vi skapade raderas när vi stängde databasanslutningen i föregående steg. Annars, så länge du håller anslutningen till databasen öppen, skulle du kunna se den tillfälliga vyn med data.

Anmärkningar:

• Du kan inte använda satserna INSERT, DELETE eller UPDATE med vyer, bara du kan använda kommandot "select from views" som visas i steg 5 i CREATE View-exemplet.
• För att ta bort en VIEW kan du använda "DROP VIEW"-satsen:

DROP VIEW AllStudentsView;

För att säkerställa att vyn tas bort kan du köra följande kommando som ger dig listan över vyer i databasen:

SELECT name FROM sqlite_master WHERE type = 'view';

Du kommer inte hitta några vyer som returnerades när vyn togs bort, enligt följande:

Förutom att återanvända komplexa frågor med vyer, SQLite hämtar även matchande rader snabbare genom index.

SQLite index

Om du har en bok och du vill söka efter ett nyckelord i den boken. Du kommer att söka efter det nyckelordet i bokens index. Sedan kommer du att navigera till sidnumret för det sökordet för att läsa mer information om det sökordet.

Men om det inte finns något index på den boken eller sidnummer, kommer du att skanna hela boken från början till slutet tills du hittar nyckelordet du söker efter. Och detta är mycket svårt, särskilt när du har ett index och en mycket långsam process för att söka efter ett nyckelord.

Indexerar i SQLite (och samma koncept gäller för andra databashanteringssystem fungerar på samma sätt som indexen som finns på baksidan av böckerna.

När du söker efter några rader i en SQLite tabell med sökkriterier, SQLite kommer att söka på alla rader i tabellen tills den hittar de rader du letar efter som matchar sökkriterierna. Och den processen blir väldigt långsam när man har större bord.

Index kommer att påskynda sökfrågor för data och hjälper till att utföra datahämtning från tabeller. Index definieras i tabellkolumnerna.

Förbättra prestanda med index:

Index kan förbättra prestandan för att söka data i en tabell. När du skapar ett index på en kolumn, SQLite kommer att skapa en datastruktur för det indexet där varje fältvärde har en pekare till hela raden där värdet hör hemma.

Sedan, om du kör en fråga med ett sökvillkor på en kolumn som är en del av ett index, SQLite söker först efter värdet på indexet. SQLite kommer inte att skanna hela tabellen efter det. Sedan kommer den att läsa platsen där värdet pekar på tabellraden. SQLite kommer att lokalisera raden på den platsen och hämta den.

Men om kolumnen du söker efter inte är en del av ett index, SQLite kommer att utföra en skanning efter kolumnvärdena för att hitta de data du letar efter. Det blir vanligtvis en långsammare process om det inte finns något index.

Föreställ dig en bok utan index och du måste söka efter ett specifikt ord. Du kommer att skanna hela boken från första sidan till sista sidan och leta efter det ordet. Men om du har ett register över den boken kommer du att leta efter ordet på den först. Hämta sidnumret där det finns och navigera sedan till det. Vilket kommer att gå mycket snabbare än att skanna hela boken från pärm till pärm.

SQLite SKAPA INDEX

För att skapa ett index på en kolumn bör du använda kommandot CREATE INDEX. Och du bör definiera det så här:

• Du måste ange namnet på indexet efter kommandot CREATE INDEX.
• Efter namnet på indexet måste du sätta nyckelordet "ON", följt av tabellnamnet där indexet kommer att skapas.
• Därefter listan med kolumnnamn som används för indexet.
• Du kan använda ett av följande nyckelord "ASC" eller "DESC" efter valfritt kolumnnamn för att ange en sorteringsordning som används för att ordna indexdata.

Exempel:

I följande exempel skapar vi indexet "StudentNameIndex" i studenttabellen i databasen "Students" enligt följande:

Steg 1) Navigera till mappen "C:\sqlite" som förklarats tidigare.

Steg 2) Öppna sqlite3.exe.

Steg 3) Öppna databasen "TutorialsSampleDB.db" med följande kommando:

.open TutorialsSampleDB.db

Steg 4) Skapa ett nytt index "StudentNameIndex" med följande kommando:

CREATE INDEX StudentNameIndex ON Students(StudentName);

Du bör inte se någon utdata för detta:

Steg 5) För att säkerställa att indexet skapades kan du köra följande fråga, som ger dig listan över index som skapats i tabellen Studenter:

PRAGMA index_list(Students);

Du bör se indexet vi just skapade returnerade:

Anmärkningar:

• Index kan skapas inte bara baserat på kolumner utan också uttryck. Något som det här:

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

"OrderTotalIndex" kommer att baseras på OrderId-kolumnen och även på multiplikationen av kvantitetskolumnens värde och priskolumnen. Så alla frågor för "OrderId" och "Quantity*Price" kommer att vara effektiva eftersom frågan kommer att använda indexet.

• Om du angav en WHERE-sats i CREATE INDEX-satsen kommer indexet att vara ett partiellt index. I det här fallet kommer det att finnas poster i indexet endast för de rader som matchar villkoren i WHERE-satsen. Till exempel i följande index:

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

  (I exemplet ovan kommer indexet att vara ett partiellt index eftersom det finns en WHERE-sats specificerad. I det här fallet kommer indexet endast att tillämpas på de order som har ett kvantitetsvärde som är större än 10000 XNUMX. Observera att detta index kallas en partiell index på grund av WHERE-satsen, inte uttrycket som används på den. Du kan dock använda uttrycken med normala index.)

• Du kan använda CREATE UNIQUE INDEX-satsen istället för CREATE INDEX för att förhindra dubbla poster för kolumnerna och därmed blir alla värden för den indexerade kolumnen unika.
• För att radera ett index, använd kommandot DROP INDEX följt av indexnamnet för att radera.

Medan index gör läsningar snabbare, låter utlösare SQLite reagera automatiskt när data ändras.

SQLite Trigger

Introduktion till SQLite Trigger

Utlösare är automatiska fördefinierade operationer som utförs när en specifik åtgärd inträffar på en databastabell. En utlösare kan definieras så att den aktiveras när någon av följande åtgärder inträffar på en tabell:

• INFOGA i en tabell.
• RADERA rader från en tabell.
• UPPDATERA en av tabellkolumnerna.

SQLite stöder FOR EACH ROW-utlösare så att de fördefinierade operationerna i utlösaren kommer att utföras för alla rader som är involverade i de åtgärder som inträffade på tabellen (oavsett om det är infoga, ta bort eller uppdatera).

SQLite SKAPA TRIGGER

För att skapa en ny TRIGGER kan du använda CREATE TRIGGER-satsen enligt följande:

• Efter CREATE TRIGGER bör du ange ett triggernamn.
• Efter triggernamnet måste du ange när exakt triggernamnet ska exekveras. Du har tre alternativ:
  • BEFORE – utlösaren kommer att exekveras före INSERT-, UPDATE- eller deletesatsen som anges.
  • Efter – utlösaren kommer att exekveras efter INSERT-, UPDATE- eller deletesatsen.
  • I STÄLLET FÖR – Det kommer att ersätta den åtgärd som hände som utlöste triggern med den sats som anges i TRIGGER. I STÄLLET FÖR trigger är inte tillämpligt med tabeller, bara med vyer.
• Sedan måste du ange typen av åtgärd, utlösaren kommer att aktiveras när det händer. Antingen DELETE, INSERT eller UPDATE.
• Du kan välja ett valfritt kolumnnamn så att utlösaren inte aktiveras om inte åtgärden hände på den kolumnen.
• Sedan måste du ange tabellnamnet där triggern ska skapas.
• Inne i utlösarens kropp bör du ange den sats som ska köras för varje rad när utlösaren aktiveras.

Triggers kommer endast att aktiveras (avfyras) beroende på typen av satsen som anges i skapa trigger-kommandot. Till exempel:

• BEFORE INSERT-utlösaren kommer att aktiveras (avfyras) före någon insert-sats.
• AFTER UPDATE-utlösaren kommer att aktiveras (avfyras) efter varje uppdateringssats, ... och så vidare.

Inuti utlösaren kan du referera till de nyligen infogade värdena med nyckelordet "nya". Du kan också referera till de raderade eller uppdaterade värdena med det gamla nyckelordet. Som följande:

• Inuti INSERT triggers – nytt nyckelord kan användas.
• Inuti UPDATE-utlösare – nya och gamla sökord kan användas.
• Inuti DELETE-utlösare – gamla nyckelord kan användas.

Exempelvis

I det följande skapar vi en trigger som utlöses innan en ny student infogas i tabellen "Studenter".

Den nya infogade studenten loggas i tabellen "StudentsLog" med en automatisk tidsstämpel för det aktuella datumet och tiden då insert-satsen inträffade. Enligt följande:

Steg 1) Navigera till katalogen "C:\sqlite" och kör sqlite3.exe.

Steg 2) Öppna databasen "TutorialsSampleDB.db" genom att köra följande kommando:

.open TutorialsSampleDB.db

Steg 3) skapa triggern "InsertIntoStudentTrigger" genom att köra följande kommando:

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

Funktionen "datetime()" ger dig det aktuella datumet och tidsstämpeln när insert-satsen inträffade. Så att vi kan logga insert-transaktionen med automatiska tidsstämplar tillagda till varje transaktion.

Kommandot bör köras framgångsrikt och du får ingen utdata:

Utlösaren ”InsertIntoStudentTrigger” aktiveras varje gång du infogar en ny student i studenttabellen. Nyckelordet ”new” refererar till de värden som ska infogas. Till exempel kommer ”new.StudentId” att vara student-ID:t som ska infogas.

Nu ska vi testa hur triggern beter sig när vi sätter in en ny elev.

Steg 4) Skriv följande kommando som kommer att infoga en ny elev i elevtabellen:

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

Steg 5) Skriv följande kommando som markerar alla rader från tabellen "StudentsLog":

SELECT * FROM StudentsLog;

Du bör se en ny rad returnerad för den nya eleven som vi precis infogade:

Den här raden infogades av utlösaren innan den nya studenten med id 11 infogades.

I det här exemplet använde vi triggern "InsertIntoStudentTrigger" som vi skapade för att logga alla insert-transaktioner i tabellen "StudentsLog" automatiskt. På samma sätt som du kan logga alla uppdateringar eller borttagningar.

Förhindra oavsiktliga uppdateringar med triggers:

Genom att använda BEFORE UPDATE-utlösare i en tabell kan du förhindra uppdateringssatserna i en kolumn baserad på ett uttryck.

Exempelvis

I följande exempel kommer vi att förhindra att någon uppdateringssats uppdaterar kolumnen "studentnamn" i tabellen Studenter:

Steg 1) Navigera till katalogen "C:\sqlite" och kör sqlite3.exe.

Steg 2) Öppna databasen "TutorialsSampleDB.db" genom att köra följande kommando:

.open TutorialsSampleDB.db

Steg 3) Skapa en ny trigger "preventUpdateStudentName" i tabellen "Students" genom att köra följande kommando

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

Kommandot ”RAISE” kommer att generera ett felmeddelande med felmeddelandet ”Du kan inte uppdatera studentnamn” och sedan förhindra att uppdateringskommandot körs.

Nu kommer vi att verifiera att utlösaren fungerar bra och att den förhindrar uppdateringar för kolumnen studentnamn.

Steg 4) Kör följande uppdateringskommando, vilket uppdaterar studentnamnet "Jack" till "Jack1".

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

Du bör få felmeddelandet vi angav på utlösaren, som säger att "Du kan inte uppdatera studentnamnet" enligt följande:

Steg 5) Kör följande kommando, vilket kommer att välja listan med elevnamn från elevtabellen.

SELECT StudentName FROM Students;

Du bör se att elevnamnet "Jack" fortfarande är detsamma och att det inte ändras:

Vanliga frågor