Primjer baze podataka classicmodels u MySQL-u

1. Preuzeti MySQL server http://dev.mysql.com/downloads/mysql/#downloads2. Instalirati MySQL serverhttp://www.wikihow.com/Install-the-MySQL-Database-Server-on-Your-Windows-PC3. Preuzeti s e-nastave2.efpu.hr SQL dump primjera baze classicmodels.zip i raspakirati zip.4. Kopirati classicmodels.sql u mapu: C:\Program Files\MySQL\MySQL Server 5.1\bin>5. U MySQL Command Line Client-u kreirati bazu classicmodels6. Pokrenuti Command Prompt (CMD)7. Promijeniti mapu u: C:\Program Files\MySQL\MySQL Server 5.1\bin>8. Izvršiti naredbu: mysql –u root –p classicmodels < classicmodels.sql

Pokrenuti MySQL Command Line Client te nakon autentikacije utipkati naredbe:

USE classicmodels;SELECT * FROM customers;Ako dobijete rezultat na upit uspješno ste instalirali MySQL server i bazu za vježbanje

1

Upravljanje bazomKreiranje baze:

Za kreiranje baze u MySQL-u, koristi se naredba CREATE DATABASE kako slijedi:

CREATE DATABASE [IF NOT EXISTS] ime_baze;

IF NOT EXISTS je opcija koji onemogućava grešku ako imenovana baza već postoji na serveru. . Ako želimo kreirati bazu classicmodels naredba je sljedeća.

CREATE DATABASE classicmodels;

Prikaz baza na serveruSHOW DATABASE naredba prikazuje sve baze postavljene na serveru. SHOW DATABASE naredba može poslužiti kako bi se vidjelo dali je baza classicmodels uspješno kreirana.

SHOW DATABASES;

On my database server, the output is :

+--------------------+| Database |+--------------------+| information_schema || classicmodels || mysql |+--------------------+8 rows in set (0.00 sec)

Izbor bazeZa izbor baze na kojoj se planira raditi koristi se USE naredba.

USE ime_baze;

Brisanje bazeBaze se briše naredbom DROP. Svi podaci i objekti unutar baze se nepovratno brišu bez postojanja "undo" naredbe.

DROP DATABASE [IF EXISTS] ime_baze;

2

Kreiranje tablicaZa kreiranje tablica koristimo CREATE TABLE naredbu.

CREATE TABLE [IF NOT EXISTS] ime_tablice( lista_kolona ) type=tip_tablice

Ime_tablice je naziv tablice koju želimo kreirati. Nakon toga se definiraju kolone s popratnim infromacijama: ime_kolone tip_podatka(veličina_podatka) [NOT] NULL. I na završetku možemo specificirati način pohrane podataka u tablicama. Ukoliko ne izabermo opciju pohrane, defaultna vrijednost biti će MyISAM. Uz MyISAM se često koristi i InnoDB.

U primjeru baze classicmodels kreiranje tablice employees izgledalo bi ovako:

CREATE TABLE employees ( employeeNumber into(11) NOT NULL, lastName varchar(50) NOT NULL, firstName varchar(50) NOT NULL, extension varchar(10) NOT NULL, email varchar(100) NOT NULL, officeCode varchar(10) NOT NULL, reportsTo int(11) default NULL, jobTitle varchar(50) NOT NULL, PRIMARY KEY (employeeNumber) );

