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 kurze MS-SQL pokračovať, vytvorí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 Comments. Komentár sa (podobne ako článok) viaže na používateľa. Viaže sa ale 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 užívateľovi a jednému článku.

Keďže komentár je časť a patrí do dvoch celkov (k článku a k užívateľovi), bude obsahovať dva stĺpce s Id článku a Id uží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ľúč užívateľa), len sme si nepovedali, že sa im tak hovorí. Okrem nich bude mať komentár text a dátum:

CREATE TABLE [Comments] (
    [Id] INT IDENTITY,
    [ArticleId] INT,
    [UserId] INT,
    [Content] NVARCHAR(MAX),
    [Date] DATETIME,
    PRIMARY KEY ([Id])
);

Vložme si nejaké komentáre:

INSERT INTO [Comments] ([UserId], [Content], [Date], [ArticleId]) VALUES
(4, 'Skvelý článok!', '2012-4-6', 1),
(4, 'Aká je teda podmienka pre vznik baktérií?', '2011-1-28', 2),
(1, 'Zasekol som sa, kde nájdem kľúč k 3. levelu?', '2011-9-30', 3),
(4, 'Ako posuniem plošinu v 5. leveli?', '2010-8-1', 3),
(1, 'Zomrel som a neuložil som hru, č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);

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 dopyt bude podobný, len bude rovno cez dve tabuľky naraz, čiže s dvoma JOINmi. JOINov môžeme mať v dopyte samozrejme koľko chceme, ale mali by sme myslieť na to, že to nie sú pre databázu úplne jednoduché operácie:

SELECT [Users].[Nickname], [Comments].[Content], [Articles].[Title]
FROM [Comments]
INNER JOIN [Users] ON [Users].[Id] = [Comments].[UserId]
INNER JOIN [Articles] ON [Articles].[Id] = [Comments].[ArticleId]
ORDER BY [Comments].[Date];

Výsledok:

Všimnite si, že sme všetky stĺpce predsadili názvom tabuľky. Malo by sa to tak robiť vždy. Tu konkrétne sa volá Content obsah komentára aj obsah článku. Pri zložitejšej štruktúre databázy sa to stáva so stĺpcami ako Date, Id, Author...

Skúste si dopyt bez názvov tabuliek, nebude fungovať:

-- tento dotaz nebude fungovat
SELECT [Users].[Nickname], [Comments].[Content], [Articles].[Title]
FROM [Comments]
INNER JOIN [Users] ON [Id] = [UserId]
INNER JOIN [Articles] ON [Id] = [ArticleId]
ORDER BY [Comments].[Date];

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 dopytoch 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 dopyte:

SELECT [U].[Nickname], [C].[Content], [A].[Title]
FROM [Comments] AS [C]
INNER JOIN [Users] AS [U] ON [U].[Id] = [C].[UserId]
INNER JOIN [Articles] AS [A] ON [A].[Id] = [C].[ArticleId]
ORDER BY [C].[Date];

Dopyt 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á.

Sekcie

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

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

CREATE TABLE [Sections] (
    [Id] INT IDENTITY,
    [Name] NVARCHAR(155),
    PRIMARY KEY ([Id])
);

Naplňme si ju dátami:

INSERT INTO [Sections] ([Name]) VALUES
('Algorithms'),
('Games');

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äzobnej 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äzobnej tabuľky bude obsahovať Id článku a Id sekcie, tak ich spolu prepoja. Vďaka tomu môžeme dopytom zistiť, aké články sú v sekcii alebo do ktorých sekcií článok patrí. Vytvorme si väzobnú tabuľku, pomenujeme ju ArticleSection:

CREATE TABLE [ArticleSection] (
    [Id] INT IDENTITY,
    [ArticleId] INT,
    [SectionId] INT,
    PRIMARY KEY ([Id])
);

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

INSERT INTO [ArticleSection] ([ArticleId], [SectionId]) VALUES
(1, 1),
(2, 1),
(2, 2),
(3, 2),
(4, 2);

A skúsme si ďalšie dopyty. Vypíšme si články v sekcii Algoritms. Vyberieme články, tie prepojíme pomocou tabuľky ArticleSection so sekciou:

SELECT [A].[Url], [A].[Title]
FROM [Articles] AS [A]
INNER JOIN [ArticleSection] AS [AS] ON [AS].[ArticleId] = [A].[Id]
INNER JOIN [Sections] AS [S] ON [AS].[SectionId] = [S].[Id]
WHERE [S].[Name] = 'Algorithms';

Výsledok:

Dopyt vyššie by bol na webe naozaj použitý 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 na dnes 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í.