SQL alapok

SQL alapok

SQL (Sequental Query Language , Structure Query Language). ) nyelv:

Olyan deklaratív nyelv, mely az adatbázisoknál alkalmazva halmaz orientált megközelítéssel, a relációs algebra műveleteinek megvalósítására alkalmas .

Adatbázist jellemzi, hogy egymással valamilyen szempontból kapcsolatban lévő adatok jól szervezett halmazát alkotja, mely számítógépen tartósan, hosszabb ideig tárolt és egyidejűleg több felhasználó párhuzamosan használhatja.

Az SQL parancsok egyszerű angol mondatok, melyek egyszerűen értelmezhetők és azt fogalmazzuk meg hogy  milyen adatokat szeretnénk megkapni.

Az SQL parancsok végrehajtása közben az adatbázis motor egy kiválasztó, optimalizáló algoritmusa határozza meg, hogy konkrétan milyen elemi lépések során, milyen módon, miket használva  állítja elő  a kért eredményt.

SQL szabványok

SQL-86, SQL-89, SQL-92, SQL:1999, SQL:2003, SQL:2006, SQL:2008
Jellemzően követő szabvány Számos adatbázis motor gyártó specifikus 'tájszólással'.  

Az SQL szabvány lekérdezés mellett lehetővé teszi: 

• Tárolt adatok karbantartását (új adatok, módosítás, törlés) 
• Sémák, szabályok definiálását (relációk, attribútumok) 
• Tárolt adatok visszakeresését  
• Hozzáférés korlátozását (felhasználók) 
• Adatok megosztását (konkurens felhasználók) 
• Adat integritás ellenőrzését (integritás kényszerek) 

SQL szintaktikai szabályok:

• Az egyes SQL parancsokat pontosvesszővel zárjuk le.
• A kis és nagybetűket nem különbözteti meg.
• A parancsok szóközökkel, tabulátorokkal tagolhatók és igény szerint több sorba írhatók.
• A szöveg konstansok szimpla aposztrófok között pl. 'alma'

SQL nyelv elemei

• Adat definíciós nyelv DDL (séma definíció és módosítás) CREATE, ALTER, DROP
• Hozzáférés (Access Control) GRANT, REVOKE
• Adat manipulációs nyelv DML INSERT, UPDATE, DELETE
• Lekérdezés (Query Language)   SELECT 
• Tranzakció kezelés COMMIT, ROLLBACK

Megszorítások (constraint)

Az adatbázis tábláinak és mezőinek használata közben automatikusan hatő kényszeritő szabályok.

Egy oszlopra vonatkozó megszorítások

• NULL az attribútum definíciójában arra utal, hogy az adat megadása nem kötelező,  ez az alapértelmezés ezért a legritkább esetben írják ki. 
• NOT NULL az attribútum definíciójában arra utal, hogy az adat megadása kötelező,    azaz nem vihető be olyan sor a relációban, ahol az így definiált adat nincs kitöltve. 
• PRIMARY KEY ez az oszlop a tábla elsődleges kulcsa. 
• UNIQUE ez az oszlop a tábla kulcsa. 
• CHECK(feltétel) csak feltételt kielégítő értékek kerülhetnek be az oszlopba. 
• [FOREIGN KEY] REFERENCES reláció_név [ (oszlop_név) ], ez az oszlop külső kulcs 

Több oszlopra vonatkozó megszorítások 

• PRIMARY KEY(oszlop1[, oszlop2, ...]) ezek az oszlopok együtt alkotják az elsődleges kulcsot. UNIQUE(oszlop1[, oszlop2, ...]) ezek az oszlopok együtt kulcsot alkotnak. 
• CHECK(feltétel) csak feltételt kielégítő sorok kerülhetnek be a táblába. F
• OREIGN KEY (oszlop1[, oszlop2, ...]) 
• REFERENCES reláció(oszlop1[, oszlop2, ...]),    az oszlopok külső kulcsot alkotnak a megadott tábla oszlopaihoz.

Példák:

CREATE TABLE uj_tablam (   

tbl_azon       INT2 PRIMARY KEY,   

nev               VARCHAR(14) NOT NULL,  

cim               VARCHAR(13) NOT NULL,  

CONSTRAINT  unev UNIQUE(nev));

• ALTER TABLE uj_tablam ADD uj_vez int2 NOT NULL;
• ALTER TABLE uj_tablam DROP COLUMN uj_vez;
• DROP TABLE uj_tablam;   

