240 likes | 352 Views
Adatbázis gyakorlat. – SELECT - 2. –. Tarcsi Ádám 2006. március. Miről lesz szó?. SELECT befejezés: GROUP BY HAVING dátum kezelés Al-select (subselect) INSERT, DELETE, UPDATE, CREATE – selecttel. GROUP BY. SELECT … FROM … [WHERE …] GROUP BY mező1, [mező 2] [ORDER BY …]
E N D
Adatbázis gyakorlat – SELECT - 2. – Tarcsi Ádám 2006. március
Miről lesz szó? • SELECT befejezés: • GROUP BY • HAVING • dátum kezelés • Al-select (subselect) • INSERT, DELETE, UPDATE, CREATE – selecttel
GROUP BY SELECT … FROM … [WHERE …] GROUP BY mező1, [mező 2] [ORDER BY …] • Csoportosítás, csoport képzés. Megszámolhatjuk, hogy hány sor van egy csoportban, vagy kiválaszthatjuk a csoport egy kívánt tagját (pl.: a legidősebbet a csoportban) • Oszlopfüggvényeket (SUM, AVG, COUNT, stb.) az egyes csoportokban külön-külön hajtja végre • A GROUP BY-ban megadott mezőnek szerepelnie kell a SELECT utáni felsorolásban.
GROUP BY – 2. • A WHERE feltételnek megfelelő sorokat csoportosítja úgy, hogy a megadott csoportosítási kifejezéseken (GROUP BY x,y…) belül az egyező értékkel bírók, illetve azok teljes sorai kerülnek 1-1 csoportba. • Üres csoportok nem kerülnek be! • Feladat: Adjuk meg, hogy az egyes tanárok hány diáknak osztályfőnökei! (Azonosítóval) SELECT Ofo, COUNT(DAzon) AS Db FROM tDiak GROUP BY Ofo;
GROUP BY feladatok • Adjuk meg az egyes tanárok (névvel megadva) által oktatott diákok átlagéletkorát! Megjegyzés: Az eredmény táblába nem kerülnek bele azok a tanárok, akik nem tanítanak senkit sem.
HAVING • Csoportokra vonatkozó feltétel megadás. csak a GROUP BY utasítással együtt használjuk.
HAVING feladatok • Adjuk meg azon tanárok nevét, akik 2 diáknál többet tanítanak! • Adjuk meg azon tanárok nevét és tanítványainak átlagéletkorát, akik legalább 3 diákot tanítanak!
Dátumkezelés • Dátum tárolás: (évszázad,) év, nap, óra, perc, másodperc, századmásodperc. • SELECT sysdate FROM dual; - rendszeridő, dátum Eredménye:2007-02-28 15:20:03.0 • Dátummal végezhető műveletek: • dátum +- szám - napokat ad hozzá, vesz el • dátum – dátum - eltelt napok száma • dátum + szám/24 - szám db órát ad a dátumhoz
Dátum konverzió • Hozzunk létre egy új táblát: tEmber(EAzon:szám,Nev:szoveg,Szuletes:date) • Töltsük fel egy rekorddal: • INSERT INTO-val próbáljuk meg feltölteni! INSERT INTOtEmber VALUES (5,'Kiss János','1988-FEB-28 10:00:00.0'); Konverziós függvény!
Konverzió • TO_DATE(‘dátumérték’,’formátum’): number,char,varchar típusú dátumértéket adott formátumú dátummá alakítja. Pl.: TO_DATE('1988.feb.28.','YYYY.mon.DD.') • Most próbáljuk újra! INSERT INTO tEmber VALUES (10,'Kiss János',TO_DATE('1988.feb.28.','YYYY.mon.DD.'));
Konverzió TO_CHAR Lehetséges dátum formátummaszkok pl.: • YYYY - teljes évszám, számmal • YEAR - évszám betűvel • MM - hónapneve (pl:.03) • MONTH - hónap teljes neve, betűvel • mon - hónap 3 betűs rövidítése, kisbetűvel • WW - hét sorszáma az évben • DD - nap sorszáma a hónapban • D - nap sorszáma a héten • DAY - nap teljes neve, szövegesen
Konverzió – TO_CHAR • TO_CHAR(érték,’formátum’): Számból, dátumból (mezőt adott formátumú dátummá) karaktert. Lekérdezéskor. • SELECT TO_CHAR(sysdate,'MONTH') FROM DUAL; • Select TO_CHAR(Szuletes,'YYYY MONTH DAY') from tEmber; • SELECT TO_CHAR(TO_DATE('1979.sep.10','YYYY.mon.DD'),'YEAR MONTH DDD WW') FROM dual
Dátumkezelő függvények * • MOTHS_BETWEEN(dátum1,dátum2): két dátum között eltelt hónapok száma • ADD_MONTHS(dátum,n): n db naptári hónapot ad hozzá a dátumhoz. n negatív is lehet. • NEXT_DAY(dátum,’nap’):Meghatározza a megadott dátum utáni első ‘nap’ nevű nap dátumát.SELECT NEXT_DAY(sysdate,'monday') FROM dual; • LAST_DAY(dátum,’nap’): A megadott dátum hónapjának utolsó napját
Dátumkezelő függvények – 2. * UPDATE tEmber SET Szuletes=ADD_MONTHS(Szuletes,-20) WHERE EAzon=5;
Feladatok: • Hány olyan diák van, akit a 2-es azonosítójú tanár tanít? • Adjuk meg a tDiak táblában szereplők születési évszámát! • Adjuk meg a 10 és 20 év közötti diákok tanárainak nevét, abc sorrendben. (Minden név csak egyszer szerepeljen!) • Listázzuk ki az összes diákot, akik 15 és 25 éves kor között vannak, vagy a vezetéknevük „Har”! • Adjuk meg a tanított diákok száma alapján csökkenő sorrendbe rendezve, hogy az egyes tanárok hány diákot tanítanak!
Allekérdezés - subselect • Allekérdezés olyan SELECT utasítás, amely értéke(ke)t ad át egy külső utasításnak. • WHERE-ben vizsgálandó értékeket másik SELECT-ből hozzuk. Pl.: Ki idősebb, mint a 32-es azonosítójú diák? SELECT nev FROM tdiak WHERE kor> (SELECT kor FROM tdiak WHERE DAzon=32); Hogy is van ez?
Allekérdezés – subselect – 2. • Jelentőségük: ha egy külső utasítás eredménytáblájában valamely sor kiválasztása egy teljes tábla kiértékelésétől függ. • Tagadás esetén. Pl.: melyik tanár nem osztályfőnök • Először a belső SELECT hajtódik végre, ez lesz a külső feltétel. • WHERE, HAVING, FROM utasításrészben • Mindig egy összehasonlító operátor jobb oldalán van! • Zárójelbe ( ) kell tenni • Nem tartalmazhat ORDER BY utasításrészt • Fajái: • egysoros • többsoros
Egysoros subselect • A belső SELECT egyetlen sort ad vissza • Néha a DISTINCT záradékkal segíthetünk! Példa: • Adjuk meg azon tanárokat, akik legalább egy diákot tanítanak! (melyik tanár osztályfőnök?) • Adjuk meg azon tanárokat, akik nem tanítanak egy diákot sem!
Többsoros subselect • Hibás, ha az előbbi alselect több sort ad vissza. • Összehasonlító operátort használunk: • IN / NOT IN: a lista bármely elemével egyenlő – adott érték előfordulása • ANY: listát ad vissza, ha van egy elem, ami kielégíti a feltételt, akkor a WHERE feltétel teljesül – legalább 1. • ALL: listát ad vissza Teljesül a feltétel, ha a visszaadott lista minden eleme minden tagja teljesíti – minden esetben igaz • EXIST: a feltétel teljesül, ha az allekérdezés legalább egy sort ad vissza. • NOT ALL: legyalább egy esetben nem igaz • NOT ANY: egyetlen esetben sem igaz
Subselect feladatok • Adjuk meg a legidősebb diák nevét! • Kiket tanít még ‘Ka Pál’ tanára? • Listázzuk ki azokat a diákokat, akik legalább kétszer olyan idősek, mint a legfiatalabb diák. • Írassuk ki azoknak a tanároknak a neveit, akik nem tanítanak 20 évnél idősebb diákokat! • Listázzuk ki azoknak a diákoknak a nevét, akik fiatalabbak minden olyan diáknál, akiknek az osztályfőnöke ‘Szlávi Péter’
Allekérdezés -2. INSERT INTO INSERT INTO <táblanév> (<mezőnév1>, …, <mezőnévn>)(<al-select>) • INSERT INTO tDiak (DAzon, Nev, Kor, TAzon)(SELECT DAzon, Nev, Kor, 1 FROM tOsztaly) • INSERT INTO tDiak (DAzon, Nev, Kor, TAzon)(SELECT *, 1 FROM tOsztaly)
Allekérdezés -3. – UPDATE • UPDATE <táblanév> SET <mezőnév1> = <érték1>, …, <mezőnévk> = <értékk> WHERE <feltétel al-select-ekkel> • Változtassuk a kor mező értékét 30-ra azon diákoknál, akiket Zsakó László tanít! UPDATE tDiak SET TAzon=3WHERE TAzon IN (SELECT TAzon FROM tTanar WHERE Nev like ’Zsakó László’)
Allekérdezés -3. – DELETE FROM • DELETE FROM <táblanév> WHERE <feltétel al-select-ekkel> • Töröljük azokat a tanárokat, akik egy diákot sem tanítanak! DELETE FROM tTanar WHERE TAzon NOT IN (SELECT TAzon FROM tDiak)
Alkérdés CREATE TABLE-ben CREATE table (… ) AS SELECT …