SQL – jak na dotazy 2.

V dnešním díle se podíváme na získávání informací z více tabulek najednou, zadefinujeme si pojem přirozeného spojení. Dále si ukážeme, že vrácené výstupní řádky můžeme zformátovat do přehledných výpisů, rovněž máme možnost si změnit jednotlivá záhlaví sloupců. Nakonec uvedu nějaké tipy a triky, kterými můžeme doladit naše dotazy, aby přesně odpovídaly našim představám.

Co se týká syntaxe příkazu SELECT, stále zůstaneme u naší zjednodušené definice z předchozího dílu. Příklady budou vycházet z databázového schématu evidence knihovny, který jsme si zadefinovali v minulém díle.

Přirozené spojení

V předchozích příkladech jsme byli vždy omezeni na jednu jedinou tabulku. Mohli jsme tedy vypisovat pouze údaje, které byly dostupné v rámci jedné tabulky. Upřimně řečeno, takové údaje jsou většinou prakticky nevyužitelné. Ve většině případech budeme chtít vypisovat údaje, které budeme potřebovat přečíst hned z několika tabulek najednou. K tomu potřebujeme znát ještě jeden pojem, a to přirozené spojení. Přirozené spojení je sloučení dvou a více tabulek „dohromady“. Každý řádek v jedné tabulce je „rozšířen“ o sloupce z tabulek dalších. Odpovídající řádek získá systém díky vazbě primární klíč – cizí klíč. Nejlépe přirozené spojení ukáže následující příklad. Mějme tabulky KNIHA a AUTOŘI a tabulku NAPSANÉ_KNIHY, která nám zprostředkovává vazbu typu M:N mezi entitami KNIHA a AUTOR. Předpokládejme, že v tabulkách jsou následující data:

KNIHA: 
ID   NÁZEV 
1021 O pejskovi 	
1022 Naše zahrada 	
1024 Jak publikovat články 	
1025 Globální oteplování 

	 
AUTOŘI: 	
ID JMÉNO PŘÍJMENÍ TITUL PROSTŘEDNÍ 	
12 jan   novák    ing    	
13 petr  nový           františek 	
14 jiří  nováček  dr 	
16 david dvořák   ing   tomáš 

	 
NAPSANÉ_KNIHY: 	
KNIHA_ID AUTOR_ID 	
1021     13 
1021     16 
1024     12 
1022     12 
1024     14 
1024     16

Nyní ukáži, jak bude vypadat přirozené spojení těchto tří tabulek. Samozřejmě, vzhledem k tomu, že operace spojení je binární (tj. definovaná na dvou operandech), spojení tří tabulek je potřeba udělat nadvakrát. Nejprve spojíme tabulku NAPSANÉ_KNIHY a KNIHA a pak k výsledné tabulce připojíme ještě tabulku AUTOŘI. Celková tabulka pro přirozené spojení tří tabulek bude vypadat následovně: (název knih je zkrácen z důvodů místa, první dva sloupce jsou z tabulky NAPSANÉ_KNIHY, další dva z tabulky KNIHA, zbývající pak z tabulky AUTOŘI)

KNIHA_ID AUTOR_ID ID    NÁZEV     ID  JMÉNO  PŘÍJMENÍ TITUL PROSTŘEDNÍ 
1021     13       1021  O pej...  13  petr   nový           františek 
1021     16       1021  O pej...  16  david  dvořák   ing   tomáš 
1024     12       1024  Jak p...  12  jan    novák    ing 
1022     12       1022  Naše ...  12  jan    novák    ing
1024     14       1024  Jak p...  14  jiří   nováček  dr 
1024     16       1024  Jak p...  16  david  dvořák   ing

Z původních tří tabulek můžeme vyčíst, že např. knihu číslo 1021, což je kniha O pejskovi, napsal autor číslo 13, jehož jméno je Petr František Nový. Ve výsledné tabulce takovou informaci máme už přímo obsaženou v prvním řádku tabulky. Sestavování SQL dotazů z více tabulek spočívá v tom, že systém si na základě vazby primární klíč – cizí klíč vytvoří „souhrnnou“ tabulku, ve které pak informace vyhledává.

Všimněte si ještě jedné skutečnosti. V tabulce NAPSANÉ_KNIHY nemáme vůbec řádek, který by obsahoval ve sloupci KNIHA_ID číslo 1025, které odpovídá knize „Globální oteplování“. To, že tuto knihu nemáme v  této tabulce obsaženou, zobrazuje, že tuto knihu nikdo nenapsal. Protože tato kniha nebyla tedy nikým napsána, není ani obsažena ve výsledné virtuální tabulce přirozeného spojení. (Ve výsledné tabulce je dvakrát sloupec ID. První patří tabulce KNIHA, druhý tabulce AUTOŘI. Kdybychom chtěli do výsledku zahrnout právě nějaká ID, museli bychom specifikovat, z jaké tabulky se mají vzít.)

