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

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

1

|

Adatbáziskezelés 4. gyakorlat

A gyakorlat során MySQL adatbázisszerver és a böngészőben futó phpMyAdmin használata javasolt. A gyakorlaton a következőket fogjuk gyakorolni: ● Adatlekérdezés ● Tábla létrehozása, feltöltése ● Nézet készítése

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.

Hajómenetrend A dbGyak adatbázisban vizsgáljuk meg a menetrend és kikoto táblák szerkezetét és tartalmát! Ezekkel a táblákkal fogunk dolgozni. SELECT * FROM kikoto; SHOW CREATE TABLE kikoto; SELECT * FROM menetrend; SHOW CREATE TABLE menetrend;

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


2

|

Kérdezzük le a D1 hajójárat menetrendjét annak minden adatával együtt! Lépésenként fogjuk a feladatot megoldani, kezdjünk neki: SELECT * FROM menetrend JOIN kikoto ON kikoto.id=menetrend.honnan WHERE jarat="D1" Már most észrevehetjük, hogy jól el kell majd neveznünk az eredményreláció oszlopait, hogy a kapott eredmény értékelhető legyen. Mivel a kikoto táblát a célállomás miatt is használnunk kell, azt is el kell neveznünk a kikötő aktuális feladatának megfelelően. A szükségtelen mezőket pedig hagyjuk el: SELECT jarat, honnan .név, indul, erkezik FROM menetrend JOIN kikoto AS honnan ON honnan .id=menetrend.honnan WHERE jarat="D1"; Így már könnyű a lekérdezéshez fűzni a célállomás nevét: SELECT jarat, honnan.név, cel.név, indul, erkezik FROM menetrend JOIN kikoto AS honnan ON honnan.id=menetrend.honnan JOIN kikoto AS cel ON cel.id=menetrend.hova WHERE jarat="D1"; Efféle hosszú parancsok gépelésekor könnyű hibázni. Próbáljuk megmondani, mi történne, ha az indul szó előtti vesszőt elfelejtettük volna begépelni: SELECT jarat, honnan.név, cel.név indul , erkezik FROM menetrend JOIN kikoto AS honnan ON honnan.id=menetrend.honnan JOIN kikoto AS cel ON cel.id=menetrend.hova WHERE jarat="D1"; Ha gondolatban megválaszoltuk, hogy mi történik, ha elvesszük a kérdéses vesszőt, próbaképpen futtassuk is le a hibás lekérdezést, hogy ellenőrizhessük az eredményt.



3

|

Visszatérve az eredeti feladatunkhoz, már csak az maradt hátra, hogy megfelelően elnevezzük az eredményreláció oszlopait: SELECT jarat AS Járat, honnan.név Honnan, cel.név Hova, indul AS Indul, erkezik AS Érkezik FROM menetrend JOIN kikoto AS honnan ON honnan.id=menetrend.honnan JOIN kikoto AS cel ON cel.id=menetrend.hova WHERE jarat="D1";



4

|

Most listázzuk ki, a Balatonfüredet érintő hajók menetrendjét két részben (külön az induló és külön az érkező hajókat). Kezdjük a Balatonfüredről induló hajók menetrendjével: SELECT cel.név AS Cél, jarat AS Járat, indul AS Indul, erkezik AS Érkezik FROM menetrend JOIN kikoto AS honnan ON honnan.id=menetrend.honnan JOIN kikoto AS cel ON cel.id=menetrend.hova WHERE honnan.név="Balatonfüred" ORDER BY indul;



5

|

Az előző lekérdezést alakítsuk át úgy, hogy a Balatonfüredre érkező hajók menetrendjét mutassa, érkezési időpontjuk szerint: SELECT honnan .név AS Honnan , jarat AS Járat, indul AS Indul, erkezik AS Érkezik FROM menetrend JOIN kikoto AS honnan ON honnan.id=menetrend.honnan JOIN kikoto AS cel ON cel.id=menetrend.hova WHERE cel .név="Balatonfüred" ORDER BY indul; Válaszoljuk meg, mi a járatszáma az aznapi 5. érkező hajónak (a fenti példa esetében J1). Tegyük fel, hogy délben érkezünk az állomásra, és a legelső induló járattal szeretnénk továbbállni. Keressük ki, hogy melyik kikötőbe indul majd ez a hajó: (A megoldásnál természetesen felhasználhatjuk Balatonfüred korábban elkészített induló menetrendjét) SELECT cel.név AS Cél, jarat AS Járat, indul AS Indul, erkezik AS Érkezik FROM menetrend JOIN kikoto AS honnan ON honnan.id=menetrend.honnan JOIN kikoto AS cel ON cel.id=menetrend.hova WHERE honnan.név="Balatonfüred" AND indul>="12:00" ORDER BY indul LIMIT 1 ;



