Page History

h2. 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.

{code}
--tuhotaan taulut
DROP TABLE user;
DROP TABLE client;
DROP TABLE project;

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

{code}
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)
 );

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

{code}
--
{code}
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)
);
{code}
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.




h3. 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:
{code}
SELECT kenttä1\[, kenttä2,...KenttäN\]
FROM Taulu1\[, Taulu2,...Taulu3\]
{code}
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:
{code}
SELECT Etunimi, Sukunimi, Osoite, Postinumero, Postitoimipaikka
FROM Oppilas
{code}
Mikäli kaikki kentät otetaan mukaan, voidaan niiden luettelemisen sijasta käyttää \*-merkkiä:
{code}
SELECT *
FROM Oppilas
{code}
Jos tulokset halutaan lajitella jonkinlaiseen järjestykseen, se onnistuu ORDER BY sivulauseella. Esim:
{code}
SELECT *
FROM Oppilas
ORDER BY Sukunimi, Etunimi
{code}
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:

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

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.

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

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


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



h3. 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.



h3. 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 )

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

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ä.



h3. 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.



h3. 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.



h3.