Most a következő helyen jársz: Adatbázis >> Elmélet >> Az SQL nyelv alapjai

Kilencedik lecke

Az SQL nyelv alapjai

SQL = Structured Query Language (struktúrált lekérdező nyelv). A relációs adatbázis-kezelés szabványos nyelve. Deklaratív nyelvek csoportjába tartozik (nem algoritmikus nyelv) nem tartalmaz algoritmus szerkezeteket (elágazás, ciklus stb.), de algoritmikus nyelvekbe beépíthető (beágyazott SQL). Az SQL halmaz orientált a relációs algebrán alapuló nyelv.

Fejlődése:

  • 1976: SEQUEL (= Structured English QUEry Language) az SQL eredeti változata, IBM-nél fejlesztették ki.
  • 1981: ORACLE 2 (SQL alapú RDBMS, nagygépre).
  • 1983: IBM: DB2 (SQL alapú RDBMS, nagygépre). A világ legnagyobb adatbázisait ma is jórészt DB2-ben kezelik.
  • 1986: szabvány SQL, az ANSI (= American National Standards Institute) definiálta.
  • 1992: SQL2 szabvány.
  • SQL3 (rekurzió, triggerek, objektum-relációs modell)

Négy utasításcsoportot foglal magában:

Adatdefiníciós utasítások (Data Definition Language – DDL), amelyek objektumok létrehozására, módosítására, törlésére valók.

Adatmanipulációs utasítások (Data Manipulation Language – DML), amelyek rekordok felvitelére, módosítására és törlésére alkalmazhatók.

Adatkezelő utasítások (Data Query Language – DQL), amelyekkel a letárolt adatokat tudjuk visszakeresni.

Adatvezérlő utasítások (Data Control Language – DCL), amelyekkel az adatvédelmi és a tranzakció-kezelő műveletek végrehajthatóak.

Az SQL utasítások kulcsszavakból (SQL names, keywords), azonosítókból, műveleti jelekből, literálokból (számszerű, dátumjellegű, szöveges konstansok) állnak. Minden utasítást pontosvesszővel kell lezárni.

Az SQL bemutatására a következő relációsémával rendelkező táblákat használom.

autó - tulajdonos sémák

Az auto táblában rsz a rendszámot jelöli és kulcs szerepet tölt be. A tul mező külső kulcsként a tulajdonost azonosítja, a tulaj tábla szazon mező értékei közül kerülhet ki.

Adatdefiníciós utasítások

Adattáblák létrehozása

CREATE TABLE táblanév
( oszlopnév adattípus  [feltétel],
...,
oszlopnév adattípus [feltétel]
[, táblaFeltételek]
);

Az adattípusok (Access-ben):

  • Szám, decimal, integer, smallint, real, double
  • Szöveg (1-255 byte), text()
  • Logikai
  • Dátum
  • Számláló (8 byte)
  • Pénznem
  • Feljegyzés
  • Ole objektum
  • HiperhivatkozásAz adattípushoz "DEFAULT érték" megadásával alapértelmezett érték definiálható. Ha ilyet nem adunk meg, az alapértelmezett érték NULL.

Oszlopfeltételek (egy adott oszlopra vonatkoznak):

NOT NULL | NULL

PRIMARY KEY: elsődleges kulcs

UNIQUE: kulcs

REFERENCES tábla(oszlop) [ON-feltételek]: külső kulcs

Táblafeltételek (az egész táblára vonatkoznak):

PRIMARY KEY (oszloplista): elsődleges kulcs

UNIQUE (oszloplista): kulcs

FOREIGN KEY (oszloplista) REFERENCES tábla(oszloplista) [ON-feltételek]: külső kulcs

Ha a (külső) kulcs több oszlopból áll, akkor csak táblafeltétel formájában adható meg.

Az SQL89 értelmezése szerint a NOT NULL záradék előírja, hogy az oszlop mezői mindíg érvényes adatot tartalmazzanak, NULL-kifejezés tilos. Ha elhagyjuk e záradékot, akkor a NULL-kifejezés az oszlopban megengedett lesz, és ha új sor létesítésekor a mezőbe nem írunk adatot, akkor annak tartalma NULL kifejezés lesz. A NULL záradék előírja, hogy amennyiben meghatározott értéket nem kapna az oszlopbeli bármely mező, akkor az adott oszloptípushoz illő semleges értéket (pl. üres karakterláncot, számtani nullát stb.) adjon a rendszer azon mezőnek.

A tábla módosításakor a definiált kulcsfeltételek automatikusan ellenőrzésre kerülnek. PRIMARY KEY és UNIQUE esetén ez azt jelenti, hogy a rendszer nem enged olyan módosítást illetve új sor felvételét, amely egy már meglévő kulccsal ütközne.

A példa táblák létrehozása

