SQL tudnivalók 1. SELECT utasítás Adatok lekérdezésére szolgál. Meg kell benne adni, hogy SELECT
•
pontosan milyen oszlopokat szeretnénk az eredményül;
•
mely adatokból (mely táblá(k)ból),
FROM
•
milyen feltételeknek megfelelő rekordokat kérünk;
WHERE
•
az eredményt mi szerint rendezzük,
ORDER BY
•
és mi szerint csoportosítsuk (a csoportok közül melyekre vagyunk kíváncsiak)
GROUP BY HAVING
•
ha hosszú a lista, akkor az első hány elem kell.
LIMIT <max. ennyi darabot kérünk>
1.A. SELECT paraméterei 1.A.1. Egyszerű oszlop kiválasztás SELECT rendszam, tomeg FROM jarmu;
1.A.2. Számított oszlopok (+kerekítés, +oszlopnév) SELECT SELECT SELECT SELECT
tomeg, 2.205*tomeg FROM jarmu; tomeg, round(2.205*tomeg) FROM jarmu; tomeg, round(2.205*tomeg) AS "Tömeg_Font" FROM jarmu; tomeg, floor(2.205*tomeg+0.5) AS "Tömeg_Font", floor((tomeg+500)/1000) AS "Tömeg_Tonna" FROM jarmu; Felfelé kerekítés: SELECT tomeg, ceil(2.205*tomeg) AS "Tömeg_Font", FROM jarmu;
1.A.3. Egyforma adatok kiszűrése: DISTINCT Hányféle gyártó van? SELECT DISTINCT gyarto FROM jarmu; Hányféle szín van?
1.A.4. Szöveg hossza SELECT tomeg, gyarto, LENGTH(gyarto) from jarmu;
1.A.5. „SELECT számológép” SELECT (6*5+11)/7; SELECT 10^4, 10^3, 10^2, 10^1, 10^1; SELECT 10^3 AS ”Köbön”, 10^2 AS ”Négyzeten”;
1.B. ORDRER BY [ASC, DESC] 1.B.1. Listázás egy mező szerint szerint SELECT gyarto, tomeg FROM jarmu ORDER BY gyarto;
SELECT szin, gyarto, tomeg FROM jarmu ORDER BY szin;
1.B.2. Listázás csökkenő sorrendben SELECT gyarto, tomeg FROM jarmu
1
SELECT szin, gyarto, tomeg FROM jarmu
ORDER BY gyarto DESC;
ORDER BY szin ASC;
1.B.3. Listázás több szempont szerint SELECT szin, gyarto, tomeg FROM jarmu ORDER BY szin DESC, gyarto ASC;
SELECT szin, gyarto, tomeg FROM jarmu ORDER BY szin DESC, gyarto;
1.C. WHERE: 1.C.1. Mezőérték (szám, szöveg, dátum, logikai) vizsgálat: Összehasonlítás számmal: SELECT WHERE tomeg>1250; Összehasonlítás szöveggel: SELECT <select oszlopok> WHERE tipus='Opel'; Összehasonlítás aposztrófot tartalmazó szövegben: dupla aposztróf jelöli az aposztrófot SELECT <select oszlopok> WHERE gyarto='McDonald''s'; Összehasonlítás dátummal: SELECT <select oszlopok> WHERE forg_datum >= '2000-01-01'; Összehasonlítás logikai IGAZ értékkel: SELECT <select oszlopok> WHERE aut_valto=true; vagy: WHERE aut_valto; Összehasonlítás logikai HAMIS értékkel: SELECT <select oszlopok> WHERE aut_valto=false; vagy: WHERE aut_valto<>true; vagy: WHERE NOT aut_valto=true; vagy: WHERE NOT aut_valto;
1.C.2. Logikai kifejezések és precedenciájuk (AND, OR, NOT, zárójelek) SELECT <select oszlopok> FROM jarmu WHERE (forg_datum >= '2000-01-01' or aut_valto=false) AND szin='zold'; SELECT <select oszlopok> FROM jarmu WHERE (forg_datum >= '2000-01-01' or aut_valto=false) AND NOT szin='zold'; SELECT <select oszlopok> FROM jarmu WHERE tomeg > 1500 AND tomeg < 5000 OR NOT szin = 'kek';
1.C.3. Mintaillesztés mezőtartalomra Fix szöveg vagy bizonyos betűk keresése
2
Tartalmaz 'e' betűt: WHERE t_nev ~ 'e'
Tartalmaz 'e' vagy 'h' betűt: WHERE t_nev ~ '[eh]'
Tartalmazza a 'hegy' szócskát: WHERE t_nev ~ 'hegy'
Tartalmazza a 'hegy' vagy 'hely' szót: WHERE t_nev ~ 'he[lg]y'
Tartalmaz számjegyet: WHERE t_nev ~ '[0-9]'
Tartalmaz (kis vagy nagy) betűt: WHERE t_nev ~ '[A-Za-z]'
Van benne valahol betű után számjegy: WHERE t_nev ~ '[A-Za-z][0-9]'
Van betű után 6-nál kisebb számjegy: WHERE t_nev ~ '[A-Za-z][0-5]'
Van benne (kisbetűs) magánhangzó:
Tartalmaz (kis vagy nagy) T vagy K betűt vagy
WHERE t_nev ~ '[aáeéiíoóöőuúüű]' WHERE t_nev ~ '[aeiouáéíóúöőüű]'
7-nél kisebb számjegyet: WHERE t_nev ~ '[tTkK0-6]'
Van benne hexadecimális számjegy: WHERE t_nev ~ '[0123456789ABCDEF]'; WHERE t_nev ~ '[0-9A-F]';
Van benne (kisb.) magánhangzó vagy szám: WHERE t_nev ~ '[0-4aáeéiíoóöőuúüű5-9]' WHERE t_nev ~ '[0-9aáeéiíoóöőuúüű]' WHERE t_nev ~ '[0aá1eé2i3í4o5ó6ö7ő8u9úüű]'
A mező eleje vagy a vége jelzése Speciális jelzések: ^ (eleje jelzés) $ (vége velzés) Betűvel kezdődik: WHERE t_nev ~ '^[A-Za-z]'
Számmal kezdődik: WHERE t_nev ~ '[0-9]'
Kisbetűvel vagy számjeggyel végzdődik: WHERE t_nev ~ '[a-z0-9]$'
Számmal végződik: WHERE t_nev ~ '[0-9]$'
Csak 1 darab számjegyből áll: WHERE t_nev ~ '^[0-9]$'
Csak 2 darab betűből áll, melyből az első nagybetű, a második vagy nagy vagy kicsi: WHERE t_nev ~ '^[A-Z][A-Za-z]$'
Magyar rendszám (3 betű, 3 szám) WHERE t_nev ~ '^[A-Z][A-Z][A-Z][0-9][0-9][0-9]$'
Régi magyar rendszám (2 betű, 2 szám, kötőjel, 2 szám) WHERE t_nev ~ '^[A-Z][A-Z][0-9][0-9]-[0-9][0-9]$'
Ismétlődő minták keresése Speciális jelzések: * - Az előtte álló karakter (vagy kifejezés) 0 vagy több alkalommal ismétlődhet + - Az előtte álló karakter (vagy kifejezés) 1 vagy több alkalommal ismétlődhet {n,m} - Az előtte álló karakter (vagy kif.) legalább n, legfeljebb m-szer ismétlődik. Csak betűből áll (vagy üres): '^[A-Za-z]*$'
Csak betűből áll, de nem üres: '^[A-Za-z]+$'
Hexadecimális szám (nem lehet üres): '^[0-9A-F]+$'
Van benne legalább 30 „A” betű egymás mellett: 'A{30}'
Magyar telefonszámok: 06-kk/aaa-bbb; 06-kk/aaa-bbbb 06-kk/aaaa-bbb; 06-kk/aaaa-bbbb (a kk körzet lehet egyjegyű is): '06-[0-9]{1,2}/[0-9]{3-4}-[0-9]{3-4}'
Számokból és betűkből álló rendszámok,melyben az összes számjegy a betűk után áll: WHERE rendszam ~ '[A-Za-z]+[0-9]+'
Egymás után egynél többször előfordul benne a „ha” szócska: 'ha(ha)+' '(ha){2}'
Csak „ha” szócskákból áll (nem lehet üres): '^(ha)+$'
Vegyes Order by hivatkozhat általunk adott oszlopnévre: SELECT gyarto as ”Cég” FROM jarmu ORDER BY Cég; ??
1.D. GROUP BY Csoportosítás Milyen színű autók vannak? SELECT szin
3
FROM jarmuvek GROUP BY szin;
Aggregátum függvények (COUNT(), SUM(), AVG(), MIN(), MAX()) Hány autó van a táblában? SELECT COUNT(*) FROM jarmuvek Melyik színű autóból mennyi van? SELECT szin, COUNT(szin) FROM jarmuvek GROUP BY szin; Melyik színű autóból mennyi a tömege a legkönyebb és a legnehezebb autónak? SELECT szin, MIN(tomeg) AS ”legkönyebb”, MAX(tomeg) AS ”legnehezebb” FROM jarmuvek GROUP BY szin; Melyik színű autóból mennyi az átlagos tömeg? SELECT szin, AVG(tomeg) AS ”árlagsúly” FROM jarmuvek GROUP BY szin; Melyik gyártó milyen színű autókat gyárt? SELECT gyarto, szin FROM jarmuvek GROUP BY gyarto, szin Melyik gyártó milyen színű autókat gyárt, és hány darab van az adott gyártó adott színű kocsijából? SELECT gyarto, szin, COUNT(szin) FROM jarmuvek GROUP BY gyarto, szin Melyik gyártó milyen színű autókat gyárt, és mennyi az egyes gyártók autóinak átlagtömege (szinenként)? SELECT gyarto, szin, AVG(tomeg) FROM jarmuvek GROUP BY gyarto, szin
Csoportok szűrése (HAVING) Mennyi az egyforma színű autók átlagtömege, melyben az átlagtömeg 1500kg felett van? SELECT gyarto, szin, AVG(tomeg) FROM jarmuvek GROUP BY gyarto, szin HAVING AVG(tomeg)>1500
4
1.E. LIMIT, komment Melyik színű autóknak a legnagyobb az átlagtömege? SELECT szin, AVG(tomeg) AS ”Átlagsúly” FROM jarmuvek GROUP BY szin ORDER BY Átlagsúly DESC LIMIT 1; -- Ez itt egy megjegyzés a két mínusz után Melyik három gyártó autói a legönnyebbek átlagosan? SELECT gyarto, AVG(tomeg) AS ”Átlagsúly” FROM jarmuvek GROUP BY gyarto ORDER BY Átlagsúly LIMIT 3; -- Csak az első 3 rekord kell nekünk Melyik három gyártó autói a legönnyebbek átlagosan, és hány autója van? SELECT gyarto, AVG(tomeg) AS ”Átlagsúly”, COUNT(*) AS ”darabszám” FROM jarmuvek GROUP BY gyarto ORDER BY Átlagsúly LIMIT 3; -- Csak az első 3 rekord kell nekünk Melyik gyártónak van a legtöbb autója? SELECT gyarto, COUNT(*) AS ”darabszám” FROM jarmuvek GROUP BY gyarto ORDER BY COUNT(*) -- Ide írhatnánk „ORDER BY darabszám”-ot is! LIMIT 1; -- Csak az első rekord kell nekünk
1.F. Összetett lekérdezések Mennyi az automata váltós nem zöld autók átlagtömege? SELECT AVG(tomeg) FROM jarmu WHERE aut_valto AND NOT szin='zold' Mennyi az automata váltós nem zöld autók átlagtömege szinenként csoportosítva? SELECT szin, AVG(tomeg) FROM jarmu WHERE aut_valto AND NOT szin='zold' GROUP BY szin SELECT szin AS ”autószín”, AVG(tomeg) FROM jarmu WHERE aut_valto GROUP BY szin HAVING NOT autószín='zold' Mennyi az átlagtömege tonnában (matematikailag helyesen kerekítve) a 2000 utáni összes és a 2001 előtti automta váltós autóknak szinenkénti bontásban? SELECT floor((avg(tomeg)+500)/1000) FROM jarmu WHERE aut_valto or forg_hely_datum>='2000-01-01' GROUP BY szin HAVING NOT autószín='zold'
5
2. Többtáblás lekérdezések Adott két tábla: a rendelések (rend) és a termékek (termek) A rend tábla felépítése:
A termek tábla felépítése:
vnev | esem | cikkszam | db cikkszam | tnev | tegysarb | tafakulcs ----------+------+----------+--- ------------+-------------------------+----------+---------bm-17oof | R | ap-103 | 1 lx-0087 | Allons-Y 1 | 1943.004 | 5 bm-16oof | R | ms-1723 | 23 nt-13240 | Kémia II. (Pfeiffer) | 0 | 5 bm-15oof | R | ms-1510 | 27 nt-13106/5 | Földrajz I. | 0 | 5 bm-10asa | R | ms-2668 | 1 ko-0156 | Irodalom 12. | 0 | 5 bm-10asa | R | ms-2612 | 1 rp-502 | Irodalom | 966.9975 | 5 bm-10asa | R | ms-2668 | -1 rk-5021-06 | Deutsch mit Grips 2 Kass| 1908 | 25 vnev: vevőnév; esem: minden rendelésnél 'R'; cikkszam: rend. könyv azonosító; db: ennyi darabot rendelt
cikkszam: tnev: tegysarb: tafakulcs:
könyv cikkszáma; tankönyv neve; tankönyv bruttó egységára; tankönyv ÁFA-kulcsa
2.A. Két tábla összekapcsolása Milyen cikkszámú és mennyiségű rendelései vannak 'bm-10asa' jelű embernek? SELECT cikkszam, db FROM rend WHERE vnev='bm-10asa'; Milyen cikkszámú könyvekből összesen mennyit rendelt 'bm-10asa'? SELECT cikkszam, sum(db) FROM rend WHERE vnev='bm-10asa' GROUP BY cikkszam; Milyen cikkszámú könyvekből összesen mennyit rendelt 'bm-10asa'? (táblanév is kiírva) SELECT rend.cikkszam, sum(rend.db) FROM rend WHERE rend.vnev='bm-10asa' GROUP BY rend.cikkszam; Milyen cikkszámú könyvekből összesen mennyit rendelt 'bm-10asa', és mi a tankönyv neve? SELECT rend.cikkszam, termek.tnev, sum(rend.db) FROM rend, termek WHERE rend.vnev='bm-10asa') AND (termek.cikkszam=rend.cikkszam) GROUP BY rend.cikkszam; Milyen cikkszámú és nevű könyvekből hány forint értékben rendelt 'bm-10asa'? SELECT rend.cikkszam, termek.tnev, sum(rend.db*termek.tegysarb) FROM rend, termek WHERE rend.vnev='bm-10asa') AND (termek.cikkszam=rend.cikkszam) GROUP BY rend.cikkszam; Milyen nevű könyvekből hány forint értékben rendelt 'bm-10asa'? SELECT termek.tnev, sum(rend.db*termek.tegysarb) FROM rend, termek WHERE rend.vnev='bm-10asa') AND (termek.cikkszam=rend.cikkszam) GROUP BY rend.cikkszam; Milyen nevű könyvekből hány forint értékben rendelt 'bm-10asa'? Ne szerepeljen az a tétel, ahol összesen 0 rendelés történt (tehát rendelt 1-et, majd visszavonta (rendelt -1-et)) SELECT termek.tnev, sum(rend.db*termek.tegysarb) FROM rend, termek WHERE rend.vnev='bm-10asa') AND (termek.cikkszam=rend.cikkszam) GROUP BY rend.cikkszam HAVING sum(rend.db*termek.tegysarb)>0; A '10a' jelű osztályban melyik embernek mennyit kell fizetni? SELECT rend.vnev, sum(rend.db*termek.tegysarb) FROM rend, termek WHERE rend.vnev~'bm-10a.*') AND (termek.cikkszam=rend.cikkszam) GROUP BY rend.vnev;
6
A '10a' jelű osztályban melyik embernek mennyit kell fizetni? SELECT rend.vnev, sum(rend.db*termek.tegysarb) (0 összegű ember ne szerepeljen) FROM rend, termek WHERE rend.vnev ~ 'bm-10a.*') AND (termek.cikkszam=rend.cikkszam) GROUP BY rend.vnev HAVING sum(rend.db*termek.tegysarb)>0; Melyik könyvből (neve és cikkszáma is kell) összesen hány darabot rendeltek? SELECT termek.tnev, rend.cikkszam, sum(rend.db) FROM rend, termek WHERE termek.cikkszam=rend.cikkszam GROUP BY rend.cikkszam; Mi a 10 legtöbbet megrendelt könyveknek a neve és cikkszáma? SELECT termek.tnev, rend.cikkszam, sum(rend.db) FROM rend, termek WHERE termek.cikkszam=rend.cikkszam GROUP BY rend.cikkszam; ORDER BY sum(rend.db) DESC LIMIT 10; Melyik könyvekből (név és cikkszám) rendeltek csak 1 darabot? SELECT termek.tnev, rend.cikkszam, sum(rend.db) FROM rend, termek WHERE termek.cikkszam=rend.cikkszam GROUP BY rend.cikkszam HAVING sum(rend.db)=1;
2.B. ÁFA-számítás Ez nem kapcsolódik szorosan a témakörhöz, de a tankönyvrendeléssel kapcsolatos matematikai gyakorlatot és általános ismereteket nem árt tudni. Tegyük fel, hogy egy termék ára 100 Ft. Ez azt jelenti, hogy a termék netto ára 100 Ft. Viszont ha veszel egy terméket, akkor a vételáron felül adót kell fizetni az államnak. Azt, hogy mennyit, az ÁFAkulcs mondja meg. Pl. az ÁFA 25%, akkor az azt jelenti, hogy 125%
7
3. Tábla készítése CREATE TABLE sajatadatok ( gyarto char(20), forg_hely date, .. )
4. INSERT INSERT INTO jarmu (gyarto, tipus, tomeg, aut_valto, forg_hely) VALUES ('Opel', 'Vectra', 1456, false, '2004-12-12')
5. UPDATE Állítsuk be az ABC123 rendszámú autó tömegét 1250 kg-ra: UPDATE jarmu SET tomeg=1250 WHERE rendszam='ABC123'; Állítsuk be az összes 'Ford'-ot automata váltósra: UPDATE jarmu SET aut_valto=true WHERE gyarto='Ford';
6. DELETE Töröljük az ABC123 rendszámú autót: DELETE FROM jarmu WHERE rendszam='ABC123'; Töröljük az összes 2000 előtti nem automata váltós kocsit: DELETE FROM jarmu WHERE forg_hely<'2000-01-01' and not aut_valto; Töröljük az összes autót! DELETE FROM jarmu;
7. Grafikus felület Egyszerű, egytáblás lekérdezések Összetett egytáblás lekérdezések (group by) Többtáblás lekérdezések Űrlap készítés Jelentéskészítés
8
