Informatika 2 - Adatbázisok használata labor
A VIK Wikiből
Ez az oldal a korábbi SCH wikiről lett áthozva.
Ha úgy érzed, hogy bármilyen formázási vagy tartalmi probléma van vele, akkor, kérlek, javíts rajta egy rövid szerkesztéssel!
Ha nem tudod, hogyan indulj el, olvasd el a migrálási útmutatót.
Ha valaki kedvet érez megformázni, nyugodtan...
Pár megjegyzés, amiben ez a nyelv különbözik az eddigiektől:
- itt szimpla egyenlőségjel az egyenlőségvizsgálat, nem dupla, mint a C-ben;
- a sztringeket nem idézőjel ("), hanem aposztróf (') határolja;
- a WHERE-en belüli logikai kifejezésben nem && jelöli az és műveletet, hanem AND, és nem || a vagy, hanem OR;
1. Milyen szintaktikájú SQL utasítással lehet létrehozni egy rekordot egy táblában?
CREATE TABLE létrehozandó_tábla_neve
(
első_oszlop_neve első_oszlopban_lévő_adatok_típusa,
...
)
Példa:
CREATE TABLE tanszekek
(
id int primary key,
shortname varchar(5),
longname varchar(40),
teachers int
)
Ez létrehoz egy táblát a tanszékeknek, amiben mindegyiknek van egy egyedi azonosítószáma (id, ez a tábla elsődleges kulcsa),
tároljuk a legfeljebb 5 karakteres rövid nevét, és a legfeljebb 40 karakteres teljes nevét.
2. Milyen szintaktikájú SQL utasítással lehet módosítani rekordokat?
UPDATE módosítandó_tábla_neve
SET első_módosítandó_oszlop_neve = első_módosítandó_oszlopba_rakandó_érték,
...
WHERE logikai_kifejezés_ami_azokra_a_sorokra_igaz_amiket_módosítani_akarunk
Példa:
UPDATE tanszekek
SET longname = 'Iszonyatos Info2 Tanszék'
WHERE shortname = 'IIT'
Ez módosítja az IIT teljes nevét, már amennyiben szerepelt a táblában. Ha nem szerepelt, nem hozza létre (!).
3. Milyen szintaktikájú SQL utasítással lehet törölni rekordokat?
DELETE FROM tábla_amiből_néhány_sort_törölni_akarunk
WHERE logikai_kifejezés_ami_azokra_a_sorokra_igaz_amiket_törölni_akarunk
vagy:
DELETE tábla_amiből_minden_sort_törölni_akarunk
Példa:
DELETE FROM tanszekek WHERE shortname = 'IIT'
Ez törli a tanszékek közül az IIT-t (bár ilyen egyszerű lenne...).
4. Hogyan lehet vizsgálni egy kifejezés nullitását?
Rossz:
SELECT FROM tanszekek WHERE shortname = null
Ez azért rossz, mert ha valamit null-lal hasonlítunk össze, az összehasonlítás eredménye mindig false, még akkor is, ha
mindkettő null (hasonlít a lebegőpontos számításoknál a NaN-nal való összehasonlításhoz), mert a null az semmivel nem egyenlő
(még önmagával se).
Jó:
SELECT FROM tanszekek WHERE shortname is null
Ezt a külön nyelvi konstrukciót lehet a null-ság vizsgálatára használni.
5. Miképp mőködik a like operátor? Milyen joker karaktereket lehet használni?
Valami reguláris kifejezés-szerű (de nem azonos vele! annak külön szintaxisa van) dolog, ami lehetőséget ad arra, hogy
stringek egy csoportját kijelöljük, hogy megadjunk egy mintát, ami több sztringre is illeszkedhet. Pl. ha egy százalékjel (%)
van benne, az tetszőleges (akár nulla hosszú) karaktersorozatot jelenthet, az aláhúzásjel egy akármilyen karaktert jelenthet,
a dupla aposztróf pedig egy szimpla aposztrófot jelent, és csak azért kell, mert egy szimpla aposztrófot leírva vége lenne a
sztringnek.
Példa:
SELECT FROM tanszekek WHERE longname like '%barack_''alma'
Ez azokat a tanszékeket fogja visszaadni, amiknek a neve tartalmazza a barack karaktersorozatot, utána valamilyen karakter
jön, utána egy idézőjel, és végül 'alma' a vége (nincs % a végén, ezért nem lehet az 'alma' után már semmi).
6. Hogyan működnek az oszlopfüggvények?
Előfordul, hogy nem a konkrét értékek érdekelnek, amik egy táblából kinyerhetőek, hanem pl. azok átlaga, vagy csak az, hogy
hány, a feltételnek megfelelő sor volt a táblában (pl. bejelentkezés: csak az az érdekes, hogy volt-e megadott nevet és
jelszót tartalmazó felhasználó a táblában, vagy nem). Ezeket a dolgokat el lehetne úgy is végezni a legtöbb esetben, hogy
lekérjük az összes lényeges sort a táblából, majd az adatbázison kívül kiszámítjuk, ami érdekel. Viszont ez azért nem a
legjobb, mert általában sok fölösleges pluszmunka ezt a rengeteg adatot az adatbázis és a külső feldolgozó közt átvinni - pl.
az átlagszámításhoz a legtöbb nyelvben külön ciklus kéne, lefoglalni az adatoknak sok memóriát, stb. Ezért a legtöbb
adatbázisengine képes pár ilyen dolgot maga is elvégezni. Pl. ha szeretnénk a tanárok átlagos számát kiszámítani azon
tanszékek közt, amiknek a nevében van 'SZ' (EVTSZ, SZIT, stb.), elküldhetjük a következő lekérést:
SELECT teachers FROM tanszekek WHERE longname like '%SZ%'
amivel lekérjük a megfelelő tanszékeken dolgozó tanárok számát, majd az adatbáziskezelőn kívül végigfuthatunk a kapott
sorokon, és kiszámíthatjuk az átlagot, de ez nem valami hatékony. Helyette az alábbi lekéréssel megmondhatjuk az engine-nek,
hogy ne a tanárok számát adja vissza, minden sorból egyet, hanem csak az összes sorban lévő teacher mező átlagát kérjük, és
nevezze ezt mondjuk average_teachers-nek:
SELECT avg(teachers) as average_teachers FROM tanszekek WHERE longnamelike '%SZ%'
Így az engine-től egyetlen sort kapunk majd vissza, és annak egyetlen, average_teachers mezője lesz, pont a kívánt értékkel.
A többi oszlopfüggvény, értelemszerű funkcióval, hasonló használattal és működéssel: max, min, sum, count.
7. Mire szolgál a having kulcsszó?
Előfordul, hogy két táblából van szükségünk egyszerre adatra, és a két tábla közt valamiféle kapcsolat van. Pl. az elsőben
cégek adatai vannak, a másodikban pedig dolgozók adatai, és a második tábla egyik mezője azt tartalmazza, hogy melyik cégnél
dolgozik az adott ember (méghozzá a hellyel való spórolás miatt csak a cég azonosítószámát, nincs benne a teljes cégnév).
Tegyük fel, hogy statisztikai célokra szükségünk lenne az összes olyan cég nevére, ahol dolgozik Smith nevű alkalmazott. Ezt
megoldhatjuk úgy, hogy először lekérjük az összes Smith vezetéknevű emberhez tartozó sort a dolgozók táblából, majd ezekből
mind kivesszük a cégazonosítót, és egy ciklussal mindegyikhez lekérjük a hozzá tartozó nevet az első táblából. Ez, az előző
pontban leírtakhoz hasonló okok miatt nem hatékony. Helyette erre is van egy eszköz magában az engine-ben, és ez a JOIN.
Megtehetjük, hogy egy ilyen lekérést intézünk az engine-hez:
SELECT DISTINCT ceg_neve FROM cegek JOIN dolgozok ON dolgozok.ceg_azonosito = cegek.ceg_azonosito WHERE dolgozok.nev LIKE
'%Smith%'
Ez arra utasítja az engine-t, hogy fogja meg mindkét táblát, és kapcsolja össze őket. Konkrétan a két tábla
Descartes-szorzatát hozza létre; ez az összes lehetséges pár halmaza. Vagyis, ha az első táblában volt 5 elem, a másodikban
pedig 10, akkor a Descartes-szorzatnak 50 eleme lesz, lesz egy olyan, ahol a dolgozok első sora van párban a cegek első
sorával, egy olyan, ahol a dolgozok első sora van párban a cegek második sorával, stb. Ezek közül aztán kiválasztja azokat,
amikre az ON utáni kifejezés igaz, vagyis csak azokkal a cég-dolgó párosokkal dolgozik tovább, ahol a cég alkalmazottja a
dolgozó. Ezekből aztán kiválasztja azokat, ahol a dolgozó neve tartalmazza a 'Smith' szöveget, végül a maradék sorokból csak
a cégnevet szedi ki, azok közül is kiszórja azokat, amik kétszer vannak (DISTINCT ceg_neve), és végül a kívánt listával térr
vissza.
Ez egészen addig jó is, amíg a kiválasztási feltétel csak egyes sorokkal dolgozik, de ha pl. azok a cégek kellenének, ahol az
összes dolgozó átlagfizetése 200 ezer fölött van, azt nem lenne ilyen egyszerű. Ugyanis, használva az oszlopfüggvényeket, le
akarhatnánk írni valami ilyesmit:
Rossz:
SELECT ceg_neve FROM cegek JOIN dolgozok ON dolgozok.ceg_azonosito = cegek.ceg_azonosito WHERE avg(dolgozok.fizetes)>200000
Ez viszont nem működne, mert az avg() oszlopfüggvény csak a már elvégzett lekérésekből számít ki valamit, amíg még a
feltételek kiértékelése és a sorok kiválogatása zajlik, addig nem is létezik, és nem használható. Ezért ezt a feltételt nem a
WHERE után kell írni, hanem van egy külön kulcsszó, a HAVING:
Jó:
SELECT ceg_neve FROM cegek JOIN dolgozok ON dolgozok.ceg_azonosito = cegek.ceg_azonosito HAVING avg(dolgozok.fizetes)>200000
És ez már a kívánt dolgot csinálja.
Megjegyzés: bizonyos esetekben a JOIN-t nem kell kiírni, és a lekérés mégis azt csinálja, mintha írtunk volna bele (implicit
JOIN).
8. Írja fel a select utasítás általános szintaktikáját!
A szögletes zárójelbeliek nem kötelezőek:
SELECT [DISTINCT] oszlopok_amiknek_az_értéke_kell
FROM táblák_amikből_adat_kell
[where logikai_kifejezés_ami_igaz_a_sorokra_amiket_ki_akarunk_választani]
[group by mi_szerint_akarjuk_csoportosítani_a_visszaadott_sorokat]
[having logikai_kifejezés_ami_JOINnál_használatos]
[order by mi_szerint_akarjuk_rendezni_a_visszaadott_sorokat]
9. Mire szolgál az isnull függvény MS SQL Serveren?
Arra jó, hogy ilyen "elromlás-biztos" módon szedjem le egy változó értékét: sokszor van olyan, hogy egy változó (táblamező,
számított kifejezés) lehet, hogy null, és ha null, akkor helyette valami mással (pl. "üres mező" stringgel) akarok dolgozni.
Ezt az isnull(p1, p2) függvény teszi lehetővé: ha p1 nem null, akkor annak az értékét adja vissza, de ha null, akkor p2
értékét, ami lehet pl. ilyen helyettesítő érték, hibaüzenet, stb.
10. Miképp lehet megadni két tábla outer joinját MS SQL Serveren?
A LEFT OUTER JOIN abban tér el a már leírt INNER JOIN-tól, hogy az INNER JOIN, ha mondjuk az első táblának volt olyan sora,
ami semmilyen módon nem került be a kiválasztott sorok közé (vagyis nem volt olyan második táblabeli sor, amivel
összepárosítva az ON-beli kifejezés igazzá vált volna), akkor egyszerűen nem kerül be. Itt viszont bekerül, és a hozzá
tartozó második táblabeli oszlopok értékei mind NULL-ok lesznek. A RIGHT OUTER JOIN ugyanez, csak a két tábla szerepe
felcserélve. A szintaktika pedig:
SELECT gyarto.*
FROM gyarto left OUTER JOIN termek ON gyartoid=gyarto.id
-- G - 2008.03.25.
- Ezen a helyen volt linkelve a(z) SQL20nyelv.pdf nevű fájl ("SQL20nyelv.pdf" link szöveggel) a régi wiki http://wiki-old.sch.bme.hu/bin/view/Villanyalap/LaborBeugro20080326 oldaláról. (Ha szükséged lenne a fájlra, akkor a pontos oldalmegnevezéssel együtt küldd el a wiki
sch.bme.hu címre a kérésedet)
- A beugró maga
Itt van egy tömörebb változat, amit én csináltam, tényleg csak a kérdésre a válasz, semmi plusz rizsa. Az első kérdésre a válasz az enyémben jobb szerintem, de az utolsóban nagyon nem vagyok biztos. -szerkesztve: 10-es kérdést kaptam, és megkaptam rá a max pontot. - 2009.03.23. Szóval itt van: 1. Milyen szintaktikájú SQL utasítással lehet létrehozni egy rekordot egy táblában? a, insert into táblanév (oszlopok listája) values (értékek listája) b, insert into táblanév (oszlopok listája) select... oszlopok listája elhagyható, select rész lekérdezést csinál, a visszaadott sorok kerülnek be 2. Milyen szintaktikájú SQL utasítással lehet módosítani rekordokat? update táblanév set oszlopnév1=érték1, oszlopnév2=érték2,… where logikai feltétel //where elhagyható 3. Milyen szintaktikájú SQL utasítással lehet törölni rekordokat? delete from táblanév where logikai felétel vagy delete táblanév <- így minden sort törlünk a táblából 4. Hogyan lehet vizsgálni egy kifejezés nullitását? kifejezés is null <- csak így lehet, a kifejezés = null értéke akkor sem lesz igaz, ha a kifejezés értéke egyébként null, mivel ha valamit null-al hasonlítunk össze, az mindig false 5. Miképp működik a like operátor? Milyen joker karaktereket lehet használni? kifejezés like string minta String összehasonlító operátor, a kifejezésben keres a mintának megfelelően, és ha a minta illeszthető a kifejezésre, akkor az operátor igaz értékkel tér vissza. A mintaillesztés case sensitive, a használható joker karakterek: _: egy betű helyettesítése %: tetszőleges hosszúságú szöveg helyettesítése dupla aposztróf: szimpla aposztróf, mivel, a szimpla aposztróf jel önmagában a stringhatároló karakter pl.: select * from termek where nev like ’%er%’ azon termékek listája, amelyek neve tartalmazza az er karaktersorozatot 6. Hogyan működnek az oszlopfüggvények? A lekérdezés eredményeként kapott rekordhalmazon lehet utólagos kalkulációt végezni velük: sum: értékek összegzése min: legkisebb érték megkeresése max: legnagyobb érték avg: átlag számítása count: rekordok megszámolása A kiszámítandó oszlopfüggvényeket a select listában kell megadni. Az adatbázis-kezelő szerver először lefuttatja a lekérdezést az oszlopfüggvények nélkül, majd az egyes rekordokat átadja az oszlopfüggvényeknek, az értékek kiszámítására. Az oszlopfüggvények a null értéket nem veszik figyelembe, kivéve a count(*) függvény. 7. Mire szolgál a having kulcsszó? Ha az oszlopfüggvény eredményére teszünk megkötést, akkor ezt a where részben nem adhatjuk meg, mivel az kiértékelődik még az oszlopfüggvények kiszámítása előtt. Ezért ha az oszlopfüggvények kimenetére szeretnénk szűrni, akkor azt külön a having kulcsszó után lehet megadni. 8. Írja fel a select utasítás általános szintaktikáját! select [discint] oszloplista from táblalista [where logikai kifejezés] [group by oszloplista] [having logikai kifejezés] [order by oszloplista] 9. Mire szolgál az isnull függvény MS SQL Serveren? isnull(kifejezés1,kifejezés2) Ha a kifejezés1 értéke nem null, akkor a kifejezés1-et adja vissza, ha null, akkor a kifejezés2-t. Néha van olyan, hogy egy változó értéke lehet, hogy null, és ha null, akkor lehet, hogy helyette valami mással szeretnék dolgozni. 10. Miképp lehet megadni két tábla outer joinját MS SQL Serveren? A join művelet tulajdonsága, hogy a nem párosítható rekordokat nem tartalmazza az eredményhalmaz. A left outer join abban tér el ettől, hogy ha mondjuk az első táblának volt olyan sora, ami semmilyen módon nem került be a kiválasztott sorok közé, akkor nem kerül be, itt viszont bekerül, és a hozzá tartozó második táblabeli oszlopok értékei mind null-ok lesznek. A right outer join ugyanez, csak a két tábla szerepe felcserélve. select * from termek right outer join gyarto on gyartoid=gyarto.id select * from gyarto left outer join termek on gyartoid=gyarto.id -- Main.meilinger - Meilinger Ákos - 2009.03.22.
