Příkaz DQL

V předchozích kapitolách jsme se naučili, jak v databázi vytvářet relace (DDL) a vkládat, měnit nebo mazat data (DML). Ale co když už data v databázi máme a chceme z nich získat informace?
Právě k tomu slouží jazyk DQL – Data Query Language.

Základním a nejdůležitějším příkazem DQL je SELECT. Pomocí něj se dokážeme dotázat databáze na libovolná data – můžeme si vybrat jen některé atributy (sloupce), zobrazit jen některé záznamy (řádky) podle podmínek (restrikce), třídit výsledky, seskupovat je nebo dokonce spojovat více relací dohromady.

Vezměme si například relaci 01Predmet, která bude mít strukturu definovanou tímto DDL příkazem:

CREATE TABLE `01Predmet` (
  `IDPredmet` int(11) NOT NULL AUTO_INCREMENT,
  `Nazev` text NOT NULL,
  `Zkratka` char(3) NOT NULL,
  PRIMARY KEY (`IDPredmet`)
);

V relaci už máme naplněná nějaká data pomocí DML příkazu INSERT INTO.

Na všechny záznamy v relaci a hodnoty všech atributů se podíváme jednoduchým dotazem SELECT:

SELECT * FROM 01Predmet;

Systém vypíše něco ve smyslu:

+-----------+-----------------------------------+---------+
| IDPredmet | Nazev                             | Zkratka |
+-----------+-----------------------------------+---------+
|         1 | Databázové systémy                | DBS     |
|         2 | Webdesign                         | WBD     |
|         3 | Aplikační software                | APS     |
|         4 | Programování a vývoj aplikací     | PVA     |
|         5 | Český jazyk a literatura          | CJL     |
|         6 | Anglický jazyk                    | ANJ     |

Hvězdička za klíčovým slovem SELECT znamená, že chci vypsat všechny atributy. To někdy ale není náš případ – chceme vypsat například pouze zkratku a název. V tom případě SELECT můžeme upravit:

SELECT Zkratka, Nazev FROM 01Predmet;
+---------+-----------------------------------+
| Zkratka | Nazev                             |
+---------+-----------------------------------+
| DBS     | Databázové systémy                |
| WBD     | Webdesign                         |
| APS     | Aplikační software                |
| PVA     | Programování a vývoj aplikací     |
| CJL     | Český jazyk a literatura          |
| ANJ     | Anglický jazyk                    |
| AJK     | Anglická konverzace               |

Někdy také chceme vybrat jen určité záznamy (nikoliv všechny). Pro takovéto výběry nám slouží tzv. restrikce, neboli část SELECTu za klíčovým slovem WHERE:

SELECT Zkratka, Nazev FROM 01Predmet WHERE IDPredmet < 5;
+---------+-----------------------------------+
| Zkratka | Nazev                             |
+---------+-----------------------------------+
| DBS     | Databázové systémy                |
| WBD     | Webdesign                         |
| APS     | Aplikační software                |
| PVA     | Programování a vývoj aplikací     |
+---------+-----------------------------------+

Termíny

Prakticky jsme si prošli projekci, selekci i restrikci. Nyní si termíny nadefinujeme:

projekce znamená, že z relace nezobrazujeme všechny atributy, ale pouze ty, které chceme zobrazit – v praxi projekci realizujeme seznamem atributů přímo za slovem SELECT.
Chceme-li zobrazit všechny atributy, používáme SELECT *.
selekce znamená, že z relace nezobrazujeme všechny záznamy, ale pouze ty, které chceme zobrazit – v praxi selekci realizujeme přidáním restrikce (tj. části za WHERE).
Chceme-li zobrazit všechny záznamy, restrikci zcela vypouštíme.
restrikce je část SELECTu za slovem WHERE – tam je jedna nebo několik podmínek, které musejí být splněny, aby záznamy byly zobrazeny.

Příklad projekce: SELECT Zkratka FROM 01Predmet;

Příklad selekce: SELECT * FROM 01Predmet WHERE IDPredmet < 5;

Restrikce z toho příkladu je IDPredmet < 5

SELECT od začátku

Příkaz SELECT je v naprosté většině případů spojený s jednou nebo několika relacemi. Je možné jej ale použít také bez relací – zkuste si například:

SELECT 5 * 5;
SELECT SQRT(36);
SELECT 19 % 5;
SELECT POWER(2, 8);

Na těchto příkladech vidíme, že je možné bez problému provádět matematické operace. SELECT ale umí více, například pracovat s řetězci nebo zobrazovat datum:

SELECT SUBSTRING("Michael", 3, 3);
SELECT NOW();
SELECT DAY(NOW());
SELECT NOW() + INTERVAL 10 DAY;

Příkaz SELECT můžeme využít také pro zobrazení některých systémových proměnných, například:

SELECT USER();
SELECT DATABASE();
SELECT VERSION();

Řazení výsledků

