SQL pro blbce

Copyright © 2014 by Vladimír Klaus, Jaroslav Hajný

Cover design © 2014 by Vladimír Klaus

Veškerá práva vyhrazena. Žádná část tohoto díla nesmí být reprodukována ani elektronicky přenášena či šířena bez předchozího písemného souhlasu majitele autorských práv.

Odpovědný redaktor: Jiří Vlček

Jazyková redakce: AUDREY software

Korektura: Vladimír Tobyáš

Sazba: Vladimír Klaus

Obálka: Vladimír Klaus

Vydání druhé

Vydalo nakladatelství Palmknihy s.r.o., Malá Štupartská 7, Praha 1, v únoru 2014

ISBN: 978-80-7486-033-1 (ePub)

ISBN: 978-80-7486-034-8 (mobi)

ISBN: 978-80-7486-035-5 (PDF)

Obsah

Úvod

Komu je příručka určena

Co zde najdete

Doprovodná databáze

Poděkování čtenářům

Co je nového

Základní příkazy

Příkaz SELECT

Příkaz ORDER BY

Příkaz WHERE

Příkaz GROUP BY

Příkaz HAVING

Agregační funkce

Funkce SUM

Funkce COUNT

Funkce AVG

Funkce MIN a MAX

Funkce FIRST a LAST

Statistické agregační funkce

Funkce STDEV

Funkce STDEVP

Funkce VAR

Funkce VARP

Další funkce

Funkce LCASE, UCASE

Funkce LEFT, RIGHT

Funkce MID

Funkce TRIM, LTRIM, RTRIM

Funkce REPLACE

Funkce SWITCH

Funkce CHOOSE

Funkce LEN

Přetypování čísla na text

Přetypování textu na číslo

Funkce ASC a ASCW

Funkce CHR a CHRW

Funkce SGN

Funkce DATEPART

Funkce ROUND

Trigonometrické funkce

Aritmetické funkce

Spojování tabulek

Příkaz INNER JOIN

Příkaz LEFT JOIN

Příkaz RIGHT JOIN

Příkazy INNER JOIN, LEFT JOIN a RIGHT JOIN graficky

Příkaz OUTER JOIN

Příkaz CROSS JOIN

Příkaz UNION a UNION ALL

Příkaz INTERSECT

Příkaz EXCEPT

Predikáty

Predikát DISTINCT

Predikát DISTINCTROW

Predikát TOP

Predikát LIKE

Predikát IS NULL

Predikát BETWEEN

Predikát IN

Predikát EXISTS

Predikáty ANY, SOME, ALL

Úprava dat v tabulkách

Příkaz INSERT

Příkaz UPDATE

Příkaz DELETE

Import dat z CSV

Práce s tabulkami

Příkaz CREATE TABLE

Příkaz ALTER TABLE

Příkaz DROP TABLE

Příkaz CREATE INDEX

Příkaz DROP INDEX

Referenční integrita

Praktické příklady

Duplicity v tabulkách

Ovlivnění pořadí ve výsledku dotazu

Triggery

Co to jsou triggery

Přidání triggeru

Odstranění triggeru

Chyba v triggeru

Ostatní

Proč zde používáme MS Access 2010?

Jak převést MDB databázi na novější ACCDB

Co to je NULL

SQL příkazy nejsou case-sensitive

Pozor na přehození ORDER a WHERE

Pořadí vyhodnocení dotazu

Používání uvozovek, apostrofů a středníků

Co to je ID typu Automatické číslo

Aliasy

Tabulky v příkladech

Tabulka Zakaznici

Tabulka Produkty

Tabulka Objednavky

Tabulka Aritmetika

Tabulka Kontakty

Tabulka Log

Tabulka Pomocna

Tabulka TrigonometrickeUdaje

Další příručky

Již vyšlo

Připravujeme

O autorech

AUDREY software

Chcete nám napsat?

Úvod

Komu je příručka určena

Všem, kteří se chtějí seznámit s jazykem SQL, ale i pro ty, kteří celý život s SQL zápasí a občas si potřebují některé detaily nebo málo známé příkazy připomenout. Všechny příkazy a funkce jsou názorně a velmi jednoduše vysvětlovány a předvedeny na mnoha konkrétních příkladech z praxe.

Co zde najdete

- 140 stránek plných snadno pochopitelného čtení