Nakon kreiranja tablice employees naredbom CREATE TABLE definiramo kolone s karakteristikama polja (tip podataka, veličina, NOT NULL, te konačno određujemo primarni ključ employeeNumber. U slučaju da tablicu više od jednog primarnog ključa, oni se mogu odvojiti zarezom. U tablici payments imamo dva primarna ključa customerNumber i checkNumber, Tablicu kreiramo na sljedeći način:

CREATE TABLE payments ( customerNumber int(11) NOT NULL, checkNumber varchar(50) NOT NULL, paymentDate datetime NOT NULL, amount double NOT NULL, PRIMARY KEY (customerNumber,checkNumber) );

3

Prikaz i opis tablica u bazi Kako bi prikayali sve tablice u bazi koristimo SHOW TABLES naredbu. Server će prikazati sve tablice koje postoje u trenutno korištenoj bazi.

SHOW TABLES

Ispis tablica u classicmodels bazi podataka

+-------------------------+| Tables_in_classicmodels |+-------------------------+| customers || employees || offices || orderdetails || orders || payments || productlines || products |+-------------------------+8 rows in set (0.00 sec)

Kako bi dobili opis i karakteristike tablice, koristimo DESCRIBE naredbu kako slijedi:

DESCRIBE employees;

Ispis servera izgleda ovako:

+----------------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------------+--------------+------+-----+---------+-------+| employeeNumber | int(11) | NO | PRI | NULL | || lastName | varchar(50) | NO | | NULL | || firstName | varchar(50) | NO | | NULL | || extension | varchar(10) | NO | | NULL | || email | varchar(100) | NO | | NULL | || officeCode | varchar(10) | NO | | NULL | || reportsTo | int(11) | YES | | NULL | || jobTitle | varchar(50) | NO | | NULL | |+----------------+--------------+------+-----+---------+-------+8 rows in set (0.02 sec)

4

Promjena strukture tablicaUz kreiranje tablica, MySQL dopušta i promjenu struktura tablica naredbom ALTER TABLE.

ALTER [IGNORE] TABLE ime_tablice opcije[, opcije...]opcije su: ADD [COLUMN] create_definition [FIRST | AFTER col_name ] or ADD [COLUMN] (create_definition, create_definition,...) or ADD INDEX [index_name] (index_col_name,...) or ADD PRIMARY KEY (index_col_name,...) or ADD UNIQUE [index_name] (index_col_name,...) or ADD FULLTEXT [index_name] (index_col_name,...) or ADD [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...) [reference_definition] or ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} or CHANGE [COLUMN] old_col_name create_definition [FIRST | AFTER column_name] or MODIFY [COLUMN] create_definition [FIRST | AFTER col_name] or DROP [COLUMN] col_name or DROP PRIMARY KEY or DROP INDEX index_name or DISABLE KEYS or ENABLE KEYS or RENAME [TO] new_table_name or ORDER BY col_name or table_options

• naredbe CHANGE i MODIFY su iste, one mogućavaju promjenu definicije/karekteristike kolona ili njihovih pozicija u tablici.

• Naredba DROP COLUMN će nepovratno obrisati kolonu i sve pripadajuće podatke u tablici.• DROP PRIMARY KEY i DROP INDEX brišu samo primarne ključeve ili indeks kolone.• RENAME naredba omogućava promjenu naziva tablice.

Brisanje tablicaKako bi obrisali tablicu iz baze, koristimo naredbu DROP TABLE:

DROP TABLE [IF EXISTS] ime_tablice [, ime_tablice,...]

MySQL dopušta brisanje više tablica od jednom. Tablice odvajamo zarezom.

Brisanje podataka unutar tablicaU nekim slučajevima želimo obrisati podatke unutar tablica a ne želimo obrisati strukturu tablice. MySQL naredba za tu radnju je TRUNCATE. Primjer:

TRUNCATE TABLE ime_tablice

5

Kreiranje indeksaIndeksi baze omogćavaju ubrzavanje pretrage i dohvat podataka.

Indekse kreiramo prilikom kreiranja tablica. Svaka kolona koja je opisana karakteristikom PRIMARY KEY, KEY, UNIQUE ili INDEX biti će indeksirana.

WHERE klauzulaWHERE klauzula omogućava izbor određenih redaka koji zadovoljavaju upit ili uvjet.

Primjer iz baze: Upit za zaposlenika poduzeća koji je istodobno i predsjednik poduzeća.

SELECT firstname,lastname,email FROM employees WHERE jobtitle="president"

Odgovor MySQL-a:

+-----------+----------+------------------------------+| firstname | lastname | email |+-----------+----------+------------------------------+| Diane | Murphy | dmurphy@classicmodelcars.com |+-----------+----------+------------------------------+1 row in set (0.00 sec)

DISTINCTNaredbom DISTINCT, možemo eliminirati sve rezultate koji se pojavljuju više puta prilikom SELECT naredbe. Kako bi našli sve nazive radnih mjesta u tablici employees, koristimo DISTINCT unutar SELECT naredbe:

SELECT DISTINCT jobTitle FROM employees;

Kao rezultat dobijemo spisak svik naziva radnih mjesta koje zaposlenik može imati.

+----------------------+| jobTitle |+----------------------+| President || VP Sales || VP Marketing || Sales Manager (APAC) || Sale Manager (EMEA) || Sales Manager (NA) || Sales Rep |+----------------------+7 rows in set (0.00 sec)

6

