Az SQL nyelv.

 

Általános áttekintés az SQL nyelvről

 

Az előző fejezetekben a relációs adatmodellre támaszkodó állománykezelő dBase rendszerek működését és használatát vettük át. A dBase rendszerek áttekintése során tapasztalhattuk, hogy a megvalósított integritási és műveleti elemek mennyire eltérnek a relációs adatmodellnél megismert irányelvektől, melynek ugyan nagyban lecsökkentették a rendszer erőforrásigényét, és széles körben elterjesztették a relációs elveket, azonban e rendszerek csak igen kis korlátozott méretű és igényű rendszerek kezelésére alkalmas. A nagyobb mértékű, érzékenyebb integritási és védelmi igényekkel fellépő rendszerek esetében a dBase alapú adatbáziskezelők nem nyújtanak megfelelő megoldást. Ezen esetekben egy tekintélyesebb,igazi relációs alapokon nyugvó adatbáziskezelőt kell kiválasztani.

Mint már korábban is mondtunk a relációs adatbáziskezelők a mini gépek kategóriájából fejlődtek ki. Az első RDBMS, a System/R kidolgozásánál fontos szempont volt, hogy minnél tökéletesebben megvalósítsák Codd elveit, a relációs adatmodell és a relációs algebra elemeit. A kezelő felület esetében tehát egy olyan parancsnyelvet kellett kidolgozni, amely lehetővé teszi a relációknak, mint rekord halmazoknak a kezelelését, s melyben a lekérdezésekben deklaratív formában lehet megadni a kívánt eredményreláció tulajdonságát, felhasználva a relációs algebra ismert műveleteit, mint pl. a szelekció, projekció vagy összekapcsolás.

Ez a kezelő felület lényegesen eltér a hagyományos rekord orientált, ciklusokat és elágazásokat tartalmazó lekérdező felületektől. A System/R-ben megvalósított relációs algebrán alapuló kezelő felületet SEQUEL-nek nevezték el, utalva e nyelv néhány alapvonására. A SEQUEL, mint rövidítés a Structured English Query Language kifejezésre utal, azaz jelzi, hogy ez egy struktúrált, az angol nyelvre épülő lekérdező nyelv. A parancsok kulcsszavai értelmes, a művelet jelentéséhez közel álló angol szavak, melyekből a parancsok mint mondatok hozhatók létre. Természetesen igen szigurúan kötött e mondatok szerkezete, emiatt is nevezhető struktúrált nyelvnek. A lekérdezés szó azért szerepel a rövidítésben, mert ugyan e nyelv többre is alkalmas, mint puszta lekérdezés, azonban a nyelv igazi ereje a lekérdezési részben, a relációs algebrára épülő komponensben rejlik. E nyelv részleteit elsőként 1974-ben publikálták. Az elnevezés a későbbiekben az SQL-re változott, de a nyelv felépítése, működési filozófiája változatlan maradt.

 

A System/R után következő RDBMS rendszerek a belső működési elveken kívül e kezelő nyelvet is áttvették, átvéve a kulcsszavakat és az operátorokat is. Emellett természetesen voltak egyéni, egyedi megvalósítások is, mint például a DEC RDB kezelő nyelve, melyek más szintaktikára épültek, azonban e nyelvek idővel mind eltűntek, nem bírták a versenyt az SQL-lel szemben. Az SQL lassan egyeduralkodóvá vált az RDBMS piacon, emiatt e igazi adatbáziskezelő szolgáltatásokat nyujtó rendszereket szokás SQL adatbázisoknak is nevezni. A SQL elterjedésének és előnyeinek köszönhetően szabvánnyá vált a relációs adatbáziskezelők világában, s azóta elterjedése mégjobban fokozódik, s lassan a korábbi xBase-es termékek is mind bevonják rendszerükbe az SQL alapú kezelő felületet is.

 

Az SQL első szabványosítási lépése az ANSI nevéhez és az 1986-os évhez kötődik. A rákövetkező évben a nemzetközi szabványhivatal az ISO is felvette és megjelent az ISO SQL szabvány. A gyakorlati élet fejlődéseit követve többször, pontosabban napjainkig kétszer módosították a szabványt, először 1989-ben, majd utána 1992-ben. Az egyes SQL szabványok megkülönböztetésére meg szokták adni az évszámot is, s így hivatkozhatunk SQL86-ra, SQL89-ra, vagy éppen SQL92-re.

Az SQL szabványok valójában a piacon megjelenő RDBMS-ek kezelő felületeit kívánják mederben tartani, vagy éppen követni. Az SQL86 éppenséggel csak a legfontosabb elemeket gyűjtötte össze a megvalósulásokból, ezért már megalkotásakor is több olyan RDBMS volt, mely kezelő nyelve többet tudott a szabványnál. Az azóta készült RDBMS megvalósítások egyre többet nyújtanak a műveletek és az integritási feltételek, és a vezérlő utasítások terén is. A lemaradást igyekeztek bepótolni az újabb. SQL89 szabvánnyal, sőt az SQL92-t már előre mutatási céllal készítették. Az SQL92 egy sor olyan elemet is tartalmaz, melyet a legfejlettebb RDBMS-ek is csak a közeljövőben tudnak megvalósítani.

Éppen ezért a SQL92 esetében a szabvány is különböző teljesítettségi fokot

állapít meg:

 

- entry level: alapszint, mely az SQL89 kiegészítve a kulcsra és

értéktartományra vonatkozó integritási feltételekkel

- intermediate level: közbenső szint

- full level: a teljes szint, mely minden elemet tartalmaz

 

Az egyes SQL szabványokról elmondható, hogy viszonylag kompatibilisek egymással alulról felfelé, azaz az SQL86 ismerete felhasználható az SQL92-ben is, ugyanis az újabb szabványok a régi elemeket rendszerint változatlanul hagyják, s a változás az újabb elemek befűzéséből ered.A SQL szabványokra épülő piacon megjelenő adatkezelő nyelveket szintén SQL nyelveknek nevezik, azonban tudnunk kell, hogy a megvalósított nyelvek a szabványos elemek mellett egyedi elemekkel is rendelkeznek, hiszen az SQL szabvány átfogó jellege ellenére több olyan dologra nem tér ki, melyek a megvalósított rendszerekben viszont szükségesek, mint pl. az adattípusok fajtái és jelölésének kérdése.

Így valójában egy konkrét RDBMS kezelő nyelvének használatához három szintet kell bejárni. Mindenek előtt ismerni kell a relációs modellt, a relációs algebrát. Ezután ismerni kell a SQL szabványt, majd legvégül meg kell ismerkedni a kiválasztott RDBMS SQL alapú kezelő nyelvével. Ha ezt az utat követjük, akkor egy másik SQL alapú rendszerre való átállást igen hatékonyan és gyorsan megoldhatjuk.

 

Az SQL részletezése előtt azonban célszerű előbb az SQL általános leírását, jellemzését áttekinteni illetve összefoglalni. Mint már az előzőekből kiderült az SQL a relációs adatmodellen alapuló adatbázisok kezelő nyelve, méghozzá szabványosított nyelve. Ebből egyrészt következik az, hogy az SQL-t nem tekinthetjük adatbáziskezelő rendszernek, hiszen az SQL annak csak egy komponense, a kezelő nyelvezete. Egy RDBMS az SQL mellett rendelkezhet más kezelő nyelvezettel is, mint pl. az RDB rendszernek (DEC VMS alapon futó RDBMS) is volt SQL alapú és saját kezelő nyelve is.

A másik lényeges jellemzője az SQL-nek, hogy kimondottan az adatbáziskezelés, az adatkezelés megvalósítására szolgál, tehát nem tartalmaz algoritmikus elemeket, mint pl. a ciklusszervezés vagy elágazások, illetve nincsenek benne a felhasználói képernyőkezelésre, a normál file kezelésre vonatkozó utasítások. Emiatt mondhatjuk, hogy az SQL nem algoritmikus nyelv, ellentétben a C vagy a Pascal nyelvvel. Az adatkezelésnél az SQL a relációs algebrára épül, tehát a relációkat halmazokon végzi, s az SQL-ben egész relációkra vonatkozó műveletek adhatók ki. Mint ismert a hagyományos programozási nyelvek a rekordorientált adatmegközelítést használják. E különbség kihangsúlyozására nevezik az SQL-t halmazorientált nyelvnek.

Az SQL és a relációs adatmodell kapcsoltára vonatkozólag megemlíthető, hogy az SQL több különböző szinten keresztül közelíti az elméleti relációs adatmodellt, amiben benne van, hogy bizonyos elemeket nem tartalmaz, ezért az SQL a relációs adatmodellnek csak részleges leképzése.

 

Az adatkezelési funkciókon belül az SQL-nek a legszélesebb lehetséges igényhalmazt ki kell elégítenie, hogy az SQL önmagában használható legyen, ne kelljen mellé más kiegészítő nyelv. Az eddig ismert adatkezelési funkciókra hivatkozva az SQL-ben is elvégezhetőnek kell lennie az adatszerkezetek definiálásának, ami megfelel a már általánosságban említett DDL nyelvi komponensnek, mely segítségével létrehozhatók és módosíthatók többek között a különböző relációk, domainek és integritási feltételek. Mivel a létezô RDBMS-ekben a relációk elnevezésére inkább a táblázat kifejezés honosodott meg, ezért a továbbiakban, ha az RDBMS-ben fizikailag tárolt relációra gondolunk, akkor a táblázat elnevezést fogjuk használni.

