Przed przystąpieniem do tego materiału znane powinny być ci podstawowe informacje: pojęcie baza danych, jak przechowywane są dane, co to są typy danych oraz najważniejsze, jak utworzyć i połączyć się z bazą danych oraz tworzyć tabele. Najpotrzebniejsze informacje znajdziesz w materiale: Wprowadzenie do baz danychDuq1BycqWWprowadzenie do baz danych.

Algorytm tworzenia i użytkowania bazy danych jest następujący:

  1. tworzenie struktury bazy danychStruktura baz danychstruktury bazy danych,

  2. wprowadzanie danych,

  3. modyfikacja lub usuwanie danychModyfikacja danychmodyfikacja lub usuwanie danych,

  4. tworzenie zapytań do bazy danych,

  5. wykonywanie zapytań,

  6. prezentacja danych.

W zależności od potrzeb wszystkie pozycje algorytmu mogą być wykonywane cyklicznie. W szczególności dotyczy to jednak punktów od 2 do 5, ponieważ strukturę bazy danych dla konkretnego przypadku wystarczy stworzyć raz. Cyklicznie do bazy danych dopisywane są kolejne dane, następuje modyfikacja już wprowadzonych danych, tworzone są i wykonywane zapytania oraz prezentowane wyniki.

Jedną z niewymienionych w algorytmie operacji jest modyfikacja struktury bazy danych po jej utworzeniu. Polega ona na zmianie sposobu organizacji danych w tabeli, na przykład poprzez dodawanie, usuwanie lub zmianę typu kolumn. Czynność ta może być potrzebna, jeśli chcemy zmienić coś w bazie danych, aby ulepszyć jej działanie, zwiększyć bezpieczeństwo lub liczbę funkcjonalności. Modyfikacja struktury bazy danych po wprowadzeniu danych powinna jednak wynikać z konieczności, rzadko jest to cykliczna część algorytmu, ponieważ może spowodować utratę, uszkodzenie lub niespójność danych. Jeśli już zaistnieje taka potrzeba, warto zrobić kopię zapasową danych przed zmianami.

Struktura baz danych1
Struktura baz danych

Struktura bazy danych to sposób organizacji i przechowywania danych w pamięci komputera. Składa się z elementów, takich jak:

  • Tabele;

  • Rekordy;

  • Pola;

  • Relacje;

Modyfikacja danych1
Modyfikacja danych

Modyfikacja danych w bazie danych to zmiana wartości, które są przechowywane w kolumnach tabeli. Możemy dodać, zmienić lub usunąć rekordy w tabeli. Modyfikacja danych w bazie danych może być potrzebna, jeśli chcemy zaktualizować, poprawić lub usunąć dane, które są nieprawidłowe, nieaktualne lub zbędne.

Przedstawione w tym materiale operacje wykonywane będą na prostej bazie danych (dane przechowywane będą w pojedynczej tabeli) w programie SQLiteStudio (wersja SQLiteStudio 3.3.3) dostępnym do pobrania na stronie https://sqlitestudio.pl.

Wprowadzanie danych

Wprowadzanie danych do bazy danych można realizować w różny sposób. Najłatwiejszym sposobem jest zaimportowanie (pobranie) danych z istniejących źródeł danych, bezpośrednio do tabel bazy danych. Dane mogą zostać zaimportowane na przykład z pliku z rozszerzeniem .csv. Plik powinien być wcześniej odpowiednio przygotowany, aby dane właściwie zostały wczytane. Pierwszy wiersz powinien zawierać nazwy pól odpowiadające nazwom kolumn w utworzonej tabeli w bazie danych, ponadto zgadzać powinien się również typ wprowadzanych danych.

1
Ćwiczenie 1

Zaimportuj do utworzonej przez siebie bazy danych informacje o filmach z pliku .csv. Plik z danymi możesz pobrać poniżej.

R1Mv9f4VdWvzC

Plik do pobrania zawierający bazę filmów. Baza zawiera informacje o reżyserze, tytule, gatunku, dacie powstania i ocenie filmu.

Plik do pobrania zawierający bazę filmów.
Źródło: Maciej Kacperski, licencja: CC BY 3.0.
Plik CSV o rozmiarze 2.18 KB w języku polskim

