Transcript Document

Povzeto po [2, 112-195]
Structured Query Language




Uvod v SQL
Pisanje SQL stavkov
Stavki skupine SQL DML
Stavki skupine SQL DDL
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
-1-
Uvod v SQL…
 SQL je transformacijsko usmerjen jezik, ki ga
sestavljata dve skupini ukazov:
– Skupina ukazov DDL (Data Definition Language) za
opredelitev strukture podatkovne baze in
– Skupina ukazov DML (Data Manipulation Language) za
poizvedovanje in ažuriranje podatkov.
 SQL do izdaje SQL:1999 ne vključuje ukazov
kontrolnega toka. Kontrolni tok je bil potrebno
obvladati s programskim jezikom ali interaktivno
z odločitvami uporabnikov.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
-2-
Uvod v SQL…
 Lastnosti SQL:
– Enostaven;
– Nepostopkoven (kaj in ne kako);
– Uporaben v okviru številnih vlog: skrbniki PB, vodstvo,
razvijalci informacijskih rešitev, končni uporabniki;
– Obstaja ISO standard za SQL;
– SQL de-facto in tudi uradno standardni jezik za delo z
relacijskimi podatkovnimi bazami.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
-3-
Zgodovina SQL
 V 1970h IBM razvija sistem System R, ki bo
temeljil na relacijskem modelu.
 1974 – D. Chamberlin in F. Boyce (IBM San Jose
Laboratory) definirata jezik ‘Structured English
Query Language’ (SEQUEL).
– SEQUEL se kasneje preimenuje v SQL
 Pozno v 1970h – Relational Software (danes
Oracle) razvije svoj SUPB, ki temelji na
relacijskem modelu in implementira SQL.
 Poleti 1979 – Oracle izda prvo komercialno
različico SQL; nekaj tednov pred IBM-ovo
implementacijo System/38
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
-4-
Standardizacija SQL
 1986, SQL-86
– ANSI izda prvo različico standarda. Leto kasneje potrdi še
ISO.
 1989, SQL-89
– majhne revizije (FIPS 127-1).
 1992, SQL-92
– večja revizija standarda.
 1999, SQL:1999 ali SQL3
– številne novosti: rekurzivne poizvedbe, prožilci, podpora
proceduralni kodi, nekateri objektni dodatki…
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
-5-
Standardizacija SQL
 2003, SQL:2003
– dodane lastnosti za delo z XML, sekvence, avto-generiranje
vrednosti…
 2006, SQL:2006
– ISO/IEC 9075-14:2006 definira način povezovanja SQL in
XML.
 2008, SQL:2008
– Izboljšave SQL:2003
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
-6-
Dodatki za proceduralnost
Vir
Naziv
Polno ime
ANSI
SQL/PSM
SQL/Persistent Stored Module
IBM
SQL PL
SQL Procedural Language
Microsoft/Sybase
T-SQL
Transact-SQL
MySQL
MySQL
MySQL
Oracle
PL/SQL
Procedural Language/SQL
PostgreSQL
PL/pgSQL
Procedural Language/PostrgeSQL
Proceduralnost dosegljiva tudi prek integracije
SUPB in programskih jezikov. Npr. SQL standard
definira dodatek SQL/JRT za podporo Javi v SQL
bazah.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
-7-
Druge razširitve SQL standarda
 SQL:2003 definira številne druge razširitve
