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

  • fel tudja sorolni a SELECT parancs legfontosabb részeit, ezek működését
  • képes az adatok kinyeréséhez szükséges feltételek megfogalmazására a SELECT parancs szintaktikai szabályainak megfelelően

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

  • lekérdezés
  • szelekció
  • projekció
  • halmazműveletek
  • végrehajtási sorrend
  • SELECT parancs
  • beágyazott SELECT
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 SELECT művelet két legfontosabb eleme
1. ábra

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
WHERE Autó.Tulajdonos=Ember.Azonosító;

A lekérdezés eredménye a 2. ábrán látható:

Egy-több kapcsolat
2. ábra

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ó
JOIN Ember ON Autó.Tulajdonos=Ember.Azonosító;

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
JOIN Ember e ON a.Tulajdonos=e.Azonosító;

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;
SELECT SIN(PI()/3), (4+1)*5;
SELECT @a:=10*PI(), @a/10;

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

  • Összehasonlító operátorok: =  <  >  <=  >=  <>  !=
  • Halmazoperátor: IN ('a','b','c')
    A fordok és opelek összes adata:
SELECT * FROM Autó WHERE Márka IN ('Ford','Opel')
Az IN utáni zárójelbe akár egy másik SELECT utasítás is tehető, aminek természetesen egyetlen mezőt szabad tartalmaznia a projekcióban (SELECT nev FROM ...). Ezekben az esetekben a hatékonysággal probléma lehet, úgyhogy csak megfelelő körültekintéssel ágyazzunk egymásba parancsokat. Lásd még: Beágyazott SELECT parancs című alfejezet.
  • Tartományba esés ellenőrzése: BETWEEN a AND b
  • Mintaillesztés: LIKE (az angol szó egyik jelentése: "olyan, mint")
    A használatához a LIKE kulcsszó után idézőjelben egy mintát kell megadnunk, ami a tényleges keresett szöveg mellett a következőket tartalmazhatja:
    _  egyetlen, bármilyen karakter
    %  akárhány karakter
    Például:
    • LIKE 'a_' a betűvel kezdődő kétbetűs
    • LIKE 'a%' a betűvel kezdődő
    • LIKE '%a%' a betűt tartalmazó
    • NOT LIKE ... a mintával nem egyező
    • CLIKE ... a mintaillesztésnél nem különbözteti meg a kis- és nagybetűket (A LIKE megkülönbözteti!)
  • Kitöltetlenség ellenőrzése: IS NULL
    Nézzük meg, mely emberek esetén hiányos a nyilvántartásunk:
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ó
FROM táblanévlista [alias]    Descartes-szorzat
[WHERE] feltételek            Szelekció
[GROUP BY oszloplista]        Csoportosítás
[HAVING feltételek]          Csoport szelekció
[ORDER BY oszloplista]        Rendezés
A kibővített SELECT művelet
3. ábra
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
FROM tanulo GROUP BY evfolyam;
Csoportosított adatok a lekérdezésben
4. ábra

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:

1.FROM ...
Descartes-szorzat, itt állítjuk elő a "bemeneti" táblákból a Descartes-szorzatnak megfelelő kimeneti tábla kezdeti formáját
2.JOIN ...
Táblák összekapcsolása
3.WHERE ...
Szelekció, tehát a reláció sorai közül válogatunk.
4.GROUP BY ...
Csoportosítás, amivel a reláció egyes sorait összevonhatjuk
5.HAVING ...
Csoport szelekció, tehát a csoportosítás végrehajtása után újra válogatunk a reláció sorai között.
6.SELECT ...
Projekció, tehát a reláció oszlopai közül válogatunk.
7.ORDER BY ...
Rendezés, itt már csak a rekordok átadásának sorrendjét állítjuk be

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.

Halmazműveletek
5. ábra
  • Unió
SELECT * FROM T1 UNION SELECT * FROM T2
A korábbi példáinkban az Ember és tanulo táblákban szereplő összes személy neve:
SELECT Név FROM Ember UNION SELECT nev FROM tanulo
  • Metszet
SELECT * FROM T1 INTERSECT SELECT * FROM T2
  • Különbség

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:

  • LENGTH() hosszúság
  • ROUND() kerekítés
  • LOWER() kisbetűsre konvertál
  • UPPER() nagybetűsre konvertál
  • REPLACE() karakterlánc-csere
  • TO_CHAR() számot vagy dátumot karakterlánccá konvertál

