Adatbázis-kezelés MS SQL nyelven és MS Access programmal

(9. osztályos informatika tananyag)

Relációs adatbázis

A számítástechnikában gyakran találkozunk relációs adatbázisokkal. Egy relációs adatbázis adattáblákból és a köztük lévő kapcsolatokból áll. Egy tábla rekordok sorozata. A rekordok mezőkből állnak. Egy táblán belül minden rekord szerkezete megegyezik, vagyis ugyanazokat típusú mezőket tartalmazza minden rekord. Elképzelhetjük úgy, hogy a tábla minden rekordja egy egyed tulajdonságait tartalmazza, ahol egy mezőnek egy tulajdonság felel meg.
Ha a tábla autók adatait tartalmazza, a mezők lehetnek pl.: rendszám, alvázszám, gyártó, típus, szín, gyártás éve. Ha a tábla filmekről szól, a mezők lehetnek: cím, rendező, bemutatás időpontja, hossz (percben), eredeti nyelv.
Ha a tábla eladások adatait tartalmazza, a mezők lehetnek: időpont, termék, eladott mennyiség.
Filmek
 cím rendező
 bemutat hossz
nyelv
Star Wars
George Lucas 1977.05.25
121
angol
American Graffiti
George Lucas
1973.08.02
112 angol
E. T. Steven Spielberg
1982.06.11 115 angol
Jurassic Park Steven Spielberg 1993.06.11 121
angol
Saul fia Nemes Jeles László 2015.06.11
107 magyar
Kincs ami nincs Sergio Corbucci
1981.12.16
102
olasz
Sörgyári capriccio
Jiří Menzel 1981.06.11
94
cseh
Star Wars 8.
Rian Johnson
NULL
NULL
angol
Ugyanezen adatbázis tartalmazhat egy másik táblát, a rendezők Oscar-díjairól is.
Oscar
rendező
 díjak
George Lucas 0
Steven Spielberg
2
Nemes Jeles László 0
Sergio Corbucci
0
Jiří Menzel 0
Ang Lee
2

A két tábla között a rendező mező teremt kapcsolatot. Ebben a példában az Oscar és a Filmek tábla között egy-több kapcsolat van, mert az Oscar tábla egy rekordjához a Filmek tábla több rekordja is tartozhat (de akár egy sem).

Az adatbázis-kezelők feladata és működése

Az adatbázis-kezelő programok létrehozzák és feltöltik adatokkal a táblákat, módosítják az adatokat (új filmeket vihetünk fel, törölhetünk rekordokat, javíthatjuk a mezők értékét), keresnek az adatok között, kilistázzák azokat, és kérdésekre válaszolnak az adatokkal kapcsolatban (pl. melyik a leghosszabb Oscar-díjas film).
A táblázatkezelő programokhoz képest a legnagyobb különbség, hogy táblázatkezelőben jellemzően egyszerre kényelmesen kijelölhető mennyiségű adattal dolgozunk, míg egy adatbázis méretének csak a tárolóeszköz szab határt - nem ritkák a több millió rekordot tartalmazó táblák. Az adatbázis-kezelők összetett kérdésekre is gyorsan válaszolnak, a táblák gyors kezeléséhez rendezést alkalmaznak. Az egymással kapcsolatban lévő táblák száma is nagy lehet.
Az adatbázis-motor feladata, hogy az adattáblákat a háttértárolón valamilyen módon tárolja, és alapszintű műveleteket végezzen azokkal (pl. adott tábla valahányadik rekordjának kiolvasása). Az adatbázis-kezelőkhöz felhasználói felület is tartozik, melyen keresztül kommunikálhatunk az adatbázis-motorral.

Az SQL nyelv