– SQL/CLI (Call-Level Interface), skupen vmesnik za izvajanje
SQL ukazov iz drugih programskih jezikov.
– SQL/MED (Management of External Data), gre za vmesnike,
s katerimi je moč delati z zunanjimi podatki (podatki, ki niso
upravljani s strani z SQL-podprtim SUPB).
– SQL/OLB (Object Language Bindings), sintaksa in semantika
SQLJ (SQL vključen v Javo).
– SQL/Schemata (Information and Definition Schemas), nabor
mehanizmov, ki omogočajo, da so SQL baze in njihovi
objekti samo-opisni (identifikacija objekta, strukturne in
druge omejitve, varnost…
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
-8-
Druge razširitve SQL standarda
 SQL:2003 definira številne druge razširitve
– The SQL/JRT (SQL Routines and Types for the Java
Programming Language), vključevanje statičnih Java metod
in rutin iz SQL aplikacij.
– The SQL/XML (XML-Related Specifications), razširitve za
uporabo XML v povezavi z SQL.
– The SQL/PSM (Persistent Stored Modules), standardizacija
proceduralnih razširitev SQL (kontrolni tok, pogojni stavki,
kurzorji, lokalne spremenljivke, shranjene procedure…).
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
-9-
Pomembnost jezika SQL…
 SQL do sedaj edini široko sprejet standardni
podatkovni jezik.
 za SQL postane del aplikacijskih arhitektur (npr. v
okviru IBM-ove arhitekture - Systems Application
Architecture (SAA).
 Strateška odločitev več pomembnih združb
– Konzorcij X/OPEN za UNIX
– Federal Information Processing Standard (FIPS) – standard
kateremu morajo ustrezati vsi SUPB-ji prodani državnim
organom v ZDA.
– …
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 10 -
Pomembnost jezika SQL
 SQL uporabljen tudi v drugih standardih
– ISO Information Resource Dictionary System (IRDS)
– Remote Data Access (RDA),...
 Interes v akademskih krogih daje jeziku
teoretično osnovo in tehnike za implementacijo
– Optimizacija poizvedb
– Distribucija podatkov
– Varnost podatkov
 Pojavljajo se specializirane implementacije SQL,
npr. za OLAP
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 11 -
Pisanje SQL stavkov…
 SQL stavki so sestavljeni iz rezerviranih in
uporabniško definiranih besed.
 Rezervirane besede so natančno določene,
napisane morajo biti pravilno, ne smejo se lomiti
med vrstice.
 Uporabniško definirane besede označujejo razne
podatkovne objekte, kot so npr. relacije, stolpci,
pogledi,…
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 12 -
Pisanje SQL stavkov…
 Večina komponent SQL stavkov je neodvisna od
velikosti pisave; izjema so tekstovni podatki.
 Da dosežemo boljšo berljivost, pišemo SQL
stavke v več vrsticah in z zamiki:
– Vsak sklop SQL stavka se začne v novi vrstici
– Sklopi so levo poravnani
– Če ima sklop več delov, je vsak v svoji vrstici in poravnan z
začetkom sklopa
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 13 -
Pisanje SQL stavkov…
 Za opis sintakse SQL stavkov bomo uporabljali
razširjeno BNF notacijo:
–
–
–
–
–
–
REZERVIRANE BESEDE z velikimi črkami,
uporabniško definirane besede z malimi črkami,
Znak | za izbiro med alternativami,
{Obvezni elementi} v zavitih oklepajih,
[Opcijski elementi] v oglatih oklepajih,
Znak … za opcijske ponovitve (0 ali več).
BNF = Backus Naur Form
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 14 -
Pisanje SQL stavkov
 Podatkovne vrednosti predstavljajo konstante v
SQL Stavkih.
 Vse ne-numerične vrednosti so zapisane v
enojnih navednicah
‘Ljubljana’
 Vse numerične vrednosti brez navednic
225.990
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 15 -
Implementacije SQL
 Med standardi SQL-92, SQL:1999; SQL:2003,…
razlike
 Implementacije ponudnikov SUPB različne
(dialekti)
 Primerjava implementacij SQL
– http://troels.arvin.dk/db/rdbms/
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 16 -
Stavki skupine SQL DML
 DML skupina zajema SQL stavke za manipulacijo
s podatki
–
–
–
–
SELECT  Izbira
INSERT  Dodajanje
DELETE  Brisanje
UPDATE  Spreminjanje
 Sintaksa SELECT stavka najbolj kompleksna
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 17 -
SELECT stavek…
SELECT [DISTINCT | ALL]
{* | [columnExpression [AS newName]] [,...] }
FROM
TableName [alias] [, ...]
[WHERE
condition]
[GROUP BY columnList] [HAVING
[ORDER BY columnList]
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 18 -
condition]
SELECT stavek…
 FROM
 WHERE
 GROUP BY
 HAVING
 SELECT
 ORDER BY
Določa tabele za poizvedbo
Filtrira vrstice
Združuje vrstice po vrednostih
izbranih stolpcev
Filtrira skupine glede na
določene pogoje
Določa stolpce, ki naj se
pojavijo v izhodni relaciji
Določa vrstni red vrstic na
izhodu
Vrstnega reda sklopov ni možno spreminjati!
Obvezna sta samo SELECT in FROM sklopa!
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 19 -
Primeri
 Za primere bomo uporabljali shemo PB o hotelih
Hotel
Room
Booking
Guest
(hotelNo, hotelName, address)
(roomNo, hotelNo, type, price)
(hotelNo, guestNo, dateFrom, dateTo, roomNo, comments)
(guestNo, guestName, guestAddress)
 Izpiši vse podatke hotelih
SELECT hotelNo, hotelName, address
FROM Hotel
ali krajše
SELECT * FROM Hotel
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 20 -
Uporaba DISTINCT
 Izpiši oznake hotelov in sob, ki so bile kdaj koli
rezervirane
SELECT DISTINCT hotelNo, roomNo
FROM Booking
Ukaz DISTINCT eliminira dvojnike
V Accessu so na voljo še DISTINCTROW in TOP n
Glej primer 1 Q_Distinct
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 21 -
Izračunana polja
 Izpiši ceno sob za deset dnevni najem
SELECT RoomNo, type, price*10 AS CenaNajema
FROM Room
Uporabljamo formule
Izračunanemu stolpcu
dodelimo naziv
Glej primer 2 Q_CalcFields
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 22 -
Iskalni kriteriji
 Izpiši oznake hotelov, ki imajo tri-posteljne sobe
(type = 3) in ceno manjšo kot 100 EUR
SELECT hotelNo
FROM Room
WHERE type = 3 AND price < 100
Pogoj 1
Pogoj 2
Pogoje združujemo z logičnimi operatorji
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 23 -
Iskanje z uporabo BETWEEN
 Izpiši vse sobe s ceno med 50 in 70 EUR
SELECT roomNo
FROM Room
WHERE price BETWEEN 50 AND 70
BETWEEN vključuje spodnjo in zgornjo mejo!
Uporabimo lahko tudi negacijo NOT BETWEEN
BETWEEN ne doda veliko SQL moči, možno izraziti posredno
Glej primer 3 Q_Between
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 24 -
Iskanje po članstvu množice
 Izpiši oznake hotelov in številke dve ali triposteljnih sob
SELECT hotelNo, roomNo
FROM Room
WHERE type IN (2,3)
Članstvo množice
Uporabimo lahko tudi negacijo NOT IN
IN ne doda veliko SQL moči; koristno pri večjih množicah
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 25 -
Iskanje z vzorcem
 Izpiši vse goste, ki živijo kjerkoli v Ljubljani (v
polju guestAddress je tudi string ‘Ljubljana’)
SELECT guestNo, guestName, guestAddress
FROM Guest
WHERE guestAddress LIKE ‘%Ljubljana%’
Iskanje z vzorcem
SQL ima dva posebna znaka za iskanje z vzorcem:
Znak % nadomešča katerikoli niz znakov
Znak _ nadomešča katerikoli znak
Glej primer 4 Q_Like
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 26 -
Iskanje z NULL vrednostjo v pogoju
 Izpiši vse rezervacije brez podanih komentarjev
SELECT *
FROM Booking
WHERE comments IS NULL
Iskanje z NULL vrednostjo
Uporabljamo lahko tudi negacijo IS NOT NULL
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 27 -
Sortiranje vrstic v izhodni relaciji
 Izpiši vse podatke o sobah, urejene po tipu sobe
od največje do najmanjše in znotraj tipa po
cenah od najmanjše do največje
SELECT *
FROM Room
ORDER BY type DESC, price ASC
ASC – ascending  naraščujoče
DESC – descending  padajoče
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 28 -
Agregiranje podatkov…
 ISO standard definira pet agregarnih operacij
–
–
–
–
–
COUNT
SUM
AVG
MIN
MAX
vrne
vrne
vrne
vrne
vrne
število vrednosti v določenem stolpcu
seštevek vrednosti v določenem stolpcu
povprečje vrednosti v določenem stolpcu
najmanjšo vrednost v določenem stolpcu
največjo vrednost v določenem stolpcu
 Vse operacije delujejo na enem stolpcu in vračajo
eno samo vrednost.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 29 -
Agregiranje podatkov…
 COUNT, MIN in MAX se uporabljajo za numerične
in ne-numerične vrednosti, SUM in AVG
zahtevata numerične vrednosti.
 Vse operacije razen COUNT(*) najprej odstranijo
vrstice z NULL vrednostjo v stolpcu, po katerem
agregiramo.
 COUNT(*) prešteje vse vrstice, ne glede na NULL
vrednosti ali duplikate.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 30 -
Agregiranje podatkov…
 Če se želimo znebiti duplikatov, uporabimo
DISTINCT pred imenom stolpca.
 DISTINCT nima učinka na MIN/MAX, lahko pa
vpliva na SUM/AVG.
 Agregarne operacije lahko uporabimo le v
SELECT ali HAVING sklopu
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 31 -
Agregiranje podatkov
 Če SELECT sklop vsebuje agregarno operacijo,
mora obstajati tudi GROUP BY sklop, sicer ni moč
dodeliti agregirane vrednosti.
SELECT roomNo, AVG(price)
FROM Room
Napačna raba agregacije
Glej primer 5 Q_Group By problem
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 32 -
Uporaba COUNT in DISTINCT
 V koliko različnih hotelih obstajajo rezervacije za
prvi teden v mesecu januarju 2005
SELECT COUNT (DISTINCT hotelNo) AS numH
FROM Booking
WHERE dateFrom = ‘1.1.2005’ AND
dateTo = ‘7.1.2005’
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 33 -
Uporaba več agregatov istočasno
 Izpiši povprečno, minimalno in maksimalno ceno
dvoposteljne sobe
SELECT AVG(price), MIN(price), MAX(price)
FROM Room
WHERE type = 2
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 34 -
Združevanje podatkov…
 Sklop GROUP BY uporabimo za združevanje
podatkov v skupine.
 SELECT in GROUP BY sta tesno povezana
– vsak element v SELECT seznamu, mora imeti samo eno
vrednost za vse elemente v skupini,
– SELECT sklop lahko vsebuje le:




PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
imena stolpcev
agregarne operacije
konstante ali
izraze, ki so sestavljeni iz kombinacije naštetih elementov.
- 35 -
Združevanje podatkov…
 Vsi stolpci, ki so navedeni v SELECT sklopu, se
morajo nahajati tudi v GROUP BY sklopu, razen
tistih, ki nastopajo samo v agregarnih operacijah.
 Če uporabljamo WHERE sklop v kombinaciji z
GROUP BY, se WHERE upošteva najprej,
združevanje pa se izvede na preostalih vrsticah.
 ISO standard jemlje NULL vrednosti kot enake,
ko gre za združevanje.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 36 -
Primer združevanja
 Izpiši število enoposteljnih, dvoposteljnih in
troposteljnih sob v vsakem hotelu
SELECT hotelNo, type, COUNT(roomNo)
FROM Room
GROUP BY hotelNo, type
Za vsako skupino hotel, tip sobe vrne število sob
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 37 -
Omejitev skupin
 HAVING sklop je namenjen uporabi v kombinaciji
z GROUP BY kot omejitev skupin, ki se lahko
pojavijo v rezultatu.
 Deluje podobno kot WHERE
– WHERE filtrira posamezne vrstice
– HAVING filtrira skupine.
 Stolpci, ki so navedeni v HAVING sklopu, morajo
biti tudi v SELECT sklopu ali v agregatih.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 38 -
Uporaba sklopa HAVING
 Izpiši število enoposteljnih, dvoposteljnih in
troposteljnih sob v vsakem hotelu ter njihovo
povprečno ceno. Upoštevaj samo tiste primere, ko
je število sob večje od 1.
SELECT hotelNo, type, COUNT(roomNo),
AVG(price)
FROM Room
GROUP BY hotelNo, type
HAVING COUNT(roomNo) > 1
Glej primer 6 Q_Having
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 39 -
Gnezdenje poizvedb…
 Nekateri SQL stavki imajo lahko vgnezdene
SELECT stavke.
 Vgnezdeni SELECT stavki se lahko uporabijo v
WHERE ali HAVING sklopih drugega SELECT
stavka (subselect).
 Vgnezdeni SELECT stavki se lahko pojavijo tudi v
INSERT, UPDATE in DELETE stavkih.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 40 -
Primer vgnezdenega SELECT stavka
 Izpiši nazive hotelov, ki imajo vsaj 10
troposteljnih sob
SELECT hotelName
Ali je stavek pravilen?
FROM Hotel
Manjka WHERE pogoj v
WHERE hotelNo IN
vgnezdenem SELECT stavku:
(SELECT hotelNo
WHERE type = 3
FROM Room
GROUP BY hotelNo
HAVING COUNT(hotelNo) > 9)
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 41 -
Pravila gnezdenja SELECT stavkov…
 Vgnezdeni SELECT stavki ne smejo uporabljati
ORDER BY sklopa.
 SELECT sklop vgnezdenega SELECT stavka lahko
zajema samo en stolpec, razen v primeru
uporabe ukaza EXISTS.
 Imena stolpcev v vgnezdenem SELECT stavku se
privzeto nanašajo na tabele iz vgnezdenega ali
zunanjega SELECT stavka (uporaba alias-ov)
Glej primer 7 Q_Exists
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 42 -
Pravila gnezdenja SELECT stavkov
 Ko je vgnezden SELECT stavek operand v
primerjavi, se mora nahajati na desni strani
enačbe.
 Vgnezdeni SELECT stavek ne more biti operand v
izrazu.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 43 -
Vaja
 Napišite SQL poizvedbo, ki vrne imena hotelov, ki
imajo nadpovprečno ceno svojih sob.
Glej primer 8 Q_Vaja
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 44 -
Uporaba ANY in ALL
 V vgnezedenih SELECT stavkih, ki vračajo en sam
stolpec, lahko uporabljamo operatorja ANY in
ALL.
 Z uporabo ALL bo pogoj izpolnjen samo, če bo
veljal za vse vrednosti, ki ji vrača poizvedba.
 Z uporabo ANY, bo pogoj izpolnjen, če bo veljal
za vsaj eno od vrednosti, ki ji poizvedba vrača.
 Če je rezultat poizvedbe prazen, bo ALL vrnil
true, ANY pa false.
 Namesto ANY lahko uporabljamo tudi SOME.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 45 -
Primer uporabe ANY
 Izpiši številke sob ter pripadajočih hotelov,
katerih cena je večja kot cena vsaj ene sobe v
hotelu ‘Hilton New York’ (hotelNo = 5).
SELECT R.hotelNo, R.roomNo
FROM room AS R
WHERE R.hotelNo <>5 AND
R.price > ANY ( SELECT price
FROM Room
WHERE hotelNo = 5 );
Glej primer 9 Q_Any
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 46 -
Primer uporaba ALL
 Izpiši številke sob ter pripadajočih hotelov,
katerih cena je večja kot cena vseh sob v hotelu
‘Hilton New York’ (hotelNo = ‘HIL’).
SELECT R.hotelNo, R.roomNo
FROM Room AS R
WHERE R.hotelNo <>5 AND
R.price > ALL ( SELECT Price
FROM Room
WHERE hotelNo = 5 );
Glej primer 10 Q_All
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 47 -
Poizvedbe po več tabelah...
 Poizvedbe po več tabelah lahko izvajamo z
uporabo vgnezdenih SELECT stavkov
 Omejitev: stolpci v rezultatu so lahko le iz ene
tabele
 V poizvedbah, ki vračajo stolpce različnih tabel,
moramo uporabljati stik.
 Stik izvedemo tako, da v sklopu FROM navedemo
tabele, v sklopu WHERE pa določimo stolpce za
stik.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 48 -
Poizvedbe po več tabelah...
 Za tabele v razdelku FROM lahko uvedemo
sinonime (alias).
 Sintaksa:
SELECT H.hotelNo, R.roomNo
FROM Hotel H, Room R
...
 Sinonimi so potrebni za ločevanje med
istoimenskimi stolpci različnih tabel.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 49 -
Primer poizvedbe po dveh tabelah
 Izpiši številke, tip in cene sob v hotelu z nazivom
‘Hilton New York’
SELECT R.roomNo, R.type, R.price
FROM Room R, Hotel H
WHERE R.hotelNo = H.hotelNo AND
H.hotelName = ‘Hilton New York’
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 50 -
Primer poizvedbe po več tabelah
 Izpiši imena in naslove gostov, ki imajo za termin
od 1.1.2005 do 6.1.2005 rezervacije v hotelu
‘Hilton New York’
SELECT G.guestName, G.guestAddress
FROM Guest G, Booking B, Hotel H
WHERE B.guestNo = G.guestNo AND
B.hotelNo = H.hotelNo AND
B.fromDate = ‘1.1.2005’ AND
B.toDate = ‘6.1.2005’ AND
H.hotelName = ‘Hilton New York’
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 51 -
Alternativni načini stika več tabel
 SQL omogoča alternativne načine stika med več
tabelami:
– FROM Room R JOIN Hotel H ON R.hotelNo = H.hotelNo
– FROM Room JOIN Hotel USING hotelNo
– FROM Room NATURAL JOIN Hotel
 Zgornji zapisi nadomestijo sklopa FROM in
WHERE
 V prvem primeru rezultat vsebuje dva identična
stolpca hotelNo.
V Accessu so na voljo ukazi INNER JOIN, LEFT JOIN, RIGHT JOIN
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 52 -
Računanje stika...
 Stik predstavlja podmnožico kartezijskega
produkta
 Če pri navedbi dveh tabel A in B v FROM sklopu
ne navedemo stika v WHERE sklopu, dobimo
kartezijski produkt med A in B.
 ISO standard nudi posebno obliko zapisa za
kartezijski produkt:
SELECT [DISTINCT | ALL] {* | columnList}
FROM Table1 CROSS JOIN Table2
V Accessu ukaz CROSS JOIN ni na voljo
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 53 -
Računanje stika
 V splošnem je postopek za generiranje rezultata
SELECT stavka s stikom naslednji:
– Sestavi kartezijski produkt tabel, ki so naštete v sklopu
FROM
– Če obstaja WHERE sklop, upoštevaj vse pogoje in iz
kartezijskega produkta izberi samo tiste vrstice, ki pogojem
ustrezajo (selekcija)
– Iz izbranih vrstic kartezijskega produkta izberi samo tiste
stolpce, ki ustrezajo naboru v SELECT sklopu (projekcija)
– Če je bil uporabljen DISTINCT operator, eliminiraj dvojnike
(del projekcije)
– Če obstaja ORDER BY sklop, ustrezno razvrsti vrstice
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 54 -
Zunanji stik...
 S pomočjo zunanjega stika dobimo v rezultat tudi
vrstice, ki nimajo stične vrednosti v drugi tabeli.
R=OSEBA
S=KRAJ
ID
Priimek in ime
PTT
PTT
Naziv
1
Kante Janez
5270
1000
Ljubljana
2
Tratnik Jože
5000
5000
Nova Gorica
3
Mali Mihael
5270
Ajdovščina
4
Brecelj Jana
1000
(Priimek in ime, PTT, Naziv zač.preb.(R))
Priimek in ime
PTT
Naziv zač. preb.
Kante Janez
5270
Ajdovščina
Tratnik Jože
5000
Nova Gorica
1000
Ljubljana
Mali Mihael
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
Brecelj Jana
- 55 -
S
Zunanji stik
 Za zapis SELECT stavka, ki vsebuje zunanji stik
med dvema tabelama, uporabimo naslednjo
sintakso:
SELECT DISTINCT H.hotelName, B.roomNo
FROM Hotel H LEFT JOIN
Booking B ON H.hotelNo = B. hotelNo
Kaj izpiše zgornja poizvedba?
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 56 -
Polni zunanji stik
 SQL omogoča tudi izvedbo polnega zunanjega
stika (Full Outer Join)
 Polni zunanji stik med tabelama A in B kot
rezultat vrne tudi tiste vrstice, ki v tabeli A ali B
nimajo stičnega para.
 Sintaksa:
SELECT DISTINCT G.guestName, H.hotelName
FROM Hotel H FULL JOIN Guest G
ON H.address = G.guestAddress
V Accessu ukaz FULL JOIN ni na voljo.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 57 -
Uporaba EXISTS in NOT EXISTS
 EXISTS in NOT EXISTS lahko uporabljamo le v
vgnezdenih poizvedbah.
 Vračajo logičen rezultat true/false.
– True dobimo, če obstaja vsaj ena vrstica v tabeli, ki je
rezultat vgnezdene poizvedbe.
– False dobimo, če vgnezdena poizvedba vrača prazno
množico.
 NOT EXISTS je negacija EXISTS.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 58 -
Uporaba EXISTS in NOT EXISTS
 (NOT) EXISTS preveri samo, če v rezultatu
vgnezdene poizvedbe (ne) obstajajo vrstice
 Število stolpcev v SELECT sklopu vgnezdene
poizvedbe je zato irelevantno
 Navadno uporabimo sintakso:
(SELECT * ...)
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 59 -
Primer uporabe EXISTS
 Izpiši vse goste, ki so kdaj koli imeli rezervacije v
hotelu Hilton New York.
SELECT guestName, guestAddress
FROM Guest G
Kaj dobimo, če ta pogoj izpustimo?
WHERE EXISTS
(SELECT *
FROM Booking B, Hotel H
WHERE B.guestNo = G.guestNo AND
B.hotelNo = H.hotelNo AND
H.hotelName = ‘Hilton New York’)
Glej primer 11 Q_Exists
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 60 -
Namesto EXISTS lahko uporabimo stik
SELECT DISTINCT G.guestName, G.guestAddress
FROM Guest G, Booking B, Hotel H
WHERE B.guestNo = G.guestNo AND
B.hotelNo = H.hotelNo AND
H.hotelName = ‘Hilton New York’)
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 61 -
Uporaba operacij nad množicami...
 Rezultate dveh ali več poizvedb lahko združujemo
z ukazi:
– Union (unija),
– Intersection (Presek)
– Difference (EXCEPT) (Razlika)
 Da lahko izvajamo naštete operacije, morata
tabeli A in B biti skladni (domene atributov
morajo biti enake).
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 62 -
Uporaba operacij nad množicami
 Sintaksa:
op [ALL] [CORRESPONDING [BY {column1 [, ...]}]]
 Če uporabimo CORRESPONDING BY, se operacija
izvede samo nad poimenovanimi stolpci
 Če uporabimo samo CORRESPONDING brez BY
člena, se operacija izvede nad skupnimi stolpci.
 Če uporabimo ALL, lahko rezultat vključuje tudi
dvojnike
V Accessu opcija CORRESPONDING BY ni na voljo.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 63 -
Primer unije
 Izpiši vse mesta, kjer je bodisi lociran kakšen
gost hotelske verige ali kakšen hotel.
(SELECT address
FROM Hotel)
UNION
(SELECT guestAddress
FROM Guest)
V Accessu se polja imenujejo tako kot v levi tabeli pri ukazu UNION.
Glej primer 12 Q_Union
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 64 -
Kako bo naziv stolpca,
ki ga poizvedba vrne?
Unija z uporabo CORRESPONDING BY
(SELECT *
FROM Hotel)
UNION CORRESPONDING BY address
(SELECT *
!
FROM Guest)
Hotel(hotelNo, hotelName, address)
Guest(guestNo, guestName, address)
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 65 -
Primer preseka
 Izpiši vse mesta, kjer je lociran kakšen gost
hotelske verige in obenem kakšen hotel.
(SELECT address
FROM Hotel)
INTERSECTION
(SELECT guestAddress
FROM Guest)
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 66 -
Presek z uporabo COORESPONDING BY
(SELECT *
FROM Hotel)
INTERSECT CORRESPONDING BY address
(SELECT *
FROM Guest)
!
Hotel(hotelNo, hotelName, address)
Guest(guestNo, guestName, address)
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 67 -
Uporaba EXCEPT
 Izpiši vse mesta, kjer je lociran kakšen gost
hotelske verige in obenem v tem kraju ni
nobenega hotela.
(SELECT guestAddress
FROM Guest)
EXCEPT
(SELECT address
FROM Hotel)
V Accessu ukaz EXCEPT ni na voljo.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 68 -
EXCEPT z uporabo COORESPONDING BY
(SELECT *
FROM Guest)
EXCEPT CORRESPONDING BY address
(SELECT *
!
FROM Hotel)
Hotel(hotelNo, hotelName, address)
Guest(guestNo, guestName, address)
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 69 -
INSERT stavek...
INSERT INTO TableName [ (columnList) ]
VALUES (dataValueList)
 Seznam columnList ni obvezen; če ga spustimo,
interpreter pričakuje vrednosti za vse stolpce
tabele, v vrstnem redu, kot so bili kreirani.
 Pri vnosu moramo vpisati najmanj vse obvezne
vrednosti (not null), razen za stolpce, pri katerih
je bila ob kreiranju določena privzeta vrednost
(DEFAULT).
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 70 -
INSERT stavek...
 Seznam dataValueList mora ustrezati seznamu
columnList:
– Število elementov v seznamih mora biti enako;
– Vrednost, ki se nanaša na nek stolpec, mora biti v seznamu
dataValueList na istem mestu, kot je stolpec v seznamu
columnList;
– Podatkovni tip vrednosti, ki se nanaša na nek stolpec, mora
biti enak kot podatkovni tip stolpca.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 71 -
Primeri INSERT stavkov...
 Vnos nove vrstice v tabelo Booking
INSERT INTO Booking
VALUES (21, 109, ’12.12.2005’, ’17.12.2005’,
109, ‘soba za nekadilce’);
 Shema relacije Booking:
Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo,
comments)
Glej primer 13 Q_Insert Simple
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 72 -
Primeri INSERT stavkov...
 Vnos nove vrstice v tabelo Booking – vnos samo