Szöveges adatokon dolgozó függvények:

  • HOUR() óraérték 0-23
  • WEEK() a hét száma 1-54

Matematikai függvények:

  • SIN() szinusz
  • PI() pi

Egyéb függvények:

  • ISNULL() null-érték
  • ROWNUM() sorok száma
  • DATABASE_VERSION() adatbázis-verziószám lekérdezése
  • CURRENT_USER() az aktuális felhasználó azonosítója

Aggregációs függvények:

  • Ezek a függvények több figyelmet igényelnek, ezért külön fejezetben foglalkozunk velük.
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:

  • MIN() legkisebb érték
  • MAX() legnagyobb érték
  • SUM() összeg
  • COUNT() elem darabszám
  • AVG() átlag

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
WHERE LENGTH(gy1.nev) = (SELECT MAX(LENGTH(gy2.nev))
FROM tanulo gy2);
A leghosszabb nevű tanuló
6. ábra
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:

  • Táblakészítés
CREATE TABLE alsosok AS
(SELECT * FROM tanulo WHERE evfolyam<=4);
Bármilyen táblastruktúra létrehozható, ilyenkor természetesen a beágyazott SELECT parancs projekciós részének megfelelő alakúnak kell lennie.
Mivel a tanulo tábla a dbGyak adatbázisban van, de mi csak a sajátunkban hozhatunk létre táblát, a fenti parancsot bárhol is adjuk ki, hibát fog jelezni (a dbGyak táblában nincs jogunk táblát létrehozni, a sajátunkban pedig nem találja a tanulo táblát.)
Ha egyszerre több adatbázis tábláit akarjuk elérni, a kívánt táblanév elé ponttal elválasztva be kell írni a szükséges adatbázis nevét. A fenti parancsot például így lehet átalakítani:
CREATE TABLE dbNEPTUN.alsosok AS (SELECT * FROM dbGyak.tanulo
WHERE evfolyam<=4);
Ne feledjük, hogy a parancsban a saját adatbázisunk nevét adjuk meg.
  • Hozzáfűzés
INSERT INTO tanulo SELECT * FROM tanulo2;
Ezt a feladatot egyébként az INTO nyelvi elemmel beágyazás nélkül is meg tudjuk oldani:
SELECT * FROM tanulo2 INTO tanulo;
Természetesen ezek a parancsok azonos táblastruktúrát feltételeznek.
A tanulo tábla módosítására nincsen jogunk, ezért a fenti parancsok pontosan ebben a formában biztosan nem működhetnek. Megoldásként a tanulo táblát másoljuk le magunknak:
CREATE TABLE dbNEPTUN.tanulo AS SELECT * FROM dbGyak.tanulo
Ezután a fenti parancsok valamelyikét kis átalakítással (a megfelelő adatbázisokra történő hivatkozással) használhatjuk a saját tanulo táblánk bővítésére, például:
INSERT INTO tanulo SELECT * FROM dbGyak.tanulo2;
  • Módosítás
    Akit a tanulo2 táblából vettünk át, annak lenullázzuk az átlagát:
UPDATE tanulo
SET atlag=0
WHERE EXISTS
(SELECT * FROM dbGyak.tanulo2 WHERE tanulo.id = tanulo2.id);
Az EXISTS kulcsszóval itt halmazelméleti megközelítésben azt fogalmaztuk meg a WHERE feltételben, hogy olyan rekordokat keresünk, ahol a beágyazott SELECT parancs eredménye bármit is tartalmaz (tehát nem üres halmaz).
  • Törlés
    Töröljük ki azon tanulókat, akik a tanulo2 táblában is szerepelnek:
DELETE FROM tanulo WHERE id IN
SELECT id FROM dbGyak.tanulo2);
Ö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
JOIN kikoto ON kikoto.id=menetrend.honnan
WHERE jarat="D1";
A D1 hajójárat menetrendje
7. ábra

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
FROM menetrend
JOIN kikoto AS honnan ON honnan.id=menetrend.honnan
WHERE jarat="D1";
A lekérdezés eredménye
8. ábra

