Rövid SQL összefoglaló

(Ihász Sándor, 1996)

Az egyes rendszerekben használatának más-más módozatai :
 - SQL mód  ( Dbase VI)
 - hívható SQL utasítások (FoxPro)
 - tiszta SQL (Oracle)

SQL lekérdezés, az SQL SELECT

 SELECT Ri1.A1, .. Rir.Ar
  FROM R1, .. , Rk
  WHERE ? ;

Ahol  R1, .. , Rk  : alaprelációk
 R.A       : R reláció A attribútuma
 ?       : DRC formula

A lekérdezéssel ekvivalens relációs algebrai kifejezés :

? Ri1.A1, .. Rir.Ar(??` (R1X .. XRk))

A kiértékelés menete a relációs algebrai kifejezés szerint történik.

Az attribútumok felsorolása helyetti * karakter az összeset helyettesesíti.

Pl.   - a negatív egyenlegű vásárlók neve
 SELECT Vnév
  FROM Vásárló
  WHERE Egyenleg<0 ;

  - azon szállítók neve, akik árujából Ló Béla rendelt már valaha
 SELECT Szállító.SzNév
  FROM Rendelés, Tétel, Szállító
  WHERE Rendelés.VNév='Ló Béla'
   AND Rendelés.Rsz=Tétel.Rsz
   AND Tétel.Termék=Szállító.Termék ;

Az egyedi attribútumok neve előtti minősítés elhagyható.
 A FROM utáni felsorolásban a relációknak alias nevet adhatunk.
 ...
 FROM Rendelés R, Tétel T, Szállító Sz
 ...

Az eredményben levő ismétlődések kiszűrése :
 SELECT DISTINCT .....

Feladat :
 - Azon vevők neve és címe, akik vásároltak egyszerre legalább 5    patareszelőt
 - Ló Béla által rendelt, 1200 piculánál olcsóbb termékek neve
 - Azon vásárlók neve és címe, akik egyenlege kisebb, mint Ló Béla    egyenlege

Speciális operátorok a WHERE klauzában

1. LIKE  -  összehasonlítás karakter-stringgel

<karakteres kifejezés> LIKE <minta>

Z betűvel kezdődő termékek listája
 SELECT Termék
  FROM Szállító
  WHERE Termék LIKE 'Z'
           OR Termék LIKE 'z' ;

A mintába tehető speciális karakterek :
  %   : tetszőleges számú karaktert helyettesíthet
  _     : egy karaktert helyettesíthet

2. IN, ANY, ALL   - Halmaz operátorok
Az összehasonlítás operandusaként szereplő halmazokat létrehozhatjuk  felsorolással vagy belső SELECT utasítással (beágyazott select,
sub-query).

a., IN     - halmazba tartozás

Azon rendelések listája, amelyek tartalmaznak Nyerget, Zablát, Lókötőt
 SELECT Rsz
  FROM Tétel
  WHERE Termék IN ('Nyereg', 'Zabla', 'Lókötő')

Ló Béla által rendelt  termékek listája
 SELECT DISTINCT Termék
  FROM Tétel
  WHERE Rsz IN
   ( SELECT Rsz
    FROM Rendelés
    WHERE VNév='Ló Béla'
   );

b.,  <kifejezés>  ? ALL <halmaz>  - a halmaz minden elemére történő                  megkötés
  Jelentése : A ? ANY S     :  (?X)(X?S ? A?X)

 <kifejezés>  ? ANY <halmaz>  - létezésre történő megkötés
  Jelentése : A ? ANY S     : (?X)(X?S ? A?X)

Pl. A legdrágább termék neve
 SELECT Termék
  FROM Szállító
  WHERE Ár > ALL
   ( SELECT Ár
    FROM Szállító
   );

Feladat :

- Azon vevők neve, akik rendeltek a legdrágább termékből
- Azon szállítók neve, akik szállítanak olyan terméket, amit a Nyerges  Ktsz is szállít
- Azon vevők neve és címe, akik rendeltek olyan terméket, amit már Ló          Béla is rendelt
 

A beágyazott select

A beágyazott select általában nem tartalmazhat minden klauzát, csak
SELECT-FROM-WHERE  felépítésű lekérdezés lehet.
Ha eredménye egy érték, akkor úgy kezelhetjük a külső lekérdezésben, mint egy konstanst.
Ha eredménye több érték (halmaz), akkor a halmazoperátorokkal kezelhetjük őket.

A beágyazott select fajtái :

 - független
 - korrelált

Ha független, akkor a kiértékeléskor először a belső lekérdezést értékeljük ki, aminek eredménye nem függ a külső lekérdezéstől.

Ha korrelált, akkor a belső lekérdezés eredménye függ a külső lekérdezéstől. Ez akkor jöhet létre, ha a belső lekérdezésben hivatkozunk a külső lekérdezésben szereplő valamely attribútumra.
Ekkor a külső lekérdezés FROM után szereplő relációinak minden sorára végrehajtódik a belső lekérdezés.

Pl.
 CSAPOLJA (Kocsma, Sör)
 SZERETI  (Korhely, Sör)
 ODAJÁR  (Korhely, Kocsma)

Azon korhelyek listája, akik látogatnak olyan kocsmát, ahol csapolnak olyan sört, amit szeret.

SELECT DISTINCT Szereti.Korhely
 FROM Szereti, Csapolja
 WHERE Szereti.Sör=Csapolja.Sör
  AND Csapolja.Kocsma IN
  ( SELECT Odajár.Kocsma
   FROM Odajár
   WHERE Odajár.Korhely=Szereti.Korhely
  );

Feladat :
- Azon korhelyek listája, akik látogatnak olyan kocsmát, ahol csapolnak  olyan sört, amit szeret.  - belső lekérdezés nélkül
- Hova menjen Ló Béla ? Azon kocsmák listája, ahol csapolnak olyan sört, amit Ló Béla szeret.
- Kiket listáz ki a következő lekérdezés ?
SELECT Korhely
 FROM Odajár
 WHERE Kocsma NOT IN
 ( SELECT Kocsma
  FROM Csapolja
  WHERE Sör IN
  ( SELECT Sör
   FROM Szereti
   WHERE Szereti.Korhely=Odajár.Korhely
  )
 );

Összesítő operátorok

Operátorok : AVG, COUNT, SUM, MIN, MAX
Szintaktika :  aggr_op(<kifejezés>)
   aggr_op(DISTINCT <kifejezés>)

Pl. - A vásárlók egyenlegének átlaga
 SELECT AVG(Egyenleg)
  FROM Vásárló ;

     - A zablát gyártók száma
 SELECT COUNT(*)  #ZablaGyártók
  FROM Szállító
  WHERE Termék = 'Zabla' ;

     - Azok száma, akik rendeltek már Nyerget
 SELECT COUNT(DISTINCT Rendelés.Vnév)
  FROM Rendelés, Tétel
  WHERE Tétel.Termék = 'Nyereg'
   AND Tétel.Rsz = Rendelés.Rsz ;

Az összesítő művelet a WHERE -el kiválasztott összes rekordra számítódik ki. Ebben az esetben az eredménylistában csak konstansok szerepelhetnek és összesítő operátorok.

Az összesítés elvégezhető csoportosítás alapján is :

 GROUP BY A1,A2, ... Ak

Két rekord akkor kerül egy csoportba az összesítéshez, ha A1, ... Ak attribútumaikban megegyeznek. Ezeknek az attribútumoknak az eredménylistában is szerepelniük kell, hogy az eredmény értelmezhető legyen.

Pl. - a termékek és azok átlagos árának listája
 SELECT Termék, AVG(Ár) Átlagár
  FROM Szállító
  GROUP BY Termék ;

A csoportokat is szűrhetjük a jellemzőik alapján :
 GROUP BY A1,A2, ... Ak
 HAVING ?

 ? : logikai kifejezés, amely a csoportokra értékelődik ki

Pl. - azon termékek neve, szállítóinak száma és átlagára, amelyet legalább ketten    szállítanak
 SELECT Termék, COUNT(*) Darab, AVG(Ár) Átlagár
  FROM Szállító
  GROUP BY Termék
  HAVING COUNT(*) > 1 ;

Pl. Azon korhelyek listája, akik csak olyan kocsmába járnak, ahol csapolnak olyan sört, amit szeret.
 SELECT Korhely
  FROM Szereti
  WHERE 0 =
   (SELECT Count (*)
    FROM Odajár
    WHERE Odajár.Korhely = Szereti.Korhely
    AND NOT Szereti.Sör  = ANY
     ( SELECT Sör
     FROM Csapolja
     WHERE Csapolja.Kocsma=Odajár.Kocsma
     )
   ) ;

Feladat :
- Azon vásárlók száma, akik még nem rendeltek Lókötőt
- Lista a termékekről és azok számáról, akik már rendeltek belőle

Adatmódosítás

1. Új rekord beszúrása egy táblázatba

INSERT INTO R [(<oszloplista>)]
 VALUES (<értéklista>) ;

Ha nem adunk meg oszloplistát, akkor minden oszlopnak értéket kell adni, a definiálásnál megadott sorrend szerint.

Pl. -egy új vevő beszúrása, ismeretlen cím esetén
 INSERT INTO Vásárló (Vnév, Egyenleg)
  VALUES ('Kiss Béla', 0 ) ;

Egy táblázathoz hozzáfűzhetjük egy lekérdezés eredményét is, természetesen az oszlopok adattípusának figyelembevételével.

NYERGES (Termék, Ár)  - A Nyerges KTSZ. árúinak adatait tartalmazó         létező táblázat

 INSERT INTO Nyerges
  SELECT Termék, Ár
   FROM Szállító
   WHERE SzNév='Nyerges KTSZ' ;

2. Rekord törlése táblázatból
 DELETE FROM R
  WHERE ? ;

Pl. - A 42-es nyerget tartalmazó rendelések törlése
 DELETE FROM Rendelés
  WHERE Rsz IN
  ( SELECT Rsz
   FROM Tétel
   WHERE Termék  = 'Nyereg / 42'
  ) ;

3. Rekord módosítása a táblázatban

 UPDATE R
  SET <attr1=érték1>, .... , <attrn=értékn>
  WHERE ? ;

Pl. A nyerges KTSZ 10% -al leértékelte a termékeit

 UPDATE Szállító
  SET Ár=0.9*Ár
  WHERE SzNév='Nyerges KTSZ' ;
 

DDL utasítások az SQL -ben

1. Adatbázis létrehozása, törlése

A valós adatbázis-kezelő rendszereknél eltérő megoldás

DBASE IV :  CREATE DATABASE <adatbázisnév> ;
    DROP DATABASE <adatbázisnév> ;

ORACLE  :   mivel az adatbázis-kezelő rendszer a saját adminisztrációját is az     adatbázisban végzi, ezért installáláskor létre kell hozni.

2.a. Táblázat létrehozása

 CREATE TABLE  R
  ( <attr1   Név1  Típus1 [constraint] > , ..... ) ;

Pl. - A Rendelés táblázat létrehozása

 CREATE TABLE Rendelés
  ( Rsz  Number(6) PRIMARY KEY,
     Dátum Date ,
     Termék CHAR(20)  NOT NULL
    ) ;

2.b. Táblázat törlése

 DROP TABLE R ;

4.a Látvány (VIEW) létrehozása

 CREATE VIEW V(A1,A2,....An) AS
  <lekérdezés> ;

V virtuális táblázat létrehozása. A látvány definícióját tárolja a rendszer, minden hivatkozáskor kiértékeli, tehát mindig az aktuális adatokat tartalmazza.

Pl. - A rendelések minden adatát tartalmazó látvány

 CREATE VIEW REN_TET(Név,Dátum,Termék,Mennyiség) AS
  SELECT Vnev, Dátum, Termék, Mennyiség
   FROM Rendelés, Tétel
   WHERE Rendelés.Rsz=Tétel.Rsz ;

Ha a látvány oszlopneveinek a SELECT eredménylistájának fejléce megfelelő, akkor nem szükséges megadni.
Definiálás után a látvány a lekérdezésekben úgy kezelhető, mint az alaptáblázatok.
Az adatmódosítás csak megkötésekkel lehetséges.

3.b.  Látvány törlése

 DROP VIEW V ;

Látvány törlésekor az alaptáblázatok nem törlődnek.

Feladat

- A vásárlók nevét és az általuk leadott rendelések számát tartalmazó lista
- A vásárlók nevét és az általuk rendelt termékek értékét tartalmazó lista

Az SQL használata

- prompt (dBase IV SQL mode, SqlPlus ...)
- az adatbázis-kezelő rendszer segédprogramjaival (sqlquery, sqldba ...)
- saját programokból

Saját programból való használat

Relációs adatbázisban tárolt adatokat (is) használó, valamely gazdanyelven írt program. A program olyan Sql hívásokat tartalmaz, amely az adatbázist kezeli.
A program saját adatterülete és a relációs adatbázis közti adatcsere biztosított.

Gazdanyelv lehet : C , Pascal , Fortran, Ada ....

 ( prog.pc )     Sql hívások

   PRECOMPILER
 

 ( prog.c )     Sql library hívások
 

      COMPILER
 

  LOADER/LINKER           SQL  Library
 

 ( prog )

Adatcsere a program és az adatbázis között :

 a., közös változókon keresztül, amelyek az Sql hívásokban konstansként       szerepelhetnek. Külön deklarációjuk van.
      Hivatkozás az sql utasításokban   -     :<változónév>

  EXEC SQL BEGIN DECLARE SECTION ;
   .......
  EXEC SQL END DECLARE SECTION ;

 b., kommunikációs területen keresztül
      Az adatbázis-kezelő üzenetei itt tárolódnak, kezelésére saját hibakezelő
      rutinok léteznek.

A végrehajtás ideje alapján megkülönböztethetünk :

 a., Azonnal végrehajtásra kerülő utasítás

  EXEC SQL EXECUTE IMMEDIATE S  ;

  ahol S nem lekérdezés
       Pl.
  EXEC SQL EXECUTE IMMEDIATE
   INSERT INTO Rendeles
    VALUES (:rendsz, :nap , :nev) ;

 b., előkészítés után végrehajtásra kerülő utasítás

  EXEC SQL PREPARE U FROM   < S  | :t > ;
   ....
  EXEC SQL EXECUTE U USING :a1, :a2 ..., :ak ;
 

  ahol   U  - hivatkozás az utasításra
   S - SQL utasítás, EXECUTE végrehajtás esetén nem lehet         SELECT
   :t - karakteres változó, amely az utasítást mint string           tartalmazza. EXECUTE végrehajtás esetén nem lehet                   SELECT
   :a1, :a2 ..., :ak : az utasításban használt változók listája

  Hatékony többször is végrehajtásra kerülő utasítások esetén, mert   ebben az esetben csak egyszer kell értelmezni az utasítást, míg    azonnali végrehajtás esetén minden alkalommal.

Lekérdezés (SELECT) beágyazott használata

A lekérdezés eredményére történő hivatkozás változók használatával történik.
A több eredménysort adó lekérdezések kezelése un. CURSOR -okon keresztül.
A cursor használatával "végig tudunk lépkedni" az eredmény sorain, az egyes sorok mezőit változókba beolvasni.

Használatának lépései :

1.  EXEC SQL PREPARE  U  FROM    S ;

2.  EXEC SQL DECLARE C CURSOR FOR U ;
3.  EXEC SQL OPEN C ;
4.  EXEC SQL WHENEVER NOTFOUND GOTO cimke ;
5.  WHILE (1) {
  EXEC SQL FETCH C INTO :a1, ... ak;
 }
6. EXEC SQL CLOSE C ;

ahol
1. a lekérdezés előkészítése,  S egy lekérdezés
2. a cursor deklarálása, C a cursor neve
3. a cursor megnyitása, a lekérdezés végrehajtása
4. a cursor kezeléséhez a "nincs több adat" deklarálása. Cursor vége esetén ugrás           a cimkére
5. az adatok ciklikus kiolvasására k mezős eredménylista esetén
6. a cursor bezárása