Lekce 10 - MS-SQL krok za krokem: Další dotazy a vazba M:N

V minulé lekci, MS-SQL krok za krokem: Dotazy přes více tabulek (JOIN), jsme se naučili JOINy, tedy dotazy nad více tabulkami.

V tomto duchu budeme dnes v MS-SQL tutoriálu pokračovat, založíme si ještě jednu tabulku s komentáři a poté si řekneme něco o vazbě M:N.

Tabulka komentáře

Pokračujme v našem redakčním systému a vytvořme si tabulku Komentare. Komentář se (podobně jako článek) váže na uživatele. Váže se ale také na článek. Máme zde tedy dvě vazby 1:N. Jeden článek má N komentářů, jeden uživatel má N komentářů. Komentář patří vždy pouze jednomu uživateli a jednomu článku.

Jelikož komentář je část a patří do dvou celků (k článku a k uživateli), bude obsahovat 2 sloupce s Id článku a Id komentáře. Těmto sloupcům s Id položky z cizí tabulky říkáme cizí klíče. Již je známe z minula (u článku byl cizí klíč uživatele), jen jsme si neřekli, že se jim tak říká. Kromě nich bude mít komentář text a datum.

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

Vložme si nějaké komentáře:

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);

Pojďme si zkusit vypsat všechny komentáře spolu s jejich autory a články, ke kterým patří. JOINy již umíme, tento dotaz bude obdobný, jen bude rovnou přes 2 tabulky najednou, čili s dvěma JOINy. JOINů můžeme mít v dotazu samozřejmě kolik chceme, ale měli bychom pamatovat na to, že to nejsou pro databázi úplně jednoduché operace.

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ýsledek:

Všimněte si, že jsme všechny sloupce předsadili názvem tabulky. Mělo by se to tak dělat vždy. Zde konkrétně se jmenuje Obsah obsah komentáře i obsah článku. U složitější struktury databáze se toto stává se sloupci jako Datum, Id, Autor...

Zkuste si dotaz bez názvů tabulek, nebude fungovat.

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

U složitějších dotazů přes více tabulek může být výhodné použít aliasy. Aliasy již také umíme, deklarují se přes klíčové slovo AS. Použijme 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];

Dotaz vypadá mnohem přehledněji, nemusíme opisovat názvy tabulek. Zkrátili jsme si je, zde jen na počáteční písmena.

Sekce

Pokračujme ve struktuře redakčního systému. Články se řadí do sekcí, ty jsou uloženy v tabulce Sekce. Je tu však malý háček. Jedna sekce může obsahovat několik článků. Jeden článek však může také patřit do několika sekcí.

Pro účely redakčního systému by samozřejmě stačilo, aby článek spadal vždy jen do jedné sekce. Tak bychom se ale nic nenaučili

Narážíme na vazbu M:N.

Vazba M:N

Vazbu M:N jsme si již vysvětlili, dalším příkladem by mohli být třeba student a předmět. Každý student chodí na několik předmětů a každý předmět má několik studentů, kteří na něj dochází.

Pojďme si založit tabulku sekcí. Bude velmi triviální, protože v ní budou jen 2 sloupce. Jeden s Id sekce a druhý s jejím názvem.

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

Naplňme si ji daty:

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

Databáze jako taková vazbu M:N neumí. To pro nás ale není překážkou a běžně se to obchází vytvořením tzv. vazební tabulky. Vazební tabulka nenese sama o sobě žádná data a slouží pouze k propojení dvou tabulek. Každý řádek vazební tabulky bude obsahovat Id článku a Id sekce, tak je spolu propojí. Díky tomu můžeme dotazem zjistit jaké články jsou v sekci nebo do kterých sekcí článek patří. Založme si vazební tabulku, pojmenujeme ji ClanekSekce:

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

Nyní ji naplníme daty, která nám články a sekce propojí:

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

A zkusme si dotaz. Vypišme si články v sekci Algoritmy. Vybereme články, ty propojíme pomocí tabulky ClanekSekce se sekcí.

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ýsledek:

Dotaz výše by byl na webu opravdu použit pro vypsání obsahu sekce. Podle vazební tabulky jsme propojili články se sekcí. Vlastně jsme připojili ty řádky vazební tabulky, které spojují daný článek a ten článek potom k jeho sekci.

To by dnes již stačilo. Ono by to vůbec na chvíli stačilo, již toho víme dost.

V následujícím cvičení, Řešené úlohy k 8.-10. lekci MS-SQL, si procvičíme nabyté zkušenosti z předchozích lekcí.