# Az SQL alapjai I

Ahhoz, hogy magunkat (mint felhasználó) megérthessük a relációs adatbáziskezelővel (RDMS), szükséges feltétele egy szabványos adatlekérdező nyelv, egy adatdefiníciós nyelv, egy adatmanipulációs nyelv, és egy adatvezérlő nyelv. Az egyik ilyen (az eddig felsorolt feltételeket teljesítő) szabványosított nyelv az SQL (Structured Query Language), melyet 1970-ben E.F. Codd foglalta össze a "A Relational Model of Data for Large Shared Data Banks" című tanulmányába [15].

Az adatbázis-rendszerek egyik fontos eleme az adatbázis. A tanultak megértéséhez és kipróbálásához a Nemzeti Szakképzési és Felnőttképzési Hivatal weboldalán [17] található 54 213 05/1/6 (szakképesítés azonosító/feladatszám/sorozatszám) Szoftverfejlesztő tanfolyam adatbáziskezelés feladatából vett Törpe tárna adatbázisát használjuk majd. A letöltéshez kattintson ide.

A dombi törpék egész évben szorgosan bányásznak. Tíz tárnában dolgoznak, aranyat, vasat, ezüstöt és rezet hoznak fel a föld mélyéből. A klánokba szerveződött törpék társadalmában a nők is ugyanúgy kiveszik a munkából a részüket, mint a férfiak, azaz ők is bányásznak. Minden törpe több helyen is dolgozhatott az év folyamán, egyszer vasat keresett, máskor például aranyat. Az alábbi adatbázis a törpék bányászati adatait tartalmazza, melynél a táblák (és a kapcsolatok) a következőképpen néznek ki:

Az adatbázis-rendszerek másik fontos eleme az adatbázis-kezelő rendszer. A tananyagban az ingyenesen használható DBMS-ek között, napjainkban talán legelterjedtebbnek nevezhető MySQL v5.5 verzióját, annak online felületen futó változatát (db-fiddle) fogjuk használni, melyet innnen lehet elérni.

• Több relációs adatbáziskezelő ismeri.

• Platformfüggetlen.

• Halmazorientált nyelv.

Az adatdefiniciós nyelv tartalmazza az adatszerkezetek létrehozását biztosító nyelvi elemeket. Használatával létrehozhatunk, törölhetünk, illetve módosíthatunk adatbázisokat, relációkat, nézet táblázatokat, indexeket. A tábla szerkezetének definiálása az attribútumok és megszorítások leírásából áll. Utóbbiak alatt az indexek és idegen kulcsok definiálását kell érteni.

Az adatbázisok létrehozására a CREATE DATABASE utasítást használhatjuk, melynek szintaxisa a következő:

CREATE DATABASE adatbázisnev
[[DEFAULT] CHARACTER SET karakterkeszlet]
[[DEFAULT] COLLATE rendezes];

Mindegyik adatbázisnak van egy alapértelmezett karakter kódolása és rendezése. Az adatbázis kódolás beállításának az a szerepe, hogy alapértelmezett (DEFAULT) értéket nyújtson táblák létrehozásakor. Amennyiben ezeket az információkat elhagyjuk, az adatbázis az éppen aktuális szerver kódolással, rendezéssel jön létre.

Egy már létező adatbázis kódolását az ALTER DATABASE parancs segítségével, melynek szintaxisa a következő:

ALTER DATABASE adatbázisnev
[[DEFAULT] CHARACTER SET karakterkeszlet]
[[DEFAULT] COLLATE rendezes];

Teljes adatbázisok törlésére is lehetőségünk van a DELETE DATABASE utasítással, melynek szintaxisa a következő:

DROP DATABASE adatbazisnev

Hozzuk létre a gyakorló adatbázisunkat torpetarna névvel utf8 karakterkészlettel és utf8_hungarian_ci rendezéssel az alábbi paranccsal:

CREATE DATABASE IF NOT EXISTS `torpetarna` 
DEFAULT CHARACTER SET utf8 
COLLATE utf8_hungarian_ci;

