SQL - podstawy

Materiał przygotowany z pomocą AI

Dane w przykładach

Wartości użyte w przykładach (stany wody, przepływy, rzędne zera wodowskazów, identyfikatory stacji) są ilustracyjne — wybrane tak, żeby były wiarygodne dla danej stacji, ale nie były weryfikowane i mogą różnić się od wartości rzeczywistych.

Środowisko do uruchomienia

Polecenia SQL można wykonać w dowolnym kliencie SQLite (sterownik sqlite3 jest częścią standardowej biblioteki Pythona). Wszystkie bloki w tym artykule zweryfikowano w SQLite 3.52 z poziomu Pythona 3.12.12. Pełna instrukcja odtworzenia środowiska: Środowisko Python dla przykładów.

SQL (Structured Query Language) to język służący do komunikacji z relacyjnymi bazami danych — zadawania pytań o dane, ich modyfikowania oraz definiowania struktury tabel. Jest standardem ISO/ANSI od 1986 roku i mimo upływu czasu pozostaje fundamentem pracy z danymi w niemal każdym systemie informatycznym.

Ten wpis przedstawia podstawy: czym jest baza danych, jak działa model relacyjny, jakie są kategorie poleceń SQL i jak najczęściej się ich używa. Przykłady zostały oparte na typowych zbiorach danych z hydrologii i meteorologii — stacjach pomiarowych i seriach obserwacji.

Czym jest baza danych

Baza danych to uporządkowany zbiór informacji wraz z mechanizmem, który pozwala je zapisywać, odczytywać i wyszukiwać w sposób spójny i bezpieczny — niezależnie od tego, czy korzysta z niej jeden użytkownik, czy tysiące jednocześnie.

W przeciwieństwie do pliku CSV, który po prostu przechowuje wiersze tekstu, baza danych:

• pilnuje typów danych (przepływ nie wyląduje przypadkiem w polu daty);
• zapewnia integralność (np. nie pozwoli zapisać pomiaru przypisanego do nieistniejącej stacji);
• obsługuje transakcje — operacje wykonywane jako całość (albo wszystko, albo nic);
• umożliwia wyszukiwanie po indeksach, co jest dużo szybsze niż przeglądanie pliku linia po linii;
• zarządza dostępem wielu użytkowników naraz bez utraty danych.

Programem, który dostarcza te wszystkie funkcje, jest system zarządzania bazą danych (DBMS, ang. Database Management System). Sama „baza danych” to zwykle konkretny zbiór tabel i danych obsługiwany przez DBMS.

Rodzaje baz danych

Bazy danych dzielimy zgrubnie ze względu na model przechowywania danych:

Typ	Przykładowe systemy	Krótko o modelu
Relacyjne	PostgreSQL, MySQL/MariaDB, SQLite, Oracle	Dane w tabelach, powiązania przez klucze
Dokumentowe	MongoDB, CouchDB	Dokumenty JSON-podobne, schemat elastyczny
Klucz-wartość	Redis, DynamoDB	Proste pary klucz → wartość, bardzo szybkie
Grafowe	Neo4j	Węzły i relacje, sieci powiązań
Kolumnowe	ClickHouse, Cassandra	Dane przechowywane kolumnami, zoptymalizowane pod analitykę
Czasowe	InfluxDB, TimescaleDB	Szeregi czasowe, wbudowane funkcje czasu

Pozostałe rodzaje określa się zbiorczo jako NoSQL (ang. Not only SQL). Każdy z nich rozwiązuje inny problem — żaden nie jest „lepszy” w oderwaniu od kontekstu. W praktyce relacyjne bazy danych są najczęstszym wyborem domyślnym i to one są tematem tego wpisu.

Hydrologia i serie czasowe

Dla intensywnych serii czasowych (pomiary co minutę z setek stacji) warto rozważyć bazy czasowe (InfluxDB, TimescaleDB — to ostatnie jest rozszerzeniem PostgreSQL). Dla typowej pracy analitycznej z danymi historycznymi (pomiary w skali doby) klasyczny PostgreSQL w zupełności wystarcza.

Bazy relacyjne

Idea jest prosta: dane przedstawiamy jako tabele (zwane też relacjami), w których wiersze opisują pojedyncze obiekty, a kolumny — ich cechy.

Tabela, wiersz, kolumna

Tabela stacje — wykaz stacji hydrologicznych:

id	kod_imgw	nazwa	rzeka	dorzecze
1	152160140	Poznań-Most Rocha	Warta	Odra
2	152210170	Warszawa-Bulwary	Wisła	Wisła
3	351180435	Kalisz	Prosna	Odra

• Tabela ma nazwę (stacje) i sztywno zdefiniowaną listę kolumn z typami danych
• Kolumna opisuje jeden atrybut — jej typ (np. liczba, tekst, data) jest zdefiniowany przy tworzeniu tabeli
• Wiersz (rekord) to jeden konkretny obiekt — jedna stacja, jeden pomiar, jedno zdarzenie
• Klucz główny (ang. primary key, tu: id) — kolumna, której wartość jednoznacznie identyfikuje wiersz w tabeli

Klucz główny i klucz obcy

Powiązania między tabelami realizujemy przez klucz obcy (ang. foreign key) — kolumnę, która wskazuje na klucz główny w innej tabeli.

Tabela pomiary — codzienne odczyty stanu wody i przepływu:

id	stacja_id	data_pomiaru	stan_wody	przeplyw
101	1	2024-05-10 06:00:00	245	64.200
102	1	2024-05-11 06:00:00	240	61.500
103	2	2024-05-10 06:00:00	312	542.000
104	1	2024-05-12 06:00:00	238	58.300

Kolumna stacja_id jest kluczem obcym — wskazuje, do której stacji z tabeli stacje należy pomiar. Stacja o id = 1 (Poznań-Most Rocha) ma trzy pomiary, stacja o id = 2 (Warszawa Bulwary) — jeden. Stan wody podajemy w centymetrach względem zera wodowskazu, przepływ — w m³/s.

