Adatbázis rendszerek Ea: Esqúel. Structured Query Language

Adatbázis rendszerek 1. 4. Ea: Esqúel Structured Query Language

B IT M A N

64/1

2015.09.17 B ITv: M AN

Témakörök



SQL alapok



DDL utasítások



DML utasítások

DQL utasítás  DCL utasítások 

64/2



Gyakorló feladatok



Ellenőrző kérdések

B IT M A N

Az SQL jellemzése  Az SQL a relációs adatbázis-kezelők szabványosított

adatmanipulációs és lekérdező nyelve  A relációs algebrára épül  Nem algoritmikus: Parancsnyelv jellegű, megfogalmazhatjuk, mit akarunk csinálni, de a megoldási algoritmust nem kell megadni. Nincsenek benne ciklusok, feltételes elágazások, stb.  Mintaillesztéses, halmazorientált: A táblákat mint a sorok (rekordok) halmazát tekintjük. Az adott utasításban megfogalmazott feltételnek eleget tevő összes sor részt vesz a műveletben

64/3

B IT M A N

Az SQL jellemzése 2.  Szabványos: Illeszkedik az SQL szabványhoz. A

szabványban van egy SQL utasításcsoport, amelyet minden SQL alapú szoftver implementációnak meg kell valósítani, de mindegyik implementáció plusz lehetőséget is nyújt a standard SQLhez képest, felülről kompatibilis a szabvánnyal  Fontosabb használati módjai: – Önállóan, fejlesztő eszközökben: pl.: SQL*Plus, – Beágyazva procedurális programozási nyelvekbe. pl.: C/C++, Pacal  Az SQL nem DBMS!

64/4

B IT M A N

Az SQL jellemzése 3.  Az SQL által lefedett területek:

– DDL: Data Definition Language – adatstruktúra definiáló utasítások – adatbázisok, táblák létrehozása, módosítása és törlése – DML: Data Manipulation Language – adatokon műveletet végző utasítások – adatok rögzítése, módosítása, törlése – DQL: Data Query Language – adat lekérdező utasítás – DCL: Data Control Language – adatvezérlő, felügyelő utasítások – tranzakciók kezelése, jogosultságok menedzselése

64/5

B IT M A N

Az SQL használata Tipikus műveleti sorrend:  Objektumok, táblák létrehozása,  adatok felvitele,  adatok lekérdezése,  adatok módosítása,  szerkezetek módosítása,  működési környezet beállítása. Rendszám Típus

Szín

Kor

Rendszám Típus

Szín

Kor

Fehér

6

JED-123

Nissan Ezüst

5

AKT-392

Trabant

GBC-765

ABC-765 64/6

Opel

Fehér

6

Opel

Kék

32

B IT M A N

Szintaxis  Kisbetű és nagybetű a nyelv alapszavaiban egyenértékű.  Utasítások sorfolytonosan írhatók, lezárás

 

 



pontosvesszővel. Szövegkonstans: 'szöveg' 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ó). Logikai műveletek: AND, OR, NOT Az utasítások szintaxisának leírásánál az elhagyható részleteket szögletes zárójelek között vannak. 64/7

B IT M A N

Az SQL utasítások csoportosítása DDL: - objektum létrehozás - objektum megszüntetés - objektum módosítás

CREATE DROP ALTER

DML: - rekord felvitel - rekord törlés - rekord módosítás

INSERT DELETE UPDATE

DQL: - lekérdezés

SELECT

DCL: - védelem - tranzakció kezelés

GRANT, REVOKE COMMIT, ROLLBACK

64/8

B IT M A N

Témakörök



SQL alapok



DDL utasítások



DML utasítások

DQL utasítás  DCL utasítások 

64/9



Gyakorló feladatok



Ellenőrző kérdések

B IT M A N

DDL – Adatstruktúra definiáló utasítások  DDL – Data Definition Language  Adatszerkezetek (elsősorban táblák) létrehozása,

módosítása, törlése  Egy tábla szerkezete, sémája a táblához tartozó mezőkkel egyértelműen megadható  A mezők megadása a mező nevének és a mező adattípusának, valamint az integritási feltételeknek a kijelölésével történik  Több tábla is létezhet ugyanazzal a szerkezettel, de a nevük nem lehet egyforma

64/10

B IT M A N

