II. RF KMF DokuWiki

4.1 Bevezetés Az SQL (Structured Query Language)- Strukturált Lekérdező Nyelv.

Az SQL szabványos adatbázis-kezelő nyelv. A lekérdezési funkciók mellett a nyelv több olyan elemmel is rendelkezik, amelyek más adatkezelési funkciók végrehajtására is alkalmasak. A nyelv legújabb szabványos változatai, pedig már egészen kiterjedt adatbázis-kezelési műveletek megvalósítására is használhatók.

Az SQL lekérdező része megfelel a relációs algebrának, ezért alkalmas a relációs adatmodell kezelésére, és az SQL segítségével meg lehet fogalmazni a matematikailag megalapozott műveleteket. Lehetőség van az SQL parancsokba történő beépítésére, ami hatékony, gyors lekérdezési lehetőséget biztosít.

Az SQL-nek több verziója van. Általában a különböző ABKR implementációk a saját képükre formált SQL megvalósítással rendelkeznek.

Az SQL nyelv szabványai:

• az ANSI (American National Standards Institute = Amerikai Nemzeti Szabványügyi Intézet) által definiált SQL;
• SQL-92 vagy SQL2 - az 1992-ben elfogadott módosított szabvány. Később ezeket az ISO (International Standars Organization) Nemzetközi Szabványügyi Szerveze) is elfogadta;
• az SQL3.

Minden SQL parancs egy kulcsszóval kezdődik, amelyet paraméterek, esetleg további kulcsszavak követhetnek. A paraméterek általában azok az objektumok, melyekre a parancsok vonatkoznak. Ilyen lehet például egy tábla vagy attribútum neve, esetleg egy konstans, vagy valamilyen azonosító.

A parancsok lehetnek egyszerűek vagy összetettek, ami azt jelenti, hogy egy parancson belül egy másik parancs is előfordulhat.

Az SQL parancsokat két fő csoportra oszthatjuk:

• DDL (Data Defintion Language) és
• DML (Data Manipulátion Language)

parancsok. A DDL parancsok definiálják az AB objektumait, a DML parancsok, pedig végrehajtják azokkal a műveleteket. A DML egyik legfontosabb parancsaihoz tartozik a lekérdezési parancs.

4.2 Táblák létrehozása, módosítása, törlése

A tábla definiálásának parancsában meg kell adni a tábla nevét, a tábla attribútumait, azok típusát és méretét. A legfontosabb adattípusok, amelyeket az SQL szabvány definiál:

• Egész számok. Ezek megadásánál a SHORTINT, INT vagy INTEGER kulcsszavakat használhatjuk;
• Valós számok, lebegőpontos tárolással. Ebben az esetben is különböző mérető számokat adhatunk meg. A FLOAT és a REAL a hagyományos programozás nyelvekből is ismert normál lebegőpontos számot jelenti. A DOUBLE PRECISION duplapontos számot jelent, míg speciálisan az SQL-ben használhatjuk a DECIMAL(<számjegy>,<tizedesjegy>) formát is, ahol explicit módon megadhatjuk, hogy a szám hány számjegyből állhat, illetve hány tizedes-jegyet tartalmazhat.
• Fix vagy változó hosszúságú karaktersorozatok. Megadásuk a CHAR(<hossz>), illetve a VARCHAR(<hossz>) paranccsal történik. A CHAR segítségével olyan attribútumot definiálhatunk, amely pontosan a megadott hosszúságú karaktersorozatként fogja tárolni az adatokat, míg a VARCHAR esetén a megadott attribútumoknál csak az aktuális számú karakter kerül tárolásra. Látszólag a VARCHAR szolgáltatja a jobb megoldást, azonban az SQL elég rugalmas ahhoz, hogy a CHAR típust is jól kezelje.
• Dátum és idő. Ezeket a DATE és TIME kulcsszavakkal lehet megadni.

A táblákat a következő paranccsal lehet létrehozni:

  CREATE TABLE <táblanév> { <attribútumdefiníció> [,<attribútumdefiníció>]… }

Az <attribútumdefiníció> paraméterben adjuk meg az egyes attribútumok nevét és típusát, a következő módon:

  <név> <típus> [**DEFAULT** <érték>]

A <név> jelenti az attribútum nevét, míg a <típus> adja meg a típust és a méretet, a fentiekben ismertetett módon. A DEFAULT paranccsal alapértelmezett értékeket adhatunk meg az egyes attribútumoknak. Ennek használata nem kötelező.

A már létező táblát módosíthatjuk az ALTER TABLE paranccsal. Lehetőség van arra is, hogy új oszlopot adjunk. Ez a következőképpen történhet:

 ALTER TABLE <táblanév> ADD <attribútumdefiníció>

