Az SQL nyelv

Az SQL nyelv a relációs adatbázis-kezelők kezelő nyelve. A nyelvet eredetileg SEQUEL-nek akarták nevezni, később ezt egyszerűsítették le SQL-re. A SEQUEL szó a Structured English Query Language szavakból képzett mozaikszó.

Ugyan az English kifejezés kikerült a végleges elnevezésből, de továbbra is a nyelv alapja nagy mértékben az angol.

Nyelvi érdekesség, hogy adatbázis-kezelő rendszertől függetlenül az alapműveletek többé-kevésbé hasonló módon vannak megvalósítva, azonban minden adatbázis-kezelő rendszernek vannak egyedi tulajdonságai és nyelvi kiegészítései. Ebből adódóan a különböző rendszerek magas szintű utasítások és néha nyelvi okok miatt is inkompatibilisek egymással.

Ebből adódóan a könyvben az SQL nyelv esetén az SQLite implementációval dolgozunk. Az SQLite nem egy teljes értékű adatbázis-kezelő. Ez alatt az értendő, hogy fejlesztésekor sosem az volt a cél, hogy egy baromi nagy, mindenre kiterjedő motort hozzanak létre. Helyette egy olyan kis méretű adatbázis motort szerettek volna megvalósítani, amely beágyazható a kész programba, így külön telepítést nem igényel. Ezen célkitűzést sikeresen megvalósították. Az SQLite igen elterjedt, számos szoftverben alkalmazzák és szinte minden programozási nyelvhez elérhető.

Az SQL nyelv négy fő részre bontható: adatdefiníciós, adatkezelési, lekérdező és adatvezérlő részekre.

A nyelv nem különbözteti meg a kis-és nagy betűket, azonban a jobb olvashatóság és az egyértelműség kedvéért a nyelvi parancsokat csupa nagybetűvel szokás írni.

Nyelvi jellemző, hogy egy kérés pontosvesszővel végződik. Ez lehetővé teszi azt, hogy egy kérés tetszőleges számú sorból álljon. Egyes rendszerek esetén a pontosvessző kitétele nem kötelező, mivel vagy egy kérést hajtanak csak végre egy időben, vagy a kérésnek egy sorban kell lennie. Az itteni példák mindegyikében a kérések pontosvesszővel végződnek.

A tárolható mezők típusai sokfélék lehetnek. Talán itt van a legnagyobb eltérés adatbázis-kezelő rendszerenként. Az SQLite típuskészlete az alábbi táblázatban található:

Típus neve	Leírás
NULL	Üres típus
INTEGER	Egész számok
REAL	Valós, lebegőpontos számok
TEXT	Szöveges adat
BLOB	Bináris adat

Az SQLite típusszerkezete dinamikus, ami azt jelenti, hogy nem az oszlophoz van tárolva a típus és az adatok aztán belekényszerítve, hanem rekordonként van tárolva a típus. Ez lehetővé teszi azt, hogy az adatbázis méret szempontjából skálázódjon. Például az INTEGER típus a beírt számtól függően 1, 2, 4, 6 vagy 8 byte-on kerül tárolásra.

A REAL típus lebegőpontos számokat tárol IEE 754 szerint, dupla pontossággal, 8 byte-on. A TEXT típus szöveges adat tárolására szolgál, méret korlát nincs. A szövegek UTF kódolással kerülnek tárolásra.

Logikai Boolean típus nincs SQLite esetén, az ilyen értékek 0 vagy 1 értékként vannak tárolva egész szám formátumban, de mező létrehozáskor alkalmazható a boolean típus jelölő. Valódi Dátum típus sincs. Dátum és idő értékek tárolására a TEXT típus vagy az INTEGER típus alkalmazható.

Más adatbázis-kezelő rendszerekkel való kompatibilitás miatt támogat további típus jelzőket is, azonban ezeket a típus jelzőket át fogja konvertálni a saját belső típusaiba.

A REAL típus lebegőpontos számokat tárol IEE 754 szerint, dupla pontossággal, 8 byte-on. A TEXT típus szöveges adat tárolására szolgál, méret korlát nincs. A szövegek UTF kódolással kerülnek tárolásra.

Logikai Boolean típus nincs SQLite esetén, az ilyen értékek 0 vagy 1 értékként vannak tárolva egész szám formátumban, de mező létrehozáskor alkalmazható a boolean típus jelölő. Valódi Dátum típus sincs. Dátum és idő értékek tárolására a TEXT típus vagy az INTEGER típus alkalmazható.

