MySQL mindenkinek 5

A következő leírás kipróbálgatására szükségünk lesz egy adatokkal feltöltött adatbázisra. Akinek nincs éppen kéznél az letöltheti innen. Ebben a részben a táblakapcsolatokkal fogunk mélyebb ismeretségbe keveredni.

Az első és második részben csak átfutottunk a táblakapcsolatok felett, most szeretném pótolni az itt tátongó hiányt.

A táblakapcsolatok adják az adatbázisok egyiknagy erejét. Mint láttuk csökkenthető segítségükkel a redundancia, ugyanazt az információt kisebb helyen el tudjuk tárolni, és az adatok karbantartása is gyerekjátékká válik a segítségükkel. Két adatbázis tábla többféle kapcsolatban is állhat egymással.

Nincs kapcsolat

Két tábla nincs közvetlen kapcsolatban egymással ha nem tartalmaznak közvetlenül egymásra utaló hivatkozásokat. A lenti példában ilyen például a diákok és a tanárok tábla, ezek között nincs közetlen adatbázis kapcsolat.

Egy a többhöz kapcsolat (1:n)

Ebben a kapcsolat típusban az egyik tábla egyetlen rekordja a másik tábla több rekordjához kapcsolódik. A példánk szerint egy tanár több osztálynak is lehet osztályfőnöke, vagy egy felhasználónak lehet a rendszerben rögzítve több hozzászólása is. Ez a kapcsolat nem fordítható meg, vagyis biztosan tudjuk, hogy egy bizonyos hozzászólás egyértleműen hozzátartozik egy userhez. Ilyen esetekben a második táblában szerepel egy mező az első tábla PRIMARY KEY-ével. Így kerül az osztályok táblába egy osztalyfonok_id mező, ami valójában a tanár tábla kapcsolódó id-jét fogja befogadni.

Egy az egyhez kapcsolat (1:1)

Az egyik adattábla egyik rekordja a másik tábla egy rekordjához kapcsolódik. Például a felhasználónak egy profilja van és a profil egyértelműen hozzátartzik egy userhez.

Több a többhöz kapcsolat (n:m)

Az egyik adattábla egyik rekordja a másik adattábla több rekordjával van kapcsolatban és a másik adattábla egy rekordja az első adattábla több rekordjávl áll kapcsolatban. Az ilyen kapcsolatok ábrázolására be kell vezetnünk egy kapcsolat leíró táblát. Ilyen a tanar_tantargy tábla, amely azt mutatja, hogy egy tanár több tantárgyat is taníthat és egy tantárgyat többen is taníthatnak. Legkönnyebben érthető példája a cimkék. Egy cimkét tetszőleges mennyiségű bejegyzésre ráragaszthatunk egy blogban, és egy bejegyzésre tetszőleges mennyiségű cimkét ragaszthatunk.

Lekérések kapcsolt táblákból

Ha belenézünk a letöltött minta adatbázisba, akkor látni fogjuk, hogy az osztalyok táblában van 4 osztályunk, van 99 diákunk és 24 tanárunk. A diak_osztaly tábla is fel van töltve adatokkal de az embereke számára már nem egykönnyen emészthető. Egy ilyen nyilvántartó program, meglehetősen leegyszerűsítve ugyan, de a valóságban is így néz ki. Egy alap lekérdezés során arra leszünk kíváncsiak, hogy mondjuk az 1/A osztályba kik járnak.

Arról a PRIMARY KEY-jel foglalkozó részben beszéltünk, hogy ez az ami egyértelműen azonosítja a rekordokat, illetve azt is láttuk, hogy a kapcsolt táblákban ez jelenik meg a rekordok összepárosítása érdekében. Tehát ha kíváncsiak vagyunk arra, hogy ki jár az 1/A osztályba akkor a következőket kell tennünk:

1. az osztalyok táblában keressük meg az 1/A osztály id-jét
2. a diak_osztaly táblában keressük ki azokat a sorokat ahol az osztalyok_id oszlopban az előbb megtalált id szerepel
3. az így kapott sorokból szedjük ki a diakok_id értékeit és keressük meg ezeket a diakok táblában

Nézzük valójában hogyan is néz ki egy ilyen lekérés.

Először is fel kell sorolnunk, hogy melyik adatokat akarjuk majd a végső eredményben megkapni. Esetünkben a diákok neveire vagyunk csak kíváncsiak.

SELECT diakok.diak

Másodszor fel kell sorolnunk, hogy melyik táblákból akarjuk összerakni az eredményt. Ezek a diaok, az osztalyok és a diak_osztaly táblák lesznek.

SELECT diakok.diak
	FROM diakok, osztalyok, diak_osztaly