Někdy budeme chtít mít výsledky seřazené nikoliv podle toho, jak se záznamy vkládaly do databázové tabulky, ale podle konkrétního atributu nebo několika atributů. K tomu budeme používat příkaz ORDER BY <nazev_atributu> a směr ASC (ascending, vzestupně) či DESC (descending, sestupně).

Například :

SELECT * FROM 01Predmet ORDER BY Zkratka;
+-----------+-----------------------------------+---------+
| IDPredmet | Nazev                             | Zkratka |
+-----------+-----------------------------------+---------+
|         7 | Anglická konverzace               | AJK     |
|         6 | Anglický jazyk                    | ANJ     |
|         3 | Aplikační software                | APS     |
|         5 | Český jazyk a literatura          | CJL     |
|         1 | Databázové systémy                | DBS     |
|        11 | Dějepis                           | DEJ     |
|        28 | Desktop publishing                | DTP     |
|        17 | Ekonomie                          | EKO     |
+-----------+-----------------------------------+---------+

SELECT * FROM 01Predmet ORDER BY Zkratka DESC;
+-----------+-----------------------------------+---------+
| IDPredmet | Nazev                             | Zkratka |
+-----------+-----------------------------------+---------+
|        23 | Základy techniky                  | ZKT     |
|        14 | Základy ekologie                  | ZEK     |
|        26 | Zabezpečovací zařízení            | ZAZ     |
|         2 | Webdesign                         | WBD     |
|        25 | Tvorba odborného textu            | TOT     |
|        16 | Tělesná výchova                   | TEV     |
|         4 | Programování a vývoj aplikací     | PVA     |
|        24 | Praxe                             | PRA     |
+-----------+-----------------------------------+---------+

Modifikátor

Modifikátor DISTINCT je speciální přepínač, který pomůže vybrat pouze jedinečné záznamy (bez duplicit). Podívejte se na příklad bez a s modifikátorem:

SELECT Titul FROM 01Ucitel;
+-------+
| Titul |
+-------+
| Mgr.  |
| Ing.  |
| Mgr.  |
| Ing.  |
| Ing.  |
| Mgr.  |
| Bc.   |
| Mgr.  |
| Mgr.  |
| Mgr.  |
| Ing.  |
| RNDr. |
| Ing.  |
| RNDr. |
| Mgr.  |
| PhDr. |
+-------+

SELECT DISTINCT Titul FROM 01Ucitel;
+-------+
| Titul |
+-------+
| Mgr.  |
| Ing.  |
| Bc.   |
| RNDr. |
| PhDr. |
+-------+

Počet a alias

Chceme-li vybrat počet záznamů odpovídajících selekci, používáme COUNT(), například:

 SELECT COUNT(*) FROM 01Predmet;
+----------+
| COUNT(*) |
+----------+
|       28 |
+----------+

Protože výsledek je pojmenován poměrně složitě COUNT(*), což může být pro následnou práci v aplikační vrstvě složitější, je možné výsledek pomocí tzv. aliasu přejmenovat. Alias uvádíme za název atributu a slovo AS:

SELECT COUNT(*) AS pocet FROM 01Predmet;
+-------+
| pocet |
+-------+
|    28 |
+-------+

Součet, průměr, minimum, maximum

Obdobně pro součet a průměr používáme SUM(), AVG(), MIN() a MAX() například:

 SELECT PocetZaku FROM 01Trida;
+-----------+
| PocetZaku |
+-----------+
|        30 |
|        30 |
|        30 |
|        24 |
+-----------+

SELECT SUM(PocetZaku) FROM 01Trida;
+----------------+
| SUM(PocetZaku) |
+----------------+
|            114 |
+----------------+

SELECT AVG(PocetZaku) FROM 01Trida;
+----------------+
| AVG(PocetZaku) |
+----------------+
|        28.5000 |
+----------------+

SELECT MIN(PocetZaku) FROM 01Trida;
+----------------+
| MIN(PocetZaku) |
+----------------+
|             24 |
+----------------+

SELECT MAX(PocetZaku) FROM 01Trida;
+----------------+
| MAX(PocetZaku) |
+----------------+
|             30 |
+----------------+

Vytvoření nového atributu spojením

Pomocí příkazu CONCAT() a následně aliasu můžeme vytvořit i kompletně nový atribut.

 SELECT CONCAT(Titul, " ", Jmeno, " ", Prijmeni) AS CeleJmeno FROM 01Ucitel;
+----------------------------+
| CeleJmeno                  |
+----------------------------+
| Mgr. Alena Brandsteinová   |
| Ing. Bohumír Šimek         |
| Mgr. Barbora Šnajdrová     |
| Ing. Eva Klozová           |
| Ing. Jaroslav Darius       |
| Mgr. Jan Farkaš            |
| Bc. Jana Kuntošová         |
| Mgr. Josef Pachovský       |
+----------------------------+

Výběr z několika relací

