Jazyk SQL

Jazyk SQL je nástroj pro komunikaci uživatele s relační databází. Oproti programovacím jazykům je jednodušší a bližší gramatice mluvené řeči. Je standardizován jako SQL ANSI. V jazyce SQL vytváříme tzv. dotazy.

SQL dotazy dělíme na dva základní typy: dotazy pro manipulaci s daty DML (data manipulation language) a DDL (data definition language) tedy dotazy pro definici dat. DML slouží pro manipulaci se záznamy v tabulkách, tedy pro vyptání dat, mazání záznamů, vyprazdňování tabulek, vkládání a aktualizování záznamů. DDL naopak slouží pro definici databázových struktur. Pro tvorbu databází, tabulek, indexů, pohledů, funkcí, triggerů atd. Dále rozlišujeme DCL a TCL, tedy data control language a transaction control language. První z nich slouží k nastavení přístupových práv (příkazy GRANT a REVOKE), druhý pak k práci s transakcemi.

Poznámka pro pokročilé

Kromě jazyka SQL můžeme psát v PostgreSQL funkce i v dalších jazycích. Mimo jiné se jedná o Perl, Python, R, JavaScript a další. Zejména však v PL/PgSQL, procedurálním jazyku PostgreSQL svou syntaxí podobného jazyku používanému v databázích Oracle.

Syntax

Základní kostra jazyka SQL vypadá zhruba následovně:

PROVEĎ
S ČÍM
ZA JAKÝCH PODMÍNEK

Pro výběr dat z tabulky tedy:

VYBER
seznam položek
Z tabulky
PRO KTERÉ PLATÍ
podmínka;

Poznámka

SQL dotazy v PostgreSQL zakončujeme středníkem.

DML

SELECT

Dotaz, kterým vybíráme data z databáze, uvozuje příkaz SELECT následovaný výčtem sloupců požadovaného výstupu. Výčet sloupců může být nahrazen * pro výběr všech sloupců. Pokud předřadíme výčtu sloupců DISTINCT bude dotaz vracet pouze unikátní kombinace hodnot. Klauzule FROM uvozuje výčet tabulek, ze kterých budeme vybírat a které mohou (ale nemusí) být propojeny klauzulí JOIN. Následovat může výčet podmínek uvedený klauzulí WHERE. Podmínky můžeme řetězit booleovskou logikou pomocí AND, OR, případně vylučovat pomocí NOT.

Nakonec můžeme použít GROUP BY pro sdružování při agregacích, ORDER BY pro sezaření záznamů či případně LIMIT a OFFSET pro omezení řádků výstupu, eventuálně další, méně obvyklé klauzule.

Jak to funguje v praxi?

Dejme tomu, že chcete zjistit, které muchomůrky jsou vhodné k jídlu. Přijdete do knihovny a zeptáte se:

Dobrý den, slečno, prosím Vás,
podívala byste se mi do Smotlachova atlasu hub a
zjistila,
které muchomůrky jsou jedlé?

Slečna půjde, vytáhne z regálu „Smotlachu“, podívá se do rejstříku a najde všechny muchomůrky, každou nalistuje a zjistí, které jsou jedlé. Ty pro Vás vypíše.

V relační databázi by to vypadalo nějak takto.

Máme tabulku nazvanou smotlacha_atlas_hub. Vypadá nějak takto:

rod

druh

rod_lat

druh_lat

popis

foto

jedla

vyskyt_lokalita

vyskyt_od

vyskyt_do

muchomůrka

růžovka

amanita

rubescens

…

ruzovka.jpg

true

