HÍREK
LEGUTÓBBI MÓDOSÍTÁSOK SAJÁT
MUNKÁK OKTATÁS és KAPCSOLAT EGYÉB
MÓDSZERTANOK TANULD MEG A JAVA JAVÁT!

Gyakorlati alapok III.

SQL (adatbázis)

 

Az SQL-nyelv alapjellemzői és 1 táblás műveletek

 

Amint az már említésre került, Edgar F. Codd (1923-2003) volt az...

 

 

...aki 1970-ben megalkotta a relációs adatmodellt és rögtön javaslatokat is tett a modellt implementáló programnyelvre. Ennek alapján alkották meg az ALPHA nevű programnyelvet, amely azonban Codd erőteljes támogatása ellenére sem terjedt el. Helyette az ALPHA alapelveit követve számos további relációs adatbáziskezelő nyelv került kidolgozásra, amelyet csak jóval később gyömöszöltek egy olyan szabványba, amelyet manapság már SQL-nek hívunk (Standard Query Language). Így vált napjaink mindegyik relációs adatbáziskezelő nyelvének alapjává a Codd-féle relációs adatmodellre támaszkodó, szabványosított SQL (ANSI X3.1986). Egy évvel később a szabványt az ISO is elfogadta.

Sokak vélekedtek úgy, hogy túl korai volt a SQL szabványosítása, mert ez hamar merevvé, rugalmatlanná tette az SQL-t. Néhány cég emiatt már viszonylag korán eltért tőle és rendszereikben önálló SQL-változatokat kezdtek használni. A változások rögzítése, “kodifikálása” végett 1992-ben a szabványt átdolgozták. Ennek ellenére az SQL-változatok további definiálása, terjedése nem állt meg, ma már több mint 50 SQL-alapú relációs adatbáziskezelő nyelv létezik. Éppen ezért nem sok értelme van a szabványok időrendi felsorolásának, bár egyértelmű hasznuk mégiscsak az, hogy olyan tartalmi-formai egységet biztosítanak, amely (szinte bármilyen) relációs adatbáziskezelő nyelv magját képezi.

Az SQL vizsgálatakor a mySQL-phpMyAdmin környezetekben való futtathatóságot vesszük kiindulópontnak. Más környezetekben az SQL-szintaxis kisebb-nagyobb mértékben különbözhet. Ha fontosnak értékelem, olyan nyelvi elemeket is meg fogok említeni, amelyek benne vannak az 1989-es alapváltozatban, de esetleg a “mi” mySQL-phpMyAdmin-környezetünkben nem ellenőrizhetők.

 

Nem oly fontos, de gyorsan mégis említsük meg azon relációs algebrai műveleteket, amelyek az SQL működésének alapját képezik (amely egyébként híven követi a Codd által felállított relációs alapelveket):

• unió,

• különbség,

• Descartes-szorzat,

• projekció,

• szelekció,

• hányados,

• metszet,

• összekapcsolás,

• természetes összekapcsolás.

 

Ezenfelül az SQL szintaktikailag erősen az angol nyelv szabályaira alapul, nagy előny tehát, ha tudunk angolul.

 

A mySQL néhány adattípusával már találkozhattunk A phpMyAdmin című fejezetben, ahol első, demonstrációs, auto_csop nevű táblánkat töltöttük fel VARCHAR és INT típusú rekordokkal. Láthatjuk, hogy a VARCHAR típus karakterek (szövegek), míg az INT típus -hasonlóan a Java int típushoz-, numerikus (egész) formátum tárolására alkalmas:

 

AUTO_CSOP

 

A fenti táblát az egyik JDBC-fejezetben (Alapszintű kapcsolódás, lekérdezés és beszúrás) tesztelési célból +1 sorral egészítettük ki:

 

AUTO_CSOP

 

Általánosságban kijelenthetjük, hogy a mySQL további adattípusai igen sokfélék, sokszor összetettek, ismeretük bár hasznos, de nem oly lényeges a jelen fejezetcsomag eredményes feldolgozásához, ugyanis mi jóval kevesebb és nem túlbonyolított adattípussal fogunk dolgozni, mint amely a mySQL-ben alapvetően rendelkezésre áll. Aki eziránt is érdeklődik, annak ajánlom figyelmébe a következő, hivatalos oldalt:

 

https://dev.mysql.com/doc/refman/8.0/en/data-types.html

 

Kiindulópontunk tehát most a fenti, 3 x 4-es AUTO_CSOP táblánk lesz, az egyszerű SQL-kifejezéseket, függvényeket csakis ebben a táblában fogjuk tesztelni. A konkrét SQL-utasítást a phpMyAdmin-felületen elérhető SQL-konzolra írjuk, majd nyomjunk az Indítás gombra:

 

 