GROUP BYAko želimo saznati koliki broj zaposlenika obnaša istu funkciju, koristimo GROUP BY klauzulu. GROUP BY omogućava dohvat redaka prema grupi.

SELECT count(*), jobTitle FROM employees GROUP BY jobTitle;

And here is the result

+----------+----------------------+| count(*) | jobTitle |+----------+----------------------+| 1 | President || 1 | Sale Manager (EMEA) || 1 | Sales Manager (APAC) || 1 | Sales Manager (NA) || 17 | Sales Rep || 1 | VP Marketing || 1 | VP Sales |+----------+----------------------+7 rows in set (0.02 sec)

HAVING klauzulaHAVING se najčešće koristi s GROUP BY klauzulom kako bi odabrali određenu grupu.

SELECT count(*), jobTitle FROM employees GROUP BY jobTitle HAVING count(*) = 1

Ovaj upit odabire naziv radnog mjesta u poduzeću za koje postoji samo jedan zaposleni.

+----------+----------------------+| count(*) | jobTitle |+----------+----------------------+| 1 | President || 1 | Sale Manager (EMEA) || 1 | Sales Manager (APAC) || 1 | Sales Manager (NA) || 1 | VP Marketing || 1 | VP Sales |+----------+----------------------+6 rows in set (0.00 sec)

7

Sortiranje klauzulom ORDER BYORDER BY omogućava sortiranje rezultata jedne ili više kolona prema rastućem ili padajućem redosljedu. ASC za rastući a DESC za padajući redosljed sortiranja. Kako bi sortirali zaposlenike po abecedi imena i radnog mjesta upisujemo sljedeće naredbe:

SELECT firstname,lastname, jobtitle FROM employees ORDER BY firstname ASC,jobtitle DESC;

+-----------+-----------+----------------------+| firstname | lastname | jobtitle |+-----------+-----------+----------------------+| Andy | Fixter | Sales Rep || Anthony | Bow | Sales Manager (NA) || Barry | Jones | Sales Rep || Diane | Murphy | President || Foon Yue | Tseng | Sales Rep || George | Vanauf | Sales Rep || Gerard | Hernandez | Sales Rep || Gerard | Bondur | Sale Manager (EMEA) || Jeff | Firrelli | VP Marketing || Julie | Firrelli | Sales Rep || Larry | Bott | Sales Rep || Leslie | Jennings | Sales Rep || Leslie | Thompson | Sales Rep || Loui | Bondur | Sales Rep || Mami | Nishi | Sales Rep || Martin | Gerard | Sales Rep || Mary | Patterson | VP Sales || Pamela | Castillo | Sales Rep || Peter | Marsh | Sales Rep || Steve | Patterson | Sales Rep || Tom | King | Sales Rep || William | Patterson | Sales Manager (APAC) || Yoshimi | Kato | Sales Rep |+-----------+-----------+----------------------+23 rows in set (0.00 sec)

8

SQL IN Naredba omogućava selektiranje svih vrijednosti koje zadovoljavaju bilo koji od zadanih vrijednosti. Primjer:

SELECT column_listFROM table_nameWHERE column IN ("list_item1","list_item2"…)

Ako želimo pronaći sve urede koji su smješteni u SAD-u i Francuskoj:

SELECT officeCode, city, phoneFROM officesWHERE country = 'USA' OR country = 'France'

Ukoliko umjesto prethodnog upita želimo koristiti SQL IN:

SELECT officeCode, city, phoneFROM officesWHERE country IN ('USA','France')

Ispis rezultata:

+------------+--------+-----------------+| officeCode | city | phone |+------------+--------+-----------------+| 2 | Boston | +1 215 837 0825 || 3 | NYC | +1 212 555 3000 || 4 | Paris | +33 14 723 5555 || 8 | Boston | +1 215 837 0825 |+------------+--------+-----------------+

Kako bi dobili sve uredu koji nisu smješteni u USA i Francuskoj možemo koristiti NOT IN klauzulu kako slijedi::

SELECT officeCode, city, phoneFROM officesWHERE country NOT IN ('USA','France')

Ispis rezultata:

+------------+--------+------------------+| officeCode | city | phone |+------------+--------+------------------+| 5 | Tokyo | +81 33 224 5000 || 6 | Sydney | +61 2 9264 2451 || 7 | London | +44 20 7877 2041 |+------------+--------+------------------+

