STRUCTURED QUERY LANGUAGE(SQL) - ALAPOK Az adatbázis-kezelők elvárásai közé tartozik az, hogy legyen egy olyan adatbázis-kezelőktől független nyelv, amely az adatdefiníciós, az adatmanipulációs és a lekérdező nyelvet ismeri. Ez a nyelv az Structured Query Language, röviden SQL.
Nyelv elemei
•
Adatdefiníciós nyelv (DDL Data Definition Language)
•
Adatmanipulációs nyelv (DML Data Manipulation Langauge)
•
Lekérdező nyelv (QUERY Language)
•
Adatvezérlő nyelv (DCL Data Control Language)
Adatdefiníciós nyelv (DDL Data Definition Language)
Az DDL-hez azok az utasítások tartoznak, amelyekkel létrehozzuk a különböző típusú táblákat. A táblákat, ha szükséges módosítjuk vagy törölhetjük vele. Ahhoz, hogy hozzálássunk a táblák kialakításához, szükséges meghatároznunk azt, hogy a tábla egyes oszlopaiba milyen adatokat akarunk tárolni, vagyis milyen típusúak legyenek az oszlopok. A következő adattípusokat tudják az adatbázis-kezelők kezelni:
Adattípusok Numerikus adatok Byte (1 byte) Decimal (12 byte) Integer (2 byte) Long (4 byte) Single(4 byte) Double (8 byte)
Text Memo Datetime Currency Counter byte) Yesno
0 - 255 egész –10^28–1 és 10^28–1 (.mdb) 28 jegy pontosság –32 768 és 32 767 egész –2 147 483 648 és 2 147 483 647 egész Negatívnál: –3,402823E38 és –1,401298E–45 közötti, Pozitívnál: 1,401298E–45 és 3,402823E38 közötti, 7 jegy pontosság. Negatívnál: –1,79769313486231E308 és –4,94065645841247E–324 közötti; Pozitívnál: 4,94065645841247E–324 és 1,79769313486231E308 közötti számok tárolása; 15 jegy pontosság Szöveg vagy szövegből és számokból álló kombináció. Legfeljebb 255 karakter. Hosszabb szöveg vagy szövegből és számokból álló kombináció. Legfeljebb 65535 karakter. Dátum és időértékek a 100. és a 9999. év között. (8 byte) Pénzértékek és matematikai számításokban használt numerikus adatok legfeljebb négy tizedeshellyel. Pontossága 15 számjegy a tizedesjeltől balra, 4 számjegy a tizedesjeltől jobbra. (8 byte) Egyedi sorszám (egyesével növekvő) vagy véletlenszerűen választott szám, amelyet a Microsoft Access ad meg. (4 Igen és Nem érték, csak két érték (Igen/Nem, Igaz/Hamis, Be/Ki) valamelyikét tartalmazó mezők. (1 bit)
Adat tábla létrehozása:
CREATE TABLE táblanév ( oszlopnév adattípus, [ oszlopnév adattípus, oszlopnév adattípus] );
A Create Tablekulcsszó után kötelező megadni a tábla nevét, ami nem tartalmazhat ékezetes betűket, majd zárójelben felsorolva az oszlopok, vagyis mezők nevét szintén ékezet nélkül az adattípus megadásával
Elsődleges kulcs megadása CREATE TABLE tábla név (mező_neve típusa PRIMARY KEY)
Példa:
CREATE TABLE tanar ( tanar_id counter primary key, tanar_nev text );
Tábla módosítása: ALTER TABLE paranccsal. Ezzel új mező vagy megszorítás felvétele történhet meglévő táblába.
Új oszlop hozzáadása: ALTER TABLE táblanév ADD mezőnév adattípus;
Példa:
ALTER TABLE tanar ADD cím text, fizetes currency;
Oszlop törlése: ALTER TABLE táblanév DROP mezőnév;
Példa:
ALTER TABLE tanar DROP cím;
Tábla törlése DROP TABLE táblanév;
Példa:
DROP TABLE tanar1;
Adat manipuláció (DML)
Részei:
•
Új adat felírása
•
Adatok módosítása
•
Adatok törlése
Adatok felvitele: INSERT INTO táblanév (mező1, mező2,....mezőn) VALUES(konstans1, konstans2,…..konstansn);
Példa:
INSERT INTO tanar ( tanar_nev, cim ) VALUES (5,'Nagy Jolán','Budapest XX. Ker. Juhar utca 2.');
Adatok módosítása UPDATE tábla név SET mező név=kifejezés WHERE feltétel A mezőt arra a kifejezésre fogja megváltoztatni, amit az = jel után írunk. Az alapértelmezés a teljes tábla. Ha szűkíteni akarjuk a változtatni kívánt mezőket, akkor a feltételek megadását használjuk. Példa: UPDATE tanar SET tanar_nev = 'Nagy Jolán' WHERE tanar_nev='Kis Jolán';
Adatok törlése DELETE FROM táblanév (WHERE feltétel); Szükségtelen sorok törlése a táblából. Az alapértelmezés mindig a teljes tábla. Természetesen csak a tartalom. Ha feltételt is megadunk csak azok a rekordok törlődnek, amelyekre a feltétel igaz. DELETE FROM táblanév
A teljes tábla tartalmának törlése.
Példa: DELETE FROM tanar WHERE tanar_nev='Nagy Jolán';
Lekérdező nyelv (QUERY Language)
Az adatbázisokat azért használjuk, hogy a nagytömegű adathalmazainkból egyszerűen és gyorsan információkhoz jussunk. Az SQL nyelv legösszetettebb és legfontosabb része az az utasítás amely arra szolgál, hogy információkhoz jussunk. Ez a utasítás a SELECT. A lekérdezés utasítását nem csak önmagában használjuk, már az DDL-ben és a DML-ben is már megjelenhet. Szintaktikája:
SELECT [ALL,DISTINCT, TOP n] mezőnév [AS aliasnév], |*.... FROM tábla név WHERE keresési feltétel GROUP BY csoportosítás HAVING csoporton belüli feltétel ORDER BY rendezési feltétel
A kulcsszavak jelentése: ALL Minden sor megjelenik (ez az alapértelmezés). DISTINCT Azonos sorok csak egyszer jelenjenek meg. TOP n Az első n rekord jelenik meg. AS Alias név a fejlécben szereplő név. SELECT DISTINCT mező1, mező2 FROM TÁBLA A TÁBLA tábla mező1 és mező2 mezőknek tartalmát kilistázzuk, ha van olyan sor ami többször is szerepel a táblánkban csak egyszer fog megjelenni, ha mező1 és mező2-ben megegyeznek.
Sokszor ha egy lekérdezést készítünk, nem csak azokra az adatokra vagyunk kíváncsiak amik a táblában vannak, hanem olyan adatokra amik a táblában lévő adatokból egy kifejezés eredményeként kapható. Az ilyen típusú adatokat számított adatoknak nevezzük.
A mezőnevek helyett, ha * áll, akkor ez az összes mezőnevet jelenti.
Példák: SELECT tanar_nev, cim FROM tanar;
Kiírja az egyezőket is.
SELECT DISTINCT tanar_nev, cim FROM tanar;
Egyezők közül csak az elsőt írja ki.
SELECT TOP 4 tanar_nev, cím, fizetes FROM tanar;
Csak az első 4-et írja ki.
SELECT tanar_nev, cím, fizetes*1.2 FROM tanar;
Számított mező(fizetés 20%-os emelése).
SELECT tanar_nev AS Név, cím AS Címe, fizetes*1.17 AS Fizetés FROM tanar;
Fejléc felirattal.
SELECT * FROM tanar;
A teljes tanar tábla megjelenítése.
Feltételes lekérdezés, WHERE Amikor a sorok között akarunk válogatni, akkor alkalmazzuk a WHERE kulcsszót, amely után megadjuk azt a feltételt, amely alapján válogatni szeretnénk. A feltételek az SQL nyelvben a következők lehetnek:
•
Egyszerű összehasonlítás(=; <>; >; >=; <; <=)
•
Összehasonlítás egy halmaz elemeivel (BETWEEN szám AND szám; IN(lista); LIKE karakterminta)
•
Összetett keresési feltétel (NOT; AND; OR)
Példák: SELECT * FROM tanar WHERE fizetes>=134000;
Kiírja azon rekordokat, ahol a fizetés nagyobb egyenlő 134000 Ft-nál.
SELECT * FROM tanar WHERE fizetes BETWEEN 120000 AND 150000; SELECT * FROM tanar WHERE cim In ('Nagykanizsa','Zalaegerszeg');
SELECT * FROM tanar WHERE tanar_nev LIKE '[A-C]*';
Kiírja azon rekordokat, ahol a fizetés 120000 Ft és 150000 Ft közötti.
Kiírja azon rekordokat, ahol a cím tartalma Nagykanizsa vagy Zalaegerszeg.
Kiírja azon rekordokat, ahol a név kezdőbetűje A és C közötti.
SELECT * FROM tanar WHERE (cím In ('nk','zeg')) And (fizetes Between 120000 And 150000);
Kiírja azon rekordokat, ahol a cím tartalma Nagykanizsa vagy Zalaegerszeg és a fizetés 120000 Ft és 150000 Ft közötti.
Csoportok képzése, GROUP BY
A GROUP BY után megadott mező szerint csoportosít. Tulajdonképpen ez olyan, mint egy gyakoriság számítás, csak a témán belül nem csak a számlálás műveletét tudjuk elvégezni, hanem egyéb műveleteket is a különböző aggregát függvényekkel. Ilyenek: SUM; COUNT; AVG; MAX; MIN;…
Példák:
SELECT cim, COUNT(tanar_id) AS Létszám FROM tanar GROUP BY cim;
Összesíti a tanárokat cím(lakóhely) szerint.
SELECT cim, SUM(fizetes) AS Összesen FROM tanar GROUP BY cim;
Összesíti a tanárok fizetését cím(lakóhely) szerint.
SELECT cim, MIN(fizetes) AS Minimális FROM tanar GROUP BY cim;
Megadja a tanárok minimális fizetését cím(lakóhely) szerint.
SELECT cim, AVG(fizetes) AS Átlag FROM tanar GROUP BY cim;
Megadja a tanárok átlagfizetését cím(lakóhely) szerint.
SELECT cím, count(*) AS Tanárszám FROM tanar GROUP BY cím;
Megadja, hogy lakóhelyek szerint hány tanár van.
HAVING
A HAVING a GROUP BY alparancsa. Amennyiben csak adott csoportokra vagyunk kíváncsiak, akkor a HAVING feltétel alparanccsal a GROUP BY-t megtoldva elérhetjük a feltételnek nem megfelelő sorok kiszűrését.
Példák:
SELECT cím, count(*) AS Tanárszám FROM tanar GROUP BY cím HAVING cím IN('Nagykanizsa','Zalaegerszeg');
SELECT cím, AVG(fizetes) AS Átlag FROM tanar GROUP BY cím HAVING AVG(fizetes)<150000;
Az előző (lakóhely szerinti) csoportosításból csak a Nagykanizsa és Zalaegerszeg csoport adatai jelennek meg.
Megadja azokat a lakóhelyeket ahol az átlagfizetés 150000 Ft alatt van.
Rendezés, ORDER BY Amikor az eredmény listában megjelent adatokat valamelyik mező szerint rendezve akarjuk megjeleníteni, egészítsük ki a SELECT-et az ORDER BY kulcsszóval. A rendezésnél a mező neve után megadhatjuk a rendezés irányát: ASC (alapértelmezett) növekvő rendezés. DESC csökkenő rendezés
Példa:
SELECT * FROM tanar WHERE fizetes BETWEEN 120000 AND 150000 ORDER BY tanar_nev DESC;
Kiírja azon rekordokat, ahol a fizetés 120000 Ft és 150000 Ft közötti, tanárnév szerint csökkenően rendezve.
Táblák összekapcsolása
Az eddigi lekérdezéseinkben mindig egy táblát használtunk. Természetesen ezeket a műveleteket több táblán is el tudom végezni, és pontosan ez a célunk, hogy kapcsolatokat teremtsünk a táblák között és így egyszerre minél több információhoz jussunk.
SELECT Tábla1.oszlop, Tábla2.oszlop, FROM Tábla1,Tábla2 WHERE ELSÖDLEGES_KULCS_OSZLOP=IDEGEN_KULCS_OSZLOP Ha nincs összekapcsolva a két tábla,. a táblák Descartes szorzatát kapjuk eredményként.
Példák:
SELECT tanar.tanar_nev, tanar.cím, tantargy.targynev FROM tanar, tantargy, tanit WHERE tanar.tanar_id=tanit.tanar_id AND tanit.targy_id=tantargy.targy_id ORDER BY tanar_nev;
Kiírja a tanár nevét, címét, szakját.
SELECT tanar.tanar_nev, COUNT(tanit.targy_id) AS Tárgy FROM tanar, tanit WHERE tanar.tanar_id=tanit.tanar_id GROUP BY tanar.tanar_nev ORDER BY tanar_nev;
Kiírja kinek hány szakja van.
Összekapcsolás másként:
A táblák összekapcsolására más lehetőségünk is van, mégpedig a JOIN használatával. Ennek három változata létezik, az INNER, a LEFT és a RIGHT JOIN.
Az INNER JOIN segítségével olyan rekordokat kapunk vissza eredményül, amelyeknél a kulcsmezők tartalmaznak azonos értéket. Használata a következő:
SELECT mezo1, mezo2, mezo3 FROM tabla1 INNER JOIN tabla2 ON tabla1.kulcsmezo= tabla2.kulcsmezo
Ennek használatakor nem jelennek meg azok a rekordok, amelyeket tartalmaz a tabla1, de nincs hozzá tartozó érték a tabla2-ben.
A LEFT JOIN használatakor a tabla1 (baloldali) táblában szereplő rekordok akkor is megjelennek, amikor nincs hozzá tartozó érték a tabla2 (jobboldali) táblában. Az utasítássorozat felépítése:
SELECT mezo1, mezo2, mezo3 FROM tabla1 LEFT JOIN tabla2 ON tabla1.kulcsmezo= tabla2.kulcsmezo
A RIGHT JOIN ugyanezt a terminológiát követi, csak éppen a tabla2-ben szereplő értékeket mutatja meg akkor is, amikor a tabla1-ben nincs neki megfelelő érték.
SELECT mezo1, mezo2, mezo3 FROM tabla1 RIGHT JOIN tabla2 ON tabla1.kulcsmezo= tabla2.kulcsmezo
Példák:
SELECT tanar.tanar_nev, COUNT(tanit.targy_id) AS Tárgy FROM tanar INNER JOIN tanit ON tanar.tanar_id=tanit.tanar_id GROUP BY tanar.tanar_nev ORDER BY tanar_nev;
Kiírja kinek hány szakja van INNER JOIN-nal.
SELECT tanar.tanar_nev, COUNT(tanit.targy_id) AS Tárgy FROM tanar LEFT JOIN tanit ON tanar.tanar_id=tanit.tanar_id GROUP BY tanar.tanar_nev ORDER BY tanar_nev;
Kiírja kinek hány szakja van LEFT JOIN-nal.
SELECT tanar.tanar_nev, COUNT(tanit.targy_id) AS Tárgy FROM tanar RIGHT JOIN tanit ON tanar.tanar_id=tanit.tanar_id GROUP BY tanar.tanar_nev ORDER BY tanar_nev;
Kiírja kinek hány szakja van RIGHT JOIN-nal.