Představme si, že kromě relace pro ukládání předmětů máme také relaci pro ukládání tříd a rozkladovou relaci:

DESCRIBE 01Trida;
+----------------+---------+------+-----+---------+-------+
| Field          | Type    | Null | Key | Default | Extra |
+----------------+---------+------+-----+---------+-------+
| IDtrida        | char(2) | NO   | PRI | NULL    |       |
| PocetZaku      | int(11) | NO   |     | NULL    |       |
| DomovskaUcebna | char(3) | NO   | MUL | NULL    |       |
| IDOboru        | int(11) | NO   | MUL | NULL    |       |
+----------------+---------+------+-----+---------+-------+

DESCRIBE 01TridaPredmet;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| IDTrida   | char(2) | NO   | PRI | NULL    |       |
| IDPredmet | int(11) | NO   | PRI | NULL    |       |
+-----------+---------+------+-----+---------+-------+

Záznamy vždy z každé relace zobrazit již umíme – nyní se zaměříme na to, abychom zobrazili údaje z více relací najednou.

Řekněme, že bychom chtěli vybrat název předmětu a ID třídy, která předmět má. Název předmětu máme jako atribut 01Predmet.Nazev, ID třídy máme uložen jako atribut 01TridaPredmet.IDTrida. Budeme tedy vybírat ze dvou relací – 01Predmet a 01TridaPredmet.

SELECT Nazev, IDTrida FROM 01Predmet 
INNER JOIN 01TridaPredmet 
   ON 01Predmet.IDPredmet = 01TridaPredmet.IDPredmet;

Všimněme si, že:

vybíráme pouze dva atributy (Nazev, IDTrida)
vybíráme z (levé) relace 01Predmet a (pravé) 01TridaPredmet
používáme INNER JOIN (tj. záznamy musejí být v obou relacích)
spojujeme relace pomocí atributu IDPredmet

Pojďme si ještě poznámky projít detailněji. Při JOINu je vždy první relace levá a druhá pravá, tj. FROM 01Predmet JOIN 01TridaPredmet nám říká, že první relace (01Predmet) je vlevo a druhá relace (01TridaPredmet) je vpravo – budeme to potřebovat především pro další typy JOINů.

Typy JOINů máme:

INNER JOIN – záznamy musejí být v obou relacích
LEFT JOIN – vypíše všechny záznamy z levé relace a odpovídající záznamy z pravé relace (jestliže neodpovídá nic, vypíše NULL) – tj. jestliže se předmět neučí v žádné třídě, bude v atributu IDTridy NULL
RIGHT JOIN – vypíše všechny záznamy z pravé relace a odpovídající záznamy z levé relace (jestliže nic neodpovídá, vypíše NULL)

Spojovací podmínka ON nám definuje, které dva atributy nám záznamy obě relace spojuje. Snažíme se, aby se atributy v jedné i v druhé relaci jmenovaly stejně, ale není to podmínka. Zde máme shodné informace v atributu 01Predmet.IDPredmet a 01TridaPredmet.IDPredmet.

Výběr ze tří relací (kardinalita M:N)

Nyní se zadání ještě trochu zpřesnilo – chceme vybrat Název předmětu, ID třídy a počet žáků.

Název předmětu máme uložený v atributu 01Predmet.Nazev, ID třídy v 01TridaPredmet.IDTrida a počet žáků v atributu 01Trida.PocetZaku. Budeme muset tedy spojit tři relace:

SELECT Nazev, 01TridaPredmet.IDTrida AS Trida, PocetZaku 
  FROM 01Predmet 
  INNER JOIN 01TridaPredmet 
     ON 01Predmet.IDPredmet = 01TridaPredmet.IDPredmet 
  INNER JOIN 01Trida 
     ON 01TridaPredmet.IDTrida = 01Trida.IDtrida;

+-----------------------------------+-------+-----------+
| Nazev                             | Trida | PocetZaku |
+-----------------------------------+-------+-----------+
| Anglická konverzace               | C1    |        30 |
| Anglická konverzace               | C2    |        30 |
| Anglická konverzace               | C3    |        30 |
| Anglická konverzace               | C4    |        24 |
...
+-----------------------------------+-------+-----------+

Všimněte si, že u atributu IDTrida jsme museli konkrétně specifikovat, z jaké relace jej chceme zobrazit (v tomto případě to je jedno, ale musí tam být nejprve název relace a až poté název atributu). Dále si všimněte, že jsme jej pomocí aliasu přejmenovali prostě na Trida.

Dotaz dokážeme ještě více zpřehlednit pomocí dalších aliasů, které nastavíme u jednotlivých relací:

SELECT p.Nazev, tp.IDTrida, t.PocetZaku
  FROM 01Predmet AS p
  INNER JOIN 01TridaPredmet AS tp 
     ON p.IDPredmet = tp.IDPredmet
  INNER JOIN 01Trida AS t 
     ON tp.IDTrida = t.IDTrida;