Kõikides õpetustes ja näidistes on kasutatavaks platvormiks Microsoft SQL Server 2008.
Tekstifunktsioonid
Tekstifunktsioonidest vaatame lähemalt järgmisi funktsioone:
- Left() - väljastab vasakult poolt nõutud arvu tähti
- Len() - väljastab sõna pikkuse
- Lower() - muudab sõna väiketähtedeks
- LTrim() - eemaldab tühikud sõna vasakult poolt
- Replace() - asendab otsitava sõna vajaminevaga
- Right() - väljastab paremalt poolt esimese tähe
- RTrim() - eemaldab tühikud sõna paremalt poolt
- Upper() - muudab sõna suurtähtedeks
Toome näited.
SELECT LEFT (eesnimi,1) as esitähed
Eelmine näide väljastab eesnimede esimesed tähed. Päringu tulemuse pealkirjaks on 'esitähed'. Kui number 1 asemel oleks 2, siis väljastatakse kaks esimest tähte.
SELECT LEN (eesnimi) as 'eesnimede pikkused'
Eelmine näide väljastab eesnimede pikkused arvuna. Päringu tulemuse pealkirjaks on 'eesnimede pikkused'.
SELECT LOWER (eesnimi) as 'väiketähed'
Eelmine näide väljastab eesnimed väiketähtedena. Päringu tulemuse pealkirjaks on 'väiketähed'.
SELECT LTRIM (eesnimi) as 'vasak pool tühikuvaba'
Eelmine näide väljastab eesnimed, kusjuures eelnevalt eemaldatakse vasakult poolt sisestatud tühikud. Päringu tulemuse pealkirjaks on 'vasak pool tühikuvaba'.
SELECT REPLACE (eesnimi, 'mati', 'martin')
Eelmine näide asendab kõik, kelle eesnimi on mati uue nimega martin.
SELECT RIGHT (eesnimi,2) as 'viimased tähed'
Eelmine näide väljastab eesnimede viimased tähed. Päringu tulemuse pealkirjaks on 'viimased tähed'. Kui number 2 asemel oleks 1, siis väljastatakse vaid viimane täht.
SELECT RTRIM (eesnimi) as 'parem pool tühikuvaba'
Eelmine näide väljastab eesnimed, kusjuures eelnevalt eemaldatakse paremalt poolt sisestatud tühikud. Päringu tulemuse pealkirjaks on 'parem pool tühikuvaba'.
SELECT UPPER (eesnimi) as 'suurtähed'
Eelmine näide väljastab eesnimed suurtähtedena. Päringu tulemuse pealkirjaks on 'suurtähed'.
30.06.2010
Eve Keerus-Jusupov
Teksti otsing
Teksti otsingu juures on käskluseks LIKE.
Otsingutingimusele on võimalik lisada:
- alljoon _ tähistab ühte suvalist sümbolit
või - protsendimärk % tähistab suvalist arvu suvalisi sümboleid
või - kandilised sulud [], kuhu saab esitada tähed, mida otsitakse.
Vaatame lähemalt.
SELECT * FROM [Eve].[dbo].inimesed where eesnimi LIKE 'Karlis'
Eelmine näide otsib andmebaasist nimega Eve tabelist nimega inimesed kõiki andmeid nendelt isikutelt, kelle eesnimi on Karlis. Funktsioon ei ole tõstutundlik, leiab sõnu nii 'karlis', KARLIS', kui ka 'karLis' ja muud variandid suur- ning väiketähtede kombinatsioonina.
where eesnimi LIKE 'M%'
Eelmine näide otsib eesnimesid, mis algavad tähega M ja peale M tähte tulevad mis iganes tähed või sümbolid.
where eesnimi LIKE '%a%'
Eelmine näide otsib eesnimesid, kus kõigepealt on mingid sümbolid, seejärel a-täht ja lõpus jälle mingid sümbolid. Samas leitakse ka näiteks sõna ma või ka lihtsalt täht a.
where eesnimi LIKE '_ati'
Eelmine näide otsib eesnimesid, kus esimest sümbolit ei teata ning järgnevad tähed 'ati'. Seega tulemuseks võib olla nii Mati kui Kati.
where eesnimi LIKE '%[vn]e'
Eelmine näide otsib eesnimesid, kus alguses on mingid sümbolid, seejärel tulevad tähed v või n ja peale seda on täht e. Tulemuseks võib olla näiteks Eve või Ene, aga ka Ave või Meene.
Kokkuvõtvalt võib öelda, et LIKE funktsiooni kasutatakse tihti ja on kõikvõimalikes teksti otsingutes väga hea abimees.
03.04.2010
Eve Keerus-Jusupov
Agregaatfunktsioonid
Agregaatfunktsioonid on funktsioonid, mis tagastavad ühe väärtuse, mis on sisestatud andmete põhjal tehtav arvutuste tulem. Näiteks on meil terve hulk kaupasid, millel on erinevad hinnad. Agregaatfunktsioon AVG() tagastab kõikide kaubahindade keskmise väärtuse. Kõige enam kasutatavad agregaatfunktsioonid on:
* AVG() - aritmeetilise keskmise arvutamiseks
* COUNT() - loendamiseks
* MAX() - suurima väärtuse leidmiseks
* MIN() - kõige väiksema väärtuse leidmiseks
* SUM() - summa leidmiseks
Siia hulka kuuluvad ka statistilised funktsioonid nagu:
* STDEV() - standardhälbe leidmiseks
* VAR() - dispersiooni leidmiseks valimi põhjal
* VARP() - dispersiooni leidmiseks üldkogumi põhjal
Aga vaatame lähemalt ja teeme järgmised 10 päringut.
1. Avame kõigepealt maakondade tabeli ja vaatame kas kõik maakonnad on olemas.
Loenduseks sobib hästi funktsioon COUNT().
Süntaks on
SELECT count (*)
2. Praegu tuli eelmise päringu väärtuseks 15 ja tulba pealkirjaks No colums name. Seega on raske aru saada, mille tulemus on 15. Edaspidi paneme väärtustele pealkirjad, et oleks tulem selge.
SELECT count (*) as 'Maakondasid kokku'
Kui pealkiri on ühe sõnaline , siis apostroofe pole vaja ehk töötab ka näide
SELECT count (*) as Maakondasid
3. Mitu inimest on tabelis?
Avame inimeste tabeli ja vaatame samuti COUNT() funktsiooniga mitu inimest on sisestatud.
4. Mis on kõige suurem sünniaasta?
SELECT MAX (synniaasta)
5. Kes on kõige lühem inimene?
SELECT MIN (pikkus)
6. Leia inimeste keskmine pikkus, päringu nimi olgu keskmine pikkus.
SELECT AVG (pikkus)
7. Leia inimeste pikkuste summa kui inimeste kogupikkus.
SELECT SUM (pikkus)
8. Mitu naist on tabelis?
Kasutame funktsiooni COUNT() ja tingimust WHERE sugu='n'
9. Mitu meest on tabelis?
10. Mis on inimeste tabelis pikkuse standardhälbeks?
Ehk mitu cm erineb iga väärtus pikkuste aritmeetilisest keskmisest keskmiselt?
SELECT STDEV (pikkus)
Vaata eelnevate päringute loomist ka videona.
10.01.2010
Eve Keerus-Jusupov
Esimene päring
Olemasolevate andmete pärimiseks on võtmesõna SELECT.
Vaatame kõigepealt päringu abil kas maakonnad on lisatud.
Tee tabeli peal parem hiireklikk ja vali Script Table as... --> SELECT To --> New Query Editor Window
Avaneb päringu aken.
Käivita päring klikkides funktsiooniklahvi F5 (sama mis nupp Excecute).
Näidatakse maakondasid sisestamsie järjekorras.
Väljasta maakonnad tähestikulises järjekorras. Kasutatakse võtmesõnu asc ja desc. asc näitab andmeid kasvavas järjekorras ja desc kahanevas järjekorras.
order by maakond asc
Maakond on eelmises näites maakondade tabelis välja nimetuseks.
1. Tee uuesti päring kõigi maakondade kohta.
2. Tee päring kõigi inimeste kohta.
3. Tee päring kõigi inimeste andmete kohta, kusjuures andmed on järjestatud eesnime järgi kasvavalt.
Kuna vaikimisi järjestataksegi andmed kasvavalt, siis võtmesõna asc ei pea päringusse tegelikult lisama --> seega lisame päringu lõppu lihtsalt
order by eesnimi
4. Tee päring kõigi inimeste andemete kohta, kus esimesena järjestatakse andmed sünniaasta järgi ja seejärel eesnimede järgi.
Seega lisame päringu lõppu tingimuse
order by synniaasta, eesnimi
Kui sul pole samal sünniaastal kahte nime, siis lisa üks inimene juurde, et näha, kas pannakse teisena eesnime järgi tähestikulisse järjekorda.
5. Tee päring, kus näitad ainult eesnime ja pikkusi, kusjuures järjestad pikkuste järgi.
Järelikult näitame päringu alguses, milliseid andeid tahame näha.
SELECT [eesnimi] , [pikkus]
6. Tee päring, kus näitad ainult eesnime, kelle sünniaasta on 1976.
Alustame päringutega, kus lisandub tingimus where
Idee on lihtne, näitame ainult neid eesnimesid, kus sünniaastale on lisatud väärtuseks 1976 ehk siis sünniaasta on võrdne arvuga 1976
WHERE synniaasta=1975
7. Tee päring, kus näitad ainult neid eesnimesid, kus sünniaasta on väiksem kui 1996.
Sama ainult kasutame märki väiksem kui <
8. Tee päring, kus näitad ainult neid eesnimesid ja sünniaastaid, kus sünniaasta on väiksem kui 1996.
Lisame eelmisele päringule juurde välja sünniaastad. Näitame seega varem kui 1996 aastal sündinutel nii eesnimesid kui ka sünniaastaid.
9. Tee päring, kus näitad ainult erinevaid sünniaastaid.
Võtmesõna erinevate andmete näitamiseks on distinct. Hea kasutada näiteks kui tahame teada mitu erinevat kaubaartiklit on laos või mitu erinevat raamatut on raamatukogus.
SELECT distinct synniaasta
10. Tee päring, kus näitad neid eesnimesid ja sünniaastaid, mis jäävad vahemikku 1995 ja 2000.
Vahemikke saab näidata võtmesõnadega between ja and
where synniaasta between 1995 and 2000
11. Esita eelmine päring ka avaldisena, kusjuures 1995 ja 2000 on kaasa arvatud.
Avaldise koostamiseks kasutame eelnevates näidetes õpitut.
where synniaasta >=1995 and synniaasta <=2000
12. Näita ainult neid eesnimesid kahanevas järjekorras, kelle pikkused on väiksemad kui 100 cm.
Ongi esimesed päringud tehtud.
Vaata eelnevate päringute loomist ka videona.
Eve Keerus-Jusupov
14. november 2009
Andmebaasi ja tabeli loomine. Andmete lisamine.
Loo uus omanimeline andmebaas.
- Tee Parem hiireklikk Databases --> New Database
- Sisesta andmebaasi nimi (database name) ja kliki nuppu OK
- Ava oma loodud andmebaas. Tee selleks andmebaasi kujundi ees oleval plussmärgil klikk.
Loo maakondade tabel.
- Tee Parem hiireklikk Tables --> New Table.
- Olgu meil esimeses tabelis 2 välja - järjekorra number ja maakonna nimetus.
Välja, mille järgi tabeli ridadele viidatakse ning kus kõik väärtused on erinevad, nimetatakse primaarvõtmeks. Primaarvõtme väärtus peab iga kirjega muutuma, selleks peab arvuti ridu automaatselt loendama. Vaikimisi hakatakse loendama numbrist 1 ning järgmine arv tuleb igal korral ühe võrra suurem. - Sisesta Column Name lahtrisse ID, tee klikk Data Type lahtril ja vali andme tüübiks int. Column Properties alt vali Identity Specification (tee klikk plussmärgil) Is Identity --> Vali Yes. Nüüd suurendatakse kirjet kogu aeg ühe võrra. Lisaks tee ID lahtril parem hiireklikk ja vali Set Primary key. ID on nüüd primaarvõti, mis üheselt idendifitseerib iga kirje.
- Sisesta järgmisesse Column Name lahtrisse Maakond, mille Data Type on varchar(50).
- Allow nulls väljadelt eemalda linnukesed. Igasse lahtirsse peab andmeid sisestama.
- Kliki nuppu Save
Lisa maakonnad.
- Vasakul pool tee tabeli nimel parem hiireklikk ja vali Script Table as --> Insert To --> New Query Editor Window
- Näiteks VALUES ('Harjumaa').
- Käskluse täidab klikk nupul Execute või funktsiooniklahv F5.
- Kui soovid mitu maakonda korraga sisestada, siis eralda väärtused sulgudega ja komaga. Ära ei tohi ka unustada, et teksti sisestamisel kasutame apostroofe.
Näiteks VALUES ('Tartumaa'),('Jõgevamaa') - Sisesta tabelisse kõik maakonnad.
Eelnevat õpetust saad vaadata ka videona.
Andmebaasid – tabelid, võtmed
Andmebaase kasutatakse väga laialdaselt, see võib olla nii telefoniraamat kui ka hinnakiri, laoseis jpm. Andmebaas on võimalus informatsiooni organiseerimiseks. Andmebaasi kasutamine tähendab otsinguid ehk päringuid.
Andmeid hoitakse seotult tabelites. Kui kõnelda andmebaaside terminoloogiast, siis iga tabelirida on üks kirje. Tabel koosneb ridadest ja veergudest. Read on kirjed ja veerud on väljad. Väli on üksik infokogus, mida kogutakse kirje jaoks. Tabeli defineerimiseks tuleb määrata milliseid välju see sisaldab, mis tüüpi see väli on ja kui pikk on iga väli.
Iga tabel sisaldab andmeid ühe objektitüübi kohta. Näiteks, ettevõte, inimene, arve, kaup. Erinevate objektitüüpide andmed kuuluvad eri tabelitesse.
Näiteks ostjate andmed paiknevad ostjate tabelis, seal võiks olla kood, nimi, aadress, telefon. Ostjatele väljastatud arved on arvete tabelis – ostja kood, arve nr, kuupäev, summa.
Tabelite sidumiseks peavad mõlemad tabelid sisaldama ühist välja – näiteks ostja kood on nii ostjate tabelis kui ka arvete tabelis. Enamasti on seos üks mitmele - nt ühele ostjale kuulub mitu arvet. Sellist seotud tabelite süsteemi nimetatakse relatsiooniandmebaasiks. Ühele ostjale võib olla väljastatud mitu arvet.
Seega relatsiooniline andmebaas koosneb paljudest erinevatest andmetabelitest. Iga tabel omab kindlaksmääratud veerge, igal veerul on nimi ja piirangud, milliseid andmeid sinna veergu võib kirjutada.
Tabelitevahelised seosed tekitataksegi relatsioonide abil – erinevates tabelites seostatakse read, mille kindlaksmääratud veergude väärtused langevad kokku.
Igal tabelil on võti – see on veerg, milles sisalduv väärtus on igas antud tabeli reas unikaalne. St. selle võtme abil on võimalik üheselt identifitseerida suvalist rida andmebaasi tabelis.
Võõrvõti on tabeli veerg, mis sisaldab mõne teise tabeli võtmeväärtusi. Relatsioone teostatakse põhiliselt ühe tabeli võtme ja teise tabeli välise võtme (võõrvõtme) veeru kokkuviimisega.
Välja tüüp näitab mis liiki informatsiooni sellesse välja kogutakse, näiteks tekstilist, arvulist või sisestatakse sinna kuupäevad, kellaajad.
Väljatüüpe arvestatakse ka näiteks päringutes, teksti otsinguks peab olema välja tüübiks tekst (char, varchar), agregaatfunktsioone (max(), min(), sum() jt) saab kasutada arvuliste väärtuste korral. Päringukeelt SQL vaatame kohe lähemalt.
SQL
SQL on enamlevinud päringukeel andmebaasidega suhtlemiseks, mis arendati IBM poolt varastel 70-del. Andmetega töötamiseks kasutati selles keelt SEQUEL (Structured English Query Language), mis on SQL keele esimeseks versiooniks. Keelt kirjeldati 1976 a. novembris ajakirjas IBM Journal of R&D. Keelele anti hiljem uus nimi - SQL (Structured Query Language). 70-ndate lõpus nägi Relational Software Inc (praegune Oracle Corporation) loodus potentsiaali ning arendas oma versiooni SQL’st.
1987 - ISO (International Organisation for Standardization) kinnitas SQL standardi esimese versiooni.
1989 – standardiseeris SQL’i Ameerika Rahvuslik Standardiseerimisinstituut (ANSI). SQL muutus vabaks, mitte ühelegi äriühingule kuuluvaks keelestandardiks.
SQL arenes äärmiselt kiiresti just tänu veebibuumile. Kui esimesed veebilehed olid justkui raamatud, siis näiteks e-poodide tulekuga pidi rakendus hakkama kasutajaga suhtlema, hoides kasutajate andmeid ja pakkudes vajaminevat kaupa või kogudes tagasisidet. Kogu selle info kogumise, hoidmise, muutmise ja pärimise jaoks oli vaja andmebaase. Praktiliselt kõik kaasaegsed veebirakendused kasutavad andmebaase. Olgu selleks siis Joomla, Drupal, Wordpress, Type või mõni muu tarkvara.
Nagu öeldud on SQL andmebaasistandard. Kuid iga SQL arendaja lisab juurde täiendavaid omadusi ja seega on tekkinud mitmeid versioone. Enam levinumad andmebaasimootorid on MySQL, PostreSQL, aga ka Microsoft SQL server.
Andmebaasi arenduse etapid
Enne andmebaasi loomist peab väga põhjalikult mõtlema, millist informatsiooni peaksid need tabelid sisaldama. Eelnevalt tuleb läbi mõelda millist infot ja milleks on vaja koguda. Andmebaasist pole kasu, kui sealt vajalikku infot kätte ei saa. Andmebaas peab rahuldama nii hetke- kui ka tulevikuvajadusi, tuleb ette näha ka seda missuguseid välju on edaspidi vaja. Analüüsi tulemusel koostatakse paberil vajalike tabelite struktuur ja määratakse tabelitevahelised seosed.
Kui te alustate oma ettevõttes või kellelegi teisele andmebaasi loomist, siis tavaliselt tasuks lähtuda tarkvara arenduse klassikalisest elutsüklist, mille esimeseks etapiks on eeluuring. Te peate teadma ja aru saama kliendi vajadustest, nõuetest ning oskama neid nõudeid arusaadavalt kaardistada. Hea oleks teada ka turul olevaid analoogseid rakendusi, et oleks võrdlusmoment ning teaks nö konkurentide häid ja halbu külgi.
Analüüsi etapis peab olema kindlaks tehtud nii kasutaja vajadused kui ka tarkvara valik. Te peate olema kindlad, et räägite nii tellijaga või kasutajatega ühist keelt. Väga tihti ei tea ka kasutajad ise mida nad täpselt tahavad ja samuti ei oska arusaadavalt ennast väljendada, sest rakenduse looja peab täpselt aru saama nii tabeli struktuuridest, omavahelistest seostest kui ka vajaminevatest päringutest.
Näiteks raamatupidamisele võib selge olla, et akadeemilisel puhkusel olevate üliõpilaste käest õppemaksu ei küsita ja üliõpilased, kes arvatakse poole õppeaasta pealt tasuta õppesse, peavad aastamaksust pool tagasi saama – kõike seda peab raamatupidamisele tehtav rakendus arvestama, aga kõik programmid nii head kui halvad teevad siiski vaid seda, mida eelnevalt programmeerija on käskinud teha. Seega väikestes rakendustes tuleb rääkida sageli nii tellijaga kui ka kasutajatega.
Realisatsiooni ja disaini etapp on kõige töömahukam osa. Siin selgub vana tõde, et mida rohkem on eelnevas analüüsi osas mööda pandud, seda raskem on kliendi nõuetele vastavat lahendust luua ehk siis väike viga analüüsi osas, on väga kulukas realisatsiooni osas.
Järgmiseks etapiks on testimine, mille käigus peaks nägema nii rakenduse töökindlust kui ka kasutamismugavust. Kas lingid töötavad, kas kujundus meeldib, kas kasutajaliides on arusaadav.
Juurutamine on süsteemi töösse rakendamine ja siin ilmneb veel hulk kasutajapoolseid ettepanekuid – neid saab siis mõistlikkuse piires arvestada.
Uuendamine on etapp, mille käigus lisatakse vajaminevaid mooduleid või alamlehti. Kasutajate vajadused ning nõuded muutuvad ning tarkvaraline rakendus peab ajaga kaasas käima.
Näitena andmebaasi tellija ja teostaja vahelisest suhtlusest on järgmine slaidiseeria ühest lasteraamatust (autor teadmata).
|
Ettevõttes mõeldi, et oleks vaja pildil olevat rakendust. |
Vajaminev rakenduse nõuded kirjutati kokku ning esitati tellimus. |
Konkursi võitnud firma projekteeris lahenduse. |
|
|
 |
|
|
Tellimuse põhjal loodud projekt |
Testijad testisid – ei töötanud, |
Tegelikult soovis ettevõtte sellist rakendust. |
Seega suhtlemine teostaja ja tellija vahel on ülioluline, eelkõige on tähtis rääkida sama keelt. Andmebaasi kasutamine tähendab tavaliselt päringute tegemist. Päringukeelena on laialt levinud SQL (Structured Query Language), mis on kujunenud standardiks.
Eve Keerus-Jusupov
02. oktoober 2009