2018. március 19., hétfő

Az SQL lekérdezések sémája



Az SQL lekérdezések a következő részekből állnak:

SELECT oszlopkifejezés,...
FROM tábla,...
WHERE sorfeltétel
GROUP BY csoportosítás
HAVING csoportfeltétel
ORDER BY rendezési szempont

A hat rész funkciója röviden (részletek a következő fejezetben):
  • SELECT : a válaszban megjelenítendő oszlopok és kifejezések listája
  • FROM : melyik táblából vagy táblákból várjuk az eredményeket 
  • WHERE : milyen feltételeknek megfelelő sorokat jelenítsünk meg a válaszban
  • GROUP BY : az eredmény rekordjait csoportosíthatjuk valamilyen oszlopkifejezés lehetséges értékei szerint
  • HAVING : a létrehozott csoportokat is megszűrhetjük
  • ORDER BY : a válasz rendezése a táblák oszlopai alapján
Nem része a szabványnak, de már sok SQL megvalósításban szerepel a következő kiegészítés:
SELECT ...
...
LIMIT megjelenítendő sorok száma

SELECT
A SELECT után soroljuk fel a megjelenítendő oszlopokat és oszlopkifejezéseket. A "SELECT * " minden oszlopot megmutat.
   
SELECT *
FROM sorozatok

SELECT cim,nemzet
FROM sorozatok

Az oszlopokra mezőnevekkel hivatkozhatunk. Az oszlopkifejezések aritmetikai műveletekkel és függvényekkel képezhetők. Az eredménytábla oszlopai átnevezhetők az AS paranccsal. MySql-ben ékezet nélküli mezőneveket és táblaneveket használunk.

   
SELECT név, fizetés + jutalom
FROM bérek
    
SELECT AVG( fizetés)
FROM bérek
    
SELECT AVG( fizetés ) AS átlagfizetés
FROM bérek

Összesítő függvények
Az összesítő függvények a tábla valamelyik oszlopa alapján egyetlen értéket számolnak ki.
  • összeg : SUM
  • átlag : AVG
  • darabszám : COUNT
  • minimum : MIN
  • maximum : MAX
A COUNT nem számolja a NULL értékeket. A COUNT(DISTINCT mezőnév) azt adja meg, hány különböző érték szerepel az oszlopban.

SELECT SUM( bevétel), SUM( kiadás )
FROM könyvelés

SELECT COUNT(DISTINCT rendező )
FROM filmek

SELECT AVG( jegy ) AS osztályátlag
FROM dolgozatok

Kifejezések a SELECT záradékban

Aritmetika
SELECT 2+2

-> 4

SELECT 2*3
    
-> 6

SELECT POW(2, 5)
    
-> 32

SELECT 8 / 5
    
-> 0.60

SELECT 8 DIV 5
    
-> 1

SELECT MOD(8, 5)
    
-> 3
Dátumkezelés
SELECT NOW()
   
-> 2010-01-08 08:26:24

SELECT YEAR( NOW() )
    
-> 2010

SELECT DATE_ADD( NOW(), INTERVAL 100 DAY )
    
-> 2010-04-18 08:28:24
Logika
SELECT IF( 4 > 3, 'rendben', 'baj van')
Karakterlánc kezelés
SELECT LEFT('Berzsenyi Dániel Gimázium', 9)

-> 'Berzsenyi'

FROM

A FROM után soroljuk fel, hogy melyik táblákat használjuk a lekérdezésben. Ha egynél több táblát használunk, össze kell kapcsolni őket. Ha egy mezőnév több táblában is előfordul, akkor kötelező minősített névvel hivatkozni rá. A minősített név alakja. táblanév.mezőnév. Ha egyértelmű, hogy melyik táblához tartozik egy mező, akkor nem kell kiírni a tábla nevét.

SELECT *
FROM sorozatok
    
SELECT cim,nev
FROM sorozatok, szereplok
WHERE sorozatok.id = szereplok.sorozat
    
A használt táblák (rövidítés céljából) az AS paranccsal átnevezhetők.

SELECT cim,nev
FROM sorozatok AS s, szereplok AS sz
WHERE s.id = sz.sorozat
    