Tábla (vagy más objektum) létrehozása CREATE objektumtípus objektumnév paraméterek; CREATE TABLE táblanév ( mezőnév adattípus [integritási feltétel], mezőnév adattípus [integritási feltétel], ••• mezőnév adattípus [integritási feltétel] [ ,további integritási feltételek] ); CREATE TABLE Autó ( Rendszám char(7) PRIMARY KEY, Típus char(25) NOT NULL, Szín char(15) DEFAULT 'Fehér', Kor number(2) CHECK (Kor > 0) );

64/11

Rendszám Típus

Szín

Kor

Fehér

6

JED-123

Nissan Ezüst

5

AKT-392

Trabant

GBC-765

Opel

Kék

32

B IT M A N

Adattípusok  Alap adattípusok:

– Char(n) – n hosszúságú karaktersorozat, – Number(n,m) – n jegyű decimális szám, ebből m tizedes jegy; Number(8,2) ↔ 123456,12 – Date – dátum.  További típusok (környezetfüggő!): – INTEGER – egész szám (röviden INT) – REAL – valós (lebegőpontos) szám – Time – időpont

64/12

B IT M A N

Integritási feltételek megadása  Primary key – elsődleges kulcs  References – idegen kulcs  Foreign key (mezőlista) References táblanév(mezőlista) 

 



– idegen kulcs megadása ha a kulcs több mezőből áll Not null – nem maradhat üresen a mező, kötelező kitölteni Unique – a mező egyedi, nem kerülhet bele két egyforma érték Default – alapérték megadása Check – értékellenőrzés

64/13

B IT M A N

Tábla módosítása ALTER TABLE táblanév [ADD (újelem, ..., újelem)] [MODIFY (módosítás, ..., módosítás)] [DROP (oszlop, ..., oszlop)];  újelem: egy "oszlopnév adattípus [feltétel]” csoport  módosítás: "oszlopnév adattípus [feltétel]” ALTER TABLE Autó ADD (Tulaj REFERENCES Ember);

 Fontos: a mezők mérete általában csak növelhető, a

DROP funkciót pedig általában nem engedik a DBMS-ek. 64/14

B IT M A N

Tábla törlése DROP TABLE táblanév;  Azok a táblák amelyekre más tábla hivatkozik, nem

törölhetők! DROP TABLE Autó;

64/15

B IT M A N

Témakörök



SQL alapok



DDL utasítások



DML utasítások

DQL utasítás  DCL utasítások 

64/16



Gyakorló feladatok



Ellenőrző kérdések

B IT M A N

DML – adatokon műveletet végző utasítások  DML – Data Manipulation Language  Rekordok (sorok) beszúrása (felvitele)  Mezők értékének módosítása

 Rekordok törlése

64/17

B IT M A N

Rekordok beszúrása INSERT INTO táblanév [(oszloplista)] VALUES (értéklista); Rendszám Típus

INSERT INTO Autó VALUES ('AKT-392', 'Trabant', 'Kék', 32);

GBC-765 JED-123

Szín

Kor

Fehér

6

Nissan Ezüst

5

Opel

 Az oszloplista elhagyható, de ekkor az összes mező

értékét meg kell adni, a megfelelő sorrendben! INSERT INTO Autó (Rendszám, Típus, Kor) VALUES ('ASD-602', 'Mazda', 7); INSERT INTO Autó VALUES ('ALM-332', 'Renault', Null, 8); INSERT INTO Autó VALUES ('IJK-222', 'Lada', Default, 25); 64/18

B IT M A N

Mezők értékének módosítása UPDATE táblanév SET mező = kifejezés, ..., mező = kifejezés [ WHERE feltétel ];  A feltételnek eleget tevő mezők módosulnak, Where

feltétel nélkül minden mezőt módosít! UPDATE Autó SET Kor=10 WHERE Rendszám='JED-123'; UPDATE Autó SET Szín= 'Barna'; UPDATE Autó SET Kor=10, Szín='Kék' WHERE Rendszám='JED-123';

 A mezőben lévő aktuális érték is felhasználható: UPDATE Autó SET Ár = Ár * 1.2 WHERE Ár<1000000; 64/19

B IT M A N

Rekordok törlése DELETE FROM táblanév [ WHERE feltétel ];  Where feltétel nélkül minden sor törlődik! DELETE FROM Autó WHERE Kor>15;

64/20

B IT M A N

Témakörök



SQL alapok



DDL utasítások



DML utasítások

DQL utasítás  DCL utasítások 

64/21



Gyakorló feladatok



Ellenőrző kérdések

B IT M A N



64/22

DQL utasítások 

Projekció



Szelekció



Feltételek megfogalmazása



Rendezés a lekérdezésben



Aggregáció a lekérdezésben



Csoportképzés a lekérdezésben



Join



Halmazműveletek



Al-select

B IT M A N

DQL – Adat lekérdező utasítás  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épzésére  Feladata: Egy vagy több adattáblából egy eredménytábla előállítása, amely a képernyőn listázásra kerül, vagy más módon használható fel. SELECT [DISTINCT] oszloplista FROM táblanévlista [WHERE feltétel]; DISTINCT: Az eredménytáblában az ismétlődő rekordokból csak egy jelenik meg. 64/23

B IT M A N

Projekció (vetítés) Projekció: csak a kijelölt mezők jelennek meg az eredmény relációban. Ár Rendszám Típus Szín Kor

Jele:

mezőlista(r)

Megvalósítása:

Fehér

6

1200000

JED-123

Nissan Ezüst

5

1500000

AKT-392

Trabant

32

25000

GBC-765

Opel

Kék

SELECT mezőnévlista FROM táblanév; SELECT Rendszám, Típus FROM autó;

Típus

EuroÁr

SELECT * FROM autó;

Opel

4285

Nissan

5357

Trabant

89

SELECT Típus, Ár/280 As EuroÁr FROM autó; 64/24

B IT M A N

Szelekció (kiválasztás) Szelekció: a megadott feltételnek eleget tévő rekordok kerülnek át az eredmény relációba.

Jele:

felt(r)

Megvalósítása: SELECT [DISTINCT] * FROM táblanév WHERE feltétel; SELECT * FROM autó WHERE Kor<10; Szín

Kor

Ár

Fehér

6

1200000

Nissan Ezüst

5

1500000

Rendszám Típus GBC-765

JED-123 64/25

Opel

B IT M A N

Feltételek megfogalmazása Használható operátorok:  Relációs operátorok (=, !=, <, >, !<, !>, <>, <=, >=)  Algebrai operátorok (+,-,*,/)  Logikai operátorok (AND, OR, NOT) Speciális logikai kifejezések:  x IS NULL: igaz, ha az x mező értéke NULL.  x BETWEEN a AND b: igaz, ha a  x  b.  x IN halmaz: igaz, ha x megegyezik a megadott halmaz egy elemével.  x LIKE minta: igaz, ha az x karaktersorozat megfelel a megadott mintának. Helyettesítő karakterek: _ - egy karakter, % - tetszőleges számú karakter 64/26

B IT M A N

Példák szelekciós feltételekre Ember:

Név

Cím Szül_Év

Kis Géza Eger

1983

Autó:

Rendszám Típus

GBC-765

Szín

Opel Fehér

Kor

Ár

6

1200000

 SELECT * FROM Ember WHERE Szül_Év BETWEEN 1975 AND 1980;  SELECT * FROM Ember WHERE Cím IS NULL;  SELECT * FROM Ember WHERE Név LIKE 'B%';  SELECT * FROM Ember WHERE Név LIKE ‘%_Géza%';  SELECT Rendszám FROM Autó WHERE Szín IN ('piros', 'kék', 'fehér');

 SELECT Rendszám,Típus FROM Autó WHERE Ár < 1000000 AND Szín

NOT LIKE '%lila%';  SELECT Rendszám FROM Autó WHERE Rendszám LIKE '_ER-6%';  SELECT Rendszám FROM Autó WHERE Rendszám LIKE '%0' or Rendszám LIKE '%2' or Rendszám LIKE '%4' or Rendszám LIKE '%6' or Rendszám LIKE '%8'; 64/27

B IT M A N

Szelekciós függvények  ABS(n): abszolút é'rték Példa: ABS(-15) = 15  LOWER(char): konverzió kisbetűsre.

   

Példa: LOWER('Kovács') = 'kovács' UPPER(char): konverzió nagybetűsre. Példa: UPPER('Kovács') = 'KOVÁCS' INITCAP(char): konverzió nagy kezdőbetűsre. Példa: INITCAP('kovács') = 'Kovács' LTRIM(char): balról szóközök eltávolítása. Példa: LTRIM(' alma ') = 'alma ' RTRIM(char): jobbról szóközök eltávolítása. Példa: RTRIM(' alma ') = ' alma'

64/28

B IT M A N

Szelekciós függvények 2.  SUBSTR(char, m[, n]): a char string m-edik karakterétől

   

n hosszú részstringet ad vissza. (Ha n nem szerepel, akkor a végéig.) Az első karakter 1-es sorszámú. Példa: SUBSTR('ABCDEFG',2,3) = 'BCD' TO_CHAR(n): konverzió numerikusról vagy dátumról karakteresre. Példa: TO_CHAR(123) = '123' TO_DATE(char): konverzió karakteresről dátumra. Példa: TO_DATE('15-JAN-06') TO_NUMBER(char): konverzió karakteresről numerikusra. Példa: TO_NUMBER('123') = 123 SQRT(kifejezés): Négyzetgyök, numerikus kifejezésre vonatkozik. Példa: SQRT(16) = 4

64/29

B IT M A N

Rendezés a lekérdezésben  Nem szerepel az eredeti modellben, ugyanis az

halmazorientált, ahol nem értelmeztük a rendezettséget.  A gyakorlatban adatainkat listákban tároljuk, ahol rendszerint valamilyen rendezettséget valósítunk meg (akár több szempont szerint is). SELECT ... FROM ... [WHERE ...] ORDER BY oszlopnév [DESC], oszlopnév [DESC],...;  ASC (ascending) : növekvő sorrend - alapértelmezés

 DESC (descending) : csökkenő sorrend  Alapértelmezésben növekvő, ha fordítva akarjuk akkor

DESC! SELECT * FROM autó ORDER BY Típus, Ár DESC;

64/30

B IT M A N

Aggregáció a lekérdezésben Aggregáció: a reláció rekordjaira összesítő érték(ek)et tartalmazó rekordot ad vissza Aggregációs függvények:  SUM(x) - összeg  AVG(x) - átlagérték  MAX(x) – legnagyobb érték  MIN(x) – legkisebb érték  COUNT(x) – elemek száma

x: numerikus mező vagy kifejezés értéke

*: minden rekordot érint

SELECT avg(Ár) FROM Autó; SELECT count(*) FROM Autó; SELECT count(Kor) FROM Autó Where Kor > 10; SELECT Sum(Ár), Avg(Kor) FROM Autó; 64/31

B IT M A N

Csoportképzés a lekérdezésben SELECT ... FROM ... [WHERE ...] GROUP BY oszloplista;  Az egyes csoportba azok a rekordok kerülnek, melyeknél

oszloplista értéke azonos.  Az eredménytáblában egy csoportból egy rekord jelenik meg  Az összesítő függvények csoportonként hajtódnak végre. SELECT Típus, avg(Ár) FROM Autó GROUP BY Típus; SELECT Típus, count(*) FROM Autó WHERE Szín='Piros' GROUP BY Típus ORDER BY Típus; SELECT Szín, min(Ár), max(Ár) FROM Autó GROUP BY Szín; 64/32

B IT M A N

Csoportok szűrése SELECT ... FROM ... [WHERE ...] GROUP BY oszloplista HAVING feltétel;  A csoportképzés után a kapott eredményreláció szűkítése

bizonyos rekordokra.  A WHERE szelekciós opció nem alkalmazható, mert az az alapreláció rekordjain értelmezett szelekcióra vonatkozik! SELECT Típus, avg(Ár) FROM Autó GROUP BY Típus HAVING avg(Ár) > 500000; SELECT Típus, count(*) FROM Autó WHERE Szín='Piros' GROUP BY Típus HAVING count(*) > 3 ORDER BY Típus;

64/33

B IT M A N

Join (szorzat, illesztés)  Ha az eredménytábla két (vagy több) táblából akarjuk az

adatokat megjeleníteni, akkor a táblák összekapcsolására a JOIN parancsot kell használni.  SQL-ben a következő típusokat alkalmazhatjuk:

– – – –

64/34

Alap join (teljes illesztés), Szelekciós join, Outer join, Self join (tábla összekapcsolása önmagával).

B IT M A N

Alap join (teljes illesztés) SELECT mezőlista FROM táblanév1, táblanév2; Autó:

Ember:

Rendszám Típus GBC-765 JED-123

Szín

Ár

Tulaj

Ekód

Név

Cím

Fehér 1200000

114

114

Kis Géza Eger

Nissan Ezüst 1500000

178

178

Jó Éva

Opel

Telefon 20-9555666

Miskolc 30-8765432

SELECT Név,Típus,Ár FROM Autó,Ember; Név

Típus

Ár

Kis Géza

Opel

1200000

Jó Éva

Opel

1200000

Kis Géza Nissan 1500000

Jó Éva 64/35

Nissan 1500000

B IT M A N

Szelekciós join 1. változat SELECT mezőlista FROM tábla1, tábla2 WHERE feltétel;  A szelekciós feltétel és a join feltétel nem válik szét!

Autó:

Ember:

Rendszám Típus

Szín

Ár

GBC-765 Opel Fehér 1200000 JED-123 Nissan Ezüst 1500000 AKT-392 Trabant Kék 25000

Tulaj

Ekód

114 199 102

114 178 102 155

Név

Cím

Telefon

Kis Géza Jó Éva Kék Béla Bő Jenő

Eger Miskolc Ózd Ózd

20-9555666 30-8765432 70-3355440 70-3355440

SELECT Név,Típus,Ár FROM Autó,Ember Where Tulaj=Ekód; Név

Típus

Ár

Kis Géza

Opel

1200000

Kék Béla Trabant 64/36

25000

B IT M A N

Szelekciós join 2. változat SELECT mezőlista FROM tábla1 INNER JOIN tábla2 ON join_feltétel WHERE feltétel;  A szelekciós feltétel és a join feltétel külön-külön megadható!

Autó:

Ember:

Rendszám Típus

Szín

Ár

GBC-765 Opel Fehér 1200000 JED-123 Nissan Ezüst 1500000 AKT-392 Trabant Kék 25000

Tulaj

Ekód

114 199 102

114 178 102 155

Név

Cím

Telefon

Kis Géza Jó Éva Kék Béla Bő Jenő

Eger Miskolc Ózd Ózd

20-9555666 30-8765432 70-3355440 70-3355440

SELECT Név,Típus,Ár FROM Autó INNER JOIN Ember ON Tulaj=Ekód Where Ár > 500000; Név

Típus

Ár

Kis Géza 64/37

Opel

1200000

B IT M A N

Outer join (külső illesztés) SELECT …FROM tábla1 [LEFT | RIGHT | FULL] OUTER JOIN tábla2 ON join_feltétel [WHERE feltétel] …;  Az illeszkedő pár nélküli rekordok is bekerülnek az eredménybe.

Autó:

Ember:

Rendszám Típus

Szín

Ár

GBC-765 Opel Fehér 1200000 JED-123 Nissan Ezüst 1500000 AKT-392 Trabant Kék 25000

Tulaj

Ekód

114 199 102

114 178 102 155

Név

Cím

Telefon

Kis Géza Jó Éva Kék Béla Bő Jenő

Eger Miskolc Ózd Ózd

20-9555666 30-8765432 70-3355440 70-3355440

SELECT Rendszám,Név FROM Autó RIGHT OUTER JOIN Ember ON Tulaj=Ekód Where Cím='Ózd'; Rendszám

Név

AKT-392 Kék Béla 64/38

Bő Jenő

RIGHT: A jobb oldali táblából jönnek a pár nélküli rekordok is, üres értékkel kiegészítve.IT AN

B

M

Outer join (külső illesztés) – Példa  Hány darab autója van az egyes embereknek? Autó: Rendszám Típus

Ember: Szín

Ár

GBC-765 Opel Fehér 1200000 JED-123 Nissan Ezüst 1500000 AKT-392 Trabant Kék 25000 LOG-416 Volvo Piros 2825000

Tulaj

Ekód

114 178 102 114

114 178 102 155

SELECT Név, count(Tulaj) As Darab FROM Autó RIGHT OUTER JOIN Ember ON Tulaj=Ekód Group By Név;

64/39

Név

Cím

Telefon

Kis Géza Jó Éva Kék Béla Bő Jenő

Eger Miskolc Ózd Ózd

20-9555666 30-8765432 70-3355440 70-3355440

Név Kis Géza Jó Éva Kék Béla Bő Jenő

Darab 2 1 1 0

B IT M A N

Self join – önillesztés :-) SELECT …FROM tábla t1, tábla t2 [WHERE feltétel] …;  A tábla kétszer szerepel, különböző álnevekkel.

Autó: Rendszám Típus

GBC-765 JED-123 HGZ-392 LOG-416

Opel Nissan Nissan Opel

Kor

Ár

Tulaj

5 8 7 6

1200000 1000000 900000 1300000

114 178 102 115

Azon autók rendszáma, melyek idősebbek, mégis drágábbak saját típustársuknál: SELECT a1.Rendszám FROM Autó a1, Autó a2 WHERE a1.Típus=a2.Típus AND a1.Kor>a2.Kor AND a1.Ár>a2.Ár); 64/40

Rendszám JED-123 LOG-416

B IT M A N

Halmazműveletek Unió: SELECT …UNION [ALL] SELECT …; Metszet: SELECT …INTERSECT SELECT …; Különbség: SELECT …MINUS SELECT …;  Csak azonos szerkezetű táblák esetén alkalmazhatók! Rendszám Típus

Szín

Ár

GBC-765 Opel Fehér 1200000 JED-123 Nissan Ezüst 1500000 AKT-392 Trabant Kék 25000 LOG-416 Volvó Kék 2825000

Tulaj

Ekód

114 178 102 114

114 178 102 155

Azok az emberek, akiknek nincs autójuk: SELECT név FROM ember MINUS SELECT név FROM ember WHERE Ekód IN (SELECT Tulaj FROM autó); 64/41

Név

Cím

Telefon

Kis Géza Jó Éva Kék Béla Bő Jenő

Eger Miskolc Ózd Ózd

20-9555666 30-8765432 70-3355440 70-3355440

Név

Név

Kis Géza Jó Éva Kék Béla Bő Jenő

Kis Géza Jó Éva Kék Béla

Név Bő Jenő

B IT M A N

AL-SELECT  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)

64/42

B IT M A N

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. SELECT …FROM …WHERE mező IN (al-select); SELECT …FROM …WHERE mező θ ANY (al-select); SELECT …FROM …WHERE mező θ ALL (al-select); SELECT …FROM …WHERE EXISTS (al-select); θ: tetszőleges relációs operátor 64/43

B IT M A N

Az AL-SELECT operátorai 2.  ANY: a halmaz minden eleméhez hasonlít, ha egyet

talál, akkor igazzal tér vissza  ALL: a halmaz minden eleméhez hasonlít, ha akár egyre nem teljesül, akkor hamisat ad vissza  EXISTS: az eredményhalmaz üres-e (ha üres, akkor hamissal tér vissza)

64/44

B IT M A N

Példák AL-SELECT-re Autó: Rendszám Típus

Ember: Szín

Ár

GBC-765 Opel Piros 1200000 JED-123 Nissan Ezüst 1500000 AKT-392 Trabant Kék 25000 LOG-416 Volvo Piros 2825000

Tulaj

Ekód

114 178 102 114

114 178 102 155

Név

Cím

Telefon

Kis Géza Jó Éva Kék Béla Bő Jenő

Eger Miskolc Ózd Ózd

20-9555666 30-8765432 70-3355440 70-3355440

 Az egri tulajdonosok autóinak árát növeljük meg 20%-al:

UPDATE Autó SET Ár=Ár*1.2 WHERE Tulaj IN (SELECT Ekód FROM Ember WHERE Cím='Eger');  Töröljük azokat az embereket, akiknek több mint 2 autójuk van:

DELETE FROM Ember WHERE Ekód IN (SELECT Ekód, count(*) FROM Ember, Autó WHERE Ekód=Tulaj GROUP BY Ekód HAVING count(*) > 2); 64/45

B IT M A N

Példák AL-SELECT-re Autó: Rendszám Típus

Ember: Szín

Ár

GBC-765 Opel Fehér 1200000 JED-123 Nissan Ezüst 1500000 AKT-392 Trabant Kék 25000 LOG-416 Volvo Piros 2825000

Tulaj

Ekód

114 178 102 114

114 178 102 155

Név

Cím

Telefon

Kis Géza Jó Éva Kék Béla Bő Jenő

Eger Miskolc Ózd Ózd

20-9555666 30-8765432 70-3355440 70-3355440

 Bármely piros autó áránál olcsóbb autók rendszámai:

SELECT Rendszám FROM Autó WHERE Ár < ANY (SELECT Ár FROM Autó WHERE Szin='piros');  Minden piros autó áránál olcsóbb autók rendszámai:

SELECT rsz FROM autó WHERE ár < ALL (SELECT ár FROM autó WHERE szin=‘piros’); 64/46

Rendszám GBC-765 JED-123 AKT-392 Rendszám AKT-392

B IT M A N

Példák AL-SELECT-re Autó: Rendszám Típus

Ember: Szín

Ár

GBC-765 Opel Fehér 1200000 JED-123 Nissan Ezüst 1500000 AKT-392 Trabant Kék 25000 LOG-416 Volvo Piros 2825000

Tulaj

Ekód

114 178 102 114

114 178 102 155

Név

Cím

Telefon

Kis Géza Jó Éva Kék Béla Bő Jenő

Eger Miskolc Ózd Ózd

20-9555666 30-8765432 70-3355440 70-3355440

 Azok az emberek, akiknek nincs autójuk (1. változat):

SELECT Név FROM Ember WHERE Ekód NOT IN (SELECT Tulaj FROM Autó);  Azok az emberek, akiknek nincs autójuk (2. változat):

SELECT E.Név FROM Ember E WHERE NOT EXISTS (SELECT * FROM Autó A WHERE A.Tulaj=E.Ekód); 64/47

B IT M A N

Témakörök



SQL alapok



DDL utasítások



DML utasítások

DQL utasítás  DCL utasítások 

64/48



Gyakorló feladatok



Ellenőrző kérdések

B IT M A N

DCL – Jogosultságok kezelése Minden felhasználó

Jogosultság adása: GRANT művelet ON objektum TO felhasználó | PUBLIC [ WITH GRANT OPTION ];

A megszerzett jog tovább adható.

Jogosultság visszavonása: REVOKE művelet ON objektum FROM felhasználó | PUBLIC;

Jogok visszavonása:  Csak attól a felhasználótól vonható vissza jog, akinek kiadtuk a jogot. Ha ez a felhasználó időközben továbbadta a jogot, az is automatikusan visszavonásra kerül. 64/49

B IT M A N

DCL – Jogosultságok kezelése 2. Engedélyezhető műveletek: SELECT: táblázat lekérdezése INSERT: táblázat bővítése DELETE: táblázat rekordjainak törlése UPDATE: táblázat rekordjainak módosítása ALL: minden művelet Jogok engedélyezése: GRANT SELECT ON Autó TO Jenő; GRANT SELECT,UPDATE(Szín) ON Autó TO PUBLIC; GRANT DELETE ON Autó TO Jenő WITH GRANT OPTION; Jogok visszavonása: REVOKE ALL ON Autó FROM Jenő; REVOKE UPDATE ON Autó FROM Public; 64/50

B IT M A N

Adatbázis rendszerek 1. Ellenőrző kérdések

B IT M A N

64/51

B IT M A N

Ellenőrző kérdések 1. 1. Melyik paranccsal lehet létrehozni adatbázis táblát? A: BUILD B: CONSTRUCT C: CREATE D: MAKE E: PREPARE 2. Melyik paranccsal lehet törölni adatbázis táblát?

A: CLOSE B: DELETE C: CLEAR D: DROP E: KILL 64/52

B IT M A N

Ellenőrző kérdések 2. 3. Helyes az alábbi parancs? Séma: Termék [ Kód, Név, Ár, Leírás ] insert into termek ('A0123', 'Sál',1500,null); A: Igen

B: Nem

4. Melyik kulcsszó hiányzik a parancsból? UPDATE termék SET

ár=ár*1.1;

A: TO B: SET C: WHERE D: FOR E: Nem hiányzik semmi a parancsból! 64/53

B IT M A N

Ellenőrző kérdések 3. 5. Melyik kulcsszó hiányzik a parancsból? SELECT név, ár FROM termék ORDER BY név; A: Egy vessző (,) hiányzik. B: SHOT BY C: WHERE D: ORDER BY E: Nem hiányzik semmi a parancsból! 6. Helyes az alábbi parancs? SELECT név, lakcím FROM vásárló WHERE fizmód= 'bankkártya' OR 'utánvét'; A: Igen 64/54

