kategória | ||||||||||
|
||||||||||
|
||
Az SQL (Structured Query Language) alapvetően lekérdező nyelv, vagyis az adatbázisban tárolt információk visszanyerésére szolgál, de rendelkezik néhány adatbeviteli és módosítási lehetőséggel is. Nem algoritmikus nyelv, így nem tartalmaz vezérlőszerkezeteket sem (elágazás, ciklus). Az SQL nyelvet sokszor valamilyen más programnyelvbe ágyazva alkalmazzák (embedded SQL). Számos adatbáziskezelő rendszer, így az ORACLE is az SQL nyelvet használja az adatok elérésére és módosítására.
Az utasítások formája egyszerű, a parancsok angol mondatok formájában fogalmazhatók meg. Egyaránt használják a felhasználók, fejlesztők és az adatbázis-adminisztrátorok feladataik végrehajtásához.
A következőkben az SQL nyelv alapjaival ismerkedünk meg.
Jelölések
A parancsok ismertetésénél a kulcsszavakat nagybetűvel írjuk. Ezeket betűről betűre pontosan le kell írni. A számítógépen tetszőlegesen írhatjuk őket kis-, vagy nagybetűkkel.
A csúcsos zárójelek < > közötti szöveg szimbólum. Ide változóneveket, vagy állandókat lehet írni.
A szögletes zárójelek [ ] közötti paraméterek megadása nem kötelező.
A | jel egymást helyettesítő szimbólumok ,vagy kulcsszavak elválasztására szolgál.
A kapcsos zárójel azt jelenti, hogy a kifejezés tetszés szerint ismételhető.
A csúcsos, szögletes és kapcsos zárójeleket a program szövegébe nem szabad beírni. Minden egyéb jelölt vesszőt, pontot és kerek zárójelet kötelezően le kell írni.
Az utasításokban tetszőlegesen elhelyezhetünk szóközöket, egy utasítást több sorban is leírhatunk. Az utasítást pontosvesszővel kell lezárni.
Az adatbázisok, táblák, mezők és más objektumok neveinek betűvel kell kezdődnie, betűket, számokat, az aláhúzásjelet és a $, vagy a # jelet tartalmazhatják. Az adatbázis-név legfeljebb 8 karakter hosszú lehet, a többi név pedig legfeljebb 30 karakterből állhat.
A táblák, vagy nézettáblák nevét minősíthetjük a tulajdonosa nevével, megkülönböztetve ezzel más felhasználó ugyanilyen nevű táblájától. Ennek jelölése: <tulajdonosnév>.<táblanév> .
A legfontosabb adattípusok
CHAR karakteres adatok tárolására szolgál, az adatok maximális hossza 255
karakter.
LONG változó hosszúságú szövegek tárolására szolgál, a szöveg mérete
2 Gbyte-ig terjedhet.
NUMBER numerikus adatok tárolására szolgál, a számokat maximum 38 jegy
pontosságig tudja ábrázolni. Az adatok lehetnek fixpontosak, vagy lebegőpontosak. A legkisebb ábrázolható szám a 10-129, a legnagyobb pedig 9.99*10124. Más adatbáziskezelő rendszerekkel való kompatibilitás miatt az SQL nyelv megenged más numerikus adattípusok deklarálását is (pl. INTEGER, FLOAT, DECIMAL, REAL) de ezek belső ábrázolása megfelel a NUMBER adattípusnak.
A numerikus adatok méretének definiálásánál az összes jegyek számát és a tizedesjegyek számát kell megadni vesszővel elválasztva.
DATE dátum- és időadatok tárolására szolgál.
Egy dátum típusú adat a következőket tartalmazza:
évszázad, év, hónap, nap, óra, perc, másodperc
A dátumrész szokásos formája NN-HHH-ÉÉ, például 12-NOV-93.
A rendszerdátumot és -időt a SYSDATE függvény segítségével kaphatjuk meg.
Egy dátum típusú adatnak leggyakrabban a dátum részét szoktuk használni, az évszázad nélkül. A további dátum és időinformációkat a dátum típusú adatból függvények segítségével tudjuk megjeleníteni.
Konstansok
Az SQL nyelvben karakteres, numerikus, logikai és dátum típusú konstansok használhatók. A karakteres és a dátum típusú konstansokat aposztrófok közé kell tenni.
Példák
'BUDAPEST'
12E2 (=1200)
9.85E-3 (=0.00985)
356e23
'09-MAR-92'
A NULL érték
Egy sorban egy adott oszlop értéke NULL, ha az adott oszlop értéke ismeretlen, vagy lényegtelen. A NULL érték nem azonos a nullával. A NULL értékkel műveletek is végezhetők, például 10*NULL=NULL.
Változók
Az SQL nyelvben a változók a konstans értékek tárolására szolgálnak.
Adatkonverzió
A kifejezések különböző adattípusokat tartalmazhatnak. Kiértékelésükhöz esetenként adatkonverzióra lehet szükség. Az ORACLE adatbáziskezelő a következő esetekben automatikusan elvégzi a konverziót:
- konstans konvertálása egy oszlop adattípusára
- egy oszlop értékének konvertálása egy konstans adattípusára
- egy oszlop értékének konvertálása egy másik oszlop adattípusára
Egyéb esetekben konverziós függvények segítségével lehet az adatkonverziót elvégezni.
Műveletek
Matematikai műveletek
összeadás
kivonás
szorzás
osztás
Karakteres műveletek
láncolás
Példa
A 'FEHÉR' || 'VÁR' eredménye 'FEHÉRVÁR' .
Relációs műveletek
egyenlő
!=,^=,<> nem egyenlő
>= nagyobb, vagy egyenlő
<= kisebb, vagy egyenlő
[NOT] IN tartalmazás (listában)
[NOT] BETWEEN x AND y igaz, ha a vizsgált érték nagyobb, vagy
egyenlő x-nél és kisebb, vagy egyenlő
y-nál.
IS [NOT] NULL igaz, ha a vizsgált érték NULL
[NOT] LIKE karaktersorozat mintával történő
összehasonlítása
Ha a NOT kulcsszót is megadjuk, akkor a művelet eredménye az eredeti művelet eredményének ellentéte lesz.
A LIKE operátor segítségével egy szöveget lehet összehasonlítani egy mintával. Ha pontos egyezést akarunk vizsgálni, akkor az = operátort kell alkalmazni. A LIKE operátornál a mintában helyettesítő karaktereket alkalmazhatunk. A % karakter tetszőleges számú karaktert helyettesíthet, az _ (aláhúzás) karakter pedig pontosan egyet. A vizsgálandó szöveget a LIKE előtt, a mintát a LIKE kulcsszó után kell megadni.
A karakterek összehasonlításánál a kis- és a nagybetű különböző karakternek számít!
Példák:
A val IN(10,20,40) kifejezés értéke igaz, ha a val változó értéke megegyezik a zárójelben felsorolt értékek valamelyikével.
A val BETWEEN 100 AND 200 értéke igaz, ha a val változó értéke a 100-200 értékek között van, vagy egyenlő valamelyikkel.
A szoveg LIKE 'B%' értéke igaz, ha a szoveg változóban található karaktersorozat B betűvel kezdődik.
A szoveg LIKE '_LO' értéke igaz, ha a szoveg változóban található karaktersorozat 3 betűből áll, és a 2., 3. karaktere LO .
Logikai műveletek
NOT negálás
OR logikai vagy
AND logikai és
A logikai műveletek ismertetése a CLIPPER nyelv leírásánál található.
Kifejezések
Egy kifejezés lehet
- oszlopnév, konstans, vagy speciális érték (pl. SYSDATE, NULL)
- függvényhívás
- kifejezések műveleti jelekkel és zárójelekkel összekapcsolva
Az SQL utasítások típusai
Adatdefiníciós utasítások (Data Definition Language - DDL)
Ezek szolgálnak az adatbázis objektumainak (táblák, indexek, nézettáblák, stb.) definiálására, létrehozására, megszüntetésére.
Adatmanipulációs utasítások (Data Manipulation Language - DML)
Ezekkel az utasításokkal tudjuk a táblák tartalmát megváltoztatni (új sor felvitele, sorok törlése, mezők módosítása). Egyes csoportosítások ide sorolják a lekérdező utasítást is.
Lekérdező utasítások (Data Query Language - DQL)
Más csoportosítások megkülönböztetik a lekérdező utasítást az adatmanipulációs utasításoktól és külön csoportba sorolják.
Tranzakció vezérlő utasítások (Transaction Control Language - TCL)
A műveletek logikai csoportokba, tranzakciókba történő csoportosítását teszik lehetővé. Két legfontosabb utasítása a COMMIT és a ROLLBACK.
Adatvezérlő utasítások (Data Control Language - DCL)
Az adatok tárolását vezérlő utasítások, ezek közé tartozik az adatbázisok létrehozására szolgáló parancs is.
Táblák létrehozása, karbantartása, törlése
Egy táblát a következő utasítással hozhatunk létre:
CREATE TABLE táblanév
(<oszlopnév> <adatleírás>
[,<oszlopnév> <adatleírás>]...)
táblanév a létrehozandó tábla neve
oszlopnév egy oszlop neve
adatleírás az oszlopban tárolandó adatok típusának és méretének megadása
Az adatok méretét a típus után zárójelben lehet megadni. Karakteres oszlopoknál a karakterek számát adhatjuk meg, numerikus oszlopoknál pedig az összes jegyek, valamint a tizedesjegyek számát, vesszővel elválasztva.
Az oszlopok megadásánál a következő opciókat alkalmazhatjuk még:
NOT NULL az adott oszlopban nem lehet üres mező
UNIQUE az adott oszlopban minden sorban
különböző értéknek kell szerepelnie
DEFAULT <kifejezés> alapértelmezés szerint ebbe az oszlopba
a megadott kifejezés értéke
kerül, ha nem adunk meg mást az adatfeltöltéskor
PRIMARY KEY az oszlop a táblában elsődleges kulcs
Az elsődleges kulcs egyedi kulcs, ezért a UNIQUE opciót nem szabad a PRIMARY KEY opcióval együtt megadni.
Példák
CREATE TABLE hallgato
(hkod NUMBER(5) PRIMARY KEY,
nev CHAR(20),
szak CHAR(10),
evf NUMBER(1) NOT NULL,
csop NUMBER(1),
eredm NUMBER(3,2),
szuld DATE)
CREATE TABLE anyag
(akod NUMBER(4) UNIQUE,
anev CHAR(20),
meny_e CHAR(3) DEFAULT 'KG' )
Ha egy táblát meg akarunk szüntetni, ezt a következő utasítással tehetjük meg:
DROP TABLE <táblanév>
Egy táblába egy sort felvinni a következő utasítással lehet:
INSERT INTO <táblanév> [(<oszlopnév>[,<oszlopnév>...)]
táblanév egy létező tábla neve
oszlopnév a tábla egy oszlopának neve
érték az adott oszlopnak megfelelő típusú érték
Ha nem adunk meg oszlopneveket, akkor az új sorban a tábla minden oszlopának adunk értéket. Ha megadjuk az oszlopneveket, akkor csak azok az oszlopok kapnak értéket, amelyek a felsorolásban szerepelnek. A többi oszlop értéke - ha nem adtunk meg default értéket - NULL lesz. Ezek az oszlopok nem lehetnek NOT NULL opcióval definiálva.
Példák
INSERT INTO hallgato (hkod,nev,szak,evf)
VALUES (10001,'KEREK ERVIN','KONYVTAR',2)
INSERT INTO anyag VALUES(2233,'AGYAG','T')
Adatmódosításokat egy táblában a következő utasítással lehet végrehajtani:
UPDATE <táblanév>
SET <oszlopnév> = <kif> [,<oszlopnév> = <kif>...]
[WHERE <feltétel>]
táblanév egy létező tábla neve
oszlopnév a tábla egy oszlopának neve
kif az adott oszlopnak megfelelő típusú értéket eredményező kifejezés
feltétel logikai kifejezés, értéke igaz, vagy hamis
Az adatok módosítása abban a sorban, vagy sorokban történik meg, melyekre a feltétel igaz. A megadott oszlopokba az adott értékek kerülnek beírásra.
Példák
UPDATE hallgato
SET eredm=3.85 WHERE nev='KIS KATALIN'
UPDATE hallgato
SET eredm=0 WHERE evf=2
Egy táblából a következő utasítás segítségével törölhetünk sorokat:
DELETE [FROM] <táblanév> [WHERE <feltétel>]
táblanév egy létező tábla neve
feltétel logikai kifejezés, értéke igaz, vagy hamis
A táblából azon sor, vagy sorok törlődnek, melyekre a megadott feltétel igaz. Ha nem adunk meg feltételt, akkor minden sor törlődik.
Példák
DELETE FROM hallgato WHERE hkod=11234
DELETE FROM anyag
Egy táblához hozzáadhatunk új oszlopokat, vagy módosíthatjuk a meglevő oszlopok definícióját a következő utasítással:
ALTER TABLE <táblanév>
[ADD (<oszlopnév> <adatleírás>
[,<oszlopnév> <adatleírás>]...)]
[MODIFY (<oszlopnév> <adatleírás>
[,<oszlopnév> <adatleírás>]...)]
táblanév egy létező tábla neve
oszlopnév egy oszlop neve
adatleírás az oszlopban tárolandó adatok típusának és méretének megadása
Az ADD opcióval új oszlopot tudunk hozzávenni a táblázathoz.
A MODIFY opcióval egy meglevő oszlop típusát, méretét és a NOT NULL opciót tudjuk megváltoztatni.
Példa
ALTER TABLE hallgato
ADD (szhely CHAR(20))
MODIFY (nev char(30))
COMMIT és ROLLBACK
Az ORACLE egy tranzakciónak tekinti az SQL utasítások egy sorozatát. A tranzakció egy COMMIT, vagy ROLLBACK utasítás utáni első SQL utasítással - illetve a legelső SQL utasítással - kezdődik és a következő COMMIT, vagy ROLLBACK utasításig - illetve a munka befejezéséig - tart.
A COMMIT utasítás megerősíti a tranzakció során elvégzett műveleteket, véglegesíti az adatokon történt változtatásokat. Hatására feloldódnak a tranzakció során létrejött lockolások.
A ROLLBACK utasítás hatására törlődnek a tranzakció során végrehajtott adatváltoztatások és feloldódnak a tranzakció során létrejött lockolások.
Ha egy tranzakció során a munka valamilyen oknál fogva megszakad, akkor az utolsó COMMIT utasítás utáni változtatások törlődnek.
Lekérdezések
Az SQL nyelv legfontosabb utasítása a SELECT utasítás, melynek segítségével egy vagy több adattáblából nyerhetünk információkat. A lekérdezés eredménye egy táblázat, amely megjeleníthető a képernyőn, vagy kinyomtatható, de lehetőség van a további feldolgozására is. Az utasítás formája:
SELECT [ ALL | DISTINCT ]
[, ]...
FROM <táblanév> [, <táblanév>]...
[ WHERE <feltétel> ]
[ GROUP BY <kif> [, <kif>]... [HAVING <feltétel>] ]
[ SELECT ... ]
[ ORDER BY [ASC | DESC]
[, [ASC | DESC]] ]...
táblanév egy létező tábla neve
kif lehet oszlopnév, vagy konstans, vagy ezekből különböző műveletekkel,
függvényekkel alkotott kifejezés
feltétel logikai kifejezés, értéke igaz, vagy hamis
A SELECT utasítással a FROM opcióban felsorolt táblák direkt szorzatából a FROM előtt felsorolt oszlopokat választjuk ki. Az erdménytábla sorait pedig a WHERE feltétellel definiálhatjuk. Ha nem adunk meg feltételt, akkor minden sort kiválasztunk a direkt szorzatból.
A különböző opciók jelentése:
ALL a lekérdezés eredményében a feltételnek megfelelő minden sor szerepel -
ez az alapértelmezés
DISTINCT a lekérdezés eredményében az azonos sorok csak egyszer
szerepelnek
a FROM opcióban megadott minden tábla minden oszlopa
szerepeljen az eredménytáblában
<táblanév>.* az adott tábla minden oszlopa szerepeljen az
eredménytáblában
A SELECT utasításban rendelkezhetünk a kiválasztott sorok csoportosításáról, rendezéséről is, erre szolgál a GROUP BY és az ORDER BY opció.
A GROUP BY opcióban megadhatunk egy, vagy több oszlopot, amelyben azonos értéket tartalmazó sorokat csoportosítani szeretnénk, általában valamilyen összesítés céljára.
Az ORDER BY opcióban azokat az oszlopokat definiálhatjuk, melyek szerint az eredménytábla rendezett lesz. Az ASC opció növekvő sorrendet jelent - ez az alapértelmezés - a DESC pedig csökkenőt.
A SELECT utasítás segítségével különböző relációalgebrai műveleteket valósíthatunk meg. Az oszlopok megadásával projekciót végzünk, a WHERE opcióban megadott feltétellel pedig szelekciót. Ha az utasítás több táblára vonatkozik, akkor ezen táblák direkt szorzatát készítjük el, amely részhalmazait a WHERE opcióban megadott feltételekkel választhatjuk ki (összekapcsolás, vagy join).
A SELECT utasításban definiálhatunk allekérdezést is, és képezhetjük lekérdezések egyesítését (UNION) , metszetét (INTERSECT) , vagy különbségét (MINUS).
A létezik (EXISTS) és minden (ALL) operátorok segítségével pedig a relációanalízist alkalmazhatjuk.
Példák SELECT utasításra
Példáinkban a következő táblákat fogjuk felhasználni:
Példák
SELECT * FROM hallgato
Ezzel az utasítással minden sort és oszlopot kiválasztunk a hallgato táblából.
Oszlopok kiválasztása
SELECT nev,eredm FROM hallgato
Ezzel az utasítással a hallgato táblából a nev és a jegy oszlopokat választjuk ki. Eredménye a következő tábla:
NEV EREDM
-------- ----- ------ ---------
KOVÁCS JÓZSEF 4.33
KEREK ANNA 3.96
SOMOGYI ERVIN 4.88
ANGYAL RÓZSA 2.56
SÓS ENDRE 3.76
SZABÓ TAMÁS 2.12
KISS TÜNDE 4.87
Sorok kiválasztása, rendezés
SELECT DISTINCT szak,evf,csop FROM hallgato
A fenti utasítás eredménytáblájának három oszlopa lesz, a szak, az evf, és a csop, és az ezen oszlopokban azonos értékeket tartalmazó sorok közül csak egy kerül át az eredménytáblába:
SZAK EVF CSOP
-------- ----- ------ -------------
GÉPÉSZ 5 4
KÖNYVTÁR 1 2
KÖNYVTÁR 1 3
KÖNYVTÁR 2 2
MEZŐGAZDÁSZ 3 1
SELECT * FROM hallgato WHERE szak='KÖNYVTÁR'
Ezzel az utasítással a hallgato táblából kiválasztunk minden oszlopot, de csak azok a hallgatók fognak szerepelni az eredménytáblában, akik a KÖNYVTÁR szakra járnak.
SELECT * FROM hallgato WHERE szak='KÖNYVTÁR' ORDER BY evf,csop
Az eredménytábla tartalma megegyezik az előzővel, csak a hallgatók évfolyam, azon belül pedig csoport szerinti rendezettségben szerepelnek benne:
HKOD NEV SZAK EVF CSOP EREDM -------- ----- ------ -------- ----- ------ ----- ----- ---------------- 10012 SZABÓ TAMÁS KÖNYVTÁR 1 2 2.12
10001 KOVÁCS JÓZSEF KÖNYVTÁR 1 3 4.33
10342 KISS TÜNDE KÖNYVTÁR 1 3 4.87
10054 SOMOGYI ERVIN KÖNYVTÁR 2 2 4.88
SELECT * FROM hallgato WHERE szak='KÖNYVTÁR'
ORDER BY evf,eredm DESC
Az eredménytábla ugyanazokat a sorokat tartalmazza, mint az előző, de a rendezettsége évfolyam szerint növekvő, eredmény szerint pedig csökkenő. Vagyis az azonos évfolyamra járó hallgatók eredmény szerint csökkenő sorrendben követik egymást:
HKOD NEV SZAK EVF CSOP EREDM
-------- ----- ------ -------- ----- ------ ----- ----- ----------------
10342 KISS TÜNDE KÖNYVTÁR 1 3 4.87
10001 KOVÁCS JÓZSEF KÖNYVTÁR 1 3 4.33
10012 SZABÓ TAMÁS KÖNYVTÁR 1 2 2.12
10054 SOMOGYI ERVIN KÖNYVTÁR 2 2 4.88
SELECT nev,eredm FROM hallgato WHERE eredm>3.5
Ezen utasítás hatására létrejövő eredménytábla csak azokat a sorokat fogja tartalmazni a hallgato táblából, ahol az ered oszlopban található érték 3.5-nél nagyobb:
NEV EREDM
-------- ----- ------ -----
KOVÁCS JÓZSEF 4.33
KEREK ANNA 3.96
SOMOGYI ERVIN 4.88
SÓS ENDRE 3.76
KISS TÜNDE 4.87
SELECT nev FROM hallgato WHERE szak IN('KÖNYVTÁR','GÉPÉSZ')
Az eredménytábla azon hallgatók nevét fogja tartalmazni, akik a KÖNYVTÁR, vagy a GÉPÉSZ szakra járnak:
NEV
----- ----- --------------
KOVÁCS JÓZSEF
SOMOGYI ERVIN
SÓS ENDRE
SZABÓ TAMÁS
KISS TÜNDE
SELECT nev,eredm FROM hallgato
WHERE eredm BETWEEN 3 AND 4.5
Az eredménytábla azon hallgatók nevét és eredményét fogja tartalmazni, akik eredménye 3.00 és 4.50 közé esik, illetve egyenlő valamelyikkel:
NEV EREDM
-------- ----- ------ --------
KOVÁCS JÓZSEF 4.33
KEREK ANNA 3.96
SÓS ENDRE 3.76
Összetett feltételt is megadhatunk logikai műveletek segítségével:
SELECT nev,eredm FROM hallgato
WHERE eredm>3.5 AND szak='KÖNYVTÁR'
Az eredménytábla a következő lesz:
NEV EREDM
-------- ----- ------ -----
KOVÁCS JÓZSEF 4.33
SOMOGYI ERVIN 4.88
KISS TÜNDE 4.87
SELECT nev,eredm FROM hallgato
WHERE eredm>4 OR eredm<3
Az eredménytábla ez lesz:
NEV EREDM
-------- ----- ------ -------
KOVÁCS JÓZSEF 4.33
SOMOGYI ERVIN 4.88
ANGYAL RÓZSA 2.56
SZABÓ TAMÁS 2.12
KISS TÜNDE 4.87
Ezt a kiválasztást a következőképpen is felírhatjuk:
SELECT nev,eredm FROM hallgato
WHERE eredm NOT (BETWEEN 3 AND 4)
Táblázatok összekapcsolása
SELECT tnev,vizsga.* FROM tantargy,vizsga
WHERE tantargy.tkod=vizsga.tkod
ORDER BY jegy DESC
Ezzel az utasítással elkészítjük a tantárgy és a vizsga tábla direkt szorzatát, majd ebből azokat a sorokat választjuk ki a WHERE opcióban megadott feltétel segítségével, amelyekben a két tantárgykód értéke megegyezik. Vagyis a tantárgy és a vizsga tábla természetes összekapcsolását készítjük el. A vizsga tábla egy sorához a tantárgy táblának az a sora fog kapcsolódni, amelyik az adott sorban szereplő tantárgyra vonatkozik. Az eredménytábla sorainak száma megegyezik a vizsga tábla sorainak számával. Az oszlopok közül a tantárgynév oszlopot és a vizsga táblában szereplő minden oszlopot kiválasztunk.
Az eredménytábla a jegyek szerinti csökkenő rendezettségű lesz:
TNEV HKOD TKOD JEGY
-------- ----- ------ -------- ----- ------ ----- ----- ----------------
INFORMATIKA 10103 102 5
SZOCIOLÓGIA 10001 105 5
KÖNYVTÁRI ISMERETEK 10001 101 4
SZOCIOLÓGIA 10276 105 4
ETIKA 10012 103 4
STATISZTIKA 10103 104 4
KÖNYVTÁRI ISMERETEK 10276 101 3
INFORMATIKA 10342 102 3
ETIKA 10004 103 3
KÖNYVTÁRI ISMERETEK 10342 101 2
INFORMATIKA 10054 102 2
STATISZTIKA 10004 104 1
SELECT nev,tnev,jegy FROM hallgato,tantargy,gyakjegy
WHERE hallgato.hkod=gyakjegy.hkod AND
gyakjegy.tkod=tantargy.tkod
Ezzel az utasítással három tábla direkt szorzatát készítjük el. Ebből azokat a sorokat választjuk ki, amelyekben a hallgatóhoz tartozó gyakorlati jegy és az adott tantárgyhoz tartozó leírás szerepel (természetes összekapcsolás). A létrejövő tábla minden sora egy hallgató nevét, egy tantárgy nevét és a hallgató abból elért eredményét tartalmazza. Egy hallgató annyi sorban szerepel, ahány tárgyból gyakorlati jegyet szerzett:
NEV TNEV JEGY
-------- ----- ------ -------- ----- ------ ----- ----- -------
KOVÁCS JÓZSEF KÖNYVTÁRI ISMERETEK 5
KOVÁCS JÓZSEF SZOCIOLÓGIA 3
SÓS ENDRE ETIKA 4
SÓS ENDRE STATISZTIKA 2
SZABÓ TAMÁS ETIKA 4
SOMOGYI ERVIN INFORMATIKA 2
KEREK ANNA INFORMATIKA 5
KEREK ANNA STATISZTIKA 5
ANGYAL RÓZSA KÖNYVTÁRI ISMERETEK 1
ANGYAL RÓZSA SZOCIOLÓGIA 4
KISS TÜNDE KÖNYVTÁRI ISMERETEK 3
KISS TÜNDE INFORMATIKA 4
SELECT DISTINCT nev FROM hallgato,vizsga
WHERE hallgato.hkod=vizsga.hkod AND jegy=1
Ezzel az utasítással azoknak a hallgatóknak a nevét kapjuk meg, akiknek van elégtelen vizsgajegye. Ha egy hallgatónak több elégtelen vizsgajegye van, akkor is csak egyszer fog a neve szerepelni az eredménytáblában a DISTINCT opció miatt.
Esetünkben csak egyetlen hallgatónak van elégtelen vizsgajegye, így az eredménytáblában csak egy név fog szerepelni: SÓS ENDRE.
SELECT DISTINCT tnev FROM tantargy,vizsga
WHERE tantargy.tkod=vizsga.tkod AND jegy<3
Ezzel az utasítással azon tantárgyneveket választottuk ki, amelyekből volt hármasnál rosszabb vizsgajegy. Az eredménytábla:
TNEV
-------- ----- ------ ------
INFORMATIKA
KÖNYVTÁRI ISMERETEK
STATISZTIKA
Egy táblát önmagával is össze lehet kapcsolni:
SELECT x.nev FROM hallgato x,hallgato y
WHERE x.eredm>y.eredm AND y.nev='KEREK ANNA'
Ezzel az utasítással azokat a hallgatókat választjuk ki a hallgato táblából, akiknek eredménye jobb, mint a KEREK ANNA nevű hallgatóé. Hogy meg tudjuk különböztetni a tábla kétféle felhasználását egymástól, helyettesítő neveket kellett bevezetnünk. Az x a hallgato tábla egyik neve, y pedig a másik. Az oszlopnevek megadásakor ezeket a helyettesítő neveket kell használni.
Két, vagy több eredménytábla egyesítése, metszete, különbsége
Két SELECT utasítás eredményének akkor lehet az egyesítését, metszetét, vagy különbségét képezni, ha az eredménytáblák azonos oszlopszámúak és a megfelelő oszlopok kompatibilis típusúak. Az oszlopneveknek nem kell azonosnak lenniük. Az eredménytáblába csak a különböző sorok kerülnek be.
SELECT hkod FROM vizsga WHERE jegy=1
UNION
SELECT hkod FROM gyakjegy WHERE jegy=1
Ezzel az utasítással azokat a hallgatókat választottuk ki, akiknek valamely tárgyból van elégtelen vizsga- , vagy gyakorlati jegye. Az eredménytábla:
HKOD
-----------
10004
10276
SELECT hkod FROM vizsga WHERE jegy=5
INTERSECT
SELECT hkod FROM gyakjegy WHERE jegy=5
Ezzel az utasítással azoknak a hallgatóknak a kódját kapjuk eredményül, akiknek volt jeles vizsgajegyük és gyakorlati jegyük is.
Az eredménytábla:
HKOD
-----------
10001
10103
SELECT hkod FROM vizsga WHERE jegy=5
MINUS
SELECT hkod FROM gyakjegy WHERE jegy!=5
Ezzel az utasítással azon hallgatók kódját választottuk ki, akiknek csak jeles gyakorlati jegyük van és volt jeles vizsgajegyük is.
Az eredménytábla:
HKOD
-----------
10103
Ha azokat a hallgatókat szeretnénk kiválasztani, akiknek csak jeles vizsga- és gyakorlati jegyük van, ezt a következő utasítással érhetjük el:
SELECT hkod FROM vizsga WHERE jegy=5
MINUS
SELECT hkod FROM vizsga WHERE jegy!=5
MINUS
SELECT hkod FROM gyakjegy WHERE jegy!=5
Láthatjuk, hogy azonos táblára vonatkozó SELECT-ek eredményét is összekapcsolhatjuk egymással. Esetünkben a fenti utasítás eredménye üres tábla lesz, mert nincs ilyen hallgató a táblákban.
Csoportképzés, oszlopfüggvények
Gyakran szükségünk van arra, hogy a kiválasztott adatokról valamilyen összesített információt kapjunk, esetleg valamilyen csoportosítás szerint. Az oszlopfüggvények arra szolgálnak, hogy egy-egy oszlop adatain végezzenek műveletet.
A leggyakrabban használt oszlopfüggvények a következők:
SUM(<oszlopnév>) összegzi az oszlopban szereplő értékeket
MIN(<oszlopnév>) az oszlopban szereplő legkisebb értéket adja
eredményül
MAX(<oszlopnév>) az oszlopban szereplő legnagyobb értéket adja
eredményül
AVG(<oszlopnév>) az oszlopban szereplő értékek átlagát számítja ki
COUNT megadja a kiválasztott sorok számát
COUNT(<oszlopnév>) megadja azon kiválasztott sorok számát,
ahol az adott oszlop értéke nem NULL
Az oszlopfüggvények - a COUNT(*) kivételével - azokat a sorokat figyelmen kívül hagyják, ahol az adott oszlopban NULL érték található.
Az oszlopfüggvények paraméterezésénél - a COUNT(*) kivételével - megadható a DISTINCT opció, melynek hatására a kiválasztott értékek közül csak a különbözőeket veszi figyelembe a függvény.
Példák
SELECT COUNT(*) FROM hallgato
A fenti utasítás segítségével azt kapjuk meg, hogy hány hallgató van a hallgato táblában.
SELECT COUNT(*) FROM hallgato WHERE szak='KÖNYVTÁR'
Ezen utasítás eredménye az, hogy hány KÖNYVTÁR szakos hallgató van a hallgato táblában.
SELECT COUNT(DISTINCT evf) FROM hallgato
Ezen utasítás eredményeként azt kapjuk meg, hogy hány féle évfolyamra járnak a hallgatók.
SELECT AVG(eredm) FROM hallgato WHERE evf=1
Ezen utasítás eredményeként megkapjuk az elsős hallgatók átlagos tanulmányi eredményét.
SELECT MAX(eredm) FROM hallgato
Ezzel az utasítással a legjobb tanulmányi eredmény értékét kapjuk meg.
A sorokat a GROUP BY opció segítségével csoportosíthatjuk. Az opcióban megadott oszlopban azonos értékkel rendelkező sorok egy csoportot alkotnak. Az opcióban több oszlopot is megadhatunk. Ekkor bármelyik oszlop megváltozása új csoportot képez.
Példák
SELECT szak,MAX(eredm) FROM hallgato GROUP BY szak
Az eredménytábla a következő lesz:
SZAK MAX(EREDM)
-------- ----- ------ ----- ----- -----
GÉPÉSZ 3.76
KÖNYVTÁR 4.88
MEZŐGAZDÁSZ 3.96
SELECT szak,evf,MAX(eredm) FROM hallgato GROUP BY szak,evf
Az eredménytábla a következő lesz:
SZAK EVF MAX(EREDM)
-------- ----- ------ ----- ----- --------- ----- ------
GÉPÉSZ 5 3.76
KÖNYVTÁR 1 4.87
KÖNYVTÁR 2 4.88
MEZŐGAZDÁSZ 3 3.96
Ha a csoportképzést valamilyen feltételhez akarjuk kötni, ezt a HAVING opcióban adhatjuk meg.
Példa
SELECT evf,MIN(eredm) FROM hallgato GROUP BY evf HAVING evf<3
Hatására az első két évfolyam legrosszabb tanulmányi eredményét kapjuk meg:
EVF MIN(EREDM)
----- ----- --------- ----- -------
1 2.12
2 4.88
Belső SELECT-ek (subqueries)
Egy SELECT utasítás WHERE feltételében állhat egy újabb SELECT utasítás (allekérdezés). Erre általában akkor van szükség, ha a sorok kiválasztása valamely más táblázatban található információn alapul. A belső SELECT hivatkozhat ugyanarra a táblázatra is, mint a külső. Ez akkor fordul elő, ha nem tudjuk egyetlen kifejezésben megfogalmazni a leválogatási feltételt. A belső SELECT-ben szerepelhet oszlopfüggvény is.
A belső SELECT-ek használata több szintű is lehet, vagyis egy belső SELECT-nek is lehet belső SELECT-je. A lekérdezések végrehajtása belűről kezdődik és kifelé halad. A legbelső SELECT hajtódik végre legelőször, majd sorban a fölötte levő szintek.
Példák
SELECT nev FROM hallgato
WHERE evf=(SELECT evf FROM hallgato WHERE nev='KOVÁCS JÓZSEF')
Ezzel az utasítással azoknak a hallgatóknak a nevét kapjuk meg, akik Kovács Józseffel egy évfolyamra járnak. A belső SELECT-ben meghatározzuk , hogy melyik évfolyamra jár Kovács József, a külső SELECT segítségével pedig azoknak a hallgatóknak a nevét választjuk ki, akiknél az évfolyam értéke megegyezik a belső SELECT eredményével.
SELECT nev,eredm FROM hallgato
WHERE eredm=(SELECT MAX(eredm) FROM hallgato)
Ezzel az utasítással megkapjuk a hallgato táblázatból a legjobb eredményt elért hallgató nevét és eredményét. A belső SELECT kiválasztja az eredményoszlopból a legnagyobb értéket, a külső SELECT segítségével meghatározzuk azokat a hallgatókat, akiknek az eredménye megegyezik ezzel az értékkel.
Az egyenlőségként megfogalmazott belső SELECT-nek mindig egyetlen értéket kell eredményeznie. Az előző belső SELECT csak egyetlen értéket ad eredményül, hiszen a legjobb eredmény értéke egyetlen érték akkor is, ha több hallgató is elérte azt. A külső SELECT eredményezhet több sort is:
SELECT tkod FROM gyakjegy
WHERE jegy=(SELECT max(jegy) FROM gyakjegy)
A belső SELECT eredménye egyetlen érték (5), a külső SELECT azonban azoknak a tantárgyaknak a kódját fogja kiválasztani a gyakjegy táblából, amelyekből volt 5-ös gyakorlati jegy. Így a következő eredményt kapjuk:
TKOD
-----------
101
104
102
Ha a belső SELECT több értéket eredményez, akkor a külső SELECT-ben az IN operátort alkalmazhatjuk:
SELECT nev FROM hallgato
WHERE hkod IN (SELECT hkod FROM vizsga WHERE jegy=1)
Ezzel az utasítással azoknak a hallgatóknak a nevét kapjuk eredményül, akiknek elégtelen vizsgajegye van. A belső select a vizsga táblából leválogatja azokat a hallgatókódokat, amelyekhez elégtelen vizsgajegy tartozik. A külső SELECT segítségével a hallgató táblából leválogatjuk a hallgatókódokhoz tartozó neveket. Láthattuk az előzőekben, hogy ezt az információt a hallgato és a vizsga táblák összekapcsolásával is megkaphatjuk.
A SELECT utasításban alkalmazható további operátorok
ALL (lista) relációs operátorral együtt alkalmazható.
Akkor ad igaz értéket eredményül, ha a reláció a lista minden elemére teljesül. A lista helyett belső SELECT is állhat.
Példa:
SELECT nev FROM hallgato WHERE
eredm > All (SELECT eredm FROM hallgato WHERE evf=1)
Ezzel az utasítással azoknak a hallgatóknak a nevét kapjuk eredményül, akiknek minden első éves hallgatónál jobb a tanulmányi eredményük. Esetünkben egy ilyen hallgató van: SOMOGYI ERVIN.
Ezt a lekérdezést az ALL kulcsszó helyett a MAX függvény segítségével is felírhattuk volna:
SELECT nev FROM hallgato WHERE evf>1 AND
eredm > (SELECT MAX(eredm) FROM hallgato WHERE evf=1)
ANY (lista) relációs operátorral együtt alkalmazható.
Akkor ad igaz értéket eredményül, ha a reláció a lista valamelyik elemére teljesül. A lista helyett belső SELECT is állhat.
Példa:
SELECT nev FROM hallgato WHERE evf>1 AND
eredm > ANY (SELECT eredm FROM hallgato WHERE evf=1)
Ezzel az utasítással azoknak a nem elsős hallgatóknak a nevét kapjuk meg, akik eredménye jobb valamely első éves hallgató eredményénél:
NEV
----- ----- ----------------
KEREK ANNA
SOMOGYI ERVIN
ANGYAL RÓZSA
SÓS ENDRE
Ezt a lekérdezést az ANY kulcsszó helyett a MIN függvény segítségével is felírhattuk volna:
SELECT nev FROM hallgato WHERE evf>1 AND
eredm > (SELECT MIN(eredm) FROM hallgato WHERE evf=1)
EXISTS belső SELECT-tel együtt alkalmazható. Akkor ad igaz eredményt, ha a belső SELECT legalább egy sort eredményezett.
Példa:
SELECT nev FROM hallgato
WHERE EXISTS (SELECT * FROM vizsga
WHERE vizsga.hkod=hallgato.hkod AND tkod=104)
Ezzel az utasítással azon hallgatók nevét kapjuk eredményül, akiknek van vizsgajegyük a 104-es kódú tantárgyból. A belső SELECT segítségével azt vizsgáljuk meg a hallgató tábla minden sorára, hogy a vizsga táblában van-e olyan sor az adott hallgatókóddal, amelyben a tantárgykód értéke 104. Ha van ilyen sor, akkor a hallgató neve bekerül az eredménytáblába, egyébként pedig nem.
A példában egy speciális típusú belső SELECT-et alkalmaztunk (correlated query). Itt a belső SELECT a külső SELECT-ben szereplő tábla minden sorára kiértékelődik. Ez azért történik így, mert a belső SELECT WHERE opciójában kapcsolatot határoztunk meg a külső SELECT-ben szereplő tábla és a belső SELECT-ben szereplő tábla között. Más típusú belső SELECT-ek esetén a belső SELECT csak egyszer értékelődik ki.
Ezt a lekérdezést másképpen is megfogalmazhatjuk:
SELECT nev FROM hallgato WHERE hkod IN (SELECT hkod FROM vizsga WHERE tkod=104)
A következő lekérdezéssel azon hallgatók nevét kapjuk meg, akiknek van vizsgajegyük statisztikából.
SELECT nev FROM hallgato WHERE hkod IN (SELECT hkod FROM vizsga WHERE tkod=(SELECT tkod FROM tantargy WHERE tnev='STATISZTIKA'))
Ebben a megfogalmazásban két egymásba ágyazott belső SELECT-et alkalmaztunk. A második belső SELECT a tantárgy táblából meghatározza a statisztika nevű tantárgy kódját. Az első belső SELECT pedig azon hallgatókódokat választja ki a vizsga táblából, akiknek van jegyük a második belső SELECT-tel meghatározott tantárgykódú tantárgyból. A külső SELECT azon hallgatók nevét eredményezi, akiknek a kódja szerepel az első belső SELECT eredménylistájában.
Az ALL, ANY és EXISTS operátorok mindegyike előtt használható a NOT kulcsszó. Ekkor a művelet eredményének negált értékét kapjuk.
Példa:
SELECT nev FROM hallgato
WHERE NOT EXISTS (SELECT * FROM vizsga
WHERE vizsga.hkod=hallgato.hkod AND jegy=1)
Ezzel a SELECT utasítással azoknak a hallgatóknak a nevét válogatjuk le, akiknek nincs elégtelen vizsgajegye.
A SELECT utasítás segítségével egy-egy lekérdezést többféleképpen is megfogalmazhatunk. Célszerű azt a megfogalmazást választani, amely az adott szempontokat a legáttekinthetőbben, a legolvashatóbban írja le. Nagy adatállományok esetén azonban az elsődleges szempont az, hogy melyik megfogalmazás biztosítja az adatok leggyorsabb megkeresését. (Ez pedig függ az SQL nyelv konkrét megvalósításától.)
Nézettáblák
Az SQL nyelvben lehetőségünk van arra, hogy egy vagy több táblából nézettáblát hozzunk létre, melynek segítségével az adatokat a számunkra könnyebben kezelhető formában láthatjuk. A nézettábla csak logikai tábla, fizikailag nem létezik. Az adatbázisban a nézettábla kialakítására szolgáló lekérdezés kerül tárolásra.
Nézettáblát az alábbi utasítással lehet létrehozni:
CREATE VIEW <név> AS <lekérdezés>
név a nézettábla neve
lekérdezés azon táblák oszlopainak és sorainak megadása, amelyeken a
nézettábla alapul. Általában ez egy SELECT utasítás, amely
nem tartalmazhat ORDER BY opciót.
Nézettáblákat bármelyik SQL utasításban használhatunk, ahol táblák használata megengedett, azzal a megszorítással, hogy amennyiben a nézettábla nem egyetlen tábla adatain alapul, vagy a nézettáblát definiáló lekérdezésben a GROUP BY opció szerepel, akkor csak lekérdezhetünk adatokat a nézettáblából. Egyébként módosításokat is végezhetünk a nézettáblában szereplő adatokon.
Példák
CREATE VIEW atlag AS SELECT hkod,nev,eredm FROM hallgato
Ezen utasítás hatására létrejön az atlag nézettábla, amely a hallgato táblából a hkod, nev, eredm oszlopokat tartalmazza.
CREATE VIEW statjegy
AS SELECT nev,evf,csop,jegy FROM hallgato,vizsga
WHERE hallgato.hkod=vizsga.hkod AND
tkod=(SELECT tkod FROM tantargy
WHERE tnev='STATISZTIKA')
A statjegy nézettábla a hallgato és a vizsga táblákon alapul. Az belső SELECT a vizsga táblából csak azokat a sorokat választja ki, ahol a tantárgy a statisztika.
A nézettáblákat a következő utasítással szüntethetjük meg:
DROP VIEW <név>
név a nézettábla neve
Az SQL nyelvhez kifejlesztették annak procedurális változatát, a PL/SQL nyelvet.
A PL/SQL tartalmazza az SQL utasításokat, továbbá vezérlő utasításokat - elágazást, kilépést, ciklusszervező utasításokat és értékadó utasítást. Ezt a nyelvet a fejlesztők használják, hatékony és fontos eszköze az alkalmazások készítésének.
Találat: 1516