SQL baze podataka

1. Sveučilište Josipa Jurja Strossmayera u Osijeku Odjel za matematiku SQL baze podataka Stjepan Poljak Josip Mikolašević Zvonimir Ivančević Slobodan Jelić

2. UVOD U SQL SQL – “Structured Query Language” - strukturirani jezik za upite povjest SQL-a počinje 1970. godine kada je razvijen u IBM-ovom istraživačkom laboratoriju u San Joseu-u, California. slaba razvijenost do 80-tih godina. 1981. godine SQL/DS a zatim se pojavljuju sustavi baza podataka Oracle i Reational Technology. do 1989. godine 70-ak različitih verzija SQL sustava 1989. godine proširenje standarda pa nastaje SQL-89 – uvođenje referencijskog integriteta. 1992. godine SQL-2 ili SQL-92 – proširenje standarda u pisanoj formi ( broj stranica ) više od 4 puta nego u ranijoj verziji. 1999. godine objavljen SQL-3 ili SQL-99 s novim mogućnostima

3. Sam SQL opisuje što želimo dobiti kao rezultat, a ne kako doći do toga i to ga svrstava u neproceduralne programske jezike za razliku od npr. C programskog jezika. • SQL je stvoren za rad sa relacijskim bazama podataka za koje dr. Codd 1970. godine iznosi 12 Coddovih pravila ( objavljenih u članku “A Relational of Data for Large Shared Data Banks” ). • SQL omogučava da tvorimo i promjenimo strukturu baze podataka, dodamo prava korisniku za pristup bazama podataka ili tablicama, da tražimo informacije od baze podataka i da mjenjamo sadržaj baze podataka. Za to imamo dvije vrste funkcija. - DDL ( Data Definition Language ) funkcija za definiciju podataka čiji je tipičan primjer naredba CREATE TABLE imeTablice(); - DML ( Data Manipulation Table ) funkcija za upravljanje podacima gdje se kao primjer može navesti osnovna SQL naredba SELECT*FROM imeTablice

4. Pristup podacima odvija se prema modelu klijent/poslužitelj To je po Bernardu H. Boar autoru knjige “Implementing Client/server Computing”, definirano kao: “ Model rada u kojem je jedna aplikacija podjeljena između više procesa koji komuniciraju (transparentno prema korisničkom kraju) da bi završili procesiranje kao jedan jedinstveni zadatak. Klijent/poslužitelj model vezuje procese da bi se dobila slika jedinstvenog sustava. Djeljivi resursi su pozicionirani klijenti koji imaju zahtjeve i mogu pristupiti ovlaštenim servisima. Arhitektura je beskonačno rekurzivna; pa poslužitelji mogu postati klijenti i zahtijevati usluge od drugih poslužitelja u mreži, itd.”.

5. TABLICE • Tablice predstavljaju dvodimenzionalne matrice čiji redovi predstavljaju naziv i svojstvo objekata pohranjenih u tablicu, a stupci svojstva objekata izražena odgovarajučim tipom podataka. Uz pomoć jedne n-torke opisali smo jedan objekt. npr:

6. Kako u SQL-u kreirati tablicu????????? CREATE TABLE osoba ( maticni broj NVARCHAR(15), ime NVARCHAR(15) NOT NULL, prezime NVARCHAR(15) NOT NULL, ulica NVARCHAR(25), mjesto NVARCHAR(15) DEFAULT ‘Zagreb’ PRIMARY KEY (maticni broj) );

7. PRIVREMENE TABLICE • Privremene tablice su posebna vrsta tablica. One postoje samo za vrijeme dok smo prijavljeni na server. • Koristimo ih za pohranjivanje rezultata nekakvih kompliciranih izraza a te rezultate mislimo koristiti u kasnijim izrazima upita ili kad je potrebno nešto napraviti u više odvojenih koraka. Tipičan primjer je kreiranje tablice iste kao neka postojeća u našem slučaju tablica osoba. Sada bi na toj novoj tablici mogli objavljivati različite upite, brisanja, računanja i na samome kraju to usporediti s nečime drugim.

8. CREATE TABLE #privremenaosoba ( maticni broj NVARCHAR(15), ime NVARCHAR(15) NOT NULL, prezime NVARCHAR(15) NOT NULL, ulica NVARCHAR(25), mjesto NVARCHAR(15) DEFAULT ‘Zagreb’ PRIMARY KEY (maticni broj) ); Primjer kreiranja privremene tablice!!!!!;)

9. Naredbe SQL jezika • SQL DDL • (engl. Data Definition Language) • Definicija objekata u bazi podataka • CREATE - kreiranje objekata baze • DROP - uklanjanje objekata baze • ALTER - izmjena definicije objekata baze • GRANT - definiranje prava pristupa podacima • REVOKE - uklanjanje definicije prava pristupa podacima

10. CREATE TABLE • Definiranje nove relacije, odnosno opis njene relacijske sheme (tablice) • U proširenoj sintaksi moguće je definirati ograničenja (CONSTRAINT) • PRIMARY KEY (primarni ključ tablice) • UNIQUE (jedinstveni ključ tablice) • FOREIGN KEY (strani ključ tablice, referencijalni integritet) • definira se atribut (ili skup atributa) promatrane tablice koji se referenciraju na primarni ključ iste ili neke druge tablice

11. Primjer: CREATE TABLE grad( pbr SMALLINT, naziv VARCHAR(50), CONSTRAINT grad_pk PRIMARY KEY(pbr) ); CREATE TABLE stanovnici( jmbg INT, "ime osobe" VARCHAR(30) NOT NULL, "prezime osobe" VARCHAR(30) NOT NULL, pbr SMALLINT, adresa VARCHAR(100) NOT NULL, CONSTRAINT stanovnici_pk PRIMARY KEY(jmbg), CONSTRAINT stanovnici_fk_grad FOREIGN KEY(pbr) REFERENCES grad(pbr) );

12. grad stanovnici

13. DROP TABLE • Uklanjanje (brisanje) relacije iz baze podataka • Za razliku od DELETE koja izbacuje samo n-torke iz relacije, ova naredba izbacuje i definiciju relacije pa relacija i njena relacijska shema više ne postoji • Sintaksa: DROP TABLE table_name • Primjer: DROP TABLE osobe

14. ALTER TABLE • Izmjena definicije postojeće relacije • Dodavanje atributa: ALTER TABLE stanovnici ADD dat_rod DATETIME; • Uklanjanje atributa:ALTER TABLE radno_mjesto DROP COLUMN broj_zaposlenih; • Izmjena postojećih atributa: ALTER TABLE racuni ALTER COLUMN nacin_placanja CHAR(1);

15. GRANT • Vlasnik relacije je uvijek korisnik koji ju je definirao naredbom CREATE TABLE, a pravo na izvršavaje SQL naredbi i kreiranje objekata vlasnik na druge osobe prenosi naredbom GRANT Za naredbe DDL-a primjeri:GRANT CREATE TABLE, CREATE VIEW TO korisnik;GRANT CREATE PROCEDURE TO korisnik; Za naredbe DML-a primjeri:GRANT SELECT ON student TO stuslu;GRANT SELECT(ime_stud, prez_stud) ON student TO korisnik;GRANT DELETE ON mjesto TO korisnik;

16. REVOKE • Oduzimanje prava korisnicima na izvršavanje SQL naredbi i/ili kreiranje objekata (suprotno od naredbe GRANT) • Sintaksa za naredbeREVOKE { ALL | statement [ ,...n ] } FROM security_account [ ,...n ] • Sintaksa za objekteREVOKE [ GRANT OPTION FOR ] { ALL [ PRIVILEGES ] | permission [ ,...n ] } { [ ( column [ ,...n ] ) ] ON { table | view } | ON { table | view } [ ( column [ ,...n ] ) ] | ON { stored_procedure | extended_procedure } | ON { user_defined_function } } { TO | FROM } security_account [ ,...n ] [ CASCADE ] [ AS { group | role } ]

17. Data Manipulation Language • Data Manipulation Language ili DML je podskup jezika koristen za dodavanje, brisanje ili mijenjanje podataka • naredbe: SELECT, UPDATE, INSERT, DELETE, MERGE

18. SELECT • vraca skup rezultata iz zapisa jedne ili vise tablica • koristi se za dobijanje nijednog ili vise redaka iz jedne ili vise glavnih tablica, privremenih tablica ili pogleda iz baze podataka • pri SELECT upitu korisnik definira opis zeljenog skupa rezultata, ali ne definira koje ce fizicke operacije biti obavljene za dobivanje tog rezultata

19. naredbe koje se koriste uz SELECT: • WHERE – definira redove koji se traze • GROUP BY – koristi se za kombiniranje redova sa srodnim vrijednostima u elemente sa manjim skupom redaka • HAVING – definira koji se redovi (traze) uz GROUP BY • ORDER BY – definira koji se stupci koriste za sortiranje rezultata

20. UPDATE • koristi se za promjenu vrijednosti u postojecem redu ili redovima • neki nacini koristenja: UPDATE tablica SET stupac=‘vrijednost’ UPDATE tablica SET stupac=‘vrijednost’ WHERE neki_uvjet • Zadnji primjer pokazuje mijenjanje vrijednosti uz neki uvjet, dakle mozemo staviti npr. WHERE stupac=‘’ OR stupac IS NULL • tada UPDATE promijeni vrijednosti samo onih redova koji imaju taj stupac prazan

21. DELETE • koristi se za brisanje podataka • neki nacini koristenja: DELETE FROM tablica DELETE FROM tablica WHERE stupac=‘vrijednost’ • prvi primjer je za brisanje redova u tablici (ne smije biti vezana za neki osnovni/strani kljuc) • drugi primjer je za brisanje redova uz uvjet da im je stupac jednak ‘vrijednost’

22. INSERT • koristi se za unosenje podataka u tablicu, red po red; vrijednosti koje se unose moraju biti istog tipa podataka kao polja u koja ih unosimo i moraju odgovarati velicini kolone • neki nacini koristenja: INSERT INTO tablica VALUES (‘vrijednost 1’, ... , ‘vrijednost n’) INSERT INTO tablica (stupac_1, ... , stupac_n) VALUES (‘vrijednost 1’, ... , ‘vrijednost n’)

23. oba primjera predstavljaju isti kod samo napisan na drugi nacin: za unosenje vrijednosti (od 1 do n) u stupce (od 1 do n) • moguce je unositi podatke i koristeci kombinaciju naredbi INSERT i SELECT INSERT INTO tablica SELECT stupac_1, ... ,stupac_n • primjer pokazuje kako u tablici dodati podatke iz neke druge tablice

24. MERGE • koristi se za kombinaciju podataka iz vise tablica • vrsta kombinacije INSERT i UPDATE elemenata • definirana po SQL:2003 standardima, iako neke baze podataka pruzaju slicnu funkcionalnost preko drugacije sintakse, koja se nekad zove “upsert” • sintaksa koristenja naredbe MERGE: MERGE INTO tablica USING tablica ON (uvjet)

