Letní akce! Lákají tě IT školení C#, Javy a PHP v Brně? Přihlas se a napiš nám do zpráv kód "BRNO 500" pro slevu 500 Kč na libovolný brněnský kurz. Lze kombinovat se slevami uvedenými u školení i použít pro více kurzů. Akce končí 28.7.

Lekce 7 - MySQL krok za krokem: Dotazy přes více tabulek (JOIN)

MySQL MySQL krok za krokem: Dotazy přes více tabulek (JOIN)

ONEbit hosting Unicorn College 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, MySQL krok za krokem: Datové typy a NULL, jsme si ukázali datové typy a vysvětlili hodnotu NULL. Dnes začneme dělat na jednoduchém redakčním systému, který může připomínat ten zde na ITnetwork. Ukážeme si dotazování přes více tabulek.

Konceptuální model

V následujících dílech si tedy v databázi vytvoříme takový zjednodušený ITnetwork. Pobavme se nejprve o tom, jak to bude vypadat. Dnes stihneme pochopitelně jen malou část. Protože obrázek někdy řekne více, než tisíc slov, začněme právě jím.

Konceptuální model redakčního systému

Co vidíte je tzv. konceptuální model. Je vytvořený pomocí notace (grafického jazyka) UML a v praxi se takovéto diagramy velmi často tvoří předtím, než začneme psát nějaký kód. Dobře si tak nejprve rozmyslíme, co že to vlastně chceme udělat.

Vidíme, že v systému figuruje uživatel, který může psát komentáře a články. Články spadají do sekcí. Jedná se tedy o databázi takového velmi jednoduchého redakčního systému, který si díky ITnetwork jistě dokážete představit.

Příprava tabulek a dat

Dnes se zaměříme na dotazy přes více tabulek. Pojďme si nejprve nějaké tabulky vytvořit. Bohatě nám budou stačit uživatelé a články.

Uživatelé

Protože uživatel bude vypadat trochu jinak, než nám vypadal doteď, založíme si tabulku uzivatele znovu. Tu současnou tedy dropneme:

DROP TABLE `uzivatele`;

Následně vytvoříme tabulku novou. Uživatel zde bude mít (kromě id) přezdívku, email a heslo:

CREATE TABLE `uzivatele` (
        `uzivatele_id` int AUTO_INCREMENT,
        `prezdivka` varchar(155),
        `email` varchar(155),
        `heslo` varchar(255),
        PRIMARY KEY (`uzivatele_id`)
);

Do uživatelů si rovnou nějaké vložíme:

INSERT INTO `uzivatele` (`prezdivka`, `email`, `heslo`) VALUES
('Míša', '[email protected]', 'dGg#@$DetA53d'),
('David', '[email protected]', '$#fdfgfHBKBKS'),
('Denny', '[email protected]', 'Jmls_aSW2RFss'),
('Ema', '[email protected]', 'fw8QT32qmcsld');

Články

Článek bude propojen s uživatelem, který ho napsal, tedy s jeho autorem. Tabulky propojíme tak, že do tabulky clanky přídáme sloupec s id autora. Tam bude hodnota id uživatele (tedy primární klíč z tabulky uzivatele), který článek napsal.

Hovoříme o vazbě 1:N (1 uživatel má N (několik) článků a každý článek patří právě jednomu uživateli). Část (zde článek) má vždy uložené id celku (zde uživatel) kam patří.

Článek bude obsahovat (opět kromě svého id) id autora, krátký popis, url, klíčová slova, titulek, obsah a datum publikace. Založme si tabulku clanky:

CREATE TABLE `clanky` (
        `clanky_id` int AUTO_INCREMENT,
        `autor_id` int,
        `popis` varchar(155),
        `url` varchar(155),
        `klicova_slova` varchar(155),
        `titulek` varchar(155),
        `obsah` text,
        `publikovano` datetime,
        PRIMARY KEY (`clanky_id`)
);

Za povšimnutí stojí asi jen použití typu TEXT pro text článku.

Dále přidáme články a k nim přiřadíme uživatele jako autory. Vzal jsem 4 články zde z ITnetwork, které jsem značně zkrátil a zjednodušil. Dotaz bude následující:

INSERT INTO `clanky` (`autor_id`, `popis`, `url`, `klicova_slova`, `titulek`, `obsah`, `publikovano`) VALUES
(1, 'Co je to algoritmus? Pokud to nevíte, přečtěte si tento článek.', 'co-je-to-algoritmus', 'algoritmus, co je to, vysvětlení', 'Algoritmus', 'Když se bavíme o algoritmech, pojďme se tedy shodnout na tom, co ten algoritmus vůbec je. Jednoduše řečeno, algoritmus je návod k řešení nějakého problému. Když se na to podíváme z lidského pohledu, algoritmus by mohl být třeba návod, jak ráno vstát. I když to zní jednoduše, je to docela problém. Počítače jsou totiž stroje a ty nemyslí. Musíme tedy dopodrobna popsat všechny kroky algoritmu. Tím se dostáváme k první vlastnosti algoritmu - musí být elementární (skládat se z konečného počtu jednoduchých a snadno srozumitelných kroků, tedy příkazů). "Vstaň z postele" určitě není algoritmus. "Otevři oči, sundej peřinu, posaň se, dej nohy na zem a stoupni si" - to už zní docela podrobně a jednalo by se tedy o pravý algoritmus. My se však budeme pohybovat v IT, takže budeme řešit problémy jako seřaď prvky podle velikosti nebo vyhledej prvek podle jeho obsahu. To jsou totiž 2 základní úlohy, které počítače dělají nejčastěji a které je potřeba dokonale promýšlet a optimalizovat, aby trvaly co nejkratší dobu. Z dalších příkladů algoritmů mě napadá třeba vyřeš kvadratickou rovnici nebo vyřeš sudoku.', '2012-3-21'),
(2, 'Bakterie jsou obdoba buněčného automatu v kombinaci s hrou.', 'bakterie-bunecny-automat', 'bakterie, automat, algoritmus', 'Bakterie', 'Bakterie jsou obdoba buněčného automatu, který vymyslel britský matematik John Horton Conway v roce 1970. Celou tuto hru řídí čtyři jednoduchá pravidla:/n/n
1. Živá bakterie s méně, než dvěma živými sousedy umírá./n
2. Živá bakterie s více, než třemi živými sousedy umírá na přemnožení./n
3. Živá bakterie s dvoumi nebo třemi sousedy přežívá beze změny do další generace./n
4. Mrtvá bakterie, s přesně třemi živými sousedy, opět ožívá./n
Tyto zdánlivě naprosto primitivní pravidla dokáží za správného počátečního rozmístění bakterií vytvořit pochodující skupinky, shluky "vystřelující" pochodující pětice, překvapivě složité souměrné exploze, oscilátory (periodicky kmitající skupinky), či nekonečnou podívanou na to, jak složité a dokonalé obrazce dokáží tyto dvě podmínky vytvořit. Celý program je koncipován jako hra, máte za úkol vytvořit co nejdéle žijící kolonii. <a href="soubory/bakterie.zip" ', '2012-2-14'),
(3, 'Cheese Mouse je oddechová plošinovka.', 'cheese-mouse-oddechova-plosinovka', 'myš, sýr, hra', 'Cheese Mouse', 'Cheese mouse je plošinovka s "horkou ostrovní atmosférou", kde ovládáte myš a musíte se dostat k sýru. V tom vám ale brání nejrůznější nástrahy a nepřatelé jako hadi, krysy, pirane, ale i roboti, mumie a nejrůznější havěť. Hru s několika petrobarevnými světy jsem dělal ještě na základní škole s Veisenem a může se pochlubit 2. místem v Bonusweb game competition, kde vyhrála 5.000 Kč. Vznikala v Game makeru o letních prázdninách, ještě v bezstarostném dětství, což značně ovlivnilo její grafickou stránku. Rád si ji občas zahraji na odreagování a zlepšní nálady. <a href="soubory/cheesemouse.zip" />', '2004-6-22'),
(2, 'Pacman je remake kultovní hry.', 'pacman-remake', 'pacman, remake, pampuch, hra, zdarma', 'Pacman', 'Jedná se o naprosto základní verzi této hry s editorem levelů, takže si můžete vytvořit svá vlastní kola. Postupem času ji hodlám ještě trochu upravit a přidat nějaké nové prvky, fullscreen a lepší grafiku. Engine hry bude také základem mého nového projektu Geckon man, který je zatím ve fázi psaní scénáře. <a href="soubory/pacman.zip" />', '2011-6-3');

Dotazy přes více tabulek

Nyní máme v databázi články a k nim přiřazené uživatele. Pojďme si udělat dotaz přes tyto 2 tabulky, získejme články a k nim připojme přezdívky jejich uživatelů. Slovo připojme jsem nepoužil náhodou, příkaz pro spojení 2 tabulek se totiž jmenuje JOIN. Napišme si dotaz a poté si ho vysvětleme. Dotazy již budeme psát na více řádků, abychom se v tom vyznali.

SELECT `titulek`, `prezdivka`
FROM `clanky`
JOIN `uzivatele` ON `autor_id` = `uzivatele_id`
ORDER BY `prezdivka`;

Výsledek:

Pacman          David
Bakterie        David
Cheese Mouse    Denny
Algoritmus      Míša

Na prvním řádku příkazu SELECT pracujeme se sloupci úplně stejně, jako kdyby byly v jedné tabulce, jednoduše vyjmenujeme, co nás zajímá. Jelikož vybíráme články a k nim připojujeme uživatele, budeme vybírat z tabulky clanky. Připojení dat z jiné tabulky uděláme pomocí příkazu JOIN, kde uvedeme tabulku, kterou připojujeme, a poté klauzuli ON. Klauzule ON je podobná jako WHERE, jen platí pro připojovanou tabulku a ne pro tu, ze které primárně vybíráme. V podmínce uvedeme, aby se ke každému článku připojil ten uživatel, jehož uzivatele_id je uvedeno ve sloupci autor_id. Výsledek jsme seřadili podle přezdívky uživatelů. Kdybychom chtěli jen nějaké články, normálně bychom před ORDER BY uvedli ještě WHERE, jak jsme zvyklí.

INNER JOIN a OUTER JOIN

INNER (vnitřní) a OUTER (vnější) JOIN jsou 2 typy příkazu JOIN. Fungují úplně stejně, jediný rozdíl je v tom, co se stane, když položka, na kterou se vazba odkazuje, neexistuje.

INNER JOIN

Pokud uvedeme v SQL dotazu pouze JOIN, pokládá ho MySQL databáze za tzv. INNER JOIN. Pokud by v našem případě neexistoval uživatel s id, které je u článku uvedeno, článek bez uživatele by vůbec nebyl ve výsledcích obsažen. Vazba je nerozdělitelná.

Pojďme si to zkusit, přidejme si článek, který bude odkazovat na id neexistujícího uživatele:

INSERT INTO `clanky` (`autor_id`, `popis`, `url`, `klicova_slova`, `titulek`, `obsah`, `publikovano`) VALUES
(99, 'Článek s neexistujím uživatelem slouží pro vyzkoušení typů JOINů.', 'clanek-bez-autora', 'clanek, join, autor, chybejici', 'Článek bez autora', 'Tento článek je přiřazen neexistujícímu uživateli s ID 99 a slouží k vyzkoušení různých typů JOINů v MySQL databázi.', '2012-10-21');

Vložený článek se odkazuje na uživatele s uzivatele_id 99, který v databázi není. Spusťme si nyní znovu náš SQL dotaz s JOINem. Pro přehlednost je lepší uvést, že chceme INNER JOIN.

SELECT `titulek`, `prezdivka`
FROM `clanky`
INNER JOIN `uzivatele` ON `autor_id` = `uzivatele_id`
ORDER BY `prezdivka`;

Výsledek:

Pacman          David
Bakterie        David
Cheese Mouse    Denny
Algoritmus      Míša

Výsledek je stále stejný, článek bez autora mezi výsledky není.

LEFT OUTER JOIN

Vnější JOINy umožňují vybírat i ty výsledky, které se nepodařilo spojit z důvodu chybějících položek. Zkusme si tzv. LEFT JOIN, který výsledek uzná, pokud existuje levá část vazby (zde článek) a pravá (ta připojovaná, zde uživatel) neexistuje. Do hodnot sloupců z připojované části se vloží NULL.

SELECT `titulek`, `prezdivka`
FROM `clanky`
LEFT JOIN `uzivatele` ON `autor_id` = `uzivatele_id`
ORDER BY `prezdivka`;

Výsledek:

Článek bez autora    NULL
Pacman               David
Bakterie             David
Cheese Mouse         Denny
Algoritmus           Míša

Vidíme, že článek se stejně vybral, i když se nepodařilo vybrat pravou část (tedy tu připojovanou, uživatele). Před spojováním tabulek je dobré se zamyslet, zda nastane případ, kdy se spojení nepodaří a co v tom případě chceme dělat. U článku by se toto v reálu stát asi nemělo.

RIGHT OUTER JOIN

Podobně jako levý vnější JOIN uznal vazbu v případě, že levá část existovala, pravý JOIN to udělá naopak. Pokud bude existovat uživatel (pravá, připojovaná část) a nebude k němu existovat článek (levá část), bude stejně v tabulce zahrnut. Osobně jsem tento JOIN ještě nepoužil. V tabulce jednoho takového uživatele máme, je jím uživatel Ema. Zkusme si tedy RIGHT JOIN:

SELECT `titulek`, `prezdivka`
FROM `clanky`
RIGHT JOIN `uzivatele` ON `autor_id` = `uzivatele_id`
ORDER BY `prezdivka`;

Výsledek:

Pacman          David
Bakterie        David
Cheese Mouse    Denny
NULL            Ema
Algoritmus      Míša

Podle očekávání zmizel Článek bez autora a objevila se Ema.

Ještě nějaké JOINy bychom určitě v MySQL nalezli, ale pro naše účely nám toto bohatě stačí.

Wherování

Teoreticky se můžeme JOINům vyhýbat a používat místo nich jednoduše jen klauzuli FROM a WHERE. Ve FROM uvedeme více tabulek oddělených čárkami. Ve WHERE specifikujeme podmínku spojení tabulek. Databáze si v ideálním případě takovýto dotaz nejprve převede na INNER JOIN a poté ho zpracuje.

SELECT `titulek`, `prezdivka`
FROM `clanky`, `uzivatele`
WHERE `autor_id` = `uzivatele_id`
ORDER BY `prezdivka`;

Výsledek je tedy stejný jako při INNER JOINu:

Pacman          David
Bakterie        David
Cheese Mouse    Denny
Algoritmus      Míša

Nevýhoda wherování je, že tak neuděláme všechny JOINy a v určitých případech mohou být dotazy méně optimalizované. Nikdy nevíme, jak dotaz databáze optimalizuje a optimalizace se bude lišit podle typu databáze. Tento způsob berte spíše jako zajímavost a nepoužívejte ho.

Příště budeme v lekci MySQL krok za krokem: Další dotazy a vazba M:N pokračovat v dotazech přes více tabulek a přidáme si do naší databáze další část redakčního systému.


 

 

Článek pro vás napsal David Čápka
Avatar
Jak se ti líbí článek?
22 hlasů
Autor pracuje jako softwarový architekt a pedagog na projektu ITnetwork.cz (a jeho zahraničních verzích). Velmi si váží svobody podnikání v naší zemi a věří, že když se člověk neštítí práce, tak dokáže úplně cokoli.
Unicorn College Autor sítě se informační technologie naučil na Unicorn College - prestižní soukromé vysoké škole IT a ekonomie.
Aktivity (3)

 

 

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

Avatar
Nikola Linková:23.2.2017 21:40

ďakujem za vysvetlenie, momentálne mám iný problém, ako nastaviť v PHP "Znakovú sadu pre pripojenie k serveru" keď chcem využiť PDO?
doteraz som používala @mysql_query("set names utf8",$db_connect);
v PDO je to však úplne inak a rozhadzuje mi to diakritiku aj v DB aj na stránke, mám tam síce doplnené CHARSET=utf8, zaujímavé však je, že na niektorých hostingoch(aj na localhoste) to funguje (tu môžem nastaviť aj v PMA zn.sadu pre pripojenie na utf8), a na iných to robí problémy, a tam mi dovolí nastaviť iba utf8mb4, resp. zakaždým keď zadám utf8, tak to hneď zmení na utf8mb4
keď zadám utf8mb4 do toho PDO dotazu, tak mi vyhodí chybu o neznámej zn. sade

zápis pre PDO mám prevzatý z jedného CMS, ale veľmi tomu nerozumiem

function dbconnect($db_host, $db_user, $db_pass, $db_name, $halt_on_error = TRUE) {
        $db_connect = TRUE;
        $db_select = TRUE;
        try {
                $pdo = dbconnection(new PDO("mysql:host=".$db_host.";dbname=".$db_name.";charset=utf8", $db_user, $db_pass));
                $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
        } catch (PDOException $error) {
                $db_connect = $error->getCode() === 1049; //unknown database
                $db_select = FALSE;
                if ($halt_on_error and !$db_connect) {
                        die("<strong>Unable to establish connection to MySQL</strong><br />".$error->getCode()." : ".$error->getMessage());
                } elseif ($halt_on_error) {
                        die("<strong>Unable to select MySQL database</strong><br />".$error->getCode()." : ".$error->getMessage());
                }
        }
        return array('connection_success' => $db_connect,
                'dbselection_success' => $db_select);
}

a na query používam túto funkciu (z toho istého CMS)

function dbquery($query, $print = FALSE) {
        global $mysql_queries_count, $mysql_queries_time;
        $start_time = microtime(TRUE);
        try {
                $result = dbconnection()->prepare($query);
                $result->execute();
                if ($print == 1) var_dump($query);
                $query_time = round((microtime(TRUE)-$start_time), 7);
                $mysql_queries_time[++$mysql_queries_count] = array($query_time, $query);
                return $result;
        } catch (PDOException $e) {
                trigger_error($e->getMessage(), E_USER_ERROR);
                if ($print == 1) var_dump($query);
                echo $e;
                return FALSE;
        }
}

dá sa to nastavenie znakovej sady niekde sem zapracovať, prípadne robím chybu ja a dá sa to riešiť aj inak?
trošku ma to trápi, lebo mysql_query pomaly vychádza z "módy"

 
Odpovědět 23.2.2017 21:40
Avatar
Odpovídá na Nikola Linková
Michal Štěpánek:24.2.2017 6:50

Před pár lety (když se ještě používalo "mysql_query") jsem si čmuchnul k ASP.NET a PHP jsem navždy opustil, takže v tomto směru ti neporadím... Jedno ale vím, že bude lepší, když si na tvůj dotaz založíš nové vlákno v sekci PHP, protože tady to jednoduše zanikne (protože to je sekce o mysql) bez odpovědi...

Odpovědět 24.2.2017 6:50
Nikdy neříkej nahlas, že to nejde. Vždycky se totiž najde blbec, který to neví a udělá to...
Avatar
RomanP
Člen
Avatar
RomanP:25.6.2017 21:59

Zjevne to roli nehraje, ale uvedomil jsem si to az v nasledujici 8. kapitole kde autor v jednom prikladu pouzil obe variace u cs. tabulky

 
Odpovědět 25.6.2017 21:59
Avatar
Odpovídá na Nikola Linková
Michal Šmahel:26.6.2017 9:48

Ahoj, používáš zdejší databázový wraper? Tam vše funguje v pohodě. Jde o to, že se jedná o statickou třídu a znaková sada se nastaví při její inicializaci a dále pak všechny dotazy pracují s danou znakovou sadou.

Alternativou je napsat si vlastní třídu (pokud ovládáš OOP) na podobné bázi.

Odpovědět 26.6.2017 9:48
Nejdůležitější je motivace, ovšem musí být doprovázena činy.
Avatar
Odpovídá na Michal Šmahel
Michal Šmahel:26.6.2017 9:53

Co se týče nastavení dané znakové sady, v PDO se předává v konfiguraci.

Tady jsem vytahal nějaké úseky ze své třídy pro znázornění, jak to zhruba funguje (odstranil jsem OOP prvky, je to jen pro názornost).

// Konfigurace (včetně znakové sady)
$pdo_config = [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8",
        PDO::ATTR_EMULATE_PREPARES => false
];

// Připojení
$connection = @new PDO(
        "mysql:host=" . $host . ";dbname=" . $name,
        $user,
        $password,
        $pdo_config
    );

// Využití
$connection->prepate("
        -- Dotaz...
");
$connection->execute();
$connection->fetchAll();
Odpovědět 26.6.2017 9:53
Nejdůležitější je motivace, ovšem musí být doprovázena činy.
Avatar
Jakub Mikšík:10. července 17:02

V tabulce uživatel máme uzivatel_id.
Tabulku clanky chceme s tabulkou uzivatel propojit pomocí uzivatel_id ale v tabulce clanky to pojmenujeme autor_id.
Proč do tabulky clanky nedáme také uzivatel_id? Z textu jsem pochopil, že to není chyba, ale záměr. Přijde mi to zbytečné a zavádějící. Pokud to má nějaký důvod, tak mám druhý dotaz. Stalo by se něco, kdybych v tabulce clanky nechal sloupec na propojení s názvem uzivatel_id?
Děkuji a mějte se.
Jakub

 
Odpovědět 10. července 17:02
Avatar
Odpovídá na Jakub Mikšík
Michal Šmahel:10. července 18:33

Pokud ti jde o název sloupce, může být takřka libovolný. Ale každopádně je na místě, aby název sloupce jasně říkal, co je dosazeno jako hodnoty sloupce.

Jestliže bys místo "autor_id" použil "uzivatel_id", trochu by sis usnadnil práci při spojování (je možné použít USING), ale také trochu znepřehlednil strukturu tabulky. Pokud to tak chceš používat, je dobré alespoň uvádět komentáře.

Odpovědět 10. července 18:33
Nejdůležitější je motivace, ovšem musí být doprovázena činy.
Avatar
Odpovídá na Michal Šmahel
Jakub Mikšík:10. července 21:42

Děkuji za odpověď, jde mi o to, že můžu mít i 10 tabulek, které budou spojeny přes uživatel_id a v tom okamžiku mi přijde lepší ty sloupce mít pojmenované stejně, protože mi to naopak tu strukturu zpřehlední. Nechápu, proč by naopak ta struktura měla být nepřehledná. Nemám praktické zkušenosti, proto se ptám.

Editováno 10. července 21:43
 
Odpovědět 10. července 21:42
Avatar
Odpovídá na Jakub Mikšík
Michal Šmahel:11. července 11:32

Je to celkem subjektivní. Pokud ti to přijde přehlednější, používej to. Jen pro ostatní raději uváděj do komentáře, jakou úlohu ten uživatel plní. Dělám to podobně a funguje to ;) . Důležité je používat v celé databázi stejný systém, aby se v tom dalo vyznat. Někdo v tom může chvíli tápat, protože je zvyklý na ten druhý způsob (konkrétně pojmenovat sloupec dle jeho úlohy). Pokud to někde sepíšeš a budeš to mít v komentáři připsané, nevidím v tom problém.

Odpovědět  +1 11. července 11:32
Nejdůležitější je motivace, ovšem musí být doprovázena činy.
Avatar
Odpovídá na Jakub Mikšík
Michal Štěpánek:12. července 13:55

Můžeš si to samozřejmě pojmenovat podle toho, jak chceš. Můžeš mít situaci, kdy s tím jedním sloupcem "uzivatel_id" z tabulky "uzivatel" budeš mít propojeno z tabulky "clanky" více sloupců, protože jeden článek může spravovat více uživatelů třeba "admin", "editor", "schvalovatel", apod.
Pak bys musel řešit, co k čemu patří...
Mít sloupce typu "uzivatel_id1", "uzivatel_id2" asi není příliš přínosné pro snadnou orientaci v DB...

Odpovědět  +2 12. července 13:55
Nikdy neříkej nahlas, že to nejde. Vždycky se totiž najde blbec, který to neví a udělá to...
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 24. Zobrazit vše