por Adam Szalay hace 7 años
393
Ver más
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
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;
Create table as(SELECT…)-> új tábla lesz abból, amit kiolvastam
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
IN lefut->eredmény->külső select
Forrás: előadás
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
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.
Ha az érték NULL(azaz nincs megadva), akkor azt 0-ként értelmezve. Így értelemszerűen más lesz az átlag.
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
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
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
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
CODD nem tartotta elfogadhatónak a relációs adatbázisban.
CREATE TABLE Termek (
Id int NOT NULL,
Nev nvarchar(50) NOT NULL,
Raktarkeszlet int NOT NULL
);
INSERT INTO Termek VALUES (NULL, NULL, NULL);
INSERT INTO Termek (Id, Nev, Raktarkeszlet) VALUES(1,NULL,15);
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.)
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
Ezzel járjuk körbe a faszerkezetet. "hierarchikus lekérdezés"
Az SQL nyelv az angolhoz hasonló.
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.
Angolszerű mondatok
Forrás: előadás diái
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!
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
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;
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
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;
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
Adatelérést vezérlő (Data Control Language –DCL)
Jogosultság szabályozás (GRANT)
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);
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)
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);