Programozás | SQL » Az SQL nyelv legfontosabb utasításai

Az SQL lekérdezőnyelv legfontosabb utasításai 2003. május 30 1. Bevezetés Az SQL rövidítés a Structured Query Language kifejezésből származik, amelyet legtöbbször Struktúrált Lekérdezőnyelvként fordítanak. Az SQL nyelv az adatbáziskezelés szabványos nyelve, nagyon sok adatbáziskezelő rendszer erre a nyelvre épül. Az SQL nyelvet kétféleképpen használhatjuk fel: • Önálló nyelvként: az SQL alapú adatbáziskezelő rendszerek interakív (párbeszédes) kliens programjai az SQL nyelvre épülnek, a kliens programok használata során legtöbbször SQL utasítások segítségével kezelhetjük az adatokat. • Beágyazott nyelvként: egyes programozási nyelvek, illetve vizuális fejlesztőkörnyezetek támogatják az SQL utasítások beágyazásának lehetőségét, tehát a programban elhelyezhetők SQL utasítások is. Ilyen rendszer például a Delphi Azt a nyelvet, amelybe beágyazzuk az SQL utasításokat gazdanyelvnek nevezzük. . 2. Az SQL

utasítások csoportosítása Az SQL nyelv utasításait négy nagy csoportra oszthatjuk: • Adatdefiníciós utasítások • Adatmanipulációs utasítások 1 • Lekérdezések • Egyéb utasítások 2.1 Adatdefiníciós utasítások Ebbe a csoportba tartoznak azok az utasítások, amelyeknek segítségével az adatbázist, illetve annak az elemeit létrehozhatjuk, törölhetjük vagy módosíthatjuk. Az elnevezés kicsit félrevezető, mert nem kizárólag a szűkebb értelemben vett adatok kezelését végzik ezek az utasítások, minden olyan utasítást ebbe a csoportba sorolhatunk, amelynek segítségével valamilyen adatbázis objektumot létrehozhatunk, módosíthatunk vagy törölhetünk. 2.2 Adatmanipulációs utasítások Ennek a csoportnak az elnevezése már sokkal kifejezőbb, mint az előző csoporté. Ide sorolhatók az adatok bevitelét, módosítását és törlését végző utasítások. 2.3 Lekérdezések Ebbe a csoportba mindössze egyetlen

utasítás tartozik, a SELECT utasítás, amelynek segítségével megjeleníthetjük az adattáblákban tárolt adatokat. 3. 3.1 Adatdefiníciós utasítások Adatbázis létrehozása CREATE DATABASE <adatbázis>. Új adatbázis létrehozása. Az utasítás egyetlen záradéka a létrehozandó adatbázis neve Adatbázis törlése DROP DATABASE <adatbázis>. 2 Létező adatbázis törlése. Szintaktikája azonos a CREATE DATABASE utasítással. 3.2 Adattábla létrehozása CREATE TABLE <tábla> ( oszlop1 típus1 [DEFAULT <érték>] [NULL | NOT NULL] [CONSTRAINT <feltnév> <feltétel>] oszlop2 típus2 [DEFAULT <érték>] [NULL | NOT NULL] [CONSTRAINT <feltnév> <feltétel>]). Új adattábla létrehozása. Az utasításnak kötelezően meg kell adni az adattábla nevét, az oszlopok nevét és típusát. Ezek mellett megadhatunk az oszlopnak alapértelmezett értéket, illetve megadhatunk ellenőrzési feltételeket,

amelyek segítségével ellenőrizhetjük az adott oszlopba történő adatbevitelt. Amennyiben egy oszlopra megadjuk az alapértelmezett értéket a tábla készítésekor, és az adatbevitel során, nem határozzuk meg a mező értékét, akkor a mezőben az alapértelmezett érték fog szerepelni. A NULL és a NOT NULL záradék segítségével meghatározhatjuk, hogy a mezőben szerepelhet-e NULL érték. A CONSTRAINT záradék segítségével ellenőrzési feltételeket adhatunk meg az adott oszlop esetében. A feltételek közül a legfontosabb, az elsődleges kulcs definiálására vonatkozó feltétel, amelyet a következőképpen írhatunk fel: CONSTRAINT kulcs PRIMARY KEY. A CONSTRAINT záradék segítségével további ellenőrzéseket is végezhetünk:. • A mező lehetséges értékeinek felsorolása: IN (felsorolás) • A mező lehetséges legkisebb és legnagyobb értékének megadása: BETWEEN érték1 AND érték2 3 Az értékek felsorolása és az

