SQL Perusteet

Mikä on SQL?

SQL, eli Structured Query Language, on standardikieli relaatiotietokantojen hallintaan. SQL:ää käytetään tiedon hakemiseen, lisäämiseen, muokkaamiseen ja poistamiseen tietokannoista. SQL-tietokantoja on monia erilaisia, kuten MySQL/MariaDB, PostgreSQL, Oracle ja Microsoft SQL.

Mikä on relaatiotietokanta?

Relaatiotietokanta on tietokantajärjestelmä (DBMS), joka järjestää tiedon yhteen tai useampaan taulukkoon, joilla on keskinäinen yhteys. Jokainen taulukko koostuu sarakkeista ja riveistä, joita kutsutaan tietueiksi. Tietokannan eri taulukot ovat keskenään yhteydessä toisiinsa tiettyjen kenttien avulla, joita kutsutaan avaimiksi. Relaatiotietokannassa yksittäinen taulukko voi olla suuri ja monimutkainen, mutta sen käsittely on helppoa, kun sitä käsitellään tietynlaisen rakenteen mukaan.

Relaatiotietokannat ovat hyvin yleisiä liiketoiminnan, hallinnon ja tietojenkäsittelyn aloilla. Ne mahdollistavat tietojen tehokkaan tallennuksen, hakemisen ja ylläpidon sekä tietojen välisen yhteyden muodostamisen ja tiedon eheyden ylläpidon.

Miten sovellus yhdistää tietokantaan?

Web-sovelluksen ja tietokantahallintajärjestelmän välille muodostetaan yhteys TCP-pohjaisen SQL-protokollan avulla. Esimerkiksi MySQL/MariaDB kuuntelee oletuksena porttia 3306. Sovellukseen määritetään käyttäjätunnus ja salasana/salaisuus/varmenne jolla sovellus tunnistaa itsensä tietokantaan. Tietokannassa on määritetty tietyt oikeudet sovelluksen käyttäjälle, jotka kannattaa tiestysti pitää minimissä jotta voidaan minimoida vahingos sellaisessa tapauksessa jossa sovellus vaarantuu.

Kun yhteys on auki, sovellus lähettää Englannin kieltä muistuttavia SQL-kyselyitä tietokannalla, ja tietokanta vastaa palauttamalla rivejä. Aina rivejä ei palaudu ollenkaan, jos kyseessä on esimerkiksi INSERT-kysely jolla ei haeta mitään, vaan luodaan uutta dataa.

SQL-Tietokannan rakenne

Tietokanta koostuu useasta taulusta, joista jokainen sisältää tietynlaista tietoa. Taulut voivat olla esimerkiksi asiakasrekisteri, tuoterekisteri tai tilaushistoria. Tauluissa olevien tietojen tallentamiseksi SQL-tietokannassa tarvitaan sarake, joka kuvaa taulussa olevaa tietoa. Sarakkeet määrittelevät taulun rakenteen ja määrittävät tietotyypit, jotka määräävät millaista tietoa sarake voi sisältää. Esimerkiksi, jos taulussa on sarakkeita "nimi" ja "osoite", niin nimi-sarakkeessa tallennetaan henkilön nimi ja osoite-sarakkeessa hänen osoitteensa.

Jokaisella taululla on yksilöllinen nimi, joka kuvaa sen sisältöä. Taulun nimi voi olla esimerkiksi "asiakkaat". Taulut voivat myös sisältää avaimen, joka auttaa yhdistämään tauluja toisiinsa. Avaimia käytetään usein viittaamaan toiseen tauluun, jolloin taulut yhdistyvät ja niiden väliset suhteet muodostuvat.

SQL-tietokantaan tallennetut tiedot koostuvat siis tauluista, joissa on useita sarakkeita, jotka kuvastavat taulussa olevaa tietoa. Taulut voivat olla keskenään yhteydessä avainten avulla. SQL-tietokannan tehokas käyttö vaatii hyvin suunnitellun taulurakenteen, joka on selkeä ja helposti ymmärrettävissä.

SQL-Kyselyt

SQL:ää käytetään eri tietokannoissa hieman eri tavalla, mutta perusperiaatteet ovat samat. SQL:ää käytetään tietojen hakemiseen, lisäämiseen, muokkaamiseen ja poistamiseen tietokannoista.

SELECT-kysely

Tämä kysely valitsee kaikki asiakkaat, jotka asuvat Helsingissä.

SELECT * FROM Asiakkaat WHERE Kaupunki = 'Helsinki';

| Nimi          | Osoite              | Kaupunki  |
| -------------| --------------------| ----------|
| Matti Meikäläinen | Esimerkkikatu 1    | Helsinki  |
| Maija Virtanen   | Testikatu 10 A 4  | Helsinki  |

INSERT-kysely

Tämä kysely lisää uuden asiakkaan tauluun Asiakkaat.

INSERT INTO Asiakkaat (Etunimi, Sukunimi, Sähköposti) VALUES ('Matti', 'Meikäläinen', 'matti.meikalainen@gmail.com');

UPDATE-Kysely

Tämä kysely päivittää asiakkaan kaupungin Tampereeksi.

UPDATE Asiakkaat SET Kaupunki = 'Tampere' WHERE Etunimi = 'Matti' AND Sukunimi = 'Meikäläinen';

DELETE-Kysely

Tämä kysely poistaa asiakkaan taulusta Asiakkaat.

DELETE FROM Asiakkaat WHERE Etunimi = 'Matti' AND Sukunimi = 'Meikäläinen';

JOIN-Kysely

Tämä kysely liittää kaksi taulua, Asiakkaat ja Tilaus, ja palauttaa kaikkien asiakkaiden etunimet ja tilauspäivämäärät.

SELECT Asiakkaat.Etunimi, Tilaus.Tilauspäivä
FROM Asiakkaat
JOIN Tilaus ON Asiakkaat.AsiakasID = Tilaus.AsiakasID;

| Etunimi  | Tilauspäivä |
| ---------| ------------|
| Matti    | 2022-01-01  |
| Maija    | 2022-02-01  |
| Juha     | 2022-02-15  |
| Laura    | 2022-03-01  |

GROUP BY -Kysely

Tämä kysely ryhmittelee asiakkaat kaupungeittain ja laskee jokaisen kaupungin asiakkaiden määrän.

SELECT Kaupunki, COUNT(*) AS Lkm
FROM Asiakkaat
GROUP BY Kaupunki;

| Kaupunki | Lkm |
| ---------| --- |
| Helsinki | 2   |
| Tampere  | 1   |

ORDER BY -Kysely

Tämä kysely palauttaa kaikki asiakkaat taulusta Asiakkaat sukunimen mukaan nousevassa järjestyksessä.

SELECT * FROM Asiakkaat ORDER BY Sukunimi ASC;

| Nimi            | Osoite                 | Kaupunki  |
| ---------------| -----------------------| ----------|
| Maija Virtanen  | Testikatu 10 A 4       | Helsinki  |
| Matti Meikäläinen | Esimerkkikatu 1       | Helsinki  |
| Laura Lahtinen  | Esimerkkikatu 10 B 12  | Tampere   |

DISTINCT -Kysely

Tämä kysely palauttaa kaikki uniikit kaupungit, joissa asiakkaat asuvat.

SELECT DISTINCT Kaupunki FROM Asiakkaat;

| Kaupunki  |
| ----------|
| Helsinki  |
| Tampere   |

LIKE -Kysely

Tämä kysely palauttaa kaikki asiakkaat, joiden sukunimi alkaa kirjaimella M.

SELECT * FROM Asiakkaat WHERE Sukunimi LIKE 'M%';

| Nimi            | Osoite                | Kaupunki  |
| ---------------| ----------------------| ----------|
| Matti Meikäläinen | Esimerkkikatu 1      | Helsinki  |
| Matti Virtanen  | Testikatu 5 A 6       | Tampere   |

IN -Kysely

Tämä kysely palauttaa kaikki asiakkaat, jotka asuvat joko Helsingissä tai Tampereella.

SELECT * FROM Asiakkaat WHERE Kaupunki IN ('Helsinki', 'Tampere');

| Nimi             | Osoite                | Kaupunki  |
| ----------------| ----------------------| ----------|
| Matti Meikäläinen | Esimerkkikatu 1      | Helsinki  |
| Maija Virtanen   | Testikatu 10 A 4     | Helsinki  |
| Laura Lahtinen   | Esimerkkikatu 10 B 12 | Tampere   |

UNION -Kysely

Tämä kysely yhdistää kaksi erillistä kyselyä yhdeksi tulostaulukoksi, joka sisältää kaikki rivit, jotka palautuvat jommastakummasta kyselystä. Palautettavat rivit näyttävät seuraavalta:

(SELECT Nimi, Osoite, Kaupunki FROM Asiakkaat WHERE Kaupunki = 'Helsinki')
UNION
(SELECT Nimi, Osoite, Kaupunki FROM Asiakkaat WHERE Kaupunki = 'Tampere')

| Nimi             | Osoite                 | Kaupunki  |
| ----------------| -----------------------| ----------|
| Matti Meikäläinen  | Esimerkkikatu 1       | Helsinki  |
| Maija Virtanen    | Testikatu 10 A 4      | Helsinki  |
| Laura Lahtinen    | Esimerkkikatu 10 B 12  | Tampere   |

Alikysely (subquery)

Tämä kysely käyttää alakyselyä löytääkseen kaikki kaupungit, joissa tehtiin tilauksia tiettynä päivänä. Tämän jälkeen se palauttaa kaikkien asiakkaiden nimet ja kaupungit, jotka sijaitsevat näissä kaupungeissa.

SELECT Nimi, Kaupunki 
FROM Asiakkaat 
WHERE Kaupunki IN 
    (SELECT Kaupunki FROM Tilaus WHERE Tilauspäivä = '2022-02-01')

| Nimi             | Kaupunki  |
| ----------------| ----------|
| Matti Meikäläinen  | Helsinki |
| Maija Virtanen    | Helsinki |

Kommentit

SQL-kommentit ovat merkkijonoja, jotka on tarkoitettu selittämään SQL-koodia ja jätetään huomioimatta tietokannan suorittaessa koodia. Ne ovat hyödyllisiä, kun selitetään monimutkaisia kyselyitä tai kun halutaan merkitä koodia, jota ei haluta suorittaa.

SQL-kommentteja on kahta tyyppiä, yksirivistä ja monirivistä.

Yksiriviset kommentit

Yksirivinen kommentti alkaa "-- " tai "#" -merkinnällä. Kaikki merkit kommenttimerkinnän jälkeen jätetään huomioimatta. Huomaa että # ei toimi kaikissa SQL-tietokannoissa. Huomaa myös että -- jälkeen täytyy tulla välilyönti ennen kommentin aloittamista.

-- Tämä on yksirivinen kommentti
SELECT * FROM Asiakkaat; -- Toinen yksirivinen kommentti

Moniriviset kommentit

Monirivinen kommentti alkaa "/*" ja päättyy "*/". Kaikki merkit näiden merkkien välissä jätetään huomioimatta.

/* Tämä on
monirivinen
kommentti */
SELECT * FROM Asiakkaat; /* Toinen monirivinen kommentti */