A rövidítés a Structured Query Language, azaz struktúrált lekérdező nyelv szóösszetételt takarja. Egy olyan programnyelv, amellyel könnyen és gyorsan tudunk RDBMS-ből, (Relational Database Management Systes) azaz Relációs adatbázisból adatot nyerni vagy módosítani.
Történetéről annyi rémlik, hogy újságírók számára lett kifejleszve, a wikipedia vonatkozó szócikkében aki akar utána tud menni a részleteknek.
Egy alapszintű ízelítőt szeretnék adni azoknak, akik egyáltalán nem találkoztal még az SQL-el. Ugyan az SQL univerzális nyelv, mégis vannak adatbázis specifikus dolgai. Mivel én Oracle környezetben használom, ezért a lent írtak elsősorban erre érvényesek. Oracle általában nagyvállalati környezetben van, adatbázist azonban igen sok helyen használnak, például mySQL, MS-SQL Server.
Amennyire egyszerű leírónyelv az SQL, annyira bonyolult és komoly queryket lehet megfogalmazni benne. A téma igen mély és szerteágazó, még említés szintjén sincs lehetőségem pár ezer karakterben mindent felsorolni, elindulva az adatbázis séma megtervezésétől a végső finomhangolásokig (tuning), s akkor az Oracle rejtelmeiről és könyvtárat megtöltő ismeretanyagáról még nem beszéltem.
Szóval az SQL alapfunkciója az adatok visszanyerése. Az adatbázisok az adatainkat táblákban tárolják, amelyek relációs kapcsolatban vannak egymással. Egy igen bonyolult, sok munkalapos excel munkafüzetet képzeljünk el, ahol bizonyos cellák egymásra hivatkoznak.
Egy iskolapéldán keresztül mutatok be néhány alapvető lépést. Természetesen a példabeli dolgok máshogy is megoldhatók – esetenként akár egyszerűbben is.
Csináljunk egy DVD bolt hálózatot, ami nyílvántartja melyik boltban milyen filmből éppen mennyi. A végletekig egyszerűsítve ehhez elégséges három adattábla. Az elsőben a film alap-adatait tároljuk (pl rendező, cím, ár, stb), a másodikban a boltokat soroljuk fel (pl bolt név, cím, stb.), míg egy harmadikban nyilvántartjuk az egyes lemezek darabszámát üzletenként. A harmadik táblánk kapcsolja össze egymással az első kettőt.
Készítsük is el ezt a tábla struktúrát,
CREATE TABLE movies (movie_id NUMBER UNIQUE, director VARCHAR2(50), title VARCHAR2(50), price NUMBER);
CREATE TABLE shops (shop_id NUMBER UNIQUE, shop_name VARCHAR2(50), shop_address VARCHAR2(50));
CREATE TABLE stock (shop_id NUMBER, movie_id NUMBER, amount NUMBER,
CONSTRAINT fk_s FOREIGN KEY (shop_id) REFERENCES shops (shop_id),
CONSTRAINT fk_b FOREIGN KEY (movie_id) REFERENCES movies (movie_id)
);
A három táblánk közül a stock fogja nyilvántartani melyik filmből mennyi van raktáron egy adott boltban. Ahhoz, hogy az adatbázis konzisztens legyen, azaz ne fordulhasson elő például hogy olyan filmre hivatkozik a tábla amely nem is létezik a nyilvántartásunkban un. foreign key referenciákat használunk. Ez a referencia biztosítja, hogy a táblába inzertált adatok valós filmekre és boltokra hivatkozzanak. A movies és shops táblák tartalmaznak egy azonosítót, ún. id-t, mely segítségével egyszerűbben tudunk az adatainkra hivatkozni. Példánkban ez az id adja a tábla egyedi kulcsát. Ez a kulcs kényszer és index is egyben, kényszer mivel az egyediség miatt nem enged több azonos előfordulást a sorszámban, és index is mivel az Oracle autómatikusan létrehoz az egyedi kulcsokhoz egy indexet, többek között azok megfelelő sebességű ellenőrzésének érdekében.
Töltsük fel a táblákat néhány példa adattal,
INSERT INTO movies VALUES (1,'Renny Harlin','Ford Fairlane kalandjai',2990);
INSERT INTO movies VALUES (2,'Len Wiseman','Die Hard 4.0',3590);
INSERT INTO movies VALUES (3,'René Laloux, Hernádi Tibor','Az ido urai',4790);
INSERT INTO shops VALUES (1,'Shop A','BP XX. Ker.');
INSERT INTO shops VALUES (2,'Shop B','BP IX. Ker.');
Végül egy kezdő raktárkészletet adunk meg,
INSERT INTO stock VALUES (1,1,25);
INSERT INTO stock VALUES (1,2,6);
INSERT INTO stock VALUES (1,3,12);
INSERT INTO stock VALUES (2,1,2);
INSERT INTO stock VALUES (2,3,9);
A példában SQL utasítások használunk az adatok bevitelére, de az életben a userek elől ez mindíg rejtve van, bármilyen frontend applikáció is álljon az adatbázi fölött, el fogja végezni ezeket a feladatokat. A felhasználónak nincs más dolga, mint a megadott mezőket kitölteni.
Ahhoz, hogy a bevitt adataink állandóvá válljanak, a
COMMIT
utasítást kell használni. Az Oracle ugyanis tranzakciókat kezel, s egészen addig tart egy tranzakció amíg le nem zárjuk. A lezárás parancs a commit, mely hatására az adatbázis kezelő véglegesíti a változásokat. Ha nem akarjuk véglegesíteni, akkor
ROLLBACK
-et kell használjunk. Commit után már nincs lehetőség rollback-re, viszon az Oracle 10g-ben megjelent egy érdekes funkció, az erősen futurisztikus hangzású Flashback Technology. Erről bővebben a linket követve.
És elérkeztünk az érdekes részhez, nézzük meg mit lehet kezdeni egy ilyen egyszerű adatbázissal. Mivel a struktúra igen egyszerű, a lekérdezéket sem lehet nagyon megbonyolítani. Lássuk,
Melyik a legdrágább film? Az legdrágább árat egyszerű meghatározni,
SELECT MAX(price) FROM movies
de ha a film címét is meg akarjuk tudni akkor már sub selectet használhatunk. Ez a sub select gyakorlatilag egy összekapcsolás, a belső select eredményét kapcsoljuk a külsőhöz, mint feltételt vagy filtert.
SELECT title, price FROM movies WHERE price = (SELECT MAX(price) FROM movies)
Melyik boltban mekkora az árukészlet értéke? Ehhez a kérdéshez mindhárom táblát használni fogjuk, hiszen az ár a filmeknél, a bolt neve a boltoknál míg a készlet a stock táblában van. Itt jön elő a relációs adatbázis lényege, a táblák egymásra hivatkozása. A táblák kulcsokkal történő összekötését join-nak hívjuk. A query felépítését lépésről lépésre mutatom be. A táblák mögötti rövidítések neve alias, amellyel query-n belül hivatkozhatunk a táblára.
Első lépés, összekötjük a három táblát egyedi azonosító szerint,
SELECT * FROM shops sh, movies mo, stock st
WHERE st.shop_id = sh.shop_id
AND st.movie_id = mo.movie_id
A lekérdezés 5 sort ad vissza, a stock táblánk sorait és a hozzájuk kapcsolt shops és movies táblák mezőit. Az eredménylistából minket a bolt neve, és az árukészlet összege érdekel, aminek szintén adhatunk egy aliast („arukeszlet”) ha kedvünk tartja,
SELECT shop_name, price*amount arukeszlet FROM shops sh, movies mo, stock st
WHERE st.shop_id = sh.shop_id
AND st.movie_id = mo.movie_id
Nincs más dolgunk, mint összeszámolni az árukészletet boltonként,
SELECT shop_name, SUM(price*amount) arukeszlet FROM shops sh, movies mo, stock st
WHERE st.shop_id = sh.shop_id
AND st.movie_id = mo.movie_id
GROUP BY shop_name
A group by segítsétégel boltonként csoportosítottuk a query által visszaadott sorokat, így a listánk két sor lett:
SHOP_NAME ARUKESZLET
Shop A 153770
Shop B 49090
Végül listázzuk ki filmcímmel együtt melyik filmből mekkora a készlet raktáranként. A lista egy filmet csak egyszer tartalmazzon. E megoldásnál a filmek táblától indulunk ki, mivel ez tartalmaz minden általunk árult filmet. Az összekötést most nem a where feltétel után, hanem még a select mezőben tesszük meg.
SELECT title,
(SELECT SUM(amount) FROM stock WHERE shop_id = 1 AND movie_id = mo.movie_id) shop1,
(SELECT SUM(amount) FROM stock WHERE shop_id = 2 AND movie_id = mo.movie_id) shop2
FROM movies mo
Mivel a Die Hard 4 hiányzik az egyik boltunkból az eredmény tartalmaz egy üres, ún. null mezőt is,
TITLE SHOP1 SHOP2
Ford Fairlane kalandjai 25 2
Die Hard 4.0 6
Az ido urai 12 9
Ha azt akarjuk, hogy helyette 0 jelenjen meg, a következő képpen kell átalakítani a queryt.
SELECT title,
NVL((SELECT SUM(amount) FROM stock WHERE shop_id = 1 AND movie_id = mo.movie_id),0) shop1,
NVL((SELECT SUM(amount) FROM stock WHERE shop_id = 2 AND movie_id = mo.movie_id),0) shop2
FROM movies mo
A használt NVL függvény minden null előfordulást a vessző után definiált értékkel helyettesít be.
Ízelítőnek egyelőre ennyi, kérdést, véleményt szívesen várok.
