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

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

B IT M A N

1/83

2017.10.24 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 

2/83



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  Adatok keresésekor nem azok helyét, hanem azok tulajdonságát adjuk meg, így általában több adatot 3/83kapunk eredményül 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++, Pascal  Az SQL nem DBMS!

4/83

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

5/83

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 6/83

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.

7/83

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

8/83

B IT M A N

Témakörök



SQL alapok



DDL utasítások



DML utasítások


9/83



Gyakorló feladatok




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

10/83

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(3) CHECK (Kor >= 0) );

11/83

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

Tábla (vagy más objektum) létrehozása Kód

Rendszám Típus Opel

Szín Fehér

1

GBC-765

2

JED-123

Nissan Ezüst

3

AKT-392

Trabant

CREATE SEQUENCE k_s; insert into Autó values (k_s.nextval, …

Kék

CREATE INDEX RIndex ON Autó (Rendszám); CREATE VIEW OpelAutó AS Select Kód, Rendszám, Szín FROM Autó Where Tipus='Opel'; 12/83

Rendszám

Kód

AKT-392

3

GBC-765

1

JED-123

2

Kód

Rendszám

Szín

1

GBC-765

Fehér

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) ↔ 12345,12 – Date – dátum.  További típusok (DBMS függő!): – INTEGER – egész szám (röviden INT) – REAL – valós (lebegőpontos) szám – Time – időpont – Boolean – logikai

13/83

B IT M A N

Integritási feltételek megadása  Primary key – elsődleges kulcs  References – hivatkozás másik táblára  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

14/83

B IT M A N

Adattáblák létrehozása – Megszorítások Create table Melós( IDM int,  IDM int Primary key,  Check (IDM > 1000),  IDM int Default '29',

 Unique (IDM),  Index (IDM),  Foreign key (IDM) references Munkakör(Mkód),

);

103/15

B IT M A N

Példák CREATE TABLE pld1 ( id INT PRIMARY KEY, nev VARCHAR(100), datum DATE, suly FLOAT(8,5));

MySQL! CREATE TABLE pld2 ( az INT PRIMARY KEY, hely VARCHAR(100), darab INT(10) UNSIGNED, Foreign key (az) references pld1(id));

CREATE TABLE pld3 ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, nev VARCHAR(50) NOT NULL, darab INT(10) unsigned not null default '0', idopont TIMESTAMP(8) DEFAULT NOW(), Unique (nev) ) TYPE=innodb; 103/16

B IT M A N

Példák CREATE TABLE pld1 ( id INT PRIMARY KEY, nev VARCHAR(100), datum DATE, suly FLOAT(8));

Oracle! CREATE TABLE pld2 ( az INT PRIMARY KEY, hely VARCHAR(100), darab NUMBER(10), Foreign key (az) references pld1(id));

CREATE SEQUENCE id_seq; Fontos a sorrend!

CREATE TABLE pld3 ( id INT default id_seq.NEXTVAL PRIMARY KEY, nev VARCHAR(50) NOT NULL, darab NUMBER(10) default '0' NOT NULL, idopont TIMESTAMP DEFAULT SYSDATE, 103/17 Unique (nev)); B IT

MAN

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 varchar(35);

– Fontos: a mezők mérete általában csak növelhető! RENAME táblanév TO újtáblanév – Tábla átnevezése

18/83

B IT M A N

Tábla módosítása

create table személy( id number(4), név varchar(30));

ALTER TABLE személy ADD szülév number(4); ALTER TABLE személy RENAME COLUMN szülév TO szüldátum; ALTER TABLE személy MODIFY szüldátum date; ALTER TABLE személy MODIFY id primary key; DESC személy;

ALTER TABLE személy DROP primary key; ALTER TABLE személy DROP COLUMN szüldátum; RENAME személy TO person;

19/83

B IT M A N

Tábla módosítása

comment on column person.id is '100 és 300 közötti adatok'; ALTER TABLE person MODIFY név default 'Kis Béla';

ALTER TABLE person MODIFY NÉV DEFAULT NULL;

20/83

B IT M A N

Tábla módosítása: megszorítások – Javaslat ALTER TABLE person ADD CONSTRAINT cn1 PRIMARY KEY(id); ALTER TABLE person ADD CONSTRAINT cn2 UNIQUE (név) ALTER TABLE person ADD CONSTRAINT cn3 CHECK (id > 200) create table department( dpno int, dpname varchar(30)); ALTER TABLE department ADD CONSTRAINT cn4 primary key(dpno); ALTER TABLE person ADD dp int; ALTER TABLE person ADD CONSTRAINT cn5 FOREIGN KEY (dp) REFERENCES department(dpno); ALTER TABLE person DROP CONSTRAINT cn1; 21/83

B IT M A N

Megszorítások listázása SELECT * FROM user_constraints WHERE table_name = 'PERSON'

 C – check constraint on a table

Csupa nagybetű!

 P – primary key  U – unique key  R – referential integrity  V – with check option, on a view

 O – with read only, on a view 22/83

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ó;

23/83

B IT M A N

Vedd észre! – Táblák létrehozási sorrendje 1. 2.

Először azokat a táblákat kell létrehozni, amelyekből nem hivatkozunk más táblákra Azután azokat, amelyekből hivatkozunk más táblákra

1

Kategoria

Termek

Kkod Nev

Kategoria Tkod Nev Ar

2 Leiras

Vasarlas Tkod Sorszam Idopont Darab VID

4 24/83

FizModok

Vasarlo VID Nev Cim

5

Fizmod

Fkod Leiras

3

B IT M A N

Vedd észre! – Táblák törlési sorrendje 1. 2.

Először azokat a táblákat lehet törölni, amelyekből hivatkozunk más táblákra Azután azokat, amelyekből nem hivatkozunk más táblákra

5

Kategoria

Termek

Kkod Nev

Kategoria Tkod Nev Ar

4 Leiras

Vasarlas Tkod Sorszam Idopont Darab VID

2 25/83

FizModok

Vasarlo VID Nev Cim

1

Fizmod

Fkod Leiras

3

B IT M A N

Lehetőségek  Tábla létrehozása másolással (új táblanévvel)

– CREATE TABLE Személyek AS SELECT * FROM Person; Person ID Név 1 Kis Béla 2 Kő Dezső 3 Hó Jenő

Dp 10 11 10

Személyek ID Név 1 Kis Béla 2 Kő Dezső 3 Hó Jenő

Dp 10 11 10

 Üres tábla létrehozása másolással

– Create table Személyek as select * from Person WHERE 1=0; Person ID Név 1 Kis Béla 2 Kő Dezső 3 Hó Jenő 26/83

Dp 10 11 10

Személyek ID Név

Dp

B IT M A N

Lehetőségek  Üres tábla létrehozása másolással, eltérő szerkezettel

– CREATE TABLE Személyek AS SELECT id, név FROM Person WHERE 1=0; Person ID Név 1 Kis Béla 2 Kő Dezső 3 Hó Jenő

27/83

Dp 10 11 10

Személyek ID Név

B IT M A N

Lehetőségek  Tábla létrehozása több táblából

– create table Személyek as select ID, Név as NAME, DPNAME from Person join Department on dp=dpno Person ID Név 1 Kis Béla 2 Kő Dezső 3 Hó Jenő

Dp 10 11 10

Department DpNo DpName 10 Designer 11 Programmer

Személyek ID Name DpName 1 Kis Béla Designer 2 Kő Dezső Programmer Designer 3 Hó Jenő 28/83

B IT M A N

Témakörök



SQL alapok



DDL utasítások



DML utasítások


29/83



Gyakorló feladatok




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

30/83

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); 31/83

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; 32/83

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;

33/83

B IT M A N

Témakörök



SQL alapok



DDL utasítások



DML utasítások


34/83



Gyakorló feladatok




B IT M A N



35/83

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. 36/83

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ó; 37/83

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 38/83

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

39/83

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'; 40/83

B IT M A N

Oracle!

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'

41/83

B IT M A N

Szelekciós függvények 2.

Oracle!

 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

42/83

B IT M A N

Oracle!

A függvények kipróbálása  select upper('KisKutyA') from dual

KISKUTYA

 select substr('KisKutyA',3,2) from dual

sK

 select to_date('2016.12.31', 'YYYY.MM.DD') from dual 12/31/2016

 select sqrt(17) from dual  select power(2, 4) from dual

43/83

4.1231056256176605498214098

16

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;

44/83

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ó;

45/83

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; 46/83

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;

47/83

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:

– – – – – – 48/83

Cross join (Descartes szorzet, teljes illesztés) Theta join – Feltételes illesztés Equijoin – Egyenlőségen alapuló illesztés Natural join – Természetes illesztés Outer join – Külső illesztés, Self join (tábla összekapcsolása önmagával).

B IT M A N

Cross join (Descartes szorzat, teljes illesztés) Relációs algebrában: r1 x r2 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 Nissan 1500000 49/83

B IT M A N

Theta join – Feltételes illesztés Relációs algebrában: r1 felt r2 SELECT mezőlista FROM tábla1, tábla2 WHERE feltétel;  Tetszőleges feltétel megadható, nemcsak egyenlőség!  Nem szükséges kapcsolat (idegen kulcs, azonos mező) a működéshez!

Autó: Rendszám Típus

Ember: Szín

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

Ár 120000 150000 25000

Ekód 114 178

Név

Összeg

Telefon

Kis Géza 180000 20-9555666 Jó Éva 100000 30-8765432

select rendszám,ár,név,összeg from Autó,Ember where összeg > ár;

50/83

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

Ár 120000 150000 25000 25000

Név Kis Géza Kis Géza Kis Géza Jó Éva

Összeg 180000 180000 180000 100000

B IT M A N

Equijoin – Egyenlőség alapú illesztés Relációs algebrában: r1 =felt r2 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étele külön-külön megadható!  Az ON részben csak egyenlő feltétel adható meg!  A táblákban nem kell megegyezniük a kapcsolattartó mezők nevének! 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 178 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ó inner join ember on tulaj=ekód where ár > 500000;

51/83

Rendszám GBC-765 JED-123

Név Kis Géza Jó Éva

B IT M A N

Equijoin – Egyenlőség alapú illesztés (2) Relációs algebrában: r1  =mező r2 SELECT mezőlista FROM tábla1 INNER JOIN tábla2 USING (mező);  A táblákban meg kell egyezniük a kapcsolattartó mezők nevének!

 Irányítható a kapcsolat a megfelelő mezőnevek megadásával! Rendszám Típus

Szín


Kor

Tulaj

Tulaj

Név

Kor

Telefon

29 14 38

114 178 102

114 178 102 155


29 41 36 18

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

select rendszám,név from autó inner join ember using (tulaj);

52/83

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

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

B IT M A N

Natural join – Természetes illesztés Relációs algebrában: r1 * r2 SELECT mezőlista FROM tábla1 NATURAL JOIN tábla2;  A táblákban meg kell egyezniük a kapcsolattartó mezők nevének!  Irányíthatatlan a kapcsolat, minden azonos nevű mező kapcsolódik! Rendszám Típus

Szín


Kor

Tulaj

Tulaj

Név

Kor

Telefon

29 14 38

114 178 102

114 178 102 155


29 41 36 18

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

select rendszám,név from autó natural join ember;

53/83

Rendszám GBC-765

Név Kis Géza

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



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 54/83

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;

55/83

Név

Cím

Telefon



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); 56/83

Rendszám JED-123 LOG-416

B IT M A N

Halmazműveletek Unió: Metszet: Különbség:

SELECT …UNION [ALL] SELECT …; SELECT …INTERSECT SELECT …; 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 57/83 IN (SELECT Tulaj FROM autó);

Név

Cím

Telefon



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

Név

Név


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)

58/83

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 59/83

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)

60/83

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



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); 61/83

B IT M A N


Ember: Szín

Ár


Tulaj

Ekód

114 178 102 114

114 178 102 155

Név

Cím

Telefon



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’); 62/83

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

B IT M A N


Ember: Szín

Ár


Tulaj

Ekód

114 178 102 114

114 178 102 155

Név

Cím

Telefon



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); 63/83

B IT M A N

Témakörök



SQL alapok



DDL utasítások



DML utasítások


64/83



Gyakorló feladatok




B IT M A N

DCL – Userek kezelése User létrehozása: CREATE USER ′usernev′@′host′ IDENTIFIED BY ′jelszó′; User törlése: DROP USER ′usernev′@′host′; Userek kezelése a sajátgépen: Create user ′breki′@′localhost′ identified by ′breki′; Drop user ′breki′@′localhost′;

65/83

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. 66/83

B IT M A N

DCL – Jogosultságok kezelése 2. Engedélyezhető műveletek:  CREATE: tábla létrehozása  DROP: tábla törlése INSERT: tábla rekordjainak bővítése  DELETE: tábla rekordjainak törlése  UPDATE: tábla rekordjainak módosítása  SELECT: tábla lekérdezése  ALL: minden művelet

67/83

B IT M A N

DCL – Jogosultságok kezelése 2. 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;

68/83

B IT M A N

w3school.com/sql

69/83

B IT M A N

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

B IT M A N

70/83

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 71/83

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!

72/83

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 73/83

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 74/83

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 75/83

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 76/83

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. 77/83

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 78/83

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 79/83használható. 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!

80/83

B IT M A N

81/83

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

82/83

B IT M A N

VÉGE 83/83

B IT M A N

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

Recommend Documents