Vorgurakendused 1 SQL naited loengust

Allikas: Lambda

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 ... kohal on parool: need on kõigile ühised). Selle peale avaneb sulle mysql käsurida, kus saab otse päringuid sisse tippida.

mysql --user=st2014 --password=... st2014
või kiiremaks stardiks dijkstras
mysql --user=st2014 --password=... -A st2014

kus password=... õige sisu küsi loengus või praksis


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 reddit 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;
describe <table>; -- siin <table> asemel peaks olema konkreetse tabeli nimi.

Sul on vaja teha oma tabelid. Peaks piisama tabelitest users, links, 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 andmbeaasi 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 links:

create table t1234_links(
id integer AUTO_INCREMENT, -- see number hakkab ise automaatselt kasvama, sisestada pole vaja!
url VARCHAR(200) 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(20), -- siia pane edaspidi lisaja username, mis vastab users tabeli username väljale
PRIMARY KEY (id));

Links tabelisse saad siis ridu lisada näiteks nii:

INSERT INTO t1234_links (url) VALUES ('http://www.ttu.ee');
INSERT INTO t1234_links (url,adder) VALUES ('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=... st2014 < 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_links;
...
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 urle koos kasutaja täisnimega,teed nii:

select t1234_users.fullname, t1234_links.url where t1234_users.username=t1234_links.adder;

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

  • Võid summeerida kokku votes tabeli read, mis vastavad antud lingile. 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_links.url plus inner join t123_links.id ja t123_votes.linkid järgi. See vajab katsetamist ja veidi manuaali lugemist ja sum ning group by näidete otsimist.
  • Võid ka panna links 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.
Personaalsed tööriistad
Nimeruumid
Variandid
Toimingud
Navigeerimine
Kasulikku
Tööriistad