Tábla létrehozása - create
A CREATE TABLE kulcsszóval lehet létrehozni egy táblát. Ez után zárójelben fel kell sorolni azokat az elemeket, amik a táblázat oszlopai lesznek, ezt mindig az oszlopnév majd a típus meghatározásával történik. Ezután jöhetnek az egyéb speciális parancsszavak.
create kulcsszó bevezeti a tábla létrehozását
null/not null jelentése, hogy az adott elemnek kötelező-e értéket adni. Ha null akkor nem, ha not null akkor kötelező értéket adni
default kulcsszóval megadhatunk egy alapértelmezett értéket, így ha később nem adjuk meg a mező értékét, akkor ennek a helyére beíródik az itt megadott érték
Ha adatbázist akarunk létrehozni, akkor a CREATE DATABASE adatbázis_nev formába kell megadni.
CREATE TABLE elso (
azonosito INT NOT NULL default 0,
nev VARCHAR(75) NOT NULL,
szul_ev DATE NOT NULL,
szem_ig_szam CHAR(8) NOT NULL,
PRIMARY KEY (azonosito)
)
CREATE DATABASE elso_adatbazis
Tábla törlése - drop
A DROP TABLE paranccsal vezetjük be a tábla törlését. Ez után következik a tábla neve amit törölni szeretnénk. A parancs lefuttatásával töröljük a tábla összes sorát és magát a tábla szerkezetét is.
Ha az adatbázist akarom törölni, akkor az a DROP DATABASE parancsszót kell használni. Ha ezt lefuttatjuk, akkor törli az összes táblát és az adatbázis szerkezetét is.
DROP TABLE elso
DROP DATABASE elso_adatbazis
Új sor hozzáadása - insert into
Az INSERT INTO parancsszóval vezetjük be az új sor hozzáadását. Ezután jön a tábla név, majd a VALUES kulcsszó. Ezután a zárójelek között az oszlopneveknek megfelelően sorrendbe bele kell írni aposztrófok közé az értékeket.
Ha nem tudjuk, hogy milyen sorrendbe vannak a tábla oszlopai, akkor a tábla név és a VALUES között zárójelben megadhatunk egy tetszőleges oszlopnév sorrendet, de ekkor ebben a sorrendben kell megadni a paramétereket a VALUES kulcsszó után.
Azt is megtehetjük, hogy nem az összes oszlopnak adunk értéket. Ekkor, ha a tábla létrehozásánál megadtunk DEFAULT értéket, akkor ez az érték íródik be a táblába. Ha nincs ilyen megadva, akkor null értéket vesz fel.
INSERT INTO elso
VALUES ('1', 'petike', '1987', '457887DH')
INSERT INTO elso (nev, ev, szem_ig_szam, azonosito)
VALUES ('petike', '1987', '457887DH', '1')
INSERT INTO elso (nev, ev, szem_ig_szam, azonosito)
VALUES ('petike', '1987', '457887DH', '1')
INSERT INTO elso (nev, ev, szem_ig_szam)
VALUES ('petike', '1987', '457887DH')
Módosítás - update
Az UPDATE kulcsszóval vezetjük be a módosítást, ezután következeik a tábla neve, amelyben a módosítást el akarjuk végezni. Ezután a SET kulcsszóval vezetjük be a módosítandó oszlopok felsorolását, ez a következő formában történik oszlop_nev='érték'. Ha több adatot akarunk egyszerre módosítani, akkor az érték és a következő oszlopnév közé egy vesszőt kell raknunk. Az utolsó oszlopnév érték pár után nem kell vesszőt raknunk.
Ügyeljünk arra, hogyha nem kerül a módosítás végére where feltétel, akkor a tábla összes sorában végbemegy a változtatás. Tehát ha azt akarjuk, hogy ez ne történjen meg, akkor írni kell egy where kulcsszót és egy relációs feltételt.
UPDATE elso SET
nev='Péter',
azonosito ='38',
ev='1977'
WHERE ev='1987'
Törlés - delete from
A DELETE kulcsszóval vezetjük be a törlést. Ez a törlés nem egyezik meg a DROP paranccsal, ezzel ugyanis csak bizonyos sorokat lehet törölni, de a tábla szerkezetét nem.
A DELETE FROM kulcsszavak után jön a tábla neve, amelyből sorokat akarunk törölni. Utána kötelező a where feltétel, mert ha elhagyjuk, a tábla összes sorát töröljük (tehát szintaktikailag elfogadja az adatbázis-kezelő rendszer). Ezért mindig óvatosan használjuk ezt a parancsot!
DELETE FROM elso WHERE ev ='1987'
Az azonosító
Miért fontos az, hogy minden táblában legyen egy azonosító?
Egy táblában fontos, hogy minden sort egyedileg meg tudjunk különböztetni, és tudjunk rá hivatkozni is. Első pillanatra furcsállhatjuk, hogy minek kell emiatt felvenni még egy oszlopot, hiszen ott van a név és majd amikor megadjuk a feltételt akkor a névvel szűkítjük, hogy mire vonatkozzon a módosítás, törlés stb...
Ez jól is fog működni mindaddig amíg nem veszünk fel két ugyanolyan nevű embert (Tóth, Szabó, Kiss vagy éppen Nagy vezetéknevű emberből sok van és az is elég gyakori, hogy a keresztnevük is ugyan az). Persze mondhatnánk, hogy felvesszük hozzá a születési dátumát ekkor viszont már a feltétel bonyolult lesz : where nev = 'Kiss János' and szuldatum='1901.12.24' . Ugyanakkor még ez sem garancia, hogy nem lesznek olyan emberek akiknek ugyanazok az adataik. Ebből látható, hogy szükséges minden sorhoz hozzárendelni egy egyedi azonosítót, ami csak ahhoz az egy emberhez tartozik. Ezt az azonosítót az informatikába ID-nak mondjuk (az ID az angol identifier szóból származik ami azonosítót jelent). Így minden táblánkban szerepelni fog egy ID oszlop.
Felmerülhet a kérdés, mi van akkor, ha két ugyanolyan ID-t adunk meg. Ekkor gondunk lenne, mert egy törlés parancs kiadásakor mind a két rekordot törölnénk. Ennek elkerülésére, amikor létrehozzunk a táblát jelezzük az adatbázis-kezelő rendszernek, hogy ennek az oszlopnak mindig egyedinek kell lennie. Ezt a primary key(oszlopnev) paranccsal tehetjük meg. A primary key elsődleges kulcsot jelent. Az adatbázisoknál kulcsnak mondjuk azokat az oszlopokat, amik egyértelműen meghatározzák, hogy a táblában melyik sorról van szó. Ekkor, ha két ugyanolyan elemet szeretnénk felvinni a táblában, akkor a másodikat nem fogja engedélyezni, és hibaüzenetet kapunk.
Honnan tudjuk, hogy mi legyen a következő kulcsunk? Erre is van egy megoldás az adatbázis-kezelő rendszerben. Minden sorhoz, hozzárendelünk egy számot, és folyamatosan növeljük a szám értékét. Tehát amikor a következő sort visszük fel akkor az előzőhöz képest eggyel nagyobb számot adunk meg.
Ezt el is végezheti az adatbázis-kezelő rendszer helyettünk, ha megadjuk, hogy automatikusan növelje ezt a számot. Erre a funkcióra szolgál az auto_increment parancs. Például a create parancsnál a következőképpen is megadhatjuk: `ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY. Az ID az oszlopnév, a not null logikusan azt jelenti, hogy nem lehet üres (hiszen mindegyiknek különbözőnek kell lennie), és végül, hogy automatikusan növekedjen az elsődleges kulcsunk.
Előfordulhat olyan is, hogy szeretnék, ha az adatbázis-kezelő figyelné, hogy az adott oszlopba ne kerüljön két ugyanolyan érték, de nem ez a kulcsunk (pl.: két ugyanolyan tajkártya szám ne legyen a táblába). Ezt az unique(oszlopnev) paranccsal adhatom meg (az angol unique szó jelentése egyedi).
Szerkezet módosítás - alter table
Az ALTER TABLE kulcsszó vezeti be a tábla szerkezetének módosítását, ezután következik a táblanév, amelyen a módosítást kívánjuk végezni.
Utána jön, az ADD vagy a DROP kulcsszó, azaz vagy hozzáadunk a tábla szerkezetéhez egy oszlopot vagy törlünk belőle egyet. Ezután következik az oszlop neve. Ha hozzáadunk, akkor itt történik a típus meghatározása is, úgy mint a create parancsnál. Törléskor csak a törlendő oszlop nevét kell beírnunk.
Ha nem a tábla utolsó oszlopa után szeretnénk beszúrni az új oszlopot, akkor az AFTER kulcsszót kell használnunk, ezt után jön az az oszlopnév, ami után szeretnénk az új oszlopot. Ilyen formába elérjük el, hogy a megadott oszlop után kerüljön beszúrásra az új oszlopunkat.
ALTER TABLE elso ADD uj_oszlop VARCHAR( 255 ) NOT NULL
ALTER TABLE elso ADD uj_oszlop VARCHAR( 255 ) NOT NULL AFTER ev
ALTER TABLE elso DROP torlendo_oszlop
Lekérdezés - select
Eddig olyan parancsszavakat néztünk, ami az adatbázisunk szerkezetét, illetve az adatokat módosította, de nem volt visszatérési értéke. Ez azt jelenti, hogy az eddig megismert függvények lefutásakor elvégződik a művelet, de a felhasználó, azaz mi nem tudjuk meg, hogy mi is történt. Ezért amikor kiadunk egy ilyen függvényt utána egy lekérdezéssel ellenőrizzük, hogy mi történ az táblánkban.
Ahhoz, hogy kapjunk vissza adatokat, meg kell ismerkednünk még egy paranccsal, ez a SELECT. Ennek mindig van visszatérési értéke, de előfordulna olyan eset is, amikor nincsen benne érték. Ez például akkor fordulhat elő, ha az előző utasítással töröltünk, és ezután a select utasítással meggyőződünk arról, hogy tényleg töröltük-e az az adott sor. Ekkor értelemszerűen nem lehet visszatérés érték, mert az előző paranccsal töröltük azt.
Lekérdezés - select *
A SELECT kulcsszóval vezetjük be a lekérdezést. Ezután következik egy felsorolás azokkal az oszlopnevekkel, amelyeknek az értékeit látni szeretnénk. Ha az összes oszloppal akarunk dolgozni, akkor elegendő a * karakter megadása, ekkor az összes oszlopot visszaadja.
Ezután következik a FROM tabla_nev páros, azaz az itt megadott táblából kérdezünk le adatokat. Így elérhetjük, hogy a táblánk összes sorát visszakapjuk.
SELECT * FROM elso
SELECT nev, ev FROM elso
Szűrés - where
Mit tehetünk akkor, hogyha nem akarjuk, hogy a tábla összes sorát visszakapjuk? Ilyenkor szűrést kell alkalmaznunk, amelyben megmondjuk, hogy melyik sorokat szeretnénk visszakapni. A feltétel kulcsszava a WHERE. Ezután következhetnek a feltételek. Mivel relációs adatbázis-kezelő rendszerrel dolgozunk ezért, értelemszerűen a feltételek is relációkkal kell megadni.
Így a feltétel lehet egyenlő ( = ), nem egyenlő ( != ), kisebb ( < ), nagyobb ( > ), kisebb egyenlő ( <= ), vagy nagyobb egyenlő ( => ).
Ha több feltételt adunk meg, akkor az AND (és kapcsolat) és az OR (vagy kapcsolat) kulcsszóval köthetünk össze azokat. A feltételek tetszőlegesen zárójelezhetőek.
Tehát a WHERE kulcsszó utáni részt mindenegyes sornál kiértékeli, ha a feltételek mindegyike megfelel az adott sorban, akkor azt a sort hozzáfűzi a visszatérési értékhez. Így csak azokat és csak azokat a sorokat kapjuk vissza, amelyek teljes mértékben megfelelnek a feltételnek.
SELECT * FROM elso WHERE ev>1950
SELECT nev, ev FROM elso WHERE ev>1950 and azonosito<100 or nev='péter'
Rendezés - order by
Az eredményt tetszőlegesen rendezhetjük is. Így nem okozhat gondot, ha egy névsort abc sorrendbe kell állítani. A rendezés kulcsszava az ORDER BY. Ez a WHERE feltétel után következik, természetesen, ha nincs WHERE feltételünk akkor a táblanév után helyezzük el ezt.
Az ORDER BY után következik egy oszlop_nev, e szerint az oszlop szerint rendeződik az eredmény. Meg lehet adni egymás után több oszlopnevet is, ilyenkor ha az első oszlopban van több ugyanolyan adat, akkor a sorrend megállapításakor a következő oszlop értékeit fogják eldönteni, hogy mi a sorrend.
Ha csökkenő sorrendet akarunk elérni, akkor az oszlopnevek után a DESC kulcsszó kerül.
SELECT * FROM elso
WHERE ev>1950
ORDER BY ev
SELECT nev, ev FROM elso
WHERE ev>1950 and azonosito<100 or nev='péter'
ORDER BY nev, azonosito DESC
Csoportosítás - group by
Ha csoportosítani kell, akkor a GROUP BY parancsszót kell alkalmaznunk. Ezután következik az az oszlop név, ami alapján csoportosítani szeretnénk. Ilyenkor ügyelni kell arra, hogy a SELECT utáni részbe csak olyan oszlopnév illetve függvényt írjunk, aminek a végrehajtásakor a végeredménye egy darab érték lesz (pl.: számoljuk meg, hányan születtek minden évben 1950 után. Ekkor a count(ev)) megszámoljuk az adott évben született emberek számát, látjuk, hogy ez minden egyes évre egy darab értéket ad vissza.
Az AS paranccsal el(át)nevezzünk egy oszlopnevet, jelen esetben a kiszámolt érték oszlopneve a darab lesz.
SELECT ev,
COUNT(ev) AS darab
FROM elso
WHERE ev>1950
GROUP BY ev
Lekérdezés - függvények
Most nézzük át azokat a funkciókat, amelyeket gyakran használjunk amikor különböző statisztikai, illetve egyéb adatokat szeretnénk megkapni.
Két csoportra oszthatjuk a függvényeket, attól függően, hogy milyen környezetbe alkalmazzuk azokat.
Amelyek egy adott adatelemen végrehajthatóak.
Amelyek a csoportosításkor használhatóak (group by).
Szöveg kezelő függvények
Szinte minden feladat során előfordul, hogy egy szöveg tartalommal kell műveleteket végeznünk. A leggyakrabban használt függvényeket nézzük meg:
SUBSTRING(szoveg, honnan, mennyit) Kimásol egy szöveg részletet a honnan indextől, 'mennyit' karakter darabot.
SUBSTRING(szoveg, honnan) Kimásolja az adott ponttól a szöveget egészen a végéig.
CONCAT(szoveg1, szoveg2, szoveg3, ...) Összefűz tetszőleges számú szövegdarabot.
CONCAT_WS(speciális elem, szoveg1, szoveg2, ...) Összefűz tetszőleges számú szövegdarabot, és az elemek közé a speciális elemet illeszti.
INSERT(szoveg, honnan, mennyit, mivel) A szöveg amibe felülírok a 'mivel' szöveggel a honnan pozíciótól, 'mennyit' karakteren keresztül.
LENGTH(szoveg) A szöveg karaktereinek a számát adja vissza.
LOWER(szoveg) A szöveget kisbetűssé alakítja.
UPPER(szoveg) A szöveget nagybetűssé alakítja.
STRCMP(szoveg1, szoveg2) Ha a két szöveg ugyanaz akkor 0-t ad vissza eredményként (különben -1 vagy 1-et attól függően, hogy melyik a nagyobb).
LOCATE(szovegdarab, szoveg) Visszaad egy számot, ami az első előfordulási helye a szövegdarabnak a szövegben.
TRIM(szoveg) Visszatér a szöveggel, de az elejéről és a végéről eltávolítja a szóközöket.
SELECT substring(nev,2,5) FROM elso
SELECT substring(nev,
locate(' ',nev)
) FROM elso
SELECT concat(nev,nev) FROM elso
SELECT concat_ws(', ', nev,nev) FROM elso
SELECT length(nev) FROM elso
SELECT upper(nev) FROM elso
SELECT lower(nev) FROM elso
SELECT trim(nev) FROM elso
Feltétel használata
Feltételt akkor használunk, hogyha egy adott érék megvizsgálása után nem csak egy irányban szeretnénk tovább haladni. Az SQL nyelvben is megvan ez a lehetőség a lekérdezések során azaz feltételeket a select parancs után kell írni. Kétféle feltétel szerkezetet különböztetünk meg a kétirányút és a többirányú elágazást.
IF (feltétel, ha igaz , ha nem igaz). Az if szóval vezetjük be, a feltételnek olyannak kell lenni ami igaz vagy hamis értéket ad vissza, és eszerint, hajtja végre az első vessző utáni rész, illetve ha nem igaz akkor a második vesző utáni részt. Vegyük észre, hogy ide nem csak egy érték kerülhet, hanem például a hamis részre tehetünk egy újabb if szerkezetet: IF(feltétel, igaz,IF(feltétel2, igaz, hamis)).
Ha több felé akarunk elágazni, akkor a case szerkezetet kell használnunk. A következő a szintaktikája: CASE WHEN [feltétel] THEN [valami] WHEN [feltétel2] THEN [valami2] ELSE [valami a maradékokra] END. Azt is megtehetjük, hogy az ELSE részt nem használjuk fel, de ekkor is le kell zárni az END paranccsal!
SELECT
IF( SUBSTRING(szem_ig_szam,1,1) = 1,'férfi','nő') AS neme FROM elso
SELECT CASE
WHEN SUBSTRING(datum,1,4)>1980 THEN 'fiatal'
WHEN SUBSTRING(datum,1,4)>1965 THEN 'közékorú'
WHEN SUBSTRING(datum,1,4)>1950 THEN 'öregedő'
ELSE 'öreg'
END AS állapota FROM elso
Matematikai függvények
Sokszor kell matematikai függvényeket is használni a szokásos +,- , *, / műveleteken kívül.
COS(X) A szám koszinuszát adja vissza.
SIN(X) A szám szinuszát adja vissza.
TAN(X) A szám tangensét adja vissza.
ABS(X) A szám abszolútértékét adja vissza.
POW(X,Y), POWER(X,Y) A szám hatványozását végzi el: xy.
RAND() Egy véletlen számot generál 0 és 1 között.
ROUND(X) Egy számot kerekít egésszé.
SQRT(X) A szám négyzetgyökét adja vissza.
TRUNCATE(X,D) A számot csonkolja. Ha 0 akkor a tizedespont utáni részt törli, ha D>0 akkor annyi tizedes értéket hagy meg amekkora a szám. Negatív is lehet a D ekkor pl.: -2 esetén a 128-ból 100-at készít.
SELECT COS(15), SIN(15), TAN(15)
SELECT ABS(-15)
SELECT POW(2,3)
SELECT RAND()
SELECT ROUND(15.6)
SELECT SQRT(16)
SELECT TRUNCATE(15.6,0)
Dátum függvények
A legtöbb problémát a dátumokkal kapcsolatban tapasztaljuk, nem mindegy hogy milyen a default formátum, és az sem triviális, hogy hogyan kell felvinni egy dátumot egy insert into segítségével.
CURDATE(), CURTIME() Visszaadja a mai nap dátumát, illetve a mostani pontos időt.
DATE_FORMAT(datum,forma) A formázókarakterek segítségével állíthatjuk elő a dátum formátumát. Nézzük meg a leggyakoribb formázó karaktereket:
év: %Y [XXXX], %y [XX],
hónap: %b [jan..dec], %c [1..12], %m [01..12] ,
nap %d [01.31], %e [1..31], %j [1..366], %W [hétfő..vasárnap],
óra: %h [01..12], %H [00..23], %k [0..23],
perc: %i [00..59],
másodperc: %s [00..59]
A hónap illetve a hét nevei az adatbázis kezelő rendszer nyelvétől függően jelenik meg.
DATE(datumidő) A dátumidő dátum részével tér vissza.
TIME(dátumidő) A dátumidő idő részével tér vissza.
MONTHNAME(datum) / DAYNAME(datum) A dátum hónap / nap nevét adja meg.
LAST_DAY(datum) Vissza adja az aktuális hónap utolsó napját (évszám hónap nap formában).
MAKEDATE(év,napokszáma) Kiszámolja a dátumot, az aktuális évszámhoz hozzáadja a megadott napok számát.
NOW() Visszaadja a mostani dátumot és a mostani időt.
YEAR(dátumidő) / MONTH(dátumidő) / DAY(dátumidő) / HOUR(dátumidő) / MINUTE(dátumidő) / SECOND(dátumidő) Visszadja az évet / hónapot / napot / órát / percet / másodpercet
SELECT curdate(), curtime()
SELECT date_format(datum, '%y %m %d %H %m %s') FROM elso
SELECT date(datum) FROM elso
SELECT time(datum) FROM elso
SELECT monthname(datum) FROM elso
SELECT last_day(datum) FROM elso
SELECT makedate('2004', 65)
SELECT now()
SELECT year(datum) FROM elso
Group by függvények
A group by függvényekhez a következő függvények tartozhatnak:
avg(oszlopnev) átlag függvény.
count(1) visszaadja, hogy hány darab.
max(oszlopnev) a maximum értéket választja ki a lehetséges közül
min(oszlopnev) a minimum értéket választja ki a lehetséges közül
sum(oszlopnev) az oszlopba szereplő számok összegét számolja ki.
pl.: Az azonos nevű embereknek, mekkora az átlagos születési éve?
Azonban vegyük észre, hogy ezeket a függvényeket group by nélkül is használhatjuk, ekkor a tábla összes sorára vonatkozik. pl.: Hány embert vettünk fel a táblába?
SELECT ev,
COUNT(ev) AS darab
FROM elso
WHERE ev>1950
GROUP BY ev
SELECT nev,
AVG(ev) AS darab
FROM elso
GROUP BY nev
SELECT COUNT(*) AS darab
FROM elso
Tábla kapcsolás - JOIN
Ha két táblából szeretnénk egyszerre lekérdezni, akkor a táblákat össze kell kapcsolnunk. Erre szolgál a JOIN kulcsszó. Három fajtája van:
INNER JOIN (kereszt)
LEFT JOIN (bal)
RIGHT JOIN (jobb)
Tudnunk kell azt is hogy az összekapcsoláskor megadhatunk az ON paraméter után egy feltételt is, ami alapján összeválogatja a sorokat. Ha viszont nem adunk meg ilyen feltételt, akkor minden sort minden sorral össze fog párosítani, ez két kétsoros táblánál 4 sort, két háromsoros táblánál 9 sort, két négy soros táblánál 16 sort ad vissza. Tehát a sorok száma a két tábla sorainak a számának a szorzatával egyezik meg.
Gondoljunk bele, hogy egy 100 és egy 200 soros táblánál, 20000 sort kapunk vissza, míg ha használjuk a ON feltételét átlagban 100 sorról beszélhetünk.
Tábla kapcsolás - INNER JOIN
A SELECT annyival egészül ki, hogy FROM tábla név után bekerül az INNER JOIN és a másik tábla nev. Ezután az ON feltétel és zárójelek között egy feltételt kell megadnunk. Utána folytatjuk a WHERE feltétellel a lekérdezés. Ennél a függvénynél csak akkor kapjuk vissza az összepárosított sort, ha mind két felében a megfelelő érték létezik. Ha elhagyjuk az ON feltételt, akkor minden sort összepárosít a másik tábla összes sorával. Hogy könnyebben kezelhető legyen a hivatkozás a lekérdezésen belül, a tábla név után beírhatunk egy rövidebb nevet, ekkor a későbbiekben ezzel a névvel hivatkozhatunk a lekérdezésben. Ez ilyen név megadását egy vesszővel zárjuk le.
Ez azért segítség nekünk, mert ha két táblában ugyanazok a nevek fordulnak elő, az oszlopnévként akkor a tabla_nev.oszlop_nev formában hivatkozunk. Ha nem így teszünk, akkor nem fog lefutni az SQL utasításunk és hibát ad vissza. Amellett előfordulhat olyan is, amikor ugyanazt a táblát vesszük egymás mellé, ilyenkor értelemszerűen kötelező ezt használni.
SELECT elso.nev, masodik.ev
FROM elso INNER JOIN masodik
SELECT nev, ev
FROM elso INNER JOIN masodik
ON (elso.azonosito=masodik.azonosito)
SELECT b.nev, a.ev
FROM elso a, INNER JOIN elso b,
ON (a.azonosito=b.azonosito)
Tábla kapcsolás - LEFT JOIN
Ez annyiban különbözik az INNER JOIN-tól, hogy amikor összekapcsolunk két táblát és ha nincsen megfelelő érték az első helyen lévő táblába a második helyen lévő táblához, akkor kiegészíti azokat NILL értékekkel. Ez akkor kelhet, amikor minden sornak szerepelni kell az eredményben, még akkor is, ha nem tartozik hozzá érték.
SELECT masodik.nev, masodik.ev
FROM elso LEFT JOIN masodik
ON (elso.azonosito=masodik.azonosito)
SELECT elso.nev, masodik.ev
FROM elso a, LEFT JOIN elso b,
ON (a.azonosito=b.azonosito)
Tábla kapcsolás - RIGHT JOIN
Ez annyiban különbözik az LEFT JOIN-tól, hogy a másik irányba egészíti ki a táblát.
Egyszerre több táblát is összekapcsolhatunk ilyenkor az ON () feltétel mögé kerül a következő JOIN aztán az ehhez tartozó ON () feltétel.
SELECT elso.nev, masodik.ev
FROM elso RIGHT JOIN masodik
ON (elso.azonosito=masodik.azonosito)
SELECT elso.nev, b.ev, c.kor
FROM elso a, RIGHT JOIN elso b,
ON (a.azonosito=b.azonosito)
RIGHT JOIN masodik c,
ON (a.azonosito=c.azonosito)
Teszt modul
Ezen rész segítségével ellenőrizheti, hogy megfelelően elsajátította az elmélet anyagát. Ezt kétféle képen teheti meg:
Az elsőnél 20 kérdést kell helyesen válaszolnia.
A másodiknál ugyancsak 20 kérdésre kell helyesen válaszolnia, de ekkor már csak 45 másodpercet szánhat maximum egy kérdésre.
A gyakorlat modul
A gyakorlat modul segítségével begyakorolhatjuk, az immár megfelelő szinten elsajátított elméletet. Ebben a részben különböző lekérdezéseket fogunk írni. Ahhoz, hogy ez könnyen menjen, ismerjük meg azt a négy táblát amivel dolgozni fogunk. Minden lesz egy oszlop ami az egyedi azonosítót tartalmazza ez a megfelelő táblanév_ID nevű oszlop (az ID az angol identifier szóból származik ami azonosítót jelent).
A Homerseklet tábla:
A hőmérséklet táblában van 5 oszlop a Homerseklet_ID ami az egyéni azonosító, a fok ami tartalmazza a hőmérséklet értéket, a datum mi tartalmazza, hogy melyik napon mérték azt az értéket, a varos tartalmazza, hogy hol mérték és a napszak tartalmazza, hogy melyik napszakban mérték azt.
Néhány eleme:
Homerseklet_ID fok datum varos napszak
1 10.5 1990-10-11 19:00:00 Budapest este
2 15.5 1990-05-06 20:00:00 Szeged este
3 2 2006-01-08 13:15:00 Budapest délben
4 -1.5 2006-01-08 12:22:00 Szeged délben
5 -3 2006-01-08 00:00:00 Budapest reggel
A Dolgozok tábla:
A dolgozók táblában 4 oszlop van a Dolgozok_ID ami az egyéni azonosító, a nev ami a dolgozó neve, a beosztas ami a dolgozó beosztása, és a telephely ami egy szám és a telephelyet azonosítja.
Néhány eleme:
Dolgozok_ID nev beosztas telephely
1 Kis Péter kefekötő 1
2 Árva János bilikészítő 2
3 Kis Jolán takarító 1
A Fizetes tábla:
A fizetés táblában az egyedi azonosítón kívül még két oszlop van, az egyik a beosztas (a dolgozók lehetséges munkakörei), a másik ber (a fizetésük).
Néhány eleme:
Fizetes_ID beosztas ber
1 kefekötő 60000
2 bilikészítő 74000
3 csavar lazító 78452
A Telephelyek tábla:
A telephely táblában van a telephely neve (nev), a címe (cim), az irányítószáma (irszam) és a telefonszáma (telefon).
Néhány eleme:
Telephelyek_ID nev cim irszam telefon
1 Kefekészítő részleg Budapest Kiskefe utca 56-72 1174 457-78-91, 457-77-88
2 Biliépítő részleg Pécs Bili utca 12 8745 06-32-58-85-60
3 Vezérigazgatóság Budapest Nagy Péter Lajos út 52-54 1066 06-1-249-98-99
A CREATE TABLE kulcsszóval lehet létrehozni egy táblát. Ez után zárójelben fel kell sorolni azokat az elemeket, amik a táblázat oszlopai lesznek, ezt mindig az oszlopnév majd a típus meghatározásával történik. Ezután jöhetnek az egyéb speciális parancsszavak.
create kulcsszó bevezeti a tábla létrehozását
null/not null jelentése, hogy az adott elemnek kötelező-e értéket adni. Ha null akkor nem, ha not null akkor kötelező értéket adni
default kulcsszóval megadhatunk egy alapértelmezett értéket, így ha később nem adjuk meg a mező értékét, akkor ennek a helyére beíródik az itt megadott érték
Ha adatbázist akarunk létrehozni, akkor a CREATE DATABASE adatbázis_nev formába kell megadni.
CREATE TABLE elso (
azonosito INT NOT NULL default 0,
nev VARCHAR(75) NOT NULL,
szul_ev DATE NOT NULL,
szem_ig_szam CHAR(8) NOT NULL,
PRIMARY KEY (azonosito)
)
CREATE DATABASE elso_adatbazis
Tábla törlése - drop
A DROP TABLE paranccsal vezetjük be a tábla törlését. Ez után következik a tábla neve amit törölni szeretnénk. A parancs lefuttatásával töröljük a tábla összes sorát és magát a tábla szerkezetét is.
Ha az adatbázist akarom törölni, akkor az a DROP DATABASE parancsszót kell használni. Ha ezt lefuttatjuk, akkor törli az összes táblát és az adatbázis szerkezetét is.
DROP TABLE elso
DROP DATABASE elso_adatbazis
Új sor hozzáadása - insert into
Az INSERT INTO parancsszóval vezetjük be az új sor hozzáadását. Ezután jön a tábla név, majd a VALUES kulcsszó. Ezután a zárójelek között az oszlopneveknek megfelelően sorrendbe bele kell írni aposztrófok közé az értékeket.
Ha nem tudjuk, hogy milyen sorrendbe vannak a tábla oszlopai, akkor a tábla név és a VALUES között zárójelben megadhatunk egy tetszőleges oszlopnév sorrendet, de ekkor ebben a sorrendben kell megadni a paramétereket a VALUES kulcsszó után.
Azt is megtehetjük, hogy nem az összes oszlopnak adunk értéket. Ekkor, ha a tábla létrehozásánál megadtunk DEFAULT értéket, akkor ez az érték íródik be a táblába. Ha nincs ilyen megadva, akkor null értéket vesz fel.
INSERT INTO elso
VALUES ('1', 'petike', '1987', '457887DH')
INSERT INTO elso (nev, ev, szem_ig_szam, azonosito)
VALUES ('petike', '1987', '457887DH', '1')
INSERT INTO elso (nev, ev, szem_ig_szam, azonosito)
VALUES ('petike', '1987', '457887DH', '1')
INSERT INTO elso (nev, ev, szem_ig_szam)
VALUES ('petike', '1987', '457887DH')
Módosítás - update
Az UPDATE kulcsszóval vezetjük be a módosítást, ezután következeik a tábla neve, amelyben a módosítást el akarjuk végezni. Ezután a SET kulcsszóval vezetjük be a módosítandó oszlopok felsorolását, ez a következő formában történik oszlop_nev='érték'. Ha több adatot akarunk egyszerre módosítani, akkor az érték és a következő oszlopnév közé egy vesszőt kell raknunk. Az utolsó oszlopnév érték pár után nem kell vesszőt raknunk.
Ügyeljünk arra, hogyha nem kerül a módosítás végére where feltétel, akkor a tábla összes sorában végbemegy a változtatás. Tehát ha azt akarjuk, hogy ez ne történjen meg, akkor írni kell egy where kulcsszót és egy relációs feltételt.
UPDATE elso SET
nev='Péter',
azonosito ='38',
ev='1977'
WHERE ev='1987'
Törlés - delete from
A DELETE kulcsszóval vezetjük be a törlést. Ez a törlés nem egyezik meg a DROP paranccsal, ezzel ugyanis csak bizonyos sorokat lehet törölni, de a tábla szerkezetét nem.
A DELETE FROM kulcsszavak után jön a tábla neve, amelyből sorokat akarunk törölni. Utána kötelező a where feltétel, mert ha elhagyjuk, a tábla összes sorát töröljük (tehát szintaktikailag elfogadja az adatbázis-kezelő rendszer). Ezért mindig óvatosan használjuk ezt a parancsot!
DELETE FROM elso WHERE ev ='1987'
Az azonosító
Miért fontos az, hogy minden táblában legyen egy azonosító?
Egy táblában fontos, hogy minden sort egyedileg meg tudjunk különböztetni, és tudjunk rá hivatkozni is. Első pillanatra furcsállhatjuk, hogy minek kell emiatt felvenni még egy oszlopot, hiszen ott van a név és majd amikor megadjuk a feltételt akkor a névvel szűkítjük, hogy mire vonatkozzon a módosítás, törlés stb...
Ez jól is fog működni mindaddig amíg nem veszünk fel két ugyanolyan nevű embert (Tóth, Szabó, Kiss vagy éppen Nagy vezetéknevű emberből sok van és az is elég gyakori, hogy a keresztnevük is ugyan az). Persze mondhatnánk, hogy felvesszük hozzá a születési dátumát ekkor viszont már a feltétel bonyolult lesz : where nev = 'Kiss János' and szuldatum='1901.12.24' . Ugyanakkor még ez sem garancia, hogy nem lesznek olyan emberek akiknek ugyanazok az adataik. Ebből látható, hogy szükséges minden sorhoz hozzárendelni egy egyedi azonosítót, ami csak ahhoz az egy emberhez tartozik. Ezt az azonosítót az informatikába ID-nak mondjuk (az ID az angol identifier szóból származik ami azonosítót jelent). Így minden táblánkban szerepelni fog egy ID oszlop.
Felmerülhet a kérdés, mi van akkor, ha két ugyanolyan ID-t adunk meg. Ekkor gondunk lenne, mert egy törlés parancs kiadásakor mind a két rekordot törölnénk. Ennek elkerülésére, amikor létrehozzunk a táblát jelezzük az adatbázis-kezelő rendszernek, hogy ennek az oszlopnak mindig egyedinek kell lennie. Ezt a primary key(oszlopnev) paranccsal tehetjük meg. A primary key elsődleges kulcsot jelent. Az adatbázisoknál kulcsnak mondjuk azokat az oszlopokat, amik egyértelműen meghatározzák, hogy a táblában melyik sorról van szó. Ekkor, ha két ugyanolyan elemet szeretnénk felvinni a táblában, akkor a másodikat nem fogja engedélyezni, és hibaüzenetet kapunk.
Honnan tudjuk, hogy mi legyen a következő kulcsunk? Erre is van egy megoldás az adatbázis-kezelő rendszerben. Minden sorhoz, hozzárendelünk egy számot, és folyamatosan növeljük a szám értékét. Tehát amikor a következő sort visszük fel akkor az előzőhöz képest eggyel nagyobb számot adunk meg.
Ezt el is végezheti az adatbázis-kezelő rendszer helyettünk, ha megadjuk, hogy automatikusan növelje ezt a számot. Erre a funkcióra szolgál az auto_increment parancs. Például a create parancsnál a következőképpen is megadhatjuk: `ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY. Az ID az oszlopnév, a not null logikusan azt jelenti, hogy nem lehet üres (hiszen mindegyiknek különbözőnek kell lennie), és végül, hogy automatikusan növekedjen az elsődleges kulcsunk.
Előfordulhat olyan is, hogy szeretnék, ha az adatbázis-kezelő figyelné, hogy az adott oszlopba ne kerüljön két ugyanolyan érték, de nem ez a kulcsunk (pl.: két ugyanolyan tajkártya szám ne legyen a táblába). Ezt az unique(oszlopnev) paranccsal adhatom meg (az angol unique szó jelentése egyedi).
Szerkezet módosítás - alter table
Az ALTER TABLE kulcsszó vezeti be a tábla szerkezetének módosítását, ezután következik a táblanév, amelyen a módosítást kívánjuk végezni.
Utána jön, az ADD vagy a DROP kulcsszó, azaz vagy hozzáadunk a tábla szerkezetéhez egy oszlopot vagy törlünk belőle egyet. Ezután következik az oszlop neve. Ha hozzáadunk, akkor itt történik a típus meghatározása is, úgy mint a create parancsnál. Törléskor csak a törlendő oszlop nevét kell beírnunk.
Ha nem a tábla utolsó oszlopa után szeretnénk beszúrni az új oszlopot, akkor az AFTER kulcsszót kell használnunk, ezt után jön az az oszlopnév, ami után szeretnénk az új oszlopot. Ilyen formába elérjük el, hogy a megadott oszlop után kerüljön beszúrásra az új oszlopunkat.
ALTER TABLE elso ADD uj_oszlop VARCHAR( 255 ) NOT NULL
ALTER TABLE elso ADD uj_oszlop VARCHAR( 255 ) NOT NULL AFTER ev
ALTER TABLE elso DROP torlendo_oszlop
Lekérdezés - select
Eddig olyan parancsszavakat néztünk, ami az adatbázisunk szerkezetét, illetve az adatokat módosította, de nem volt visszatérési értéke. Ez azt jelenti, hogy az eddig megismert függvények lefutásakor elvégződik a művelet, de a felhasználó, azaz mi nem tudjuk meg, hogy mi is történt. Ezért amikor kiadunk egy ilyen függvényt utána egy lekérdezéssel ellenőrizzük, hogy mi történ az táblánkban.
Ahhoz, hogy kapjunk vissza adatokat, meg kell ismerkednünk még egy paranccsal, ez a SELECT. Ennek mindig van visszatérési értéke, de előfordulna olyan eset is, amikor nincsen benne érték. Ez például akkor fordulhat elő, ha az előző utasítással töröltünk, és ezután a select utasítással meggyőződünk arról, hogy tényleg töröltük-e az az adott sor. Ekkor értelemszerűen nem lehet visszatérés érték, mert az előző paranccsal töröltük azt.
Lekérdezés - select *
A SELECT kulcsszóval vezetjük be a lekérdezést. Ezután következik egy felsorolás azokkal az oszlopnevekkel, amelyeknek az értékeit látni szeretnénk. Ha az összes oszloppal akarunk dolgozni, akkor elegendő a * karakter megadása, ekkor az összes oszlopot visszaadja.
Ezután következik a FROM tabla_nev páros, azaz az itt megadott táblából kérdezünk le adatokat. Így elérhetjük, hogy a táblánk összes sorát visszakapjuk.
SELECT * FROM elso
SELECT nev, ev FROM elso
Szűrés - where
Mit tehetünk akkor, hogyha nem akarjuk, hogy a tábla összes sorát visszakapjuk? Ilyenkor szűrést kell alkalmaznunk, amelyben megmondjuk, hogy melyik sorokat szeretnénk visszakapni. A feltétel kulcsszava a WHERE. Ezután következhetnek a feltételek. Mivel relációs adatbázis-kezelő rendszerrel dolgozunk ezért, értelemszerűen a feltételek is relációkkal kell megadni.
Így a feltétel lehet egyenlő ( = ), nem egyenlő ( != ), kisebb ( < ), nagyobb ( > ), kisebb egyenlő ( <= ), vagy nagyobb egyenlő ( => ).
Ha több feltételt adunk meg, akkor az AND (és kapcsolat) és az OR (vagy kapcsolat) kulcsszóval köthetünk össze azokat. A feltételek tetszőlegesen zárójelezhetőek.
Tehát a WHERE kulcsszó utáni részt mindenegyes sornál kiértékeli, ha a feltételek mindegyike megfelel az adott sorban, akkor azt a sort hozzáfűzi a visszatérési értékhez. Így csak azokat és csak azokat a sorokat kapjuk vissza, amelyek teljes mértékben megfelelnek a feltételnek.
SELECT * FROM elso WHERE ev>1950
SELECT nev, ev FROM elso WHERE ev>1950 and azonosito<100 or nev='péter'
Rendezés - order by
Az eredményt tetszőlegesen rendezhetjük is. Így nem okozhat gondot, ha egy névsort abc sorrendbe kell állítani. A rendezés kulcsszava az ORDER BY. Ez a WHERE feltétel után következik, természetesen, ha nincs WHERE feltételünk akkor a táblanév után helyezzük el ezt.
Az ORDER BY után következik egy oszlop_nev, e szerint az oszlop szerint rendeződik az eredmény. Meg lehet adni egymás után több oszlopnevet is, ilyenkor ha az első oszlopban van több ugyanolyan adat, akkor a sorrend megállapításakor a következő oszlop értékeit fogják eldönteni, hogy mi a sorrend.
Ha csökkenő sorrendet akarunk elérni, akkor az oszlopnevek után a DESC kulcsszó kerül.
SELECT * FROM elso
WHERE ev>1950
ORDER BY ev
SELECT nev, ev FROM elso
WHERE ev>1950 and azonosito<100 or nev='péter'
ORDER BY nev, azonosito DESC
Csoportosítás - group by
Ha csoportosítani kell, akkor a GROUP BY parancsszót kell alkalmaznunk. Ezután következik az az oszlop név, ami alapján csoportosítani szeretnénk. Ilyenkor ügyelni kell arra, hogy a SELECT utáni részbe csak olyan oszlopnév illetve függvényt írjunk, aminek a végrehajtásakor a végeredménye egy darab érték lesz (pl.: számoljuk meg, hányan születtek minden évben 1950 után. Ekkor a count(ev)) megszámoljuk az adott évben született emberek számát, látjuk, hogy ez minden egyes évre egy darab értéket ad vissza.
Az AS paranccsal el(át)nevezzünk egy oszlopnevet, jelen esetben a kiszámolt érték oszlopneve a darab lesz.
SELECT ev,
COUNT(ev) AS darab
FROM elso
WHERE ev>1950
GROUP BY ev
Lekérdezés - függvények
Most nézzük át azokat a funkciókat, amelyeket gyakran használjunk amikor különböző statisztikai, illetve egyéb adatokat szeretnénk megkapni.
Két csoportra oszthatjuk a függvényeket, attól függően, hogy milyen környezetbe alkalmazzuk azokat.
Amelyek egy adott adatelemen végrehajthatóak.
Amelyek a csoportosításkor használhatóak (group by).
Szöveg kezelő függvények
Szinte minden feladat során előfordul, hogy egy szöveg tartalommal kell műveleteket végeznünk. A leggyakrabban használt függvényeket nézzük meg:
SUBSTRING(szoveg, honnan, mennyit) Kimásol egy szöveg részletet a honnan indextől, 'mennyit' karakter darabot.
SUBSTRING(szoveg, honnan) Kimásolja az adott ponttól a szöveget egészen a végéig.
CONCAT(szoveg1, szoveg2, szoveg3, ...) Összefűz tetszőleges számú szövegdarabot.
CONCAT_WS(speciális elem, szoveg1, szoveg2, ...) Összefűz tetszőleges számú szövegdarabot, és az elemek közé a speciális elemet illeszti.
INSERT(szoveg, honnan, mennyit, mivel) A szöveg amibe felülírok a 'mivel' szöveggel a honnan pozíciótól, 'mennyit' karakteren keresztül.
LENGTH(szoveg) A szöveg karaktereinek a számát adja vissza.
LOWER(szoveg) A szöveget kisbetűssé alakítja.
UPPER(szoveg) A szöveget nagybetűssé alakítja.
STRCMP(szoveg1, szoveg2) Ha a két szöveg ugyanaz akkor 0-t ad vissza eredményként (különben -1 vagy 1-et attól függően, hogy melyik a nagyobb).
LOCATE(szovegdarab, szoveg) Visszaad egy számot, ami az első előfordulási helye a szövegdarabnak a szövegben.
TRIM(szoveg) Visszatér a szöveggel, de az elejéről és a végéről eltávolítja a szóközöket.
SELECT substring(nev,2,5) FROM elso
SELECT substring(nev,
locate(' ',nev)
) FROM elso
SELECT concat(nev,nev) FROM elso
SELECT concat_ws(', ', nev,nev) FROM elso
SELECT length(nev) FROM elso
SELECT upper(nev) FROM elso
SELECT lower(nev) FROM elso
SELECT trim(nev) FROM elso
Feltétel használata
Feltételt akkor használunk, hogyha egy adott érék megvizsgálása után nem csak egy irányban szeretnénk tovább haladni. Az SQL nyelvben is megvan ez a lehetőség a lekérdezések során azaz feltételeket a select parancs után kell írni. Kétféle feltétel szerkezetet különböztetünk meg a kétirányút és a többirányú elágazást.
IF (feltétel, ha igaz , ha nem igaz). Az if szóval vezetjük be, a feltételnek olyannak kell lenni ami igaz vagy hamis értéket ad vissza, és eszerint, hajtja végre az első vessző utáni rész, illetve ha nem igaz akkor a második vesző utáni részt. Vegyük észre, hogy ide nem csak egy érték kerülhet, hanem például a hamis részre tehetünk egy újabb if szerkezetet: IF(feltétel, igaz,IF(feltétel2, igaz, hamis)).
Ha több felé akarunk elágazni, akkor a case szerkezetet kell használnunk. A következő a szintaktikája: CASE WHEN [feltétel] THEN [valami] WHEN [feltétel2] THEN [valami2] ELSE [valami a maradékokra] END. Azt is megtehetjük, hogy az ELSE részt nem használjuk fel, de ekkor is le kell zárni az END paranccsal!
SELECT
IF( SUBSTRING(szem_ig_szam,1,1) = 1,'férfi','nő') AS neme FROM elso
SELECT CASE
WHEN SUBSTRING(datum,1,4)>1980 THEN 'fiatal'
WHEN SUBSTRING(datum,1,4)>1965 THEN 'közékorú'
WHEN SUBSTRING(datum,1,4)>1950 THEN 'öregedő'
ELSE 'öreg'
END AS állapota FROM elso
Matematikai függvények
Sokszor kell matematikai függvényeket is használni a szokásos +,- , *, / műveleteken kívül.
COS(X) A szám koszinuszát adja vissza.
SIN(X) A szám szinuszát adja vissza.
TAN(X) A szám tangensét adja vissza.
ABS(X) A szám abszolútértékét adja vissza.
POW(X,Y), POWER(X,Y) A szám hatványozását végzi el: xy.
RAND() Egy véletlen számot generál 0 és 1 között.
ROUND(X) Egy számot kerekít egésszé.
SQRT(X) A szám négyzetgyökét adja vissza.
TRUNCATE(X,D) A számot csonkolja. Ha 0 akkor a tizedespont utáni részt törli, ha D>0 akkor annyi tizedes értéket hagy meg amekkora a szám. Negatív is lehet a D ekkor pl.: -2 esetén a 128-ból 100-at készít.
SELECT COS(15), SIN(15), TAN(15)
SELECT ABS(-15)
SELECT POW(2,3)
SELECT RAND()
SELECT ROUND(15.6)
SELECT SQRT(16)
SELECT TRUNCATE(15.6,0)
Dátum függvények
A legtöbb problémát a dátumokkal kapcsolatban tapasztaljuk, nem mindegy hogy milyen a default formátum, és az sem triviális, hogy hogyan kell felvinni egy dátumot egy insert into segítségével.
CURDATE(), CURTIME() Visszaadja a mai nap dátumát, illetve a mostani pontos időt.
DATE_FORMAT(datum,forma) A formázókarakterek segítségével állíthatjuk elő a dátum formátumát. Nézzük meg a leggyakoribb formázó karaktereket:
év: %Y [XXXX], %y [XX],
hónap: %b [jan..dec], %c [1..12], %m [01..12] ,
nap %d [01.31], %e [1..31], %j [1..366], %W [hétfő..vasárnap],
óra: %h [01..12], %H [00..23], %k [0..23],
perc: %i [00..59],
másodperc: %s [00..59]
A hónap illetve a hét nevei az adatbázis kezelő rendszer nyelvétől függően jelenik meg.
DATE(datumidő) A dátumidő dátum részével tér vissza.
TIME(dátumidő) A dátumidő idő részével tér vissza.
MONTHNAME(datum) / DAYNAME(datum) A dátum hónap / nap nevét adja meg.
LAST_DAY(datum) Vissza adja az aktuális hónap utolsó napját (évszám hónap nap formában).
MAKEDATE(év,napokszáma) Kiszámolja a dátumot, az aktuális évszámhoz hozzáadja a megadott napok számát.
NOW() Visszaadja a mostani dátumot és a mostani időt.
YEAR(dátumidő) / MONTH(dátumidő) / DAY(dátumidő) / HOUR(dátumidő) / MINUTE(dátumidő) / SECOND(dátumidő) Visszadja az évet / hónapot / napot / órát / percet / másodpercet
SELECT curdate(), curtime()
SELECT date_format(datum, '%y %m %d %H %m %s') FROM elso
SELECT date(datum) FROM elso
SELECT time(datum) FROM elso
SELECT monthname(datum) FROM elso
SELECT last_day(datum) FROM elso
SELECT makedate('2004', 65)
SELECT now()
SELECT year(datum) FROM elso
Group by függvények
A group by függvényekhez a következő függvények tartozhatnak:
avg(oszlopnev) átlag függvény.
count(1) visszaadja, hogy hány darab.
max(oszlopnev) a maximum értéket választja ki a lehetséges közül
min(oszlopnev) a minimum értéket választja ki a lehetséges közül
sum(oszlopnev) az oszlopba szereplő számok összegét számolja ki.
pl.: Az azonos nevű embereknek, mekkora az átlagos születési éve?
Azonban vegyük észre, hogy ezeket a függvényeket group by nélkül is használhatjuk, ekkor a tábla összes sorára vonatkozik. pl.: Hány embert vettünk fel a táblába?
SELECT ev,
COUNT(ev) AS darab
FROM elso
WHERE ev>1950
GROUP BY ev
SELECT nev,
AVG(ev) AS darab
FROM elso
GROUP BY nev
SELECT COUNT(*) AS darab
FROM elso
Tábla kapcsolás - JOIN
Ha két táblából szeretnénk egyszerre lekérdezni, akkor a táblákat össze kell kapcsolnunk. Erre szolgál a JOIN kulcsszó. Három fajtája van:
INNER JOIN (kereszt)
LEFT JOIN (bal)
RIGHT JOIN (jobb)
Tudnunk kell azt is hogy az összekapcsoláskor megadhatunk az ON paraméter után egy feltételt is, ami alapján összeválogatja a sorokat. Ha viszont nem adunk meg ilyen feltételt, akkor minden sort minden sorral össze fog párosítani, ez két kétsoros táblánál 4 sort, két háromsoros táblánál 9 sort, két négy soros táblánál 16 sort ad vissza. Tehát a sorok száma a két tábla sorainak a számának a szorzatával egyezik meg.
Gondoljunk bele, hogy egy 100 és egy 200 soros táblánál, 20000 sort kapunk vissza, míg ha használjuk a ON feltételét átlagban 100 sorról beszélhetünk.
Tábla kapcsolás - INNER JOIN
A SELECT annyival egészül ki, hogy FROM tábla név után bekerül az INNER JOIN és a másik tábla nev. Ezután az ON feltétel és zárójelek között egy feltételt kell megadnunk. Utána folytatjuk a WHERE feltétellel a lekérdezés. Ennél a függvénynél csak akkor kapjuk vissza az összepárosított sort, ha mind két felében a megfelelő érték létezik. Ha elhagyjuk az ON feltételt, akkor minden sort összepárosít a másik tábla összes sorával. Hogy könnyebben kezelhető legyen a hivatkozás a lekérdezésen belül, a tábla név után beírhatunk egy rövidebb nevet, ekkor a későbbiekben ezzel a névvel hivatkozhatunk a lekérdezésben. Ez ilyen név megadását egy vesszővel zárjuk le.
Ez azért segítség nekünk, mert ha két táblában ugyanazok a nevek fordulnak elő, az oszlopnévként akkor a tabla_nev.oszlop_nev formában hivatkozunk. Ha nem így teszünk, akkor nem fog lefutni az SQL utasításunk és hibát ad vissza. Amellett előfordulhat olyan is, amikor ugyanazt a táblát vesszük egymás mellé, ilyenkor értelemszerűen kötelező ezt használni.
SELECT elso.nev, masodik.ev
FROM elso INNER JOIN masodik
SELECT nev, ev
FROM elso INNER JOIN masodik
ON (elso.azonosito=masodik.azonosito)
SELECT b.nev, a.ev
FROM elso a, INNER JOIN elso b,
ON (a.azonosito=b.azonosito)
Tábla kapcsolás - LEFT JOIN
Ez annyiban különbözik az INNER JOIN-tól, hogy amikor összekapcsolunk két táblát és ha nincsen megfelelő érték az első helyen lévő táblába a második helyen lévő táblához, akkor kiegészíti azokat NILL értékekkel. Ez akkor kelhet, amikor minden sornak szerepelni kell az eredményben, még akkor is, ha nem tartozik hozzá érték.
SELECT masodik.nev, masodik.ev
FROM elso LEFT JOIN masodik
ON (elso.azonosito=masodik.azonosito)
SELECT elso.nev, masodik.ev
FROM elso a, LEFT JOIN elso b,
ON (a.azonosito=b.azonosito)
Tábla kapcsolás - RIGHT JOIN
Ez annyiban különbözik az LEFT JOIN-tól, hogy a másik irányba egészíti ki a táblát.
Egyszerre több táblát is összekapcsolhatunk ilyenkor az ON () feltétel mögé kerül a következő JOIN aztán az ehhez tartozó ON () feltétel.
SELECT elso.nev, masodik.ev
FROM elso RIGHT JOIN masodik
ON (elso.azonosito=masodik.azonosito)
SELECT elso.nev, b.ev, c.kor
FROM elso a, RIGHT JOIN elso b,
ON (a.azonosito=b.azonosito)
RIGHT JOIN masodik c,
ON (a.azonosito=c.azonosito)
Teszt modul
Ezen rész segítségével ellenőrizheti, hogy megfelelően elsajátította az elmélet anyagát. Ezt kétféle képen teheti meg:
Az elsőnél 20 kérdést kell helyesen válaszolnia.
A másodiknál ugyancsak 20 kérdésre kell helyesen válaszolnia, de ekkor már csak 45 másodpercet szánhat maximum egy kérdésre.
A gyakorlat modul
A gyakorlat modul segítségével begyakorolhatjuk, az immár megfelelő szinten elsajátított elméletet. Ebben a részben különböző lekérdezéseket fogunk írni. Ahhoz, hogy ez könnyen menjen, ismerjük meg azt a négy táblát amivel dolgozni fogunk. Minden lesz egy oszlop ami az egyedi azonosítót tartalmazza ez a megfelelő táblanév_ID nevű oszlop (az ID az angol identifier szóból származik ami azonosítót jelent).
A Homerseklet tábla:
A hőmérséklet táblában van 5 oszlop a Homerseklet_ID ami az egyéni azonosító, a fok ami tartalmazza a hőmérséklet értéket, a datum mi tartalmazza, hogy melyik napon mérték azt az értéket, a varos tartalmazza, hogy hol mérték és a napszak tartalmazza, hogy melyik napszakban mérték azt.
Néhány eleme:
Homerseklet_ID fok datum varos napszak
1 10.5 1990-10-11 19:00:00 Budapest este
2 15.5 1990-05-06 20:00:00 Szeged este
3 2 2006-01-08 13:15:00 Budapest délben
4 -1.5 2006-01-08 12:22:00 Szeged délben
5 -3 2006-01-08 00:00:00 Budapest reggel
A Dolgozok tábla:
A dolgozók táblában 4 oszlop van a Dolgozok_ID ami az egyéni azonosító, a nev ami a dolgozó neve, a beosztas ami a dolgozó beosztása, és a telephely ami egy szám és a telephelyet azonosítja.
Néhány eleme:
Dolgozok_ID nev beosztas telephely
1 Kis Péter kefekötő 1
2 Árva János bilikészítő 2
3 Kis Jolán takarító 1
A Fizetes tábla:
A fizetés táblában az egyedi azonosítón kívül még két oszlop van, az egyik a beosztas (a dolgozók lehetséges munkakörei), a másik ber (a fizetésük).
Néhány eleme:
Fizetes_ID beosztas ber
1 kefekötő 60000
2 bilikészítő 74000
3 csavar lazító 78452
A Telephelyek tábla:
A telephely táblában van a telephely neve (nev), a címe (cim), az irányítószáma (irszam) és a telefonszáma (telefon).
Néhány eleme:
Telephelyek_ID nev cim irszam telefon
1 Kefekészítő részleg Budapest Kiskefe utca 56-72 1174 457-78-91, 457-77-88
2 Biliépítő részleg Pécs Bili utca 12 8745 06-32-58-85-60
3 Vezérigazgatóság Budapest Nagy Péter Lajos út 52-54 1066 06-1-249-98-99
Nincsenek megjegyzések:
Megjegyzés küldése