25. INDEKSI • Ako ne postoji nikakav indeks – SQL server pristupa i skladišti podatke za duže vremensko razdoblje nego kada postoje indeksi (skup podataka bez indekasa naziva se hrpa). • Kod pristupa tako spremljenim podacima, SQL server mora sekvencijalno pretraživati tablicu. • Tablici se mogu pridružiti dva tipa indeksa i to grupirajući i negrupirajući. • Grupirajući indeksi mjenjaju fizički raspored podataka u bazi pomoću kojega se ubrzava pristup podacima. • Negrupirajući indeksi. Ovi indeksi ne mjenjaju fizički raspored podataka u bazi, već održavaju pokazače ka samim podacima. • Postoje i dvije metode pravljenja indeksa. Prvi je grafički pomoću Eneterprise Managera a drugi je korištenje čarobnjaka index Tuning.

26. Formiranje indeksa po atributu IME_PREZIME relacije trgovac CREATE INDEX IME_INDEX ON TRGOVAC(IME_PREZIME); • Formiranje jedinstvenog indeksa po atributu MB relacije CREATE UNIQUE INDEX MB_INDEX ON TRGOVAC (MB); • Naredba DROP INDEX služi za izbacivanje postojećeg indexa i baze podataka i njegove definicije iz kataloga podataka. DROP INDEX IME_INDEX; ( izbacuje se postojeći indeks IME_INDEKS relacije TRGOVAC)

27. UPITI (Queries) • jedna od najmoćnijih osobina SQL baza podataka • povezivanje tabela • dohvaćanje podataka po zadanim kriterijima • komande koje rade sljedeće: • kreiranje i uklanjanje tabela • dodavanje, mijenjanje ili uklanjanje redaka i polja • pretraživanje više tabela radi pronalaženja određenih informacija • mijenjanje informacija o zaštiti Primjer 1. SELECT U.Ime, U.Prezime, U.JMBG, U.[Datum sklapanja radnog ugovora], U.[Mjesečna plaća] FROM Uposlenik AS U WHERE [Mjesečna plaća]>=1000;

28. Bezuvjetno spajanje tabela • cilj SQL upita:manipuliranje podacima iz više tabela • BEZUVJETNO SPAJANJE TABELA = Kartezijev produkt redaka iz svih tabela koje želimo spojiti SELECT * FROM Tabela1, Tabela2;

29. = Kartezijev produkt skupova R i T

30. Spajanje tabela po uvjetu jednakosti • bezuvjetno spajanje rijetko se koristi • uglavnom je zadan uvjet po kojemu se tabele spajaju • jedan od uvjeta je i JEDNAKOST Primjer 2. SELECT Lijek.[Bar-kod], Lijek.Naziv, Proizvođač.Naziv FROM Lijek, Proizvođač WHERE Lijek.Proizvođač=Proizvođač.Šifra; Lijek.Proizvođač = Proizvođač.Šifra UVJET JEDNAKOSTI LIJEK - PROIZVOĐAČ

31. Unutarnji spojevi (INNER JOINS) • služe za učitavanje zapisa iz više tabela da bi dao jedan skup zapisa • naredba JOIN – spaja tabele na osnovu zajedničkog stupca i daje zapise čije se vrijednosti poklapaju u spojenim tabelama Primjer 3. SELECT Lijek.[Bar-kod], Lijek.Naziv, Proizvođač.Naziv FROM Proizvođač INNER JOIN Lijek ON Proizvođač.Šifra=Lijek.Proizvođač; • proizvođačima u tabeli Proizvođač pridružuju se lijekovi koje oni proizvode a nalaze se u tabeli Lijek • proizvođači koji nisu proizveli niti jedan lijek u tabeli Lijek ne nalaze se u rezultatu

32. Spoljašnji spojevi (OUTER JOINS) • postoje tri vrste spoljašnjih spojeva: • DESNI SPOLJAŠNJI SPOJ • LIJEVI SPOLJAŠNJI SPOJ • POTPUNI SPOLJAŠNJI SPOJ Desni spoljašnji spoj (RIGHT OUTER JOIN ili RIGHT JOIN) • uvijek veže zapise iz tabele s desne strane odredbe JOIN sa zapisima iz tabele s lijeve strane Primjer 4. SELECT [Bar-kod], Lijek.Naziv, Proizvođač.Naziv FROM Proizvođač RIGHTJOIN Lijek ON Proizvođač.Šifra=Lijek.Proizvođač;

