8. díl - MS-SQL krok za krokem: Další dotazy a vazba M:N

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

V minulém dílu seriálu tutoriálů o MS-SQL databázi jsme se naučili JOINy, tedy dotazy nad více tabulkami. V tomto duchu budeme dnes 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 komentáře. 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] TEXT,
        [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 už 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:

Ema      Jak rozjedu plošinu v 5. levelu?      Cheese Mouse
Ema      Jak je tedy přesně ta podmínka...     Bakterie
Denny    Nerozumím tomu!                       Algoritmus
Míša     Zasekla jsem se v této hře...         Cheese Mouse
Denny    Dobrá hra!                            Pacman
Ema      Super článek!                         Algoritmus
Míša     Umřel jsem a nemám hru uloženou...    Pacman
David    Super článek!                         Algoritmus

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 3
Ambiguous column name 'Id'.
Msg 209, Level 16, State 1, Line 4
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, dělají 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] varchar(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:

co-je-to-algoritmus         Algoritmus
bakterie-bunecny-automat    Bakterie

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. Proto bude příští díl věnován procvičování dotazů, několik si jich vymyslíme a napíšeme, budou jednoduché i složité, od všeho trochu.


 

  Aktivity (1)

Článek pro vás napsal Michal Žůrek (misaz)
Avatar
Autor se věnuje tvorbě aplikací pro počítače, mobilní telefony, mikroprocesory a tvorbě webových stránek a webových aplikací. Nejraději programuje ve Visual Basicu a TypeScript. Ovládá HTML, CSS, JavaScript, TypeScript, C# a Visual Basic.

Jak se ti líbí článek?
Celkem (7 hlasů) :
4.857144.857144.857144.857144.85714


 



 

 

Komentáře

Avatar
Skrat
Člen
Avatar
Skrat:

Kedy pokracujeme ???

 
Odpovědět 20.4.2014 22:27
Avatar
Odpovídá na Skrat
Michal Žůrek (misaz):

Ten článek vyšel dneska, pokračování bude asi za týden.

Odpovědět  +1 20.4.2014 22:33
Nesnáším {}, proto se jim vyhýbám.
Avatar
Skrat
Člen
Avatar
Skrat:

Oky doky bude to dlhe cakanie :D

 
Odpovědět 20.4.2014 22:46
Avatar
N-nojmi
Člen
Avatar
N-nojmi:

Celá sekce je parádní, jen tak dál 8-)

Odpovědět 30.4.2014 17:25
Není hloupých lidí, jen lidí co málo používají google...
Avatar
medvedovic115:

Zdravím, myslím, že výsledok posledného dotazu by mal byť len:

co-je-to-algoritmus Algoritmus

Odpovědět 28. února 16:59
There is only one true good, knowledge, and one true evil, ignorance.
Avatar
Odpovídá na medvedovic115
Michal Štěpánek:

Neměl, protože se dotazuje na to, do které "Sekce" patří

WHERE [S].[Nazev] = 'Algoritmy'

a tam patří oba...

Odpovědět 28. února 17:19
Nikdy neříkej nahlas, že to nejde. Vždycky se totiž najde blbec, který to neví a udělá to...
Avatar
Odpovídá na Michal Štěpánek
medvedovic115:

Jo, beriem späť, mal som chybu v kóde :) nabudúce budem pozornejší

Odpovědět 28. února 18:00
There is only one true good, knowledge, and one true evil, ignorance.
Avatar
Mirek Senk
Člen
Avatar
Mirek Senk:

Měl bych jeden možná hloupý dotaz.
Je důležité pořadí tabulek za "ON"?
Např:
INNER JOIN [ClanekSekce] as [CS] ON [CS].[ClanekId] = [C].[Id]
vs.
INNER JOIN [ClanekSekce] as [CS] ON [C].[Id] = [CS].[ClanekId]

děkuji.

 
Odpovědět 31. srpna 16:43
Avatar
Odpovídá na Mirek Senk
Michal Žůrek (misaz):

ne, pak si musíš dávat pozor akorát na LEFT a RIGHT joiny.

Odpovědět  +1 31. srpna 16:54
Nesnáším {}, proto se jim vyhýbám.
Děláme co je v našich silách, aby byly zdejší diskuze co nejkvalitnější. Proto do nich také mohou přispívat pouze registrovaní členové. Pro zapojení do diskuze se přihlas. Pokud ještě nemáš účet, zaregistruj se, je to zdarma.

Zobrazeno 9 zpráv z 9.