Databázové operace

V relačních databázích pracujeme s relací nebo s několika relacemi – často ale nepotřebujeme vidět celou relaci (všechny atributy a všechny záznamy) tak, jak ji máme uloženou. Potřebujeme z ní „vyříznout“ jen určitá data, nebo ji spojit s jinou a zobrazit jen některé vybrané atributy nebo jen některé záznamy. K tomu slouží databázové operace.

Operace nad jednou relací

Tyto operace nám pomáhají zredukovat množství dat, které z jedné relace můžeme dostat. Ne všechny relace jsou tak jednoduché, jak je vytváříme při školních cvičeních – představte si, že normální relace má klidně i několik stovek atributů a miliony nebo miliardy záznamů. V těchto případech prostě nelze zadat „zobraz všechna data z relace“ (SELECT * FROM Relace).

Projekce (výběr atributů)

Operace projekce slouží k vertikálnímu ořezání relace, tj. potlačení atributů, které nechceme vidět. Představme si na příkladu relace Students(#SSID, FirstName, LastName, BirthDate, JoinedDate, Street, HouseNr, City, ZIP, Status), že zadání je zobrazit pouze jméno a příjmení. Projekci tedy provedeme:

SELECT FirstName, LastName FROM Students;

Tím, že za klíčové slovo SELECT nenapíšeme hvězdičku, ale pouze jeden nebo více atributů, dotaz vrátí pouze vyjmenované atributy a atributy, které jsme nevyjmenovali, potlačí.

Selekce (výběr záznamů)

Operace selekce slouží k horizontálnímu ořezání relace, tj. potlačení záznamů, které nechceme vidět. Pravidlo zní, že počet zobrazených záznam je ve výsledku menší nebo roven počtu záznamů v původní relaci. Přestavme si, že v relaci Students máme všechny žáky, kteří kdy na školu chodili. My ale chceme zobrazit pouze žáky, kteří mají Status „A“ (aktivní). Selekci tedy provedeme:

SELECT * FROM Students WHERE Status = "A";

Část, která následuje za WHERE, nazýváme restrikce. Restrikce se může skládat z jedné nebo několika částí – části pak spojujeme klasickými logickými operátory AND, resp. OR. Představme si například, že zadání zní, abychom vybrali všechny aktivní žáky z Rakovníka. Selekce by tedy vypadala následovně:

SELECT * FROM Students WHERE Status = "A" AND City = "Rakovník";

Spojování relací (JOIN)

Máme-li v databázovém modelu data, která chceme vybírat, uložena v několika relací, je třeba relace spojit. Základním typem spojení je tzv. kartézský součin (značíme pomocí ×). Vezměte si příklad, že v katalogu triček máme relaci s barvami a druhou relaci s velikostmi. Nyní chceme vypsat všechny vzájemné kombinace:

+----+-----------+
| ID | barva     |
+----+-----------+
|  1 | bílá      |
|  2 | červená   |
|  3 | khaki     |
+----+-----------+
+----------+--------+
| velikost | popis  |
+----------+--------+
| L        | Large  |
| M        | Medium |
| S        | Small  |
+----------+--------+

SELECT * FROM 98_barvy JOIN 98_velikosti;
+----+-----------+----------+--------+
| ID | barva     | velikost | popis  |
+----+-----------+----------+--------+
|  1 | bílá      | L        | Large  |
|  2 | červená   | L        | Large  |
|  3 | khaki     | L        | Large  |
|  1 | bílá      | M        | Medium |
|  2 | červená   | M        | Medium |
|  3 | khaki     | M        | Medium |
|  1 | bílá      | S        | Small  |
|  2 | červená   | S        | Small  |
|  3 | khaki     | S        | Small  |
+----+-----------+----------+--------+

Vidíme tedy, že prostý JOIN (nebo též CROSS JOIN) nám vypíše 9 záznamů (3 záznamy z relace barvy krát 3 záznamy z relace velikosti). Jednoduše bychom operaci mohli zapsat jako barvy × velikosti. Použití kartézského součinu je v databázích výjimečné – vede často k explozi počtu výsledných záznamů – a často je výsledkem chyby, že prostě zapomeneme dodat společný atribut (viz dále).

Nejčastěji spojení definujeme na základě společného atributu, který se vyskytuje v obou relacích (nejčastěji primární a cizí klíč). Představme si například, že chceme vybrat jméno a příjmení zákazníka společně s datem objednávky a celkovou cenou. Data máme uložena v relacích Customers(#CustomerEmail, FirstName, LastName, Street, HouseNr, City, ZIP, CreationDate, Status) a Orders(#OrderID, CreationDate, TotalPrice, Status, CustomerEmail). Toto konkrétní spojení bychom mohli realizovat pomocí následujícího příkazu:

SELECT FirstName, LastName, Orders.CreationDate, TotalPrice FROM Customers JOIN Orders ON Customers.CustomerEmail = Orders.CustomerEmail;

Všimněte si, že nejprve vybíráme z relace Customers a k ní přidáváme data z relace Orders. V tom případě relace Customers je vlevo a relace Orders vpravo. Zapamatujme si to pro další využití.

Spojení máme dva, resp. tři typy:

• INNER JOIN (vnitřní) – vybere pouze průnik dat z obou relací
• OUTER JOIN (vnější)
  • LEFT JOIN – vybere všechny záznamy z levé relace a odpovídající záznamy z relace pravé
  • RIGHT JOIN – vybere všechny záznamy z pravé relace a odpovídající záznamy z relace levé

INNER JOIN

Jedná se o nejčastější typ spojení, které vrátí pouze ty záznamy, které existují v obou relacích. V našem případě nám vrátí pouze ty zákazníky, kteří mají alespoň jednu objednávku (resp. ty objednávky, které mají přiřazeného zákazníka).

Představme si pro účely tohoto příkladu například následující hodnoty v relaci Customers:

A následující hodnoty v relaci Orders:

INNER JOIN jak je uveden výše nám vrátí pouze projekci (pouze atributy FirstName, LastName z relace Customers a CreationDate a TotalPrice z relace Orders) a pouze takové záznamy, které existují na obou stranách, čili:

Lucii Veselou nám tento typ spojení vůbec nevrátí, neboť nemá vytvořenou žádnou objednávku. Také nevidíme objednávku 12003, a to z toho důvodu, že zákazník s e-mailem karel.holy@gmail.com již v relaci Customers neexistuje.

LEFT JOIN

Zde má prioritu levá relace (v našem příkladu Customers). Dotaz vrátí všechny záznamy z levé relace a u těch, které nemají odpovídající záznam v pravé relaci, vyplní NULL:

V případě, že by někteří zákazníci měli více objednávek, vrátí dotaz více záznamů, u kterých budou stejná data zákazníků.

Někdy se může stát, že budeme potřebovat spojit relaci s tou samou relací – říkáme tomu rekurzivní vztah. Ukážeme si nyní na příkladu relace Zamestnanci, kde kromě primárního klíče zamestnanec_id máme také cizí klíč nadrizeny, který odkazuje zpět na stejnou relaci Zamestnanci a atribut zamestnanec_id. Prostý select vypadá následovně:

SELECT * FROM 02zamestnanci;
+----------------+-------------+----------------+-----------------------------+-----------+
| zamestnanec_id | jmeno       | prijmeni       | pozice                      | nadrizeny |
+----------------+-------------+----------------+-----------------------------+-----------+
|              1 | Jan         | Jirátko        | ředitel                     |      NULL |
|              2 | Jindřiška   | Kopřivová      | zástupkyně                  |         1 |
|              3 | Tereza      | Provazníková   | předsedkyně PK humanitní    |         1 |
|              4 | Josef       | Kýna           | předseda PK IT              |         1 |
|              5 | Petr        | Lelovski       | odborný učitel IT           |         4 |
|              6 | Václav      | Němec          | odborný učitel IT           |         4 |
+----------------+-------------+----------------+-----------------------------+-----------+

Budeme-li ale chtít vypsat příjmení zaměstnance a příjmení jeho nadřízeného, budeme muset udělat LEFT JOIN (abychom vybrali i pana ředitele, který nadřízeného nemá):

SELECT z.prijmeni, n.prijmeni AS nadrizeny FROM 02zamestnanci z LEFT JOIN 02zamestnanci n ON z.nadrizeny
= n.zamestnanec_id;
+----------------+-----------+
| prijmeni       | nadrizeny |
+----------------+-----------+
| Jirátko        | NULL      |
| Kopřivová      | Jirátko   |
| Provazníková   | Jirátko   |
| Kýna           | Jirátko   |
| Lelovski       | Kýna      |
| Němec          | Kýna      |
+----------------+-----------+

RIGHT JOIN

Zde má prioritu pravá relace (v našem příkladu Orders). Dotaz vrátí všechny záznamy z pravé relace a u těch, které nemají odpovídající záznam v levé relaci, vyplní NULL:

Množinové operace

Někdy potřebujeme pracovat s výsledky dotazů jako s matematickými množinami. Aby takové operace fungovaly, musejí mít oba porovnávané výsledky dotazů stejnou strukturu (stejný počet a typ atributů).

Představme si opět na příkladu, že máme relaci Products(#ProdID, ProductName, Price, StockAmount, Discount) – kde jsou uložené všechny produkty v našem e-shopu. Pomocí jednoduchého dotazu bychom chtěli získat všechny produkty, které jsou na skladě a zároveň nejsou ve slevě. K tomu bychom mohli použít množinovou operaci rozdíl (EXCEPT):

SELECT ProductName, Price FROM Products WHERE StockAmount > 0
EXCEPT SELECT ProductName, Price FROM Products WHERE Discount != 0;

Všimněme si, že první SELECT vybere všechny produkty, které jsou na skladě (jejich skladové množství je větší než nula). Druhý SELECT vybere jenom takové produkty, které mají nastavenou slevu. Vybíráme stejné dva atributy (ProductName, Price), takže můžeme použít množinovou operaci. EXCEPT nám udělá rozdíl, tj. od všech produktů na skladě odebere záznamy se slevou (tj. ve výsledku budeme mít všechny záznamy na skladě beze slevy).

Stejně tak můžeme používat například množinové operace UNION (sloučení) nebo INTERSECT (průnik).