A gyakorlat során MySQL adatbázis szerver és a böngészőben futó phpmyadmin használata javasolt. A gyakorlat során a következőket fogjuk gyakorolni:

ÜZLETI INFORMATIKAI ESZKÖZÖK Adatbáziskezelés

1

|

Adatbáziskezelés 3. gyakorlat

A gyakorlat során MySQL adatbázisszerver és a böngészőben futó phpMyAdmin használata javasolt. A gyakorlat során a következőket fogjuk gyakorolni: ● Tábla kapcsolatok létrehozása, kezelése ● Új tábla létrehozása, adatokkal való feltöltése ● Lekérdezés csoportosítással ● Lekérdezés több tábla kapcsolatával ● Nézet létrehozása

Csatlakozás az adatbázishoz Indítsunk egy böngészőt, és írjuk bele: http://193.224.129.119/dbgyak . A gyakorlat céljaira használjuk az uNEPTUNKÓD felhasználónevet. Az ehhez tartozó jelszó is ugyanaz. Az ABC123 neptunkódú hallgató felhasználói neve tehát uABC123, jelszava uABC123. A továbbiakban, amikor e leírásban az uNEPTUN vagy hasonló nevet használjuk, akkor azt minden hallgató a saját neptun kódjának behelyettesítésével értelmezze.

Új feladatok Vizsgáljuk meg a jarmu tábla szerkezetét: SHOW CREATE TABLE jarmu Ellenőrizzük, hogy a tábla típusa InnoDB legyen, ha nem az, a korábban már használt paranccsal állítsuk be a jarmu és napsugár táblákat: ALTER TABLE napsugár ENGINE=InnoDB; ALTER TABLE jarmu ENGINE=InnoDB; © Fülep Dávid (Széchenyi István Egyetem), 201415


2

|

Azt is láthatjuk, hogy milyen néven (pl. jarmu_ibfk_1) hivatkozhatunk a két tábla kapcsolatára. Ez a név hasznos például, ha törölni akarjuk: ALTER TABLE jarmu DROP FOREIGN KEY jarmu_ibfk_1; Tegyük fel, most azt szeretnénk megoldani, hogy ha egy gyereket törlünk, mert például kijárta az óvodát, akkor a hozzá tartozó jármű is törlődjön az adatbázisból: ALTER TABLE jarmu ADD FOREIGN KEY(tulajdonos) REFERENCES napsugár (id) ON DELETE CASCADE; Próbájunk is egy olyan gyereket törölni, akinek van járműve és figyeljük meg, mi történik. (Visszafelé nem működik ez a törlés, hiszen ha a járművet töröljük, a gyerek megmarad! Igény esetén ezt is meg lehetne oldani például triggerrel, de a konkrét megoldás nem tananyag.) Számoljuk meg, hány gyerek van a napsugár táblában: SELECT COUNT(*) FROM napsugár; Csoportosítsuk a gyerekeket a csoportkódjuk alapján: SELECT * FROM napsugár GROUP BY csoportkód Figyelem! Ennek a lekérdezésnek nehéz értelmet találni: az egyes csoportokba tartozó “első” rekord adatait írta ki, ez a gyakorlatban nem hasznos. A csoportképzésnél nekünk kell meghatározni, milyen adatokra vagyunk kíváncsiak a csoportról. Például, nézzük meg az egyes csoportok létszámát: SELECT csoportkód, COUNT(*) AS Csoportlétszám FROM napsugár GROUP BY csoportkód

© Fülep Dávid (Széchenyi István Egyetem), 201415


3

|

Oldjuk meg, hogy a csoportkódok mellett/helyett a csoportok valódi nevei szerepeljenek! Ehhez egy kis előkészületre és újabb ismeretekre van szükségünk, ezért jegyezzük meg ezt a feladatot, és később visszatérünk rá. Kezdjük el a megvalósításhoz szükséges lépéseket. Ehhez hozzunk létre egy csoport táblát, töltsük fel adatokkal, kössük a napsugár táblához. Ezt a tanultak alapján végezzük el önállóan. Gyakorlásképpen a megfelelő SQL parancsokkal végezzük el ezt a feladatot is, ne a kliens grafikus felületén. Ha a csoport táblát létrehoztuk, akkor egy olyan lekérdezést kell írnunk, amiben összekötjük a napsugár és csoport táblákat egymással, hiszen a szükséges adatok egy része az egyik, másik része a másik táblában van tárolva. Az összekapcsolás megértéséhez először képezzük a két tábla Descartes szorzatát: SELECT * FROM napsugár, csoport;



4

|

Figyeljük meg jól az eredményt: minden gyerekhez minden csoportot hozzákötöttünk. Azt mondhatjuk, hogy a Descartes szorzat teljes halmazát leképezni a sok esetben értelmetlen, haszontalan, ráadásul a táblák elemszámának szorzata adja az eredményhalmaz sorainak a számát, ami hatalmas rekordszámhoz vezethet. Esetünkben csak a megfelelően párosításokra van szükség, ahol a párosítás alapja a napsugár tábla Csoportkód mezőjének és a Csoport tábla id mezőjének az egyezősége: SELECT * FROM napsugár, csoport WHERE napsugár.csoportkód = csoport.id;



5

|

Így már a megfelelő párok képződtek:

Ebben a lekérdezésben kétszer is szerepel a csoportkód, holott valójában egyik oszlopra sincsen szükségünk. Ezért ki is vehetjük a lekérdezésből, és csak a számunkra fontosakat hagyjuk meg, például: SELECT napsugár.név AS Gyerek, jel AS Jel, csoport.név AS Csoportnév, anyjaneve FROM napsugár, csoport WHERE napsugár.csoportkód = csoport.id; Figyeljünk arra, hogy ha egy mező neve nem egyértelmű a táblanév nélkül (mert több táblában is van ugyanolyan nevű), akkor meg kell adnunk a tábla nevét is táblanév.mezőnév formában. © Fülep Dávid (Széchenyi István Egyetem), 201415


6

|

Most már visszatérhetünk a korábbi feladatunkhoz, számoljuk össze az egyes csoportok létszámát, és a lekérdezés eredményében a csoportok nevei szerepeljenek: SELECT csoport.név AS Csoportnév, COUNT(*) AS Csoportlétszám FROM napsugár, csoport WHERE napsugár.csoportkód = csoport.id GROUP BY csoportkód;



7

|

Az eredménynek ehhez hasonlóan kell kinéznie:

A fenti lekérdezést az óvoda vezetése olyan gyakran lefuttatja, hogy jó lenne valahogy megoldani, hogy a fenti listához bármikor hamar hozzájussunk. Első megoldásként eszünkbe juthat, hogy egy új táblát generálunk a fenti lekérdezés beágyazásával: CREATE TABLE letszamok AS SELECT csoport.név AS Csoportnév, COUNT(*) AS Csoportlétszám FROM napsugár, csoport WHERE napsugár.csoportkód = csoport.id GROUP BY csoportkód; Ez azonban elég gyenge megoldás, hiszen statikus. Töröljük is ki önállóan! Jó lehet azonban például az év eleji állapot rögzítésére: CREATE TABLE letszamok_2014_szept_1 AS SELECT csoport.név AS Csoportnév, COUNT(*) AS Csoportlétszám FROM napsugár, csoport WHERE napsugár.csoportkód = csoport.id GROUP BY csoportkód;



8

|

Készítsünk egy olyan dinamikus táblát, aminek tartalma mindig a fenti lekérdezés aktuális eredményét tartalmazza! Az ilyen speciális táblákat nézetnek (VIEW) nevezzük: CREATE VIEW letszamok AS SELECT csoport.név AS Csoportnév, COUNT(*) AS Csoportlétszám FROM napsugár, csoport WHERE napsugár.csoportkód = csoport.id GROUP BY csoportkód; A nézet tehát olyan tábla, ahol nem a benne tárolt adatokat, hanem az adatok előállítására szolgáló lekérdezést tároljuk. Gondoljuk végig ennek a hatásait: ● A nézetnek gyakorlatilag nincs helyfoglalása ● A nézet használata némileg több időt vehet igénybe, hiszen a kapcsolt lekérdezést mindig le kell futtatni ● A nézet tartalma mindig más táblák tartalmától függ ● A nézeten logikus módon nem lehet adatokat módosítani, például rekordot törölni ● A nézetek kényelmessé teszik a gyakori lekérdezések használatát ● A nézet segítségével nagyon jól szabályozhatók az egyes felhasználók jogkörei. A fentiek figyelembe vételével a nézetet úgy használhatjuk, mint egy táblát: SELECT * from letszamok; Listázzuk ki azon csoportokat, amelyek létszáma az átlagosnál magasabb: SELECT * FROM letszamok WHERE Csoportlétszám > (SELECT AVG(Csoportlétszám) FROM letszamok); Készítsünk nézetet, ami a győri lakcímmel rendelkező gyerekek összes adatát tartalmazza!

A feladat befejezése A munka végeztével jelentkezzünk ki az adatbázis kliensből.


A gyakorlat során MySQL adatbázis szerver és a böngészőben futó phpmyadmin használata javasolt. A gyakorlat során a következőket fogjuk gyakorolni:

Recommend Documents