Biranje ostalih svojstava kolona (Choosing other column properties)

Dva usko povezana pojma sa projektovanjem baza podataka su indeksi i kjucevi.

Indeks je nacin da se od baze podataka zahteva paznja prema vrednostima konkretne kolone ili kombinacije kolona.

Kljuc u tabeli baze podataka je sastvani deo procesa "normalizacije" koji se koristi u projektovanju slozenijih baza podataka. Postoje sve vrste kljuceva: primarni i strani (eng. foreign). Svaka tabela treba da ima tacno jedan kljuc, aprimarni kljuc jedne tabele je cesto povezan kao strani kljuc druge tabele.

Primarni kljuc mora da postuje tri pravila:

• uvek mora da ima vrednost
• ta vrednost ne sme nikad da se menja
• ta vrednost mora za svaki zapis da bude jedinstvena u toj tabeli

1. Pokrenite XAMPP. Kliknite na Admin u liniji MySQL-a ili ukucajte u address bar http://localhost/phpmyadmin/.
2. Sa leve strane odaberite bazu podataka, u ovom primeru, motori i kliknite na tabelu motociklisti.

Tipovi podataka u MySQL-u (Data Types in MySQL)

Biranje tipova za kolone (Choosing the types of columns)

Kad jednom odredite tabele i kolone potrebne u bazi podataka, trebalo bi da odredite tip podatka za svaku kolonu. Kada pravite tabelu, MySQL zahteva da se za svaku kolonu eksplicitno navede koju vrstu informacija ce da sadrzi. Postoje tri osnovna tipa:

• tekst (srting)
• brojevi
• datum i vreme 

Pravilno biranje tipova  za kolone odredjuje koje informacije ce moci da se cuvaju i na koji nacin. Takodje, pravilno biranje tipova utice na sveukupne performanse baze podataka. 

Mnogi tipovi prihvataju neobavezan atribut Length. koji ogranicava duzinu. Uglaste zagrade [] oznacavaju da je parametar neobavezan i da se stavlja u zagradu. Za bolje performanse baze podataka, trebalo bi da postavite neka ogranicenja na kolicinu podataka.

Napomena: ako pokusate da ubacite string duzine od pet znakova u kolonu CHAR(2), zadnja tri znaka ce biti odsecena. Sacuvala bi se samo prva dva znaka, ostatak bi bio zauvek izgubljen. To vazi za svako polje  kojem je fiksirana duzina (CHAR, VARCHAR, INT, itd.).

Imenovanje elemenata baze podataka (Naming of database elements)

Kada pravite baze podataka i tabele, trebalo bi da smisljate imena (identifikatore), koja su jasna, znacajna i koja se lako kucaju. 

Identifikatori:

• treba da sadrze samo slova (a-z, A-Z), brojeve (0-9) i donju crtu (_) bez razmaka.
• ne treba da budu isti kao postojece kljucne reci (kao neki SQL pojam ili ime funkcije)
• treba voditi racuna od malim i velikim slovima
• ne smeju da budu duzi od 64 znaka tj. 64 bajta (priblizno)
• moraju da budu jedinstveni u svom opsegu

Objasnjenje: rec treba smo upotrebili sa znacenjem da je dobra praksa u pitanju a ne stroga pravila. Postovanje ovih preporuka pomaze da se izbegnu komplikacije. Poslednje pravilo znaci da tebela ne moze da ima dve kolone koje se isto zovu i da baza podataka ne moze da ima  dve tabele koje se isto zovu.

Brisanje zapisa iz baze podataka (Deleting records from the database)

Za brisanje redova iz tabele sluzi iskaz delete. 

delete [low_priority] [quick] [ignore] from tabela
[where] uslov
[order by] kolone
[limit] broj

Napomena: ako napisete samo:

delete from tabela;

izbrisacete sve redove iz tabele, zato budite pazljivi.

Ako imate kupca koji dugo nista nije kupio, pa zelite da ga obrisete.

Primer:

delete from kupci

where sifrakupca=7;

Tabela izmene koje omogucava iskaz alter table (Table-changes for statement ALTER TABLE)

Naknadne izmene struktura tabela (Any subsequent changes to the structure of table)

Osim azuriranja redova, ponekad cete zeleti da izmenite strukturu tabela u bazi podataka. To mozete postici uz pomoc iskaza alter table. 

Sintaksa:

alter table [ignore] tabela izmena [, izmena ...]

Napomena: u ANSI SQL-u je moguca samo jedna izmena po iskazu alter table, dok je u MySQL-u mozete da zadate neogranicen broj izmena u istom iskazu. 

Ako upotrebite odredbu ignore, a izmena koju uvodite duplira neku vrednost u koloni primarnog kljuca, red u kome se nalazi prva takva vrednost ostace ce sacuvan u izmenjenoj tabeli, dok se drugi takvi redovi brisu. Ako ne zadate odredbu ignore, izmena nece biti uneta u tabelu.

U sledecem clanku je tabela - izmene koje omogucava iskaz alter table. 

Ako u koloni ne mogu da stanu podaci koje treba da sadrzi, npr., predvideli ste da u tabeli kupci imena kupaca budu max. duzine 50 znakova. 

Azuriranje zapisa u bazi podataka (Updating records in the database)

Osim ucitavanja podataka iz baze, neke medju njima cete  cesto i menjati. Npr, mozete povecati cenu motora. Za to se koristi iskaz update.

Sintaksa:

update ime_tabele
set kolona1=izraz1,kolona2=izraz2,...
[where uslovi]
[order by redosled]

[limit broj]

Objasnjenje:

Poenta je da se u tabeli ime_tabele sve navedene kolone azuriraju u zavisnosti od zadatih uslova. Pomocu odredbe where azuriranje mozete ograniciti samo na odredjene redove, a pomocu odredbe limit mozete da  ogranicite ukupan broj azuriranih redova. Odredba order by koristi se samo u kombinacji sa odredbom limit; npr. kada zelite da azurirate samo prvih deset redova, a zelite prvo da ih poredjate odredjenim redosledom. Opcije low priority i ignore deluju na isti nacin kao iskazu insert. 

Upotreba podupita umesto privremene tabele (Using subqueries instead of temporary tables)

Podupit mozete navesti u odredbi from spoljasnjeg upita. 

Ako vam je potrebno da pretrazujete rezultate podupita, koji u tom slucaju igra ulogu privremene tabele.

Primer:

select * from
(select sifrakupca, ime from kupci where grad='Split')
as split_kupci;

Ovaj fajl sacuvajte kao podupiti.sql.

1. Pokrenite XAMPP. Kliknite na Admin u liniji MySQL-a ili ukucajte u address bar http://localhost/phpmyadmin/

2. Sa leve strane izaberite bazu podataka volimmotore, a sa desne izberite karticu SQL.
3. Ubacite ovaj kod i kliknite dugme "Go".

Podupiti koji vracaju redove podataka (Subqueries that return rows of data)

Svi primeri koje smo do sada videli vracali su samo po jednu vrednost. Postoje i upiti koji vracaju ceo red podataka, koji se moze porediti sa redovima podataka u spoljasnjem upitu.

Ako vam je potrebno da pronadjete sve redove u jednoj tabeli koji postoje i u drugoj tabeli. Opsti oblik sintakse:

select c1, c2, c3
from t1
where (c1, c2, c3) in (select c1, c2, c3 from t2);

All the examples we have seen so far returned only one value. There are queries that return the entire row of data, which is comparable to the data with rows in the outer query.

If you need to find all the rows in a table that also exist in the other table. The general syntax:

SELECT c1, c2, c3
FROM t1
WHERE (c1, c2, c3) in (SELECT c1, c2, c3 FROM t2);

Korelirani podupiti (Correlated subqueries)

Korelirani podupiti su nesto slozeniji jer elemente spoljnog upita mozete upotrebiti u unutrasnjem upitu. Traze se redovi iz unutrasnjeg upita koji odgovaraju redovima iz spoljasnjeg upita.

Primer:

select brojmotora, imemotora
from motori
where not exists
(select * from stvari_porudzbina 
where stvari_porudzbina.brojmotora=motori.brojmotora);

Ova fajl sacuvajte kao korelirani-podupiti.sql.

Napomena: posto je do sada kupljen svaki motor, za potrebe ovog primera, dodala sam dva nova motora. Da bi ste uneli ove podatke sledite korake 1, 2, 3.

Osnovni podupiti (Basic subqueries)

