SQL – jak na dotazy 1.

Po několika dílech se dostávám k vyhledávání informací v databázi. Pro získávání jakýchkoliv informací slouží jediný příkaz SELECT, který je ze všech příkazů jazyka SQL nejsložitější, má nejbohatší strukturu a nejvíce možností. V souvislosti s tímto příkazem rozšířím naše vědomosti o další základní pojmy, které se v relačních databázích používají. Těmito pojmy budou dnes projekce a restrikce. Příkazu SELECT pro jeho bohaté využití se budeme věnovat několik dílů, v nichž se budu snažit pojmout všechny možnosti použití. V dnešním si povíme základní vlastnosti a nejjednodušší použití.

Základní databázové schéma

Pro potřebu příkladů, které budu dále uvádět si zadefinujme novou databázi. Již nebudu používat model „naší“ firmy, o který jsem se opíral v příkladech v předchozích článcích, ale zavedu model evidence knihovny. Následuje přehled a popis jednotlivých tabulek pro náš model (pozn.: v databázovém světě se pro pořadové číslo velmi často používá označení ‚id‘ – jako identifikátor). Budeme evidovat informace o knihách a jejich výtiscích, nakladatelstvích a autorech. Náš celý systém se bude skládat z pěti tabulek:

Tabulka: Kniha

CREATE TABLE kniha (id INTEGER, název VARCHAR(50), PRIMARY KEY (id))

Tabulka KNIHA bude obsahovat jenom názvy knih. Každá kniha (ve smyslu dílo) bude mít své ID.

Tabulka: Autoři

CREATE TABLE autoři (id INTEGER, jméno VARCHAR(10), příjmení VARCHAR(20), titul VARCHAR(7), prostřední VARCHAR(10), PRIMARY KEY (id))

U každého autora budeme evidovat jeho jméno a příjmení, dále titul a případně prostřední jméno.

Tabulka: Napsané_knihy

CREATE TABLE napsané_knihy (publikace_id INTEGER, autor_id INTEGER, PRIMARY KEY (publikace_id, autor_id), FOREIGN KEY (publikace_id) REFERENCES kniha (id), FOREIGN KEY (autor_id) REFERENCES autoři (id))

Tato tabulka zprostředkovává vazbu mezi tabulkami KNIHA a AUTOŘI, typu M:N (jeden autor může napsat více knih, jedna kniha může být napsána více autory).

Tabulka: Nakladatelství

CREATE TABLE nakladatelství (id INTEGER, název VARCHAR(20), adr_ulice VARCHAR(20), adr_číslo INTEGER, adr_město VARCHAR(15), adr_psč NUMBER(5,0), telefon VARCHAR(15), email VARCHAR(40), web_stránka VARCHAR(60), PRIMARY KEY (id))

O každém nakladatelství si budeme uchovávat jeho název, plnou adresu, telefon, kontaktní e-mail a webovou stránku.

Tabulka: Výtisk

CREATE TABLE výtisk (id INTEGER, publikace_id INTEGER, nakladatel_id INTEGER, vazba VARCHAR(10) DEFAULT ‚pevná‘, cena NUMBER(8,2) DEFAULT 0, počet_stran INTEGER, hmotnost NUMBER(6,1), vydání INTEGER, rok_vydání INTEGER, PRIMARY KEY (id), FOREIGN KEY (kniha_id) REFERENCES kniha (id), FOREIGN KEY (nakladatel_id) REFERENCES nakladatelství (id))

Tato tabulka popisuje konkrétní výtisk jednoho díla. Zároveň reprezentuje vazbu mezi tabulkami KNIHA a NAKLADATELSTVÍ, opět typu M:N (jedno dílo může být vydáno různými nakladatelstvími, jedno nakladatelství může vydat více knih).

Na tyto tabulky se budu v následujících příkladech, pokud neuvedu jinak, odkazovat.

Syntaxe příkazu SELECT

Jak už jsem uvedl v úvodu, příkaz SELECT je velmi rozmanitý a skýtá obecně velmi mnoho možností použití. Kompletní syntaxe vypadá následovně:

‚select‘ := SELECT seznam sloupců FROM seznam tabulek [WHERE restrikce] [GROUP BY výrazy pro seskupení] [HAVING doplňující podmínky pro skupinu] [UNION ‚select‘] [INTERSECT ‚select‘] [MINUS ‚select‘] [ORDER BY dle čeho třídit]

Uvedená definice je rekurzivní z toho důvodu, že pomocí klíčových slov UNION, INTERSECT a MINUS, můžeme dělat sjednocení vrácených sloupců z uvedených dotazů, nebo průnik či doplněk. V dnešním díle se budeme o příkazu SELECT bavit na zjednodušené úrovni, jejiž syntaxe by mohla vypadat takto:

SELECT seznam sloupců FROM seznam tabulek [WHERE restrikce] [ORDER BY dle čeho třídit]

SELECT seznam sloupců

Nejprve si řekneme, co znamená pojem projekce. Projekci si lze představit jako zobrazení konkrétní položky (sloupce). Jednotlivé řádky tabulky nám mohou vystupovat jako n-tice hodnot, např. v tabulce AUTOŘI jeden řádek je pětice různých hodnot (id, jméno, příjmení, titul, prostřední). Kdybychom se v tabulce zajímali pouze o tituly, udělali bychom projekci na složku titul, zapisujeme P[titul]. Kdyby nás zajímalo jméno a příjmení, udělali bychom projekci P[jméno, příjmení].

Jako seznam sloupců uvedeme právě projektované sloupce, tj. sloupce, jejichž hodnoty nás zajímají. Obecně zde můžeme uvést výčet sloupců z více tabulek. Pokud by se některé názvy sloupců v různých tabulkách opakovaly, pak je potřeba název sloupce uvést ve tvaru jméno_tabulky.jméno_sloupce. Jména sloupců oddělujeme čárkou.

FROM seznam tabulek

V této části uvedeme tabulky, ze kterých chceme data získávat. Všechny sloupce, zadané za klíčovým slovem SELECT, musí patřit do zde uvedených tabulek. Názvy tabulek oddělujeme také čárkou. Zatím budeme pracovat s jednou tabulkou.

Jednoduchý výpis tabulky

Pro výpis obsahu tabulky použijeme příkaz SELECT v jeho nejjednodušší podobě. Následující příklady provedou výpis základních údajů z tabulek:

SELECT id, název FROM kniha SELECT * FROM autoři SELECT název, adr_ulice, adr_číslo, email FROM nakladatelství

První SELECT zobrazí obsah celé tabulky KNIHA. Druhý příkaz zobrazí obsah celé tabulky AUTOŘI. Znak hvězdička nahrazuje názvy všech sloupců v tabulce a to ve stejném pořadí, jak jsou sloupce v tabulce vytvořeny. Třetí SELECT zobrazí jen některé vybrané sloupce z tabulky NAKLADATELSTVÍ.

Restrikce

S tímto pojmem jsme se již několikrát setkali v předchozím díle, kdy jsem mluvil o aktualizaci dat v tabulkách. Jistě si vzpomenete, že v příkazech UPDATE a DELETE jsme uváděli část WHERE s podmínkami, které nám říkaly, na které řádky se má příkaz aplikovat. Právě ono omezování řádků pomocí podmínek se nazývá restrikce.

WHERE restrikce

Podmínka se skládá z kombinace logických výrazů a logických spojek AND, OR a NOT a kulatých závorek. Logické výrazy můžeme sestavovat z názvů sloupců, z relačních operátorů a speciálních klíčových slov. V jednotlivých částech logického výrazu mohou vystupovat aritmetické výrazy se základními matematickými operátory a dále řetězcové výrazy s operátory pro řetězce. Přehled základních operátorů ukazuje následující tabulka:

a>b, a<b, a&gt=b;, a<=b, a=b IS NULL, IS NOT NULL LIKE ‚K%‘, LIKE ‚L____‘, LIKE ‚_b%k‘ AND, OR, NOT, (, )