6

|

Határozzuk meg, hogy legkésőbb hány órakor lehet hajóval visszaérkezni Alsóörsre! A következő lekérdezéssel megkereshetjük a választ: SELECT honnan.név AS Honnan, jarat AS Járat, erkezik AS Érkezik FROM menetrend JOIN kikoto AS honnan ON honnan.id=menetrend.honnan JOIN kikoto AS cel ON cel.id=menetrend.hova WHERE cel.név="Alsóörs" ORDER BY erkezik DESC LIMIT 1; A pontos válaszhoz természetesen a megfelelő mezőt tegyük a projekcióba: SELECT erkezik AS Érkezik FROM menetrend JOIN kikoto AS honnan ON honnan.id=menetrend.honnan JOIN kikoto AS cel ON cel.id=menetrend.hova WHERE cel.név="Alsóörs" ORDER BY erkezik DESC LIMIT 1;



7

|

Képezzük a G2 járat menetrendjét: SELECT honnan.név AS Honnan, cel.név AS Hova, indul AS Indul, erkezik AS Érkezik FROM menetrend JOIN kikoto AS honnan ON honnan.id=menetrend.honnan JOIN kikoto AS cel ON cel.id=menetrend.hova WHERE jarat="G2"; Mi a G2 járat végállomása? Kétféleképpen is megoldjuk: Talán legegyszerűbb megoldásként kiindulhatunk az előző lekérdezésből: SELECT cel.név AS Hova FROM menetrend JOIN kikoto AS honnan ON honnan.id=menetrend.honnan JOIN kikoto AS cel ON cel.id=menetrend.hova WHERE jarat="G2" ORDER BY erkezik DESC LIMIT 1 ; Gondolkodhatunk azonban úgy is, hogy a G2 járathoz tartozó azon szakaszokat keressük, ahol a célállomás nem szerepel a G2 járat szakaszainak kiinduló állomásán: SELECT cel.név AS Hova FROM menetrend JOIN kikoto AS cel ON cel.id=menetrend.hova WHERE jarat="G2" AND hova NOT IN (SELECT honnan FROM menetrend m WHERE m.honnan = menetrend.hova AND m.jarat="G2") ;



8

|

A menetrend elemzése alapján állapítsuk meg, hány hajóra van szükség a menetrend megvalósításához! Megfigyelhetjük, hogy a járatkódok kezdőbetűje jelenthet egyegy konkrét hajót (valószínűleg valóban ez alapján kerültek meghatározásra a járatkódok). Ezen feltételezés alapján számoljuk meg, hány hajója üzemel a jelen feladatban vizsgált hajótársaságnak! SELECT COUNT(DISTINCT SUBSTR(jarat,1,1)) FROM menetrend A megoldásban a járatkódok első betűi közül a különbözőeket számoltuk össze. Tárolni szeretnénk az egyes hajók nevét. Tervezzük meg, hova érdemes tenni. Fogalmazzuk meg, miért jó, vagy miért nem jó megoldás a hajóneveket a menetrend vagy a kikoto táblában tárolni, esetleg miért jó vagy nem jó megoldás egy új tábla létrehozása e célból. Egy lehetséges jó megoldás: CREATE TABLE dbNEPTUN.hajo (id CHAR(1), név VARCHAR(30)); Táblát természetesen csak a dbNEPTUN nevű saját adatbázisunkban tudunk létrehozni. A továbbiakban tehát két adatbázis adataiból kell dolgoznunk, így mindig ki kell majd írni a táblák elé az



9

|