Az átnevezésre akkor is szükség lehet, ha egy lekérdezésben egy táblát önmagával kell összekapcsolni.
WHERE
A WHERE után adjuk meg annak feltételét, hogy melyik sorok kerüljenek az eredménybe. Egy feltételben mezők és konstanskifejezések értékét hasonlíthatjuk össze. Összetett feltételek logikai műveletekkel képezhetők.
  • "és" : AND
  • "vagy" : OR
  • "nem" : NOT

SELECT cim
FROM sorozatok
WHERE kezdet < 2000
    
SELECT cim
FROM sorozatok
WHERE nemzet = 'magyar'
        
SELECT nev
FROM szereplok
WHERE (sorozat = 1) AND (fo = 'i')
    
Karakterláncok esetében használhatunk dzsóker karaktereket. Ekkor az egyenlőség helyett a LIKE parancsot használjuk. 

%    0 vagy több tetszőleges karakter
_    egy tetszőleges karakter 
(MS-W alatt '*' és '?')

SELECT cim,web
FROM sorozatok 
WHERE web LIKE '%.hu'

ORDER BY, LIMIT és TOP

Rendezés

Az ORDER BY a választábla sorait rendezi. Azokat az oszlopokat (vagy sorszámukat) kell felsorolni, ami szerint rendezni szeretnénk a választ. Ha több oszlopot is megadunk, a második (,harmadik,...) csak akkor számít, ha az első (korábbi) szempont szerint "egyenlő" a két sor.

A rendezés iránya alapból növekvő, ha csökkenő sorrendet szeretnénk, a DESC módosítót kell alkalmazni.

Példák

SELECT név, fizetés
FROM bérek
ORDER BY fizetés DESC

SELECT osztály, név
FROM tanulók
ORDER BY 1, 2

SELECT termék, ár
FROM katalógus
ORDER BY ár*(1+áfa)

Rendezhetünk olyan oszlop (vagy oszlopkifejezés) szerint is, ami nem szerepel a SELECT után. Viszont ha az oszlop sorszámát adjuk meg a rendezésnél, az a SELECT után felsorolt kifejezések közötti sorszámot jelenti.

Választábla részének kiválasztása

Előfordulhat, hogy a választábla egy részét akarjuk megjeleníteni.

Első néhány sor kiírása

SELECT ...
FROM ...
WHERE ...
LIMIT 10

Rész kiválasztása

SELECT ...
FROM ...
WHERE ...
LIMIT 100,20

Első néhány sor kiírása máshogy

SELECT TOP 10 előadó
FROM slágerlista
WHERE ...

GROUP BY

Példák

SELECT név, SUM(befizetés)
FROM könyvelés
GROUP BY név


SELECT név, SUM(befizetés) - SUM(kifizetés) AS "egyenleg"
FROM könyvelés
GROUP BY név


SELECT név, SUM(befizetés) - SUM(kifizetés) AS "egyenleg"
FROM könyvelés
GROUP BY név, azonosító

HAVING

Példák

SELECT diák, AVG(jegy) AS "tanulmányi átlag"
FROM napló
GROUP BY diák
HAVING AVG(jegy) >= 4.0
ORDER BY 2 DESC
Táblák összekapcsolása
Jól megtervezett (normalizált) adatbázisokban az összetartozó információk sokszor több táblába vannak szétosztva. Ezzel elkerülhető a redundancia, továbbá az adatok módosításnál vagy törlésénél így egyszerűbb elkerülni bizonyos hibákat (inkozisztens adatok, információvesztés).

Amikor lekérdezünk az adatbázisból, össze kell szednünk az összetartozó adat-töredékeket. Erre szolgál a táblák összekapcsolása. Alapvetően kétféle összekapcsolást használunk:
  • belső összekapcsolás (INNER JOIN)
  • külső összekapcsolás (OUTER JOIN)
