SQL aloittelijoille
Mureakuha
Sisällysluettelo |
Sisältö
Tässä artikkelissa perehdytään neljään yleisimpään SQL-kyselyyn (SELECT, INSERT, UPDATE, DELETE), sekä joidenkin tarpeellisten funktioiden käyttöön. Artikkeli on lähinnä tarkoitettu sellaisille jotka haluavat oppia SQL:n perusteet. Tämän artikkelin esimerkkien pitäisi toimia suurimmassa osassa sovelluksia jotka käyttävät SQL:ää vaikka niiden syntaksissa saattaakin paikoin olla eroavaisuuksia.
Esimerkeissä käytettävät taulut
Artikkelissa viitataan seuraaviin tauluihin. Kuvitellaan että Asiakas-taulu sisältää yrityksen kaikki asiakkaat, Tuotteet-taulu kaikki yrityksen myymät tuotteet ja niiden hinnat, sekä Kaupat-taulu, joka sisältää yrityksen myyntitapahtumat.
Taulujen rakenne
Taulu Kenttä Tyyppi Asiakkaat Asiakasnro Laskuri Nimi Teksti Tuotteet Tuotenro Laskuri Tuotenimi Teksti Hinta Valuutta Varasto Luku Kaupat Paivamaara Paivamaara Asiakasnro Luku Tuotenro Luku Maara Luku
Taulujen esimerkkitiedot
Asiakkaat-taulu: 1 Pekka 2 Matti 3 Päivi 4 Heli Tuotteet-taulu: 1 Auto 10000€ 10 2 Lapio 30€ 100 3 Nenäliina 0.01€ 10000 4 Takki 45€ 200 Kaupat-taulu: 01.02.2005 2 1 1 02.03.2005 4 2 5 23.03.2005 1 1 2 17.03.2005 2 1 1 17.03.2005 2 3 2000 17.03.2005 2 4 2 15.04.2005 3 3 56 20.04.2005 3 2 12 22.04.2005 1 1 65 13.05.2005 3 1 10
Nyrkkisääntönä taulun suunnitelussa tulisi olla, ettei siihen voi missään olosuhteissa tulla kahta täysin identtistä tietoa. Kaupat -taulussa olisi periaatteessa mahdollista, että joku hakee saman päivän aikana samaa tuotetta useamman kerran. (esim. Matti takin aamulla ja toisen samanlaisen iltapäivällä vaikkapa vaimolleen.) Ongelma hoituu helposti laskurilla ja sitä kannattaa käyttää ennemmin liikaa kuin liian vähän.
SQL-kyselyt
Select-lause
Select (kentät) From (taulu) [where, Order By, Group By]
Select-lauseella voit hakea tietoa tietokannan tauluista tiettyjen hakuehtojen ja määritysten mukaisesti. Oletetaan esimerkiksi että haluat kaikki tiedot Asiakkaat-taulusta. Tällöin voit käyttää seuraavanlaista SQL-lausetta.
Kysely: SELECT * FROM Asiakkaat Tulos: Asiakasnro Nimi 1 Pekka 2 Matti 3 Päivi 4 Heli
Jos käytät *-merkkiä, tarkoittaa se sitä, että haetaan jokainen taulun kenttä. Voit myös hakea vain tietyn kentän tiedot (tai erotella haluamasi kentät pilkulla Select Asiakasnumero, Nimi...):
Kysely: SELECT Nimi FROM Asiakkaat Tulos: Nimi Pekka Matti Päivi Heli
Haluatkin nimet aakkosjärjestykseen? Ei hätää Order By-määre auttaa. Sille annetaan kentän nimi, sekä järjestetäänkö tulos nousevaan (asc) vai laskevaan (desc) järjestykseen. Jos et anna nouseva/laskeva määrettä, niin oletuksena on nouseva järjestys. Voit käyttää Order By-määrettä myös Where-ehdon kanssa jos haluat, muista tällöin laittaa Where-ehto ennen Order By:tä.
Kysely: SELECT Nimi FROM Asiakkaat ORDER BY Nimi ASC Tulos: Nimi Heli Matti Pekka Päivi
Jos haluat rajata haun koskemaan ainoastaan asiakasta jonka nimi on esimerkiksi 'Pekka' voit tehdä sen lisäämällä Where-ehdon Select-lauseen perään. Huomaa, että Pekka on laitettu '-merkkien sisään. Koska Nimi-kenttä on tekstityyppinen täytyy toimia näin, jos kysely suoritettaisiin luku-tyyppiseen Asiakasnro-kenttään (...Where Asiakasnro = 1) ei '-merkkejä käytettäisi.
Kysely: SELECT * FROM Asiakkaat WHERE Nimi = 'Pekka' Tulos: Asiakasnro Nimi 1 Pekka
Huomaa, että jos taulu sisältäisi kaksi Pekka-nimistä henkilöä (joilla on kuitenkin molemmilla oma asiakasnumeronsa), niin kysely palauttaisi nuo molemmat "Pekat". Tästä syystä on hyvä olla tietueet täysin yksilöivä asiakasnro-kenttä.
Where-ehdolle voi antaa monta määrettä lisäämällä ehtojen väliin aina And(ja) tai Or(tai). Seuraavissa esimerkeissä ensimmäinen ei palauta lainkaan tietueita koska Pekan Asiakasnumero ei ole 2 ja toinen palauttaa kaksi tietuetta koska ehdossa on "tai"-määre.
Kysely: SELECT * FROM Asiakkaat WHERE Nimi = 'Pekka' AND Asiakasnro = 2 Tulos: (ei tietueita) Kysely: SELECT * FROM Asiakkaat WHERE Nimi = 'Pekka' OR Asiakasnro = 2 Tulos: Asiakasnro Nimi 1 Pekka 2 Matti
Sitten muutamia hyödyllisiä funktioita. Haluat esimerkiksi tietää montako asiakasta yritykselläsi on. Voit käyttää hyödyksesi
Count-funktiota seuraavasti (Huomaa As-määre, jolla voidaan antaa sarakkeelle vaihtoehtoinen nimi, tässä tilateessa se on hyödyllinen koska muutoin tietokanta antaisi tietueelle itse keksimänsä nimen tyyliin "Exp001" tms.)
Kysely: SELECT Count(*) AS Asiakasmaara FROM Asiakkaat Tulos: Asiakasmaara 4
Kysely siis laskee montako tietuetta taulu sisältää. Voit tietysti myös rajata kyselyä Where-ehdolla.
Sitten hieman monimutkaisempiin kyselyihin. Mitä jos haluammekin tietää mitä Pekka on yritykseltämme ostanut. Tämä tieto on tallennettu Kaupat-tauluun. Voimme tietysti tehdä yksinkertaisen kyselyn jossa haemme kaikki Kaupat-taulun tietueet jotka liittyvät pekkaan:
Kysely: SELECT * FROM Kaupat WHERE Asiakasnro = 1 Tulos: Paivamaara Asiakasnro Tuotenro Maara 23.03.2005 1 1 2 22.04.2005 1 1 65
Näemme, että pekka on ostanut tuotetta numero yksi 23.3.2005 kaksi kappaletta ja 22.4.2005 65 kappaletta. Tämä ei kerro tavalliselle käyttäjälle mikä tuote on kyseessä. Tarvitsemme siis kyselyyn myös tuotteen nimen selväkielisenä tekstinä. Koska tuotteen nimi on tallennettu toiseen tauluun (Kaupat) niin joudumme käyttämään ns. liitoskyselyä. En paneudu tässä artikkelissa liitoskyselyihin syvällisemmin vaan käyn läpi ainoastaan Inner Join -tyyppisen liitoskyselyn.
Eli haluamme hakea tietoa kahdesta taulusta niin että vastaukseen tulee tietoa näistä molemmista. Teemme kyselyn joka tuottaa muuten samanlaisen vastauksen kuin yllä, mutta siinä on Tuotenro kenttä korvattu Tuotteet-taulun Tuotenimi-kentällä:
Kysely: SELECT Paivamaara, Asiakasnro, Tuotenimi, Maara FROM Kaupat INNER JOIN Tuotteet ON Kaupat.Tuotenro = Tuotteet.Tuotenro WHERE Asiakasnro = 1 Tulos: Paivamaara Asiakasnro Tuotenimi Maara 23.03.2005 1 Auto 2 22.04.2005 1 Auto 65
Kyselyssä kerrotaan ensin halutut kentät, joista Tuotenimi haetaan Tuotteet-taulusta ja muut Kaupat-taulusta. Inner Join -lauseessa kerrotaan mitkä taulut yhdistetään ja mitkä kentät vastaavat tauluissa toisiaan. Huomaa, että sekä Kaupat-, että Tuotteet-tauluissa on samanniminen kenttä Tuotenro. Jotta kentät voitaisiin erottaa toisistaan voidaan kentän eteen kirjoittaa taulun nimi ja piste.
Otetaan vielä yksi esimerkki josta nähdään Matin ostamien tavaroiden yhteishinta. Matti siksi, että hänellä näyttää olevan enemmän kauppoja tehtynä, voit tietysti käyttää mitä asiakasta haluat. Tässä tapauksessa tarvitaan Tuotteet-taulun Hinta-kenttää, joka kertoo meille paljonko yksi kappale tuotetta maksaa.
Kysely: SELECT Paivamaara, Tuotenimi, Maara, (Hinta*Maara) AS Lasku FROM Kaupat INNER JOIN Tuotteet ON Kaupat.Tuotenro = Tuotteet.Tuotenro WHERE Asiakasnro = 2 Tulos: Paivamaara Tuotenimi Maara Lasku 1.2.2005 Auto 1 10 000€ 17.3.2005 Auto 1 10 000€ 17.3.2005 Nenäliina 2000 20€ 17.3.2005 Takki 2 90€
Ei tämän enempää liitoksista, koska tämän on tarkoitus olla aloittelijoille.
Insert-lause
Insert Into (taulu) (kentät) Values (arvot)
Insert-lauseella voit lisätä tietoa tauluihin. Yksinkertaisuudessaan se toimii näin:
INSERT INTO Asiakkaat (Nimi) VALUES ('Antti')
Tai vaihtoehtoisesti näin:
INSERT INTO Asiakkaat SET Nimi = 'Antti'
Lisäämme asiakkaan Antti Asiakkaat-tauluun. Nimi asetetaan taas heittomerkkien sisään koska Nimi-kenttä on tekstityyppinen. Jos jokainen kenttä täytetään, silloin ei tarvitse määritellä sarakkeiden nimiä
Meidän ei tarvitse asettaa arvoa kentälle Asiakasnro koska se se on laskuri tyyppinen kenttä - tietokanta antaa kentälle automaattisesti seuraavan vapaan numeron kun uusi tietue luodaan.
Uusi tuote voitaisiin lisätä näin:
INSERT INTO Tuotteet (Tuotenimi, Hinta, Varasto) VALUES ('Vasara',20,100)
Tai vaihtoehtoisella kirjoitustavalla näin:
INSERT INTO Tuotteet SET Tuotenimi = 'Vasara', Hinta = 20, Varasto = 100
Vasaran hinnaksi tulisi 20 euroa ja varastosaldo on 100.
Update-lause
Update (taulu) Set (kenttä)=(arvo) [Where]
Update-lauseella voit muokata tauluissa jo olemassa olevaa tietoa. Tämän lauseen käytön kanssa kannattaa olla tarkka, sillä mikäli unohdat määrittää Where-ehdon tai se on määritelty väärin, korvataan kaikki taulussa olevat tiedot tähän määrittämälläsi uusilla tiedoilla.
Where-osa siis määrittää mitkä tietueet päivitetään.
Jos esimerkiksi käyttäisimme seuraavaa SQL-lausetta (jota emme halua tietenkään tehdä), niin jokaisen asiakkaan nimeksi asetettaisiin Liisa:
UPDATE Asiakkaat SET Nimi = 'Liisa'
Seuraava tapaus taas on hyvinkin mahdollinen. Haluamme muuttaa asiakkaan Heli (Asiakasnumero 4) nimeksi Liisa niin voimme tehdä sen näin:
UPDATE Asiakkaat SET Nimi = 'Liisa' WHERE Asiakasnro = 4
Huomaamme, että yksi tuotteistamme on hieman väärin nimetty ja hinnoiteltu. Päätämme päivittää tietoja:
UPDATE Tuotteet SET Tuotenimi = 'Auto - Ford KA', Hinta = 15000 WHERE Tuotenro = 1
Delete-lause
Delete From (taulu) [Where]
Delete-lauseella voidaan tauluissa olevia tietoja poistaa. Kuten Update-lauseessa, myös tässä on ensiarvoisen tärkeää määrittää Where-ehto oikein. Jos unohdat määrittää Where-ehdon kokonaan niin kysely poistaa taulusta kaikki tiedot ja voit menettää tuhansia tietueita bittiavaruuteen.
Eli jos haluamme vaikka poistaa tuotevalikoimastamme tuotteen Nenäliina (Tuotenro 3) niin se onnistuu seuraavasti:
DELETE FROM Tuotteet WHERE Tuotenro = 3
Lopuksi vielä esimerkki kuinka päivämääriä käytetään Where-ehdossa. Haluamme poistaa kaikki maksutapahtumat jotka on tehty ennen päivämäärää 1.4.2005. Päivämäärien syntaksi vaihetelee hieman käytettävästä sovelluksesta, mutta ainakin Microsoft Accessissa käytetään syntaksia #mm/dd/yyyy# (huomaa että kuukausi on ensimmäisenä). Usein käytetään myös muotoa 'dd.mm.yyyy'.
DELETE FROM Tuotteet WHERE Paivamaara < #4/1/2005#