- přehledné členění do 90 kapitol

- 180 příkladů SQL včetně komentářů

- 220 obrázků doprovázejících všechny příklady

- vysvětlení nejpoužívanějších příkazů, funkcí, predikátů

- užitečná upozornění a poznámky z praxe

- odlišnosti příkazů v různých databázích

- …a další řadu bonusů a článků na webu

Doprovodná databáze

V příručce jsou na všech místech používané příklady, které vycházejí z existující MS Access databáze, která je zdarma k dispozici na stránkách http://www.sqlproblbce.cz.
Databáze také obsahuje velké množství SQL dotazů k okamžitému vyzkoušení. Na webu najdete dvě verze – klasický MDB soubor (pro MS Access 2002 a vyšší) a pak novější verzi ACCDB (pro MS Access 2007 a vyšší).

Poděkování čtenářům

Hned na úvod chceme poděkovat, že jste si zakoupili tuto příručku. Nebo nezakoupili a přesto chcete rychle a jednoduše podpořit autory v další práci?

Pak stačí jediné – převést 99 Kč na účet 19-4092520297/0100.

Děkujeme.

Co je nového

Pokud vlastníte první verzi této příručky, možná oceníte seznam zcela nových kapitol, tak i seznam kapitol, ve kterých došlo k nějakým změnám, doplnění příkladů nebo opravám.

Zcela nové kapitoly

- Funkce FIRST a LAST

- Funkce REPLACE

- Funkce SWITCH

- Funkce CHOOSE

- Funkce LEN

- Přetypování čísla na text

- Přetypování textu na číslo

- Funkce ASC a ASCW

- Funkce CHR a CHRW

- Funkce SGN

- Trigonometrické funkce

- Aritmetické

- Příkaz INTERSECT

- Příkaz EXCEPT

- Import dat z CSV

- Příkaz CREATE INDEX

- Příkaz DROP INDEX

- Referenční integrita

- Duplicity v tabulkách

- Ovlivnění pořadí ve výsledku dotazu

- Co to jsou triggery

- Přidání triggeru

- Odstranění triggeru

- Chyba v triggeru

- Jak převést MDB databázi na novější ACCDB

- Pozor na přehození ORDER a WHERE

- Pořadí vyhodnocení dotazu

Změněné kapitoly

- Doprovodná databáze

- Příkaz ORDER BY

- Funkce COUNT

- Funkce MIN a MAX

- Funkce STDEV

- Funkce LCASE, UCASE

- Funkce MID

- Funkce ROUND

- Příkaz INNER JOIN

- Příkaz LEFT JOIN

- Příkaz UNION a UNION ALL

- Predikát DISTINCT

- Predikát LIKE

- Predikát IN

- Predikát EXISTS

- Příkaz INSERT

- Příkaz ALTER TABLE

- Příkaz DROP TABLE

- Co to je NULL

- Aliasy

- Tabulky v příkladech

Základní příkazy

Příkaz SELECT

Tento základní a nejčastěji používaný příkaz slouží k získání (výběru) dat z databázové tabulky. Začínáme slovem SELECT (vybrat), následuje „*“ (všechny sloupce), klíčové slovo FROM (odkud) a nakonec i název tabulky.

Tímto příkazem provedeme výběr všech sloupců (*) a všech záznamů (řádek) z tabulky Zakaznici.

SELECT* FROM Zakaznici

Následující příkaz také vybírá všechny záznamy, ale již pouze sloupce ID a Jmeno.

SELECTID, Jmeno FROM Zakaznici

Upozornění: Takže raději ještě jednou! Hvězdička neznamená všechny řádky, ale všechny sloupce. Všechny řádky to jsou automaticky, pokud nezadáme nějakou podmínku, viz dále příkazWHERE.

Příkaz SELECT je možné dále vylepšovat pomocí dalších příkazů, funkcí a predikátů, což bude popsáno v dalších částech.

Příkaz ORDER BY

Už tedy umíme vybírat data z tabulky příkazem SELECT, nyní se je naučíme řadit. Pokud totiž příkaz k řazení nebude použit, budou záznamy většinou v pořadí, v jakém byly do tabulky vkládány. Přesněji řečeno – pořadí není definováno.

Takto jednoduše seřadíme všechny zákazníky dle příjmení, a to vzestupně – v tomto případě (jedná se o textové pole) tedy od A do Z.