Schemat i normalizacja

Cały układ tabel — ich nazwy, kolumny, typy, klucze i powiązania — nazywamy schematem bazy danych. Projektowanie schematu polega na takim podziale danych na tabele, żeby:

• nie duplikować informacji (zamiast wpisywać nazwę i lokalizację stacji w każdym pomiarze, wpisujemy je raz w stacje i odwołujemy się przez stacja_id);
• każda tabela opisywała jeden typ obiektu (stacja, pomiar, opad, krzywa konsumpcyjna — osobno);
• powiązania między tabelami były jasne i jednoznaczne.

Ten proces nazywa się normalizacją. Dzielenie danych na osobne tabele ułatwia ich aktualizację (zmiana współrzędnych stacji w jednym miejscu zamiast w setkach tysięcy pomiarów), ale zwiększa liczbę tabel, które trzeba łączyć przy odczycie. Granicę dobiera się pragmatycznie.

ACID — gwarancje transakcji

Relacyjne bazy danych zapewniają zestaw gwarancji znanych jako ACID:

• Atomicity (atomowość) — transakcja wykonuje się w całości albo wcale
• Consistency (spójność) — po transakcji baza jest w poprawnym stanie (klucze obce się zgadzają, ograniczenia są spełnione)
• Isolation (izolacja) — równoczesne transakcje nie wpływają na siebie nawzajem
• Durability (trwałość) — zapisane dane przetrwają awarię systemu

Dzięki ACID można bezpiecznie obsługiwać operacje, w których częściowy stan jest gorszy niż brak zmiany — np. korektę całej serii pomiarów po zmianie zera wodowskazu razem z aktualizacją metadanych stacji.

Popularne silniki RDBMS

Silnik	Charakterystyka
PostgreSQL	Open source, bardzo zaawansowany, mocne wsparcie dla typów (JSON, geometrie przez PostGIS), pierwszy wybór w nowych projektach
MySQL/MariaDB	Open source, prostszy, szeroko stosowany w aplikacjach webowych
SQLite	Plik z bazą danych zamiast serwera; idealny do prototypów, aplikacji desktopowych i mobilnych
MS SQL Server	Microsoft, popularny w środowisku korporacyjnym i .NET
Oracle	Komercyjny, używany w dużych systemach bankowych i ERP

Wszystkie obsługują standard SQL, ale każdy ma własne dialekty — drobne różnice w składni i zestawie funkcji. Większość poleceń pokazanych poniżej zadziała we wszystkich, ale przy zaawansowanych zapytaniach zawsze warto sprawdzić dokumentację konkretnego silnika.

PostGIS dla danych przestrzennych

Jeśli przechowujesz lokalizacje stacji, przebiegi rzek czy granice zlewni, wybór PostgreSQL z rozszerzeniem PostGIS jest niemal naturalny — daje typy geometryczne, indeksy przestrzenne i bogaty zestaw funkcji (ST_Distance, ST_Within, ST_Intersects).

Czym jest SQL

SQL jest językiem deklaratywnym — opisujemy, co chcemy uzyskać, a nie jak to obliczyć. Silnik bazy danych sam decyduje, w jakiej kolejności przeczytać tabele, których indeksów użyć i jak połączyć dane. Tym różni się od języków proceduralnych (Python, Java), gdzie krok po kroku opisujemy algorytm.

Polecenia SQL dzielimy na cztery kategorie:

Skrót	Nazwa	Co robi
DDL	Data Definition Language	Definiuje strukturę bazy: CREATE, ALTER, DROP
DML	Data Manipulation Language	Operuje na danych: SELECT, INSERT, UPDATE, DELETE
DCL	Data Control Language	Zarządza uprawnieniami: GRANT, REVOKE
TCL	Transaction Control Language	Steruje transakcjami: BEGIN, COMMIT, ROLLBACK

Składnia

Polecenia SQL kończymy średnikiem ;. Słowa kluczowe są niewrażliwe na wielkość liter (SELECT = select), ale przyjęło się pisać je WIELKIMI literami, a nazwy tabel i kolumn — małymi. Identyfikatory ze spacjami lub znakami specjalnymi ujmujemy w cudzysłowy — składnia różni się dialektami: "moja kolumna" w PostgreSQL i SQLite (oraz w MS SQL Server przy QUOTED_IDENTIFIER ON), `moja kolumna` w MySQL (które domyślnie traktuje "..." jako literał stringa, chyba że ustawiony jest tryb ANSI_QUOTES), [moja kolumna] jako wariant typowy dla MS SQL Server.

DDL — definiowanie struktury

CREATE TABLE

Tworzy nową tabelę. Dla każdej kolumny podajemy nazwę i typ danych, opcjonalnie ograniczenia (klucze, NOT NULL, wartości domyślne).

CREATE TABLE stacje (
    id                INTEGER PRIMARY KEY,
    kod_imgw          VARCHAR(20) UNIQUE,
    nazwa             VARCHAR(100) NOT NULL,
    rzeka             VARCHAR(100),
    dorzecze          VARCHAR(50),
    rzedna_zera       DECIMAL(7, 2),
    data_uruchomienia DATE DEFAULT CURRENT_DATE
);

• PRIMARY KEY — klucz główny, wartość unikalna i niepusta
• NOT NULL — pole musi mieć wartość
• UNIQUE — wartość nie może się powtarzać (kod IMGW jest unikalny dla każdej stacji)
• DEFAULT — wartość domyślna, jeśli nie podamy własnej

Tabela z kluczem obcym — pomiary podpięte pod stacje:

CREATE TABLE pomiary (
    id            INTEGER PRIMARY KEY,
    stacja_id     INTEGER NOT NULL,
    data_pomiaru  TIMESTAMP NOT NULL,
    stan_wody     INTEGER,             -- cm względem zera wodowskazu
    przeplyw      DECIMAL(10, 3),      -- m³/s
    FOREIGN KEY (stacja_id) REFERENCES stacje(id)
);

