SQLite Trigger, Views & Index med exempel
Marcus Allen
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.
Exempelvis: I följande exempel kommer vi att skapa en vy med namnet "All StudentsView" 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 borde se utsikten "All StudentsView" 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.
Exempelvis:
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 tillfällig vy "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;
Steg 4) Se till att tempvyn "All StudentsTempView" 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:
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.
Exempelvis:
I följande exempel kommer vi att skapa ett index "StudentNameIndex” på elevbordet i ”Studenter" databas 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 "StudentnamnIndex" 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.
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.
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 kommer vi att skapa en utlösare som aktiveras innan vi infogar en ny elev i "Studenter”Tabell.
Det kommer att logga in den nyligen infogade studenten i tabellen "Studentlogg” med en automatisk tidsstämpel för det aktuella datumet och klockan när infogningssatsen inträffade. Som 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 "datum Tid()" ger dig det aktuella datumet och tidsstämpeln när infogningssatsen hände. Så att vi kan logga insättningstransaktionen med automatiska tidsstämplar som läggs till varje transaktion.
Kommandot bör köras framgångsrikt och du får ingen utdata:
Utlösaren "InsertIntoStudentTrigger” avfyras varje gång du infogar en ny elev i elevtabellen. den "ny” nyckelord hänvisar till de värden som kommer att infogas. Till exempel "new.StudentId” kommer att vara student-id som kommer att 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 kommer att välja alla rader från "Studentlogg" tabell:
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 " vi skapade, för att logga eventuella infoga transaktioner i tabellen "Studentlogg” automatiskt. På samma sätt kan du logga vilken uppdatering som helst, eller ta bort uttalanden.
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" på bordet "Studenter" 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;
Den "HÖJA”kommandot kommer att visa ett fel med ett felmeddelande ” Du kan inte uppdatera studentnamn ", och då kommer det att förhindra att uppdateringssatsen 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, som kommer att uppdatera elevens namn "jack" att vara "Jack1".
UPDATE Students SET StudentName = 'Jack1' WHERE StudentName = 'Jack';
Du bör få felmeddelandet som vi angav på avtryckaren, som säger att "Du kan inte uppdatera studentnamn" 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:
Sammanfattning
Visningar, index och utlösare är mycket kraftfulla verktyg för att administrera en SQLite databas. Du kan spåra dataändringsoperationerna när de sker på en tabell. Du kan också optimera datahämtningen genom att skapa index.
