SQLite Widoki, indeks i wyzwalacz z przykładem

W codziennym użytkowaniu SQLite, będziesz potrzebować narzędzi administracyjnych w swojej bazie danych. Można ich także używać do wydajniejszego wykonywania zapytań do bazy danych poprzez tworzenie indeksów lub do zwiększenia możliwości ponownego użycia poprzez tworzenie widoków.

SQLite Zobacz

Widoki są bardzo podobne do tabel. Ale widoki są tabelami logicznymi; nie są przechowywane fizycznie jak tabele. Widok składa się z instrukcji Select.

Można zdefiniować widok dla złożonych zapytań i używać tych zapytań ponownie w dowolnej chwili, wywołując widok bezpośrednio, zamiast przepisywać zapytania od nowa.

Instrukcja UTWÓRZ WIDOK

Aby utworzyć widok w bazie danych, możesz użyć instrukcji CREATE VIEW, po której następuje nazwa widoku, a następnie umieścić żądane zapytanie.

Przykład: W poniższym przykładzie utworzymy widok o nazwie „AllStudentsView” w przykładowej bazie danych „TutorialsSampleDB.db” w następujący sposób:

Krok 1) Otwórz Mój komputer i przejdź do następującego katalogu „C:\sqlite”, a następnie otwórz „sqlite3.exe”:

Krok 2) Otwórz bazę danych „TutorialsSampleDB.db” za pomocą następującego polecenia:

Krok 3) Poniżej znajduje się podstawowa składnia polecenia sqlite3 służąca do tworzenia widoku

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;

Polecenie takie nie powinno wyświetlać żadnych wyników:

Krok 4) Aby mieć pewność, że widok zostanie utworzony, możesz wybrać listę widoków w bazie danych, uruchamiając następujące polecenie:

SELECT name FROM sqlite_master WHERE type = 'view';

Powinieneś zobaczyć zwrócony widok „AllStudentsView”:

Krok 5) Teraz nasz widok został utworzony, możesz go używać jako normalnej tabeli, mniej więcej tak:

SELECT * FROM AllStudentsView;

To polecenie spowoduje zapytanie do widoku „AllStudents” i wybranie z niego wszystkich wierszy, jak pokazano na poniższym zrzucie ekranu:

Widoki tymczasowe

Widoki tymczasowe są tymczasowe dla bieżącego połączenia z bazą danych użytego do jego utworzenia. Następnie, jeśli zamkniesz połączenie z bazą danych, wszystkie widoki tymczasowe zostaną automatycznie usunięte. Widoki tymczasowe są tworzone za pomocą jednego z następujących poleceń:

• UTWÓRZ WIDOK TEMP lub
• UTWÓRZ WIDOK TYMCZASOWY.

Widoki tymczasowe są przydatne, jeśli chcesz wykonać pewne operacje na jakiś czas i nie potrzebujesz, aby był to widok stały. Tak więc po prostu tworzysz widok tymczasowy, a następnie wykonujesz przetwarzanie za pomocą tego widoku. Later gdy zamkniesz połączenie z bazą danych, zostanie ona automatycznie usunięta.

Przykład:

W poniższym przykładzie otworzymy połączenie z bazą danych, a następnie utworzymy widok tymczasowy.

Następnie zamkniemy to połączenie i sprawdzimy, czy widok tymczasowy nadal istnieje, czy nie.

Krok 1) Otwórz plik sqlite3.exe z katalogu „C:\sqlite”, jak wyjaśniono wcześniej.

Krok 2) Otwórz połączenie z bazą danych „TutorialsSampleDB.db” uruchamiając następujące polecenie:

.open TutorialsSampleDB.db

Krok 3) Napisz następujące polecenie, które utworzy widok tymczasowy „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;

Krok 4) Upewnij się, że widok tymczasowy „AllStudentsTempView” został utworzony, uruchamiając następujące polecenie:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

Krok 5) Zamknij plik sqlite3.exe i otwórz go ponownie.

Krok 6) Otwórz połączenie z bazą danych „TutorialsSampleDB.db” za pomocą następującego polecenia:

.open TutorialsSampleDB.db

Krok 7) Uruchom następujące polecenie, aby uzyskać listę tymczasowych widoków utworzonych w bazie danych:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

Nie powinieneś widzieć żadnego wyniku, ponieważ widok tymczasowy, który utworzyliśmy, został usunięty, gdy zamknęliśmy połączenie z bazą danych w poprzednim kroku. W przeciwnym razie, dopóki utrzymujesz połączenie z bazą danych otwarte, będziesz mógł zobaczyć widok tymczasowy z danymi.

Uwagi:

• W przypadku widoków nie można używać instrukcji INSERT, DELETE ani UPDATE, można jedynie użyć polecenia „wybierz z widoków”, jak pokazano w kroku 5 w przykładzie CREATE View.
• Aby usunąć WIDOK, możesz użyć instrukcji „DROP VIEW”:

DROP VIEW AllStudentsView;

Aby mieć pewność, że widok zostanie usunięty, możesz uruchomić następujące polecenie, które wyświetli listę widoków w bazie danych:

SELECT name FROM sqlite_master WHERE type = 'view';

Nie zwrócono żadnych widoków, ponieważ widok został usunięty w następujący sposób:

Oprócz ponownego wykorzystywania złożonych zapytań z widokami, SQLite Przyspiesza również wyszukiwanie pasujących wierszy za pomocą indeksów.

SQLite wskaźnik

Jeśli masz książkę i chcesz wyszukać słowo kluczowe dotyczące tej książki. Tego słowa kluczowego będziesz szukać w indeksie książki. Następnie przejdziesz do numeru strony danego słowa kluczowego, aby uzyskać więcej informacji na jego temat.

Jeśli jednak nie ma indeksu w tej książce ani numerów stron, będziesz skanować całą książkę od początku do końca, aż znajdziesz poszukiwane słowo kluczowe. A to jest bardzo trudne, zwłaszcza gdy masz indeks i bardzo powolny proces wyszukiwania słowa kluczowego.

Indeksy w SQLite (i ta sama koncepcja obowiązuje dla innych systemy zarządzania bazą danych również) działa w taki sam sposób, jak indeksy znajdujące się na odwrocie ksiąg.

Kiedy szukasz niektórych wierszy w pliku SQLite tabela z kryteriami wyszukiwania, SQLite przeszuka wszystkie wiersze tabeli, aż znajdzie wiersze, których szukasz i które spełniają kryteria wyszukiwania. Proces ten staje się bardzo powolny, gdy masz większe stoły.

Indeksy przyspieszą wyszukiwanie danych i ułatwią wyszukiwanie danych z tabel. Indeksy definiuje się w kolumnach tabeli.

Poprawa wydajności dzięki indeksom:

Indeksy mogą poprawić wydajność wyszukiwania danych w tabeli. Kiedy tworzysz indeks w kolumnie, SQLite utworzy strukturę danych dla tego indeksu, w której każda wartość pola ma wskaźnik do całego wiersza, do którego należy ta wartość.

Następnie, jeśli uruchomisz zapytanie z warunkiem wyszukiwania w kolumnie będącej częścią indeksu, SQLite najpierw wyszuka wartość w indeksie. SQLite nie przeskanuje w poszukiwaniu tego całej tabeli. Następnie odczyta lokalizację, w której znajduje się wartość dla wiersza tabeli. SQLite zlokalizuje wiersz w tej lokalizacji i pobierze go.

Jeśli jednak kolumna, której szukasz, nie jest częścią indeksu, SQLite wykona skanowanie wartości kolumn, aby znaleźć dane, których szukasz. Zwykle będzie to wolniejszy proces, jeśli nie ma indeksu.

Wyobraź sobie książkę bez indeksu i musisz wyszukać określone słowo. Przeskanujesz całą książkę od pierwszej do ostatniej strony w poszukiwaniu tego słowa. Jeśli jednak masz indeks dotyczący tej książki, najpierw poszukaj znajdującego się w niej słowa. Uzyskaj numer strony, na której się ona znajduje, a następnie przejdź do niej. Co będzie znacznie szybsze niż skanowanie całej książki od deski do deski.

SQLite UTWÓRZ INDEKS

Aby utworzyć indeks w kolumnie należy użyć polecenia CREATE INDEX. I powinieneś zdefiniować to w następujący sposób:

• Po poleceniu CREATE INDEX należy podać nazwę indeksu.
• Po nazwie indeksu należy wpisać słowo kluczowe „ON”, a następnie nazwę tabeli, w której indeks zostanie utworzony.
• Następnie lista nazw kolumn używanych w indeksie.
• Aby określić kolejność sortowania stosowaną do uporządkowania danych indeksu, po nazwie dowolnej kolumny można użyć jednego z następujących słów kluczowych: „ASC” lub „DESC”.

Przykład:

W poniższym przykładzie utworzymy indeks „StudentNameIndex” w tabeli studentów w bazie danych „Students” w następujący sposób:

Krok 1) Przejdź do folderu „C:\sqlite”, jak wyjaśniono wcześniej.

Krok 2) Otwórz sqlite3.exe.

Krok 3) Otwórz bazę danych „TutorialsSampleDB.db” za pomocą następującego polecenia:

.open TutorialsSampleDB.db

Krok 4) Utwórz nowy indeks „StudentNameIndex” za pomocą następującego polecenia:

CREATE INDEX StudentNameIndex ON Students(StudentName);

Nie powinieneś widzieć żadnych wyników dla tego:

Krok 5) Aby mieć pewność, że indeks został utworzony, możesz uruchomić następujące zapytanie, które zwróci listę indeksów utworzonych w tabeli Studenci:

PRAGMA index_list(Students);

Powinieneś zobaczyć zwrócony indeks, który właśnie utworzyliśmy:

Uwagi:

• Indeksy można tworzyć nie tylko na podstawie kolumn, ale także wyrażeń. Coś takiego:

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

Wartość „OrderTotalIndex” będzie bazować na kolumnie OrderId, a także na przemnożeniu wartości kolumny Quantity i wartości kolumny Price. Zatem każde zapytanie o „OrderId” i „Quantity*Price” będzie skuteczne, ponieważ zapytanie będzie korzystać z indeksu.

• Jeśli w poleceniu CREATE INDEX określono klauzulę WHERE, indeks będzie indeksem częściowym. W takim przypadku w indeksie będą wpisy tylko dla wierszy, które spełniają warunki w klauzuli WHERE. Na przykład w poniższym indeksie:

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

  (W powyższym przykładzie indeks będzie indeksem częściowym, ponieważ określono klauzulę WHERE. W tym przypadku indeks zostanie zastosowany tylko do tych zamówień, których wartość ilościowa jest większa niż 10000. Należy pamiętać, że indeks ten nazywany jest częściowym indeks ze względu na klauzulę WHERE, a nie użyte w niej wyrażenie. Można jednak używać wyrażeń z normalnymi indeksami.)

• Możesz użyć instrukcji CREATE UNIQUE INDEX zamiast CREATE INDEX, aby zapobiec duplikowaniu wpisów w kolumnach, dzięki czemu wszystkie wartości w indeksowanej kolumnie będą unikalne.
• Aby usunąć indeks, użyj polecenia DROP INDEX, po którym należy podać nazwę indeksu do usunięcia.

Podczas gdy indeksy przyspieszają odczyty, wyzwalacze pozwalają SQLite Reaguj automatycznie po każdej zmianie danych.

SQLite Cyngiel

Wprowadzenie do SQLite Cyngiel

Wyzwalacze to automatyczne, wstępnie zdefiniowane operacje wykonywane, gdy w tabeli bazy danych wystąpi określona akcja. Wyzwalacz można zdefiniować tak, aby był uruchamiany, gdy w tabeli wystąpi jedna z następujących akcji:

• WSTAW do tabeli.
• USUŃ wiersze z tabeli.
• ZAKTUALIZUJ jedną z kolumn tabeli.

SQLite obsługuje wyzwalacz FOR EACH ROW, dzięki czemu wstępnie zdefiniowane operacje w wyzwalaczu zostaną wykonane dla wszystkich wierszy powiązanych z akcjami wykonanymi w tabeli (niezależnie od tego, czy jest to wstawianie, usuwanie czy aktualizacja).

SQLite STWÓRZ WYZWALACZ

Aby utworzyć nowy TRIGGER, możesz użyć polecenia CREATE TRIGGER w następujący sposób:

• Po CREATE TRIGGER należy określić nazwę wyzwalacza.
• Po nazwie wyzwalacza należy określić, kiedy dokładnie nazwa wyzwalacza ma zostać wykonana. Masz trzy opcje:
  • BEFORE – wyzwalacz zostanie wykonany przed określoną instrukcją INSERT, UPDATE lub Delete.
  • After – wyzwalacz zostanie wykonany po podanej instrukcji INSERT, UPDATE lub Delete.
  • ZAMIAST – Zastąpi akcję, która uruchomiła wyzwalacz, instrukcją określoną w TRIGGER. Wyzwalacz INSTEAD OF nie ma zastosowania do tabel, tylko do widoków.
• Następnie musisz określić rodzaj akcji, wyzwalacz zostanie uruchomiony, gdy to nastąpi. USUŃ, WSTAW lub AKTUALIZUJ.
• Możesz wybrać opcjonalną nazwę kolumny, aby reguła nie została uruchomiona, chyba że akcja została wykonana w tej kolumnie.
• Następnie należy podać nazwę tabeli, w której zostanie utworzony wyzwalacz.
• Wewnątrz treści wyzwalacza należy określić instrukcję, która powinna zostać wykonana dla każdego wiersza po uruchomieniu wyzwalacza.

Wyzwalacze zostaną aktywowane (uruchomione) tylko w zależności od typu instrukcji określonej w poleceniu tworzenia wyzwalacza. Na przykład:

• Wyzwalacz BEFORE INSERT zostanie aktywowany (uruchomiony) przed jakąkolwiek instrukcją wstawiania.
• Wyzwalacz AFTER UPDATE zostanie aktywowany (uruchomiony) po każdej instrukcji aktualizacji… i tak dalej.

