POIZVEDOVANJE V SQL-2.del 13. April

Od prejšnič SQL

->Stavek SELECT....

-> naslednjič TRANSAKCIJE....

STIKI:

Ali poizvedovanje po več tabelah. Združujemo jih po tujih ključih.

2 načina uporabe stikov:

-uporabimo ukaze

-stik realiziramo s pogojem

Če pogoja ne napišemo imamo kartezični produkt.

Če pišemo poizvedbe v SELECT stavku v več tabelah uprabljamo sinonime za ime tabele. Sinonimi so nujni. V relacijah imamo lahko istoimenske atribute.

Primer:

􀂃 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.H.B.fromDate = ‘1.1.2005’ AND

B.toDate = ‘6.1.2005’ AND

H.hotelName = ‘Hilton New York’

Različni ukazi za poizvedovanje v različnih implementacijah SQLa.

Računaje stika:

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

 Dediciran ukaz: CROSS JOIN eksplicitno prevajalniku povemo, da je naša zelja narediti kartezični produkt.

Postopek za generiranje rezultata SELECT stavka s stikom:

1.     kartezijski produkt

2.     selekcija –izberemo ustrezne vrstice

3.     projekcija –izberemo ustrezne stolpce

4.     DISTINCT –eliminiramo dvojnike

5.     ORDER BY – razvrščanje po pogojih; ni obvezen

Zunanji stik:

S pomočjo zunanjega stika dobimo v rezultat tudi vrstice, ki nimajo stične vrednosti v drugi tabeli.

Sintaksa:

SELECT DISTINCT H.hotelName

FROM Hotel H LEFT JOIN

Booking B ON H.hotelNo = B. hotelNo

_ Kaj izpiše zgornja poizvedba ?

ODG:

Nazive hotelov + št. sob. Samo za tiste hotele kjer najdemo par v bookingu.

Gre za  levo odprti stik: izpišejo se vsi hoteli. če imajo rezervacije, se izpišejo še št. sob

Če bi imeli naraven stik bi se izpisali samo ustrezni hoteli.

Polni zunanji stik: / Full Outer Join

Vrne tudi tiste vrstice, ki v tabeli A in B nimajo stičnega para.

Sintaksa:

SELECT DISTINCT G.guestName, H.hotelName

FROM Hotel H FULL JOIN Guest G

ON H.address = G.guestAddress

EXISTS / NOT EXSISTS

Ukaza , ki vračata logičen rezultat. Samo True/False. Ne vračata nobenih numeričnih vrednosti. Preverja ali obstaja nekaj kot rezultat poizvedbe ali ne.

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

Primer:

Izpiši vse goste, ki so kdaj koli imeli rezervacije v

hotelu Hilton New York.

SELECT guestName, guestAddress

FROM Guest G

WHERE EXISTS          à Were true je zmeraj izpoljen, were false pa nikoli.

(SELECT *

FROM BookingB HotelH

Booking B, Hotel H

WHERE B.guestNo = G.guestNo AND

B.hotelNo = H.hotelNo AND   à Kaj dobimo, če ta pogoj izpustimo? to je klasičen stik med 2. tabelama. Če ga izpustimo dobimo kartezični produk. --zapisi iz 1. tabele se bodo združili z 2. tabelo. Exsist bo vedno vračal true.

H.hotelName = ‘Hilton New York’)

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’)

Stik časovno najbolj potretna relacija.

OPERACIJE NAD MNOŽICAMI:

-unija

-presek

-razlika

Domene atributov a in b tabele morajo biti enake.

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

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)

-Kako bo naziv stolpca, ki ga poizvedba vrne?

Polja morajo imeti enak pomen. V prvi relaciji so drugačna imen a kot pri drugi. Vzame se prva relacija. Odg: address

Unija z uporabo CORRESPONDING BY

(SELECT *

FROM Hotel)

UNION CORRESPONDING BY address

