Transcript Document

Osnove SQL
Prof. dr MILORAD K. BANJANIN
Univerzitet u Istočnom Sarajevu
Tehnološki fakultet Zvornik
OPISIVANJE REALNOG SVETA
OBJEKAT
POSMATRANJA
PODACI PREDSTAVLJAJU VREDNOSTI POJEDINIH
ATRIBUTA KOJI SE UNOSE U MEMORIJU
BAZA PODATAKA
zbirka uzajamno povezanih podataka, koji su memorisani sa
kontrolisanom redundansom, da bi optimalno služili različitim
aplikacijama
podaci su memorisani nezavisno od programa koji ih koriste
za dodavanje novih podataka i modifikovanje ili pretraživanje postojećih
podataka koriste se zajednicki i kontrolisani pristupi
Data
Definition
Language
SISTEM ZA UPRAVLJANJE
BAZAMA PODATAKA (SUBP)
DDL
memorisanje i
odrzavanje
podataka
STRUKTURA
Data Manipulation Language
DML
manipulisanje
podacima
Logička (logički pogled na bazu podataka - model podataka i
model procesa)
Fizička (interni pogled na podatke)
RELACIONA BAZA PODATAKA
odnosi između podataka su u obliku dvodimenzionih tabela
K O L O N E sadrže samo vrednosti jednog domena
R E D O V I se razlikuju između sebe
K LJ U Č R E L A C I J E predstavlja atribut koji jednoznačno
identifikuje red
KRITERIJUMI ZA OCENU RELACIONIH SUBP-a
1
Struktura baze podataka se na logičkom nivou predstavlja samo tabelama
2
Svaki podatak u relacionoj bazi podataka dostupan je preko kombinacije
imena tabele (relacije), vrednosti primarnog ključa i imena kolne (atributa)
3
Specifičan indikator (razlicit od "praznog" niza karaktera, nule ili bilo kog
broja) koristi se za predstavljanje nula vrednosti, bez obzira na tip podatka
4
Sistem poseduje katalog (rečnik) podataka koji se logički predstavlja
na isti način kao i sama baza podataka - korisnik može da koristi isti
jezik da bi pristupio tim meta podacima
5
Bez obzira koliko jezika i načina korišćenja sistem podržava, mora
posedovati barem jedan jezik čije se naredbe mogu izraziti kao niz
karaktera sa dobro definisanom sintaksom i koji podržava:
(1) definiciju podataka
(2) definiciju pogleda
(3) manipulaciju podatka, interaktivno i kroz programe
(4) definiciju pravila integriteta
(5) autorizaciju (sigurnost)
(6) granice transakcija (BEGIN, COMMIT, ROLLBACK)
6
Sistem poseduje efikasan algoritam pomoću koga može da odredi, za
svaki definisani pogled, u trenutku njegovog definisanja, da li se i koje
operacije održavanja mogu primeniti na taj pogled – čiji rezultat se
smešta u katalog baze podataka
7
Nad baznim relacijama i nad pogledima mogu se izvršavati ne samo
operacije pretraživanja, već i operacije održavanja baze podataka
8
Aplikacioni program i interaktivna komunikacija ostaju neizmenjeni kada se
promeni fizička organizacija baze ili fizički metod pristupa
9
Aplikacioni program i interaktivna komunikacija ostaju nepromenjene
kada se bilo koje promene, koje ne menjaju odgovarajući sadržaj
tabele, unesu u baznu tabelu
10
Pravila integriteta se definišu u okviru definicije baze podataka i čuvaju
se u katalogu - ne implementiraju se kroz aplikacione programe
11
Sve navedene karakteristike su nezavisne od distribucije baze podataka
12
Ako relacioni sistem poseduje ili može da radi sa nekim jezikom treće
generacije u kome se obrađuje jedna n-torka u jednom trenutku
vremena, kroz taj jezik se ne mogu zaobići pravila integriteta zadana
preko samog relacionogjezika
Structured Query Language standardni relacioni upitni jezik
jezik za direktan intereaktivni pristup
bazi podataka
Sastoji se iz: DDL (Data Definition Language) i
DML (Data Manipulation Language) naredbi
DDL naredbe
CREATE
ALTER
DROP
Mogu se kreirati/promeniti/obrisati
tabele
indeksi
pogledi
Postoje i drugi objekti, ali su specifični za svakog
proizvođača (sekvence, tablespace-ovi, itd.)
TIPOVI PODATAKA
INTEGER, NUMBER(dužina,dec), NUMERIC, DECIMAL
brojevi (celobrojni, razlomci)
nisu floating point!
FLOAT, DOUBLE
floating point brojevi
CHAR(dužina), VARCHAR(dužina)
stringovi fiksne i varijabilne dužine
dužina nije impozantna!
DATE, TIME, TIMESTAMP
datum, vreme i datum sa vremenom
BOOLEAN, BIT
boolean tip podatka
BLOB, CLOB, TEXT
Binary Large OBject, za smeštanje binarnih objekata (slika, filmova, itd.)
TEXT se koristi za smeštanje velike količine teksta
KREIRANJE/IZMENA/BRISANJE TABELA
Kreiranje tabele - Opšta sintaksa
CREATE TABLE ime_tabele
(obl1
tip_podatka
[, obl2
tip_podatka
[NULL | NOT NULL]
[NULL | NOT NULL]...])
PRIMER
CREATE TABLE PROFESORI
(PROFESOR_ID
INTEGER
IME
VARCHAR(25)
PREZIME
VARCHAR(35)
ZVANJE
VARCHAR(15)
Izmena tabele (definicije) - Opšta sintaksa
ALTER TABLE ime_tabele
ADD|MODIFY|DROP parametri
not null,
not null,
not null,
not null)
Dodavanje novih kolona (ADD) - Opšta sintaksa
ALTER TABLE ime tabele
ADD (obl1
tip_podatka [NULL | NOT NULL]
[, obl2 tip_podatka [NULL | NOT NULL)]...])
PRIMER
ALTER TABLE PROFESORI
ADD (Plata NUMBER(5,2))
Izmena tabele (MODIFY) - Opšta sintaksa
ALTER TABLE ime_tablele
MODIFY (obl1 tip_podatka [NULL | NOT NULL]
[, obl2 tip_podatka [NULL | NOT NULL)]...])
PRIMER
ALTER TABLE PROFESORI
MODIFY (Plata NUMBER(7,2))
Menja tip, dužinu,
NULL/NOT NULL
Potencijalno
ogromni problemi
zbog konverzije
tipova - nekad i
nemoguće (zbog
primarnih ključeva,
spoljnih ključeva,
itd.)
Izbacuje kolonu - Ponekad nemoguće (zbog primarnih
ključeva, stranih ključeva, itd.)
Izmena tabele (DROP) - Opšta sintaksa
ALTER TABLE ime_tabele
DROP [COLUMN] ime_kolone
PRIMER
ALTER TABLE PROFESORI
DROP Plata
Uklanjanje tabela - Opšta sintaksa
DROP TABLE ime_tabele
PRIMER
DROP TABLE PROFESORI
KREIRANJE/PROMENA/BRISANJE INDEKSA
Kreiranje indeksa - Opšta sintaksa
Kreira indeks nad
kolonom/kolonama
CREATE [UNIQUE] INDEX ime_indeksa
ON ime_tabele (obl1 [ASC|DESC]
[, obl2 [ASC|DESC]...])
PRIMER
CREATE UNIQUE INDEX PROFESORI_KEY
ON PROFESORI (PROFESOR_ID)
Uklanjanje indeksa - Opšta sintaksa
DROP INDEX ime_indeksa
PRIMER
DROP INDEX PROFESORI_KEY
DML naredbe
INSERT
SELECT
UPDATE
DELETE
Ubacivanje novih podataka u tabelu (INSERT)
Opšta sintaksa 1
Ubacuju se ili kompletne
kolone ili samo one koje
moraju da budu unete
(NOT NULL)
INSERT INTO ime_tabele (obl1 [, obl2, ...])
VALUES (vred1 [, vred2 ...])
Opšta sintaksa 2
INSERT INTO ime_tabele
VALUES (vred1 [, vred2 ...])
PRIMER
INSERT INTO PROFESORI
VALUES (1, ‘Petar’, ‘Petrović’, ‘Docent’)
Pretraživanje baze podataka (SELECT)
Opšti oblik (obavezni elementi)
SELECT {* | obl [, obl2 ...]}
FROM ime_tabele
Opšti (prošireni) oblik
Ako se ne navede,
podrazumeva se ALL, tj.
svi redovi, bez obzira na
duplikate
SELECT [ALL|DISTINCT] {* | obl [alias] [, obl2 [alias2] ...]}
FROM tabela [alias] [, tabela2 [alias2]...]
[WHERE uslov ]
[ORDER BY obl [ASC|DESC] [, obl2 [ASC|DESC]...]]
[GROUP BY obl [, obl2...] [HAVING uslov]]
PRIMER
Prikazati sva zvanja u tabeli PROFESORI (bez duplikata)
SELECT DISTINCT ZVANJE FROM PROFESORI
Selekcija željenih slogova – WHERE klauzula
Zadaje se uslov iza WHERE klauzule
Unutar uslova se mogu koristiti sledeći operatori
=
!=
> >= <=
NOT IN
IN
[NOT] BETWEEN x AND y
EXISTS
[NOT] LIKE
IS [NOT] NULL
NOT
AND
OR
jednako
nije jednako
veće, veče ili jednako,...
nije unutar liste
unutar liste
[nije] između x i y
ako postoji barem jedan slog
[nije] poput zadatog stringa
[nije] NULL
negacija
logičko I
logičko ILI
PRIMER
Svi profesori čija plata je između 2000 i 3000 KM
SELECT * FROM PROFESORI WHERE PLATA BETWEEN 2000 AND 3000
Funkcije
Postoji veliki broj funkcija koje operišu nad podacima unutar izraza
AVG(obl)
SUM(obl)
MIN(obl)
MAX(obl)
COUNT({*|[DISTINCT]obl})
ABS(broj)
MOD(br1, br2)
POWER(br, e)
LENGTH(str)
UPPER(str)
SUBSTRing(str, pos [, d])
CONCAT(str1, str2, str3, ...)
YEAR(obl)
srednja vrednost
suma svih obeležja
minimalna vrednost
maksimalna vrednost
broj pojavljivanja
apsolutna vrednost
br1 po modulu br2
br na e-ti stepen
dužina stringa
u velika slova
podstring od str, sa poz. pos
konkatenacija stringova
vraća godinu iz datuma
PRIMER
Naći sumu svih plata za docente
SELECT SUM(PLATA) FROM PROFESORI WHERE ZVANJE=‘Docent’
Izmena podataka (UPDATE)
Omogućuje izmenu podataka u tabeli
Opšta sintaksa
UPDATE ime_tabele
SET obl1=vred1 [,obl2=vred2...]
[WHERE uslov]
Bez WHERE klauzule menjaju se
podaci u celoj tabeli, a upotrebom
WHERE klauzule se precizira koji
slog se želi izmeniti
PRIMER
UPDATE PROFESORI
SET ZVANJE=‘Docent’
WHERE PROFESOR_ID=1
Brisanje slogova (DELETE)
Briše slogove u tabeli
Opšta sintaksa
DELETE FROM ime_tabele
[WHERE uslov]
Bez WHERE klauzule se brišu
podaci u celoj tabeli, a upotrebom
WHERE klauzule se precizira koji
slog se želi brisati
PRIMER
DELETE FROM PROFESORI
WHERE PROFESOR_ID=1
Primarni ključ (PRIMARY KEY)
Definiše se prilikom definisanja tabele
Opšta sintaksa
CONSTRAINT ime_ograničenja PRIMARY KEY (obl1 [, obl2, ...])
PRIMER
CREATE TABLE RADNA_MESTA (
RADNO_MESTO_ID INTEGER NOT NULL,
NAZIV VARCHAR(20),
CONSTRAINT PK_RADNO_MESTO PRIMARY KEY (RADNO_MESTO_ID)
)
Veze
Referencijalni integritet garantuje da će podaci u tabelama koje su
povezane imati smisla
PRIMER
Radnik  Radno mesto
ako se ne definiše referencijalni integritet postoji
problem kod: dodavanja, brisanja i izmene
Strani ključ (FOREIGN KEY)
Definiše se prilikom kreiranja tabele
Opšta sintaksa
CONSTRAINT ime_ograničenja FOREIGN KEY (obl1)
REFERENCES tabela_na_koju_se_referencira (obl_iz_strane_tabele)
[ON UPDATE akcija] [ON DELETE akcija]
PRIMER
CREATE TABLE RADNICI (
RADNIK_ID INTEGER NOT NULL,
IME VARCHAR(20),
RADNO_MESTO_ID INTEGER,
CONSTRAINT PK_RADNIK PRIMARY KEY (RADNIK_ID),
CONSTRAINT FK_RADNIK_RADNO_MESTO FOREIGN KEY
(RADNO_MESTO_ID) REFERENCES RADNA_MESTA
(RADNO_MESTO_ID)
)
Upiti nad više tabela
Tabele se mogu “kombinovati” spajanjem, koje se vrši
navođenjem u FROM klauzuli
Ako se ne precizira, radi se Dekartov proizvod tabela (“svaki sa svakim”)
PRIMER
SELECT * FROM RADNICI, RADNA_MESTA
Spajanje se vrši po paru (spoljni ključ prve tabele, primarni ključ
druge tabele)
PRIMER
izlistati imena svih profesora i nazive predmeta koje predaju
SELECT A.IME, A.PREZIME, B.NAZIV FROM PROFESORI A,
PREDMETI B, PREDAJE C
WHERE A.PROFESOR_ID=C.PROFESOR_ID AND
B.PREDMET_ID=C.PREDMET_ID
Spojevi (Joins)
u SQL
Spojevi
PRIMER
Postoje dve tabele u bazi podataka:
Proizvodi (Naziv, Cena, Kategorija, Proizvodjac)
Kompanija (Ime, Akcija, Zemlja)
Potrebno je pronaći sve proizvode jeftinije od 200$ proizvedene u
Japanu i selektovati njihova imena i cene
SELECT Naziv, Cena
FROM
Proizvod, Kompanija
WHERE Proizvodjac=Ime AND Zemlja=‘Japan’ AND Cena <= 200
Veza između tabela Proizvod i Kompanija
Proizvodi
Naziv
Cena
Kategorija
Proizvodjac
Windows XP
$119.99
Software
PhotoShop
CS
$429.99
Software
Matična
ploča
$149.99
Hardware
Asus
Monitor
$253.99
Hardware
Samsung
Kompanija
Ime
Akcija
Zemlja
Microsoft
Microsoft
25
USA
Adobe
Adobe
65
USA
Asus
15
Japan
Naziv
Cena
Matična ploča
$149.99
Značenje (Semantika) SQL upita
SELECT a1, a2, …, ak
FROM
R1 AS x1, R2 AS x2, …, Rn AS xn
WHERE Uslov
1. Ugneždene petlje
Odgovor = {}
for x1 in R1 do
for x2 in R2 do
…..
for xn in Rn do
if Uslov
then Answer = Answer  {(x1,…,xk)}
return Answer
2. Paraleleni zadaci
Answer = {}
for all assignments x1 in R1, …, xn in Rn do
if Conditions then Answer = Answer  {(x1,…,xk)}
return Answer
Ugnježdeni upiti
Upiti kod kojih je jedna SELECT naredba ugrađena u WHERE
klauzulu druge SELECT naredbe
Predikatski izrazi: IN, ANY, ALL, EXISTS
IN
Operator IN podrazumeva da je levi operand unutar liste
navedene kao desni operand ovog operatora
PRIMER
Prikazati najbolje plaćene radnike po radnim mestima
SELECT IME, PLATA
FROM RADNICI
WHERE (RADNO_MESTO_ID, PLATA) IN (SELECT RADNO_MESTO_ID,
MAX(PLATA) FROM RADNICI GROUP BY RADNO_MESTO_ID)
ANY
Operator ANY podrazumeva bilo koju vrednost iz
ugnježdenog upita da ispunjavaju uslov
Izraz =ANY je ekvivalentan IN izrazu
PRIMER
Prikazati radnike koji zarađuju više od proseka
SELECT IME, PLATA
FROM RADNICI
WHERE PLATA > ANY (SELECT AVG(PLATA) FROM RADNICI)
ALL
Operator ALL podrazumeva sve vrednosti iz ugnježdenog
upita da ispunjavaju uslov
Izraz != ALL ekvivalentan NOT IN izrazu
PRIMER
Prikazati radnike koji imaju platu veću od svih radnika
SELECT IME FROM RADNICI
WHERE PLATA >= ALL (SELECT PLATA FROM RADNICI)
EXISTS
Proverava da li rezultujuća tabela ima barem jedan red
PRIMER
Prikazati ona radna mesta na kojima nema ni jednog
radnika
SELECT * FROM RADNA_MESTA
WHERE NOT EXISTS (
SELECT * FROM RADNICI WHERE
RADNICI.RADNO_MESTO_ID=RADNA_MESTA.RADNO_MESTO_ID)
SELECT A.RADNO_MESTO_ID, A.NAZIV
FROM RADNA_MESTA A, RADNICI B
WHERE
A.RADNO_MESTO_ID = B.RADNO_MESTO_ID (+) AND
B.RADNIK_ID IS NULL
SELECT RADNA_MESTA.RADNO_MESTO_ID, RADNA_MESTA.NAZIV
FROM RADNA_MESTA LEFT JOIN RADNICI
ON RADNA_MESTA.RADNO_MESTO_ID=RADNICI.RADNO_MESTO_ID
WHERE RADNICI.RADNIK_ID IS NULL
HVALA NA
PAŽNJI
Prof. dr Milorad Banjanin