SQLite Trigger, Ansichten und Index mit Beispiel
Markus Allen
Im täglichen Gebrauch von SQLite, benötigen Sie einige Verwaltungstools für Ihre Datenbank. Sie können sie auch verwenden, um die Abfrage der Datenbank durch die Erstellung von Indizes effizienter zu gestalten oder durch die Erstellung von Ansichten wiederverwendbar zu machen.
SQLite Anzeigen
Ansichten sind Tabellen sehr ähnlich. Aber Ansichten sind logische Tabellen; Sie werden nicht wie Tabellen physisch gespeichert. Eine Ansicht besteht aus einer SELECT-Anweisung.
Sie können eine Ansicht für Ihre komplexen Abfragen definieren und diese Abfragen jederzeit wiederverwenden, indem Sie die Ansicht direkt aufrufen, anstatt die Abfragen erneut zu schreiben.
CREATE VIEW-Anweisung
Um eine Ansicht für eine Datenbank zu erstellen, können Sie die CREATE VIEW-Anweisung gefolgt vom Namen der Ansicht verwenden und anschließend die gewünschte Abfrage einfügen.
Ejemplo: Im folgenden Beispiel erstellen wir eine Ansicht mit dem Namen „AllStudentsView” in der Beispieldatenbank „TutorialsSampleDB.db“ wie folgt:
Schritt 1) Öffnen Sie „Arbeitsplatz“ und navigieren Sie zum folgenden Verzeichnis „C:\sqlite"Und dann öffnen"sqlite3.exe"
Schritt 2) Öffnen Sie die Datenbank“TutorialsSampleDB.db“ durch den folgenden Befehl:
Schritt 3) Nachfolgend finden Sie eine grundlegende Syntax des SQLite3-Befehls zum Erstellen der Ansicht
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;
Es sollte keine Ausgabe des Befehls wie diese geben:
Schritt 4) Um sicherzustellen, dass die Ansicht erstellt wird, können Sie die Liste der Ansichten in der Datenbank auswählen, indem Sie den folgenden Befehl ausführen:
SELECT name FROM sqlite_master WHERE type = 'view';
Sie sollten die Ansicht sehen „AllStudentsView" ist zurück gekommen:
Schritt 5) Jetzt ist unsere Ansicht erstellt, Sie können sie als normale Tabelle verwenden, etwa so:
SELECT * FROM AllStudentsView;
Dieser Befehl fragt die Ansicht „AllStudents“ ab und wählt alle Zeilen daraus aus, wie im folgenden Screenshot gezeigt:
Temporäre Ansichten
Temporäre Ansichten sind für die aktuelle Datenbankverbindung, die zu ihrer Erstellung verwendet wurde, temporär. Wenn Sie dann die Datenbankverbindung schließen, werden alle temporären Ansichten automatisch gelöscht. Temporäre Ansichten werden mit einem der folgenden Befehle erstellt:
- TEMP-ANSICHT ERSTELLEN, oder
- VORÜBERGEHENDE ANSICHT ERSTELLEN.
Temporäre Ansichten sind nützlich, wenn Sie vorübergehend einige Vorgänge ausführen möchten und keine permanente Ansicht benötigen. Sie erstellen also einfach eine temporäre Ansicht und führen dann Ihre Verarbeitung mithilfe dieser Ansicht durch. Later Wenn Sie die Verbindung zur Datenbank schließen, wird diese automatisch gelöscht.
Ejemplo:
Im folgenden Beispiel öffnen wir eine Datenbankverbindung und erstellen dann eine temporäre Ansicht.
Danach schließen wir diese Verbindung und prüfen, ob die temporäre Ansicht noch existiert oder nicht.
Schritt 1) Öffnen Sie sqlite3.exe aus dem Verzeichnis „C:\sqlite“, wie bereits erklärt.
Schritt 2) Öffnen Sie eine Verbindung zur Datenbank“TutorialsSampleDB.db“, indem Sie den folgenden Befehl ausführen:
.open TutorialsSampleDB.db
Schritt 3) Schreiben Sie den folgenden Befehl, der eine temporäre Ansicht erstellt:AlleStudentenTempView"
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;
Schritt 4) Stellen Sie sicher, dass die temporäre Ansicht „AlleStudentenTempView“ wird durch Ausführen des folgenden Befehls erstellt:
SELECT name FROM sqlite_temp_master WHERE type = 'view';
Schritt 5) Schließen Sie sqlite3.exe und öffnen Sie es erneut.
Schritt 6) Öffnen Sie eine Verbindung zur Datenbank“TutorialsSampleDB.db“ durch den folgenden Befehl:
.open TutorialsSampleDB.db
Schritt 7) Führen Sie den folgenden Befehl aus, um die Liste der in der Datenbank erstellten temporären Ansichten abzurufen:
SELECT name FROM sqlite_temp_master WHERE type = 'view';
Sie sollten keine Ausgabe sehen, da die temporäre Ansicht, die wir erstellt haben, gelöscht wird, als wir im vorherigen Schritt die Datenbankverbindung geschlossen haben. Andernfalls können Sie die temporäre Ansicht mit Daten sehen, solange Sie die Verbindung mit der Datenbank geöffnet halten.
Anmerkungen:
- Sie können die Anweisungen INSERT, DELETE oder UPDATE nicht mit Ansichten verwenden, sondern nur den Befehl „Aus Ansichten auswählen“, wie in Schritt 5 im Beispiel CREATE View gezeigt.
- Um eine VIEW zu löschen, können Sie die „DROP VIEW“-Anweisung verwenden:
DROP VIEW AllStudentsView;
Um sicherzustellen, dass die Ansicht gelöscht wird, können Sie den folgenden Befehl ausführen, der Ihnen die Liste der Ansichten in der Datenbank anzeigt:
SELECT name FROM sqlite_master WHERE type = 'view';
Es werden keine Ansichten zurückgegeben, da die Ansicht wie folgt gelöscht wurde:
SQLite Index
Wenn Sie ein Buch haben und nach einem Schlüsselwort für dieses Buch suchen möchten. Sie werden im Index des Buches nach diesem Schlüsselwort suchen. Anschließend navigieren Sie zur Seitenzahl für dieses Schlüsselwort, um weitere Informationen zu diesem Schlüsselwort zu lesen.
Wenn es jedoch weder einen Index noch Seitenzahlen für das Buch gibt, müssen Sie das ganze Buch von Anfang bis Ende durchsehen, bis Sie das gesuchte Schlüsselwort finden. Und das ist sehr schwierig, insbesondere wenn Sie einen Index haben und die Suche nach einem Schlüsselwort sehr langsam ist.
Indizes in SQLite (Und das gleiche Konzept gilt auch für andere Datenbankmanagementsystem (auch) funktioniert auf die gleiche Weise wie die Verzeichnisse im hinteren Teil der Bücher.
Wenn Sie nach einigen Zeilen in einem suchen SQLite Tabelle mit Suchkriterien, SQLite durchsucht alle Zeilen der Tabelle, bis die gesuchten Zeilen gefunden werden, die den Suchkriterien entsprechen. Und dieser Prozess wird sehr langsam, wenn Sie größere Tabellen haben.
Indizes beschleunigen Suchabfragen nach Daten und helfen beim Abrufen von Daten aus Tabellen. Indizes werden für die Tabellenspalten definiert.
Leistungsverbesserung mit Indizes:
Indizes können die Leistung der Datensuche in einer Tabelle verbessern. Wenn Sie einen Index für eine Spalte erstellen, SQLite erstellt eine Datenstruktur für diesen Index, in der jeder Feldwert einen Zeiger auf die gesamte Zeile hat, zu der der Wert gehört.
Wenn Sie dann eine Abfrage mit einer Suchbedingung für eine Spalte ausführen, die Teil eines Index ist, SQLite sucht zunächst im Index nach dem Wert. SQLite wird nicht die gesamte Tabelle danach durchsuchen. Dann wird die Stelle gelesen, an der der Wert für die Tabellenzeile steht. SQLite sucht die Zeile an dieser Stelle und ruft sie ab.
Wenn die gesuchte Spalte jedoch nicht Teil eines Index ist, SQLite führt einen Scan nach den Spaltenwerten durch, um die gesuchten Daten zu finden. Wenn kein Index vorhanden ist, ist der Prozess normalerweise langsamer.
Stellen Sie sich ein Buch ohne Index vor und Sie müssen nach einem bestimmten Wort suchen. Sie durchsuchen das gesamte Buch von der ersten bis zur letzten Seite und suchen nach diesem Wort. Wenn Sie jedoch einen Index zu diesem Buch haben, suchen Sie zuerst nach dem Wort darin. Ermitteln Sie die Seitenzahl dort, wo sie sich befindet, und navigieren Sie dann dorthin. Das geht viel schneller, als das ganze Buch von vorne bis hinten zu scannen.
SQLite INDEX ERSTELLEN
Um einen Index für eine Spalte zu erstellen, sollten Sie den Befehl CREATE INDEX verwenden. Und Sie sollten es wie folgt definieren:
- Sie müssen den Namen des Index nach dem CREATE INDEX-Befehl angeben.
- Nach dem Namen des Index müssen Sie das Schlüsselwort „ON“ einfügen, gefolgt vom Namen der Tabelle, in der der Index erstellt wird.
- Dann die Liste der Spaltennamen, die für den Index verwendet werden.
- Sie können nach jedem Spaltennamen eines der folgenden Schlüsselwörter „ASC“ oder „DESC“ verwenden, um eine Sortierreihenfolge anzugeben, die zum Sortieren der Indexdaten verwendet wird.
Ejemplo:
Im folgenden Beispiel erstellen wir einen Index „StudentenNameIndex“ auf dem Schülertisch im „Die Kursteilnehmer“-Datenbank wie folgt:
Schritt 1) Navigieren Sie zum Ordner „C:\sqlite“, wie bereits erklärt.
Schritt 2) Öffnen Sie sqlite3.exe.
Schritt 3) Öffnen Sie die Datenbank“TutorialsSampleDB.db“ durch den folgenden Befehl:
.open TutorialsSampleDB.db
Schritt 4) Erstellen Sie einen neuen Index „StudentNameIndex“ mit dem folgenden Befehl:
CREATE INDEX StudentNameIndex ON Students(StudentName);
Hierfür sollte keine Ausgabe angezeigt werden:
Schritt 5) Um sicherzustellen, dass der Index erstellt wurde, können Sie die folgende Abfrage ausführen, die Ihnen die Liste der in der Tabelle „Students“ erstellten Indizes liefert:
PRAGMA index_list(Students);
Sie sollten sehen, dass der Index, den wir gerade erstellt haben, zurückgegeben wurde:
Anmerkungen:
- Indizes können nicht nur basierend auf Spalten, sondern auch auf Ausdrücken erstellt werden. Etwas wie das:
CREATE INDEX OrderTotalIndex ON OrderItems(OrderId, Quantity*Price);
Der „OrderTotalIndex“ basiert auf der Spalte „OrderId“ und auch auf der Multiplikation des Spaltenwerts „Menge“ und des Spaltenwerts „Preis“. Daher ist jede Abfrage nach „OrderId“ und „Quantity*Price“ effizient, da die Abfrage den Index verwendet.
- Wenn Sie in der CREATE INDEX-Anweisung eine WHERE-Klausel angegeben haben, handelt es sich bei dem Index um einen Teilindex. In diesem Fall enthält der Index nur Einträge für die Zeilen, die den Bedingungen in der WHERE-Klausel entsprechen. Beispielsweise im folgenden Index:
CREATE INDEX OrderTotalIndexForLargeQuantities ON OrderItems(OrderId, Quantity*Price) WHERE Quantity > 10000;
(Im obigen Beispiel handelt es sich bei dem Index um einen Teilindex, da eine WHERE-Klausel angegeben ist. In diesem Fall wird der Index nur auf die Bestellungen angewendet, deren Mengenwert größer als 10000 ist. Beachten Sie, dass dieser Index als Teilindex bezeichnet wird Index aufgrund der WHERE-Klausel, nicht des darauf verwendeten Ausdrucks. Sie können die Ausdrücke jedoch mit normalen Indizes verwenden.)
- Sie können die CREATE UNIQUE INDEX-Anweisung anstelle von CREATE INDEX verwenden, um doppelte Einträge für die Spalten zu verhindern und somit alle Werte für die indizierte Spalte eindeutig zu sein.
- Um einen Index zu löschen, verwenden Sie den Befehl DROP INDEX gefolgt vom zu löschenden Indexnamen.
SQLite Auslösen
Einführung in die SQLite Auslösen
Trigger sind automatische vordefinierte Operationen, die ausgeführt werden, wenn eine bestimmte Aktion in einer Datenbanktabelle auftritt. Ein Trigger kann so definiert werden, dass er immer dann ausgelöst wird, wenn eine der folgenden Aktionen in einer Tabelle auftritt:
- In eine Tabelle einfügen.
- Zeilen aus einer Tabelle löschen.
- AKTUALISIEREN Sie eine der Tabellenspalten.
SQLite unterstützt den Trigger FOR EACH ROW, sodass die vordefinierten Vorgänge im Trigger für alle Zeilen ausgeführt werden, die an den in der Tabelle aufgetretenen Aktionen beteiligt sind (egal ob es sich um Einfügen, Löschen oder Aktualisieren handelt).
SQLite TRIGGER ERSTELLEN
Um einen neuen TRIGGER zu erstellen, können Sie die Anweisung CREATE TRIGGER wie folgt verwenden:
- Nach CREATE TRIGGER sollten Sie einen Triggernamen angeben.
- Nach dem Triggernamen müssen Sie angeben, wann genau der Triggername ausgeführt werden soll. Sie haben drei Möglichkeiten:
- VORHER – der Trigger wird vor der angegebenen INSERT-, UPDATE- oder Löschanweisung ausgeführt.
- Nachher – der Trigger wird nach der angegebenen INSERT-, UPDATE- oder Löschanweisung ausgeführt.
- INSTEAD OF – Die Aktion, die den Trigger ausgelöst hat, wird durch die im TRIGGER angegebene Anweisung ersetzt. Der INSTEAD OF-Trigger ist nicht auf Tabellen anwendbar, sondern nur auf Ansichten.
Trigger werden nur abhängig vom Typ der im Befehl „create trigger“ angegebenen Anweisung aktiviert (ausgelöst). Zum Beispiel:
- Der BEFORE INSERT-Trigger wird vor jeder INSERT-Anweisung aktiviert (ausgelöst).
- Der AFTER UPDATE-Trigger wird nach jeder Update-Anweisung usw. aktiviert (ausgelöst).
Innerhalb des Triggers können Sie mit dem Schlüsselwort „new“ auf die neu eingefügten Werte verweisen. Außerdem können Sie mit dem Schlüsselwort „old“ auf die gelöschten oder aktualisierten Werte verweisen. Wie folgt:
- Innerhalb von INSERT-Triggern kann ein neues Schlüsselwort verwendet werden.
- Innerhalb von UPDATE-Triggern können neue und alte Schlüsselwörter verwendet werden.
- Innerhalb von DELETE-Triggern kann das alte Schlüsselwort verwendet werden.
Beispiel
Im Folgenden erstellen wir einen Trigger, der ausgelöst wird, bevor ein neuer Student in das „Die Kursteilnehmer" Tabelle.
Der neu eingefügte Schüler wird in die Tabelle eingetragen.StudentenLog“ mit einem automatischen Zeitstempel für das aktuelle Datum und die Uhrzeit, zu der die Insert-Anweisung ausgeführt wurde. Wie folgt:
Schritt 1) Navigieren Sie in das Verzeichnis „C:\sqlite“ und führen Sie sqlite3.exe aus.
Schritt 2) Öffnen Sie die Datenbank“TutorialsSampleDB.db“, indem Sie den folgenden Befehl ausführen:
.open TutorialsSampleDB.db
Schritt 3) Erstelle den Auslöser“InsertIntoStudentTrigger„Durch Ausführen des folgenden Befehls:
CREATE TRIGGER InsertIntoStudentTrigger
BEFORE INSERT ON Students
BEGIN
INSERT INTO StudentsLog VALUES(new.StudentId, datetime(), 'Insert');
END;
Die Funktion "Terminzeit()" gibt Ihnen den aktuellen Datums- und Zeitstempel, als die Einfügeanweisung ausgeführt wurde. Damit wir die Einfügungstransaktion mit automatischen Zeitstempeln protokollieren können, die jeder Transaktion hinzugefügt werden.
Der Befehl sollte erfolgreich ausgeführt werden und Sie erhalten keine Ausgabe:
Der Auslöser "InsertIntoStudentTrigger„wird jedes Mal ausgelöst, wenn Sie einen neuen Schüler in die Schülertabelle einfügen. Der "neuDas Schlüsselwort „bezieht sich auf die Werte, die eingefügt werden. Zum Beispiel das „new.StudentId„ist der Studentenausweis, der eingefügt wird.
Jetzt testen wir, wie sich der Trigger verhält, wenn wir einen neuen Schüler einfügen.
Schritt 4) Schreiben Sie den folgenden Befehl, der einen neuen Studenten in die Studententabelle einfügt:
INSERT INTO Students VALUES(11, 'guru11', 1, '1999-10-12');
Schritt 5) Schreiben Sie den folgenden Befehl, der alle Zeilen aus dem „StudentenLog" Tisch:
SELECT * FROM StudentsLog;
Für den neuen Studenten, den wir gerade eingefügt haben, sollte eine neue Zeile zurückgegeben werden:
Diese Zeile wurde vom Trigger eingefügt, bevor der neue Schüler mit der ID 11 eingefügt wurde.
In diesem Beispiel haben wir den Auslöser „ InsertIntoStudentTrigger „Wir haben erstellt, um alle Einfügungstransaktionen in der Tabelle zu protokollieren“StudentenLog” automatisch. Auf die gleiche Weise können Sie alle Aktualisierungs- oder Löschanweisungen protokollieren.
Verhindern unbeabsichtigter Updates mit Triggern:
Durch die Verwendung von BEFORE UPDATE-Triggern für eine Tabelle können Sie die Aktualisierungsanweisungen für eine Spalte basierend auf einem Ausdruck verhindern.
Beispiel
Im folgenden Beispiel verhindern wir, dass eine Update-Anweisung die Spalte „studentname“ in der Tabelle „Students“ aktualisiert:
Schritt 1) Navigieren Sie in das Verzeichnis „C:\sqlite“ und führen Sie sqlite3.exe aus.
Schritt 2) Öffnen Sie die Datenbank“TutorialsSampleDB.db“, indem Sie den folgenden Befehl ausführen:
.open TutorialsSampleDB.db
Schritt 3) Erstellen Sie einen neuen Trigger“verhindernUpdateStudentName" auf dem Tisch "Die Kursteilnehmer” durch Ausführen des folgenden Befehls
CREATE TRIGGER preventUpdateStudentName
BEFORE UPDATE OF StudentName ON Students
FOR EACH ROW
BEGIN
SELECT RAISE(ABORT, 'You cannot update studentname');
END;
Das "ERZIEHEN„Befehl löst einen Fehler mit einer Fehlermeldung aus“ Sie können studentname nicht aktualisieren “, und dann wird die Ausführung der Update-Anweisung verhindert.
Jetzt überprüfen wir, ob der Auslöser ordnungsgemäß funktioniert und jede Aktualisierung der Spalte „studentname“ verhindert.
Schritt 4) Führen Sie den folgenden Update-Befehl aus, der den Studentennamen aktualisiert:Jack" zu sein "Jack1".
UPDATE Students SET StudentName = 'Jack1' WHERE StudentName = 'Jack';
Sie sollten die Fehlermeldung erhalten, die wir beim Auslöser angegeben haben und die besagt: „Sie können studentname nicht aktualisieren“ wie folgt:
Schritt 5) Führen Sie den folgenden Befehl aus, der die Liste mit den Studentennamen aus der Studententabelle auswählt.
SELECT StudentName FROM Students;
Sie sollten sehen, dass der Schülername „Jack“ immer noch derselbe ist und sich nicht ändert:
Zusammenfassung
Ansichten, Indizes und Trigger sind sehr leistungsstarke Tools zur Verwaltung eines SQLite Datenbank. Sie können die Datenänderungsvorgänge verfolgen, wenn sie in einer Tabelle stattfinden. Sie können den Datenbankdatenabruf auch optimieren, indem Sie Indizes erstellen.