W pliku znajduje się 38 wierszy. Pierwszy wiersz w pliku jest wierszem nagłówkowym, a dane w wierszach oddzielone są znakiem średnika. Plik zawiera informacje o filmach, w każdym wierszu znajdują się następujące dane:

  • Reżyser - pole tekstowe;

  • Tytuł - pole tekstowe;

  • Gatunek - pole tekstowe;

  • Data - pole typu data;

  • Ocena - pole numeryczne typu rzeczywistego.

Pamiętaj, że typy danych w tabeli powinny być zgodne z typami importowanych danych.

W razie problemów możesz skorzystać z gotowego pliku bazy danych zamieszczonego poniżej. Plik zawiera bazę danych składającą się z tabeli o typach pól zgodnych z podanymi w pliku z danymi.

RokPH2hwzV5mO

Plik do pobrania zawierający utworzoną bazę filmów. Baza zawiera informacje o reżyserze, tytule, gatunku, dacie powstania i ocenie filmu.

Plik do pobrania zawierający utworzoną bazę filmów.
Źródło: Maciej Kacperski, licencja: CC BY 3.0.
Plik DB o rozmiarze 16.00 KB w języku polskim

Podstawowym sposobem komunikacji z bazą danych jest zapisanie zapytań w języku SQLJęzyk SQL (ang. Structured Query Language)języku SQL (ang. Structured Query Language). Język SQL jest wykorzystywany przez oprogramowanie instalowane na serwerach, komputerach, ale również do wykonywanych za pomocą przeglądarki internetowej modyfikacji danych i realizacji zapytań.

Język SQL (ang. Structured Query Language)1
Język SQL (ang. Structured Query Language)

Strukturalny język zapytań uniwersalnie stosowany w systemach bazodanowych do komunikowania się z bazą danych. Używany jest do tworzenia, modyfikowania i pobierania danych. Jest również podstawowym językiem programowania baz danych, pozwalającym na tworzenie i modyfikowanie obiektów bazy danych np. tabel.

W celu wprowadzania danych do tabeli bazy danych wykorzystuje się instrukcje języka SQL w postaci:

Linia 1. INSERT INTO nazwa podkreślnik tabeli otwórz nawias kwadratowy otwórz nawias okrągły pole1 przecinek pole2 przecinek pole3 przecinek kropka kropka kropka poleN zamknij nawias okrągły zamknij nawias kwadratowy VALUES otwórz nawias okrągły wartość1 przecinek wartość2 przecinek wartość3 przecinek kropka kropka kropka wartośćN zamknij nawias okrągły średnik.

gdzie pole1, pole2, ...poleN - oznaczają pierwsze, drugie i kolejne pola w tabeli, a wartość1, wartość2, ...wartośćN oznaczają wartości, które zostaną zapisane w rekordzie w tych kolumnach (polach).

Chcąc dodać szczegółowe dane na temat filmu do tabeli Opis_filmu, komendy napisane w języku SQL będą miały następującą składnię:

Linia 1. INSERT INTO Opis podkreślnik filmu otwórz nawias okrągły Rezyser przecinek Tytul przecinek Gatunek przecinek Data przecinek Ocena zamknij nawias okrągły VALUES otwórz nawias okrągły apostrof Jan Jakub Kolski apostrof przecinek ‘Serce przecinek Serduszko’ przecinek ‘Dramat’ przecinek ‘2014‑11‑21’ przecinek 5 kropka 6 zamknij nawias okrągły średnik. Linia 2. INSERT INTO Opis podkreślnik filmu VALUES otwórz nawias okrągły ‘Luc Besson apostrof przecinek ‘Leon zawodowiec’ przecinek ‘Dramat’ przecinek ‘1994‑09‑14’ przecinek 8 kropka 2 zamknij nawias okrągły średnik.

Przedstawione powyżej instrukcje w języku SQL umożliwiają dodanie kolejnych dwóch rekordów do tabeli Opis_filmu. Można zauważyć, że składnia tych instrukcji jest nieco inna. W pierwszej zastosowano pełną składnię polecenia INSERT, w drugiej zastosowano uproszczoną składnię bez podawania wszystkich kolumn. W drugim przypadku, jeśli składnia będzie poprawna to dane zostaną wstawione automatycznie do kolejnych kolumn.