MULTIPOLYGON(((…

1.6.

31.10.

SQL dotaz potom bude vypadat následovně:

SELECT
   rod
   , druh
   , foto
FROM smotlacha_atlas_hub
WHERE
   rod = 'muchomůrka'
   AND jedla = true;

V překladu do češtiny by dotaz mohl znít:

VYBER
   seznam požadovaných údajů
Z tabulky
[PRO KTERÉ PLATÍ
   podmínka]

JOIN

Rozlišujeme dva typy příkazu JOIN, tj. spojení tabulek: INNER JOIN a OUTER JOIN.

INNER JOIN vrátí pouze takové záznamy, kde došlo k nalezení potřebné hodnoty v obou tabulkách. Naproti tomu OUTER JOIN vrací pro jednu, případně obě tabulky všechny záznamy. OUTER JOIN dělíme na LEFT JOIN, RIGHT JOIN a FULL JOIN. LEFT a RIGHT JOIN vrací všechny záznamy z levé nebo pravé tabulky. FULL JOIN vrátí všechny záznamy z obou tabulek. Speciální situací je CROSS JOIN, který vrací kartézský součin záznamů v obou tabulkách.

Záznamy obvykle párujeme pomocí klauzule ON, za kterou následují podmínky propojení podobně jako za klauzulí WHERE. Alternativou je použití klauzule USING, kde je uveden název sloupce, který musí být v obou tabulkách. Další možností je NATURAL JOIN, který použije stejně pojmenované sloupce. Ten však nedoporučeme příliš používat, zvláště v databázích s proměnlivou strukturou.

-- vyber "rod druh", "lokalita", "vyskyt"
SELECT houby.rod || ' ' || houby.druh, lokalita.nazev, houby.vyskyt
-- z tabulky houby
FROM houby
-- spoj podle sloupečků s id houby
JOIN lokalita ON houby.id = lokalita.houby_id
-- ale pouze tam, kde lokalita je v oblasti "Vysočina"
WHERE ST_Intersects(
   (
      SELECT geom FROM oblasti WHERE nazev = 'Vysočina'
   )
   , lokalita.geom)
-- a pouze tam, kde výsky je "od"
AND houby.vyskyt @> '2015-07-15'::timestamp;

SELECT houby.rod || ' ' || houby.druh
FROM houby
JOIN r_recept ON r_recept.houby_id = houby.id
JOIN recept ON recept.id = r_recept.recept_id
WHERE recept.nazev = 'smaženice';

UPDATE

UPDATE slouží k aktualizování hodnot vybraných sloupců. Používá se klauzule WHERE a výrazy. Také je možno použít klauzuli FROM a aktualizovat tabulku hodnotami z jiných tabulek.

Příklad nastavení výskýtu od 1.června pro všechny houhy z rodu „amanita“:

UPDATE smotlacha_atlas_hub SET vyskyt_od = '1.6.' WHERE rod_lat = 'amanita';

DELETE

DELETE slouží k mazání vybraných záznamů z tabulek.

Příklad odstranění všech jedlých hub z tabulky:

DELETE smotlacha_atlas_hub WHERE jedla = true;

TRUNCATE

TRUNCATE slouží k okamžitému vyprázdnění celé tabulky. Je rychlejší, než použití DELETE bez podmínek.

TRUNCATE smotlacha_atlas_hub;

Množinové operace

Množinové operace pracují s výsledky více poddotazů. Jedná se o UNION, UNION ALL, EXCEPT a INTERSECT.

UNION vrací sjednocení záznamů z obou dotazů. Záznamy, které jsou výsledkem (tvz. recordset) obou dotazů, jsou po sjednocení obsaženy pouze jednou. Naproti tomu UNION ALL vrátí všechny záznamy, výsledkem sjednocení je tedy součet záznamů z obou recordsetů.

Poznámka pro pokročilé

Pokud víme, že záznamy se mezi dotazy neduplikují, je lepší použít UNION ALL. Provádění pak bude efektivnější, protože si ušetříme porovnávání obou výstupních recordsetů.

EXCEPT vrací rozdíl, tedy pouze takové záznamy, které se vyskytují pouze v prvním recordsetu. INTERSECT vrací jejich průnik. Tedy záznamy, které se vyskytují v obou recordsetech.

Poddotazy

V rámci dotazu můžeme dotazovat další vnořené dotazy uzavřené do závorek.

SELECT recepty.* FROM
(
SELECT DISTINCT recept_id FROM r_recept WHERE houby_id IN
   (
      SELECT * FROM houby WHERE rod = 'bedla'
   )
) recepty_na_bedly
JOIN recepty ON recepty.id = recepty_na_bedly.recept_id;

DDL

CREATE a DROP jsou základní příkazy z Data Definition Language. Pomocí nich vytváříme tabulky, pohledy, omezení, funkce, typy a další.

A co prostorová databáze?

Dejme tomu, že nás zajímají jen ty houby, které rostou v okruhu třiceti kilometrů od Pece pod Sněžkou, kde hodláme strávit dovolenou.

V takovém případě slečna musí porovnat místo výskytu s vámi zadanou lokalitou.

Poznámka pro pokročilé

Je zjevné, že k požadovanému výsledku se může slečna dobrat různými, různě efektivními způsoby. Postup, kterým bude pracovat se nazývá prováděcí plán (query plan). K volbě ideálního způsobu slouží statistiky, které si databáze ukládá a které jsou aktualizovány po každém dotazu.

Dotaz do SQL může potom vypadat následovně:

SELECT
   rod
   , druh
   , foto
FROM smotlacha_atlas_hub
WHERE
   rod = 'muchomůrka'
   AND jedla = true
   AND ST_Distance(vyskyt_lokalita,
   'SRID=5514;POINT(-641455 -987918)'::geometry) < 3e4;