Úvod do jazyka SQL
První blok
Organizační…
• Docházka nepovinná
• Domácí úkoly nepovinné
• Zkouška - jediná nutná věc
Co je to “databáze"?
Ve skutečnosti soubor(y)…
… stejně jako třeba tabulka v Excelu
V rámci tohoto předmětu budeme používat SQLite
Konkrétně webové rozhraní sqliteonline.com
K vyzkoušení
posp.cz/w/knihy
Co je to tabulka?
Pokud vás tato sekce zajímá, doporučuji zhlédnout video
záznam v Teams
Tabulka
Jak s databází pracovat?
Dotazovací jazyk
SELECT authors, title
FROM books
WHERE year_published = 2001
Pomocí dotazovacího jazyka
SQL se dotazujeme databáze
na data v tabulkách.
SQL je standard od roku 1986
Kontext
Central European Association
for Canadian Studies
(Vytvořeno značkovacím jazykem HTML)
Web
Web
Server
Web
Server
PHP /
Python /
Ruby /
Node.js /
ASP / …
Web
Server
Databáze
PHP /
Python /
Ruby /
Node.js /
ASP / …
SELECT author, title
FROM books
WHERE Year = 1984
SQL
Web
Server
Databáze
SQL
Data
Web
Server
Databáze
SQL
Data
Web
Server
Databáze
SQL
Data
Web
Server
Databáze
SQL
Data
SELECT title, publisher
FROM books
Vypsání pouze konkrétních sloupečků
SELECT title AS nazev
FROM books
Přejmenování sloupečku ve výpisu
SELECT *
FROM books
WHERE …
SELECT *
FROM books
WHERE sloupec = 'hodnota'
V materiálech (videa, prezentace, procvičování) z minulých
běhů tohoto předmětu jsem v těchto případech používal
dvojité uvozovky:
...sloupec = "hodnota"...
to však v aktuální verzi SQLite končí chybou:
SQLITE_ERROR: sqlite3 result code 1: no such column: ".." - should this be a string literal in single-quotes?
Řešením vždy je (ostatně jak chyba přímo napovídá) změnit za
jednoduché:
...sloupec = 'hodnota'...
SELECT *
FROM books
WHERE sloupec = hodnota
Možno použít pro čísla, nebo pokud chceme srovnávat s
hodnotou v jiném sloupci
SELECT * FROM books WHERE year_published = 2000
SELECT * FROM books WHERE title = original_title
vypíše knihy, kde název přeložené verze je stejný jako název
originálu
SELECT *
FROM books
WHERE sloupec < hodnota
SELECT *
FROM books
WHERE sloupec > hodnota
Menší/větší nebo rovno
SELECT *
FROM books
WHERE sloupec >= hodnota
SELECT *
FROM books
WHERE sloupec <= hodnota
Vždy musí být ve tvaru >=, případně <=.
Pokud použijete => či =<, dotaz skončí chybou.
Brno
Blansko
Adamov
Bílovice nad Svitavou
Obřany
Brno
Blansko
Adamov
Bílovice nad Svitavou
Obřany
name = "brno"
'=' srovnává přesnou hodnotu, čili včetně velikosti písmen
Brno
Blansko
Adamov
Bílovice nad Svitavou
Obřany
name LIKE "brno"
'LIKE' velikosti písmen neřeší
Brno
Blansko
Adamov
Bílovice nad Svitavou
Obřany
name LIKE "bRnO"
'LIKE' velikosti písmen neřeší
Brno
Blansko
Adamov
Bílovice nad Svitavou
Obřany
name LIKE "Svitavou"
Brno
Blansko
Adamov
Bílovice nad Svitavou
Obřany
name LIKE "b%"
'%' má význam "cokoliv/žolík/wildcart"
Brno
Blansko
Adamov
Bílovice nad Svitavou
Obřany
name LIKE "%o"
Brno
Blansko
Adamov
Bílovice nad Svitavou
Obřany
name LIKE "%ov%"
SELECT *
FROM books
WHERE sloupec1 = hodnota1
AND sloupec2 = hodnota2
SELECT *
FROM books
WHERE sloupec1 = hodnota1
OR sloupec2 = hodnota2
Vypište knihy, které byly vydány v roce
2000 na Slovensku nebo Chorvatsku
Vypište knihy, které byly vydány v roce
2000 nebo 1999 na Slovensku
4 + 3 × 5 = 19
(4 + 3) × 5 = 35
Pozor na priority
p1 OR p2 AND p3
(p1 OR p2) AND p3
SELECT *
FROM books
WHERE
sloupec1 = hodnota1 AND sloupec2 = hodnota2 OR
sloupec3 = hodnota3
SELECT *
FROM books
WHERE
(sloupec1 = hodnota1 AND sloupec2 = hodnota2) OR
sloupec3 = hodnota3
SELECT *
FROM books
WHERE
(sloupec1 = hodnota1 AND sloupec2 = hodnota2)
OR sloupec3 = hodnota3
Když ve WHERE kombinujete AND a OR,
vždy používejte závorky!
SELECT title, year
FROM books
LIMIT 10
OFFSET 10
LIMIT
OFFSET 0
OFFSET 1
OFFSET 2
OFFSET 3
LIMIT
OFFSET
SELECT title, year
FROM books
ORDER BY ...
SELECT title, year
FROM books
ORDER BY ... ASC/DESC
ASC = ascending, vzestupně. Výchozí řazení
DESC = descending, sestupně
SELECT title, year
FROM books
ORDER BY a, b, c
Když se hodnota ve sloupci A rovná, tak se rozhoduje podle
sloupce B, atd
SELECT title AS t, year
FROM books

