Oppaat: MySQL ja PHP: Osa 6 - Lisäys, muutos ja poisto

Tässä oppaassa tutustumme tarkemmin kyselyihin INSERT (rivin lisäys), UPDATE (rivin muutos) ja DELETE (rivin poisto). Näissä kyselyissä voi käyttää luontevasti monia samoja merkintöjä kuin kyselyssä SELECT.

Esimerkkitaulu

Seuraava taulu tulokset on tarkoitettu nettisivulla olevan pelin tuloslistan tallennukseen. Taulussa kenttä nimi on pelaajan nimi ja kenttä tulos on hänen saamansa tulos.

CREATE TABLE tulokset (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nimi TEXT,
    tulos INT
);

Rivien lisäys

Tähän asti uuden rivin lisäykseen on käytetty seuraavaa kyselyä:

INSERT INTO tulokset (nimi, tulos) VALUES ('Pekka', 35);

Vaihtoehtoinen tapa kirjoittaa äskeinen kysely on tässä:

INSERT INTO tulokset SET nimi = 'Pekka', tulos = 35;

Jos rivejä lisätään samalla kerralla useampia, kyselyn voi muodostaa näin:

INSERT INTO tulokset (nimi, tulos)
VALUES ('Leena', 20),
       ('Matti', 30),
       ('Liisa', 40);

Näiden kyselyiden jälkeen taulussa on seuraavat rivit:

+----+-------+-------+
| id | nimi  | tulos |
+----+-------+-------+
|  1 | Pekka |    35 |
|  2 | Leena |    20 |
|  3 | Matti |    30 |
|  4 | Liisa |    40 |
+----+-------+-------+

Rivien kopiointi

Kyselyllä INSERT voi myös kopioida rivejä taulusta toiseen. Seuraavassa luodaan taulu parhaat ja kopioidaan siihen taulusta tulokset rivit, joissa tulos on yli 30:

CREATE TABLE parhaat (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nimi TEXT,
    tulos INT
);

INSERT INTO parhaat (nimi, tulos)
SELECT nimi, tulos FROM tulokset WHERE tulos > 30;

Tämän jälkeen taulu parhaat sisältää seuraavat rivit:

+----+-------+-------+
| id | nimi  | tulos |
+----+-------+-------+
|  1 | Pekka |    35 |
|  2 | Liisa |    40 |
+----+-------+-------+

Rivien muutos

Kyselyssä UPDATE muutettavat rivit voi valita samaan tapaan kuin kyselyssä SELECT valitaan haettavat rivit. Rivin kenttien uudet arvot voi määrittää niiden vanhojen arvojen perusteella.

Tehdään seuraavaksi joukko muutoksia tähän tauluun:

+----+-------+-------+
| id | nimi  | tulos |
+----+-------+-------+
|  1 | Pekka |    35 |
|  2 | Leena |    20 |
|  3 | Matti |    30 |
|  4 | Liisa |    40 |
+----+-------+-------+

Seuraava kysely muuttaa Matin tulokseksi 50:

UPDATE tulokset SET tulos = 50 WHERE nimi = 'Matti';

+----+-------+-------+
| id | nimi  | tulos |
+----+-------+-------+
|  1 | Pekka |    35 |
|  2 | Leena |    20 |
|  3 | Matti |    50 |
|  4 | Liisa |    40 |
+----+-------+-------+

Seuraava kysely muuttaa nimeksi Nimetön, jos tulos on alle 40:

UPDATE tulokset SET nimi = 'Nimetön' WHERE tulos < 40;

+----+---------+-------+
| id | nimi    | tulos |
+----+---------+-------+
|  1 | Nimetön |    35 |
|  2 | Nimetön |    20 |
|  3 | Matti   |    50 |
|  4 | Liisa   |    40 |
+----+---------+-------+

Seuraava kysely kaksinkertaistaa kaikki tulokset:

UPDATE tulokset SET tulos = tulos * 2;

+----+---------+-------+
| id | nimi    | tulos |
+----+---------+-------+
|  1 | Nimetön |    70 |
|  2 | Nimetön |    40 |
|  3 | Matti   |   100 |
|  4 | Liisa   |    80 |
+----+---------+-------+

Seuraava kysely kasvattaa Liisan tulosta viidellä:

UPDATE tulokset SET tulos = tulos + 5 WHERE nimi = 'Liisa';

+----+---------+-------+
| id | nimi    | tulos |
+----+---------+-------+
|  1 | Nimetön |    70 |
|  2 | Nimetön |    40 |
|  3 | Matti   |   100 |
|  4 | Liisa   |    85 |
+----+---------+-------+

Rivien poisto

Kyselyssä DELETE poistettavat rivit valitaan samoin kuin kyselyissä SELECT ja UPDATE.

Seuraava kysely poistaa rivit, joissa nimi on Nimetön:

DELETE FROM tulokset WHERE nimi = 'Nimetön';

+----+-------+-------+
| id | nimi  | tulos |
+----+-------+-------+
|  3 | Matti |   100 |
|  4 | Liisa |    85 |
+----+-------+-------+

Seuraava kysely poistaa rivit, joissa tulos on jaollinen 10:llä:

DELETE FROM tulokset WHERE tulos % 10 = 0;

+----+-------+-------+
| id | nimi  | tulos |
+----+-------+-------+
|  4 | Liisa |    85 |
+----+-------+-------+

Varoitus

Jos kyselyissä UPDATE ja DELETE ei määritä ehtoa, kaikkia rivejä muutetaan tai kaikki rivit poistetaan. Samoin jos ehto on väärä, vääriä rivejä muutetaan tai väärät rivit poistetaan. Tämän vuoksi näitä kyselyjä käyttäessä täytyy olla varovainen, jottei taulusta katoa tietoa.

Merkinnällä LIMIT voi rajoittaa muutettavien tai poistettavien rivien määrää. Seuraava kysely poistaa aina korkeintaan kaksi riviä, vaikka kyselyssä ei ole ehtoa:

DELETE FROM tulokset LIMIT 2;

Poisto vai muutos?

Taulun tietojen poistoon on kaksi lähestymistapaa: joko tiedot poistetaan aidosti käyttäen DELETE-kyselyä tai sitten tiedot vain merkitään poistetuiksi UPDATE-kyselyllä.

Jälkimmäisessä tapauksessa tauluun lisätään uusi kenttä poisto, jonka arvo on aluksi 0, mutta jos rivi poistetaan, kentän arvoksi muutetaan 1. Lisäksi aina kun taulusta haetaan tietoa, mukaan valitaan vain rivit, joissa kentän poisto arvo on 0. Tämän menettelyn etuna on, että poistetut tiedot on tarvittaessa helppoa palauttaa muuttamalla kentän poisto arvoksi taas 0.