Kirjoittaja: Antti Laaksonen (2009).
Tämä opas jatkaa SELECT-kyselyn esittelyä. Aiheita ovat toistuvien rivien poistaminen, rivimäärän rajoitus, yhteenvetokyselyt (esim. kuinka monta riviä taulussa on), ryhmittelyt (esim. kuinka monta kertaa kukin kentän arvo esiintyy taulussa) ja tulostaulun kenttien nimeäminen.
Lisätään esimerkkitauluun muutama rivi lisää:
INSERT INTO tuotteet (nimi, hinta) VALUES ('nauris', 4);
INSERT INTO tuotteet (nimi, hinta) VALUES ('purjo', 2);Nyt taulussa ovat kaikkiaan seuraavat rivit:
SELECT * FROM tuotteet;
+----+----------+-------+ | id | nimi | hinta | +----+----------+-------+ | 1 | kaali | 3 | | 2 | porkkana | 2 | | 3 | peruna | 3 | | 4 | retiisi | 4 | | 5 | lanttu | 1 | | 6 | selleri | 3 | | 7 | nauris | 4 | | 8 | purjo | 2 | +----+----------+-------+
Jos kentän arvo kahdella rivillä on sama, tulostaulussa voi olla monta kertaa sama rivi:
SELECT hinta FROM tuotteet;
+-------+ | hinta | +-------+ | 3 | | 2 | | 3 | | 4 | | 1 | | 3 | | 4 | | 2 | +-------+
Merkintä DISTINCT poistaa toistuvat rivit tulostaulusta:
SELECT DISTINCT hinta FROM tuotteet;
+-------+ | hinta | +-------+ | 3 | | 2 | | 4 | | 1 | +-------+
Merkintä LIMIT rajoittaa tulostaulun rivien määrää. Tästä on hyötyä esimerkiksi tilanteissa, joissa haun tuottamat tiedot täytyy jakaa monelle sivulle.
Seuraava kysely valitsee neljä ensimmäistä riviä:
SELECT * FROM tuotteet LIMIT 4;
+----+----------+-------+ | id | nimi | hinta | +----+----------+-------+ | 1 | kaali | 3 | | 2 | porkkana | 2 | | 3 | peruna | 3 | | 4 | retiisi | 4 | +----+----------+-------+
Seuraava kysely valitsee kaksi riviä neljännestä rivistä alkaen:
SELECT * FROM tuotteet LIMIT 3, 2;
+----+---------+-------+ | id | nimi | hinta | +----+---------+-------+ | 4 | retiisi | 4 | | 5 | lanttu | 1 | +----+---------+-------+
Rivien numerointi alkaa nollasta, mikä selittää kyselyssä olevan luvun 3.
Yhteenvetokyselyt tuottavat tulokseksi vain yhden lukuarvon, joka lasketaan kaikkien hakuehtoa vastaavien rivien perusteella. Tärkeimmät kyselyt ovat COUNT (lukumäärä), SUM (summa), AVG (keskiarvo), MAX (suurin arvo) ja MIN (pienin arvo).
Seuraava kysely laskee, kuinka monta tuotetta on yhteensä:
SELECT COUNT(*) FROM tuotteet;
+----------+ | COUNT(*) | +----------+ | 8 | +----------+
Seuraava kysely laskee, kuinka monen tuotteen hinta on 4:
SELECT COUNT(*) FROM tuotteet WHERE hinta = 4;
+----------+ | COUNT(*) | +----------+ | 2 | +----------+
Seuraava kysely laskee, kuinka monta eri hintaa on:
SELECT COUNT(DISTINCT hinta) FROM tuotteet;
+-----------------------+ | COUNT(DISTINCT hinta) | +-----------------------+ | 4 | +-----------------------+
Seuraava kysely laskee, paljonko maksaa ostaa peruna ja retiisi:
SELECT SUM(hinta) FROM tuotteet WHERE nimi IN ('peruna', 'retiisi');+------------+ | SUM(hinta) | +------------+ | 7 | +------------+
Seuraava kysely etsii halvimman ja kalleimman hinnan:
SELECT MIN(hinta), MAX(hinta) FROM tuotteet;
+------------+------------+ | MIN(hinta) | MAX(hinta) | +------------+------------+ | 1 | 4 | +------------+------------+
Merkintä GROUP BY yhdistää samaan ryhmään rivit, joilla on samat arvot annetuissa kentissä. Merkintää voi käyttää samalla tavalla kuin merkintää DISTINCT, mutta lisäksi sen yhteydessä voi käyttää yhteenvetokyselyjä.
Seuraava kysely ryhmittelee rivit hinnan mukaan:
SELECT hinta FROM tuotteet GROUP BY hinta;
+-------+ | hinta | +-------+ | 1 | | 2 | | 3 | | 4 | +-------+
Seuraava kysely kertoo jokaisen hintaryhmän tuotteiden määrän:
SELECT hinta, COUNT(*) FROM tuotteet GROUP BY hinta;
+-------+----------+ | hinta | COUNT(*) | +-------+----------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 2 | +-------+----------+
Seuraava kysely toimii muuten samoin kuin edellinen, mutta se ottaa huomioon vain tuotteet, joiden nimi alkaa p-kirjaimella:
SELECT hinta, COUNT(*) FROM tuotteet WHERE nimi LIKE 'p%' GROUP BY hinta;
+-------+----------+ | hinta | COUNT(*) | +-------+----------+ | 2 | 2 | | 3 | 1 | +-------+----------+
Seuraava kysely kertoo jokaisen hintaryhmän aakkosissa ensimmäisen tuotteen:
SELECT hinta, MIN(nimi) FROM tuotteet GROUP BY hinta;
+-------+-----------+ | hinta | MIN(nimi) | +-------+-----------+ | 1 | lanttu | | 2 | porkkana | | 3 | kaali | | 4 | nauris | +-------+-----------+
Merkinnän HAVING avulla voi asettaa lisäehtoja, mitkä ryhmitellyt rivit otetaan mukaan tulostauluun.
Seuraava kysely hakee hintaryhmät, joissa on ainakin kaksi tuotetta:
SELECT hinta, COUNT(*) FROM tuotteet GROUP BY hinta HAVING COUNT(*) >= 2;
+-------+----------+ | hinta | COUNT(*) | +-------+----------+ | 2 | 2 | | 3 | 3 | | 4 | 2 | +-------+----------+
Seuraava kysely hakee hintaryhmät, joissa aakkosissa ensimmäinen tuote sisältää k-kirjaimen:
SELECT hinta, MIN(nimi) FROM tuotteet GROUP BY hinta HAVING MIN(nimi) LIKE '%k%';
+-------+-----------+ | hinta | MIN(nimi) | +-------+-----------+ | 2 | porkkana | | 3 | kaali | +-------+-----------+
Tulostaulun kentille voi antaa uusia nimiä AS-merkinnän avulla:
SELECT MIN(hinta) AS halvin FROM tuotteet;
+--------+ | halvin | +--------+ | 1 | +--------+
Kenttien uudet nimet selventävät tulostaulua:
SELECT hinta AS ryhma, COUNT(*) AS maara FROM tuotteet GROUP BY hinta;
+-------+-------+ | ryhma | maara | +-------+-------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 2 | +-------+-------+
Minulla tuli vastaan tilanne, jossa tarvitsin käyttäjän jolla on eniten loki-merkintöjä tietokannassa. Loogisesti hakukysely olisi
SELECT userid, MAX(COUNT(*)) FROM userlogs GROUP BY userid
Valitettavasti yhteenvetokyselyitä ei voi käyttää sisäkkäin, joten haluttu tulos pitää saada ulos eri tavalla. Tämä onnistuu ryhmittelemällä loki-merkinnät käyttäjän id:n mukaan, niin kuin aikaisemmassa ei-toimivassa esimerkissäkin järjestelemällä nämä tulokset suuruusjärjestykseen ja rajoittamalla tulostaulun rivit yhteen. Näin saat halutessasi myös enemmän tuloksia, esimerkiksi 5 eniten loki-merkintöjä omaavaa käyttäjää.
SELECT userid, COUNT(*) AS logs FROM userlogs GROUP BY userid ORDER BY logs DESC LIMIT 1 -- tai vaikka LIMIT 5
Huomio! Kommentoi tässä ainoastaan tämän oppaan hyviä ja huonoja puolia. Älä kirjoita muita kysymyksiä tähän. Jos koodisi ei toimi tai tarvitset muuten vain apua ohjelmoinnissa, lähetä viesti keskusteluun.