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