értékhatárok megadása esetén az adatok bevitele során ellenőrzésre kerül, hogy a megadott érték megfelele a megadott feltételeknek. 3.3 Adattábla törlése DROP TABLE <tábla>. Az utasítás záradékaként meg kell adni a törlendő adattábla nevét. 3.4 Adattábla szerkezetének módosítása ALTER TABLE <tábla> <módosítások>. Az ALTER TABLE utasítás segítségével a tábla szerkezetén tudunk változtatni. A lehetőségek a következők: • Új oszlop hozzáadása a táblához: ALTER TABLE <tábla> ADD <oszlopnév> <típus> • Létező oszlop törlése: ALTER TABLE <tábla> DROP <oszlopnév> • Létező oszlop nevének módosítása: ALTER TABLE <tábla> ALTER <oszlopnév> TO <új-oszlopnév> • Létező oszlop típusának módosítása: ALTER TABLE <tábla> ALTER <oszlopnév> TYPE <típus> 3.5 Index létrehozása CREATE [UNIQUE] [ASC|DESC] INDEX <indexnév> ON

<tábla> (oszlop). A CREATE INDEX utasítás segítségével egy indexet hozhatunk létre egy adattáblához. Az indexek jelentős mértékben gyorsíthatják az adatok lekérdezését, keresését, a jól megválasztott indexek segítségével akár többszörösére is növelhető az adatkezelés gyorsasága. 4 A UNIQUE záradék segítségével egyedi indexeket hozhatunk létre, ez célszerű lehet, ha a tábla elsődleges kulcsa az indexelendő mező. Az ASC, illetve DESC záradék segítségével meghatározhatjuk, hogy növekvő vagy csökkenő sorrendben szeretnénk-e indexelni a mezőket. Az indexnév az index, a tábla az adattábla, az oszlop az indexelni kívánt mező neve. 3.6 Index törlése DROP INDEX <indexnév>. Az utasítás segítségével megszüntethetjük a záradékban megadott indexet. 4. 4.1 Adatmanipulációs utasítások Adatok bevitele INSERT INTO <tábla> [(oszloplista)] VALUES (<értéklista>). Az INSERT INTO utasítás

segítségével új adatokat szúrhatunk be az adattáblába. Az utasítás záradékaként megadhatjuk az oszlopok listáját, de ez nem kötelező. Ha nem soroljuk fel az oszlopokat, akkor az utasítás alapértelmezés szerint az összes oszlopot figyelembe veszi. A VALUES záradékban kell megadni a beszúrandó adatok listáját, az értékek számának meg kell egyezni az oszlopok számával. 4.2 Adatok törlése DELETE FROM <tábla> [WHERE <feltétel>]. A DELETE utasítás törli a táblából a WHERE záradékban meghatározott feltételnek megfelelő rekordokat. Ha a WHERE záradékot elhagyjuk, az az összes rekord törlését eredményezi. 5 A WHERE záradékban megadható feltételek nagyon sokfélék lehetnek. A legfontosabb lehetőségek a következők:. • Egyenlőségvizsgálat: oszlop=érték A feltétel azokat a rekordokat jelenti, ahol az oszlop értéke megegyezik az egynlőségjel után álló értékkel. • Reláció vizsgálata:

