Sapientia - Erdélyi Magyar TudományEgyetem (EMTE ) Csíkszereda

1. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda • 3. Előadás • Az SQL adatbázisnyelv (Tartalom) • Az SQL története • Az SQL felépítése • Adattípúsok • SQL utasítások

2. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda • Az SQL története • 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: (rekurzió, triggerek, objektum-relációs modell)

3. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda • Az SQL felépítése(Szintaxis) • Kisbetű és nagybetű a nyelv alapszavaiban egyenértékű. • Utasítások sorfolytonosan írhatók, lezárás pontosvesszővel. • Változó nincs, csak tábla- és oszlopnevekre lehet hivatkozni. Kifejezésben hivatkozás egy tábla adott oszlopára: tábla.oszlop (ha a tábla egyértelmű, akkor elhagyható). • Alias név: név AS másodnév (egyes implementációkban AS elhagyható). • Szövegkonstans: 'szöveg'

4. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda • Az SQL felépítése(Szintaxis)2 • Dátum: DATE '1968-05-12'. Egyes rendszerek az SQL szabványtól eltérő konvenciót alkalmaznak, például 13-NOV-94 (Oracle), 02/15/1994 (dBase). • Idő: TIME '15:31:02.5' (óra, perc, másodperc). • Stringek konkatenációja: + (xBase), || (Oracle). • Relációjelek: =, <=, >=, !=, <> • Logikai műveletek: AND, OR, NOT. Egy logikai kifejezés értéke ISMERETLEN (UNKNOWN), ha benne NULL érték szerepel

5. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda Az adattípusok (rendszerenként eltérők lehetnek): CHAR(n) n hosszúságú karaktersorozat VARCHAR(n) legfeljebb n hosszúságú karaktersorozat INTEGER egész szám (röviden INT) REAL valós (lebegőpontos) szám, más néven FLOAT DECIMAL(n[,d]) n jegyű decimális szám, ebből d tizedesjegy DATE dátum TIME idő

6. Adattípusok a MySQL-benNumerikus típusok BIT[(M)] TINYINT[(M)] [UNSIGNED] [ZEROFILL] BOOL, BOOLEAN SMALLINT[(M)] [UNSIGNED] [ZEROFILL] MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] INT[(M)] [UNSIGNED] [ZEROFILL] INTEGER[(M)] [UNSIGNED] [ZEROFILL] BIGINT[(M)] [UNSIGNED] [ZEROFILL] FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] REAL[(M,D)] [UNSIGNED] [ZEROFILL] FLOAT(p) [UNSIGNED] [ZEROFILL] DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL] NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL], FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]

7. Dátum és idő reprezentációja DATE DATETIME TIMESTAMP TIME YEAR[(2|4)]

8. String tipusok CHARACTER SET CHARACTER SET binary ASCII UNICODE BINARY [NATIONAL] CHAR[(M)] [NATIONAL] VARCHAR(M) BINARY(M) VARBINARY(M) TINYBLOB TINYTEXT [CHARACTER SET charset_name] BLOB[(M)] TEXT[(M)] [CHARACTER SET charset_name] MEDIUMBLOB MEDIUMTEXT [CHARACTER SET charset_name] LONGBLOB LONGTEXT [CHARACTER SET charset_name] ENUM('value1','value2',...) [CHARACTER SET charset_name] SET('value1','value2',...) [CHARACTER SET charset_name]

9. Ekvivalens adattipusok más szállítók tipusaival

10. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda SQL utasítások Az SQL utasításait két fő csoportba szokták sorolni: - DDL (= Data Definition Language): adatstuktúra definiáló utasítások. - DML (= Data Manipulation Language): adatokon műveletet végző utasítások. Jelen anyagban - az RDBMS fő feladatai alapján - az alábbi csoportokban tárgyaljuk az SQL utasításokat: - adatbázisséma definiálása (DDL) - adatok aktualizálása (DML) - lekérdezési lehetőségek (DML)

11. Adatbázissémák definiálása (DDL) Adatbázisséma létrehozására a CREATE DATABASE/ SCHEMA utasítás szolgál. CREATE {DATABASE/SCHEMA}[IF NOT EXISTS] db_name [create_specification] ... create_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name Adatbázisséma átnevezése RENAME {DATABASE | SCHEMA} db_name TO new_db_name;