Í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
FROM menetrend
JOIN kikoto AS honnan ON honnan.id=menetrend.honnan
JOIN kikoto AS cel ON cel.id=menetrend.hova WHERE jarat="D1";
A lekérdezés eredménye
9. ábra

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
FROM menetrend
JOIN kikoto AS honnan ON honnan.id=menetrend.honnan
JOIN kikoto AS cel ON cel.id=menetrend.hova
WHERE jarat="D1";

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:

A lekérdezés eredménye
10. ábra

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,
erkezik AS Érkezik
FROM menetrend
JOIN kikoto AS honnan ON honnan.id=menetrend.honnan
JOIN kikoto AS cel ON cel.id=menetrend.hova
WHERE jarat="D1";
A lekérdezés eredménye
11. ábra

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
FROM menetrend
JOIN kikoto AS honnan ON honnan.id=menetrend.honnan 
JOIN kikoto AS cel ON cel.id=menetrend.hova
WHERE honnan.név="Balatonfüred" ORDER BY indul;
A lekérdezés eredménye
12. ábra

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
FROM menetrend
JOIN kikoto AS honnan ON honnan.id=menetrend.honnan
JOIN kikoto AS cel ON cel.id=menetrend.hova
WHERE cel.név="Balatonfüred" ORDER BY indul;

A lekérdezés eredménye
13. ábra

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
FROM menetrend
JOIN kikoto AS honnan ON honnan.id=menetrend.honnan
JOIN kikoto AS cel ON cel.id=menetrend.hova
WHERE  honnan.név="Balatonfüred"
AND indul>="12:00"
ORDER BY indul LIMIT 1;
A lekérdezés eredménye
14. ábra

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
FROM menetrend
JOIN kikoto AS honnan ON honnan.id=menetrend.honnan
JOIN kikoto AS cel ON cel.id=menetrend.hova
WHERE cel.név="Alsóörs" ORDER BY erkezik DESC LIMIT 1;
A lekérdezés eredménye
15. ábra

A pontos válaszhoz természetesen a megfelelő mezőt tegyük a projekcióba:

SELECT erkezik AS Érkezik
FROM menetrend
JOIN kikoto AS honnan ON honnan.id=menetrend.honnan
JOIN kikoto AS cel ON cel.id=menetrend.hova
WHERE cel.név="Alsóörs" ORDER BY erkezik DESC LIMIT 1;
A lekérdezés eredménye
16. ábra

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
FROM menetrend
JOIN kikoto AS honnan ON honnan.id=menetrend.honnan
JOIN kikoto AS cel ON cel.id=menetrend.hova
WHERE jarat="G2";
A lekérdezés eredménye
17. ábra

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
FROM menetrend
JOIN kikoto AS honnan ON honnan.id=menetrend.honnan
JOIN kikoto AS cel ON cel.id=menetrend.hova
WHERE jarat="G2" ORDER BY erkezik DESC LIMIT 1;
A lekérdezés eredménye
18. ábra

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
FROM menetrend
JOIN kikoto AS cel ON cel.id=menetrend.hova
WHERE  jarat="G2"
AND hova NOT IN
(SELECT honnan FROM menetrend m
WHERE m.honnan = menetrend.hova AND m.jarat="G2");
A lekérdezés eredménye
19. ábra

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
A lekérdezés eredménye
20. ábra

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
FROM menetrend);

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
JOIN hajo ON hajo.id=SUBSTR(menetrend.jarat,1,1);
A lekérdezés eredménye
21. ábra

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,
indul Indul, erkezik AS Érkezik
FROM menetrend
JOIN hajo ON hajo.id=SUBSTR(menetrend.jarat,1,1)
JOIN kikoto honnan ON honnan.id=menetrend.honnan
JOIN kikoto hova ON hova.id=menetrend.hova
ORDER BY Hajó, indul;
A lekérdezés eredménye
22. ábra

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,
erkezik AS Érkezik
FROM menetrend
JOIN hajo ON hajo.id=SUBSTR(menetrend.jarat,1,1)
JOIN kikoto honnan ON honnan.id=menetrend.honnan
JOIN kikoto hova ON hova.id=menetrend.hova
WHERE hajo.név = "Tornádó"
ORDER BY Hajó, indul;
A lekérdezés eredménye
23. ábra

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,
erkezik AS Érkezik FROM menetrend
JOIN hajo ON hajo.id=SUBSTR(menetrend.jarat,1,1)
JOIN kikoto honnan ON honnan.id=menetrend.honnan
JOIN kikoto hova ON hova.id=menetrend.hova
WHERE hajo.név = "Tornádó"
ORDER BY indul;
A lekérdezés eredménye
24. ábra

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
JOIN kikoto honnan ON honnan.id=menetrend.honnan
GROUP BY honnan ORDER BY COUNT(*) DESC;
A lekérdezés eredménye
25. ábra

