3_eloadas_vol2

Meer zoals dit

DML utasítások és tranzakciókezelés

door Kiss Máté

4. előadás gondolattérképe

door Anna Szalay

Előadás_3_Mindmap_MülhauserAttila_AN40E0

door Attila Mülhauser

Adatintegritási szabályok (adatbázis kényszerek)

door Szilard Denes

SQL szintaxis

Bonyolítás

Tárolt lekérdezések

Materializált nézet

Az eddigiekben a virtuális nézetekkel ismerkedtünk meg: amikor az általuk reprezentált adathoz hozzá akarunk férni, akkor a nézetbe ágyazott lekérdezés minden egyes alkalommal kiértékelődik. A nézetek egy másik fajtája az ún. materializált nézet, amely a nézetbe ágyazott lekérdezés által előállított rekordokat fizikailag is eltárolja. Ezáltal amikor az általuk megjelenített adatot fel akarjuk használni, akkor az rögtön rendelkezésre áll a lemezen, így a rájuk építő lekérdezések jelentősen gyorsulnak. Az ilyen nézeteket rendszeresen frissíteni is kell, hiszen az általuk hivatkozott táblák tartalma is folyamatosan változhat.

Ez az adattárházak kedvenc eleme, mert a képlet és az adat is átmásolódik.

Forrás: jegyzet, előadás

Nézet létrehozása

Csak nézet születik, adatokat nem tárol.

Forrás: előadás

Ha egy olyan nézetre van szükségünk, ami a legnagyobb megrendelés-állománnyal rendelkező termékeket mutatja, akkor azt a következő utasítással hozhatjuk létre:

CREATE VIEW TopTermekek AS SELECT Termek.TermekKod, Nev, SUM(Darab) Darab FROM Termek INNER JOIN MegrendelesTetel ON Termek.TermekKod=MegrendelesTetel.TermekKod GROUP BY Termek.TermekKod, Termek.Nev ORDER BY SUM(Darab) DESC;

Eredménytárolás (statikus)

Create table as(SELECT…)-> új tábla lesz abból, amit kiolvastam

Összetett lekérdezések

A lekérdezések tetszőlegesen egymásba ágyazhatók a SELECT után és a WHERE feltétel után egyaránt.

SELECT nev, leiras FROM gyumolcsok WHERE nev IN (SELECT DISTINCT gyumcsolnev FROM kiszalltiasok);

A SELECT utáni al-lekérdezéseknek csak egyetlen visszatérési értéke lehet(nem lehet az eredmény több sor).

SELECT nev, (SELECT COUNT(1) FROM kiszallitasok WHERE gyumolcsnev = nev) FROM gyumolcsok;

Forrás: http://nyelvek.inf.elte.hu/leirasok/SQL/index.php?chapter=7

Többoszlopos allekérdezés

IN lefut->eredmény->külső select

Forrás: előadás

Inline nézet

A materializált nézetet létrehozó SELECT utasítás meghatározza, hogy a materializált nézet milyen adatokat tartalmaz. Csak néhány megszorítás van, amit specifikálhatunk. Tetszőleges számú táblát összekapcsolhatunk. A táblák mellett nézeteket, inline nézeteket (allekérdezés a SELECT utasítás FROM utasításrészében), allekérdezéseket, materializált nézeteket használhatunk az összekapcsolásban. A definiáló lekérdezés SELECT listájában viszont nem lehet allekérdezés, de például a WHERE feltételben igen.

Forrás: http://www.tankonyvtar.hu/hu/tartalom/tamop412A/2010-0011_oracle_adattarhaz/lecke4_lap2.scorml

Csoportfüggvények

Több sorhoz egy soros eredményt adnak vissza. Ugyanúgy függvényként működnek, mint például az excelben. Nem adja meg, hogy melyik az a bizonyos rekord, csupán egy értéket ad vissza.

NVL

Ha az érték NULL(azaz nincs megadva), akkor azt 0-ként értelmezve. Így értelemszerűen más lesz az átlag.

AVG

az AVG(A) művelet az A oszlopban tárolt numerikus értékek átlagát állítja elő.

