DQL utasítások-2

ORDER BY záradék

A lekérdezés eredménye egy rekordhalmaz, melyben a rekordok sorrendje esetleges. (Ez implementáció-függő, de nem mindig azonos a rekordok felvitelének sorrendjével, mert törlések-módosítások esetén üres lapok képződhetnek egy táblában, melyeket az adatbáziskezelő felhasználhat pl. egy új rekord hozzáadásánál.) Lehetőségünk van arra, hogy a lekérdezésben kérjük a visszakapott halmaz rendezését az „ORDER BY” záradékkal:

1-26. ábra -

kepek3/15fejezet-1-26.jpg


Alapértelmezett rendezés a növekvő sorrend, ha ettől el szeretnénk térni, az oszlopnév utáni „DESC” kulcsszóval jelezhetjük:

1-27. ábra -

kepek3/15fejezet-1-27.jpg


A rendezésben több oszlop is részt vehet, a rendezés irányát oszloponként megadhatjuk.

1-28. ábra -

kepek3/15fejezet-1-28.jpg


Mielőtt tovább mennénk, töltsük fel a „Kiadasok” táblánkat néhány rekorddal. A feltöltés során alkalmazzuk az alkérdések használatát, melyek mindig egy skaláris értéket adnak eredményül (ellenkező esetben hibaüzenetet kapnánk):

Táblák összekapcsolása

Mindeddig olyan egyszerű lekérdezéseket írtunk, ahol egyetlen táblából nyertünk ki adatot. Nyilván érezzük, hogy nem sok értelme lenne a táblák közötti kapcsolatoknak, ha csak ennyire volna lehetőségünk. Két vagy több táblát összekapcsolhatunk, ha a „SELECT” utasítást a következőképpen módosítjuk:

SELECT oszlopfelsorolás FROM táblafelsorolás

WHERE feltételek

Ha a feltételeket elhagyjuk, a két tábla Descartes szorzatát kapjuk eredményül, az eredményhalmaz számossága éppen a két halmaz számosságának a szorzata lesz:

Ebből az eredményhalmazból természetesen csak azokra a sorokra vagyunk kíváncsiak, ahol tényleges kapcsolat van a két sor között, vagyis ahol a „Konyvek” tábla sorában szereplő „Iro_FK” érték megegyezik az „Irok” táblában lévő „Iro_PK”-val. Ezt a feltételt többféleképpen megadhatjuk, pl. a „WHERE” feltételben:

1-29. ábra -

kepek3/15fejezet-1-29.jpg


Láthatjuk, hogy az oszlopfelsorolás helyetti „*” mindegyik tábla összes oszlopát szolgáltatja, az oszlopokat a táblafelsorolás sorrendjében szerepeltetve. Szűkítsük a megjelenítendő oszlopok körét. Mivel több táblából válogathatunk adatokat, ezért az oszlopok neve előtt a táblát is feltüntettük, ahonnan az oszlop származik „táblanév.oszlopnév” formátumban. Erre elsősorban akkor van szükség, ha mindkét táblának van olyan nevű oszlopa, melyre hivatkozunk, egyébként elhagyható. Ezzel együtt mindenképpen javasoljuk a használatát, mert egy táblamódosítás során új oszlopot hozzáadva egy táblához könnyen előfordulhat, hogy az az oszlopnév már szerepel egy kapcsolt táblában, így egy korábban helyesen lefutó lekérdezésünk egyszerre hibára fut.

1-30. ábra -

kepek3/15fejezet-1-30.jpg


A „FROM” utáni táblafelsorolásban a táblákhoz alias neveket rendelhetünk. Ha ezt megtesszük, a táblák hivatkozott oszlopaira mindenhol az alias névvel kell hivatkoznunk. Az előző lekérdezésünket átírtuk ennek megfelelően, az eredményhalmaz ugyanaz lesz, mint amit a mutat:

Másik lehetőség az összekapcsolásra a „JOIN” használata:

SELECT oszlopfelsorolás

FROM tábla1 {INNER|RIGHT OUTER|LEFT OUTER|FULL OUTER} JOIN

tábla2 ON összekapcsolási feltétel ….

Mi a különbség az „INNER” és az „OUTER” típusú összekapcsolások között? „INNER JOIN”-ról akkor beszélünk, ha azok és csak azok a rekordok kerülnek bele az eredményhalmazba, melyek az összekapcsolási feltételnek megfelelnek. „LEFT OUTER JOIN” esetén az első tábla összes rekordjához megpróbálunk rekordokat párosítani a második táblából a feltétel alapján. Ha sikerül, akkor az belekerül az eredményhalmazba. Ha nem, akkor az első tábla pár nélküli sorai is az eredményhalmazba kerülnek, miközben a második tábla oszlopait NULL értékkel szerepeltetjük. A „RIGHT OUTER JOIN” értelemszerűen az első és második tábla megfordításával adódik. „FULL OUTER JOIN” esetén a párosított sorok mellett mindkét táblából szerepeltetjük a nem párosítható sorokat, a megfelelő oszlopértékeket „NULL”-al.