WHERE ...

ORDER BY ... ASC/DESC
LIMIT ..
OFFSET ..
Funkce
Funkce
... de
fi
nované něco ...
Funkce
"umocnění"
f(x) = x2
Funkce
"umocnění"
f(x) = x25
25
Funkce
"all lower-case
ASCII characters
are converted to
their upper-case
equivalent."
UPPER()
https://www.sqlite.org/
lang_corefunc.html#upp
er
ahoj
AHOJ
ROUND(1.566) = 2

ROUND(1.566, 2) = 1.57 

SUBSTR('Pospisil', 1, 3) = 'Pos'
LENGTH('ahoj') = 4
Podívejte se na
https://www.sqlite.org/lang_corefunc.html
Každá databáze má "svoje" funkce
Vypište knihy, které v originále začínají
na člen "The"
SELECT * FROM books
WHERE original_title LIKE 'The%'
Tento dotaz však vrací i knihy začínající např. na They
přesnější je tak
SELECT * FROM books
WHERE original_title LIKE 'The %'
Pokud bychom chtěli být úplně přesní, tak
SELECT * FROM books
WHERE original_title LIKE 'The %' OR original_title LIKE 'The'
Vypište knihy, které v originále začínají
na člen "The"
Za pomoci funkcí:
SELECT * FROM books
WHERE SUBSTR(original_title, 1, 4) LIKE 'The '
Vypište knihy, které v prvních 3 znacích
názvu obsahují "g"
SELECT * FROM books
WHERE SUBSTR(original_title, 1, 3) LIKE '%g%'
Agregační funkce
AVG, MIN, MAX, COUNT, SUM
posp.cz/w/fund
Tyto agregační funkce jsou součástí standardu
Agregační funkce
Kouzlo!
sloupec
Číslo/řetězec
Agregační
funkce pracují
s množinou
řádků, které
dotaz vrátí
posp.cz/w/fund
Jaký je průměrný příspěvek z webu,
kolik celkem příspěvků bylo a jaká je
jejich suma?
SELECT COUNT(*) as pocet_prispevku, SUM(castka) as
suma, AVG(castka) as prumer
FROM prispevky WHERE zdroj LIKE 'web'
“Date” funkce
https://www.sqlite.org/lang_datefunc.html
“Date” funkce
strftime()
“Date” funkce
strftime()
strftime(format, timestring, modi
fi
er, modi
fi
er, ...)
“Date” funkce
strftime()
strftime(format, timestring, modi
fi
er, modi
fi
er, ...)
Jak vybrat květnové
příspěvky?
1. Tzv. varianta “hodně na sílu”
jak vybrat květnové příspěvky
6 způsobů
SELECT *
FROM prispevky WHERE
datum = '2023-05-01' OR
datum = '2023-05-02' OR .. ... ..
1. Tzv. varianta “hodně na sílu”
2. Bez znalosti date/string funkcí
A. 2x pomocí nerovností
jak vybrat květnové příspěvky
6 způsobů
SELECT *
FROM prispevky WHERE
datum >= '2023-05-01' AND
datum <= '2023-05-31'
1. Tzv. varianta “hodně na sílu”
2. Bez znalosti date/string funkcí
A. 2x pomocí nerovností
jak vybrat květnové příspěvky
6 způsobů
SELECT *
FROM prispevky WHERE
datum >= '2023-05-01' AND
datum < '2023-06-01'
Nemusíte řešit
kolik dní má
květen
1. Tzv. varianta “hodně na sílu”
2. Bez znalosti date/string funkcí
A. 2x pomocí nerovností
B. Bez nich!
jak vybrat květnové příspěvky
6 způsobů
SELECT *
FROM prispevky WHERE
datum LIKE '2023-05%'
1. Tzv. varianta “hodně na sílu”
2. Bez znalosti date/string funkcí
A. 2x pomocí nerovností
B. Bez nich!
3. Pouze se znalostí “date” funkcí
jak vybrat květnové příspěvky
6 způsobů
SELECT *
FROM prispevky WHERE
strftime('%m', datum) = '05'
6 způsobů
1. Tzv. varianta “hodně na sílu”
2. Bez znalosti date/string funkcí
A. 2x pomocí nerovností
B. Bez nich!
3. Pouze se znalostí “date” funkcí
4. Pouze se znalostí “string” funkcí
jak vybrat květnové příspěvky
SELECT *
FROM prispevky WHERE
substr(datum, 1, 7) = '2023-05'
GROUP BY
Agregace
Počet, průměr, součet…
Pro danou množinu řádků výstupu vždy jedno číslo
GROUP BY
GROUP BY + agregace
Agreguje se každá "skupina" samostatně
Agregační funkce
Kouzlo!
sloupec
Číslo/řetězec
Agregační funkce
Ko KoKo
Agregační funkce
Ko KoKo
GROUP BY color
Příklady
1. Kolik bylo příspěvků z webu a kolik osobně?
2. Jaká je nejčastější výše příspěvku?
3. Kolik se vybralo v jednotlivých měsících?
1) SELECT zdroj, count(*)
FROM prispevky
GROUP BY zdroj
2) SELECT castka, count(*)
FROM prispevky
GROUP BY castka
ORDER BY count(*) DESC
3) SELECT strftime('%m', datum), COUNT(*) FROM prispevky
GROUP BY strftime('%m', datum)
ORDER BY strftime('%m', datum)
WHERE + GROUP BY +
agregace
where BLUE or RED
WHERE + GROUP BY +
agregace
GROUP BY color
WHERE + GROUP BY +
agregace
Agregační funkce
číslo
číslo
Příklady
Jaká je nejčastější výše příspěvku z webu
SELECT castka, count(*)
FROM prispevky
WHERE zdroj LIKE 'web'
GROUP BY castka
ORDER BY count(*) DESC
CSV
V rámci bloku jsme si ukázali, jak dostat "do databáze", pokud
je mám např. v Excelu a jak naopak dostat výsledky nějakého
SQL dotazu zpět do Excelu. Pokud vás toto zajímá, podívejte
se na konec video záznamu v Teams