/*az alkalmazott táblában mennyi a fizetések átlaga/

SELECT AVG(fizetes) FROM alkalmazott;

Forrás: http://www.agt.bme.hu/szakm/adatb/ora.htm

MIN, MAX

a MIN(A) illetve MAX(A) operátorok az A oszlop legkisebb illetve legnagyobb értékeit adják vissza. Numerikus értékeket tároló oszlopoknál a működésük egyértelmű, stringeket esetén pedig abc szerint adja vissza az értékeket.

/*az alkalmazott táblában mennyi a fizetések minimumam-maximuma/

SELECT MIN(fizetes) FROM alkalmazott;

SELECT MAX(fizetes) FROM alkalmazott;

Forrás: http://www.agt.bme.hu/szakm/adatb/ora.htm

Sum

a SUM(A) művelet összegzi az A oszlopban tárolt numerikus értékeket

/*az alkalmazott táblában mennyi a fizetések összege/

SELECT SUM(fizetes) FROM alkalmazott;

Forrás: http://www.agt.bme.hu/szakm/adatb/ora.htm

Count

a COUNT(A) művelet megszámolja, hogy hány ürestől különböző, de nem feltétlen egyedi érték található az A oszlopban. Egyszerűsítésként használható a COUNT(*) kifejezés is, amely a reláció összes sorának számát adja vissza. (itt az A az előadás diáin a DistinctComission volt)

Ez a fv. a NULL értéket ignorálja.

Forrás: jegyzet, előadás

/* managerek szama */
select count(*) from alkalmazott where beosztas = 'MANAGER';

Forrás: http://www.agt.bme.hu/szakm/adatb/ora.htm

Null érték

CODD nem tartotta elfogadhatónak a relációs adatbázisban.

Ha meg akarjuk tiltani, akkor használjuk a NOT NULL opciót

CREATE TABLE Termek (

Id int NOT NULL,

Nev nvarchar(50) NOT NULL,

Raktarkeszlet int NOT NULL

);

Nem csak stringre, bármilyen típusra lehetséges

INSERT INTO Termek VALUES (NULL, NULL, NULL);

Egy rekord egy attribútuma nincs beállítva, üres

Vagy mert NULL értéket adunk:

INSERT INTO Termek (Id, Nev, Raktarkeszlet) VALUES(1,NULL,15);

Probléma

Gyakori hiba

0 érték nem egyenlő zérussal

SELECT * FROM Hallgatók WHERE Neptun = NULL;

0 az értéke(üres eredményhalmaz), mivel a Neptun értéke sehol sem 0

SELECT * FROM Hallgatók WHERE Neptun IS NULL;

nincs értéke (Ez a jó, ha hiányzó értékre keresünk.)

Összekapcsolódásnál furcsán viselkedik

Hierarchikus lekérdezések

Fastruktúrájú adatokat relációs táblákban helyezhetünk el, és hierarchikus lekérdezésekkel tudjuk rekonstruálni a hierarchiát.

Forrás: előadás diái

START WITH

Ezzel járjuk körbe a faszerkezetet. "hierarchikus lekérdezés"

Szabványosság

Az SQL nyelv az angolhoz hasonló.

Különböző nyelvjárások

Az amerikai szabványügyi hivatal a cégek hatására több utasítást is elfogadott, így nyelvjárások alakultak ki.

MySQL

PostgreSQL

Oracle

MS SQL

Nyelvi elemek

Angolszerű mondatok

Forrás: előadás diái

DQL

Az SQL-ben szigorú a szórend, mint a németben.

Színkódolás:

piros - kötelező megadni

narancssárga - kötelező, de választható

zöld - választható

kék - választhatónak a választható része

citromsárga - (logikai) condition feltétel: minden egyes sorra eldönti hogy igaz-e; csak egy feltétel de ez lehet összetett feltétel!

ORDER BY

NEM RELÁCIÓS MŰVELET! (mivel a relációban a sorok sorrendje tetszőleges) lehet úgy rendezni amit a select meg sem jelenít

megj: feldolg. sorrend: 4. ergo lehet alissal hivatkozni

LIMIT

Az első hány találatot listázza ki.