Az összekapcsolás lényege, hogy több táblából egyet csinál oly módon, hogy az eredmény tábla sorai az eredeti táblák megfelelő sorainak egymás mellé írásával keletkeznek. Hogy mit jelent a "megfelelő", az az összekapcsolás feltételében adható meg. Ez leggyakrabban abból áll, hogy az egyik tábla rekordjaiban található valamelyik mező meg kell egyezzen a másik tábla rekordjának egy mezőjével. Itt tipikusan a feltételben szereplő egyik mező kulcs szokott lenni, de ez nem kötelező.
Példa
Tekintsük a következő táblákat:
Diák(név,of)
név
of
Aladár
1
Bori
2
Csilla
2
Tanár(sor,név)
sor
név
1
Kiss Lenke
2
Gálos László
3
Konta Gyula
Ha a Diák tábla "of" mezőjét rendeljük a Tanát tábla "sor" mezőjéhez, akkor a következő összekapcsolt táblát kapjuk.
Diák.név
of
sor
Tanár.név
Aladár
1
1
Kiss Lenke
Bori
2
2
Gálos László
Csilla
2
2
Gálos László
Belső összekapcsolása
Összekapcsolás a "WHERE" záradékban
SELECT *
FROM T1, T2
WHERE T1.kulcs = T2.kulcs
Összekapcsolás a "FROM" záradékban
SELECT *
FROM T1 INNER JOIN T2 ON T1.kulcs = T2.kulcs
Több tábla összekapcsolása
SELECT *
FROM T1, T2, T3
WHERE T1.ka = T2.ka AND T2.kb = T3.kb

SELECT *
FROM (T1 INNER JOIN T2 ON T1.ka=T2.ka) INNER JOIN  T3 ON T2.kb=T3.kb
Külső összekapcsolás
Asszimetrikus, lehet jobb (RIGHT) vagy bal (LEFT).
SELECT *
FROM T1 LEFT OUTER JOIN T2 ON T1.kulcs=T2.kulcs
Ha a T1 tábla adott rekordjához nincs "pár" T2-ben, akkor is bekerül az eredmény táblába, és T2 mezői "NULL" értéket kapnak.

Allekérdezések

Példák

SELECT név
FROM emberek
WHERE város = (SELECT város FROM emberek WHERE név="Jeffrey D. Ullman")


SELECT név
FROM dolgozók
WHERE fizetés < ALL (SELECT fizetés FROM dolgozók WHERE szakma="tanár")

Halmazműveletek

Unió

Az unió műveletével különböző táblákból kinyert adatokat egyesíthetünk egy közös választáblában.

SELECT név
FROM 11C
WHERE matematika_jegy >= 4

UNION

SELECT név
FROM 11A
WHERE (magyar_jegy >= 4) AND (történelem_jegy >= 4)

Metszet


Különbség

Adattípusok

Ezen a lapon néhány elterjedt adatbázis-kezelő rendszer adattípusait hasonlítjuk össze.

Számok

 Típus
 MYSQL
 Libre Office Base
 MS Office Access
 1 byte-os rövid egész
 TINYINT


 2 byte-os rövid egész
 SMALLINT


 3 byte-os egész
 MEDIUMINT


 4 byte-os egész
 INT / INTEGER


 8 byte-os hosszú egész
 BIGINT


 fixpontos törtek
 NUMERIC / DECIMAL


 szimpla lebegőpontos
 FLOAT


 dupla lebegőpontos
 DOUBLE


 bitek (max 64)
 BIT

Beszúrás

Új rekordok beszúrása táblába. Megadhatjuk az összes mező értékét, és az is lehet, hogy csak néhány mezőt töltünk ki.
INSERT INTO tábla
VALUES (érték1, érték2, ...)

INSERT INTO tábla (mező1, mező2, ...)
VALUES (érték1, érték2, ...)

Módosítás

Meglévő adatrekordok módosítása.
UPDATE tábla 
SET mező1 = kifejezés1, mező2 = kifejezés2, ...
WHERE feltétel

Törlés

Valamilyen feltételnek megfelelő sorokat törölhetünk egy táblából.
DELETE FROM tábla
WHERE feltétel
Tankönyvrendelés
A középiskolás diákok részére tanáraik minden év kora tavaszán megrendelik a következő évben használt tankönyveket. A rendeléshez  kapcsolódó adatokat adatbázisban rögzítik. Az adatbázis néhány évre vonatkozóan tartalmaz kitalált adatokat.