éppen nem aktuális adatbázis nevét is. A lenti példákban mi a dbGyak adatbázist használjuk alapértelmezetten, így a dbNEPTUN táblahivatkozásait fogjuk adatbázisnévvel írni. A fentiekben meghatároztuk, hány hajóra van szükségünk. Ha a hajo táblát a fentiekhez hasonló módon hoztuk létre, akkor a szükséges kódokat érdemes egy lekérdezéssel beletölteni a táblába: INSERT INTO dbNEPTUN.hajo ( SELECT DISTINCT SUBSTR(jarat,1,1), NULL FROM menetrend ); Ezzel a szükséges kódokhoz tartozó rekordok létrejöttek, de az egyes hajók neveinek betöltését kénytelenek vagyunk kézzel elvégezni, például: UPDATE dbNEPTUN.hajo SET név="Tornádó" WHERE id="A"; Készítsünk olyan lekérdezést, ami a teljes menetrendet az egyes hajók szerint megmutatja. A lekérdezés elkészítéséhez a következő lekérdezés adja az alapot: SELECT * FROM menetrend JOIN dbNEPTUN.hajo ON dbNEPTUN.hajo.id=SUBSTR(menetrend.jarat,1,1) Bővítsük ki a lekérdezést az egyes állomások neveivel a korábbiak alapján. Végeredményként valami hasonló lekérdezést kapunk: SELECT hajo.név Hajó, honnan.név Honnan, hova.név Hova, indul Indul , erkezik AS Érkezik FROM menetrend JOIN dbNEPTUN.hajo ON hajo.id=SUBSTR(menetrend.jarat,1,1) JOIN kikoto honnan ON honnan.id=menetrend.honnan JOIN kikoto hova ON hova.id=menetrend.hova ORDER BY Hajó, indul Szűkítsük a menetrendet a Tornádó nevű hajó mozgására: SELECT hajo.név Hajó, honnan.név Honnan, hova.név Hova, indul Indul , erkezik AS Érkezik FROM menetrend JOIN dbNEPTUN.hajo ON hajo.id=SUBSTR(menetrend.jarat,1,1) JOIN kikoto honnan ON honnan.id=menetrend.honnan JOIN kikoto hova ON hova.id=menetrend.hova WHERE hajo.név = "Tornádó" ORDER BY Hajó, indul



10

|

Készítsünk a Tornádó hajó menetrendjéről egy tornado nevű nézetet! CREATE VIEW dbNEPTUN.tornado AS SELECT honnan.név Honnan, hova.név Hova, indul Indul , erkezik AS Érkezik FROM menetrend JOIN dbNEPTUN.hajo ON hajo.id=SUBSTR(menetrend.jarat,1,1) JOIN kikoto honnan ON honnan.id=menetrend.honnan JOIN kikoto hova ON hova.id=menetrend.hova WHERE hajo.név = "Tornádó" ORDER BY indul



11

|

Készítsünk listát az egyes kikötőkről, hogy melyeknek a legnagyobb a hajóforgalma! A forgalom szerint rendezzük sorba a kikötőket. A megoldásnál felhasználhatjuk azt a tényt, hogy ugyanannyi hajó érkezik be a kikötőbe, mint ahány elindul onnan, tehát elegendő például csak az indulásokat figyelni: SELECT honnan.név Honnan, COUNT(*) "Induló járat" FROM menetrend JOIN kikoto honnan ON honnan.id=menetrend.honnan GROUP BY honnan ORDER BY COUNT(*) DESC

Hány járat indul naponta Fonyódról? SELECT honnan.név Honnan, COUNT(*) "Induló járat" FROM menetrend JOIN kikoto honnan ON honnan.id=menetrend.honnan AND honnan.név="Fonyód" GROUP BY honnan ORDER BY COUNT(*) DESC;



12

|

Listázzuk ki azon kikötőket, ahonnan naponta több, mint 10 járat indul: SELECT honnan.név Honnan, COUNT(*) "Induló járat" FROM menetrend JOIN kikoto honnan ON honnan.id=menetrend.honnan GROUP BY honnan HAVING COUNT(*)>10 ORDER BY COUNT(*) DESC;

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 gyakorlaton a következőket fogjuk gyakorolni:

Recommend Documents