(SELECT *

FROM Guest)

 Address:            Hotel(hotelNo, hotelName, address)

Guest(guestNo, guestName, address)

Če polje ni enako poimenovano CORRESPONDING BY ne deluje!!!

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)

Presek z uporabo COORESPONDING BY

(SELECT *

FROM Hotel)

INTERSECT CORRESPONDING BY address  à Naredi presek, vendar zgolj po polju address

(SELECT *

FROM Guest)

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)

EXCEPT z uporabo COORESPONDING BY

(SELECT *

FROM Guest)

EXCEPT CORRESPONDING BY address

(SELECT *

FROM Hotel)

Address -> Hotel(hotelNo, hotelName, address)

Guest(guestNo, guestName, address)

INSERT stavek:

Izjemno močno poizvedovanje.

Za dodajanje karšnih koli zapisov v podatkovno bazo.  Paziti moramo na vrstni red stoplcev; na obvezne vrednosti. Je možnost dodajanja privzete vrednosti. (DEFAULT)

Sintaksa:

INSERT INTO TableName [ (columnList) ]

VALUES (dataValueList) à v istem vrstnem redu kot so imena stolpcev

[] à pomeni neobvezno.

􀂃 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).

Seznam dataValueList mora ustrezati seznamu

columnList:

Predstavlja vrednosti, ki jih vnašamo

– Š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.

Primer:

􀂃 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)

􀂃 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);

-Vnos več vrstic iz ene ali več drugih tabel...

INSERT INTO TableName [ (columnList) ]

SELECT ...

Če navedemo imena stolpcev, potem lahko določene vrdnosti izpustmo. Razen če niso obvezne.

Lahko naredimo tudi SELECT stavek in njegov rezultat vpišemo v tabelo.

Primer:

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

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

WHERE hotelNo NOT IN

(SELECT DISTINCT hotelNo

FROM Booking

WHERE B.dateFrom>=date()));

Kaj se zgodi če spodnji del stavka :

(SELECT hotelNo, hotelName, 0

FROM Hotel

WHERE hotelNo NOT IN

(SELECT DISTINCT hotelNo

FROM Booking

WHERE B.dateFrom>=date()));

izpustimo?

Odg: Prvi del nam ne vrne tistih hotelov v katerih ni nobene rezervacije. Zelimo da je tabela napolnjena z vsem hoteli. Umetno dodamo vse tiste ki nimajo nobene rezervacije. --> napišemo 0. 

DRUG NAČIN: Rešitev z Levo-Odprim stikom

UPDATE data 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.

Sprememba stoplcev v tabeli. WHERE skolp je neobvezen. –Predstavlja pogoje. Če WHERE-a ni, spreminjamo celotno tabelo. V imenovane stolpce se 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.

Primer:

Vse dvoposteljne sobe v hotelu z oznako 201 povišaj za 5%

UPDATE Room

SET price = price * 1,05

WHERE type = 2 AND hotelNo = 201

DN: V vseh hotelih zmanjšaj ceno najdražjih spb za 10% !

Ali lahko napišemo takšen UPDATE stavek v enem sklopu?

DELETE stavek:

Sintaksa:

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

Z DELETOM vedno brišemo VRSTICE. ( ne brišemo posameznih vrednosti.-->  te postavimo na NULL, za to uporabljamo UPDATE stavek.)

Primer:

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‘

)

STAVKI  za definicijo, tabel, ključev, omejitev.... (Stavki skupine SQL DDL)

Podatkovni tipi:

So imaginarni. Vsak SUPB jih imaginira posvoje.

􀂃 DDL skupina zajema SQL stavke za manipulacijo

s strukturo podatkovne baze.

􀂃 Vsebina:

– 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 feature ALTER TABLE stavkih?

– Kako kreirati in brisati poglede z SQL-om?

– Kako SUPB izvaja operacije nad pogledi?

– Pod kakšnimi pogoji so pogledi spremenljivi?

– Prednosti in slabosti pogledov

– Način delovanja ISO transakcijskega modela

– Uporaba GRANT in REVOKE stavkov v okviru zagotavljanja varnosti

INTEGRITY ENHANCEMENT FEATURE

Zagotavljanje skladnosti podatkov.  Med omejitve, ki nam določajo skladnost določamo:

– 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)

Da gre za obvezen podatek, navdedemo ko kreiramo tabelo.

Primeri: Glej prosojnice!!! NOT NULL == podatek je obvezen (229/115)

Domene lahko tudi ukinemo, Uporabimo ukaz DROP.

Sintaksa:

DROP DOMAIN DomainName

[RESTRICT | CASCADE]

Celovitost entitet lociramo s primarnim ključem.

Vsaka tabela ima lahko natanko 1 primarni ključ. Obvezno mora biti enoličen.

Ukaz UNIQUE: Enoličnost neosnovnih stolpcev lahko

zagotavljamo z uporabo UNIQUE

OMEJITVE POVEZAV:

Celovitost povezav zagotavlja, da če ima FK ( tuji ključ)  neko vrednost, potem se ta vrednost nahaja v primarnem ključu povezane tabele.

Rediferecialna integriteta: Ne dovoli da nekaj vnesemo, če ne obstaja.  Lahko zagotovimo da se povezave ne uničijo. 

Ponavadi SUPBji realizirajo več možnosti.

4 možnosti:

– CASCADE

– SET NULL

-SET DEFAULT

– NO ACTION

Povezava med tabelami se ne sme zgubiti.

SPLOŠNE OMEJITVE:

Splošne omejitve določimo z CHECK/UNIQUE opcijami v CREATE in ALTER TABLE stavkih.

CREATE ASSERTION AssertionName

CHECK (searchCondition)

Primer: Glej prosojnice!!!

SQL DDL omogoča kreiranje in brisanje

podatkovnih objektov, kot so: shema, domena,

tabela, pogled in indeks.

􀂃 Glavni SQL DDL stavki so:

CREATE SCHEMA DROP SCHEMA

CREATE/ALTER DOMAIN DROP DOMAIN

CREATE/ALTER TABLE DROP TABLE

CREATE VIEW DROP VIEW

􀂃 Mnogi SUPB-ji omogočajo tudi

CREATE INDEX DROP INDEX

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.

KREIRANJE SHEME:

Sintaksa :

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 si objekti

􀂃 CASCADE: brišejo vsi objekti,povezani s shemo. Če katerokoli brisanje ne

uspe, se zavrne celotna operacija.

KREIRANJE TABELE

Postopek:

-ime tabele

-ime stolpca in njegov podatkovni tip

} -à zaključi navajanje stolpcev

Sintaksa:

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)] [,…] })

Za množico stolpcev lahko zahtevamo da so enolični. Ko smo jih navedli še 1x povemo UNIQUE.

- določimo tuje ključe

-znotraj tujega ključa povemo diferencialno itengriteto

Primer: 1.) Kreiranje domen :

CREATE DOMAIN hotelNumber AS NUMERIC(3)

CHECK (VALUE IN (SELECT hotelNo FROM Hotel));

Najprej kreiramo domene

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);

        

2.) kreiranje tabele

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 …);

ALTER TABLE stavek:

S njim spreminjamo tabelo. Dodajamo ali ukinjamo stolpce v tabeli. Dodajamo ali ukinemo omejitve tabele;

– Za stolpce v tabeli določamo ali ukinjamo privzete vrednosti;

-Spreminjamo podatkovne tipe stoplcev v tabeli;

Primer:

􀂃 Spremeni tabelo Booking tako, da ukineš privzeto vrednost stolpca fromDate.

ALTER TABLE Booking

ALTER fromDate DROP DEFAULT;

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’;

Stavek DROP TABLE:

Izbriše celotno tabelo.  Tudi celotno strukturo.

Sintaksa:

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;