12. Adatbázisséma törlése (eldobása) DROP {DATABASE | SCHEMA} [IF EXISTS] db_name; Adatbázisséma módosítása ALTER {DATABASE | SCHEMA} [db_name] alter_specification ... ALTER {DATABASE | SCHEMA} db_name UPGRADE DATA DIRECTORY NAME (5.1 előtti feljavítása) alter_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name Adatbázisséma megnyitása (aktuálissá tétele): USE db_name;

13. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda Relációsémák definiálása (DDL) Relációséma létrehozására a CREATE TABLE utasítás szolgál, amely egyben egy üres táblát is létrehoz a sémához. Az attribútumok definiálása mellett a kulcsok és külső kulcsok megadására is lehetőséget nyújt: CREATE TABLE táblanév ( oszlopnév adattípus [feltétel], ... ..., oszlopnév adattípus [feltétel] [, táblaFeltételek] );

14. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda Feltételek (egy adott oszlopra vonatkoznak): PRIMARY KEY: elsődleges kulcs UNIQUE: kulcs (egyértékűség) 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

15. 1. Példa. SZAMLA (szamlaszam, AFA, Datum, kliensKod) KLIENS(klienskod, név, Bank, Bankszamla, adoszam) CREATE TABLE Kliens ( klienskodinteger PRIMARY KEY, nev CHAR(20), BankCHAR(20), Bankszamla CHAR(30), adoszam CHAR(30) ); CREATE TABLE Szamla ( szamlaszaminteger PRIMARY KEY, AFAinteger, datumDate, Osszeg Decimal(10,2), klienskodinteger REFERENCES Kliens(klienskod) );

16. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda 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)];

17. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda • újelem: egy "oszlopnév adattípus [feltétel]", vagy egy "táblafeltétel", mint a CREATE TABLE utasításban. • - módosítás: "oszlopnév adattípus [feltétel]". • Oszlopok törlését nem minden rendszer engedi meg. • Példák: • ALTER Table TermekAdd (gyartoEV integer); • ALTER Table Termek DROP Column gyartoEv;

18. Adattábla aktualizálása (DML) Új sor felvétele táblába 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. Példák: INSERT INTO Szamla(AFA, szamlaszam, klienskod) VALUES (0.19, 12345, 6783); INSERT INTO Szamla VALUES (12345, 0.19,’2008-04-21’ , 6783);

19. 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ák: UPDATE Kliens SET lakcím = "Szeged, Rózsa u. 5." WHERE klienskod = 6783; UPDATE Kliens SET Bank = “Reiffeisen" WHERE Bank = “Banca Agricola";

20. 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). Példa: DELETE FROM Kliens WHERE klienskod>200 AND klienskod<400; DELETE FROM Kliens;!!!!! – mindent töröl

21. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda Lekérdezések(SELECT) Egy vagy több adattáblából egy eredménytáblát állít elő A SELECT utasítás általános alakja 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

22. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda Egyszerű lekérdezések SQL-ben Szelekció:f(R) SELECT *FROM R WHERE f; Példa: Film(cim, ev, hossz, szines, studioNev, producerAzon); Szinesz(nev, cim, nem, szuletesnap); SELECT * FROM Film WHERE hossz>100 AND szines;

23. Vetítés(Projekció): SELECT FROM R; Példa: SELECT studioNev, cim FROM Film; Alias (AS) használata - másodnév SELECT cim AS nev, hossz*0,016667 AS hosszOrakban FROM Film WHERE studioNev=‘Disney’;

24. Konstansok használata a SELECT záradékban SELECT cim , hossz*0,016667 AS hosszOrakban FROM Film WHERE studioNev=‘Disney’; Karakterláncok összehasonlítása: s LIKE p , ahol s karakterlánc és p egy minta Helyettesítő karakterek (standard SQL): %, _ Helyettesítő karakterek (Access): *, ? SELECT * SELECT * FROM Film FROM Film WHERE cim LIKE ‘csill*’ WHERE cim LIKE ‘*lága’

