4. SQL-KIELEN PERUSTEET

4.1 Yleistä

SQL eli Structured Query Languaqe Standardoitu ja laajimmin käytetty kieli relaatiotietokantojen yhteydessä.

SQL-kieli sisältää mm. seuraavat ominaisuudet:

• tietokannan rakenteen määrittely ja muuttaminen
• kyselyjen tekeminen
• tietojen päivitys: lisääminen, muuttaminen ja poistaminen
• oikeuksien ja turvallisuuden hoitaminen

SQL:ää voidaan käyttää monin eri tavoin :

• Vuorovaikutteinen SQL:
  Käytetään antamalla SQL-käskyjä omassa ikkunassaan ja saadaan vastaukseen suoraan omaan ikkunaansa.
  Tällä kurssilla käytetään MySQL-tietokantaa näin ottamalla pääteyhteys tietokantapalvelimelle.
• Upotettu SQL:
  SQL-käskyt upotetaan ohjelmointikieleen tai sovellus/raporttikehittimeen. Vastaukset saadaan suoraan ohjelmointikielen muuttujiin. Käskyt voidaan upottaa ohjelmiin sellaisinaan tai käyttää tietokannnan hallintajärjestelmien API-rajapintoja. API on Application Programming Interface, jonka avulla saadaan ohjelmasta yhteys tietokantaan.  Käytetyimpiä API-rajapintoja ovat Microsoftin ODBC ja Java ohjelmissa käytettävä JDBC.
  Tällä kurssilla käytetään MySQL-tietokantaa upottamalla SQL-käskyt PHP-kielisiin ohjelmalauseisiin.
• Dynaaminen SQL:
  SQL-käskyjä luodaan dynaamisesti ohjelmakoodissa ja lähetetään generoitu SQL-käsky tietokantajärjestelmälle käännettäväksi ja suoritettavaksi.

SQL-kielestä on olemassa eri versioita ja tietokannan hallintajärjestelmien käyttämissä SQL-kielen versioissa on pieniä eroavaisuuksia. Peruslauseet toimivat samalla tavoin lähes kaikissa järjestelmissä. Tällä kurssilla SQL-kielen lauseet esitetään MySQL:n mukaisessa muodossa.

SQL-lauseissa kirjainkoolla ei ole muuten merkitystä kuin kenttien ja taulujen nimissä. Selkeyden vuoksi kieleen kuuluvat sanat kirjoitetaan usein isoilla kirjaimilla. Myöskään rivinvaihdoilla ei ole merkitystä. Kenttien nimissä isoilla ja pienillä kirjoitettu kenttä tarkoittavat eri kenttää, joten niiden suhteen tulee olla tarkkana.

sivun alkuun

4.2. Tietokannan rakenteen määrittely

Jokaisen taulun sarakkeelle (kentälle) voidaan määritellä:

• sarakkeen nimi
• tietotyyppi
• pakollisuus (NOT NULL)
• sallittujen arvojen rajoitukset
• oletusarvot

4.2.1.  MySQL:n tärkeimmät tietotyypit:

4.2.2. Taulun luominen: CREATE TABLE

Perussyntaksi:
CREATE TABLE taulun_nimi(
sarake1 TIETOTYYPPI RAJOITTEET,
sarake1 TIETOTYYPPI RAJOITTEET,
sarake1 TIETOTYYPPI RAJOITTEET);

Esimerkki. Luodaan Osasto-taulu.

CREATE TABLE Osasto( 
OsastoID INTEGER AUTO_INCREMENT PRIMARY KEY, 
OsastoNimi VARCHAR(64) NOT NULL)
TYPE = InnoDB; 

Selityksiä koodille:

• auto_increment numeroi tietueet automaattisesti (vrt. Accessin laskuri)
• primary key määrittää perusavaimen.
• not null määrittää, että kentän arvo ei voi olla tyhjä
• määritys Type=innodb tarvitaan MySQL:ssä, jos käytetään viite-eheyksiä (yhteydet)

Viiteyhteydet määritellään foreign key-rajoitteen avulla.

Esimerkki. Luodaan työntekijä-taulu, jossa on viiteavaimena edellisen osasto-taulun OsastoID.

CREATE TABLE Tyontekija(
TyontekijaID INT PRIMARY KEY, 
Sukunimi VARCHAR(30)
Etunimi VARCHAR(30),
OsastoID INTEGER,
INDEX(OsastoID),
FOREIGN KEY(OsastoID)REFERENCES Osasto(OsastoID))
type=innodb;

Selityksiä koodille:

•  INDEX(OsastoID) luo indeksin OsastoID-kentälle. Indeksit ovat eräänlaisia hakemistoja, joiden avulla voidaan nopeuttaa kyselyjä. Joissain MySQL:n versioissa on pakko luoda indeksi kentälle, joka toimii viiteavaimena.
• FOREIGN KEY(OsastoID) REFERENCES Osasto(OsastoID) määrittää, että OsastoID on viiteavain, joka viittaa Osasto-taulun samannimiseen kenttään. Kentän nimen ei tarvitse olla sama molemmissa tauluissa.

4.2.3. Taulun rakenteen tutkiminen, muuttaminen ja poistaminen

Tietokannan kaikki taulut saadaan näkyviin lauseella: SHOW TABLES;

Taulun sarakkeet saadaan näkyviin lauseella: SHOW COLUMNS FROM taulun_nimi;

