PEDNKA 6 SQL zkladn spojen tabulek Spojen tabulek

PŘEDNÁŠKA 6 SQL: základní spojení tabulek

Spojení tabulek Základním principem relačního modelu dat jsou relace modelované prostřednictvím cizích klíčů. Vytváření relací chceme dosáhnout efektivního návrhu databáze a zajištění integrity dat. Klasické (jednoduché) SQL dotazy SELECT již známe. V praxi, ale budeme potřebovat získávat data z více tabulek najednou. Existuje více způsobů jak v dotazu spojit a získat data z více tabulek. Spojení tabulek se označujeme jako JOIN.

Princip činnosti • Z hlediska efektivního návrhu modelujeme data podle entit do více tabulek. V koncových aplikacích, které databázi využívají, ale chceme zobrazovat data jinak. Chceme je zobrazit jako informace mající smysl. • Viz data a informace v první přednášce. • Uvažujme např. tabulku vypisující seznam knih VKOL.

Jednoduchá ukázka Máme tabulku knihy: Tabulka autori: kniha_id nazev autor_id jmeno prijmeni 1 Broučci 10 10 Jan Karafiát 2 Babička 20 20 Božena Němcová • Uživateli by takový pohled na data byl velmi málo užitečný a nepřehledný. • Potřebujeme z něj získat informace: výsledek dotazu spojení. Kniha Autor Broučci Jan Karafiát Babička Božena Němcová

Dotaz spojení // Nejprve vybírám atributy z obou tabulek, které mají být zobrazeny ve výsledku SELECT knihy. nazev AS Kniha, // pokud chci nahradit název sloupce, použiju alias CONCAT (autori. jmeno, ' ', autori. prijmeni) AS Autor // funkce CONCAT() spojí dvě nebo více hodnot v jeden řetězec (Jan ' ' Karafiát) // CONCAT() umožňuje spojit jen hodnoty na stejném řádku výsledku // apostrofy představují mezeru, spojuji teda jméno, mezeru a příjmení FROM knihy, autori // Určuju tabulky zdrojových dat, oddělujeme čárkou – čárkové spojení WHERE knihy. autor_id = autori. autor_id; // Podmínka spojení, porovnávám hodnoty cizího klíče tabulky knihy a primárního klíče tabulky autori. Podmínka samozřejmě může obsahovat i filtrování.

Filtrování ve spojení • Dotaz spojení by měl např. ukázat pouze knihy napsané Boženou Němcovou a to ve stejném formátu jako předchozí příklad. Dotaz jednoduše doplníme o další dílčí podmínku. SELECT knihy. nazev AS Kniha, CONCAT (autori. jmeno, ' ', autori. prijmeni) AS Autor FROM knihy, autori WHERE knihy. autor_id = autori. autor_id AND autori_id = 20; Kniha Autor Babička Božena Němcová

Relace M: N Tabulka knihy: Tabulka autori: kniha_id nazev autor_id jmeno prijmeni 1 Učitelé a technologie 10 Klára Šeďová 2 E-learning 20 Jiří Zounek Vazební tabulka kniha_autor: kniha_id autor_id 1 10 1 20 2 20

Dotaz spojení SELECT knihy. nazev AS Kniha, CONCAT( autori. jmeno, ' ', autori. prijmeni ) AS Autor FROM knihy, autori, kniha_autor WHERE knihy. kniha_id = kniha_autor. kniha_id AND kniha_autor_id = autori. autor_id; Kniha Autor Učitelé a technologie Klára Šeďová Učitelé a technologie Jiří Zounek E-learning Jiří Zounek

Agregační funkce() • Základní - Počet (COUNT), součet (SUM), průměr (AVG), minimum (MIN) a maximum (MAX). • Výpočet funkce se provádí buď nad všemi záznamy v odpovědi na dotaz (pokud dotaz neobsahuje GROUP BY) nebo nad každou skupinou určenou GROUP BY. • Pro funkce SUM a AVG musí být argument číselného typu, pro funkce MAX a MIN buď číselného typu nebo některého z typů typu DATE, TIMESTAMP, BOOLEAN a řetězec znaků. • Mimo to argumentem funkce COUNT smí být také znak *, v takovém případě funkce vrací počet všech hodnot, včetně těch, které se rovnají NULL. • Pokud se před argumentem funkce uvede DISTINCT, uvažují se pouze různé hodnoty argumentů (u funkcí MAX a MIN to nemá žádný význam). • Agregační funkce se v dotazech používají v klauzuli SELECT nebo HAVING. Nelze je používat v klauzulích WHERE nebo GROUP BY. V klauzuli ORDER BY je nutno je nahradit jménem výsledného sloupce, které lze specifikovat pomocí AS v klauzuli SELECT.

Seskupení v dotazu • Klauzule (příkaz) GROUP BY se používá zejména ve spojení s agregačními funkcemi pro seskupení dat do skupin. • Např. chceme seskupit počty (COUNT) knih každého autora do jednoho výsledku. • Chceme získat toto: Autor Pocet_Knih Šeďová 1 Zounek 2

Seskupení v dotazu SELECT a. prijmeni AS Autor, COUNT (ka. kniha_id) AS Pocet_Knih FROM autori AS a, kniha_autor AS ka WHERE a. autor_id = ka. autor_id GROUP BY a. autor_id ; // Atribut podle kterého se bude seskupovat: primární klíč výchozí tabulky

Seskupení v dotazu • Bez použití příkazu GROUP BY by předchozí dotaz dopadl následovně: Autor Knih Šeďová 3 Agregační funkce COUNT() bez použití klauzule GROUP BY sečte všechny řádky! • My. SQL našel příjmení Šeďová a Zounek, ale agregační funkce vypočítala jen celkovou hodnotu počtu záznamů odpovídající podmínce. Bez klauzule GROUP BY nelze agregační funkci COUNT použít pro seskupení k určité hodnotě (id autora).

Seskupení v dotazu Jinou možností by bylo zjištění, kolik má kniha autorů (srovnejte s předchozím dotazem): SELECT k. nazev AS Kniha, COUNT(ka. autor_id) AS Autori FROM knihy AS k, kniha_autor AS ka WHERE k. kniha_id = ka. kniha_id GROUP BY k. kniha_id ; Kniha Autori Učitelé a technologie 2 E-learning 1

GROUP_CONCAT() • Funkce GROUP_CONCAT() funguje podobně jako CONCAT(), ale umožňuje spojit hodnoty z různých záznamů do jednoho pole. • Příklad: • Vzpomeňme dříve uvedený výsledek dotazu spojení (snímek 8): Kniha Autor Učitelé a technologie Klára Šeďová Učitelé a technologie Jiří Zounek E-learning Jiří Zounek • Předložit toto uživateli aplikace by chtělo hodně odvahy . • Naštěstí máme k dispozici funkci GROUP_CONCAT(), která spojí jména autorů.

GROUP_CONCAT() SELECT k. nazev AS Kniha, GROUP_CONCAT(a. prijmeni SEPARATOR ', ' ) AS Autori // SEPARATOR určuje znak oddělení hodnot FROM knihy AS k, autori AS a, kniha_autor AS ka WHERE k. kniha_id = ka. kniha_id AND ka. autor_id = a. autor_id GROUP BY k. kniha_id; Kniha Autori Učitelé a technologie Šeďová, Zounek E-learning Zounek

Klauzule HAVING • Pomocí HAVING můžeme do dotazů vkládat podmínky s agregačními funkcemi, což za klíčovým výrazem WHERE nelze (jedině pomocí vnořených dotazů). Příklad: Chceme vypsat názvy knih, které mají více než jednoho autora: Řešení: Bude potřeba spojit data s tabulek knihy a kniha_autor. Z tabulky knihy potřebujeme znát název knihy a její identifikátor, pro podmínku spojení s tabulkou kniha_autor. V tabulce kniha_autor potřebujeme vypočítat počty autorů pro jednotlivé knihy a vložit je do podmínky klauzule HAVING.

Řešení • SELECT k. nazev FROM knihy AS k, kniha_autor AS ka WHERE k. kniha_d = ka. kniha_id GROUP BY k. kniha_id // Používáme agr. funkci COUNT, proto nesmíme vynechat klauzuli GROUP BY HAVING COUNT( ka. autor_id ) > 1;

Zapamatujte si • Čárkové spojení • Seskupení v dotazu GROUP BY • Podmínky použití GROUP BY • CONCAT, GROUP_CONCAT • Klauzule HAVING