25. SELECT * SELECT * FROM Film FROM Film WHERE cim LIKE ‘*or*’WHERE cim LIKE ‘Wayne???world’ Speciális karakterek használata a mintában ‘ (egyszeres idézőjel) keressük a következő kombinációt: ‘s SELECT * FROM Film WHERE cim LIKE ‘*’’s*’ megduplázzuk az egyszeres idézőjelet s LIKE ‘x%%x%’ ESCAPE ‘x’ Az x% egy % karakternek fog megfelelni. A minta olyan karaktersornak felel meg, amelyik % al kezdődik és %-al végződik

26. Null értékre vonatkozó szabályok: Amikor egy aritmetikai műveletben, mint x vagy +, legalább az egyik tag NULL, akkor az eredmény is NULL Amikor egy NULL értéket hasonlítunk össze bármely más értékkel, beleértve a NULL-t is, egy összehasonlítási operá-tor segítségével, mint = vagy >, az eredmény ISMERETLEN. Logikai műveletek a háromértékű logikában Szabály: könnyen megjegyezhető, ha az IGAZ=1, HAMIS=0, ISMERETLEN=1/2. Két logikai értékre alkalmazott AND eredménye a két érték minimuma. Két logikai értékre alkalmazott OR eredménye a két érték maximuma. A v logikai érték tagadásának értéke 1-v

28. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda SELECT cím FROM Film WHERE cím LIKE ‘Halálos _______’ (7 db. alulhúzás) (Halálos fegyver v. Halálos játszma) Szűrőfeltételek: WHERE <oszlop> BETWEEN <kifejezés_1> AND <kifejezés_2 > WHERE <oszlop> IN (<kifejezés_1>, <kifejezés_2> [,...])

29. Példa: SELECT Név FROM Alkalmazottak WHERE Fizetés BETWEEN 300 AND 500; Legyen az Egyetem nevű adatbázis a következő relációsémákkal: Szak (SzakKod, SzakNév, Nyelv); Csoport (CsopKod, Evfolyam, SzakKod); Diák(Törzsszám, Név, SzemSzám, Cím, SzületésiDatum, CsopKod, Átlag); SELECT Név FROM Diák WHERE CsopKod IN (’531’, 532’, ’631’);

30. Több relációra vonatkozó lekérdezések Descartes szorzat: R1 xR2 SELECT * FROM R1, R2 Théta-összekapcsolás: R1 ⋈ θR2 SELECT * FROM R1, R2 WHERE θ; Természetes összekapcsolás: R1 ⋈ R2 = SELECT * FROM R1, R2 WHERE

31. Példa: SELECT Diak.Név, Csoport.CsopKod, Csoport.Evfolyam, SzakKod FROM Diák, Csoport WHERE Diák.CsopKod = Csoport.CsopKod; SELECT Diak.Név, Csoport.CsopKod, Evfolyam, SzakKod FROM Diák INNER JOIN Csoport ON Diák.CsopKod = Csoport.CsopKod; SELECT Név FROM Diák, Csoport WHERE Diák.CsopKod = Csoport.CsopKod AND Evfolyam = 3;

32. Halmazműveletek (egyesítés, különbség és metszet) : (SELECT Név, UtcaSzám FROM Szállítók WHERE Helység = “Kolozsvár”) UNION (SELECT Név, UtcaSzám FROM Vevők WHERE Helység = “Kolozsvár”); (SELECT SzemSzám FROM Alkalmazottak) EXCEPT (SELECT SzemSzám FROM Managerek); (SELECT Név FROM Tanárok) INTERSECT (SELECT Név FROM Diákok);

33. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda Ismétlődő sorok (DISTINCT ) A SELECT kulcsszó után a DISTINCT szó segítségével kérhetjük az azonos sorok megszüntetését. Példa: Az Egyetem adatbázis esetén keressük azon csoportokat, amelyekben vannak diákok, akik átlaga kisebb, mint 7. SELECT DISTINCT CsopKod FROM Diákok WHERE Átlag < 7;

34. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda • Összefoglaló kérdések • Az SQL szintaxisa • Adattípusok az SQL-ben • SQL utasítások típusai • Relációs sémák definiálása, törlése, módosítása • Oszlopra és sorra vonatkozó feltételek • Adattáblák aktualizálása • Karakterláncok összehasonlítása • Szorzatés összekapcsolás az SQL –ben • Egyesítés , metszet és különbség a SQL –ben