Kirjautuminen

Haku

Tehtävät

Oppaat: MySQL ja PHP: Osa 5 - Hakukyselyt III

  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.

Tietokantakyselyssä voi käyttää laskutoimituksia ja funktioita varsin luonnollisella tavalla. Näin voi hakea vaikkapa kaikki käyttäjät, joiden nimen pituus on viisi kirjainta tai jotka ovat syntyneet tiistaina. Tässä oppaassa tutustumme lukuarvojen ja merkkijonojen käsittelyyn, ja päivämäärien käsittelyyn palaamme opassarjan 7. osassa.

Esimerkkitaulu

Aiempi esimerkkitaulu kelpaa tässä oppaassa sellaisenaan:

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

Lukuarvojen käsittely

Laskutoimitukset +, -, *, / ja % toimivat luonnollisella tavalla. Lisäksi käytössä on joukko matemaattisia funktioita, kuten ABS (itseisarvo), ROUND (pyöristys), POW (potenssilasku) ja SQRT (neliöjuuri).

Seuraava kysely hakee tuotteiden hinnat, kun niistä saa alennusta 25 %:

SELECT nimi, hinta, hinta * 0.75 AS tarjous FROM tuotteet;
+----------+-------+---------+
| nimi     | hinta | tarjous |
+----------+-------+---------+
| kaali    |     3 |    2.25 |
| porkkana |     2 |    1.50 |
| peruna   |     3 |    2.25 |
| retiisi  |     4 |    3.00 |
| lanttu   |     1 |    0.75 |
| selleri  |     3 |    2.25 |
| nauris   |     4 |    3.00 |
| purjo    |     2 |    1.50 |
+----------+-------+---------+

Seuraava kysely laskee kalleimman ja halvimman tuotteen hinnan erotuksen:

SELECT MAX(hinta) - MIN(hinta) AS erotus FROM tuotteet;
+--------+
| erotus |
+--------+
|      3 |
+--------+

Seuraava kysely hakee tuotteet, joita voi ostaa tusinan niin, että hinta on alle 25:

SELECT nimi FROM tuotteet WHERE hinta * 12 < 25;
+----------+
| nimi     |
+----------+
| porkkana |
| lanttu   |
| purjo    |
+----------+

Seuraava kysely muuttaa markkahintaiset tuotteet eurohintaisiksi ja pyöristää hinnat kahden desimaalin tarkkuudelle:

SELECT nimi, hinta AS markka, ROUND(hinta / 5.94573, 2) AS euro FROM tuotteet;
+----------+--------+------+
| nimi     | markka | euro |
+----------+--------+------+
| kaali    |      3 | 0.50 |
| porkkana |      2 | 0.34 |
| peruna   |      3 | 0.50 |
| retiisi  |      4 | 0.67 |
| lanttu   |      1 | 0.17 |
| selleri  |      3 | 0.50 |
| nauris   |      4 | 0.67 |
| purjo    |      2 | 0.34 |
+----------+--------+------+

Merkkijonojen käsittely

Merkkijonojen yhdistys tapahtuu funktiolla CONCAT. Muita merkkijonoihin liittyviä funktioita ovat mm. LENGTH (pituus), LEFT (alkuosan erotus), RIGHT (loppuosan erotus), MID (keskiosan erotus), INSTR (etsintä) ja REPLACE (korvaus).

Seuraava kysely hakee nimet ja lisää niiden ympärille kulmasulut:

SELECT CONCAT('<', nimi, '>') AS tagi FROM tuotteet;
+------------+
| tagi       |
+------------+
| <kaali>    |
| <porkkana> |
| <peruna>   |
| <retiisi>  |
| <lanttu>   |
| <selleri>  |
| <nauris>   |
| <purjo>    |
+------------+

Seuraava kysely hakee tuotteet, joiden nimessä on kuusi kirjainta:

SELECT nimi FROM tuotteet WHERE LENGTH(nimi) = 6;
+--------+
| nimi   |
+--------+
| peruna |
| lanttu |
| nauris |
+--------+

Seuraava kysely muodostaa tuotteille kaksikirjaimiset lyhenteet:

SELECT nimi, LEFT(nimi, 2) AS lyhenne FROM tuotteet;
+----------+---------+
| nimi     | lyhenne |
+----------+---------+
| kaali    | ka      |
| porkkana | po      |
| peruna   | pe      |
| retiisi  | re      |
| lanttu   | la      |
| selleri  | se      |
| nauris   | na      |
| purjo    | pu      |
+----------+---------+

Seuraava kysely hakee nimet, joiden neljäs ja viides kirjain ovat samat:

SELECT nimi FROM tuotteet WHERE MID(nimi, 4, 1) = MID(nimi, 5, 1);
+----------+
| nimi     |
+----------+
| porkkana |
| retiisi  |
| lanttu   |
+----------+

Seuraava kysely kertoo, missä kohdissa nimiä on ensimmäinen r-kirjain:

SELECT nimi, INSTR(nimi, 'r') AS kohta FROM tuotteet;
+----------+-------+
| nimi     | kohta |
+----------+-------+
| kaali    |     0 |
| porkkana |     3 |
| peruna   |     3 |
| retiisi  |     1 |
| lanttu   |     0 |
| selleri  |     6 |
| nauris   |     4 |
| purjo    |     3 |
+----------+-------+

Seuraava kysely hakee nimet, joissa on l-kirjain, ja muuttaa tulostaulussa l-kirjaimet r-kirjaimiksi:

SELECT REPLACE(nimi, 'l', 'r') AS uusi FROM tuotteet WHERE INSTR(nimi, 'l');
+---------+
| uusi    |
+---------+
| kaari   |
| ranttu  |
| serreri |
+---------+

Muita funktioita

Funktio IF valitsee vaihtoehdon sen mukaan, onko ehto tosi vai epätosi. Monimutkaisempiin valintoihin soveltuu rakenne CASE.

Seuraava kysely luokittelee tuotteet niiden hinnan mukaan:

SELECT nimi, IF(hinta <= 2, 'halpa', 'kallis') AS kuvaus FROM tuotteet;
+----------+--------+
| nimi     | kuvaus |
+----------+--------+
| kaali    | kallis |
| porkkana | halpa  |
| peruna   | kallis |
| retiisi  | kallis |
| lanttu   | halpa  |
| selleri  | kallis |
| nauris   | kallis |
| purjo    | halpa  |
+----------+--------+

Seuraava kysely antaa kommentin tuotteista:

SELECT nimi,
       CASE nimi WHEN 'nauris' THEN 'nam'
                 WHEN 'lanttu' THEN 'hyi'
                 ELSE 'ok' END AS tuomio
FROM tuotteet;
+----------+--------+
| nimi     | tuomio |
+----------+--------+
| kaali    | ok     |
| porkkana | ok     |
| peruna   | ok     |
| retiisi  | ok     |
| lanttu   | hyi    |
| selleri  | ok     |
| nauris   | nam    |
| purjo    | ok     |
+----------+--------+

Seuraava kysely mainitsee maistuvat ja halvat tuotteet:

SELECT nimi,
       CASE WHEN nimi = 'nauris' THEN 'nam'
            WHEN hinta <= 2 THEN 'halpa'
            ELSE 'huono' END AS tuomio
FROM tuotteet;
+----------+--------+
| nimi     | tuomio |
+----------+--------+
| kaali    | huono  |
| porkkana | halpa  |
| peruna   | huono  |
| retiisi  | huono  |
| lanttu   | halpa  |
| selleri  | huono  |
| nauris   | nam    |
| purjo    | halpa  |
+----------+--------+

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