SQLite Views, Index & Trigger mit Beispiel

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 Ansehen

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 öffnen Sie dann „sqlite3.exe“:

Schritt 2) Öffnen Sie die Datenbank „TutorialsSampleDB.db“ mit folgendem 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';

Es sollte die Ansicht „AllStudentsView“ zurückgegeben werden:

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 beschrieben.

Schritt 2) Stellen Sie eine Verbindung zur Datenbank „TutorialsSampleDB.db“ her, indem Sie folgenden Befehl ausführen:

.open TutorialsSampleDB.db

Schritt 3) Schreiben Sie den folgenden Befehl, der eine temporäre Ansicht namens „AllStudentsTempView“ erstellt:

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 „AllStudentsTempView“ erstellt wird, indem Sie den folgenden Befehl ausführen:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

Schritt 5) Schließen Sie sqlite3.exe und öffnen Sie es erneut.

Schritt 6) Stellen Sie mit folgendem Befehl eine Verbindung zur Datenbank „TutorialsSampleDB.db“ her:

.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.

Hinweis:

• 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:

Neben der Wiederverwendung komplexer Abfragen mit Ansichten, SQLite Außerdem werden übereinstimmende Zeilen durch Indizes schneller abgerufen.

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 „StudentNameIndex“ für die Tabelle „students“ in der Datenbank „Students“ wie folgt:

Schritt 1) Navigieren Sie wie zuvor beschrieben zum Ordner „C:\sqlite“.

Schritt 2) Öffnen Sie sqlite3.exe.

Schritt 3) Öffnen Sie die Datenbank „TutorialsSampleDB.db“ mit folgendem Befehl:

.open TutorialsSampleDB.db

Schritt 4) Erstellen Sie einen neuen Index „StudentNameIndex“ mit folgendem 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:

Hinweis:

• 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.

Während Indizes Lesegeschwindigkeiten ermöglichen, erlauben Trigger SQLite reagiert automatisch bei jeder Datenänderung.

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.
• Anschließend müssen Sie die Art der Aktion angeben, bei der der Auslöser ausgelöst wird. Entweder DELETE, INSERT oder UPDATE.
• Sie können einen optionalen Spaltennamen auswählen, damit der Auslöser nur dann ausgelöst wird, wenn die Aktion in dieser Spalte ausgeführt wurde.
• Anschließend müssen Sie den Tabellennamen angeben, in dem der Trigger erstellt wird.
• Im Hauptteil des Triggers sollten Sie die Anweisung angeben, die für jede Zeile ausgeführt werden soll, wenn der Trigger ausgelöst wird.

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 vor dem Einfügen eines neuen Studenten in die Tabelle „Students“ ausgelöst wird.

Der neu eingefügte Student wird mit einem automatischen Zeitstempel, der dem Datum und der Uhrzeit des Einfügevorgangs entspricht, in die Tabelle „StudentsLog“ eingetragen. Dies geschieht wie folgt:

Schritt 1) Navigieren Sie zum Verzeichnis „C:\sqlite“ und führen Sie sqlite3.exe aus.

Schritt 2) Öffnen Sie die Datenbank „TutorialsSampleDB.db“, indem Sie folgenden Befehl ausführen:

.open TutorialsSampleDB.db

Schritt 3) Erstellen Sie den Trigger „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 „datetime()“ liefert den aktuellen Zeitstempel zum Zeitpunkt der Einfügeanweisung. So können wir die Einfügetransaktion protokollieren und jeder Transaktion automatisch einen Zeitstempel hinzufügen.

Der Befehl sollte erfolgreich ausgeführt werden und Sie erhalten keine Ausgabe:

Der Trigger „InsertIntoStudentTrigger“ wird jedes Mal ausgelöst, wenn Sie einen neuen Studenten in die Tabelle „Students“ einfügen. Das Schlüsselwort „new“ bezieht sich auf die einzufügenden Werte. Beispielsweise ist „new.StudentId“ die einzufügende Studenten-ID.

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 der Tabelle „StudentsLog“ auswählt:

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 von uns erstellten Trigger „InsertIntoStudentTrigger“ verwendet, um alle Einfügevorgänge in der Tabelle „StudentsLog“ automatisch zu protokollieren. Auf die gleiche Weise können Sie auch Aktualisierungs- oder Löschvorgänge 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 zum Verzeichnis „C:\sqlite“ und führen Sie sqlite3.exe aus.

Schritt 2) Öffnen Sie die Datenbank „TutorialsSampleDB.db“, indem Sie folgenden Befehl ausführen:

.open TutorialsSampleDB.db

Schritt 3) Erstellen Sie einen neuen Trigger namens „preventUpdateStudentName“ für die Tabelle „Students“, indem Sie den folgenden Befehl ausführen.

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

Der Befehl „RAISE“ löst einen Fehler mit der Fehlermeldung „Sie können studentname nicht aktualisieren“ aus und verhindert anschließend die Ausführung der Aktualisierungsanweisung.

Jetzt überprüfen wir, ob der Auslöser ordnungsgemäß funktioniert und jede Aktualisierung der Spalte „studentname“ verhindert.

Schritt 4) Führen Sie den folgenden Aktualisierungsbefehl aus, der den Schülernamen „Jack“ in „Jack1“ ändert.

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

Sie sollten die von uns im Trigger angegebene Fehlermeldung erhalten, die besagt, dass „Sie können den Studentennamen nicht aktualisieren“ wie folgt lautet:

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:

Häufig gestellte Fragen