Sql

SQL-kieli

SQL (Structured Query Language) on tietokantakieli, jonka avulla käyttäjä voi määritellä tietokannan, ylläpitää sitä ja kohdistaa siihen kyselyitä.

SQL-kieli on kaikkien nykyaikaisten tietokannanhallintajärjestelmien taustalla. Kun esimerkiksi Access'issa tehdään kysely, syntyy siitä SQL-lause. Mutta eri ohjelmistovalmistajat sisällyttävät SQL-kielen toteutuksiinsa omia lisäyksiään, jotka tekevät niistä vaikeaselkoisia ja yhteensopimatonta.

SQL-kieli on hyvin laaja, se  sisältää esimerkiksi lauseet tietokantataulujen luontiin ja tuhoamiseen.

--tuhotaan taulut
DROP TABLE user;
DROP TABLE client;
DROP TABLE project;

Edellisten käskyjen suhteen on syytä olla tarkkana, koska kaikki taulujen sisältö tuhoutuu.

CREATE TABLE user
(
 user_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
 client_id INTEGER,
 name TEXT NOT NULL,
 username TEXT NOT NULL,
 password TEXT NOT NULL,
 FOREIGN KEY(client_id) REFERENCES client(client_id)
 );

–

-- Describe CLIENT
CREATE TABLE client
(
    client_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    name TEXT
);

–

CREATE TABLE project
(
    project_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    name TEXT,
    header TEXT, --Header to raports
    date DATETIME,
    parent_project_id INTEGER,
    client_id INTEGER,
    author TEXT,
    notes TEXT,
    ActiveProject Boolean NOT NULL, -- only one project is active
    FOREIGN KEY(client_id) REFERENCES client(client_id)
);

SQL-kielessä on lauseet  tietueiden valintaan SELECT,  tietojen päivittämiseen eli niiden tietosisällön muuttamiseen UPDATE, uuden tietueen luomiseen INSERT ja tietue(id)en tuhoamiseen DELETE.

SELECT-lause

SELECT-lauseen voidaan sanoa olevan SQL:n perusta. SELECT lauseella valitaan yhdestä tai useammasta tietokantataulusta tietyt kentät näytettäviksi. Mitkä tietueet valitaan määrätään ns. sivulauseella, joka vastaa kyselyn ehtoa Access'issa. SELECT lauseessa ehdot vain voidaan asettaa huomattavasti monipuolisemmin.

SELECT-lauseen yleinen muoto eli ns. syntaksi on:

SELECT kenttä1\[, kenttä2,...KenttäN\]
FROM Taulu1\[, Taulu2,...Taulu3\]

Kenttä1, KenttäN, Taulu1, TauluN viittaavat tietokannassa määriteltyihin todellisiin kentän nimiin. Jos otamme esimerkiksi Oppilas-taulun, jossa on Etunimi, Sukunimi, Osoite, Postinumero ja Postitoimipaikka-kentät, kirjoitettaisiin SELECT lause, joka palauttaa kaikkien oppilaiden kaikki tiedot seuraavasti:

SELECT Etunimi, Sukunimi, Osoite, Postinumero, Postitoimipaikka
FROM Oppilas

Mikäli kaikki kentät otetaan mukaan, voidaan niiden luettelemisen sijasta käyttää *-merkkiä:

SELECT *
FROM Oppilas

Jos tulokset halutaan lajitella jonkinlaiseen järjestykseen, se onnistuu ORDER BY sivulauseella. Esim:

SELECT *
FROM Oppilas
ORDER BY Sukunimi, Etunimi

lajittelee oppilaat ensin sukunimen, sitten etunimen mukaan.

Access-kyselyiden ehtoja vastaa SQL-kielessä WHERE sivulause. Kun Access-kyselyn rakennenäkymässä asetetaan ehtoja, Access toteuttaa ne SQL-kielen WHERE-lauseella. Se sijoitetaan FROM ja ORDER sivulauseiden väliin, esim:

SELECT *
FROM Oppilas
WHERE Sukunimi > 'O' AND Sukunimi < 'Q'
ORDER BY Sukunimi, Etunimi