Az egységes kezelhetőség miatt napjaink relációsadatbázis-kezelői rendelkeznek SQL értelmezővel. Az SQL (Structured Query Language, query=lekérdezés) egy felület az adatbázis-motorhoz. Előnye, hogy az adatbázis fizikai szerkezetétől (pontosan hogyan tárolja a motor a háttértárolón az adatokat) függetlenül ugyanúgy kezelhetjük az egyes adatbázisokat. Bár a nyelv nagyjából egységes, az egyes adatbázis-kezelőknél különbségek tapasztalhatók (nyelvjárások), ebben a jegyzetben az MS SQL nyelvjárással foglalkozunk.
Például a következő összetett SQL parancs:
SELECT Filmek.rendező FROM Filmek
       INNER JOIN Oscar ON Filmek.rendező=Oscar.rendező
       WHERE díjak=0
       GROUP BY Filmek.rendező
       HAVING COUNT(cím)>1
       ORDER BY Filmek.rendező;
ábécérendben kilistázza az Oscar-díjat nem kapott rendezők közül azokat, akik egynél több filmet rendeztek.

Egyszerű, egytáblás választó lekérdezések

Az előző példában látható egy választó lekérdezés. Ez SELECT paranccsal kezdődik. Feladata, hogy végigmenjen egy tábla rekordjain, és kilistázza a feltételnek megfelelő rekordok adott mezőit. A parancs formátuma:
SELECT mezőlista FROM tábla [ WHERE feltétel ] ;
A mezőlista mezők vesszővel elválasztott listája. A feltétel bármi lehet, ami az egyes rekordok mezőiből kiszámítható. A mezőlista helyén a * a tábla összes mezőjét jelenti. Ha nem adunk meg feltételt (az elhagyható részeket szögletes zárójel jelöli), a tábla összes rekordját listázza. Nagy táblák esetén ezt ne tegyük.
Adott tábla adott mezőjére tábla.mező módon hivatkozhatunk, de ha egyértelmű (pl. egytáblás lekérdezések esetén), a tábla nevét elhagyhatjuk.
A feltételekben a következő műveletek szerepelhetnek:
  • AND, OR, NOT (és, vagy, tagadás) logikai műveletek, akár zárójelekkel. A ! is tagadást jelent.
  • Relációs jelek: <, >, =, <=, >=, <> vagy != a nem egyenlő jelölésére.
  • Matematikai műveletek.
  • LIKE keresőszöveg szövegrészletre való kereséshez. a keresőszövegben a * karakter tetszőleges szövegrészletet jelölhet. Pl. "George*" a George-dzsal kezdődő nevű szövegeket jelenti. A ? egyetlen karaktert helyettesíthet.
  • BETWEEN érték AND érték intervallum jelölésére.
  • Szám, szöveg, dátum, logikai konstansok. A szöveget idézőjelek közé kell tenni ("majom"), a dátumot # jelek közé (pl. #2015.05.30#). Logikai konstansok lehetnek a true és a false.
  • Függvények, melyek adott értékekből más értéket számítanak ki. Ilyen pl. a YEAR, MONTH, DATE függvény, mely a megadott dátum évét, hónapját, napját számítja ki, pl. MONTH(#2016.09.07#) értéke 9.
    Szövegfüggvények: LEFT(szöveg,szám): a szöveg első valahány karaktere, RIGHT ugyanez utolsó karakterekre.
Példák lekérdezésekre:
SELECT * FROM Filmek;
SELECT cím FROM Filmek WHERE hossz>120;
SELECT cím FROM Filmek WHERE hossz<90 OR (nyelv=magyar AND bemutat>=#1980.01.01#);
SELECT cím,rendező FROM Filmek
       WHERE rendező NOT LIKE "George*" AND rendező NOT LIKE "Steven*";
SELECT cím FROM filmek WHERE hossz BETWEEN 90 AND 110;
SELECT rendező FROM Oscar WHERE LEFT(rendező,1)="S"

A mezőlistában nem csak mezők, hanem bármilyen kiszámítható érték szerepelhet. A következő példa csak a filmek bemutatásának évét mutatja:
SELECT cím,YEAR(bemutat) FROM Filmek;

Speciális érték a NULL. Ez különbözik mind az üres szövegtől. mind a nulla számértéktől; azt jelenti, hogy nincs adat. Például nem ismert a film bemutatójának ideje. A NULL értékek kezeléséhez speciális műveletek tartoznak:
  • IS NULL (a mező értéke NULL)
  • IS NOT NULL (a mező értéke nem NULL
SELECT cím FROM Filmek WHERE bemutat IS NOT NULL;

Rendezés

A végeredmény rendezhető is, ekkor a választó lekérdezés így folytatódik:
SELECT ... ORDER BY szempont [ ASC/DESC ] [,...] ;
Az ORDER BY után egy vagy - többszempontú rendezésnél - több kifejezés, általában mező szerepel. Többszempontú rendezésnél ha az első szempont szerint vannak egyformák, a következő szempontot veszi figyelembe. ASC=növekvő, DESC=csökkenő (alapból ASC). Példák:
SELECT * FROM Filmek ORDER BY cím;
SELECT * FROM Filmek ORDER BY bemutat DESC;
SELECT * FROM Filmek ORDER BY YEAR(bemutat) DESC,cím ASC;

Az utolsó példa a filmeket a bemutatás éve szerint rendezi csökkenő sorrendbe, az azonos évben bemutatottakat pedig cím szerint ábécérendbe.

Elsők kiválasztása

Ha nem a teljes eredményre van szükségünk, hanem annak csak első n rekordjára, a lekérdezést így kell kezdeni:
SELECT TOP n ...
Ugyanakkor az adattáblában a rekordok sorrendje esetleges, nincs sok értelme tehát kiválasztani pont az első kettőt. Ilyen formában akkor használhatjuk a lekérdezést, ha mindegy, melyik rekordra van szükségünk. Pl.:
SELECT TOP 1 * FROM Filmek;

Ha azonban ezt rendezéssel kombináljuk, máris sokkal hasznosabb:
SELECT TOP 1 cím FROM Filmek ORDER BY hossz DESC;
Ha a filmeket hossz szerint csökkenő sorrendbe rakjuk, az első lesz a leghosszabb. Így az első rekord kiválasztásával kiírhattuk a leghosszabb film címét.  A példában két leghosszabb (121 perces) film is szerepel: ilyen esetben a lekérdezés mindkettőt megadja! További példák:
SELECT TOP 1 rendező FROM Oscar ORDER BY díjak DESC;
SELECT TOP 1 cím,rendező FROM Filmek ORDER BY bemutat;

Csoportosítás és részösszegek

Ha a rekordokat valamely szempont (pl. a filmek nyelve) szerint csoportosítjuk, az eredményben annyi rekord szerepel, ahány csoportunk van (a példában 4). A lekérdezés ilyenkor nem a tábla egyes rekordjaira, hanem a csoportra jellemző értékeket ad meg (pl. melyik nyelvű filmből mennyi van, milyen hosszú a leghosszabb film).
SELECT ... GROUP BY csoportosítási alap ...

Ez a példa nyelv alapján csoportosítja a rekordokat. Minden csoportnyk kiírja a nyelv mezőjét (ez a csoport jellemzője), eredményül 4 rekordot kapunk.
SELECT nyelv FROM Filmek GROUP BY nyelv;

Több csoportosítási szempontot használva több, kisebb csoportot kapunk. Ha a filmeket nyelven kívül bemutatás hónapja szerint is csoportosítjuk, az angol filmeken belül is 4 csoport keletkezik, így összesen 7 rekordunk lesz. Vagyis azok a rekordok kerülnek egy csoportba, melyeknél mindkét csoportosítási alap megegyezik.

A lekérdezésben a következő összegző függvényeket használhatjuk:
  • SUM, AVG: összeg és átlag
  • MIN, MAX: az értékek közül a legkisebb/legnagyobb
  • COUNT: az értékek darabszáma, a NULL értékeket nem számolja bele
Ez a példa kiszámítja a filmek átlagos hosszát:
SELECT AVG(hossz) FROM Filmek;
ez pedig a júiusban bemutatott filmek számát:
SELECT COUNT(cím) FROM Filmek WHERE MONTH(bemutat)=6;

Ha csoportosítást is használunk, az összegző függvények minden csoportban elvégzik az összegzést.
Példa: melyik nyelven hány film van?
SELECT nyelv,COUNT(cím) FROM Filmek GROUP BY nyelv;

Látható, hogy a kiírandó mezőknél a csoportosítási alap mellett még összegző függvények fordulhatnak elő, hiszen ezek is a csoportra egyértelműen jellemző adatokat határoznak meg.
COUNT használatánál majdnem mindegy, melyik mezőt számoljuk meg. A legutóbbi példában azonban COUNT(cím) helyett COUNT(bemutat)-ot írva eggyel kevesebb lesz az angol nyelvű filmek száma, mert a NULL értékű bemutatást nem számolja bele.

WHERE vagy HAVING

A WHERE utáni feltételt az adatbázis-kezelő minden egyes rekordra vizsgálja. A csoportosítást csak a feltételt teljesítő rekordokra végzi el. A csoportképzés utáni összegzett eredményeket a HAVING használatával vizsgálhatjuk. A sorrend tehát: WHERE, GROUP BY, HAVING.

Melyik hónapban mutattak be 1-nél több filmet?
SELECT MONTH(bemutat) FROM Filmek GROUP BY MONTH(bemutat) HAVING COUNT(cím)>1;
Ha ráadásul csak az angol nyelvű filmek érdekelnek:
SELECT MONTH(bemutat) FROM Filmek WHERE nyelv="angol" GROUP BY MONTH(bemutat) HAVING COUNT(cím)>1;

További példa:
SELECT nyelv FROM Filmek GROUP BY nyelv HAVING COUNT(cím)=1;

Kapcsolatok: INNER JOIN

A példabeli két tábla között a Rendező mező teremt kapcsolatot. Szeretnénk kiírni a filmek címe mellett a film rendezője Oscar-díjainak számát is.
SELECT cím,díjak FROM Filmek,Oscar;
A lekérdezésben ekkor mindkét táblát megadhatjuk. Azt azonban nem adtuk meg, hogyan kell a két táblát összekapcsolni. Az eredményben összesen 8*6=48 rekord szerepel, az adatbázis-kezelő ugyanis az első tábla minden rekordjához párosította a második tábla összes rekordját! Erről meggyőződhetünk ezzel a lekérdezéssel:
SELECT Filmek.rendező,Oscar.rendező FROM Filmek,Oscar;
Ez nyilván helytelen. (Figyeld meg, hogy itt már az azonos nevű mezők miatt meg kellett adni, melyik tábla rendező mezőjére hivatkozunk!)
Eddigi ismereteinkkel is megoldhatjuk a feladatot: a 48 lehetséges párosításból válogassuk ki azokat, amelyeknél a két táblából való rendező mezők megegyeznek.
SELECT cím,díjak FROM Filmek,Oscar WHERE Filmek.rendező=Oscar.rendező;
és valóban, így már csak a szükséges 8 rekordot kaptuk. Ez azonban - legalábbis nagy adatbázisoknál - nem hatékony.

Kapcsolat megadásával az adatbázis-kezelő végigmegy az első táblán, és minden rekordhoz megkeresi a kapcsolat által megadott párját (vagy több párját) a másik táblából.
SELECT ... FROM tábla1 INNER JOIN tábla2 ON mező1=mező2 ...
A példában:
SELECT cím,díjak FROM Filmek INNER JOIN Oscar ON Filmek.rendező=Oscar.rendező;
Ha nincs pár, 0, ha több pár is van, több rekord lesz az eredmény.


Comments