SQL s databázovým systémem PostgreSQL
Tento článek byl převzat z www.pgsql.cz a následně modifikován.
PostgreSQL podporuje kompletní repertoár příkazů ANSI SQL92 a částečně ANSI SQL2003. Tento článek představuje představuje jednoduchý úvod do SQL včetně základní terminologie a několika rad. Příklady užitečných SQL příkazů naleznete v článku SQL Triky.
SQL
Structured Query Language
SQL je v současnosti nejrozšířenějším dotazovacím (neprocedurálním) programovacím jazykem určeným k definici, údržbě a vyhledávání dat v relačních databázích. V roce 1970 publikoval Dr. E. F. Codd svou fundamentální teoretickou práci o relačním datovém modelu. Jeho práce se stala základem výzkumného projektu Systém/R, který probíhal v druhé polovině sedmdesátých let v laboratořích fy. IBM. V rámci tohoto projektu vznikl i jazyk SQL. Na přelomu sedmdesátých a osmdesátých již existovaly první komerční verze stávajících RDBMS systémů Oracle a IBM.
V roce 1986 byl vytvořen první ANSI standard jazyka. Posledním ANSI standardem je ANSI SQL:2003. Požadavky ANSI SQL:2003 splňuje jen nemnoho RDBMS. Zatím nejrozšířenější RDBMS dodržují ANSI SQL:1999 případně starší ANSI SQL:1992. PostgreSQL podporuje úplně ANSI SQL:1999 a částečně ANSI SQL:2003.
Pravidla pro normalizaci databáze
Jazyk SQL je určen pro databáze, které používají tzv. relační databázový model. V něm jsou data uložená v tabulkách s jasně definovanou a relativně neměnnou strukturou. Datový záznam odpovídá řádku jedné nebo několika tabulek. Datový atribut pak průniku konkrétního sloupce a konkrétního řádku tabulky. Až na několik nepodstatných vy jímek platí, že jedna buňka tabulky uchovává jednu hodnotu. Data se v tabulkách ukládají v předepsaném formátu a typu. Separací dat do samostatných tabulek se snažíme předejít redundanci dat. Příkladem chybně navržené datové tabulky může být tato (porušuje podmínky tzv. normálních forem):
- 0NF (nultá normální forma): Tabulka je v nulté normální formě právě tehdy, existuje-li alespoň jedno pole, které obsahuje více než jednu hodnotu.
- 1NF (první normální forma): Tabulka je v první normální formě, jestliže lze do každého pole dosadit pouze jednoduchý datový typ (jsou dále nedělitelné).
- 2NF (druhá normální forma): Tabulka je ve druhé normální formě, jestliže je v první a navíc platí, že existuje klíč a všechna neklíčová pole jsou funkcí celého klíče (a tedy ne jen jeho částí).
- 3NF (třetí normální forma): Tabulka je ve třetí normální formě, jestliže každý neklíčový atribut není transitivně závislý na žádném klíči schématu neboli je-li ve druhé normální formě a zároveň neexistuje jediná závislost neklíčových sloupců tabulky.
postgres=# select * from rodina;
rodic | dite
----------------+----------------
Jindřich Suchý | Jindřich Suchý
Jindřich Suchý | Lenka Suchá
(2 rows)
Naopak příkladem dobře navržené sady tabulek může být:
postgres=# SELECT * FROM rodic; id | jmeno | prijmeni ----+----------+---------- 1 | Jindřich | Suchý (1 row) postgres=# SELECT * FROM dite; id | rodic | jmeno | prijmeni ----+-------+----------+---------- 1 | 1 | Lenka | Suchá 2 | 1 | Jindřich | Suchý (2 rows) postgres=# SELECT rodic.*, dite.* FROM rodic JOIN dite ON rodic.id = dite.rodic; id | jmeno | prijmeni | id | rodic | jmeno | prijmeni ----+----------+----------+----+-------+----------+---------- 1 | Jindřich | Suchý | 1 | 1 | Lenka | Suchá 1 | Jindřich | Suchý | 2 | 1 | Jindřich | Suchý (2 rows)
3NF dosahujeme dělením dat do samostatných tabulek. Počet sloupů tabulky by se měl pohybovat mezi dvěma až osmi. Za normálních okolností (99%) by všechny tabulky s kterými pracujeme měly být v 3NF. Nepoužívejte široké tabulky. Co ušetříte na spojení tabulek ztratíte na pomalejším čtení tabulky - s délkou záznamu klesá počet záznamu na stránce a roste počet čtení (přístupů na disk). To, že se nezobrazují všechny záznamy v tabulce neznamená, že se nenačítají z disku.
Kardinalita
Relační datový model umožňuje jednoduše pracovat s tabulkami, jejichž vzájemný vztah, který označujeme jako kardinalitu, je 1:1 (jednomu záznamu jedné tabulky odpovídá jeden záznam druhé tabulky) 1:n (jednomu záznamu prvé tabulky odpovídá n (žádný nebo více záznamů z druhé tabulky). V SQL systémech je tento vztah explicitně určen rovností hodnot některých sloupců (klíčů) v tabulkách. V případě, že slučujeme tabulky, musíme tuto rovnost explicitně uvést jako logický výraz v SQL příkazu:
SELECT * FROM rodic JOIN dite ON rodic.id = dite.rodic_id;
Klíč z nezávislé tabulky (rodic) označujeme jako primární klíč, klíč ze závislé tabulky označujeme jako cizí klíč. Požadavkem na primární klíč je jeho jedinečnost (žádná hodnota se nesmí opakovat). Od cizího klíče požadujeme jeho integritu s primárním klíčem (hodnoty se mohou opakovat, ale nesmí se vyskytnout žádná hodnota, která se nevyskytuje v primárním klíči). Od SQL systémů očekáváme, že nám dokáže zajistit splnění těchto požadavků, tzv. zajištění referenční integrity.
Kromě vazeb 1:1 a 1:n existuje ještě vazba n:m. Abychom mohli tento vztah vyjádřit, potřebujeme pomocnou tzv. asociační tabulku, která obsahuje pouze (v obvyklých případech) dva cizí klíče. Na příkladu relace mezi entitami AUTOR a KNIHA uvedu všechny možné interpretace:
- 1:1 - každý autor napsal jednu knihu a nesmí napsat žádnou další,
- 1:n - každý autor smí vydat několik knih (limitním případem je žádná kniha),
- n:m - každý autor smí vydat několik knih, na jedné knize se smí podílet více autorů.
SELECT autor.*, kniha.*
FROM autor a
JOIN autor_kniha ak ON a.id = ak.autor_id
JOIN kniha k ON ak.kniha_id = k.id;
DDL
Jazyk SQL se skládá z několika příkazů rozdělených do dvou základních skupin: příkazy pro definici datových struktur (Data Definition Language) a příkazy pro práci s obsahem tabulek (Data Modeling Language).
DDL má deklarativní charakter a umožňuje nám vytvářet, modifikovat a rušit databázové objekty: tabulky, pohledy, indexy, jmenné prostory, tabulkové prostory, databáze, uložené procedury a funkce, triggery a uživatele. Objekty vytváříme příkazem CREATE, rušíme příkazem DROP, přidáváme přístupová práva příkazem GRANT nebo naopak příkazem REVOKE přístupová práva odebíráme. Vlastnosti objektů měníme příkazem ALTER. Každý SQL příkaz se skládá z několika volitelných částí; musíme ale respektovat logické vazby mezi těmito částmi a musíme tyto části zapisovat ve správném pořadí např. klauzule WHERE je za klauzulí FROM a před klauzulemi GROUP BY a ORDER BY.
CREATE TABLE dite ( id SERIAL PRIMARY KEY, rodic INTEGER NOT NULL REFERENCES rodic(id), jmeno VARCHAR(15) NOT NULL CHECK jmeno <> '', prijmeni VARCHAR(15) NOT NULL CHECK prijmeni <> '' ); ALTER TABLE dite ADD COLUMN vek INTEGER NOT NULL CHECK vek > 0; GRANT SELECT ON TABLE dite TO public;
DML
DML příkazy slouží k manipulaci s daty v databázi. ANSI SQL:1999 definije příkazy INSERT, UPDATE, DELETE, a SELECT. Tato základní sada je ještě v ANSI SQL:2003 rozšířena o příkaz MERGE (není podporován PostgreSQL).
- Příkaz INSERT se používá pro přidávání nových záznamů do tabulky
- Příkaz UPDATE se používá pro aktualizaci existujících záznamů v tabulce
- Příkaz DELETE se používá pro odstranění záznamů z tabulky
- Příkaz SELECT se používá pro zobrazení a hledání záznamů v tabulce
INSERT INTO dite(rodic,jmeno, prijmeni,vek) VALUES(1,'Jindřich','Suchý',12); UPDATE dite SET vek = 13 WHERE id = 2; DELETE FROM dite WHERE vek > 26; SELECT * FROM dite WHERE rodic = 1;
Nejčastěji používaným příkazem DML příkazem je nejspíš příkaz SELECT. Skládá se s následujících povinných a nepovinných klauzulí (při zápisu je nezbytné respektovat pořadí klauzulí):
SELECT <seznam zobrazovaných sloupců> FROM <zdroj nebo zdroje tabulek> WHERE <výběrová kritéria> [GROUP BY <kritéria pro sdružování záznamů>] [HAVING <kritéria pro filtrování sdružených záznamů>] [ORDER BY <způsob řazení dat>]
Příkladem může být dotaz, který zobrazí všechny rodiče, které mají více než jedno dítě:
SELECT rodic.* FROM rodic WHERE id IN (SELECT rodic FROM dite GROUP BY rodic HAVING count(*) > 1)
Transakce
V kterémkoliv databázovém systému mají transakce nezastupitelnou roli. Zajišťují konzistenci dat bez ohledu na chyby hardwaru, operačního systému, aplikace nebo databázového systému. Transakce spojuje sadu příkazů do jednoho celku, a to tak, že se změny v datech způsobené jednotlivými příkazy se stanou trvalými pokud se úspěšně provede každý příkaz transakce. První neúpěšný příkaz způsobí přerušení transakce a odvolání všech změn v datech. Klasickým příkladem je přesun určité částky z jednoho úču na druhý:
BEGIN; UPDATE ucty SET castka = castka + 100 WHERE id = 122; UPDATE ucty SET castka = castka - 100 WHERE id = 133; COMMIT;
Pokud bychom tyto dva SQL nevložili do transakce, mohlo by dojít (v mezním případě), že by na účet uživatele 122 přibyla částka 100 aniž by se uživateli 133 odečetla z účtu. Druhým úkolem transakcí je izolace uživatelů. Neuzavřené transakce jsou izolovány od ostatních uživatelů - ty stále vidí původní obsah. A až od potvrzení transakce uvidí aktualizované hodnoty, nicméně aktualizace všech hodnot se provede v jednom časovém kvantu. Díky tomu se nemůže stát, že by druhá transakce viděla data nekonzistentní, např. s celkovou částkou na všech účtech o 100 vyšší. Tato úroveň izolace se označuje jako READ COMMITED a v PostgreSQL je pro transakce výchozí.
Izolace transakcí má zajistit subjektivní dojem, že uživatel pracuje s databází sám. Takže například pokud opakujeme nějaký SQL dotaz, tak aniž bychom modifikovali data, předpokládáme stejný výsledek. Tento předpoklad s úrovní READ COMMITED nejsme schopni zajistit. Kdokoliv může během naší transakce přidat řádek do tabulky a provést COMMIT. Proto tu je úroveň READ SERIALIZABLE. Při této úrovni uživatel vidí pouze ty změny v datech, které byly potvrzeny před prvním voláním příkazu SELECT. Díky tomuto mechanismu všechny SQL dotazy musí vracet stejné hodnoty. To, že aplikace vidí tatáž data neznamená, že nedošlo k jejich změně. V případě, že se pokusí modifikovat záznamy, které byly změněny souběžnou transakcí, čeká se na dokončení souběžné transakce. Pokud skončí odvoláním, normálně se pokračuje, pokud ale své změny potvrdí, aktuální transakce skončí chybou
ERROR: could not serialize access due to concurrent update
SQL/PSM
Jazyk SQL byl navržen jako jednoduchý neprocedurální jazyk sloužící primárně k uložení dat do databáze a k získání dat z databáze. V praxi se časem ukázalo, že je příliš jednoduchý, a že často musíme psát externí aplikace, abychom dokázali realizovat potřebné operace nad daty, a že také často nedokážeme pomocí jednoduchých prostředků zajistit referenční a doménovou integritu v komplikovaném prostředí firemních aplikací. Externí aplikace v architektuře klient-server mají jednu základní nevýhodu - vyžadují relativně intenzivní komunikaci na síti mezi klientem a serverem. V okamžiku, kdy pracujete s několika tisíci záznamy to už může představovat výkonostní problém. Řešením bylo přenést část tzv. obchodní logiky na databázový server. K tomu muselo být SQL rozšířeno o několik základních konstrukcí které známe z klasických programovacích jazyků: proměnné, cykly, podmínky, procedury a funkce, ošetření vyjímek. Pomocí těchto konstrukcí dokážeme realizovat samostatné programy, jejichž kód celkem logicky ukládáme do databáze a které spouštíme v kontextu databáze.
Uložené procedury slouží především k:
- Generování výstupů, které nelze jednoduše nebo efektivně realizovat pomocí příkazu SELECT.
- Přenesení zpracování dat z klienta na server.
- Realizaci složitějších pravidel přístupu, referenční a doménové integrity, které nelze realizovat standardními prostředky.
- Rozšíření základní sady vestavěných funkcí o funkce jinych RDBMS z důvodu zjednodušení portace databáze.
ANSI SQL:1999 definuje tato rozšíření jazyka v kapitole PSM (Persistent Stored Modules). Tuto syntaxi používá DB2 a MySQL5. Podobné jsou i jazyky Transact SQL fy. Microsoft a Procedural SQL RDBMS Firebird. Svou vlastní cestou jde Oracle s jazykem PL/SQL. PL/SQL je procedurální jazyk na bázi programovacího jazyka ADA rozšířený o SQL. PostgreSQL tento jazyk přebírá a upravuje. Oracle implementoval PL/SQL ve verzi Oracle 6 v roce 1988, v PostgreSQL se PL/pgSQL objevil ve verzi 6.3 o deset let později. V roce 2005 je PL/pgSQL plnohodnotným jazykem procedurálním programovacím jazykem.
Integrace PL/pgSQL do systému je absolutní, sdílí datové typy, funkce. Naopak v SQL příkazech můžeme používat funkce vytvořené v PL/pgSQL. V PL/pgSQL jsme schopni vytvářet funkce, jejichž výsledkem je tabulka.
CREATE OR REPLACE FUNCTION init(IN _od integer, _do integer) RETURNS void AS $$
DECLARE _v integer = 1;
BEGIN
FOR _i IN _od .. _do LOOP
_v := _v + _v;
INSERT INTO data(hodnota) VALUES (_v);
END LOOP;
END;
$$ LANGUAGE plpgsql;
SELECT init(1, 10);
Použití prefixu _ pro lokální proměnné je konvence zabraňující kolizi názvu proměnných s názvy sloupců tabulek, a kolizi s klíčovými slovy SQL.
PostgreSQL nenabízí žádný jednoduchý prostředek pro číslování řádků. Funkce, která zajistí stránkování a zároveň přidání čísla řádku může mít následující podobu:
CREATE OR REPLACE FUNCTION strana_tel_seznamu(
IN _strana integer, IN _zns integer,
OUT _rc integer,
OUT _jmeno varchar(20), OUT _prijmeni varchar(20), OUT _tel numeric(9,0)
) RETURNS SETOF RECORD AS $$
DECLARE
_rc integer; _r seznam%ROWTYPE;
BEGIN
_rc := (_strana - 1)*_zns;
FOR _r IN SELECT * FROM seznam LIMIT _zns OFFSET (_strana - 1)*zns LOOP
_jmeno := _r.jmeno; _prijmeni := _r.prijmeni; _tel := _r.tel;
_rc := _rc + 1;
RETURN NEXT;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM strana_tel_seznamu(2,30);
Předpokládejme, že máme v databázi očíslovaná data, a nechceme, pokud to je jen trochu možné, aby v řadě čísel byla díra. Máme dvě možnosti: buď všechny údaje před operací zkontrolovat, tak abychom měli jistotu, že se operace povede, nebo operaci zkusmo provést, a pokud se operace provede úspěšně, tak požádat o číslo z naší posloupnosti a číselnou hodnotu u vloženého záznamu aktualizovat.
CREATE OR REPLACE FUNCTION new_entry(IN _v varchar) RETURNS void AS $$
DECLARE
_id integer;
_tmp_code integer; _true_code integer;
BEGIN
/* kod objednavky neznam, pri testovacim pokusu jej nahradim nahodnym cislem.
Muze se stat, ze zpusobim vyjimku UNIQUE_VIOLATION. Zkusim to pak znova. */
FOR i IN 1..20 LOOP /* maximalne 20 pokusu */
BEGIN
SELECT INTO _tmp_code MAX(kod) FROM data;
_tmp_code := COALESCE(_tmp_code, 1) + 20 + CAST(random()*100 AS integer);
INSERT INTO data(kod, v) VALUES(_tmp_code, _v);
_id := lastval(); _true_code := nextval('kod');
UPDATE data SET kod = _true_code WHERE id = _id;
RETURN;
EXCEPTION
WHEN unique_violation THEN
NULL; -- nedelej nic
WHEN others THEN
RAISE EXCEPTION '%', SQLERRM;
END;
END LOOP;
END;
$$ LANGUAGE plpgsql;
Přínosem používání uložených procedur není jen menší zatížení sítě, ale i rychlejší a bezpečnější provádění SQL příkazů. Všechny SQL příkazy se předpřipravují, tj. před jejich prvním provedením se připraví prováděcí plán, který se použije i při každém dalším použití SQL příkazu. Všechny předpřipravené SQL příkazy jsou parametrizovány, nehrozí tudíž riziko SQL injection.
CREATE FUNCTION bezpecne_zobrazeni(_par varchar) RETURNS SETOF varchar AS $$
DECLARE _v varchar;
BEGIN
IF _par = '%' THEN
RAISE EXCEPTION 'Pouziti zakazaneho znaku';
END IF;
FOR _v IN SELECT prijmeni FROM seznam WHERE prijmeni LIKE _par LOOP
RETURN NEXT _v;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM bezpecne_zobrazeni('P%');
V čem je tato procedura bezpečnější než použití obyčejného, na straně klienta připraveného, SQL dotazu? V tom, že si můžeme být jisti, že bez ohledu na hodnotu argumentu _par se bude provádět dotaz:
SELECT prijmeni FROM seznam WHERE prijmeni LIKE _par;
Na straně klienta není až takový problém podvrhnout jako parametr hodnotu "%' UNION SELECT usename FROM pg_user". Při sestavení SQL dotazu, třeba v php, dojde k vygenerování a provedení řetězce:
SELECT prijmeni FROM seznam WHERE prijmeni LIKE '%' UNION SELECT usename FROM pg_user;
Tímto dotazem dokážeme seznam uživatelů. Chytřejšími injekcemi dokážeme zjistit strukturu databáze a postupně přečíst všechna data, ke kterým má klientská aplikace oprávnění. Snažte se alespoň omezit délku řetězce a zakázat použití znaků '"%.
V RDBMS Oracle jsou dvě velice užitečné funkce: next_day a last_day. Pokud připravujeme aplikaci zároveň pro PostgreSQL, tak je buďto nesmíme použít (PostgreSQL je neobsahuje) nebo si je musíme dopsat:
CREATE OR REPLACE FUNCTION ERROR(IN msg VARCHAR) RETURNS varchar AS $$
BEGIN
RAISE EXCEPTION '%', msg;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION last_day(IN d date) RETURNS date AS $$
BEGIN
RETURN CAST(date_trunc('month',current_date + interval '1month') AS date) - 1;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION next_day(IN _d date, IN _day varchar) RETURNS date AS $$
DECLARE _id integer; _dow integer;
BEGIN
_dow := EXTRACT(dow FROM _d);
_id := CASE lower(_day)
WHEN 'sunday' THEN 0
WHEN 'monday' THEN 1
WHEN 'tuesday' THEN 2
WHEN 'wednesday' THEN 3
WHEN 'thursday' THEN 4
WHEN 'friday' THEN 5
WHEN 'saturday' THEN 6
ELSE
CAST(ERROR(E'Wrong identifier for day \''||_day||E'\'') AS integer)
END;
RETURN CASE _id <= _dow
WHEN true THEN _d + (_id - _dow + 7)
WHEN false THEN _d + (_id - _dow)
END;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
Podobně můžeme PostgreSQL rozšířit o funkce podporované jinými RDBMS.
Použití uložených procedur si vynucuje změny v aplikaci. Může mít negativní i positivní dopad na přenositelnost aplikace. Kód SP zpravidla není přenositelný (záleží odkud a kam, chcete portovat aplikaci). Na druhou stranu, kód který je platformně závislý je soutředěn v jednom nebo několika modulech. Z mé zkušenosti mohu SP jedině doporučit. Díky SP se zjednoduší kód aplikace a zpravidla řádově zrychlí.
Doporučení pro návrh databází
- V názvu tabulek a sloupců nepoužívejte klíčová slova.
- Snažte se, aby položky tvořící primární klíč, byly deklarovány na začátku tabulky.
- Pokud nemáte k dispozici data, použijte hodnotu NULL. Nepoužívejte 0 nebo prázdný řetězec.
- Nepoužívejte prefixy (např. t_processes).
- Nepoužívejte velbloudí (Camel) způsob zápisu identifikátorů (např. isoCode).
- Názvy sloupečků by měli být výstižné, rozumně dlouhé, zapsané malými písmeny.
- Tabulky nazývejte v množném čísle a zapisujte s prvním velkým a ostatními malými písmeny.
- Klíčova slova zapisujte vždy malými nebo vždy velkými písmeny.
- Používejte předem dohodnutou sadu sufixů (_id, _code, _date, _nbr, _name, _size, _tot, _cat, _class, _type).
- Nepoužívejte postfixy PK, FK.
- Název tabulky nepoužívejte v názvu sloupce
- Nepoužívejte speciální znaky (vyjímka znak "_"), diakritiku v identifikátorech tabulek a sloupců.
- Za čárkou nebo středníkem vždy použijte mezeru nebo nový řádek, nezačínejte řádek čárkou.
- Odsazujte: 1 mezera začátek klauzule, 2 mezery pokračování klauzule, 3 mezery pokračování výrazu, slovo pokračování seznamu.
- Poddotaz začínejte vždy na novém řádku, závorky svažte s poddotazem.
- Hledejte přirozené kódy.
- Držte pohromadě atributy, které spolu věcně, chronologicky souvisí.
- je chybou rozdělit data do tabulek: FemalePersonel, MalePersonel, prodej2001, prodej2002, atd.
- je chybou když datum máte ve třech sloupcích (den, měsíc, rok),
- je chybou, když data rozdělíte zbytečně do dvou řádků, např. zahájení události, konec události.
- Pokud navrhujete vlastní výčtový kód, definujte si vlastní náhradu za NULL (0 Unknown, 1 Male, 2 Female, 9 Not applicable (nemá smysl)).
- Kódy držte v databázi v speciálních tabulkách. Je chybou používat jednu univerzální tabulku pro všechny třídy kódů.
- Pokud to lze, používejte standardizované konstrukce a funkce.
Příklad vhodně naformátovaného vnořeného SQL příkazu:
SELECT DISTINCT pilot
FROM PolotSkills AS PS1
WHERE NOT EXISTS
(SELECT *
FROM Hangar
WHERE NOT EXISTS
(SELECT *
FROM PilotSkills AS PS2
WHERE PS1.pilot = PS2.pilot
AND PS2.plane = Hangar.plane
)
);
Ukázka zápisu spojení tabulek:
SELECT O1.order_nbr, ..
FROM Orders AS O1
INNER JOIN
OrderDetails AS D1
ON O1.order_nbr = D1.order_nbr
WHERE D1.dept = 'mens wear';
Ukázka zápisu konstrukce CASE:
CASE WHEN foo = 1 THEN 'bar'
WHEN foo = NULL THEN 'no_bar'
ELSE NULL END
Vnořené poddotazy a odvozené tabulky
SQL umožňuje rekurzivně vložit dotaz do jiného dotazu. Pokud je dotaz vložen do části WHERE, označujeme vložený dotaz jako vnořený (nested). Pokud je vložen do části FROM označujeme jej jako odvozenou tabulku (derived table), nebo někdy také jako dočasný pohled. Vložený dotaz může být prostřednictvím hodnot atributů spojen s vnějším dotazem - vázaný, někdy také korelovaný dotaz.
Příklad vnořeného dotazu (vyhledání všech finských měst lidnatějších než nejlidnatější město v ČR):
SELECT mesta FROM databaze_mest
WHERE pocet_obyvatel > ALL
(SELECT pocet_obyvatel FROM databaze_mest WHERE stat = 'CZ')
AND stat = 'FI';
Příklad korelovaného vnořeného dotazu (zobrazení zaměstnanců s největší mzdou v rámci oddělení):
SELECT prijmeni, oddeleni, mzda FROM zamestnanci zo
WHERE mzda =
(SELECT MAX(mzda) FROM zamestnanci zi WHERE zi.oddeleni = zo.oddeleni);
PostgreSQL podporuje více sloupcové predikáty:
SELECT prijmeni, mzda, oddeleni FROM zamestnanci
WHERE (mzda, oddeleni) IN
(SELECT MAX(mzda), oddeleni FROM zamestnanci GROUP BY oddeleni);
Příklad odvozené tabulky (opět zobrazení zaměstnanců s největší mzdou v rámci oddělení):
SELECT prijmeni, dt.mm AS mzda, dt.oddeleni FROM zamestnanci z1, (SELECT MAX(mzda) AS mm, oddeleni FROM zamestnanci GROUP BY oddeleni) dt WHERE z1.mzda = dt.mm AND z1.oddeleni = dt.oddeleni;
Některé vnořené dotazy lze zapsat pomocí různě efektivních příkazů. Ukázkovým příkladem je náhrada "> ALL" funkcí MAX v poddotazu (druhý dotaz je vhodnější):
SELECT * FROM a WHERE a > ALL(SELECT v FROM b); SELECT * FROM a WHERE a > (SELECT MAX(v) FROM b);
Vnitřní a vnější spojení tabulek
Existují dva základní způsoby zápisu spojení tabulek. Na starší variantu založenou na výčtu tabulek v části FROM byste měli raději zapomenout. Jednak podporuje pouze vnitřní spojení, tj. zobrazení pouze zpárovaných záznamů a jednak může, díky nedokonalému zápisu, být zdrojem velice závažné chyby, která může mít vliv na výkon serveru. Ještě před několika lety pro tuto variantu hovořil fakt, že ne všechny databáze podporovaly zápis JOIN, což je ale v současnostni pasé.
Najděte chybu na následujícím příkladu:
SELECT DISTINCT a.* FROM a, b, c WHERE a.id = b.pid;
Chybu byste měli vidět na první pohled. O to hůř se hledá v komplikovaném dotazu. Zpravidla na ni upozorňuje použití fráze DISTINCT. Tou chybou je buďto nesvázání tabulky c nebo nevypuštění této tabulky ze seznamu zdrojů. Díky tomu se počet řádků výsledku násobí počtem řádků tabulky c a opět ruší frází DISTINCT. V případě, že tato tabulka je malá, tak výkonnostní propad bude minimální, bude se ale zvětšovat s růstem této tabulky. Vytvářejí se tak chyby, které začnou působit potíže až po několika letém provozu systémů (samozřejmě v době, kdy už v podniku není nikdo, kdo by o systému něco věděl, a systém se občas, nepravidelně, ale naprosto totálně zpomalí).
Způsob spojení frází JOIN podporují všechny rozšířené SQL databáze a skutečně není jediný důvod proč používat starší variantu. Způsob zápisu je jednoduchý:
SELECT .. FROM a [LEFT|RIGHT|CROSS|FULL] JOIN b ON log. výraz
Při operaci JOIN se vytváří kartézký součin tabulek a a b. Log. výraz vymezuje podmnožinu tohoto součinu, která má smysl. Většinou je to výraz rovnosti primárního klíče a cizího klíče. Pokud bych měl tabulku rodičů a dětí, pak kartézký součin těchto tabulek přiřadí ke každé osobě z tabulky rodičů každé dítě z tabulky dětí, což je nesmysl. Platné jsou pouze ty dvojice, které vyhovují podmínce rovnosti primárního klíče v tab. rodiče a cizího klíče v tabulce děti.
SELECT * FROM rodice JOIN deti ON rodice.id = deti.rodic_id
Vnitřní spojení zobrazí pouze odpovídající si záznamy z obou tabulek. Vnější spojení zobrazí buďto všechny záznamy z tabulky a (LEFT JOIN) a relevantní záznamy z tabulky b, nebo relevantní záznamy z tabulky a a všechny záznamy z tabulky b (RIGHT JOIN), případně všechny záznamy z tabulek a a b (FULL JOIN). Výsledkem SQL dotazu může být jedině tabulka. Pokud vnější spojení zobrazí na jedné straně určité hodnoty, a na druhé straně nenajde relevantní záznam, doplní druhou stranu hodnotami NULL. Toho se často využívá jako vyhledávací kritérium. Příkladem může být dotaz na všechny rodiče, kteří nemají ani jedno dítě.
SELECT * FROM rodice LEFT JOIN deti ON rodice.id = deti.rodic_id WHERE deti.id IS NULL;
Primární klíč z tabulky deti bude mít ve spojení hodnotu NULL pouze v tom případě, že daný rodič nemá žádné dítě. Jinak, z požadavků na primární klíč, musí být vždy NON NULL.
Kompozice dotazů
Operací UNION můžeme spojit vrácené množiny záznamů. Varianta UNION ALL vrátí všechny záznamy včetně možných duplicitních záznamů. Jelikož neprovádí eliminaci duplicit, vyhodnotí se rychleji. Můžeme ji použít ve spojení s predikátem IN, který interně duplicity odstraňuje, nebo tam, kde víte, že budete spojovat disjunktní množiny.
SELECT 'maximum' AS typ, prijmeni, dt.mm AS mzda, dt.oddeleni FROM zamestnanci z1, (SELECT max(mzda) AS mm, oddeleni FROM zamestnanci GROUP BY oddeleni) dt WHERE z1.mzda = dt.mm AND z1.oddeleni = dt.oddeleni UNION ALL SELECT 'minimum' AS typ, prijmeni, dt.mm AS mzda, dt.oddeleni FROM zamestnanci z1, (SELECT max(mzda) AS mm, oddeleni FROM zamestnanci GROUP BY oddeleni) dt WHERE z1.mzda = dt.mm AND z1.oddeleni = dt.oddeleni ORDER BY dt.oddeleni, typ;
Dotaz získá maximální a minimální mzdy, a díky seřazení zobrazí pod sebou zaměstnance s největší a nejmenší mzdou v oddělení. Kromě operace UNION můžeme použít operace INTERSECT (průnik) a EXCEPT (rozdíl).
Použití podmíněných výrazů
Bez podmíněných výrazů by se těžko žilo. Dokud nebyly k dispozici, musely se i nepatrné transformace údajů provádět na klientské části aplikace, nebo se musely psát speciální funkce. Uvedu příklad. Dejme tomu, že budeme mít chronologickou řadu hodnot, z které budu chtít agregovat čtvrthodinové sumy. S konstrukcí CASE je tato úloha snadno řešitelná:
SELECT date_trunc('hour',cas) +
CASE
WHEN EXTRACT(minute FROM cas) BETWEEN 0 AND 14 THEN 0
WHEN EXTRACT(minute FROM cas) BETWEEN 15 AND 29 THEN 15
WHEN EXTRACT(minute FROM cas) BETWEEN 30 AND 44 THEN 30
WHEN EXTRACT(minute FROM cas) BETWEEN 45 AND 59 THEN 45
END * interval '1 minute' AS tcas,
SUM(hodnota) FROM data
GROUP BY tcas ORDER BY tcas;
CASE
Podmíněný výraz má dva základní tvary:
CASE výraz WHEN hodnota THEN hodnota .. ELSE hodnota END CASE WHEN vyraz THEN hodnota .. ELSE hodnota END
Postupně se vyhodnocují všechny větve, první pravdivou vyhodnocení končí. Pokud žádná větev nenípravdivá, a chybí větev ELSE, pak má výraz hodnotu NULL. Příkaz můžeme použít pro substituci kódu, pro ochranu před dělením nulou atd:
SELECT CASE je_muz THEN 'Muz' ELSE 'Zena' END AS Pohlavi FROM zamestnanci; SELECT CASE attr <> 0 THEN 100/attr*100 ELSE NULL END FROM data;
COALESCE
Dlouhou dobu byla konstanta NULL tabu. Pokud se objeví v libovolném výrazu, je výsledkem výrazu hodnota NULL. Navíc existovaly obavy o efektivnost implementace této hodnoty. Rozšířilo se používání zástupných konstant např. 0 nebo -1, případně prázdného řetězce. PostgreSQL ukládá NULL hodnotu jako 1 bit, tudíž alokuje mnohem méně prostoru než pro zástupnou konstantu: varchar nulové délky potřebuje 4Byte. Takže této konstanty bych se nebál, jediné místo, kde se nesmí objevit je primární klíč.
Přesto někdy potřebujeme sestavit výraz, kde se nám NULL může objevit, ale nechceme, aby NULL bylo výsledkem. Příkladem je sestavení jména a příjmení do jednoho řetězce, kdy jméno máme nepovinné. ANSI SQL a PostgreSQL řeší tuto situaci pomocí funkce COALESCE.
SELECT COALESCE(jmeno||' '||prijmeni, prijmeni,'');
Výsledkem funkce je první ne NULL parametr.
LEAST a GREATEST
Tyto funkce mohou sloužit podobně jako funkce COALESCE. Také dokáží nahradit hodnotu NULL a k tomu vyberou minimální (LEAST) nebo maximální (GREATEST) hodnotu ze seznamu parametrů. Např. potřebuji odfiltrovat všechny menší a NULL hodnoty než je určitá mez. Chci ale zachovat řádky (např. obsahují i jiné údaje, nebo je pro mne důležitý počet záznamů):
SELECT GREATEST(mereni, 0) FROM data;
Variace příkazu SELECT
V SQL je naprosto běžné, že jednu úlohu můžeme vyřešit několika různými způsoby. V našem příkladu mám tabulku zaměstnanců o 1000 záznamech a tabulku pracovišť o pěti záznamech (číselník). Budu chtít vyřešit klasickou úlohu dohledání zaměstnanců s největší mzdou v rámci pracoviště. První varianta využívá vnořený korelovaný poddotaz:
SELECT label, prijmeni, mzda FROM zamestnanci z, pracoviste p WHERE mzda = (SELECT MAX(mzda) FROM zamestnanci WHERE pracoviste = z.pracoviste) AND z.pracoviste = p.id;
Tento dotaz vyžaduje index nad sloupcem mzda. Bez něj trvá jeho prvádění 1360 ms, s ním 54 ms. Další varianty jsou postavené nad vnějším spojením a použitím odvozených tabulek. V podstatě se jedná z hlediska výkonu o ekvivalentní řešení. Nevyžadují index nad sloupcem mzda a doba provádění je cca 18 ms.
SELECT label, prijmeni, MAX FROM zamestnanci z JOIN
(SELECT MAX(mzda), pracoviste FROM zamestnanci GROUP BY pracoviste) p
ON p.max = z.mzda AND z.pracoviste = p.pracoviste
JOIN pracoviste p2 ON p2.id = z.pracoviste;
SELECT label, prijmeni, max FROM zamestnanci z JOIN
(SELECT MAX(mzda), pracoviste, label FROM zamestnanci z JOIN pracoviste p
ON p.id = z.pracoviste GROUP BY pracoviste, label) s
ON z.pracoviste = s.pracoviste AND z.mzda = max;
Mezi verzemi PostgreSQL dochází ke změnám v algoritmu hledání optimální strategie vyhodnocení dotazu. Zatím jsem se s tím nesetkal, ale musím to brát v úvahu, že po přechodu na novější verzi, budu muset provést opětovně optimalizaci databáze. Pokud budu mít k dispozici některý z nástrojů podporující automatické testování kódu dost si zjednoduším práci. Předpokladem je oddělení SQL příkazů od kódu aplikace. Následující příklad může sloužit jako ukázka (naštěstí pozitivní). Ve verzi 8.0 a nižších byl velký výkonostní rozdíl mezi oběmi variantami. Ve verzi 8.1 se obě varianty zpracují za stejný čas.
V klauzuli WHERE preferujte spojení dotazů (UNION ALL) před složenými výrazy - optimalizátor je připravený na optimalizaci dotazů, ne aritmetických výrazů. Dejme tomu, že bych do náš příklad rozšířil o tabulku mobil_tel, kde eviduji telef. cislo u zamestnancu, kteří obdrželi mobil. A z nějakého důvodu potřebuji vypsat seznam zaměstnanců, kteří mají určité telefonní číslo včetně jednoho konkrétního zaměstnance. První neoptimální varianta:
SELECT * FROM zamestnanci WHERE id = 10 OR id = ANY (SELECT zam_id FROM mobil_tel WHERE skupina = 4);
a druhá optimální (pro 8.0 a nižší)
SELECT * FROM zamestnanci
WHERE id IN (SELECT zam_id FROM mobil_tel WHERE skupina = 4
UNION ALL
SELECT 10)
V čem je rozdíl? Složený výraz id = 10 OR .. jsem nahradil jednodušším id IN (... a odstranil jsem binární operátor OR. Bylo by nepraktické, abyste hledali všechny variace SQL dotazu a testovali je. Nicméně, měli byste si každý SQL příkaz otestovat a pokud nebudete spokojeni s jeho efektivitou, tak se pokusit najít jiný tvar.
Optimalizace
Základem je vhodně navržená databáze:
- vhodně navržená a zdokumentovaná struktura tabulek, kterou můžeme rozšiřovat a udržovat,
- vhodně zvolené datové typy, např. vyvarovat se použití varcharu místo timestampu atd.,
- vhodně zvolené a udržované omezení pro doménovou integritu (databáze se jen velmi obtížně dodatečně čistí),
- pravidelné provádění auditu databáze: rušení nepoužívaných indexů, pracovních tabulek a pohledů,
- vždy se snažte zapsat výrazy obsahující porovnání tak, aby jednu stranu porovnání tvořil jeden atribut
SELECT * FROM test WHERE a+1 = 100 OR b+1 = 100; -- chybně SELECT * FROM test WHERE a = 100-1 OR b = 100-1; -- správně
Často lze jeden SQL dotaz napsat několika způsoby. Pokud je to možné, snažte se vyvarovat vnořených, korelovaných i odvozených dotazů a používejte JOIN, i když to pravděpodobně ne vždy bude možné a může se také zdát, že JOIN bude náročnější. Většinou opak je pravdou, a je menší riziko, že se použije opravdu nevhodný prováděcí plán. A to při chybně nastavených indexech může znamenat opravdu markantní rozdíl.
Příklad převodu vnořeného dotazu na klauzuli JOIN. Pokud budu chtít zobrazit všechny autory, kteří napsali skripta, tak mohu napsat několik sémanticky ekvivalentních SQL dotazů:
SELECT * FROM lide WHERE EXISTS(SELECT id FROM skripta WHERE lide.id = autor); SELECT * FROM lide WHERE id IN (SELECT autor FROM skripta); SELECT DISTINCT lide.* FROM lide JOIN skripta ON lide.id = autor; SELECT lide.* FROM lide JOIN (SELECT DISTINCT autor FROM skripta) p ON p.autor = lide.id;
které budou různě rychlé v závislosti na poměru tabulky lide ku tabulce skripta, v závislosti na rozdělení dat. Pravděpodobně nejrychlejší bude druhý a čtvrtý příklad. První má problém se sekvenčním čtením tabulky autor, a opakovaným vykonáváním poddotazu. Chyba třetího je eliminace velkého množství řádek. Tomu předejdeme pokud klauzuli DISTINCT umístíme do odvozené tabulky. Pro představu, časy vykonávání jednotlivých dotazů je: 3000, 27, 436, 36.
Vždy se snažte najít SQL dotaz, který byl řešením Vašeho úkolu. Omezte skládání výsledku na klientu. Omezte počet dotazů, které posíláte na SQL server. V případě, že by byl dotaz příliš komplikovaný a nečitelný, napište uloženou SRF funkci. Pokud můžete, používejte před připravené příkazy.