Hasonlóan történhet egy oszlop eltávolítása a táblából. Ebben az esetben elegendő az attribútum a nevét megadni:

 ALTER TABLE <táblanév> DROP <attribútumnév>

Lehetőségünk van az egész tábla törlésére is:

  DROP <táblanév>

Egy tábla definiálásakor a tábla nevén és az attribútumokon kívül egyéb információt is lehet megadni. Ilyenek a

• kulcsok
• az attribútum értékekre vonatkozó megszorítások.

Először a kulcsok, és az egyedi értékekkel bíró attribútumok megadásának módját ismertetjük. Az SQL-ben alapvetően az elsődleges kulcs megadására van lehetőségünk, ahogy azt a legtöbb ABKR megköveteli. Ha az elsődleges kulcsot szeretnénk definiálni, akkor a tábla létrehozását kibővíthetjük megfelelő opciókkal (záradékokkal). Ez a következőképpen néz ki:

 CREATE TABLE <táblanév> { <attribútumdefiníció> [UNIQUE]  [,<attribútumdefiníció> [UNIQUE]]… [,PRIMARY KEY (<kulcsattribútum> [,<kulcsattribútum>]…)|UNIQUE (<kulcsattribútum>) ]}

A UNIQUE kulcsszó segítségével minden egyes attribútumnál megadhatjuk, hogy az adott attribútum csak egyedi értékeket vehet fel. A <kulcsattribútum> paraméterben kell megadni annak az attribútumnak a nevét, amely a kulcsot alkotja, vagy annak egy részét képezi. Amennyiben csak egy attribútum tartozik a kulcshoz, akkor használhatjuk mind a PRIMARY KEY, mind a UNIQUE parancsokat. Több attribútumból álló kulcsot csak a PRIMARY KEY kulcsszóval definiálhatunk.

4.3 Idegen kulcsok alkalmazása

Az SQL lehetőséget biztosít az idegen kulcsok definiálására is. Ennek módja az alábbi:

  CREATE TABLE <táblanév> { <attribútumdefiníció> [REFERENCES <táblanév> (<attribútumnév>)] [,<attribútumdefiníció> [REFERENCES <táblanév> (<attribútumnév>)]]… [,FOREIGN KEY (<kulcsattribútum> [,<kulcsattribútum>]…) <táblanév> (<kulcsattribútum>[,<kulcsattribútum>]…) ]}

Láthatjuk, hogy az idegen kulcs megadása hasonló az elsődleges kulcshoz. A különbség mindössze annyi, hogy az idegen kulcsnál mindig meg kell adni, hogy az attribútum melyik másik tábla kulcsmezőjéhez kapcsolódik. Amennyiben az idegen kulcs egy attribútumból áll, használhatjuk a REFERENCES záradékot, ha azonban az idegen kulcs összetett, akkor a FOREIGN KEY záradékkal kell definiálnunk.

Az ABKR-nek gondoskodnia kell a hivatkozási épség fenntartásáról. Ez azt jelenti, hogy ha egy idegen kulcsban hivatkozunk egy másik táblának egy kulcsértékére, akkor a megadott értékű előfordulásnak létezni kell. Amennyiben olyan módosító, vagy törlő művelet hajtódik végre, amely a hivatkozási épséget megsérti, akkor az ABKR-nek ezt kezelnie kell.

Az ABKR-nek következő lehetőségei vannak:

• a műveletet nem engedi végrehajtani
• a műveletet megengedi, de a hivatkozási épség fenntartása érdekében automatikusan korrigálja az adatbázist

Ez a korrigálás kétféleképpen történhet.

Az egyik az, hogy ha egy hivatkozott sort törlünk, vagy módosítunk, akkor a rá hivatkozó előfordulások is törlődnek, vagy módosulnak a másik táblában

A másik lehetőség az, hogy a helytelen hivatkozásokat az ABKR egy NULL (üres) értékkel korrigálja.

Ha azt akarjuk, hogy egy korrigáló eljárás aktiválódjon a hivatkozási épség megsérülésekor, akkor azt a tábla definiálásában kell megadni. Mivel a hivatkozási épség az idegen kulcsokra vonatkozik, ezért azt csak az idegen kulcsokkal kell alkalmazni. Ezeket a megszorításokat a REFERENCES záradékban lehet megadni:

  REFERENCES <táblanév> (<attribútumnév>) [ON DELETE SET NULL| CASCADE] [ON UPDATE SET NULL| CASCADE]

Az ON DELETE részben azt adhatjuk meg, hogy a törlés során bekövetkezett hivatkozás épség sérülését hogyan kezelje a rendszer, míg az ON UPDATE részben a módosításkor bekövetkezőt. Mindkét esetben a két lehetőség közül választhatunk:

• SET NULL – a NULL érték beállítása
• CASCADE – a hivatkozó táblában is korrigálást hajtunk végre.

4.4 Megszorítások definiálása

A tábla definiálásakor más megszorítások is megadhatók. Ennek legegyszerűbb esete az, amikor a megszorítás az egyes attribútumok értékeire vonatkozik. Ezt az attribútum leírásakor kell megadni, és ennek a formája a következő: <attribútumdefiníció> NOT NULL|CHECK (<feltétel>).

A NOT NULL opció azt jelenti, hogy az adott attribútum nem vehet fel NULL (üres) értéket. A CHECK kulcsszó után tetszőleges feltételt adhatunk. Az erre vonatkozó szabályok megegyeznek a SELECT parancsban használt WHERE záradék lehetséges feltételével.

A feltétel ellenőrzése sor beszúrásakor, vagy az attribútum módosításakor történik. Ennél általánosabb megszorítások is megfogalmazhatók. Lehetnek olyanok, amelyek sorokra vonatkoznak, és lehetnek olyan globális megszorítások, amelyek a teljes adatbázisra vonatkoznak. A sorokra vonatkozó megszorítások ellenőrzése a sorban történő bármilyen módosításkor megtörténik. A teljesen általános megszorítások ellenőrzése minden olyan módosításkor bekövetkezik, aminek az adott feltételre hatása lehet. A sorra vonatkozó feltételek megadása szintén a CHECK paranccsal történik, ezt a tábla definiálásának a végén kell megadni. Formája az alábbi:

 CREATE TABLE <táblanév> { <attribútumdefiníció>   [,<attribútumdefiníció>]… [CHECK <feltétel>]}

A globális megszorítások definiálása külön kulcsszóval történik, melynek formája a következő:

 CREATE ASSERTION <név> CHECK <feltétel>

Példa. Adjuk meg azt az SQL parancsot, amely létrehozza a Dolgozó táblát!

 CREATE TABLE Dolgozó {A_dolgozó_törzsszáma CHAR(7), Név VARCHAR(50), Sz_Helye VARCHAR(30) Sz_Ideje DATE, Fizetés DECIMAL(7,0) }

Példa. Adjuk meg azt az SQL parancsot, amely létrehozza a Dolgozó táblát, és az elsődleges kulcsnak az A_dolgozó_törzsszáma mezőt definiálja!

  CREATE TABLE Dolgozó {A_dolgozó_törzsszáma CHAR(7) PRIMARY KEY, Név VARCHAR(50), Sz_Helye VARCHAR(30) Sz_Ideje DATE, Fizetés DECIMAL(7,0) }

Egy másik lehetséges megoldás:

 CREATE TABLE Dolgozó {A_dolgozó_törzsszáma CHAR(7), Név VARCHAR(50), Sz_Helye VARCHAR(30) Sz_Ideje DATE, Fizetés DECIMAL(7,0) PRIMARY KEY (A_dolgozó_törzsszáma)}

Példa. Tegyük fel, hogy a Kifizetés táblában található A_dolgozó_törzsszáma nevű mező idegen kulcs, amely a Dolgozó táblával való kapcsolatot valósítja meg. Adjuk meg azt az SQL parancsot, amely a fenti Kifizetés táblát létrehozza, úgy hogy a hivatkozási épség sérülésekor az ABKR azt automatikusan frissítéssel korrigálja!

  CREATE TABLE Kifizetés {A_kifizetés_dátuma DATE, A_kifizetett_bér DECIMAL(7,0), A_levont_adóelőleg DECIMAL(7,0), A_dolgozó_törzsszáma CHAR(7) REFERENCES Dolgozó (A_dolgozó_törzsszáma) ON UPDATE CASCADE ON DELETE CASCADE}

Egy másik lehetséges megoldás:

  CREATE TABLE Kifizetés {A_kifizetés_dátuma DATE, A_kifizetett_bér DECIMAL(7,0), A_levont_adóelőleg DECIMAL(7,0), A_dolgozó_törzsszáma CHAR(7), FOREIGN KEY (A_dolgozó_törzsszáma) Dolgozó (A_dolgozó_törzsszáma) ON UPDATE CASCADE ON DELETE CASCADE}

Példa. Adjuk meg azt az SQL parancsot, amely a Dolgozó táblát úgy definiálja, hogy a Fizetés mező esetén mindig ellenőrzésre kerüljön, hogy az éppen megadott érték eléri-e egy minimális bér összegét, mondjuk 55000 Ft-ot!

  CREATE TABLE Dolgozó {A_dolgozó_törzsszáma CHAR(7), Név VARCHAR(50),     Sz_Helye VARCHAR(30),  Sz_Ideje DATE, Fizetés DECIMAL(7,0) CHECK A_dolgozó_fizetése >55000}