Ukoliko želimo pronaći sve narudžbe u tablici orders koje imaju vrijednost veću od 60000, koristimo SQL IN.

Prvo moramo selektirati sve narudžbe (orders) , i detalje o narudžbi (orderDetails)

SELECT orderNumberFROM orderDetailsGROUP BY orderNumberHAVING SUM (quantityOrdered * priceEach) > 60000

9

te:

SELECT orderNumber,customerNumber,status,shippedDateFROM orders WHERE orderNumber IN ( SELECT orderNumber FROM orderDetails GROUP BY orderNumber HAVING SUM(quantityOrdered * priceEach) > 60000

)

rezultat za sve narudžbe s vrijednošću preko 60000:

+-------------+----------------+---------+---------------------+| orderNumber | customerNumber | status | shippedDate |+-------------+----------------+---------+---------------------+| 10165 | 148 | Shipped | 2003-12-26 00:00:00 || 10287 | 298 | Shipped | 2004-09-01 00:00:00 || 10310 | 259 | Shipped | 2004-10-18 00:00:00 |+-------------+----------------+---------+---------------------+

SQL BETWEEN

omogućava dohvat vrijednosti unutar zadanog raspona. Sintaksa SQL BETWEEN-a:

SELECT column_list FROM table_name WHERE column_1 BETWEEN lower_range AND upper_range

Kako bi našli sve proizvode čija je cijena koštanja u rasponu od 90 do 100, upisujemo sljedeći upit:

SELECT productCode,ProductName,buyPrice FROM products WHERE buyPrice BETWEEN 90 AND 100 ORDER BY buyPrice DESC

Rezultat:

+-------------+--------------------------------------+----------+| productCode | ProductName | buyPrice |+-------------+--------------------------------------+----------+| S10_1949 | 1952 Alpine Renault 1300 | 98.58 || S24_3856 | 1956 Porsche 356A Coupe | 98.3 || S12_1108 | 2001 Ferrari Enzo | 95.59 || S12_1099 | 1968 Ford Mustang | 95.34 || S18_1984 | 1995 Honda Civic | 93.89 || S18_4027 | 1970 Triumph Spitfire | 91.92 || S10_4698 | 2003 Harley-Davidson Eagle Drag Bike | 91.02 |+-------------+--------------------------------------+----------+

Rezultat sadrži sve proizvode koji imaju cijenu u rasponu od 90 do 100 uključujući i one koji imaju vrijednosti 90 i 100.

Kako bi pronašli sve proizvode kojima cijene nisu u rasponu od 20 do 100 imamo sljedeći upit:

SELECT productCode,ProductName,buyPrice FROM products WHERE buyPrice NOT BETWEEN 20 AND 100 ORDER BY buyPrice DESC

10

+-------------+-------------------------------------+----------+| productCode | ProductName | buyPrice |+-------------+-------------------------------------+----------+| S10_4962 | 1962 LanciaA Delta 16V | 103.42 || S18_2238 | 1998 Chrysler Plymouth Prowler | 101.51 || S24_2972 | 1982 Lamborghini Diablo | 16.24 || S24_2840 | 1958 Chevy Corvette Limited Edition | 15.91 |+-------------+-------------------------------------+----------+

Ekvivalent gornjem upitu:

SELECT productCode,ProductName,buyPrice FROM products WHERE buyPrice < 20 OR buyPrice > 100 ORDER BY buyPrice DESC

SQL LIKE Omogućava traženje prema poklapanju skupa slova/znakova unutar kolone u tablici. SQL LIKE se najčešće koristi s naredbama SELECT i WHERE.

Ukoliko želimo pretražiti sve zaposlenike u tablici employees kojima ime počinje s slovom "a":

SELECT employeeNumber, lastName, firstName FROM employees WHERE firstName LIKE 'a%'

+----------------+----------+-----------+| employeeNumber | lastName | firstName |+----------------+----------+-----------+| 1611 | Fixter | Andy |+----------------+----------+-----------+1 row in set (0.00 sec)

Ukoliko želimo pretražiti sve zaposlenike kojima prezime završava s "on":

SELECT employeeNumber, lastName, firstName FROM employees WHERE lastName LIKE '%on'

+----------------+-----------+-----------+| employeeNumber | lastName | firstName |+----------------+-----------+-----------+| 1088 | Patterson | William || 1216 | Patterson | Steve |+----------------+-----------+-----------+2 rows in set (0.00 sec)

11

Ukoliko želimo pretražiti sve zaposlenike koji u prezimenu imaju izraz "on":

SELECT employeeNumber, lastName, firstNameFROM employeesWHERE lastName LIKE '%on%'

+----------------+-----------+-----------+| employeeNumber | lastName | firstName |+----------------+-----------+-----------+| 1088 | Patterson | William || 1102 | Bondur | Gerard || 1216 | Patterson | Steve || 1337 | Bondur | Loui || 1504 | Jones | Barry |+----------------+-----------+-----------+5 rows in set (0.00 sec)

SQL LIKE možemo koristiti i s operatorom NOT kako bi saznali sve podatke koji ne sadrže traženi izraz. Kako bi pronašli sve zaposlenike kojima ime ne počinje s "B", upišemo:

SELECT employeeNumber, lastName, firstNameFROM employeesWHERE lastName NOT LIKE 'B%'

+----------------+-----------+-----------+| employeeNumber | lastName | firstName |+----------------+-----------+-----------+| 1088 | Patterson | William || 1188 | Firrelli | Julie || 1216 | Patterson | Steve || 1286 | Tseng | Foon Yue || 1323 | Vanauf | George || 1370 | Hernandez | Gerard || 1401 | Castillo | Pamela || 1504 | Jones | Barry || 1611 | Fixter | Andy || 1612 | Marsh | Peter || 1619 | King | Tom || 1621 | Nishi | Mami || 1625 | Kato | Yoshimi || 1702 | Gerard | Martin |+----------------+-----------+-----------+14 rows in set (0.00 sec)

SQL UNION Omogućava kombiniranje dva ili više seta rezultata SELECT naredbi u jedan set rezultata. you to combine two or more result sets from select statements into a single result set. Korištenje SQL UNION je kako slijedi:

SELECT naredbUNION [DISTINCT | ALL]SELECT naredba UNION [DISTINCT | ALL]

…

12

Kolone svake SELECT naredbe moraju imati isti tip podataka. Zadan način UNION ne prikazuje retke s istim podacima u rezultatu. Ukoliko koristimo UNION ALL, dupli rezultati ostati će u prikazu.

Ukoliko želimo spojiti kupce i zaposlenike u jednu tablicu:

SELECT customerNumber id, contactLastname nameFROM customersUNIONSELECT employeeNumber id,firstname nameFROM employees

Rezultat:

id name ------ --------------- 103 Schmitt 112 King 114 Ferguson 119 Labrune 121 Bergulfsen 124 Nelson 125 Piestrzeniewicz 128 Keitel 129 Murphy 131 Lee

Ukoliko želimo sortirati podatke s ORDER BY, moramo koristiti zagrade za SELECT naredbe, te sortiranje izvršiti nakon zadnje zagrade:

(SELECT customerNumber id,contactLastname nameFROM customers)UNION(SELECT employeeNumber id,firstname nameFROM employees)ORDER BY name,id

13

SQL JoinS prijašnjim naredbama naučili smo kako dohvatiti podatke iz jedne tablice koristeći SELECT naredbe dok će se u nastavku obrađivati naredbe za dohvat podataka iz više tablica.

Kako bi dobili kompletne podatke, moramo koristiti SQL JOIN klauzule u naredbi SELECT. Forma za SQL JOIN u MySQL-u je sljedeća:

SELECT column_list FROM table_1 [INNER | LEFT | RIGHT] table_2 ON conditions_2 [INNER | LEFT | RIGHT] table_3 ON conditions_3 ... WHERE conditions

Nakon SELECT naredbe je lista naziva kolona iz kojih želimo dohvatiti podatke. Te kolone moraju biti u tablicama koje smo selektirali. Nakon slektiranja prve (glavne) kolone, navodimo tablice koje želimo spojiti. Možemo korisiti INNER JOIN, LEFT JOIN ili RIGHT JOIN. Nakon JOIN klauzule navodimo uvjete spajanja. Ukoliko se zadovolje svi uvjeti spajanja, MySQL će izbaciti rezultat upita.

Korištenje INNER JOININNER JOIN se koristi pri dohvatu podataka iz svih tablica baziranih na uvjetima navedenim nakon riječi ON.

U primjeru baze classicmodels imamo tablice employees i offices. Te dvije tablice su povezane kolonom officeCode. Kako bi saznali tko je iz koje države, koristimo INNER JOIN za povezivanje tablica. SQL naredbe:

SELECT employees.firstname, employees.lastname, offices.country, offices.stateFROM employeesINNER JOIN offices ON offices.officeCode = employees.officeCode

Rezultat:

+-----------+-----------+-----------+------------+| firstname | lastname | country | state |+-----------+-----------+-----------+------------+| Diane | Murphy | USA | CA || Mary | Patterson | USA | CA || Jeff | Firrelli | USA | CA || William | Patterson | Australia | NULL || Gerard | Bondur | France | NULL || Anthony | Bow | USA | CA || Leslie | Jennings | USA | CA || Leslie | Thompson | USA | CA || Julie | Firrelli | USA | MA || Steve | Patterson | USA | MA || Foon Yue | Tseng | USA | NY || George | Vanauf | USA | NY || Loui | Bondur | France | NULL || Gerard | Hernandez | France | NULL || Pamela | Castillo | France | NULL || Larry | Bott | UK | NULL |

14

| Barry | Jones | UK | NULL || Andy | Fixter | Australia | NULL || Peter | Marsh | Australia | NULL || Tom | King | Australia | NULL || Mami | Nishi | Japan | Chiyoda-Ku || Yoshimi | Kato | Japan | Chiyoda-Ku || Martin | Gerard | France | NULL |+-----------+-----------+-----------+------------+23 rows in set (0.02 sec)

Ako želimo izlistati samo zaposlenike iz SAD-a:

SELECT e.firstname, e.lastname, stateFROM employees eINNER JOIN offices p ON o.officeCode = e.officeCode AND country = 'USA'

Rezultat:

+-----------+-----------+-------+| firstname | lastname | state |+-----------+-----------+-------+| Diane | Murphy | CA || Mary | Patterson | CA || Jeff | Firrelli | CA || Anthony | Bow | CA || Leslie | Jennings | CA || Leslie | Thompson | CA || Julie | Firrelli | MA || Steve | Patterson | MA || Foon Yue | Tseng | NY || George | Vanauf | NY |+-----------+-----------+-------+10 rows in set (0.00 sec)

Using LEFT and RIGHT JOINLEFT JOIN se može koristiti kada želimo dohvatiti podatke iz glavne tablice, iako nemamo odgovarajuću podudarajuću kolonu u ostalim tablicama. RIGHT JOIN se koristi za dohvat podataka iz svih ostalih tablica osim glavne tablice koja ne mora imati odgovorajuću podudarajuću kolonu.

Kao primjer u classicmodels bazi, kada poduzeće želi otvoriti novi ured, SQL naredbe za ubacivanje novog ureda, izgledala bi ovako:

INSERT INTO classicmodels.offices (officeCode, city, phone, addressLine1, addressLine2, state, country, postalCode, territory )VALUES ('8',

15

'Boston', '+1 215 837 0825', '1550 dummy street', 'dummy address', 'MA', 'USA', '02107', 'NA' )

U vrijeme dok poduzeće još nije zaposlilo radnike, tablica novog ureda je prazna. Ukoliko želimo znati kojem uredu pripada koji zaposlenik, koristiti ćemo RIGH JOIN:

SELECT firstname, lastname, addressLine1 FROM employees e RIGHT JOIN offices o ON o.officeCode = e.officeCode

+-----------+-----------+--------------------------+| firstname | lastname | addressLine1 |+-----------+-----------+--------------------------+| Mary | Patterson | 100 Market Street || Diane | Murphy | 100 Market Street || Jeff | Firrelli | 100 Market Street || Anthony | Bow | 100 Market Street || Leslie | Jennings | 100 Market Street || Leslie | Thompson | 100 Market Street || Julie | Firrelli | 1550 Court Place || Steve | Patterson | 1550 Court Place || Foon Yue | Tseng | 523 East 53rd Street || George | Vanauf | 523 East 53rd Street || Gerard | Bondur | 43 Rue Jouffroy D'abbans || Loui | Bondur | 43 Rue Jouffroy D'abbans || Gerard | Hernandez | 43 Rue Jouffroy D'abbans || Pamela | Castillo | 43 Rue Jouffroy D'abbans || Martin | Gerard | 43 Rue Jouffroy D'abbans || Mami | Nishi | 4-1 Kioicho || Yoshimi | Kato | 4-1 Kioicho || William | Patterson | 5-11 Wentworth Avenue || Andy | Fixter | 5-11 Wentworth Avenue || Peter | Marsh | 5-11 Wentworth Avenue || Tom | King | 5-11 Wentworth Avenue || Larry | Bott | 25 Old Broad Street || Barry | Jones | 25 Old Broad Street || NULL | NULL | 1550 dummy street |+-----------+-----------+--------------------------+24 rows in set (0.00 sec)