Každý databázový systém má svá různá rozšíření, toto je úplný základ, který by měl většinou dodržovat každý systém. Zastavím se na chvíli u některých operátorů. Klíčovo slovo IS NULL se používá pro zapsání podmínky, že nějaká položka (sloupec) má nedefinovanou hodnotu (uživatel ji nezadal). IS NOT NULL slouží pro opačný případ, kdy se ptáme, zdali položka má definovanou hodnotu.

Operátor LIKE slouží pro porovnávání řetězců a navíc nabízí možnost jakýchsi jednoduchých regulárních výrazů. LIKE ‚K%‘ znamená řetězec, který začíná na písmeno ‚K‘ (znak ‚%‘ nahrazuje libovolný počet znaků, i nula). LIKE ‚L____‘ je zápis řetězce, který začíná na písmeno ‚L‘ a má právě 5 znaků (znak ‚_‘ nahrazuje libovolný jeden znak). Příklad LIKE ‚_b%k‘ je jen kombinací dvou předchozích. Říká, že řetězec obsahuje na druhém místě písmeno ‚b‘, následované libovolným počtem znaků, které pak končí písmenem ‚k‘. Následuje pár příkladů s  užitím restrikce:

Příklady

Názvy všech nakladatelství, která jsou z Brna:

SELECT název FROM nakladatelství WHERE adr_psč >= 60000   AND adr_psč <= 64400

Název a e-mail všech nakladatelství, která mají svou webovskou stránku:

SELECT název, email FROM nakladatelství WHERE web_stránka IS NOT NULL

Příjmení všech autorů, jejichž křestní jména začínají na písmeno ‚T‘, a kteří nemají žádný titul:

SELECT příjmení FROM autoři WHERE jméno LIKE ‚T%‘   AND titul IS NULL

ORDER BY dle čeho třídit

Jak už samo klíčové slovo ORDER BY napovídá, slouží pro třídění výstupu. Třídění výstupních řádků podle hodnot určitého sloupce nebo sloupců je velmi užitečné a často využívané. Sloupce, podle kterých chceme třídit, uvedeme jako seznam za ORDER BY. Jednotlivé sloupce od sebe oddělíme opět čárkou. Základní třídění probíhá lexikograficky u řetězcových položek. Třídění dle čísel, datumu, apod. probíhá podle našeho očekávání. Pokud byste v řetězcovém třídění zaznamenali nějaké anomálie typu, že písmeno ‚ch‘ se bere jako dvojici písmen ‚c‘ a ‚h‘, nebo že by systém špatně zařazoval slova, která by začínala na nějaké diakritické písmeno, bude problém v lokálním nastavení vašeho databázového systému. Systému je nutné říct, že vaše prostředí je české.

Příklad

Chceme seznam jmen všech brněnských nakladatelství s jejich kompletními adresami, tříděno dle názvů ulic (pakliže sídlí ve stejné ulici, dle jejich čísla):

SELECT název, adr_ulice, adr_číslo, adr_město, adr_psč FROM nakladatelství WHERE adr_psč >=60000   AND adr_psč <=64400 ORDER BY adr_ulice, adr_číslo

Nejčastěji jsou hodnoty tříděny vzestupně, od nějnižší po nejvyšší. Pokud bychom chtěli provést třídění sestupně (inverzní), použijeme třídění v následujícím tvaru:

ORDER BY dle čeho třídit DESC

Použití modifikátoru DESC by mělo fungovat ve většině databázových systémů. Kromě klíčového slova DESC existuje i ASC, které je pro základní vzestupné třídění, jeho uvedení je však nepovinné a zpravidla se tedy ani neuvádí.

V příštím díle se podíváme hloubeji na psaní SQL dotazů, povíme si o výpisu informací z více tabulek (zadefinujeme si pojem přirozeného spojení tabulek). Dále se dovíte zajímavé tipy a triky, jak můžeme zpřehledňovat a formátovat výstupní řádky.

Starší komentáře ke článku

Pokud máte zájem o starší komentáře k tomuto článku, naleznete je zde.