7. diel - MS-SQL krok za krokom: Otázky cez viac tabuliek (JOIN)
V predchádzajúcom cvičení, Riešené úlohy k 1.-5. lekciu MS-SQL, sme si precvičili získané skúsenosti z predchádzajúcich lekcií.
Dnes v MS-SQL tutoriálu začneme pracovať na jednoduchom redakčnom systéme, ktorý môže pripomínať ten tu na ITnetwork. Ukážeme si dopytovania cez viac tabuliek.
Konceptuálny model
V nasledujúcich dieloch si teda v databáze vytvoríme taký zjednodušený ITnetwork. Pobavte sa najprv 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.
Čo vidíte je tzv. Konceptuálne 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 najprv rozmyslíme, čo že to vlastne chceme urobiť.
Vidíme, že v systéme figuruje užívateľ, ktorý môže písať komentáre a články. Články spadajú do sekcií. Jedná sa teda o databáze takého veľmi jednoduchého redakčného systému, ktorý si vďaka ITnetwork iste dokážete predstaviť.
Príprava tabuliek a dát
Dnes sa zameriame na otázky cez viac tabuliek. Poďme si najprv nejaké tabuľky vytvoriť. Bohato nám budú stačiť užívatelia a články.
Užívatelia
Pretože užívateľ bude vyzerať trochu inak, než nám vyzeral doteraz,
založíme si tabuľku Uzivatele
znova. Tú súčasnú teda
dropneme:
DROP TABLE [Uzivatele];
Následne vytvoríme tabuľku novú. Užívateľ tu bude mať (okrem
Id
) prezývku, mail a heslo:
CREATE TABLE [Uzivatele] ( [Id] INT IDENTITY, [Prezdivka] NVARCHAR(155), [Email] NVARCHAR(155), [Heslo] NVARCHAR(255), PRIMARY KEY ([Id]) );
Pozn .: Môže chvíľu trvať, než sa nová tabuľka v Server Exploreri zobrazí.
Do užívateľov si rovno nejaké vložíme:
INSERT INTO [Uzivatele] ([Prezdivka], [Email], [Heslo]) VALUES ('Míša', '[email protected]', 'dGg#@$DetA53d'), ('David', '[email protected]', '$#fdfgfHBKBKS'), ('Denny', '[email protected]', 'Jmls_aSW2RFss'), ('Ema', '[email protected]', 'fw8QT32qmcsld');
Články
Článok bude prepojený s užívateľom, ktorý ho napísal, teda s jeho
autorom. Tabuľky prepojíme tak, že do tabuľky Clanky
pridáme
stĺpec s Id autora. Tam bude hodnota Id užívateľa (teda primárny kľúč z
tabuľky Uzivatele
), 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 uží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 Clanky
:
CREATE TABLE [Clanky]( [Id] INT IDENTITY, [AutorId] INT, [Popis] NVARCHAR(155), [Url] NVARCHAR(155), [KlicovaSlova] NVARCHAR(155), [Titulek] NVARCHAR(155), [Obsah] NVARCHAR(MAX), [Publikovano] DATETIME, PRIMARY KEY ([Id]) );
Za povšimnutie stojí asi len použitie typu NVARCHAR(MAX)
pre
text článku.
Ďalej pridáme články ak nim priradíme používateľa ako autormi. Vzal som 4 články tu z ITnetwork, ktoré som značne skrátil a zjednodušil. Otázka bude nasledujúci:
INSERT INTO [Clanky] ([AutorId], [Popis], [Url], [KlicovaSlova], [Titulek], [Obsah], [Publikovano]) VALUES (1, 'Co je to algoritmus? Pokud to nevíte, přečtěte si tento článek.', 'co-je-to-algoritmus', 'algoritmus, co je to, vysvětlení', 'Algoritmus', 'Když se bavíme o algoritmech, pojďme se tedy shodnout na tom, co ten algoritmus vůbec je. Jednoduše řečeno, algoritmus je návod k řešení nějakého problému. Když se na to podíváme z lidského pohledu, algoritmus by mohl být třeba návod, jak ráno vstát. I když to zní jednoduše, je to docela problém. Počítače jsou totiž stroje a ty nemyslí. Musíme tedy dopodrobna popsat všechny kroky algoritmu. Tím se dostáváme k první vlastnosti algoritmu - musí být elementární (skládat se z konečného počtu jednoduchých a snadno srozumitelných kroků, tedy příkazů). "Vstaň z postele" určitě není algoritmus. "Otevři oči, sundej peřinu, posaň se, dej nohy na zem a stoupni si" - to už zní docela podrobně a jednalo by se tedy o pravý algoritmus. My se však budeme pohybovat v IT, takže budeme řešit problémy jako seřaď prvky podle velikosti nebo vyhledej prvek podle jeho obsahu. To jsou totiž 2 základní úlohy, které počítače dělají nejčastěji a které je potřeba dokonale promýšlet a optimalizovat, aby trvaly co nejkratší dobu. Z dalších příkladů algoritmů mě napadá třeba vyřeš kvadratickou rovnici nebo vyřeš sudoku.', '2012-3-21'), (2, 'Bakterie jsou obdoba buněčného automatu v kombinaci s hrou.', 'bakterie-bunecny-automat', 'bakterie, automat, algoritmus', 'Bakterie', 'Bakterie jsou obdoba buněčného automatu, který vymyslel britský matematik John Horton Conway v roce 1970. Celou tuto hru řídí čtyři jednoduchá pravidla:/n/n 1. Živá bakterie s méně, než dvěma živými sousedy umírá./n 2. Živá bakterie s více, než třemi živými sousedy umírá na přemnožení./n 3. Živá bakterie s dvoumi nebo třemi sousedy přežívá beze změny do další generace./n 4. Mrtvá bakterie, s přesně třemi živými sousedy, opět ožívá./n Tyto zdánlivě naprosto primitivní pravidla dokáží za správného počátečního rozmístění bakterií vytvořit pochodující skupinky, shluky "vystřelující" pochodující pětice, překvapivě složité souměrné exploze, oscilátory (periodicky kmitající skupinky), či nekonečnou podívanou na to, jak složité a dokonalé obrazce dokáží tyto dvě podmínky vytvořit. Celý program je koncipován jako hra, máte za úkol vytvořit co nejdéle žijící kolonii. <a href="soubory/bakterie.zip" ', '2012-2-14'), (3, 'Cheese Mouse je oddechová plošinovka.', 'cheese-mouse-oddechova-plosinovka', 'myš, sýr, hra', 'Cheese Mouse', 'Cheese mouse je plošinovka s "horkou ostrovní atmosférou", kde ovládáte myš a musíte se dostat k sýru. V tom vám ale brání nejrůznější nástrahy a nepřatelé jako hadi, krysy, pirane, ale i roboti, mumie a nejrůznější havěť. Hru s několika petrobarevnými světy jsem dělal ještě na základní škole s Veisenem a může se pochlubit 2. místem v Bonusweb game competition, kde vyhrála 5.000 Kč. Vznikala v Game makeru o letních prázdninách, ještě v bezstarostném dětství, což značně ovlivnilo její grafickou stránku. Rád si ji občas zahraji na odreagování a zlepšní nálady. <a href="soubory/cheesemouse.zip" />', '2004-6-22'), (2, 'Pacman je remake kultovní hry.', 'pacman-remake', 'pacman, remake, pampuch, hra, zdarma', 'Pacman', 'Jedná se o naprosto základní verzi této hry s editorem levelů, takže si můžete vytvořit svá vlastní kola. Postupem času ji hodlám ještě trochu upravit a přidat nějaké nové prvky, fullscreen a lepší grafiku. Engine hry bude také základem mého nového projektu Geckon man, který je zatím ve fázi psaní scénáře. <a href="soubory/pacman.zip" />', '2011-6-3');
Otázky cez viac tabuliek
Teraz máme v databáze články ak nim priradené užívateľa. Poďme si
urobiť dotaz cez tieto 2 tabuľky, získajte články ak nim pripojme prezývky
ich užívateľov. Slovo pripojme som nepoužil náhodou, príkaz pre spojenie 2
tabuliek sa totiž volá JOIN
. Napíšme si otázku a potom si ho
vysvetlíme. Otázky už budeme písať na viac riadkov, aby sme sa v tom
vyznali.
SELECT [Titulek], [Prezdivka] FROM [Clanky] JOIN [Uzivatele] ON [Clanky].[AutorId] = [Uzivatele].[Id] ORDER BY [Prezdivka];
výsledok:
titulok | Prezdivka |
---|---|
baktérie | David |
pacman | David |
Cheese Mouse | Denny |
algoritmus | Míša |
SELECT
pracujeme so stĺpci úplne
rovnako, ako keby boli v jednej tabuľke, jednoducho vymenujeme, čo nás
zaujíma. Keďže vyberáme články ak nim pripájame užívateľa, budeme
vyberať z tabuľky Clanky
. Pripojenie dát z inej tabuľky
urobíme pomocou príkazu JOIN
, kde uvedieme tabuľku, ktorú
pripájame, a potom klauzulu ON
. Ak majú v dvoch tabuľkách
stĺpce rovnaké názvy, predsadíme stĺpec ešte názvom tabuľky, do ktorej
patrí a oddelíme bodkou. Klauzula ON
je podobná ako
WHERE
, len platí pre pripojenú 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 užívateľ, ktorého Id
je uvedené v stĺpci
AutorId
. Výsledok sme zoradili podľa prezývky uží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šie)
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 len JOIN
, pokladá ho MS-SQL databázy
za tzv. INNER JOIN
. Ak by v našom prípade neexistoval užívateľ
s Id
, ktoré je u článku uvedené, článok bez uží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 užívateľa:
INSERT INTO [Clanky] ([AutorId], [Popis], [Url], [KlicovaSlova], [Titulek], [Obsah], [Publikovano]) VALUES (99, 'Článek s neexistujím uživatelem slouží pro vyzkoušení typů JOINů.', 'clanek-bez-autora', 'clanek, join, autor, chybejici', 'Článek bez autora', 'Tento článek je přiřazen neexistujícímu uživateli s ID 99 a slouží k vyzkoušení různých typů JOINů v MS-SQL databázi.', '2012-10-21');
Vložený článok sa odvoláva na užívateľov s Id
99, ktorý
v databáze nie je. Spustite si teraz znovu náš SQL dotaz s JOIN. Pre
prehľadnosť môžeme uviesť, že chceme INNER JOIN
.
SELECT [Titulek], [Prezdivka] FROM [Clanky] INNER JOIN [Uzivatele] ON [Clanky].[AutorId] = [Uzivatele].[Id] ORDER BY [Prezdivka];
výsledok:
titulok | Prezdivka |
---|---|
baktérie | David |
pacman | David |
Cheese Mouse | Denny |
algoritmus | Míša |
LEFT OUTER JOIN
Vonkajšie join umožňujú 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 užívateľ) neexistuje. Do hodnôt
stĺpcov z pripojovanej časti sa vloží NULL
.
SELECT [Titulek], [Prezdivka] FROM [Clanky] LEFT JOIN [Uzivatele] ON [Clanky].[AutorId] = [Uzivatele].[Id] ORDER BY [Prezdivka];
výsledok:
titulok | Prezdivka |
---|---|
Článok bez autora | NULL |
baktérie | David |
pacman | David |
Cheese Mouse | Denny |
algoritmus | Míša |
Right OUTER JOIN
Podobne ako ľavý vonkajší JOIN
uznal väzbu v prípade, že
ľavá časť existovala, pravý JOIN
to urobí naopak. Pokiaľ
bude existovať uží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ého
používateľa máme, je ním používateľ Ema. Skúsme si teda
RIGHT JOIN
:
SELECT [Titulek], [Prezdivka] FROM [Clanky] RIGHT JOIN [Uzivatele] ON [Clanky].[AutorId] = [Uzivatele].[Id] ORDER BY [Prezdivka];
výsledok:
titulok | Prezdivka |
---|---|
baktérie | David |
pacman | David |
Cheese Mouse | Denny |
NULL | ema |
algoritmus | Míša |
Ešte nejaké join by sme určite v MS-SQL našli, ale pre naše účely nám toto bohato stačí.
Wherování
Teoreticky sa môžeme JOINům vyhýbať a používať namiesto nich
jednoducho len klauzulu FROM
a WHERE
. Vo
FROM
uvedieme viac tabuliek oddelených čiarkami. Vo
WHERE
špecifikujeme podmienku spojenie tabuliek. Databáza si v
ideálnom prípade takýto dotaz najprv prevedie na INNER JOIN
a
potom ho spracuje.
SELECT [Titulek], [Prezdivka] FROM [Clanky], [Uzivatele] WHERE [Clanky].[AutorId] = [Uzivatele].[Id] ORDER BY [Prezdivka];
Výsledok je teda rovnaký ako pri INNER JOIN:
titulok | Prezdivka |
---|---|
baktérie | David |
pacman | David |
Cheese Mouse | Denny |
algoritmus | Míša |
V budúcej lekcii, MS-SQL krok za krokom: Ďalšie otázky a väzba M: N , budeme pokračovať v dotazoch cez viac tabuliek a pridáme si do našej databázy ďalšiu časť redakčného systému.