obveznih vrednosti
INSERT INTO Booking
VALUES (21, 109, ’12.12.2005’, ’17.12.2005’,
109, null);
stolpec comments je neobvezen
 ali
INSERT INTO Booking (hotelNo, guestNo,
dateFrom, dateTo, roomNo)
VALUES (21, 109, ’12.12.2005’, ’17.12.2005’,
109);
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 73 -
Primeri INSERT stavkov...
 Vnos več vrstic iz ene ali več drugih tabel...
INSERT INTO TableName [ (columnList) ]
SELECT ...
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 74 -
Primeri INSERT stavkov...
 Predpostavimo, da imamo tabelo HotelRez, ki za
vsak hotel pove oznako hotela, naziv hotela in
število trenutno odprtih rezervacij
HotelRez(hotelNo, hotelName, NumRez)
 S pomočjo tabel Hotel in Booking napolnimo
tabelo HotelRez
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 75 -
Primeri INSERT stavkov...
INSERT INTO HotelRez
(SELECT H.hotelNo, H.hotelName, COUNT(*)
FROM Hotel H, Booking B
WHERE H.hotelNo = B.hotelNo AND
B.dateFrom >= date()
GROUP BY H.hotelNo, H.hotelName)
UNION
(SELECT hotelNo, hotelName, 0
FROM Hotel
Kaj se zgodi, če
WHERE hotelNo NOT IN
spodnji
del SQL stavka
(SELECT DISTINCT hotelNo
spustimo?
FROM Booking
WHERE B.dateFrom>=date()));
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 76 -
UPDATE stavek...
UPDATE TableName
SET columnName1 = dataValue1
[, columnName2 = dataValue2...]
[WHERE searchCondition]
 TableName se lahko nanaša na ime osnovne
