Adatbázis-technológia

1. Adatbázis-technológia 5. előadás Az SQL

2. Az előadás célja • ismertetni az SQL jelentőségét; • bemutatni az SQL kialakulásának történetét; • bemutatni az adatdefiniálás módját az SQL-ben; • bemutatni a különböző adatkezelési módokat, úgymint adatfelvitel, adatmódosítás és -lekérdezés; • bemutatni az adatelérési jogok korlátozásának lehetőségeit az SQL-ben; • megmutatni, hogy az SQL hogyan használható egy harmadik generációs programnyelven belül.

3. Az előadás vázlata • Az SQL története. • Előnyök és hátrányok. • Adatdefiniálás. • Adatkezelés. • Adatlekérdezés. • Adatvezérlés: az adatelérés korlátozása.

4. Történet • SEQUEL (Structured English QUEry Language): az IBM kísérleti relációs adatbázis-kezelő rendszerének (System R) relációs nyelve. • SQL (Structured Query Language):az IBM kereskedelmi adatbázis-kezelő rendszereinek (DB2, SQL/DS) relációs nyelve • SQL1: ANSI 1986 • SQL2: ISO 1992 • SQL3: tervezés alatt

5. Az SQL felépítése SQL Adatdefiní-ciós nyelv Adatke-zelő nyelv Adatve-zérlő nyelv CREATEALTERDROP SELECTUPDATEINSERTDELETE GRANTREVOKE

6. Az SQL 1992-es szabványa már egységes lekérdező nyelv SQL Adatdefiní-ciós nyelv Adatke-zelő nyelv Adatve-zérlő nyelv CREATEALTERDROP SELECTUPDATEINSERTDELETE GRANTREVOKE

7. Adatbázis létrehozása CREATESCHEMA kölcsönző;létrehozza a “kölcsönző” nevű adatbázist. CREATESCHEMA kölcsönző AUTHORIZATION Fruzsina;létrehozza a “kölcsönző” nevű adatbázist, és ezt az adatbázist a “Fruzsina” azonosítójú felhasználó használhatja. (SQL1: CREATEDATABASE kölcsönző; )

8. FLOAT(p) REAL DOUBLE PRECISION INTEGER SMALLINT DECIMAL(p,s) BIT(n) CHARACTER(n) DATE TIME TIMESTAMP Adattípusok

9. Táblázat definiálása CREATETABLE ügyfél (ügyfélkód CHAR(8) NOT NULL,ügyfélnév CHAR(30) NOT NULL,ügyfélcím CHAR(40),telefon CHAR(10),szdátum DATE,belépés DATE, PRIMARYKEY (ügyfélkód)); CREATETABLE kölcsönző.ügyfél (...

10. Táblázat definiálása idegen kulccsal CREATETABLE kölcsönzés (szszám CHAR(6) NOT NULL,ügyfél CHAR(8) NOT NULL,dátum DATENOT NULL,időpont TIME NOT NULL,kidő INT(2),kdíj DEC(10,2),CONSTRAINT f1 PRIMARYKEY (szszám),CONSTRAINT f2 FOREIGNKEY (ügyfél) REFERENCES ügyfél(ügyfélkód))ON DELETE CASCADEON UPDATE CASCADE;

11. További táblázatok I. CREATETABLE kazetta (kazettakód CHAR(10) NOT NULL,kategória CHAR(2) NOT NULL,kazettacím CHAR(30) NOT NULL,év INT(4),rendező CHAR(6),PRIMARYKEY (kazettakód),FOREIGNKEY (rendező) REFERENCES személy(személykód));

12. További táblázatok II. CREATETABLE szereplő (kazettakód CHAR(10) NOT NULL,személykód CHAR(6) NOT NULL ,PRIMARYKEY (kazettakód,személykód),FOREIGNKEY (személykód) REFERENCES személy(személykód)); Összetett kulcs

13. Adatbázis törlése DROPSCHEMA kölcsönző CASCADE;a “kölcsönző” nevű adatbázis törlése minden tartozékával együtt. DROPSCHEMA kölcsönző RESTRICT;a “kölcsönző” nevű adatbázis törlése csak akkor történik meg, ha az adatbázis üres.

14. Táblázat törlése DROPTABLE kölcsönzés CASCADE;a “kölcsönzés” nevű táblázat törlése minden hivatkozásával együtt. DROPTABLE kölcsönzés RESTRICT;a “kölcsönzés” nevű táblázat törlésére csak akkor kerül sor, ha semmilyen hivatkozás nem vonatkozik rá.

15. Táblázat módosítása ALTERTABLE kölcsönző.ügyfél ADD t INT(1) DEFAULT 0;a “kölcsönző” nevű adatbázis “ügyfél” nevű táblázatát kiegészítjük egy “t” nevű oszloppal a törzstagság jelzésére. Ennek az oszlopnak az alapértéke 0. ALTERTABLE kölcsönző.ügyfélDROP belépés CASCADE;a “kölcsönző” nevű adatbázis “ügyfél” nevű táblázatából töröljük a “belépés” nevű oszlopot.

16. Adatok táblázatba írása INSERTINTO ügyfél VALUES (‘92051802’,’Kiss Pál’, ’József u. 23.’,’454454’); INSERTINTO ügyfél(ügyfélkód, ügyfélnév) VALUES (‘94022303’,’Kiss Katalin’); INSERTINTO ügyfél(ügyfélnév, ügyfélcím) VALUES (’Szabó Sándor’,’Mária u. 16.’);

17. Adatok táblázatba írása INSERTINTO ügyfél VALUES (‘92051802’,’Kiss Pál’, ’József u. 23.’,’454454’); INSERTINTO ügyfél(ügyfélkód, ügyfélnév) VALUES (‘94022303’,’Kiss Katalin’); INSERTINTO ügyfél(ügyfélnév, ügyfélcím) VALUES (’Szabó Sándor’,’Mária u. 16.’); Ez az utasítás nem lesz végrehajtva, mert a “szám” nevű mező nem kapott értéket, holott “nemnulla” kulcsként definiáltuk.

18. Táblázatsor(ok) törlése DELETEFROM ügyfél WHERE ügyfélnév=‘Szabó János’; DELETEFROM kölcsönzés WHERE dátum<‘1996-01-01’; DELETEFROM kölcsönzés;

19. Táblázatsor(ok) módosítása UPDATE kölcsönzésSET ügyfélcím=‘Mária u. 20’WHERE ügyfélkód=‘92051802’; UPDATE ügyfélSET t=1WHERE ügyfélkód<‘96010100’;

20. A SELECT utasítás általános szerkezete SELECT <tulajdonságtípusok listája>FROM <táblázatnevek listája>[WHERE <feltétel>][GROUP BY <csoportosító tulajdonságtípusok>][HAVING <csoportfeltétel>][ORDER BY <tulajdonságtípusok listája>];

21. Példa a SELECT utasításra (P0) SELECT * FROM ügyfél; A teljes “ügyfél” táblázat megjelenítése.

22. Példa a SELECT utasításra (P1) SELECT * FROM ügyfél WHERE t = 1; Az “ügyfél” táblázatból megjeleníti a törzstagokat. (Relációs algebra: SELECT)

23. Példa a SELECT utasításra (P2) SELECT * FROM ügyfél WHERE ügyfélkód>‘95010100’ AND ügyfélkód<‘96010100’; Az “ügyfél” táblázatból az 1995-ben belépett ügyfelek minden adatát megjeleníti. (Relációs algebra: SELECT)

24. Példa a SELECT utasításra (P3) SELECT ügyfélnév, ügyfélkód FROM ügyfél; Az “ügyfél” táblázatból az ügyfelek nevét és azonosító kódját jeleníti meg. (Relációs algebra: PROJECT)

25. Példa a SELECT utasításra (P4) SELECT ügyfélnév, ügyfélkód FROM ügyfélWHERE t = 1; Az “ügyfél” táblázatból a törzstagok nevét és azonosító kódját jeleníti meg. (Relációs algebra: PROJECT+SELECT)

26. Példa a SELECT utasításra (P5) SELECT szszám, kdíj FROM kölcsönzés WHERE ügyfél=‘92051802’ AND dátum<‘1996-03-31’; A “kölcsönzés” táblázatból a 92051802 azonosítójú ügyfél 1996 március 31. előtti kölcsönzéseinek számlaszámát és a kölcsönzési díjat jeleníti meg. (Relációs algebra: PROJECT+SELECT)

27. Példa a SELECT utasításra (P6) SELECT ügyfél FROM kölcsönzés; A “kölcsönzés” táblázatból megjeleníti az ügyfél azonosító kódját. (Minden ügyfél annyiszor szerepel, ahányszor kazettát kölcsönzött!)

28. Példa a SELECT utasításra (P7) SELECT DISTINCT ügyfél FROM kölcsönzés; A “kölcsönzés” táblázatból megjeleníti az ügyfél azonosító kódját. (Minden ügyfél csak egyszer szerepel!)

29. Példa a SELECT utasításra (P8) SELECT * FROM kölcsönzés, ügyfél WHERE ügyfél=ügyfélkód; A “kölcsönzés” táblázatot kiegészíti az “ügyfél” táblázatból az ügyfél minden adatával. (Relációs algebra: NATURAL JOIN).

30. Példa a SELECT utasításra (P9) SELECT szszám, ügyfélnév FROM kölcsönzés, ügyfél WHERE ügyfél=ügyfélkód AND kidő=4; A “kölcsönzés” és “ügyfél” táblázat összekapcsolá-sával azokat a számlaszámokat és ügyfélneveket jeleníti meg, ahol a kölcsönzés 4 órára szólt. (Relációs algebra: NATURAL JOIN+SELECT+PROJECT).

31. Példa a SELECT utasításra (P10) SELECT kölcsönzés.szszám, ügyfél.ügyfélnév FROM kölcsönzés, ügyfél WHERE kölcsönzés.ügyfél=ügyfél.ügyfélkód AND kölcsönzés.kdíj=4; Ugyanazt az eredményt adja, mint az előző példa, csak jobban kihangsúlyoztuk, hogy egy tulajdonság melyik táblázatban található.

32. Példa a SELECT utasításra (P11) SELECT DISTINCT dátum FROM kölcsönzés WHERE ügyfél IN (SELECT ügyfélkód FROM ügyfélWHERE szdátum<‘1937-01-01’); Milyen napokon voltak kölcsönözni az 1937 előtt született ügyfelek.

33. Példa a SELECT utasításra (P12) SELECTSUM(kdíj), MAX(kdíj), MIN(kdíj),AVG(kdíj) FROM kölcsönzés; A “kölcsönzés” táblázat alapján kiszámolja és megjeleníti a kölcsönzési díjak összegét, a kölcsönzési díjak maximális, minimális és átlagos értékét.

34. Példa a SELECT utasításra (P13) SELECTSUM(kdíj), MAX(kdíj), MIN(kdíj),AVG(kdíj) FROM ügyfél, kölcsönzés WHERE ügyfél=ügyfélkód AND t=1; A “kölcsönzés” táblázat alapján kiszámolja és megjeleníti a törzstagok által fizetett kölcsönzési díjak összegét, maximális, minimális és átlagos értékét.

35. Példa a SELECT utasításra (P14) SELECT COUNT(*) FROM ügyfél; Az “ügyfél” táblázat sorainak számát, azaz az ügyfelek számát adja.

36. Példa a SELECT utasításra (P15) SELECT COUNT(DISTINCT ügyfél) FROM kölcsönzés WHERE dátum=>‘1997-02-01’ AND dátum=<‘1997-02-28’ ; 1997 februárjában hány ügyfél kölcsönzött kazettát.

37. Példa a SELECT utasításra (P16) SELECT * FROM kazetta GROUP BY rendező; A “kazetta” táblázat megjelenítése úgy, hogy az filmek a rendezők szerint vannak csoportosítva.

38. Példa a SELECT utasításra (P17) SELECT rendező, COUNT(*) FROM kazetta GROUP BY rendező; A “kazetta” táblázat alapján az jelenik meg, hogy egy rendezőnek hány filmje található a kölcsönző-ben.

39. Példa a SELECT utasításra (P18) SELECT * FROM kazetta ORDER BY rendező, kazettacím; A “kazetta” táblázat megjelenítése úgy, hogy az azonos rendezőjű filmek egymás után, betűrend-ben jelennek meg, és a rendezők is betűrendben szerepelnek.

40. Példa a SELECT utasításra (P19) SELECT ügyfélnév FROM ügyfél WHERE (SELECT COUNT(*) FROM kölcsönzés WHERE ügyfél=ügyfélkód)>=20; A legalább 20 kölcsönzést teljesítő ügyfelek nevét jeleníti meg.

41. Példa a SELECT utasításra (P20) SELECT ügyfélnév, COUNT(*) FROM ügyfél, kölcsönzés WHERE ügyfél=ügyfélkódGROUP BY ügyfélkód HAVINGCOUNT(*)>20; A húsznál több kölcsönzést lebonyolító ügyfelek nevét és kölcsönzéseinek számát jeleníti meg.

42. Virtuális táblázatok(látványok, nézetek) definiálása CREATEVIEW teljes kölcsönzés AS SELECT * FROM kölcsönzés, ügyfél WHERE ügyfél=ügyfélkód; A (P8) eredményét virtuális táblázatként definiáljuk. Az így létrehozott nézetek táblázatként viselked-nek, és ugyanúgy használhatók SQL utasítások-ban, kivéve az adatmódosításokat.

43. Példa virtuális táblázatra (P21) CREATEVIEW ügyfél95 ASSELECT ügyfélkód, ügyfélnévFROM kölcsönzés, ügyfél WHERE ügyfél=ügyfélkód ANDdátum>=1995-01-01 AND dátum<=1995-12-31; Az 1995-ben kölcsönzést teljesítő ügyfelek kódját és nevét tartalmazó nézet létrehozása.

44. Példa virtuális táblázatra (P22) CREATEVIEW ügyfél96 ASSELECT ügyfélkód, ügyfélnévFROM kölcsönzés, ügyfél WHERE ügyfél=ügyfélkód ANDdátum>=1996-01-01 AND dátum<=1996-12-31; Az 1996-ben kölcsönzést teljesítő ügyfelek kódját és nevét tartalmazó nézet létrehozása.

45. Táblázatok egyesítése (P23) SELECT * FROM ügyfél95 UNIONSELECT * FROM ügyfél96 ORDER BY ügyfélnév; Az 1995-1996-os kétéves időszakban kölcsönzést teljesítő ügyfelek kódját és nevét tartalmazó lekérdezés. (Relációs algebra: UNION)

46. Táblázatok különbsége (P24) SELECT * FROM ügyfél95 EXCEPTSELECT * FROM ügyfél96 ORDER BY ügyfélnév; Azoknak az ügyfeleknek kódját és nevét adja meg az ügyfélnév szerint rendezve, akik 1995-ben kölcsönöztek kazettát, de 1996-ban már nem. (Relációs algebra: DIFFERENCE)

47. Táblázatok metszete (P25) SELECT * FROM ügyfél95 INTERSECTSELECT * FROM ügyfél96 ORDER BY ügyfélnév; Azoknak az ügyfeleknek kódját és nevét adja meg az ügyfélnév szerint rendezve, akik 1995-ben is és 1996-ban is kölcsönöztek kazettát. (Relációs algebra: INTERSECTION)

48. Adatelérés korlátozása I. Az SQL lehetővé teszi, hogy megfelelő felhasználó-azonosítás mellett az egyes felhasználóknak kü-lönböző jogokat adjunk az adatbázisban tárolt adatok elérését illetően. Az adatelérési privilégiumok a következőek: • SELECT • INSERT • UPDATE • DELETE • REFERENCES

49. Adatelérés korlátozása II. A REFERENCES privilégium megengedi a fel-használónak, hogy olyan táblázatot hozzon létre, amely a megnevezett oszlopra hivatkozik. Az INSERT, UPDATE és REFERENCES privilégiumok megadása vonatkozhat egy egész táblázatra, vagy egy táblázat néhány oszlopára. Jogosultság megadása: GRANT utasítás. Jogosultság visszavonása: REVOKE utasítás.

50. Példák jogosultság megadására GRANT ALL PRIVILEGES ON ügyfél TO Fruzsina, Viktor;Az ügyfél táblázaton minden jogosultság megadása a Fruzsina és Viktor azonosítójú felhasználóknak. GRANT SELECT ON kazetta TO PUBLIC;A kazetta táblázatban mindenki szabadon kereshet. GRANT UPDATE (év, rendező) ON kazetta TO Fruzsina;A kazetta táblázatban Fruzsina módosíthatja az év és rendező oszlopokat.