A DDL mellett az SQL része az adatkezelő, úgynevezett DML csoport is, melyben elvégezhetők a táblázatokban tárolt adatok módosítása, törlése vagy új adatok felvitele.

Ugyan legtöbbször az adatok lekérdezését is ebbe a körbe veszik, de az SQL-ben, a relációs modellben betöltött fontossága miatt külön csoportot alkotnak a lekérdező utasítások, melyre a Query elnevezést használjuk. A

megvalósított RDBMS kezelő nyelvek hatásának köszönhetően az SQL tartalmaz a relációs adatmodellhez szorosan nem kötődő utasításokat is, melyekkel a műveletek végrehajtását szabályozhatjuk, vezérelhetjük. Emiatt a negyedik utasításcsoportot adatvezérlő csoportnak is nevezik, melynek jelölése a DCL.

 

Az SQL utasításokat több különböző módon is eljuttathatjuk az RDBMS-hez. A legegyszerűbb módszer egy interaktív SQL parancsértelmező használata, mely közvetlenül kapcsolódik az RDBMS-hez. Ebben a megjelenő prompt után kiadhatjuk az SQL parancsot, melynek rögtön végre is hajtódik, s az eredményt a terminálra kiírva kapjuk meg. Mivel így csak adatkezelő utasításokat adhatunk ki és feltételezi, hogy a felhasználó ismeri az SQL-t, ez a módszer nem alkalmas normál alkalmazói programok készítésére. Ehhez az SQL utasításokat algoritmikus elemekkel kell kibővíteni, melyre kétféle lehetőség is kínálkozik. Egyrészt magát az SQL-t lehet egy létező algoritmikus nyelvbe beépíteni, beágyazni, másrészt az SQL-t lehet kibővíteni algoritmikus elemekkel. Természetesen ez utóbbi esetben a kapott nyelv már igen messze esik az SQL szabványtól. A valóságban viszont mindkét eset előfordul.

 

Összefoglalóan a következőket állapíthatjuk meg az SQL-ről:

 

- relációs RDBMS kezelő nyelv szabvány, nem RDBMS

- relációs algebrán alapszik

- szöveges, nem algoritmikus, előíró jellegű utasításokat tartalmaz

- halmazorientált

- négy utasításcsoportot tartalmaz:

- adatdefiníciós

- adatlekérdező

- adatkezelő

- adatvezérlő

- kiadható interaktívan és algoritmikus környezetbe építve

 

Az SQL általános áttekintése után az SQL utasításokat vesszük sorra,

mégpedig elsőként a legegyszerűbb SQL86 szabványt, mivel sok esetben

már ez is elegendő erővel rendelkezik utasításaink kiadásához köszönhetően

a kompatibilitásnak, s egyszerűsége miatt könnyebben is elsajátítható. Az

SQL86 ismertetése után röviden összefoglaljuk az SQL92 legfontosabb

változásait és lehetőségeit.

 

 

Az SQL86 szabvány DDL utasításai

 

Elsôként a DDL komponenst vesszük át, hiszen az alkalmazások során is elôbb meg kell alkotni a struktúrákat, az üres adatszerkezeti elemeket, hogy a késôbbiek folyamán fel tudjuk tölteni azokat adatokkal, illetve fel tudjuk használni a bennük letárolt adatokat. A relációs adatmodell esetére vonatkoztatva ez azt jelenti, hogy elôbb létre kell hoznunk a táblázatokat, mely során üres, azaz adatok nélküli táblázatok jönnek létre. Szemléletesen kifejezve úgy is mondhatnánk, hogy elsôként a táblázatokat fejlécét alkotjuk meg, s a táblázat sorait csak késôbbi műveletekkel hozzuk létre.

 

A táblázat szerkezete, sémája, mint már ismert, a táblázathoz tartozó mezôkkel írható le. Azaz, ha megadjuk hogy milyen mezőkből épül fel a táblázat, akkor ezzel egyértelműen megadjuk a táblázat szerkezetét is, tehát két táblázat szerkezete különbözik egymástól, ha található olyan mezô, mely az egyikben benne van,a másikban nincs. A mezôk megadása pedig a mezô névének és a mezô adattípusának a kijelölésével történik. Mivel a szerkezetek megadása nem azonosít egyértelműen egy táblázatot, hiszen több táblázat is létezhet ugyanazzal a szerkezettel, másrészrôl a szerkezet leírása igen hosszadalmas, ezért minden táblázat kap egy egyedi azonosító nevet az adatbázison belül. Ezzel a névvel egyértelműen lehet azonosítani a táblázatokat a műveletek során. A táblázat névnek tehát az adatbázison belül, a mezônévnek pedig a táblázaton belül kell egyedinek lennie. Összefoglalva a táblázat létrehozásakor meg kell adni a táblázat nevét valamint azôt alkotó mezôk nevét és típusát.

 

Mivel a relációs modellnek az integritási feltételek is szerves részei, és az integritási feltételek szorosan kötôdnek a táblázatokhoz, rendszerint a táblázathoz annak megszűnéséig kötôdnek, ezért a táblázatok létrehozásakor lehetôséget kell adni a hozzá kapcsolódó integritás feltételek definiálására is. Az SQL’86 szabvány nem tartalmazza az összes általunk említett integritási feltételt, azok együttesen csak a későbbi szabványokban jelentek meg. Így itt még nincs lehetőség sem a kulcs, sem a kapcsolókulcs integritási feltételek megadására. Az átmeneti helyzetet jól mutatja, hogy a korábbi RDBMS változatok ugyan már elfogadták a PRIMARY KEY integritási feltételt, azonban figyelmen kívül hagyták ezta megkötést, azaz nem tudták biztosítani az elsődleges kulcs feltétel ellenőrzését.

 

A továbbiakban az alábbi két táblán mutatjuk be az utasításokra vonatkozó példákat:

 

AUTO

TUL

RSZ

TIP

SZIN

EVJ

AR

1

bkx720

Opel

bordo

1991

1000000

1

cmt111

Golf

piros

1981

350000

2

aaa156

Trabant

feher

1985

100000

3

lui999

Opel

kek

1991

450000

1

kjs234

Lada

kek

1989

275000

 

EMBER

 

ID

NEV

SZULEV

CIM

1

Bela

1975

Budapest

2

Geza

1979

Miskolc

3

Feri

1974

Pecs

 

Az SQL utasításait az angol nyelvhez igazítva rögzítette le, ennek megfelelôen az adatszerkezetek létrehozásának utasítása a CREATE utasítás. A táblázat létrehozása a következô utasítással lehetséges:

 

CREATE TABLE táblázatnév (m1 t1 [i1][,..., mi ti [ii]],[ig]);

 

Az SQL utasításokat mindíg pontosvesszô határolja, zárja le. Az utasítás több több soron keresztül is folytatódhat, csak a pontosvessző jelzi az utasítás végét. Az utasítás leírásában az mi mezônevet, a ti típust, az ii mezőhöz kötött, míg az ig mezőcsoporthoz kötött integritási feltételt jelent. Az integritási feltételt lehet mezôhöz kötve és táblázathoz kötve is megadni. Az ii a következô elemeket tartalmazhatja:

 

UNIQUE : a mezô egyedi értékeket tartalmaz, azaz egy érték nem

fordulhat elô egynél több rekordban

NOT NULL : a mezônek tartalmaznia kell értéket, nem maradhat üres

egyetlen egy rekordban sem

 

Egy mezőre több integritási feltétel is megadható, ekkor egymás után írjuk az egyes integritási elemek kulcsszavait. Az ig esetében külön meg kell adni, hogy a feltétel mely mezôre vonatkozik. A mezőneveket rendszerint a kulcsszót követő zárójelben adhatjuk meg. A megadott két feltételből csak az UNIQUE értelmezhető külön mezőcsoporton, melynek használhata:

 

UNIQUE (m1 [,m2..,mi])

 

Az adattípusok esetében sajnos nem lehet szabványosított hivatkozást megadni, mivel a legtöbb elterjedt RDBMS rendszer más-más elnevezést használ. Mi most csak a legfontosabb alaptípusokat a következô megjelölésekkel együtt vesszük át:

 

CHAR (n) : n hosszúságú szöveg

NUMBER (n [,m]) : n hosszú számjegy, melybôl m a tizedesjegyek hossza

DATE : dátum

 

A példaként megadott táblák az alábbi utasításokkal hozhatók létre:

 

CREATE TABLE AUTO (TUL NUMBER(3) NOT NULL,

RSZ CHAR(7) NOT NULL UNIQUE,

TIP CHAR(10),

SZIN CHAR(10),

EVJ NUMBER(4),

AR NUMBER(8));

 

CREATE TABLE EMBER (ID NUMBER(3) NOT NULL UNIQUE,

NEV CHAR(20),

SZULEV NUMBER(4),

CIM CHAR(20));

 