Sorbarendezés módja

Gyakori igény, hogy a lekérdezés során előállított rekordokat valamely attribútum szerint sorba rendezzük.

DESC (descending, csökkenő)

Csökkenő sorbarendezés

ASC (ascending, növekvő; ALAPÉRTELMEZET!)

Növekvő sorbarendezés; alapértelmezett

GROEP BY

A csoportosítási műveletet az SQL-ben a GROUP BY utasítással valósíthatjuk meg, amelyet a csoportosító attribútumok követnek. A csoportosítás a szelekció (WHERE feltétel) alkalmazása után történik meg, és szintaktikailag is a szelekciós feltétel után következik. Ha a lekérdezés csoportosítást alkalmaz, akkor a SELECT kulcsszó után csak csoportosító attribútumok vagy aggregált értékek állhatnak: ekkor az aggregálás csoportonként történik meg.

HAVING

Ha a csoportosítás után előálló rekordokra szeretnénk szelekciós feltételt megadni, akkor erre a célra használhatjuk a GROUP BY utáni HAVING kulcsszót, amely mögött egy további szelekciós feltételt adhatunk meg, ami már a csoportosítás(aggregálás) után alkalmazódik.

Forrás:

http://docs.oracle.com/javadb/10.10.1.2/ref/rrefsqlj14854.html

Ha azt szeretnénk megtudni, hogy melyek azok a kategóriák, amelyekben legalább 5 különböző, 10.000 Ft-nál többe kerülő termék fajta található, akkor az a következőképp válaszolhatjuk meg:

SELECT Kategoria, COUNT(*) FROM Termek WHERE Ar > 10000 GROUP BY Kategoria HAVING COUNT(*) >=5;

WHERE

A WHERE kulcsszó utáni szelekciós feltétel határozza meg, hogy a vizsgált rekordok közül melyik fog bekerülni a végeredménybe.

BETWEEN

logikai feltétel

ez a szűrés

FROM

Feldolgozási sorrendben az 1. => Nem tudunk aliassal hivatkozni, mivel a lekérdezés logikája szerint a select később jön

Tábla

Felsoroljuk melyik táblá(k)ból szeretnénk adatokat kinyerni.

Forrás: előadás

JOIN

A relációs adatbázis-kezelő rendszerek igazi ereje abban rejlik, hogy lehetőséget nyújtanak olyan lekérdezések megfogalmazására és végrehajtására, amelyek több relációból gyűjtik össze a kívánt adatot.

Forrás: Előadás

Descartes szorzat (Cross join)

Descartes szorzat: a valós élet nem nagyon generál olyen helyzeteket, ahol ezt használnunk kellene. Rengeteg rekord lenne, valószínűleg sok felesleges sorral is egy táblázatban.

"Általában ezt olyankor használjuk, amikor egy korábbi hibát szeretnénk orvosolni."

Forrás: Előadás, előadás diái

SELECT * FROM Termek CROSS JOIN Gyarto;

Az eredmény reláció sémája a két séma egymás után illesztéséből áll elő.

Itt a termék tábla minden oszlopát összeszorozzuk a gyártó tábla minden oszlopával (mivel nem adtunk meg ON klauzulát).

Külső összekapcsolások (Outer Join)

Más információs igényt valósít meg, pl 2 jegy egy hallgató: elsőnek van párja, másodiknak már nincs, eddigiek nem adnák ki eredménynek.

FULL OUTER JOIN

A FULL OUTER JOIN mindent megjelenít. Akkor is, ha valamelyik oldalon nincsen párja.

Forrás: Előadás diái

SELECT Szemely.Nev, Szulo.Nev FROM Szemely

FULL OUTER JOIN Szemely Szulo

ON Szemely.AnyaId=Szulo.Id OR Szemely.ApaId=Szulo.Id;

RIGHT OUTER JOIN

Példa: Hasonlóan, mint a left outer join esetében.

SELECT GyartoNev, TermekNev FROM Gyarto

RIGHT OUTER JOIN Termek ON Gyarto.GyartoKod=Termek.GyartoKod

WHERE Termek.TermekKod IS NULL;

LEFT OUTER JOIN