Ha az utasítás helyes volt, a parancs lefut és a képernyőn megtekinthetjük a végeredményt:

 

 

 

Az SQL-utasítások a jelen fejezetből egy az egyben be is másolhatók, ám javaslatom, hogy inkább következetesen írjuk be őket!

 

Az SQL-alapfüggvények, operátorok, egyéb jellemzők:

• CREATE

• DROP

• DELETE

• INSERT

• UPDATE

• SELECT

• COUNT

• AVG

• SUM

• MAX

• MIN

• BETWEEN

• NOT BETWEEN

• IN

• NOT IN

• LIKE

• NOT LIKE

• ORDER BY (ASC, DESC)

• IS NULL

• IS NOT NULL

• Operátorok

• Helyettesítő karakterek (wildcard characters)

• Egyéb szintaktikai elemek

 

CREATE

Adatszerkezet létrehozása (adatbázis, tábla). Például hozzuk létre az auto_csop nevű táblát:

 

CREATE table auto_csop

 

Végeredmény: az auto_csop nevű tábla létrehozása.

 

DROP

Adatszerkezet végleges törlése (adatbázis, tábla). Például töröljük végleg az auto_csop nevű táblát:

 

DROP table auto_csop

 

Végeredmény: az auto_csop nevű tábla végleges törlése.

 

DELETE

Megadott feltételek szerint töröl. Például töröljük a tábla legalsó sorát:

 

BEYOND

500

20000

 

DELETE FROM `auto_csop`WHERE `auto_csop_nev`= 'BEYOND'

 

Végeredmény:

auto_csop_nev	km_dij	napi_dij
NORMAL	80	5000
EXTRA	120	7500
LUXUS	300	15000

 

INSERT

Megadott feltételek szerint inzertál (beszúr). Például állítsuk helyre az előzőleg törölt, legalsó sort:

 

INSERT INTO auto_csop VALUES ('BEYOND','500','20000')

 

Végeredmény:

auto_csop_nev	km_dij	napi_dij
NORMAL	80	5000
EXTRA	120	7500
LUXUS	300	15000
BEYOND	500	20000

 

UPDATE

Megadott feltételek szerint már létező rekordot változtat meg, ír felül. Például változtassuk meg az egyik napi_dij rekordot 20000-ról 25000-re:

 

UPDATE auto_csop SET napi_dij = '25000' WHERE `auto_csop_nev` = 'BEYOND'

 

Végeredmény:

auto_csop_nev	km_dij	napi_dij
NORMAL	80	5000
EXTRA	120	7500
LUXUS	300	15000
BEYOND	500	25000

 

Vigyázat!

 

Ha nem adunk meg egyéb feltételeket (a WHERE után részben)...

 

UPDATE auto_csop SET napi_dij = '25000'

 

...akkor az utasítás az összes rekordot felülírja!

 

Végeredmény:

auto_csop_nev	km_dij	napi_dij
NORMAL	80	25000
EXTRA	120	25000
LUXUS	300	25000
BEYOND	500	25000

 

Helyreállítások:

• UPDATE auto_csop SET napi_dij = '5000' WHERE `auto_csop_nev` = 'NORMAL'

• UPDATE auto_csop SET napi_dij = '7500' WHERE `auto_csop_nev` = 'EXTRA'

• UPDATE auto_csop SET napi_dij = '15000' WHERE `auto_csop_nev` = 'LUXUS'

SELECT

A lekérdezés alapparancsa, önmagában nem, csakis más parancsokkal kombinálva működőképes. Legegyszerűbb SELECT lekérdezésünk a következő lehet: kérdezzük le az auto_csop tábla teljes, aktuális tartalmát:

 

SELECT * FROM `auto_csop`

 

Végeredmény:

auto_csop_nev	km_dij	napi_dij
NORMAL	80	5000
EXTRA	120	7500
LUXUS	300	15000
BEYOND	500	20000

 

COUNT

A megadott oszlopban lévő rekordok számát adja vissza.

 

SELECT COUNT(`auto_csop_nev`) AS AutoCsoportokSzama FROM auto_csop;

 

Szöveges megfogalmazása következő:

 

 Válaszd ki az auto_csop táblából az auto_csop_nev oszlopot és számold meg!

Az eredmény felirata AutoCsoportokSzama legyen!

 

Végeredmény: AutoCsoportokSzama 4.

 

DISTINCT esetén csak a különböző értékű rekordokat számlálja.

 

AVG

A megadott oszlopban lévő rekordok átlagát adja vissza.

 

SELECT AVG(`napi_dij`) AS NapiDijakAtlaga FROM auto_csop;

 

Szöveges megfogalmazása következő:

 

 Válaszd ki az auto_csop táblából a napi_dij oszlopot és vedd az átlagát!

Az eredmény felirata NapiDijakAtlaga legyen!

 

Végeredmény: NapiDijakAtlaga 11875.0000.

 

DISTINCT esetén az azonos értékű rekordokat egyszeresen veszi.

 

SUM

A megadott oszlopban lévő rekordok összegét adja vissza.

 

SELECT SUM(`napi_dij`) AS NapiDijakOsszege FROM auto_csop;

 

Szöveges megfogalmazása következő:

 

 Válaszd ki az auto_csop táblából a napi_dij oszlopot és összegezd!

Az eredmény felirata NapiDijakOsszege legyen!

 

Végeredmény: NapiDijakOsszege 47500.

 

DISTINCT esetén csak a különböző értékű rekordokat összegzi.

 

MAX

A megadott oszlopban lévő rekordok maximumértéke.

 

SELECT MAX(`napi_dij`) FROM auto_csop;

 

Végeredmény: MAX('napi_dij) 20000.

 

DISTINCT esetén az azonos értékű rekordokat egyszeresen veszi.

 

MIN

A megadott oszlopban lévő rekordok minimumértéke.

 

SELECT MIN(`napi_dij`) FROM auto_csop;

 

Végeredmény: MIN('napi_dij) 5000.

 

DISTINCT esetén az azonos értékű rekordokat egyszeresen veszi.

 

BETWEEN

A függvény segítségével adott alsó és felső tartomány között választhatjuk ki a rekordokat. Például keressük meg az olyan rekordokat, amelyek 200 és 400 kilométerdíj között helyezkednek el:

 

SELECT km_dij FROM auto_csop WHERE km_dij BETWEEN 200 AND 400;

 

Végeredmény: km_dij 300.

 

NOT BETWEEN

A függvény segítségével adott alsó és felső tartomány között nem található rekordokat választhatunk ki. Például keressük meg az olyan rekordokat, amelyek nem 200 és 400 kilométerdíj között helyezkednek el:

 

SELECT km_dij FROM auto_csop WHERE km_dij NOT BETWEEN 200 AND 400;

 

Végeredmény:

km_dij

80

120

500

 

IN

A függvény segítségével azt keressük, hogy van-e a függvényben szereplő, paraméterként megadott rekord. Például keressünk pontosan 5000 és 7500 napidíjas autócsoportneveket (amelyekről még nem tudjuk, hogy léteznek-e):

 

SELECT auto_csop_nev, napi_dij FROM auto_csop WHERE napi_dij IN (5000, 7500)

 

Végeredmény:

NORMAL  5000

EXTRA  7500

 

Mi történik abban az esetben, ha nincs (3000):

 

SELECT auto_csop_nev, napi_dij FROM auto_csop WHERE napi_dij IN (3000)

 

Végeredmény:

auto_csop_nev, napi_dij

 

NOT IN

A függvény segítségével keressük az összes, a függvényben nem szereplő rekordot. Például listázzuk ki az összes olyan autócsoportnevet, amelynek napidíja nem 5000 (és amelyekről még nem tudjuk, hogy léteznek-e):

 

SELECT auto_csop_nev, napi_dij FROM auto_csop WHERE napi_dij NOT IN (5000)

 

Végeredmény:

EXTRA  7500

LUXUS  15000

BEYOND  20000

 

Mi történik abban az esetben, ha nincs:

 

SELECT auto_csop_nev, napi_dij FROM auto_csop WHERE napi_dij NOT IN (3000)

 

Végeredmény:

NORMAL  5000

EXTRA  7500

LUXUS  15000

BEYOND  20000

 

LIKE

A függvényt leginkább az "olyan mint" kifejezéssel fordíthatjuk le. A függvény segítségével keressük azt a rekordot, amelyik a LIKE paramétereként van megadva.

 

SELECT auto_csop_nev, km_dij, napi_dij FROM auto_csop WHERE auto_csop_nev LIKE ("LUXUS");

 

Végeredmény:

LUXUS  300  15000

 

NOT LIKE

A függvényt leginkább a  "nem olyan mint" kifejezéssel fordíthatjuk le. A függvény segítségével keressük azt a rekordot, amelyik nem egyezik a LIKE paramétereként megadott értékkel.

 

SELECT auto_csop_nev, km_dij, napi_dij FROM auto_csop WHERE auto_csop_nev NOT LIKE ("LUXUS");

 

Végeredmény:

NORMAL  80  5000

EXTRA  120  7500

BEYOND  500  20000

 

ORDER BY

Ezzel a paranccsal lehetőségünk van a lekérdezés eredményét rendezni:

• ASC - növekvő sorrendben,

• DESC - csökkenő sorrendben.

Növekvő sorrend:

 

SELECT napi_dij FROM auto_csop ORDER BY napi_dij ASC

 

Végeredmény:

napi_dij

5000

7500

15000

20000

 

Csökkenő sorrend:

 

SELECT napi_dij FROM auto_csop ORDER BY napi_dij DESC

 

Végeredmény:

napi_dij

20000

15000

7500

5000

 

 

IS NULL

Az adatbázis adminisztrálásakor (főként INSERT, UPDATE) megengedhető, hogy a rekord ne kapjon kezdőértéket, hanem NULL értékkel legyen feltöltve. A NULL érték nem egyenlő a matematikai zéróval, hanem csupán egyszerű "feltöltetlenséget" jelent!

 

Az alábbi lekérdezésben a végeredmény üres lesz, hiszen a napi_dij oszlop minden rekordja fel van töltve:

 

SELECT napi_dij FROM auto_csop WHERE napi_dij IS NULL

 

Végeredmény:

napi_dij

 

IS NOT NULL

Az adatbázis adminisztrálásakor (főként INSERT, UPDATE) megengedhető, hogy a rekord ne kapjon kezdőértéket, hanem NULL értékkel legyen feltöltve. A NULL érték nem egyenlő a matematikai zéróval, hanem csupán egyszerű "feltöltetlenséget" jelent!

 

Az alábbi lekérdezésben a végeredmény teljes lista lesz, hiszen a napi_dij oszlop minden rekordja fel van töltve:

 

SELECT napi_dij FROM auto_csop WHERE napi_dij IS NOT NULL

 

Végeredmény:

napi_dij

5000

7500

15000

20000

 

 

Operátorok

Az operátorok - hasonlóan a Java szintaxisához-, műveleti jelek, jelzések:

• + - összeadás

• - - kivonás

• * - szorzás

• / - osztás

• % - maradékképzés

• & - bitenkénti ÉS művelet

• | - bitenkénti VAGY művelet

• ^ - bitenkénti kizáró VAGY

• = - egyenlő

• > - nagyobb

• < - kisebb

• >= nagyobb vagy egyenlő

• <= kisebb vagy egyenlő

• <> - nem egyenlő

Helyettesítő karakterek (wildcard characters)

Ezek olyan speciális karakterek, amelyek 1 vagy több karakter helyettesítésére szolgálnak, ezáltal képesek vagyunk a lekérdezések tartományait bővíteni.

 

% - "ezután bármi jöhet"

Külső példa: ba% -> barát, baba, bakancs, stb.

 

Például keressük meg az összes L betűvel kezdődő autócsoportnevet (L%):

 

SELECT auto_csop_nev FROM auto_csop WHERE auto_csop_nev LIKE ("L%")

 

Végeredmény:

auto_csop_nev

LUXUS

 

_ (alsó szóköz) - "bármilyen, de csak 1 karakter"

Külső példa: t_r -> tar, tor, túr, stb.

 

Például keressük meg az összes olyan autócsoportnevet, amelyik ráillik a következő mintára (E___A):

 

SELECT auto_csop_nev FROM auto_csop WHERE auto_csop_nev LIKE ("E___A")

 

Végeredmény:

auto_csop_nev

EXTRA

 

[] - "csak tartományon belüli"

Külső példa: t[oú]r -> tor, túr, de nem jó: tar, tőr.

 

^ - a fenti inverze, "nem a tartományon belüli"

Külső példa: t[^oú]r -> tar, tőr, de nem jó: tor, túr.

 

- - "csak hosszabb tartományon belüli"

Külső példa: t[a-ú]r -> tar, tor, tőr, túr.

 

A fentiekkel további, nagyon okos kombinációkat tudunk fabrikálni. Nézzünk erre néhány példát:

• LIKE 'C%' -> összes C betűvel kezdődő szöveg,

• LIKE '%a' -> összes a betűvel végződő szöveg,

• LIKE '%ba%' -> összes ba betűkombinációt tartalmazó szöveg,

• LIKE '_a%' -> összes olyan szöveg, amelynek a 2. karaktere a,

• LIKE 'C%a' -> összes olyan szöveg, amelynek 1. karaktere C és utolsó karaktere a.

Egyéb szintaktikai elemek

• Az általam tapasztalt MySQL-környezet nem követeli meg a sorvégi, utasításlezáró ; karaktert, a kód működőképes nélküle is,

• a ' és " egyes-kettes aposztrófjelek néha keverhetők, de nem mindig! Inkább a ' jelet használjuk!

További SQL-függvényeket, valamint több táblás műveleteket a következő fejezetben tanulmányozhatunk.