Feladatok
  1. Készítsen új adatbázist tkrendel néven! A mellékelt négy adattáblát (diak.txt, rendeles.txt, tk.txt, tkar.txt) importálja az adatbázisba a fájlnévvel azonos táblanéven (diak, rendeles, tk, tkar)! Az állományok tabulátorral tagolt, UTF-8 kódolású szövegfájlok, az első sorok a mezőneveket tartalmazzák. A létrehozás során minden táblában állítsa be a megfelelő típusokat, és jelölje meg a kulcsnak alkalmas mezőt!

    Táblák:

    diak     (az, nev, osztaly)
    az a diák azonosítója (szám), ez a kulcs
    nev a diák neve (szöveg); elképzelhető, hogy az iskolába névrokonok is járnak/jártak
    osztaly az osztály azonosítója (szöveg)

    rendeles (az, ev, tkaz, diakaz, ingyenes)
    az a rendelés azonosítója (szám), ez a kulcs
    ev a rendelés éve (szám)
    tkaz a rendelt könyv azonosítója (szám)
    diakaz a diák azonosítója (szám)
    ingyenes az adott könyvet a diák ingyenesen kéri (logikai), az ingyenes könyvekből nem származik bevétel

    tk (az, kiadoikod, cim, targy)
    az a tankönyv azonosítója (szám), ez a kulcs
    kiadoikod a tankönyvet az adott kiadónál azonosítja (szöveg). Az első két karakter a kiadót határozza meg (például a Neumann Könyvkiadónál NK), az utána következő karakterek pedig a kiadón belül különbözetik meg a könyveket
    cim a tankönyv címe (szöveg)
    targy a tankönyv ehhez a tantárgyhoz kapcsolódik (szöveg)

    tkar (ev, tkaz, ertek)
    ev a tankönyvár erre az évre érvényes (szám), adott évben csak az a könyv rendelhető, ami árat kapott
    tkaz a tankönyv azonosítója (szám) az ev mezővel összetett kulcsot alkot
    ertek a tankönyv ára az adott évben (szám)
A következő feladatok megoldásánál a lekérdezéseket és a jelentést a zárójelben olvasható néven mentse! Ügyeljen arra, hogy a megoldásban pontosan a kívánt mezők, kifejezések szerepeljenek, felesleges mezőt ne jelenítsen meg!
  1. Készítsen lekérdezést, amely megjeleníti, hogy mely tárgyak oktatásához használnak Neumann Könyvkiadós kiadványt! Ügyeljen arra, hogy minden tantárgy csak egyszer jelenjen meg! (2neumann)
  2. Az irodalom és a történelem tárgy tankönyveinek rendelése 2004 és 2006 között Tóth tanárnő feladata volt. Készítsen lekérdezést, amellyel meghatározza, hogy melyik volt a legdrágább kiadvány, aminek rendelését Tóth tanárnő intézte! Adja meg a könyv címét! (3leg)
  3. Készítsen lekérdezést, amellyel meghatározza a rendelések alapján az évenként ténylegesen befolyt összeget az esetleges ingyenességek figyelembevételével! (4evente)
  4. Készítsen lekérdezést, amely megadja azon könyvek címét és kiadói kódját, amelyekből egy darabot sem rendeltek! (5egysem)
  5. Készítsen lekérdezést, amely megadja azon könyvek címét, amelyek olcsóbbak voltak 2007-ben, mint 2006-ban! (6olcsobb)
  6. Készítsen lekérdezést, amely megadja, hogy az utolsó olyan évben, amelynek rendeléseiről rendelkezünk adatokkal, mely könyvekből rendeltek 50 darabnál többet! Jelenítse meg a könyv címét és a rendelés darabszámát! (7darab50)
  7. Készítsen jelentést, amely tantárgyanként csoportosítva jeleníti meg a 2005-ben rendelhető könyvek címét és árát! A tantárgy neve mellett jelenítse meg az ahhoz tartozó kiadványok számát! Ha a jelentést lekérdezésből készíti, akkor azt ugyanazon a néven mentse! (8ev2005)
  8. Meg kell állapítani, hogy melyik osztályból hányan vették igénybe az ingyenes tankönyveket. Készítse el a 9ingyenes lekérdezést vagy ideiglenes táblát, aminek segítségével az alábbi lekérdezés megadja a helyes választ! (9ingyenes)

    SELECT osztaly, count(diak) AS Db
    FROM 9ingyenes
    GROUP BY osztaly
    ORDER BY count(diak) DESC;


Bejegyezte: dátum:

Nincsenek megjegyzések:

Megjegyzés küldése

Feliratkozás: Megjegyzések küldése (Atom)