tabele ali ime pogleda.
 Sklop SET določa nazive enega ali več stolpcev
ter nove vrednosti teh stolpcev.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 77 -
UPDATE stavek
 WHERE sklop je neobvezen:
– Če ga spustimo, se v imenovane stolpce vpišejo nove
vrednosti za vse vrstice v tabeli;
– Če WHERE sklop določimo, se spremembe zgodijo zgolj za
vrstice, ki ustrezajo WHERE pogojem.
 Nove podatkovne vrednosti morajo ustrezati
podatkovnemu tipu stolpca.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 78 -
Primeri UPDATE stavkov
 Vse dvoposteljne sobe v hotelu Hilton (HotelNo =
5) povišaj za 5%
UPDATE Room
SET price = price * 1,05
WHERE type = 2 AND hotelNo = 5
Glej primer 14 Q_Update Simple, 15 Q_Update Complex
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 79 -
Vaja
 V vseh hotelih zmanjšaj ceno najdražjih sob za
10%!
Glej primer 16 Q_Vaja
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 80 -
DELETE stavek
DELETE FROM TableName
[WHERE searchCondition]
 TableName se lahko nanaša na ime osnovne
tabele ali ime pogleda.
 WHERE sklop ni obvezen. Če ga spustimo,
zbrišemo vse vrstice v tabeli. Tabela ostane.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 81 -
Primeri DELETE stavkov
 Izbriši vse potekle rezervacije, ki se nanašajo na
hotel Slon.
DELETE FROM Booking
WHERE FromDate < ‘25.11.2005’ AND
hotelNo IN (SELECT hotelNo
FROM Hotel
WHERE hotelName = ‘Hotel Slon’
)
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 82 -
Stavki skupine SQL DDL...
 DDL skupina zajema SQL stavke za manipulacijo
s strukturo podatkovne baze.
 Kaj si bomo pogledali:
–
–
–
–
–
–
–
–
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
Podatkovni tipi, ki jih podpira SQL standard.
Namen sklopa “integrity enhancement feature”.
Kako definirati omejitve z SQL-om?
Kako uporabiti “integrity enhancement feature” v CREATE in
ALTER TABLE stavkih?
Delo s pogledi (ang. view)?
Način delovanja ISO transakcijskega modela
Uporaba GRANT in REVOKE stavkov v okviru zagotavljanja
varnosti
…
- 83 -
Podatkovni tipi v SQL standardu
Vir: [2,159]
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 84 -
Integrity Enhancement Feature...
 Za zagotavljanje skladnosti podatkov SQL
standard ponuja več vrst omejitev:
– Obveznost podatkov
– Omejitve domene (Domain constraints)
– Pravila za celovitost podatkov (Integrity constraints)
 Celovitost entitet (Entity Integrity)
 Celovitost povezav (Referential Integrity)
– Števnost (Multyplicity)
– Splošne omejitve (General constraints)
 Omejitve so lahko definirane v CREATE in ALTER
TABLE stavkih.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 85 -
Integrity Enhancement Feature...
 Obveznost podatkov
hotelNo
Numeric(3) NOT NULL
 Omejitve domene
CHECK
spol CHAR NOT NULL
CHECK (spol IN (‘M’, ‘Ž’))
V Accessu DOMENE niso na voljo.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 86 -
Integrity Enhancement Feature...
 CREATE DOMAIN
CREATE DOMAIN DomainName [AS] dataType
[DEFAULT defaultOption]
[CHECK (searchCondition)]
Primer:
CREATE DOMAIN Tspol AS CHAR
CHECK (VALUE IN (‘M’, ‘Ž’));
Spol Tspol NOT NULL
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 87 -
Integrity Enhancement Feature...
 searchCondition lahko vsebuje iskalno tabelo
(lookup table):
CREATE DOMAIN guestNo AS CHAR(4)
CHECK (VALUE IN (SELECT guestNo FROM
Guest));
 Domeno lahko ukinemo z uporabo stavka DROP
DOMAIN:
DROP DOMAIN DomainName
[RESTRICT | CASCADE]
Access omogoča definicijo LOOKUP tabel.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 88 -
Kaj naj se zgodi, če je
domena trenutno v uporabi
IEF – Celovitost entitet
 Primarni ključ tabele mora vsebovati enolično
neprazno vrednost v vsaki vrstici tabele.
 ISO standard podpira tuje ključe s sklopom
FOREIGN KEY v okviru CREATE in ALTER TABLE
stavkov.
PRIMARY KEY(hotelNo, roomNo)
 Vsaka tabela ima lahko največ en primarni ključ.
Enoličnost neosnovnih stolpcev lahko
zagotavljamo z uporabo UNIQUE
UNIQUE(priimek)
V Accessu je mogoče definirati validacijska pravila.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 89 -
IEF – Celovitost povezav...
 FK (tuji ključ) je stolpec ali množica stolpcev, ki
povezujejo vsako vrstico tabele A z vrstico
referenčne tabele B, kjer se ujemajo vrednosti
A.FK = B.PK.
 Celovitost povezav zagotavlja, da če ima FK neko
vrednost, potem se ta vrednost nahaja v
primarnem ključu povezane tabele.
 ISO standard omogoča definicijo tujih ključev s
sklopom FOREIGN KEY v CREATE in ALTER
TABLE
FOREIGN KEY(hotelNo) REFERENCES Hotel
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 90 -
IEF – Celovitost povezav...
 Vsak INSERT/UPDATE stavek, ki skuša kreirati FK
vrednost v tabeli, brez da bi ta vrednost obstajala
kot PK v povezani tabeli, je zavrnjen.
 Ob zavrnitvi so možne naslednje akcije
–
–
–
–
CASCADE
SET NULL
SET DEFAULT
NO ACTION
Access omogoča zagotavljanje celovitosti povezav, vendar ne omogoča vseh od zgoraj naštetih možnosti.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 91 -
IEF – Celovitost povezav...
 Določimo z uporabo ON UPDATE, ON DELETE
ON UPDATE SET NULL
 Primeri:
FOREIGN KEY (hotelNo) REFERENCES Hotel
ON DELETE SET NULL
FOREIGN KEY (guestNo) REFERENCES Guest
ON UPDATE CASCADE
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 92 -
IEF – Splošne omejitve
 Splošne omejitve določimo z CHECK/UNIQUE
opcijami v CREATE in ALTER TABLE stavkih.
CREATE ASSERTION AssertionName
CHECK (searchCondition)
Access ne omogoča kreiranja objektov z ukazom CREATE, saj ne implementira DDL ukazov .
Izjema so ukazi v zvezi s kreiranjem, spreminjanjem in brisanjem tabel CREATE/DROP/ALTER TABLE.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 93 -
IEF – Splošne omejitve – primer
CREATE ASSERTION PrevecRezervacij
CHECK (NOT EXISTS (SELECT hotelNo, dateFrom,
roomNo
FROM Booking
GROUP BY hotelNo dateFrom,
roomNo
HAVING COUNT(*) > 1))
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 94 -
Kreiranje podatkovnih objektov...
 SQL DDL omogoča kreiranje in brisanje
podatkovnih objektov, kot so: shema, domena,
tabela, pogled in indeks.
 Glavni SQL DDL stavki so:
CREATE SCHEMA
CREATE/ALTER DOMAIN
CREATE/ALTER TABLE
CREATE VIEW
DROP
DROP
DROP
DROP
SCHEMA
DOMAIN
TABLE
VIEW
 Mnogi SUPB-ji omogočajo tudi
CREATE INDEX
DROP INDEX
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 95 -
Kreiranje podatkovnih objektov...
 Relacije in drugi podatkovni objekti obstajajo v
nekem okolju.
 Vsako okolje vsebuje enega ali več katalogov,
vsak katalog pa množico shem.
 Shema je poimenovana kolekcija povezanih
podatkovnih objektov.
 Objekti v shemi so lahko tabele, pogledi,
domene, trditve, dodelitve, pretvorbe in znakovni
nizi. Vsi objekti imajo istega lastnika.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 96 -
Kreiranje sheme
CREATE SCHEMA [Name |
AUTHORIZATION CreatorId ]
DROP SCHEMA Name [RESTRICT | CASCADE ]
 RESTRICT (privzeto): shema mora biti prazna,
sicer brisanje ni možno.
 CASCADE: kaskadno se brišejo vsi objekti,
