Kurssiblogi
Kurssiblogissa ilmestyy silloin tällöin kurssimateriaalia täydentävää sisältöä, jonka tavoitteena on antaa uusia näkökulmia kurssin aiheisiin.
11.3.2020
Millainen raportti vaadittiin harjoitustyön arvosanaan 5? Tästä voit katsoa kaksi sellaista malliraporttia:
Palautetut raportit olivat keskimäärin oikein hyviä. Kolme yleisintä syytä raportin arvosanan alentamiseen olivat:
- Tehokkuustesti puuttuu tai sen tulokset eivät ole järkeviä (esim. testi on tehty eri tavalla kuin ohjeessa tai indeksit eivät ole sopivia).
- Sovellus ei pidä luotettavasti huolta siitä, että tietyssä sarakkeessa on eri arvo jokaisella taulun rivillä (erityisesti kun on samanaikaisia käyttäjiä).
- SQL-komennossa ei ole käytetty parametreja (?-merkintä komennossa), vaan muuttujia on yhdistetty suoraan komentoon.
3.3.2020
Alikyselyt ovat käteviä, mutta todella monen kyselyn voi toteuttaa ilman niitä käyttämällä sopivasti monen taulun kyselyä ja muita tekniikoita.
Esimerkiksi luvussa 4.2 olevan kyselyn
SELECT nimi, tulos FROM Tulokset WHERE tulos = (SELECT MAX(tulos) FROM Tulokset);voi toteuttaa myös ilman alikyselyä seuraavasti:
SELECT A.nimi, A.tulos
FROM Tulokset A LEFT JOIN Tulokset B ON B.tulos > A.tulos
GROUP BY A.id HAVING COUNT(B.tulos)=0;Myös useimmat SQL Trainerin tehtävät voi ratkoa ilman alikyselyitä. Itse asiassa tiedossa on tällä hetkellä alikyselytön ratkaisu kaikkiin tehtäviin seuraavia lukuun ottamatta:
72, 75, 89, 90, 91, 93, 94
Haaste: Osaatko ratkoa jonkin näistä ilman alikyselyä?
25.2.2020
Tietokannan käyttämistä voi haitata sen lukittuminen. Näin käy seuraavassa koodissa:
Connection db1 = DriverManager.getConnection("jdbc:sqlite:testi.db");
Statement s1 = db1.createStatement();
s1.execute("SELECT * FROM Testi");
Connection db2 = DriverManager.getConnection("jdbc:sqlite:testi.db");
Statement s2 = db2.createStatement();
s2.execute("INSERT INTO Testi (x) VALUES (1)");Koodi antaa viimeisellä rivillä ilmoituksen
The database file is locked (database is locked)
eikä pysty suorittamaan INSERT-komentoa.
Ongelmana on, että koodi luo kaksi erillistä yhteyttä tietokantaan
ja ensimmäinen yhteys (db1) on vielä käynnissä,
kun toinen yhteys (db2) muodostetaan.
Tarkemmin komentoa INSERT ei voida suorittaa,
koska kysely SELECT on vielä vireillä.
Lukittumisen estämiseen on ainakin kaksi tapaa.
Yksi tapa on käyttää close-metodia,
jonka avulla voi sulkea tietokantaan liittyvän olion.
Esimerkiksi voisimme sulkea avatut yhteydet näin äskeisessä koodissa:
Connection db1 = DriverManager.getConnection("jdbc:sqlite:testi.db");
Statement s1 = db1.createStatement();
s1.execute("SELECT * FROM Testi");
db1.close();
Connection db2 = DriverManager.getConnection("jdbc:sqlite:testi.db");
Statement s2 = db2.createStatement();
s2.execute("INSERT INTO Testi (x) VALUES (1)");
db2.close();Toinen tapa on käyttää koko ajan vain yhtä yhteyttä:
Connection db = DriverManager.getConnection("jdbc:sqlite:testi.db");
Statement s1 = db.createStatement();
s1.execute("SELECT * FROM Testi");
Statement s2 = db.createStatement();
s2.execute("INSERT INTO Testi (x) VALUES (1)");Kun tietokantaan on vain yksi yhteys, sitä ei tarvitse sulkea. Yhteys kuitenkin sulkeutuu automaattisesti, kun ohjelman suoritus päättyy.
Seuraava koodi kuitenkin edelleen lukitsee tietokannan:
Connection db = DriverManager.getConnection("jdbc:sqlite:testi.db");
Statement s1 = db.createStatement();
s1.execute("SELECT * FROM Testi");
Statement s2 = db.createStatement();
s2.execute("DROP TABLE Testi");Nyt virheilmoitus on vähän erilainen
A table in the database is locked (database table is locked).
Tässä tapauksessa ongelman ratkaisee, että suljemmekin kyselyn:
Connection db = DriverManager.getConnection("jdbc:sqlite:testi.db");
Statement s1 = db.createStatement();
s1.execute("SELECT * FROM Testi");
s1.close();
Statement s2 = db.createStatement();
s2.execute("DROP TABLE Testi");21.2.2020
Miten alikyselyt oikeastaan toimivat ja mihin kohtaan kyselyä alikysely tulisi sijoittaa?
Tässä auttaa ajatella alikyselyä niin, että se palauttaa jonkin tuloksen, joka sijoitetaan pääkyselyyn vastaavaan kohtaan. Alikysely voi palauttaa yksittäisen arvon, listan arvoista tai kaksiulotteisen taulun tietoa.
Tarkastelemme seuraavaksi alikyselyitä seuraavan taulun Elokuvat avulla:
id nimi vuosi
---------- ---------- ----------
1 Lumikki 1937
2 Fantasia 1940
3 Pinocchio 1940
4 Dumbo 1941
5 Bambi 1942 Tapaus 1: Sarakkeen arvo haetaan alikyselystä
Tässä tapauksessa alikysely on kyselyn SELECT-osassa,
ja se palauttaa yksittäisen arvon, josta tulee yksi tulostaulun sarakkeista.
Seuraava kysely käy läpi elokuvat ja muodostaa tulostaulun, jossa on kolme saraketta: elokuvan nimi ja vuosi sekä elokuvien yhteismäärä. Kolmas arvo on haettu alikyselyllä, joka antaa saman tuloksen jokaiselle riville.
SELECT nimi, vuosi, (SELECT COUNT(*) FROM Elokuvat) FROM Elokuvat;nimi vuosi (SELECT COUNT(*) FROM Elokuvat)
---------- ---------- -------------------------------
Lumikki 1937 5
Fantasia 1940 5
Pinocchio 1940 5
Dumbo 1941 5
Bambi 1942 5 Tapaus 2: Alikysely luo taulun, josta haetaan tietoa
Tässä tapauksessa alikysely on kyselyn FROM-osassa,
ja se palauttaa kokonaisen taulun tietoa.
Pääkysely hakee tietoa tästä taulusta kuin se olisi tietokannassa oleva taulu.
Seuraava kysely hakee tietoa taulusta, joka on alikyselyn muodostama. Taulussa on kaikki elokuvat, jotka on julkaistu vuonna 1940.
SELECT nimi FROM (SELECT * FROM Elokuvat WHERE vuosi=1940);nimi
----------
Fantasia
Pinocchio Huomaa, että yllä oleva kysely toimii samoin kuin tämä kysely:
SELECT nimi FROM Elokuvat WHERE vuosi=1940;Tapaus 3: Alikysely esiintyy kyselyn ehdossa
Tässä tapauksessa alikysely voi palauttaa joko yksittäisen arvon tai listan arvoja, riippuen miten sitä käytetään ehdossa.
Esimerkiksi seuraava kysely hakee niiden elokuvien nimet, joilla on varhaisin julkaisuvuosi. Tässä alikysely palauttaa yksittäisen arvon (julkaisuvuosi).
SELECT nimi FROM Elokuvat WHERE vuosi=(SELECT MIN(vuosi) FROM Elokuvat);nimi
----------
Lumikki Seuraava kysely puolestaan hakee niiden elokuvien nimet, jotka on julkaistu vuonna 1940. Tässä alikysely palauttaa listan arvoja (elokuvien id-numerot).
SELECT nimi FROM Elokuvat WHERE id IN (SELECT id FROM Elokuvat WHERE vuosi=1940);nimi
----------
Fantasia
Pinocchio Huomaa, että äskeinen kysely toimii samoin kuin tämä kysely:
SELECT nimi FROM Elokuvat WHERE vuosi=1940;Tapaus 4: Alikysely esiintyy jossain muussa kyselyn osassa
Alikysely voi esiintyä lähes missä tahansa kyselyn osassa,
kunhan se palauttaa sopivan arvon.
Esimerkiksi seuraava alikysely LIMIT-osassa
valitsee puolet taulun riveistä:
SELECT nimi FROM Elokuvat LIMIT (SELECT COUNT(*) FROM Elokuvat)/2;nimi
----------
Lumikki
Fantasia Tässä tapauksessa alikysely palauttaa arvon 5, minkä seurauksena taulusta haetaan 5 / 2 = 2 (pyöristys alaspäin) riviä.
15.2.2020
Harjoitustyön tehokkuustestissä on ohjeena suorittaa lisäyskomennot saman transaktion sisällä. Mitä tämä tarkoittaa ja miksi näin pitäisi tehdä?
Tarkastellaan seuraavaa Java-koodia, joka luo taulun Testi
ja lisää siihen miljoona riviä:
Statement s = db.createStatement();
s.execute("CREATE TABLE Testi (x INTEGER)");
for (int i = 1; i <= 1000000; i++) {
PreparedStatement p = db.prepareStatement("INSERT INTO Testi (x) VALUES (?)");
p.setInt(1,i);
p.executeUpdate();
}Tässä jokainen INSERT-komento on erillinen transaktio
(koska oletuksena näin on), minkä vuoksi koodi toimii hitaasti.
Koodin suorituksessa voisi mennä ehkä tunti aikaa.
Saamme kuitenkin tehostettua koodia huomattavasti tekemällä lisäykset transaktion sisällä. Tämä onnistuu muuttamalla silmukkaa näin:
s.execute("BEGIN TRANSACTION");
for (int i = 1; i <= 1000000; i++) {
PreparedStatement p = db.prepareStatement("INSERT INTO Testi (x) VALUES (?)");
p.setInt(1,i);
p.executeUpdate();
}
s.execute("COMMIT");Tämän seurauksena koodi vie aikaa vain noin kymmenen sekuntia.
Itse asiassa voimme parantaa vielä koodia siirtämällä INSERT-komennon
valmistelun silmukan ulkopuolelle, jolloin se täytyy tehdä vain kerran:
s.execute("BEGIN TRANSACTION");
PreparedStatement p = db.prepareStatement("INSERT INTO Testi (x) VALUES (?)");
for (int i = 1; i <= 1000000; i++) {
p.setInt(1,i);
p.executeUpdate();
}
s.execute("COMMIT");Tämä koodi vie aikaa vain noin sekunnin – hieno parannus tehokkuuteen.
12.2.2020
SQLitessä ei ole erillistä tyyppiä ajanhetkien
(päivämäärä/kellonaika) käsittelyä varten,
mutta ajanhetket voi tallentaa merkkijonoina (esim. muoto
yyyy-mm-dd hh:mm:ss toimii) ja käyttää sitten SQLiten tarjoamia funktioita.
Esimerkiksi voimme luoda näin taulun Tapahtumat,
jossa on ajanhetkeä kuvaava sarake aika, ja lisätä siihen sisältöä:
sqlite> CREATE TABLE Tapahtumat(id INTEGER PRIMARY KEY, aika TEXT);
sqlite> INSERT INTO Tapahtumat(aika) VALUES ('2020-02-12 15:30:00');
sqlite> INSERT INTO Tapahtumat(aika) VALUES ('2020-02-12 18:30:00');
sqlite> INSERT INTO Tapahtumat(aika) VALUES ('2020-02-12 18:45:00');Yksi kätevä funktio on strftime, jonka avulla voi kysyä ajanhetkeen
liittyvää tietoa. Esimerkiksi seuraava kysely hakee joka rivistä
ajanhetken tuntiosan:
sqlite> SELECT strftime('%H',aika) FROM Tapahtumat;
15
18
18Seuraava kysely puolestaan hakee tapahtumat, joissa minuuttiosa on 30:
sqlite> SELECT aika FROM Tapahtumat WHERE strftime('%M',aika)='30';
2020-02-12 15:30:00
2020-02-12 18:30:00Lisää tietoa funktion strftime mahdollisuuksista ja SQLiten
muista asiaan liittyvistä funktioista löydät
tästä.
Huomaa, että olisimme voineet luoda taulun myös näin:
sqlite> CREATE TABLE Tapahtumat(id INTEGER PRIMARY KEY, aika DATETIME);SQLitessä ei ole tyyppiä DATETIME, mutta kuten blogin edellisessä
kirjoituksessa havaittiin, tyypin voi halutessaan tekaista itse.
Tässä tapauksessa etuna on, että taulun määrittelystä näkee selkeämmin,
että sarakkeeseen on tarkoitus tallentaa ajanhetki.
10.2.2020
SQLitessä sarakkeen tyyppi on melko häilyvä käsite. Tästä antaa näytteen seuraava keskustelu SQLiten kanssa:
sqlite> CREATE TABLE Tuotteet(nimi TEXT, hinta INTEGER);
sqlite> INSERT INTO Tuotteet (nimi,hinta) VALUES ('nauris','kallis');
sqlite> SELECT * FROM Tuotteet;
nauris|kallisTaulussa Tuotteet sarakkeen hinta tyyppi on INTEGER,
mutta SQLite kuitenkin hyväksyy hinnaksi merkkijonon.
Ideana SQLitessä on, että sarakkeen tyyppi on suositeltu tyyppi sarakkeessa olevalle tiedolle, mutta sarakkeeseen pystyy laittamaan mitä tahansa muutakin tietoa.
Itse asiassa voimme jopa keksiä tyypin nimen itse:
sqlite> CREATE TABLE Tuotteet(nimi TEXT, hinta HASSU);Tässä sarakkeen tyyppinä on HASSU (jota ei ole todellisuudessa olemassa),
mutta SQLite ei hetkahda asiasta vaan luo taulun ja sitä voi käyttää.
Tässä taulussa sarakkeessa hinta voi olla mitä tahansa tietoa, kuten yleensäkin.
7.2.2020
Yksi harjoitustyön osa on mitata, kauanko SQL-komentojen suorittaminen vie aikaa. Miten tämä kannattaisi tehdä?
Hyvä tapa on käyttää ohjelmointikielen tarjoamaa menetelmää hakea kulunut aika jostain kiinteästä ajanhetkestä. Tämän ajan voi hakea ennen komentojen suoritusta ja sen jälkeen, jolloin aikojen erotus kertoo suoritusajan.
Javassa sopiva metodi on System.nanoTime,
joka hakee kuluneen ajan nanosekunteina
(sekunnissa on miljardi nanosekuntia):
long aika1 = System.nanoTime();
// komentojen suoritus
long aika2 = System.nanoTime();
System.out.println("Aikaa kului "+(aika2-aika1)/1e9+" sekuntia");Pythonissa puolestaan moduulin time funktio time
hakee ajan sekunteina:
from time import time
aika1 = time()
# komentojen suoritus
aika2 = time()
print("Aikaa kului",aika2-aika1,"sekuntia")31.1.2020
Arvo NULL tarkoittaa SQL:ssä, että jotain tietoa ei ole saatavilla.
Tämä on joissain tilanteissa kätevää, mutta voi myös aiheuttaa ongelmia ja yllätyksiä.
Jos NULL esiintyy kaavassa, se hävittää kaiken ympärillään.
Esimerkiksi NULL+1 on NULL ja samoin 5*NULL+2 on NULL.
NULL onkin selkeästi eri asia kuin luku nolla.
Huomaa, että SQLite ei näytä kyselyn tuloksessa tekstiä NULL vaan vain tyhjää:
sqlite> SELECT NULL;
sqlite> SELECT NULL+1;
sqlite> SELECT 5*NULL+2;Joskus kyselyn osana on lauseke, joka saattaa olla NULL,
ja haluamme muuttaa mahdollisen arvon NULL luvuksi nolla.
Tyypillinen tilanne on, kun kyselyssä esiintyy LEFT JOIN,
GROUP BY sekä koostefunktio SUM,
joka antaa yleensä sarakkeen arvojen summan mutta NULL silloin,
kun riviä ei ole yhdistetty.
Tähän on ainakin kolme mahdollista tapaa:
- Funktion
IFNULL(a,b)arvo ona, josaei oleNULL, jab, josaonNULL. EsimerkiksiIFNULL(5,0)on5jaIFNULL(NULL,0)on0. - Funktio
COALESCE(...)antaa listan ensimmäisen arvon, joka ei oleNULL, tai arvonNULL, jos jokainen arvo onNULL. FunktioIFNULLon funktionCOALESCEerikoistapaus, kun parametrien määrä on kaksi. EsimerkiksiCOALESCE(5,0)on5jaCOALESCE(NULL,0)on0. - Voimme myös käyttää
CASE-rakennetta:CASE WHEN a IS NULL THEN b ELSE a ENDtarkoittaa samaa kuinIFNULL(a,b). Tämä ei ole kuitenkaan kovin tyylikäs tapa.
29.1.2020
Mitä tarkkaan ottaen tapahtuu, kun LEFT JOIN yhdistää kolme tai useampia tauluja?
Tarkastellaan esimerkkinä seuraavaa tilannetta:
Seuraava kysely ilmoittaa jokaiselle asiakkaalle ostoskorin tuotteiden määrän:
SELECT A.nimi, COUNT(T.id)
FROM Asiakkaat A LEFT JOIN Ostokset O ON A.id = O.asiakas_id
LEFT JOIN Tuotteet T ON T.id = O.tuote_id
GROUP BY A.id;nimi COUNT(T.id)
---------- -----------
Uolevi 2
Maija 3
Aapeli 0Mitä tässä siis tapahtuu? Tällaisen kyselyn voi tulkita aina kahden periaatteen avulla:
LEFT JOINvalitsee kaikki rivien yhdistelmät, jotka toteuttavat annetun ehdon, ja lisäksi kerran jokaisen vasemman taulun rivin, jota ei muuten valittu kertaakaan.- Jos tauluja on yli kaksi, yhdistämiset tapahtuvat vasemmalta oikealle.
Äskeisen kyselyn tulkinnassa voimme lähteä liikkeelle tilanteesta, jossa yhdistämme ensin kaksi ensimmäistä taulua. Saamme paremman kuvan asiasta, kun haemme kaikki sarakkeet ja poistamme ryhmittelyn:
SELECT * FROM Asiakkaat A LEFT JOIN Ostokset O ON A.id = O.asiakas_id;id nimi asiakas_id tuote_id
---------- ---------- ---------- ----------
1 Uolevi 1 2
1 Uolevi 1 5
2 Maija 2 1
2 Maija 2 4
2 Maija 2 5
3 Aapeli Koska Aapelin ostoskorissa ei ole mitään tuotteita,
tulostauluun ilmestyy ylimääräinen rivi Aapelille,
jossa taulun Ostokset sarakkeiden arvoina on NULL.
Lisätään sitten peliin kolmas taulu:
SELECT * FROM Asiakkaat A LEFT JOIN Ostokset O ON A.id = O.asiakas_id
LEFT JOIN Tuotteet T ON T.id = O.tuote_id;id nimi asiakas_id tuote_id id nimi hinta
---------- ---------- ---------- ---------- ---------- ---------- ----------
1 Uolevi 1 2 2 porkkana 5
1 Uolevi 1 5 5 selleri 4
2 Maija 2 1 1 retiisi 7
2 Maija 2 4 4 lanttu 8
2 Maija 2 5 5 selleri 4
3 Aapeli Tässä tapauksessa vasen taulu on Asiakkaat A LEFT JOIN Ostokset O
ja oikea taulu on Tuotteet T.
Koska vasemman taulun Aapelin rivi ei täsmää mihinkään oikean taulun riviin,
tästä tulee taas ylimääräinen rivi, jossa sarakkeiden arvoina on NULL.
Tämä on tavallinen idea kolmen tai useamman taulun LEFT JOIN -kyselyissä:
mahdolliset NULL-rivit seuraavat mukana jokaisen uuden taulun yhdistämisessä.
Tämän vuoksi jos ensimmäinen yhdistys on LEFT JOIN,
niin myös muidenkin pitää olla.
25.1.2020
Harva asia on puhuttanut kurssilla yhtä paljon kuin SQL Trainerin tehtävät 93 ja 94, joissa tulee vertailla käyttäjien kaverilistoja. Kuinka moinen onnistuu SQL:llä?
Hyvä askel kohti ratkaisua on koettaa ensin laskea jokaiselle
käyttäjäparille, montako yhteistä kaveria heillä on.
Koska jokainen kaverisuhde on oma rivinsä taulussa Kaverit,
meidän täytyy pohjimmiltaan vertailla kaverisuhteita toisiinsa
yksi kerrallaan.
Seuraava kysely käy läpi päätasolla kaikki käyttäjäparit ja hakee heidän nimensä. Kolmas tulostaulun sarake tulee alikyselystä, joka käy läpi käyttäjien kaverilistat ja vertailee niitä toisiinsa. Tuloksena saamme jokaisesta parista tietoon yhteisten kaverien määrät.
SELECT A.nimi, B.nimi, (SELECT SUM(C.kaveri_id=D.kaveri_id)
FROM Kaverit C, Kaverit D
WHERE C.kayttaja_id=A.id AND D.kayttaja_id=B.id) tulos
FROM Kayttajat A, Kayttajat B;Tehtävän 93 tilanteessa kyselyn tulos on seuraava:
nimi nimi tulos
---------- ---------- ----------
Uolevi Uolevi 2
Uolevi Maija 0
Uolevi Liisa 1
Uolevi Kaaleppi 2
Maija Uolevi 0
Maija Maija 1
Maija Liisa 1
Maija Kaaleppi 0
Liisa Uolevi 1
Liisa Maija 1
Liisa Liisa 2
Liisa Kaaleppi 1
Kaaleppi Uolevi 2
Kaaleppi Maija 0
Kaaleppi Liisa 1
Kaaleppi Kaaleppi 2Tulostaulusta selviää esimerkiksi, että Uolevilla ja Kaalepilla on kaksi yhteistä kaveria sekä että Maijalla ja Liisalla on yksi yhteinen kaveri.
Ehkä oudoin kohta kyselyssä on summa SUM(C.kaveri_id=D.kaveri_id).
Tässä käytetään hyväksi SQL:n ehtojen ominaisuutta:
jos ehto on tosi, niin sen tulos on luku 1.
Alikysely vertaa toisiinsa kaverilistoja ja ehdon tulos on 1
aina, kun löytyy yhteinen kaveri.
Niinpä näiden tulosten summa kertoo, montako yhteistä kaveria on yhteensä.
Tällä idealla on mahdollista ratkaista tehtävät 93 ja 94. Niissä pitää vielä ryhmitellä tietoa sopivasti sekä verrata yhteisten kaverien määrää kaikkien kavereiden määrään.
23.1.2020
Tarkastellaan tilannetta, jossa taulussa Elokuvat on elokuvia
ja haluamme selvittää, mikä on suurin määrä elokuvia,
jotka ovat ilmestyneet samana vuonna.
Esimerkiksi taulussa
id nimi vuosi
---------- ---------- ----------
1 Lumikki 1937
2 Fantasia 1940
3 Pinocchio 1940
4 Dumbo 1941
5 Bambi 1942 haluttu tulos on 2, koska vuonna 1940 ilmestyi kaksi elokuvaa.
Tämä on vähän hankalalta vaikuttava tilanne,
koska meidän tulisi tehdä sisäkkäin kyselyt
COUNT, joka laskee ilmestymismääriä,
ja sitten MAX, joka hakee suurimman arvon.
SQL ei salli kuitenkaan kyselyä SELECT MAX(COUNT(vuosi)) tai vastaavaa.
Voimme ottaa kuitenkin lähtökohdaksi kyselyn, joka ryhmittelee elokuvat vuoden mukaan ja hakee jokaisesta ryhmästä elokuvien määrän:
SELECT COUNT(*) FROM Elokuvat GROUP BY vuosi;COUNT(*)
----------
1
2
1
1 Näistä luvuista pitää vielä saada haettua suurin, mikä onnistuu alikyselyn avulla.
Tässä tapauksessa kätevä tapa on käyttää alikyselyä niin,
että sen tulos on pääkyselyn FROM-osassa,
jolloin alikysely luo taulun, josta pääkysely hakee tietoa:
SELECT MAX(c) FROM (SELECT COUNT(*) c FROM Elokuvat GROUP BY vuosi);MAX(c)
----------
2 Entä voisiko tehtävän ratkaista ilman alikyselyä? Kyllä, koska voimme järjestää tulokset suurimmasta pienimpään ja valita tulostaulun ensimmäisen rivin:
SELECT COUNT(*) c FROM Elokuvat GROUP BY vuosi ORDER BY c DESC LIMIT 1;c
----------
2 21.1.2020
SQL:ssä rivien järjestämisen ORDER BY -osassa voi toteuttaa myös
ilmoittamalla lukuna, monennenko sarakkeen mukaan järjestetään.
Esimerkiksi seuraava kysely järjestää rivit toisen sarakkeen
eli nimen mukaan:
SELECT * FROM Tuotteet ORDER BY 2;id nimi hinta
---------- ---------- ----------
4 lanttu 8
3 nauris 4
2 porkkana 5
1 retiisi 7
5 selleri 4Tässä ei ole vielä mitään yllättävää, mutta seuraava kysely näyttää,
että SQL:ssä 1+1 ei aina ole 2.
Tämä kysely ei järjestä rivejä toisen sarakkeen mukaan:
SELECT * FROM Tuotteet ORDER BY 1+1;id nimi hinta
---------- ---------- ----------
1 retiisi 7
2 porkkana 5
3 nauris 4
4 lanttu 8
5 selleri 4 Tässä on kysymys siitä, että sarakkeen järjestysnumeron antaminen toimii vain silloin, kun annetaan yksittäinen luku. Jos kuitenkin annetaan jokin muu lauseke, järjestäminen tapahtuu tämän lausekkeen arvon mukaan.
Lauseke 1+1 ei ole kovin mielekäs järjestämisessä,
koska sen arvo jokaiselle riville on sama eli jokainen rivi
on "yhtä suuri".
Tässä on järkevämpi esimerkki, jossa järjestetään lausekkeen
LENGTH(nimi) mukaan eli nimen pituuden mukaan:
SELECT * FROM Tuotteet ORDER BY LENGTH(nimi);id nimi hinta
---------- ---------- ----------
3 nauris 4
4 lanttu 8
1 retiisi 7
5 selleri 4
2 porkkana 5 Toinen esimerkki on kurssimateriaalissa mainittu
ORDER BY RANDOM(), jossa jokaiselle riville
tulee satunnainen luku järjestyksen perustaksi.
20.1.2020
LEFT JOIN on usein mystiseltä tuntuva hakutapa SQL:n perusteiden opettelussa.
Siinäkin auttaa kuitenkin hakea ensin kaikki sarakkeet ilman lisäehtoja,
jotta näkee paremmin, mitä kyselyssä oikeasti tapahtuu.
Tarkastellaan esimerkkiä, jossa tietokannassa on kursseja ja opettajia.
Taulun Opettajat sisältö on:
id nimi
---------- ----------
1 Kaila
2 Kivinen
3 Laaksonen Taulun Kurssit sisältö on:
id nimi opettaja_id
---------- ---------------------- -----------
1 Ohjelmoinnin perusteet 1
2 Tietorakenteet ja algo 2
3 Laskennan mallit 2 Lopullinen tavoitteemme on hakea jokaisesta opettajasta kurssien määrä,
mutta tehdään ensin koemielessä puhdas LEFT JOIN -kysely:
SELECT * FROM Opettajat O LEFT JOIN Kurssit K ON K.opettaja_id=O.id;id nimi id nimi opettaja_id
---------- ---------- ---------- ---------------------- -----------
1 Kaila 1 Ohjelmoinnin perusteet 1
2 Kivinen 3 Laskennan mallit 2
2 Kivinen 2 Tietorakenteet ja algo 2
3 Laaksonen Tästä näkyy LEFT JOIN -kyselyn idea: jos jotain vasemman taulun riviä
ei onnistuta parittamaan minkään oikean taulun rivin kanssa
(kuten tässä esimerkissä taulun Opettajat riviä 3),
niin tulostauluun ilmestyy lisärivi, jossa on kyseisen vasemman taulun rivin
sisältö ja oikean taulun rivin kohdalla kaikissa sarakkeissa NULL.
Nyt voimme koettaa viimeistellä kyselyn lisäämällä haettavat sarakkeet ja ryhmittelyn:
SELECT O.nimi, COUNT(*) FROM Opettajat O LEFT JOIN Kurssit K ON K.opettaja_id=O.id GROUP BY O.id;nimi COUNT(*)
---------- ----------
Kaila 1
Kivinen 2
Laaksonen 1 Jotain meni kuitenkin pieleen, koska kysely väittää, että Laaksosella olisi yksi kurssi.
Ongelmana on, että COUNT(*) laskee mukaan kaikki rivit,
myös vaikka osassa sarakkeista olisi NULL.
Ratkaisuna on tarkentaa laskentaa näin:
SELECT O.nimi, COUNT(K.id) FROM Opettajat O LEFT JOIN Kurssit K ON K.opettaja_id=O.id GROUP BY O.id;nimi COUNT(K.id)
---------- ----------
Kaila 1
Kivinen 2
Laaksonen 0Tässä erona on, että COUNT(K.id) laskee mukaan vain rivit,
joissa sarakkeen K.id arvona ei ole NULL.
Nyt kysely toimii oikein eikä Laaksosella näy kursseja.
Huomaa myös sanojen ON ja WHERE ero:
jos ON-osassa oleva ehto ei päde,
vasemman taulun rivi pääsee kuitenkin mukaan kerran,
mutta jos WHERE-osassa oleva ehto ei päde,
vasemman taulun rivi ei pääse mukaan lainkaan.
Seuraava kysely havainnollistaa asiaa:
SELECT * FROM Opettajat O LEFT JOIN Kurssit K WHERE K.opettaja_id=O.id;id nimi id nimi opettaja_id
---------- ---------- ---------- ---------------------- -----------
1 Kaila 1 Ohjelmoinnin perusteet 1
2 Kivinen 3 Laskennan mallit 2
2 Kivinen 2 Tietorakenteet ja algo 2 Erona aiempaan on, että sanan ON sijasta kyselyssä on sana WHERE.
Niinpä vasemmasta taulusta ei tule riviä 3.
Tässä on vielä esimerkki, jossa kyselyssä on sekä ON että WHERE:
SELECT * FROM Opettajat O LEFT JOIN Kurssit K ON K.opettaja_id=O.id WHERE O.id<>2;id nimi id nimi opettaja_id
---------- ---------- ---------- ---------------------- -----------
1 Kaila 1 Ohjelmoinnin perusteet 1
3 LaaksonenNyt toisaalta rivi 3 tulee mukaan ON-osan ansiosta mutta riviä 2
ei tule mukaan WHERE-osan takia.
Jos molemmat ehdot laittaisi ON-osaan, niin tulos olisi taas toinen:
SELECT * FROM Opettajat O LEFT JOIN Kurssit K ON K.opettaja_id=O.id AND O.id<>2;id nimi id nimi opettaja_id
---------- ---------- ---------- ---------------------- -----------
1 Kaila 1 Ohjelmoinnin perusteet 1
2 Kivinen
3 LaaksonenNyt vasemmasta taulusta tulee mukaan sekä rivi 2 että rivi 3,
joissa molemmissa oikean taulun rivin kohdalla on NULL-arvoja.
17.1.2020
Kurssikanavalla heräsi kysymys: miten SQL:ssä voi laskea riveille järjestysluvut?
Tarkastellaan esimerkkinä taulua Nimet,
jonka jokaisella rivillä on id-numero ja nimi:
id nimi
---------- ----------
1 Uolevi
2 Maija
3 Liisa
4 VihtoriTavoitteemme on tehdä kysely, joka antaa nimet aakkosjärjestyksessä ja lisäksi jokaisen nimen järjestysluvun eli tiedon siitä, monesko nimi kyseinen nimi on järjestyksessä:
nimi
---------- ----------
Liisa 1
Maija 2
Uolevi 3
Vihtori 4Yksi tapa muodostaa kysely on laatia alikysely, joka laskee jokaisella rivillä, monenko rivin nimi on aakkosissa korkeintaan yhtä suuri kuin kyseisen rivin nimi:
SELECT nimi, (SELECT COUNT(*) FROM Nimet WHERE nimi <= N.nimi) c FROM Nimet N ORDER BY c;Tämä on usein kätevä tekniikka: voimme laskea lukumäärän tai jotain muuta tietoa niistä riveistä, jotka ovat järjestyksessä ennen riviä.
Alikyselyn voi usein korvata usean taulun kyselyllä, ja niin on myös tässä tehtävässä. Voimme muodostaa alikyselyä käyttävän kyselyn sijasta seuraavan kahden taulun kyselyn:
SELECT A.nimi, COUNT(*) c FROM Nimet A, Nimet B WHERE B.nimi <= A.nimi GROUP BY A.nimi ORDER BY c;Tässä muodostamme kaikki kahden nimen parit, joissa jälkimmäinen nimi on aakkosissa korkeintaan yhtä suuri kuin ensimmäinen nimi. Sitten ryhmittelemme tulokset ensimmäisen nimen mukaan, jolloin rivien määrät antavat halutun tuloksen.
Tämän kyselyn toiminta on helpompaa ymmärtää, jos haemme ensin kaikki tulokset ilman ryhmittelyä ja sarakkeiden valintaa:
SELECT * FROM Nimet A, Nimet B WHERE B.nimi <= A.nimi;id nimi id nimi
---------- ---------- ---------- ----------
1 Uolevi 1 Uolevi
1 Uolevi 2 Maija
1 Uolevi 3 Liisa
2 Maija 2 Maija
2 Maija 3 Liisa
3 Liisa 3 Liisa
4 Vihtori 1 Uolevi
4 Vihtori 2 Maija
4 Vihtori 3 Liisa
4 Vihtori 4 Vihtori Tämä on yleensäkin hyvä keino suunnitella kyselyä: tulostamme ensin kaikki tiedot ja alamme sitten miettiä ryhmittelyä ja haettavan tiedon rajoittamista.
Vielä yksi tapa ratkaista tehtävä on käyttää SQLiten
ikkunafunktiota ROW_NUMBER:
SELECT nimi, ROW_NUMBER() OVER (ORDER BY nimi) FROM Nimet;Tämä on kurssin ulkopuolista asiaa, mutta jotkut ovat löytäneet funktion Googlella ja se sopiikin hyvin tähän tilanteeseen. Kurssilla saa ilman muuta hakea tietoa muualta ja käyttää kaikkia SQLiten ominaisuuksia, mutta toisaalta tehtävissä voi myös luottaa siihen, että ne on mahdollista ratkaista kurssin materiaalin perusteella.