5. loeng - Veebitehnoloogiad III: SQL

Üldist

SQL (Structured Query Language) on tänaseks juba teenekas andmebaasikeel, mille juured ulatuvad esimeste relatsiooniliste andmebaaside tekkeni seitsmekümnendate keskel. SQL kuulub samasse põlvkonda Oracle'i, Ingres' ja DB2 andmebaasidega ning standardiseeriti esmakordselt 1989. aastal Ameerika Rahvusliku Standardiseerimisinstituudi (ANSI) poolt, muutudes vabaks, mitte ühelegi äriühingule kuuluvaks keelestandardiks - see ilmselt on taganud ka SQLi pika eluea. SQLil on viis peamist arenguetappi - SQL-86 (esmane standard), SQL:89 (vähesed muudatused; ANSI standard), SQL92 (SQL2), SQL:99 (SQL3) ja SQL:2003 (hilisemates versioonides on väiksemaid muudatusi) - kuid põhiosa (ANSI SQL) keelest on jäänud siiski enam-vähem samaks.

Sarnaselt Linuxiga on ka SQLi juures tekkinud olukord, kus erinevad distribuutorid lisavad ühisele tuumale täiendavaid omadusi ning tekib mitmeid eri versioone. Erinevalt Linuxil kasutatavast GPL litsentsist ei keela SQL (oma Berkeley juurte tõttu; samast ülikoolist pärit BSD tarkvaralitsents on samavõrd liberaalne) tuletiste muutmist omandvaraks ning suur osa tuletatud versioone ongi omavahel ühildumatu omandvara. Eriti kahetsusväärne on aga praktiliselt kogu SQL standardi enese uuemate (200x) versioonide saadavus üksnes kinnise, tasulise dokumentatsioonina.

Uue tõuke SQLi arengule andis veebibuum, õigemini selle teine järk dünaamilise veebi leviku näol. Kui algaegade veebilehed olid üsna sarnased raamatutega, siis ärirakenduste kiire areng tõi peagi välja veebipoesüsteemid ja muud sarnased rakendused, kus veebileht ei esitanud vaid infot, vaid suhtles aktiivselt kasutajaga. Et HTML-standard oma puhtal kujul ei säilita olekuid, oli üheks täienduseks ka veebilehtede sidumine andmebaasidega (töötluse ja infovahetuse eest hoolitsesid alguses enamasti C-s ja Perlis kirjutatud CGI programmid, hiljem võtsid suure osa sellest tööst üle PHP, Java, Javascript, VBscript jt). Veebiandmebaasidest saavutasid suurima leviku uued vabatarkvaralised SQL teisendid, millest levinumad on MySQL ja PostgreSQL. Lisaks neile on raskema kategooria kommertsrakendustes suur tähtsus Oracle'il ja DB2-l, ka Microsofti MS SQL on küllaltki levinud (ehkki andmebaaside vallas ei ole Microsoft nii märgatavat edu saavutanud kui mõnel muul alal). Hea ülevaate erinevate dialektide iseärasustest annab [L] OpenSourcery.

Põhimõisted

Relatsiooniline andmebaas koosneb tabelitest. Iga tabel kujutab endast kindla struktuuriga infokogumit, mille elementideks on kirjed ehk read. Iga rida koosneb omakorda andmeväljadest, eri kirjete samas positsioonis olevad andmeväljad moodustavad veeru.

Näiteks võiks olla andmebaas INIMENE, kus tabeliteks on inimese eri aspekte käsitlevad infokogumid - anatoomilised mõõdud, lähisugulased, meditsiiniline kaart, kutse- ja haridusandmed jne. Olgu tabel Mõõdud näiteks järgmine:

Tabel Koolid on aga selline:

Lihtsamad päringud

Select

Üldine valiku- ja kuvamislause. Üldkuju:

SELECT veerg1 [, veerg2, ... ] FROM tabel  [WHERE tingimus] ;

NB! Enamik SQLi dialektidest ei ole tõstutundlikud, s.t. käske võib anda nii suur- kui väiketähtedes. Toodud käsu kantsulgudes olev osa ei ole kohustuslik. Lause lõpeb semikooloniga nagu mitmetes programmeerimiskeeltes (C, PHP).

Siin veerg1 (ja soovi korral ka teised) on kuvatav veerg, tabel on otsingukohaks olev tabel. Kui soovime kuvada kogu kirjet, asendame veeru tärniga (*). Näiteks:

SELECT * FROM Mõõdud;	                 - kuvatakse kogu tabel Mõõdud
SELECT nimi, keskkool FROM Koolid;               - kuvatakse tabeli Koolid väljad nimi ja keskkool

Päringu täpsustamiseks on WHERE-osa:

SELECT * FROM Koolid WHERE keskkool="Tallinna 54. KK";  -kuvab Mari ja Helle kirjed
SELECT nimi FROM Mõõdud WHERE pikkus > 180; 	- kuvab Eriku ja Arturi nimed