A létrehozott táblázatok szerkezetét az SQL86 szabványban nem lehet már módosítani, erre csak a késôbbi változatok adnak lehetôséget, s erre fog szolgálni az ALTER parancs. Ha módosítani nem is, de törölni lehet a táblázatokat az SQL86 keretében. A táblák törlésének parancsa a DROP. A törlés utasítása formailag igen egyszerű, csak meg kell adni táblázat nevét:

 

DROP TABLE táblázatnév;

 

Az elôzôleg létrehozott auto táblázatot a következô utasítás törli ki az

adatbázisból:

 

DROP TABLE auto;

 

A CREATE TABLE utasítással létrehozott táblázat a bázistáblázatok közé tartozik, azaz a tartalmazott rekordok fizikailag is letárolásra kerülnek. Mint már említettük a bázistáblázatokon kívül létezik egy úgynevezett view is, mely egy nevesített leszármaztatott táblázatnak tekinthetô. A view-hoz tartozó rekordok nincsenek tehát közvetlenül letárolva az adatbázisban, eltérôen a bázistáblázat rekordjaitól. Természetesen a view rekordjainak minden mezôje benn van az adatbázisban, valamely bázistáblázatban, de nics olyan fizikai adatsor, mely a view-nak megfelelô struktúrában és rekord elôfordulásokkal tárolná a view egy elôfordulását. A view tartalma mindig egy, a táblázatokon értelmezett lekérdezési műveletsor eredménye, s a rendszer a view-hoz kapcsolódóan, a megnevezés mellett az elôállító műveletsort tárolja, s szükség esetén, azaz amikor hivatkozunk a view-ra, akkor elvégzi a kijelölt műveletsort, s a kapott eredménytáblázatot adja meg mint a view aktuális elôfordulása. A view létrehozásának parancsa:

 

CREATE VIEW viewnév [(m1 [,m2...,mi])] AS műveletsor;

 

Az mi mezônevet jelent, ugyanis lehetôség van arra, hogy a view az egyes mezôket a felhasznált alaptáblázatokban megadott nevüktôl elérô, új névvel azonosítjuk. Ha nem adunk meg mezôneveket, akkor a view mezô ugyanazt az elnevezést viselik, mint az alaptáblázatban is viselnek. A műveletsor egy lekérdezési műveletsort jelent, melynek a SELECT utasítás lesz a kulcsszava. A SELECT utasítást késôbb vesszük, most csak elôrevetítésként a példában egy olyan auto2 view-t kreálunk, mely a FIAT típusú autók rendszámait tartalmazza csak:

 

CREATE VIEW auto2 AS SELECT rsz FROM auto WHERE tip LIKE 'Fiat%';

 

A létrehozott view hasonlóan szüntethetô meg, mint a bázistáblázat:

 

DROP VIEW viewnév;

 

Ami elsô rátekintésre is szembetűnhet, hogy a táblázatok definiálásakor végül is csak minimális számú integritási feltételt lehetett megadni, illetve azokat is csak részlegesen lehet használni az adatbázistervezés tükrében. Ez alatt most arra gondolunk, hogy a relációs modellben a tervezés során minden táblázathoz kötelezôen hozzárendeltünk egy kulcsmezôcsoportot. Az SQL nyelvben viszont nem kötelezô kulcsmezô létezése, hiszen táblázat megadásakor elhagyhatunk bárminemű integritási feltételt, ami a relációs adatmodell elmélete szerint ugyan megengedett, de ez egy újabb esetleges hibaforrást jelenthet az adatbázis tervezése során.

 

 

Az SQL86 DML utasításai

 

Ha már léteznek táblázatok, melyek a létrehozáskor tehát még üresek, akkor elkezdhetjük ôket adatokkal feltölteni. Ezen tevékenységek végrehajtására szolgálnak a DML utasítások. A három idetartozó alaptevékenység

 

- az adatok bevitele

- az adatok törlése

- az adatok módosítása

 

Az adatok felvitele az INSERT utasítással történik, mely során meg kell adni, hogy mely táblázatba visszük fel az adatokat, illetve meg kell adni, hogy milyen mezôértékeket vegyen fel az új rekord. Az utasítás alakja:

 

INSERT INTO táblázatnév VALUES (e1 [,e2...,ei]);

 

Az utasításban az ei szimbólum mezôértéket jelenti. A numerikus adatértéknél számjegyeket és tizedespontot használhatunk, míg a szöveges adatkonstansokat idézôjelekkel határoltan adhatjuk meg. A dátum típusú adatok megadása rendszerint egy kicsit körülményesebb, mivel egy konverziós függvény közbeiktatásával állíthatjuk elô a dátumtípusú adatot. Az zárójelben megadott adatértékek sorba hozzárendelôdnek az egyes mezôkhöz. Az elsô érték a táblázat létrehozásakor elsôként megadott mezôhöz rendelôdik, míg az utolsó érték a felsorolásban utolsónak megadott mezôhöz rendelôdik. Ebbôl következôen az értéklistában ugyanannyi elemnek kell szerepelnie, mint amennyi mezôt a táblázat tartalmaz. Példaként bôvítsük az autó táblázatot egy rekorddal:

 

INSERT INTO auto VALUES (3,'bhk546','Fiat','kek',1989,NULL);

 

A listában szereplô NULL érték arra utal, hogy üresen hagyjuk az ár mezôt. A felvitt auto tul mezője a 3 értéket, az rsz mezôje a bhk546 értéket, a tip mezôje a Fiat, a szin mezôje a kek, míg az evj mezôje az 1989 értéket kapja. Az ár üresen marad.

 

A rekordok felvitelének van egy másik útja is as SQL-ben, amikor is a beszúrandó rekordokat nem egyenként visszük fel, hanem egy már létezô táblákból állítjuk elô a felvivendô rekordokat, azaz egy lekérdezési eredménytáblázat rekordjait tároljuk le egy táblázatban. Ebben az esetben a beszúrásnál meg kell adni a lekérdezési műveletsort is, mely az elôzôekhez hasonlóan SELECT kulcsszóval fog kezdôdni.. Az utasítás alakja:

 

INSERT INTO táblázatnév műveletsor

 

A művelet egy későbbiekben tárgyalandó SELECT lekérdezési utasítás lehet.

 

A felvitt rekordok kitörlésére a DELETE utasítás szolgál. A törlés egyértelműen elvégezhetô, ha megadjuk, hogy melyik táblázatból, mely rekordokat töröljük ki. A rekordok kiválasztása egy szelekciós feltétellel lehetséges. A szelekciós feltétel a rekordokon értelmezett logikai kifejezés, mely igaz vagy hamis értéket vehet fel. Ha a kifejezés értéke igaz, akkor törlôdik a rekord, ha a feltétel hamis akkor nem törlôdik a rekord. Ha a szelekciós feltétel nem szerepel az utasításban, akkor az alapértelmezés szerint minden rekord törlôdik a táblázatból. Ez nem azt jelenti, hogy maga a táblázat törlôdik, mert az megmarad csak éppen üresen. Az utasítás alakja:

 

 

DELETE FROM táblázatnév [ WHERE feltétel];

 

A feltétel egy összetett logikai kifejezés is lehet, melyben az elemi

kifejezéseket az

 

AND : logikai és

OR : logikai vagy

NOT : logikai tagadás

 

köti össze vagy módosítja. Az egyes elemi kifejezésekben az alábbi relációs operátorok szerepelhetnek, ahol most a reláció kifejezését matematikai értelemben használjuk:

 

= :egyenlô

> :nagyobb mint

< :kisebb mint

>= :nagyobb egyenlô mint

<= :kisebb egyenlô mint

<> :nem egyenlô

BETWEEN :között

IN :valamely listaértékkel megegyezik

LIKE :hasonló

IS NULL :üres

 

A szokásos relációjelek mellett négy speciális relációjel található. A BETWEEN operátorral megvizsgálhatjuk, hogy egy érték valamely intervallumon belül helyezkedik-e el vagy sem. Alakja:

o1 BETWEEN o2 AND o3

 

ahol o2 kisebb egyenlô mint o3, és a kifejezés akkor szolgáltat igaz értéket, ha o1 értéke az o2-nél nagyobb egyenlô és az o3 értékénél pedig kisebb egyenlô. Az IN operátorral egy lista elemeivel hasonlíthatunk össze egy értéket, alakja:

 

o1 IN (o2 [,o3...,oi])

 

A fenti kifejezés akkor ad vissza igaz értéket, ha o1 értéke megegyezik valamely listabeli értékkel. A LIKE operátor a sztringek összehasonlítására szolgál. A normál = operátortól eltérôen alkalmas nem szigorú egyezôség vizsgálatra is. Ugyanis az = operátor esetén akkor teljesül az egyenlôség, ha a két oldal byte-ról byte-ra megegyeznek egymással. A LIKE operátorral lehet közelítô egyezést is vizsgálni, azaz amikor csak bizonyos részsztringek egyezôségét, azaz bizonyos minták elôfordulását követeljük meg. Használata:

 

o1 LIKE 'sssss' [ESCAPE 'x']

 

A fenti kifejezés az o1 értékét az 'sssss' mintához hasonlítja. A minta tartalmazhat normál karaktereket és úgynevezett joker karaktereket is, hasonlóan a DOS-ból ismert állományspecifikációs * és ? karakterekhez. Itt is két ilyen speciális karakter létezik, melyek jelölése:

 

% : egy tetszôleges karaktersorozatot helyettesít