Wewnątrz wyzwalacza możesz odwołać się do nowo wstawionych wartości, używając słowa kluczowego „new”. Możesz również odwołać się do usuniętych lub zaktualizowanych wartości, używając słowa kluczowego old. W następujący sposób:

• Wewnątrz wyzwalaczy INSERT – można użyć nowego słowa kluczowego.
• Wewnątrz wyzwalaczy UPDATE – można używać nowych i starych słów kluczowych.
• Wewnątrz wyzwalaczy DELETE – można użyć starego słowa kluczowego.

Przykład

Poniżej utworzymy wyzwalacz, który zostanie uruchomiony przed dodaniem nowego ucznia do tabeli „Uczniowie”.

Rejestruje nowo dodanego studenta w tabeli „StudentsLog” z automatycznym znacznikiem czasu dla bieżącej daty i godziny wykonania polecenia insert. W następujący sposób:

Krok 1) Przejdź do katalogu „C:\sqlite” i uruchom sqlite3.exe.

Krok 2) Otwórz bazę danych „TutorialsSampleDB.db” uruchamiając następujące polecenie:

.open TutorialsSampleDB.db

Krok 3) utwórz wyzwalacz „InsertIntoStudentTrigger” poprzez uruchomienie następującego polecenia:

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

Funkcja „datetime()” zwróci aktualny znacznik czasu i daty wykonania polecenia insert. Dzięki temu możemy zarejestrować transakcję insert z automatycznym dodawaniem znaczników czasu do każdej transakcji.

Polecenie powinno zostać pomyślnie uruchomione i nie otrzymasz żadnych danych wyjściowych:

Wyzwalacz „InsertIntoStudentTrigger” będzie uruchamiany za każdym razem, gdy wstawisz nowego studenta do tabeli „students”. Słowo kluczowe „new” odnosi się do wartości, które zostaną wstawione. Na przykład „new.StudentId” będzie identyfikatorem studenta, który zostanie wstawiony.

Teraz przetestujemy, jak zachowuje się wyzwalacz, gdy wstawimy nowego ucznia.

Krok 4) Napisz następujące polecenie, które wstawi nowego ucznia do tabeli students:

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

Krok 5) Napisz następujące polecenie, które wybierze wszystkie wiersze z tabeli „StudentsLog”:

SELECT * FROM StudentsLog;

Powinieneś zobaczyć nowy wiersz zwrócony dla nowego ucznia, którego właśnie wprowadziliśmy:

Ten wiersz został wstawiony przez wyzwalacz przed wstawieniem nowego ucznia o identyfikatorze 11.

W tym przykładzie użyliśmy utworzonego przez nas wyzwalacza „InsertIntoStudentTrigger”, aby automatycznie rejestrować wszystkie transakcje insertów w tabeli „StudentsLog”. W ten sam sposób można rejestrować polecenia aktualizacji i usuwania.

Zapobieganie niezamierzonym aktualizacjom za pomocą wyzwalaczy:

Używając wyzwalaczy BEFORE UPDATE w tabeli, można zapobiec instrukcji aktualizacji w kolumnie na podstawie wyrażenia.

Przykład

W poniższym przykładzie zapobiegniemy, aby jakakolwiek instrukcja aktualizacji aktualizowała kolumnę „studentname” w tabeli Students:

Krok 1) Przejdź do katalogu „C:\sqlite” i uruchom sqlite3.exe.

Krok 2) Otwórz bazę danych „TutorialsSampleDB.db” uruchamiając następujące polecenie:

.open TutorialsSampleDB.db

Krok 3) Utwórz nowy wyzwalacz „preventUpdateStudentName” w tabeli „Students”, uruchamiając następujące polecenie

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

Polecenie „RAISE” spowoduje błąd i wyświetlenie komunikatu o błędzie „Nie można zaktualizować studentname”, a następnie uniemożliwi wykonanie polecenia aktualizacji.

Teraz sprawdzimy, czy wyzwalacz działa dobrze i uniemożliwia aktualizację kolumny imienia ucznia.

Krok 4) Uruchom następujące polecenie aktualizacji, które zaktualizuje nazwę ucznia „Jack” na „Jack1”.

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

Powinieneś otrzymać komunikat o błędzie, który określiliśmy w wyzwalaczu, mówiący: „Nie można zaktualizować nazwy studenta” w następujący sposób:

Krok 5) Uruchom następujące polecenie, które wybierze listę imion i nazwisk studentów z tabeli students.

SELECT StudentName FROM Students;

Powinieneś zobaczyć, że imię ucznia „Jack” jest nadal takie samo i nie ulega zmianie:

FAQ