Relacyjne bazy danych - laboratorium 5
Post

Relacyjne bazy danych - laboratorium 5

05/03/2023
Paweł Lelental
8

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 i uruchom PSQL na Dockerze. W następnym kroku, zademostrowane zostanie graficzne środowisko, dzięki któremu łatwo wykonamy skrypty z laboratorium04 i z którym będzie pracować przez resztę zajęć.

Konfiguracja zintegrowanego środowiska programistycznego dla baz danych

Instalacja

Na zajęciach będziemy pracować na IDE DataGrip od JetBrains. Narzędzie pozwala na pracę z wieloma DBMS na raz, lokalnie bądź zdalnie. Stanowiska laboratoryjne już posiadają zaintalowane oprogramowanie, wymagane jest natomiast wskazanie serwera licencji. Zostanie ono podane w czasie zajęć.

Natomiast dla studentów pracujących na własnych maszynach oraz do pracy z domu, udostępniam poniższą instrukcję:

  1. Pobierz ze strony JetBrains narzędzie DataGrip: link.
  2. Zainstaluj narzędzie
  3. Po uruchomieniu, program prosi o podanie licencji. Jeśli jej nie masz, możesz całkowicie za darmo ją uzyskać wypełniając ten formularz link. W formularzu podaj swój email uczelniany. Po weryfikacji, uzyskasz dostęp do pełnej gamy produktów firmy JetBrains (DataGrip, IntelliJ, Rider, ReSharper, PhpStorm, Webstorm itd.)

Połączenie z bazą danych

Uruchom środowisko. Podczas pierwszego uruchomienia oraz po wypełnieniu monitu z licencją, powinno pojawić się poniższe okno. Klikamy New Project.

Desktop View

Następnie pojawi się okno dialogowe z opcją zdefiniowania nazwy projektu. Uzepełnij je wpisująć rbd.

Desktop View

Po zatwierdzeniu pojawi się główny ekran środowiska DataGrip:

Desktop View

Aby skonfigurować połączenie wybierz znak plusa w Database Explorer, który znajduje się po lewej stronie aplikacji. Następnie z rozwiniętego menu wybierz Data Source, a następnie PostgresSQL.

Desktop View

Po wykonaniu powyższej czynności, ukaże się okno konfiguracyjne połączenia z bazą danych:

Desktop View

Uzupełnij ją według poniższej instrukcji:

  1. Name: taxi@localhost
  2. User: ‘postgres’
  3. Password: lele, bądź inne jeśli zdefiniowałeś swoje własne podczas tworzenia kontenera
  4. Database: taxi, czyli nazwa stworzonej bazy danych z poprzednich zajęć

Okienko po wypełnieniu, powinno wyglądać tak:

Desktop View

Przejdź teraz do zakładki schema. Zaznacz opcję All databases.

Desktop View

Wróć do zakładki General. Sprawdź połączenie z bazą klikając Test Connection. Może być wymagane pobranie wcześniej dodatkowych sterowników. Jeśli ich nie posiadasz, środowisko automatycznie to wykryje i zaproponuje pobranie je za Ciebie. Monit o brakujących sterownikach wyświetli się powyżej Test Connection. W takim przypadku należy kliknąć Download. Po pobraniu jeszcze raz sprawdź połączenie. Jeśli wszystko zostało skonfigurowane poprawnie, pojawi się poniższy monit.

Desktop View

Po przetestowaniu połączenia kliknij przycisk Ok.

Wgranie skryptów z poprzednich zajęć

W lewym oknie, kliknij prawym przyciskiem na bazę danych, a następnie wybierz New a potem Query Console

Desktop View

Otworzy się okno, wklej do niego skrypty z poprzednich zajęć

Desktop View

Zaznacz wszystkie skrypty (używając skrótu klawiszego CTRL + A) i kliknij przycisk zielonej strzałki (execute).

Desktop View

SQL part 2

Filtrowanie danych

WHERE

Podstawą filtrowania danych w SQL, jest dodanie klauzuli WHERE do zapytania SELECT z odpowiednim warunkiem.

Na przykład, dla tabeli adresowej, zapytanie o wszystkie ulice w Gdańsku, prezentowała by się następująco:

1

SELECT street_name FROM address WHERE city = 'Gdańsk';

Klauzula WHERE może przyjąć wiele operatorów do warunków:

  1. = - równa się
  2. != - negacja / nie równa się
  3. > - większe
  4. < - mniejsze
  5. >= - większe lub równe
  6. <= - mniejsze lub równe
  7. BETWEEN - wartości w przedziale
  8. LIKE - zbliżone wartości, które muszą zaczynać się lub kończyć na zdefiniowaną wartość. Wartości neatrulne oznaczone są tzw. wildcardem, czyli %
  9. IN - wartości w zdeklarowanej liście wartości

Dodatkowo klauzula WHERE przyjmuje operatory logiczne, które pozwalają łączyć warunki:

  1. AND
  2. OR
  3. NOT

Przykład zapytania o numery indeksów studentów, którzy nazywają się Jakub, Michał lub Arkadiusz, są urodzeni po 01.01.1995, zaczeli studia pomiędzy 2015 a 2019, nie skończyli studiów pomiędzy 2017 a 2020 i ich nazwisko kończy się na ‘ski’:

1
2
3
4
5
6

SELECT index_number FROM student 
  WHERE first_name IN ('Jakub', 'Michał', 'Arkadiusz') 
    AND date_of_birth > '1995-01-01'
    AND study_start_year BETWEEN 2015 AND 2019
    AND study_end_year NOT BETWEEN 2017 AND 2020
    AND last_name LIKE '%ski';

Grupowanie danych

GROUP BY

Dzięki zastasowaniu klauzuli GROUP BY jesteśmy w stanie zgrupować wiersze w tzw. wiersze podsumowujące. Przykładem zastasowania GROUP BY może być zapytanie o liczbę studentów zapisanych na dany przedmiot. Klauzula GROUP BY często stosowany jest razem z tzw. funkcjami agregującymi jak np. COUNT, SUM, AVG, MIN czy MAX.

Przykład użycia:

1
2
3

SELECT COUNT(student_id), subject
  FROM students
GROUP BY subject

HAVING

Minusem klauzuli GROUP BY jest fakt niemożliwości łączenia go z klauzulą WHERE przy funcjach agregujących. Rozwiązaniem problemu jest użycie klauzuli HAVING.

Przykład:

1
2
3
4

SELECT COUNT(student_id), subject
  FROM students
GROUP BY subject
HAVING COUNT(student_id) > 13784

Modyfikowanie danych

UPDATE

Do aktualizacji danych encji, używana jest komenda UPDATE. Jednakże podczas używania tej komendy trzeba zachować ostrożność. Sformułowanie podanego poniżej zapytania:

1

UPDATE address SET city = 'Gdańsk';

spowoduje że dla wszystkich rekordów w tabeli adress, zostanie ustawiona dla kolumna city wartość Gdańsk!

Jest to bardzo częsty błąd popełniany przez początkujących deweloperów. Dlatego też, komendę UPDATE zawsze powinno się łączyć z klauzulą WHERE, tak by zaktualizować tylko i wyłącznie interesujący nas rekord.

1

UPDATE address SET city = 'Gdańsk' WHERE id = 1;

Usuwanie danych

DELETE

Do skasowania danego rekordu z tabeli należy użyć komendy DELETE. UWAGA! Tak jak w przypadku klauzuli UPDATE, nie dodanie do zapytania klauzuli WHERE spowoduje że dotknięte naszą operacją zostaną wszystkie rekordy tabeli.

Prawidłowe zapytanie z komendą DELETE powinno wyglądać następująco:

1

DELETE FROM address WHERE id = 1;

TRUNCATE

Z kolei, jeśli usunięcie wszystkich danych z tabeli jest przez nas zamierzoną operacją, to do usunięcia wszystkich rekordów powinniśmy użyć komedy TRUNCATE

1

TRUNCATE TABLE address;

Modyfikowanie struktur

ALTER TABLE

Komenda ALTER TABLE wykorzystywana jest do dodawania, modyfikowania oraz usuwania kolumn w tabeli. Za jej pomocą możemy dodać lub usunąć również contrainty.

Przykład użycia:

1
2

ALTER TABLE students
ADD index_number varchar(10);

ALTER COLUMN

Klauzula ALTER COLUMN wykorzystywana jest do modyfikowania typu danych kolumny wraz z komendą ALTER TABLE.

Przykład:

1
2

ALTER TABLE students
ALTER COLUMN index_number INTEGER;

ADD CONSTRAINT

Aby dodać constrainty do wcześniej stworzonej tabeli, należy wykorzystać klauzulę ADD CONSTRAINT wraz z komendą ALTER TABLE lub też podczas tworzenia tabeli (komenda CREATE TABLE).

Przykład dodania klucza obcego tabeli adres dla tabeli student. Ważne! jeśli ważne jest również dodanie kolumny do tabeli, która będzie przechowywała klucz obcy.

1
2
3
4
5
6

ALTER TABLE students
  ADD COLUMN address_id INT NOT NULL;

ALTER TABLE students  
  ADD CONSTRAINT id_address_fk
  FOREIGN KEY (address_id) REFERENCES address(id);

Dodatkowo, podczas dodawania constrainów, możemy zdefiniować zachowanie się rekordów na akcje aktualizacji bądź usunięcia danych. Mowa o tzw nadaniach akcji:

  1. ON UPDATE NO ACTION oraz ON DELETE NO ACTION - domyślny contraint dla aktualizacji oraz usuwania danych. Jeśli constraint zostanie złamany, wywołany zostanie błąd jeśli istnieją jakiekolwiek referencyjne wiersze
  2. ON UPDATE RESTRICT oraz ON DELETE RESTRICT - działanie identyczne jak w NO ACTION z tą różnicą że błąd zostanie wywołany odrazu, a nie np. podczas końca tranzakcji
  3. ON UPDATE CASCADE oraz ON DELETE CASCADE - aktualizacja lub usunięcie odbędzie się kaskadowo. To znaczy że dana akcja dotknie również referencyjne wiersze
  4. ON DELETE SET NULL - akcja dostępna tylko dla DELETE. Podczas wykonywania operacji usunięcia, ustawiany jest NULL w wierszu, w którym posiadamy referencje do usuwanego wiersza
  5. ON DELETE SET DEFAULT - akcja dostępna tylko dla DELETE. Podczas wykonywania operacji usunięcia, ustawiana jest domyślna wartość (jeśli jest zdefiniowana) w wierszu, w którym posiadamy referencje do usuwanego wiersza

Akcje dla aktualizacji oraz usuwania można ze sobą łączyć. Przykład:

1
2
3
4
5
6
7
8

ALTER TABLE students
  ADD COLUMN address_id INT NOT NULL;

ALTER TABLE students  
  ADD CONSTRAINT id_address_fk
  FOREIGN KEY (address_id) REFERENCES address(id)
  ON DELETE NO ACTION
  ON UPDATE CASCADE;

Poniżej również przykład dodania nowej tabeli students wraz z kluczem obcym do tabeli address:

1
2
3
4
5
6
7

CREATE TABLE students(
  id serial PRIMARY KEY,
  index_number text,
  address_id INT NOT NULL,
  CONSTRAINT fk_address_id
    FOREIGN KEY(address_id) REFERENCES address(id)
);

Usuwanie struktur

DROP

DROP to podstawowa komenda do usuwania całych tabel.

Przykład usunięcia tabeli address:

1

DROP TABLE address;

Funkcje agregujące

AVG

Funkcja wyliczającą średnią dla danej kolumny numerycznej.

Przykład, zwracający średnią wartość roku urodzenia wszystkich studentów, którzy zaczeli studia w 2023 roku:

1
2
3
4

SELECT AVG(birth_year) 
  FROM students
WHERE start_of_studies = 2023;

SUM

Funkcja zwracająca sumę, dla danej kolumny numerycznej.

Przykład, zwracający całkowitą sumę czesnego zapłaconego przez studenta o indeksie s13784:

1
2

SELECT SUM(fee) FROM tuition_fees
  WHERE student_index = 's13784';

MIN

Funkcja zwracająca minimalna wartość kolumny numerycznej bądź datowej.

Przykład, zwracający minimalną wartość zapłaconego czesnego przez studenta o indeksie s13784:

1
2

SELECT MIN(fee) FROM tuition_fees
  WHERE student_index = 's13784';

MAX

Funkcja zwracająca maksymalną wartość kolumny numerycznej bądź datowej.

Przykład, zwracający maksymalną wartość zapłaconego czesnego przez studenta o indeksie s13784:

1
2

SELECT MAX(fee) FROM tuition_fees
  WHERE student_index = 's13784';

COUNT

Funkcja zwracająca liczbę wierszy spełniającą dany warunek.

Przykład, zwracający liczbę wierszy (a zarazem też studentów), którzy zaczęli studia w 2023 roku:

1
2

SELECT COUNT(*) FROM students
  WHERE start_of_studies = 2023;

Zadania

Pamiętaj by zapisać wykonane skrypty w swoim repozytorium. Na potrzeby dzisiejszych zajęć do folderu migrations dodaj i pracuj na pliku V2__lab05.sql. Kontynuujemy pracę nad bazą danych z poprzedniego laboratorium.

  1. Wykorzystując opisane wyżej klauzule, dodaj do tabel brakujące klucze obce, gdzie:
    • Musisz wyczyścić wszystkie dane z co najmniej jednej tabeli (ale nie ze wszystkich tabel, zostaw przynajmniej po jednej tabeli, które mają ze sobą relacje)
    • Musisz co najmniej raz usunąć jedną tabelę i stworzyć ją od nowa z kluczem obcym
    • Musisz co najmniej raz zmodyfikować tabele i dodać do niej klucz obcy
    • W pozostałych nie wyczyszczonych tabelach, zmodyfikuj dane przypisując do nich klucz obcy, którego wartość jest z tabeli z którą aktualizowana tabela ma relacje
  2. Dodaj do każdej tabeli przynajmniej 50 rekordów. Wykorzystaj przy tym narzędzie do mockowania danych np. Mockaroo
PJATK, Relacyjne bazy danych
pjatk rbd psql