BAZY DANYCH · INF.03

Funkcje, raporty i bezpieczeństwo

Raport dla dyrekcji wymaga dat, tekstu i warunków. Poznasz funkcje MySQL, wyrażenie CASE, przygotowywanie raportów oraz zasady uprawnień i ochrony przed SQL injection.

Funkcje daty i tekstu

W bibliotece rzadko interesuje nas sama data zapisana w kolumnie. Chcemy wiedzieć, ile dni minęło od terminu zwrotu, w jakim miesiącu wypożyczono najwięcej książek albo jak podpisać kartę czytelnika. Do takich obliczeń służą funkcje wbudowane MySQL. Funkcja przyjmuje wartość, przetwarza ją i zwraca wynik, który można pokazać w kolumnie lub użyć w warunku.

Zacznijmy od dat. CURDATE() zwraca dzisiejszą datę, NOW() datę i czas, a YEAR(), MONTH() i DAY() wyciągają z daty jej część. Najbardziej przydatna w bibliotece jest DATEDIFF(data_późniejsza, data_wcześniejsza), która zwraca liczbę dni między datami. Wynik może być ujemny, jeśli pierwsza data jest wcześniejsza — to nie błąd, tylko informacja o kierunku obliczenia.

SELECT w.id,
       k.tytul,
       w.data_zwrotu,
       w.termin_zwrotu,
       DATEDIFF(w.data_zwrotu, w.termin_zwrotu) AS dni_spoznienia
FROM wypozyczenia w
JOIN ksiazki k ON k.id = w.ksiazka_id
WHERE w.data_zwrotu IS NOT NULL
  AND DATEDIFF(w.data_zwrotu, w.termin_zwrotu) > 0
ORDER BY dni_spoznienia DESC, w.id;

Drugi przykład pokazuje formatowanie daty. DATE_FORMAT(data, '%d.%m.%Y') zamienia datę na napis w polskim porządku dziennym. Samo przechowywanie daty w formacie RRRR-MM-DD jest wygodne dla bazy, ale w raporcie dla nauczyciela lepiej wygląda 02.09.2026. Pamiętaj, że formatowanie zmienia tylko wyświetlanie, a nie wartość w tabeli.

SELECT DATE_FORMAT(data_wypozyczenia, '%d.%m.%Y') AS data,
       COUNT(*) AS liczba_wypozyczen
FROM wypozyczenia
WHERE data_wypozyczenia >= '2026-09-01'
  AND data_wypozyczenia < '2026-10-01'
GROUP BY data_wypozyczenia
ORDER BY data_wypozyczenia;

Funkcje tekstowe pozwalają zbudować czytelne opisy bez zmieniania danych w tabeli. CONCAT skleja napisy, UPPER i LOWER zmieniają wielkość liter, LEFT i RIGHT biorą fragment z początku lub końca, a CHAR_LENGTH liczy znaki. Do usuwania spacji służy TRIM, a do zamiany fragmentu REPLACE. Wszystkie te funkcje można łączyć w jednym wyrażeniu.

SELECT CONCAT(UPPER(nazwisko), ' ', UPPER(LEFT(imie, 1)), '.') AS karta,
       LOWER(COALESCE(email, 'brak e-mail')) AS email,
       COALESCE(telefon, 'brak telefonu') AS telefon
FROM uczniowie
WHERE klasa_id = 1 AND aktywny = 1
ORDER BY nazwisko, imie;

Uwaga na NULL. W MySQL funkcja CONCAT zwraca NULL, jeśli choć jeden argument jest NULL. Dlatego przed sklejeniem warto zamienić brak wartości na tekst przez COALESCE lub IFNULL. CONCAT_WS zachowuje się inaczej: pomija argumenty NULL, ale nadal nie zastąpi ich napisem. W raportach z brakującymi telefonami jawny napis „brak telefonu” jest lepszy niż pusta komórka, bo czytelnik od razu wie, że danych nie ma.

Przykłady z życia

1. Karta biblioteczna. Pani bibliotekarka chce na jednej kartce widzieć „KOWALSKA A.”, adres e-mail i telefon. Dane w tabeli są rozdzielone na kolumny, ale funkcje CONCAT, UPPER i COALESCE potrafią złożyć z nich czytelny podpis bez zmieniania bazy.

