MySQL dotazy v IT centre viacerých tabuliek (JOINS)

učebné materiály pre hodiny informatiky

(posledná zmena na tejto stránke: 13.11.2016)

tabuliek

Videonávod

Ak sa vám video páči iba pozri na, sotva sa niečo naučíte. Pre najlepší efekt učenia pracujte priamo na počítači a postupujte podľa príkladov.

Fólie

(posledná synchronizácia prezentácie PDF: 02.08.2017)

Ak nevidíte prezentáciu PDF, stiahnite si ju kliknutím sem: Priame stiahnutie PDF prezentácie

Táto postupnosť predpokladá znalosť nasledujúceho obsahu: Úvod do príkazov SELECT v MySQL.

Cvičenia s podrobnými riešeniami a vysvetleniami nájdete tu: Cvičenia o PRIPOJENÍ v MySQL

MySQL výpis „customer-simply.sql“

Aby ste pochopili príklady na snímkach a vo videonávode, stiahnite si tento výpis MySQL: customer-simply.sql, otvorte ho v Workbench (File -> Open SQL Script ...) a nechajte ho bežať. Vytvorí sa databáza „Zákazníci“, ktorá obsahuje dve tabuľky („Umiestnenia“ a „Zákazníci“).

Dotaz na údaje z dvoch tabuliek

Príklad scenára: Obrázok zobrazuje dve prepojené tabuľky, ktoré sú prepojené s cudzím kľúčom. Popremýšľajte, ktoré sú primárne kľúče a ktoré cudzie kľúče.

Riešenie:
Zákazníci (customerID, meno zákazníka, meno zákazníka, telefón zákazníka, ↑ krajina zákazníka, číslo zákazníka)
krajinách (landID, countryName, countryDelivery status)

Primárne kľúče sú preto customerID a countryID (rozpoznateľné podľa podčiarknutia), cudzí kľúč je customerLand v tabuľke zákazníkov (rozpoznateľný šípkou pred customerCountry). Cudzí kľúč kundeLand označuje primárny kľúč krajín tabuľky.

Karta „zákazník“ preto obsahuje pole „customerLand“. Sem sa zadáva ID krajiny, ktoré odkazuje na pole landID v tabuľke „krajiny“.

Ak chcete vo výberovom dotaze zobraziť meno zákazníka a názov krajiny v plnom texte, musíte načítať údaje z oboch tabuliek.

Kód na vygenerovanie týchto dvoch tabuliek:

s fiktívnymi údajmi:

Možnosť 1: PRIPOJTE SA/ĽAVÉ PRIPOJTE SA

Vyberie zadané polia (customerID, customerName, customerCountry, countryID, countryName) z dvoch tabuliek zákazníci a krajiny (krajiny zákazníka JOIN) a je obmedzené na prípady, keď sa customerCountry zhoduje s countryID (WHERE customerCountry = countryID).

Ak sa tu vynechá vyhlásenie WHERE, všetky dátové záznamy od „zákazníkov“ sa skombinujú so všetkými dátovými záznamami z „krajín“, takže s 3 krajinami a 4 zákazníkmi sa generuje celkovo 12 riadkov (z toho štyri sa vydávajú trikrát - teda 8 nadbytočný).

Tento nezmyselný výsledok sa nazýva karteziánsky súčin. Vždy skontrolujte výsledky dotazu, aby ste zistili, že ste ich dali zmysel so správnymi obmedzeniami.

Ak v tabuľkách Polia s rovnakými názvami musí byť určená tabuľka, z ktorej sa má hodnota prevziať, syntax: názov tabuľky.názov poľa

VĽAVO SA PRIPOJTE

Ak je v tabuľke „zákazníci“ zadaná hodnota pre customerCountry, ktorú v tabuľke „krajín“ nenájdete, potom sa tento údajový záznam neuskutoční. Takýto nesprávny záznam údajov by bol doplnkom vyššie uvedeného zoznamu SQL, napr.

LandID 9 neexistuje, takže dátový záznam nie je výstupom vo vyššie uvedenom dotaze.