Ez már egy szintaktikaliag megfelelő SELECT utasítás, akár le is futtathatjuk. 99 diákunk van, ez a lekérés viszont 39.204 sort ad vissza, és szemmel láthatóan a nevek ismétlődnek benne. Ez azért van, mert ha a sELECT utasítás után több táblát sorolunk fel, akkor létrejön egy ideiglenes tábla amelyben a felsorolt táblák minden sora minden másik táblákbeli sorral párosításra kerül. A diakok táblában 99 rekord van, az osztalyok táblában 4, a diak_osztaly táblában szintén 99, vagyis ezeket 99*4*99 variációban lehet egymás mellé pakolni, ezért kapunk eredményül 39.204 sort.
Ha a felsorolt mezőket kicseréljük egy *-ra és lefuttatjuk a SELECT * FROM diakok, osztalyok, diak_osztaly utasítást, akkor az eredményben látni is fogjuk a létrejövő ideiglenes, mindent magába foglaló nagy táblát.

Az összes további utasítás már csak arra szolgál, hogy az így létrejött hatalmas ideiglenes táblából kiszedegessük azokat a sorokat ami nekünk ebből ténylegesen kell.

Következő lépésként a táblák között fenálló táblakapcsolatokat kell leírnunk. Tóth Éva 396-szor szerepel, mivel minden sor minden sorral párosítsra kerül. De melyik az ami ezek közül értelmes is? Az első oszlopban van a diak tábla id-je, az utolsó előtti oszlopban pedig a diak_osztaly táblában szereplő diakok_id. Nekünk ugye az kell ahol ez a kettő megegyezik, azaz azok a sorok ahol a diakok tábla id-je és a diak_osztaly tábla diak_id-je összepárosítható. Lássuk!

SELECT *
	FROM diakok, osztalyok, diak_osztaly
	WHERE diakok.id = diak_osztaly.diakok_id

A WHERE feltételiben nem elég a mezőneveket megadni, hanem a táblanév.mezőnév formátumban kell őket megnevezni, mivel előfordulhatnak ütközések. Pl id mező van a diakok és az osztaélyok táblában is.

Eredményül 396 sort kapunk, ami azért nem tökéletes, de jobb mint a 39.000. Tóth Évából is már csak 4 van. Ez amiatt van, hogy a diakok tábla rekordjait már összepárosítottuk a diak_osztaly táblával, de az osztalyok tábla sorait még nem.

SELECT *
	FROM diakok, osztalyok, diak_osztaly
	WHERE diakok.id = diak_osztaly.diakok_id
	AND osztalyok.id = diak_osztaly.osztalyok_id

99 sor, pontosan ennyi diákunk van! Viszon bennünket nem érdekelnek most csak az 1/A-sok. Következő lépésünkben meg kell mondanunk, hogy az összes sor párosítás közül mi azokra vagyunk kíváncsiak ahol fenáll az a tény, hogy az osztaly id-je 1, azaz az 1/A osztalyról van szó.

SELECT *
	FROM diakok, osztalyok, diak_osztaly
	WHERE diakok.id = diak_osztaly.diakok_id
	AND osztalyok.id = diak_osztaly.osztalyok_id
	AND osztalyok.id = 1

Összesen 25 sort kaptunk eredményül ami már egész elfogadható osztálylétszám. Az utolsó sorba azt is írhattuk volna hogy AND osztalyok.osztaly = '1/A' ugyanezt az eredményt kaptuk volna. Hogy miért nem ezt tettük arra majd később fogunk visszatérni, akkor maikor az indexekkel foglalkozunk.

Persze nekünk nincs szükségünk a felesleges sallangokra, csupán a diákok neveire. Cseréljük vissza a *-ot az eredeti diakok.diak-ra.

SELECT diakok.diak
	FROM diakok, osztalyok, diak_osztaly
	WHERE diakok.id = diak_osztaly.diakok_id
	AND osztalyok.id = diak_osztaly.osztalyok_id
	AND osztalyok.id = 1

Aki ismer pár angol szót, az elolvashatja a fenti parancsot.
VÁLASZD KI a diakok.diak-ot
a diakok, osztalyok, diak_osztaly táblákból
AHOL a diakok.id = diak_osztaly.diakok_id
ÉS az osztalyok.id = diak_osztaly.osztalyok_id
ÉS az osztalyok.id = 1

A feltétel felcserélhetőek itt azért pont ebben a sorrendben szerepelnek met csak.

Megismerkedtünk a WHERE, és az AND jelentésével, ide kívánkozik még az OR, azaz a vagy. Kérjük le azoknak a diákoknak a nevét akik vagy az 1/A-ba vagy a 2/A-ba járnak.

A lekérdezésünk eleje teljesen azonos az előzővel, csak a végét módosítjuk.

SELECT diakok.diak
	FROM diakok, osztalyok, diak_osztaly
	WHERE diakok.id = diak_osztaly.diakok_id
	AND osztalyok.id = diak_osztaly.osztalyok_id
	AND
		(osztalyok.id = 1 OR
		osztalyok_id = 4)

Ha belekerül a feltételek közé egy OR is, akkor már nem feltétlenül csereberélhetőek fel a feltételek sorrendje.