Podupiti se najcesce koriste kada se rezultat jednog (unutrasnjeg) upita poredi u uslovu drugog (spoljasnjeg) upita. 

Ako zelite da pronadjete porudzbinu sa najvecim iznosom.

Primer:

select sifrakupca, iznos
from porudzbine
where iznos = (select max(iznos) from porudzbine);

Napomena: nema razmak izmedju max i otvorene zagrade.

Ovaj fajl sacuvajte kao osnovni-podupiti.sql.

1. Pokrenite XAMPP. Kliknite na Admin u liniji MySQL-a ili ukucajte u address bar http://localhost/phpmyadmin/

2. Sa leve strane izaberite bazu podataka volimmotore, a sa desne izberite karticu SQL.

3. Ubacite ovaj kod i kliknite dugme "Go".

Podupiti (Subqueries)

Podupit (en. subquery) je upit ugnjezden unutar drugog upita. 

Vrste i upotreba podupita:

• osnovni podupiti
• korelirani podupiti
• podupiti koji vracaju redove podataka
• upotreba podupita umesto privremene tabele

Subquery is the request nested inside another query.

Types and use of subqueries:

• basic subqueries
• correlated subqueries
• subqueries that return rows of data
• using subqueries instead of temporary tables

Izbor redova koje upit treba da vrati-limit (The selection of rows that query should return-LIMIT)

Limit je jedna od odredaba iskaza select koja omogucava da naznacite koji redovi iz rezultata treba da budu vraceni. Ova odredba ima dva parametra: broj pocetnog reda i ukupan broj redova koje upit treba da vrati.

Primer: 

select ime
from kupci
limit 2, 3;

Ovaj fajl sacuvajte kao limit.sql.

1. Pokrenite XAMPP. Kliknite na Admin u liniji MySQL-a ili ukucajte u address bar http://localhost/phpmyadmin/

2. Sa leve strane izaberite bazu podataka volimmotore, a sa desne izberite karticu SQL.

3. Ubacite ovaj kod i kliknite dugme "Go".

Tabela grupnih funkcija (Group functions table)

avg (kolona) - daje prosecnu vrednost u navedenoj koloni.

count (stavke) - ako navedete kolonu, ova funkcija daje ukupan broj vrednosti u toj koloni (null se zanemarije). Ukoliko ispred imena kolone dodate rec distinct, dobicete ukupan broj jedinstvenih vrednosti u toj koloni. Ako zadate count(*), dobicete ukupan broj redova u tabeli, bez obzira na to da li neki medju njima sadrze vrednosti null. 

min (kolona) - daje minimalnu vrednost u navedenoj koloni. 

max (kolona) - daje maksimalnu vrednost u u navedenoj koloni.

std (kolona) - daje standarnu devijaciju vrednosti u u navedenoj koloni.

stddev (kolona) - isto kao i prethodna funkcija.

sum (kolona) - daje ukupan zbir vrednost u navedenoj koloni. 


AVG (column) - gives the average value in the specified column.

COUNT (items) - if you specify the column, this function gives the total number of values in the column (NULL is undermining). If in the front of the column name you add the word DISTINCT, you will get the total number of unique values in that column. If you specify a COUNT(*), you will get the total number of rows in the table, regardless of whether some of them contain NULL values.

MIN (COLUMN) - gives a minimum value in the specified column.

MAX (COLUMN) - gives a maximum value in in the  specified column.

STD (COLUMN) - gives a standard deviation values in in the specified column.

STDDEV (COLUMN) - the same as the previous function.

SUM (COLUMN) - gives a total sum value in the specified column.

Tipovi spojeva u MySQL-u (Types of joins in MySQL)

Dekartov proizvod - daje sve kombinacije svih redova iz svih spojenih tabela. Imena tabela razdvajaju se zarezima, a odredba where se ne zadaje.

Puni spoj - isto kao prethodni.

Unakrsni spoj - isto kao prethodni. Izmedju imena tabela koje spajate mozete zadati rezervisane reci cross join. 

Unutrasnji spoj - semanticki ekvivalentan zarezu. Mozete zadati rezervisane reci inner join. Ako ne zadate uslov pomocu odredbe where, ovaj tip spoja ekvivalentan je punom spoju. Da bi ste od ovog uslova napravili pravi unutrasnji spoj, zadajte uslov u odredbi where. 