Dane wprowadzane są do tabeli poprzez dodanie jednego rekordu. Dodawanie wielu rekordów za pomocą języka SQL realizowane jest cyklicznie za pomocą polecenia INSERT z różnymi argumentami. Komendy wpisywać możesz w edytorze SQL dostępnym w większości programów do baz danych.

2
Ćwiczenie 2

Do utworzonej bazy Filmy wprowadź rekordy zawierające dane o twoich ulubionych filmach.

Modyfikacja danych

Kolejną operacją, jaką można wykonać na rekordach zapisanych w tabelach bazy danych, jest operacja modyfikacji danych. Oznacza ona zmianę zawartości pól w rekordach. Składnia polecenia w języku SQL jest następująca:

Linia 1. UPDATE nazwa podkreślnik tabeli. Linia 2. SET pole1 znak równości wartość1 przecinek pole2 znak równości wartość2 kropka kropka kropka kropka przecinek poleN znak równości wartośćN. Linia 3. WHERE otwórz nawias kwadratowy warunek zamknij nawias kwadratowy średnik.

W celu zdefiniowania warunku, który określi, w jakim rekordzie chcemy zmienić dane należy użyć klauzuli WHERE.

Uwaga!

Jeżeli w instrukcji UPDATE nie zostanie zdefiniowany warunek, to zmienione zostaną wartości we wszystkich rekordach tabeli.

Sprawdzimy, jak działa instrukcja UPDATE ... SET modyfikując wcześniej zapisany rekord z filmem o tytule Leon zawodowiec, zmieniając jego ocenę na 9.2.

Linia 1. UPDATE Opis podkreślnik filmu. Linia 2. SET Ocena znak równości 9 kropka 2. Linia 3. WHERE Tytul znak równości ’Leon zawodowiec’ średnik.

Po wykonaniu polecenia UPDATE zawartość tabeli Opis_filmu dla rekordu Tytul=’Leon zawodowiec’ w polu Ocena zostanie zmieniona.

1
Ćwiczenie 3

W utworzonej bazie Filmy zmień ocenę dwóch filmów wybraną metodą.

Wyszukiwanie informacji w bazie danych

Jedną z najczęściej wykonywanych operacji w bazach danych jest operacja wyszukiwania danych. Można ją wykonać, korzystając z interfejsu programu lub przy pomocy zapytań napisanych w języku SQL.

Operacja wyszukiwania rekordów spełniających określone kryteria była już realizowana przy okazji użycia poprzednich poleceń związanych z modyfikacją danych, gdzie za pomocą klauzuli WHERE zostały wybrane rekordy podlegające modyfikacji.

Wyszukiwanie rekordów w tabeli w interfejsie programu jest realizowane poprzez zastosowanie filtrowania danych w zakładce widoku tabeli lub widoku formularza. W pole wyszukiwania wprowadzany jest szukany ciąg znaków. W widoku tabeli pokazywane są rekordy spełniające kryterium filtrowania danych.

R1ZBNo8b1QTY2
Zrzut ekranu programu SQLiteStudio z filtrowaniem danych.
Źródło: GroMar, licencja: CC BY 3.0.

Takie filtrowanie posiada oczywiście znaczne ograniczenia. Bez problemu można wyszukać zadane ciągi znaków, liczby, czy daty, ale w tym trybie przeszukiwane są jednocześnie wszystkie kolumny i szukając np. filmów z oceną 8, pojawią się również filmy mające liczbę 8 w swojej dacie. Nie zadziała również stawianie kryteriów typu większe, mniejsze lub równe i tym podobne. Szukając filmów z oceną większą niż 8, wpisując >8, nie dostaniemy żadnych wyników, chyba, że jakaś ocena zawiera ciąg znaków >8.

Instrukcją w języku SQL realizującą zapytania do bazy danych jest instrukcja SELECT. Składnia instrukcji jest następująca:

Linia 1. SELECT pole1 przecinek pole2 przecinek poleaN. Linia 2. FROM nazwa podkreślnik tabeli. Linia 3. WHERE otwórz nawias kwadratowy warunek zamknij nawias kwadratowy.

W instrukcji występują już znane elementy określające pola tabeli, nazwę tabeli oraz warunki wyszukiwania. Instrukcja SELECT ma znacznie większe możliwości niż proste filtrowanie dostępne w interfejsie użytkownika. Możemy wybrać, jakie pola z tabeli będą prezentowane w końcowym widoku, a także warunki wyboru rekordów mogą dotyczyć każdego pola.

Przykładowe zapytanie dotyczące ocen filmu dla Opis_filmu wyświetli wszystkie informacje o filmach, które mają ocenę większą niż 7. Kod polecenia będzie następujący:

Linia 1. SELECT Rezyser przecinek Tytul przecinek Gatunek przecinek Data przecinek Ocena. Linia 2. FROM Opis podkreślnik filmu. Linia 3. WHERE Ocena zamknij nawias ostrokątny 7 średnik.

W wyniku działania tego polecenia otrzymamy rekordy, które spełniają podany warunek. Wyniki mogą być zaprezentowane w widoku siatki lub formularza.

2
Ćwiczenie 4

W utworzonej bazie Filmy z zaimportowaną biblioteką filmów z Ćwiczenia 1 wyszukaj filmy z datą premiery między styczniem 2001 a grudniem 2006. Wyświetl tylko ich tytuł, datę premiery oraz ocenę. Plik z danymi możesz również pobrać poniżej.

RzUewm31IuxpL

Plik do pobrania zawierający bazę filmów. Baza zawiera informacje o reżyserze, tytule, gatunku, dacie powstania i ocenie filmu.

Plik do pobrania zawierający bazę filmów.
Źródło: Maciej Kacperski, licencja: CC BY 3.0.
Plik CSV o rozmiarze 2.18 KB w języku polskim

Usuwanie danych

Czasem zachodzi potrzeba usunięcia błędnych danych. Usunięcie danych polega na skasowaniu całego rekordu lub grupy rekordów w tabeli. Operacja usuwania danych może być realizowana za pomocą interfejsu programu w widoku tabeli lub widoku formularza.

Operację usuwania danych można również wykonać za pomocą języka SQL. Składnia polecenia jest następująca:

Linia 1. DELETE FROM nazwa podkreślnik tabeli. Linia 2. WHERE otwórz nawias kwadratowy warunek zamknij nawias kwadratowy średnik.

Jak widać, składnia tego polecenia, oprócz wskazania tabeli, zawiera tylko klauzulę WHERE. Właściwe zdefiniowanie warunku umożliwi usunięcie odpowiedniego rekordu.

Uwaga!

Jeżeli w instrukcji DELETE nie zostanie zdefiniowany warunek, to zostaną usunięte wszystkie rekordy w tabeli.

Utwórzmy w edytorze SQL kod polecenia, które usunie rekord z tabeli Opis_filmu o wartości pola Tytul równej Terminator.

Linia 1. DELETE FROM Opis podkreślnik filmu. Linia 2. WHERE Tytul znak równości ’Terminator’ średnik.

Po wprowadzeniu kodu do okna zapytań SQL i wykonaniu zapytania otrzymamy wynik w postaci nowej zawartości tabeli.

Ważne!

Użycie instrukcji DELETE musi być przeprowadzone bardzo ostrożnie, gdyż w większości baz danych kasowanie rekordów jest ostateczne. Nie będziemy mogli odzyskać usuniętych danych.

2
Ćwiczenie 5

W utworzonej bazie Filmy z zaimportowaną biblioteką filmów z Ćwiczenia 1 usuń wszystkie rekordy wybranego reżysera. Wybranym reżyserem, może być na przykład Steven Spielberg.Plik z danymi możesz również pobrać poniżej.

Rr7fB2pD9jegK

Plik do pobrania zawierający bazę filmów. Baza zawiera informacje o reżyserze, tytule, gatunku, dacie powstania i ocenie filmu.

Plik do pobrania zawierający bazę filmów.
Źródło: Maciej Kacperski, licencja: CC BY 3.0.
Plik CSV o rozmiarze 2.18 KB w języku polskim

