A MySQL több tábla (JOINs) IT-központon keresztül kérdez
tananyagok anyaga informatika órákhoz
(utolsó változás ezen az oldalon: 2016.11.13.)
Videó bemutató
Ha csak a videó tetszik megnézi, aligha tanul meg valamit. A legjobb tanulási hatás elérése érdekében dolgozzon közvetlenül a számítógépen, és kövesse a példákat.
Fóliák
(a PDF-prezentáció utolsó szinkronizálása: 2017.08.02.)
Ha nem látja a PDF-prezentációt, kattintson ide a letöltéshez: Közvetlen letöltés PDF előadás
Ez a sorrend a következő tartalom ismeretét feltételezi: Bevezetés a SELECT lekérdezésekbe a MySQL-ben.
Gyakorlatok részletes megoldásokkal és magyarázatokkal itt található: Gyakorlatok a JOIN-okról a MySQL-ben
MySQL dump "customer-simply.sql"
Annak érdekében, hogy megérthesse a diák és a videó bemutató példáit, töltse le ezt a MySQL kiírást: customer-simply.sql, nyissa meg a Workbench-ben (File -> Open SQL Script ...), és hagyja futni. Létrejön egy "Ügyfelek" adatbázis, amely két táblázatot tartalmaz ("Helyek" és "Ügyfelek").
Lekérdezési adatok két táblából
Példa forgatókönyvre: A kép két összekapcsolt táblázatot mutat, amelyek idegen kulccsal vannak összekapcsolva. Gondoljon arra, hogy melyek az elsődleges kulcsok és melyek az idegen kulcsok.
Megoldás:
Ügyfelek (Ügyfél-azonosító, ügyfél neve, ügyfél neve, ügyfél telefonja, ↑ ügyfél országa, ügyfél száma)
országok(landID, countryName, countryDelivery status)
Az elsődleges kulcsok tehát az ügyfél-azonosító és az ország-azonosító (az aláhúzással felismerhetők), a külföldi kulcs az ügyfél-tábla az ügyfél-tábla (az ügyfél-ország előtti nyíllal felismerhető). A kundeLand idegen kulcs a táblázat országainak elsődleges kulcsára utal.
Az „ügyfél” fül tehát egy „customerLand” mezőt tartalmaz. Itt adják meg egy ország azonosítóját, amely a landID mezőre utal az „országok” táblázatban.
Ha az ügyfél nevét és az ország nevét teljes szövegben szeretné megjeleníteni a SELECT lekérdezésben, akkor mindkét táblából be kell töltenie az adatokat.
Kód a két táblázat előállításához:
néhány dummy adattal:
1. lehetőség: CSATLAKOZZ/BALADJ CSATLAKOZÁS
Kiválasztja a megadott mezőket (customerID, customerName, customerCountry, countryID, countryName) a két tábla vevő és ország (customer JOIN országok) közül, és csak azokra az esetekre korlátozódik, ahol a customerCountry megegyezik az országID-del (WHERE customerCountry = countryID).
Ha a WHERE utasítás itt elhagyásra kerül, az „ügyfelek” összes adatrekordját az „országok” összes adatrekordjával kombináljuk, így 3 ország és 4 ügyfél esetén összesen 12 sort adunk ki (ebből négyet háromszor adunk ki - tehát 8 felesleges).
Ezt az értelmetlen eredményt derékszögű terméknek nevezzük. Mindig nézze át a lekérdezés eredményeit, hogy meggyőződjön arról, hogy a lekérdezés eredményeit helyes megszorításokkal értelmezte.
Ha a táblákban Azonos nevű mezők meg kell adni azt a táblázatot, amelyből az értéket vesszük, szintaxis: tábla neve.mezõ neve
BAL CSATLAKOZÁS
Ha az „ügyfelek” táblában olyan érték van megadva, amely nem található meg az „országok” táblázatban, akkor ez az adatrekord nem kerül kiadásra. Ilyen helytelen adatrekord a fenti SQL felsorolás mellett lenne, pl.
A landID 9 nem létezik, ezért az adatrekord nem kerül kiadásra a fenti lekérdezésben.
A LEFT JOIN opcióval a bal („bal”) táblázat összes adatrekordja kimenetre kerül:
Figyelem: A "WHERE" helyébe "ON" lép.
2. lehetőség: implicit jelölés
A JOIN kifejezett megírása helyett egyszerűen vesszővel választhatjuk el a lekérdezendő táblázatokat:
Egyébként a INNER JOIN pontosan megegyezik a JOIN-nal. Az egyik szintaktikus cukorról beszél.
Feladatok
Minden gyakorlat megtalálható az anyaggyűjteményben (ott minden további fájl, például képek, osztálydiagramok vagy HTML-sablonok!).
Az aktuális gyakorlatot itt töltheti le txt fájlként.
***** Gyakorlatok: MySQL - SELECT: Több tábla lekérdezése JOIN-okkal *****
MySQL06_1: derékszögű termék
Magyarázza el saját szavaival, miért nem ad értelmes eredményt ez a lekérdezés:
--- MySQL kód indul ---
SELECT * FROM ügyfelek, helyek
--- A kód vége ---
MySQL06_2: Egyszerű lekérdezések két táblán - DB ügyfél-egyszerű
Használja ezt az adatbázist ehhez a gyakorlathoz: 07mysql/_dumps/customers/customers-simply.sql
A) Írja be az összes ügyfél nevét, irányítószámát és városát. A lista tartalmazza az ügyfél nevét és a lakóhely nevét.
B) Írja be a 79312 irányítószámmal rendelkező összes ügyfél nevét és lakóhelyét.
C) Adja meg az összes Emmendingenben élő ügyfél nevét és lakóhelyét (a korlátozási kritérium NEM az irányítószám, hanem az „Emmendingen”).
D) Írja be minden olyan ügyfél nevét, lakóhelyét és lakosainak számát, akik 70 000 lakosnál nagyobb helyen élnek.
E) Sorolja fel az összes olyan helyet, ahol kevesebb mint 1 000 000 lakos van.
F) Írja be az összes olyan ügyfél nevét és helynevét, akik 100 000–1 500 000 lakosú helyeken élnek.
G) Írja be az ügyfelek nevét, irányítószámát és városnevét mindazon ügyfelek számára, akiknek neve "e" -et tartalmaz, valamint minden olyan várost, amely "u" -ot vagy "r" -et tartalmaz (tehát a frUd from stUden pontosan úgy jelenik meg, mint jEssE a bRnz-től) FrEd sóktól, de nem, és a martin sem Hamburgból).
MySQL06_2a: Gyakorlatok két tábla lekérdezésein: Könyvesbolt
Használja ezt az adatbázist a gyakorlathoz:
07mysql/_dumps/buchladen/buchladen.sql
A) Sorolja fel az összes olyan szállítót, aki Reute-ben él. Kimenet: beszállító neve, helyneve, irányítószáma
B) Jelenítsen meg egy listát azokról a kiadókról, akiknek neve L betűvel kezdődik. Kiadás: kiadói név, helységnév; kérjük rendezni helynév szerint csökkenő sorrendben.
C) Jelenítse meg az összes beszállító listáját, akiknek neve Schus-szal kezdődik. vagy Loe . kezdődik és akik Emmendingenben ülnek. Kimenet: Szállító neve, helyneve szállító neve szerint rendezve növekvő sorrendben.
D) Jelenítsen meg egy listát az összes helyről, de csak azokat a helyeket, ahol a kiadó a n nevével rendelkezik.
E) Adja meg az összes könyv és beszállító listáját (figyelem: olvassa el 3 táblázatot!)
F) Adjon meg egy listát az összes könyvről, amelynek ára meghaladja a 10 eurót (kiadás: beszállító neve, könyv címe, eladási ár) - Vigyázat: olvassa el 3 táblázatot
G) Adja ki az összes könyv listáját (kiadás: könyv címe, kategória) - Vigyázat: 3 táblázat lekérdezése
MySQL06_3: CSATLAKOZÁS vs. LEFT CSATLAKOZÁS
Ehhez a gyakorlathoz használja ezt az adatbázist: 07mysql/_dumps/customers/customers-simply.sql
Nézze meg a következő két állítást, és magyarázza el, hogy az eredménytáblák hogyan fognak eltérni. Ezt követően próbálja ki a parancsokat, és ellenőrizze a választ.
--- MySQL kód indul ---
-- 1. kijelentés: BAL CSATLAKOZÁS
KIVÁLASZTÁSA k.customer_id, k.name, o.name ügyfelektől AS k LEFT JOIN helyek ON BE vagy irányítószám = k.ort_postcode RENDELÉS k.customer_id
-- 2. állítás: EQUI-JOIN
KIVÁLASZTÁSA k.customer_id, k.name, o.name from AS AS k, helyek AS o WHERE vagy irányítószám = k.ort_postcode ORDER BY k.customer_id
--- A kód vége ---
MySQL06_4: Gyakorlatok legfeljebb 5 táblázat feletti lekérdezésekhez: Könyvesbolt
Használja ezt az adatbázist a gyakorlathoz:
07mysql/_dumps/buchladen/buchladen.sql
A) Minden beszállítót keresünk, akik Freiburgban vannak. Adja meg a szállító nevét, szállítójának helyét és irányítószámát.
(Megoldás: Három beszállító van.)
B) Minden müncheni székhelyű kiadót keresünk. Adja meg a kiadó nevét és megjelenési helyét.
(Megoldás: Három kiadó van.)
C) Várunk minden olyan könyvet, amelyet Verlag Assal adott ki. Adja meg a könyv címét, a megjelenés évét és a kiadó nevét a megjelenés éve szerint rendezve csökkenő sorrendben.
(Tipp: Négy könyv készül.)
D) Minden könyvet keresünk a Schustermann szállítótól. Kiadási könyv címe és szállító neve.
(Megoldás: Három könyvet adnak ki.)
E) Minden thrillert keresnek. Kiadási könyv címe és kategóriája könyv címe szerint rendezve.
(Tipp: Öt könyvet adnak ki, az első az "Egy utolsó csók".)
F) Minden romantikus regényt keresünk. Jelenítse meg a könyv, kategória és kiadó nevét, növekvő sorrendben, a könyv címe szerint rendezve.
(Megoldás: Hét feljegyzést adnak ki: "A vidéki orvos és titkára" először.)
G) Sabrina Müller összes könyvét keressük. Írja be a szerző vezetéknevét, utónevét és könyvének címét, sorrendben a könyv címe szerint.
(Tipp: Négy rekordot adunk vissza, először a "Vad vadászat John Smithre - megtorlás".)
H) Sabrina Müller összes thrillere a keresett. Írja be a szerző nevét, a könyv címét és a kategóriát.
(Megoldás: Két adatsor kerül kimenetre.)
(Figyelem: ezt még nem lehet megtenni!)
Keresünk Sabrina Müller összes könyvét, amely a thriller vagy a humor kategóriákba sorolható. Írja be a szerző nevét, a könyv címét és a kategóriát.
Annak érdekében, hogy a "Vad vadászat John Smithen - A bosszú" könyv ne jelenjen meg kétszer, az eredményt könyv címe szerint kell csoportosítania, hozzáadva a ORDER BY szót a ORDER BY elé:
--- MySQL kód indul ---
GROUP BY cím
--- A kód vége ---
Annak érdekében, hogy mindkét kategória megjelenjen a könyv "leírás" oszlopában, ne egyszerűen a "leírás" mezőnevet használja a kategória kimenetére, hanem inkább
--- MySQL kód indul ---
GROUP_CONCAT (név)
--- A kód vége ---
Ez azt jelenti, hogy a több kategóriába sorolt könyvek esetében az összes kategória egy mezőben jelenik meg, vesszővel elválasztva.
(Megoldás: Három adatrekord készül.)