Jednakovredni spoj - uparuje redove iz tabela u spoju pomocu uslovnog  izraza i operatora =. U standardnom SQL-u to je spoj sa odredbom where.

Menjanje tipa podatka i cena motora (Changing the data type and motobikes prices)

Da bi promenila cene koje su automatski bile na 99.99, koje ce nam trebati za primer u sledecem clanku, promenila sam tip podatka i duzinu/vrednosti.

Korak 1 - promena tipa podatka i duzine/vrednosti.

1.  Otvorite phpmyadmin tako sto cete pokrenuti XAMPP. Kliknite na Admin u liniji MySQL-a ili ukucajte u address bar http://localhost/phpmyadmin

2. Izaberite bazu podataka volimmotore. Kliknite na tabelu motori.

3. Kliknite na karticu Structure. 

Grupisanje podataka i grupne funkcije (Grouping data and group functions)

Ako je potrebno da znate koliko redova ispunjava odredjeni uslov ili koja je prosecna vrednost odredjene kolone, npr. prosecna vrednost po porudzbini. Za takve upite u MySQL-u postoje grupne funkcije (en. aggregate functions).

Ako zelite da znate prosecnu vednost porudzbine, mozete upotrebiti sledeci upit.

Primer 1 (ovaj fajl sacuvajte kao grupne-funkcije.sql):

select avg(iznos)
from porudzbine;

1. Pokrenite XAMPP. Kliknite na Admin u liniji MySQL-a ili ukucajte u address bar http://localhost/phpmyadmin/

2. Sa leve strane izaberite bazu podataka volimmotore, a sa desne izberite karticu SQL.
3. Ubacite ovaj kod i kliknite dugme "Go".

Ucitavanje podataka odredjenim redosledom-odredba Order by (Loading data in a certain order-clause Order by)

Odredba order by sortira redove po redosledu vrednosti iz jedne ili vise kolona navedenih u iskazu select.

Primer:

select ime, adresa
from kupci 
order by ime;

Ovaj kod sacuvajte kao orderby.sql.

Objasnjenje:

Ovaj upit vraca imena poredjana po abecednom redu i adrese kupaca.

1.  Otvorite phpmyadmin tako sto cete pokrenuti XAMPP. Kliknite na Admin u liniji MySQL-a ili ukucajte u address bar http://localhost/phpmyadmin

2. Izaberite bazu podataka volimmotore. Kliknite na karticu SQL.
3. Unesite kod iz fajla orderby.sql i kliknite na dugme "Go".

Upotreba drugih imena za tabele-alijasi (The use of other names in the table-aliases)

Privremeno nazvati kolonu drugim imenom su alijasi. Alijase mozete da zadate na pocetku teksta upita i da ih potom koristite u preostalom delu upita. Alijasi su cesto korisni i kao skracena imena. 

Upit iz prethodnog clanka se moze napisati

select k.ime 
from kupci as k, porudzbine as p, stvari_porudzbina as sp, 
motori as m
where k.sifrakupca = p.sifrakupca
and p.brojporudzbine = sp.brojporudzbine
and sp.brojmotora = m.brojmotora

and m.imemotora like '%KTM%';

Ovaj fajl sacuvajte kao alijasi.sql.

Objasnjenje:
U delu upita koji sadrzi deklaracije tabela koje nameravte da koristite, treba da dodate odredbu AS iza imena svake tabele za koju deklarisete alijas. 

Ako zelite da spojite tabelu samu sa sobom upotrebite alijase. Ovakvo spajanje korisno je ako zelite da nadjete sve redove u tabeli koji sadrze istu vrednost. Na primer, ako hocete da nadjete sve kupce koji zive u istom gradu - za istu tabelu (kupci) zadajte dva alijasa.

Primer 1:

select k1.ime, k2.ime, k1.grad
from kupci as k1, kupci as k2
where k1.grad = k2.grad
and k1.ime != k2.ime;

Objasnjenje:

Upit smo sastavili kao da umesto jedne tabele kupci, imamo dve razlicite tabele, c1 i c2, koje spajamo preko kolone grad. Ali nam je bio potreban jos jedan uslov k1.ime != k2.ime, da bismo iskljucili redove u kojima je kupac povezan sam sa sobom.