autó - tulajdonos séma

create table tulaj (
szazon text(11) primary key,
nev text(30),
cím text(40);

create table auto(
rsz text(7) primary key,
tipus text(20),
szin text(20),
ar money,
evj decimal,
tul references tulaj(szazon);

Relációséma törlése

DROP TABLE táblanév;

Hatására a séma és a hozzá tartozó adattábla törlődik.

Relációséma módosítása

ALTER TABLE táblanév
[ADD (újelem, ..., újelem)]
[MODIFY (módosítás, ..., módosítás)]
[DROP (oszlop, ..., oszlop)];

Az újelem: egy "oszlopnév adattípus [feltétel]", vagy egy "táblafeltétel", mint a CREATE TABLE utasításban.

A módosítás: "oszlopnév adattípus [feltétel]".

Adatmanipulációs utasítások.

A táblába új sor felvétele

INSERT INTO táblanév [(oszloplista)]
VALUES (értéklista);

Ha oszloplista nem szerepel, akkor valamennyi oszlop értéket kap a CREATE TABLE-ben megadott sorrendben. Egyébként, az oszlopnév-listában nem szereplő mezők NULL értéket kapnak. A táblába adatokat tölthetünk át másik táblából is, ha a VALUES(értéklista) helyére egy allekérdezést írunk.

A példatábla feltöltése:

insert into tulaj
values (27110142233, Kovács Ákos, Szerencs Sugár út 24);

insert into auto
values (abc-123, Trabi, kék, 130000, 1990, 27110142233);

Sor(ok) módosítása

UPDATE táblanév
SET oszlop = kifejezés, ..., oszlop = kifejezés
[ WHERE feltétel ];

Az értékadás minden olyan soron végrehajtódik, amely eleget tesz a WHERE feltételnek. Ha WHERE feltétel nem szerepel, akkor az értékadás az összes sorra megtörténik.

Példának nézzük azt az esetet, amikor módosítani akarom a tulaj tábla név értékét. Mivel egy sort tartalmaz a tábla, így nem kell kijelölni a módosítandó sort.

update tulaj
set nev = "Kovács Kázmér";

Ha több sort tartalmazna a tábla, akkor minden sor név mezőjét az előbbi névre változtatná.

Sor(ok) törlése

DELETE FROM táblanév
[ WHERE feltétel ];

Hatására azok a sorok törlődnek, amelyek eleget tesznek a WHERE feltételnek. Ha a WHERE feltételt elhagyjuk, akkor az összes sor törlődik (de a séma megmarad). Ilyenkor a tábla szerkezete megmarad, csak a tartalma törlődik.

Példának töröljük a tulaj tábla egyetlen rekordját.

delete from tulaj;

Adatlekérdező utasítás

A lekérdező nyelv egyetlen utasításból áll, mely számos alparancsot tartalmazhat, és a lekérdező utasítások többszörös mélységben egymásba ágyazhatók. Arra használjuk, hogy egy vagy több adathalmazból (reláció) egy adathalmazt állítsunk elő. A bemeneti adatokon, a relációs algebra műveleteit hajtjuk végre, és kapunk egy eredmény táblát.

A SELECT utasítás az alábbi alparancsokból állhat:

SELECT [DISTINCT] oszloplista --> projekció

FROM táblanévlista -->Descartes-szorzat

[WHERE feltétel] --> szelekció

[GROUP BY oszloplista] --> csoportosítás

[HAVING feltétel] --> csoport-feltétel

[ORDER BY oszloplista]; --> rendezés

Az egyes alparancsok megadási sorrendje a fent leírttak szerint történik, végrehajtási sorrendjük viszont az alábbi:

1. FROM --> Descartes-szorzat

2. WHERE --> szelekció

3. GROUP BY --> csoportosítás

4. HAVING --> csoport-szelekció

5. SELECT  projekció

6. ORDER BY --> rendezés

A SELECT utasítás alapváltozata:

SELECT [DISTINCT] oszloplista
FROM táblanévlista
[WHERE feltétel];

A "SELECT A1,...,An FROM T1,...,Tn WHERE feltétel" utasítás egyenértékű a következő relációs algebrai kifejezéssel:

relációs algebrai kifejezés

Vagyis, a felsorolt táblák Descartes-szorzatából szelektáljuk a feltételnek eleget tévő sorokat, majd ezekből projekcióval választjuk ki az E eredménytábla oszlopait. A DISTINCT opciót akkor kell kiírni, ha az azonos sorokból csak egyet kívánunk megtartani. Ha oszloplista helyére * karaktert írunk, ez valamennyi oszlop felsorolásával egyenértékű. A SELECT legegyszerűbb változatával adattábla listázását érhetjük el.

A WHERE záradékban feltételt kell megadni, és a rendszer csak a feltételnek megfelelő sorokat válogatja ki. A kifejezések elemei lehetnek oszlopok nevei, operátorok, mezőkre vonatkozó függvények. Csak azonos típusú kifejezéseket hasonlíthatunk össze (pl. numerikust numerikussal stb.). Az összehasonlításra használható operátorok: <, >, <=, >=, =, valamint nem egyenlő: != vagy <>.

Egy mező értéke, két határérték között van-e?

mező érték BETWEEN érték1 And érték2

Tartalmazást:

mező érték IN (érték1, érték2, …)

Szöveg mintaillesztése:

mező érték LIKE illesztő szöveg

Illesztő karakterek: *, ?, #, [karakterlista],   [!karakterlista]

Például listázzuk ki az auto tábla adatait!

select * from auto;

A továbbiakba tekintsük a következő adatokkal feltöltött és a relációsémának megfelelő táblát.

autó séma

rsz

tipus

szin

ar

evj

tul

abc-123

Trabi

kék

130000

1990

27110142233

bbb-222

Lada

piros

250000

1988

17812202244

dfa-333

Lada

kék

200000

1985

16612214477

A relációs algebra műveleteinek megvalósítása:

Projekció:

SELECT [DISTINCT] A1,...,An FROM T;

Példa:

SELECT DISTINCT tipus FROM auto;

Visszaadja a típusokat az auto táblából. A DISTINCT miatt csak az egyedi értékeket válogatja ki.

Szelekció:

SELECT * FROM T WHERE feltétel;

Példa:

SELECT * FROM auto ar > 200000;

Visszaadja a 200000-től nagyobb értékű autók minden adatát.

Descartes-szorzat: T1 x T2

SELECT * FROM T1,T2;

Példa:

SELECT * FROM auto, tulaj;

Megkapjuk a két tábla Descartes szorzatát.

Természetes összekapcsolás

Állítsuk elő azt az eredménytáblát, amely a tulajdonoshoz rendeli az autója rendszámát és a típusát.

SELECT tulaj.szazon, tulaj.nev, auto.rsz, auto.tipus
FROM tulaj, auto
WHERE tulaj.szazon = auto.tul;

Halmazműveletek relációk között

Unió:

(SELECT * FROM T1)
UNION
(SELECT * FROM T2);

Metszet:

(SELECT * FROM T1)
INTERSECT
(SELECT * FROM T2);

Különbség:

(SELECT * FROM T1)
MINUS
(SELECT * FROM T2);

Az eredménytábla rendezése

Bár a relációs modell nem definiálja a rekordok sorrendjét, a gyakorlatban rendszerint valamilyen rendezettségben kívánjuk látni az eredményt. Erre szolgál az
ORDER BY oszlopnév [DESC], ..., oszlopnév [DESC]
alparancs, amely a SELECT utasítás végére helyezhető, és az eredménytáblának a megadott oszlopok szerinti rendezését írja elő. Alapértelmezés szerint a rendezés növekvő sorrendben történik, ha fordítva kívánjuk, a DESC (descending) kulcsszó írandó a megfelelő oszlopnév után.

Például készítsünk egy olyan lekérdezést, amely visszaadja az auto tábla rekordjait ar mező szerint novekvő rendezettséggel.

SELECT * FROM auto ORDER BY ar;

Csoportosítás (GROUP BY, HAVING)

Ha a tábla sorait csoportonként szeretnénk összesíteni, akkor a SELECT utasítás a
GROUP BY oszloplista
alparanccsal bővítendő. Egy csoportba azok a sorok tartoznak, melyeknél oszloplista értéke azonos. Az eredménytáblában egy csoportból egy rekord lesz. Az összesítő függvények csoportonként hajtódnak végre. A SELECT után összesítő függvényen kívül csak olyan oszlopnév feltüntetésének van értelme, amely a GROUP BY-ban is szerepel.

A GROUP BY által képezett csoportok közül válogathatunk a
HAVING feltétel
alparancs segítségével,amelynél csak a feltételnek eleget tevő csoportok kerülnek összesítésre az eredménytáblába.

Összesítő függvények

Egy oszlop értékeiből egyetlen értéket hoznak létre (például átlag). Általános alakjuk:

függvénynév ( [DISTINCT] oszlopnév )

Ha DISTINCT szerepel, akkor az oszlopban szereplő azonos értékeket csak egyszer kell figyelembe venni. A számításnál a NULL értékek figyelmen kívül maradnak. Az egyes függvények:

  • AVG: átlagérték.
  • SUM: összeg.
  • MAX: maximális érték.
  • MIN: minimális érték.
  • COUNT: elemek száma.

Ennél a függvénynél oszlopnév helyére * is írható, amely valamennyi oszlopot együtt jelenti.

Irodalomjegyzék: