8. diel - MS-SQL krok za krokom: Ďalšie otázky a väzba M: N

V minulej lekcii, MS-SQL krok za krokom: Otázky cez viac tabuliek (JOIN) , sme sa naučili JOIN, teda otázky nad viacerými tabuľkami.

V tomto duchu budeme dnes v MS-SQL tutoriálu pokračovať, založíme si ešte jednu tabuľku s komentármi a potom si povieme niečo o väzbe M: N.

Tabuľka komentáre

Pokračujme v našom redakčnom systéme a vytvorme si tabuľku Komentare. Komentár sa (podobne ako článok) viaže na používateľa. Viaže sa ale tiež na článok. Máme tu teda dve väzby 1: N. Jeden článok má N komentárov, jeden užívateľ má N komentárov. Komentár patrí vždy len jednému užívateľovi a jednému článku.

Keďže komentár je časť a patria do dvoch celkov (k článku ak užívateľovi), bude obsahovať 2 stĺpce s Id článku a Id komentáre. Týmto stĺpcom s Id položky z cudzej tabuľky hovoríme cudzie kľúče. Už ich poznáme z minulosti (u článku bol cudzí kľúč užívateľa), len sme si nepovedali, že sa im tak hovorí. Okrem nich bude mať komentár text a dátum.

CREATE TABLE [Komentare] (
    [Id] INT IDENTITY,
    [ClanekId] INT,
    [UzivatelId] INT,
    [Obsah] NVARCHAR(MAX),
    [Datum] DATETIME,
    PRIMARY KEY ([Id])
);

Vložme si nejaké komentáre:

INSERT INTO [Komentare] ([UzivatelId], [Obsah], [Datum], [ClanekId]) VALUES
(4, 'Super článek!', '2012-4-6', 1),
(4, 'Jak je tedy přesně ta podmínka pro vznik bakterie?', '2011-1-28', 2),
(1, 'Zasekla jsem se v této hře, kde najdu klíč do 3. levelu?', '2011-9-30', 3),
(4, 'Jak rozjedu plošinu v 5. levelu?', '2010-8-1', 3),
(1, 'Umřel jsem a nemám hru uloženou, co mám dělat?', '2012-4-14', 4),
(3, 'Dobrá hra!', '2012-4-6', 4),
(3, 'Nerozumím tomu!', '2011-4-6', 1),
(2, 'Super článek!', '2012-5-6', 1);

Poďme si skúsiť vypísať všetky komentáre spolu s ich autormi a články, ku ktorým patrí. JOIN už vieme, tento dotaz bude obdobný, len bude rovno cez 2 tabuľky naraz, čiže s dvoma JOIN. Join môžeme mať v dotaze samozrejme koľko chceme, ale mali by sme pamätať na to, že to nie sú pre databázu úplne jednoduché operácie.

SELECT [Uzivatele].[Prezdivka], [Komentare].[Obsah], [Clanky].[Titulek]
FROM [Komentare]
INNER JOIN [Uzivatele] ON [Uzivatele].[Id] = [Komentare].[UzivatelId]
INNER JOIN [Clanky] ON [Clanky].[Id] = [Komentare].[ClanekId]
ORDER BY [Komentare].[Datum];

výsledok:

Prezdivka	obsah	titulok
ema	Ako rozbehnem plošinu v 5. leveli?	Cheese Mouse
ema	Ako je teda presne tá podmienka ...	baktérie
Denny	Nerozumiem tomu!	algoritmus
Míša	Zasekla som sa v tejto hre ...	Cheese Mouse
ema	Super článok!	algoritmus
Denny	Dobrá hra!	pacman
Míša	Umrel som a nemám hru uloženú ...	pacman
David	Super článok!	algoritmus

Všimnite si, že sme všetky stĺpce předsadili názvom tabuľky. Malo by sa to tak robiť vždy. Tu konkrétne sa volá Obsah obsah komentáre i obsah článku. U zložitejšie štruktúry databázy sa toto stáva sa stĺpci ako Datum, Id, Autor...

Skúste si otázku bez názvov tabuliek, nebude fungovať.

-- tento dotaz nebude fungovat
SELECT [Uzivatele].[Prezdivka], [Komentare].[Obsah], [Clanky].[Titulek]
FROM [Komentare]
INNER JOIN [Uzivatele] ON [Id] = [UzivatelId]
INNER JOIN [Clanky] ON [Id] = [ClanekId]
ORDER BY [Komentare].[Datum];