SELECT * FROM ZakazniciORDER BYPrijmeni

Chceme-li řadit sestupně, použijeme klíčové slovo DESC (u vzestupného se dá zase použít ASC, ale to není povinné). Takto získáme záznamy o prodejích, řazené dle částky od nejvyšší do nejnižší.

SELECT * FROM ObjednavkyORDER BYCastkaDESC

A dá se řadit podle více sloupců? Samozřejmě! Krásným příklad je seřazení zákazníků dle měst a pak (dalo by se říci v rámci měst) i dle příjmení a (v rámci příjmení) také dle jména. V níže uvedeném příkladu, kde je velmi málo dat v tabulce, je to dobře patrné pouze u Prahy 8 a Příjmení.

SELECT * FROM ZakazniciORDER BYMesto, Prijmeni, Jmeno

Ještě jedna specialita. Vypíšeme všechny zákazníky, seřadíme dle PSČ a dále pak podle toho, jak byli přidáváni do tabulky, ale počínaje naposledy přidaným. Využijeme k tomu ID, což je údaj, který se automaticky zvyšuje o 1 při každém přidání záznamu. Samozřejmě se nezvyšuje sám od sebe, ale musíme zvyšování tomuto poli nastavit.

SELECT * FROM ZakazniciORDER BYPSC, IDDESC

Aby to nebylo tak jednoduché, je tu ještě jedna možnost, jak zadat řazení výsledku – a to pomocí relativní pozice sloupců ve výsledku. To může být velká výhoda, pokud měníte sloupce za příkazem SELECT, ale zároveň to ze stejného důvodu může způsobit neočekávané řazení – když někdo pouze změní vybírané sloupce a zapomene upravit hodnoty v ORDER BY.

SELECT Nazev, Cena FROM Produkty ORDER BY 2, 1

Tento příkaz tedy odpovídá tomuto:

SELECT Nazev, Cena FROM Produkty ORDER BY Cena, Nazev

Příkaz WHERE

Zatím jsme vždy vybírali všechny záznamy, nyní se je naučíme filtrovat pomocí jedné nebo více podmínek.

Takto získáme všechny zákazníky, jejichž příjmení je Sobota.

SELECT * FROM ZakazniciWHEREPrijmeni = "Sobota"

A takto získáme záznamy objednávek, které byly alespoň za 1000 (korun).

SELECT * FROM ObjednavkyWHERECastka >= 1000

A co když potřebujeme všechny objednávky pana Soboty, za které utratil více než 500 korun? Tak to je už složitější úkol (údaje jsou ve dvou tabulkách), o tom si povíme později v části INNER JOIN.

Kombinace podmínek se provádí pomocí logických operátorů (AND, OR apod.). Takto získáme všechny zákazníky z Prahy 8, kteří se jmenují Veselý.

SELECT * FROM ZakazniciWHEREPrijmeni = "Veselý"ANDMesto = "Praha 8"

AND = a současně, tj. musí platit obě podmínky.

Takto získáme všechny produkty, které se jmenují „Myš“ nebo „Klávesnice“.

SELECT * FROM ProduktyWHERENazev = "Myš"ORNazev = "Klávesnice"

OR = nebo, tj. musí platit buď jedna podmínka, nebo druhá, případně obě.

Jak vidíte, výsledkem je pouze jeden záznam, protože produkt, který by se jmenoval „Klávesnice“ vůbec neexistuje.

Malinko složitější je sestavení dotazu, kde je v podmínce uvedeno datum. Ono jde totiž o to, že se datum v různých zemích může zapisovat různě, a proto bylo třeba vymyslet a stanovit jednotný zápis bez ohledu na zemi nebo nějaké lokální nastavení. Tímto zápisem je #měsíc/den/rok#.

Poznámka: V jiných databázích je možné (nutné) použít místo znaku # apostrof. V MS Accessu to nelze, protože by to pak chápal jako textový řetězec

Pomocí tohoto dotazu získáme objednávky, které byly provedeny 14. 9. 2012.

SELECT * FROM ObjednavkyWHEREDatum = #9/14/2012#

Velmi užitečné je také použití predikátu LIKE, pomocí kterého můžeme podmínku zadat s jistou mírou neurčitosti či volnosti.