A relációk létrehozására a CREATE TABLE utasítást használhatjuk, melynek szintaxisa a következő:

CREATE TABLE relacionev
(attributumnev adattípus [(meret)] [CONSTRAINT megszorításnev] [attributummegszoritas],
attributumnev adattípus [(meret)] [CONSTRAINT megszorításnev] [attributummegszoritas],... ) 
[CONSTRAINT megszorításnev] [tablamegszoritas];

Mindegyik relációnak be van állítva a karakter kódolása és a rendezése. Egy reláció létrehozásakor lehetőségünk van ennek beállításával az alábbi szintaxis mellett:

CREATE TABLE relacionev
(attributumnev adattípus [(meret)] [CONSTRAINT megszorításnev] [attributummegszoritas],
attributumnev adattípus [(meret)] [CONSTRAINT megszorításnev] [attributummegszoritas],... ) 
[CONSTRAINT megszorításnev] [tablamegszoritas]
[DEFAULT CHARACTER SET karakterkeszlet [COLLATE rendezes]];

A reláció létrehozása során, ha egy adott attribútumnál nem definiálunk külön karakterkészletet illetve rendezést, akkor a relációnak beállított karakterkészlettel jönnek létre az attribútum értékek. Egy attribútum karakterkészlete és rendezése minden további nélkül eltérhet azonban a reláció beállított alapértelmezett értékektől.

A relációs adatbáziskezelőkben lehetőség van beállítani az attribútumok adattípusát. Ezek közül megkülönböztetjük a bináris, szöveges, numerikus, dátum és idő típusú adattípusokat. Amennyiben az attribútum adattípusát már megadtuk, ettől eltérő típussal rendelkező adat nem vihető be.

A relációs adatbáziskezelőkben lehetőség van beállítani a megszorításokat (adatintegritás). Az adatintegritás az adatok megbízhatóságát, hitelességét és inkonzisztencia mentességet biztosítja. A megszorítások különböző szintjeit különböztetjük meg:

• Attribútum megszorítások: egy attribútumra vonatkozó érvényes érték előfordulások elfogadhatóságát vizsgálja meg.

• Rekord megszorítások: egy teljes rekordon belül az egymáshoz kapcsolódó attribútum értékek elfogadhatóságát vizsgálja.

• Reláció megszorítások: egy teljes reláción belül az összes rekord előfordulás elfogadhatóságát vizsgálja.

• Adatbázis megszorítások: egy teljes adatbázison belül az összes reláció összes attribútumának elfogadhatóságát vizsgálja.

A reláció módosítására az ALTER TABLE parancsot használhatjuk. A módosítás jellege legyen a reláció egy új attribútummal való bővítése, melynek szintaxisa a következő:

ALTER TABLE relacionev ADD attributumnev adattipus [(meret)];

Teljes relációk törlésére is lehetőségünk van a DROP TABLE utasítással, melynek szintaxisa a következő:

DROP TABLE relacionev;

A törpék adatai közé vegyük fel a születési évet (mint attribútumot) (szul_ev) a torpek relációba az alábbi paranccsal:

ALTER TABLE torpek ADD szul_ev INT (4);

Az attribútum alapértelmezett értéke a már meglevő sorokban NULL lesz.

Egy reláció karakterkészletének és rendezésének megváltoztatásához az ALTER TABLE parancsot használhatjuk, melynek szintaxisa a következő:

ALTER TABLE relacionev DEFAULT CHARACTER SET karakterkeszlet [COLLATE rendezes]; 

A relációkhoz indexeket is rendelhetünk, amelyek helyes megválasztása esetén a lekérdezések felgyorsíthatók. Az indexek létrehozására a következő utasítás szolgál: Az indexek a CREATE [modosito] INDEX paranccsal hajthatjuk végre, melynek szintaxisa a következő:

CREATE [modosito] INDEX indexnev ON relacionev (attributumnev, attributumnev, ...);

