SQL – jak na dotazy 2.

19. září 2000

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í):

tabulka1.klíč = tabulka2.klíč

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ů.

tabulka1.klíč = tabulka2.klíč
AND tabulka2.klíč = tabulka3.klíč

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:

SELECT název
FROM kniha, výtisk
WHERE kniha.id = výtisk.kniha_id
  AND rok_vydání = 1998

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

SELECT příjmení, jméno
FROM autoři, kniha, napsané_knihy
WHERE kniha.id = napsané_knihy.kniha_id
  AND napsané_knihy.autor_id = autoři.id
  AND kniha.název LIKE ‚Jak publikovat články‘

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

SELECT název
FROM kniha, autoři, napsané_knihy
WHERE kniha.id = napsané_knihy.kniha_id
  AND autoři.id = napsané_knihy.autor_id
  AND jméno = ‚Alois‘
  AND příjmení = ‚Jirásek‘

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

SELECT kniha.název
FROM kniha, výtisk, nakladatelství
WHERE kniha.id = výtisk.kniha_id
  AND výtisk.nakladatel_id = nakladatelství.id
  AND nakladatelství.název = ‚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:

SELECT nakladatelství.název
FROM nakladatelství, výtisk, kniha, napsané_knihy, autoři
WHERE nakladatelství.id = výtisk.nakladatel_id
  AND výtisk.kniha_id = kniha.id
  AND kniha.id = napsané_knihy.kniha_id
  AND napsané_knihy.autor_id = autoři.id
  AND nakladatelství.rok_vydání = 1996
  AND autoři.jméno = ‚Robin‘
  AND autoři.příjmení = ‚Cook‘

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ů:

SELECT [MODIFIKÁTOR] jména 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:

SELECT DISTINCT nakladatelství.název
FROM nakladatelství, výtisk, kniha, napsané_knihy, autoři
WHERE nakladatelství.id = výtisk.nakladatel_id
  AND výtisk.kniha_id = kniha.id
  AND kniha.id = napsané_knihy.kniha_id
  AND napsané_knihy.autor_id = autoři.id
  AND nakladatelství.rok_vydání = 1996
  AND autoři.jméno = ‚Robin‘
  AND autoři.příjmení = ‚Cook‘

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:

SELECT TOP 5 příjmení, jméno
FROM kniha, autoři, napsané_knihy
WHERE kniha.id = napsané_knihy.kniha_id
  AND napsané_knihy.autor_id = autoři.id
  AND kniha.název = ‚Jak publikovat články‘
ORDER BY příjmení, jméno

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:

jméno_sloupce AS alias_sloupce

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ě:

SELECT TOP 10 kniha_id AS číslo, název AS dílo
FROM kniha

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:

SELECT a + ‚+‘ + b + ‚=‘ AS výraz, a+b AS součet
FROM temp

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:

SELECT příjmení+‘,’+jméno AS ‚celé jméno‘
FROM kniha, autoři, napsané_knihy
WHERE kniha.id = napsané_knihy.kniha_id
  AND napsané_knihy.autor_id = autoři.id
  AND název = ‚O pejskovi‘

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.

Štítky: Články

Mohlo by vás také zajímat

Nejnovější

6 komentářů

  1. David Kolečkář

    Pro 14, 2009 v 14:04

    Rád bych sdělil autorovi tohohle článku, že by bylo lepší, kdyby si vše pořádně vyzkoušel dřív, než začne psát kódy. Je tam chyb jak máku, od špatně pojmenovaných sloupců, které ukládá nějak a jinak vyvolává, až po volání neexistujících sloupců v tabulkách…například: poslední SELECT -> nakladatelství.rok_vydání -> v tabulce nakladatelství žádný sloupec rok_vydání neexistuje -> rok_vydání se nachází v tabulce výtisk…při vytváření tabulky v prvním díle napsané_knihy vytváříte sloupec publikace_id, ale potom ukládáte do tabulky sloupec kniha_id…to stejné v tabulce výtisk…
    a pak to dapadá tak, že člověk, který se chce něco naučit ztácí čas přemýšlením kde má chybu…Dále by bylo super, kdyby byla k dispozici databáze se kterou pracujete, protože jí něak plníte v prvním díle a ve druhém už voláte úplně jiné hodnoty…no mazec prostě.

    Díky.

    Odpovědět
  2. Miroslav Kučera

    Pro 14, 2009 v 14:33

    David Kolečkář: uvedomujete si, ze reagujete na 9 let (!) stary clanek? :-)

    Odpovědět
  3. Miroslav Vrána

    Říj 21, 2012 v 0:42

    D.Kolečkář má pane Kučero pravdu. Není to chyba autora, ale chyba reakce interval.cz. Například je zde věta: „Předpokládejme, že v tabulkách jsou následující data:“
    Data se ale za těch 13 let ztratila. Přitom i dnes existuje spoustu lidí, kteří se chtějí naučit základní dotazy SQL a hledají na intervalu …
    A omlouvám se, že reaguji po dalších třech letech.

    Odpovědět
  4. Luboš

    Lis 26, 2013 v 13:37

    Pravda, pravda.

    Odpovědět
  5. Anonym

    Led 1, 2014 v 10:07

    Já se učím SQL od základu i v roce 2014… a je nás asi víc, kteří uvítají relevantní texty…

    Odpovědět
  6. Miroslav Kučera

    Led 2, 2014 v 13:15

    Data doplněna zpět do článku :)

    Odpovědět

Napsat komentář

Vaše e-mailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *