10. diel - MS-SQL krok za krokom: Ďalšie dopyty a väzba M:N

V predchádzajúcej lekcii, MS-SQL krok za krokom: Dopyty cez viac tabuliek (JOIN), sme sa naučili JOINy, teda dopyty nad viacerými tabuľkami.

V tomto duchu budeme dnes v MS-SQL tutoriáli 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árov

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 však aj na článok. Máme tu teda dve väzby 1:N. Jeden článok má N komentárov, jeden používateľ má N komentárov. Komentár patrí vždy iba jednému používateľovi a jednému článku:

Keďže komentár je časť a patrí do dvoch celkov (k článku a k používateľovi), bude obsahovať 2 stĺpce s Id článku a Id používateľa. Týmto stĺpcom s Id položky z cudzej tabuľky hovoríme cudzie kľúče. Už ich poznáme z minula (pri článku bol cudzí kľúč použí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,
    [ClanokId] INT,
    [PouzivatelId] INT,
    [Obsah] NVARCHAR(MAX),
    [Datum] DATETIME,
    PRIMARY KEY ([Id])
);

Na cudzie kľúče spravidla zavádzame tzv. integritné obmedzenia. My ich však pre jednoduchosť zanedbávame. Cudzím kľúčom a ich integritným obmedzeniam sa budeme podrobnejšie venovať v lekcii MS-SQL krok za krokom: Cudzie kľúče 1.

Vložme si nejaké komentáre:

INSERT INTO [Komentare] ([PouzivatelId], [Obsah], [Datum], [ClanokId]) VALUES
(4, 'Super článok!', '2012-4-6', 1),
(4, 'Aká je teda presne tá podmienka na vznik baktérie?', '2011-1-28', 2),
(1, 'Zasekla som sa v tejto hre, kde nájdem kľúč do 3. levelu?', '2011-9-30', 3),
(4, 'Ako rozbehnem plošinu v 5. leveli?', '2010-8-1', 3),
(1, 'Zomrela som a nemám hru uloženú, čo mám robiť?', '2012-4-14', 4),
(3, 'Dobrá hra!', '2012-4-6', 4),
(3, 'Nerozumiem tomu!', '2011-4-6', 1),
(2, 'Super článok!', '2012-5-6', 1);

Zložitejšie dotazy cez viac tabuliek

Poďme si skúsiť vypísať všetky komentáre spolu s ich autormi a článkami, ku ktorým patria. JOINy už vieme, tento dotaz bude podobný, len bude rovno cez 2 tabuľky naraz, čiže s dvoma JOINmi:

SELECT [Pouzivatelia].[Prezyvka], [Komentare].[Obsah], [Clanky].[Titulok]
FROM [Komentare]
INNER JOIN [Pouzivatelia] ON [Pouzivatelia].[Id] = [Komentare].[PouzivatelId]
INNER JOIN [Clanky] ON [Clanky].[Id] = [Komentare].[ClanokId]
ORDER BY [Komentare].[Datum];

JOINov 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.

Výsledok:

Prezyvka	Obsah	Titulok
Ema	Ako rozbehnem plošinu v 5. leveli?	Cheese Mouse
Ema	Aká je teda presne tá podmienka...	Baktérie
Tomáš	Nerozumiem tomu!	Algoritmus
Michaela	Zasekla som sa v tejto hre...	Cheese Mouse
Ema	Super článok!	Algoritmus
Tomáš	Dobrá hra!	Pacman
Michaela	Zomrela som a nemám hru uloženú...	Pacman
Ján	Super článok!	Algoritmus

Všimnime si, že sme všetky stĺpce predsadili názvom tabuľky, aby nedošlo k zmäteniu MS-SQL. Malo by sa to tak robiť vždy. Tu konkrétne sa volá Obsah obsah komentára aj obsah článku. Pri zložitejšej štruktúre databázy sa toto stáva so stĺpcami ako Datum, Id, Autor...

Skúsme si dotaz bez názvov tabuliek, nebude fungovať:

-- tento dotaz nebude fungovať
SELECT [Pouzivatelia].[Prezyvka], [Komentare].[Obsah], [Clanky].[Titulok]
FROM [Komentare]
INNER JOIN [Pouzivatelia] ON [Id] = [PouzivatelId]
INNER JOIN [Clanky] ON [Id] = [ClanokId]
ORDER BY [Komentare].[Datum];

MS-SQL vyhodí hlášku, pretože nedokáže v ON klauzulách rozlíšiť, ktorej tabuľke stĺpec Id patrí:

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

Použitie aliasov

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

SELECT [P].[Prezyvka], [K].[Obsah], [C].[Titulok]
FROM [Komentare] AS [K]
INNER JOIN [Pouzivatelia] AS [P] ON [P].[Id] = [K].[PouzivatelId]
INNER JOIN [Clanky] AS [C] ON [C].[Id] = [K].[ClanokId]
ORDER BY [K].[Datum];

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

Sekcie

Pokračujme v štruktúre redakčného systému. Články sa radia do sekcií, tie sú uložené v tabuľke Sekcie. 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 však 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ň dochádzajú.

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

CREATE TABLE [Sekcie] (
    [Id] INT IDENTITY,
    [Nazov] NVARCHAR(155),
    PRIMARY KEY ([Id])
);

Naplňme si ju dátami:

INSERT INTO [Sekcie] ([Nazov]) VALUES
('Algoritmy'),
('Hry');

Väzobná tabuľka

Databáza ako taká väzbu M:N nevie. To pre nás však nie je prekážkou a bežne sa to obchádza vytvorením tzv. väzobnej tabuľky. Väzobná tabuľka sama osebe nenesie žiadne dáta a slúži iba na prepojenie dvoch tabuliek. Každý riadok väzobnej tabuľky bude obsahovať Id článku a Id sekcie, tak ich 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 ClanokSekcia:

CREATE TABLE [ClanokSekcia] (
    [Id] INT IDENTITY,
    [ClanokId] INT,
    [SekciaId] INT,
    PRIMARY KEY ([Id])
);

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

INSERT INTO [ClanokSekcia] ([ClanokId], [SekciaId]) VALUES
(1, 1),
(2, 1),
(2, 2),
(3, 2),
(4, 2);

Väzobná tabuľka ClanokSekcia teraz obsahuje nasledujúce záznamy:

Id	ClanokId	SekciaId
1	1	1
2	2	1
3	2	2
4	3	2
5	4	2

Význam napríklad druhého záznamu možno interpretovať nasledovne:

Článok s Id 2 patrí do sekcie s Id 1.

Vidíme, že pomocou takejto tabuľky dokážeme zistiť, do ktorých sekcií patrí konkrétny článok a naopak ktoré články nájdeme v konkrétnej sekcii.

Dotaz cez väzobnú tabuľku

A skúsme si dotaz. Vypíšme si články v sekcii Algoritmy. Vyberieme články, tie prepojíme pomocou tabuľky ClanokSekcia so sekciou:

SELECT [C].[Url], [C].[Titulok]
FROM [Clanky] AS [C]
INNER JOIN [ClanokSekcia] AS [CS] ON [CS].[ClanokId] = [C].[Id]
INNER JOIN [Sekcie] AS [S] ON [CS].[SekciaId] = [S].[Id]
WHERE [S].[Nazov] = 'Algoritmy';

Výsledok:

Url	Titulok
co-je-to-algoritmus	Algoritmus
bakterie-bunkovy-automat	Baktérie

Dotaz vyššie by sa na webe naozaj použil na vypísanie obsahu sekcie. Podľa väzobnej tabuľky sme prepojili články so sekciou. Vlastne sme pripojili tie riadky väzobnej 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ť.

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