Kirjautuminen

Haku

Tehtävät

Oppaat: MySQL ja PHP: Osa 4 - Hakukyselyt II

  1. Osa 1 - Johdanto
  2. Osa 2 - PHP ja PDO
  3. Osa 3 - Hakukyselyt I
  4. Osa 4 - Hakukyselyt II
  5. Osa 5 - Hakukyselyt III
  6. Osa 6 - Lisäys, muutos ja poisto
  7. Osa 7 - Tietotyypit
  8. Osa 8 - Tietokannan suunnittelu
  9. Osa 9 - Monta taulua
  10. Osa 10 - Lisätietoa
  11. Liite 1 - MySQL:n komentorivityökalu
  12. Liite 2 - phpMyAdmin-sovellus

Kirjoittaja: Antti Laaksonen. Vuosi: 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.

Esimerkkitaulu

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 |
+----+----------+-------+

Toistuvat rivit

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 |
+-------+

Rivimäärän rajoitus

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

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 |
+------------+------------+

Ryhmittely

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    |
+-------+-----------+

Ryhmittelyn ehdot

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 kentät

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 |
+-------+-------+

Kirjoita kommentti

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.

Muista lukea keskustelun ohjeet.
Tietoa sivustosta