Klauzula FOREIGN KEY ... REFERENCES mówi: kolumna stacja_id musi zawierać wartość, która istnieje jako id w tabeli stacje. Próba wstawienia pomiaru z nieistniejącym identyfikatorem stacji zostanie odrzucona — co eliminuje klasyczne „pomiary-sieroty” bez przypisanej stacji.

Najczęściej używane typy danych

Typ	Co przechowuje
INTEGER / INT	Liczby całkowite (np. stan wody w cm)
BIGINT	Duże liczby całkowite
DECIMAL(p, s)	Liczby dziesiętne ze stałą precyzją (np. przepływ, opad)
REAL, DOUBLE	Liczby zmiennoprzecinkowe (temperatura, parametry modelu); MS SQL Server używa REAL i FLOAT(n) zamiast DOUBLE
VARCHAR(n)	Tekst zmiennej długości, do n znaków
TEXT	Tekst dowolnej długości (notatki obserwatora)
DATE	Data (bez godziny)
TIMESTAMP	Data z godziną (typowo: znacznik czasu pomiaru)
BOOLEAN	Wartość logiczna TRUE/FALSE (MySQL: alias na TINYINT(1); MS SQL Server: brak — używaj BIT)
JSON / JSONB	Dokument JSON; JSONB (binarny, indeksowalny) tylko w PostgreSQL, MySQL 5.7+ ma JSON

Liczby dziesiętne — DECIMAL czy REAL?

REAL/DOUBLE (zmiennoprzecinkowe) dają błędy zaokrągleń (0.1 + 0.2 ≠ 0.3). Tam, gdzie precyzja musi być dokładnie zachowana — np. kwoty pieniężne lub odczyty kalibrowanych przyrządów, które mają być wiernie wyświetlone — używaj DECIMAL(p, s). Dla wartości fizycznych z kontrolowaną niepewnością pomiaru (przepływ z dokładnością do trzeciego miejsca, temperatura do dziesiątej) DECIMAL jest naturalnym wyborem; DOUBLE bywa OK dla parametrów modeli, gdzie precyzja na poziomie 15 cyfr znaczących wystarcza.

ALTER TABLE — zmiana struktury

-- dodanie kolumny
ALTER TABLE stacje ADD COLUMN obserwator VARCHAR(100);
 
-- usunięcie kolumny
ALTER TABLE stacje DROP COLUMN obserwator;
 
-- zmiana typu kolumny - PostgreSQL
ALTER TABLE stacje ALTER COLUMN nazwa TYPE VARCHAR(150);
 
-- MySQL/MariaDB
-- ALTER TABLE stacje MODIFY COLUMN nazwa VARCHAR(150);
 
-- MS SQL Server (bez słowa kluczowego TYPE)
-- ALTER TABLE stacje ALTER COLUMN nazwa VARCHAR(150);

SQLite a ALTER COLUMN

SQLite nie obsługuje zmiany typu kolumny przez ALTER TABLE. Standardowa procedura: utworzyć nową tabelę z poprawnym typem, skopiować dane przez INSERT INTO ... SELECT, usunąć starą i zmienić nazwę nowej (ALTER TABLE ... RENAME TO ...).

DROP TABLE — usunięcie tabeli

DROP TABLE pomiary;

DROP jest nieodwracalny

DROP TABLE usuwa tabelę razem z wszystkimi danymi. Bez backupu nie ma drogi powrotnej. Niektóre silniki obsługują DROP TABLE IF EXISTS (nie wyrzuci błędu, jeśli tabela nie istnieje) — ale to nie chroni przed pomyłkową nazwą. Pamiętaj też o kolejności: najpierw kasujemy pomiary (tabela z kluczem obcym), potem stacje (tabela nadrzędna), inaczej silnik odrzuci DROP ze względu na zależność.

DML — operowanie na danych

INSERT — wstawianie wierszy

-- wszystkie kolumny w kolejności definicji
INSERT INTO stacje VALUES (1, '152160140', 'Poznań-Most Rocha', 'Warta', 'Odra', 49.54, '1820-01-01');
 
-- z wybraniem kolumn (zalecane)
INSERT INTO stacje (kod_imgw, nazwa, rzeka, dorzecze)
VALUES ('152210170', 'Warszawa Bulwary', 'Wisła', 'Wisła');
 
-- wiele wierszy naraz
INSERT INTO stacje (kod_imgw, nazwa, rzeka, dorzecze) VALUES
    ('351180435', 'Kalisz', 'Prosna', 'Odra'),
    ('150170300', 'Wrocław Trestno', 'Odra', 'Odra'),
    ('151210015', 'Kraków Bielany', 'Wisła', 'Wisła');

Wskazanie kolumn ((kod_imgw, nazwa, rzeka, dorzecze)) jest bezpieczniejsze niż wstawianie po kolejności — jeśli ktoś doda nową kolumnę do tabeli, zapytanie nie zacznie nagle wstawiać złych wartości w niewłaściwe miejsca.

SELECT — odczyt danych

To najczęściej używane polecenie w SQL. Pełna struktura:

SELECT kolumny
FROM tabela
WHERE warunek
GROUP BY kolumny
HAVING warunek
ORDER BY kolumny
LIMIT n;

Najprostszy przykład:

SELECT * FROM stacje;

* oznacza „wszystkie kolumny”. W praktyce lepiej wymienić te, których potrzebujemy:

SELECT kod_imgw, nazwa, rzeka FROM stacje;

DISTINCT — eliminacja duplikatów

SELECT DISTINCT zwraca tylko unikalne wiersze:

-- unikalna lista rzek występujących w tabeli stacji
SELECT DISTINCT rzeka FROM stacje;
 
-- unikalne pary rzeka + dorzecze
SELECT DISTINCT rzeka, dorzecze FROM stacje;

DISTINCT działa na całym zestawie wybranych kolumn. DISTINCT rzeka, dorzecze zwraca unikalne pary, a nie tylko unikalne rzeki — jeśli ta sama rzeka płynie przez kilka dorzeczy (rzadkie, ale możliwe na granicach administracyjnych), pojawi się więcej niż raz.

