Számítógép és szoftver üzemeltetői vizsga help!
-
#19
SQL lekérdezés, az SQL SELECT
Az egyszerű Select általános formája :
SELECT Ri1.A1, .. Rir.Ar
FROM R1, .. , Rk
WHERE ? ;
Ahol R1, .. , Rk : alaprelációk
R.A : R reláció A attribútuma
? : DRC formula
A lekérdezéssel ekvivalens relációs algebrai kifejezés :
? Ri1.A1, .. Rir.Ar(??` (R1X .. XRk))
A kiértékelés menete a relációs algebrai kifejezés szerint történik.
Az attribútumok felsorolása helyetti * karakter az összeset helyettesesíti.
Pl. - a negatív egyenlegű vásárlók neve
SELECT Vnév
FROM Vásárló
WHERE Egyenleg<0 ;
- azon szállítók neve, akik árujából Ló Béla rendelt már valaha
SELECT Szállító.SzNév
FROM Rendelés, Tétel, Szállító
WHERE Rendelés.VNév='Ló Béla'
AND Rendelés.Rsz=Tétel.Rsz
AND Tétel.Termék=Szállító.Termék ;
Az egyedi attribútumok neve előtti minősítés elhagyható.
A FROM utáni felsorolásban a relációknak alias nevet adhatunk.
...
FROM Rendelés R, Tétel T, Szállító Sz
...
Az eredményben levő ismétlődések kiszűrése :
SELECT DISTINCT .....
Feladat :
- Azon vevők neve és címe, akik vásároltak egyszerre legalább 5 patareszelőt
- Ló Béla által rendelt, 1200 piculánál olcsóbb termékek neve
- Azon vásárlók neve és címe, akik egyenlege kisebb, mint Ló Béla egyenlege
Speciális operátorok a WHERE klauzában
1. LIKE - összehasonlítás karakter-stringgel
<karakteres kifejezés> LIKE <minta>
Z betűvel kezdődő termékek listája
SELECT Termék
FROM Szállító
WHERE Termék LIKE 'Z'
OR Termék LIKE 'z' ;
A mintába tehető speciális karakterek :
% : tetszőleges számú karaktert helyettesíthet
_ : egy karaktert helyettesíthet
2. IN, ANY, ALL - Halmaz operátorok
Az összehasonlítás operandusaként szereplő halmazokat létrehozhatjuk felsorolással vagy belső SELECT utasítással (beágyazott select,
sub-query).
a., IN - halmazba tartozás
Azon rendelések listája, amelyek tartalmaznak Nyerget, Zablát, Lókötőt
SELECT Rsz
FROM Tétel
WHERE Termék IN ('Nyereg', 'Zabla', 'Lókötő')
Ló Béla által rendelt termékek listája
SELECT DISTINCT Termék
FROM Tétel
WHERE Rsz IN
( SELECT Rsz
FROM Rendelés
WHERE VNév='Ló Béla'
);
b., <kifejezés> ? ALL <halmaz> - a halmaz minden elemére történő megkötés
Jelentése : A ? ANY S : (?X)(X?S ? A?X)
<kifejezés> ? ANY <halmaz> - létezésre történő megkötés
Jelentése : A ? ANY S : (?X)(X?S ? A?X)
Pl. A legdrágább termék neve
SELECT Termék
FROM Szállító
WHERE Ár > ALL
( SELECT Ár
FROM Szállító
);
Feladat :
- Azon vevők neve, akik rendeltek a legdrágább termékből
- Azon szállítók neve, akik szállítanak olyan terméket, amit a Nyerges Ktsz is szállít
- Azon vevők neve és címe, akik rendeltek olyan terméket, amit már Ló Béla is rendelt
A beágyazott select
A beágyazott select általában nem tartalmazhat minden klauzát, csak
SELECT-FROM-WHERE felépítésű lekérdezés lehet.
Ha eredménye egy érték, akkor úgy kezelhetjük a külső lekérdezésben, mint egy konstanst.
Ha eredménye több érték (halmaz), akkor a halmazoperátorokkal kezelhetjük őket.
A beágyazott select fajtái :
- független
- korrelált
Ha független, akkor a kiértékeléskor először a belső lekérdezést értékeljük ki, aminek eredménye nem függ a külső lekérdezéstől.
Ha korrelált, akkor a belső lekérdezés eredménye függ a külső lekérdezéstől. Ez akkor jöhet létre, ha a belső lekérdezésben hivatkozunk a külső lekérdezésben szereplő valamely attribútumra.
Ekkor a külső lekérdezés FROM után szereplő relációinak minden sorára végrehajtódik a belső lekérdezés.
Pl.
CSAPOLJA (Kocsma, Sör)
SZERETI (Korhely, Sör)
ODAJÁR (Korhely, Kocsma)
Azon korhelyek listája, akik látogatnak olyan kocsmát, ahol csapolnak olyan sört, amit szeret.
SELECT DISTINCT Szereti.Korhely
FROM Szereti, Csapolja
WHERE Szereti.Sör=Csapolja.Sör
AND Csapolja.Kocsma IN
( SELECT Odajár.Kocsma
FROM Odajár
WHERE Odajár.Korhely=Szereti.Korhely
);
Feladat :
- Azon korhelyek listája, akik látogatnak olyan kocsmát, ahol csapolnak olyan sört, amit szeret. - belső lekérdezés nélkül
- Hova menjen Ló Béla ? Azon kocsmák listája, ahol csapolnak olyan sört, amit Ló Béla szeret.
- Kiket listáz ki a következő lekérdezés ?
SELECT Korhely
FROM Odajár
WHERE Kocsma NOT IN
( SELECT Kocsma
FROM Csapolja
WHERE Sör IN
( SELECT Sör
FROM Szereti
WHERE Szereti.Korhely=Odajár.Korhely
)
);
Összesítő operátorok
Operátorok : AVG, COUNT, SUM, MIN, MAX
Szintaktika : aggr_op(<kifejezés>)
aggr_op(DISTINCT <kifejezés>)
Pl. - A vásárlók egyenlegének átlaga
SELECT AVG(Egyenleg)
FROM Vásárló ;
- A zablát gyártók száma
SELECT COUNT(*) #ZablaGyártók
FROM Szállító
WHERE Termék = 'Zabla' ;
- Azok száma, akik rendeltek már Nyerget
SELECT COUNT(DISTINCT Rendelés.Vnév)
FROM Rendelés, Tétel
WHERE Tétel.Termék = 'Nyereg'
AND Tétel.Rsz = Rendelés.Rsz ;
Az összesítő művelet a WHERE -el kiválasztott összes rekordra számítódik ki. Ebben az esetben az eredménylistában csak konstansok szerepelhetnek és összesítő operátorok.
Az összesítés elvégezhető csoportosítás alapján is :
GROUP BY A1,A2, ... Ak
Két rekord akkor kerül egy csoportba az összesítéshez, ha A1, ... Ak attribútumaikban megegyeznek. Ezeknek az attribútumoknak az eredménylistában is szerepelniük kell, hogy az eredmény értelmezhető legyen.
Pl. - a termékek és azok átlagos árának listája
SELECT Termék, AVG(Ár) Átlagár
FROM Szállító
GROUP BY Termék ;
A csoportokat is szűrhetjük a jellemzőik alapján :
GROUP BY A1,A2, ... Ak
HAVING ?
? : logikai kifejezés, amely a csoportokra értékelődik ki
Pl. - azon termékek neve, szállítóinak száma és átlagára, amelyet legalább ketten szállítanak
SELECT Termék, COUNT(*) Darab, AVG(Ár) Átlagár
FROM Szállító
GROUP BY Termék
HAVING COUNT(*) > 1 ;
Pl. Azon korhelyek listája, akik csak olyan kocsmába járnak, ahol csapolnak olyan sört, amit szeret.
SELECT Korhely
FROM Szereti
WHERE 0 =
(SELECT Count (*)
FROM Odajár
WHERE Odajár.Korhely = Szereti.Korhely
AND NOT Szereti.Sör = ANY
( SELECT Sör
FROM Csapolja
WHERE Csapolja.Kocsma=Odajár.Kocsma
)
) ;
Feladat :
- Azon vásárlók száma, akik még nem rendeltek Lókötőt
- Lista a termékekről és azok számáról, akik már rendeltek belőle
Adatmódosítás
1. Új rekord beszúrása egy táblázatba
INSERT IGNORE INTO R [(<oszloplista>)]
VALUES (<értéklista>) ;
Ha nem adunk meg oszloplistát, akkor minden oszlopnak értéket kell adni, a definiálásnál megadott sorrend szerint.
Pl. -egy új vevő beszúrása, ismeretlen cím esetén
INSERT IGNORE INTO Vásárló (Vnév, Egyenleg)
VALUES ('Kiss Béla', 0 ) ;
Egy táblázathoz hozzáfűzhetjük egy lekérdezés eredményét is, természetesen az oszlopok adattípusának figyelembevételével.
NYERGES (Termék, Ár) - A Nyerges KTSZ. árúinak adatait tartalmazó létező táblázat
INSERT IGNORE INTO Nyerges
SELECT Termék, Ár
FROM Szállító
WHERE SzNév='Nyerges KTSZ' ;
2. Rekord törlése táblázatból
DELETE FROM R
WHERE ? ;
Pl. - A 42-es nyerget tartalmazó rendelések törlése
DELETE FROM Rendelés
WHERE Rsz IN
( SELECT Rsz
FROM Tétel
WHERE Termék = 'Nyereg / 42'
) ;