Más adatbázis-kezelő rendszerekkel való kompatibilitás miatt támogat további típus jelzőket is, azonban ezeket a típus jelzőket át fogja konvertálni a saját belső típusrendszerére, méghozzá az alábbi módon:

Típus	Leírás
INT, INTEGER, TINYINT, SMALLINT, MEDIUMINT, BIGINT, UNSIGNED BIG INT, INT2, INT8	INTEGER
CHARACTER(20), VARCHAR(255), VARYING CHARACTER(255), NCHAR(55), NVARCHAR(100), TEXT, CLOB	TEXT
BLOB	BLOB
REAL, DOUBLE, DOUBLE PRECISION, FLOAT	REAL
NUMERIC, DECIMAL(10,5), BOOLEAN, DATE, DATETIME	NUMERIC

A NUMERIC típus az úgynevezett Joker típus. Az ilyen típusú adatok bármire konvertálódhatnak az adat felépítésétől függően. Például a 3000-es érték INTEGER mezőként realizálódik, azon belül is 2 byte-on, mivel 16 biten bőven kifejezhető az érték. Ellenben ha a C# decimal típusát szeretnénk tárolni, akkor az TEXT típusú mezőben fog tárolódni.

Az SQL három állapotú logikája

Az SQL nyelv három állapotú logikát alkalmaz, ami az üres típus miatt szükséges. Az alap logikai műveletek igazságtáblái így a következőképpen módosulnak:

Háromállapotú ÉS művelet

A \ B	Igaz	Hamis	null
Igaz	Igaz	Hamis	null
Hamis	Hamis	Hamis	Hamis
null	null	Hamis	null

Háromállapotú VAGY művelet

A \ B	Igaz	Hamis	null
Igaz	Igaz	Igaz	Igaz
Hamis	Igaz	Hamis	null
null	Igaz	null	null

Háromállapotú NEM művelet

Igaz	Hamis
Hamis	Igaz
null	null

Háromállapotú egyenlőség művelet

A \ B	Igaz	Hamis	null
Igaz	Igaz	Hamis	null
Hamis	Hamis	Igaz	null
null	null	null	null

A null érték null értékhez hasonlítva null eredményt ad. Ennek az az oka, hogy a null értékű mező nem rendelkezik valódi értékkel, inkább egy helyőrzőként viselkedik.

A null értékek kezelésére és tesztelésére az SQL nyelvben az IS NULL és az IS NOT NULL kiegészítések használhatóak.

Táblák, adatbázisok létrehozása

Több adatbázist kezelő motorok, rendszerek esetén először is adatbázisra van szükségünk. SQLite esetén az adatbázis kiválasztása és létrehozása a fájl betöltésével és létrehozásával van megvalósítva, így ott nem kell létrehoznunk külön parancs segítségével. Adatbázis létrehozásra használt parancs:

CREATE DATABASE adatbazis;
USE adatbazis;

Az adatbázist létrehozása után használatba is kell venni. Ezt valósítja meg a példa második sora. Adatbázis létrehozásánál megadható még az is, hogy csak akkor hozza létre az adatbázist a rendszer, ha még az nem létezik. Ennek szintaxisa:

CREATE DATABASE IF NOT EXIST adatbazis;

Táblák szintén a create paranccsal hozhatóak létre:

CREATE TABLE tablaneve (
    mező típus,
    mező2 típus,
);

A mezők és típusok mellett kényszerek is megadhatóak az adott mezőkre. A kényszerek olyan szabályrendszerek, amelyek a mezőkre alkalmazva igen különfélék lehetnek. A leggyakoribb kényszerek, amik megadhatóak:

Kényszer neve	Leírás
NOT NULL	Azt jelzi, hogy az adott mező értéke sosem lehet NULL. Ha NULL értéket akarunk a mezőbe tenni, akkor a kérés nem fog lefutni.
UNIQUE	Azt jelzi, hogy a mező értékének egyedinek kell lennie. Amennyiben nem egyedi, akkor a kérés nem fog lefutni.
PRIMARY KEY	Elődleges kulcsként jelöli meg a mezőt. A NOT NULL és UNIQUE kényszert vonja magával.
FOREIGN KEY	Külső kulcsra hivatkozás
CHECK	Adat ellenőrzés tárolás előtt.
DEFAULT	Alapértelmezett érték megadása
AUTO_INCREMENT	Az érték automatikus növelése. Elsődleges kulcsok esetén szokták alkalmazni.