_ : egyetlen egy karaktert helyettesít

 

Ekkor az o1 és a minta összevetésekor a normál karaktereknél pontos egyezôséget követel meg a rendszer, míg a % karakter tetszôleges szövegrészt helyettesíthet illetve az _ pedig egyetlen egy karaktert helyettesít. Mivel a fenti két karakter szerepe kötött, probléma léphet fel, ha ezeket normál karakterként kívánjuk felhasználni. A probléma megoldására vezették be a ESCAPE opciót, mellyel kijelölhetünk egy olyan karaktert, mely nem fordul elô a normál karaktereink között, és ennek a szerepe az lesz, hogy a fenti két joker karakter elé írva a mintában, jelezze, hogy azok nem helyettesítôként, hanem normál karakterekként szerepelnek. Ez az ESCAPE karakter mindig csak a közvetlenül utána álló joker karakterre vonatkozik. A leírásban x jelentette a kiválasztott ESCAPE karaktert. A LIKE kifejezés akkor ad igaz értéket, ha az o1 illeszkedik a mintára. Példaként vesszük azt az esetet, amikor azon o1 sztringértékeket keressük, melyek a 'DOS_' résszel kezdôdnek:

 

o1 LIKE 'DOS^_%' ESCAPE '^'

 

A negyedik operátor az IS NULL, mely azt vizsgálja, hogy az elôtte álló kifejezés üres-e vagy sem. Alakja:

 

o1 IS NULL

 

A kifejezés igaz értéket vesz fel, ha o1 nem üres. A fenti operátorok közül a normál relációjelek mindegyikének megvan a tagadása is a jelek között, viszont ez utóbbi négy operátor egyikének sincs negált párja az operátorok között. Ezért ezek negáltját a NOT tagadást jelölô kulcsszóval képezhetjük, méghozzá a következô formában:

 

o1 NOT BETWEEN o2 AND o3

o1 NOT IN (o2 [,o3...,oi])

o1 NOT LIKE 'sssss' [ESCAPE 'x']

o1 IS NOT NULL

 

A NOT szó az IS NULL operátor kivételével az operátor szó elé kerül. A fenti

kifejezések jelentése sorban a következô:

 

o1 nincs o2 és o3 között

o1 nincs benne a listában

o1 nem hasonlít a mintára

o1 nem üres, azaz tartalmaz adatot

 

Az relációs operátorok operandusai, azaz az o1,o2 stb. oi értékei jelenthetnek konstansokat, és jelenthetnek mezôket is. A mezôk megadásánál azonban nemcsak az bázistáblázat mezôi szerepelhetnek, hanem a kiterjesztett táblázat mezôi is, azaz azon mezôk, melyek értékei az alapmezôkbôl származtathatók. A származtatás során hivatkozhatunk az alapmezô mellett konstans adatokra is, melyeket különbözô nem-logikai operátorokkal köthetünk össze. Ezek az operátorok megôrizhetik az operandusok adattípusát, de lehetnek konverziós operátorok is. Az operátorok nagy része különbözô rendszerspecifikus függvényben nyilvánul meg, de emellett megtalálhatók a közismert aritmetikai operátorok is, mint

 

+ : összeadás

- : kivonás

* : szorzás

/ : osztás

A többi operátor leírását azonban az SQL szabvány nem tartalmazza, így az alkalmazott RDBMS kézikönyvekhez kell nyúlni a további operátorok illetve függvények megismeréséhez.

 

Az elôbbiekben ismertetett szelekciós feltétel szemléltetésére azon utasítást mutatjuk be, amely kitörli az auto táblázatból azon rekordokat, melyek vagy a Trabant tipusra vonatkoznak vagy pedig 155555 Ft alatt van az áruk:

 

DELETE FROM auto WHERE tip LIKE 'Trabant%' OR ar < 155555;

 

A DELETE utasítással kapcsolatban még egyszer felhívjuk a figyelmet arra, hogy a

 

DELETE FROM auto;

 

utasítás az auto táblázat minden rekordját kitörli. Ha véletlenül mégis elkövetnénk ezt a hibát, szerencsére akkor sem igazán megijedni, mivel az SQL RDBMS rendszerek rendszerint rendelkeznek tranzakció visszagörgetési opcióval, s így a törlési parancs után még van esély az adatok visszaszerzésére. Ehhez ki kell adni a

 

ROLLBACK

 

utasítást, amely minden korábban végrehajtott tevékenységünket visszagörgeti, tehát újra el kell végezni a szükséges lépéseket, cserébe viszont visszakapjuk az elveszett adatokat.

 

A DML utasítások harmadik eleme az adatok módosítására vonatkozik. A módosítás egyértelmű elvégzéséhez meg kell adni, hogy mely táblázatban, azon belül mely rekordokban mely mezôk értékét kell módosítani és hogy milyenek legyenek az új értékek. Ezen adatok a következô utasítással adhatók meg:

 

UPDATE táblázatnév SET m1=e1 [,m2=e2...,mi=ei] [WHERE feltétel];

A parancsban a mi jelenti a módosítandó mezô nevét, ei a mezô új értékét, és a WHERE után álló feltétel pedig az elôbb már megismert szelekciós feltételt jelenti. Hasonlóan a DELETE parancshoz itt is igaz, hogyha elhagyjuk a szelekciós feltételt, akkor a táblázat minden rekordjában módosulnak a mezôértékek. Az auto táblában a FIAT típusok árának 25%-os emelése a következô utasítással érhetô el:

 

UPDATE auto SET ar = 1.25*ar WHERE tip LIKE 'Fiat%';

 

Egy igen érdekes kérdés a DDL utasításoknak a view-kra történô alkalmazási lehetôségeinek vizsgálata. Az relációs adatmodell elmélete szerint a táblázatok között nincs különbség a kezelhetôség tekintetében, ezért ugyanúgy lehetne dolgozni a bázistáblázatokkal mint a view táblázatokkal. Azonban a gyakorlati problémák miatt, ez sokszor csak igen nehezen megoldható, sokszor nem is egyértelmű, az utasítás eredménye a bázistáblázatokra vetítve, hiszen minden módosításnak a bázistáblázatoknál kell végrehajtódnia. E nehézségek miatt az SQL86 csak a bázistáblázatok vonatkozásában engedi meg a DML utasítások végrehajtását.

 

 

SQL96 lekérdezési utasítása

 

Az utasítások harmadik csoportja a lekérdezésekhez kapcsolódik. A lekérdezések mindegyike a SELECT utasításhoz kötôdik, igaz ennek az utasításnak számos variációja létezik. A SELECT utasításnak ugyanis alkalmasnak kell lennie a megismert relációs algebrai lekérdezô műveletek végrehajtására, mint pl. a szelekció, a projekció vagy a join. A jobb megértés végett nem egyszerre tekintjük át a teljes lehetôségskálát, hanem fokozatosan bôvítjük az utasításkört. A SELECT utasítás használata közben ne feledjük el, hogy eredménye mindig egy újabb táblázat lesz.

 

Elsôként a projekciót vesszük. A projekció a táblázatnak mezôire történô leképzését jelenti. Meg kell tehát adnunk, hogy mely táblázatból mely mezôk értékeire van szükségünk. Az utasítás alakja:

 

SELECT m1 [,m2...,mi] FROM táblázatnév;

 

Ahol mi mezônevet jelent. Ha tehát az autó táblából csak a rendszámok

érdekelnek bennünket, akkor a

 

SELECT rsz FROM auto;

 

utasítást kell kiadnunk. Az utasítás eredménye az alábbi listával adható meg:

 

RSZ

-------

bkx-720

cmt-111

aaa-156

lui-999

kjs-234

 

A

 

SELECT nev,cim FROM ember;

 

utasítás az emberek nevét és címét adja eredményül:

 

NEV CIM

-------------------- --------------------

Bela Budapest

Geza Miskolc

Feri Pecs

 

A projekcióval kapcsolatban két megjegyzés fűzhetô. Az elsô arra irányul, ha minden mezôre kíváncsiak vagyunk. Ekkor ugyanis nem szükséges minden mezônevet felsorolni, elegendô egy * karaktert megadni a mezôneveknél. Ha tehát az emeberek minden adatát tudni szeretnénk, akkor a

 

SELECT * FROM auto;

 

utasítást kell kiadni. Ekkor a következő listát kapjuk vissza:

 

ID NEV SZULEV CIM

---------- -------------------- ---------- --------------------

1 Bela 1975 Budapest

2 Geza 1979 Miskolc

3 Feri 1974 Pecs

 

A másik megjegyzés arra vonatkozik, hogy lehetnek olyan projekciók, melyeknél az eredménytáblázatban bizonyos rekordok többször is elôfordulhatnak. Ugyan ez az elméleti relációs adatmodell szerint ennek nem szabad lenne bekövetkeznie, de mint már mondtuk, az SQL csak egy közelítése az elméleti modellnek. Bizonyos esetekben hasznos lehet ez a duplázódás, máskor viszont feleslegesnek tűnik. Ezért az SQL lehetôséget ad az ismétlôdések elkerülésére, s a DISTINCT opcióval az eredménytáblázat minden sora különbözô lesz. Az opciót közvetlenül a SELECT kulcsszó után kell megadni. Ha tehát az autoípusokat szeretnénk kilistázni, akkor ehhez az alábbi parancsot kell kiadni:

 