oszlop<érték | oszlop>érték | oszlop<=érték | oszlop>=érték Ez a feltétel csak numerikus és dátum típusú mezők esetén alkalmazható. • Értékhatárok vizsgálata: oszlop between érték1 and érték2 A feltétel akkor teljesül, ha az oszlop értéke érték1 és érték2 közé esik. Csak numerikus és dátum típusú mezők esetén alkalmazható. • Hasonlóság vizsgálata: mező like %̈érték%̈ Ezt a vizsgálatot csak a karakteres típusú mezők esetén alkalmazhatjuk. Ha a % jelek között megadott érték szerepel a szövegben, akkor a feltétel teljesül. Másik formája a % jelek nélküli alak: mező like érték, de ebben az esetben a mezőben található adatnak karakterről karakterre azonosnak kell lennie a megadott értékkel. 4.3 Adatok módosítása UPDATE <tábla> SET <mező1=érték1>[,<mező2=érték2>,.,<mezőx=értékx>] [WHERE <feltétel>]. Az UPDATE utasítás segítségével a már feltöltött

mezők értékét módosíthatjuk. Az utasításnak meg kell adni a tábla nevét, illetve a SET záradékban a módosítandó mezők nevét, majd egyelőségjel után az új értéket. A WHERE záradék használata nem kötelező, ha elhagyjuk, akkor az összes mező értéke módosítva lesz. A WHERE záradékban megadható feltételek azonosak a DELETE utasításnál leírt feltételekkel. 6 5. Lekérdezések Ebbe a csoportba mindössze egyetlen SQL utasítás tartozik, a SELECT utasítás. A SELECT utasítás talán a legsokoldalúbb az összes utasítás közül, segítségével bármilyen lekérdezés megvalósítható. 5.1 A SELECT utasítás legegyszerűbb formája SELECT * FROM <tábla>. Ebben a formában a táblában található összes adat lekérdezhető, tehát az összes oszlop összes sora megjelenítésre kerül. 5.2 Az oszlopok válogatása A SELECT utasításnak megadhatjuk egyértelműen, hogy az adattábla mely oszlopait szeretnénk

megjeleníteni. Ebben az esetben a * jel helyén vesszővel elválasztva fel kell sorolni az oszlopok neveit. SELECT oszlop1, oszlop2,.,oszlopn FROM <tábla> A megjelenítés során alapértelmezés szerint az oszlopok neve jelenik meg. Ezt a működést felülbírálhatjuk, megadhatjuk egyértelműen az oszlopok fejlécét a következő formában. SELECT oszlop1 fejléc1, oszlop2 fejléc2,.,oszlopn fejlécn FROM <tábla> Ha ebben a formában használjuk a SELECT utasítást, akkor az oszlopok felett a fejléc1, fejléc2,. ,fejlécn szöveg fog megjelenni 5.3 A sorok válogatása A SELECT utasítás használata során is lehetőség van arra, hogy feltételekhez kössük a rekordok megjelenítését, tehát csak olyan rekordokat listázzunk, amelyek a megadott feltételnek eleget tesznek. Ebben az esetben a WHERE záradékot kell használnunk, a következő formában: SELECT oszlop1, oszlop2,.,oszlopn FROM <tábla> WHERE <feltétel> A WHERE záradékban

megadható feltételek azonosak az eddig leírt feltételekkel. 7 5.4 Kifejezések használata a lekérdezésben Az SQL nyelv lehetőséget nyújt arra, hogy a lekérdezések során a mezők értékeivel műveleteket végezzünk, számított értékeket jelenítsünk meg. A műveletek köre korlátozott, de az adatbáziskezelés során leggyakrabban előforduló műveleteket minden SQL változat támogatja. Ezek a műveletek az átlag, összeg és a darabszám számítása illetve a minimum és a maximum érték keresése. A számítási műveletek kifejezésekként írhatók fel, amelyben a mezők nevei, illetve konstans értékek lehetnek az operandusok, és a négy matematikai alapművelet műveleti jelei lehetnek az operátorok. A négy alapművelet mellett használhatjuk az SQL nyelv függvényeit. Példák egyszerű számítási műveletekre: SELECT mezo1*1.25, mezo2 FROM <tábla> SELECT mezo1-mezo2 FROM <tábla>. Példák az SQL függvények használatára:

SELECT max(mezo1) FROM <tabla> SELECT avg(mezo2) FROM <tabla>. 5.5 Ismétlődő sorok kihagyása Egyes esetekben előfordulhat, hogy a tábla egy-egy sorában egy oszlopon belül azonos értékek fordulnak elő. A lekérdezés során alapértelmezés szerint minden sor megjelenítésre kerül, tehát az ismétlődő sorok annyiszor jelennek meg a képernyőn, ahányszor a táblában szerepelnek. Amennyiben szeretnénk ezeket az ismétlődéseket kiszűrni, akkor a DISTINCT záradékot kell használnunk a lekérdezés során. Formája a következő: SELECT mezo1, DISTINCT mezo2 FROM <tabla>. Az eredmény rendezett megjelenítése A lekérdezés során alapértelmezés szerint az adatok a felvitel sorrendjében jelennek meg. Lehetőség van azonban az adatok sorba 8 rendezésére az ORDER BY záradék használatával, az adatok növekvő és csökkenő sorrendben egyaránt megjeleníthetők. SELECT * FROM <tabla> ORDER BY <mezo> [ASC|DESC]. A

rendezés az ORDER BY záradékban megadott mező értéke szerint történik, a rendezés irányát az ASC vagy DESC kulcsszó használata határozza meg. Ha nem rendelkezünk egyértelműen a rendezés irányáról, akkor alapértelmezés szerint az ASC-t használja az SQL, azaz növekvő sorrendben rendez. Az ORDER BY záradék használatával történő rendezés csak a megjelenítés során kerül figyelembevételre, az adattábla rekordjainak fizikai sorrendje nem változik, tehát csak logikai rendezésről van szó. 5.6 A talált sorok csoportosítása A lekérdezések során lehetőség van arra, hogy a lekérdezett sorokat valamely oszlop értéke alapján csoportosítsuk, és az egy-egy csoportot képező sorok oszlopain végezzünk el egy vagy több, oszlopra vonatkozó számítási műveletet. Ehhez a SELECT utasítás GROUP BY záradékát kell felhasználnunk, melynek formája a következő:. SELECT <oszlop1>, <függvény(oszlop2)> FROM <tábla>

GROUP BY <oszlop1>. A példa értelmezése: A SELECT utasítás először lekérdezi az összes sort, majd csoportosítja azokat az oszlop1 értéke alapján. Az egyes csoportokba tartozó sorok oszlop2 oszlopán elvégzi a megadott függvényt, majd megjeleníti az eredményt. 5.7 Csoportosítás feltétellel Ha a csoportosítás során szeretnénk meghatározni, hogy a csoportok közül csak azok jelenjenek meg, amelyek bizonyos feltételeknek megfelelnek, akkor már nem használhatjuk a WHERE záradékot. A 9 csoportosított adatokra a HAVING záradék használatával adhatunk meg feltételeket, a következő formában:. SELECT <oszlop1>, <függvény(oszlop2)> FROM <tábla> GROUP BY <oszlop1> HAVING <feltétel>. A HAVING záradék esetén ugyanazokat a feltételeket használhatjuk, mint a WHERE záradék esetében. 5.8 Adatok lekérdezése több adattáblából, az adattáblák összekapcsolása Az adatbáziskezelés során alapvető

feladat több adattábla összekapcsolása egy-egy mezőn keresztül. Az ilyen lekérdezések esetében a SELECT utasítás FROM záradékában az összes érintett adattáblát fel kell sorolni, a mezők felsorolása során pedig azt is meg kell adni, hogy mely adattáblában található az adott mező. A tényleges összekapcsolást a WHERE záradék teszi lehetővé, amelyben feltételként meg kell adni, hogy csak azok a rekordok jelenjenek meg a lekérdezés során, ahol a két táblát összekapcsoló mezők értéke egyenlő. SELECT tabla1.mezo1, tabla1mezo2,,tabla1mezon, tabla2.mezo1,tabla2mezo2,,tabla2mezon FROM tabla1,tabla2 WHERE tabla1.mezo1=tabla2mezo2 A lekérdezést a következképpen értelmezhetjük:. A tabla1 és a tabla2 táblából lekérdezzük a fesorolt mezőket, amelyek esetében igaz, hogy a tabla1 táblában és a tabla2 táblában található mezo1 mező értéke egyenlő. A mezők felsorolásánál a tábla nevét és a mező nevét ponttal választjuk el