povezani s shemo. Če katerokoli brisanje ne
uspe, se zavrne celotna operacija.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 97 -
Kreiranje tabele...
CREATE TABLE TableName
({colName dataType [NOT NULL] [UNIQUE]
[DEFAULT defaultOption]
[CHECK searchCondition] [,...]}
[PRIMARY KEY (listOfColumns),]
{[UNIQUE (listOfColumns),] […,]}
{[FOREIGN KEY (listOfFKColumns)
REFERENCES ParentTableName [(listOfCKColumns)],
[ON UPDATE referentialAction]
[ON DELETE referentialAction ]] [,…]}
{[CHECK (searchCondition)] [,…] })
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 98 -
Primer kreiranja tabele...
Najprej kreiramo domene
CREATE DOMAIN hotelNumber AS NUMERIC(3)
CHECK (VALUE IN (SELECT hotelNo FROM Hotel));
CREATE DOMAIN guestNumber AS NUMERIC(3)
CHECK (VALUE IN (SELECT guestNo FROM Guest));
CREATE DOMAIN rezervDate AS DATE;
CHECK(VALUE BETWEEN ‘1.1.1995’ AND ‘1.1.2200’);
CREATE DOMAIN roomNumber AS INTEGER;
CHECK(VALUE BETWEEN 100 AND 545);
CREATE DOMAIN comments AS VARCHAR(100);
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 99 -
Primer kreiranja tabele...
potem kreiramo tabelo
CREATE TABLE Booking (
hotelNo
hotelNumber NOT NULL,
CONSTRAINT PrevecRezervacij…
guestNo guestNumber NOT NULL,
dateFrom rezervDate NOT NULL
DEFAULT date(),
dateTo
rezervDate NOT NULL,
roomNo roomNumber NOT NULL,
comments comments,
PRIMARY KEY (hotelNo),
FOREIGN KEY (guestNo) REFERENCES Guest
ON DELETE SET NULL ON UPDATE CASCADE …);
Glej primer 17 Q_CreateTable
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 100 -
ALTER TABLE stavek...
 S stavkom ALTER TABLE lahko:
–
–
–
–
Dodajamo ali ukinjamo stolpce v tabeli;
Dodajamo ali ukinemo omejitve tabele;
Za stolpce v tabeli določamo ali ukinjamo privzete vrednosti;
Spreminjamo podatkovne tipe stolpcev v tabeli;
Glej primer 18 Q_AlterTable
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 101 -
Primeri ALTER TABLE stavkov...
 Spremeni tabelo Booking tako, da ukineš privzeto
vrednost stolpca fromDate.
ALTER TABLE Booking
ALTER fromDate DROP DEFAULT;
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 102 -
Primeri ALTER TABLE stavkov...
 Spremeni tabelo Booking tako, da ukineš
omejitev, da nobena soba nobenega hotela ne
sme imeti več kot eno rezervacijo na isti dan. V
tabelo Gost dodaj stolpec Spol.
ALTER TABLE Booking
DROP CONSTRAINT prevecRezervacij;
ALTER TABLE Gost
ADD Spol NOT NULL DEFAULT = ‘M’;
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 103 -
Stavek DROP TABLE
 S pomočjo stavka DROP TABLE ukinemo tabelo.
Obenem se zbrišejo vsi zapisi tabele.
DROP TABLE TableName [RESTRICT | CASCADE]
 Restrict: Ukaz se ne izvede, če obstajajo objekti,
ki so vezani na tabelo, ki jo brišemo.
 Cascade: kaskadno se brišejo vsi vezani objekti.
 Primer:
DROP TABLE Gost RESTRICT;
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 104 -
Stavek CREATE VIEW...
CREATE VIEW ViewName [ (newColumnName [,...]) ]
AS subselect
[WITH [CASCADED | LOCAL] CHECK OPTION]
 Vsakemu stolpcu pogleda lahko dodelimo novo
ime.
 Če določimo imena stolpcev, potem morajo stolpci
SELECT stavka ustrezati stolpcem pogleda.
 Če imena stolpcev ne določimo, se uporabijo imena
stolpcev iz SELECT stavka.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 105 -
Stavek CREATE VIEW...
 WITH CHECK OPTION: zagotavlja, da če vrstica
ne izpolnjuje WHERE pogoja, ni dodana v
osnovno tabelo, nad katero je pogled osnovan.
 Potrebujemo SELECT privilegij nad vsemi
tabelami, uporabljenimi v SELECT stavku ter
USAGE privilegij nad vsemi domenami, ki jih
uporabljajo stolpci SELECT stavka.
 Pogled ukinemo z ukazom DROP VIEW
DROP VIEW ViewName [RESTRICT | CASCADE]
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 106 -
Primer horizontalnega pogleda
 Kreiraj pogled RecepcijaSlon tako, da bodo v
recepciji hotela Slon videli samo svoje
rezervacije.
CREATE VIEW RecepcijaSlon
AS SELECT *
FROM Booking B, Hotel H
WHERE B.hotelNo = H.hotelNo AND
H.hotelName = ‘Hotel Slon’;
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 107 -
Primer vertikalnega pogleda
 Izdelaj pogled vseh sob hotela Slon brez cen.
CREATE VIEW RoomsSlon
AS SELECT R.roomNo, R.type
FROM Room R, Hotel H
WHERE R.hotelNo = H.hotelNo AND
H.hotelName = ‘Hotel Slon’;
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 108 -
Primer pogleda z grupiranjem
CREATE VIEW HotelRez (hNo, hName, hStRez)
AS (
SELECT H.hotelNo, H.hotelName, COUNT(*) AS Cnt
FROM Hotel H, Booking B
WHERE H.hotelNo = B.hotelNo AND
B.dateFrom >= date()
GROUP BY H.hotelNo, H.hotelName)
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 109 -
Izvedba pogleda...
 Imamo naslednji SELECT stavek
SELECT hNo, hName, hStRez
View HotelRez
FROM HotelRez
WHERE hNo > 201
CREATE VIEW
HotelRez (hNo, hName, hStRez)
ORDER BY hName
AS
SELECT H.hotelNo, H.hotelName, COUNT(*) AS Cnt
FROM Hotel H, Booking B
WHERE H.hotelNo = B.hotelNo AND
B.dateFrom >= date()
GROUP BY H.hotelNo, H.hotelName
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 110 -
Izvedba pogleda...
 (I) Imena stolpcev pogleda iz SELECT stavka so
prevedena v imena SELECT stavka, ki definira
pogled:
SELECT hNo, hName, hStRez
SELECT H.hotelNo, H.hotelName, COUNT(*) AS Cnt
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 111 -
Izvedba pogleda...
 (II) Imena iz FROM sklopa pogleda so zamenjana
z imeni FROM sklopa SELECT stavka, ki definira
pogled:
FROM HotelRez
FROM Hotel H, Booking B
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 112 -
Izvedba pogleda...
 (III) WHERE sklop SELECT stavka se združi z
WHERE sklopom iz SELECT stavka, ki definira
pogled:
WHERE H.hotelNo > 201 AND
H.hotelNo = B.hotelNo AND
B.dateFrom >= date()
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 113 -
Izvedba pogleda...
 (IV) GROUP BY in HAVING sklop se kopirata iz
SELECT stavka, ki definira pogled:
GROUP BY H.hotelNo, H.hotelName
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 114 -
Izvedba pogleda...
 (V) ORDER BY se kopira iz SELECT stavka. Imena
stolpcev se zamenjajo z imeni stolpcev iz SELECT
stavka, ki definira pogled
ORDER BY hName
ORDER BY H.hotelName
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 115 -
Izvedba pogleda...
 Po prevedbi dobimo stavek:
SELECT H.hotelNo, H.hotelName, COUNT(*) AS Cnt
FROM Hotel H, Booking B
WHERE H.hotelNo > 201 AND
H.hotelNo = B.hotelNo AND
B.dateFrom >= date()
GROUP BY H.hotelNo, H.hotelName
ORDER BY H.hotelName
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 116 -
Omejitve pogledov...
 Pri kreiranju in uporabi pogledov veljajo številne
omejitve:
– Če je stolpec A v pogledu V definiran z agregatno funkcijo:
 Stolpec A lahko nastopa le v SELECT ali ORDER BY sklopu v
stavkih, ki do pogleda V dostopajo;
 Stolpec A ne smemo uporabiti v WHERE sklopu niti ne sme
nastopati kot argument v agregatni funkciji poizvedbe nad
pogledom V.
V Accessu večina teh omejitev ne velja.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 117 -
Primeri
SELECT COUNT(hStRez)
FROM HotelRez;
SELECT *
FROM HotelRez
WHERE hStRez > 2;
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 118 -
Omejitve pogledov...
 Omejitve pogledov (nadaljevanje):