2. Lista spóźnień. Na tablicy ogłoszeń pojawia się lista książek oddanych po terminie. DATEDIFF liczy dni spóźnienia, a DATE_FORMAT pokazuje datę w formacie dziennym. Uczeń od razu widzi, czy spóźnił się o dwa dni, czy o dwa tygodnie.

3. Raport miesięczny. Kierownik biblioteki chce wiedzieć, ile wypożyczeń rozpoczęto w każdym dniu września. Data jest grupowana i formatowana, a COUNT(*) liczy wpisy. Bez funkcji daty trzeba by ręcznie przepisywać dni z arkusza.

Matematyka: DATEDIFF przypomina odejmowanie punktów na osi liczbowej: wynik mówi, jak daleko od siebie leżą dwie daty. Kolejność argumentów ma znaczenie, podobnie jak kolejność liczb w odejmowaniu — zamiana miejscami zmienia znak wyniku.

Funkcje liczbowe, CASE i raporty

Raport biblioteczny rzadko kończy się na wypisaniu wierszy. Najczęściej trzeba policzyć książki w kategoriach, uśrednić ceny, zsumować kary albo podzielić czytelników na grupy. Do tego służą funkcje liczbowe i agregujące. ROUND zaokrągla wynik, CEIL w górę, FLOOR w dół, ABS usuwa znak, a MOD zwraca resztę z dzielenia. Agregaty COUNT, SUM, AVG, MIN i MAX liczą wartości dla całej grupy wierszy.

Agregat bez grupowania zwraca jeden wiersz dla całej tabeli. Jeśli chcesz osobny wynik dla każdej kategorii, dodaj GROUP BY. Wtedy każda grupa daje jeden wiersz, a w SELECT mogą wystąpić kolumny grupujące i agregaty. Kolumna, której nie ma w GROUP BY i nie jest objęta agregatem, w MySQL może zostać wybrana, ale jej wartość jest nieprzewidywalna — lepiej tego unikać.

SELECT k.nazwa AS kategoria,
       COUNT(*) AS liczba_ksiazek,
       ROUND(AVG(s.cena), 2) AS srednia_cena,
       SUM(s.stan) AS egzemplarze
FROM ksiazki s
JOIN kategorie k ON k.id = s.kategoria_id
GROUP BY k.id, k.nazwa
HAVING COUNT(*) >= 2
ORDER BY srednia_cena DESC;

Klauzula HAVING filtruje grupy po obliczeniu agregatów, a WHERE filtruje pojedyncze wiersze przed grupowaniem. To rozróżnienie jest częstym pytaniem egzaminacyjnym. Warunek „kategoria ma co najmniej dwie książki” jest warunkiem na grupie, więc należy do HAVING. Warunek „książka kosztuje więcej niż 40 zł” dotyczy wiersza, więc należy do WHERE.

SELECT tytul, cena,
       CASE
         WHEN cena < 40 THEN 'tanio'
         WHEN cena < 60 THEN 'średnio'
         ELSE 'drogo'
       END AS polka_cenowa
FROM ksiazki
ORDER BY cena;

Wyrażenie CASE działa jak instrukcja warunkowa w języku programowania. Sprawdza kolejne warunki WHEN i zwraca wartość pierwszego spełnionego. Jeśli żaden nie pasuje, zwraca wartość po ELSE, a bez ELSE — NULL. Można je stosować nie tylko w SELECT, ale też w ORDER BY i wewnątrz agregatów, na przykład do zliczania warunkowego.

SELECT k.nazwa AS kategoria,
       SUM(CASE WHEN s.dostepna = 1 THEN 1 ELSE 0 END) AS dostepne,
       SUM(CASE WHEN s.dostepna = 0 THEN 1 ELSE 0 END) AS niedostepne
FROM ksiazki s
JOIN kategorie k ON k.id = s.kategoria_id
GROUP BY k.id, k.nazwa
ORDER BY k.nazwa;

Dobry raport odpowiada na konkretne pytanie i ma jednoznaczne nagłówki. Zamiast kolumny AVG(cena) lepiej nadać alias srednia_cena, a zamiast COUNT(*) — liczba_ksiazek. Wartości pieniężne zaokrąglaj do dwóch miejsc, a liczby egzemplarzy pozostawiaj całkowite. Po wykonaniu raportu sprawdź, czy suma grup zgadza się z sumą dla całej tabeli — to prosty test, czy nie zgubiłeś wierszy przez JOIN lub zbyt wąski warunek.

