9. diel - MySQL krok za krokom - Dotazy cez viacero tabuliek (JOIN)

V predchádzajúcom kvíze, Kvíz - Výber dát, radenie a dátové typy v MySQL, sme si overili nadobudnuté skúsenosti z predchádzajúcich lekcií.

V MySQL tutoriále začneme pracovať na databáze pre redakčný systém, ukážeme si výber cez viacero tabuliek pomocou príkazu JOIN, INNER, LEFT a RIGHT JOIN.

Konceptuálny model

V nasledujúcich lekciách si v databáze vytvoríme taký zjednodušený ITnetwork. Pobavme sa najskôr o tom, ako to bude vyzerať. Dnes stihneme pochopiteľne len malú časť. Pretože obrázok niekedy povie viac ako tisíc slov, začnime práve ním:

To, čo vidíte, je tzv. konceptuálny model. Je vytvorený pomocou notácie (grafického jazyka) UML a v praxi sa takéto diagramy veľmi často tvoria predtým, než začneme písať nejaký kód. Dobre si tak najskôr rozmyslíme, čo to vlastne chceme robiť.

Vidíme, že v systéme figuruje používateľ, ktorý môže písať komentáre a články. Články spadajú do sekcií. Ide teda o databázu takého veľmi jednoduchého redakčného systému, ktorý si vďaka ITnetwork určite dokážete predstaviť.

Príprava tabuliek a dát

Dnes sa zameriame na dotazy cez viacero tabuliek. Poďme si najskôr nejaké tabuľky vytvoriť. Bohato nám budú stačiť používatelia a články.

Používatelia

Pretože používateľ bude vyzerať trochu inak, než vyzeral doteraz, založíme si tabuľku users znova. Tú súčasnú teda odstránime:

DROP TABLE `users`;

Následne vytvoríme tabuľku novú. Používateľ tu bude mať (okrem id) prezývku, e-mail a heslo:

CREATE TABLE `users` (
    `user_id` int AUTO_INCREMENT,
    `nickname` varchar(155),
    `email` varchar(155),
    `password` varchar(255),
    PRIMARY KEY (`user_id`)
);

Do používateľov si rovno nejakých vložíme:

INSERT INTO `users` (`nickname`, `email`, `password`) VALUES
('Michael', '[email protected]', 'dGg#@$DetA53d'),
('David', '[email protected]', '$#fdfgfHBKBKS'),
('Denny', '[email protected]', 'Jmls_aSW2RFss'),
('Emma', '[email protected]', 'fw8QT32qmcsld');

Články

Článok bude prepojený s používateľom, ktorý ho napísal, teda s jeho autorom. Tabuľky prepojíme tak, že do tabuľky articles pridáme stĺpec s id autora. Tam bude hodnota id používateľa (teda primárny kľúč z tabuľky users), ktorý článok napísal.

Hovoríme o väzbe 1:N (1 používateľ má N (niekoľko) článkov a každý článok patrí práve jednému používateľovi). Časť (tu článok) má vždy uložené id celku (tu používateľ) kam patrí.

Článok bude obsahovať (opäť okrem svojho id) id autora, krátky popis, url, kľúčové slová, titulok, obsah a dátum publikácie. Založme si tabuľku articles:

CREATE TABLE `articles` (
    `article_id` int AUTO_INCREMENT,
    `author_id` int,
    `description` varchar(155),
    `url` varchar(155),
    `title` varchar(155),
    `content` text,
    `published` datetime,
    PRIMARY KEY (`article_id`)
);

Za povšimnutie stojí asi len použitie typu TEXT pre text článku.

Ďalej pridáme články a k nim priradíme používateľov ako autorov. Vzal som 4 články tu z ITnetwork, ktoré som značne skrátil a zjednodušil. Dotaz bude nasledujúci:

INSERT INTO `articles` (`author_id`, `description`, `url`, `title`, `content`, `published`) VALUES
(1, "What's an algorithm? If you don't know, read this article.", 'what-is-an-algorithm', 'Algorithm', "When we talk about algorithms, let's agree on what an algorithm is all about. Simply put, the algorithm is a guide to solving a problem. When we look at it from a human perspective, the algorithm might be a guide to get up in the morning. Even if it sounds simple, it's quite a problem. Computers are machines and they do not think. Therefore, we must describe all the steps of the algorithm. This brings us to the first property of the algorithm - it must be elementary (consisting of a finite number of simple and easy-to-understand steps, i.e. commands). 'Get out of bed' is certainly not an algorithm. 'Open your eyes, take out the blanket, sit down, put your feet on the floor and rise' - this sounds quite detailed and would be a true algorithm. But we'll probably work in IT, so we'll solve problems of sorting elements by their value or of searching for an element based on its content. These are the two basic tasks that computers do most often and which need to be thought through and optimized to take as little time as possible. As another example of algorithm, it could be also to solve a quadratic equation or to solve a sudoku.", '2012-3-21'),
(2, 'Bacteria is a cellular automaton in combination with a game.', 'bacteria-cellular-automaton', 'Bacteria', 'Bacteria is a cellular automaton developed by the British mathematician John Horton Conway in 1970. This whole game is controlled by four simple rules:/n/n
1. A living bacteria with fewer than two living neighbors die./n
2. A living bacteria with more than three living neighbors dies due to overgrowth./n
3. A living bacteria with two or three living neighbors survives without a change to the next generation./n
4. A dead bacteria with exactly three living neighbors becomes alive again./n
With a right initial placement of the bacteria, these seemingly primitive rules can create walking formations, clusters "firing" walking fives, surprisingly complex symmetrical explosions, oscillators (periodically oscillating groups), or the infinite spectacle of how complex and perfect shapes can be created by these four conditions. The entire program is conceived as a game, where your goal is to create a colony living as long as possible.', '2012-2-14'),
(3, 'Cheese Mouse is a relaxing 2D game.', 'cheese-mouse-relaxing-platformer', 'Cheese Mouse', 'Cheese mouse is a 2D game with a "hot island atmosphere" where you control a mouse and you have to get to the cheese. But there are lots of pitfalls and enemies like snakes, rats, piranhas, robots, mummies, and all sorts of creatures. I created the game with several colorful worlds at the elementary school with Veisen and it ended up at the 2nd place in the Bonusweb game competition, where it won $200. It was created in Game Maker through summer holidays, in my childhood, which greatly influenced its graphic design. I like to play it sometimes to relax and for better mood.', '2004-6-22'),
(2, 'Pacman is a remake of the iconic game.', 'pacman-remake', 'Pacman', "This is an absolutely basic version of this game including a level editor, so you can create your own levels. Over time, I'm going to modify it a bit and add some new features, fullscreen and better graphics. The engine of the game will also be the core of my new Geckon man project, which is still in the writing stage.", '2011-6-3');

Dotazovanie cez viacero tabuliek

Teraz máme v databáze články a k nim priradených používateľov. Poďme si urobiť dotaz cez tieto 2 tabuľky, získajme články a k nim pripojme prezývky ich používateľov. Slovo pripojme som nepoužil náhodou, príkaz pre spojenie 2 tabuliek sa totiž volá JOIN. Napíšme si dotaz a potom si ho vysvetlíme. Dotazy už budeme písať na viac riadkov, aby sme sa v tom vyznali:

SELECT `title`, `nickname`
FROM `articles`
JOIN `users` ON `author_id` = `user_id`
ORDER BY `nickname`;

Výsledok:

Bacteria        David
Pacman          David
Cheese Mouse    Denny
Algorithm       Michael

Na prvom riadku príkazu SELECT pracujeme so stĺpcami úplne rovnako, ako keby boli v jednej tabuľke, jednoducho vymenujeme, čo nás zaujíma. Keďže vyberáme články a k nim pripájame používateľov, budeme vyberať z tabuľky articles. Pripojenie dát z inej tabuľky urobíme pomocou príkazu JOIN, kde uvedieme tabuľku, ktorú pripájame, a potom klauzulu ON. Klauzula ON je podobná ako WHERE, len platí pre pripájanú tabuľku a nie pre tú, z ktorej primárne vyberáme. V podmienke uvedieme, aby sa ku každému článku pripojil ten používateľ, ktorého user_id je uvedené v stĺpci author_id. Výsledok sme zoradili podľa prezývky používateľov. Keby sme chceli len nejaké články, normálne by sme pred ORDER BY uviedli ešte WHERE, ako sme zvyknutí.

INNER JOIN a OUTER JOIN

INNER (vnútorný) a OUTER (vonkajší) JOIN sú 2 typy príkazu JOIN. Fungujú úplne rovnako, jediný rozdiel je v tom, čo sa stane, keď položka, na ktorú sa väzba odkazuje, neexistuje.

INNER JOIN

Ak uvedieme v SQL dotaze iba JOIN, pokladá ho MySQL databáza za tzv. INNER JOIN. Ak by v našom prípade neexistoval používateľ s id, ktoré je pri článku uvedené, článok bez používateľa by vôbec nebol vo výsledkoch obsiahnutý. Väzba je nerozdeliteľná.

Poďme si to skúsiť, pridajme si článok, ktorý bude odkazovať na id neexistujúceho používateľa:

INSERT INTO `articles` (`author_id`, `description`, `url`, `title`, `content`, `published`) VALUES
(99, 'An article with a non-existent user to test different JOINS.', 'article-without-user', 'Article without an author', 'This article is assigned to a nonexistent user with the ID of 99 and is used to test different types of JOINS in the MySQL database.', '2012-10-21');

Vložený článok sa odkazuje na používateľa s user_id 99, ktorý v databáze nie je. Spustite si teraz znovu náš SQL dotaz. Pre prehľadnosť je lepšie uviesť, že chceme INNER JOIN:

SELECT `title`, `nickname`
FROM `articles`
INNER JOIN `users` ON `author_id` = `user_id`
ORDER BY `nickname`;

Výsledok:

Bacteria        David
Pacman          David
Cheese Mouse    Denny
Algorithm       Michael

Výsledok je stále rovnaký, článok bez autora medzi výsledkami nie je.

LEFT OUTER JOIN

Vonkajší JOIN umožňuje vyberať aj tie výsledky, ktoré sa nepodarilo spojiť z dôvodu chýbajúcich položiek. Skúsme si tzv. LEFT JOIN, ktorý výsledok uzná, ak existuje ľavá časť väzby (tu článok) a pravá (tá pripájaná, tu používateľ) neexistuje. Do hodnôt stĺpcov z pripájanej časti sa vloží NULL:

SELECT `title`, `nickname`
FROM `articles`
LEFT JOIN `users` ON `author_id` = `user_id`
ORDER BY `nickname`;

Výsledok:

Article without an author    NULL
Pacman                       David
Bacteria                     David
Cheese Mouse                 Denny
Algorithm                    Michael

Vidíme, že článok sa aj tak vybral, hoci sa nepodarilo vybrať pravú časť (teda tú pripájanú, používateľa). Pred spájaním tabuliek je dobré sa zamyslieť, či nastane prípad, keď sa spojenie nepodarí, a čo v tom prípade chceme robiť. Pri článku by sa toto v reáli stať asi nemalo.

RIGHT OUTER JOIN

Podobne ako ľavý vonkajší JOIN uznal väzbu v prípade, že ľavá časť existovala, pravý JOIN to urobí naopak. Ak bude existovať používateľ (pravá, pripájaná časť) a nebude k nemu existovať článok (ľavá časť), bude rovnako v tabuľke zahrnutý. Osobne som tento JOIN ešte nepoužil. V tabuľke jedného takéhoto používateľa máme, je ním používateľ Emma. Skúsme si teda RIGHT JOIN:

SELECT `title`, `nickname`
FROM `articles`
RIGHT JOIN `users` ON `author_id` = `user_id`
ORDER BY `nickname`;

Výsledok:

Pacman          David
Bacteria        David
Cheese Mouse    Denny
NULL            Emma
Algorithm       Michael

Podľa očakávania zmizol Article without an author a objavila sa Emma.

Ešte nejaký JOIN by sme určite v MySQL našli, ale na naše účely nám toto bohato stačí.

Wherovanie

Teoreticky sa môžeme JOINom vyhýbať a používať namiesto nich jednoducho len klauzulu FROM a WHERE. Vo FROM uvedieme viacero tabuliek oddelených čiarkami. Vo WHERE špecifikujeme podmienku spojenia tabuliek. Databáza si v ideálnom prípade takýto dotaz najprv prevedie na INNER JOIN a potom ho spracuje:

SELECT `title`, `nickname`
FROM `articles`, `users`
WHERE `author_id` = `user_id`
ORDER BY `nickname`;

Výsledok je teda rovnaký ako pre INNER JOIN:

Pacman          David
Bacteria        David
Cheese Mouse    Denny
Algorithm       Michael

Nevýhodou wherovania je, že tak neurobíme všetky JOINy a v určitých prípadoch môžu byť dotazy menej optimalizované. Nikdy nevieme, ako dotaz databázy optimalizuje a optimalizácia sa bude líšiť podľa typu databázy. Tento spôsob berte skôr ako zaujímavosť a nepoužívajte ho.

V nasledujúcom cvičení, Riešené úlohy k 8.-9. lekcii MySQL/MariaDB, si precvičíme nadobudnuté skúsenosti z predchádzajúcich lekcií.