– Pogled, ki je kreiran z združevanjem (GROUP BY), ne smemo
stakniti z osnovno tabelo ali pogledom.
SELECT hNo, hName, hStRez
FROM HotelRez HR, Booking B
WHERE HR.hNo = B.hotelNo
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 119 -
Omejitve pogledov...
 Spreminjanje vsebine pogledov ni vedno možno
 Veljajo mnoge omejitve
 Primer
INSERT INTO HotelRez
VALUES (201, ‘HotelSlon’, 2);
 V osnovno tabelo Booking bi morali dodati dve
rezervaciji, ki se nanašati na hotel Slon. Nimamo
dovolj podatkov!
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 120 -
Omejitve pogledov...
 ISO standard določa, da je pogled možno
spreminjati samo, če veljajo naslednji pogoji:
– Opcija DISTINCT v pogledu ni uporabljena;
– Vsak element v SELECT seznamu stavka, ki definira pogled,
se nanaša na stolpec ene izmed osnovnih tabel; noben
stolpec se ne pojavi več kot enkrat;
– FROM sklop v pogledu se nanaša samo na eno tabelo ali
pogled, pri čemer pogled ne sme temeljiti na stiku, uniji,
preseku ali razliki;
– V pogledu ni vgnezdenih poizvedb;
– Sklopa GROUP BY in HAVING v pogledu nista uporabljena;
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 121 -
Uporaba WITH CHECK OPTION…
 Vrstice v pogledu obstajajo, ker izpolnjujejo
WHERE pogoj SELECT stavka, ki pogled definira.
 Če se vrstica spremeni in ne zadošča več pogoju,
izgine iz pogleda.
 Nove vrstice se v pogledu pojavijo le, če
zadoščajo WHERE pogoju.
 Vrstice, ki vstopijo ali zapustijo pogled,
imenujemo selitvene vrstice (migrating rows).
 WITH CHECK OPTION prepoveduje selitev vrstic
iz pogleda.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 122 -
Uporaba WITH CHECK OPTION…
 Za vpliv nad hierarhijo pogledov sta na voljo
opciji LOCAL in CASCADED.
 CASCADED (privzeto): nobeno dodajanje/
spreminjanje vrstice pogleda V ali vrstice
kateregakoli drugega pogleda, ki direktno ali
indirektno izhaja iz pogleda V, ne sme povzročiti,
da bi se ta vrstica, dodana ali spremenjena, selila
iz pogleda V.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 123 -
Uporaba WITH CHECK OPTION…
 LOCAL: nobeno dodajanje/spreminjanje vrstice
pogleda V ali vrstice kateregakoli drugega
pogleda, ki direktno ali indirektno izhaja iz
pogleda V, ne sme povzročiti, da bi se ta vrstica,
dodana ali spremenjena, selila iz pogleda V,
razen če se seli tudi iz nadrejene tabele ali
pogleda.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 124 -
Primer
CREATE VIEW DvoposteljneSobe
AS SELECT hotelNo, roomNo, type, price
FROM Room
WHERE type = 2
WITH CHECK OPTION;
 Ne moremo spremeniti sobe iz dvo v eno
posteljno, ker bi s tem povzročili selitev vrstice iz
pogleda.
 Ne moremo vnesti nove sobe v pogled, ki ne bi
bila dvoposteljna.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 125 -
Prednosti in slabosti pogledov
 PREDNOSTI
–
–
–
–
–
–
–
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
 SLABOSTI:
Podatkovna neodvisnost
Ažurnost
Večja varnost
Manjša kompleksnost
Udobnost
Prilagodljivost
Podatkovna celovitost
– Omejitve spreminjanja
– Omejitve strukture
– Slabša učinkovitost
- 126 -
Materializirani pogledi
 Postopek za izvedbo pogleda je lahko počasen,
še posebej pri pogostem dostopanju do pogleda.
 Materializirani pogled ob prvi uporabi shrani
pogled kot začasno tabelo za kasnejšo rabo.
 Ob kasnejši rabi večja učinkovitost.
 Problem vzdrževati ažurno stanje, če se osnovne
tabele pogosto spreminjajo.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 127 -
Vzdrževanje materializiranega pogleda
 Zagotavljanje ažurnosti v materializiranem
pogledu imenujemo vzdrževanje pogleda.
 Prenašamo samo tiste spremembe, ki so
potrebne, da je pogled ažuren.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 128 -
Vaja
 Napiši poizvedbo v SQL jeziku, ki bo izpisala
naziv hotela z najvišjo povprečno ceno hotelskih
sob.
SELECT H.hotelName, avg(R.price) AS AvgPrice
FROM Room AS R, Hotel AS H
WHERE R.hotelNo = H.hotelNo
GROUP BY R.hotelNo, H.hotelName
HAVING avg(R.price) = (SELECT max(AvgPrice) AS maxAvgPrice
FROM (SELECT R.hotelNo, avg(R.price) AS AvgPrice
FROM Room AS R
GROUP BY R.hotelNo))
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 129 -
Transakcije…
 SQL definira transakcijski model z ukazoma
COMMIT in ROLLBACK.
 Transakcija je logična enota dela z enim ali več
SQL ukazi. S stališča zagotavljanja skladnega
stanja je atomarna.
 Spremembe, ki so narejene znotraj poteka
transakcije, niso vidne navzven drugim
transakcijam, dokler transakcija ni končana.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 130 -
Transakcije...
 Transakcija se lahko zaključi na enega od štirih
načinov:
– Transakcija se uspešno zaključi s COMMIT; spremembe so
permanentne.
– Transakcija se prekine z ROLLBACK; spremembe, narejene s
transakcijo, se razveljavijo.
– Program, znotraj katerega se izvaja transakcija, se uspešno
konča. Transakcija je potrjena implicitno (brez COMMITa).
– Program, znotraj katerega se izvaja transakcija, se ne konča
uspešno. Transakcija se implicitno razveljavi (brez
ROLLBACKa).
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 131 -
Transakcije
 Nova transakcija se začne z novim SQL stavkom,
ki transakcijo inicira.
 SQL transakcij ne moremo gnezditi.
 Transakcijo nastavimo s pomočjo ukaza SET
TRANSACTION
SET TRANSACTION
[READ ONLY | READ WRITE] |
[ISOLATION LEVEL READ UNCOMMITTED |
READ COMMITTED|REPEATABLE READ
|SERIALIZABLE ]
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 132 -
Transakcije…
 READ ONLY – pove, da transakcija vključuje
samo operacije, ki iz baze berejo.
– SUPB bo dovolil INSERT, UPDATE in DELETE samo nad
začasnimi tabelami.
 ISOLATION LEVEL – pove stopnjo interakcije, ki
jo SUPB dovoli med to in drugimi transakcijami.
–
–
–
–
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
 Varen je samo način SERIALIZABLE (vrača
serializabilne urnike)
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 133 -
Transakcije…
 Kršenje serializabilnosti, dovoljene s strani
posameznega nivoja izolacije
– Dirty read: transakcija prebere podatek, ki je bil zapisan s
strani druge transakcije, čeprav je še nepotrjen.
– Nonrepeatable read: transakcija ponovno prebere podatek,
ki ga je predhodno že prebrala, če ga je neka druga potrjena
transakcija spremenila ali brisala v vmesnem obdobju.
– Phantom read: transakcija izvede poizvedbo, ki vrača
množico zapisov, ki ustrezajo iskalnem pogojem. Ko
transakcija ponovi poizvedbo kasneje, se v rezultatu pojavijo
dodatne vrstice, ki so bile v vmesnem času dodane s strani
druge potrjene transakcije.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 134 -
Transakcije…
Isolation level
Dirty Read
Nonrepeatable
read
Phantom read
READ
UNCOMMITTED
Y
Y
Y
READ
COMMITTED
N
Y
Y
REPEATABLE
READ
N
N
Y
SERIALIZABLE
N
N
N
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 135 -
Takojšnje in zapoznele omejitve...
 Včasih želimo, da se omejitve ne bi upoštevale
takoj, po vsakem SQL stavku, temveč ob
zaključku transakcije.
 Omejitve lahko definiramo kot