WHERE — filtrowanie wierszy

SELECT * FROM stacje
WHERE rzeka = 'Warta';
 
SELECT * FROM pomiary
WHERE przeplyw > 100 AND data_pomiaru >= '2024-05-01';
 
SELECT * FROM stacje
WHERE rzeka IN ('Warta', 'Wisła', 'Prosna');
 
SELECT * FROM stacje
WHERE nazwa LIKE 'Poznań%';

Operatory:

Operator	Znaczenie
=, <> (lub !=)	Równe, różne
<, >, <=, >=	Porównania
BETWEEN a AND b	W zakresie (włącznie z końcami)
IN (...)	Należy do listy
LIKE 'wzór'	Pasuje do wzorca (% = dowolny ciąg, _ = jeden znak)
IS NULL, IS NOT NULL	Sprawdzenie braku wartości
AND, OR, NOT	Operatory logiczne

NULL to nie to samo, co „brak”

NULL w SQL oznacza „wartość nieznana”. W danych pomiarowych pojawia się często — np. gdy w danym dniu nie wykonano odczytu albo czujnik był uszkodzony. Porównanie kolumna = NULL zawsze daje wynik nieznany (nie TRUE ani FALSE), więc taki warunek nigdy nie zwróci wierszy. Do sprawdzenia używamy IS NULL / IS NOT NULL. Funkcje agregujące (AVG, SUM) ignorują NULL — co bywa pożądane (pomijamy luki) lub mylące (uważaj, gdy mówisz „średnia z całej serii”).

ORDER BY — sortowanie

SELECT * FROM stacje
ORDER BY rzeka ASC, nazwa ASC;       -- rosnąco (domyślnie)
 
SELECT * FROM pomiary
ORDER BY data_pomiaru DESC, przeplyw DESC; -- malejąco po dacie, potem po przepływie

LIMIT i OFFSET — ograniczenie liczby wyników i paginacja

SELECT * FROM pomiary
ORDER BY przeplyw DESC
LIMIT 10;       -- 10 najwyższych zaobserwowanych przepływów
 
-- paginacja: druga "strona" po 10 wyników
SELECT * FROM pomiary
ORDER BY data_pomiaru DESC
LIMIT 10 OFFSET 10;     -- pomiń pierwsze 10, zwróć kolejne 10

OFFSET n przeskakuje pierwsze n wierszy przed zwróceniem wyniku. Bez ORDER BY używanie LIMIT/OFFSET nie ma sensu — kolejność wierszy jest niezdefiniowana, więc kolejne strony mogą zwracać niespójne wyniki.

W MS SQL Server zamiast LIMIT używa się TOP n (SELECT TOP 10 ...); paginację realizuje się przez ORDER BY ... OFFSET n ROWS FETCH NEXT m ROWS ONLY.

Duże offsety są wolne

LIMIT 10 OFFSET 1000000 zmusza bazę do odczytu i odrzucenia miliona wierszy zanim zwróci 10. Dla głębokich stron lepsza jest paginacja oparta na kursorze — WHERE id > <ostatnie_id_z_poprzedniej_strony> ORDER BY id LIMIT 10.

UPDATE — modyfikacja istniejących wierszy

UPDATE stacje
SET dorzecze = 'Odra'
WHERE id = 1;
 
-- korekta serii przepływów po nowej krzywej konsumpcyjnej (+3%)
UPDATE pomiary
SET przeplyw = przeplyw * 1.03
WHERE stacja_id = 1 AND data_pomiaru >= '2024-01-01';

UPDATE bez WHERE zmienia całą tabelę

UPDATE pomiary SET przeplyw = 0 bez WHERE wyzeruje przepływy we wszystkich pomiarach we wszystkich stacjach. To jeden z klasycznych „strzałów w stopę”. Zanim wykonasz UPDATE, najpierw uruchom SELECT z tym samym WHERE, żeby zobaczyć, których wierszy dotyczy.

DELETE — usunięcie wierszy

DELETE FROM pomiary
WHERE data_pomiaru < '2000-01-01';
 
DELETE FROM stacje WHERE id = 5;

Ta sama uwaga co przy UPDATE — DELETE FROM tabela bez WHERE usunie wszystkie wiersze. Dodatkowo: usunięcie wiersza z stacje, do którego odwołują się pomiary, zostanie zablokowane przez klucz obcy. Zachowanie można zmienić w definicji FK — ON DELETE CASCADE (razem ze stacją skasują się wszystkie jej pomiary), ON DELETE SET NULL (pomiary zostają, ale tracą powiązanie), ON DELETE RESTRICT (równoważne z domyślnym blokowaniem).

JOIN — łączenie tabel

Prawdziwa siła SQL ujawnia się przy łączeniu tabel. Załóżmy, że chcemy listę pomiarów z nazwą stacji i rzeką:

SELECT p.id, p.data_pomiaru, p.stan_wody, p.przeplyw, s.nazwa, s.rzeka
FROM pomiary p
INNER JOIN stacje s ON p.stacja_id = s.id;

• INNER JOIN stacje s — łącz z tabelą stacje, alias s
• ON p.stacja_id = s.id — warunek dopasowania: klucz obcy z pomiary równy kluczowi głównemu w stacje
• p.stan_wody, s.nazwa — z których tabel pochodzą kolumny

Rodzaje JOIN-ów

JOIN	Co zwraca
INNER JOIN	Tylko wiersze, które mają dopasowanie w obu tabelach
LEFT JOIN	Wszystkie z lewej + dopasowane z prawej; brak dopasowania → NULL
RIGHT JOIN	Wszystkie z prawej + dopasowane z lewej (rzadziej używane)
FULL OUTER JOIN	Wszystkie z obu, niedopasowane uzupełnione NULL
CROSS JOIN	Iloczyn kartezjański — każdy wiersz z każdym (uważaj, może być ogromny)

