Vorgurakendused 1 SQL naited loengust

Detailsema seletuse andmebaasindusse leiad w3schoolsi tutorialist, aga siin lehel on konkreetsed käsura parameetrid dijkstras ja näited, mis peaks olema neljanda praksi jaoks piisavad.

Andmebaasiga tegutsemise katsetamiseks ja tabelite tegemise proovimiseks on mõistlik kasutada mysql käsurida. Pärast saad katsetatud ja töötavad päringud panna php koodi.

Käsurealt andmebaasi sisselogimiseks ja katsetamiseks tee nii (st2014 on andmebaasi nimi, st2014 samuti kasutajanimi ja parool on progress: need on kõigile ühised). Selle peale avaneb sulle mysql käsurida, kus saab otse päringuid sisse tippida.

mysql --user=st2014 --password=progress st2014
või pigem kasuta kiiremaks stardiks dijkstras
mysql --user=st2014 --password=progress -A st2014

Andmebaas on enne kasutamist vaja teha, selle praksi jaoks on see aga juba ette tehtud, sina seda st2014 kasutajana teha ei saa, välja näeks andmebaasi tegemine aga selliselt (nüüd ja edaspidi on kõik näited mysql käsurealt):

create database st2014;

Niimoodi saab vaadata, mis andmebaasid kasutatavad on ja valida endale kasutamiseks õige baas, aga jällegi: seda pole sul vaja teha, sest oled juba st2014 andmebaasi mysqli käsurealt sisestanud:

show databases;
use st2014;

Tabelite tegemisel on abiks korra vaadata, mis tabelid on olemas, samuti võid uudishimust vaadata, mis on mõne konkreetse tabeli väljad:

show tables; -- neid on väga palju, enamus varem tudengite poolt teistes praksides tehtud
describe <table>; -- siin <table> asemel peaks olema konkreetse tabeli nimi.

Sul on vaja teha oma tabelid. Peaks piisama tabelitest users, news, votes, comments. Kuna selles praksis on hulk tudengeid sama andmebaasi kallal ja kõigil on sama kasutajanimi, siis on vaja tabeli nimele panna ette prefiksina oma matriklinumber, et teistega segi ei läheks. Kui andmebaasi kasutajaid oleks vähe või kõik teeks sama projekti, siis seda prefiksit muidugi vaja ei läheks.

Teeme näiteks tabeli t1234_users:

create table t1234_users(
username VARCHAR(30),
fullname VARCHAR(40),
password VARCHAR(20), -- password on meil lahtiselt, mis "päris" rakenduses ei sobiks
PRIMARY KEY (username));  -- username on nüüd alati unikaalne, kahte rida sama usernamega baas ei luba

Ja proovime kohe lisada natuke ridu:

INSERT INTO t1234_users 
(username,fullname,password) VALUES('tanel', 'Tanel Tammet', 'abc');
INSERT INTO t1234_users 
(username,fullname,password) VALUES('kaire', 'Kaire Kass', 'koer');

ja proovime seejärel teha querysid:

select * from t1234_users; -- loetleb terve tabeli sisu
select * from t1234_users order by fullname desc; -- sorteerib lisaks fullname järgi (variandid on desc või asc)
select * from t1234_users where username='tanel'; -- leiab read (siin ainult üks), mis vastavad antud tingimustele
select * from t1234_users where username='tanel' and password='abc'; sama, aga mitme tingimusega

Teeme prooviks veel teise tabeli news:

create table t1234_news(
id integer AUTO_INCREMENT, -- see number hakkab ise automaatselt kasvama, sisestada pole vaja!
txt VARCHAR(500) not null, -- not null peale ei luba andmebaas välja tühjaks jätta
added timestamp default now(), -- siia pannakse automaatselt hetkeaeg, jälle pole sisestada vaja!
adder varchar(30), -- siia pane edaspidi lisaja username, mis vastab users tabeli username väljale
PRIMARY KEY (id)); -- see ütleb, et id-d ei tohi korduda

news tabelisse saad siis ridu lisada näiteks nii:

INSERT INTO t1234_news (txt) VALUES ('incredible news');
INSERT INTO t1234_news (txt,adder) VALUES ('check this http://www.reddit.com','kaire');

NB! Esialgselt sisestatud tabeleid on edaspidi vaja muuta, täiendada jne. Käsitsi sisestamine oleks selle juures talumatult kohmakas. Selle asemel tee hoopis tekstifail (näiteks nimega tables.sql), kuhu paned sisse tabeli tegemise ja näitedata jne ja siis saad edaspidi kõik need käsud lihtsalt failist peale lasta selliselt (see näide muidugi linuxi, mitte mysql käsurealt):

mysql --user=st2014 --password=progress st2014 -A < tables.sql 

Seejuures on hea mõte panna faili algusse olemasolevate tabelite kustutamine, muidu lihtsalt uusi tabeleid ei tehta:

drop table t1234_users;
drop table t1234_news;
...
create table ...

Kuidas teha päringut, mis annaks vastused korraga mitmest tabelist? Kõige lihtsam on teha nn inner joini, mis ühe tabeli igale reale lisab datat teistest tabelitest, vastavalt esimese tabeli nn primary key väljale. Teistes tabelites peab samuti olema antud siis esimese tabeli primary key. Näiteks, kui tahad kõiki lisatud uudiseid koos kasutaja täisnimega,teed nii:

select t1234_users.fullname, t1234_news.txt from t1234_users, t1234_news where t1234_users.username=t1234_news.adder;

Kuidas lugeda kokku uudise score? Siin on kaks peamist võimalust:

• Võid summeerida kokku votes tabeli read, mis vastavad antud uudisele. Selleks tuleb teha mitte päris triviaalne päring, mis teeb mh sum(t123_votes.num) (kus num on 1 või -1) ja samas päringus group_by t123_news.txt plus inner join t123_news.id ja t123_votes.newsid järgi. See vajab katsetamist ja veidi manuaali lugemist ja sum ning group by näidete otsimist.
• Võid ka panna news tabelile kohe score välja ja uuendada seda (a la score=1+score) iga kord, kui keegi hääletab. Sel juhul jääb votes tabel kasutusele ainult selleks, et takistada samal inimesel mitu korda hääletamist.