2012.05.11.
Adatbázisok I A relációs algebra
2
3
4
1
2012.05.11.
Feladatok
tkód
Tantárgy cím kredit
oktkód
Feladatok 1. Az átlagos kreditpontszám: Γavg(kredit)(Tantárgy)
oktató
2. A Matematika tanszéken oktatók létszáma: Γcount(*)(σtanszék=‘Matematika’ (Oktató))
Oktató név tanszék fizetés
3. A legnagyobb kreditpontszámú tantárgyak címe: Πcím (σkredit=Γmax(kredit) (Tantárgy) (Tantárgy)) 5
Feladatok 4.
6
Az SQL nyelv
Az átlagnál alacsonyabb kreditpontú tantárgyak címe és oktatóik neve: Πcím, név (σkredit<Γavg(kredit) (Tantárgy) (Tantárgy) >
5. Tanszékenként az oktatók létszáma: Γtanszék tanszék, count(*) (Oktató)
• SQL (Structured Query Language) Deklaratív nyelv, 1974-ben publikálták • Halmaz orientált megközelítés, a relációs algebra műveleteinek megvalósítására • Előzménye a SEQUEL (IBM) (Structured English Query Language) Codd., 1971 – Strukturált, – Angolnyelvre épülő – Lekérdező nyelv
7
8
2
2012.05.11.
SQL nyelv
SQL nyelv
• Relációs adatmodellen alapuló • Szabványosított nyelv (ANSI), ISO – – – – – – –
1986 ⇒ SQL86 1989 ⇒ SQL89 1992 ⇒ SQL92 SQL:1999 SQL:2003 SQL:2006 SQL:2008
• Kiterjed az adatkezelő tevékenységekre: – – – –
Adatdefiniáló (DDL) Adatkezelő (DML) Lekérdező (DQL) Vezérlő (DCL) 9
10
SQL nyelv tulajdonságai
SQL felhasználási lehetőségei
• SQL-ben az eredményként kívánt adatokat kell specifikálni, de semmilyen előírást, eljárást nem kell megadni az eredmény előállítására vonatkozóan. • Minden művelet, lekérdezés (Query) inputja és outputja csak adatbázis tábla lehet, kivéve, amikor bizonyos értékeket paraméterként adunk meg. • A lekérdezések több szinten egymásba ágyazhatók. Egy lekérdezés eredmény táblája felhasználható egy másik lekérdezés egyik bemeneti táblájaként.
• Fejlesztőeszközökbe beépítve (űrlap, jelentés) – CASE eszközök – Alkalmazásgenerátorokban pl. uniPaaS
• Interaktív felhasználás (SQL konzol) – Ad hoc lekérdezések – adatbázis karbantartás
• Más programnyelvekbe beépítve – procedurális nyelvekben (pl. C, C#, C++, Java, Php, stb.) – adatbázis-kezelők saját procedurális kiegészítőibe (PL/SQL)
• SQL szkriptek
• Deklaratív, leíró, a műveleti lépéseket kell megadni • Magasszintű parancsok • Nem tartalmaz vezérlési, IO elemeket • Halmazorientált • Bővülő nyelv • Van interaktív és beépülő változata • NEM adatbázis kezelő rendszer, az adatbázis kezelő integráns része • Nem tartalmaz algoritmikus elemeket
11
12
3
2012.05.11.
SQL nyelv tulajdonságai
SQL nyelv tulajdonságai • Bár táblázatokkal dolgozunk, melynek sorai és oszlopai vannak, a rekordok és a mezők mégsem indexelhetők közvetlenül, mint a programozási nyelvekben egy mátrix (kétdimenziós tömb) elemei. Tehát nem hivatkozhatunk közvetlenül egy mező értékére úgy, hogy az i-dik sor j-edik eleme. • Arra azonban van lehetőség, hogy a rekordokat automatikusan sorszámozzuk, s ezt használjuk elsődleges kulcsnak.
• Alapértelmezés szerint minden műveletet a megadott táblák összes rekordjára végrehajtja, ezért nem kell külön ciklusokat definiálni az SQL utasításokban • A procedurális programnyelvektől eltérően, az eredmény táblázatokat nem kell előre deklarálni, a mezőneveket és a típusokat automatikusan definiálja bennük. 13
SQL szintakszisa
14
SQL szintaxisa • SELECT Mely mezők, számítások kerüljenek az eredmény táblába? • FROM Mely táblá(k)ból válassza ki a kívánt adatokat? • WHERE Milyen feltételek alapján válassza ki a rekordokat a táblából? • GROUP BY Mi alapján képezzen csoportokat a rekordokból? • HAVING Milyen feltételek vonatkozzanak a megalkotott csoportokra? • ORDER BY Mely mező(k) szerint rendezze a kiválasztott rekordokat? • A fentiek közül csak a SELECT és a FROM rész a kötelező, a többi opcionális
• Az SQL nyelvben egyetlen parancs, a SELECT parancs szolgál az adatok lekérdezésére • alkalmas a relációs algebra minden műveletének a leképezésére SELECT projekciós rész FROM alaptáblák WHERE szelekciós rész GROUP BY csoportképző kif. HAVING csoportszűrő feltétel ORDER BY mezőlista [ASC|DESC] ; 15
16
4
2012.05.11.
Szelekciós feltétel A feltétel megfogalmazásánál használható operátorok: relációs operátorok
Példa relációk
AUTO
– – – –
(=, !=, <, >, !<, !>, <>, <=, >=) algebrai operátorok (+,-,*,/) logikai operátorok (AND, OR, NOT) o1 [NOT] BETWEEN o2 AND o3: az o1 érték az o2 és az o3 közé esik – o1 [NOT] IN (o2 [,o3,...,oi]): az o1 érték a megadott értékhalmazba esik – o1 [NOT] LIKE 'ss' [ESCAPE 'x']: az o1 string hasonlítása a mintához, ahol %: tetszőleges karaktersorozatot helyettesít _: egyetlen egy karaktert helyettesít – o1 IS [NOT] NULL: annak vizsgálata, hogy az o1 üres-e
TUL
RSZ
TIP
Bordó
EVJ 1991
AR
BKX-720
1
CMT-111
Golf
Piros
1981
350000
2
AAA-156
Trabant
Fehér
1985
100000
3
LUI-999
Opel
Kék
1991
450000
1
KJS-234
Lada
Kék
1989
275000
Szemely
Opel
SZIN
1
1000000
ID
NEV
SZULEV CIM
1
Béla
1975
Budapest
2
Géza
1979
Miskolc
3
Feri
1974
Pécs
17
18
Példa
Példák • Autók rendszámai: SELECT rsz FROM auto; Eredmény: RSZ
• Tulajdonos neve és címe: SELECT nev, cím FROM szemely; Eredmény:
• Az tulajdonosok minden adatát listázzuk ki: SELECT * FROM szemely; Eredmény: ID
NEV
SZULEV CIM
1
Béla
1975
Budapest
CMT-111
NEV
CIM
2
Géza
1979
Miskolc
AAA-156
Béla
Budapest
3
Feri
1974
Pécs
LUI-999
Géza
Miskolc
KJS-234
Feri
Pécs
BKX-720
A * karakter jelöli, hogy minden mezőre kíváncsiak vagyunk 19
20
5
2012.05.11.
Példa • Az autó típusok listája: SELECT tip FROM auto;
Szövegmintát tartalmazó feltétel A Like operátorban adjuk meg a szövegmaszkot, ahol
TIP Opel
– "%": tetszőleges karaktersorozatot helyettesít – "_": egyetlen egy karaktert helyettesít
Golf Trabant
•
Opel
•
Lada
Lista ismétlés nélkül: SELECT DISTINCT tip FROM auto;
• • •
TIP Opel
Olyan lekérdezésekben használatos, ahol nem ismerjük teljes egészében egy mező értékét Pl. Olyan autók melyeknek a rendszáma K betűvel kezdődik, stb. Nyomozásnál hasznos. SELECT Tulajdonos, Rendszam FROM Auto WHERE Rendszam Like "R%";
Golf Trabant Lada 21
Al-lekérdezések (Al-SELECT)
22
Az Al-SELECT operátorai Az al-lekérdezés eredményétől függően különböző operátorokat kapcsolhatunk az al-SELECT-hez: • ha az eredmény egy rekord: skalár operátorok (relációs operátorok) használhatók • ha az eredmény több rekord: halmazoperátorok (IN, ANY, ALL, EXISTS) használhatók
• az SQL támogatja azt a lehetőséget, hogy a szelekciós feltételben nemcsak létező, letárolt adatelemekre hivatkozzunk, hanem számított kifejezéseket is alkalmazhassunk • a számítást egy másik SELECT utasítással tudjuk megadni • tehát az egyik lekérdezés szelekciós feltételében hivatkozunk egy másik lekérdezés eredményére • az al-lekérdezést mindig zárójelben kell megadni, hogy elemei elkülönüljenek • formailag megegyezik a normál SELECT utasítással (kivétel:al-SELECT-ben nem lehet rendezni)
– SELECT …FROM …WHERE m IN (al-select); – SELECT …FROM …WHERE m (relop) ANY (al-select); – SELECT …FROM …WHERE m (relop)ALL (al-select);
• SELECT …FROM …WHERE EXISTS (al-select); (relop): tetszőleges relációs operátor • ANY : a halmaz bármely eleméhez hasonlít, ha egyet talál igazzal tér vissza • ALL : a halmaz minden eleméhez hasonlít, ha akár egyre nem teljesül hamisat ad vissza • EXISTS : az eredményhalmaz üres-e (ha üres, akkor hamissal tér vissza) 23
24
6
2012.05.11.
Példák al-SELECT-re
Példák al-SELECT-re
• Annak az autónak a rendszáma, amelynek ára kisebb mint valamely piros autó ára: SELECT rsz FROM autó WHERE ár < ANY (SELECT ár FROM autó WHERE szin=‘piros’);
• A piros autók áránál kisebb áru autók rendszáma: SELECT rsz FROM autó WHERE ár < ALL (SELECT ár FROM autó WHERE szin=‘piros’);
• Azok az emberek, akiknek nincs autójuk: SELECT s.név, s.cím FROM szemely s WHERE NOT EXISTS (SELECT * FROM autó a WHERE a.tulaj=s.ID); SELECT név FROM szemely WHERE ID NOT IN (SELECT tul FROM autó);
• Írassuk ki a kék színű autó tulajdonosainak nevét: SELECT nev, cim FROM szemely WHERE id IN(SELECT tul FROM auto WHERE szin=‘kék’); NEV
CIM
Béla
Budapest
Feri
Pécs
25
26
Feladatok
Összesített, aggregált értékek
1. Írassuk ki azokat az autókat, amelyek ára az átlagár alatt van.
• Csoportokra képeznek egyértékű mezőt.
SELECT * FROM auto WHERE ar < (SELECT AVG(ar) FROM auto);
– Min (k) – Max (k) – AVG(k) – SUM(k) – COUNT(k)
2. Írassuk ki a legidősebb autó rendszámát, típusát és évjáratát! SELECT rendszam, tipus, evj FROM auto WHERE evj = (SELECT MIN(evj) FROM auto);
3. Írassuk ki az autók összértékét! SELECT SUM(ar) as Érték FROM auto; 27
28
7
2012.05.11.
SQL szerver Management Studio
Az SQL Server
30
SQL Editor ill. Query Designer
SQL Editor ill. Query Designer
31
32
8