Ahhoz, hogy a különböző típusú összekapcsolásokat megértsük, szúrjunk be néhány sort a „Konyvek” és az „Irok” táblába. Mindegyik sort úgy állítottuk össze, hogy ne legyen kapcsolata a másik táblával.

Lássuk tehát, hogyan alakul a különböző típusú „JOIN”-ok eredménye. Az „INNER JOIN” hatására a szerinti eredményhalmazt kapjuk:

A „LEFT OUTER JOIN” hatására eggyel több sor kerül az eredménytáblába, mégpedig a következő tartalommal: az „Irok” tábla oszlopaiban az Irok_PK=11 sor értékeivel, a „Konyv” tábla oszlopaiban pedig csupa „NULL” elemmel:

1-31. ábra -

kepek3/15fejezet-1-31.jpg


A „RIGHT OUTER JOIN” a „Konyvek” táblából veszi azokat a sorokat, melyekhez nem talál párt az „Irok” táblában, vagyis az „INNER JOIN”-hoz képest két sorral bővül az eredménytáblánk:

1-32. ábra -

kepek3/15fejezet-1-32.jpg


Végül a „FULL OUTER JOIN” a „RIGHT” és a „LEFT” egyesítése lesz, mindkét táblából bekerülnek a pár nélküli sorok:

1-33. ábra -

kepek3/15fejezet-1-33.jpg


Több táblát is összekapcsolhatunk a megismert módszer kiterjesztésével. A következő lekérdezésben a 4 táblánk összekapcsolásával előállítjuk azt a táblát, ami tulajdonképpen azokat az adatokat tartalmazza, mint a kiindulásként megadott . Figyeljük meg, hogy az alias nevek megadásánál nem használtuk az „AS” kulcsszót, mivel elhagyható (bár sokszor megkönnyíti az olvasást).

1-34. ábra -

kepek3/15fejezet-1-34.jpg


Az összekapcsolás során bármely tábla helyett tetszőleges alkérdést is írhatunk. Az előző lekérdezésünket átírhatjuk a következő módon, az eredmény ugyanaz lesz:

Táblák egyesítése, metszete és különbsége

A fenti halmazműveleteket akkor végezhetjük el két (vagy több) lekérdezésen, ha mindegyik lekérdezésben megegyezik az oszlopok száma, típusaik pedig sorrendben típus-kompatibilisek.

Egyesítés (unió)

Az egyesítés művelete az „UNION” vagy „UNION ALL”. A „UNION” a hagyományos értelemben vett halmazegyesítést valósítja meg, vagyis az azonos sorok csak egyszer szerepelnek az eredményhalmazban, ezzel szemben a „UNION ALL” minden előfordulást (az azonosakat is) belevesz az eredménybe.

1-35. ábra -

kepek3/15fejezet-1-35.jpg


1-36. ábra -

kepek3/15fejezet-1-36.jpg


A „UNION ALL” hatására az 1. és a 7. sor, ugyanolyan tartalommal, kétszer szerepel az eredményhalmazban (mivel mindkét lekérdezésben benne vannak):

1-37. ábra -

kepek3/15fejezet-1-37.jpg


Az egyesítés során kizárólag típus megfeleltetés történik, a következő lekérdezés hibátlanul lefut, bár az eredménynek nem sok értelme van. Figyeljük meg, hogy az eredmény-oszlopok neve megegyezik az első lekérdezésben szereplő oszlopnevekkel.

1-38. ábra -

kepek3/15fejezet-1-38.jpg


Az egyesítés során a különböző oszloptípusok között implicit típuskonverzió zajlik, a típus-precedenciának megfelelően, az eredményoszlop típusa a magasabb precedenciájú típus lesz.

1-39. ábra -

kepek3/15fejezet-1-39.jpg


Táblák metszete

Két lekérdezés metszetét azok a sorok képezik, melyek mindkét lekérdezésben előfordulnak. Az eredményhalmazban a közös sorok pontosan egyszer szerepelnek. A metszet operátora az „INTERSECT”.

1-40. ábra -

kepek3/15fejezet-1-40.jpg


Táblák különbsége

Két lekérdezés különbsége az a halmaz, melyben azok a rekordok szerepelnek, melyek az első lekérdezésben benne vannak, de a másodikban nincsenek. A különbséget az „EXCEPT” operátorral tudjuk megvalósítani.

1-41. ábra -

kepek3/15fejezet-1-41.jpg


GROUP BY záradék

A lekérdező „SELECT” utasításban csoportokat képezhetünk a rekordhalmaz meghatározott oszlopai szerint. Ekkor az eredményhalmazban minden csoportelőfordulás pontosan egyszer szerepel. A csoportképzés utasítása a „GROUP BY” záradék. Nézzünk egy egyszerű példát rá:

A lekérdezés eredménye egy olyan halmaz, ahol minden „Iro_FK” érték szerepel, ami a „Konyvek” táblában van, de mindegyik pontosan egyszer. Ez eddig teljesen ugyanúgy működik, mintha azt írtuk volna, hogy:

A csoportképzésben ezzel egyidejűleg használhatunk ún. aggregát függvényeket. Az aggregát függvényeket azokra a mezőkre alkalmazzuk, melyek a csoportképzésben nem vesznek részt, ugyanakkor összevont információt tartalmaznak a csoportban lévő egyedek darabszámáról, összegéről, átlagáról, stb… Ezek a következők:

  • COUNT(oszlop)

    • A csoportban szereplő oszlop előfordulások darabszámát adja vissza. Két ugyanolyan tartalmú oszlop 2 darabnak számít. Ezt elkerülhetjük, ha COUNT(DISTINCT oszlop)-ot használunk. Az oszlop helyett adhatunk „*”-ot is, ekkor a csoportban szereplő összes sor darabszámát kapjuk.

  • SUM(oszlop)

    • A csoportban szereplő oszlop-értékek összege. Az oszlopnak értelemszerűen numerikus típusúnak kell lennie.

  • AVG(oszlop)

    • A csoportban szereplő oszlop-értékek átlaga. Az oszlopnak értelemszerűen numerikus típusúnak kell lennie.

  • MIN(oszlop)

    • A csoportban szereplő oszlop-értékek legkisebbike. Bizonyos implementációkban ez csak numerikus lehet, máshol karakteres típusú oszlop is elfogadott.

  • MAX(oszlop)

    • A csoportban szereplő oszlop-értékek legnagyobbika. Bizonyos implementációkban ez csak numerikus lehet, máshol karakteres típusú oszlop is elfogadott.

Számoljuk meg, hogy egy írónak hány könyve van a „Konyvek” és az „Irok” tábla összekapcsolásával. Az összekapcsolási feltételt a „WHERE” záradékban adjuk meg:

1-42. ábra -

kepek3/15fejezet-1-42.jpg


A csoportképzésben nem csak egy, hanem több oszlop is részt vehet. Vegyük észre, hogy a „GROUP BY” záradékban pontosan azok az oszlopok szerepelnek, amelyek a „SELECT” utáni, aggregát függvényt nem tartalmazó oszlopfelsorolásban (az oszlopok sorrendje nem lényeges).

1-43. ábra -

kepek3/15fejezet-1-43.jpg


Ha olyan oszlopo(ka)t szerepeltetünk a lekérdezésben, amely(ek) a „GROUP BY” záradékban nincsen(ek) felsorolva, hibaüzenetet kapunk:

Ugyanakkor kevesebb oszlopunk lehet a „SELECT” listában, mint a „GROUP BY” felsorolásban, bár ettől az eredményünk valószínűleg értelmetlen lesz:

1-44. ábra -

kepek3/15fejezet-1-44.jpg


A következő példában alkalmazzuk a megismert aggregát függvényeket. Figyeljük meg, hogy az aggregát függvényekkel műveleteket is végezhetünk:

1-45. ábra -

kepek3/15fejezet-1-45.jpg


A „GROUP BY” záradékban a „HAVING” kulcsszó után megadhatunk feltételt, mely az aggregát oszlopok értékeire vonatkozik. Az előző lekérdezésünket egészítsük ki azzal a feltétellel, hogy csak azokra az írókra vagyunk kíváncsiak, akitől legalább 3 darab könyvünk van, és mindegyik könyv 1900 után született:

1-46. ábra -

kepek3/15fejezet-1-46.jpg


A „GROUP BY” záradékkal összesítő sorokat is beszúrhatunk a lekérdezésünkbe a „ROLLUP” vagy a „CUBE” használatával. A „ROLLUP” hatására egyrészt minden csoportra megképződik a „GROUP BY” aggregát sor, a csoportosításban résztvevő oszlopokra részösszesen sorok, valamint egy mindösszesen sor is.

Futtassuk le a következő két lekérdezést, először „ROLLUP” nélkül, majd azzal.

1-47. ábra -

kepek3/15fejezet-1-47.jpg


Az eredeti sorokhoz hozzáadódott kiadónként egy összesen sor, ezekben az „IroNev” helyén NULL érték szerepel, valamint egy mindösszesen sor, amiben a „KiadoNev” és az „IroNev” is NULL értékkel van kitöltve.

A „CUBE” nem csak a „GROUP BY” oszlopfelsorolásban kifelé haladva képzi meg az összesen sorokat, hanem minden lehetséges oszlopkombinációban. Nézzük meg ezt az előző lekérdezésben:

1-48. ábra -

kepek3/15fejezet-1-48.jpg


Most nem csak a „KiadoNev” értékeire képződtek összesen sorok, hanem az „Ironev” összes lehetséges értékére is, és ott a „KiadoNev” oszlop kapott NULL értéket.