Stacje z liczbą pomiarów (LEFT JOIN, żeby uwzględnić też te bez pomiarów):

SELECT s.nazwa, s.rzeka, COUNT(p.id) AS liczba_pomiarow
FROM stacje s
LEFT JOIN pomiary p ON s.id = p.stacja_id
GROUP BY s.id, s.nazwa, s.rzeka
ORDER BY liczba_pomiarow DESC;

Aliasy

p i s w przykładach to aliasy tabel — krótkie nazwy ułatwiające pisanie. Słowo kluczowe AS jest opcjonalne (FROM pomiary AS p = FROM pomiary p). Aliasy są wręcz wymagane, gdy w zapytaniu pojawia się ta sama tabela kilka razy.

Aliasy stosujemy też do kolumn — głównie żeby nadać sensowne nazwy wynikom funkcji:

SELECT COUNT(*) AS liczba_stacji FROM stacje;

Funkcje agregujące i GROUP BY

Agregacje pozwalają obliczyć statystyki na grupach wierszy:

Funkcja	Co zwraca
COUNT(*)	Liczba wierszy w grupie
COUNT(kol)	Liczba wierszy z niepustą wartością kol
SUM(kol)	Suma wartości
AVG(kol)	Średnia
MIN(kol)	Wartość najmniejsza
MAX(kol)	Wartość największa

Bez GROUP BY agregacja działa na całej tabeli — np. statystyki przepływu w całej serii pomiarowej:

SELECT COUNT(*), AVG(przeplyw), MIN(przeplyw), MAX(przeplyw)
FROM pomiary;

Z GROUP BY — na grupach (statystyki per stacja):

SELECT stacja_id,
       COUNT(*) AS liczba_pomiarow,
       AVG(przeplyw) AS sredni_przeplyw,
       MAX(przeplyw) AS max_przeplyw
FROM pomiary
GROUP BY stacja_id
ORDER BY sredni_przeplyw DESC;

HAVING — filtr po agregacji

Klauzula WHERE filtruje wiersze przed agregacją, HAVING — po:

SELECT stacja_id, AVG(przeplyw) AS sredni_przeplyw
FROM pomiary
WHERE data_pomiaru >= '2024-01-01'    -- filtruj wiersze
GROUP BY stacja_id
HAVING AVG(przeplyw) > 50;            -- filtruj grupy

To zwróci stacje, na których średni przepływ z 2024 roku przekracza 50 m³/s.

SELECT — Kolejność wykonywania klauzul

Pisząc zapytanie zaczynamy od SELECT, ale baza wykonuje klauzule w innej kolejności:

1. FROM         - wybierz źródła danych (tabele, JOIN-y)
2. WHERE        - odfiltruj wiersze
3. GROUP BY     - pogrupuj
4. HAVING       - odfiltruj grupy
5. SELECT       - wylicz kolumny wynikowe
6. DISTINCT     - usuń duplikaty
7. ORDER BY     - posortuj
8. LIMIT        - ogranicz liczbę

Stąd np. nie można w WHERE użyć aliasu kolumny zdefiniowanego w SELECT — bo SELECT wykona się dopiero po WHERE. Z drugiej strony aliasu wolno użyć w ORDER BY, bo sortowanie wykonuje się po SELECT.

Podzapytania (subqueries)

Podzapytanie to zapytanie umieszczone wewnątrz innego zapytania. Pozwala wyrazić zależności, których nie da się zapisać prostym JOIN-em — albo zapisać je czytelniej.

Podzapytanie zwykłe — w klauzuli WHERE

-- pomiary ze stacji na Warcie
SELECT * FROM pomiary
WHERE stacja_id IN (SELECT id FROM stacje WHERE rzeka = 'Warta');
 
-- pomiary ze stacji bez wpisanej rzędnej zera
SELECT * FROM pomiary
WHERE stacja_id IN (SELECT id FROM stacje WHERE rzedna_zera IS NULL);

To samo dałoby się zapisać przez JOIN — wybór zależy od czytelności i (rzadziej) wydajności.

Podzapytanie skalarne — zwraca jedną wartość

Podzapytanie skalarne zwraca jeden wiersz i jedną kolumnę. Można go użyć wszędzie, gdzie sensowna jest pojedyncza wartość — w SELECT, WHERE, UPDATE.

-- pomiary, w których przepływ jest powyżej globalnej średniej
SELECT * FROM pomiary
WHERE przeplyw > (SELECT AVG(przeplyw) FROM pomiary);
 
-- każdy pomiar wraz z odchyleniem od średniej globalnej
SELECT id, data_pomiaru, przeplyw,
       przeplyw - (SELECT AVG(przeplyw) FROM pomiary) AS odchylenie
FROM pomiary;

Podzapytanie skalarne wykonuje się raz — silnik liczy AVG(przeplyw) jeden raz i podstawia wynik.

Podzapytanie skorelowane — odwołanie do wiersza zewnętrznego

Podzapytanie skorelowane korzysta z kolumn z zewnętrznego zapytania — wykonuje się raz dla każdego wiersza zewnętrznej tabeli.

-- pomiary, w których przepływ przekracza średnią dla TEJ stacji
SELECT p.id, p.stacja_id, p.data_pomiaru, p.przeplyw
FROM pomiary p
WHERE p.przeplyw > (
    SELECT AVG(p2.przeplyw)
    FROM pomiary p2
    WHERE p2.stacja_id = p.stacja_id    -- korelacja
);

Wewnętrzne AVG(...) jest liczone osobno dla każdego wiersza p — średnia dotyczy tylko pomiarów tej samej stacji co p. Bez warunku WHERE p2.stacja_id = p.stacja_id byłaby to globalna średnia, jak w przykładzie wyżej.

EXISTS — sprawdzenie istnienia

EXISTS zwraca TRUE, jeśli podzapytanie zwróci choć jeden wiersz:

-- stacje, które mają przynajmniej jeden pomiar
SELECT * FROM stacje s
WHERE EXISTS (
    SELECT 1 FROM pomiary p WHERE p.stacja_id = s.id
);
 