A korábban megtervezett adatbázis tábláinak létrehozó parancsa:

CREATE TABLE Kiado(
	Nev TEXT,
	Cim TEXT,
	PRIMARY KEY (NEV));

CREATE TABLE Szerzo(
	SZID INT AUTO_INCREMENT,
	Keresztnev TEXT NOT NULL,
	Vezeteknev TEXT NOT NULL,
	PRIMARY KEY (SZID)
);

CREATE TABLE konyv(
	ISBN INT,
	Cim TEXT,
	EV INT,
	KiadoNev Text,
	SZID INT
	PRIMARY KEY (ISBN),
	FOREIGN KEY (SZID) REFERENCES Szerzo(SZID),
	FOREIGN KEY (KiadoNEv) REFERENCES Kiado(Nev),
);

Táblák módosítása, törlése, ürítése

A táblák törlése és ürítése közötti lényeges különbség, hogy az ürítés csak a tábla tartalmát törli, maga a tényleges törlés parancs a tábla tartalmát és a táblát is törli. Adatokat törölni egy táblából a delete parancs segítségével tudunk:

DELETE FROM tábla neve;

A fenti parancs minden adatot töröl a megadott nevű táblából. A törlés finomítható szűrő feltételek megadásával, amire a parancs WHERE szekciója szolgál:

DELETE FROM tábla neve WHERE feltételek;

A feltételek igen sokfélék lehetnek. Operátorokkal lehet megadni őket. Az SQL nyelv operátorait az alábbi táblázat mutatja be:

Operátor	Leírás
=	Egyenlőség tesztelése
<>	Nem egyenlő
>	Nagyobb, mint
<	Kisebb, mint
<=	Kisebb, vagy egyenlő
>=	Nagyobb vagy egyenlő
BETWEEN	Értéktartomány meghatározása. Például 10 és 20 között: BETWEEN 10 AND 20
LIKE	Szöveges keresés, pontosabban hasonlóság keresése
IN	Több keresési érték meghatározása egy adott oszlophoz

Több feltétel is megadható. A feltételek között logikai kapcsolatokkal prioritást kell felállítani. A lehetséges logikai kapcsolatok az ÉS (AND kulcsszó), VAGY (OR kulcszó) vagy NEM (NOT kulcsszó).

A könyv táblából azon sorok törlése, ahol az év 1970 és 1975 között van:

DELETE FROM Konyv WHERE Ev BETWEEN 1970 AND 1975;

A könyv táblából azon sorok törlése, ahol a kiadó a Jó kiadó és az év nagyobb, mint 2011:

DELETE FROM Konyv WHEE Kiado LIKE ”Jó kiadó” AND Ev > 2011;

Amennyiben sémával együtt szeretnénk törölni egy táblát, akkor a DROP parancs használandó:

DROP TABLE táblanév;

Tábla módosítás alatt a tábla sémájának módosítása értendő. Erre akkor lehet szükség, ha a táblát bővíteni szeretnénk további mezőkkel. A séma módosítása annál több időt fog igénybevenni, minél több adat van tárolva a táblában. Séma módosításra az ALTER kulcsszó használható. Séma módosításnál oszlopokat adhatunk hozzá és oszlopokat törölhetünk, illetve meglévő oszlopok típusát módosíthatjuk.

Oszlop hozzáadásának szintaxisa:

ALTER TABLE táblanév ADD oszlopnév oszlop típus;

Oszlop törlése:

ALTER TABLE táblanév DROP COLUMN oszlopnév;

Oszlop típusának módosítása:

ALTER TABLE táblanév ALTER COLUMN oszlopnév oszlop típus;

Tábla módosítási műveletek közé tartozik még az utólagos kényszer hozzáadás és eltávolítás:

ALTER TABLE ADD kényszer (mezőnév);
ALTER TABLE DROP kényszer (mezőnév);

Értékek rögzítése, frissítése

Értékeket az INSERT parancs segítségével tudunk rögzíteni. Itt két támogatott szintaxis van:

INSERT INTO táblanév VALUES (Érték1, Érték2, Érték3);
INSERT INTO táblanév (Oszlop1, Oszlop2, Oszlop3) VALUES (Érték1, Érték2, Érték3);

