Lekce 8 - 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

Unicorn College ONEbit hosting Tento obsah je dostupný zdarma v rámci projektu IT lidem. Vydávání, hosting a aktualizace umožňují jeho sponzoři.

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:

Prezdivka Obsah Titulek
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
Ema Super článek! Algoritmus
Denny Dobrá hra! Pacman
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 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:

Url Titulek
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í lekce, MS-SQL krok za krokem: Poddotazy, věnována procvičování dotazů, několik si jich vymyslíme a napíšeme, budou jednoduché i složité, od všeho trochu.


 

 

Článek pro vás napsal Michal Žůrek - misaz
Avatar
Jak se ti líbí článek?
12 hlasů
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.
Aktivity (5)

 

 

Komentáře
Zobrazit starší komentáře (5)

Avatar
Michal Štěpánek:28.2.2016 17:19

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

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

a tam patří oba...

Odpovědět 28.2.2016 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:28.2.2016 18:00

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

Odpovědět 28.2.2016 18:00
There is only one true good, knowledge, and one true evil, ignorance.
Avatar
Mirek Senk
Člen
Avatar
Mirek Senk:31.8.2016 16:43

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.8.2016 16:43
Avatar
Odpovídá na Mirek Senk
Michal Žůrek - misaz:31.8.2016 16:54

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

Odpovědět  +1 31.8.2016 16:54
Nesnáším {}, proto se jim vyhýbám.
Avatar
Jan Troják
Člen
Avatar
Jan Troják:28.9.2017 20:38

V předposledním zdroj. kodu más jednou malé 'as' a není na konci ';'. Jinak super článek, díky za něj

 
Odpovědět 28.9.2017 20:38
Avatar
Odpovídá na Jan Troják
Michal Žůrek - misaz:28.9.2017 21:04

dík za upozornění. Ani malé as ani chybějící středník však nemění funkčnost kódu. Střeník je v T-SQL povinný, jen pokud máš více příkazu za sebou a velikost písmen klíčových slov nerozlišuje.

Odpovědět 28.9.2017 21:04
Nesnáším {}, proto se jim vyhýbám.
Avatar
Jan Troják
Člen
Avatar
Odpovídá na Michal Žůrek - misaz
Jan Troják:28.9.2017 23:16

Já vím, ale vypadá to blbě, když to je jednou malé a jednou velké + to někoho může zmást

 
Odpovědět  +1 28.9.2017 23:16
Avatar
blazoid
Člen
Avatar
blazoid:17. června 19:01

Ahoj, mám dotaz, který se týká vazeb, dále ale i nějakého přiřazení nějaké tabulky k položce jiné tabulky:

Dejme tomu, že mám tabulku "platby", kde jsou evidovány veškeré ekonomické transakce v rámci podniku. Dále budu mít tabulku "bezhotovostní platby", kde budou evidovány veškeré platby provedené například převodem a tabulku "hotovostní platby", kde budou evidovány veškeré platby v hotovosti. Důvodem, proč je to takto rozděleno je, že bezhotovostní platby mají jiné atributy než platby v hotovosti (například bezhotovostní budou mít variabilní symbol, číslo účtu - naproti tomu hotovostní mají zase údaj o osobě, od které byla platba přijata, dál mě nic nenapadá...:-))

V tabulce "platby" budu chtít odkazovat na id buď v tabulce "bezhotovostní platby" nebo na id v tabulce "hotovostní platby", přičemž chci odkazovat pouze na jednu z tabulek (dejme tomu, že je dáno pravidlo, že ke každé platbě se může vázat pouze jeden typ transakce) podle toho, jakým způsobem platba proběhla. Jednou z možností je programově ošetřit to, že v případě zaevidování platby bude vyplněno pouze id jednoho typu transakce a ve druhém bude null (program přiřazení dvou typů transakcí prostě nepovolí), mě však spíš zajímá, zda se toto dá ošetřit i nějak elegantně přímo v databázi (tedy, už je v jednom ze sloupců transakcí id vyplněno, typ transakce již této platbě byl přiřazen, nebude tedy povoleno přiřadit druhý typ - databáze to nedovolí)....

Díky za odpovědi :-)

 
Odpovědět 17. června 19:01
Avatar
Odpovídá na Michal Žůrek - misaz
Jakub Ondrák:21. června 16:55

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

Zkoušel jsem to v minulé lekci, a je to jedno i co do LEFT a RIGHT JOINů :-O . Přijde mi, že se to left/right váže k postavení co k čemu připojujeme. Např. u FROM [Clanky] JOIN [Uzivatele] když je LEFT, tak se zahrnou všechny záznamy vlevo (tj. v tabulce Clanky); u RIGHT zase vše, co je v tabulce Uzivatele

 
Odpovědět  +1 21. června 16:55
Avatar
Odpovídá na Jakub Ondrák
Michal Žůrek - misaz:21. června 17:59

asi jo, nikdy mě nenapadlo to zkoušet.

Odpovědět 21. června 17:59
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 10 zpráv z 15. Zobrazit vše