Baloldal minden sorát kiírja. Akkor is, ha nincs párja.

Forrás: előadás diái

Példa:

SELECT GyartoNev, TermekNev FROM Gyarto

LEFT OUTER JOIN Termek ON Gyarto.GyartoKod=Termek.GyartoKod

WHERE Termek.TermekKod IS NULL;

Nem egyen-összekapcsolások (Non equi-join)

NON-EQUI-join: a két tábla között nincsen egyenlőség. Például: fizetési osztályok, sávos adózás stb..

Forrás: előadás diái

SELECT e.first_name, e.last_name, e.salary, j.grade_level

FROM workers e JOIN job_grades j

ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;

Forrás: előadás diái

Egyen-összekapcsolások (Equi-Join)

Egyen-összekapcsolások (EQUI-join): párbarendezés két azonos oszlop alapján, összekapcsolás feltétele az egyenlőség

ON

Általában ON klauzulát használunk. (Akkor használhatjuk, amikor az összejoinolt klauzulák között nincsenek azonos nevű oszlopok.)

Forrás:

http://docs.oracle.com/javadb/10.10.1.2/ref/rrefsqljusing.html

select department_name, city
from departments dept
join locations loc
on (d.location_id = l.id);

USING

A USING klauzula akkor hasznos, amikor két oszlopnak azonos a neve, amiket JOIN-oltunk.

Forrás:

http://docs.oracle.com/javadb/10.10.1.2/ref/rrefsqljusing.html

SELECT * FROM COUNTRIES JOIN CITIES
     USING (COUNTRY);

Forrás:

http://docs.oracle.com/javadb/10.10.1.2/ref/rrefsqljusing.html

Natural-Join

A természetes illesztést SQL-ben a NATURAL JOIN operátorral lehet végrehajtani.

Az a lényeg, hogy a két táblát természetesen összeilleszti. Ahol ugyanazolyan nevűek az oszlopok, azt egynek veszi, ahol különböznek, azt külön kiírja. Tehát pl. ha van két táblám, és mindegyikben 3-3 oszlop van, amelyikből 1-1 megegyezik, akkor a SELECT utasítás eredményül egy 5 oszlopos eredmény fog adni.

SELECT * FROM Termek NATURAL JOIN Gyarto;

Select

Ez a vetítés relációnak felel meg.

(Feldolgozási sorrendben a 3.)

Vesszővel választjuk el a felsorolást (amikor felsoroljuk melyik oszlopokat szeretnénk megkapni).

Forrás: előadás, gyakorlat

AS

A kapott (eredmény)oszlop elnevezésére szolgál(nem szükséges).

Forrás: Gyakorlat

*

Az összes oszlopot jelenítse meg.

Oszlopnevek

Felsoroljuk a megjeleníteni kívánt oszlopokat.

Predikátumok

Az Structured Query Language (strukturált lekérdezőnyelv – SQL)-lekérdezések által kijelölt rekordok körét szabják meg.

Forrás: https://support.office.com/hu-hu/article/ALL-DISTINCT-DISTINCTROW-TOP-predik%C3%A1tumok-24f2a47d-a803-4c7c-8e81-756fe298ce57

DISTINCTROW

Mezők helyett teljes rekordok alapján hagyja el a többszörös adatokat.

Fontos: Halmaz szemantika az igaz rá.

Forrás:

https://support.office.com/hu-hu/article/ALL-DISTINCT-DISTINCTROW-TOP-predik%C3%A1tumok-24f2a47d-a803-4c7c-8e81-756fe298ce57

Vegyünk például egy olyan lekérdezést, amely a Vevőkód alapján illeszti a Vevők és a Rendelések táblát. A Vevők tábla nem tartalmaz többszörös Vevőkód mezőket, a Rendelések tábla viszont igen, hiszen egy-egy vevőhöz több rendelés is tartozhat. Az alábbi SQL-utasítás szemlélteti, hogy a DISTINCTROW segítségével hogyan kérdezheti le azoknak a cégeknek a listáját, amelyekhez legalább egy rendelés tartozik, ha a rendelések további részleteire nem kíváncsi:

SELECT DISTINCTROW Cégnév FROM Vevők INNER JOIN Rendelések ON Vevők.Vevőkód = Rendelések.Vevőkód ORDER BY Cégnév;

ALL

Alapértelmezett választás, amennyiben a fenti predikátumok egyikét sem használja. A Microsoft Access-adatbázismotor minden olyan rekordot visszaad, amely megfelel az SQL-karakterlánc/utasítás szereplő feltételeknek.

Fontos: Az ALL zsák szemantikát követ.

az Alkalmazottak tábla összes rekordját adja vissza:

SELECT ALL * FROM Alkalmazottak ORDER BY Alkalmazottkód;

Forrás:

https://support.office.com/hu-hu/article/ALL-DISTINCT-DISTINCTROW-TOP-predik%C3%A1tumok-24f2a47d-a803-4c7c-8e81-756fe298ce57

DISTINCT

Elhagyja azokat a rekordokat, amelyek többszörös adatokat tartalmaznak a kijelölt mezőkben.

Fontos: A DISTINCT-re a halmaz szemantika igaz.

Forrás: Előadás, https://support.office.com/hu-hu/article/ALL-DISTINCT-DISTINCTROW-TOP-predik%C3%A1tumok-24f2a47d-a803-4c7c-8e81-756fe298ce57

SELECT DISTINCT szulev FROM minta.alkalmazott;

Forrás: 3. gyakorlat

DCL

Adatelérést vezérlő (Data Control Language –DCL)

Jogosultság szabályozás (GRANT)

DDL

Adatleíró (Data Definition Language -DDL)

Felhasználói objektumok kezelése

Új objektumok (adatbázisok, relációk, nézetek, kényszerek, ...) létrehozása

Meglévő objektumok módosítása (pl reláció kibővítése), törlése

CREATE, ALTER, DROP...

Forrás:db_02_SQL

DROP

Tábla törlése

szintaxis: DROP TABLE <táblanév>

Példa: DROP TABLE MegrendelesTetel;

PURGE

A PURGE végérvényesen kitörli a táblát gépről. (Ezzel szembe a DROP csak a lomtárba helyezi.)

Forrás:

http://www.dba-oracle.com/t_oracle_purge_recycle_bin.htm

DROP TABLE alkalmazott PURGE; 

Forrás: 2. gyakorlat

ALTER

Létező attribútum tulajdonságainak változtatása

Példa: ALTER TABLE <táblanév> MODIFY <oszlopnév>

<új típus> <új megkötések>;

Létező attribútum törlése

Szintaxis: ALTER TABLE <táblanév> DROP COLUMN <oszlopnév>

Példa: ALTER TABLE Termek DROP COLUMN Raktarkeszlet;

Új attribútum felvétele

Szintaxis: ALTER TABLE <táblanév> ADD <új oszlop> <típus> <megköt.>

Példa: ALTER TABLE Termek ADD Leiras nvarchar(500) DEFAULT ’?’;

CREATE

Új adatbázis létrehozása

CREATE TABLE Megrendeles(

Id int,

Megrendelo nvarchar(50),

Cim nvarchar(50),

Datum datetime);

DML

Adatmódosító (Data Manipulation Language –DML)

Új adatok (példányok, entitások) beszúrása (INSERT)

Létező adatok módosítása (UPDATE)

Létező adatok törlése (DELETE)

Utasítások

DELETE

Létező adatok törlése

Ki szeretnénk törölni a "Alfreds Futterkiste" customer-t a "Customers" táblából.

DELETE FROM Customers

WHERE CustomerName='Alfreds Futterkiste' AND ContactName='Maria Anders';

Forrás: https://www.w3schools.com/sql/sql_delete.asp

UPDATE

A táblában már elérhető adatok frissítése.

Változtassuk meg a város oszlopban az egyik rekordot a Customers táblában:

UPDATE Customers

SET City='Hamburg'

WHERE CustomerID=1;

Forrás: https://www.w3schools.com/sql/sql_update.asp

INSERT

Új adat bevitele

Példa

INSERT INTO Termek (Id, Nev, Raktarkeszlet) VALUES (1, ’Alma’, 15);