-- stacje bez żadnego pomiaru
SELECT * FROM stacje s
WHERE NOT EXISTS (
    SELECT 1 FROM pomiary p WHERE p.stacja_id = s.id
);

SELECT 1 — silnik sprawdza tylko obecność wierszy, więc lista kolumn nie ma znaczenia.

NOT IN z NULL — pułapka

NOT IN (SELECT col FROM tabela ...) zwróci pusty zbiór, jeśli choć jedna wartość zwrócona przez podzapytanie jest NULL — to konsekwencja trójwartościowej logiki SQL (x NOT IN (..., NULL) daje wartość nieznaną, nie TRUE). NOT EXISTS jest na to odporne i powinien być wyborem domyślnym do wyszukiwania braku dopasowania.

Skorelowane podzapytanie a JOIN

Podzapytania skorelowane bywają wolne — w pesymistycznym scenariuszu wykonują się raz dla każdego wiersza zewnętrznej tabeli. Często to samo można wyrazić przez JOIN z agregacją w podzapytaniu (lub w CTE) i pozwolić planiście bazy na lepszą optymalizację. EXPLAIN pokaże, czy dany kształt zapytania jest optymalny.

UNION — łączenie wyników

UNION skleja wyniki dwóch zapytań w jeden zestaw. Wymaganie: oba zapytania muszą mieć tę samą liczbę kolumn i kompatybilne typy.

-- stacje na Warcie i Wiśle w jednym wyniku
SELECT id, nazwa, rzeka FROM stacje WHERE rzeka = 'Warta'
UNION
SELECT id, nazwa, rzeka FROM stacje WHERE rzeka = 'Wisła';

Ten konkretny przykład jest akademicki — WHERE rzeka IN ('Warta', 'Wisła') jest prostsze. UNION naprawdę przydaje się, gdy zapytania pobierają z różnych tabel:

-- pomiary archiwalne + pomiary bieżące (gdyby były rozdzielone na dwie tabele)
SELECT data_pomiaru, stacja_id, przeplyw FROM pomiary_archiwalne
UNION ALL
SELECT data_pomiaru, stacja_id, przeplyw FROM pomiary_biezace
ORDER BY data_pomiaru DESC;

UNION vs UNION ALL

Wariant	Zachowanie	Wydajność
UNION	Eliminuje duplikaty (jak DISTINCT)	Wolniejsze
UNION ALL	Zachowuje wszystkie wiersze, łącznie z powtórkami	Szybsze

Jeśli wiesz, że zapytania nie zwrócą identycznych wierszy (np. dane z rozłącznych okresów), używaj UNION ALL — pominiesz niepotrzebną deduplikację.

ORDER BY w UNION umieszczamy raz, na końcu — sortuje wynik połączony, a nie poszczególne zapytania składowe.

Widoki (VIEW)

Widok to zapisane w bazie zapytanie, które można odpytywać tak, jakby było tabelą. Po stronie użytkownika wygląda jak zwykła tabela; po stronie bazy każde odwołanie wykonuje zapytanie definiujące widok.

CREATE VIEW srednie_dobowe AS
SELECT s.id                  AS stacja_id,
       s.nazwa,
       s.rzeka,
       DATE(p.data_pomiaru)  AS dzien,
       AVG(p.stan_wody)      AS sredni_stan,
       AVG(p.przeplyw)       AS sredni_przeplyw
FROM stacje s
JOIN pomiary p ON s.id = p.stacja_id
GROUP BY s.id, s.nazwa, s.rzeka, DATE(p.data_pomiaru);
 
-- odpytywanie widoku jak zwykłej tabeli
SELECT * FROM srednie_dobowe
WHERE rzeka = 'Warta'
ORDER BY dzien DESC
LIMIT 7;

Po co używać widoków:

• Czytelność — częste, skomplikowane zapytanie chowamy za prostą nazwą
• Spójność — wszyscy konsumenci danych korzystają z jednej, zatwierdzonej definicji
• Bezpieczeństwo — można nadać GRANT SELECT tylko na widok ukrywający kolumny wrażliwe lub filtrujący wiersze (np. widok pokazujący tylko stacje z danego dorzecza)
• Refaktoring — zmiana struktury tabel nie wymaga przepisywania kodu klientów, jeśli widok zachowa kontrakt

Usunięcie widoku:

DROP VIEW srednie_dobowe;

Widoki materializowane

Widok zwykły nie przechowuje danych — przy każdym SELECT zapytanie definiujące jest wykonywane od nowa. Dla skomplikowanych agregacji na dużych zbiorach to bywa wolne.

Niektóre silniki (PostgreSQL, Oracle) oferują widoki materializowane (CREATE MATERIALIZED VIEW) — wynik jest fizycznie zapisany na dysku, a odświeżany ręcznie (REFRESH MATERIALIZED VIEW) lub harmonogramem. Idealne dla przedwyliczanych raportów, np. miesięcznych statystyk przepływów. MS SQL Server udostępnia pokrewną konstrukcję — indexed views (widok z założonym indeksem klastrowym) — w której planista może użyć fizycznie zapisanego widoku zamiast wyliczać zapytanie od nowa.

-- PostgreSQL
CREATE MATERIALIZED VIEW miesieczne_przeplywy AS
SELECT stacja_id,
       DATE_TRUNC('month', data_pomiaru) AS miesiac,
       AVG(przeplyw)                     AS sredni_przeplyw,
       MAX(przeplyw)                     AS max_przeplyw
FROM pomiary
GROUP BY stacja_id, DATE_TRUNC('month', data_pomiaru);
 
-- odświeżenie po dodaniu nowych pomiarów
REFRESH MATERIALIZED VIEW miesieczne_przeplywy;

MySQL nie obsługuje natywnie widoków materializowanych — w praktyce realizuje się je przez tabelę cache odświeżaną cron-em lub triggerem.

Indeksy