SELECT DISTINCT tip FROM auto;

 

A kapott eredménylista:

 

TIP

----------

Golf

Lada

Opel

Trabant

 

A táblázat kiterjesztésének lehetôségét már korábban tárgyaltuk a szelekciós feltétel megadásakor, így most csak visszautalunk rá, megjegyezve, hogy a származtatott mezôt a projekciós részben is megadhatjuk. Ha pl. az autok árainak dollárban történô kiírásához az alábbi utasítás tartozik:

 

SELECT rsz, tip, ar/135.54 FROM auto;

 

ahol minden autóra még a rendszámot és a típust is kiírtuk.

 

A másik nagy műveletcsoport a szelekció. Ebben ki kell jelölni azon rekordokat, melyek átkerülnek az eredménytáblába. A szelekciós feltétel jellege és megadásának formája megegyezik a DELETE és UPDATE parancsoknál bemutatott szelekciós feltétellel. Mivel egy lekérdezési utasítás több relációs algebrai részműveletet tartalmazhat, ezért a SELECT utasításban szerepelhetnek már korábban megismert elemek is, az éppen vizsgált műveleti rész mellett. A részművelet tárgyalásánál viszont már csak a rövidített jelölést fogjuk használni. A szelekció megadása ennek megfelelően a következő alakban történik:

 

SELECT .... FROM ... WHERE feltétel;

 

ahol a SELECT és FROM utáni részek a korábban megadott elemeket tartalmazhatják. A piros színkódú autók rendszámainak lekérdezése így a következôképpen alakul:

 

SELECT rsz FROM auto WHERE szin = 'piros';

 

Az eredménytábla:

 

RSZ

-------

cmt-111

 

 

A szelekciós műveletek bemutatására vegyük a következő példákat.

 

- Irassuk ki az 1977 elott szuletett emberek neveit:

 

SELECT nev FROM ember WHERE szulev<1977;

 

Az eredménylista:

 

NEV

--------------------

Bela

Feri

 

- Irassuk ki a nem piros autok összes adatat:

 

SELECT * FROM auto WHERE szin<>'piros';

 

Az eredménylista:

 

TUL RSZ TIP SZIN EVJ AR

----------------------------------------------------------------------

1 bkx-720 Opel bordo 1991 1000000

2 aaa-156 Trabant feher 1985 100000

3 lui-999 Opel kek 1991 450000

1 kjs-234 Lada kek 1989 275000

 

- Irassuk ki az 1975 és 1980 között született emberek adatait:

 

SELECT * FROM ember WHERE szulev BETWEEN 1975 AND 1980;

 

Az eredménylista:

 

ID NEV SZULEV CIM

-------------------------------------------------------

1 Bela 1975 Budapest

2 Geza 1979 Miskolc

 

A BETWEEN után megadott két paraméter esetében az egyenlőség is megengedett.

 

- Irassuk ki a piros, kék, vagy zöld szinű autók rendszámait !

 

SELECT rsz FROM auto WHERE szin IN ('piros','kek','zold');

 

Az eredménylista:

 

RSZ

-------

cmt-111

lui-999

kjs-234

 

Az IN operátor után egy halmazt adhatunk meg a zárójelek közt az elemek felsorolásával.

 

- Listázzuk ki azokat a sorokat, ahol a CIM mező üres.

 

SELECT * FROM ember WHERE cim IS NULL;

 

Az eredménylista most üres lesz:

 

ID NEV SZULEV CIM

-------------------------------------------------

 

- Listázzuk ki a "G" betűvel kezdődő emberek adatait !

 

SELECT * FROM ember WHERE nev LIKE 'G%';

 

Az eredménylista:

 

ID NEV SZULEV CIM

---------------------------------------------

2 Geza 1979 Miskolc

 

Ebben az esetben a %-jel minden további karaktert lefed (JOKER-karakter). Most irjuk ki azokat az embereket, amelyek nevének első. karaktere bármi lehet, a név többi része pedig "eza" -val egyenlő:

 

SELECT * FROM ember WHERE nev LIKE '_abor%';

 

Az eredménylista:

 

ID NEV SZULEV CIM

---------------------------------------------

2 Geza 1979 Miskolc

 

Ebben a példában az "_" (aláhúzás) jel egy karakter helyen töltött be JOKER szerepet. Most listazzuk ki azokat a neveket, amiben szerepel az "ez" sztring:

 

SELECT * FROM ember WHERE nev LIKE '%ez%';

 

Az eredménylista:

 

ID NEV SZULEV CIM

---------------------------------------------

2 Geza 1979 Miskolc

 

 

- Hozzunk létre egy ideiglenes mezőt az életkorra, ha most 1997-et írunk:

 

SELECT nev,1996-szulev FROM ember;

 

Az eredménylista:

 

NEV 1996-SZULEV

-----------------------------------

Bela 21

Geza 17

Feri 22

 

- Most irassuk ki az autók tipusait és árait dollárban megadva (az aktuális átváltási arány: 1USD=190Ft):

 

SELECT tip, ar/190 FROM auto;

 

Az eredménylista:

 

TIP AR/150

--------------------------------

Opel 6666.66667

Golf 2333.33333

Trabant 666.666667

Opel 3000

Lada 1833.33333

 

Az elôbb már említettük, hogy az SQL fizikai tároláshoz, a gyakorlati élethez közelebb állóan valósítja meg a táblázatok kezelését, mint az elméleti relációs algebra. Ennek következô példája, amikor egy olyan opciót mutatunk be, mely nem szerepel az eredeti modellben, mégpedig a rekordok rendezését. Az elméleti modell ugyanis halmazorientált, melyben nem értelmeztük a rendezettséget. A gyakorlatban viszont adatainkat nem halmazokban, sokkal inkább listákban tároljuk, ahol rendszerint valamilyen rendezettséget valósítunk meg, pl. a hallgatók neveit ABC sorrendben tároljuk. A rendezettség gyakorlati jelentôségének tükrében az SQL is lehetôvé teszi az eredményrekordok megadott sorrendbe történô rendezését. A rendezés opciójának kulcsszava az ORDER BY és használata a következô:

 

SELECT ... FROM ... [WHERE ...]

ORDER BY k1 [ASC|DESC][,k2 [ASC|DESC]...,ki[ASC|DESC]];

 

A kifejezésben a ki egy kifejezést takar, melyet valamely mezôbôl vagy mezôkbôl kapunk a már ismertetett operátorok segítségével. A legegyszerűbb esetben a ki egy mezônévvel egyezik meg. A kifejezés mögött álló ASC illetve DESC a rendezettség irányára utal. Az ASC jelenti a növekvô sorrendet, míg a DESC a csökkenô érték szerinti sorrendet jelöli ki. Az alapértelmezés a növekvô sorrend, amely akkor érvényesül, ha nem használjuk sem az ASC sem a DESC kulcsszavakat.

A rendezéshez több kifejezést is felhasználhatunk. Az elsônek megadott kifejezés lesz az elsôdleges rendezési elv. Azaz a k1 értéke dönt elsôként a sorrend megállapításánál. Ha viszont két rekordnál a k1 értéke azonos lenne, akkor kap szerepet a következô, k2 kifejezés. Ebben az esetben ezen, másodlagos kifejezés dönti el a sorrendet. Ha k2 értéke is azonos lenne akkor a k3 kifejezés kerül sorra a sorrend meghatározásához, illetve hasonlóan lehet továbbmenni az i. Helyen megadott kifejezések értelmezésében. Ha például a a nem piros színű autók adatait kivánjuk kilistázni szín szerint csökkenő listában és azonos szín esetén típus szerint növekvôen, akkor az alábbi utasítást kell kiadni:

 

SELECT * FROM auto WHERE szin<>'piros' ORDER BY szin DESC, tip;

 

Az eredménylista:

 

TUL RSZ TIP SZIN EVJ AR

----------------------------------------------------------------------

1 kjs-234 Lada kek 1989 275000

3 lui-999 Opel kek 1991 450000

2 aaa-156 Trabant feher 1985 100000

1 bkx-720 Opel bordo 1991 1000000

 

Az ORDER BY opció a halmaz orientált szemlélettôl való eltérés következtében csak korlátozottan használható, ugyanis csak a végsô, kiírt eredménytáblázatra vonatkozhat.

 

 

A gyakorlatban is nagy jelentôsége van a csoportképzés műveletének is. A csoportképzés annyit jelentett, hogy a táblázat rekordjaiból csoportokat képeztünk, ahol egy csoportba a valamilyen szempont szerint összeillô rekordok tartoznak, s az eredménytáblázatban egy rekord egy csoportnak fog megfelelni, azaz egy rekord egy csoport adatait írja le. Mivel az eredménytáblázat is kielégíti az 1NF feltételt, ezért minden mezônek egyértékűnek kell lennie, azaz olyan értékeket tartalmazhat csak, mely a csoportra nézve egyértelmű.

A csoportokra az SQL rendelkezésre bocsát olyan operátorokat, melyek a csoportból ilyen egyértékű mezôt hoznak létre az eredménytáblázatban. Az alábbi operátorok állnak rendelkezésre, melyek formailag függvényszerűen használhatók:

 

MIN(k) : minimumérték

