11. přednáška předmětu KIV/DBM1, zastupující přednášející: Ing. Tomáš Kotouč, ZČU v Plzni

Přednáška předmětu KIV/DBM1
11. přednáška, zastupující přednášející: Ing. Tomáš Kotouč

Úvod do SQL.

Současné relační databáze ve většině případů obsahují uživatelský jazyk SQL (Structured Query Language). Jedná se o jazyk, kterým je možné si v databázi zakládat nové objekty (tabulky, pohledy, ..), jim přiřazovat práva, z tabulek vybírat záznamy, přidávat záznamy, měnit záznamy a odstraňovat záznamy atd.

Náš minikurz se zaměří pouze na výběr záznamů (SELECT), přidávání záznamů (INSERT), úpravu záznamů (UPDATE) a na odstraňování záznamů (DELETE).

Nejprve si připravme jednoduchou tabulku, na které si budeme ukazovat všechny příklady. Máme tabulku PISNE_CD obsahující údaje o písničkách na našich CD.

Titul Interpret Minut Sekund Poznamka

Mašinka Semtex 5 29

Svařák Kutloch 2 21 mostecký tradicionál

I Love You Queen 27 37 instrumentálka

Medvídek Semtex 3 01

I Love You Queen 16 59

Ach jó, jak já jsem línej Medvídek Pražský Výběr 4 09 kvalitka

I Love You Disharmonici 1 48

Výběr záznamů - příkaz SELECT

Nejprve syntaktická konstrukce příkazu:

SELECT  [DISTINCT|ALL] {*|[sloupec_vyraz [AS nove_jmeno] [,...]}
  FROM  tabulka [alias] [,...]
[WHERE  podminka]
[GROUP  BY seznam_sl] [HAVING podminka]
[ORDER  BY seznam_sl]

Volný překlad: SELECT .. výběr, FROM .. z, WHERE .. kde, GROUP BY .. seskupit podle, ORDER BY .. setřidit podle.

Výběr z tabulky:

  SELECT * FROM pisne_cd;

vybere všechna data z naší tabulky, sloupce i řádky seřadí tak, jak jsou uvedeny v tabulce:

Titul	Interpret	Minut	Sekund	Poznamka
Mašinka	Semtex	5	29
Svařák	Kutloch	2	21	mostecký tradicionál
I Love You	Queen	27	37	instrumentálka
Medvídek	Semtex	3	01
I Love You	Queen	16	59
Ach jó, jak já jsem línej Medvídek	Pražský Výběr	4	09	kvalitka
I Love You	Disharmonici	1	48

Výběr určitých sloupců (projekce):

  SELECT interpret, titul FROM pisne_cd;

vybere z naší tabulky všechny řádky ale jen určené sloupce a to v určeném pořadí:

Interpret	Titul
Semtex	Mašinka
Kutloch	Svařák
Queen	I Love You
Semtex	Medvídek
Queen	I Love You
Pražský Výběr	Ach jó, jak já jsem línej Medvídek
Disharmonici	I Love You

.. Queen "I Love You" je zde dvakrát.

Výběr s eliminací redundantních řádků :

  SELECT DISTINCT titul FROM pisne_cd;

vybere z naší tabulky jen určený sloupec a pokud se vybírané řadky opakují, vybere je jen jednou:

Titul

Mašinka

Svařák

I Love You

Medvídek

Ach jó, jak já jsem línej Medvídek

.. "I Love You" je zde jen jednou, přestože v tabulce existuje třikrát.

  SELECT DISTINCT interpret, titul FROM pisne_cd;

vybere z naší tabulky jen určené sloupce a pokud se vybírané řadky opakují, vybere je jen jednou:

Interpret	Titul
Semtex	Mašinka
Kutloch	Svařák
Queen	I Love You
Semtex	Medvídek
Pražský Výběr	Ach jó, jak já jsem línej Medvídek
Disharmonici	I Love You

.. Queen "I Love You" je zde jen jednou, píseň "I love You" je zde dvakrát, protože ji nazpívali různé skupiny.

Pozn.: klauzule ALL slouží k výběru všech řádků včetně redundancí. Tento výběr je předpokládán implicitně, proto se ALL nemusí používat. Po pravdě řečeno - mnoho RDBMS jej ani nezná.

Výběr s odvozeným sloupcem (výrazem):

  SELECT titul, interpret, minut * 60 + sekund FROM pisne_cd;

vybere z naší tabulky všechny řádky a jen určené sloupce, přičemž do posledního sloupce vypočítá uvedený vzorec nad zadanými sloupci:

Titul	Interpret	minut * 60 + sekund
Mašinka	Semtex	329
Svařák	Kutloch	141
I Love You	Queen	1657
Medvídek	Semtex	181
I Love You	Queen	1017
Ach jó, jak já jsem línej Medvídek	Pražský Výběr	249
I Love You	Disharmonici	108

Pojmenování odvozeného sloupce (s nadpisem sloupce "minut * 60 + sekund" by se nam špatně pracovalo, a tak si jej nějak pojmenujeme):

  SELECT titul, interpret, minut * 60 + sekund AS cas_celkem FROM pisne_cd;

vybere z naší tabulky všechny řádky a jen určené sloupce, přičemž do posledního sloupce vypočítá uvedený vzorec nad zadanými sloupci a sloupec pojmenuje "cas_celkem":

Titul	Interpret	cas_celkem
Mašinka	Semtex	329
Svařák	Kutloch	141
I Love You	Queen	1657
Medvídek	Semtex	181
I Love You	Queen	1017
Ach jó, jak já jsem línej Medvídek	Pražský Výběr	249
I Love You	Disharmonici	108

Výběr záznamů s podmínkou (selekce):

  SELECT titul, interpret, minut, sekund FROM pisne_cd WHERE minut >= 4;

vybere z naší tabulky uvedené sloupce a jen ty řádky, které splňují uvedenou podmínku:

Titul	Interpret	Minut	Sekund
Mašinka	Semtex	5	29
I Love You	Queen	27	37
I Love You	Queen	16	59
Ach jó, jak já jsem línej Medvídek	Pražský Výběr	4	09

V podmínce WHERE je možné použít následující operátory :

= rovno

< menší než

> větší než

<= menší rovno

>= větší rovno

<> nerovno (ISO standard)

!= nerovno (některé dialekty SQL)

AND a zároveň

OR nebo

NOT negace

Rafinovanější dotaz:
```
SELECT titul, interpret, minut, sekund 
  FROM pisne_cd 
 WHERE minut >= 4
   AND minut <= 5;
```
Titul Interpret Minut Sekund

Mašinka Semtex 5 29

Ach jó, jak já jsem línej Medvídek Pražský Výběr 4 09
Členství ve výčtu = množině (klauzule IN, NOT IN):
```
SELECT  titul, interpret
  FROM  pisen_cd
 WHERE  interpret IN ('Queen', 'Semtex');
```
vybere všechna uvedené sloupce a jen ty řádky, kde interpret je buď "Queen" nebo "Semtex":

Titul Interpret

Mašinka Semtex

I Love You Queen

Medvídek Semtex

I Love You Queen
Vyhledávání podle podřetězce znaků (klauzule LIKE, NOT LIKE) :
```
SELECT  titul, interpret
  FROM  pisne_cd
 WHERE  titul LIKE '%Medvídek%';
```
vybere z naší tabulky uvedené sloupce a jen ty řádky, kde titul na kterém koliv místě obsahuje slovo "Medvídek":

Titul Interpret

Medvídek Semtex

Ach jó, jak já jsem línej Medvídek Pražský Výběr

Kdyby ve výrazu chybělo první "%", vybral by jen písničku od skupiny "Semtex", protože písnička od skupiny "Pražský Výběr" má před slovem "Medvídek" ještě jiný text.
Kdyby ve výrazu chybělo druhé "%", vybral by oba záznamy, protože písnička od skupiny "Semtex" za slovem "Medvídek" už nemá žádný text.
Kdyby v písničce od skupiny "Pražský Výběr" byl "medvídek" s malým "m", tento záznam by se nevybral.

Speciální znaky používané při vyhledávání podřetězců :

% (procenta) libovolný počet libovolných znaků (včetně 0)

_ (podtržítko) jeden libovolný znak

Nedefinovaná hodnota atributu (NULL, NOT NULL):

SELECT  titul, interpret, poznamka
  FROM  pisne_cd
 WHERE  poznamka IS NOT NULL;

vybere z naší tabulky uvedené sloupce a řádky jen ty, které mají vyplněnou položku "poznamka":

Titul	Interpret	Poznamka
Svařák	Kutloch	mostecký tradicionál
I Love You	Queen	instrumentálka
Ach jó, jak já jsem línej Medvídek	Pražský Výběr	kvalitka

Třídění výsledků dotazu pomocí klauzule ORDER BY sloupec [ASC|DESC]:

ASC vzestupné třídění (ascending order); implicitní

DESC sestupné třídění (descending order)

Pokud za názvem sloupce není uvedeno ani "ASC" ani "DESC", bere to systém jako kdyby tam bylo uvedeno "ASC".

SELECT  interpret, titul
  FROM  pisne_cd
 ORDER BY titul DESC, interpret ASC;

vybere z naší tabulky všechny řádky a pouze uvedené sloupce a řádky setřidí nejprve podle titulu od "Z" po "A" a v rámci stejného titulu seřadí záznamy podle interpreta od "A" po "Z":

Interpret	Titul
Kutloch	Svařák
Semtex	Medvídek
Semtex	Mašinka
Disharmonici	I Love You
Queen	I Love You
Queen	I Love You
Pražský Výběr	Ach jó, jak já jsem línej Medvídek

SELECT DISTINCT  titul, interpret, minut, sekund
  FROM  pisne_cd
 ORDER BY minut, sekund;

vybere z naší tabulky všechny řádky a pouze uvedené sloupce a řádky setřidí nejprve podle minut od nejmenší po největší a v rámci stejných minut podle sekund od nejmenší po největší. Pokud se stejný záznam vyskytne vícekrát, vybere ho jen jednou (DISTINCT):

Titul	Interpret	Minut	Sekund
I Love You	Disharmonici	1	48
Svařák	Kutloch	2	21
Medvídek	Semtex	3	01
Ach jó, jak já jsem línej Medvídek	Pražský Výběr	4	09
Mašinka	Semtex	5	29
I Love You	Queen	16	59
I Love You	Queen	27	37

Agregační funkce:

COUNT() vrací počet hodnot ve specifikovaném sloupci

SUM() vrací součet hodnot ve specifikovaném sloupci

AVG() vrací průměr z hodnot ve specifikovaném sloupci

MIN() vrací minimum z hodnot ve specifikovaném sloupci

MAX() vrací maximum z hodnot ve specifikovaném sloupci

Pozn.: agregační funkce nelze použít v klauzuli WHERE.

Výběr počtu záznamů:

SELECT COUNT(titul) FROM pisen_cd;

v naší tabulce spočte počet záznamů, kde titul je vyplněný:

count(titul)

SELECT COUNT(poznamka) FROM pisen_cd;

v naší tabulce spočte počet záznamů, kde je poznámka vyplněna (IS NOT NULL):

count(poznamka)

SELECT COUNT(*) pocet FROM pisen_cd;

v naší tabulce spočte počet záznamů. Je mu jedno kolik jich je prázdných. Nadpis "count(*)" změní na "pocet":

pocet

SELECT COUNT(DISTINCT titul) pocet FROM pisen_cd;

v naší tabulce spočte počet písniček, pokud je název písničky uveden několikrát, bere se jen jednou:

pocet

SELECT min(minut * 60 + sekund) nejkratsi,
       max(minut * 60 + sekund) nejdelsi,
       avg(minut * 60 + sekund) prumer,
       sum(minut * 60 + sekund) delka_celkem,
       count(*) pocet
  FROM pisne_cd

Nad naší tabulkou spočte dané vzorce:

nejkratsi	nejdelsi	prumer	delka_celkem	pocet
108	1657	526	3682	7

Slučování vybraných záznamů do skupin (klauzule GROUP BY):

SELECT titul, 
       COUNT(*) pocet,
       avg(minut * 60 + sekund) prumer,
       min(minut * 60 + sekund) nejkratsi,
       max(minut * 60 + sekund) nejdelsi
  FROM pisen_cd
 GROUP BY titul;

zobrazí název písničky a u každé z nich, kolikrát se v naší tabulce vyskytuje. Zároveň záznamy setřídí (ASC) postupně podle všech položek, podle kterých se seskupuje:

Titul	pocet	prumer	nejkratsi	nejdelsi
Ach jó, jak já jsem línej Medvídek	1	249	249	249
I Love You	3	927	108	1657
Mašinka	1	329	329	329
Medvídek	1	181	181	181
Svařák	1	141	141	141

Dotazy nad více tabulkami:

Do naší tabulky přidáme ještě číslo CD, na kterém se vyskytuje a zřídíme si tabulku CDček CD, která bude obsahovat název CD a jeho číslo.

CD_ID Titul Interpret Minut Sekund Poznamka

1234 Mašinka Semtex 5 29

1234 Svařák Kutloch 2 21 mostecký tradicionál

9876 I Love You Queen 27 37 instrumentálka

1234 Medvídek Semtex 3 01

5678 I Love You Queen 16 59

1234 Ach jó, jak já jsem línej Medvídek Pražský Výběr 4 09 kvalitka

1234 I Love You Disharmonici 1 48

N:1

CD_ID Nazev

1234 Výběr toho nejlepšího z ČR

9876 Queen - A Night At The Opera

5678 Queen - Love Over Gold

Při výběru z více tabulek je nutné určit přes jaké sloupce budou tabulky propojeny (v našem případě přes jen jeden sloupec a to CD_ID). U sloupců je z důvodu jednoznačnosti nutné udávat plně kvalifikované názvy sloupců (tedy včetně názvů tabulek nebo jejich aliasů):

SELECT  CD.nazev, P.titul, P.interpret, P.poznamka
  FROM  pisne_CD P, CD
 WHERE  P.CD_ID = CD.CD_ID;

spojí tabulky a vybere požadované sloupce:

nazev	titul	interpret	poznamka
Výběr toho nejlepšího z ČR	Mašinka	Semtex
Výběr toho nejlepšího z ČR	Svařák	Kutloch	mostecký tradicionál
Queen - A Night At The Opera	I Love You	Queen	instrumentálka
Výběr toho nejlepšího z ČR	Medvídek	Semtex
Queen - Love Over Gold	I Love You	Queen
Výběr toho nejlepšího z ČR	Ach jó, jak já jsem línej Medvídek	Pražský Výběr	kvalitka
Výběr toho nejlepšího z ČR	I Love You	Disharmonici

Sjednocení, rozdíl a průnik dotazů:

Nejprve si zavedeme ještě jednu tabulku pisne_MC, která bude obsahovat písničky, které máme na kazetách. Bude se od pisne_CD lišit v tom, že nebude obsahovat sloupec "poznamka":

Titul Interpret Minut Sekund

Kapička Kutloch 2 56

Svařák Kutloch 2 21

Medvídek Semtex 3 01

I Love You Disharmonici 2 29

Pozn.: dotazy musí vracet stejný počet sloupců a typy jednotlivých sloupců si musí odpovídat. Není tedy nutné, aby se stejně jmenovali, ale aby byli stejného typu.

Sjednocení dotazů (klauzule UNION):

SELECT titul, interpret, minut, sekund
  FROM pisne_CD
UNION
SELECT titul, interpret, minut, sekund
  FROM pisne_MC;

vybere všechny záznamy z tabulky pisne_CD a přidá k nim záznamy z tabulky pisne_MC. Záznamy které jsou shodné uvede jen jednou a záznamy setřídí postupně podle všech sloupců:

Titul	Interpret	Minut	Sekund
Ach jó, jak já jsem línej Medvídek	Pražský Výběr	4	09
I Love You	Disharmonici	1	48
I Love You	Disharmonici	2	29
I Love You	Queen	16	59
I Love You	Queen	27	37
Kapička	Kutloch	2	56
Mašinka	Semtex	5	29
Medvídek	Semtex	3	01
Svařák	Kutloch	2	21

Disharmonici mají různou délku skladby, takže je systém vybere dvakrát.

Nepřejeme-li si odstranit duplicitní záznamy, musíme použít pro sjednocení klauzuli UNION ALL

Rozdíl dotazů (klauzule MINUS):

SELECT titul, interpret, minut, sekund
  FROM pisne_CD
MINUS
SELECT titul, interpret, minut, sekund
  FROM pisne_MC;

vybere všechny záznamy z tabulky pisne_CD a odstraní všechny záznamy, které se vyskytují v tabulce pisne_MC. Záznamy setřídí postupně podle všech sloupců:

Titul	Interpret	Minut	Sekund
Ach jó, jak já jsem línej Medvídek	Pražský Výběr	4	09
I Love You	Disharmonici	1	48
I Love You	Queen	16	59
I Love You	Queen	27	37
Mašinka	Semtex	5	29

Disharmonici mají různou délku skladby, takže je systém vybere také.

Průnik dotazů (klauzule INTERSECT):
```
SELECT titul, interpret, minut, sekund
  FROM pisne_CD
INTERSECT
SELECT titul, interpret, minut, sekund
  FROM pisne_MC;
```
vybere záznamy, které jsou shodné v obou tabulkách. Záznamy setřídí postupně podle všech sloupců:

Titul Interpret Minut Sekund

Medvídek Semtex 3 01

Svařák Kutloch 2 21

Disharmonici mají různou délku skladby, takže je systém nevybere.

Vkládání záznamů (INSERT):

INSERT INTO pisne_CD 
       VALUES ('Tři čuníci', 'Nohavica', 3, 21, NULL);

do tabulky pisne_CD přidá jeden nový záznam.

INSERT  INTO pisne_CD (titul, interpret, minut, sekund, poznamka)
(
SELECT titul, interpret, minut, sekund, NULL
  FROM pisne_MC
MINUS
SELECT titul, interpret, minut, sekund, NULL
  FROM pisne_CD
);

do tabulky pisne_CD přidá ty záznamy z tabulky pisne_MC, které v tabulce pisne_CD ještě nejsou = rozdíl pisne_MC a pisne_CD.

Aktualizace záznamů (UPDATE):
```
UPDATE pisne_CD
   SET poznamka = 'XXX'
 WHERE interpret = 'Queen';
```
v tabulce změní poznámku na "XXX" v záznamech, kde interpret je "Queen".
Rušení záznamů (DELETE):
```
DELETE
  FROM pisne_CD
 WHERE titul like '%Medvidek%';
```
z tabulky vymaže všechny záznamy, kde se v titulu vyskytuje slovo "Medvídek".

Pokud najdete nějaké nesrovnalosti, zašlete je, prosím, na e-mailovou adresu kotouc@civ.zcu.cz.

=	rovno
<	menší než
>	větší než
<=	menší rovno
>=	větší rovno
<>	nerovno (ISO standard)
!=	nerovno (některé dialekty SQL)
AND	a zároveň
OR	nebo
NOT	negace

%	(procenta) libovolný počet libovolných znaků (včetně 0)
_	(podtržítko) jeden libovolný znak

ASC	vzestupné třídění (ascending order); implicitní
DESC	sestupné třídění (descending order)

COUNT()	vrací počet hodnot ve specifikovaném sloupci
SUM()	vrací součet hodnot ve specifikovaném sloupci
AVG()	vrací průměr z hodnot ve specifikovaném sloupci
MIN()	vrací minimum z hodnot ve specifikovaném sloupci
MAX()	vrací maximum z hodnot ve specifikovaném sloupci