33. Lijevi spoljašnji spoj (LEFT OUTER JOIN ili LEFT JOIN) • uvijek veže zapise iz tabele s lijeve strane odredbe JOIN sa zapisima iz tabele s desne strane Primjer 5. SELECT [Bar-kod], Lijek.Naziv, Proizvođač.Naziv FROM Proizvođač LEFT JOIN Lijek ON Proizvođač.Šifra=Lijek.Proizvođač; Potpuni spoljašnji spoj (FULL OUTER JOIN ili OUTER JOIN) • koristimo ga kada želimo vidjeti sve zapise iz obje tabele

34. Spajanje više tabela • Zadatak: Ispisati sve lijekove zajedno sa njihovim farmaceutskim oblicima • PROBLEM: umjesto naziva - nalazi se šifra

35. Rješenje: Spojiti tabele Lijek, Farmaceutski oblik, Kratica – Farmaceutski oblik

36. Primjer 6. SELECT Lijek.[Bar-kod], Lijek.Naziv, [Kratica - Farmaceutski oblik].[Šifrirani pojam] FROM ([Kratica - Farmaceutski oblik] INNER JOIN [Farmaceutski oblik] ON [Kratica - Farmaceutski oblik].Šifra=[Farmaceutski oblik].Naziv) INNER JOIN Lijek ON [Farmaceutski oblik].Šifra=Lijek.[Farmaceutski oblik];

37. Pogledi • Postoje samo kao definicije upita nad jednom ili više tablica • ne čuvaju podatke u sebi (prividne relacije bez vlastitih podataka) • Omogućuju • prilagodbu logičkog modela podataka specifičnim potrebama korisnika • provođenje zaštite protiv neovlaštenog pristupa podacima • Izvršavaju se u trenutku upita pomoću naredbe CREATE VIEW • Pogled se briše naredbom DROP VIEW

38. CREATE VIEW • Kreiranje pogleda • Sintaksa:CREATE VIEW [< owner > . ] view_name [ (column [ ,...n ] ) ] AS select_statement • Primjer:CREATE VIEW muski_studenti AS SELECT mbr_stud, ime_stud, prez_stud FROM student WHERE spol = 'M';

39. Procedure • procedura je upit koji se cuva u bazi podataka • pisanje procedure se zapocinje sa CREATE PROCEDURE ime_procedure AS programski_kod • pozivanje procedure se obavlja sa EXEC ime_procedure • moze biti bez ili sa ulaznim parametrima

40. prednosti: • smanjuje promet kroz mrezu (sacuvane su na posluzitelju) • promjene se stoga obavljaju samo na jednom mjestu

41. USKLADIŠTENE PROCEDURE (eng. Stored Procedures) • Upit koji se čuva u SQLServer-ovoj bazi podataka, nije ugrađena u osnovne komponente aplikacija na klijentskim računalima • Osnovni cilj: • Povećanje brzine prijenosa podataka unutar mreže • Prevođenje upita – povezivanje tabela SELECT Lijek.[Bar-kod], Lijek.Naziv, Lijek.Cijena FROM Lijek INNER JOIN ([Farmakoterapijska skupina] INNER JOIN [Specifikacija FSL] ON [Farmakoterapijska skupina].Šifra=[Specifikacija FSL].Šifra) ON Lijek.Kod=[Specifikacija FSL].Kod WHERE ((([Farmakoterapijska skupina].Naziv)="Analgetik")) ORDER BY Lijek.Naziv; Problem: učestalo slanje upita serveru od strane klijenta = zagušenje mreže, gubljenje dijelova upita, ponovno slanje

42. Riješenje: USKLADIŠTENE PROCEDURE • Upit velikog prometa pohraniti na server kao uskladištenu proceduru • SQL Server-u proslijediti samo naziv uskladištene procedure Sintaksa: EXEC ime_uskladištene_procedure