Przykłady z życia

1. Podsumowanie kiermaszu. Po kiermaszu książek nauczyciel chce wiedzieć, ile pozycji sprzedano w każdej kategorii i jaka była średnia cena. GROUP BY daje osobny wiersz dla kategorii, a SUM i AVG liczą wartości. Bez grupowania powstałby jeden ogólny wynik, z którego nie da się odczytać, co się sprzedało najlepiej.

2. Progi cenowe w księgarni. Sprzedawca oznacza książki metkami „do 40 zł”, „40–60 zł” i „powyżej 60 zł”. Wyrażenie CASE przypisuje każdą książkę do właściwego przedziału, a potem można policzyć, ile jest pozycji w każdej grupie. To szybki sposób na przygotowanie oferty promocyjnej.

3. Raport kar. Kierownik chce sumę kar według klas i średnią karę dla wypożyczeń, które w ogóle zostały obciążone opłatą. Warunek „kara większa od zera” trafia do WHERE, a sumowanie i średnia do agregatów. Dzięki temu średnia nie jest zaniżona przez wypożyczenia bez kary.

Geografia: klimatolodzy liczą średnią temperaturę dla regionu, a nie dla całego kraju, bo grupowanie po regionie ujawnia różnice, które giną w jednej średniej. Tak samo raport biblioteki grupuje książki po kategorii, żeby porównywać podobne zbiory, a nie mieszać podręczniki z komiksami.

Matematyka: AVG to średnia arytmetyczna, a ROUND zaokrągla wynik do wskazanej liczby miejsc. SUM dodaje składniki, ale tylko w obrębie jednej grupy. To dokładnie te operacje, które wykonujesz w tabeli na lekcji matematyki, tyle że SQL robi je dla tysięcy wierszy naraz.

Uprawnienia, SQL injection i kopie zapasowe

Baza biblioteki zawiera dane uczniów, więc dostęp do niej musi być kontrolowany. W MySQL każdy użytkownik ma nazwę, host i zestaw uprawnień. Zamiast pracować na koncie root, które może wszystko, tworzymy osobne konto dla bibliotekarza i przyznajemy mu tylko potrzebne operacje. To zasada najmniejszych uprawnień: konto raportowe potrzebuje SELECT, konto obsługi wypożyczeń także INSERT i UPDATE, a usuwanie rezerwacji może być ograniczone do jednej tabeli.

CREATE USER 'bibliotekarz'@'localhost'
  IDENTIFIED BY 'SilneHaslo2026!';

GRANT SELECT, INSERT, UPDATE
  ON biblioteka_szkolna.*
  TO 'bibliotekarz'@'localhost';

GRANT DELETE
  ON biblioteka_szkolna.rezerwacje
  TO 'bibliotekarz'@'localhost';

SHOW GRANTS FOR 'bibliotekarz'@'localhost';

Uprawnienia można odbierać poleceniem REVOKE. Jeśli bibliotekarz przestaje usuwać rezerwacje, odbieramy mu DELETE tylko z tej tabeli, nie ruszając pozostałych praw. Konto root w XAMPP często nie ma hasła i działa wyłącznie lokalnie — to wygodne w pracowni, ale niedopuszczalne na serwerze publicznym. Osobne konto z hasłem i ograniczonym hostem to minimum bezpieczeństwa.

Drugie zagrożenie to SQL injection. Powstaje, gdy aplikacja skleja zapytanie z danymi od użytkownika. Jeśli pole wyszukiwania przyjmie tekst ' OR '1'='1, naiwnie zbudowane zapytanie może zwrócić wszystkie książki zamiast jednej. W gorszym scenariuszu atakujący dopisze polecenie DELETE lub UPDATE. Samo „ukrycie” formularza nie chroni — każdy może wysłać żądanie bez przeglądarki.

-- Przykład niebezpiecznego zlepienia danych (nie rób tak w aplikacji):
-- SELECT id, tytul FROM ksiazki WHERE tytul = '' OR '1'='1';

