Wprowadzenie
Na dzisiejszych laboratoriach kontynujemy dalej zagadnienia związane z SQL. Do pracy na zajęciach wymagane są gotowe zadania z poprzednich zajęć. Jeśli pracujesz na nowym stanowisku, pobierz swoje repozytorium, uruchom PSQL na Dockerze. Połącz się za pomocą DataGripa z bazą oraz wgraj swoje skrypty.
SQL part 4
Widoki
VIEW
Widok jest wirtualną tabelą opartą na wynikach zdefiniowanego zapytania. Zawiera on wiersze i kolumny, tak jak prawdziwa tabela. Pola w widoku są polami z jednej lub wielu rzeczywistych tabel. Dzięki widokowi jesteśmy w stanie zaprezentować skomplikowane zapytania, tak jakby były jedną tabelą.
Przykład stworzenia widoku:
1
2
3
4
CREATE VIEW teachers_who_were_students AS
SELECT * FROM teachers
INNER JOIN students
ON teachers.user_id = students.user_id;
Aby usunąć widok, należy wobec niego użyć komendy DROP tak jak w przypadku tabel.
Przykład usunięcia stworzonego wcześniej widoku:
1
DROP VIEW teachers_who_were_students;
Aby zaktualizować widok, należy użyć komendy CREATE OR REPLACE, do której przekazujemy nowe zapytanie.
Przykład aktualizacji widoku, w którym dodatkowo dodajemy wszystkie informacje z tabeli users robiąc LEFT JOIN po kluczu głównym user.id oraz kluczy obcym teachers.user_id:
1
2
3
4
5
6
CREATE OR REPLACE VIEW teachers_who_were_students AS
SELECT * FROM teachers
INNER JOIN students
ON teachers.user_id = students.user_id
LEFT JOIN users
ON teachers.user_id = users.id;
Z kolei aby zaktualizować właściwości widoku, należy użyć komendy ALTER VIEW.
Przykład zmiany nazwy widoku:
1
ALTER VIEW teachers_who_were_students RENAME TO lecturers_who_were_students;
Indeksy
Indeksy w bazach danych to struktury, które przyspieszają wyszukiwanie i odwoływanie się do konkretnych rekordów w tabelach. Są one szczególnie przydatne, kiedy mamy do czynienia z dużymi ilościami danych, ponieważ umożliwiają szybsze przeszukiwanie tabel i wykonywanie zapytań. Minusem indeksów jest to że obciążają system podczas wprowadzania danych. Dlatego ważne jest umiejętne projektowanie indeksów.
Przykład stworzenia indeksu na tabeli students opierający się na kolumnie student_index:
1
2
CREATE INDEX idx_student ON students (student_index);
Procedury
Procedury to zbiór instrukcji SQL, które są zdefiniowane i przechowywane na serwerze bazy danych. Procedury można wywoływać kilkukrotnie, aby przetworzyć różne zestawy danych. Są one szczególnie przydatne w przypadku powtarzających się operacji, zwłaszcza dla tych które są złożone i ich implementacja zajęłaby dużo czasu. Procedury są pisane zazwyczaj języku PL/PGSQL, natomiast domyślnym językiem (jeśli procedura posiada sql body) jest SQL.
Przykład procedury, która doda kolumnę oznaczającą o formie hybrydowej zajęć do tabeli studentów i zaktualizuje wszystkie wartości w nowo dodanej kolumnie na true, dla studentów zaczynających rok studiów w 2020:
1
2
3
4
5
6
7
8
CREATE PROCEDURE add_hybrid_column_to_the_students()
LANGUAGE plpgsql
AS $$
BEGIN
ALTER TABLE students ADD COLUMN is_hybrid BOOL;
UPDATE students SET is_hybrid = true WHERE start_year = 2020;
END;
$$
Aby wywołać procedurą należy użyć komendy call:
1
CALL add_hybrid_column_to_the_students();
Funkcje
Funkcje są bardzo podobne do procedur, ich największą różnicą jest to że potrafią one zwracać dane.
Przykład stworzenia funkcji zwracającej studentów urodzonych pomiędzy rokiem year1 a year2:
1
2
3
4
5
6
7
8
9
CREATE OR REPLACE FUNCTION get_students(year1 INTEGER, year2 INTEGER)
RETURNS TABLE (name TEXT, birthday DATE)
LANGUAGE plpgsql
AS $$
BEGIN
RETURN QUERY SELECT s.name::text, s.birthday::date FROM students s
WHERE EXTRACT(YEAR FROM s.birthday) BETWEEN year1 AND year2;
END;
$$
Aby wywołac funkcję należałoby użyć poniższej komendy:
1
SELECT * FROM get_students(1995, 2023);
Funkcje wyzwalne - triggery
Triggery to specjalne obiekty, które pozwalają na wykonywanie określonych działań w momencie wystąpienia określonych zdarzeń. Trigger może uruchamiać się po wstawieniu, aktualizacji lub usunięciu wiersza z tabeli. Są one często wykorzystywane w celu automatyzacji działań lub weryfikacji danych.
Przykład:
Chcemy dodać weryfikację wieku do tabeli students. W momencie dodania nowego studenta, jego wiek musi być większy niż 18 lat. W przeciwnym wypadku, powinniśmy dostać odpowiedni wyjątek.
Zacznijmy od stworzenia funkcji weryfikującej wiek:
1
2
3
4
5
6
7
8
9
10
11
CREATE OR REPLACE FUNCTION check_age()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
IF (EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM NEW.birthday)) < 18 THEN
RAISE EXCEPTION 'Wiek studenta musi być większy lub równy 18';
END IF;
RETURN NEW;
END;
$$
Następnie dodajmy trigger, który wywoła się automatycznie w momenciu próby dodania danych nowego studenta:
1
2
3
4
5
CREATE TRIGGER check_age_trigger
BEFORE INSERT
ON students
FOR EACH
ROW EXECUTE FUNCTION check_age();
Zadania
Pamiętaj by zapisać wykonane skrypty w swoim repozytorium. Na potrzeby dzisiejszych zajęć do folderu migrations dodaj i pracuj na pliku V4__lab07.sql. Kontynuujemy pracę nad bazą danych z poprzedniego laboratorium.
- Dla jakichkolwiek tabel napisz zapytania lub stwórz
- widok wykorzystujący subquery
- indeks
- procedurę
- funkcję zwracającą dane
- trigger wykorzystujący funkcję bądź procedurę z warunkami logicznymi