Příkaz GROUP BY

Tento skvělý příkaz slouží ke slučování (seskupování), tedy ke zjištění unikátních hodnot v jednom sloupci, nebo případně unikátních kombinací hodnot ve více sloupcích.

Díky slučování můžete získat například statistické údaje. Ale popořádku. Asi si dovedete představit, jak složité by bylo v davu 1000 lidí zjistit, z jakých měst jednotliví lidé jsou a kolik jich v daném městě celkem je. Zcela logicky se taková věc řeší tak, že se lidé rozdělí do skupin dle jednotlivých měst a pak sečteme lidi v jednotlivých skupinách - získáme počet lidí v jednotlivých městech.

Zdálo by se tedy logické napsat něco takového. Ale to nelze!

SELECT Prijmeni, Mesto FROM Zakaznici GROUP BY Mesto

Poznámka: Platí jednoduché pravidlo - seznam sloupců v příkazu GROUP BY musí odpovídat seznamu sloupců v příkazu SELECT, ovšem bez sloupců s agregačními funkcemi.

Při seskupování často využíváme nějakou agregační funkci, a jak vidíte v tomto příkladu, je předchozí pravidlo splněno a dotaz bude fungovat.

SELECTCOUNT(Prijmeni), Mesto FROM ZakazniciGROUP BYMesto

Pokud se vám nelíbí, že výsledná tabulka má první sloupec pojmenovaný „Expr1000“ (záleží na databázi, takto to pojmenoval MS Access 2010), pak si můžete příkaz vylepšit zadáním pomocného názvu - aliasu. Tedy v našem případě, aby se sloupec, který počítá příjmení v jednotlivých městech, jmenoval třeba „Pocet“.

SELECTCOUNT(Prijmeni)ASPocet, Mesto FROM ZakazniciGROUP BYMesto

Jestliže jste si toho nevšimli před chvílí, nyní byste už určitě měli. Jak je možné, že u Prahy 5 je počet roven 0? Kdyby tam nikdo nebydlel, tak by ve výsledku takový řádek vůbec neměl figurovat. Jenomže on tam jeden člověk bydlí, jen nemá v tabulce zákazníků vyplněné příjmení (která tu sčítáme). A když není vyplněné, tak je tam NULL a hodnoty NULL se nedají sčítat. Více v části Co to je NULL.

Abychom se tomuto problému vyhnuli, musíme použít malý trik pomocí funkce NZ(). Ta nám (v tomto případě) vrátí příjmení, ale pokud by náhodou bylo NULL, pak vrátí text "nevyplněno". A výskyt takové hodnoty se už může sčítat, takže nám dotaz bude fungovat zcela korektně.

SELECT COUNT(NZ(Prijmeni, "nevyplněno")) AS Pocet, Mesto
FROM Zakaznici GROUP BY Mesto

Poznámka: Příkaz GROUP BY se liší od predikátu DISTINCT možností použití s agregační funkcí.

Příkaz HAVING

Tento příkaz umožňuje definování podmínky, podobně jako to umožňuje příkaz WHERE. Jeho nasazení je nutné, pokud chcete mít v podmínce výsledek nějaké agregační funkce.

Dejme tomu, že si chceme udělat malou statistiku – kteří zákazníci a za kolik u nás objednali. To není nic složitého – seskupíme je podle jejich ID a v těchto skupinách zjistíme počet a součet.

SELECT IdZakaznika, COUNT(Castka), SUM(Castka)
FROM Objednavky
GROUP BY IdZakaznika

Dotaz nyní upravíme tak, abychom si vypsali pouze takové zákazníky, kteří u nás objednali celkově za více než 5000 Kč. Kromě toho do dotazu doplníme pojmenování výsledku agregačních funkcí pomocí aliasů.

SELECT IdZakaznika, COUNT(Castka)ASPocet, SUM(Castka)ASCelkem
FROM Objednavky
GROUP BY IdZakaznika
HAVINGSUM(Castka) > 5000

Za pozornost stojí také to, že za HAVING musíte zopakovat agregační funkci, nikoliv její alias. Toto tedy nebude fungovat! Více informací v části Sloupcové aliasy.

Vážení čtenáři, právě jste dočetli ukázku z knihy SQL pro blbce.
Pokud se Vám líbila, celou knihu si můžete zakoupit v našem e-shopu.