-- Bezpieczne rozwiązanie w PHP: zapytanie parametryzowane
-- $stmt = $pdo->prepare('SELECT id, tytul FROM ksiazki WHERE tytul LIKE ?');
-- $stmt->execute(['%' . $szukany . '%']);

W zapytaniu parametryzowanym znaki zapytania są miejscami na wartości, a nie częścią tekstu SQL. Sterownik wysyła zapytanie i dane osobno, więc nawet cudzysłów w polu wyszukiwania nie zmieni struktury polecenia. Dodatkowo walidujemy typ danych, ograniczamy długość pól i nie pokazujemy użytkownikowi surowych błędów bazy. Uprawnienia ograniczają skutki ewentualnego włamania: konto raportowe z samym SELECT nie usunie tabeli, nawet jeśli injection się uda.

Trzecim filarem jest kopia zapasowa. Kopia logiczna to plik SQL z poleceniami CREATE TABLE i INSERT, tworzony przez mysqldump lub eksport w phpMyAdmin. Kopia fizyczna to kopia plików danych — szybsza przy dużych bazach, ale trudniejsza w przenoszeniu. W szkolnym projekcie wystarczy logiczna, wykonywana regularnie i przed każdą ryzykowną zmianą. Trzymaj ją poza katalogiem publicznym i nie wysyłaj mailem, jeśli zawiera dane osobowe.

-- Wiersz poleceń Windows, nie edytor SQL:
-- mysqldump -u root -p --single-transaction --result-file=kopia.sql biblioteka_szkolna

-- Po odtworzeniu sprawdź liczby wierszy:
SELECT COUNT(*) AS uczniowie FROM uczniowie;
SELECT COUNT(*) AS wypozyczenia FROM wypozyczenia;

Kopia bez testu odtworzenia jest obietnicą, nie zabezpieczeniem. Przynajmniej raz na jakiś czas zaimportuj ją do osobnej bazy i sprawdź, czy tabele powstają, czy klucze obce są poprawne i czy liczby wierszy się zgadzają. W razie incydentu kolejność jest prosta: odetnij dostęp, zmień hasła, sprawdź logi, oceń zakres zmian, odtwórz dane z kopii i dopiero potem wracaj do pracy. Pośpiech bez kopii zwykle powiększa szkody.

Przykłady z życia

1. Klucz do biblioteki. Woźny ma klucz do wszystkich drzwi, bibliotekarz tylko do wypożyczalni, a uczeń w ogóle nie wchodzi za ladę. Uprawnienia w bazie działają tak samo: każde konto dostaje dostęp do tych tabel i operacji, których naprawdę potrzebuje.

2. Zgubiony telefon z dostępem do dziennika. Nauczyciel traci telefon z zapisanym hasłem. Dobrą reakcją jest natychmiastowa zmiana hasła i odebranie uprawnień, a nie czekanie do końca dnia. Kopia zapasowa pozwala sprawdzić, czy w tym czasie nie zmieniono ocen.

3. Kopia przed aktualizacją. Przed wgraniem nowej wersji aplikacji administrator robi eksport bazy i testuje odtworzenie na komputerze lokalnym. Jeśli aktualizacja uszkodzi dane, wraca do kopii w kilka minut, zamiast odtwarzać wszystko ręcznie.

Informatyka: hasło do bazy jest jak klucz w kodzie aplikacji: nie powinno leżeć w repozytorium ani w pliku dostępnym przez przeglądarkę. Konfigurację trzyma się poza katalogiem publicznym, a w repozytorium zapisuje się tylko przykład bez prawdziwych danych.

Ćwiczenia SQL

Zadania do samodzielnego wykonania w edytorze SQL.

• 1Ile dni po terminie?Przygotuj listę zwróconych książek oddanych po terminie wraz z liczbą dni spóźnienia.Zadanie i podpowiedzi →
• 2Karty czytelników klasy 1AZbuduj czytelne karty uczniów klasy 1A z użyciem funkcji tekstowych i obsługi NULL.Zadanie i podpowiedzi →
• 3Raport kategoriiPolicz książki i średnie ceny w kategoriach, pokazując tylko te z co najmniej dwiema książkami.Zadanie i podpowiedzi →
• 4Archiwum zwróconych wypożyczeńUtwórz tabelę archiwalną i przenieś do niej kopię wszystkich zwróconych wypożyczeń.Zadanie i podpowiedzi →