SQL === SQL znamená *Structured Query Language*, tedy jazyk pro strukturované dotazy. Jazyk slouží pro dotazování se na data v [relačních databázích](#relační-databáze). Umožňuje základní operace nad daty: vytvoření, získání, změnu a odstranění dat. Také slouží k definici toho, jak data mají vypadat a umožňuje pokročilé programování. T-SQL ----- Jazyk SQL je standardizovaný, (má svoji normu). Reálné databázové systémy (MariaDB, PostgreSQL, Microsoft SQL, Oracle atd.) tuto normu z větší části dodržují, existují však drobné odchylky. Velká většina textu platí obecně, někde však použijeme speciality z jazyka pro Microsoft SQL s názvem T-SQL (Transact-SQL). Jeho dokumntace je na adrese . Zdroje k výuce ============== Odkazy na zdroje, které ve výuce používáme a/nebo obsahují informace k výuce. Vzorové databáze Microsoft -------------------------- Repozitář se vzorovými databázemi: V tomto repozitáři je i skript pro vytvoření vzorové databáze Pubs: Microsoft Visual Studio ----------------------- Vývojové prostředí Microsoft Visual Studio Community Edition je ke stažení zdarma na adrese . Při standardní instalaci je nainstalována i lokální databáze. V rámci vývojového IDE je dostupný i SQL server browser. Kurz jazyka SQL --------------- Na internetu v případě zájmu dohledáte mnoho kurzů SQL. Web w3schools nabízí kromě stručného tutoriálu i vzorovou databázi a online editor SQL příkazů. Odkaz: Microsoft Management Studio --------------------------- Pokročilý nástroj na správu MSSQL databází. Odkaz: Relační databáze ================ Relační databáze je jedním z typů databází. Databáze obecně je počítačový systém k ukládání a následnému získávání údajů, tedy dat. V relačních databázích jsou data ukládána do tabulek, které si můžeme představit jako formátované tabulky Excelu s několika dalšími vlastnostmi: - každá tabulka se skládá z definice sloupců (představíme si jako nadpis sloupce v Excelu) a samotných dat (řádky tabulky), - definice sloupců obsahuje nejen název sloupce, ale i datový typ sloupce a případné omezení pro data (např. ve sloupci *Narozen* bude uložena hodnota typu datum, ve sloupci *Mohsova tvrdost* bude celé kladné číslo menší nebo rovno 10). Teoretické pojmy: - *Relace* je tabulka v databázi, - *Atribut* je definice sloupce v tabulce, má určen *datový typ* (číslo, řetězec, datum, ...) a *doménu* (povolené hodnoty), - *Záznam* je řádek dat tabulky. *Příklad* *Za použití teoretických pojmů:* **Relace** *Studenti* má **atribut** *Příjmení* **datového typu** nvarchar (znaky) o maximální délce 100, tedy např. hodnota "Novák" patří do **domény** tohoto datového typu, hodnota 1.1.1980 nepatří do **domény** tohoto datového typu. *Přeloženo znamená*: V **tabulce** *Studenti* je **sloupec** *Příjmení*: může obsahovat nejvýše 100 znaků. "Novák" mohu zapsat, 1.1.1980 ne (to je datum) *Příklad* **Relace** *Materiály* obsahuje **atribut** *Tvrdost* **datového typu** celé číslo, jeho **doména** je množina {1, 2, 3, 4, 5, 6, 7, 8, 9, 10}. *Přeloženo znamená*: V **tabulce** *Materiály* je **sloupec** *Tvrdost*, povolené hodnoty jsou celá čísla od 1 do 10. Klíč ---- Klíč slouží jako jednoznačná identifikace záznamu v tabulce. To znamená, že když známe klíč, můžeme podle něj najít správný řádek v tabulce. Také to znamená, že v tabulce nejsou dva záznamy se stejným klíčem. *Příklad* V tabulce *Studenti* může být klíčem např. sloupec *Číslo ISIC*. Když znám číslo ISIC, mohu podle něj najít studenta. Klíč může být tvořen více sloupci v tabulce. *Příklad* V tabulce *Bankovní účty* může být klíčem dvojice sloupců: *Číslo účtu* a *kód banky*. *Příklad* V tabulce *Adresy* může být klíčem pětice sloupců *Stát*, *Město*, *Ulice*, *Číslo popisné*, *Číslo bytu*. V relačních databázích rozeznáváme několi druhů klíčů. ### Kandidátní klíč (Candidate key) Kandidátní klíč jsou jakékoli sloupce, které jednoznačně identifikují záznam. Jedna tabulka může obsahovat více kandidátních klíčů. *Příklad* Tabulka *Studenti* obsahuje kandidátní klíč *Rodné číslo*, dalším kandidátním klíčem je sloupec *Číslo ISIC*. (Studenta mohu najít podle rodného čísla, studenta také mohu najít podle čísla ISIC). ### Primární klíč (Primary key) Primární klíč je jeden z kandidátních klíčů, který si vybereme a budume nadále používat jako "vyvolený". *Příklad* Tabulka *Studenti* má primární klíč *Rodné číslo* nebo také: *Příklad* Tabulka *Studenti* má primární klíč *Číslo ISIC* Je na autorovi návrhu databáze, pro který z kandidátních klíčů se rozhodne. Měl by brát v úvahu případné budoucí komplikace (např. zahraniční student nemá rodné číslo nebo student ztratí kartu ISIC a je mu vystavena nová s jiným číslem apod.). *Poznámka* Velmi často se do tabulek uměle přidává sloupec s názvem *ID* (identifikátor) a datovým typen GUID (Globally Unique Identifier). V jazyce T-SQL mají sloupce obsahující GUID datový typ s názvem *uniqueidentifier*. Jedná se o číslo délky 16 bytů (většinou se zapisuje jako 32 hexadecimálních číslic). Databáze obsahují funci pro přidělení nového GUID takovým způsobem, aby bylo "jedinečné v celém vesmíru" - je téměř nemožné standardním postupem získat dva stejné GUID. V T-SQL nový GUID vrací funkce *newid()*. ### Cizí klíč (Foreign key) Cizí klíč je takový sloupec tabulky, který obsahuje hodnotu primárního klíče z jiné tabulky. *Příklad* V databázi centrální evidence automobilů jsou tabulky: - tabulka *Provozovatele* se sloupci *Jméno*, *Příjmení*, *Rodné číslo* , - tabulka *Vozidla* se sloupci *SPZ*, *Značka vozidla*, *Rodné číslo provozovatele*. Sloupec *Rodné číslo* v tabulke *Provozovatele* je primární klíč. Sloupec *Rodné číslo provozovatele* v tabulce *Vozidla* je cizí klíč do tabulky *Provozovatele*. SELECT (jedna tabulka) ====================== Příkaz SELECT umožňuje vybírat záznamy z více tabulek. V této části budeme pro jednoduchost popisovat výběr z jedné tabulky, později probereme i výběr z více tabulek. Struktura příkazu ----------------- ```sql SELECT FROM WHERE GROUP BY HAVING ORDER BY ``` | | | | --- | --- | | SELECT | jaké sloupce vybírám, jaké výpočty provádím | | FROM | z jaké tabulky (tabulek) vybírám | | WHERE | podmínka, kterou musí záznamy splňovat PŘED sdružením záznamů (GROUP BY) | | GROUP BY | podle jakých sloupečků sdružuji záznamy při výpočtech | | HAVING | podmínka, kterou musí záznamy splňovat PO sdružení záznamů (GROUP BY) | | ORDER BY | Jak seřadím výsledek | SELECT, FROM ------------ Jaké sloupečky (SELECT) ze které tabulky nebo tabulek (FROM) vybírám. *Příklad* ```sql SELECT kontakty.jmeno, kontakty.prijmeni FROM kontakty ``` Pokud je jméno sloupce jednoznačné, je možné použít zkrácený tvar bez jména tabulky: ```sql SELECT jmeno, prijmeni FROM kontakty ``` ### DISTINCT Vynechá z výsledné tabulky duplicitní řádky. *Příklad* Pokud tabulka kontakty obsahuje záznamy. | *Id* | *jmeno* | *prijmeni* | | --- | --- | --- | | 1 | Jan | Novák | | 2 | Petr | Novák | | 3 | Jan | Dvořák | *Příklad* Dotaz BEZ použití DISTINCT ```sql SELECT jmeno FROM kontakty ``` vrátí záznamy | | | --- | | Jan | | Petr | | Jan | Dotaz s použitím DISTINCT ```sql SELECT DISTINCT jmeno FROM kontakty ``` vrátí záznamy | | | --- | | Jan | | Petr | ### TOP Vrátí pouze tolik řádků, kolik je uvedeno. *Příklad* ```sql SELECT TOP 5 jmeno, prijmeni FROM kontakty ``` Téměř vždy se používá spolu s ORDER BY ([viz níže](#top-1)), jinak je výsledek nepředvídatelný. ### CASE Používá se pro výpočet nové hodnoty ve sloupci na základě podkladové hodnoty. *Příklad* ```sql SELECT misto, datum, cas, oblacnost, CASE WHEN oblacnost <= 1/8.0 THEN 'jasno' WHEN oblacnost <= 2/8.0 THEN 'skoro jasno' WHEN oblacnost <= 3/8.0 THEN 'malá oblačnost' WHEN oblacnost <= 4/8.0 THEN 'polojasno' WHEN oblacnost <= 5/8.0 THEN 'oblačno' WHEN oblacnost <= 6/8.0 THEN 'velká oblačnost' WHEN oblacnost <= 7/8.0 THEN 'skoro zataženo' ELSE 'zataženo'               END AS oblacnost_slovy, teplota, FROM predpoved ``` ### AS (ALIAS) Slouží k dočasnému přejmenování tabulky a/nebo přejmenování sloupce výsledku dotazu. Příklad (alias sloupce): ```sql SELECT jmeno1 AS krestni,  prijmeni FROM kontakty ``` Příklad (alias tabulky): ```sql SELECT kon_cr.jmeno, kon_cr.prijmeni FROM kontakty_na_zakazniky_z_ceske_republiky AS kon_cr ``` Příklad (alias sloupce i tabulky): ```sql SELECT kon_cr.jmeno AS krestni, kon_cr.prijmeni FROM kontakty_na_zakazniky_z_ceske_republiky AS kon_cr ``` Ve většině implementací SQL je možné klíčové slovo AS vypustit, předchozí příklad lze zapsat: ```sql SELECT kon_cr.jmeno krestni, kon_cr.prijmeni FROM kontakty_na_zakazniky_z_ceske_republiky kon_cr ``` WHERE ----- *Příklad* ```sql SELECT jmeno, prijmeni FROM kontakty WHERE prijmeni = 'Novák' ``` Podmínky se dají spojovat operátory AND, OR, NOT ```sql SELECT jmeno, prijmeni FROM kontakty WHERE prijmeni = 'Novák' AND jmeno = 'Jan' ``` ### Základní operátory Mohu použít operátory: | | | | --- | --- | | \= | rovná se | | <> | nerovná se, v MSSQL lze použít i != | | \>  | větší | | <  | menší | | \>= | větší nebo rovno | | <= | menší nebo rovno | Všimněte si: - řetězce uzavíráme do jednoduchých uvozovek, tedy 'Novák', nikoli "Novák" - rovnost se zapisuje jedním symbolem, nikoli dvěma, tedy prijmeni = 'Novák', nikoli prijmeni == 'Novák' Pozor na prioritu podmínek: NOT má přednost před AND, AND má přednost před OR. Pokud chci např. všechny učitele s příjmením Novák i studenty s příjmením Novák, tento dotaz je ŠPATNĚ (vybere všechny studenty s příjmením Novák a k nim i všechny učitele s libovolným příjmením): ```sql SELECT jmeno, prijmeni, role FROM kontakty WHERE prijmeni = 'Novák' AND role = 'student' OR role = 'učitel' ``` je totiž shodný s dotazem: ```sql SELECT jmeno, prijmeni, role FROM kontakty WHERE (prijmeni = 'Novák' AND role = 'student') OR (role = 'učitel') ``` správný dotaz by byl: ```sql SELECT jmeno, prijmeni, role FROM kontakty WHERE prijmeni = 'Novák' AND (role = 'student' OR role = 'učitel') ``` ### IS NULL, IS NOT NULL Hodnota null znamená „neznámá hodnota". Pokud je v podmínce WHERE použito porovnání s null hodnotou, je výsledek nedefinován (undefined) a podmínka není splněna. *Příklad* Uvažujme tabulku studenti: | *jmeno* | *prijmeni* | *datum_slozeni_zkousky* | | --- | --- | --- | | Jan | Novák | 2024-06-03 | | Jiří | Novotný | null | | František | Rovný | null | Oba následující dotazy NEVRÁTÍ ani jeden záznam. ```sql SELECT jmeno, prijmeni, FROM studenti WHERE datum_slozeni_zkousky = null ``` ```sql SELECT jmeno, prijmeni, FROM studenti WHERE datum_slozeni_zkousky <> null ``` Porovnání na null hodnotu se správně provádí **IS NULL** a **IS NOT NULL**. *Příklad* ```sql SELECT jmeno, prijmeni, FROM studenti WHERE datum_slozeni_zkousky IS NULL ``` ```sql SELECT jmeno, prijmeni, FROM studenti WHERE datum_slozeni_zkousky IS NOT NULL ``` ### BETWEEN Vybírá záznamy v daném rozmezí, včetně hraničních hodnot. *Příklad* ```sql SELECT nazev, cena FROM zbozi WHERE cena BETWEEN 100 AND 180 ``` Vybere všechno zboží s cenou od 100 včetně do 180 včetně. Následující dotaz vrátí stejné výsledky: ```sql SELECT nazev, cena FROM ZBOZI WHERE cena >= 100 AND cena <= 180 ``` ### IN Vybírá záznamy, které jsou shodné s uvedenými hodnotami. *Příklad* ```sql SELECT vyrobce, model, cena FROM mobilni_telefony WHERE vyrobce IN ('Apple', 'Google', 'Samsung') ``` Následující dotaz vrátí stejné výsledky jako předchozí: ```sql SELECT vyrobce, model, cena FROM mobilni_telefony WHERE vyrobce = 'Apple' OR vyrobce = 'Google' OR vyrobce = 'Samsung' ``` #### Použití vnořeného SELECT V podmínce IN může být jako seznam použit i vnořený SELECT, který vrací právě jeden sloupec s hodnotami, které se dají porovnat s hodnotami sloupce, podle kterého se řídí IN podmínka. *Příklad* ```sql SELECT nazev, cena FROM zbozi WHERE id_vyrobce IN (SELECT id FROM vyrobce WHERE stat = 'ČR') ``` ### LIKE Vybírá záznamy, které odpovídají masce (šabloně). Používá se pouze pro řetězce. V masce se dají použít zástupné znaky: | | | | --- | --- | | % (procento) | nahrazuje libovolný počet znaků (i nula znaků) | | _ (podtržítko) | nahrazuje právě jeden znak | *Příklad* Vybere obce s názvem 'Horní Lhota', 'Dolní Lhota', 'Lhota', ale NEVYBERE 'Lhota u Brodu'. ```sql SELECT nazev, kraj, pocet_obyvatel FROM obce WHERE nazev LIKE '%Lhota' ``` *Příklad* Vybere obce s názvem 'Horní Lhota', 'Dolní Lhota', 'Lhota' a VYBERE i 'Lhota u Brodu'. ```sql SELECT nazev, kraj, pocet_obyvatel FROM obce WHERE nazev LIKE '%Lhota%' ``` *Příklad* Vybere záznamy s kódem např. 'C135', 'CF35', ale nevybere 'CE45' ani 'C1358'. ```sql SELECT nazev, kod FROM vyrobky WHERE kod LIKE 'C_35' ``` Masky se dají kombinovat. *Příklad* Vybere záznamy s kódem např. 'AQ12345CC359876', 'AQC135', ale nevybere 'BAQC35', 'AQC1235'. ```sql SELECT nazev, kod FROM vyrobky WHERE kod LIKE 'AQ%C_35%' ``` ### ANY Splňuje podmínku, pokud je vybrán alespoň jeden záznam vnořeného SELECT. *Příklad* Vybere všechny makléře, kteří uzavřeli alespoň jednu objednávku za milion nebo více. ```sql SELECT nazev FROM makleri WHERE id = ANY (SELECT id_makler FROM objednavky WHERE cena_celkem >= 1000000) ``` ### ALL Splňuje podmínku, pokud všechny záznamy vnořeného SELECT splňují podmínku. *Příklad* Vybere všechny mobily, které jsou levnější než všechny mobily od Apple. ```sql SELECT vyrobce, model, cena FROM mobily WHERE cena < ALL (SELECT cena FROM mobily where vyrobce = 'Apple') ``` GROUP BY -------- Podle jakých sloupců se budou provádět výpočty. Výpočty se nazývají **agregační funkce**, protože seskupují (aggregate) záznamy. Pomůcka: GROUP BY je podle těch sloupců, podle kterých se NEPOČÍTÁ. *Příklad* ```sql SELECT id_ridic, sum(trestne_body) as soucet_bodu, sum(pokuta) as pokuta_celkem, count(\*) as pocet_pokut FROM prestupky GROUP BY id_ridic ``` ### Agregační funkce | | | | --- | --- | | MIN | minimum | | MAX | maximum | | AVG | průměr | | SUM | součet | | COUNT | počet | Příklad operace (výpočet průměrné známky, pokud známky nemají váhy): ```sql SELECT id_student, trida, AVG(znamka) as prumer FROM znamky GROUP BY id_student, trida ``` Ve výpočtech lze používat i matematické operace (výpočet průměrné známky, pokud známky mají váhy): ```sql SELECT id_student, trida, SUM(znamka \* vaha) / SUM(vaha) as prumer FROM znamky GROUP BY id_student, trida ``` HAVING ------ Podmínka, která se uplatní PO agregačních funkcích. Příklad (vyber jedničkáře): ```sql SELECT id_student, trida, sum(znamka \* vaha) / sum(vaha) as prumer FROM znamky GROUP BY id_student, trida HAVING sum(znamka \* vaha) / sum(vaha) < 1.5 ``` Místo výrazu pro výpočet lze použít název sloupce: ```sql SELECT id_student, trida, sum(znamka \* vaha) / sum(vaha) as prumer FROM znamky GROUP BY id_student, trida HAVING prumer < 1.5 ``` Podmínky WHERE a HAVING lze kombinovat (jedničkáři z 3.D): ```sql SELECT id_student, trida, sum(znamka \* vaha) / sum(vaha) as prumer FROM znamky WHERE id_trida = '3D' GROUP BY id_student, id_trida HAVING prumer < 1.5 ``` ORDER BY -------- Podle jakých sloupců bude výsledný dotaz seřazen. *Příklad* ```sql SELECT id_student, trida, avg(znamka) as prumer FROM znamky GROUP BY id_student, trida ORDER BY trida, prumer ``` Pro řazení od největšího k nejmenšímu se uvede klauzule DESC (seřaď po třídách od nejmenší k největší, v rámci třídy seřaď podle známky od nejhorší k nejlepší): ```sql SELECT id_student, trida, avg(znamka) as prumer FROM znamky GROUP BY id_student, trida ORDER BY trida, prumer DESC ``` ### TOP Patří k části SELECT ([viz výše](#top)). Téměř vždy se používá v kombinaci s ORDER BY *Příklad* ```sql SELECT TOP 5 vyrobce, model, maximalni_rychlost FROM auta ORDER BY maximalni_rychlost ``` UNION ----- Příkazem UNION je možné spojit výsledky dvou a více příkazů SELECT dohromady. *Příklad* ```sql SELECT datum, -castka as castka FROM faktury UNION SELECT datum, castka FROM platby ``` Použití příkazu má několik omezení: - jednotlivé příkazy SELECT musí vracet stejný počet sloupců, - odpovídající sloupce musí mít  odpovídající (kompatibilní) datový typ. Lze tedy pomocí UNION spojit dva SELECTy, pokud např.  prvý v prvém sloupci vrací datový typ numeric (číslo), a druhý v prvém sloupci vrací money (peníze). Pokud by vraceli např. date (datum) a numeric (číslo), spojení není možné a dotaz skončí chybou. Poznámky: - část ORDER BY je možné uvést pouze jednou **na konci** příkazu, v jednotlivých SELECT se ORDER BY nemůže uvést (řadíme až výsledek po sloučení), - UNION automaticky odstraní ty řádky, které jsou společné pro jednotlivé SELECTy, - duplicitní řádky se zachovají při použití UNION ALL. *Příklad* Dotaz ```sql SELECT 1 UNION SELECT 1 ``` vrátí záznam | | | --- | | 1 | Dotaz ```sql SELECT 1 UNION ALL SELECT 1 ``` vrátí záznamy | | | --- | | 1 | | 1 | SELECT z více tabulek ===================== Nyní popíšeme hlavně ty části příkazu SELECT, které se týkají výběru z více tabulek. Před samotným výkladem je potřeba pochopit pojem [klíč](#klíč) - hlavně primární klíč a cizí klíč. Databáze -------- V následujících ukázkách budeme pracovat se vzorovou databází, skripty pro její vytvoření jsou k dispozici v části [Vzorová databáze](#vzorová-databáze). ### Tabulka vyucujici obsahuje data: | id | jmeno | prijmeni | | --- | --- | --- | | 1 | Jan | Rychtařík | | 2 | Martin | Svázaný | | 3 | Tomáš | Plácal | | 4 | Milan | Chrastil | Sloupec *id* je primární klíč. ### Tabulka ucebny obsahuje data: | id |oznaceni | patro | id_vyucujici_zodpovedny | --- | --- | --- | --- | | 1 | ICT1 | 2 | 1 | | 2 | ICT2 | 2 | 1 | | 3 | ICT3 | 2 | 4 | | 4 | ICT4 | 2 | NULL | | 5 | ICT5 | 2 | 2 | Sloupec *id* je primární klíč. Sloupec *id_vyucujici_zodpovedny* je cizí klíč do tabulky *Vyucujici*. V prvém řádku je zachyceno, že zodpovědným vyučujícím za učebnu ICT1 je Jan Rychtařík (*id_vyucujici_zodpovedny* má hodnotu 1, v tabulce *Vyucujici* má primární klíč hodnotu 1 v řádku Jan Rychtařík). Ve třetím řádku je zachyceno, že zodpovědným vyučujícím za učebnu ICT3 je Milan Chrastil (*id_vyucujici_zodpovedny* má hodnotu 4, v tabulce *Vyucujici* má primární klíč hodnotu 4 v řádku Milan Chrastil). Ve čtvrtém řádku je zachyceno, že učebna ICT4 nemá přiděleného zodpovědného vyučujícího. Krátce řečeno: Za učebnyICT1 a ICT2 zodpovídá Jan Rychtařík, za učebnu ICT3 zodpovídá Milan Chrastil, učebna ICT4 nemá zodpovědného vyučujícího a za učebnu ICT5 zodpovídá Martin Svázaný. Tomáš Plácal nezodpovídá za žádnou učebnu. ### Tabulka vybaveni_uceben obsahuje data: | id | id_ucebny | nazev | evidencni_kod | | --- | --- | --- | --- | | 1 | 1 | 'projektor' | 'p42' | | 2 | 1 | 'tabule' | 't58' | | 3 | 2 | 'televizor' | 'tv15' | 4 | 2 | 'televizor' | 'tv17' | | 5 | 3 | 'projektor' | 'p12' | | 6 | 3 | 'tabule' | 't57' | | 7 | 4 | 'projektor' | 'p38' | | 8 | 4 | 'tabule' | 't51' | | 9 | 5 | 'projektor' | 'p89' | | 10 | 5 | 'tabule' | 't46' | Sloupec *id* je primární klíč. Sloupec *id_ucebny* je cizí klíč do tabulky *Ucebny*. V prvém řádku je zachyceno, že učebna ICT1 je vybavena projektorem s evidenčním kódem 'p42'. Ve čtvrtém řádku je zachyceno, že učebna ICT2 je vybavena televizorem s evidenčním kódem 'tv17'. ### Tabulka predmety obsahuje data: | id | nazev | zkratka | | --- | --- | --- | | 1 | Programování | PRG | | 2 | Vývoj aplikací | VAP | | 3 | Operační systémy | OPS | | 4 | Aplikační software | ASW | Sloupec *id* je primární klíč. ### Tabulka vyucujici_predmety obsahuje data: | id | id_vyucujici | id_predmety | | --- | --- | --- | | 1 | 1 | 4 | | 2 | 2 | 1 | | 3 | 2 | 2 | | 4 | 3 | 1 | | 5 | 3 | 2 | | 6 | 4 | 3 | Sloupec *id* je primární klíč. Sloupec *id_vyucujici* je cizí klíč do tabulky *Vyucujici*, slopec *id_predmety* je cizí klíč do tabulky *Predmety*. V tabulce *vyucujici_predmety* je zachyceno, že: - prvý záznam: vyučující číslo 1 (Jan Rychtařík) učí předmět číslo 4 (Aplikační software) - druhý záznam: vyučující číslo 2 (Martin Svázaný) učí předmět číslo 1 (Programování) - třetí záznam: vyučující číslo 2 (Martin Svázaný) učí předmět číslo 2 (Vývoj aplikací) - atd. ### Tabulka rozvrh obsahuje data: | id | id_vyucujici | id_predmety | id_ucebny | den | hodina | | --- | --- | --- | --- | --- | --- | | 1 | 1 | 4 | 5 | čt | 1 | | 2 | 2 | 2 | 5 | út | 3 | | 3 | 2 | 1 | 1 | út | 4 | | 4 | 2 | 2 | 5 | st | 2 | | 5 | 2 | 2 | 5 | st | 5 | | 6 | 2 | 2 | 5 | st | 7 | | 7 | 2 | 2 | 5 | st | 8 | | 8 | 2 | 2 | 5 | st | 9 | | 9 | 3 | 2 | 5 | út | 5 | | 10 | 3 | 2 | 5 | út | 6b | | 11 | 3 | 2 | 5 | st | 1 | | 12 | 3 | 1 | 3 | st | 2 | | 13 | 3 | 1 | 3 | st | 3 | | 14 | 3 | 1 | 2 | st | 4 | | 15 | 3 | 2 | 5 | st | 6a | | 16 | 4 | 3 | 3 | út | 4 | | 17 | 4 | 3 | 3 | út | 5 | | 18 | 4 | 3 | 3 | st | 4 | | 19 | 4 | 3 | 3 | st | 5 | Sloupec *id* je primární klíč. Sloupec *id_vyucujici* je cizí klíč do tabulky *Vyucujici*, slopec *id_predmety* je cizí klíč do tabulky *Predmety*, sloupec id_ucebny je cizí klíč do tabulky *Ucebny*. V tabulce **rozvrh** je zachyceno, že: - prvý záznam: vyučující číslo 1 (Jan Rychtařík) vyučuje předmět číslo 4 (Aplikační software) v učebně číslo 5 (ICT5) ve čtvrtek 1. hodinu. - druhý záznam: vyučující číslo 2 (Martin Svázaný) vyučuje předmět číslo 2 (Vývoj aplikací) v učebně číslo 5 (ICT5) v úterý 3. hodinu. - atd. SELECT, FROM ------------ V části FROM můžeme uvést libovolný počet tabulek. Výsledkem je kombinace všech řádků všech tabulek (kartézký součin). *Příklad* Výsledkem příkazu ```sql SELECT vyucujici.prijmeni, ucebny.oznaceni FROM vyucujici, ucebny ``` je tabulka s celkem 25 řádky (4 řádky tabulky *vyucujici* krát 5 řádků tabulky *ucebny*): | prijmeni | oznaceni | | --- | --- | | Rychtařík | ICT1 | | Rychtařík | ICT2 | | Rychtařík | ICT3 | | Rychtařík | ICT4 | | Rychtařík | ICT5 | | Svázaný | ICT1 | | Svázaný | ICT2 | | Svázaný | ICT3 | | Svázaný | ICT4 | | Svázaný | ICT5 | | Plácal | ICT1 | | Plácal | ICT2 | | Plácal | ICT3 | | Plácal | ICT4 | | Plácal | ICT5 | | Chrastil | ICT1 | | Chrastil | ICT2 | | Chrastil | ICT3 | | Chrastil | ICT4 | | Chrastil | ICT5 | Můžeme si představit, že v části FROM při uvedení více tabulek vznikne "obří" tabulka, která obsahuje všechny možné kombinace řádků všech tabulek. Tato obří tabulka je pak zpracována dalšími částmi příkazu tak, jak to již známe z části [SELECT - jedna tabulka](#select-jedna-tabulka): část WHERE vybere jen řádky "obří" tabulky splňující podmínku, část GROUP BY záznamy sdruží, část HAVING vybere sdružené záznamy splňující podmínky, část ORDER BY výsledek seřadí a část SELECT do výsledku vybere jen uvedené sloupce. Jinými slovy: > Výběr z více tabulek je stejný jako výběr z jedné "obří" tabulky vzniklé po spojení těchto tabulek. Pokud bychom chtěli předchozí dotaz upravit tak, abychom dostali informaci o tom, který vyučující je zodpovědný za jakou učebnu, doplníme část WHERE. *Příklad* ```sql SELECT vyucujici.prijmeni, ucebny.oznaceni FROM vyucujici, ucebny WHERE vyucujici.id = ucebny.id_vyucujici_zodpovedny ``` Výsledkem je tabulka | prijmeni | oznaceni | | --- | --- | | Rychtařík | ICT1 | | Rychtařík | ICT2 | | Chrastil | ICT3 | | Svázaný | ICT5 | Všimněte si: ve WHERE části dotazu je podmínka, kdy požadujeme, aby se primární klíč tabulky *vyucujici* rovnal cizímu klíči *id_vyucujici_zodpovedny* tabulky *ucebny* do tabulky *vyucujici*. To je jedna z hlavních myšlenek relačních databází. ### INNER JOIN INNER JOIN slouží ke spojení dvou tabulek stejným způsobem, jako bychom uvedli jména tabulek v části FROM a spojovací podmínku v části WHERE. *Příklad* ```sql SELECT vyucujici.prijmeni, ucebny.oznaceni FROM vyucujici INNER JOIN ucebny ON vyucujici.id = ucebny.id_vyucujici_zodpovedny ``` vrátí stejný výsledek jako ```sql SELECT vyucujici.prijmeni, ucebny.oznaceni FROM vyucujici, ucebny WHERE vyucujici.id = ucebny.id_vyucujici_zodpovedny ``` INNER JOIN je možné řetězit, to znamená použít víc příkazů INNER JOIN za sebou. *Příklad* ```sql SELECT vyucujici.prijmeni, ucebny.oznaceni, vybaveni_uceben.nazev, vybaveni_uceben.evidencni_kod FROM vyucujici INNER JOIN ucebny ON vyucujici.id = ucebny.id_vyucujici_zodpovedny INNER JOIN vybaveni_uceben ON vybaveni_uceben.id_ucebny = ucebny.id ``` Nejdříve se provede spojení tabulek *vyucujici* a *ucebny*. Můžeme si představit, že výsledkem je "velká" tabulka, která obsahuje všechny sloupce z tabulky *vyucujici* i tabulky *ucebny*. Tato tabulka se v dalším kroku spojí s tabulkou *vybaveni_uceben* a vznikne "ještě větší" tabulka, která obsahuje všechny slouce z tabulek *vyucujici*, *ucebny* a *vybaveni_uceben*. Nakonec se z této "ještě větší" tabulky vyberou sloupce *prijmeni*, *oznaceni*, *nazev* a *evidencni kod*. Výsledkem posledního dotazu tedy budou záznamy: | prijmeni | oznaceni | nazev | evidencni_kod | --- | --- | --- | --- | | 'Rychtařík' | 'ICT1' | 'projektor' | 'p42' | | 'Rychtařík' | 'ICT1' | 'tabule' | 't58' | | 'Rychtařík' | 'ICT2' | 'televizor' | 'tv15' | | 'Rychtařík' | 'ICT2' | 'televizor' | 'tv17' | | 'Chrastil' | 'ICT3' | 'projektor' | 'p12' | | 'Chrastil' | 'ICT3' | 'tabule' | 't57' | | 'Svázaný' | 'ICT5' | 'projektor' | 'p89' | | 'Svázaný' | 'ICT5' | 'tabule' | 't46' | Dotaz vrátí seznam vybavení za které jsou jednotliví učitelé zodpovědní včetně informace o tom, v jaké učebně se vybavení nachází. Předchozí dotaz odpovídá dotazu bez použití INNER JOIN: ```sql SELECT vyucujici.prijmeni, ucebny.oznaceni, vybaveni_uceben.nazev, vybaveni_uceben.evidencni_kod FROM vyucujici, ucebny, vybaveni_uceben WHERE vyucujici.id = ucebny.id_vyucujici_zodpovedny AND vybaveni_uceben.id_ucebny = ucebny.id ``` Proč tedy vůbec používat INNER JOIN? Je to hlavně kvůli čitelnosti dotazů. Při použití INNER JOIN je lépe vidět, jak se postupně tabulky spojují. Lepší čitelnost vynikne při použití další podmínky WHERE. *Příklad* ```sql SELECT vyucujici.prijmeni, ucebny.oznaceni, vybaveni_uceben.nazev, vybaveni_uceben.evidencni_kod FROM vyucujici INNER JOIN ucebny ON vyucujici.id = ucebny.id_vyucujici_zodpovedny INNER JOIN vybaveni_uceben ON vybaveni_uceben.id = ucebny.id WHERE vyucujici.prijmeni = N'Rychtařík' ``` Všimněte si, že podmínky, na základě kterých se tabulky spojují, jsou v části INNER JOIN, podmínka na další omezení záznamů je v části WHERE. *Poznámka:* V části WHERE je při porovnání uveden řetězen N'Rychtařík'. Tento zápis znamená, že řetězev je typu NVARCHAR, tedy v kódování Unicode. Pokud by byl řetězec uveden jako 'Rychtařík', jednalo by se o datový typ VARCHAR, tedy ANSI kódování a při konverzi by mohlo podle nastavení databáze dojít ke ztrátě znaků. Bez použití INNER JOIN by dotaz vypadal: ```sql SELECT vyucujici.prijmeni, ucebny.oznaceni, vybaveni_uceben.nazev, vybaveni_uceben.evidencni_kod FROM vyucujici, ucebny, vybaveni_uceben WHERE vyucujici.id = ucebny.id_vyucujici_zodpovedny AND vybaveni_uceben.id = ucebny.id AND vyucujici.prijmeni = N'Rychtařík' ``` Zde se již přehlednost ztrácí. ### LEFT JOIN V některých případech je potřeba do JOIN zahrnout i ty záznamy, které by jinak "vypadly", protože neexistují odpovídající záznamy v obou tabulkách. Příklad: ```sql SELECT vyucujici.prijmeni, ucebny.oznaceni FROM vyucujici, ucebny WHERE vyucujici.id = ucebny.id_vyucujici_zodpovedny ``` Výsledkem je tabulka všech vyučujících a učeben, za které jsou zodpovědní. Pokud však některý z vyučujících nezodpovídá za žádnou učebnu, není ve výsledném dotazu uveden. V tomto případě chybí ve výsledku vyučující Plácal. | prijmeni | oznaceni | | --- | --- | | Rychtařík | ICT1 | | Rychtařík | ICT2 | | Chrastil | ICT3 | | Svázaný | ICT5 | Řešením je použití LEFT JOIN, které spojuje tabulky stejným způsobem jako INNER JOIN a navíc přidá ze záznamů z tabulky stojící vlevo od LEFT JOIN ty, které nemají žádný odpovídající záznam v tabulce stojící vpravo od LEFT JOIN. Pokud takové záznamy z tabulky vlevo existují a ve výsledném dotazu mají být uvedeny i sloupce z tabulky vpravo, jejich hodnota je NULL. Příklad: ```sql SELECT vyucujici.prijmeni, ucebny.oznaceni FROM vyucujici LEFT JOIN ucebny ON vyucujici.id = ucebny.id_vyucujici_zodpovedny ``` vrátí výsledek | prijmeni | oznaceni | | --- | --- | | Rychtařík | ICT1 | | Rychtařík | ICT2 | | Svázaný | ICT5 | | Plácal | NULL | | Chrastil | ICT3 | Vyučující Plácal je zde uveden s tím, že hodnota sloupce *oznaceni* je NULL. ### RIGHT JOIN Jak již název napovídá, RIGHT JOIN je obdobný [LEFT JOIN](#left-join) jen s tím rozdílem, že do výsledného výběru jsou zahrnuty i všechny řádky tabulky vpravo od RIGHT JOIN, které nemají žádné odpovídající záznamy v tabulce vlevo od RIGHT JOIN. *Příklad* ```sql SELECT vyucujici.prijmeni, ucebny.oznaceni FROM vyucujici RIGHT JOIN ucebny ON vyucujici.id = ucebny.id_vyucujici_zodpovedny ``` vrátí výsledek | prijmeni | oznaceni | | --- | --- | | Rychtařík | ICT1 | | Rychtařík | ICT2 | | Chrastil | ICT3 | | NULL | ICT4 | | Svázaný | ICT5 | Učebna ICT4 je zde uvedena s tím, že hodnota *prijmeni* je NULL. ### FULL JOIN FULL JOIN spojí obě tabulky tak, že ve výsledku jsou uvedeny i ty záznamy z obou tabulek, které nemají odpovídající záznamy v druhé z tabulek. *Příklad* ```sql SELECT vyucujici.prijmeni, ucebny.oznaceni FROM vyucujici FULL JOIN ucebny ON vyucujici.id = ucebny.id_vyucujici_zodpovedny ``` vrátí výsledek | prijmeni | oznaceni | | --- | --- | | Rychtařík | ICT1 | | Rychtařík | ICT2 | | Svázaný | ICT5 | | Plácal | NULL | | Chrastil | ICT3 | | NULL | ICT4 | Ve výsledném dotazu můžeme vidět jak informaci o tom, že vyučující Plácal nezodpovídá za žádnou učebnu, tak informaci o tom, že učebna ICT4 nemá přiděleného zodpovědného vyučujícího. Vzorová databáze ================ Skripty pro vzorovou databázi uvádíme ve dvou verzích: - pro [MSSQL server](#skripty-pro-mssql) s Unicode znaky, - podle [SQL normy](#skripty-podle-sql-normy-pro-většinu-systémů), v MSSQL server se použije ANSI kódování znaků. Skripty pro MSSQL ----------------- ```sql SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; -- Tabulka vyucujici CREATE TABLE vyucujici( id int NOT NULL primary key, jmeno nvarchar(100) NOT NULL, prijmeni nvarchar(100) NOT NULL); INSERT vyucujici (id, jmeno, prijmeni) VALUES (1, N'Jan', N'Rychtařík'), (2, N'Martin', N'Svázaný'), (3, N'Tomáš ', N'Plácal'), (4, N'Milan', N'Chrastil'); -- Tabulka ucebny CREATE TABLE ucebny( id int NOT NULL primary key, oznaceni nvarchar(5) NOT NULL, patro numeric(18, 0) NOT NULL, id_vyucujici_zodpovedny int NULL); INSERT ucebny (id, oznaceni, patro, id_vyucujici_zodpovedny) VALUES (1, N'ICT1', 2, 1), (2, N'ICT2', 2, 1), (3, N'ICT3', 2, 4), (4, N'ICT4', 2, NULL), (5, N'ICT5', 2, 2); -- Tabulka vybaveni_uceben CREATE TABLE vybaveni_uceben( id int NOT NULL primary key, id_ucebny int NOT NULL, nazev nvarchar(100) NOT NULL, evidencni_kod nvarchar(10) NOT NULL); -- Tabulka vybaveni_uceben INSERT vybaveni_uceben (id, id_ucebny , nazev, evidencni_kod ) VALUES (1, 1, N'projektor', N'p42'), (2, 1, N'tabule', N't58'), (3, 2, N'televizor', N'tv15'), (4, 2, N'televizor', N'tv17'), (5, 3, N'projektor', N'p12'), (6, 3, N'tabule', N't57'), (7, 4, N'projektor', N'p38'), (8, 4, N'tabule', N't51'), (9, 5, N'projektor', N'p89'), (10, 5, N'tabule', N't46'); -- Tabulka predmety CREATE TABLE predmety( id int NOT NULL primary key, nazev nvarchar(100) NOT NULL, zkratka nchar(3) NOT NULL); INSERT predmety (id, nazev, zkratka) VALUES (1, N'Programování', N'PRG'), (2, N'Vývoj aplikací', N'VAP'), (3, N'Operační systémy', N'OPS'), (4, N'Aplikační software', N'ASW'); -- Tabulka vyucujici_predmety CREATE TABLE vyucujici_predmety( id int NOT NULL primary key, id_predmety int NOT NULL, id_vyucujici int NOT NULL); INSERT vyucujici_predmety (id, id_predmety, id_vyucujici) VALUES (1, 1, 4), (2, 2, 1), (3, 2, 2), (4, 3, 1), (5, 3, 2), (6, 4, 3); -- Tabulka rozvrh CREATE TABLE rozvrh( id int NOT NULL primary key, id_vyucujici int NOT NULL, id_predmety int NOT NULL, id_ucebny int NOT NULL, den nchar(2) NULL, hodina nvarchar(2) NULL); INSERT rozvrh (id, id_vyucujici, id_predmety, id_ucebny, den, hodina) VALUES (1, 1, 4, 5, N'čt', N'1'), (2, 2, 2, 5, N'út', N'3'), (3, 2, 2, 5, N'út', N'4'), (4, 2, 2, 5, N'st', N'2'), (5, 2, 2, 5, N'st', N'5'), (6, 2, 2, 5, N'st', N'7'), (7, 2, 2, 5, N'st', N'8'), (8, 2, 2, 5, N'st', N'9'), (9, 3, 2, 5, N'út', N'5'), (10, 3, 2, 5, N'út', N'6b'), (11, 3, 2, 5, N'st', N'1'), (12, 3, 1, 3, N'st', N'2'), (13, 3, 1, 3, N'st', N'3'), (14, 3, 1, 2, N'st', N'4'), (15, 3, 2, 5, N'st', N'6a'), (16, 4, 3, 3, N'út', N'4'), (17, 4, 3, 3, N'út', N'5'), (18, 4, 3, 3, N'st', N'4'), (19, 4, 3, 3, N'st', N'5'); ``` Skripty podle SQL normy pro většinu systémů ------------------------------------------- ```sql -- Tabulka vyucujici CREATE TABLE vyucujici( id int NOT NULL primary key, jmeno varchar(100) NOT NULL, prijmeni varchar(100) NOT NULL); INSERT INTO vyucujici (id, jmeno, prijmeni) VALUES (1, 'Jan', 'Rychtařík'), (2, 'Martin', 'Svázaný'), (3, 'Tomáš ', 'Plácal'), (4, 'Milan', 'Chrastil'); -- Tabulka ucebny CREATE TABLE ucebny( id int NOT NULL primary key, oznaceni varchar(5) NOT NULL, patro numeric(18, 0) NOT NULL, id_vyucujici_zodpovedny int NULL); INSERT INTO ucebny (id, oznaceni, patro, id_vyucujici_zodpovedny) VALUES (1, 'ICT1', 2, 1), (2, 'ICT2', 2, 1), (3, 'ICT3', 2, 4), (4, 'ICT4', 2, NULL), (5, 'ICT5', 2, 2); -- Tabulka vybaveni_uceben CREATE TABLE vybaveni_uceben( id int NOT NULL primary key, id_ucebny int NOT NULL, nazev varchar(100) NOT NULL, evidencni_kod varchar(10) NOT NULL); -- Tabulka vybaveni_uceben INSERT INTO vybaveni_uceben (id, id_ucebny , nazev, evidencni_kod ) VALUES (1, 1, 'projektor', 'p42'), (2, 1, 'tabule', 't58'), (3, 2, 'televizor', 'tv15'), (4, 2, 'televizor', 'tv17'), (5, 3, 'projektor', 'p12'), (6, 3, 'tabule', 't57'), (7, 4, 'projektor', 'p38'), (8, 4, 'tabule', 't51'), (9, 5, 'projektor', 'p89'), (10, 5, 'tabule', 't46'); -- Tabulka predmety CREATE TABLE predmety( id int NOT NULL primary key, nazev varchar(100) NOT NULL, zkratka nchar(3) NOT NULL); INSERT INTO predmety (id, nazev, zkratka) VALUES (1, 'Programování', 'PRG'), (2, 'Vývoj aplikací', 'VAP'), (3, 'Operační systémy', 'OPS'), (4, 'Aplikační software', 'ASW'); -- Tabulka vyucujici_predmety CREATE TABLE vyucujici_predmety( id int NOT NULL primary key, id_predmety int NOT NULL, id_vyucujici int NOT NULL); INSERT INTO vyucujici_predmety (id, id_predmety, id_vyucujici) VALUES (1, 1, 4), (2, 2, 1), (3, 2, 2), (4, 3, 1), (5, 3, 2), (6, 4, 3); -- Tabulka rozvrh CREATE TABLE rozvrh( id int NOT NULL primary key, id_vyucujici int NOT NULL, id_predmety int NOT NULL, id_ucebny int NOT NULL, den nchar(2) NULL, hodina varchar(2) NULL); INSERT INTO rozvrh (id, id_vyucujici, id_predmety, id_ucebny, den, hodina) VALUES (1, 1, 4, 5, 'čt', '1'), (2, 2, 2, 5, 'út', '3'), (3, 2, 2, 5, 'út', '4'), (4, 2, 2, 5, 'st', '2'), (5, 2, 2, 5, 'st', '5'), (6, 2, 2, 5, 'st', '7'), (7, 2, 2, 5, 'st', '8'), (8, 2, 2, 5, 'st', '9'), (9, 3, 2, 5, 'út', '5'), (10, 3, 2, 5, 'út', '6b'), (11, 3, 2, 5, 'st', '1'), (12, 3, 1, 3, 'st', '2'), (13, 3, 1, 3, 'st', '3'), (14, 3, 1, 2, 'st', '4'), (15, 3, 2, 5, 'st', '6a'), (16, 4, 3, 3, 'út', '4'), (17, 4, 3, 3, 'út', '5'), (18, 4, 3, 3, 'st', '4'), (19, 4, 3, 3, 'st', '5'); ```