ebook Podstawy baz danych PostgreSQL: przykłady i ćwiczenia Lidia Stępień, Marcin R. Stępień, Artur Gola

Wydawca: Uniwersytet Jana Długosza w Częstochowie

Rok wydania: 2024

Przedmowa
Baza danych dotyczy zawsze pewnego fragmentu rzeczywistości (obszaru analizy) i stanowi
zbiór danych, posiadający określoną strukturę wewnętrzną, który reprezentuje ten
fragment rzeczywistości i ułatwia przetwarzanie zgromadzonych danych. Do opisu struktury
wewnętrznej bazy danych służą modele danych, pozwalające opisać własności danych
w sposób ścisły (często sformalizowany) z wykorzystaniem języka matematyki, np. algebry
relacji w modelu relacyjnym. Oprócz opisywania struktury danych model także określa
dozwolone operacje na danych oraz sposób nakładania ograniczeń (więzów) zapewniających
poprawność bazy danych.
Do obsługi bazy danych (rozumianej jako zbiór danych) wykorzystuje się system zarządzania
bazą danych (SZBD, ang. Database Management System, DBMS). Jest on zorganizowanym
zbiorem narzędzi umożliwiających realizację istotnych dla użytkownika operacji
na danych. Dotychczas, w oparciu o różne modele, powstało wiele systemów zarządzania
bazami danych, jednak wciąż najbardziej popularne pozostają te oparte o model relacyjny.
Niezależnie od wyboru, oprogramowanie tego typu wymaga poznania podstaw teoretycznych
modelu oraz języka wysokiego poziomu SQL (ang. Structured Query Language).
Trudności nauki należy upatrywać w różnorodności i skomplikowaniu oprogramowania
SZBD oferowanego przez różnych producentów. Autorzy tego podręcznika proponują naukę
w oparciu o PostgreSQL jako jeden z najpopularniejszych otwartych systemów zarządzania
relacyjnymi bazami danych (https://db-engines.com/en/blog_post/106).
Oprogramowanie to od wielu lat nie spada w światowym rankingu popularności SZBD.
W roku 2023 PostgreSQL zajął pierwsze miejsce w rankingu, a od wielu lat ciągle pozostaje
w pierwszej dziesiątce wśród prawie 400 wykorzystywanych SZBD wszystkich typów
(https://db-engines.com/en/).
Proponowany podręcznik zawiera wprowadzenie do podstawowych instrukcji języka
SQL wykonywanych w relacyjnych bazach danych bogato ilustrowanych przykładami oraz
zadania do samodzielnego rozwiązania utrwalające nabyte umiejętności. Czytelnik zostanie
zapoznany ze sposobem tworzenia tabel (podstawowych struktur danych w modelu
relacyjnym), ich modyfikowania, usuwania oraz manipulowania danymi. Pokażemy, jak
stosując indeksy, polepszyć czas wykonania zapytania, omawiając przy tym wybrane pod-
6
7 Przedmowa
stawowe zagadnienia związane z funkcjami języka proceduralnego plpgSQL. W ostatnim
rozdziale przedstawimy sposób tworzenia aplikacji bazodanowej w języku Java, przy czym
zakładamy, że czytelnik opanował już umiejętności programowania w tym języku.

Spis treści ebooka Podstawy baz danych PostgreSQL: przykłady i ćwiczenia

Spis treści
Przedmowa 6
1. Wprowadzenie do systemu PostgreSQL 8
1.1. Architektura systemu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
1.2. Przygotowanie środowiska . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
1.2.1. Instalowanie systemu PostgreSQL dla systemu Windows . . . . . . 9
1.2.2. Instalowanie systemu PostgreSQL dla systemu Linux . . . . . . . . 10
1.3. Polecenia klienta psql . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
1.4. Funkcje systemu informatycznego . . . . . . . . . . . . . . . . . . . . . . . 12
1.5. Komentarze . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
1.6. Typy danych i operatory języka SQL . . . . . . . . . . . . . . . . . . . . . 13
1.6.1. Typy danych . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
1.6.2. Operatory matematyczne . . . . . . . . . . . . . . . . . . . . . . . 16
1.6.3. Operatory logiczne i porównania . . . . . . . . . . . . . . . . . . . 16
1.6.4. Symbole i operatory służące do budowania wyrażeń regularnych . 18
2. Podstawowe operacje na tabelach 20
2.1. Tworzenie tabeli . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
2.2. Modyfikowanie struktury tabeli . . . . . . . . . . . . . . . . . . . . . . . . 25
2.3. Dodawanie nowych danych do tabeli . . . . . . . . . . . . . . . . . . . . . 28
2.4. Modyfikowanie i usuwanie danych . . . . . . . . . . . . . . . . . . . . . . . 30
2.5. Usuwanie tabeli . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
2.6. Zadania do samodzielnego rozwiązania . . . . . . . . . . . . . . . . . . . . 33
3. Proste zapytania do bazy danych 36
3.1. Ogólna postać polecenia SELECT . . . . . . . . . . . . . . . . . . . . . . 37
3.2. Pobieranie danych . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
3.2.1. Klauzula SELECT . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
3.2.2. Klauzula WHERE . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
3.2.3. Klauzula ORDER BY . . . . . . . . . . . . . . . . . . . . . . . . . 47
3.3. Kopiowanie danych . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
3
Spis treści 4
3.4. Zadania do samodzielnego rozwiązania . . . . . . . . . . . . . . . . . . . . 50
4. Łączenie danych 53
4.1. Łączenie poziome tabel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
4.1.1. Złączenie wewnętrzne . . . . . . . . . . . . . . . . . . . . . . . . . 54
4.1.2. Złączenie zewnętrzne . . . . . . . . . . . . . . . . . . . . . . . . . . 58
4.1.3. Iloczyn kartezjański - CROSS JOIN . . . . . . . . . . . . . . . . . . 60
4.2. Łączenie pionowe relacji . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
4.3. Zadania do samodzielnego rozwiązania . . . . . . . . . . . . . . . . . . . . 62
5. Funkcje agregujące 64
5.1. Funkcje operujące na grupach wierszy . . . . . . . . . . . . . . . . . . . . 64
5.1.1. Klauzula GROUP BY . . . . . . . . . . . . . . . . . . . . . . . . . 66
5.1.2. Klauzula HAVING . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
5.2. Oczyszczanie danych i sprawdzanie ich jakości . . . . . . . . . . . . . . . . 70
5.3. Funkcja okna . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
5.4. Zadania do samodzielnego rozwiązania . . . . . . . . . . . . . . . . . . . . 76
6. Zagnieżdżanie zapytań 78
6.1. Kategorie podzapytań . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
6.1.1. Podzapytania niezależne . . . . . . . . . . . . . . . . . . . . . . . . 79
6.1.2. Podzapytania skorelowane . . . . . . . . . . . . . . . . . . . . . . . 82
6.2. Zagnieżdżanie rekurencyjne . . . . . . . . . . . . . . . . . . . . . . . . . . 85
6.3. Wyrażenia WITH . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
6.4. Podzapytania i funkcje agregujące w praktyce . . . . . . . . . . . . . . . . 88
6.5. Zadania do samodzielnego rozwiązania . . . . . . . . . . . . . . . . . . . . 89
7. Tworzenie i wykorzystanie perspektyw 91
7.1. Podstawowe operacje związane z perspektywami . . . . . . . . . . . . . . 91
7.2. Perspektywy w działaniu . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
7.3. Zadania do samodzielnego rozwiązania . . . . . . . . . . . . . . . . . . . . 97
8. Tworzenie i wykorzystanie indeksów 98
8.1. Indeks typu B-tree . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
8.2. Funkcje składowane . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
8.3. Przykładowa sesja . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
8.4. Skuteczne korzystanie z indeksów . . . . . . . . . . . . . . . . . . . . . . . 110
8.5. Zadania do samodzielnego rozwiązania . . . . . . . . . . . . . . . . . . . . 111
9. Transakcje 114
Spis treści 5
9.1. Kontrola współbieżności . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114
9.1.1. Blokady odczytu/zapisu . . . . . . . . . . . . . . . . . . . . . . . . 115
9.1.2. Zasięg działania blokad . . . . . . . . . . . . . . . . . . . . . . . . 115
9.2. Transakcje . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116
9.2.1. ACID . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118
9.2.2. Poziomy izolacji . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
9.2.3. Zakleszczenie . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
9.2.4. Transakcje w PostgreSQL - AUTOCOMMIT . . . . . . . . . . . . . . . 123
9.3. Zadania do samodzielnego rozwiązania . . . . . . . . . . . . . . . . . . . . 123
10. Tworzenie aplikacji bazodanowych w języku Java 125
10.1. Przygotowanie środowiska pracy . . . . . . . . . . . . . . . . . . . . . . . 125
10.2. Java JDBC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
10.3. Rodzaje sterowników w JDBC . . . . . . . . . . . . . . . . . . . . . . . . 128
10.4. Szkielet aplikacji bazodanowej w Javie . . . . . . . . . . . . . . . . . . . . 129
10.4.1. Przykład – nawiązywanie połączenia z bazą danych . . . . . . . . 129
10.5. Praca z poleceniami SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . 135
10.6. Wyjątki SQLException . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137
10.7. Przykład – tworzenie tabeli w bazie danych . . . . . . . . . . . . . . . . . 140
10.8. Przykład – wstawianie danych do tabeli . . . . . . . . . . . . . . . . . . . 140
10.9. Przykład – wykonywanie zapytań . . . . . . . . . . . . . . . . . . . . . . 142
10.10.Przykład – tworzenie aplikacji z GUI . . . . . . . . . . . . . . . . . . . . 143
10.11.Zadania do samodzielnego rozwiązania . . . . . . . . . . . . . . . . . . . 150
Bibliografia 152
Dodatek A 153
Dodatek B 160