Az index létrehozásánál a UNIQUE módosító megadásával a reláció valamennyi sorában különbözőnek kell lennie az index kulcsnak. Hozzunk létre egy kozet nevezetű indexet a kozetek reláció nev attribútumára az alábbi paranccsal:

CREATE UNIQUE INDEX kozet ON kozetek (nev);

A parancsban megadott UNIQUE következtében a kozetek reláció minden sorában különböző névnek kell szerepelnie. Már létező érték bevitele hibát fog eredményezni.

Az indexek törlésére lehetőségünk van a DROP INDEX utasítással, melynek szintaxisa a következő:

DROP INDEX indexnev ON relacionev;

A kozetek nevezetű attribútum indexét a következő paranccsal törölhetjük ki:

DROP INDEX `nev` ON `kozetek`;

A relációkhoz elsődleges kulcsot is rendelhetünk, mely az ALTER TABLE paranccsal hajthatjuk végre, melynek szintaxisa a következő:

ALTER TABLE relacionev ADD CONSTRAINT megkotesnev PRIMARY KEY (attributumnev);

A fenti parancs akkor fog működni, amennyiben az adott attribútumhoz van már társítva NOT NULL megkötés. Az elsődleges kulcs megkötést a tábla létrehozásával együtt szokták definiálni. A gyakorló adatbázisban a relációk eleve így vannak definiálva. Az egyik megközelítés szerint azon attribútumnál, melyet elsődleges kulcsnak szeretnénk megjelölni, az Auto_increment megkötést is szokták használni annak érdekében, hogy egy reláción belül garantálja az adatbázis-kezelő az egyedi rekordok jelenlétét. Ehhez az elsődleges kulcsnak értelemszerűen egész számnak kell lennie.

Az elsődleges kulcsok törlésére lehetőségünk van a DROP TABLE utasítással, melynek szintaxisa a következő:

ALTER TABLE relacionev DROP CONSTRAINT megkotesnev;

A relációkhoz idegen kulcsot is rendelhetünk, mely az ALTER TABLE paranccsal hajthatjuk végre, melynek szintaxisa a következő:

ALTER TABLE relacionev ADD CONSTRAINT megkotesnev 
FOREIGN KEY (attributumnev) 
REFERENCES relacionev(attributumnev) 
ON DELETE kapcsolattipus 
ON UPDATE kapcsolattipus;

Az idegen kulcs megkötést a tábla létrehozásával együtt szokták definiálni. A gyakorló adatbázisnál az idegen kulcsok külön utasítással vannak beállítva.

Az idegen kulcsok törlésére lehetőségünk van a DROP TABLE utasítással, melynek szintaxisa a következő:

ALTER TABLE tablanev DROP FOREIGN KEY megkotesnev;

A nézettáblák olyan virtuális relációk az adatbázisban, melyek adatot nem tárolnak és származtatott adatokat tartalmaznak az adatbázisban jelen lévő relációkból. Alkalmazás szempontjából például felhasználhatóak jogosultsági szinteknek megfelelő relációk kreálására. A nézettáblákat a CREATE VIEW paranccsal hajthatjuk végre, melynek szintaxisa a következő:

CREATE VIEW nezettablanev [(attributumnev, attributumnev,...)] AS lekerdezes;

Ahogy láthatjuk, a lekerdezes nevezetű lekérdezés eredmény-relációja fogja alkotni a nézettáblát. Értelemszerűen a nézettáblán (néhány esetet kivéve) ugyanazon műveletek is végrehajthatóak, mint a "felhasznált" táblázatokon. Amennyiben a nézettábla megváltozik, a vele kapcsolatban álló táblák is megváltoznak.

Hozzunk létre egy tuzeskoho elnevezésű nézettáblázatot, ahol a felhasznált eredménytábla legyen a TüzesKohó klán névsora az alábbi paranccsal:

CREATE VIEW tuzeskoho AS SELECT * FROM torpek WHERE klan = 'TüzesKohó';

A nézettáblák törlésére lehetőségünk van a DROP VIEW utasítással, melynek szintaxisa a következő:

DROP VIEW nezettablanev;