MS-SQL vyhodí hlášku:

Msg 209, Level 16, State 1, Line 4
Ambiguous column name 'Id'.
Msg 209, Level 16, State 1, Line 5
Ambiguous column name 'Id'.

Pri zložitejších otázok cez viac tabuliek môže byť výhodné použiť aliasy. Aliasy už tiež vieme, deklarujú sa cez kľúčové slovo AS. Použime je v tomto dotazu.

SELECT [U].[Prezdivka], [K].[Obsah], [C].[Titulek]
FROM [Komentare] AS [K]
INNER JOIN [Uzivatele] AS [U] ON [U].[Id] = [K].[UzivatelId]
INNER JOIN [Clanky] AS [C] ON [C].[Id] = [K].[ClanekId]
ORDER BY [K].[Datum];

Otázka vyzerá oveľa prehľadnejšie, nemusíme opisovať názvy tabuliek. Skrátili sme si ich, tu len na počiatočné písmená.

Sekcia

Pokračujme v štruktúre redakčného systému. Články sa radí do sekcií, tie sú uložené v tabuľke Sekce. Je tu však malý háčik. Jedna sekcia môže obsahovať niekoľko článkov. Jeden článok však môže tiež patriť do niekoľkých sekcií.

Na účely redakčného systému by samozrejme stačilo, aby článok spadal vždy iba do jednej sekcie. Tak by sme sa ale nič nenaučili

Narážame na väzbu M: N.

Väzba M: N

Väzbu M: N sme si už vysvetlili, ďalším príkladom by mohli byť napríklad študent a predmet. Každý študent chodí na niekoľko predmetov a každý predmet má niekoľko študentov, ktorí na neho dochádza.

Poďme si založiť tabuľku sekcií. Bude veľmi triviálne, pretože v nej budú len 2 stĺpce. Jeden s Id sekcie a druhý s jej názvom.

CREATE TABLE [Sekce] (
    [Id] INT IDENTITY,
    [Nazev] NVARCHAR(155),
    PRIMARY KEY ([Id])
);

Naplňme si ju dátami:

INSERT INTO [Sekce] ([Nazev]) VALUES
('Algoritmy'),
('Hry');

Databáza ako taká väzbu M: N nevie. To pre nás ale nie je prekážkou a bežne sa to obchádza vytvorením tzv. Väzobné tabuľky. Väzobné tabuľka nenesie sama o sebe žiadne dáta a slúži iba na prepojenie dvoch tabuliek. Každý riadok väzobné tabuľky bude obsahovať Id článku a Id sekcie, tak je spolu prepojí. Vďaka tomu môžeme dotazom zistiť aké články sú v sekcii alebo do ktorých sekcií článok patrí. Založme si väzobné tabuľku, pomenujeme ju ClanekSekce:

CREATE TABLE [ClanekSekce] (
    [Id] INT IDENTITY,
    [ClanekId] INT,
    [SekceId] INT,
    PRIMARY KEY ([Id])
);

Teraz ju naplníme dátami, ktorá nám články a sekcie prepojí:

INSERT INTO [ClanekSekce] ([ClanekId], [SekceId]) VALUES
(1, 1),
(2, 1),
(2, 2),
(3, 2),
(4, 2);

A skúsme si dotaz. Vypíšme si články v sekcii Algoritmy. Vyberieme články, tie prepojíme pomocou tabuľky ClanekSekce sa sekcií.

SELECT [C].[Url], [C].[Titulek]
FROM [Clanky] AS [C]
INNER JOIN [ClanekSekce] AS [CS] ON [CS].[ClanekId] = [C].[Id]
INNER JOIN [Sekce] AS [S] ON [CS].[SekceId] = [S].[Id]
WHERE [S].[Nazev] = 'Algoritmy';

výsledok:

url	titulok
čo-je-to-algoritmus	algoritmus
baktérie-bunkový-automat	baktérie

Otázka výška by bol na webe naozaj použitý pre vypísanie obsahu sekcie. Podľa väzobné tabuľky sme prepojili články sa sekcií. Vlastne sme pripojili tie riadky väzobné tabuľky, ktoré spájajú daný článok a ten článok potom k jeho sekcii.

To by dnes už stačilo. Ono by to vôbec na chvíľu stačilo, už toho vieme dosť.

Preto bude budúci lekcie, MS-SQL krok za krokom: poddotazy , venovaná precvičovanie otázok, niekoľko si ich vymyslíme a napíšeme, budú jednoduché i zložité, od všetkého trochu.