Enamik võrdlustehteid on SQLis üsna arusaadavad - =, >, <, <> , >= ,<= Tähelepanelik tuleks aga olla sageli pruugitava kombinatsiooni PHP + SQL kasutamisel, kuna võrdlustehete süntaks on neis erinev. Lisaks neile on aga olemas ka LIKE-operaator, mis võimaldab otsida väärtuse osa järgi - kasutatakse protsendimärki % suvalise märgijada tähenduses (nagu paljudes muudes kohtades *). Näiteks:

SELECT * FROM Koolid WHERE keskkool LIKE 'Ta%';   - kuvab  kõik kirjed, kus keskkooli 
				nimi algab 'Ta'-ga (Mari, Artur, Helle)
SELECT * FROM Mõõdud WHERE nimi LIKE '%Eriste'; - kuvab kirjed, kus nimi lõpeb 'Eriste'-ga (Erik)
SELECT * FROM Koolid WHERE keskkool LIKE '%salu%'; - kuvab kirjed,  kus keskkooli nimes 
                                sisaldub tekst 'salu' (Artur)

Tähelepanuks: LIKE'i argumendiks olev tekst peab olema ülakomade (mitte jutumärkide) vahel.

Veel ühe töövahendina võib mainida BETWEEN-operaatorit:

SELECT * FROM Mõõdud WHERE pikkus BETWEEN 140 AND 180;

Viimaks on võimalik võrrelda ka hulkadesse kuuluvust:

SELECT * FROM Mõõdud WHERE jalanumber IN ("45", "37", "44");

Create table

Tabeliloomiskäsk. Üldkuju:

CREATE TABLE tabel (veerg1 tüüp1 [ piirang1 ] [, veerg2 tüüp2 [piirang2], ... ] );

Argumentidena tuleb loetleda soovitavate veergude nimed ja tüübid. Piirangud on mõeldud sisestatava info valideerimiseks (näiteks lubatakse vaid täisarve), nende kasutamine pole nõutav.

Näide:

CREATE TABLE Mõõdud (nimi varchar(30), synniaeg number(4), pikkus number(3), 
    kaal number(3), jalanumber number(2));

Peamised tüübid (NB! ANSI SQL; tuletistes esineb erinevusi):

• char(pikkus) - kindla pikkusega tekstiväärtus, pikkus näidatud sulgudes
• varchar(pikkus) - muutuvpikkusega tekstiväärtus, suurim lubatud pikkus näidatud sulgudes
• number(kohtade arv) - arv, suurim lubatud kohtade arv näidatud sulgudes
• number(kohti kokku, kohti peale koma) - arv, suurim lubatud kohtade arv ning kohtade arv peale koma näidatud sulgudes
• date - kuupäev

Insert into

Lisamiskäsk. Üldkuju:

INSERT INTO tabel (veerg1 [, veerg2, ...] VALUES (väärtus1 [, väärtus2, ... ] );

Siin tabel on tabeli nimi, kuhu lisatakse, seejärel loetletakse lisatavad veerud ning viimaks samas järjestuses nende väärtused. Näiteks:

INSERT INTO Mõõdud (nimi, synniaeg, pikkus, kaal, jalanumber 
     VALUES 'Jüri Juurikas',  1954, 175, 90, 43);

NB! Klassikaline SQL nõuab ülakomasid vaid tekstiväärtuste ümber, samas näiteks MySQL eeldab seda kõigi väärtuste kohta tüübist olenemata.

Update

Muutmiskäsk. Üldkuju:

UPDATE tabel SET veerg1 = uus_väärtus1 [, veerg2 = uus_väärtus2, ...] WHERE tingimus;

Tingimus koosneb veerunime(de)st, väärtus(t)est ja tehetest. Näide:

UPDATE Mõõdud SET pikkus = 167, kaal = 54 WHERE nimi = 'Mari Matvere';
UPDATE Koolid SET keskkool = 'Tabasalu Gümnaasium' 
      WHERE keskkool = 'Tabasalu KK' AND  nimi < > 'Mari Matvere';

Delete

Kustutuskäsk. Üldkuju:

DELETE FROM tabel [WHERE tingimus];

Tingimus koostatakse nagu eelmises näites. Näiteks:

DELETE FROM Mõõdud WHERE nimi = 'Jüri Juurikas';

NB! Tingimuse ärajätmine kustutab KÕIK kirjed tabelist (kuid mitte tabelit ennast)!

Drop table

Tabelikustutuskäsk. Üldkuju:

DROP TABLE tabel;

Näide: DROP TABLE Koolid;

Erinevalt DELETE'ist kustutab nii sisu kui tabeli enda.

MySQL

Kahe rootslase ja ühe soomlase poolt üheksakümnendate algul asutatud firmast sai kümnekonna aastaga ühe edukama võrguandmebaasisüsteemi haldaja ning üle 70 töötajaga korralik ettevõte. Firma omandas 2008. aastal Sun, mille omakorda ostis aasta hiljem ära Oracle - ent MySQL on omaette tütarfirma ja kaubamärgina tänini alles. Huvitav on MySQLi litsentseerimissüsteem - paralleelselt pakutakse nii GPLi kui ka kommertslitsentsi. MySQLi andmebaasi üheks suurimaks plussiks on kahtlemata kiirus, samas on ta vähem ANSI SQLiga ühilduv kui näiteks PostgreSQL (vt. [L] autorite selgitusi). MySQL sobib seega rohkem veebikeskkonda, kus andmearhitektuur pole üleliia keeruline ning vaja on suuremat töökiirust.

PostgreSQL

PostgreSQLi juured on ühes Berkeley ülikooli 1986. aasta teadusprojektis, nüüdseks areneb ta tõelise vaba tarkvara projekti viisil rahvusvahelise meeskonna koostöö viljana, mida toetab rida firmasid alates Kanadas loodud PostgreSQL Inc.-iga ja lõpetades Red Hatiga USA-s. BSD litsentsi all levivat PostgreSQLi peetakse vabatarkvaraliste andmebaaside lipulaevaks, mis järgib küllalt suures osas ANSI SQLi ning sisaldab paljusid professionaalseid võimalusi. Võrreldes MySQLiga on PostgreSQL mõnevõrra aeglasem ja võib-olla seetõttu veebikeskkonnas veidi vähem levinud.

MySQL: lihtsa andmebaasi loomine

Teeme näitena ühe lihtsa rakenduse - veebiaadresside andmebaasi. Alustame andmebaasi loomisest - selleks käivitame oma kasutajanime ja parooli abil MySQLi:

[kasutaja@server kasutaja]$ mysql -u kasutaja -p 

Sõltuvalt andmebaasi installatsiooniviisist võib kasutajale olla antud õigus luua andmebaase või ka mitte (viimane variant on avalikumates kohtades märksa tõenäolisem). Kui õigusi pole, on meil kasutada ainuüksi omanimeline andmebaas, kuhu saame luua andmetabeleid. Kui aga õigused on olemas (näiteks oma isiklikus arvutis), siis saame luua soovitava baasi ja anda kasutajale sinna ligipääsuõigused:

CREATE DATABASE lingid;
GRANT ALL ON lingid.* TO kasutaja@server IDENTIFIED BY "parool";

Loome andmebaasi tabeli, mille nimeks olgu samuti lingid:

CREATE TABLE lingid (
  nr int(11) NOT NULL auto_increment,
  aadress varchar(255) default NULL,
  kirjeldus varchar(255) default NULL,
  lisatud date default NULL,
  UNIQUE KEY nr (nr)
)

Mõned kommentaarid: esimene väli nr on identifikaator, mis lisatakse igale kirjele; see suureneb iga järgmise lisamisega automaatselt ühe võrra (auto_increment) ega tohi jääda tühjaks - selle järgi toimub ka indekseerimine (KEY).

Lisame ühe kirje (NB! Väli nr lisatakse auto_increment'iga automaatselt):

INSERT INTO lingid (aadress, kirjeldus, lisatud VALUES '', 'http:/www.delfi.ee',  'Delfi', '2007-09-10');

Et aga selline käsitsi baasis "nokkimine" muutub varsti ebamugavaks, võtame appi PHP.

PHP sisaldab muuhulgas ka käsustikku enamlevinud SQL-andmebaaside kasutamiseks. Järgnevalt on toodud põhilised tehnikad MySQL-andmebaasi ühendamiseks PHP programmiga. Täpsema info ja kogu käsustiku kirjelduse võib huviline leida [L] PHP manuaalist.

Kõigepealt peame looma andmebaasiserveriga ühenduse ja siis valima vajaliku baasi.

/* loome ühenduse andmebaasiga pannes kirja serverinime, kasutajanimi ja parool */
    $yhendus = mysql_connect("$serverinimi", "$MySQLi_kasutaja", "$parool");
 
/* valime andmebaasi - sama, mis MySQLi use-käsk */
     mysql_select_db("$baasi_nimi", $yhendus); 

Kui ühendus on olemas ja baas valitud, võime hakata tegema päringuid.

Näiterakendus

Loengus näidatud ja kommenteeritud minirakenduse koodi võib leida [L] siit

Tähelepanuks: andmebaasi sisu sisaldub failis lingid.dump. Oletades, et kasutajal on ligipääsuõigused baasile lingid, saab failis olevad andmed baasi lisada järgmiselt:

mysql -u kasutaja -p lingid < lingid.dump

Võrdluseks: vastupidine protsess (baasist faili) käib tavaliselt mysqldump-käsuga:

mysqldump -u kasutaja -p  lingid > lingid.dump

Lihtne lingikogu: PHP/MySQL näidisrakendus

... on allalaetav [L] siit. See tuleb lahti pakkida oma serveri veebikataloogi (loob eraldi alamkataloogi lingid).

Viiteid

1995-2012, Kaido Kikkas

Käesoleva dokumendi paljundamine, edasiandmine ja/või muutmine on sätestatud kas GNU Vaba Dokumentatsiooni Litsentsi versiooni 1.2 või uuemaga (Litsentsi ingliskeelne täistekst) või Creative Commonsi Autorile viitamine + Jagamine samadel tingimustel 3.0 Eesti litsentsiga

1995-2012, by Kaido Kikkas. This document is distributed under either GNU Free Documentation License (v1.2 or newer) or Creative Commons Attribution-ShareAlike 3.0 Estonia license.