Példa. Adjuk meg azt az SQL parancsot, amely egy olyan globális megszorítást definiál, amely ellenőrzi, hogy a Dolgozó táblában az összes fizetés együttesen ne haladja meg a 10.000.000 Ft-ot!

  CREATE ASSERTION Összefizetés CHECK (10000000 >= (SELECT SUM(Fizetés) FROM Dolgozó))

4.5 Táblák indexelése

Példa.

  CREATE INDEX Diak_NeveInd  ON  Students ( Diak_Neve)

A Student tábla Diak_Neve attribútum indexelve lesz, és az index neve Diak_NeveInd.

Egy másik lehetőség:

  CREATE UNIQUE INDEX Diak_NeveInd  ON  Students ( Diak_Neve)

Az index törlése:

  DROP INDEX Diak_NeveInd

Példák a CREATE parancs alkalmazására

 CREATE TABLE Jegyek  ( Azon INTEGER NOT NULL UNIQUE, Jegy INTEGER, Datum DATE, Stud_Num  INTEGER NOT NULL,

Tantargy_Num INTEGER NOT NULL, UNIQUE (Stud_Num, Tantargy_Num) )

  CREATE TABLE Jegyek  ( Azon INTEGER NOT NULL PRIMARY KEY, Jegy INTEGER, Datum DATE, Stud_Num  INTEGER NOT NULL,

Tantargy_Num INTEGER NOT NULL, UNIQUE (Stud_Num, Tantargy_Num) )

  CREATE TABLE Jegyek  ( Azon INTEGER NOT NULL UNIQUE, Jegy INTEGER, Datum DATE, Stud_Num  INTEGER NOT NULL,

Tantargy_Num INTEGER NOT NULL, PRIMARY KEY (Stud_Num, Tantargy_Num) )

  CREATE TABLE Jegyek  ( Azon INTEGER NOT NULL PRIMARY KEY, Jegy INTEGER CHECK (Jegy =<5), Datum DATE,

Stud_Num INTEGER NOT NULL, Tantargy_Num INTEGER NOT NULL, UNIQUE (Stud_Num,Tantargy_Num) )

 CREATE TABLE Jegyek  ( Azon INTEGER NOT NULL PRIMARY KEY, Jegy INTEGER CHECK IN (1, 2, 3, 4, 5), Datum DATE,

Stud_Num INTEGER NOT NULL, Tantargy_Num INTEGER NOT NULL, UNIQUE (Stud_Num,Tantargy_Num) )

  CREATE TABLE Jegyek  ( Azon INTEGER NOT NULL PRIMARY KEY, Jegy INTEGER, Datum DATE, Stud_Num  INTEGER NOT NULL,

Tantargy_Num INTEGER NOT NULL, UNIQUE (Stud_Num,Tantargy_Num) CHECK (Jegy IN ( 4, 5) AND Datum> 15/06/2001)

  CREATE TABLE Jegyek  ( Azon INTEGER NOT NULL PRIMARY KEY, Jegy INTEGER CHECK IN (1, 2, 3, 4, 5)  DEFAULT = 5,

Datum DATE, Stud_Num INTEGER NOT NULL, Tantargy_Num INTEGER NOT NULL, UNIQUE (Stud_Num,Tantargy_Num) )

  CREATE TABLE Jegyek ( Azon INTEGER NOT NULL PRIMARY KEY, Jegy INTEGER, Datum DATE, Stud_Num INTEGER NOT NULL,

Tantargy_Num INTEGER NOT NULL, FOREING KEY ( Stud_Num ) REFERENCES Students (Stud_Num))

Stud_Num – külső kulcs, kapcsolat a Students-el.
Vagy

  CREATE TABLE Jegyek ( Azon INTEGER NOT NULL PRIMARY KEY, Jegy INTEGER, Datum DATE, Stud_Num INTEGER NOT NULL 

REFERENCES Students (Stud_Num), Tantargy_Num INTEGER NOT NULL )

Ha a Students-ben a Stud_Num attribútum PRIMARY KEY, akkor a szülő-táblában azt nem kell megadni:

  CREATE TABLE Jegyek ( Azon INTEGER NOT NULL PRIMARY KEY,  Jegy INTEGER, Datum DATE, Stud_Num INTEGER NOT NULL 

REFERENCES Students, Tantargy_Num INTEGER NOT NULL )

Feladat. Adjuk meg azt az SQL parancsot, amely hozzáadja a Dolgozó táblához a dolgozó lakcímét!