SQL Fügvények és GROUP BY
Egy kis ismétlés
avagy hogy nézz ki egy query
http://sqlfiddle.com/#!9/34a9810/11/0
SELECT FirstName, LastName <- szelekció
FROM Persons <- honnan (melyik táblából)
WHERE DateOfBirth>'1990-01-01' <- szűrés
ORDER BY FirstName <- sorrendezés
; <- pontos vessző a lezárás
egyéb Query tulajdonságok:
SELECT 1+2;
SELECT 'Ez egy String'; <- Fontos a String mindig idézőjel/apostroph
SELECT Ez Nem; közt van.
SELECT FirstName FROM Persons WHERE DateOfBirth>'1998-01-01';
SELECT UPPER(FirstName) FROM Persons WHERE DateOfBirth>'1998-01-01';
Kiíratást módosító függvények
Szűrést segítő függvények
SELECT count(1) FROM Mentesek WHERE gyepi='Kisvárda';
SELECT count(1) FROM Mentesek WHERE gyepi LIKE 'Kisvárda%';
STRING Függvények
CHAR_LENGTH(string) - Egy adott string karaktereinek számát adja vissza
SELECT CHAR_LENGTH("Test String");
LENGTH(string) - Egy adott string hosszát adja meg (bitenként)
SELECT LENGTH("Test String");
CONCAT(str1,str2,...) - Stringeket lehet egy stringgé alakítani
SELECT CONCAT(LastName, " " , FirstName) From Peoples;
LEFT(string,meddig) - Adott karakter számnál a Stringet elvágja balról
SELECT LEFT(DateTime,10) as date FROM invoice;
RIGHT(string,meddig) - Adott karakter számnál a Stringet elvágja jobbról
SELECT RIGHT(DateOfBirth, 5) as birthday FROM Persons;
MID(string, mettől,meddig) - Középen vágja el az adott stringet, adott karaktertől, adott számig
SELECT MID(ZIP,2,2) FROM address;
REPLACE(String,mit,mire) - adott STRING-et lecseréli másik STRING-re
SELECT REPLACE("This Text Generated by Human","Human","AI")
LOWER(String) - String-et kisbetüssé tesz
SELECT LOWER('THIS text going TO BE SMALL Caps');
UPPER(String) - Stringet-et nagybetüssé tesz (Kiabálhatsz :D )
SELECT UPPER('everything is BIG caps');
SUBSTRING_INDEX(string,keresés,melyik irány) - adott kifejezésnél vágja le a stringet, megadható hanyadik találatnál (1 az elötte, -1 az utánna)
SELECT SUBSTRING_INDEX("www.w3schools.com", ".", 1);
LIKE '%%' - Stringben keres, szűrésnél használjuk, kis-nagy betűre nem érzékeny, a %-jelel adjuk meg, hogy lehet előtte több karater is
SELECT Email FROM People WHERE Email LIKE '%@gmail.%';
SZÁM(NUMERIC) alapú fügvények
Count(melyik oszlop) - Sorokat számolja össze
SELECT Count(1) FROM Mentesek;
SUM(szám/oszlop) - Összeadja a megadott oszlopok értékét
SELECT SUM(price) FROM Invoice;
ABS(szám/oszlop) - Abszolút érték képzés
SELECT SUM(ABS(price)) as return_price FROM Invoice WHERE price<0;
AVG(szám/oszlop) - Átlag érték képzés
SELECT AVG(Temperature) FROM daily_forecast WHERE month='03';
MAX(szám/oszlop) - Legnagyobb érték kiválasztása
SELECT MAX(Temperature) FROM daily_forecast WHERE month='03';
MIN(szám/oszlop) - legalacsonyabb érték kiválasztása
SELECT MIN(Temperature) FROM daily_forecast WHERE month='03';
DÁTUM alapú fügvények
NOW() - Jelenlegi dátumot és időt adja vissza (server idő)
SELECT NOW();
CURRENT_TIMESTAMP() - ugyanaz, csak hosszabban :D
ADDDATE(dátum,amit hozzá adsz) - Dátumhoz hozzá adás, pl napokat
SELECT ADDDATE(NOW(), INTERVAL 5 day);
ADDTIME(dátum, amit hozzá adsz) - Ugyanaz, csak időt is lehet pl percet/órát és nem kell az interval
SELECT ADDTIME(NOW(), "03:00:00");
DATEDIFF(amiből kivonod, amit kivonsz) - dátum kivonás, pl mennyi ideje van egy kutyi idinél
SELECT DATEDIFF(NOW(),erkezes) FROM mentes_stat;
TIMEDIFF(amiből kivonod, amit kivonsz) - ugyanaz, csak idővel
YEAR(dátum) - Csak az év részét veszi egy dátumnak
SELECT YEAR(date_of_birth) FROM PEOPLE;
MONTH(dátum) - ugyanaz, hónappal
DAY(dátum) - ugyanaz nappal, sőt még van HOUR,MINUTE,SECOND is
Egyéb fügvények
IN(felsorolás) - szűrési feltétel, a zárojelben megadott értékek közül kell megegyeznie
SELECT ProductID,ProductName,Price FROM Products WHERE ProductID IN(22,44,55);
DISTINCT(oszlop) - group by egy oszlopos megfelelője azaz egy oszlopos csoportosítás
SELECT DISTINCT(gyepi) FROM Mentesek; SELECT gyepi FROM Mentesek group by gyepi;
IF(feltétel,igaz,hamis) - Feltételes megfeletetés, például bináris érték szövegessé tétele
SELECT IF (ivartalanitas=1,"ivartalan","ivaros") from mentes_stat;
LIMIT hanyadik sortól, mennyi sort- lekérdezés sorok számát lehet limitálni
SELECT * FROM Mentesek LIMIT 20,30
ASC,DESC - ORDER BY sorrendjének megadása, ASC növekvő, DESC csökkenő
SELECT LastName,FirstName FROM gazdik ORDER BY LastName ASC;
SELECT ProductName,Price FROM Products ORDER BY Price DESC;
NOT - tagadás,
SELECT varos FROM gazdik WHERE varos NOT IN('Budapest');
SELECT id FROM Products WHERE id NOT IN(11,22,33);
NULL - null érték
SELECT mfoek_nev FROM Mentesek WHERE gazdi is NULL;
SELECT mfoek_nev FROM Mentesek WHERE gazdi is NOT NULL;
AS - átnevezheted az oszlopot táblát
SELECT COUNT(pupID) AS mentveny_szam FROM Mentesek;
GROUP BY
csoportosítás oszlopok szerint
A GROUP BY kifejezés olyan oszlopokat csoportosít, melynek azonos az értékük
Például, ha arra vagyunk kiváncsiak hány mentvény lakik különböző városokban, akkor város szerint csoportosíthatunk
SELECT varos,count(1) FROM gazdik GROUP BY varos;
GROUP BY HAVING
A Having-gel egy újabb szűrési feltételt tudunk felvenni, például, ha email szerinti duplikációt keresünk, vagy csak azon városokat ahol több mint 2 kutya lakik
SELECT email,count(1) FROM Persons GROUP BY email HAVING count(email)>1;
SELECT varos,count(1) FROM gazdik GROUP BY varos HAVING count(varos)>2;
GYAKORLÁS
Gyakorló felület elérhetőség:
https://phpmyadmin.freedb.tech/
GYAKORLÓ FELADATOK
1) Írjuk ki a koordit, és mentvény MFÖEK neveket, a Mentések táblából ahol Kitti, Kati és Zsófi volt a koordi (IN -re lesz szükség)
2) írjuk ki a Mentesek táblából az mföek_nevek-et név hossz szerinti sorrendben (LENGTH-re lehet szükség)
3) számoljuk meg 2023-ban hány mentvény gazdisodott
(LEFT-re lehet szükség, meg count-ra)
4) mennyi a legtöbb nap a gazdisodásig (Max-ra lehet szükség), mennyi a legkevesebb(MIN-re :) )
5) Hány gazdinak van gmail-es email címe(SUBRTING_INDEX)
6) Budapesti mentvények melyik kerületben hányan laknak (MID)
7) Hány 2023-as mentvényünk van eddig
8) hány állatkánk ivartalanított
9) kérd le a 100. sortól 20 sort, kiírva a mentvény mföek_nevét és betüvel kiírva, hogy ivaros vagy ivartalanított
GYAKORLÓ FELADATOK
10) Kik azok a mentvények akiknek több mint 60 nap volt a gazdisodásig
11) melyik idi hányszor idizett, sorrendbe rendezve a legtöbb idizéstől
12) Melyik koordinak hány koordináltja volt?
13) van-e gazdink több mentvénnyel?
MEGOLDÁSOK
1) Select koordi,mfoek_nev FROM Mentesek where koordi in('Fa Kitti','Buzás Kati','Puzder Zsófia');
2) select mfoek_nev,LENGTH(mfoek_nev) FROM Mentesek order by LENGTH(mfoek_nev);
3) select count(1) from Mentesek where gazdis_datum like '2023%';
4) select Max(nalunk_toltot_nap),Min(nalunk_toltot_nap) from Mentesek;
5) select count(1) from gazdik where SUBSTRING_INDEX(email,'@',-1) = 'gmail.com';
6) select MID(ZIP,2,2),count(1) from gazdik where varos='Budapest' group by 1;
7) select count(1) from mentes_stat where erkezes like '2023%';
8) select count(1) from mentes_stat where ivartalanitas=1;
9) select mentveny_name,IF(ivartalanitas=1,"ivartalan","ivaros") FROM mentes_stat limit 100,20;
MEGOLDÁSOK
10) select mfoek_nev,nalunk_toltot_nap from Mentesek where nalunk_toltot_nap>60;
11) select idi,count(1) from Mentesek group by 1 order by 2 desc;
12) select koordi,count(1) from Mentesek group by 1;
13) select gazdi_nev from gazdik group by gazdi_nev having count(gazdi_nev)>1;
Vége
Házi Feladat (nem kötelező)
(lépj be a phpmyadmin-ba)
1) Sorold fel az összes mföek mentvényt akinek Z betüvel kezdődik az mföek neve(LIKE)
2) hányan gazdisodtak 2022-ben?(COUNT)
3) Budapesten kívül melyik városban lakik a legtöbb gazdis mentvény?(NOT IN)
(privátban kérem a queryket )
Felhasznált irodalom:
https://www.w3schools.com/mysql/mysql_ref_functions.asp
deck
By Gábor Opitzer