RIGHT JOIN povlači sve podatke iz druge tablice (offices) te podatke iz prve tablice, čak kada i uvjeti ne poklapaju.

16

Spajanje unutar tablice JOIN klauzula se može koristiti tako da se tablica spoji sama sa sobom. U situaciji gdje bi htjeli saznati tko kome odgovara u organizacijskoj strukturi, koristimo sljedeće naredbe:

SELECT concat(e.firstname,',',e.lastname) AS employee, concat(m.firstname,',',m.lastname) AS manager FROM employees AS m INNER JOIN employees AS e ON m.employeeNumber = e.reportsTo ORDER BY employee

+------------------+-------------------+| employee | manager |+------------------+-------------------+| Andy,Fixter | William,Patterson || Barry,Jones | Gerard,Bondur || Foon Yue,Tseng | Anthony,Bow || George,Vanauf | Anthony,Bow || Gerard,Hernandez | Gerard,Bondur || Jeff,Firrelli | Diane,Murphy || Julie,Firrelli | Anthony,Bow || Larry,Bott | Gerard,Bondur || Leslie,Jennings | Anthony,Bow || Leslie,Thompson | Anthony,Bow || Loui,Bondur | Gerard,Bondur || Martin,Gerard | Gerard,Bondur || Mary,Patterson | Diane,Murphy || Pamela,Castillo | Gerard,Bondur || Peter,Marsh | William,Patterson || Steve,Patterson | Anthony,Bow || Tom,King | William,Patterson || Yoshimi,Kato | Mami,Nishi |+------------------+-------------------+

SUM FunkcijaSUM funkcija daje razultat sume svih vrijednosti unutar izraza.

Primjer na tablici: OrderDetails

Kako bi dobili ukupnu vrijednost u novcu za svaki proizvod koji je na prodaju, koristimo SUM te grupiramo podatke po proizvodu:

SELECT productCode,sum(priceEach * quantityOrdered) totalFROM orderdetailsGROUP by productCode

productCode total ----------- ---------S10_1678 90157.77S10_1949 190017.96S10_2016 109998.82S10_4698 170686S10_4757 127924.32

17

S10_4962 123123.01S12_1099 161531.48

Kako bi rezultat dao više detalja o proizvodu, moramo spojiti tablicu OrderDetails s tablicom Product.

SELECT P.productCode, P.productName, SUM(priceEach * quantityOrdered) totalFROM orderdetails OINNER JOIN products P ON O.productCode = P.productCodeGROUP by productCodeORDER BY total

productCode productName total ----------- ------------------------------------------- ---------S24_1937 1939 Chevrolet Deluxe Coupe 28052.94S24_3969 1936 Mercedes Benz 500k Roadster 29763.39S24_2972 1982 Lamborghini Diablo 30972.87S24_2840 1958 Chevy Corvette Limited Edition 31627.96S32_2206 1982 Ducati 996 R 33268.76S24_2022 1938 Cadillac V-16 Presidential Limousine 38449.09S50_1341 1930 Buick Marquette Phaeton 41599.24S24_1628 1966 Shelby Cobra 427 S/C 42015.54S72_1253 Boeing X-32A JSF 42692.53

AVG FunkcijaAVG se koristi za izračun prosječne vrijednosti izraza (AVG ignorira NULL vrijednosti)

AVG(expression)

AVG funkciju možemo iskoristiti kako bi izračunali prosječnu cijenu svih proizvoda.

SELECT AVG(buyPrice) average_buy_priceFROM Products

rezultat:

average_buy_price----------------- 54.395181818182

18

MAX i MIN FunkcijaMAX funkcija izbacuje najveću vrijednost dok funkcija MIN izbacuje najmanju vrijednost u skupu.

MAX(expression)

MIN(expression)

MIN i MAX funkcija koja izbacuje rezultat najviše i najniže cijene proizvoda:

SELECT MAX(buyPrice) highest_price, MIN(buyPrice) lowest_priceFROM Products

Rezultat:

highest_price lowest_price------------- ------------ 103.42 15.91

COUNT FunkcijaCOUNT funkcija za rezultat dalje broj stavki iz izraza. COUNT funkciju možemo koristiti kada želimo saznati koliko proizvoda imamo u tablici:

SELECT COUNT(*) AS TotalFROM products

Total ------ 110

19

INSERT naredba INSERT naredba omogućava dodavanje/ubacivanje jednog ili više retka u tablicu. U MySQL-u INSERT naredba se koristi kako slijedi:

INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] table_name [(column_name,...)] VALUES ((expression | DEFAULT),...),(...),... [ ON DUPLICATE KEY UPDATE column_name=expression, ... ]

INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] table_name [(column_name,...)] SELECT statement

INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] table_name SET column_name=(expression | DEFAULT), ... [ ON DUPLICATE KEY UPDATE column_name=expression, ... ]

U prvom izrazu ubacuje se novi podatak u redak, u već postojeću tablicu, sa specifikacijom naziva kolone te podatak koji pripada polju. Primjer dodavanja novog ureda u tablicu offices:

INSERT INTO classicmodels.offices (officeCode, city, phone, addressLine1, addressLine2, state, country, postalCode, territory ) VALUES ('8', 'Boston', '+1 215 837 0825', '1550 dummy street', 'dummy address', 'MA', 'USA', '02107', 'NA' )

U drugom izrazu, umjesto ubacivanja konkretnih podataka, dio podataka se uzima SELECT naredbom. Kao primjer, možemo stvoriti novu privremenu tablicu koja sadrži sve urede u SAD-u.

INSERT INTO temp_table SELECT * FROM offices WHERE country = 'US'

U trećem izrazu, omogućeno je da se u određenu kolonu ubaci novi podatak:

INSERT INTO productlines SET productLine = 'Luxury Cars'

Što znači da ubacujemo samo podataka u kolonu productLine u tablici productLines.

20

SQL UPDATENaredbu koristimo za ažuriranje postojećih podataka u tablici. Mogu se mijenjati podaci pojedinog retka, grupe redaka pa čak i svih redaka u tablici.

UPDATE table_name [, table_name...] SET column_name1=expr1 [, column_name2=expr2 ...] [WHERE condition]

SET klauzulu određuje kolona/kolone i promijenjene vrijednosti. Promijenjene vrijednosti mogu biti konstante, izrazi ili upit.

U tablici employees, ukoliko želimo ažurirati email adresu Mary Patterson s employeeNumber 1 novom email adresom mary-patterso@classicmodelcars.com, koristimo sljedeće naredbe:

Prije ažuriranja:

SELECT firstname, lastname, email FROM employees WHERE employeeNumber = 1

+-----------+-----------+--------------------------------+| lastname | firstname | email |+-----------+-----------+--------------------------------+| Patterson | Mary | mpatterso@classicmodelcars.com |+-----------+-----------+--------------------------------+1 row in set (0.02 sec)

Ažuriranje emaila mary-patterso@classicmodelcars.com

UPDATE employees SET email = 'mary-patterso@classicmodelcars.com' WHERE employeeNumber = 1

Ukoliko ponovno pogledamo podatak o email adresama za Mary:

+-----------+-----------+------------------------------------+| lastname | firstname | email |+-----------+-----------+------------------------------------+| Patterson | Mary | mary-patterso@classicmodelcars.com |+-----------+-----------+------------------------------------+1 row in set (0.00 sec)

21

SQL DELETEomogućava brisanje jednog ili više redaka: statement in MySQL is as follows:

DELETE FROM table_name [WHERE conditions] [ORDER BY ...] [LIMIT rows]

DELETE table_name[.*] [, table_name[.*] ...] FROM table-references [WHERE where_definition]

DELETE FROM table_name[.*] [, table_name[.*] ...] USING table-references [WHERE where_definition]

U prvom primjeru DELETE naredbe, FROM je tablica u kojoj želimo obrisati podatke, dok WHERE klauzula određuje koji redak brišemo. Ukoliko nema WHERE klauzule, brišu se svi podaci u tablici.

U drugom primjeru, DELETE naredba omogućava brisanje redaka iz više tablica.

Treći primjer naredbe

The third form of DELETE statement is quite similar to the second form except that instead of FROM keyword it uses USING keyword.

Ukoliko želimo obrisati sve zaposlenike u uredu s brojem 4:

DELETE FROM employees WHERE officeCode = 4

Kako bi obrisali sve zaposlenike, ne navodimo WHERE klauzulu:

DELETE FROM employees

22