Indeks to pomocnicza struktura danych, która przyspiesza wyszukiwanie po wybranej kolumnie. Bez indeksu baza musi przeczytać wszystkie wiersze, żeby znaleźć pasujące — z indeksem skacze do nich bezpośrednio. W tabelach pomiarowych z milionami rekordów to różnica między zapytaniem trwającym ułamki sekundy a kilkoma minutami.

CREATE INDEX idx_pomiary_stacja ON pomiary(stacja_id);
CREATE INDEX idx_pomiary_data ON pomiary(data_pomiaru);
CREATE INDEX idx_pomiary_stacja_data ON pomiary(stacja_id, data_pomiaru);

Ten ostatni — indeks złożony — przyspiesza zapytania filtrujące po obu kolumnach jednocześnie (typowy przypadek: „pomiary stacji X z zakresu dat Y-Z”).

Klucze główne i kolumny z UNIQUE mają indeks tworzony automatycznie. Klucze obce — zależnie od silnika: MySQL/MariaDB (InnoDB) tworzy automatycznie, jeśli nie istnieje już indeks pokrywający kolumnę FK; PostgreSQL i MS SQL Server nie tworzą — trzeba zakładać ręcznie.

Kiedy zakładać indeks

Na kolumnach często pojawiających się w WHERE, JOIN ... ON i ORDER BY. W kontekście danych hydrologicznych: stacja_id i data_pomiaru to oczywiści kandydaci. Indeks przyspiesza odczyt, ale spowalnia INSERT/UPDATE/DELETE (bo trzeba go aktualizować przy każdej operacji zapisu). W tabelach z bardzo intensywnym zapisem nie zakładamy indeksów „na zapas”.

Selektywność ma znaczenie. Indeks na kolumnie BOOLEAN lub takiej z kilkoma wartościami (np. dorzecze z 5-7 nazwami w skali Polski) zwykle nie pomaga — planista bazy i tak wybierze pełen skan, bo każda wartość filtruje za mało wierszy. Indeksy działają najlepiej na kolumnach o wysokiej kardynalności (stacja_id, data_pomiaru, kod_imgw).

Transakcje (TCL)

Transakcja grupuje kilka operacji w jedną logiczną całość. Albo wszystkie zostaną zatwierdzone, albo żadna.

Klasyczny przykład w hydrologii: korekta zera wodowskazu. Gdy okaże się, że rzędna zera wodowskazu została mylnie ustalona (lub uległa zmianie po remoncie urządzenia), wszystkie historyczne stany wody wymagają korekty razem z metadanymi stacji. Częściowy stan, w którym pomiary są poprawione, a metadane jeszcze stare (lub odwrotnie), wprowadza niespójność, która później jest trudna do wykrycia.

BEGIN;                          -- rozpoczęcie transakcji
 
-- przesunięcie zera o 5 cm w dół: wszystkie historyczne stany wody rosną o 5 cm
UPDATE pomiary
SET stan_wody = stan_wody + 5
WHERE stacja_id = 1;
 
-- aktualizacja rzędnej zera w metadanych stacji
UPDATE stacje
SET rzedna_zera = rzedna_zera - 0.05
WHERE id = 1;
 
COMMIT;                         -- zatwierdzenie zmian

Jeśli między UPDATE-ami coś pójdzie nie tak (np. proces zostanie przerwany), wykonujemy:

ROLLBACK;                       -- wycofanie wszystkich zmian z transakcji

Bez transakcji moglibyśmy zostać w stanie „pomiary skorygowane, ale metadane stacji jeszcze stare” — i przy najbliższej analizie dostać wartości niezgodne z rzeczywistością. Transakcja gwarantuje atomowość — jest to A z ACID.

Składnia różni się subtelnie między dialektami: PostgreSQL i MySQL akceptują BEGIN lub START TRANSACTION; SQLite — BEGIN lub BEGIN TRANSACTION; MS SQL Server — BEGIN TRANSACTION (ze skrótem BEGIN TRAN).

Uprawnienia (DCL)

Polecenia DCL kontrolują, kto i co może robić w bazie:

-- nadanie uprawnień (np. konto aplikacji odczytującej dane do raportów)
GRANT SELECT ON stacje TO uzytkownik_app;
GRANT SELECT ON pomiary TO uzytkownik_app;
 
-- konto operatora wprowadzającego nowe pomiary
GRANT SELECT, INSERT ON pomiary TO operator_imgw;
 
-- wszystkie uprawnienia (dla administratora)
GRANT ALL ON stacje TO admin;
 
-- odebranie
REVOKE INSERT ON pomiary FROM operator_imgw;

W praktyce zarządzaniem uprawnieniami zajmuje się administrator bazy. Programista zwykle dostaje gotowe konto z odpowiednim zestawem uprawnień. Sensowny podział kont: aplikacja raportująca tylko SELECT, ETL wprowadzający dane SELECT/INSERT, korektor danych UPDATE/DELETE na ograniczonym zakresie, administrator ALL.

Dobre praktyki

Bezpieczeństwo

• Nigdy nie buduj zapytań przez sklejanie stringów z danymi od użytkownika. To otwiera furtkę na SQL injection — atakujący może wstrzyknąć własny kod SQL i np. odczytać całą tabelę użytkowników. Zamiast tego używaj prepared statements (parametryzowanych zapytań) dostępnych w każdym języku programowania
• Prepared statements chronią tylko wartości, nie nazw obiektów. Jeśli z jakiegoś powodu nazwa tabeli, kolumny lub kierunek sortowania pochodzi od użytkownika (ORDER BY ${kolumna}, dynamiczne raporty), parametryzacja nie pomoże — trzeba zwalidować nazwę po białej liście dozwolonych identyfikatorów
• Zasada minimalnych uprawnień — aplikacja łączy się z bazą na koncie z minimalnym zestawem praw potrzebnym do działania. Skrypt pobierający dane z IMGW i wstawiający je do tabeli pomiary nie potrzebuje uprawnień do DROP TABLE

Czytelność i wydajność

