KURZUS: Informatikai rendszerek alapjai
MODUL: Adatbázis-kezelés
3. lecke: Lekérdezések
Cél: Az adatkezelő feladatok közül a legbonyolultabb, amikor egyetlen paranccsal valamely adatot (adatokat) akarunk kinyerni az adatbázisból. Az SQL nyelvben alapvetően a SELECT parancs alkalmas arra, hogy a tárolt adatokat kinyerhessük. E parancs emiatt szerteágazó, bonyolult, így külön leckét szentelünk a megtanulásának. Bár az adatbázis-kezelés szóhasználata szerint minden, az adatbázis-szerver felé küldött parancs lekérdezés (query), sokszor mégis csak az adatok kinyerésére szolgáló parancsokat nevezzük lekérdezésnek, hiszen egyéb esetben nem információszerzésre irányuló kérdéseket teszünk fel, hanem utasítás jellegű parancsokat adunk ki. Vegyük emellett észre, hogy a SELECT parancsban sem kérdezünk nyelvtani értelemben, hanem az SQL logikája szerint utasítást adunk: a parancsokat szó szerint magyarra fordítva, a lekérdező parancsok így kezdődnek: "Válogasd ki nekem azokat az adatokat, amelyek..." | ||
Követelmények: Ön akkor sajátította el megfelelően a tananyagot, ha | ||
| ||
Időszükséglet: A tananyag elsajátításához (a feladatok megoldásával együtt) hozzávetőlegesen 6 órára lesz szüksége. | ||
Kulcsfogalmak | ||
| ||
A SELECT parancs | ||
A SELECT utasítás az egyetlen parancs, amivel adatokat nyerhetünk ki az adatbázisból. Ez már önmagában sejteti, hogy egy szerteágazóan használható paranccsal van dolgunk: a SELECT a legösszetettebb az SQL parancsok között. Ebben a leírásban megmutatjuk a használatát a legfontosabb esetekben, de több részletre egyáltalán nem térünk ki, ezért ez a bemutatás nem teljes. Elegendő azonban ahhoz, hogy jól átlássuk a működési logikáját és dokumentáció alapján tetszőlegesen bonyolult lekérdezéseket is összeállíthassunk. | ||
Az ismerkedés elején érdemes azonban azt is megérteni, hogy konkrét adatbázis-szerkezet és feladat esetén a modellből adódóan kitalálhatók olyan lekérdezések, amit nem tudunk egyetlen SELECT paranccsal megvalósítani - tehát nem biztos, hogy a mi hibánk, ha egy bonyolult esetre nem sikerül az elvárt végeredményt biztosító egyetlen parancsot megalkotni - ilyen esetekben kerülőutakon, több lekérdezés egymás után történő végrehajtásával kaphatjuk meg a várt eredményt. Az ilyen helyzetek azonban ritkák, mint látni fogjuk, egészen bonyolult kérdésekre is választ kaphatunk egyetlen jól összerakott SELECT paranccsal. | ||
Tekintsük az 1. ábra táblázatát, ahol az M2 és M4 mezőkre (oszlopokra) van szükségünk, de csak a szelekciós feltétel által meghatározott rekordok (sorok) esetében. Ezt a lekérdezést a következőképpen fogalmazhatjuk meg: | ||
SELECT M2, M4 FROM táblanév WHERE szelekciós feltétel |
A projekció az a művelet, amivel egy reláció (tábla) egyes mezőit (oszlopait) válogatjuk ki (szűkítjük le) egy lekérdezésben. |
|
A szelekció az a művelet, amivel egy reláció (tábla) egyes rekordjait (sorait) válogatjuk ki (szűkítjük le) egy lekérdezésben. |
A szelekció meghatározása a WHERE kulcsszó után történik. A feltételek megadási módjával később, a Szelekciós feltételek című szakaszban foglalkozunk. | |||||||||||||||
A projekció célja, hogy az eredményben csak a számunkra fontos adatok (pontosabban mezők) szerepeljenek. A projekció meghatározása úgy történik, hogy a SELECT és FROM kulcsszavak között tételesen felsoroljuk (vesszővel elválasztva, nekünk tetsző sorrendben) a szükséges mezőket. Ha minden mezőre szükségünk van, akkor a teljes lista begépelését megspórolhatjuk egyetlen * jellel, ebben az esetben a mezők megjelenési sorrendjére nincs befolyásunk, tábladefiníciókor használt sorrendben fognak megjelenni. Ha több táblát használunk, akkor természetesen a táblák mezői pedig eszerint követik egymást. | |||||||||||||||
Azt a táblát, amire a keresésünk vonatkozik, a FROM után írjuk. Ha nem egy tábla, hanem több tábla Descartes-szorzata (lásd Descartes-szorzat, halmazok című szakasz) alapján keresünk, akkor itt lehet a táblákat felsorolni, vesszővel elválasztva. Ebben az esetben szinte biztos, hogy nem az összes párosításra van szükségünk, hanem a WHERE után egy olyan feltételt is megfogalmazunk, ami előírja a több tábla összekapcsolásának módját. | |||||||||||||||
Az alábbi példában megvalósítjuk a korábban, a 4. ábrán bemutatott Autó és Ember táblák 3. ábrán felvázolt összetartozó elemeinek előállítását. A két tábla megtalálható a dbGyak adatbázisban. | |||||||||||||||
SELECT Autó.*, Ember.Név FROM Autó, Ember | |||||||||||||||
A lekérdezés eredménye a 2. ábrán látható: | |||||||||||||||
| |||||||||||||||
Az ábrán látható, hogy ezen lekérdezés végrehajtásán 4 tízezred másodpercig dolgozott a szerver. (Ha ennél többet kellett várnunk, annak már a használt internetes-webes technológia az oka.) Egy ilyen típusú lekérdezés nagyobb táblaméreteknél azonban erősen leterhelheti az adatbázis-kezelő rendszert, hiszen Descartes-szorzat előállítását kértük, ami nagy tábláknál igen nagy méretű lehet! Jobb megoldás ezt a helyzetet mindenképpen elkerülni. | |||||||||||||||
Több tábla összekötésének ennél finomabban szabályozható és általában jelentősen hatékonyabb módja is van, a JOIN. Ennek a szerteágazó műveletnek minden részletével nem foglalkozunk a tananyagban, de használjuk egyszerű eseteit. | |||||||||||||||
Kérjük JOIN művelettel az összetartozó párok előállítását: | |||||||||||||||
SELECT Autó.*, Ember.Név FROM Autó | |||||||||||||||
A példában a JOIN az Ember táblát úgy kapcsolta a relációhoz, hogy az ON kulcsszó után megadtuk az összekapcsolás feltételét. (Figyeljük meg, ez a feltétel ugyanaz, amit a Descartes szorzat használatánál WHERE feltételben adtunk meg.) Bár a használt adatbázis-szerver optimalizáló képességétől is függ, de ez a lekérdezés jellemzően sokkal gyorsabban lefut, ugyanazt a végeredményt produkálva. | |||||||||||||||
A * használatára is láthatunk példát, az Autó táblából minden mezőt, az Emberek táblából csak a nevet íratjuk ki. A két tábla közötti kapcsolatot az adja, hogy járművek táblájában van egy Tulajdonos mező, ahova beírjuk egy személy kulcsmezőjének értékét. | |||||||||||||||
A példa kapcsán észrevehető, hogy ugyanazt a táblanevet többször is le kell írni, ami zavaró lehet, különösen, ha a táblanév hosszú. Ezért van lehetőség - egyetlen lekérdezés erejéig - a tábláinknak egy rövid nevet adni, használjuk most az a (Autó) és e (Ember) betűket táblanév helyettesítő névként (alias), így olvashatóbbak a hosszú parancsok: | |||||||||||||||
SELECT a.*, e.Név FROM Autó a | |||||||||||||||
Sok implementáció-függő speciális módja is van a SELECT használatának, amivel szintén nem foglalkozunk részletesen. A következő számolás például működik, és a várt eredményt adja: | |||||||||||||||
SELECT 5*5; | |||||||||||||||
Fentiek alapján észrevehetjük, hogy a szerteágazó SELECT parancs gyakorlatilag egyetlen elemét sem kötelező megadni. A legegyszerűbb SELECT parancs ezek alapján: | |||||||||||||||
SELECT 1 | |||||||||||||||
Szelekciós feltételek | |||||||||||||||
A szelekciós feltételeket egyetlen logikai kifejezésként a WHERE kulcsszó után adjuk meg. Több feltétel esetén a feltételeket AND, OR, NOT logikai kifejezéssel (ha kell, akár zárójelezéssel) kapcsoljuk össze. Például: | |||||||||||||||
SELECT * FROM tábla WHERE m1<5 AND (m2=3 OR m2>8) | |||||||||||||||
A szelekciós feltételekben használható nyelvi elemek | |||||||||||||||
| |||||||||||||||
SELECT * FROM Autó WHERE Márka IN ('Ford','Opel') | |||||||||||||||
| |||||||||||||||
| |||||||||||||||
SELECT * FROM Ember WHERE Lakcím IS NULL | |||||||||||||||
Érdekesség: A NULL érték című szakaszbana NULL érték kapcsán láttuk, hogy egyszerű összehasonlítással (pl. IF mezo=NULL) nem lehet eldönteni egy adatról, hogy NULL értékű-e, hiszen annak a műveletnek az eredménye is mindig NULL érték lenne. E probléma elkerülésére való az SQL speciális, IS NULL megoldása, vagy az ISNULL() függvény használata, a fenti példában az előbbit használtuk. | |||||||||||||||
Érdekesség: Ha az előző példát kipróbáltuk, azt tapasztaltuk, hogy a szerver nem talált NULL értékű lakcímet. Pedig jól láthatóan a lakcímek nincsenek kitöltve. Ennek az az oka, hogy valójában ki vannak töltve, mégpedig egy üres karakterlánccal (""). A képernyőn a nulla hosszúságú karakterlánc és a NULL érték sokszor nem különböztethető meg, de technikailag van különbség. Ilyen problémába gyakran beleütközünk, ezt fel kell tudni ismerni. | |||||||||||||||
A SELECT utasításból eddig tehát a bemeneti táblák megadását, a szelekciót és projekciót ismerjük, de még sok más részt meg kell tanulnunk. A projekciós részben is van egy új elem, a DISTINCT. A DISTINCT kulcsszóval azt biztosítjuk, hogy az eredményreláció minden sora különböző legyen. Nézzük a SELECT utasítás kibővített alakját (lásd a 29. ábrát is), az új elemek működésével foglalkozunk a továbbiakban: | |||||||||||||||
SELECT [DISTINCT] oszloplista Projekció | |||||||||||||||
| |||||||||||||||
Csoportosítás | |||||||||||||||
Sokszor szükségünk van arra, hogy az eddig tárgyalt projekció és szelekciós feltétel után egy újabb válogatást végezzünk valamely oszlopértékek azonossága alapján. Például, egy iskolai tanuló-nyilvántartásból kilistázzuk a gyerekek nevét, tanulmányi átlagát és évfolyamát (a tanulo tábla megtalálható a dbGyak adabázisban): | |||||||||||||||
SELECT nev, atlag, evfolyam FROM tanulo | |||||||||||||||
Nem az egyes gyerekek tanulmányi átlagára, hanem az évfolyamok átlagára van szükségünk, ezért a fenti lekérdezéssel kapott rekordokat csoportosítjuk az évfolyam azonossága alapján: | |||||||||||||||
SELECT ... FROM tanulo GROUP BY evfolyam | |||||||||||||||
A projekciós részt most azért nem írtuk, mert módosítanunk kell rajta. Az egyes gyerekek nevére eleve nincs szükségünk, a tanulmányi átlagukat pedig évfolyamonként (csoportonként) átlagolnunk kell. Hasznos lehet még látni, hogy az egyes évfolyamokon hány gyerek van. Két függvénnyel kell megismerkednünk: az AVG (average - átlag) és a COUNT (számol) aggregációs függvényekkel, amikre további példákat majd a Aggregációs függvények című szakaszban láthatunk. A lekérdezésünk tehát így alakul: | |||||||||||||||
SELECT evfolyam, COUNT(*) AS Letszam, AVG(atlag) AS Evfolyamatlag | |||||||||||||||
| |||||||||||||||
A szelekcióval kiválasztott sorok csoportosításának szempontját (egy, vesszővel (,) elválasztva több mezőt) a GROUP BY kulcsszó után írjuk. Ha az így előállt listát szeretnénk tovább szűkíteni (csoportszelekció), például csak 3-es átlagot elérő évfolyamokra vagyunk kíváncsiak, azt a HAVING kulcsszó után tehetjük meg. Ez előző lekérdezést folytatva: | |||||||||||||||
... HAVING AVG(atlag) >= 3 | |||||||||||||||
Végrehajtási sorrend | |||||||||||||||
A SELECT parancsok esetében komoly jelentősége van annak, hogy az egyes parancs-részeket milyen sorrendben értelmezzük mi, illetve a parancsértelmező. Ez az ún. végrehajtási sorrend logikus, de nem magától értetődő, hiszen eltér a szokásos balról-jobbra olvasási rendtől. Nézzük tehát az egyes részlépések precedenciáját: | |||||||||||||||
| |||||||||||||||
Természetes, hogy egyes részlépések kimaradhatnak, hiszen nem kötelező mindet egy időben használni. Azt is jegyezzük meg, hogy bár a fenti sorrend igen jó kiindulási alap a megértéshez, de összetett SELECT utasításoknál sokszor nem triviális a részműveletek végrehajtásának pontos sorrendje, mikéntje. Konkrét esetekben, elsősorban hatékonysági elemzésekhez vannak különböző vizsgálati eszközök, ezekkel azonban e tananyagban nem foglalkozunk. | |||||||||||||||
Halmazműveletek | |||||||||||||||
A relációs adatmodell megismerésekor a Descartes szorzat, halmazok című szakaszban említettük már, hogy a modell a tárolt adatokat halmazként definiálja, és halmazként is kezeli. | |||||||||||||||
Van néhány olyan művelet, ahol ez a halmazos megközelítés nem csak a modell működésének megértéséhez fontos, hanem mi magunk akarunk konkrét halmazműveleteket végezni: ilyenek az unió, metszet, különbség képzések (31. ábra). Ezek a halmazműveletek természetesen csak megfelelő (kompatibilis) táblaszerkezet esetén működnek. | |||||||||||||||
| |||||||||||||||
| |||||||||||||||
SELECT * FROM T1 UNION SELECT * FROM T2 | |||||||||||||||
| |||||||||||||||
SELECT Név FROM Ember UNION SELECT nev FROM tanulo | |||||||||||||||
| |||||||||||||||
SELECT * FROM T1 INTERSECT SELECT * FROM T2 | |||||||||||||||
| |||||||||||||||
Fontos látni, hogy a halmazműveleteknél nincsen semmiféle sorrendiség a halmaz elemei között. Az SQL nyelv használata során észrevehetünk azonban két esetet, amikor a halmazelemek sorrendjével is kell törődnünk. Az egyik a projekció, ahol természetesen csak valamely sorrendben tudjuk felsorolni a szükséges mezőket, a másik pedig a lekérdezés eredményének rekordonkénti sorba állítása, erre való az ORDER BY módosító. Ha nem adnánk meg, akkor is kialakulna valamilyen sorrend, természetesen, ahogyan az adatbázis-kezelő sorba veszi a megtalált adatokat. Ez a két eset annyira természetes, hogy nem zavar minket, hogy valójában listát képeztünk a halmazokból. | |||||||||||||||
Rendezés | |||||||||||||||
Ahogy az imént említettük, a lekérdezések eredménye elméletileg egy halmaz, amit a gyakorlatban mindig valamely listába rendezve kapunk meg az adatbázis-kezelőtől. Sok esetben meg szeretnénk határozni, hogy milyen sorrendben kérjük a kinyert adatokat. Az ORDER BY kulcsszó használatával egy vagy akár több oszlopot is felsorolhatunk (természetesen csak a projekcióban szereplő mezők közül): | |||||||||||||||
ORDER BY oszlopnév [DESC], ..., oszlopnév [DESC] | |||||||||||||||
A rendezés alapértelmezésben növekvő, ha fordítva szeretnénk, akkor a DESC (descending - csökkenő) szót kell a mezőnév után írni. Ezt akár mezőnként is külön-külön meghatározhatjuk. A normál, növekvő rendezés kulcsszava az ASC (ascending - növekvő), ezt azonban, felesleges kiírni, mert ez az alapértelmezett. | |||||||||||||||
Az ORDER BY mindig a legutolsó művelet, mivel a relációs modell halmazokkal dolgozik, a rendezésnek korábban semmilyen értelme nem lenne, ezért nem is megengedett. | |||||||||||||||
Függvények | |||||||||||||||
Az SQL parancsokban sok-sok, a programozási nyelvekből, vagy matematikából ismerős függvény használható. Nagyon sok beépített függvény létezik, a pontos függvénylista implementációfüggő, ezért a részletekért mindig az adott adatbázis saját dokumentációját érdemes megnézni. | |||||||||||||||
Saját függvények is definiálhatók (CREATE FUNCTION ...), ez minden implementációban különböző, és ebben a tananyagban nem foglalkozunk vele. | |||||||||||||||
Az attribútumok általában mezőnevek, de a függvények egymásba ágyazhatók, így attribútumok helyén más függvények is lehetnek. | |||||||||||||||
A függvények a paraméterben szereplő értékre, vagy akár a táblára vagy a teljes adatbázisra vonatkoznak, értelemszerűen. A teljesség igénye nélkül, nézzünk néhány példát, ami alapján sejtésünk lehet a használható függvények köréről: | |||||||||||||||
Szöveges adatokon dolgozó függvények: | |||||||||||||||
| |||||||||||||||
Szöveges adatokon dolgozó függvények: | |||||||||||||||
| |||||||||||||||
Matematikai függvények: | |||||||||||||||
| |||||||||||||||
Egyéb függvények: | |||||||||||||||
| |||||||||||||||
Aggregációs függvények: | |||||||||||||||
| |||||||||||||||
Aggregációs függvények | |||||||||||||||
A függvények között külön helyet foglalnak el az ún. aggregációs függvények, amelyek nem egyetlen rekord mezőértékén, hanem az összes kiválasztott rekordon hajtódnak végre. E csoport legjellemzőbb képviselői a következők: | |||||||||||||||
| |||||||||||||||
Saját aggregációs függvények is definiálhatók (CREATE AGGREGATE FUNCTION), ennek implementáció-függő részleteire nem térünk ki. | |||||||||||||||
Lássunk néhány példát. Legrosszabbul teljesítő diák tanulmányi átlaga: | |||||||||||||||
SELECT MIN(atlag) FROM tanulo | |||||||||||||||
A leghosszabb név (ez hasznos lehet akkor, amikor egy pl. egy fix hosszúságú név mezőt tervezünk, vagy képernyőképek tervezésénél): | |||||||||||||||
SELECT MAX(LENGTH(nev)) FROM tanulo | |||||||||||||||
A következőben pedig példát láthatunk arra, hogyan tudunk egy aggregációs függvényérték mellé valamely konkrét mezőértéket tenni. A leghosszabb nevű gyerek: | |||||||||||||||
SELECT nev AS 'Leghosszabb név' FROM tanulo gy1 | |||||||||||||||
| |||||||||||||||
Beágyazott SELECT parancs | |||||||||||||||
A legutóbbi példában egy korábban nem látott trükkel éltünk: egy SELECT parancsba alkalmas helyen beágyaztunk egy másik SELECT parancsot. Ezt az egymásba ágyazást az SQL nyelv megengedi, így nagyon kibővülnek a lehetőségeink. | |||||||||||||||
A SELECT parancs ráadásul nem csak SELECT parancsba, hanem más SQL parancsokba is ágyazható. Erre leginkább olyan esetekben van szükség, amikor a lekérdezés eredményét nem szükséges tárolni, hanem csak tovább akarunk dolgozni a kapott (rész)eredményhalmazzal. | |||||||||||||||
Figyeljük meg, hogy a Halmazműveletek című szakaszban tárgyalt halmazműveletek is valójában két SELECT parancs egybeágyazását jelentik. | |||||||||||||||
A következő tipikus példák kapcsán nézzük meg, a valódi lekérdezéseken kívül mi mindenre lehet használható a beágyazott SELECT parancs: | |||||||||||||||
| |||||||||||||||
CREATE TABLE alsosok AS | |||||||||||||||
| |||||||||||||||
CREATE TABLE dbNEPTUN.alsosok AS (SELECT * FROM dbGyak.tanulo | |||||||||||||||
| |||||||||||||||
| |||||||||||||||
INSERT INTO tanulo SELECT * FROM tanulo2; | |||||||||||||||
| |||||||||||||||
SELECT * FROM tanulo2 INTO tanulo; | |||||||||||||||
| |||||||||||||||
CREATE TABLE dbNEPTUN.tanulo AS SELECT * FROM dbGyak.tanulo | |||||||||||||||
| |||||||||||||||
INSERT INTO tanulo SELECT * FROM dbGyak.tanulo2; | |||||||||||||||
| |||||||||||||||
UPDATE tanulo | |||||||||||||||
| |||||||||||||||
| |||||||||||||||
DELETE FROM tanulo WHERE id IN | |||||||||||||||
Összetett lekérdezések | |||||||||||||||
A korábbiakban, a Létrehozás - CREATE című pontban létrehoztunk és a Beszúrás - INSERT című pontban adatokkal feltöltöttünk két táblát (kikoto és menetrend), amivel a balatoni hajómenetrendet írtuk le. Most megnézünk néhány, ezzel kapcsolatos összetett lekérdezést. A feladathoz tartozó két tábla a dbGyak adatbázisban is megtalálható. Most bármelyiket használhatjuk. | |||||||||||||||
Tanulmányozzuk a két tábla felépítését! A kikoto táblában csupán a hajókikötők nevei vannak, egyedi azonosítóval ellátva. A menetrend tábla pedig tartalmazza, hogy melyik hajó, milyen időpontban, honnan hova közlekedik. | |||||||||||||||
A most tárgyalt lekérdezések a korábbiaknál nagyobb figyelmet igényelnek, hiszen egyre bonyolultabb feladatokat akarunk megoldani velük. Javasoljuk, hogy minden egyes példát próbáljanak ki, lehetőleg begépelve (nem pedig a kész parancs átmásolásával), és akkor jobban megértik a parancsok logikáját. Ez a leírt példák pontos követése mellett sok egyéni kísérletezésre is lehetőséget biztosít. | |||||||||||||||
Kérdezzük le a D1 hajójárat menetrendjét annak minden adatával együtt! | |||||||||||||||
Lépésenként fogjuk a feladatot megoldani, kezdjünk neki: | |||||||||||||||
SELECT * FROM menetrend | |||||||||||||||
| |||||||||||||||
Már most észrevehetjük, hogy jól el kell majd neveznünk az eredményreláció oszlopait, hogy a kapott eredmény értékelhető legyen. Mivel a kikoto táblát a célállomás miatt is használnunk kell, azt is el kell neveznünk a kikötő aktuális feladatának megfelelően. A szükségtelen mezőket pedig hagyjuk el: | |||||||||||||||
SELECT jarat, honnan.név, indul, erkezik | |||||||||||||||
| |||||||||||||||
Így már könnyű a lekérdezéshez fűzni a célállomás nevét: | |||||||||||||||
SELECT jarat, honnan.név, cel.név, indul, erkezik | |||||||||||||||
| |||||||||||||||
Efféle hosszú parancsok gépelésekor könnyű hibázni. Próbáljuk megmondani, mi történne, ha az indul szó előtti vesszőt elfelejtettük volna begépelni: | |||||||||||||||
SELECT jarat, honnan.név, cel.név indul, erkezik | |||||||||||||||
Ha gondolatban megválaszoltuk, hogy mi történik, ha elvesszük a kérdéses vesszőt, próbaképpen futtassuk is le a hibás lekérdezést, hogy ellenőrizhessük az eredményt: | |||||||||||||||
| |||||||||||||||
Visszatérve az eredeti feladatunkhoz, már csak az maradt hátra, hogy megfelelően elnevezzük az eredményreláció oszlopait: | |||||||||||||||
SELECT jarat AS Járat, honnan.név Honnan, cel.név Hova, indul AS Indul, | |||||||||||||||
| |||||||||||||||
Most listázzuk ki, a Balatonfüredet érintő hajók menetrendjét két részben (külön az induló és külön az érkező hajókat). Kezdjük a Balatonfüredről induló hajók menetrendjével: | |||||||||||||||
SELECT cel.név AS Cél, jarat AS Járat, indul AS Indul, erkezik AS Érkezik | |||||||||||||||
| |||||||||||||||
Az előző lekérdezést alakítsuk át úgy, hogy a Balatonfüredre érkező hajók menetrendjét mutassa, érkezési időpontjuk szerint: | |||||||||||||||
SELECT honnan.név AS Honnan, jarat AS Járat, indul AS Indul, erkezik AS Érkezik | |||||||||||||||
| |||||||||||||||
Válaszoljuk meg, mi a járatszáma az aznapi 5. érkező hajónak (a fenti példa esetében J1). | |||||||||||||||
Tegyük fel, hogy délben érkezünk az állomásra, és a legelső induló járattal szeretnénk továbbállni. Keressük ki, hogy melyik kikötőbe indul majd ez a hajó: (A megoldásnál természetesen felhasználhatjuk Balatonfüred korábban elkészített induló menetrendjét) | |||||||||||||||
SELECT cel.név AS Cél, jarat AS Járat, indul AS Indul, erkezik AS Érkezik | |||||||||||||||
| |||||||||||||||
Határozzuk meg, hogy legkésőbb hány órakor lehet hajóval visszaérkezni Alsóörsre! | |||||||||||||||
A következő lekérdezéssel megkereshetjük a választ: | |||||||||||||||
SELECT honnan.név AS Honnan, jarat AS Járat, erkezik AS Érkezik | |||||||||||||||
| |||||||||||||||
A pontos válaszhoz természetesen a megfelelő mezőt tegyük a projekcióba: | |||||||||||||||
SELECT erkezik AS Érkezik | |||||||||||||||
| |||||||||||||||
Képezzük a G2 járat menetrendjét: | |||||||||||||||
SELECT honnan.név AS Honnan, cel.név AS Hova, indul AS Indul, erkezik AS Érkezik | |||||||||||||||
| |||||||||||||||
Mi a G2 járat végállomása? Kétféleképpen is megoldjuk: Talán legegyszerűbb megoldásként kiindulhatunk az előző lekérdezésből: | |||||||||||||||
SELECT cel.név AS Hova | |||||||||||||||
| |||||||||||||||
Gondolkodhatunk azonban úgy is, hogy a G2 járathoz tartozó azon szakaszokat keressük, ahol a célállomás nem szerepel a G2 járat szakaszainak kiinduló állomásán: | |||||||||||||||
SELECT cel.név AS Hova | |||||||||||||||
| |||||||||||||||
A menetrend elemzése alapján állapítsuk meg, hány hajóra van szükség a menetrend megvalósításához! Megfigyelhetjük, hogy a járatkódok kezdőbetűje jelenthet egy-egy konkrét hajót (valószínűleg valóban ez alapján kerültek meghatározásra a járatkódok). Ezen feltételezés alapján számoljuk meg, hány hajója üzemel a jelen feladatban vizsgált hajótársaságnak! | |||||||||||||||
SELECT COUNT(DISTINCT SUBSTR(jarat,1,1)) FROM menetrend | |||||||||||||||
| |||||||||||||||
Elég csúnyán néz ki. az eredménytábla. Módosítsuk a fejlécét! | |||||||||||||||
A megoldásban a járatkódok első betűi közül a különbözőeket számoltuk össze. | |||||||||||||||
Tárolni szeretnénk az egyes hajók nevét. Tervezzük meg, hova érdemes tenni. | |||||||||||||||
Fogalmazzuk meg, miért jó, vagy miért nem jó megoldás a hajóneveket a menetrend vagy a kikoto táblában tárolni, esetleg miért jó vagy nem jó megoldás egy új tábla létrehozása e célból. | |||||||||||||||
Egy lehetséges jó megoldás: | |||||||||||||||
CREATE TABLE hajo (id CHAR(1), név VARCHAR(30)); | |||||||||||||||
A fentiekben meghatároztuk, hány hajóra van szükségünk. Ha a hajo táblát a fentiekhez hasonló módon hoztuk létre, akkor a szükséges kódokat érdemes egy lekérdezéssel beletölteni a táblába: | |||||||||||||||
INSERT INTO hajo (SELECT DISTINCT SUBSTR(jarat,1,1), NULL | |||||||||||||||
Ezzel a szükséges kódokhoz tartozó rekordok létrejöttek, de az egyes hajók neveinek betöltését kénytelenek vagyunk kézzel elvégezni, például: | |||||||||||||||
UPDATE hajo SET név="Tornádó" WHERE id="A" | |||||||||||||||
Készítsünk olyan lekérdezést, ami a teljes menetrendet az egyes hajók szerint megmutatja. A lekérdezés elkészítéséhez a következő lekérdezés adja az alapot: | |||||||||||||||
SELECT * FROM menetrend | |||||||||||||||
| |||||||||||||||
Bővítsük ki a lekérdezést az egyes állomások neveivel a korábbiak alapján. Végeredményként valami hasonló lekérdezést kapunk: | |||||||||||||||
SELECT DISTINCT hajo.név Hajó, honnan.név Honnan, hova.név Hova, | |||||||||||||||
| |||||||||||||||
Szűkítsük a menetrendet a Tornádó nevű hajó mozgására: | |||||||||||||||
SELECT hajo.név Hajó, honnan.név Honnan, hova.név Hova, indul Indul, | |||||||||||||||
| |||||||||||||||
Készítsünk a Tornádó hajó menetrendjéről egy tornado nevű nézetet! | |||||||||||||||
CREATE VIEW tornado AS SELECT honnan.név Honnan, hova.név Hova, indul Indul, | |||||||||||||||
| |||||||||||||||
Készítsünk listát az egyes kikötőkről, hogy melyeknek a legnagyobb a hajóforgalma! A forgalom szerint rendezzük sorba a kikötőket. A megoldásnál felhasználhatjuk azt a tényt, hogy ugyanannyi hajó érkezik be a kikötőbe, mint ahány elindul onnan, tehát elegendő például csak az indulásokat figyelni: | |||||||||||||||
SELECT honnan.név Honnan, COUNT(*) "Induló járat" FROM menetrend | |||||||||||||||
| |||||||||||||||
Hány járat indul naponta Fonyódról? | |||||||||||||||
SELECT honnan.név Honnan, COUNT(*) "Induló járat" | |||||||||||||||
| |||||||||||||||
Listázzuk ki azon kikötőket, ahonnan naponta több, mint 10 járat indul: | |||||||||||||||
SELECT honnan.név Honnan, COUNT(*) "Induló járat" FROM menetrend | |||||||||||||||
| |||||||||||||||
A hajózásról van, akinek a borozás jut az eszébe. Tegyük fel, minden barátunkról tárolni akarjuk, hogy milyen italt szeret. Amikor vendégséget szervezünk, hasznát vehetjük ezeknek az információknak. | |||||||||||||||
Készítsük el a tároláshoz szükséges legegyszerűbb struktúrájú táblát név és ital mezőkkel: | |||||||||||||||
CREATE TABLE kimitszeret (nev VARCHAR(30), ital VARCHAR(30)) | |||||||||||||||
Töltsük is fel a táblát a barátaink adataival. Ne feledjük, jól tervezett táblában a mezőkbe elemi adatoknak kell kerülniük, tehát aki több italt szeret, azokat többször vigyük fel külön rekordként. (A redundanciával ebben az egyszerű példában most ne törődjünk.) Például: | |||||||||||||||
| |||||||||||||||
Jókedvű társaságban központi kérdés, ki szereti és ki nem szereti a bort, ezért e kérdést alaposan körbejárjuk. Listázzuk ki azon embereket, akik nem szeretik a bort! A fenti, nagyon egyszerű táblából nem is olyan egyszerű kiolvasni a választ, hiszen az egyes rekordokat tekintve, azaz egyszerű szelekcióval nem tudunk válaszolni a kérdésre, a rekordokat valahogy egyszerre kell vizsgálnunk. A kimitszeret táblából beágyazott SELECT segítségével megkaphatjuk a bort nem ivók nevét: | |||||||||||||||
SELECT DISTINCT nev | |||||||||||||||
| |||||||||||||||
Egy másik halmazelméleti megoldást is meg tudunk fogalmazni: | |||||||||||||||
SELECT DISTINCT nev | |||||||||||||||
| |||||||||||||||
Soroljuk fel egymás mellé, ki mit szeret: | |||||||||||||||
SELECT nev, GROUP_CONCAT(ital SEPARATOR ',') AS 'Ezeket szereti' | |||||||||||||||
A GROUP_CONCAT (group concatenate, magyarul csoportösszefűzés) függvény segítségével a GROUP BY által csoportosított adatokon végzünk egy szövegösszefűzést: az egyes csoportokba tartozó rekordok ital mezőit írjuk egymás után, vesszővel elválasztva. | |||||||||||||||
| |||||||||||||||
Hagyjuk el azon embereket, akik a bort vagy a bort is szeretik: | |||||||||||||||
SELECT nev, GROUP_CONCAT(ital SEPARATOR ',') AS 'Ezeket szereti' | |||||||||||||||
| |||||||||||||||
Talán furcsa, hogy a GROUP_CONCAT függvény két helyen is szerepel a parancsban. Ez azért van, mert a SELECT utáni projekciós részbe bevéve vissza akartuk kapni a függvény tényleges értékeit, a HAVING után pedig a csoportképzés utáni feltételvizsgálathoz ugyanazt használtuk. | |||||||||||||||
Kicsit egyszerűsítve, ha úgysem kell visszaadni az italokat, akkor csak a csoportosítás miatt keressük a bort: | |||||||||||||||
SELECT nev | |||||||||||||||
| |||||||||||||||
A következő lekérdezés eredménye is ugyanaz: | |||||||||||||||
SELECT DISTINCT i1.nev | |||||||||||||||
| |||||||||||||||
A JOIN utasítás kapcsán már említettük, hogy csak a legegyszerűbb eseteivel foglalkozunk. Itt a LEFT JOIN változatra azért volt szükség, hogy két tábla közül az elsőnek (a bal oldalon álló i1-nek, innen az elnevezés) minden rekordja megjelenjen az eredményben akkor is, ha nem illeszkedik hozzájuk érték a második (jobb oldali, i2) táblában. | |||||||||||||||
Végezetül tekintsük a következő megoldást, az SQL nyelven ez a parancs is ugyanezt kell, hogy eredményezze: | |||||||||||||||
SELECT nev FROM kimitszeret GROUP BY nev | |||||||||||||||
Ezt a formát sajnos több adatbázis-kezelő nem képes értelmezni, pedig maga a parancs helyes. Az ilyen részletmegoldásoknál ütközik ki az egyes adatbázis-kezelők képességei közötti különbözőség. Ha ilyen problémába ütközünk, akkor nem tehetünk mást, mint az adott feladatot kicsit másképp oldjuk meg - láthattuk, egészen egyszerű problémák esetén is hány különböző megoldási lehetőségünk van. Ebben a feladatban éppen az SQL sokszínűségét, variálhatóságát igyekeztünk demonstrálni, hogy hány különböző módon eljuthatunk ugyanazon megoldásig. |
Leckezáró kérdések, feladatok | |||||||||
1. Írja ide azt a kulcsszót, amit lekérdezésekben annak biztosítására használunk, hogy az eredményreláció minden sora különböző legyen: ![]() | |||||||||
2. Mit eredményez a következő parancsrészlet egy SQL parancsban? ORDER BY Hajó, indul
![]() | |||||||||
3. Írja ide azt a legegyszerűbb parancsot, amivel meg tudja határozni, hány rekord van az eset nevű táblában! A parancsot csupa nagybetűvel gépelje, a tábla nevét a feladatban megadott módon! ![]() | |||||||||
4. Döntse el, hogy az állítás igaz vagy hamis!
![]() | |||||||||
5. Sorolja fel az összes olyan kulcsszót, ami minden adatlekérdező SQL utasításban kötelezően szerepel ![]() | |||||||||
6. Melyik állítás igaz a projekcióra?
![]() | |||||||||
7. A szükséges táblák és mezők rendelkezésre állása esetén helyes-e az alábbi parancs? SELECT név,lakcim FROM Ember1 UNION SELECT név FROM Ember2
![]() | |||||||||
Mit eredményez a következő parancs? SELECT 1,2,a FROM szotar WHERE a='három';
![]() | |||||||||
9. Hány B betűvel kezdődő kikötő van a dbGyak adatbázis kikoto táblájában? ![]() | |||||||||
10. Naponta hány alkalommal indul útra utasokkal az A3 jelű hajójárat? (A dbGyak adatbázis menetrend táblája alapján) ![]() |