Sortowanie danych w bazie danych

Dane w postaci rekordów są wprowadzane do tabel bazy danych w sposób nieuporządkowany. Najczęściej stosowanym sposobem uporządkowania prezentowanej struktury danych jest stosowanie sortowania rekordów z zastosowaniem określonych kryteriów. Przykładem sposobu sortowania może być wyświetlanie listy uczniów, gdzie nazwiska i imiona są uporządkowane alfabetycznie.  W bazie danych operacja sortowania wyświetlanych danych może zostać wykonana za pomocą interfejsu użytkownika lub za pomocą poleceń języka SQL.

Realizacja sortowania danych za pomocą interfejsu użytkownika jest dostępna w programie SQLiteStudio w zakładce Dane dla Widoku Siatki. Należy wskazać kursorem myszy wybrane pole tabeli poprzez wskazanie nazwy pola w nagłówku tabeli i kliknięcie lewym przyciskiem myszki. Wskazana nazwa pola zmieni wygląd, a dane zostaną posortowane w kolejności alfabetycznej.

R1YkR74FLnprc
Zrzut ekranu programu SQLiteStudio z posortowanymi alfabetycznie danymi. 
Źródło: GroMar, licencja: CC BY 3.0.

Uniwersalnym sposobem sortowania danych w bazach danych jest zastosowanie instrukcji języka SQL. Składnia instrukcji sortującej dane umożliwia ich sortowanie według wielu pól.

Linia 1. SELECT nazwa podkreślnik kolumny przecinek nazwa podkreślnik kolumny. Linia 2. FROM nazwa podkreślnik tabeli. Linia 3. ORDER BY nazwa podkreślnik kolumny przecinek nazwa podkreślnik kolumny ASC kreska pionowa DESC średnik.

Polecenie SELECT umożliwia dokonanie wyboru pól, które mają być prezentowane w widoku końcowym. Polecenie FROM określa tabelę, na której mamy wykonać operację sortowania danych. Poleceniem odpowiedzialnym za operację sortowanie jest instrukcja ORDER BY, której parametrami są nazwy pól oraz parametry określające rodzaj sortowania:

  • ASC –rosnąco (alfabetyczne)

  • DESC – malejąco.

W poniższym przykładzie zapytanie wybierze dwa pola z tabeli Opis_filmu, będą to pola Rezyser i Tytul. Rekordy zostaną uporządkowane rosnąco dla dwóch pól Rezyser oraz Tytul. Kod polecenia będzie następujący:

Linia 1. SELECT Rezyser przecinek Tytul. Linia 2. FROM Opis podkreślnik filmu. Linia 3. ORDER BY Rezyser przecinek Tytul ASC średnik.

Wynikiem działania tego polecenia są wszystkie rekordy z tabeli Opis_filmu uporządkowane rosnąco – w porządku alfabetycznym. Wyświetlone zostaną tylko pola RezyserTytul. Wyniki mogą być zaprezentowane w widoku siatki lub formularza.

1
Ćwiczenie 6

W utworzonej bazie Filmy z zaimportowaną biblioteką filmów z Ćwiczenia 1 posortuj tabelę tytułów filmowych po gatunku. W obrębie jednego gatunku filmy powinny być posortowane od najlepiej do najgorzej ocenianych. Jeśli kilka filmów w jednym gatunku ma takie same oceny, tytuły uporządkuj alfabetycznie. Za pomocą polecenia SQL wykonaj takie samo sortowanie, ale wyświetl tylko tytuł i datę powstania filmu. Plik z danymi możesz również pobrać poniżej.

Rr7fB2pD9jegK

Plik do pobrania zawierający bazę filmów. Baza zawiera informacje o reżyserze, tytule, gatunku, dacie powstania i ocenie filmu.

Plik do pobrania zawierający bazę filmów.
Źródło: Maciej Kacperski, licencja: CC BY 3.0.
Plik CSV o rozmiarze 2.18 KB w języku polskim

Wykorzystaj poniższy dzienniczek do zapisania swoich notatek lub przemyśleń.

Rd0uhhX3e7r1m
Źródło: GroMar, licencja: CC BY 3.0.