• Wymieniaj kolumny w SELECT zamiast SELECT * — kod jest bardziej odporny na zmiany schematu, a baza może zoptymalizować zapytanie
• EXPLAIN (PostgreSQL/MySQL) lub EXPLAIN PLAN (Oracle) pokazuje, jak silnik zamierza wykonać zapytanie. Niezbędne narzędzie do strojenia wydajności — szczególnie przy długich seriach pomiarowych
• Aliasy w JOINach — krótsze i czytelniejsze niż pełne nazwy tabel
• Komentarze: -- komentarz do końca linii, /* komentarz blokowy */

Konwencje nazewnictwa

Element	Konwencja
Tabele	liczba mnoga, małymi: stacje, pomiary
Kolumny	małymi, opisowo: stan_wody, data_pomiaru
Klucz obcy	<tabela>_id: stacja_id, dorzecze_id
Indeksy	idx_<tabela>_<kolumny>
Bez polskich znaków	data_pomiaru zamiast data_pomíaru

To są konwencje rozpowszechnione w polskich projektach — żaden silnik ich nie wymusza, ale spójność w obrębie projektu znacznie ułatwia czytanie kodu.

Testowanie destrukcyjnych operacji

Przed UPDATE lub DELETE zawsze:

-- 1. SELECT z tym samym WHERE - sprawdź, których wierszy dotyczy
SELECT COUNT(*) FROM pomiary WHERE data_pomiaru < '2000-01-01';
 
-- 2. opakuj w transakcję, żeby móc się wycofać
BEGIN;
DELETE FROM pomiary WHERE data_pomiaru < '2000-01-01';
-- sprawdź wynik (np. SELECT COUNT(*))
COMMIT;       -- zatwierdź albo:
ROLLBACK;     -- wycofaj

Backup przede wszystkim

Przed jakąkolwiek operacją zmieniającą strukturę bazy w środowisku produkcyjnym (ALTER TABLE, DROP, masowe UPDATE/DELETE) — backup. Każdy DBMS ma narzędzia eksportu (pg_dump w PostgreSQL, mysqldump w MySQL, .dump w SQLite). Dla serii pomiarowych obejmujących dziesięciolecia obserwacji utrata danych może być nieodwracalna.

Pełny przykład

Budujemy małą bazę danych hydrologicznych od zera:

-- 1. Struktura
CREATE TABLE stacje (
    id                INTEGER PRIMARY KEY,
    kod_imgw          VARCHAR(20) UNIQUE,
    nazwa             VARCHAR(100) NOT NULL,
    rzeka             VARCHAR(100),
    dorzecze          VARCHAR(50),
    rzedna_zera       DECIMAL(7, 2)
);
 
CREATE TABLE pomiary (
    id            INTEGER PRIMARY KEY,
    stacja_id     INTEGER NOT NULL,
    data_pomiaru  TIMESTAMP NOT NULL,
    stan_wody     INTEGER,
    przeplyw      DECIMAL(10, 3),
    FOREIGN KEY (stacja_id) REFERENCES stacje(id)
);
 
CREATE INDEX idx_pomiary_stacja_data ON pomiary(stacja_id, data_pomiaru);
 
-- 2. Dane
INSERT INTO stacje (id, kod_imgw, nazwa, rzeka, dorzecze, rzedna_zera) VALUES
    (1, '152160140', 'Poznań-Most Rocha', 'Warta',  'Odra',  49.54),
    (2, '152210170', 'Warszawa Bulwary',  'Wisła',  'Wisła', 75.17),
    (3, '351180435', 'Kalisz',            'Prosna', 'Odra',  NULL);
 
INSERT INTO pomiary (id, stacja_id, data_pomiaru, stan_wody, przeplyw) VALUES
    (101, 1, '2024-05-10 06:00:00', 245,  64.200),
    (102, 1, '2024-05-11 06:00:00', 240,  61.500),
    (103, 2, '2024-05-10 06:00:00', 312, 542.000),
    (104, 1, '2024-05-12 06:00:00', 238,  58.300),
    (105, 3, '2024-05-10 06:00:00', 156,   8.150);
 
-- 3. Raport: stacje wg liczby pomiarów i średniego przepływu
-- Uwaga: `NULLS LAST` to składnia PostgreSQL/Oracle/SQLite 3.30+.
-- Przenośnie: `ORDER BY sredni_przeplyw IS NULL, sredni_przeplyw DESC`
SELECT s.nazwa, s.rzeka,
       COUNT(p.id)      AS liczba_pomiarow,
       AVG(p.przeplyw)  AS sredni_przeplyw,
       MAX(p.przeplyw)  AS max_przeplyw
FROM stacje s
LEFT JOIN pomiary p ON s.id = p.stacja_id
GROUP BY s.id, s.nazwa, s.rzeka
ORDER BY sredni_przeplyw DESC NULLS LAST;
 
-- 4. Korekta zera wodowskazu w transakcji
BEGIN;
UPDATE pomiary SET stan_wody = stan_wody + 5 WHERE stacja_id = 1;
UPDATE stacje  SET rzedna_zera = rzedna_zera - 0.05 WHERE id = 1;
SELECT id, nazwa, rzedna_zera FROM stacje WHERE id = 1;
COMMIT;

Zobacz także

• IT — indeks narzędzi informatycznych
• Komendy terminala - Linux i macOS — klienci CLI baz danych (psql, mysql, sqlite3) uruchamia się z powłoki
• Python - podstawy — łączenie z bazą z poziomu kodu (sqlite3, psycopg, SQLAlchemy)
• IMGWTools — biblioteka do pobierania danych obserwacyjnych IMGW, naturalne źródło rekordów do tabeli pomiary
• PostgreSQL Documentation — najpełniejsza dokumentacja relacyjnego silnika open source
• PostGIS — rozszerzenie PostgreSQL dla danych przestrzennych
• SQLite Documentation — dokumentacja SQLite, dobra do nauki bez konfigurowania serwera
• MySQL Reference Manual — dokumentacja MySQL