– INITIALLY IMMEDIATE – ob začetku transakcije;
– INITIALLY DEFERRED – ob zaključku transakcije.
 Če izberemo INITIALLY IMMEDIATE (privzeta
možnost), lahko določimo tudi, ali je zakasnitev
moč določiti kasneje. Uporabimo [NOT]
DEFERRABLE.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 136 -
Takojšnje in zapoznele omejitve
 Način upoštevanja omejitev za trenutno
transakcijo nastavimo z ukazom SET
CONSTRAINTS.
SET CONSTRAINTS
{ALL | constraintName [, . . . ]}
{DEFERRED | IMMEDIATE}
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 137 -
Nadzor dostopa...
 Ena od pomembnih nalog SUPB je zagotoviti
varnost dostopa do podatkovne baze.
 Večina današnjih SUPB omogoča eno ali obe od
naslednjih možnosti:
– Subjektivno določen nadzor dostopa (Discretionary access
control)
– Obvezen nadzor dostopa (Mandatory access control)
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 138 -
Nadzor dostopa...
 Subjektivno določen nadzor dostopa:
– vsak uporabnik ima določene dostopne pravice (privilegije)
nad dostopom do objektov podatkovne baze.
– Tipično uporabnik pravice dobi, ko kreira objekt.
– Pravice lahko posreduje drugim uporabnikom na osnovi
lastne presoje.
– Tak način nadzora je tvegan.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 139 -
Nadzor dostopa
 Obvezen nadzor dostopa:
– vsak objekt podatkovne baze ima določeno stopnjo
zaupnosti (npr. zaupno, strogo zaupno,...),
– vsak subjekt (uporabnik, program) potrebuje za delo z
objektom določeno raven zaupanja (clearance level).
– Za različne operacije (branje, pisanje, kreiranje,...) nad
objekti podatkovne baze lahko subjekti potrebujejo različne
nivoje zaupanja
– Ravni zaupanja so strogo urejene
– Značilno za varovana okolja, npr. vojska
– Eden znanih modelov takega nadzora je Bell-LaPadula
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 140 -
Nadzor dostopa in SQL...
 Vsak uporabnik podatkovne baze ima dodeljeno
določeno pooblastilo (authorisation), ki mu ga
dodeli skrbnik podatkovne baze (DBA
Administrator). Pooblastilo je obenem tudi
identifikator uporabnika.
 Navadno se za pooblastilo uporablja uporabniško
ime ter geslo.
 SQL omogoča preverjanje pooblastila, s čimer
identificira uporabnika.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 141 -
Nadzor dostopa in SQL...
 Vsak SQL stavek, ki ga SUPB izvede, se izvede na
zahtevo določenega uporabnika.
 Preden SUPB SQL stavek izvede, preveri
dostopne pravice uporabnika nad objekti, na
katere se SQL nanaša.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 142 -
Nadzor dostopa in SQL...
 Vsak objekt, ki ga z SQL-om kreiramo, ima
lastnika.
 Lastnika identificiramo na osnovi pooblastila, ki je
določeno v shemi, kateri objekt pripada, in sicer
v sklopu AUTHORIZATION
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 143 -
Nadzor dostopa in SQL...
 Dostopne pravice ali privilegiji določajo, kakšne
operacije so uporabniku dovoljene nad določenim
objektom podatkovne baze.
 SQL standard pozna naslednje pravice:
–
–
–
–
–
SELECT – pravica branja podatkov
INSERT – pravica dodajanja podatkov
UPDATE – pravica spreminjanja podatkov (ne tudi brisanja)
DELETE – pravica brisanja podatkov
REFERENCES – pravica sklicevanja na stolpce določene
tabela v omejitvah
– USAGE – pravica uporabe domen, sinonimov, znakovnih
nizov in drugih posebnih objektov podatkovne baze
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 144 -
Nadzor dostopa in SQL...
 Pravice v zvezi z dodajanjem (INSERT) in
spreminjanjem (UPDATE) tabel ali pogledov so
lahko določene na ravni stolpcev tabele/pogleda.
 Enako velja za pravice sklicevanja (REFERENCES)
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 145 -
Nadzor dostopa in SQL...
 Ko uporabnik kreira tabelo s CREATE TABLE
avtomatsko postane lastnik tabele z vsemi
pravicami.
 Ostalim uporabnikom dodeli pravice z ukazom
GRANT.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 146 -
Nadzor dostopa in SQL...
 Ko uporabnik kreira pogled s CREATE VIEW
avtomatsko postane lastnik, ne dobi pa nujno
vseh pravic.
 Za kreiranje pogleda potrebuje SELECT pravice
nad tabelami, iz katerih sestavlja pogled, ter
REFERENCES pravice nad tabelami, katerih
stolpce uporablja v definiciji omejitev.
 Ob kreiranju pogleda dobi pravice INSERT,
UPDATE in DELETE, če te pravice ima nad vsemi
tabelami, ki jih pogled zajema.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 147 -
Nadzor dostopa in SQL...
 Uporaba ukaza GRANT
GRANT {PrivilegeList | ALL PRIVILEGES}
ON ObjectName
TO {AuthorizationIdList | PUBLIC}
[WITH GRANT OPTION]
 PrivilegeList – je sestavljen iz ene ali več pravic,
ločenih z vejico (INSERT, UPDATE,...)
 ALL PRIVILEGES – dodeli vse pravice.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 148 -
Nadzor dostopa in SQL...
 PUBLIC – omogoča dodelitev pravic vsem
trenutnim in bodočim uporabnikom.
 ObjectName – se nanaša na osnovno tabelo,
pogled, domeno, znakovni niz, dodelitve in
prevedbe.
 WITH GRANT OPTION – dovoljuje, da uporabnik
naprej dodeljuje pravice.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 149 -
Primer dodeljevanja pravic...
 Vsem Vodjem oddelkov dodaj vse pravice nad
tabelo Delavec.
GRANT ALL PRIVILEGES
ON Delavec
TO VodjaOddelka WITH GRANT OPTION;
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 150 -
Primer dodeljevanja pravic
 Uporabnikom Osebje in Direktor dodeli SELECT in
UPDATE pravice nad stolpcem Plača v tabeli
Delavec.
GRANT SELECT, UPDATE (Plača)
ON Delavec
TO Osebje, Direktor;
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 151 -
Nadzor dostopa in SQL...
 Z ukazom REVOKE pravice odvzamemo
REVOKE [GRANT OPTION FOR]
{PrivilegeList | ALL PRIVILEGES}
ON ObjectName
FROM {AuthorizationIdList | PUBLIC}
[RESTRICT | CASCADE]
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 152 -
Nadzor dostopa in SQL...
 ALL PRIVILEGES določa vse pravice, ki jih je
uporabnik, ki REVOKE uporabi, dodelil
uporabniku ali uporabnikom, na katere se
REVOKE nanaša.
 GRANT OPTION FOR – omogoča, da se pravice,
ki so bile dodeljene prek opcije WITH GRANT
OPTION ukaza GRANT, odvzema posebej in ne
kaskadno.
 RESTRICT, CASCADE – enako kot pri ukazu
DROP
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 153 -
Nadzor dostopa in SQL...
 REVOKE ukaz ne uspe, kadar SUPB ugotovi, da bi
njegova izvedba povzročila zapuščenost
objektov:
– Za kreiranje določenih objektov so lahko potrebne pravice.
Če take pravice odstranimo, lahko dobimo zapuščene
objekte.
– Če uporabimo opcijo CASCADE, bo REVOKE ukaz uspel tudi
v primeru, da privede do zapuščenih objektov. Kot posledica
bodo ti ukinjeni.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 154 -
Nadzor dostopa in SQL...
 Če uporabnik Ua odvzema pravice uporabniku Ub
potem pravice, ki so bile uporabniku Ub
dodeljene s strani drugih uporabnikov, ne bodo
odvzete.
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 155 -
Primer odvzemanja pravic...
 Odvzemi SELECT pravice nad tabelo Delavec
vsem uporabnikom.
REVOKE SELECT
ON Delavec
FROM PUBLIC;
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 156 -
Primer odvzemanja pravic
 Uporabnikom iz skupine Osebje in Direktor
odvzemi vse pravice na tabelo Delavec.
REVOKE ALL PRIVILEGES
ON Delavec
FROM Osebje, Direktor;
PODATKOVNE BAZE
Modul: SQL
©Laboratorij za informatiko
- 157 -