A tábla kiürítés parancs a kényszerek (alkalmazott külső kulcs hivatkozás) miatt nem mindig hajtható végre!

• CREATE INDEX belepes_index ON alkalmazott(belepes);
• DROP INDEX belepes_index;
• CREATE USER user_név IDENTIFIED BY jelszó;
• DROP USER név;
• ALTER USER név IDENTIFIED BY jelszó;

INSERT  – új sorok hozzáadása a relációhoz
               INSERT INTO tablam (név1, név2, ...) VALUES (érték1, érték2, ...); 

UPDATE – egy vagy több oszlop tartalmának módosítása
             UPDATE tablam SET név1 = érték1, név2 = érték2, ... WHERE logikai feltétel; 

DELETE – sorok törlése a relációból
             DELETE tablam   [WHERE logikai feltétel]; -- (Feltétel nélkül a tábla teljes tartalmát törli! )

SELECT - eredmény sorok megjelenítése, listázása
              SELECT * FROM tablam WHERE logikai feltétel ; 
               SELECT nev1, nev2, ... FROM tablam WHERE logikai feltétel ; 

A sorok egyediségének biztosítása SELECT DISTINCT attribútum1, attribútum2, … FROM reláció_név; 

Logikai feltétel 

Operátor  Értelmezés 
= egyenlő
!= <> ^= nem egyenlő
> nagyobb
>= nagyobb egyenlő
< kisebb
<= kisebb egyenlő
BETWEEN x AND y adott értékek közé esik
IN (a, b, c, ...) az értékek között található
LIKE minta hasonlít a mintára ( speciális elemei % és _  )
NOT Logikai tagadás
AND Logikai és
OR Logikai vagy

Precedenciák:  

•  =, !=, <>, ^=, >, >=, <, <= 
•  NOT 
•  AND 
•  OR

A SELECT eredmény lista szűkítőfeltételek közé tartozik az ALL, DISTINCT,  TOP. 

• Alapértelmezett működés az összes listázása. (ALL) 
• A DISTINCT a kiválasztott mezők szerint ismétlődő adatok elhagyására szolgál. 
• A TOP-pal a lista tartomány tetejéről választhatunk ki rekordokat

Több táblás lekérdezések 

Descartes szorzat :

• SELECT * FROM reláció1, reláció2; 

Egyen összekapcsolás:

Általában a táblákat szorosan kell illeszteni. Ezzel a művelettel a két táblának azok a rekordjai kombinálódnak, melyek egy közös mezőben azonos értéket tartalmaznak. A szoros illesztésű lekérdezés az egyik tábla minden olyan rekordját visszakeresi, amelyik megfelel egy másik tábla rekordjainak.

• SELECT * FROM tablam1, tablam2 WHERE tablam1.nev1 = tablam2.nev2; 
• SELECT * FROM tablam1 INNER JOIN tablam2 ON tablam1.nev1 = tablam2.nev2;

Ha a kapcsolómezők típusa nem szám, akkor típusuk és tartalmuk fajtája szerint egyezniük kell. 

Tábla összekapcsolása önmagával:

• SELECT alias1.nev1, alias2.nev2, … FROM tablam AS alias1, tablam AS alias2;

Külső összekapcsolás:

 A laza külső illesztésű lekérdezés annyiban különbözik a szoros illesztésütől, hogy mindenképpen visszaadja az egyik illesztett tábla rekordjait, akkor is, ha nincsenek kapcsolódó rekordok a másikban. A baloldali illesztés a bal oldali tábla rekordjaihoz kapcsolja a másik tábla azon rekordjait, melyek az illesztési feltételeknek megfelelnek. A jobb oldali illesztés ugyanez, csak ott a jobb oldali tábla rekordjaihoz kapcsolja a másik tábla, illesztésnek megfelelő rekordjait.

Az illesztési műveletek egymásba ágyazhatók. Ha illesztett lekérdezést készítünk annak előnye, hogy az illesztett táblákat is úgy kezeli, mintha ugyanabban a táblában lennének, melyhez illesztve lettek. 

• SELECT * FROM tablam1 LEFT | RIGHT JOIN tablam2 ON tablam1.nev1 = tablam2.nev2;

Táblák közötti halmaz műveletek:

• SELECT … UNION SELECT ...; 
• SELECT … INTERSECT SELECT ...; 
• SELECT … EXCEPT SELECT ...;

Egymásba ágyazott lekérdezéseknél két lehetőség van:

• a beágyazott SELECT egy rekordot ad vissza 
• a beágyazott SELECT több rekordot add vissza 

Speciális összehasonlítások: exists, in, not in, > all, < any

Korrelált lekérdezés – a beágyazott lekérdezés visszautal a külső lekérdezésre, a belső lekérdezés a külső lekérdezés minden egyes rekordjára újra kiértékelésre kerül

SELECT nev FROM osztaly o WHERE NOT EXISTS (

SELECT * FROM alkalmazott WHERE oszt_azon = o.oszt_azon); 

--- (osztályok, melyeknek nincs dolgozója )

Nézet 

A nézetek olyan virtuális táblák, amelyek a fizikai táblákat felhasználva a tárolt adatok más és
más logikai modelljét, csoportosítását tükrözik. Nézetek a
CREATE VIEW <nézetnév> [(<oszlopnév> [, <oszlopnev>, ...])] 
AS <lekérdezés>;  
A nézet lekérdezésre az egyedüli megkötés, hogy rendezést nem tartalmazhat. 

Indexek 

Az indexek a táblákban való keresést gyorsítják meg.

CREATE [UNIQUE] INDEX <indexnév>
ON <táblanév> (<oszlopnév> [, <oszlopnév> , ...]);
Ha indexet az UNIQUE kulcsszóval definiáltuk, a rendszer biztosítja, hogy az adott oszlop csak egyedi értékeket kaphat

Néhány adatbázis fogalom

Adatbázis séma: 

az adatbázis szerkezete, amit tervezéskor, egyszeri alkalommal hozunk létre, ritkán változtatjuk. Az adatbázis séma megadja, hogy az adatbázisban milyen egyedek vannak, ezek milyen tulajdonságokkal és kapcsolatokkal rendelkeznek. A szerkezet leírása a az adatmodellek jelölési rendszerével történik.

Adatbázis előfordulás 

alatt az adatbázis pillanatnyi tartalmát értjük Általában a tervezés befejezésekor, az adatbázis használatba vételekor történik az adatok elsődleges betöltése az adatbázisba. A mindennapi használat során az adatbázis előfordulás, a tartalom az, ami állandóan változik. Az adatbázis tartalmának változtatásához az adatmodell műveleti halmazát használjuk. 

 Tábla (table): 

az adattárolás alapegysége. Az adatok struktúrált tárolásáért felelős. Oszlopokból és sorokból áll mely megfeleltethető egy relációnak, az oszlopoknak megadható a típusa, amely meghatározza, hogy a sorok (a rekordok) az adott mezőn milyen jellegű adatot tartalmazhatnak.. A tábla, a tábla oszlopai és a táblában tárolt rekordok egyedien azonosíthatóak.  

Partíciók (partitioning): 

A tábla partíciókra osztható, a rekordok csoportjai szét vannak osztva az egyes partíciók között (vízszintes felbontás). Alapértelmezetten az egész tábla egyetlen partíció, nincsen felbontás. Többpartíciós táblákkal skálázhatóbbá tehető az adatbázis, de bevezetésük inkább nagyméretű tábláknál javasolt, ahol sok erőforrás spórolható meg ha nem a tábla egészét szükséges módosítani/lekérni/karbantartani. Kívülről nézve a tábla, mint logikai egység atomi, a partícionálás transzparens módon működik.

Nézet (view): 

virtuális tábla. Különböző táblákból összeállított adatok megjelenítésére alkalmas. Fizikai tárolása azonban nincs, az adatokat csak származtatja a táblákból. Az adatbázisban is csak a nézetet létrehozó utasítás (SQL select) tárolódik. A nézetek alkalmasak arra, hogy korlátozzák a felhasználó által látható adatokat illetve, hogy átformálják az adatokat. Erre lehet példa táblák összekapcsolása, értékek aggregálása (összegzése) egy oszlopban.

Kulcs (key): 

az adat, amely alapján biztosan azonosítható a rekord. Egy táblának lehet több kulcsa is, de csak egyetlen elsődleges kulcsa és csak erre jönnek létre automatikusan indexek.

 Index: 