Pri LEFT JOIN sa vypisujú všetky dátové záznamy ľavej („ľavej“) tabuľky:

Pozor: „WHERE“ sa nahrádza „ON“.

Možnosť 2: implicitná notácia

Namiesto toho, aby sme explicitne písali JOIN, môžeme tabuľky, na ktoré sa má dotazovať, jednoducho oddeliť čiarkami:

Mimochodom, INNER JOIN je úplne rovnaké ako JOIN. Jeden hovorí o syntaktickom cukre.

Cvičenia

Všetky cvičenia nájdete v zbierke materiálov (tiež všetky ďalšie súbory, ako sú obrázky, diagramy tried alebo šablóny HTML!).

Aktuálne cvičenie si môžete stiahnuť tu ako súbor txt.

***** Cvičenia: MySQL - VÝBER: Dotaz na viac tabuliek pomocou SPOJENÍ *****


MySQL06_1: karteziánsky produkt

Vysvetlite vlastnými slovami, prečo tento dopyt neposkytuje zmysluplný výsledok:

--- Spustenie MySQL kódu ---
VYBERTE * OD ZÁKAZNÍKOV, miest
--- Koniec kódu ---

MySQL06_2: Jednoduché dotazy na dve tabuľky - DB jednoduché pre zákazníka

Použite túto databázu na toto cvičenie: 07mysql/_dumps/customers/customers-simply.sql

A) Zadajte meno, PSČ a mesto všetkých zákazníkov. Zoznam obsahuje meno zákazníka a názov miesta, kde žije.

B) Zadajte meno a miesto bydliska všetkých zákazníkov, ktorí majú PSČ 79312.

C) Zadajte meno a miesto pobytu všetkých zákazníkov, ktorí žijú v Emmendingene (kritériom obmedzenia NIE JE PSČ, ale „Emmendingen“).

D) Zadajte meno, miesto bydliska a počet obyvateľov pre všetkých zákazníkov, ktorí žijú v mieste s viac ako 70 000 obyvateľmi.

E) Uveďte zoznam všetkých miest s menej ako 1 000 000 obyvateľmi.

F) Zadajte meno zákazníka a názov miesta pre všetkých zákazníkov, ktorí žijú na miestach s počtom obyvateľov medzi 100 000 a 1 500 000.

G) Zadajte meno zákazníka, PSČ a názov mesta pre všetkých zákazníkov, ktorých meno obsahuje „e“ a všetky mestá, ktoré obsahujú „u“ alebo „r“ (takže frEd from stUden sa zobrazí presne ako jEssE od bRnz, frEd from salt but not and also does martin from hamburg).

MySQL06_2a: Cvičenie pri dotazoch naprieč dvoma tabuľkami: kníhkupectvo

Použite túto databázu na toto cvičenie:
07mysql/_dumps/buchladen/buchladen.sql

A) Uveďte zoznam všetkých dodávateľov, ktorí žijú v meste Reute. Výstup: meno dodávateľa, názov miesta, PSČ

B) Zobraziť zoznam všetkých vydavateľov, ktorých mená začínajú na L. Vydanie: meno vydavateľa, názov miesta; triedte podľa názvu miesta v zostupnom poradí.

C) Zobrazte zoznam všetkých dodávateľov, ktorých mená začínajú na Schus. alebo Loe . začína a kto sedí v Emmendingene. Výstup: Názov dodávateľa, názov miesta, zoradené podľa názvu dodávateľa vzostupne.

D) Zobrazte zoznam všetkých miest, ale iba tých miest, na ktorých má vydavateľ sídlo, a v jeho názve je písmeno n.


E) Poskytnite zoznam všetkých kníh a ich dodávateľov (pozor: pozrite si 3 tabuľky!)

F) Poskytnite zoznam všetkých kníh, ktoré stoja viac ako 10 eur (vydanie: meno dodávateľa, názov knihy, predajná cena) - Pozor: pozrite si 3 tabuľky

G) Vytvorte zoznam všetkých kníh (vydanie: názov knihy, kategória) - pozor: vyhľadajte 3 tabuľky

MySQL06_3: JOIN vs. LEFT JOIN

Použite túto databázu na toto cvičenie: 07mysql/_dumps/customers/customers-simply.sql

Prezrite si nasledujúce dva výroky a vysvetlite, ako sa budú výsledkové tabuľky líšiť. Potom vyskúšajte príkazy a skontrolujte svoju odpoveď.

--- Spustenie MySQL kódu ---
-- Výrok 1: LEFT JOIN
VYBERTE k.customer_id, k.name, o.name OD ZÁKAZNÍKOV AS k LEVÉ PRIPOJTE sa k lokalitám AS o ON alebo postcode = k.ort_postcode OBJEDNAŤ podľa k.customer_id

-- Výrok 2: EQUI-JOIN
VYBERTE k.customer_id, k.name, o.name OD ZÁKAZNÍKA AS k, umiestnenia AS o WHERE alebo PSČ = k.ort_postcode OBJEDNAŤ podľa k.customer_id
--- Koniec kódu ---

MySQL06_4: Cvičenie pri dotazoch až pre 5 tabuliek: Kníhkupectvo

Použite túto databázu na toto cvičenie:
07mysql/_dumps/buchladen/buchladen.sql


A) Hľadáme všetkých dodávateľov so sídlom vo Freiburgu. Zadajte meno dodávateľa, umiestnenie dodávateľa a PSČ.
(Riešenie: Výstupom sú traja dodávatelia.)

B) Hľadáme všetkých vydavateľov, ktorí majú sídlo v Mníchove. Zadajte meno vydavateľa a miesto vydania.
(Riešenie: Existujú traja vydavatelia.)

C) Hľadáme všetky knihy, ktoré vyšli vo vydavateľstve Verlag Assal. Zadajte názov knihy, rok vydania a meno vydavateľa, zoradené podľa roku vydania v zostupnom poradí.
(Rada: Vydávajú sa štyri knihy.)

D) Hľadáme všetky knihy od dodávateľa Schustermann. Názov výstupnej knihy a meno dodávateľa.
(Riešenie: Budú vydané tri knihy.)

E) Všetky thrillery sú hľadané. Výstupný názov a kategória knihy, zoradené podľa názvu knihy.
(Rada: Bude rozdaných päť kníh, prvá z nich je „One Last Kiss“.)

F) Hľadáme všetky romantické romány. Zobraziť názov knihy, kategóriu a meno vydavateľa zoradené podľa názvu knihy vo vzostupnom poradí.
(Riešenie: Výstupom je sedem záznamov, najskôr „Vidiecky lekár a jeho sekretárka“.)

G) Hľadáme všetky knihy od Sabriny Müllerovej. Zadajte priezvisko, krstné meno a názov knihy zoradené podľa názvu knihy v zostupnom poradí.
(Rada: Vrátia sa štyri záznamy, najskôr „Wild Hunt for John Smith - Retribution“.)

H) Hľadané sú všetky thrillery Sabriny Müllerovej. Uveďte meno autora, názov knihy a kategóriu.
(Riešenie: Výstupom sú dve množiny údajov.)

(Varovanie: to ešte vlastne nemôžete!)
Hľadáme všetky knihy od Sabriny Müllerovej, ktoré je možné zaradiť do kategórií thriller alebo humor. Uveďte meno autora, názov knihy a kategóriu.

Aby sa kniha „Wild Hunt on John Smith - The Revenge“ neobjavila dvakrát, musíte výsledok zoskupiť podľa názvu knihy tak, že pred ORDER BY zadáte ORDER BY:

--- Spustenie MySQL kódu ---
SKUPINA PODĽA názvu
--- Koniec kódu ---

Aby sa obe kategórie v tejto knihe zobrazovali aj v stĺpci „description“, nepoužívajte na výstup kategórie iba názov poľa „description“, ale radšej použite
--- Spustenie MySQL kódu ---
GROUP_CONCAT (meno)
--- Koniec kódu ---
To znamená, že u kníh klasifikovaných do niekoľkých kategórií sú všetky kategórie zobrazené v jednom poli oddelené čiarkami.
(Riešenie: Výstupom sú tri dátové záznamy.)