egymástól, az egyértelmű hivatkozás érdekében. Ugyanez megfigyelhető a WHERE záradék feltételének meghatározása során is. 10 Előfordulhat, hogy a szükséges adattáblák neve hosszú, így az utasítás nagyon hosszúra nyúlik, ha minden egyes mező előtt még a tábla teljes nevét is megadjuk. Ebben az esetben célszerű a táblákhoz egy-egy ideiglenes nevet rendelni, amelyekkel a lekérdezés során a táblára hivatkozhatunk. Mivel az ideiglenes nevet magunk választhatjuk meg, célszerű egyszerű elnevezéseket használni, a lekérdezés egyszerűsítése érdekében. Nézzünk erre egy példát: SELECT t1.mezo1, t1mezo2, t2mezo1, t2mezo2 FROM tabla1 t1, tabla2 t2 WHERE t1.mezo1=t2mezo2 Az ideiglenes nevet tehát a FROM záradékban a tábla neve után kell megadni, ezt a nevet a lekérdezés során bárhol felhasználhatjuk a tábla azonosítására. 5.9 Allekérdezések Az allekérdezések használatára akkor van szükség, ha a WHERE záradékban

olyan feltételt szeretnénk megfogalmazni, amely egy másik tábla vagy esetleg ugyanazon tábla egy oszlopának összes értékéhez hasonlítja a vizsgált mezőt. Szintén allekérdezést kell alkalmaznunk abban az esetben, ha a WHERE záradékban valamilyen oszlopfüggvényt (MIN, MAX, SUM, AVG) szeretnénk használni. Az allekérdezések tulajdonképpen olyan lekérdezések, amelyek egy SELECT utasítás WHERE záradékában szerepelnek. Formájuk a következő: SELECT mezo1, mezo2 FROM tabla1 WHERE mezo1=(select avg(mezo1) from tabla1). Az előbbi lekérdezés azokat a sorokat fogja megjeleníteni, amelyeknél a mező1 mező értéke egyenlő a mező1 átlagával az egész táblára nézve. Az alkérdésben tehát felhasználhatjuk akár azt a táblát is, amelyen a f̈ől̈ekérdezés alapul. Figyeljünk arra, hogy az allekérdezések esetén az eredmény csak egyetlen sorból állhat, mivel másképp nem lenne egyértelmű a feltétel megadása. 11 5.10 Külső

összekapcsolási műveletek Az adattáblák összekapcsolását legtöbb esetben a SELECT utasítás WHERE záradékban megfogalmazott feltétellel célszerű összekapcsolni. Ez a módszer azonban csak akkor használható, ha a kapcsolatot kialakító mezők értéke egyik táblában sem lehet NULL érték, mivel a NULL értéket a WHERE záradék nem tudja kiértékelni. Ha bármelyik táblában előfordulhat null érték a kapcsolat kialakításában résztvevő mezők esetében, akkor már nem használhatjuk ezt a megoldást, csak a táblák között ilyen esetben csak külső kapcsolat alakítható ki. A külső összekapcsolás lényege, hogy nem kötelező a kapcsolódó mezők értékének minden sorban megegyezni, megengedhető a mezők eltérése, sőt akár a NULL érték is. Az összekapcsolás során rendelkezhetünk arról, hogy az adatok megjelenítése során a jobb oldali, a bal oldali esetleg mindkét táblából listázzuk ki azokat a mezőket, amelyeknek értéke

a két táblában eltérő. A külső összekapcsolást a következőképpen végezhetjük el:. SELECT t1.mezo1,,t1mezon, t2mezo1,,t2mezon FROM tabla1 t1 INNER <LEFT | RIGHT | FULL> JOIN tabla2 t2 ON <t1.mezo1=t2mezo1> A három összekapcsolási mód közötti különbség a nem egyenlő mezők listázásának módjában található meg: a LEFT kulcsszó esetén a bal oldali, a RIGHT kulcsszó esetén a jobb oldali, a FULL kulcsszó esetén pedig mindkét oldali táblából ki lesznek listázva azok a mezők is, amelyeknek értéke nem egyenlőek a másik táblában található kapcsolómező értékével. 6. Ajánlott irodalom Stolnicki Gyula: SQL kézikönyv. 12