Esimerkin SELECT lause valitsi oppilaista vain ne, joiden sukunimi alkaa P-kirjaimella. Toisin sanoen sukunimen on oltava suurempi kuin pelkkä O-kirjain ja pienempi kuin pelkkä Q-kirjain. Nämä olisivat Access-kyselyn rakennenäkymän Sukunimi- ja Etunimi-kenttiin sijoitettavat ehdot. Monien vaihtoehtoisten käyttötapojensa vuoksi käsittelemme WHERE sivulauseen kuitenkin seuraavassa erikseen.

WHERE sivulause

Koska WHERE-sivulausetta käytetään muidenkin SQL-lauseiden kuin pelkästään SELECT-lauseen sivulauseena ja kun sen avulla vielä luodaan yhteydetkin, on se syytä käsitellä omana tärkeänä kokonaisuutenaan.

SELECT * FROM project, testtype_static WHERE project.ActiveProject=1 AND project.project_id=testtype_static.project_id

WHERE-sivulauseen tärkeitä elementtejä ovat sanat AND, OR ja NOT. Nämä ovat loogisia operaattoreita. Sanomme esim. että "tänään on kylmä päivä JA sataa vettä". Jos etsimme jonkun ajanjakson päivien joukosta sellaiset päivät, jolloin on ollut kylmä JA on satanut, saamme tulokseksi juuri ne päivät, joille on kirjattu, että ne ovat olleet kylmiä JA että on satanut. Tämä kysely ei ilmoita meille niitä päiviä, jolloin on pelkästään satanut eikä niitä päiviä, jolloin on pelkästään ollut kylmä.

Sen sijaan jos kysymme päiviä, jolloin on satanut TAI on ollut kylmä, saamme luettelon päivistä, jolloin on pelkästään satanut tai on ollut pelkästään kylmä. Tässä on kuitenkin tavanomaiseen kielenkäyttöön pieni ero: mukaan otetaan myös sellaiset päivät jolloin on SEKÄ satanut ETTÄ on ollut kylmä. Yleensähän puhutussa kielessä TAI-sana pyrkii karsimaan vaihtoehdot erilleen. Esimerkiksi laulun sanat: "oli kylmä tai vaikkapa vari" tarkoittavat että olipa kylmä tai lämmin, mutta eivät suinkaan sitä, että olipa molemmat yhtäaikaa. Tällaista TAI-sanan käyttöä kutsutaan logiikassa poissulkevaksi TAI'ksi. Sen sijaan TAI joka hyväksyy molemmat vaihtoehdot, on nimeltään mukaanlukeva TAI.

Nyt pitää huomata, että 'suurempi kuin' ei pidä sisällään yhtäsuuruutta. Sellainen arvo joka on yhtäsuuri ei tule tässä vertailussa huomioiduksi. Tällöin olisi pystyttävä käyttämään sitä merkkiä, jota matematiikassa kutsutaan 'suurempi tai yhtä suuri kuin' merkiksi. ASCII merkistöllä ei edes pystytä esittämään sitä, koska se ei kuulu ASCII-merkistön valikoimaan. Kysymys on kuitenkin 'suurempi kuin' merkistä, jonka alla on viiva, ikäänkuin yhtäsuuruusmerkin alempi osa. Mikäli olit tarkkaavainen, saatoit huomata missä salaisuus piilee. Edellä käytettiin sanontaa 'suurempi TAI yhtä suuri kuin'. Siis esim:

WHERE (Kenttä1 > 10) OR (Kenttä1 = 10)

Tähän on kuitenkin SQL-kielessä helpotus. 'Suurempi kuin', 'pienempi kuin' ja yhtäsuuruus-merkkejä voidaan yhdistellä. Niinpä edellä esitetty ehto voitaisiin yhtä hyvin kirjoittaa:

WHERE (Kenttä1 >= 10)

luetaan: kun Kenttä1 on suurempi-tai-yhtäsuuri-kuin 10

Numeroilla suoritettava vertailu on kuitenkin luontevaa. Mutta miten voidaan verrata kirjaimia ja niistä muodostettuja sanoja toisiinsa? Silloin kun etsitään esimerkiksi pelkästään 'Miettisiä' on tehtävä selkeä ja ehto asetetaan seuraavasti:

WHERE Kenttä1 = 'Miettinen'

Jos Kenttä1:n sisältönä on täsmälleen sana 'Miettinen' saa tämä ehtolauseke arvon 'tosi'. Tämä on yleinen sanontatapa logiikassa. Ehto on ikäänkuin väittämä, jota testataan eri arvoilla. Vain silloin, kun tietokannan Kenttä1 pitää sisällään sanan 'Miettinen' toteutuu ehto, sen molemmat puolet ovat todellakin yhtäsuuret. Lisäksi on syytä huomata, että käytettäessä lukuja, niitä ei ympäröidä millään erikoismerkeillä, mutta jos kyseessä on ns. merkki- eli kirjaintieto, on se ympäröitävä yksinkertaisilla lainausmerkeillä. Numerotietoa ja merkkitietoa ei voi verrata ehdossa toisiinsa, ellei numerotietoa muuteta ensin merkkitiedoksi tai päinvastoin. Numerotieto poikkeaa merkkitiedosta siinä, että numerotiedolla voidaan suorittaa matemaattisia toimenpiteitä, merkkitiedolla ei voida.

ASCII-merkeillä (numeroilla ja kirjaimilla) on silti tietty, sovittu järjestyksensä, jossa blankko on aina ensimmäisenä. Seuraavana tulevat erikoismerkit, sitten numerot, sitten suuret aakkoset ja lopuksi pienet aakkoset. Näin pelkät yhden kirjaimen muodostamat kokonaisuudet asettuvat aina ennen kuin vastaavalla kirjaimella alkavat pitemmät sanat. Esimerkiksi A on suurempi AA, koska A:n perässä voidaan ajatella olevan blankkoja. Seuraavassa havainnollistuksessa on kuvitellut blankot merkitty pisteillä. Ne asettuvat aina ennen muita merkkejä:

A....
AA...
AAB..
B....
BB...
BBC..

Tästä syystä voidaan asettaa ehto, jossa kysytään onko jokin sana suurempi kuin 'O' JA pienempi kuin 'Q'. Kaikki 'P'-kirjaimella alkavat sanat tuottavat arvon 'tosi' ehtolauseessa:

WHERE (sana > 'O') AND (sana < 'Q')

Seuraavassa on esimerkki valinnasta kahdesta eri taulusta

SELECT * FROM project, testtype_static WHERE project.ActiveProject=1 AND project.project_id=testtype_static.project_id

UPDATE-lause

UPDATE-lauseen avulla saadaan tiedot päivittymään tietokantaan. Sen syntaksi on varsin yksinkertainen.

UPDATE Taulu
SET Kenttä1 = arvo [,
Kenttä2 = arvo,...
KenttäN = arvo ]
WHERE lause

Yhtäsuuruusmerkki SET-lauseissa tarkoittaa, että kentän sisällöksi asetetaan (set) arvo. Tämä arvo on useimmiten peräisin asiakkaalta, joka on syöttänyt sen lomakkeeseen. Myöhemmin opimme, miten arvo saadaan lomakkeelta UPDATE-lauseen käyttöön.

INSERT-lause

INSERT-lause sijoittaa (luo) uuden tietueen tauluun ja samalla sen kenttiin voidaan sijoittaa arvoja. Sen syntaksi on

INSERT INTO Taulu
( Kenttä1 [, Kenttä2,..KenttäN] )
VALUES ( arvo1 [, arvo2,..arvoN )

INSERT INTO Anturi  VALUES ('1', '2', '0', 'Voimaanturi', 'IO', '1', '0', '1', 'AIN0', '1', '1', 'kN', '0.003076923');

INSERT-lauseessa kuuluu käyttää INTO-osaa, jolla kerrotaan mihin tauluun lisäyksen halutaan tapahtuvan. Suluissa oleva kenttäluettelo ja VALUES ( ) -sivulauseen arvojono on vastaavat toisiaan. Tällöin esimerkiksi jonossa kolmantena oleva kenttä saa sisällökseen kolmannen arvon arvojonosta. Sellaiset tietokantataulun kentät, joille INSERT-lauseessa ei anneta arvoa, saavat arvokseen tietokannassa ao. kentälle määrätyn oletusarvon. Tässä on syytä olla tarkkana, sillä sellaisille kentille, joille 'Arvo tarvitaan' on annettava jokin sisältö, vaikkapa vain blankko merkkitiedolle ja 0 numerotiedolle. INSERT-lauseessa ei käytetä WHERE sivulausetta, sillä tietoja ollaan vasta tallentamassa tietokantaan, eikä millään ehdolla näin ollen edes olisi merkitystä.

DELETE-lause

DELETE(tuhoa)-lauseen toiminta on yksioikoista. Tuhotaan kaikki tietueet, jotka WHERE-lause määrää. Lauseen toteutus saattaa myös olla varsin tuhoisaa. Jos WHERE-lause unohtuu pois, lause tuhoaa kaikki taulun tietueet. DELETE-lauseen syntaksi:

DELETE Taulu
FROM Taulu
WHERE lause

Tuhotaan siis tietue "Taulu" taulusta "Taulu". WHERE lauseella määrätään mikä (tai mitkä) tietueet. Huom! Jos DELETE lauseesta jätetään WHERE-sivulause pois, niin se tuhoaa taulun kaikki tietueet.

SQL-lauseiden toiminta

SQL-kielisten lauseiden voidaan ajatella hakevan tietueita yksi kerrallaan. Ne aloittavat aina taulun alusta, sen ensimmäisestä tietueesta.

- Mikäli tietue täyttää WHERE-lauseen ehdot, sen tiedoille tehdään mitä SQL-lause edellyttää , se joko valitaan (SELECT) tai sitä muutetaan (UPDATE) tai se tujotaan (DELETE).
- Kun toimenpiteet on tehty - tai on todettu ettei tietue täytä WHERE-lauseen ehtoja - siirrytään seuraavaan tietueeseen.

WHERE-lauseen ehdosta riippuu, löytyykö taulusta yksi tai useampia tietueita tai sitten ei yhtään. Tämä pätee kaikkiin muihin tarkastelemiinne SQL-lauseisiin paitsi INSERT-lauseeseen, joka vasta luo uuden tietueen. INSERT-lauseen yhteyteen ei siten kuulukaan WHERE-lauseen käyttö.

WHERE-lauseella saadaan myös aikaiseksi ns. yhteys kahden tai useamman taulun välille. Tällöin ehto muotoillaan siten, että se edellyttää taulujen välille jonkin yhteisen tekijän. Oppilaan ja hänen koulutasonsa välinen riippuvuus on esimerkki tällaisesta tekijästä. Toisessa taulussa ovat oppilaiden tiedot, joihin kuuluu jokaisen oppilaan tasonumero (Optaso). Tasot puolestaan on talletettu toiseen tauluun ja niilläkin on oma tasonumeronsa (Tid). Yhteys syntyy SELECT-lauseen ehdosta:

SELECT *
FROM Oppilas, Taso
WHERE Optaso = Tid.

Haetaan siis yhtäaikaa oppilaita, joilla jokaisella on optaso:nsa ja tasoja, joilla jokaisella on tid:nsä. Nämä yhdistetään SELECT-lauseessa. Joukko-opillisesti on kysymys leikkauksesta. Jos esimerkiksi etsitään oppilaita, jotka ovat ala-asteella, etsitään joukko-opillisesti sellaista oppilas-joukon ja taso-joukon leikkausta, jonka muodostaa tietty oppilaiden osajoukko JA tietty tasojen osajoukko.

WHERE lauseessa tällainen leikkaus saadaan aikaiseksi käyttämllä relaatio-operaattorina AND'ia (JA-operaattoria):

SELECT *
FROM Oppilas, Taso
WHERE (Optaso = Tid) AND (Tid = 1)

Itse asiassa koko relaatiotietokantateoria perustuu joukko-oppiin. WHERE-lauseella voidaan toteuttaa kaikki joukko-opin perusoperaatiot, yhdiste, leikkaus, erotus sekä karteesinen tulo. Relaatiotietokantoja varten onkin luotu oma erillinen relaatioalgebransa.