43. Uskladištene procedure bez ulaznih parametara • nema prosljeđivanja parametara • zahtjeva dodatnu sintaksu za definiranje uskladištene procedure CREATE PROCEDUREDBO.Analgetici AS SELECT Lijek.[Bar-kod], Lijek.Naziv, Lijek.Cijena FROM Lijek INNER JOIN ([Farmakoterapijska skupina] INNER JOIN [Specifikacija FSL] ON [Farmakoterapijska skupina].Šifra=[Specifikacija FSL].Šifra) ON Lijek.Kod=[Specifikacija FSL].Kod WHERE ((([Farmakoterapijska skupina].Naziv)="Analgetik")) ORDER BY Lijek.Naziv; • poziv procedure (implementacija: MS SQL Server 2000) USE Ljekarna EXEC Analgetici

44. Uskladištene procedure sa ulaznim parametrima • Problem: Što ako korisnik želi spisak lijekova iz neke druge farmakoterapijske skupine (a ne analgetike kao u prethodnom primjeru)? • Rješenje:Uskladištena procedura sa ulaznim parametrima • potrebno je definirati ulaznu varijablu CREATE PROCEDUREDBO.SkupinaL @skupina varchar(30) AS SELECT Lijek.[Bar-kod], Lijek.Naziv, Lijek.Cijena FROM Lijek INNER JOIN ([Farmakoterapijska skupina] INNER JOIN [Specifikacija FSL] ON [Farmakoterapijska skupina].Šifra=[Specifikacija FSL].Šifra) ON Lijek.Kod=[Specifikacija FSL].Kod WHERE ((([Farmakoterapijska skupina].Naziv)=@skupina)) ORDER BY Lijek.Naziv; @skupina @varchar(30) = ‘Analgetik’ • poziv procedure (implementacija: MS SQL Server 2000) USE Ljekarna EXEC SkupinaL ‘Antipiretik’

45. OKIDAČI (engl. Triggers) • uskladištena procedura koja se ne poziva naredbom EXEC nego se automatski aktivira prilikom izvršavanja određenih akcija od strane korisnika • Primjer: Unos roka valjanosti lijeka u bazu podataka – rok valjanosti ne smije biti duži od 60 mjeseci – okidači su “čuvari” referencijalnog integriteta baze podataka • 3 tipa okidača koji se često koriste: • INSERT • DELETE • UPDATE Okidači tipa INSERT • promjena sadržaja unutar baze podataka • sprečavanje umetanja novog zapisa • lančano ažuriranje tabela u bazi podataka – konzistencija baze

46. Princip rada okidača: • kada korisnik pokušava unijeti zapis, SQL Server kopira taj zapis u dvije tabele: • tabelu okidača (engl. Trigger Table) • specijalnu tabelu inserted INSERT Lijek VALUES (12, “3838989512453”, ”Haldol”, 8, 7, 60, 1, 1, 54.88) 12 383898…. ……………… 54.88 12 383898…. ……………… 54.88 • Okidač tipa INSERT na primjeru baze podataka Ljekarna • aktivira se promjenom cijene lijeka • nakon učitavanja cijene, SQL Server pohranjuje podatke u gore navedene tabele • aktivira se okidač INSERT i koristeći podatke iz tabele inserted mijenja sadržaj stupca Cijena u tabeli Lijek

47. CREATE TRIGGERUnosNoveCijeneON[Lijek] FOR INSERT AS UPDATELijek SETLijek.Cijena = inserted.Cijena FROMLijekJOINinserted ON Lijek.Kod=inserted.Kod; • Sintaksa za kreiranje okidača INSERT (Implementacija SQL Server 2000) Korištenjem naredbe INSERT na tabeli Lijek, aktivirat će se okidač UnosNoveCijene!!!