Hány járat indul naponta Fonyódról?

SELECT honnan.név Honnan, COUNT(*) "Induló járat"
FROM menetrend
JOIN kikoto honnan ON honnan.id=menetrend.honnan
AND honnan.név="Fonyód"
GROUP BY honnan
ORDER BY COUNT(*) DESC;
A lekérdezés eredménye
26. ábra

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
JOIN kikoto honnan ON honnan.id=menetrend.honnan
GROUP BY honnan
HAVING COUNT(*)>10
ORDER BY COUNT(*) DESC;
A lekérdezés eredménye
27. ábra

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:

A lekérdezés eredménye
28. ábra

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
FROM kimitszeret WHERE NOT EXISTS
(SELECT * FROM kimitszeret k
WHERE kimitszeret.nev = k.nev AND k.ital = "bor");
A lekérdezés eredménye
29. ábra

Egy másik halmazelméleti megoldást is meg tudunk fogalmazni:

SELECT DISTINCT nev
FROM kimitszeret
WHERE nev NOT IN
(SELECT nev FROM kimitszeret WHERE ital= "bor");
A lekérdezés eredménye
30. ábra

Soroljuk fel egymás mellé, ki mit szeret:

SELECT nev, GROUP_CONCAT(ital SEPARATOR ',') AS 'Ezeket szereti'
FROM kimitszeret GROUP BY nev;

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.

A lekérdezés eredménye
31. ábra

Hagyjuk el azon embereket, akik a bort vagy a bort is szeretik:

SELECT nev, GROUP_CONCAT(ital SEPARATOR ',') AS 'Ezeket szereti'
FROM kimitszeret GROUP BY nev
HAVING GROUP_CONCAT(ital SEPARATOR ',') NOT LIKE '%bor%';
A lekérdezés eredménye
32. ábra

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
FROM kimitszeret
GROUP BY nev
HAVING GROUP_CONCAT(ital SEPARATOR ',') NOT LIKE '%bor%';
A lekérdezés eredménye
33. ábra

A következő lekérdezés eredménye is ugyanaz:

SELECT DISTINCT i1.nev
FROM kimitszeret i1
LEFT JOIN kimitszeret i2 ON (i1.nev = i2.nev AND i2.ital="bor")
WHERE i2.nev IS NULL;
A lekérdezés eredménye
34. ábra

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
HAVING COUNT(ital= "bor") = 0;

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
Az adatokat a Hajó tábla indul mezőjének rendezésével adja vissza.
Rendezi a Hajó tábla adatait, utána pedig indulás szerinti sorrendben adja vissza az adatokat
Az eredményrelációt az abban szereplő Hajó, ezután pedig az indul mezők értékei szerint növekvő sorrendben adja vissza.
Ebből a parancsrészletből ezt nem lehet egyértelműen megállapítani.
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!
Ugyanazt a lekérdezési feladatot esetenként többféle SELECT paranccsal is megvalósíthatjuk, de ezek között a végrehajtás szempontjából semmilyen különbség nincsen.
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?
A lekérdezés végrehajtásánál a visszaadandó mezők számát szabályozza.
Megtörténhet, hogy a projekciós rész nem szűkíti a visszaadandó mezők számát.
A lekérdezés végrehajtásánál a visszaadandó rekordok számát szabályozza.
A projekció a műveletek közül elsőként hajtódik végre, emiatt a projekcióban nem szereplő mezők nem is vehetnek részt az SQL parancs valamely más részében.
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
igen
nem
Mit eredményez a következő parancs?
SELECT 1,2,a FROM szotar WHERE a='három';
Egy vagy két olyan rekordot ad vissza, amelynek mezői rendre: 1,2,három
Egy vagy több olyan rekordot ad vissza, amelynek mezői rendre: 1,2,három
Nulla, egy vagy több olyan rekordot ad vissza, amelynek mezői rendre: 1,2,három
A parancs hibás, mert az 1,2 szám és az a betű egybe van írva.
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)