Gyakorlatok CDTárra
Kapcsolódj a szerver cd_catalog adatbázisához!
Milyen táblák vannak az adatbázisban?
SHOW TABLES;
+-----------------+
| Tables_in_cdtar |
+-----------------+
| albumok |
| eloadok |
| szamok |
+-----------------+
Milyen szerkezetűek ezek?
DESC eloadok;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| nev | varchar(64) | NO | | NULL | |
| alapitva | int(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
DESC albumok;
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| album | varchar(255) | NO | | NULL | |
| eloado_id | int(11) | NO | MUL | NULL | |
+-----------+--------------+------+-----+---------+----------------+
DESC szamok;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| album_id | int(11) | NO | MUL | NULL | |
| sorszam | int(11) | NO | | NULL | |
| cim | varchar(64) | NO | | NULL | |
+----------+-------------+------+-----+---------+----------------+
Listázd az előadókat!
SELECT * FROM eloadok;
+----+-------------------+----------+
| id | nev | alapitva |
+----+-------------------+----------+
| 1 | Zámbó Jimmy | 1972 |
| 2 | Pendulum | 2002 |
| 3 | Prodigy | 1990 |
| 4 | Infected Mushroom | 1996 |
| 5 | Omega | 1968 |
+----+-------------------+----------+
Listázd a lemezeket!
SELECT * FROM albumok;
+----+------------------------+-----------+
| id | album | eloado_id |
+----+------------------------+-----------+
| 1 | Csak egy vallomás | 1 |
| 2 | Karácsony Jimmyvel | 1 |
| 3 | Hold Your Colour | 2 |
| 4 | Immersion | 2 |
| 5 | Everybody in the Place | 3 |
| 6 | Experience | 3 |
| 7 | Invaders Must Die | 3 |
| 8 | World's on Fire | 3 |
| 9 | Return to the Sauce | 4 |
| 10 | Gammapolisz | 5 |
+----+------------------------+-----------+
Hány szám van a gyűjteményünk CD-in?
SELECT count(id) AS db FROM szamok;
+-----+
| db |
+-----+
| 116 |
+-----+
Listázd a lemezeket és előadóit!
SELECT album, nev FROM eloadok, albumok WHERE albumok.eloado_id=eloadok.id;
+------------------------+-------------------+
| album | nev |
+------------------------+-------------------+
| Csak egy vallomás | Zámbó Jimmy |
| Karácsony Jimmyvel | Zámbó Jimmy |
| Hold Your Colour | Pendulum |
| Immersion | Pendulum |
| Everybody in the Place | Prodigy |
| Experience | Prodigy |
| Invaders Must Die | Prodigy |
| World's on Fire | Prodigy |
| Return to the Sauce | Infected Mushroom |
| Gammapolisz | Omega |
+------------------------+-------------------+
Megoldás LEFT JOIN-nal:
SELECT album, nev
FROM eloadok
LEFT JOIN albumok ON albumok.eloado_id=eloadok.id;
+------------------------+-------------------+
| album | nev |
+------------------------+-------------------+
| Csak egy vallomás | Zámbó Jimmy |
| Karácsony Jimmyvel | Zámbó Jimmy |
| Hold Your Colour | Pendulum |
| Immersion | Pendulum |
| Everybody in the Place | Prodigy |
| Experience | Prodigy |
| Invaders Must Die | Prodigy |
| World's on Fire | Prodigy |
| Return to the Sauce | Infected Mushroom |
| Gammapolisz | Omega |
+------------------------+-------------------+
Listázd a lemezeket és a rajtuk levő számokat!
SELECT albumok.album, szamok.cim FROM albumok, szamok WHERE szamok.album_id=albumok.id;
+------------------------+------------------------------------------------------+
| album | cim |
+------------------------+------------------------------------------------------+
| Csak egy vallomás | Csak Egy Vallomás |
| Csak egy vallomás | A Világ Lassan Útra Kész |
| Csak egy vallomás | Volt Egy Lány |
| Csak egy vallomás | Éjek Asszonya |
| Csak egy vallomás | Várlak |
| Csak egy vallomás | I Love You |
| Csak egy vallomás | Ave Maria |
| Csak egy vallomás | Gondolj Egy Régi Dalra |
| Csak egy vallomás | Dalolj Gitár |
| Csak egy vallomás | Kisfiam (Legszebb Könnyek) |
| Csak egy vallomás | Vándor Vagyok Ezen A Földön |
| Csak egy vallomás | Valahol Bús Dal Szól |
| Karácsony Jimmyvel | Csendes Éj |
| Karácsony Jimmyvel | Szent Karácsony Éjjel |
...
| Gammapolisz | A száműzött |
| Gammapolisz | Hajnal a város felett |
| Gammapolisz | Arc nélküli ember |
| Gammapolisz | Ezüst eső |
| Gammapolisz | Gammapolis II. |
+------------------------+------------------------------------------------------+
Hány száma van az egyes eloadoknak?
SELECT album_id FROM szamok;
+----------+
| album_id |
+----------+
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
...
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
+----------+
116 rows in set (0.000 sec)
SELECT count(album_id), album_id FROM szamok GROUP BY album_id;
+-----------------+----------+
| count(album_id) | album_id |
+-----------------+----------+
| 12 | 1 |
| 12 | 2 |
| 14 | 3 |
| 14 | 4 |
| 12 | 5 |
| 11 | 6 |
| 10 | 7 |
| 12 | 8 |
| 11 | 9 |
| 8 | 10 |
+-----------------+----------+
SELECT
count(album_id), album_id, albumok.album
FROM
szamok
LEFT JOIN albumok ON album_id=albumok.id
GROUP BY album_id;
+-----------------+----------+------------------------+
| count(album_id) | album_id | album |
+-----------------+----------+------------------------+
| 12 | 1 | Csak egy vallomás |
| 12 | 2 | Karácsony Jimmyvel |
| 14 | 3 | Hold Your Colour |
| 14 | 4 | Immersion |
| 12 | 5 | Everybody in the Place |
| 11 | 6 | Experience |
| 10 | 7 | Invaders Must Die |
| 12 | 8 | World's on Fire |
| 11 | 9 | Return to the Sauce |
| 8 | 10 | Gammapolisz |
+-----------------+----------+------------------------+
SELECT
count(album_id), albumok.album
FROM
szamok
LEFT JOIN albumok ON album_id=albumok.id
GROUP BY album_id;
+-----------------+------------------------+
| count(album_id) | album |
+-----------------+------------------------+
| 12 | Csak egy vallomás |
| 12 | Karácsony Jimmyvel |
| 14 | Hold Your Colour |
| 14 | Immersion |
| 12 | Everybody in the Place |
| 11 | Experience |
| 10 | Invaders Must Die |
| 12 | World's on Fire |
| 11 | Return to the Sauce |
| 8 | Gammapolisz |
+-----------------+------------------------+
SELECT
count(album_id) as db, albumok.album, eloadok.nev
FROM
szamok
LEFT JOIN albumok ON album_id=albumok.id
LEFT JOIN eloadok ON albumok.eloado_id=eloadok.id
GROUP BY album_id
ORDER BY nev, cim;
+----+------------------------+-------------------+
| db | album | nev |
+----+------------------------+-------------------+
| 11 | Return to the Sauce | Infected Mushroom |
| 8 | Gammapolisz | Omega |
| 14 | Immersion | Pendulum |
| 14 | Hold Your Colour | Pendulum |
| 12 | World's on Fire | Prodigy |
| 10 | Invaders Must Die | Prodigy |
| 12 | Everybody in the Place | Prodigy |
| 11 | Experience | Prodigy |
| 12 | Csak egy vallomás | Zámbó Jimmy |
| 12 | Karácsony Jimmyvel | Zámbó Jimmy |
+----+------------------------+-------------------+
Adatok bevitele
Adatok beszúrása az INSERT INTO SQL paranccsal történik:
INSERT INTO eloadok (nev, alapitva) VALUES ('Omega',1962);
Ellenőrizzük, hogy valóban rögzítettük-e:
SELECT * FROM eloadok;
Vegyünk fel a CD-k táblába egy Omega lemezt:
INSERT INTO albumok (album, eloado_id) VALUES ('Gammapolisz',5);
Majd vegyünk fel számokat is:
INSERT INTO szamok (sorszam, cim, album_id) VALUES (1, 'Start ', 10);
INSERT INTO szamok (sorszam, cim, album_id) VALUES (2, 'Nyári éjek asszonya', 10);
Egy Excel tükk
Nagy tömegű adat bevitelét lehetővé tevő INSERT INTO utasítások előállítására az Excel is használható. Az adatok:
Start – Gammapolis I.
Nyári éjek asszonya
Őrültek órája
A száműzött
Hajnal a város felett
Arc nélküli ember
Ezüst eső
Gammapolis II.
A végeredmény:
Az alkalmazott képlet:
="INSERT INTO szamok (sorszam, cim, album_id) VALUES (" & A3 & ", '" & B3 & "', 10);"
A kinyert SQL mondatok:
INSERT INTO szamok (sorszam, cim, album_id) VALUES (1, 'Start – Gammapolis I.', 10);
INSERT INTO szamok (sorszam, cim, album_id) VALUES (2, 'Nyári éjek asszonya', 10);
INSERT INTO szamok (sorszam, cim, album_id) VALUES (3, 'Őrültek órája', 10);
INSERT INTO szamok (sorszam, cim, album_id) VALUES (4, 'A száműzött', 10);
INSERT INTO szamok (sorszam, cim, album_id) VALUES (5, 'Hajnal a város felett', 10);
INSERT INTO szamok (sorszam, cim, album_id) VALUES (6, 'Arc nélküli ember', 10);
INSERT INTO szamok (sorszam, cim, album_id) VALUES (7, 'Ezüst eső', 10);
INSERT INTO szamok (sorszam, cim, album_id) VALUES (8, 'Gammapolis II.', 10);
Adatok módosítása
Adatok módosítását a UPDATE utasítással végezzük.
Módosítsuk a 120-as ID-jű szám címét Gammapolisz 3-ra!
UPDATE szamok SET cim='Gammapolisz 3' WHERE id=120;
Egy képzeletbeli dolgozók táblában adjunk 10%-os béremelést a 20 évnél fiatalabb nőknek!
UPDATE dolgozok SET ber = ber * 1.1 WHERE nem = 'no' AND kor < 20;
Ugyanebben a táblában a béremelés mellett adjunk 100.000 Ft jutalmat is!
UPDATE dolgozok SET ber = ber * 1.1, jutalom=100000 WHERE nem = 'no' AND kor < 20;
Adatok törlése
A törlés utasítása a DELETE, szintaxisa nagyon hasonló a SELECT-hez.
Töröljük a 120-as ID-jű számot!
DELETE FROM szamok WHERE id=120;
Gyakorlatok
Hány zenekar van most az adatbázisban?
SELECT count(nev) FROM eloadok;
Hány O-val kezdődő nevű van?
SELECT nev FROM eloadok WHERE nev like 'O%';
Listázd a eloadókat nevük szerint visszafelé rendezve!
SELECT nev FROM eloadok ORDER BY nev DESC;
A Legrégebben alapított zenekar neve (inkább a második, a beágyazott SELECT-et alkalmazó megoldást érdemes megtanulni):
SELECT nev FROM eloadok ORDER BY alapitva limit 1;
SELECT nev FROM eloadok WHERE alapitva=(SELECT min(alapitva) FROM eloadok);
Listázzuk a CD-ket és előadóikat!
SELECT album, nev
FROM albumok, eloadok
WHERE albumok.eloado_id = eloadok.id
ORDER BY nev;
Hány lemeze van az eloadóknak?
SELECT count(eloado_id), id
FROM albumok
GROUP BY eloado_id;
Ne zenekar id-t, hanem előadót írjuk ki:
SELECT count(eloado_id) AS darab, nev
FROM albumok, eloadok
WHERE albumok.eloado_id=eloadok.id
GROUP BY nev
ORDER BY darab;
Hány szám van az egyes lemezeken?
SELECT count(album_id), id FROM szamok GROUP BY album_id;
Lássuk ezt a lemezek címeivel!
SELECT
count(album_id), albumok.album
FROM szamok, albumok
WHERE szamok.album_id=albumok.id
GROUP BY album_id;
Jelenítsd meg az előadók nevei is!
SELECT
count(album_id) as darab, albumok.album, eloadok.nev
FROM szamok, albumok, eloadok
WHERE
szamok.album_id=albumok.id AND
eloadok.id=albumok.eloado_id
GROUP BY album_id
ORDER BY nev;
Hány számunk van egy-egy előadótól?
SELECT
count(album_id) as darab, eloadok.nev
FROM szamok, albumok, eloadok
WHERE
szamok.album_id=albumok.id AND
eloadok.id=albumok.eloado_id
GROUP BY eloadok.id
ORDER BY nev;