Výpis z více tabulek

Chceme-li získat informace z více tabulek, musíme v dotazu uvést jejich přirozené spojení. V příkazu SELECT přirozené spojení zapisujeme v části WHERE, a to zápisem ve tvaru (za klíč dosadíme primární a cizí):

kde TABULKA1 a TABULKA2 jsou tabulky, které přirozeně spojujeme. Pokud spojujeme tři a více tabulek, nemůžeme napsat TABULKA1.KLÍČ = TABULKA2.KLÍČ = TABULKA3.KLÍČ, ale musíme tuto rovnosti zapsat po více částech. Tento postup lze aplikovat na libovolný počet tabulek, jak bude vidět z příkladů.

Všechny tabulky, které se přirozeného spojení zúčastní, musíme úvest za klíčovým slovem FROM. V části WHERE, když se odkazujeme na názvy sloupců, může též dojít ke kolizi názvů: název sloupce bude stejný pro dvě a více tabulek. V takovém případě názvu sloupce musí předcházet jméno tabulky a znak tečka.

Příklady

Následuje pár příkladů využívající přirozeného spojení více tabulek. Každý příklad lze zapsat několika způsoby, vždy uvádím jeden z možných.

Názvy všech knih, jejichž výtisky byly vydány v roce 1998:

Kteří autoři napsali knihu „Jak publikovat články“?

Názvy všech knih, které napsal Alois Jirásek:

Názvy všech děl, která vydalo nakladatelství ‚Iota‘:

Ve kterých nakladatelstvích byla v roce 1996 vydána díla autora Robina Cooka? Odpověď nám dá následující dotaz:

V uvedených příkladech může dojít k duplicitám. To jest, ve výstupu se mohou nějaké řádky opakovat. Jedním z možných nástrojů, jak tyto nežádoucí duplicity odstranit, je použití modifikátoru DISTINCT.

Modifikátor DISTINCT

Zůstaňme u posledního příkladu. Pokud by nějaké nakladatelství v roce 1996 vydalo např. 3 různá díla od Robina Cooka, dostali bychom název takového nakladatelství ve výstupním výpise celkem třikrát. Modifikátor DISTINCT slouží k odstranění duplicit v odpovědích na příkaz SELECT. Modifikátory se zapisují hned za klíčové slovo SELECT, ještě před názvy projektovaných sloupců:

Takže očekávanou odpověď pro otázku v posledním příkladu, bychom získali následujícím příkazem:

Modifikátor TOP

Dalším, často používaným modifikátorem, je klíčové slovo TOP. Někdy se nám totiž může stát, že vrácených řádků vyhovujících zadanému dotazu může být velmi mnoho. Modifikátor TOP zajistí vrácení jenom prvních N řádků. Číslo N zadáme přímo v  dotazu za klíčovým slovem TOP. Kdybychom chtěli získat abecedně prvních pět autorů publikace „Jak publikovat články“, napsali bychom následující dotaz:

Modifikátor ALL

Posledním základním modifikátorem, který bych zde mohl uvést, je ALL. Ten je implicitní a znamená vrácení všech vyhovujících záznamů. To obecně chceme nejčastěji, a tak tento modifikátor nemusíme zapisovat.

Modifikace záhlaví sloupců

Vždy, když dostaneme výpis řádků na nějaký dotaz, sloupce ve výpisu jsou pojmenovány přesně tak, jak jsme je zapsali při vytváření tabulky. Jazyk SQL nám umožňuje měnit názvy sloupců, podle naší potřeby. K tomu slouží klíčové slovo AS, které se uvádí za jméno sloupce, a za ním následuje nový název:

Kdybychom chtěli vypsat prvních 10 záznamů z tabulky KNIHA a chtěli bychom, aby sloupce byly pojmenovány jako ČÍSLO a DÍLO, zapsali bychom příkaz SELECT následovně:

Jednoduchý formát výstupních sloupců

Někdy je užitečné formátovat výstupní položky do tvaru, který potřebujeme v naší konkrétní aplikaci. Např. budeme-li vypisovat nějaké finanční částky, budeme chtít za každou částku připsat řetězec ‚Kč‘, apod. Nebo můžeme s číselnými položkami provádět aritmetické operace, řetězcové položky slučovat do jedné, apod. Následuje pár praktických příkladů:

V pomocné tabulce TEMP máme dva sloupce A a B, které představují hodnoty proměnných A a B. Následující SELECT vrátí řádky ve tvaru A + B = součet:

Vypište jména všech autorů knihy ‚O pejskovi‘. Jména vypište jako jediný sloupec pojmenovaný CELÉ JMÉNO a bude se skládat z příjmení a jména odděleného čárkou:

Tím jsem snad vyčerpal základní možnosti jednoduchého použití příkazu SELECT. V  příštím díle se budu věnovat pokročilým dotazům jazyka SQL, a to konkrétně dotazům s užitím agregace.

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

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