Barna Endre: SQL
TARTALOMJEGYZÉK
1.
Az SQL története és jellemzői
2.
Az SQL objektumai
2.1.
Katalógus táblázatok
2.2.
Táblázat
2.3.
Szinonima
2.4.
Alias név
2.5.
VIEW (látvány)
2.6.
Index
3.
3.1.
A dBASE IV. – SQL használata
Az SQL kezelése
1 / 68
Barna Endre: SQL
3.2.
4.
Az SQL parancsok bevitele
Adatbázis kezelő műveletek
4.1.
A létező AB -ok listázása
- SHOW DATABASE
4.2.
Új AB létrehozás
- CREATE DATABASE
4.3.
Létező AB megnyitása
- START DATABASE
4.4.
Aktív (korábban megnyitott) AB bezárása
4.5.
Létező AB törlése
5.
- STOP DATABASE
- DROP DATABASE
Táblázat kezelő műveletek
5.1.
Táblázat létrehozás
- CREATE TABLE
5.2.
Táblázat Import / Export
- DBDEFINE
5.3.
Táblázat attribútumainak módosítása
- ALTER TABLE … ADD
5.4.
Táblázat törlése
- DROP TABLE
2 / 68
Barna Endre: SQL
6.
Adatkarbantartó mûveletek
6.1.
Feltöltés rekordokkal
6.2.
Adatok (mezők tartalmának) módosítása - UPDATE ... SET
6.3.
Rekordok (sorok) törlése - DELETE
7.
- INSERT INTO ... VALUES
SQL lekérdezések - a SELECT parancs
7.1.
A SELECT parancs szerkezete - SELECT … FROM
7.2.
A WHERE alparancs feltétel listájának szerkezete és elemei
7.3.
A WHERE alparancsban használatos állítások - BETVEEN, IN, LIKE
7.4.
Csoportképző függvények - COUNT, SUM, MAX, MIN, AVG
7.5.
ORDER BY ... alparancs
7.6.
GROUP BY ...
alparancs
3 / 68
Barna Endre: SQL
7.7.
HAWING ...
alparancs
7.8.
Az UNION
alparancs
8.
Látvány létrehozása - CREATE VIEW
9.
Indexelés - CREATE INDEX … ON
1. Az SQL története és jellemzői
1979. az IBM kifejlesztette a SEQUEL egységesített lekérdező nyelvet. Ebből alakult ki az SQL név.
4 / 68
Barna Endre: SQL
Az adatbázis ( későbbiekben AB ) kezelő programokat gyártó szoftveres cégek törekszenek a lekérdező nyelv szabványosítására.
Az ISO 1989 -ben, majd 1992 -ben adott ki SQL’92 szabványt. (A dBASE IV - SQL még az 1989 -es szabvány szerint dolgozik.)
Az SQL fejlett, Relációs AB kezelő, lekérdező nyelv.
Néhány 1992 -es nyelv: INFORMIX, INGRES, MS SQL Server, Novell XQL, ORACLE, SYBASE.
SQL jellemzők:
5 / 68
Barna Endre: SQL
- az adatkatalógus is az AB része,
- az adatdefiníció és az AB kezelés elkülönül,
- az adatdefiníció, -kezelés, -biztonság önállóan is programozható,
- adatcsere lehetősége, más program nyelven kezelt adatállományokkal.
Szakkönyvek: - Novotrade Kiadó: Jack L. és Carolyn J.Hursch - dBASE IV SQL,
- COMPUTERBOOKS Kiadó: Stolnicki Gyula - SQL kézikönyv
- MS Querry - Felhasználói Kézikönyv
6 / 68
Barna Endre: SQL
2. Az SQL objektumai Katalógus táblázatok
Táblázatok ( bázistáblák )
Szinonimák
Alias táblanevek
Látványok ( más néven VIEW -k )
Indexek
2.1. Katalógus táblázatok: az AB paramétereit rögzítő, az adatokat tároló táblázatokkal azonos kiterjesztésű rendszer táblázatok.
7 / 68
Barna Endre: SQL
2.2 Táblázat (más néven Reláció, Egyed típus) : a Relációs AB alapegysége, sorokból (Rekord, Egyed előfordulás) és oszlopokból (Attribútum, Tulajdonság ) áll. A táblázatban adatokat tárolunk, amelyek a sor/oszlop metszetben (Mező, Attribútum előfordulás, Tulajdonság előfordulás) helyezkednek el.
TÁBLANÉV Egyed típus
Tulajdonság típus
Oszlop1
Oszlop2
…
.
OszlopN adat
adat
8 / 68
Barna Endre: SQL
adat
adat
adat
adat
adat
adat
adat
Tulajdonság előfordulás
Egyed előfordulás
9 / 68
Barna Endre: SQL
2.3. Szinonima: a táblázat vagy VIEW számára valamilyen okból új nevet kell adni (a régi is érvényben marad) amellyel szintén hivatkozni lehet rá. (pl. túl hosszú az eredeti név és sokszor kell a parancsokban megadni.)
2.4. Alias név: az eredeti névvel együtt adjuk meg parancsokban, amikor az (általában egymásba ágyazott) hivatkozások ezt igénylik. (rekurzív külső kulcs alkalmazása esetén)
2.5. VIEW (látvány) . látszólagos (virtuális) táblázat.
Azonos módon használható, mint egy valódi táblázat. A program csak a létrehozásához szükséges utasításokat és paramétereket tárolja el és amikor használni kívánjuk újra generálja.
10 / 68
Barna Endre: SQL
A VIEW valódi táblázat(ok) és/vagy másik VIEW (-k) oszlopaiból összeállított Tulajdonság típusokat és szűrőfeltételekkel meghatározott Egyed előfordulásokat tartalmaz.
Fontos tudni, hogy a VIEW -ban végrehajtott módosítások, visszahatnak azokra a táblá(k)ra, amely(ek)ből készült.
A VIEW -k alkalmazásának célja:
- redundancia (adatismétlés) elkerülése,
- adat védelem - megakadályozható, hogy az eredeti táblázattal dolgozzon illetéktelen személy.
- a VIEW létrehozása mindig kapcsolódik, egy őt követő SELECT (kiválasztó) parancshoz, mert meg kell határozni, hogy milyen adatok szerepeljenek a VIEW -ban és ezek honnan származzanak
11 / 68
Barna Endre: SQL
- a VIEW oszlopnevei lehetnek az eredetiek (ahonnan származnak) de adhatunk teljesen új nevet is nekik, VIEW létrehozásával, újraszervezhető az AB pl. két tábla összevonható és az eredeti táblák törölhetők,
- látvány létrehozható tetszőleges számú táblázatból,
- NEM INDEXELHETŐ.
2.6. Index: a táblázat és VIEW sorba rendezése egy vagy több Attribútum szerint. Előre kell létrehozni őket, majd az SQL használata közben már nem kell vele foglalkozni, mert a táblához rendelést a program megteszi. ( Fontos a jó AB tervezés ! )
3. A dBASE IV. – SQL használata
12 / 68
Barna Endre: SQL
3.1. Az SQL kezelése
A dBASE -en belül indítható az SQL.
A dBASE bejelentkezése után, a Parancs sorban ki kell adni a SET SQL ON parancsot, amire megjelenik az SQ L. prompt.
Beállítható a C:DBASE CONFIG.DB állományban, hogy azonnal az SQL. prompt jelentkezzen be:
ASSIST = OFF -ra állítás
és SQL = ON
beírás
13 / 68
Barna Endre: SQL
Az SQL -ből kilépni a prompt után kiadott QUIT paranccsal lehet.
Az SQL és a dBASE között bármikor átválthatunk:
dBASE SQL
dBASE prompt után: SET SQL ON
SQL dBASE
SQL
prompt után: SET SQL OFF
FIGYELEM ! Az SQL táblázat dBASE -ben közvetlenül olvasható, de módosítani TILOS !
14 / 68
Barna Endre: SQL
Ha használni is akarjuk az SQL táblát a dBASE -ben, akkor át kell másolni egy dBASE könyvtárba !
Fordítva is lehetséges az adatcsere, de csak megfelelő SQL paranccsal importálható a dBASE állomány.
dBASE állomány használatba vétele SQL alatt:
Az szükséges dBASE állománynak az SQL könyvtárban kell lennie és ki kell adni a következő parancsot:
DBDEFINE állomány név;
15 / 68
Barna Endre: SQL
Az SQL HELP:
Hasonlóan működik, mint a dBASE IV. -ben, hívható az F1 -gyel vagy a HELP beírásával.
On-line HELP segít a hibás parancs beírásakor is, a hiba valószínű okának megjelenítésével.
3.2. Az SQL parancsok bevitele
SQL parancs bevitelére két mód van:
16 / 68
Barna Endre: SQL
- az SQL. prompt után, egy sorban folyamatosan, max. 254 karakteres lehet,
- a CTRL + HOME leütése után, a bejelentkező szerkesztő képernyőn strukturált formában, max. 1024 karakteres lehet.
Minden parancsot ; (pontosvessző) zár le !
Parancssoros bevitelkor <Enter> -rel indítjuk a végrehajtást,
A szerkesztő képernyős parancsbevitelt esetén, az <Enter> csak a sorváltásra szolgál, a CTRL + END leütéssel történik az érvényesítés.
17 / 68
Barna Endre: SQL
A parancs általános szerkezete:
PARANCSSZÓ < paraméterek > ZÁRADÉK < paraméter > < operátor > < feltétel > ;
pl. parancssoros üzemmódban a Parancs és indítása:
SELECT oszlop1, oszlop2 FROM tábla WHERE adat1 <> 10 ; <Enter>
18 / 68
Barna Endre: SQL
pl. szerkesztő üzemmódban a Parancs és indítása:
SELECT oszlop1, oszlop2 <Enter> - új sor kezdése
FROM tábla <Enter>
WHERE adat1 <> 10 ; < CTRL + END > - a parancs indítása
– a TAB –ot csak egyszer kell hazsnálni, ezután a szerkesztő már automatikusan beállítja a következő sorban.
19 / 68
Barna Endre: SQL
4. Adatbázis kezelő műveletek
4.1 A létező AB -ok listázása SHOW DATABASE
Minden korábban létrehozott AB listázásra kerül, függetlenül az elérési útvonalától. Ez az információ az AB leíró táblában tárolódik és a kezelőnek nem is kell tudnia. (indokát lásd a 4.2. pontban)
20 / 68
Barna Endre: SQL
4.2. Új AB létrehozás CREATE DATABASE [< útvonal>]
Hatására létrejön egy új alkönyvtár a megadott útvonal könyvtárában -vel, amelyben az új AB működési környezetét leíró paraméter táblázatok is létrejönnek. Az AB adminisztrációs állományba pedig eltárolódik az új AB elérési útvonala és neve.
Vigyázni kell, mert ha nem adunk meg útvonalat mindig az indító könyvtárba hozza létre az új AB -t.
21 / 68
Barna Endre: SQL
4.3. Létező AB megnyitása START DATABASE < ABnév >
A szabálytalanul törölt - nem az SQL programon belül, hanem DOS alatt - AB -ok is listázásra kerülnek, amelyeket természetesen nem lehet elindítani. (lásd AB adminisztrációs állományt, amelyből csak akkor törlődik az AB ha SQL –en belül töröltünk.)
4.4. Aktív (korábban megnyitott) AB bezárása STOP DATABASE []
Az megadása nem kötelező, mert úgyis csak az aktuális (futó) adatbázis az, amelyik lezárható.
22 / 68
Barna Endre: SQL
Másik AB indítása automatikusan leállítja a futót, tehát egyszerre csak egy AB használható !
4.5. Létező AB törlése DROP DATABASE
Fontos, hogy előbb be kell zárni az AB -t, majd csak ezután törölhető.
A parancs az AB adminisztrációs állományát aktualizálja, de fizikailag nem törli a könyvtárat ! Ha törölni is akarjuk, akkor azt fájlkezelő programmal tehetjük meg.
5. Táblázat kezelő műveletek
23 / 68
Barna Endre: SQL
5.1. Táblázat létrehozás CREATE TABLE (
...
) ;
Pl. CREATE TABLE mintatabla ( azonosito INTEGER, nev CHAR (25), sz_datum DATE, fizetes DECIMAL (8,2) ) ;
Szerkesztő üzemmódban célszerű dolgozni. A művelet végeztével egy üres adattábla jön létre, amelyet majd feltöltünk adatokkal.
24 / 68
Barna Endre: SQL
A Táblázat méretei:
max. 255 oszlop,
max. 4000 byte / sor (az egy rekordban tárolható karakterek száma),
max. 2 billió byte karakter egy táblában
max. 8 karakter hosszú, betűvel kezdődik és az _ (alsó vonás) karakter lehet benne. Az SQL parancsok betűkombinációi kizártak.
max. 10 karakteres lehet, betűvel kell kezdődnie.
-at és megadásuk módját lásd az alábbi összefoglalóban:
25 / 68
Barna Endre: SQL
5.2. Táblázat Import / Export
Az import lehetőségek csak meghatározott állomány formátumokra érvényesek (mint a dBASE esetében).
Más formátumok esetén célszerű az EXCEL - dBASE konverzió és dBASE -ből már közvetlenül importálható (átvehető) a táblázat.
Importálni lehet üres (ekkor csak a szerkezetét) vagy már adatokkal teli táblát is.
dBASE tábla SQL AB –ba történő importálásának menete:
26 / 68
Barna Endre: SQL
- előbb a dBASE IV. tábla átmásolása az AB aktuális könyvtárába,
- majd a DBDEFINE < táblanév > aktiváló parancs kiadása.
Exportálni dBASE -be nem is kell, mert közvetlenül használható egy SQL tábla a dBASE -ben. A dBASE –be exportált táblázat ott csak olvasható lesz, tilos módosítani
5.3. Táblázat attribútumainak módosítása
- új attribútum (oszlop) hozzáadása:
27 / 68
Barna Endre: SQL
ALTER TABLE ADD ( ,
...
) ;
Az új attribútumok az eredeti tábla attribútumai után kerülnek felvételre, beszúrás nem lehetséges. (De nem is kell, lásd CODD 3. megállapítása.)
Az új attribútumok (mezők) természetesen üresek lesznek, amiket később ki lehet tölteni.
28 / 68
Barna Endre: SQL
Pl. ALTER TABLE mintatabla ADD ( cím telefon CHAR (10) )
katona
CHAR (40),
LOGICAL ;
- attribútum törlés a táblából: NEM LEHETSÉGES
- attribútum sorrend és/vagy típus módosítása: NEM LEHETSÉGES
Figyelem ! Mivel oszlopot törölni, sorrendet vagy típust változtatni nem lehet, de ha erre mégis szükség lenne, akkor ezt csak a tábla újraszerkesztésével tudjuk megoldani. Ekkor az eredeti táblából a leválogatás (SELECT) parancs segítségével létrehozható a kívánt tartalmú új tábla majd a régit töröljük. (lásd később)
29 / 68
Barna Endre: SQL
5.4. Táblázat törlése
DROP TABLE
A parancs hatására nem csak a tábla, hanem minden hozzá kapcsolódó objektum is automatikusan törlődik!
Ez a parancs nem jó a tábla tartalmának törlésére ( teljes adattartalom ürítésére) ! Ha törölni kell a tábla tartalmát, lásd a 6.3. pontot.
6. Adatkarbantartó műveletek
30 / 68
Barna Endre: SQL
6.1. Feltöltés rekordokkal
- teljes adatsor (egy teljes rekord) felvitele beszúró utasítással:
INSERT INTO VALUES ( adat1, adat1, . . . , adatN ) ;
31 / 68
Barna Endre: SQL
A felviendő adatok az eredeti tábla attribútum sorrendjében, vesszővel elválasztva kerülnek felsorolásra.
Az adat típusok megadási szabálya:
Numerikus adat: a szeparátor karakter tizedes pont Pl. 3.45, 120, 2003, 78
Karakteres:
Dátum:
az adat idézőjelek közé illesztendő Pl. ”abc” , ”FERI” , ”Audi”
dátum formátumban kapcsos zárójelek között, ponttal elválasztva { 9999.99.99 }
vagy szöveges formátumban de karakterről dátum formátumra átalakító függvény argumentumaként CTOD ( '9999/99/99' ) Pl. { 2001.02.23 } , CTOD ( ”2001/02/23 )
Logikai: a logikai értéket pontok közé kell írni IGAZ: .T. .t. .Y. .y. HAMIS: .F. .f. .N. .n.
Pl. INSERT INTO mintatabla ( 3412, ”Kiss Gergő”, {1990.07.10}, 12345.60, ”Mór Fő u. 12.”, ”22-134627”, .Y. ) ;
32 / 68
Barna Endre: SQL
- nem teljes adatsor (a rekord nem minden adatának) beszúrása:
Ekkor a VALUES előtt fel kell sorolni, hogy melyik attribútumok (oszlopok) adatait kívánjuk felvinni. Az adatlistában a felsorolás sorrendjében kell az adatokat elhelyezni.
INSERT INTO (
Pl.
INSERT INTO mintatabla (azonosíto, nev, katona ) VALUES ( 1231, ”Körösi Bea”, .N. ) ;
33 / 68
Barna Endre: SQL
- beszúrás másik táblázatból:
Ebben az esetben egy már létező táblázatból írhatunk át teljes (vagy meghatározott attribútumokat tartalmazó) rekordot sőt egyszerre több rekordot is az aktuális táblába.
Az, hogy a forrás táblából melyik rekordokra vonatkozzon az átírás (beillesztés) szűrő feltétellel határozzuk meg, illetve az áthozandó attribútumokat projekcióval választjuk ki.
Fontos! Csak azonos típusú és kisebb vagy egyenlő hosszú adat szúrható be másik táblából.
INSERT INTO SELECT < oszlopnév lista> FROM [ WHERE ]
34 / 68
Barna Endre: SQL
Megjegyzés: a szűrést (ha szükséges) a WHERE záradék (alparancs), a projekciót a SELECT parancs hajtja végre.
6.2. Adatok (mezők tatalmának) módosítása
Az adatkarbantartás ilyen módon elég nehézkes. Mindig valamilyen feltételnek megfelelő rekordban történik a mező tartalom módosítás. A feltétel megadása vonatkozhat egy vagy több attribútum értékre, amely feltétel fennállása esetén a kijelölt adat módosításra kerül. Feltételben szerepelhet maga a módosítandó adat is.
35 / 68
Barna Endre: SQL
A feltétel természetesen kijelölhet egy vagy több rekordot is, amely(ek)ben a mező tartalom módosítás végrehajtásra kerül.
Figyelem ! Ha nem adunk meg feltételt, a tábla minden rekordjában módosul a kijelölt mező tartalma. Erre egyébként a program fel is hívja a kezelő figyelmét egy üzenettel.
A mező tartalom módosítása történhet egy konstanssal, amely minden feltételnek megfelelő rekordban felülírja a mezőtartalmat, és történhet egy kifejezésse l , amely egy számítási eljárás eredményét írja be a megfelelő mezőbe.
- minden attribútum előfordulás (az egész oszlop) tartalmának módosítása: (nincs WHERE)
36 / 68
Barna Endre: SQL
UPDATE SET = | ;
pl. UPDATE mintatabla SET fizetes = 80000.00 ; - mindenki fizetése 80 ezer forint lesz
UPDATE mintatabla SET fizetes = (fizetes * 1.2) ; - mindenki 20 %-os fizetésemelést kapott
37 / 68
Barna Endre: SQL
- adott feltételnek megfelelő tételsorok módosítása:
UPDATE SET = | WHERE ;
pl. UPDATE mintatabla SET fizetes = (fizetes * 1.2) WHERE sz_datum < {1980.01.01} ;
- csak az 1980. január 1. előtt születettek fizetése emelkedik 20 %-kal.
38 / 68
Barna Endre: SQL
6.3. Rekordok (sorok) törlése
DELETE FROM WHERE
Figyelem ! A WHERE nélkül kiüríti az egész táblázatot.
Pl.
DELETE FROM mintatabla WHERE nev = ”Kovács Béla” ;
- minden Kovács Béla nevűt töröl az állományból.
39 / 68
Barna Endre: SQL
7. SQL lekérdezések - a SELECT parancs
Előbb, mindig szövegesen (köznyelvi formában) kell megfogalmazni, hogy mit kívánunk végrehajtani.
Válasszunk ki a következő attribútumokat (oszlopokat), és/vagy használjunk csoportosító függvény(eke)t
A kiválasztás történjen a következő táblázat(ok)ból és/vagy WIEW -(k)ból ... [egyesítse a táblákat]
A kiválasztás feltételei a következők ...
Az eredményt rendezze a következő feltétel(ek) szerint …
40 / 68
Barna Endre: SQL
Az eredményt csoportosítsa a következő feltétel(ek) szerint …
A csoportokból válasszon ki egy feltétel szerinti csoportot …
Tegye a következőt, a kiválasztott adatokkal ;
7.1. A SELECT parancs szerkezete
SELECT [ DISTINCT ] ,
FROM [ UNION ]
WHERE
ORDER BY
41 / 68
Barna Endre: SQL
GROUP BY
HAVING
SAVE TO TEMP <újtábla neve> [ ] [ KEEP ] ;
A SELECT SQL parancs, a többi ALPARANCS. (más néven ZÁRADÉK vagy KLAUZA )
[ DISTINCT ] - kiegészítő parancs, azt jelenti, hogy a feltétel alapján kiválasztott, esetlegesen azonos tartalmú rekordok közül a kiírás csak az egyiket jelenítse meg.
- azon attribútum nevek felsorolása vesszővel elválasztva, amelyeket a lekérdezésben meg akarunk jeleníteni (ez egy projekció). Az attribútumok ebben a sorrendben fognak majd kiírásra kerülni. Fontos: ha több táblából akarunk attribútumokat megjeleníteni a lekérdezésben, és a táblákban esetleg egyes attribútumoknak azonos a nevük, akkor a hivatkozás a táblanév. attribútumnév formájában történhet (azaz meg kell adni a forrástábla nevét majd kell egy . (pont) és ezt követi az attribútum neve)
- az SQL speciális csoport képző függvényei (lásd 7.4. pontban)
42 / 68
Barna Endre: SQL
FROM - azoknak a tábláknak a nevei, amelyek attribútumait felsoroltuk az attribútum listában
[ UNION ] - két (vagy több) táblázat azonos típusú és hosszú adataira vonatkozóan, adat duplikálás mentes egyesítési parancs
WHERE - egy (vagy több) attribútumra vonatkozó konkrét feltétel (a feltétel megadás módját lásd 7.2. az állításokat lásd a 7.3. pontban)
ORDER BY - egy (vagy több) attribútum név, amely(ek) sorrendjében látni kívánjuk az eredményt (a rendezés több attribútum esetén „egymásba ágyazott”)
43 / 68
Barna Endre: SQL
GROUP BY - egy (vagy több) attribútum név, amelyek szerint csoportosítani kívánjuk a megjelenő eredményt (ez is egymásba ágyazott eredményt ad)
HAVING - a GROUP BY után alkalmazható alparancs, ezzel megadható, hogy csak azok a csoport eredmények jelenjenek meg, amelyek a feltételnek eleget tesznek
SAVE TO TEMP <újtábla neve> [ ] [ KEEP ]
- ha a lekérdezés eredményét később használni akarjuk (nem csak megtekinteni), akkor alkalmazandó a SAVE TO TEMP alparancs
SAVE TO TEMP <újtábla neve> - annak az új táblának a neve, amelybe el akarjuk menteni a lekérdezés eredményét
44 / 68
Barna Endre: SQL
[ ] - ha az új táblában nem akarjuk a lekérdezésben szereplő minden attribútumot szerepeltetni, akkor itt külön felsorolhatjuk azokat, amelyekre szükség van, ha minden adatot el akarunk menteni, nem kell megadni semmit.
[ KEEP ] - ha megadjuk, akkor a tábla az AB bezárása után is megmarad, egyébként csak addig „él” amíg be nem zárjuk az aktuális AB –t.
Figyelem ! A KEEP –elt táblát a program az indító könyvtárban helyezi el és nem az AB saját könyvtárában.
Pl. SELECT oszlop2, oszlop8, tabla1.oszlop1, tabla23.oszlop1, view4.oszlop5
FROM tabla1, tabla23, view4
WHERE oszlop2 <> 2 * oszlop8 AND tabla1.oszlop1 = 100 OR view4.oszlop5 !< 500
ORDER BY oszlop8
45 / 68
Barna Endre: SQL
GROUP BY tabla23. oszlop1, oszlop2
HAVING SUM (oszlop2) < 50
SAVE TO TEMP ujtabla KEEP ;
Fontos megjegyzés !
A SELECT paranccsal elsősorban szűrés (leválogatás) történik bár a SAVE TO TEMP paranccsal az említett megszorítássokkal létrehozható új tábla is, de nem jöhet létre VIEW.
Ha a SELECT -tel AB táblát vagy VIEW -t akarunk létrehozni, meg kell előzze a CREATE TABLE … illetve a CREATE VIEW … utasítás.
A létrehozó ( CREATE ) utasítást közvetlenül követi a lekérdezés, a fenti szerkezetben, az AS előtét paranccsal.
CREATE TABLE
46 / 68
Barna Endre: SQL
AS SELECT . . . a lekérdezés folytatása
Figyelem ! A SELECT sorrendje szigorúan azonos, a megelőző CREATE ... utasítás listájának sorrendjével.
7.2. A WHERE alparancs feltétel listájának szerkezete és elemei
A feltétel egy vagy több relációból állhat. Az összetett feltétel zárójelezhető, relációk és logikai műveletek szerepelnek benne.
47 / 68
Barna Endre: SQL
A feltétel bal oldala: a vizsgált attribútum neve
A feltétel jobb oldala: a feltétel értéke, amely lehet konstans vagy kifejezés (számított érték)
Közöttük a reláció jel van: =, <, >, <=, >=, <> vagy != (mindkettő nem egyenlőt jelent), !<, !> a nem kisebb illetve nem nagyobb jele.
A feltétel logikai operátorral összekötve több relációt is tartalmazhat, az operátorok: NOT, AND, OR
A két összehasonlítandó adatnak azonos típusúnak kell lennie, a numerikus típusok azonban típustól függetlenül hasonlíthatók.
A karakteres adatok hasonlításánál a kis és NAGY betű eltérőnek számít, mert a hasonlítás az ASCII kódok alapján történik.
Ha több azonos nevű oszlop is van, itt is együtt kell szerepelnie a táblanévnek az oszlopnévvel. (ugyanúgy mint a SELECT -nél)
7.3. A WHERE alparancsban használatos állítások
48 / 68
Barna Endre: SQL
Az állítások szerkezete: WHERE ÁLLÍTÁS
- a BETVEEN állítás
WHERE [ NOT ] BETWEEN AND
A mező tartalma beleesik az értékhatárokba, illetve kívül esik [ NOT ] a megjelölt értékhatárokon.
49 / 68
Barna Endre: SQL
Csak BETWEEN esetén a határok is elfogadott értékek, NOT BETWEEN esetén a határok nem elfogadott értékek.
A határértékek numerikus, karakter és dátum típusúak lehetnek.
Pl. WHERE fizetes BETWEEN 50000 AND 80000
WHERE sz_dat NOT BETWEEN {1978.06.12} AND {2000.01.31}
- az IN állítás
WHERE [ NOT ] IN ( érték1, érték2, . . . , értékN )
50 / 68
Barna Endre: SQL
A mező tartalma, IN esetén a zárójelek közt felsorolt értékek valamelyike, NOT IN esetén egyik felsorolt értékkel sem egyező.
A lista tartalmazhat numerikus, karakter és dátum típusú konstansokat.
Pl. WHERE azonosito IN ( 2312, 3521, 4581, 3499 ) feldolgozásban
WHERE azonosito NOT IN ( 2312, 3521, 4581, 3499 ) feldolgozunk
- csak ezek vesznek részt a
- ezeken kívül mindenkit
- a LIKE állítás
51 / 68
Barna Endre: SQL
WHERE [ NOT ] LIKE ”karaktersorozat”
A LIKE után megadott karaktersorozatot tartalmazza a mező, illetve NOT LIKE esetén nem tartalmazza.
Ez az állítás csak karakteres adattípusú adatokra vonatkozhat
A hasonlító karaktersorozatban használhatók JOKER karakterek. A % jel karakter sorozatot helyettesít, az alsó vonás _ egyetlen karaktert helyettesít.
Itt is lényeges a kis és a NAGY betű különbözősége !
Pl. WHERE nev LIKE ”Kovács%” - a Kovács vezetéknevűeket választja ki
WHERE nev LIKE „”%Piroska” - a Piroska keresztnevűeket választja ki
52 / 68
Barna Endre: SQL
WHERE cím NOT LIKE „”MÓR%” - csak azokat nem választja ki akiknek a lakcíme MÓR –ral kezdődik, DE a Mór vagy mór kezdetűt már ki fogja választani.
7.4. Csoportképző függvények
A függvények szerkezete: FÜGGVÉNYNÉV ( argumentum )
( argumentum ) - annak az attribútumnak a neve, amelyre a függvény vonatkozik, vagy lehet egy attribútumot tartalmazó kifejezés is.
A COUNT esetén az argumentum lehet a * (csillag) is, amikor a tábla rekordjainak
53 / 68
Barna Endre: SQL
darabszámát akarjuk lekérdezni.
COUNT ( ) - tétel (rekord) számláló,
SUM ( ) - oszlop összegét számítja ki,
MIN ( ) - az oszlopban szereplő minimum. értéket adja vissza,
MAX ( ) - az oszlopban szereplő maximum. értéket adja vissza,
AVG ( ) - az oszlop értékeinek átlagát számítja ki
A függvényeket általában a SELECT után alkalmazzuk. A COUNT kivételével mindegyik csak numerikus típusú mezőre alkalmazható.
pl. SELECT COUNT ( * ) FROM dolgozo ;
- a dolgozo állomány tételszámát adja
54 / 68
Barna Endre: SQL
SELECT COUNT ( * ) FROM nevsor WHERE V_NEV ="Kovács"; - a névsorban található Kovács vezetéknevűek száma
SELECT MAX(fizetes), MIN(fizetes), AVG(fizetes) FROM dolgozo; - a dolgozói fizetések maximumát, minimumát és átlagát adja meg
7.5. ORDER BY ... alparancs
ORDER BY [ ASC | DESC ]
55 / 68
Barna Endre: SQL
- vesszővel elválasztott oszlopnév (attribútum) felsorolás vagy
<sorszám> - a SELECT –ben felsorolt oszlopnév lista elemeinek sorszáma, vesszővel elválasztva
[ ASC ] - a rendezési sorrend emelkedő sorrendű legyen, nem szükséges kiírni mert ez az alapértelmezés
[ DESC ] - a rendezési sorrend csökkenő sorrendű legyen, ezt ha szükséges meg kell adni
Ha a rendezés 2 vagy több elem (attribútum) szerint történik a rendezés egymásba ágyazó jellegű. Ez azt jelenti, hogy az első helyen szereplő attribútum egyezősége esetén veszi figyelembe a második helyen szereplő, a második egyezősége esetén a harmadik (és így tovább) helyen szereplő attribútumok értékét a végső sorrend kialakításában.
Pl. SELECT név, cím, telefon FROM mintatábla ORDER BY cím ;
SELECT név, cím, telefon FROM mintatábla ORDER BY cím DESC, név ;
SELECT név, cím, telefon FROM mintatábla ORDER BY 2 DESC, 1 ;
56 / 68
Barna Endre: SQL
7.6. GROUP BY ... alparancs
GROUP BY
Csoport műveletek (összegzés, min / max érték, darabszám vagy átlagérték csoportonkénti kiíratásra szolgáló alparancs. Az oszlop tartalma alapján (az azonos mező értékekből) csoportot képez, majd a csoportból csak egyetlen sort jelenít meg. A csoportképző függvényt a SELECT -ben kell definiálni. (lásd a 7.1. pontot)
Előírás, hogy az előtte levő SELECT utasítás tökéletesen meg kell egyeznie a GROUP BY .
Pl. SELECT cím, név, MAX (fizetes) FROM mintatábla GROUP BY cím ;
57 / 68
Barna Endre: SQL
- a parancs kiírja annak az azonos címen lakó személynek a címét, nevét és keresetét, aki az adott címen a legnagyobb fizetéssel rendelkezik.
7.7. HAWING ... alparancs
HAWING
A GROUP BY után használva, egy csoport értékre ( összegre, minimumra, stb.) vonatkozó
58 / 68
Barna Endre: SQL
feltétel.
Figyelem ! Ha nem GROUP BY után használjuk, az egész táblát egyetlen csoportnak tekinti.
Pl. SELECT cím, név, MAX (fizetes) FROM mintatábla GROUP BY cím
HAWING MAX (fizetes) > 100000 ;
Az előző kiíratásból csak a 100 ezer forintnál magasabb fizetésűeket választja ki eredményül.
59 / 68
Barna Endre: SQL
7.8. UNION alparancs
Abban az esetben használjuk, ha két (vagy több) állományból ki szeretnénk íratni azonos jellegű (például cím) adatot. Ilyenkor a forrás állományok adatait egymás után írja úgy, hogy a többszörösen előforduló adatokból csak egyet visz át az eredmény táblába. A forrásadatokra azonban szigorú előírások vonatkoznak. Teljesen azonos típusúnak (még a numerikus típusnak is) és azonos hosszúnak kell lennie a forrás adatoknak. Az attribútum neveknek ( mezőneveknek) azonban nem kell azonosaknak lenni. Az összevonás több, a feltételnek megfelelő adatot is érinthet !
pl. SELECT helyseg FROM lakohely - A helyseg, cim és lakas nevű adatokat emeli ki UNION és vonja össze a cimke nevű, véglegesen
SELECT cim FROM cimlista
UNION
elmentett táblába úgy, hogy az esetleg kettő
vagy mindhárom állományba szereplő azonos
SELECT lakas FROM nevsor
cím adatokból csak egyet tart meg címke
60 / 68
Barna Endre: SQL
SAVE TO TEMP cimke KEEP ;
táblában.
8. Látvány VIEW létrehozása
A VIEW tehát egy látszólagos táblázat. Fizikailag nem létezik, csak a létrehozásához szükséges utasítások tárolódnak el és ha a feldolgozás során hivatkozunk rá ezen utasítások alapján létrejön a memóriában egy „virtuális” tábla.
A VIEW táblaként viselkedik, azaz módosítható az adata, ami visszahat az eredeti táblára is. De még újabb látvány is készíthető belőle.
A VIEW egy vagy több, létező tábla adataiból áll össze, szűrés (szelekció) illetve attribútum kiválogatás (projekció) útján. Ebből következik, hogy létrehozása (CREATE VIEW) során meg kell adni a létrehozandó VIEW nevét, attribútumainak neveit, valamint a forrás attribútum neveket és azt, hogy ezek melyik táblában vannak.
CREATE VIEW [ ( ) ] AS SELECT ( )
[ WITH CHECK OPTION ] ;
61 / 68
Barna Endre: SQL
- az új látvány neve, amellyel később hivatkozunk rá
[ ( ) ] - az új látvány attribútumainak felsorolása. Ha nem adjuk meg a -t, a forrás tábla vagy táblák (lásd ) mindegyike az eredeti nevével és a felsorolás sorrendjében átkerül a VIEW –ba. Ha az attribútum neveket vagy akár csak a sorrendet meg akarjuk változtatni, már meg kell adni a -t.
- azoknak az attribútumoknak a nevei, amelyek átkerülnek majd a VIEW –ba
( ) - annak a táblának (azoknak a tábláknak) a neve(i), amelyekből származnak az attribútumok
62 / 68
Barna Endre: SQL
[ WITH CHECK OPTION ] - ha a látványt adat módosításra is használjuk, akkor célszerű megadni, a nem megengedett módosítás letiltása érdekében.
Pl. CREATE VIEW telefonok ( név, tel_szam, lakcím )
AS SELECT mintatábla ( név, telefon, cím ) ;
Minden mintatábla rekordból kiíratjuk a telefonok nevű látványba a név, telefon és cím adatot. A látványban új nevet kapott a telefon és a cím.
CREATE VIEW mortelefon
AS SELECT mintatábla ( név, telefon, cím )
WHERE cím LIKE ”Mór%”
63 / 68
Barna Endre: SQL
GOUP BY név ;
A mortelefon nevű látványban az eredeti névvel szerepelnek az attribútumok, viszont csak a Móron lakók adatait soroljuk fel névsorban.
9. Indexelés
Az adatfeldolgozási feladatok gyorsabb elvégzését a jól megtervezett INDEX állományok létrehozása biztosítja.
Az index tulajdonképpen egy előre definiált álladó sorrend.
Az adattáblák módosításával együtt az index állományok is azonnal módosulnak.
Az AB összes indexét a SYSIDXS.DBF rendszertábla tartalmazza.
Egy táblázathoz, egy indexállományban max.47 indexelési sorrendet adhatunk meg.
64 / 68
Barna Endre: SQL
- Index létrehozása:
CREATE [ UNIQUE ] INDEX [ ]
ON < ( oszlopnév lista ) > [DESC] ;
[ UNIQUE ] - ha azt akarjuk, hogy az index egyben annak a táblának amelyre vonatkozik az indexelés egyedi azonosítója is legyen (csak egyszer fordulhasson elő a táblázatban), akkor egyedi UNIQUE indexet kell definiálni
[ ] - külön index név megadása nélkül, . MDX jön létre.
65 / 68
Barna Endre: SQL
- annak a táblának a neve, amelyre az indexelés vonatkozik
< ( oszlopnév lista ) > - a tábla azon attribútumainak a felsorolása, amelyek szerint a rendezettségnek érvényesülnie kell. Itt is érvényesül az egymásba ágyazott rendezettség. (lásd az ORDER BY parancsnál)
[DESC] - a rendezettség csökkenő sorrendiségét állítja be, a növekvő sorrend az alapértelmezett
Pl. CREATE UNIQUE INDEX alapsor ON mintatábla (azonosító) ;
66 / 68
Barna Endre: SQL
A mintatáblához létrehoztunk egy alapsor nevű indexet, amely azonosító növekvő sorrendben rendez és egyúttal az azonosító attribútumot „kineveztük” egyedi azonosítónak is.
CREATE INDEX nevsor ON mintatábla (cím, név) ;
Az index nevsor néven egy lakcím ezen belül névsoros rendezettséget hozott létre.
- Index megszüntetése:
Ha nincs szűkség egy rendezettségre, akkor az index meg is szüntethető.
67 / 68
Barna Endre: SQL
DROP INDEX ;
68 / 68
