Přednášky

, pak je potřeba název sloupce uvést ve tvaru jméno_tabulky.jméno_sloupce
jména sloupců se oddělují čárkou Projekce pomocí příkazu SELECT - příklady první SELECT zobrazí obsah celé tabulky KNIHA (jiné sloupce tabulka neobsahuje)
znak * nahrazuje názvy všech sloupců v tabulce a to ve stejném pořadí, jak jsou sloupce v tabulce vytvořeny Restrikce pomocí příkazu SELECT restrikce je výběr řádků (záznamů) tabulky zadáním nějaké podmínky za klíčové slovo WHERE příkazu SELECT
podmínka = 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 Základní operátory klíčové slovo IS NULL se používá pro zapsání podmínky, že nějaká položka (sloupec) má nedefinovanou hodnotu (uživatel ji nezadal) a IS NOT NULL slouží pro opačný případ, kdy se ptáme, zdali položka má definovanou hodnotu
každý databázový systém má svá různá rozšíření Operátor LIKE operátor LIKE slouží pro porovnávání řetězců:
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),
LIKE '_b%k' je kombinací dvou předchozích a ří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' Restrikce – příklad I Názvy všech nakladatelství, která jsou z Brna: Restrikce – příklad II Název a e-mail všech nakladatelství, která mají svou webovskou stránku: Restrikce – příklad III Příjmení všech autorů, jejichž křestní jména začínají na písmeno 'T', a kteří nemají žádný titul: Řazení výstupu příkazu SELECT pro třídění výstupu slouží klíčové slovo ORDER BY příkazu SELECT
sloupce, podle kterých chceme třídit, se uvádějí jako seznam za ORDER BY a oddělují se od sebe čárkou
nejčastěji jsou hodnoty tříděny vzestupně, od nejnižší po nejvyšší pro sestupné třídění (inverzní) se použije třídění v následujícím tvaru:
klíčové slovo ASC (vzestupné třídění) je nepovinné Řazení výstupu příkazu SELECT - 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): Spojování tabulek přirozené spojení = sloučení dvou a více tabulek "dohromady", tj. každý řádek v jedné tabulce je "rozšířen" o sloupce z tabulek dalších
odpovídající řádky získá systém díky vazbě primární klíč - cizí klíč
přirozené spojení v rámci příkazu SELECT zapisujeme v části WHERE, a to zápisem ve tvaru (za klíč dosadíme primární a cizí): Spojování více tabulek 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
všechny tabulky, které se přirozeného spojení účastní, se musí uvést za klíčovým slovem FROM
v části WHERE, kde se odkazujeme na názvy sloupců, může dojít ke kolizi názvů  názvu sloupce musí předcházet jméno tabulky a znak tečka Spojování tabulek - příklad I Mějme tabulky KNIHY a AUTOŘI a tabulku NAPSANÉ_KNIHY s následujícími daty: Spojování tabulek - příklad I Celková tabulka pro přirozené spojení tří tabulek vypadá následovně (název knih je zkrácen z důvodů místa): Spojování tabulek - příklad II Názvy všech knih, jejichž výtisky byly vydány v roce 1998: Spojování tabulek - příklad III Kteří autoři napsali knihu "Jak publikovat články"? Spojování tabulek - příklad IV Názvy všech knih, které napsal Alois Jirásek: Spojování tabulek - příklad V Názvy všech děl, která vydalo nakladatelství 'Iota': Spojování tabulek - příklad VI Ve kterých nakladatelstvích byla v roce 1996 vydána díla autora Robina Cooka? 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, před názvy projektovaných sloupců Modifikátor DISTINCT - příklad název nakladatelství, které vydalo v roce 1996 nějaké dílo od Robina Cooka Modifikátor TOP modifikátor TOP zajistí vrácení jenom prvních N řádků
číslo N se zadává přímo v dotazu za klíčovým slovem TOP Modifikátor ALL je implicitní a znamená vrácení všech vyhovujících záznamů  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 umožňuje měnit názvy sloupců, podle naší potřeby pomocí klíčového slova AS, které se uvádí za jméno sloupce, a za ním následuje nový název: Modifikace záhlaví sloupců - příklad 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ě: Formát výstupních sloupců jazyk SQL umožňuje formátovat výstupní položky do tvaru, který potřebujeme v naší konkrétní aplikaci, např.:
připsaní řetězce 'Kč' za každou částku v korunách,
provádění aritmetických operací s položkami,
slučování řetězcových položek do jedné, apod. Formát výstupních sloupců – příklad I V pomocné tabulce TEMP máme dva sloupce A a B, které představují hodnoty proměnných A a B
následující příkaz SELECT vrátí řádky ve tvaru A + B = součet: Formát výstupních sloupců – příklad II Výpis jmen všech autorů knihy 'O pejskovi‚
Jména se vypíší jako jediný sloupec pojmenovaný CELÉ JMÉNO, který bude složen z příjmení a jména odděleného čárkou: Dotazy v SQL s agregací agregace = seskupení několika řádků, přičemž pro tuto "skupinu" řádků můžeme pomocí agregačních funkcí počítat různé matematické operace
seskupení se provádí podle hodnot určitých sloupců – lze seskupovat podle hodnot jednoho sloupce, podle dvou, obecně podle kolika chceme
seskupení spočívá v tom, že když se objeví dva řádky, jejichž hodnoty se ve vybraných sloupcích (v těch, podle kterých seskupujeme) shodují, databázový systém na ně bude pohlížet jako na jeden řádek Dotazy s agregací - příklad Pokud provedeme seskupení podle sloupce KNIHA_ID, výsledek bude mít pouze tři řádky, na zbývající sloupce je pak nutné aplikovat agregační funkce, např. počet hodnot Dotazy v SQL s agregací v příkazu SELECT za klíčová slova GROUP BY uvádíme sloupce, podle kterých chceme jednotlivé záznamy seskupit:
GROUP BY se píše před ORDER BY
kritérium pro případné třídění výstupu se vždy udává až na konec příkazu SELECT Dotazy v SQL s agregací nejjednodušší použití je napsání příkazu bez agregační funkce: Dotazy v SQL s agregací nedefinované hodnoty sloupců se do výsledků agregačních funkcí nezapočítávají (výjimkou je funkce COUNT(*), která počítá všechny položky bez ohledu na jejich obsah)
v argumentu agregační funkce nemusí být vždy uveden jen jeden sloupec  lze zapsat matematický výraz, např: Agregační funkce COUNT
SUM
AVG
MIN
MAX Agregační funkce COUNT(*) slouží k získání počtu záznamů v rámci jedné skupiny agregovaných řádků
nejjednodušší příklad použití agregační funkce COUNT, který vrátí počet záznamů v tabulce, je následující:
COUNT lze použít pro zjištění počtu záznamů v rámci jednotlivých skupin v rámci příkazu SELECT se musí do GROUP BY uvést jména sloupců, podle kterých se bude seskupovat Funkce COUNT(*) – příklad I Vypište ve formě jméno knihy, následované počtem autorů, kolik autorů má každá kniha (do výpisu se nezahrnou názvy knih, které nemají žádného autora): Funkce COUNT(*) – příklad II Vypište seznam měst, ve kterých sídlí nakladatelství, u každého města uveďte, kolik z nakladatelství má kontaktní e-mail nebo webovskou stránku: Funkce SUM(sloupec) vrátí součet hodnot v uvedeném sloupci v rámci shluknuté skupiny záznamů
Příklad - seznam všech názvů knih a nakladatelství se součtem cen výtisků daného díla v daném nakladatelství: Funkce AVG(sloupec) počítá aritmetický průměr z vybraných hodnot zadaného sloupce
Příklad - pro všechny knihy, které byly vydány v brněnských nakladatelstvích zjistěte průměrnou hmotnost: Funkce MIN(sloupec) vrací minimum z hodnot ve sloupci ze seskupených záznamů
Příklad - seznam všech nakladatelství + rok, ve kterém byla vydána nejstarší kniha: Funkce MAX(sloupec) vrací maximum z hodnot zadaného sloupce
Příklad - seznam nakladatelství, a pro každé z nich uvést, kolik stojí jejich nejdražší výtisk: Další použití agregačních funkcí - HAVING v části HAVING příkazu SELECT lze nastavit další restrikci po provedení agregace
v části HAVING typicky figurují agregační funkce HAVING - příklad chceme seznam všech knih a nakladatelství a pro každou dvojici kniha - nakladatel součet cen výtisků daného díla v daném nakladatelství
do výpisu zahrneme ale jen ta nakladatelství, která vydala alespoň 3 různá vydání dané knihy Třídění dle výsledků agregační funkce chceme-li výpis setřídit podle hodnoty agregační funkce, za klíčové slovo ORDER BY uvedeme agregační funkci
Příklad - seznam všech knih s jejich průměrnou cenou, seřazený právě podle této ceny: Třídění dle výsledků agregační funkce některé databázové systémy mají omezení, které neumožňuje zadat přímo agregační funkci za ORDER BY  použijeme modifikátor AS a dáme sloupci agregované funkce nějaký alias a ten uvedeme za ORDER BY Skládání SQL dotazů z matematického pohledu můžeme na výstupní řádky, které vyhovují našemu SQL dotazu, pohlížet jako na množinu  s množinami můžeme provádět základní operace:
sjednocení,
průnik,
rozdíl Sjednocení dotazů pro sjednocení výsledků dotazů slouží klíčové slovo UNION, za kterým následuje další příkaz SELECT
Příklad: Chceme seznam všech děl, které napsal Alois Jirásek, nebo Vítězslav Nezval (2 varianty: s/bez použití sjednocení - oba zápisy vrátí stejnou množinu záznamů) Příklad – bez využití sjednocení Příklad – s využitím sjednocení Průnik dotazů pro průnik dotazů se používá klíčové slovo INTERSECT
Příklad: Chceme názvy všech knih, které napsal Jan Novák a zároveň Petr Nový:
bez použití INTERSECT - dotaz je chybný, poněvadž se podmínky restrikce vždy vyhodnocují v rámci jednoho řádku
s použitím průniku (správné řešení) Průnik dotazů - příklad  Průnik dotazů - příklad Rozdíl dotazů rozdíl dotazů získáme pomocí klíčového slova MINUS
Chtějme seznam všech knih, které napsal Jan Novák, ale zároveň které nenapsal Petr Nový:
bez použití rozdílu (vrátí všechny názvy knih, které napsal Jan Novák, ale nevezme už vůbec v úvahu, že je nesměl napsat Petr Nový)
s použitím rozdílu (správné řešení) Rozdíl dotazů - příklad  Rozdíl dotazů - příklad Doplněk zvláštní užití rozdílu dotazů – v prvním SELECTu uvedeme celou možnou množinu odpovědí a druhým SELECTem pak z výsledku vypustíme ty řádky, které nechceme
Příklad - seznam knih, na kterých se nepodíleli Jan Novák a Petr Nový jako spoluautoři Doplněk - příklad Zásady pro skládání dotazů I počet projektovaných sloupců v jednotlivých SELECTech musí být stejný, a jednotlivé sloupce musí být stejného datového typu (ne tedy nutně přesně stejné názvy sloupců)
pokud chceme třídit výstup, klíčové slovo ORDER BY použijeme až úplně na konci zápisu složeného příkazu  nesmíme třídit jednotlivé SELECTy Zásady pro skládání dotazů II množinové operace UNION, INTERSECT a MINUS se vyhodnocují zezadu, tj. jde se odspodu, jako první se vyhodnotí poslední množinový operátor, jako poslední se vyhodnotí první uvedený
v případě nejednoznačnosti lze v některých systémech dílčí SELECTy uzávorkovat pomocí kulatých závorek Skládání dotazů - příklad Vnořené dotazy v SQL vnořený dotaz je příkaz SELECT vnořený do jiného příkazu SELECT
vnořené dotazy se používají tam, kde potřebujeme nejprve zjistit nějakou informaci a v závislosti na ní zjistit pak informace další
Jednoduché vnořené dotazy (poddotazy)
Poddotazy vracející více hodnot Jednoduché vnořené dotazy (poddotazy) vnořený příkaz SELECT vracející jednu hodnotu
píše se do závorek a je uveden v části WHERE
vnořený SELECT se vyhodnotí pouze jednou, hodnotu si SQL server "zapamatuje" a s ní pak porovnává všechny ostatní hodnoty v tabulce
seznam všech zaměstnanců, kteří mají nadprůměrný plat: Poddotazy – příklad I Zjistěte název a cenu nejlevnější knihy. Poddotazy – příklad II Vypište jména všech autorů, kteří se podíleli na psaní nejdelší knihy (=s největším počtem stran)
Poddotazy vracející více hodnot vnořování dotazů rozšiřuje klasickou množinu relačních operátorů o operátory další, které se aplikují z levé strany na sloupec a z pravé strany na vnořený dotaz vracející více hodnot
operátor IN slouží pro jednoduché porovnání, zdali se hodnota sloupce vlevo vyskytuje mezi hodnotami vrácených vnořeným dotazem Poddotazy vracející více hodnot – příklad I Názvy knih vydané v letech 1997 až 2000 pomocí operátoru IN: Poddotazy vracející více hodnot výraz v závorce za IN je množina všech hodnot, ve kterých se má daná hodnota hledat, přičemž většinou se neuvádí výčet explicitně, ale vrací jej nějaký vnořený dotaz
v případě dlouhých názvů některých tabulek je výhodou je v rámci dotazu nahradit kratšími názvy
aliasy tabulek se narozdíl od aliasů sloupců, neuvozují modifikátorem AS Poddotazy vracející více hodnot – příklad II Dotaz na názvy knih vydaných v týchž letech, jako díla Aloise Jiráska (jsou použity aliasy pro názvy tabulek): Poddotazy vracející více hodnot operátor ANY nebo SOME určují, že relace se vztahuje na alespoň jednu z hodnot, kterou vrátí vnořený dotaz, operátor ALL aplikuje relaci na všechny hodnoty vrácené poddotazem
operátory ANY(SOME) a ALL mají následující syntaxi: Poddotazy vracející více hodnot – příklad III, varianta a Seznam knih od Vítězslava Nezvala, jejichž výtisky nebyly nikdy dražší, než kterýkoliv výtisk díla Aloise Jiráska: Poddotazy vracející více hodnot – příklad III, varianta b dotaz uvedený jako varianta a můžeme přepsat použitím operátoru ANY v kombinaci s operátorem NOT (rozdíl bude jen v řádku, kde uvádíme sloupec CENA): Vnější spojení standard SQL definuje tzv. vnější spojení, které nám umožní do souhrnné tabulky dostat i ty řádky, které se při použití vnitřního spojení do výsledné souhrnné tabulky nedostanou (nemají odpovídající partnery v jiných tabulkách – viz přímé spojení) Vnější spojení chceme-li použít vnějšího spojení, musíme příkaz SELECT zapsat v odlišné syntaxi: Vnější spojení jako první a druhou tabulku uvedeme názvy tabulek, které chceme spojit
použitím LEFT nebo RIGHT JOIN řekneme, že v pravé nebo v levé tabulce dovolíme výskyt prázdných hodnot
za klíčové slovo ON dáme podmínku pro spojení, která má stejný tvar, jako v případě klasického vnitřního spojení Vnější spojení – příklad I Chceme seznam všech pozdravů (nejen vyslovených) a u každého z nich uvedeno, kolikrát byl vysloven:
Spojujeme zleva - v tabulce POZDRAV chceme do výstupu všechny její záznamy, i když nemají odpovídající "partnery" ve druhé tabulce Vnější spojení – odlišnosti DS některé databázové systémy se v případě vnějšího spojení liší od standardu:
MySQL se tohoto standardu drží, ale nelze použít RIGHT JOIN  řešení = prohození pořadí první a druhé tabulky a zápisem LEFT JOIN
systém Oracle povoluje obě možnosti (levou i pravou), jen u názvu sloupce v restrikci zapíšeme vlevo nebo vpravo (tj. v závislosti, zda-li chceme dělat LEFT nebo RIGHT JOIN) speciální operátor (+) Vnější spojení – příklad II (Oracle) Seznam všech pozdravů (nejen vyslovených) a u každého z nich uvést, kolikrát byl vysloven:
Ve sloupci, ve kterém chceme povolit existenci nulového počtu odpovídajících partnerů v jiné tabulce, zapíšeme operátor (+). Pohledy pohledy = virtuální (logické) tabulky v databázi, které zpřehledňují práci s daty a sestavování dotazů
pohledy mají své velké uplatnění z hlediska ochrany dat, např. můžeme vytvořit pohled, který bude mít stejný obsah jako tabulka záznamů o lidech ve firmě, s tím, že nebude obsahovat sloupec RODNÉ_ČÍSLO  jako správci systému nemusíme nastavovat právo na čtení tabulky LIDÉ, ale dáme právo na čtení takového pohledu a máme zajištěno, že si rodná čísla nepřečte žádná neoprávněná osoba Pohledy pohledem lze spojit více tabulek
nad pohledy lze vykonávat standardní operace INSERT, DELETE, UPDATE, SELECT
z hlediska práce s tabulkami se pohled (často nazývaný view) jeví jako normální tabulka
pohledy lze využít v souvislosti s vnořováním dotazů  vytvoření view pro subdotaz, který se často opakuje Pohledy pohled se vytváří příkazem CREATE VIEW, jehož syntaxe je následující:
view je určeno výsledkem SQL dotazu, který může být libovolně složitý
zápis [(názvy sloupců)] použijeme tehdy, chceme-li nějakým způsobem změnit názvy sloupců Pohledy – příklad I Máme tabulku LIDÉ v níž evidujeme údaje o zaměstnanci ve firmě.
Tabulka obsahuje sloupce ID, JMÉNO, PŘÍJMENÍ, RODNÉ_ČÍSLO, VZDĚLÁNÍ
Uživatelům zpřístupníme údaje z této tabulky, vyjma rodných čísel: Pohledy – příklad I, pokr. Uživatelé budou pracovat s pohledem LIDÉ_BEZ_RC, jako by to byla normální tabulka.
Pokud bude chtít uživatel zobrazit seznam všech vysokoškolsky vzdělaných lidí, zapíše následující dotaz: Pohledy – příklad II (propojení více tabulek) pro skupinu ekonomů, která má právo vědět, kolik má kdo základní plat, ale nemá mít přístup k osobnímu ohodnocení, zkonstruujeme pohled spojující tabulku LIDÉ a PLATY (obsahuje sloupce ID_OSOBA, ZÁKL_PLAT, OSOBNÍ, PŘÍPL_VEDENÍ): Pohledy – příklad III view lze použít pro změnu názvů i formátů sloupců
vytvoříme pohled, který bude mít stejnou strukturu jako tabulka LIDÉ, jen JMÉNO a PŘÍJMENÍ bude zobrazeno jako jeden sloupec s názvem CELÉ_JMÉNO: Pohledy – příklad IV pro pohledy lze využít restrikce a omezit tak i některé řádky
Sekretářka bude mít právo manipulovat pouze se záznamy středoškoláků: Ochrana dat proti neoprávněnému přístupu práva přístupu do systému poskytuje správce systému
obecný tvar příkazu SQL udělující přístupová práva je následující:
odstranění práva přístupu se provádí příkazem REVOKE se zadáním požadavku stejně jako u GRANT Výběrový dotaz v SQL zajišťuje výběr určité skupiny záznamů z databáze na základě uživatelem definované podmínky nebo více podmínek: Výběrový dotaz v SQL - příklad Příkaz vybere všechny záznamy z tabulky srouby, jejichž velikost je větší než 10 a menší než 50 a tyto šrouby seřadí podle velikosti: Tabulkový dotaz v SQL zajišťuje uložení určité skupiny záznamů do tabulky na základě uživatelem definované podmínky nebo více podmínek: Tabulkový dotaz v SQL - příklad Příkaz vybere všechny záznamy z tabulky srouby, jejichž velikost je větší než 10 a menší než 50, tyto záznamy seřadí podle velikosti a vloží do tabulky srouby_1050: Mazací dotaz v SQL zajišťuje odstranění určité skupiny záznamů z tabulky na základě uživatelem definované podmínky nebo více podmínek: Mazací dotaz v SQL – příklad Příkaz odstraní všechny záznamy z tabulky srouby, jejichž velikost je menší než 10 a větší než 50: Aktualizační dotaz v SQL zajišťuje aktualizaci určité skupiny záznamů v tabulce na základě uživatelem definované podmínky nebo více podmínek: Aktualizační dotaz v SQL - příklad zajišťuje aktualizaci určité skupiny záznamů v tabulce na základě uživatelem definované podmínky nebo více podmínek: Predikáty predikát srovnává znakový sloupec se znakovou konstantou
ve znakové konstantě podtržítko ( _ ) nahrazuje jeden libovolný znak, znak "%" nahrazuje libovolný počet znaků získáme adresy studentů, bydlících v Praze adresa LIKE "%Praha%" získáme příjmení studentů libovolné délky začínající písmenem N příjmení LIKE "N%" Predikáty – příklad použitím predikátu LIKE lze dosáhnout např. tuto selekci tabulky Studenti: