SQLite Wyzwalacz, widoki i indeks z przykładem
Marcusa Allena
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 „Widok wszystkich studentów” w przykładowej bazie danych „PoradnikiPrzykładDB.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 „Plik sqlite3.exe"
Krok 2) Otwórz bazę danych „PoradnikiPrzykładDB.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ć widok „Widok wszystkich studentów” zwracane jest:
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 sqlite3.exe z katalogu „C:\sqlite”, jak wyjaśniono wcześniej.
Krok 2) Otwórz połączenie z bazą danych „PoradnikiPrzykładDB.db„uruchamiając następujące polecenie:
.open TutorialsSampleDB.db
Krok 3) Napisz następujące polecenie, które utworzy widok tymczasowy „WszyscyStudenciTempView"
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 „WszyscyStudenciTempView” tworzy się poprzez uruchomienie następującego polecenia:
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 „PoradnikiPrzykładDB.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:
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 „Indeks imienia ucznia” na stole uczniów w „Studenci”baza danych 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 „PoradnikiPrzykładDB.db” za pomocą następującego polecenia:
.open TutorialsSampleDB.db
Krok 4) Utwórz nowy indeks „Indeks imienia ucznia„używając 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.
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.
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
W dalszej części utworzymy wyzwalacz, który zostanie uruchomiony przed wstawieniem nowego ucznia do „Studenci" stół.
Zaloguje nowo wstawionego ucznia do tabeli „Dziennik studentów” z automatycznym znacznikiem czasu dla bieżącej daty i godziny, kiedy nastąpiło polecenie insert. Jak następuje:
Krok 1) Przejdź do katalogu „C:\sqlite„i uruchom sqlite3.exe.
Krok 2) Otwórz bazę danych „PoradnikiPrzykładDB.db„uruchamiając następujące polecenie:
.open TutorialsSampleDB.db
Krok 3) utwórz wyzwalacz „WstawIntoStudentTrigger„Uruchamiając następujące polecenie:
CREATE TRIGGER InsertIntoStudentTrigger
BEFORE INSERT ON Students
BEGIN
INSERT INTO StudentsLog VALUES(new.StudentId, datetime(), 'Insert');
END;
Funkcja „data i godzina ()” wyświetli aktualną datę i godzinę wykonania instrukcji wstawiania. Abyśmy mogli logować wstawioną transakcję z automatycznymi znacznikami czasu dodawanymi do każdej transakcji.
Polecenie powinno zostać pomyślnie uruchomione i nie otrzymasz żadnych danych wyjściowych:
Spust "WstawIntoStudentTrigger” będzie uruchamiany za każdym razem, gdy wstawisz nowego ucznia do tabeli uczniów. „nowychSłowo kluczowe odnosi się do wartości, które zostaną wstawione. Na przykład „nowy.StudentId” będzie identyfikatorem ucznia, 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 zaznaczy wszystkie wiersze z „Dziennik studentów" tabela:
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 wyzwalacza „ WstawIntoStudentTrigger ” stworzyliśmy, aby rejestrować wszelkie transakcje wstawiania w tabeli „Dziennik studentów” automatycznie. W ten sam sposób możesz zalogować dowolną aktualizację lub usunąć wyciągi.
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 „PoradnikiPrzykładDB.db„uruchamiając następujące polecenie:
.open TutorialsSampleDB.db
Krok 3) Utwórz nowy wyzwalacz „zapobiegajAktualizacjiNazwaStudenta" na stole "Studenci„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;
"PODNIEŚĆ” polecenie zgłosi błąd z komunikatem o błędzie „ Nie możesz zaktualizować nazwy ucznia „, a następnie uniemożliwi to wykonanie instrukcji 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ę studenta „Jacek" być "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, że „Nie możesz zaktualizować nazwy ucznia”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:
Podsumowanie
Widoki, indeksy i wyzwalacze to bardzo potężne narzędzia do administrowania SQLite baza danych. Możesz śledzić operacje modyfikacji danych, gdy mają miejsce w tabeli. Możesz również optymalizować operację pobierania danych z bazy danych, tworząc indeksy.
