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.

SELECT ID, 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říkaz WHERE .

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 Zakaznici ORDER BY Prijmeni

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 Objednavky ORDER BY Castka DESC

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 Zakaznici ORDER BY Mesto, 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 Zakaznici ORDER BY PSC, ID DESC

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 Zakaznici WHERE Prijmeni = "Sobota"

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

SELECT * FROM Objednavky WHERE Castka >= 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 Zakaznici WHERE Prijmeni = "Veselý" AND Mesto = "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 Produkty WHERE Nazev = "Myš" OR Nazev = "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 Objednavky WHERE Datum = #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.

SELECT COUNT (Prijmeni), Mesto FROM Zakaznici GROUP BY Mesto

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

SELECT COUNT (Prijmeni) AS Pocet, Mesto FROM Zakaznici GROUP BY Mesto

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

Příkaz HAVING