Transcript SQL baze podataka - Odjel za matematiku

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
- 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
-
-
-
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
- 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.”.
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:
MATIČNI BROJ
IME
PREZIME
ULICA
MJESTO
0102968383911
Pero
Perić
Gajeva 3
Zagreb
0302982383818
Ivan
Ivić
0305972383915
Marko
Marić
Požega
Divaltova 67
Osijek
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)
);
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.
Primjer kreiranja privremene tablice!!!!!;)
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)
);
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
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
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)
);
grad
pbr
naziv
stanovnici
jmbg
ime
osobe
prezime
osobe
pbr
adresa
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
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);
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;
REVOKE
• Oduzimanje prava korisnicima na izvršavanje
SQL naredbi i/ili kreiranje objekata (suprotno od
naredbe GRANT)
• Sintaksa za naredbe
REVOKE { ALL | statement [ ,...n ] }
FROM security_account [ ,...n ]
• Sintaksa za objekte
REVOKE [ 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 } ]
Data Manipulation Language
• Data Manipulation Language ili DML je
podskup jezika koristen za dodavanje,
brisanje ili mijenjanje podataka
• naredbe: SELECT, UPDATE, INSERT,
DELETE, MERGE
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
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
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
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’
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’)
• 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
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)
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.
- 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)
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;
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;
Redak
Oznaka
Redak
Oznaka
redak1
tabela1
redak1
tabela2
redak2
tabela1
redak2
tabela2
redak3
tabela1
redak3
tabela2
Redak
Oznaka
Redak
Oznaka
redak1
tabela1
redak1
tabela2
redak1
tabela1
redak2
tabela2
redak1
tabela1
redak3
tabela2
redak2
tabela1
redak1
tabela2
redak2
tabela1
redak2
tabela2
redak2
tabela1
redak3
tabela2
redak3
tabela1
redak1
tabela2
redak3
tabela1
redak2
tabela2
redak3
tabela1
redak3
tabela2
R  r1 , r2 , r3 , T  t1 , t2 , t3 
R  T  r1 , t1 , r1 , t2 , r1 , t3 , r2 , t1 , r2 , t2 , r2 , t3 , r3 , t1 , r3 , t2 , r3 , t3 
R T
= Kartezijev produkt skupova R i T
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;
UVJET JEDNAKOSTI
Lijek.Proizvođač = Proizvođač.Šifra
LIJEK - PROIZVOĐAČ
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
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č
RIGHT JOIN Lijek
ON Proizvođač.Šifra=Lijek.Proizvođač;
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
Spajanje više tabela


Zadatak: Ispisati sve lijekove zajedno sa njihovim farmaceutskim
oblicima
PROBLEM: umjesto naziva - nalazi se šifra

Rješenje: Spojiti tabele Lijek, Farmaceutski oblik, Kratica –
Farmaceutski oblik
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];
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
CREATE VIEW
• Kreiranje pogleda
• Sintaksa:
CREATE VIEW [< owner > . ] view_name [ (
column [ ,...n ] ) ]
AS
select_statement
• Primjer:
CREATE
SELECT
FROM
WHERE
VIEW muski_studenti AS
mbr_stud, ime_stud, prez_stud
student
spol = 'M';
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
prednosti:
• smanjuje promet kroz mrezu (sacuvane su
na posluzitelju)
• promjene se stoga obavljaju samo na
jednom mjestu
USKLADIŠTENE PROCEDURE (eng. Stored
Procedures)


Upit koji se čuva u SQL Server-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

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
Uskladištene procedure bez ulaznih parametara


nema prosljeđivanja parametara
zahtjeva dodatnu sintaksu za definiranje uskladištene procedure
CREATE PROCEDURE DBO.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
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 PROCEDURE DBO.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)
@skupina
@varchar(30) = ‘Analgetik’
ON Lijek.Kod=[Specifikacija
FSL].Kod
WHERE ((([Farmakoterapijska skupina].Naziv)=@skupina))
ORDER BY Lijek.Naziv;

poziv procedure (implementacija: MS SQL Server 2000)
USE Ljekarna
EXEC SkupinaL ‘Antipiretik’
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
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

Sintaksa za kreiranje okidača INSERT (Implementacija SQL Server 2000)
CREATE TRIGGER UnosNoveCijene ON [Lijek]
FOR INSERT
AS
UPDATE Lijek
SET Lijek.Cijena = inserted.Cijena
FROM Lijek JOIN inserted
ON Lijek.Kod = inserted.Kod;
Korištenjem naredbe INSERT na tabeli Lijek,
aktivirat će se okidač UnosNoveCijene!!!