MAX(k) : maximumérték

AVG(k) : átlagérték

SUM(k) : összeg

COUNT(k): darabszám

 

Az operátoroknál szereplô k szimbólum egy tetszôleges, elôzôekben már értelmezett kifejezés lehet. Ekkor a kifejezés kiértékelôdik a csoport minden rekordjára, s visszakapjuk az értékek minimumát, maximumát, átlagát, összegét és darabszámát. Itt a darabszám azon rekordok darabszáma, melyeknél a kifejezés nem ad üres értéket. Ha magára a csoportban elhelyezkedô rekordok darabszámára vagyunk kíváncsiak, akkor a

 

COUNT(*)

 

kifejezést használják. A csoportok képzése a GROUP BY opcióval történik, melyben meg kell adni egy kifejezést is. Azon rekordok kerülnek egy csoportba, melyekre a kifejezés azonos értéket vesz fel. A csoportképzés általános alakja:

 

SELECT ... FROM ... [WHERE ...] GROUP BY k [ORDER BY ..];

 

A csoportképzésnél arra kell elsôsorban figyelni, hogy csak olyan mezôk szerepelhetnek az eredménytáblázatban, melyek minden csoportra nézve egyértékűek. Példaként a csoportképzésre vegyük az esetet, amikor az egyes autótípusok átlagárait szeretnénk megkapni. Ehhez ugyanis elôbb csoportokba bontjuk az autókat a típus szerint, majd minden csoportra képezzük az ar mezô átlagát és az eredménytáblázatba a típus és az átlagár mezôket vesszük be, melyek minden csoportra egyértékűek lesznek. Az utasítás alakja:

 

SELECT tip, AVG(ar) FROM auto GROUP BY tip;

 

A csoportképzés esetén felléphet olyan igény is, hogy a felhasználó nem minden csoportra kíváncsi, hanem csak egyes, bizonyos feltételeknek eleget tévô csoportok adatait kéri le. Ezzel úgymond szeretnénk a kapott csoport táblázat rekordjai között szelektálni. Erre a már megismert WHERE szelekciós opció viszont nem alkalmas, mert az az alaptáblázat rekordjain értelmezett szelekcióra vonatkozik, tehát azt jelöli ki, hogy mely rekordokból képezzük a csoportokat, s nem azt szabályozza, hogy mely csoportok kerüljenek be az eredmény táblázatba. Ezért egy újabb opcióra lesz szükség, mely a csoportok szelekciójára vonatkozik. Ezen opció kulcsszava a HAVING, melyet egy feltétel követ. Az opció hatására azon csoportok kerülnek be az eredménytáblázatba, melyek kielégítik a HAVING után megadott feltételt. Az opció általános alakja:

 

SELECT ... FROM ... [WHERE ...] GROUP BY ... HAVING feltétel [ORDER BY ..];

A feltételben az eredménytáblázatban szereplô mezôk szerepelhetnek, azaz olyan kifejezést adhatunk meg, amely minden csoportra egyértelműen kiértékelhetô. Példaként vehetjük azt az esetet, amikor csak a 555555 Ft alatti átlagárral rendelkezô típusokat listázzuk ki, melyhez a következô SQL parancsot kell kiadni:

SELECT tip, AVG(ar) FROM auto GROUP BY tip HAVING AVG(ar) < 555555;

 

A csoportképzés gyakorlására vegyük az alábbi példa utasításokat.

 

- Irassuk ki az autók átlagévjáratát:

 

SELECT AVG(evj) FROM auto;

 

Az eredmény:

 

AVG(EVJ)

-------------

1987.4

 

- Irassuk ki a minimum évjáratot (a legidősebb autót):

 

SELECT MIN(evj) FROM auto;

 

Az eredmeny:

 

MIN(EVJ)

----------

1981

 

- Irassuk ki az autók összértékét:

 

SELECT SUM(ar) FROM auto;

 

Az eredmény:

 

SUM(AR)

----------

2175000

 

- Irassuk ki azt, hogy milyen autótipusok léteznek, és azt, hogy hány darab van beloluk:

 

SELECT tip,COUNT(tip) FROM auto GROUP BY tip;

 

Az eredménylista:

 

TIP COUNT(TIP)

----------------------------------

Golf 1

Lada 1

Opel 2

Trabant 1

 

A COUNT aggregációs függvény a nemüres mezők darabszámát adja vissza. Ha a rekordok szamára vagyunk kiváncsiak, akkor a COUNT(*) kifejezést hasznaljuk.

 

- Irassuk ki az autók tipusait, és a tipusok átlagárait:

 

SELECT tip, AVG(ar) FROM auto GROUP BY tip;

 

Az eredménylista:

 

TIP AVG(AR)

------------------------------

Golf 350000

Lada 275000

Opel 725000

Trabant 100000

 

- Irassuk ki azon autók tipusait és átlagéletkorát, amelyek átlagéletkora 1986 utani:

 

SELECT tip,AVG(evj) FROM auto GROUP BY tip HAVING AVG(evj)>1986;

 

Az eredménylista:

 

TIP AVG(EVJ)

-----------------------------

Lada 1989

Opel 1991

 

- Mely kék szinű autótipusok átlagévjárata fiatalabb 1990-nél:

 

SELECT tip FROM auto WHERE szin='kek' GROUP BY tip HAVING AVG(evj)>1990;

 

Az eredménylista:

 

TIP

----------

Opel

 

- Irassuk ki az AUTO-táblában szereplő összes autotipust, a tipushoz tartozó darabszám szerint rendezve.

 

SELECT tip FROM auto GROUP BY tip ORDER BY COUNT(*);

 

Az eredménylista:

 

TIP

----------

Golf

Lada

Trabant

Opel

 

A relációs algebra és az SQL egyik leggyakrabban használt műveletei közé mindenképpen be kell venni az egyesítés, a join műveletét is. E művelet fontosságának egyik legfôbb oka, hogy az adatbázis tervezése során, a normalizálással az információkat szétbontjuk táblázatokra, azaz egy összetettebb lekérdezéshez szükséges információk több táblázatban szétszórva helyezkednek el, így a lekérdezés során össze kell gyűjteni ezen adatokat a különbözô táblázatokból, ahol az összetartozás bizonyos mezôk érékeinek kapsolatán alapszik. Azt a folyamatot, amikor több táblázatból származó adatokból állítunk elô egy újabb eredménytáblázatot, egyesítésnek vagy join-nak nevezzük.

Az SQL-ben két táblázat egyesítésének legegyszerűbb formája, amikor a két táblázat Descartes szorzatát képezzük, mely során az eredménytáblázat egy rekordja úgy áll elô, hogy az egyik táblázat rekordjához hozzáfűzzük a másik táblázat egy rekordját, ahol az eredménytáblázat minden lehetséges párosítást tartalmaz. Ha tehát az egyik, A-val jelölt táblázat az M1, M2, M3 mezôket tartalmazza és a táblázat elôfordulás 3 rekordból áll, míg a másik, B-vel jelölt táblázat az N1, N2 mezôket tartalmazza és az elôfordulása 4 rekordból épül fel, akkor az eredménytáblázat az M1, M2, M3, N1, N2 mezôket fogja tartalmazni, és az eredménytáblázat elôfordulása 12 rekordot foglal magába, hiszen ennyi lehetséges párosítása lehet az A beli és a B beli rekordoknak.

 

A

 

 

 

B

 

 

A*B

 

 

 

 

M1

M2

M3

 

N1

N2

 

M1

M2

M3

N1

N2

1

2

3

 

7

8

 

1

2

3

7

8

4

5

6

 

9

10

 

1

2

3

9

10

 

 

 

 

11

12

 

1

2

3

11

12

 

 

 

 

 

 

 

4

5

6

7

8

 

 

 

 

 

 

 

4

5

6

9

10

 

 

 

 

 

 

 

4

5

6

11

12

 

Két táblázat Descartes szorzatának elôállításához a következô SQL utasítást kell kiadni:

 

SELECT * FROM táblázatnév1, táblázatnév2;

 

A példában megadott lekérdezéshez az alábbi utasítás tartozik:

 

SELECT * FROM A, B;

 

Az így elôálló egyesítéstáblázatot ezután tetszôlegesen tovább lehet alakítani a már megismert opciókkal. Erre rendszerint szükség is van, hiszen csak nagyon ritkán van szükség két táblázat rekordjainak teljes Descartes szorzatára, legtöbbször csak a Descartes szorzat bizonyos részhalmazára van szükségünk. A szorzat táblázat szelekciójával valósítható meg például a táblázatok összekapcsolására szolgáló kulcs és kapcsolókulcs szerkezet alapján elôálló rekordpárok kijelzése. A bemutatandó példához vegyünk egy másik táblázatot is, melyet a következô utasítással hozunk létre:

 

CREATE TABLE ember (kod NUMBER(4), nev CHAR(30), cim CHAR(30));

 

Ekkor egy tulajdonosi kapcsolat áll fenn az ember és az auto egyedek között, melyet a kód-tulaj kulcs-kapcsolókulcs szerkezettel tartunk nyilván. A fenti két táblázat felhasználásával az autók rendszámainak és a tulajdonosaik nevének, címének kiíratása a következô paranccsal állítható elô:

 