Az első szintaxis esetén az összes mezőnek az értékét meg kell adnunk, míg a második szintaxis esetén csak a táblanév után meghatározott oszlopok értékeit kell megadni. A második szintaxis esetén a kihagyott mezők NULL értékkel kerülnek rögzítésre, vagy ha a mezőn lett beállítva DEFAULT kényszer, akkor annak az értéke fog bekerülni. Az INSERT kérés lefutását a tábla létrehozásakor beállított kényszerek meggátolhatják.

Frissíteni az UPDATE parancs segítéségével lehet. Az UPDATE működése hasonlít a DELETE működéséhez. Alapértelmezetten feltételek nélkül az összes rekordot frissíti. Az UPDATE szintaxisa a következő:

UPDATE TABLE SET oszlop=érték, oszlop2=érték2;
UPDATE TABLE SET oszlop=érték, oszlop2=érték2 WHERE feltételek;

Lekérdezések

A lekérdezések segítségével hasznos adatokat nyerhetünk ki az adatbázisunkból. Táblázatszerűen adnak vissza adatot, azonban a SELECT által visszaadott adatok sosem kerülnek külön tárolásra az adatbázisban. Alapvetően a SELECT parancs szintaxisa a következő:

SELECT mezőnevek FROM táblanév;
SELECT mezőnevek FROM táblanév WHERE feltételek;

A mezőnevek helyére ha *-ot írunk, akkor az adott tábla összes mezője lekérdezésre kerül. Szűrő feltételek nélkül az összes rekord visszaadásra kerül. A szűrő feltételekre ugyanazok a szabályok vonatkoznak, mint UPDATE és DELETE esetén.

A lekérdezések során lehetőségünk van sorba rendezésre és az eredmények, találatok számának limitálására is. A sorba rendezés szintaxisa:

SELECT mezőnevek FROM táblanév WHERE feltételek ORDER BY mezőnév sorrend.

A kérésben a sorrend helyére ASC vagy DESC írandó. Az ASC növekvő sorrendet állít, a DESC pedig csökkenő sorrendet. Több mező alapján is rendezhetünk. Ebben az esetben az ORDER BY kitétel után a mezőneveket és sorrendeket vesszővel elválasztva kell megadni. A rendezés prioritásos, balról jobbra haladva lesznek végrehajtva a rendezések.

Az eredmény rekordok számának a korlátozására a TOP kulcsszó lett bevezetve:

SELECT TOP limit FROM táblanév;

A TOP parancs tetszőlegesen kombinálható a többi SELECT kitétellel, de számos adatbázis-kezelő rendszerben nem támogatott. Ez nem jelenti azt, hogy nem lehet limitálni a találatok számát, csupán azt jelenti, hogy másként van megvalósítva.

A másként megvalósítást a LIMIT kulcsszóval lehet még megoldani. Ezt a megoldást használja a MySQL rendszer, míg a TOP kulcsszó Microsoft termékek esetén jellemző. A LIMIT szintaxisa:

SELECT mezőnevek FROM táblanév LIMIT limit;

A LIMIT szintén tetszőlegesen kombinálható a többi SELECT parancs segítségével.

Lehetőségünk van dinamikusan legenerált adatok lekérdezésére is az adattáblából a SELECT utasítás segítségével. Ezt általánosan aggregációnak nevezzük.

Aggregációs függvényekből adatbázis-kezelő rendszertől függően rengeteg létezik. Talán a rendszerek között itt található a legnagyobb eltérés. A leggyakoribb SQL függvények, amelyeknek rendszertől függetlenül működniük kellene:

Függvény	Leírás
AVG()	Paraméterben megadott mező átlagolása
COUNT()	Sorok számolása megadott mező alapján
MAX()	Paraméterben megadott mező maximumának keresése
MIN()	Paraméterben megadott mező minimumának keresése
SUM()	Paraméterben megadott mező összegzése

Az alábbi példa a Könyv táblából az Év minimumát keresi ki:

SELECT MIN(ev) FROM konyv;

A lekérdezés eredményében egy oszlopot kapunk és egy sort, MIN(ev) névvel. Ez jelen esetben informatív, de nem a legjobban néz ki. Ezért az AS kulcsszóval alternatív nevet adhatunk a mezőnek:

SELECT MIN(ev) AS Legoregebb FROM konyv;

Amennyiben nem csak egy aggregációs függvényt szeretnénk futtatni a teljes táblán, akkor az eredményeket, hogy legyen értelmük, csoportosítani kell. Erre szolgál a GROUP BY kulcsszó, ami megadja, hogy az aggregáció mi alapján legyen csoportba rendezve. GROUP BY nélkül a fentebb említett kérések nem fognak eredményt adni, csak hibát. Az alábbi példa a könyv táblából kiadónként kikeresi a legfiatalabb könyvet:

SELECT kiadonev, MAX(ev) as Legfiatalabb FROM konyv GROUP BY kiadonev;

Adódhat olyan szituáció, hogy az aggregált mezők eredményét szűrni kell. Erre a célra a WHERE záradék nem használható, mivel ez még az aggregáció előtt fog lefutni. Aggregált adatok szűrésére a HAVING záradék használható.

HAVING esetén különösen jól jön az AS kulcsszó használata, mivel, ha a függvényünket AS kulcsszóval elneveztük, akkor az ott megadott névvel hivatkozhatunk a mezőre, nem kell még egyszer bemásolni a teljes függvényhívást. Az alábbi példa a Könyv táblából csak az olyan kiadók nevét és kiadott könyvek számát jeleníti meg, ahol a könyvek száma nagyobb, mint tíz:

SELECT kiadonev, COUNT(ISBN) as KiadottDarab FROM konyv
GROUP BY kiadonev
HAVING KiadottDarab > 10;

A HAVING záradék esetén ugyanazok a feltételrendszerek használhatóak, mint a WHERE esetén.

Többtáblás lekérdezések

A lekérdezések akkor válnak igazán érdekessé, ha több táblán kell műveletet végezni, illetve több táblát is össze kell kapcsolni a kívánt eredmények eléréséhez. A nyelvben erre valóak a JOIN (összefűzés) műveletek. A JOIN műveletekből számos változat létezik.

JOIN műveletekből az alábbi típusokat különböztetjük meg:

INNER JOIN

Visszaadja az összes sort, ha van legalább egy egyezés mindkét táblázatban.

SELECT oszlopok FROM tabla1
INNER JOIN tabla2 ON tabla1.oszlop=tabla2.oszlop;

LEFT JOIN

Minden sor visszaadása a bal oldali táblából és az ezekhez illeszkedő sorok a jobb oldali táblából.

SELECT oszlopok FROM tabla1
LEFT JOIN tabla2 ON tabla1.oszlop=tabla2.oszlop;

RIGHT JOIN

Minden sor visszaadása a jobb oldali táblából és az ezekhez illeszkedő sorok a bal oldali táblából.

SELECT oszlopok FROM tabla1
RIGHT JOIN tabla2 ON tabla1.oszlop=tabla2.oszlop;

FULL JOIN

Minden sor visszaadása, ha a műveletben szereplő táblák egyikében van találat.

SELECT oszlopok FROM tabla1
FULL OUTER JOIN tabla2 ON tabla1.oszlop=tabla2.oszlop;

Lekérdezések mentése táblába

Lehetőség van a lekérdezések táblába mentésére is. Ennek előnye akkor van, ha több táblából kell adatokat összevadászni egy másik lekérdezés számára, amit viszonylag sűrűn kell futtatni, viszont a törzs adatok nem sűrűn frissülnek, illetve még exportálás esetén is hasznos funkció tud lenni.

A létrehozáskor a SELECT összes eddig ismertetett mechanizmusa működik, a változás csupán annyi, hogy az utasítás az elején kiegészül egy INTO kulcsszóval, ami után meg kell adnunk a tábla nevét, amibe írni fog a rendszer:

SELECT INTO ujtabla from regi_tabla where feltetelek;

Meglévő táblába rekordok lekérdezése

Nem csak új táblát tudunk létrehozni a lekérdezés alapján. Lehetőségünk van a lekérdezésünk eredmény soraival feltölteni egy már létező tábla sémát is. Ennek számos előnye van. Egy életképes szituáció az lehet, hogy van egy mérési rendszerünk, amely percenként adatokat rögzít. Ezen adatokból napi statisztikát kell készíteni. Ez megvalósítható úgy, hogy a napi adatokból lekérdezzük a statisztikát az adott napra, és azt eltároljuk egy másik táblában egy sorként.

Az ehhez használható szintaxis az INSERT INTO műveletet bővíti:

INSERT INTO tabla SELECT mezonevek FROM masiktabla WHERE feltetelek;
INSERT INTO tabla (mezonvev1, mezonev2) SELECT mezonevek FROM masiktabla WHERE feltetelek;

Tags Alter, Create, Drop, Full, Inner, Insert, Join, Language, Left, Query, Right, Select, Sql, Structured, Típusok