Az adatok a táblákban rendezetlenül helyezkednek el. Az indexek a rekordokhoz való hozzáférést gyorsítják és külön struktúrában tárolódnak az adatbázisban. Általában olyan oszlopra érdemes indexet létrehozni, amelyre várhatóan gyakran lesz megfogalmazva keresés. Az indexek épülhetnek olyan oszlopokra is, amelyekre nincs megkövetelve a rekordok egyedisége.

Kétféle index létezik: 
 - Fürtözött index (clustered index): az indexrekordok tárolási sorrendje megegyezik az adatrekordok tárolási sorrendjével. Mivel egynél több ellentmondásmentes rendezési sorrend nem feltétlen definiálható fürtözött index is csak egyetlen oszlopra épülhet. Az SQL Serverben a fürtözött indexek B*-fa struktúrában tárolódnak. Az indexfa alsó szintjén pedig adatblokkok (SQL Server terminológiában lapok) szerepelnek.
 - Nem-fürtözött index (non-clustered index): az indexrekordok sorrendje független a tárolási sorrendtől. A nem-fürtözött indexek csak olyan táblákon hozhatók létre, amelyeken szerepel már fürtözött index. Szintén B*-fa struktúrában tárolódnak ám az alsó szint speciális. Ha a tábla rendelkezik fürtözött index-szel, akkor a megfelelő kulcs lesz a levélelemben. Ha ilyen nincs, akkor egy mutató az adatrekordra.

Kényszer (constraint):

 az adatintegritást elősegítő szabályok egy oszlop mezőire. A tábla rekordjainak be kell tartaniuk ezen szabályokat. Miután létrehoztuk a kényszereket az SQL Server automatikusan ellenőrzi a betartásukat.

Elsődleges kulcs (primary key): 

gyakorlatilag a kitöltött és egyediség kényszerek együtt.

Külső kulcs (foreign key): 

az oszlop mezői csak egy másik tábla rekordjainak megfelelő mezőjéből kerülhet ki. A külső kulcs kényszer tehát táblák közötti kapcsolatok azonosítására szolgál.

 Alapértemezett érték (defaults): 

a kényszerekhez hasonló módon oszlopokra definiálható. Az alapértelmezett értéket hordozza, amivel inicializálódik a rekord, amennyiben a létrehozásakor az érintett mezőt üresen hagyták. Az érték-jellegű kényszerekhez hasonlóan bonyolult számítások eredménye is lehet egy alapértelmezett érték, de akár egy konstans is.

 Tárolt eljárás és függvény (stored procedure): 

egy tárolt eljárás T-SQL utasítások gyűjteménye, amely a szerveroldalon futtatható (pl. egy bonyolult fáradtságos többtáblát érintő feltételes adatmódosítás) egyetlen felhasználói utasításként. Annak megfelelően, hogy ad-e vissza értéket vagy sem beszélhetünk tárolt eljárásról illetve tárolt függvényről.

 Trigger: 

Adatbázis eseményekre aktiválódó tárolt eljárás. Közvetlenül nem hívható meg.

Sémák (Schemas) 

Az SQL Server sémái az adatbázison belül elhelyezkedő névterek, amelyek objektumok csoportjait tárolják (pl. táblákat, nézeteket). Minden objektum az adatbázisban sémákba van rendezve. A sémák hozzá lehetnek rendelve egy felhasználóhoz vagy adatbázis-szerephez, korlátozva ezzel az általuk elérhető objektumokat az adatbázison belül. A saját sémában levő objektumokra nem kell előtaggal együtt hivatkozni. Ha egy felhasználó hozzá akar férni más séma objektumához és van erre jogosultsága ezt megteheti, amennyiben a másik sémában levő objektumra a séma nevével együtt hivatkozik

Szinonimák (Synonyms) 

A nem saját sémában levő objektumokhoz való hozzáféréshez a teljes nevükkel kell rájuk hivatkozni. Ez feltételezi a másik séma ismeretét is, ami kellemetlen követelmény. A sémagazdák és az erre jogosultak létrehozhatnak szinonimákat a saját sémájukban a külső sémákban levő objektumokra (amennyiben ahhoz hozzáférnek természetesen). A szinonima ezután bárhol használható, ahol a névtérrel kiegészített objektumnévre lenne szükség. A szinonimák tehát alternatív névként funkcionálnak elfedik a külső séma jellegzetességei és egyben erőforrások is, amelyek engedélyekhez köthetők, így rugalmasabbá tehetők a sémák közötti műveletek