SELECT rsz, nev, cim FROM auto, ember WHERE tulaj = kod;

 

Az eredménytáblázatot tehát úgy kapjuk meg, hogy elôbb képezzük az összes lehetséges auto és ember rekordpárosítást, majd ebbôl kiválasztjuk azokat, melyekben az auto rekordrész tulajdonos mezôkódja megegyezik az ember rekordrész azonosító kódszámával, hiszen a kapcsolat e két mezô értékazonosságán keresztül valósul meg. A példánkban az RDBMS egyértelműen meg tudja határozni, hogy milyen információkat kell kiemelni, hiszen minden hivatkozási név is egyértelmű, hiszen nincsenek azonos nevű mezôk a két táblázatban. Viszont más példákat véve, elôfordulhat, hogy mindkét táblázatban szerepel ugyanolyan nevű mezô. Ebben az esetben már nem egyértelmű, hogy mely mezôre is gondolunk az SQL parancsban. Ekkor pontosítani kell a megjelölést, nem elegendô csupán a mezônév megadása, a táblázat nevét is megadjuk, s ezen együttes hivatkozás formája a következô:

 

táblázatnév.mezônév

 

Ez alapján az elôzô példa a következô alakot öltené:

 

SELECT auto.rsz, ember.nev, ember.cim FROM auto, ember WHERE auto.tulaj = ember.kod;

 

A pontosított hivatkozás ugyan mindig egyértelmű, viszont a felhasználónak hosszabb parancsokat kell kiadni, hiszen mindannyiszor le kell írni a táblázat nevét is, valahányszor a mezôre hivatkozunk. A tömörség végett az SQL lehetôséget egy rövidebb írásmódra is, melyben minden táblázatnévhez egy egyértelmű rövidítést rendelhetünk, s a mezônevekben elegendô csak ezen rövidített névre hivatkozni. A rövidítést a FROM opcióban adjuk meg a teljes táblázatnév után, alakja:

 

táblázatnév rövidítés

 

Ismét az elôzô példánál maradva, az utasítás a következô formában írható le:

 

SELECT a.rsz, e.nev, e.cim FROM auto a, ember e WHERE a.tulaj = e.kod;

 

Az SQL utasításokban viszonylag szabadon használhatjuk az elôbb említett mezôhivatkozásokat, tetszôlegesen választhatunk a megadott lehetôségek között, csak akkor kell a legpontosabb, táblázatnevet is tartalmazó megadást választani, ha a mezônév önmagában nem elegendô, azaz több táblázatban is elôfordul ugyanaz a mezônév.

 

A join műveletének szemléltetésére néhány példát mutatunk be.

 

- Irassuk ki az összetartozó auto-ember párosokat.

 

SELECT a.rsz, e.nev FROM auto a,ember e WHERE a.tul = e.id;

 

Az eredménylista:

 

RSZ NEV

-------------------------

bkx-720 Bela

cmt-111 Bela

kjs-234 Bela

aaa-156 Geza

lui-999 Feri

 

Irassuk ki az 5 évesnél idősebb autók rendszámait és a tulajdonos nevét:

 

SELECT a.rsz,e.nev FROM auto a,ember e WHERE a.evj < 1991 AND a.tul = e.id;

 

Az eredménylista:

 

RSZ NEV

--------------------------

cmt-111 Bela

kjs-234 Bela

aaa-156 Geza

 

- Irassuk ki az emberek neveit autóik darabszámával együtt:

 

SELECT e.nev,COUNT(*) FROM ember e,auto a WHERE a.tul = e.id GROUP BY e.nev;

 

Az eredménylista:

 

NEV COUNT(*)

-----------------------------

Bela 3

Feri 1

Geza 1

 

- Irassuk ki azon emberek nevet, akiknek 1-nél több autójuk van:

 

SELECT e.nev FROM ember e, auto a WHERE a.tul = e.id GROUP BY e.nev HAVING COUNT(*)>1;

Az eredménylista:

 

NEV

-----------

Bela

 

 

A SQL query komponensének, a SELECT utasításnak már az eddigiekben is számos olyan opcióját láttuk, mely kellô rugalmasságot adott a lekérdezések megfogalmazásában. Maradtak viszont olyan lekérdezések, melyek az eddigi opciókkal nem oldhatók meg, például tekinthetjük azt a kérést, hogy listázzuk ki azon emberek neveit, akinek nincs autója. Ehhez sem a sima szelekció, sem a két tábla egyesítése nem elegendô, hiszen az ott szereplô feltételek rekordszinten gondolkodnak, a mi esetünkben pedig arra lenne szükség, hogy csak azon rekordok kerüljenek bele az eredménytáblázatba az ember táblázatból, melyeknél a kód mezô értéke nem szerepel az auto táblázatnál egyetlen egy rekord esetén sem a tulaj mezôben. Itt tehát a feltételben egy egész táblázatra vonatkozó hivatkozás szerepel. A példában a megoldás az lenne, hogyha elôbb lekérdeznénk az összes tulaj mezôértéket az auto táblázatban, s utána ezen listával hasonlítanánk össze az emberek kód mezôjének értékeit. Vagyis a szelekciós feltételben egy másik lekérdezés eredményére hivatkozhatnánk.

Az SQL szerencsére támogatja az ilyen jellegű szelekciós feltétel megadást is. Ezt az opciót nevezik subquery-nek, ami tehát annyit jelent, hogy az egyik lekérdezés szelekciós feltételében hivatkozunk egy másik lekérdezés eredményére, amit allekérdezésnek (subquery) is neveznek. Az allekérdezést mindig zárójelben kell megadni, hogy elemei elkülönüljenek, elválaszthatók legyenek a fô lekérdezés opcióitól. Az allekérdezés formailag megegyezik a normál SELECT utasítás alakjával, azzal a különbséggel, hogy az allekérdezésben nem használható a rendezett kiírást elôíró ORDER BY opció, tehát az allekérdezés eredményét rendezettség nélküli halmaznak kell tekinteni.

 

Az allekérdezést az eredményétôl függôen más és más típusú operátorokhoz kapcsolhatjuk. Ha az eredmény egyetlen egy érték, akkor a skalárokhoz kötôdô operátorokat használhatjuk, mint pl. a relációs operátorokat. Ha viszont a lekérdezés eredménye több rekordot is tartalmaz, akkor csak a halmazokat kezelô operátorok jöhetnek szóba. Eddig egy ilyen operátort vettünk, az IN operátort. Emellett az allekérdezéseknél használható egy másik ilyen jellegű operátor is, mely azt vizsgálja meg, hogy az allekérdezés eredménytáblázata üres-e vagy sem. Ezen operátor alakja:

 

EXISTS (allekérdezés)

 

Ha az allekérdezés eredménytáblázata nem üres, akkor a fenti kifejezés igaz értéket ad vissza, ha pedig üres az eredménytáblázat, akkor pedig hamis értéket szolgáltat. A EXISTS operátor tagadásának alakja:

 

NOT EXISTS (allekérdezés)

 

A példaként említett lekérdezés, tehát amikor azon emberek neveit kérjük le, akiknek nincs autója, az alábbi SQL utasítás alakban fogalmazható meg:

 

SELECT e.nev FROM ember e WHERE NOT EXISTS (SELECT * FROM auto a WHERE a.tulaj=e.kod);

 

Az eredménytáblát más módon is megkaphatjuk, amikor elôbb elôállítjuk az autóval rendelkezô személyek kódjainak listáját:

 

SELECT e.nev FROM ember e WHERE e.kod NOT IN (SELECT a.tulaj FROM auto a);

 

A példában bemutatott kétfajta megközelítés legalapvetôbb különbsége, hogy a második esetben a szelekciós feltétel minden rekordnál ugyanarra a allekérdezésre vonatkozik, míg az elsô esetben minden allekérdezés más és más eredményt szolgáltathat. Emiatt egy jó optimalizáló esetén a második esetben csak egyszer kell az allekérdezést végrehajtani, míg a másik esetben többször is lefut az allekérdezés.

 

Az SQL lehetőséget ad minden és létezik logikai kvantorok megvalósítására az allekérdezések esetében. A normál, a skalár értékek összehasonlítására alkalamzott reláció operátorokat ki lehet terjeszteni a több értéket visszaadó szelekciókra is. A létezik operátora az ANY, a minden kvantor operátora pedig az ALL. Ezen operátorokat a halmazt visszaadó lekérdezés előtt, de az alkalmazott skalár relációs operátor után kell szerepeltetni az SQL utasításon belül. A

 

kif reláció_operátor ANY (halmaz)

 

akkor igaz értékű, ha a halmaznak legalább egy elemére igaz értékű a

 

kif reláció_operátor halmezelem

 

kifejezés. A

 

kif reláció_operátor ALL (halmaz)

 

pedig akkor ad igaz értéket, ha a halmaznak minden elemére teljesül a

 

kif reláció_operátor halmezelem

 

kifejezés.

 

Az al-szelekciók bemutatására vegyük át a következő mintapéldákat.

- Irassuk ki a kék szinű autók tulajdonosainak a neveit és címeit (vagyis irassuk ki azon emberek adatait, akiknek azonositószáma benne van a kék autók tulajdonosainak a kódjai halmazában):

 

SELECT nev, cim FROM ember WHERE id IN (SELECT tul FROM auto WHERE szin = 'kek');

 

Az eredménylista:

 

NEV CIM

-----------------------------

Bela Budapest

Feri Pecs

 

- Irassuk ki azon embereket, akiknek nincs autója.

 

SELECT nev,cim FROM ember WHERE id NOT IN (SELECT tul FROM auto);

 

Az eredménylista üres lesz:

 

NEV CIM

-----------------------------

 

- Irassuk ki azokat az autókat, amelyek ára az átlagár alatt van:

 

SELECT * FROM auto WHERE ar < (SELECT AVG(ar) FROM auto);

 

Az eredménylista:

 

TUL RSZ TIP SZIN EVJ AR

-----------------------------------------------------------------------

1 cmt-111 Golf piros 1981 350000

2 aaa-156 Trabant feher 1985 100000

1 kjs-234 Lada kek 1989 275000

 

Irassuk ki azokat az autókat, melyek ára nagyobb valamely másik autó áránál:

 

SELECT * FROM auto a WHERE a.ar > ANY (SELECT b.ar FROM auto b);

 

Az eredménylista:

 

TUL RSZ TIP SZIN EVJ AR

----------------------------------------------------------------------

1 bkx-720 Opel bordo 1991 1000000

1 cmt-111 Golf piros 1981 350000

3 lui-999 Opel kek 1991 450000

1 kjs-234 Lada kek 1989 275000

 

 

Az SQL86 tartalmaz még egy, ritkábban használt lehetôséget is, a táblázatok uniójának a műveletét. Két azonos felépítésű táblázat fűzhetô egymáshoz az alábbi SQL utasítással:

 

SELECT ... UNION SELECT ...;

 

Az eddigiekben bemutatott opciók, lehetôségek bizonyítják, hogy milyen rugalmas felhasználható az SQL SELECT utasítása, s a kellô kreativitással szinte minden lehetséges lekérdezési igény kielégítô. Az SQL prescriptív jellege miatt tudnunk kell az elvégzendô műveleteket, mielôtt kiadnánk a megfelelô SQL utasítást. Összetettebb esetekben több különbözô úton is el lehet jutni az eredménytáblázatokhoz, melyek esetleg a végrehajtás hatékonyságában különbözhetnek egymástól.

 

Az SQL86 DCL utasításai

 

Az SQL komponenseibôl már csak egy maradt hátra, a DCL rész. Az SQL86 ezen a területen a felhasználók jogosultságainak nyilvántartási parancsaira korlátozódik. A adatbáziskezelés egyik alapfeltevése ugyanis, hogy az adatbázist több felhasználó is használhatja, mindenki letárolhatja benne a saját adatait. Mivel az adatbázis több különbözô felhasználó információit együttesen tárolja, az RDBMS-nek kell gondoskodnia arról, hogy mindenki csak a jogosult műveleteket végezhesse el, csak a jogosult adatokhoz férhessen hozzá. Az SQL rendszerekben, hasonlóan az operációs rendszerekhez minden felhasználónak van egy azonosító neve és egy jelszava. Minden felhasználói névhez majd egy jogosultsági kör rendelhetô. A SQL rendszerekben minden objektumnak, melyekbôl mi most a táblázatokat vettük, van egy tulajdonosa, mégpedig az a felhasználó, aki létrehozta. A táblázatokon elvégezhetô műveleteket jogosultság ellenôrzése szempontjából az alábbi csoportokba bontjuk:

 

SELECT : táblázat lekérdezése

INSERT : táblázat bôvítése

DELETE : táblázat rekordjainak törlése

UPDATE(m1 [,m2...,mi]) : táblázat rekordjainak módosítása

 

Ekkor táblázatokként megadhatjuk, hogy az egyes felhasználóknak milyen műveleteket engedélyezünk, ahol a fenti négy műveleteket lehet engedélyezni. Az UPDATE műveletnél az engedély csak a felsorolt mezônevekre vonatkozik, illetve ha nincs megadva mezônév, akkor bármely mezô módosítható. Alapesetben csak a tulajdonosnak van joga a műveletek végrehajtására, s csak a tulajdonos adhat át jogokat a táblázatra vonatkozólag.. Az operációs rendszerhez hasonlóan itt is léteznek privilégiumok, melyek lehetôvé teszik, hogy bizonyos kitüntetett felhasználók, mint pl. a rendszergazdák, az explicit jogosultság nélkül is elérhetnek bármely adatot az adatbázisban.

Az jogosultságok megadása a GRANT utasítással történik, melyben meg kell adni, hogy mely táblázatra, mely műveleteket és kinek engedélyezünk. Az utasítás alakja:

 

GRANT művelet ON táblázatnév TO felhasználó [WITH GRANT OPTION];

 

Az utasításban a művelet alatt egyrészt az elôbb említett négy műveletet értjük, mely kibôvül egy új kulcsszóval az

 

ALL

 

kulcsszóval, mely azt jelenti, hogy mind a négy műveletet engedélyezzük a megadott felhasználónak. Ha az opcionális WITH GRANT OPTION kifejezés is megadjuk, akkor a megadott felhasználó jogosulttá válik arra, hogy a most átadott jogokat továbbadhassa más felhasználóknak is. Azaz ezután már bárki megszerezheti a kiadott jogokat. A gyakorlat során elôfordulhatnak olyan esetek, amikor a megadott jogokat vissza kell vonni valamilyen okok miatt, pl. az illetô beosztása megváltozik, s ekkor a korábban kiadott jogok már nem alkalmazhatók.

A kiadott jogosultságok megszüntetése a REVOKE utasítással lehetséges, melyben meg kell adni a felhasználót, a visszavont műveletek és a vonatkozó táblázatot is. Az utasítás alakja:

 

REVOKE művelet ON táblázatnév FROM felhasználó;

 

Az utasítás hatására a kijelölt felhasználó többé már nem végezheti el a megadott műveletek a megadott táblázaton. Az a felhasználó vonhatja vissza a jogosultságokat, akinek van joga adományozni is, azaz a tulajdonosnak.

 

Példaként vegyük a autó táblában való olvasási jog engedélyezését a gi476 azonosítójú felhasználónak. Itt az azonosítás az RDBMS-en belüli azonosítást jelent, ami sok esetben lényegesen különbözhet az operációs rendszerben alkalmazott azonisítótól:

 

GRANT SELECT ON auto TO gi476;

 

Vonjuk vissza minden jogot az ember táblára vonatkozólag ugyanettől a személytől:

 

REVOKE ALL FROM gi476;

 

A vezérlő utasításokhoz szokás sorolni a műveletvégrehajtást szabályozó, úgynevezett tarnzakció kezelő utasításokat is. Az SQL szabványban két, a tranzakció végét ejlző utasítást szokás definiálni. Az egyik utasítás a tranzakció siekres befejezését jelenti, míg a másik a korábban végrehajtott tevékenységek visszagörgetését, azaz megsemmísítését írja elő. A tranzakció sikeres befejezeésének utasítása:

 

COMMIT;

 

Ennek hatására a korábban végrehajtott tevékenységek véglegesítődnek, megőrződnek az adatbázisban. Ezzel szemeben a

 

ROLLBACK;

 

hatására a korábbi tevékenységek érvényteleődnek, mintha ki sem adtuk volna őket. Ezzel hibával félbeszakadt műveletsort lehet töröltetni.

 

Az SQL86 lezárásaként pillantsuk még vissza egy fontos utasításra, a VIEW-k létrehozására. Most már ismert az megadott műeletsor pontos megadásának szintaktikája is. Az elkészült nézeti tábla a későbbi lekérdezési műveletekben ugyanúgy használható mint az alaptáblák, így például összekapcsolható más táblákkal is.

 

Gyakorlásként nézzünk még néhány mintautasítást a VIEW-k használatának gyakorlására.

 

- Hozzunk létre embaut néven olyan VIEW-t, mely az autók és tulajdonosuk adatait tartalmazza rekordonként. A VIEW az auto rendszámát, tipusát és a tulajdonos nevét tartalmazza:

 

CREATE VIEW embaut AS SELECT a.rsz, a.tip, e.nev FROM auto a, ember e WHERE a.tul = e.id;

 

- Kérdezzük le az embaut nézeti tábla tartalmá:

 

SELECT * FROM embaut;

 

Az eredménylista:

 

RSZ TIP NEV

bkx-720 Opel Bela

cmt-111 Golf Bela

aaa-156 Trabant Geza

lui-999 Opel Feri

kjs-234 Lada Bela

 

- Kérdezzük le az O betűvel kezdődő tipusú autók tulajdonosainak neveit a nézeti táblából:

 

SELECT nev FROM embout WHERE tip LIKE ‘O%’;

 

Az eredménylista

 

NEV

Bela

Feri

 

- Szüntessük meg az elkészült nézeti táblát:

 

DROP VIEW embaut;

 

 

Ezzel át is vettük az SQL86 szabványt, mely mint említettük a létező RDBMS kezelő felületek magját fogja csak át. A későbbiekben áttekintjük az SQL92 szabványt is, mely már átöleli és bizonyos esetekben túl is mutat a mai RDBMS rendszerek lehetőségein. Az SQL92 ismeretésénél bővebben ki fogunk térni az Oracle rendszer SQL kezelő felületének a bemutatására is.