Kirjoittaja: Antti Laaksonen (2009).
Tämä opassarja käsittelee MySQL-tietokannan käyttämistä PHP-ohjelmoijan näkökulmasta. Lukijan oletetaan tuntevan nettisivujen ja PHP:n perusteet, mutta tietokannoista ei tarvitse olla aiempaa kokemusta.
Tietokanta on järkevä tapa tallentaa, hakea ja muuttaa tietoa tehokkaasti. Tietokantaan on luontevaa tallentaa nettisivuston muuttuva tietosisältö, kuten käyttäjien tiedot, keskustelupalstan viestit tai verkkokaupan tuotteet.
MySQL on yksi tietokantajärjestelmä, joka on usein käytössä edullisissa webhotelleissa. Muita tietokantajärjestelmiä ovat esimerkiksi Oracle, PostgreSQL ja SQL Server.
MySQL-järjestelmä muodostuu tietokannoista, jotka sisältävät tauluja. Taulut koostuvat edelleen kentistä ja riveistä. Yksi tietokanta voisi sisältää verkkokaupan tuotteet, asiakkaat, ostotapahtumat ja muun tietosisällön. Yksi tietokannan taulu voisi sisältää kaikkien tuotteiden tiedot. Taulun kenttinä voisivat olla tuotteen tunnusnumero, nimi ja hinta, ja yksi taulun rivi voisi sisältää äskeiset tiedot yhdestä tuotteesta.
Kyselyt hakevat ja muuttavat tietokannan tietoja. Kyselyt kirjoitetaan SQL-kielellä, jota käytetään myös monissa muissa tietokantajärjestelmissä. Eri järjestelmien SQL-kielet poikkeavat toisistaan, mutta suurin osa perusasioista on yhteisiä.
Tietokannan käyttöön eli kyselyiden suoritukseen ja niiden tulosten tarkasteluun on monta tapaa. Tässä opassarjassa kerrotaan, miten tietokantaa käytetään PHP:n kautta. MySQL:n mukana tulee komentoriviltä käytettävä hallintatyökalu, jota käsittelee liite 1. Monissa webhotelleissa taas on asennettuna nettiselaimella käytettävä phpMyAdmin-sovellus, jota käsittelee liite 2.
Seuraavassa on taulu tuotteet, jonka kentät ovat id, nimi ja hinta. Taulussa on kolme riviä, jotka sisältävät kaalin, porkkanan ja perunan tiedot. Jokaisella tuotteella on yksilöllinen tunnusnumero (kenttä id), jonka avulla siihen on helppoa viitata.
+----+----------+-------+ | id | nimi | hinta | +----+----------+-------+ | 1 | kaali | 3 | | 2 | porkkana | 2 | | 3 | peruna | 3 | +----+----------+-------+
Kysely CREATE TABLE luo uuden taulun tietokantaan. Seuraava kysely luo äskeistä esimerkkiä vastaavan taulun tuotteet:
CREATE TABLE tuotteet (
id INT PRIMARY KEY AUTO_INCREMENT,
nimi TEXT,
hinta INT
);Jokaisen kentän nimen jälkeen ilmoitetaan sen tietotyyppi ja mahdolliset muut määritykset. Tyyppi INT tarkoittaa kokonaislukua, ja tyyppi TEXT tarkoittaa merkkijonoa. Määritys PRIMARY KEY tarkoittaa, että kenttä on taulun avain: joka rivillä kentän arvo on eri, jolloin rivit voidaan yksilöidä kentän avulla. Määritys AUTO_INCREMENT tarkoittaa, että aina kun tauluun lisätään uusi rivi, tähän kenttään tulee seuraava vapaana oleva tunnusnumero.
Kysely INSERT lisää tauluun rivin. Seuraavat kolme kyselyä lisäävät tauluun kolme riviä niin, että sen sisältö vastaa esimerkkiä:
INSERT INTO tuotteet (nimi, hinta) VALUES ('kaali', 3);
INSERT INTO tuotteet (nimi, hinta) VALUES ('porkkana', 2);
INSERT INTO tuotteet (nimi, hinta) VALUES ('peruna', 3);Jokaisessa kyselyssä ilmoitetaan ensin, mille kentille annetaan arvot, ja sitten, mitkä kenttien arvot ovat. Kentälle id ei anneta arvoa, koska se saa seuraavan vapaan tunnusnumeron määrityksen AUTO_INCREMENT ansiosta.
Kysely SELECT hakee tietoa tietokannasta. Seuraava kysely hakee kaikki taulussa olevat rivit:
SELECT * FROM tuotteet;
Kysely tuottaa seuraavan tulostaulun:
+----+----------+-------+ | id | nimi | hinta | +----+----------+-------+ | 1 | kaali | 3 | | 2 | porkkana | 2 | | 3 | peruna | 3 | +----+----------+-------+
Tulostaulu muodostuu tietokannan taulun tapaan kentistä ja riveistä. Tässä tapauksessa tulostaulu vastaa rakenteeltaan tietokannan taulua.
Seuraava kysely hakee rivit, joissa hinta on 3:
SELECT * FROM tuotteet WHERE hinta = 3;
+----+----------+-------+ | id | nimi | hinta | +----+----------+-------+ | 1 | kaali | 3 | | 3 | peruna | 3 | +----+----------+-------+
Seuraava kysely hakee rivit, joissa nimi on peruna tai porkkana:
SELECT * FROM tuotteet WHERE nimi = 'peruna' OR nimi = 'porkkana';
+----+----------+-------+ | id | nimi | hinta | +----+----------+-------+ | 2 | porkkana | 2 | | 3 | peruna | 3 | +----+----------+-------+
Joskus tulostaulussa on vain yksi lukuarvo. Näin toimii esimerkiksi seuraava kysely, joka hakee taulun rivien määrän:
SELECT COUNT(*) FROM tuotteet;
+----------+ | COUNT(*) | +----------+ | 3 | +----------+
SELECT on hyvin monipuolinen kysely, kuten myöhemmissä oppaissa huomataan.
Kysely UPDATE muuttaa taulun rivejä. Seuraava kysely muuttaa perunan uudeksi hinnaksi 4:
UPDATE tuotteet SET hinta = 4 WHERE nimi = 'peruna';
Kysely SET-osassa ilmoitetaan, mitä kenttiä muutetaan ja mitkä ovat niiden uudet arvot. Kyselyn WHERE-osassa ilmoitetaan, mitä rivejä muutetaan.
Tämän kyselyn jälkeen taulun sisältö on seuraava:
+----+----------+-------+ | id | nimi | hinta | +----+----------+-------+ | 1 | kaali | 3 | | 2 | porkkana | 2 | | 3 | peruna | 4 | +----+----------+-------+
Kysely DELETE poistaa taulusta rivejä. Seuraava kysely poistaa porkkanan tiedot taulusta:
DELETE FROM tuotteet WHERE nimi = 'porkkana';
Kyselyn WHERE-osassa ilmoitetaan, mitkä rivit poistetaan.
Tämän kyselyn jälkeen taulun sisältö on seuraava:
+----+--------+-------+ | id | nimi | hinta | +----+--------+-------+ | 1 | kaali | 3 | | 3 | peruna | 4 | +----+--------+-------+
Kyselyssä olevat avainsanat (SELECT, WHERE yms.) voi kirjoittaa isolla tai pienellä. Kyselyssä olevat lukuarvot (esim. 3) kirjoitetaan sellaisenaan. Kyselyssä olevat merkkijonot (esim. kaali) kirjoitetaan heittomerkkien tai lainausmerkkien sisään. Kyselyssä voi käyttää välilyöntejä ja rivinvaihtoja vapaasti. Kyselyn loppuun kirjoitetaan puolipiste (;).
MySQL:n dokumentaatio on hyvä tietolähde perusasioiden oppimisen jälkeen:
Mielestäni täällä voisi mainita, että tietokanta luodaan näin:
CREATE DATABASE tietokanta;
ja valitaan käytettäväksi:
USE tietokanta;
php:ssä voi luoda taulun lennosta, esim.
$pdo = new PDO("mysql:host=localhost;dbname=dbname", "user", "password"); $sql = "CREATE TABLE IF NOT EXISTS `MYTABLE` ( `nro` int(11) NOT NULL AUTO_INCREMENT, `nimi` varchar(50) NOT NULL, PRIMARY KEY (`nro`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1" ; $query = $pdo->prepare($sql); $query->execute();
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.