Uvod u relacione baze podataka i SQL

Uvod

U teoriji, termin "baza podataka" označava bilo kakvu organizovanu kolekciju podataka koja se čuva u elektronskom obliku, * međutim, godinama unazad navedena odrednica se tipično poistovećuje sa relacionim bazama podataka, budući da su upravo relacioni sistemi za skladištenje i obradu podataka (SQL Server, Oracle, Db2, MySql, PostgreSQL - da navedemo samo neke), ** najrasprostranjeniji i najčešće korišćeni. ***

Za prosleđivanje upita, odnosno (u opštem smislu), za poslove administracije relacionih baza, koristi se SQL (Structured Query Language), deskriptivni/neproceduralni jezik specifično namenjen radu sa bazama podataka.

Ako se osvrnemo na to da se baze podataka koriste u većini desktop programa i na skoro svim sajtovima, jasno je da je u pitanju pojava od izuzetnog značaja u računarskoj industriji, i to je više nego dobar razlog da se (u nastavku) što bolje upoznamo sa relacionim bazama podataka i SQL-om ....

Osnove relacionog modela

U najpraktičnijem smislu, relaciona baza podataka se može shvatiti kao sistem koji čine: međusobno povezane tabele (formatirane na racionalan i ekonomičan način), i alati koji omogućavaju da se (kasnije) dođe do neophodnih podataka (što brže i što jednostavnije).

Osnovne ideje su prilično jednostavne, tehnikalije nisu 'previše komplikovane', ali, u praksi, upoznavanje sa bazama podataka ipak zahteva izvesno vreme i temeljit pristup.

Da bismo što bolje ilustrovali osnovnu ideju koja stoji iza relacionog modela (i da bismo se postepeno upoznali sa principima dizajna i upotrebe relacionih baza podataka), proći ćemo kroz sve korake u projektovanju jednostavne baze podataka koja koristi dve tabele, preko kojih se beleže informacije o prodavcima i prodaji artikala u 'fiktivnoj' prodavnici. *

Prva tabela sadrži podatke o prodavcima i gotovo se ne razlikuje od tabele kakvu bismo za slične potrebe napravili u Excel-u:

Za razliku od relativno jednostavnog/'očekivanog' formata prethodne tabele, dizajn tabele prodaja (u kojoj se u svakom slogu beleže podaci o prodaji jednog artikla), zahteva malo više promišljanja, i stoga ćemo pažljivije razmotriti kako treba organizovati ovakvu tabelu.

Prvo ćemo sagledati zašto ne treba koristiti određene formate zapisa (koji bi u ovom trenutku vrlo verovatno mogli da vam padnu na pamet).

Teoretski, 'mogli' bismo (pored ostalih podataka o prodaji), beležiti samo ime i prezime prodavca:

Drugi način (koji takođe nije pravo rešenje), podrazumeva da se u tabeli prodaja beleže svi podaci o prodavcu:

Prvi format zapisa ima dva nedostatka:

• pre svega, kombinacija imena i prezimena nije jedinstven podatak i ne upućuje obavezno na jednu osobu (npr. "Petar Jovanović" može biti ime i prezime bar dva različita prodavca) *
• ukoliko je potrebno dobiti više informacija o prodavcu:
• u tabeli prodaja nema dodatnih podataka
• ako se podaci o prodavcu 'ručno' pretražuju u tabeli prodavci, ne možemo biti sigurni da čitamo prave podatke (iz gore navedenih razloga)

Drugi format zapisa je potpun, u smislu da podaci o prodavcu (koji se inače beleže; preko tabele prodavci), jesu zapisani - i sada jeste moguće razlikovati prodavce, * ali ....

U pitanju je format koji nije ni malo elegantan, pregledan i praktičan (baš, baš - nimalo 🙂), a ako bismo "skroz preterali" tj. ako bismo podatke upisivali ručno, neelegantnosti i nepreglednosti bismo mogli dodati i povećanu mogućnost pojave grešaka.

Potrebno je naći rešenje koje omogućava da jedinstveni prodavac bude naveden u tabeli prodaja na jednostavan, ekonomičan i nedvosmisleno prepoznatljiv način - po mogućnosti preko samo jednog podatka (i pogotovo bez navođenja (svih) detalja putem "copy-paste metoda"), a potrebno je istovremeno da podaci o prodavcima budu zapisani ("negde i nekako"), i - po potrebi dostupni.

Pošto su podaci o prodavcima već zapisani (u zasebnoj tabeli), praktično rešenje je - pripisati svakom prodavcu jedinstven identifikacioni broj - i potom povezati tabele.

Primarni ključ

Jedinstveni podatak u okviru jednog sloga, po kome je moguće prepoznati ceo slog (i razlikovati ga od ostalih slogova, tj. ostalih 'redova'), * naziva se primarni ključ.

U praksi, za primarni ključ se gotovo uvek koriste celobrojne vrednosti (tj. 'redni brojevi slogova'), pri čemu su takvi 'redni brojevi' sadržani u jednoj koloni ** koja se tipično označava kao "id".

U tabeli prodavci, dodaćemo kolonu sa nazivom id, u kojoj će redom biti upisane celobrojne vrednosti od 1 do n, tako da svaki broj upućuje na jednog prodavca - uvek istog.

Uspostavljanje veze između tabela (sekundarni ključevi)

Nakon definisanja primarnog ključa u određenoj tabeli (npr. u tabeli prodavci), potrebno je uspostaviti vezu (tj. 'relaciju') između dve tabele.

U primeru kojim se bavimo, uspostavlja se relacija između tabela prodavci i prodaja.

Pošto je uspostavljena veza između tabela (videćemo kasnije i kako se to izvodi 'za prave', preko upita), u tabeli prodaja više se ne navodi ime i prezime prodavca (pogotovo ne svi podaci), već se samo navodi identifikacioni broj iz tabele prodavci - primarni ključ, što (naravno) znači da se polje prodavac_id * u tabeli prodaja nadalje popunjava podacima brojčanog tipa, a ne podacima tekstualnog tipa (popunjavanje se obavlja tako da u svakom slogu polje prodavac_id predstavlja primarni ključ prodavca koji je prodao određeni artikl).

Kada se primarni ključ iz jedne tabele upotrebi u drugoj tabeli (radi povezivanja sa podacima iz prve tabele), takav podatak prepoznaje se kao sekundarni ključ.

Prirodni i surogatni primarni ključevi

U prethodnim odeljcima opisali smo tipičan postupak koji podrazumeva da se za primarni ključ tabele biraju celobrojne vrednosti, međutim, iako određena celobrojna vrednost u polju id jeste vezana za samo jedan red (to jest, iako se vrednosti ne ponavljaju po slogovima), mora se primetiti (npr) da broj 1 - sam po sebi - nema nikakve suštinske i prirodne veze sa osobom "Petar Jovanović" (čiji su podaci zabeleženi u prvom redu), * pa možemo reći da takav podatak predstavlja surogatni (tj. veštački) primarni ključ, a usput se postavlja i pitanje da li je ceo pristup optimalan.

U bilo kojoj tabeli koju projektujemo, važno je prepoznati podatak, u okviru sloga, koji na prirodan način identifikuje slog (u primeru: podatak koji identifikuje osobu), to jest, treba prepoznati koje od već postojećih polja se javlja kao prirodni primarni ključ.

Kada prepoznamo polje koje može da se iskoristi kao 'prirodni primarni ključ', praktično se moramo zapitati i da li takvo polje (zapravo) treba da se pojavi kao primarni ključ?

Načelno je moguće izabrati da jedan od već postojećih podataka bude formalno prepoznat kao primarni ključ, ali, to (razume se) može biti samo podatak koji se u okviru određene kolone ne ponavlja (tj. podatak koji je u okviru određene kolone jedinstven).

U primeru koji razmatramo, ime nije jedinstven podatak (više prodavaca može imati ime Petar, ili bilo koje drugo ime), prezime takođe nije jedinstven podatak (a nije ni datum rođenja).

E-mail adrese zapravo jesu jedinstvene - pod uslovom da su verifikovane (kao što smo još ranije primetili), međutim, u pitanju je podatak tekstualnog tipa, a tekstualni podaci se pretražuju na manje efikasan način od brojčanih.

Shodno navedenom: u primeru kojim se bavimo, ipak je najpraktičnije da se za primarni ključ izabere jedinstven celobrojni podatak iz kolone "id" (a isti je slučaj i sa ogromnom većinom tabela sa kojima ćete se sretati), ali - potrebno je koristiti oba ključa u jednom slogu, to jest, nikako se nećemo "odreći" prirodnog primarnog ključa samo zato što smo uveli veštački primarni ključ.

Veštački i prirodni primarni ključ, obavljaju različite zadatke:

• veštački primarni ključ (tipično označen kao "id") - stvar je konvencije (većina tabela u bazama podataka ima primarni ključ celobrojnog tipa, sa nazivom "id")
• prirodni primarni ključ je tu da omogući raspoznavanje 'entiteta', * odnosno - da spreči dupliranje slogova u tabelama

Summa summarum: u većini dobro dizajniranih tabela, tipično se pojavljuje polje id koje se formalno prepoznaje i koristi kao primarni ključ, ali, u suštinskom smislu, 'identitet' pojedinačnog sloga nije određen prisustvom 'celobrojne vrednosti', već - prisustvom određenog podatka koji je jedinstven (ili prisustvom kolekcije podataka koji zajedno predstavljaju jedinstvenu celinu).

U najpraktičnijem smislu, navedeni podatak (ili kolekcija podataka), igra važnu ulogu pri unosu novih slogova, što ćemo dodatno objasniti na primeru tabele prodavci.

Pri dodavanju novog prodavca u tabelu, prodavcu će id (tipično) biti dodeljen automatski (i neće imati "prirodne i suštinske" veze sa prodavcem), što znači da se za proveru novog prodavca (u smislu toga da li prodavac sa navedenim podacima već postoji u tabeli), moraju koristiti ostali podaci.

Ako među podacima koji se koriste za upis novog prodavca postoji podatak koji predstavlja prirodni primarni ključ (JMBG, verifikovana e-mail adresa i sl), moguće je proveriti da li u tabeli već postoji slog u kome je dati podatak naveden (da li već postoji prodavac sa datim JMBG-om ili sa datom e-mail adresom i sl), dok - ukoliko među podacima nema prirodnog primarnog ključa - zapravo ne postoji način da se slogovi raspoznaju!

Entiteti i atributi

Pošto smo razumeli kako se podaci 'raspoređuju po tabelama' (u okviru relacionog modela), potrebno je da se upoznamo i sa nekoliko termina koji opisuju pojave sa kojima smo se prethodno susretali.

Entitet(i)

U opštem smislu, pojam entiteta u bazama podataka odnosi se na tip objekta * (najčešće složeni tip), koji u datom sistemu ima značaj i koji se može nedvosmisleno odrediti i zapisati, ali, termin 'entitet' može označavati i konkretan objekat (tj. slog), koji pripada određenoj 'kategoriji entiteta'.

Entiteti iste kategorije * zapisuju se u okviru jedne tabele, pri čemu je za svaki entitet vezan jedinstven primarni ključ.

Takođe (kao što smo već videli), entiteti iz jedne tabele mogu se navoditi u drugim tabelama preko sekundarnih (tj. spoljnih) ključeva.

U bazi koju projektujemo, jedan od entiteta je prodavac (sledi "prevod" gornja tri pasusa):

• 'prodavac' je u opštem smislu definisan kao skup pojedinačnih podataka (ime, prezime, datum rođenja i sl), i pri tom su podaci o određenom pojedinačnom prodavcu "nedvosmisleno određeni i zapisani (u jednom redu)"
• svaki prodavac u tabeli prodavci označen je jedinstvenim primarnim ključem
• prodavci (kao entiteti), pojavljuju se u tabeli prodaja ** - preko spoljnih ključeva (a mogu se naravno pojavljivati i u drugim tabelama)

Atribut(i)

Termin "atribut" može se (takođe) definisati na dva načina:

• sa jedne strane, atribut predstavlja jednu od zajedničkih osobina svih entiteta određene kategorije
• sa druge strane, atribut može biti i pojedinačni podatak koji se koristi pri definisanju određenog entiteta

Da pojasnimo dodatno ....

Jedan entitet ima više atributa (za prodavca, atributi su: ime, prezime, datum rođenja, datum stupanja u radni odnos i sl).

Bilo koji pojedinačni entitet iz određene grupe entiteta (praktično, iz iste tabele), ima svaki od atributa koji su pripisani svim entitetima date kategorije: svi prodavci imaju ime i prezime (pri čemu svakog prodavca odlikuje njegovo sopstveno ime i prezime), za sve prodavce se beleži datum stupanja u radni odnos (pri čemu se za svakog prodavca beleži datum stupanja u radni odnos koji se odnosi na datog prodavca), i sl.

Tipovi relacija između entiteta

U smislu toga koliko puta se spoljni ključ iz jedne tabele može pojaviti u drugoj tabeli, razlikuju se sledeći tipovi relacija:

• relacija 1:1 (jedan prema jedan) - spoljni ključ određenog entiteta iz jedne tabele, može se u drugoj tabeli pojaviti samo u jednom slogu
• relacija 1:n (jedan prema više) - spoljni ključ određenog entiteta iz jedne tabele, može se u drugoj tabeli pojaviti u više slogova
• relacija n:n (više prema više) - više entiteta iz jedne tabele povezuje se sa više entiteta iz druge tabele (preko posredničke tabele)

Relacija 1:n (jedan prema više)

Tipično se među entitetima uspostavlja relacija 1:n ("jedan prema više"), * koja, kao što je već navedeno, podrazumeva da se entitet iz tabele T1 može pojaviti u tabeli T2 proizvoljan broj puta (preko spoljnjeg ključa):

Kao konkretan primer može poslužiti tabela prodaja: jedan prodavac (entitet iz tabele prodavci), može se pojaviti u više slogova u tabeli prodaja.

Relacija 1:1 (jedan prema jedan)

Relacija 1:1 ("jedan prema jedan"), nije uobičajena u istoj meri kao relacija 1:n, ali, svakako se pojavljuje (dovoljno često) i, ovoga puta, svaki entitet iz tabele T1 može se u tabeli T2 pojaviti samo jednom:

U svojstvu primera, možemo zamisliti bazu podataka u kojoj se beleže podaci u okviru određene sportske lige: ako svaki klub (tj. entitet iz tabele klubovi), može imati samo jednog glavnog trenera, i ako svaki glavni trener (entitet iz tabele osoblje), ima pravo da trenira samo jedan klub, možemo zamisliti da u tabeli klubovi postoji polje glavni_trener - preko koga je naveden id tj. spoljni ključ trenera (čiji su podaci zapisani u tabeli osoblje).

Ali - ako ste raspoloženi za dodatno 'naprezanje vijuga' (pre nego što spontano dođemo do sličnih primera u kasnijem toku članka) - možemo zamisliti i nešto drugačiju organizaciju u bazi podataka "sportska_liga".

Recimo, preko pomoćne tabele treneri, id-ovi trenera mogli bi se spajati sa id-ovima klubova, čime se praktično beleži informacija o tome koji trener trenira koji klub.

U tehničkom smislu, tabela treneri bila bi sačinjena od polja: id, trener_id i klub_id, pri čemu je polje trener_id povezano sa tabelom osoblje preko relacije 1:1, a isto važi i za polje klub_id, koje je povezano sa tabelom klubovi takođe preko relacije 1:1.

Relacija n:n (više prema više)

Što se tiče relacije n:n ("više prema više"), za sada ćemo (samo) dodatno razmotriti opšte ideje.

Uzmimo za primer bazu podataka filmska_enciklopedija, u kojoj se kao entiteti pojavljuju glumci i filmovi (sa podacima koji su zapisani u tabelama glumci i filmovi), pri čemu je glavno pitanje: gde i kako (to jest, u kom formatu), treba zabeležiti podatke o tome u kojim filmovima se određeni glumac pojavljivao (ili - podatke o tome koji glumci su se pojavili u određenom filmu).

Intuitivno možete zaključiti (primera radi), da pokušaj da se u tabeli glumci, u jednom slogu navedu svi filmovi koji se vezuju za glumca, veoma podseća na "pokušaj" (koji smo videli ranije), da se u tabeli prodaja navedu svi podaci o prodavcu, i stoga - "razmišljamo dalje" ....

Za sada je najbitnije znati da se relacija n:n ne može implementirati na efikasan način preko jedne tabele, to jest - potrebno je koristiti posredničku tabelu, međutim, bez obzira na to što smo mnoge pojedinosti već nagovestili u prethodnim odeljcima, opširniju diskusiju o implementaciji relacije n:n (kao i ostalih relacija), ostavljamo za kasnije, jer smatramo da je potrebno da se prvo detaljnije upoznamo sa ostalim osnovnim osobinama baza podataka (takođe, i sa SQL sintaksom), da bismo što bolje razumeli kako su (i pre svega zašto), implementacije različitih relacija izvedene na specifičan način koji ćemo opisati (ali, bez brige, nije ni iz daleka komplikovano). :)

Struktura relacione baze i organizacija podataka

Da bi baza podataka bila u stanju da pruži korisne informacije na brz i efikasan način (a upravo je, to što smo naveli - prava svrha relacionih i drugih baza podataka), potrebno je da tabele budu pravilno koncipirane i pravilno strukturirane, i takođe je potrebno koristiti efikasne upite.

Za početak, pozabavićemo se strukturom tabela.

Tabela, polje, slog

Tabela je sistem međusobno povezanih ćelija u koje se upisuju podaci o određenoj kategoriji entiteta, a same ćelije su raspoređene u redove i kolone.

Pojedinačna ćelija (u tabeli) naziva se "polje":

Polje je odrednica koja se koristi i za celu kolonu (budući da cela kolona sadrži "polja istog tipa"):

Ceo red (u tabeli) naziva se "slog":

U svakoj tabeli, "slog" predstavlja jedan složeni podatak, tj. jedan "entitet", pa tako (npr) u tabeli prodavci, svaki red sadrži sve podatke koji se beleže o jednom prodavcu (što znači da cela tabela sadrži podatke o prodavcima); podaci o osobama koje učestvuju u prodaji se (nadalje) u drugim tabelama ne zapisuju direktno (što bi samo povećalo memorijsko zauzeće i mogućnost pojave grešaka), već se po potrebi koriste podaci iz tabele prodavci - preko ključa (baš kao što smo još na početku ustanovili).

Svaki od podataka u jednom slogu, pripada određenom (prigodno odabranom) tipu podataka: primarni ključ je (gotovo uvek) celobrojna vrednost, a ostali podaci se mogu zapisivati kao brojevi, tekst, datumi (u posebnom formatu), boolean vrednosti (true i false) i sl.

Shodno prethodnim smernicama, deluje da smo u tabelama prodavci i prodaja izabrali optimalan format za pojedinačne podatke, ali .... možda bi neko od čitalaca bio sklon da se zapita: da li smo (možda) mogli izabrati i neki sažetiji format (?!) .... i stoga ćemo prodiskutovati ukratko o optimalnom formatu slogova ....

Atomski podaci

Budući da je tabela u bazi podataka namenjena zapisivanju kolekcija složenih podataka (tako da se u svakom slogu pojavljuje jedan entitet), jedno od osnovnih pitanja je: kako (tačno) treba organizovati (odnosno zapisati) podatke u okviru jednog sloga.

Ako bismo, u tabeli prodavci, za zapis podataka u svakom slogu odredili svega dva polja: id (int) i osoba (text), prva kolona sadržala bi redni broj, dok bismo u drugu kolonu upisivali "sve ostale podatke", što (na primeru jednog sloga), može imati sledeći oblik:

		
id:    1
osoba: "Jovan Petrović, Ruzveltova 25, 41 godina, Mašinski inženjer, jocapet79@gmail.com"
		
	

Nije teško intuitivno zaključiti da u gornjem zapisu "nešto ne štima", iako deluje da je u tehničkom smislu sve u redu.

U smislu efikasne obrade, prethodni format zapisa ne pomaže ni najmanje: zarad dolaženja do nekog od "unutrašnjih podataka", potrebno je - umesto da se podatak pročita neposredno - proći kroz nisku znakova (polje osoba je samo obična niska), i potom je potrebno izdvajati pojedinačne delove (a ukoliko neki od izdvojenih podataka nije tekstualnog tipa, izvesna količina procesorskog vremena trošila bi se na konverziju podatka iz tekstualnog zapisa u brojčani).

Umesto zapisa preko (jedne) niske, složeni podaci se dele na "atome" tj. podatke koji se ne mogu 'dalje usitnjavati', i (kao što smo već nagovestili), za svaki podatak koristi se odgovarajući tip.

U tabeli koju projektujemo: podatak u polju id zapisuje se kao celobrojna vrednost, podaci u poljima ime, prezime i e-mail zapisuju se kao niske, a datum se zapisuje preko specijalizovanog formata (datetime), koji ne predstavlja običan tekst, već (praktično), podatak brojčanog tipa.

Uz pravilan odabir pojedinačnih polja, tabela prodavci je formatirana na znatno prikladniji (i pregledniji) način:

		
polje           | vrednost            | tip podatka
-----------------------------------------------------
id:             | 1                   | celobrojni
ime:            | Jovan               | niska znakova  
prezime:        | Petrović            | niska znakova
ulica:          | Ruzveltova          | niska znakova
broj:           | 25                  | celobrojni
datum_rodjenja: | 18.08.1979.         | datum
zanimanje:      | Mašinski inženjer   | niska znakova
email:          | jocapet79@gmail.com | niska znakova
		
	

Rad sa bazama podataka podrazumeva korišćenje tekstualnih komandi, bez obzira na to da li se bazama pristupa preko programa * koji dolaze uz RDBMS, ili preko programskih jezika, i stoga je neophodno dobro se upoznati sa SQL sintaksom.

U praksi, SQL sintaksa nije univerzalna, tj. svaki RDBMS ** ima svoju sintaksu, ali (srećom), međusobne razlike u većini situacija nisu 'velike i suštinske', već je svaka konkretna SQL implementacija svojevrsna "varijacija na temu", pri čemu ima mnogo više sličnosti nego razlika.

Početni plan za upoznavanje sa SQL-om je sledeći:

• definisaćemo pojam upita
• osvrnućemo se na jezik SQL u opštem smislu
• upoznaćemo se ukratko sa MySql-om *

.... nakon čega ćemo preći na detaljno upoznavanje sa uobičajenim SQL upitima. ***

Pojam upita u bazama podataka

Upit je niz tekstualnih komandi * preko kojih se od baze zahteva određeni vid obrade podataka: kreiranje nove baze ili nove tabele u postojećoj bazi, unos podataka, čitanje, izmena, brisanje pojedinačnih podataka, dodavanje ili uklanjanje kolona (a postoje i određene komande koje ćemo za sada preskočiti, jer prevazilaze okvire uvodnog članka).

Upiti se zadaju preko (unapred definisanih) komandi jezika SQL.

SQL (Structured Query Language) - osnovne postavke

SQL ("Structured Query Language") je deskriptivni programski jezik koji se u sistemima za upravljanje bazama podataka koristi za prosleđivanje upita, to jest, u najpraktičnijem smislu - za zadatke koji su vezani za obradu podataka i administraciju RDBMS sistema (kao što smo nagovestili još u uvodu).

Međutim, budući da je u pitanju pristup koji se razlikuje od proceduralnog programiranja (kako u opštem smislu, tako i u smislu sintakse samih naredbi), napravićemo kraću digresiju i objasnićemo razliku ....

Jezici u kojima programeri moraju detaljno definisati postupke za rešavanje različitih problema (primer takvog jezika je C), pripadaju kategoriji proceduralnih programskih jezika.

Nasuprot navedenom principu, deskriptivni jezici (deskripcija = opis), koriste se za opisivanje problema (tj. opisivanje 'prirode problema'), korišćenjem unapred definisane sintakse, pri čemu se ne opisuje procedura za rešavanje problema, već se različiti zahtevi rešavaju po unapred definisanim procedurama na koje programeri i korisnici nemaju direktnog uticaja.

Na primer, u SQL-u, pri pozivanju upita za čitanje tabele, korisnik navodi: tip upita (čitanje), naziv tabele i, po potrebi, ograničenja i uslove ("učitati i prikazati podatke iz tabele 'xyz', ali, samo podatke koji se odnose na jun 2020.") - nakon čega sistem pristupa pronalaženju podataka i prikazuje rezultat korisniku.

U nastavku, prikazaćemo upite preko kojih se može kreirati (i popuniti) baza koju smo u uvodnim poglavljima koristili kao primer, * s tim da ćemo se prvo upoznati (ukratko), sa konkretnim DBMS sistemom koji ćemo koristiti ....

Kratak uvod u MySql

MySql je kvalitetan, jednostavan, * popularan i rado korišćen ** DBMS koji omogućava veliku brzinu i efikasnost u obradi podataka - i pri tom nije težak za savladavanje.

Što se tiče 'tehnikalija', MySql se može instalirati i konfigurisati samostalno, a dolazi i uz paket XAMPP o kome smo već pisali u članku o pokretanju lokalnog web servera.

Za isprobavanje primera iz članka, pokrenite XAMPP (pokrenite servise Apache i MySql) i unesite sledeću adresu u adresnu liniju browsera:

		
localhost/phpmyadmin
		
	

.... čime se pokreće web aplikacija preko koje se može pristupati bazama podataka na sistemu.

Kreiranje baze preko upita (CREATE DATABASE)

Da bismo mogli da prosleđujemo upite, potrebno je prvo otvoriti prozor za unos SQL upita (uz MySql dolazi i "pravi" konzolni program, ali, uvažićemo to da je rad u grafičkom okruženju ipak udobniji za početno upoznavanje):

U opštem smislu, nova baza kreira se po sledećem obrascu:

		
CREATE DATABASE naziv_baze;
		
	

U konkretnom primeru kojim se bavimo, nova baza može se kreirati preko sledećeg (konkretnog) upita:

		
CREATE DATABASE prodaja;
		
	

Prethodni upit poslužiće sasvim dobro, ali, ako je kreiranje baze potrebno izvesti na "skroz zvaničan" način, upit je potrebno upotpuniti dodatnim uslovima i smernicama:

		
CREATE DATABASE IF NOT EXISTS prodaja
	CHARACTER SET utf8
	COLLATE utf8_general_ci;
		
	

Novi upit biće izvršen samo ukoliko baza sa navedenim imenom ne postoji, a usput smo definisali i metod za enkodiranje znakova (UTF-8), koji omogućava unos ćiriličnih i latiničnih znakova.

Kreiranje tabela preko upita (CREATE TABLE)

Nova tabela (unutar postojeće baze), kreira se po sledećem obrascu:

		
CREATE TABLE naziv_tabele
(
	polje_1 (tip polja, osobine ....)
	polje_2 (tip polja, osobine ....)
	....
	polje_n (tip polja, osobine ....)
);
		
	

Pošto smo prethodno kreirali novu bazu (i pošto smo još na početku osmislili strukturu tabela), možemo napisati upite za kreiranje tabela:

		
USE prodaja;

CREATE TABLE prodavci
(
	id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
	ime varchar(255) NOT NULL,
	prezime varchar(255) NOT NULL,
	datum_rodjenja datetime NOT NULL,
	adresa varchar(255) NOT NULL,
	broj int NOT NULL,
	email varchar(255) NOT NULL
);

CREATE TABLE prodaja
(
	id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
	datum datetime NOT NULL,
	artikl varchar(255) NOT NULL,
	cena double NOT NULL,
	kolicina double NOT NULL,
	prodavac_id int NOT NULL,
	FOREIGN KEY (prodavac_id) REFERENCES prodaja(id)
)
		
	

Kao što se da primetiti: za svako polje je izabran odgovarajući tip podatka, polje id smo proglasili za primarni ključ u obe tabele, a dodali smo i direktivu AUTO_INCREMENT, koja će omogućiti da id (u obe tabele) bude automatski uvećan za 1 pri unosu svakog novog sloga.

Budući da se u tabeli prodaja pozivamo na id-ove prodavaca iz tabele prodavci (FOREIGN KEY (prodavac_id) REFERENCES prodaja(id)) i budući da je unos podataka u tabele, sledeći korak koji preduzimamo, prvo ćemo razmotriti mehanizam koji proverava da li prodavci koji se navode u tabeli prodaja - zapravo postoje.

Referencijalni integritet

Pretpostavljamo da mnogi čitaoci unapred razumeju šta bi moglo da se desi ukoliko pokušamo da unesemo nepostojeći id prodavca u tabelu prodaja, ali, prodiskutovaćemo ipak malo više o celoj problematici (uz pretpostavku da, za početak, ne vodimo računa o "referencijalnom integritetu").

Ukoliko pokušamo da unesemo novi 'diskutabilan' slog u tabelu prodaja (na primer: prodaju artikla koju je obavio prodavac sa rednim brojem 17), podatak će biti uredno unet i tabela prodaja će sama po sebi biti korektna (još jednom - pretpostavka je da, za sada, RDBMS ne vodi računa o referencijalnom integritetu), međutim, ako nakon unosa prosledimo (drugi) upit, preko koga se zahtevaju podaci o "fantomskom" prodavcu #17 - čiji id ne postoji u tabeli prodavci - sistem će (opravdano) prijaviti grešku.

Da se ne bi dešavali "ispadi" (kakve smo opisali), potrebno je da baza podataka, pri svakom upisu u određenu tabelu, vodi računa o tome da li spoljnji ključ koji se upisuje - zapravo postoji u tabeli za koju je vezan, a pojam 'referencijalnog integriteta' odnosi se upravo na mere koje DBMS preduzima da onemogući korisnike da se pozivaju na podatke (tj. entitete) koji ne postoje u bazi.

U tabeli koju smo kreirali, budući da jeste podešeno da baza podataka vodi računa o referencijalnom integritetu, pri pokušaju unosa prodaje koju je obavio 'prodavac sa id-om 17', bila bi prijavljena greška, i slog ne bi bio unet.

Upis podataka u tabele (INSERT)

Sada kada znamo da će nas MySql sprečiti da se "upucamo u nogu" po pitanju referenciranja nepostojećih prodavaca i sl, možemo se posvetiti unosu podataka.

Unos podataka (u postojeće tabele), obavlja se po sledećem obrascu:

		
INSERT INTO naziv_tabele
	(polje_1, polje_2 .... polje_n)
VALUES
	(vrednost_1, vrednost_2 .... vrednost_n)
		
	

Pogledajmo i upit preko koga se mogu uneti podaci u tabele koje smo prethodno kreirali:

		
INSERT INTO prodavci
	(ime, prezime, datum_rodjenja, email)
VALUES
	("Petar", "Jovanović", "1979-06-15", "petar_jovanovic@str.co.rs"),
	("Ivan", "Kovačević", "1993-04-27", "ivan_kovacevic@str.co.rs"),
	("Jelena", "Marković", "1984-09-09", "jelena_markovic@str.co.rs"),
	("Ana", "Stanković", "1991-08-17", "ana_stankovic@str.co.rs");

INSERT INTO prodaja
	(datum, artikl, cena, kolicina, prodavac_id)
VALUES
	("2020-06-15", "Sveska", 80.00, 4, 1),
	("2020-06-15", "Olovka", 56.57, 2, 1),
	("2020-06-15", "Gumica", 34.96, 2, 2),
	("2020-06-15", "Selotejp", 134.57, 1, 2);
		
	

Po pokretanju gornja dva upita, podaci su uredno uneti u obe tabele (nismo pokušali da "prevarimo" MySql), i stoga se podaci nadalje mogu: čitati, menjati, brisati, ili obrađivati na neki drugi način.

Čitanje podataka preko upita (SELECT)

Upit za čitanje podataka (iz postojeće tabele), zadaje se preko komande SELECT, prema sledećem obrascu:

		
SELECT
	polja
FROM
	naziv_tabele
WHERE
	uslov
		
	

Jednostavno čitanje celokupnog sadržaja tabele prodaja, može se izvesti preko sledećeg upita:

		
SELECT * FROM prodaja
		
	

Prethodni upit vratiće sve slogove iz tabele prodaja (odnosno: sva polja - svih slogova).

		
id | ime    | prezime   | datum_rodjenja | email
--------------------------------------------------------------------
1  | Petar  | Jovanović | 1979-06-15     | petar_jovanovic@str.co.rs
2  | Ivan   | Kovačević | 1993-04-27     | ivan_kovacevic@str.co.rs
3  | Jelena | Marković  | 1984-09-09     | jelena_markovic@str.co.rs
4  | Ana    | Stanković | 1991-08-17     | ana_stankovic@str.co.rs
		
	

Međutim, ukoliko je potrebno (zarad preglednosti), prikazati samo određena polja, * ili je potrebno da se u prikaz rezultata privremeno dodaju polja koja podrazumevaju obradu postojećih polja, ** polja se mogu navesti pojedinačno (a postoje i dodatne opcije):

		
SELECT
	datum,
	artikl,
	cena AS 'Cena po artiklu',
	kolicina AS 'Količina',
	cena * kolicina AS 'Ukupna cena po artiklu',
FROM
	prodaja
		
	

Prethodni upit vratiće tabelu sledeće sadržine:

		
datum      | artikl    | Cena po artiklu | Količina | Ukupna cena po artiklu
----------------------------------------------------------------------------
2020-06-15 | Sveska    | 80              | 4        | 320
2020-06-15 | Olovka    | 56.57           | 2        | 113.14
2020-06-15 | Gumica    | 34.96           | 2        | 69.92
2020-06-15 | Selotejp  | 134.57          | 1        | 134.57
		
	

Kao što vidimo, preko upita je moguće dobiti (privremenu) tabelu sa poljima koja predstavljaju rezultat obrade polja tabele koja je zapisana u bazi podataka (množili smo vrednosti polja cena i kolicina u okviru svakog sloga), i takođe je moguće koristiti preglednije nazive za polja ("alijase"), a da bismo na najbolji način zaokružili priču o SELECT upitima, prikazaćemo i kako se podaci mogu filtrirati i sortirati, za šta se koriste sledeće opcije:

• za filtriranje se koristi klauzula WHERE (uz precizno navođenje uslova)
• za sortiranje se koristi klauzula ORDER BY (uz navođenje konkretnog kriterijuma za sortiranje)

Sledeći upit:

		
SELECT
	datum,
	artikl,
	cena AS 'Cena po artiklu',
	kolicina AS 'Količina',
	cena * kolicina AS 'Ukupna cena po artiklu'
FROM
	prodaja
WHERE
	cena * kolicina > 300
ORDER BY
	cena * kolicina DESC
		
	

.... vratiće samo slogove kod kojih je ukupna vrednost (jednog) prodatog artikla veća od 300 Din (za razliku od upita SELECT bez klauzule WHERE, koji vraća sve slogove), pri čemu će tabela biti sortirana u nerastući poredak, po kriterijumu najveće cene.

Upit za ažuriranje slogova (UPDATE)

Za izmenu već unetih podataka koristi se komanda UPDATE prema sledećem obrascu:

		
UPDATE
	naziv_tabele
SET
	polje=vrednost -- naredba dodele (nije uslov)
WHERE
	uslov
		
	

Za primer, pretpostavićemo da je potrebno ažurirati vrednost polja email u prvom slogu u tabeli prodavci:

		
UPDATE
	prodavci
SET
	email='petar_m_jovanovic@str.co.rs'
WHERE
	id=1
		
	

Komanda UPDATE sama po sebi nije destruktivna (u tom smislu da ne briše slogove), ali, pri korišćenju ove komande moramo biti veoma pažljivi, jer - ukoliko se izostavi klauzula WHERE (preko koje se precizira koje slogove je potrebno ažurirati) - biće ažurirani svi slogovi, pri čemu (u iole praktičnom smislu) - ne postoji "undo"!

Uz klauzulu WHERE id=1, upit sa slike #29 se odnosi samo na slog u kome polje id ima vrednost 1.

Upit za uklanjanje slogova (DELETE)

Za uklanjanje slogova koristi se komanda DELETE prema sledećem obrascu:

		
DELETE FROM
	naziv_tabele
WHERE
	uslov
		
	

Za primer, pretpostavićemo da je potrebno ukloniti treći slog u tabeli prodavci:

		
DELETE FROM
	prodavci
WHERE
	id=3
		
	

Pozivanjem gornjeg upita biće uklonjen slog u tabeli prodavci u kome polje id ima vrednost 3, ali (kao što čitaoci verovatno naslućuju :)), slede i dodatna upozorenja ....

Naveli smo da je potrebno postupati veoma pažljivo pri pozivanju upita za ažuriranje, pri čemu se uslov ne sme izostavljati (osim, naravno, ukoliko nam jeste namera da polje u svim redovima dobije istu vrednost). *

Sa komandom DELETE potrebno je postupati još pažljivije jer upit za brisanje jeste destruktivan: pri pozivu komande uz uslov, dolazi do nepovratnog brisanja jednog reda (što je slučaj sa gornjim primerom; inače uslov može obuhvatiti i više redova).

Ako se uslov izostavi, biće obrisani SVI SLOGOVI (i nema 'vraćanja' preko opcije undo)!

Stoga - budite veoma pažljivi. :)

Izmena strukture tabele (ALTER TABLE, ADD, DROP)

Za izmenu strukture tabele, koristi se komanda ALTER TABLE uz dodatne komande kao što su ADD, DROP i sl, prema sledećem obrascu:

		
ALTER TABLE
	naziv_tabele
ADD ili DROP -- RENAME TO, MODIFY/CHANGE COLUMN i sl ....
	opcije
		
	

Preko dodatne komande ADD, umeću se nove kolone u postojeće tabele, a preko (dodatne) komande DROP, uklanjaju se postojeće kolone (naravno, sa pripadajućim sadržajem).

Pogledajmo i nekoliko dodatnih primera ....

Dopunjavanje strukture tabele prodavci, koje podrazumeva umetanje (npr) kolone sa kućnim adresama prodavaca, može se obaviti na sledeći način:

		
ALTER TABLE
	prodavci
ADD
	kucna_adresa varchar(255) NOT NULL
AFTER
	email;
		
	

Uklanjanje određene kolone (uz korišćenje pomoćne komande DROP), obavlja se na sledeći način:

		
ALTER TABLE
	prodavci
DROP
	kucna_adresa
		
	

Implementacija relacija u MySql-u

Iako se korisnici baza podataka uglavnom sreću sa relacijom 1:n, svakako je potrebno biti upoznat i sa načinom za implementiranje relacije 1:1 (sa kojom ćete se sretati bar ponekad), a pogotovo je potrebno biti upoznat sa načinom za implementiranje relacije n:n (koja se zapravo sreće prilično često).

Implementacija relacije 1:n

Pri implementaciji relacije 1:n ne moraju se preduzimati dodatni koraci, budući da je u pitanju podrazumevana relacija.

Za primer (i podsećanje), uzećemo tabele prodavci i prodaja (koje smo već koristili) i, ukoliko se pri definisanju polja ne navedu dodatna ograničenja ....

		
CREATE TABLE prodaja
(
	--ostala polja,
	prodavac_id int NOT NULL,
	FOREIGN KEY (prodavac_id) REFERENCES prodavci(id)
)
		
	

.... bilo koji od entiteta koji se referenciraju preko spoljnjeg ključa, moći će u tabeli da se pojavi više puta (u gornjem primeru: prodavac, predstavljen preko spoljnjeg ključa prodavac_id, moći će da se pojavi u proizvoljnom broju slogova u tabeli prodaja).

Implementacija relacije 1:1

Relacija 1:1 takođe se definiše na jednostavan način: pri kreiranju tabele, uz odgovarajuće polje navodi se rezervisana reč UNIQUE.

Uzmimo (ponovo) za primer gradove, države i glavne gradove.

Tabele sa prethodnog dijagrama mogu se kreirati i povezati preko sledećeg SQL koda:

		
CREATE DATABASE IF NOT EXISTS mini_geografija
	CHARACTER SET utf8,
	COLLATE utf8_general_ci;

USE mini_geografija;

CREATE TABLE grad
(
	id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
	naziv varchar(255) NOT NULL,
	broj_stanovnika int NOT NULL
);

CREATE TABLE drzava
(
	id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
	naziv varchar(255) NOT NULL,
	broj_stanovnika int NOT NULL
);

CREATE TABLE glavni_gradovi
(
	id int NOT NULL AUTO_INCREMENT PRIMARY KEY,

	-- uz polje grad_id mora stajati odrednica UNIQUE
	grad_id int NOT NULL UNIQUE,
	
	-- uz polje drzava_id (praktično) NE SME
	-- stajati odrednica UNIQUE
	drzava_id NOT NULL,
	
	FOREIGN KEY (grad_id) REFERENCES gradovi(id),
	FOREIGN KEY (drzava_id) REFERENCES drzave(id)
)
		
	

Ranije smo razmotrili da se grad u tabeli glavni_gradovi može pojaviti jednom a država više puta, i stoga, pri definisanju polja tabele glavni_gradovi: uz grad (grad_id), stoji odrednica UNIQUE, dok uz državu (drzava_id), ne stoje nikakve dodatne odrednice.

Implementacija relacije n:n

Videli smo da se relacije 1:1 i (pogotovo) 1:n implementiraju na vrlo jednostavan način, međutim, za implementaciju relacije n:n potrebno je malo više pažnje, i dodatna tabela.

Ako se vratimo na primer sa povezivanjem glumaca i filmova, može se primetiti da sledeći format sloga u tabeli glumci (koji je već 'nagovešten' prvi put kada smo pomenuli primer) ....

		
id:      1,
ime:     "Danilo",
nadimak: "Bata",
prezime: "Stojković",
filmovi: "Varljivo Leto '68; Ko to tamo peva; Maratonci trče počasni krug;"
		
	

.... umnogome podseća na neodgovarajuće formate za povezivanje entiteta (sa kojima smo se već sretali u uvodnim poglavljima članka), i može se zaključiti da nije u pitanju optimalno rešenje za implementaciju relacije n:n.

Budući da zapis podataka preko jedne tabele nije optimalno rešenje, relacija n:n se u praksi tipično implementira preko posredničke tabele (preko koje se spajaju id-ovi odgovarajućih entiteta):

U konkretnom primeru, jedna od osnovnih tabela sadrži podatke o glumcima ....

		
CREATE TABLE glumci
(
	id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
	ime varchar(255) NOT NULL,
	prezime varchar(255) NOT NULL,
	datum_rodjenja datetime NOT NULL
)
		
	

.... druga osnovna tabela sadrži podatke o filmovima ....

		
CREATE TABLE filmovi
(
	id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
	naziv varchar(255) NOT NULL,
	godina_proizvodnje int NOT NULL
)
		
	

.... a preko dodatne (posredničke) tabele, entiteti iz prve tabele (glumci), spajaju se sa entitetima iz druge tabele (filmovima):

		
CREATE TABLE povezivanje
(
	id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
	glumac_id int NOT NULL,
	film_id int NOT NULL,

	FOREIGN KEY (glumac_id) REFERENCES glumci(id),
	FOREIGN KEY (film_id) REFERENCES filmovi(id)
)
		
	

Preko posredničke tabele, omogućili smo povezivanje proizvoljnog broja glumaca sa proizvoljnim brojem filmova (a budući da smo uključili proveru referencijalnog integriteta, možemo se osloniti na to da će sam DBMS voditi računa da se u tabeli povezivanje ne pojave glumci koji ne postoje u bazi, niti filmovi koji ne postoje u bazi).

Ugnežđeni upiti

Pošto smo se u prethodnim poglavljima upoznali sa osnovama relacionog modela i jednostavnijim upitima, u nastavku ćemo se upoznati i sa nekoliko kompleksnijih tehnika.

Ugnežđavanje upita (prva kategorija kompleksnijih upita o kojima ćemo diskutovati u uvodnom članku), podrazumeva korišćenje određenog upita unutar drugog upita.

Za primer ćemo uzeti operaciju koju smo nagovestili na početku članka: pronalaženje (svih) podataka o prodavcu koji je obavio određenu prodaju (koja je zabeležena u tabeli prodaja), a ako se pitate "u čemu je (tačno) problem", podsetićemo se na organizaciju podataka koju smo (takođe) uveli na početku:

• u tabeli prodaja upisani su samo id-ovi prodavaca (iz tabele prodavci)
• u tabeli prodavci ne postoje podaci o prodaji artikala (to jest, podaci o tome ko je od prodavaca prodao koje artikle)

Problem se može rešiti preko sledećeg upita ....

		
SELECT
	ime, prezime, email
FROM
	prodavci
WHERE
	prodavci.id = (SELECT prodaja.prodavac_id FROM prodaja WHERE prodaja.id=3)
		
	

.... koji prvo pronalazi id prodavca, posle čega se nađeni id koristi u upitu koji pretražuje tabelu prodavci.

Da pojasnimo dodatno:

• prvo se izvršava unutrašnji upit (koji je obuhvaćen zagradama)
• navedeni upit se svodi na jedan brojčani podatak
• brojčani podatak koristi se kao kriterijum za pretragu u WHERE klauzuli glavnog upita

Po koracima:

- "Unutrašnji" SELECT upit ....

		
SELECT prodaja.prodavac_id FROM prodaja WHERE prodaja.id=3
		
	

.... praktično pronalazi (samo) id prodavca koji je u tabeli prodaja povezan sa određenom prodajom.

- Rezultat izvršavanja unutrašnjeg upita je jedna celobrojna vrednost, posle čega 'glavni upit' praktično postaje ....

		
SELECT
	ime, prezime, email
FROM
	prodavci
WHERE
	prodavci.id = 2
		
	

.... što je sintaksa koja sama po sebi predstavlja vrlo jednostavan upit (nalik osnovnim upitima sa kakvima smo se susretali još u odeljku koji je posvećen komandi SELECT).

Pridruživanje podataka (JOIN)

Da bismo se što bolje upoznali sa opcijama za 'spajanje podataka', sagledaćemo nekoliko primera koje smo nagovestili na početku (i upoznaćemo se sa 'tehnikalijama' koje stoje iza svega).

Najvažniji primer (već pomenut u prethodnom poglavlju), tiče se kreiranja pregleda prodaje u kome su prodavci predstavljeni imenom i prezimenom, ali, kasnije ćemo koristiti druge, prikladnije primere.

U svakom slučaju, preko komande JOIN moguće je spojiti podatke iz dve ili više tabela, pri čemu se kao kriterijum za spajanje koriste podaci koji se pojavljuju u svim tabelama koje učestvuju u spajanju (naravno, kriterijumi se moraju navesti precizno).

Izvršavanjem sledećeg upita:

		
SELECT
	prodaja.datum,
	prodaja.artikl,
	prodaja.cena,
	prodaja.kolicina,
	prodavci.ime,
	prodavci.prezime
FROM
	prodavci INNER JOIN prodaja ON
	prodavci.id = prodaja.prodavac_id
		
	

.... u pregledu se pojavljuju sve pojedinačne prodaje artikala - uz koje stoji ime i prezime prodavca:

		
datum      | artikl   | cena   | kolicina | ime   | prezime
-------------------------------------------------------------
2020-06-15 | Sveska   | 80     | 4        | Petar | Jovanović
2020-06-15 | Olovka   | 56.57  | 2        | Petar | Jovanović
2020-06-15 | Gumica   | 34.96  | 2        | Ivan  | Kovačević
2020-06-15 | Selotejp | 134.57 | 1        | Ivan  | Kovačević
		
	

Verujemo da se na ovom mestu (sasvim prirodno) javljaju i pitanja: kako se podaci zapravo izdvajaju i spajaju, i kako će (tačno) slogovi biti sortirani?

Kako se podaci spajaju i izdvajaju?

Pretpostavljamo da prepoznajete zašto smo praktičan rezultat izvršavanja komande za spajanje dve tabele prikazali odmah (to jest pre 'uvodne teorije') - nismo želeli da vas ostavimo pod utiskom da ćete naredne odeljke čitati "tek onako". :)

Upoznavanje sa teorijom nećemo preskakati (naravno), ali, na ovom mestu pre svega moramo primetiti da glavni primer koji koristimo u članku (baza podataka o prodaji artikala), nije u stanju da obuhvati sve tipične situacije do kojih može doći pri spajanju podataka preko komande JOIN, i stoga ćemo se privremeno poslužiti drugim primerom - preko koga ćemo bolje objasniti teoriju.

Dodatni primer biće baza podataka preko koje se beleže podaci vezani za upis studenata na kurseve (na početku akademske godine), pri čemu će baza biti implementirana preko sledećih tabela:

• studenti - podaci o studentima
• kursevi - podaci o kursevima
• upis - pomoćna tabela preko koje se povezuju podaci iz prve dve tabele

Pre nego što se osvrnemo na različite varijacije komande JOIN, osvrnimo se na različite situacije opšteg tipa, koje se daju zamisliti tokom upisa studenata na kurseve. *

Dok upis (još uvek) traje, u određenom trenutku može nastati jedna od sledećih situacija:

• među studentima ima onih koji se još uvek nisu prijavili ni za jedan kurs
• postoje kursevi za koje nema prijava (i naravno ....)
• postoje kursevi za koje je prijavljen određen broj studenata

.... pri čemu je potrebno da postoji i sistem koji omogućava "očitavanje trenutnog stanja" (tj. pronalaženje korisnih informacija).

Različite varijante komande JOIN - mogu pomoći u rešavanju postavljenog zadatka.

Koristićemo usput i šematski prikaz, u kome su tabele popunjene tako da je određen broj studenata (s1-s4), prijavljen za određen broj kurseva (k1-k3, k5), preko tabele upis:

INNER JOIN

U opštem smislu, preko sledeće naredbe ....

		
SELECT
	polja
FROM
	T1 INNER JOIN T2 on T1.px=T2.py
		
	

.... izdvajaju se i prikazuju svi slogovi iz obe tabele za koje važi da polje px iz tabele T1 ima istu vrednost kao polje py iz tabele T2.

Međutim, pravi smisao (različitih varijacija) komande JOIN gotovo je nemoguće razumeti bez konkretnog konteksta (tj. bez prikladnog / lako razumljivog primera), i stoga ćemo se usmeriti (upravo) na konkretan primer sa upisom studenata na kurseve (koji je, doduše, složeniji sam po sebi, i podrazumeva da se dve tabele povezuju preko posredničke tabele).

U primeru: ako se za spajanje koristi komanda INNER JOIN, upit će izdvojiti samo one slogove iz tabele studenti koji su se pojavili u tabeli upis, i samo one slogove iz tabele kursevi koji su se pojavili u tabeli upis.

		
SELECT
	CONCAT(studenti.ime, " ", studenti.prezime) AS "Student",
	studenti.br_indeksa,
	kursevi.naziv AS "Kurs"
FROM
	(studenti INNER JOIN upis ON studenti.id=upis.student_id)
	INNER JOIN kursevi ON kursevi.id=upis.kurs_id
ORDER BY
	studenti.id, kursevi.id
		
	

U praktičnom smislu: u pregledu će se pojaviti samo studenti koji su prijavljeni za kurseve i samo oni kursevi za koje postoje prijave.

LEFT JOIN

U opštem smislu, preko sledeće naredbe ....

		
SELECT
	polja
FROM
	T1 LEFT JOIN T2 on T1.px=T2.py
		
	

.... izdvajaju se i prikazuju svi slogovi iz obe tabele za koje važi da polje px iz tabele T1 ima istu vrednost kao polje py iz tabele T2 (kao u slučaju kada se poziva komanda INNER JOIN), ali, prikazuju se i svi ostali slogovi iz tabele T1 (to jest, slogovi iz "leve" tabele).

U primeru: ako se za spajanje koristi komanda LEFT JOIN, upit će izdvojiti sve slogove iz tabele studenti, i samo one slogove iz tabele kursevi koji su se pojavili u tabeli upis.

		
SELECT
	CONCAT(studenti.ime, " ", studenti.prezime) AS "Student",
	studenti.br_indeksa,
	kursevi.naziv AS "Kurs"
FROM
	(studenti LEFT JOIN upis ON studenti.id=upis.student_id)
	LEFT JOIN kursevi ON kursevi.id=upis.kurs_id
ORDER BY
	studenti.id, kursevi.id
		
	

U praktičnom smislu: u pregledu će se pojaviti svi studenti i samo oni kursevi za koje postoje prijave.

U prethodnom slučaju (kada smo koristili komandu INNER JOIN): po izvršavanju upita, pored podataka o svakom studentu bio je naveden i jedan od kurseva (to nismo prikazali na "plavo-zelenim" šemama, ali, možete primetiti ako pokrenete upite).

U slučaju komande LEFT JOIN, pored podataka o studentu koji nije prijavljen za kurs, stoji vrednost NULL (na slici simbolično označeno tamnijom nijansom plave). Na ovaj način (i s obzirom na metodu sortiranja koju smo izabrali), u rezultujućoj tabeli se lako mogu primetiti studenti koji nisu prijavljeni za kurseve.

RIGHT JOIN

U opštem smislu, preko sledeće naredbe ....

		
SELECT
	polja
FROM
	T1 RIGHT JOIN T2 on T1.px=T2.py
		
	

.... izdvajaju se i prikazuju svi slogovi iz obe tabele za koje važi da polje px iz tabele T1 ima istu vrednost kao polje py iz tabele T2, a prikazuju se i sva polja iz tabele T2 (tj. iz "desne" tabele).

U primeru: ako se za spajanje koristi komanda RIGHT JOIN, upit će izdvojiti samo one slogove iz tabele studenti koji su se pojavili u tabeli upis i (ovoga puta), sve slogove iz tabele kursevi.

		
SELECT
	CONCAT(studenti.ime, " ", studenti.prezime) AS "Student",
	studenti.br_indeksa,
	kursevi.naziv AS "Kurs"
FROM
	(studenti RIGHT JOIN upis ON studenti.id=upis.student_id)
	RIGHT JOIN kursevi ON kursevi.id=upis.kurs_id
ORDER BY
	studenti.id, kursevi.id
		
	

U praktičnom smislu: u pregledu će se pojaviti prijavljeni studenti i svi kursevi, i stoga (s obzirom na izabranu metodu sortiranja), biće lako uočiti kurseve za koje ne postoje prijave (na slici označeno tamnijom nijansom zelene).

OUTER JOIN

U opštem smislu, preko sledeće naredbe ....

		
SELECT
	polja
FROM
	T1 FULL OUTER JOIN T2 on T1.px=T2.py
		
	

.... izdvajaju se i prikazuju svi slogovi iz obe tabele za koje važi da polje px iz tabele T1 ima istu vrednost kao polje py iz tabele T2, ali - prikazuju se i ostali slogovi iz obe tabele.

U primeru: ako se za spajanje koristi komanda OUTER JOIN, upit će prikazati sve studente i sve kurseve.

		
SELECT
	CONCAT(studenti.ime, " ", studenti.prezime) AS "Student",
	studenti.br_indeksa,
	kursevi.naziv AS "Kurs"
FROM
	(studenti FULL OUTER JOIN upis ON studenti.id=upis.student_id)
	FULL OUTER JOIN kursevi ON kursevi.id=upis.kurs_id
ORDER BY
	studenti.id, kursevi.id
		
	

Ukoliko je količina podataka umerena, ovakav pregled može (na primer) biti od koristi ako se rezultujuća tabela odštampa (pa 'odokativnom' metodom možemo primetiti informacije koje nas zanimaju).

U svakom slučaju, kako god da su podaci izdvojeni, ostaje pitanje kako će slogovi u rezultujućoj tabeli biti sortirani.

Sortiranje podataka

Ako se pri prosleđivanju upita ne navede kriterijum za sortiranje, DBMS će samostalno izabrati način sortiranja (prema rasporedu podataka u memoriji i sl), i stoga - ukoliko je potrebno da podaci budu precizno sortirani prema određenom kriterijumu - svakako je najbolje da kriterijum navedemo sami.

U primerima sa upisom studenata, "prećutno" je izabran odgovarajući metod sortiranja, međutim, za primer iz baze prodaja, 'ručno' ćemo podesiti da podaci budu sortirani po datumu prodaje - ORDER BY prodaja.datum:

		
SELECT
	prodaja.datum,
	prodaja.artikl,
	prodaja.cena,
	prodaja.kolicina,
	prodavci.ime,
	prodavci.prezime
FROM
	prodavci INNER JOIN prodaja ON
	prodavci.id = prodaja.prodavac_id
ORDER BY
	prodaja.datum
		
	

Uprošćena sintaksa za INNER JOIN i još jedan praktičan primer

SQL dozvoljava da se upit za spajanje podataka, za koji bi inače bilo potrebno koristiti komandu INNER JOIN, pozove preko drugačije sintakse (koja je, reklo bi se, elegantnija):

		
SELECT
	prodaja.datum,
	prodaja.artikl,
	prodaja.cena,
	prodaja.kolicina,
	prodavci.ime,
	prodavci.prezime
FROM
	prodavci, prodaja
WHERE
	prodavci.id = prodaja.prodavac_id
ORDER BY
	prodaja.datum
		
	

Prikazani pristup deluje "manje zvanično" (budući da se ne koristi komanda INNER JOIN), ali, rezultat je (praktično) isti.

Preostaje da se osvrnemo na još jedan primer (koji možete isprobati bilo preko komande JOIN, bilo preko uprošćene sintakse koju smo prikazali).

Može se primetiti da polja cena i kolicina ne upućuju 'baš najbolje' na pravi smisao podataka koji se pojavljuju u kolonama:

• za cenu nije naznačeno da li je u pitanju cena po jedinici količine (ili ukupna cena za artikl, koja uzima u obzir i količinu)
• nije precizirano da li je u cenu uračunat porez (ili nije)
• za količinu nije naznačena jedinica mere

.... a mogli bismo navesti i još koju primedbu.

Shodno svemu što je navedeno, upit se može sažeti i upotpuniti na sledeći način:

		
SELECT
	prodaja.artikl,
	CONCAT(prodaja.cena * prodaja.kolicina, "din.")
		AS "Cena po artiklu (bez PDV-a)",
	CONCAT(prodavci.ime, " ", prodavci.prezime)
		AS "Prodavac"
FROM
	prodavci INNER JOIN prodaja ON
	prodavci.id = prodaja.prodavac_id
ORDER BY
	prodaja.datum
		
	

• vrednosti polja cena i kolicina (iz tabele prodaja), množe se unutar funkcije CONCAT, a rezultat množenja se predstavlja preko aliasa "Cena po artiklu (bez PDV-a)"
• podaci o prodavcu se spajaju preko funkcije CONCAT *

Pokretanjem gornjeg upita, dobija se tabela sledećeg sadržaja:

		
artikl   | Cena po artiklu (bez PDV-a) | Prodavac
--------------------------------------------------------
Sveska   | 320                         | Jelena Marković
Olovka   | 113.14                      | Petar Jovanović
Gumica   | 69.92                       | Ivan Kovačević
Selotejp | 134.57                      | Ivan Kovačević
		
	

Pre nego što se posvetimo agregatnim funkcijama (čime ćemo završiti uvodni članak o relacionim bazama podataka), podsetićemo se na konstataciju (koju smo izneli ranije), o tome da je prava svrha baza podataka - pronalaženje korisnih informacija.

Nekada je 'najbolja informacija' u datom trenutku - pregled tabele koja sadrži veću količinu podataka, ali, često je najbolja informacija - jedan podatak, koji se dobija obradom veće količine podataka (i može se koristiti na razne načine) ....

Agregatne funkcije

Agregatne funkcije u MySql-u (i drugim RDBM sistemima), koriste se za obavljanje proračuna preko kojih se određena kolekcija podataka * svodi na jedan podatak.

MySql omogućava korišćenje pet agregatnih funkcija:

• MIN (minimum)
• MAX (maksimum)
• COUNT (prebrojavanje)
• SUM (suma)
• AVG (prosek)

Opšta šema izvršavanja je sledeća ....

		
SELECT AGR_F(raspon_polja) FROM tabela;
		
	

.... s tim da je (po potrebi) moguće dodavati i klauzule GROUP BY i HAVING.

U narednim odeljcima, detaljnije ćemo prikazati smisao i upotrebnu vrednost prethodno navedenih opcija.

MIN

Agregatna funkcija za pronalaženje minimalne vrednosti, poziva se prema sledećem obrascu:

		
SELECT MIN(raspon_polja) FROM tabela;
		
	

Među svim vrednostima u kolonama koje su označene kao "raspon_polja", pronalazi se (i na kraju ispisuje), najmanja vrednost.

Ako za primer uzmemo da je potrebno pronaći cenu najjeftinijeg prodatog artikla (u tabeli prodaja), * problem se rešava preko sledećeg upita:

		
SELECT MIN(prodaja.cena) FROM prodaja;

-- Rezultat: 34.96
		
	

MAX

Agregatna funkcija za pronalaženje maksimalne vrednosti, poziva se prema sledećem obrascu:

		
SELECT MAX(raspon_polja) FROM tabela;
		
	

Među svim vrednostima u kolonama koje su označene kao "raspon_polja", pronalazi se (i na kraju ispisuje), najveća vrednost.

Za primer možemo uzeti upit koji pronalazi datum i vreme poslednje prodaje:

		
SELECT MAX(prodaja.datum) FROM prodaja;

-- Rezultat: 2020-06-15 00:00:00
		
	

Da bismo se (polako) pripremili za glavni primer, upoznaćemo se sa klauzulom GROUP BY, preko upita u kome se pronalazi najskuplji prodati artikl za svakog prodavca:

		
SELECT
	prodaja.prodavac_id,
	MAX(cena)
FROM
	prodaja
GROUP BY
	prodaja.prodavac_id
ORDER BY 
	prodaja.prodavac_id

-- Rezultat:
-- prodavac_id | MAX (cena)
-- ------------------------
-- 1           | 214.5
-- 2           | 134.57
-- 3           | 80
-- 4           | 214.5
		
	

Da pojasnimo:

• bez klauzule GROUP BY, rezultat upita je 'doslovno najskuplji' artikl u celoj tabeli, ali, uz klauzulu GROUP BY, za svakog prodavca pronalazi se grupa slogova u kojima je dati prodavac obavio prodaju, i potom se u svakoj grupi pronalazi najskuplji artikl
• klauzula ORDER BY ovoga puta nije od 'prevelike važnosti', ali, obezbeđuje da prodavci budu poređani prema id-ovima

Pogledajmo još jedan primer ....

Ukoliko je potrebno prepraviti prethodno prikazani upit, tako da se u rezultujućoj tabeli pojavi podatak vezan za jednog od prodavaca, problem se rešava uz upotrebu klauzule HAVING:

		
SELECT
	prodaja.prodavac_id,
	MAX(cena)
FROM
	prodaja
GROUP BY
	prodaja.prodavac_id
HAVING
	prodaja.prodavac_id = 1

-- Rezultat:
-- prodavac_id | MAX (cena)
-- ------------------------
-- 1           | 214.5
		
	

Nastavićemo uz pretpostavku da su čitaoci do sada stekli predstavu o smislu agregatnih funkcija u MySql-u (nadamo se da je tako :)), i stoga ćemo preostale tri funkcije prikazati uprošćeno ("šematski"), zarad očuvanja preglednosti članka.

COUNT

Agregatna funkcija za prebrojavanje polja, poziva se na sledeći način:

		
-- Šema:
SELECT COUNT(raspon_polja) FROM tabela;

-- Primer:
SELECT COUNT(DISTINCT prodaja.artikl) FROM prodaja

-- Rezultat: 5
-- Objašnjenje: Ukupno pet različitih
-- artikala se pojavljuje u pregledu prodaje
		
	

SUM

Agregatna funkcija za sumiranje vrednosti u poljima, poziva se na sledeći način:

		
-- Šema:
SELECT SUM(raspon_polja) FROM tabela;

-- Primer:
SELECT SUM(prodaja.cena * prodaja.kolicina) FROM prodaja

-- Rezultat: 1790.1499
-- Objašnjenje: Ukupna vrednost svih
-- prodatih artikala je: 1790.1499
		
	

Za razliku od ranijih primera, ovoga puta smo agregatnoj funkciji predali na obradu raspon vrednosti koje se dobijaju obradom polja iz više kolona.

AVG

Agregatna funkcija za računanje prosečne vrednosti u poljima, poziva se na sledeći način:

		
-- Šema:
SELECT AVG(raspon_polja) FROM tabela;

-- Primer:
SELECT AVG(prodaja.cena) FROM prodaja

-- Rezultat: 96.195
-- Objašnjenje: Prosečna cena prodatog
-- artikla je: 96.195
		
	

Primer složenog upita sa agregatnim funkcijama

Razmotrićemo primer pronalaženja sloga u kome se pojavljuje najveća cena za prodati artikl, tako da se u obzir uzima cena pojedinačnog artikla i količina, i da se pri tom podaci prikazuju na 'pregledan' način (uz imenovanje prodavca i sl).

Za početak, potrebno je pronaći slog koji odgovara prethodno navedenom kriterijumu (za šta se može iskoristiti upit koji je idejno sličan jednom od 'agregatnih upita' koje smo prethodno prikazali) ....

		
SELECT MAX(prodaja.cena * prodaja.kolicina) FROM prodaja
		
	

.... međutim, preko gornjeg upita, ne pronalaze se svi podaci (već samo "osnovni podatak"), ali, ponovo se srećemo sa situacijom u kojoj se "rezultat jednog upita može iskoristiti unutar drugog upita".

Da bismo pronašli i ostale podatke (tj. da bi na kraju bio formiran 'pravi' pregled sloga u kome se pojavljuje najveći iznos prodaje pojedinačnog artikla), potrebno je da osmislimo opsežniji upit, u kome se prethodna komanda pojavljuje u svojstvu "unutrašnjeg upita":

		
SELECT
	prodaja.datum,
    prodaja.artikl,
    CONCAT(prodaja.cena * prodaja.kolicina, "din.")
        AS "Cena po artiklu (bez PDV-a)",
    CONCAT(prodavci.ime, " ", prodavci.prezime)
        AS "Prodavac"
FROM
    prodavci INNER JOIN prodaja ON
    prodavci.id = prodaja.prodavac_id
WHERE
	prodaja.cena * prodaja.kolicina = (SELECT MAX(prodaja.cena * prodaja.kolicina) FROM prodaja)
ORDER BY
    prodaja.datum

-- Rezultat:
-- datum      | artikl | Cena po artiklu | Prodavac
-- -------------------------------------------------------
-- 2020-06-15 | Sveska | 320din.         | Jelena Marković
		
	

Gornji upit je svakako ponešto složeniji od (većine) prethodnih upita, ali, sadrži sve osnovne ideje sa kojima smo se već sretali:

• obradu vrednosti iz različitih polja
• korišćenje MySql funkcija
• aliase
• INNER JOIN (zarad spajanja podataka iz više tabela)
• korišćenje agregatne funkcije (ovoga puta, u okviru "unutrašnjeg upita")

Prikazani upit poslužiće i kao uvod u neki novi članak o bazama podataka, u kome ćemo se baviti složenim upitima.

Rezime ....

Posle teoretske diskusije i praktičnih primera, osvrnućemo se takođe (za sam kraj), na svojevrstan "paradoks" koji ćete verovatno uočiti čim budete počeli da se bavite bazama podataka u 'produkcijskom' okruženju (pri čemu će situacija najverovatnije delovati .... "pomalo čudno").

Sa jedne strane, sasvim je moguće da ćete se odmah susresti sa projektima koji će od vas nedvosmisleno zahtevati da dodatno unapredite poznavanje baza podataka (u odnosu na nivo koji je prikazan u članku), dok - sa druge strane - lako možete doći u kontakt i sa određenim projektima (koji su prilično ozbiljni sami po sebi), u kojima se koristi znatno manji podskup SQL komandi za rad sa bazama (u odnosu na opcije koje su prikazane u članku), ali, ne dajte se zbuniti.

Prva situacija je jasna sama po sebi, a za drugu situaciju takođe postoji sasvim jednostavno objašnjenje: u mnogim informacionim sistemima nema potrebe za "ekstravagantnim zahvatima" nad bazama podataka, već je samo neophodno da baza podataka - isporučuje podatke (na brz i pouzdan način).

Sledeći korak u vezi sa bazama podataka (u smislu članaka koje ćemo objaviti, i nešto čemu ćemo se posvetiti u (vrlo) doglednoj budućnosti), biće korišćenje baza podataka u programskim jezicima (pre svega, 'najuobičajeniji primer' - MySql i PHP, ali, biće i drugih primera).