B: Nem

B IT M A N

Ellenőrző kérdések 4. 7. Melyik paranccsal lehet kiíratni azokat a termékeket, melyeknek üres a leírás mezője? A: select név from termék where leírás =' '; B: select név from termék where leírás = NULL; C: select név from termék where leírás IS NULL; D: select név from termék where leírás EMPTY; E: select név from termék where leírás LIKE ' '; 8. Helyes az alábbi parancs? SELECT terméknév FROM termék WHERE ár > 200 and < 500; A: Igen 64/55

B: Nem

B IT M A N

Ellenőrző kérdések 5. 9. Melyik kulcsszó hiányzik a parancsból? SELECT név FROM termék WHERE ár > 1000; A: TO D: FROM

B: SET E: GROUP BY

C: WHERE E: SHORT BY

10. Helyes az alábbi parancs? SELECT count(*) FROM vásárló;

A: Igen

B: Nem

11. Helyes az alábbi parancs? SELECT count(*) FROM vásárló WHERE az='az'; A: Igen 64/56

B: Nem

B IT M A N

Ellenőrző kérdések 6. 12. Melyik paranccsal lehet módosítani az adatbázis tábla szerkezetét? A: ALTER B: CHANGE C: MODIFY D: AMEND E: REVISED 13. Melyik a kakukktojás? A: DDL B: DQL C: DML D: DBL E: DCL 64/57

B IT M A N

Ellenőrző kérdések 7. 14. Adja meg a feladatokat megvalósító SQL parancsokat! Típus Rendszám

Kor

Autó

Szín

A: Hozza létre a táblát: B: Vigyen fel egy rekordot: C: Írassa ki a kék autók rendszámát: D: Írassa ki az átlagkort: E: Hány darab piros autó van? F: Írassa ki azokat a rendszámokat, melyekben van B betű. G: Írassa ki azokat a rendszámokat, melyekben nincs B betű. H: Törölje ki az Opeleket. I: Írassa a 4 és 8 év közötti korú autók rendszámát. 64/58

B IT M A N

Ellenőrző kérdések 8. 15. Alakítson kategóriákat, és sorolja az alábbi parancsokat az egyes kategóriákba! Kategóriák:

A: insert D: create G: drop 64/59

Parancsok:

B: select E: update H: delete

C: grant F: modify I: revoke

B IT M A N

Ellenőrző kérdések 9. 16. Igaz vagy Hamis az állítás? Az SQL utasításaiban a kisbetű és a nagybetű egyenértékű. A DDL parancsok az adatokon műveleteket végeznek. A szöveg konstansokat aposztrófok " " közé kell tenni. Az SQL-ben a * (csillag) csak a count paramétereként használható: count(*). A projekciót a select paranccsal lehet megvalósítani. Az AVG(ár) from termék; parancs kiírja az átlagárat. A delete termék; parancs kitörli az összes terméket. Az inner join-nak létezik left típusa. A having parancs csak a group by paranccsal együtt használható. 64/60 B IT M A N

Ellenőrző kérdések 10. 17. Az adott séma alapján adja meg az SQL parancsokat! Termék [ Tkód, Név, Ár, Leírás ] Raktár [ RaktHely, Tkód, Darab ]

A: Mely termékek leírásában szerepel a női szó? B: Mely raktárhelyeken van 100-nál több termék? C: Mely raktárhelyeken van bikini nevű termék? D: Mennyi a termékek átlagára? E: Összesen hány darab bikini van a raktárban? F: Összesen mennyi értékű bikini van a raktárban? G: Növelje meg a kesztyű nevű, bőr leírású termékek árát 1000 forinttal. H: Törölje ki a nulla darabszámú raktárhelyeket! 64/61

B IT M A N

64/62

Gratulálok! Ön átvette a tananyagot, és letesztelte a tudását! B IT M A N

Felhasznált irodalom  Kovács László elektronikus jegyzetei  Szelezsán János: Adatbázisok, LSI

Oktatóközpont, 1999  Baksáné Varga Erika elektronikus jegyzetei  IHM: Adatbázis-kezelés, elektronikus jegyzet  Stolniczki Gyula: SQL kézikönyv

64/63

B IT M A N

VÉGE 64/64

B IT M A N