Taulun rakennetta voi muuttaa ALTER TABLE -lauseella. Katso sen tarkempi syntaksi muutostarpeen mukaan MySQL:n dokumentaatiosta osoitteesta www.mysql.com.

Taulun voi poistaa kokonaan lauseella: DROP TABLE taulun_nimi;

sivun alkuun

4.3. Taulujen tietojen ylläpito

4.3.1. Rivin lisääminen: INSERT INTO

Perussyntaksi:
INSERT INTO taulun_nimi (kenttä1,kenttä2,..) VALUES(arvo1,arvo2,..);

Jos kaikille kentille annetaan arvot, ei niiden nimiä ole pakko luetella.

Esimerkki. Lisätään Osasto-tauluun Myynti-osasto ja Työntekijä-tauluun osastolla työskentelevä Terttu Testaaja.

INSERT INTO Osasto (OsastoNimi) VALUES ('Myynti');

INSERT INTO Tyontekija (TyontekijaID, Sukunimi, Etunimi, OsastoID) 
   VALUES (1,'Testaaja','Teemu', 1);

Selityksiä koodille:

• Merkkijono-tyyppiset arvot tulee kirjoittaa lainausmerkkeihin tai heittomerkkeihin ' '
• OsastoID on kenttä, joka saa arvonsa automaattisesti. Ensimmäisenä tietokantaan syötetyn osaston 'Myynti' ID:n arvoksi tulee 1

Esimerkki. Rivien lisäämisen onnistuminen voidaan tarkistaa hakemalla kaikki tietueet tauluista SELECT-lauseilla.

SELECT * FROM Osasto;
SELECT * FROM Tyontekija;

4.3.2. Tietueiden päivitys: UPDATE

Perussyntaksi:
UPDATE taulun_nimi SET kenttä=arvo, kenttä=arvo … WHERE päivitettävät rivit määrittävä ehto;

WHERE-ehtoja käsitellään tarkemmin SELECT-lauseen yhteydessä.

Esimerkki. Muutetaan Terttu Testaajan sukunimeksi Esimerkkilä.

UPDATE Tyontekija SET sukunimi='Esimerkkilä' WHERE TyontekijaID=1;

3.3. Rivin poistaminen: DELETE

Perussyntaksi:
DELETE FROM taulun_nimi WHERE poistettavan tietueen yksilöivä ehto

Esimerkki. Poistetaan Teemu Esimerkkilän tiedot Työntekijä-taulusta.

DELETE FROM Tyontekija WHERE TyontekijaID=1;

sivun alkuun

4.4. Tietueiden haku: SELECT

Tietokantaan voidaan tehdä hyvin monenlaisia hakuja erilaisilla SELECT-lauseilla. Tällä kurssilla käsitellään vain yksinkertaisia perushakuja.

Perussyntaksi:
SELECT haettavat kentät pilkulla erotettuna
FROM pilkulla erotettuna taulut, joista haetaan
WHERE hakuehto, joka määrittää mitkä rivit haetaan
GROUP BY miten ryhmitellään
HAVING mitkä ryhmittelyn tulosriveistä haetaan
ORDER BY miten lajitellaan

Antamalla FROM-määreeseen * -merkki, saadaan kaikki sarakkeet tulosjoukkoon.
Samojen rivien toistuminen voidaan estää DISTINCT-määreellä SELECT-sanan perässä

Esimerkki. Haetaan kaikki kaikki Työtekijä-taulun kentät nimen mukaisessa aakkosjärjestyksessä.

SELECT * FROM Tyontekija 
ORDER BY Sukunimi, Etunimi;

Esimerkki. Haetaan kaikki kaikkien osasto 1:n työntekijöiden nimet.

SELECT Sukunimi, Etunimi FROM Tyontekija 
WHERE OsastoID=1
ORDER BY Sukunimi, Etunimi;

Merkkijonohakuja osittaisilla ehdoilla käyttämällä LIKE -operaattoria:

• %merkin paikalla voi olla 0,1, tai useampi merkki (vrt. Access *)
• _ merkin kohdalla voi olla mikä tahansa merkki (vrt. Access ?)

Esimerkki. Haetaan kaikki A:lla alkavat sukunimet.

SELECT * FROM henkilo WHERE Sukunimi LIKE 'A%';

Liitoksen avulla yhdistetään kahden tai useamman taulun sarakkeita samaan tulosjoukkoon.
Liitos voidaan tehdä kahdella eri tavalla:

• Perinteinen syntaksi: WHERE-ehdon käyttö
• Uusi syntaksi: JOIN-osan käyttö

Esimerkki. Haetaan molemmilla tavoilla kaikkien Työntekijöiden nimet ja osastot.

SELECT Osasto.OsastoNImi, Tyontekija.Sukunimi, Tyontekija.Etunimi 
FROM Osasto, Tyontekija 
WHERE Osasto.OsastoID=Tyontekija.OsastoID;

SELECT Osasto.OsastoNImi, Tyontekija.Sukunimi, Tyontekija.Etunimi 
FROM Osasto
JOIN Tyontekija ON Osasto.OsastoID=Tyontekija.OsastoID;

Selityksiä koodille:

• Jos samanniminen sarake on molemmissa tauluissa, on taulun nimi pakko merkitä sarakenimen eteen. Sarakenimi on suositeltavaa merkitä aina liitosten yhteydessä.
• Käytettäessä JOIN-syntaksia, esitetään liitosehdot  ON-lauseessa erillään muista mahdollisista hakuehdoista, jotka ovat WHERE-lauseessa
  
   

sivun alkuun