A tuzeskoho nevezetű nézettáblát a következő paranccsal törölhetjük ki:

DROP VIEW tuzeskoho;

Az adatmanipulációs nyelv tartalmazza az új rekordok beszúrásához és törléséhez, az attribútumok módosításához szükséges nyelvi elemeket.

A relációk rekordokkal való feltöltésére lehetőségünk van a INSERT utasítással, melynek szintaxisa a következő:

INSERT INTO relacio [(attributumnev, attributumnev, ...)] 
VALUES (attributumertek, attributumertek, ...);

Lehetőségünk van arra is, hogy az attribútumnevek felsorolása nélkül felvigyük az adott relációhoz a rekordokat. Ekkor arra kell ügyelnünk, hogy az egyes attribútumokhoz társított attribútum értékek az attribútumok definálásának sorrendjét kövessék. A másik fontos kritéreim az adatok sikeres felvtelére az, hogy az attribútum értékek adattípusa és az attribútumoknál beállított adattípusa megegyezzen. Ellenkező esetben hibaüzenetet kapunk. Ahogy a megkötések közt korábban láttuk, az attribútumoknál lehetőségünk van beállítani azt, hogy kötelezővé tegyük-e az attribútumérték megadását új rekord felvételekor, vagy sem. Az előbbi esethez a NOT NULL megkötést, az utóbbihoz pedig a NULL megkötést illik választani.

A torpek relációhoz az alábbi paranccsal adjunk hozzá egy új törpöt (és azok adatait):

INSERT INTO torpek (nev, klan, nem, suly, magassag) 
VALUES ('Trad Morf', 'Vasököl', 'férfi', 69, 136)

A szul_ev attribútum értékét nem adtuk meg, így értéke NULL.

Ahhoz, hogy egy létező relációban szereplő attribútum értékeket megváltoztassuk, az UPDATE parancsot érdemes használni az alábbi szintaxis mellett:

UPDATE relacionev SET 
attributumnev = attributumertek, attributumnev = attributumertek, ... 
[WHERE feltetel];

A fenti szintaxissal egyszerre nemcsak egy rekord módosítható, hanem több is. A WHERE utáni feltétel azon rekordokat szűrhetjük le a módosításhoz, melyekre a feltétel értéke igaz. Amennyiben a WHERE utáni feltételt elhagyjuk, akkor a reláció összes rekordjára érvényes lesz az UPDATE utasítás.

A torpek relációban az alábbi paranccsal az összes suly-t állítsuk be 55 kg-ra:

UPDATE torpek SET suly = 55;

A torpek reláció azon rekordjánál az alábbi paranccsal módosítsuk a törp magasságát 136-ra, ahol a diak_azonosito = 25 feltétel teljesül:

UPDATE turpek SET magassag = 136 WHERE diak_azonosito = 25;

A relációk rekordainak törlésére lehetőségünk van a DELETE utasítással, melynek szintaxisa a következő:

DELETE FROM relacionev [WHERE feltetel];

A fenti szintaxissal egyszerre nemcsak egy rekord törölhető, hanem több is. A WHERE utáni feltétel azon rekordokat szűrhetjük le a törléshez, melyekre a feltétel értéke igaz. Amennyiben a DELETE utáni feltételt elhagyjuk, akkor a reláció összes rekordjára érvényes lesz a DELETE utasítás.

A torpek relációból az alábbi paranccsal töröljük ki a 40-es azonosítójú törpöt:

DELETE FROM torpek WHERE id = 40;

A kérdés csak az, hogy megtehetjük-e ezt az adatbázis konzisztenciájának elvesztése nélkül? Ha az kihol relációban szerepel a törölt törpe azonosítója, akkor ezek inkonzistenssé teszik az adatbázisunkat. Helyesebb az alábbi két törlő parancsot kiadni, ha semmi szin alatt sem szeretnénk elveszteni az adatbázis konzisztenciáját:

DELETE FROM torpek WHERE id = 40;
DELETE FROM kihol WHERE torpe_id = 40;