1 / 47

SQL baze podataka

Sveučilište Josipa Jurja Strossmayera u Osijeku Odjel za matematiku. SQL baze podataka. Stjepan Poljak Josip Mikolašević Zvonimir Ivančević Slobodan Jelić. UVOD U SQL. SQL – “Structured Query Language” - strukturirani jezik za upite

cleta
Télécharger la présentation

SQL baze podataka

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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!!!

More Related