Bazy_danych_w07

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

SQL

–

łą

czenie tabel,

podzapytania

i widoki

Wykład 7

20.11.2007

Antoni Dydejczyk

http://www.ftj.agh.edu.pl/~antek

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Plan wyk

adu

Łączenie tabel - powiązania krzyŜowe, wewnętrzne i zewnętrzne

Powiązania 1-1, 1-n i n-m w deklaracjach SQL

Polecenie JOIN: LEFT, RIGHT, FULL oraz INNER

Porównywanie zbiorów - słowa kluczowe

UNION, INTERSECT, EXCEPT

Zapytania podrzędne - powiązane i niepowiązane

Wirtualne tabele tworzone w standardzie SQL

widoki (perspektywy)

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Poprzedni wyk

Polecenie SELECT - uŜywamy do pobierania danych z bazy.

Struktura polecenia SELECT:

SELECT

opisuje nazwy kolumn, wyraŜenia arytmetyczne, funkcje

FROM

nazwy tabel lub widoków

WHERE

warunek (wybieranie wierszy)

GROUP BY

nazwy kolumn

HAVING

warunek (grupowanie wybieranych wierszy)

ORDER BY

nazwy kolumn lub pozycje kolumn

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Poprzedni wyk

Omówione słowa kluczowe :

DISTINCT

- wyszukanie nie powtarzających się wierszy

- wskazanie zbioru wartości dla warunku

BETWEEN

- wskazanie zakresu wartości dla warunku

- określenie wartości dla warunku

Omówione funkcje agregujące:

SUM(), AVG(), MIN(), MAX(), COUNT()

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Poprzednie wyk

ady

SQL

DDL

DML
DML

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Łą

czenie tabel

Powiązania krzyŜowe

Tworzą iloczyn kartezjański pomiędzy dwoma zbiorami danych.

Powiązania wewnętrzne

Tworzą podzbiór iloczynu kartezjańskiego pomiędzy dwoma zbiorami.
Wymagają klauzuli warunkowej w celu określenia kryteriów jakie
muszą być spełnione, aby rekordy były powiązane.

Powiązanie zewnętrzne

Podobnie do powiązań wewnętrznych tworzy podzbiór iloczynu
kartezjańskiego. RóŜnica polega na tym, Ŝe zwraca co najmniej jeden
egzemplarz kaŜdego wiersza z określonego zbioru. Jest to albo zbiór
pierwszy, albo drugi, względnie obydwa w zaleŜności od rodzaju
powiązania zewnętrznego. Brakujące wartości kolumn dla pustych
części wiersza, które nie spełniają powiązania, zwracane są jako
wartości NULL.

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Łą

czenie tabel

Standardowo:

SELECT lista_kolumn
FROM tabela [, tabela, ... ]
[ WHERE warunek ]

SELECT *
FROM tabela1, tabela2

--> iloczyn kartezjański

SELECT *
FROM tabela1, tabela2
WHERE tabela1.atr1 = tabela2.atr2

AND tabela1.atr12 = tabela2.atr22 ;

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Łą

czenie tabel

przyk

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Relacja jeden - do - jeden

Reprezentuje jedna tabela ( dwie )

osoba (id, nazwisko, imię)
opis_osoby (id, PESEL, NIP, kolor_oczu, ... )

powiązanie pomiędzy relacjami: osoba.id i opis_osoby.id

SELECT id, nazwisko, imię, PESEL, NIP
FROM osoba, opis_osoby
WHERE osoba.id = opis_osoba.id

AND nazwisko = ‘Kowalski’;

Łą

czenie tabel

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Łą

czenie tabel

przyk

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Łą

czenie tabel

przyk

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Łą

czenie tabel

Relacja jeden - do - wielu

Reprezentują dwie tabele

osoba (id, nazwisko, imię)
opis_osoby (id, PESEL, NIP, kolor_oczu, ... )
adres (ida, id, miasto, kod)

powiązanie pomiędzy tabelami: osoba.id, opis_osoby.id i adres.id

SELECT nazwisko, imię, miasto
FROM osoba, adres
WHERE osoba.id = adres.id

AND id = 1 ;

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Łą

czenie tabel

przyk

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Łą

czenie tabel

przyk

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Łą

czenie tabel

Relacja wiele - do - wielu

Reprezentują dwie tabele plus tabela łącząca

osoba (id, nazwisko, imię)
opis_osoby (id, PESEL, NIP, kolor_oczu, ... )
adres (ida, id, miasto, kod)
kamienica (idk, miasto, kod, ulica, numer,nazwa)
osoba_kamienica (id, idk)

SELECT O.nazwisko, K.nazwa, K.miasto
FROM osoba O, kamienica K, osoba_kamienica OK
WHERE O.id = OK.id AND K.idk = OK.idk

AND K.miasto = ‘Krakow’ ;

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Łą

czenie tabel

przyk

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Łą

czenie tabel

przyk

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Łą

czenie tabel

Łączenie tabel wykorzystujące słowo kluczowe – JOIN (standard SQL2).

Złączenia tabel - wewnątrz polecenia FROM

wewnętrzne

[INNER] JOIN

zewnętrzne

LEFT [OUTER] JOIN
RIGHT [OUTER] JOIN
FULL [OUTER] JOIN

Składnia

SELECT list_kolumn
FROM tabela1 JOIN tabela2 ON warunek1
WHERE warunek2

Uwaga:

Polecenia powyŜsze naleŜą do standardu SQL2 wobec tego nie są
reprezentowane we wszystkich implementacjach serwerów SQL.

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Łą

czenie tabel

przyk

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Łą

czenie tabel

przyk

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Łą

czenie tabel

przyk

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Łą

czenie tabel

przyk

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Łą

czenie tabel

przyk

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Łą

czenie tabel

przyk

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Łą

czenie tabel

przyk

SELECT
[Zrodlo].[id_nucl],

[Izotop].[id],

[Izotop].[nuclid],

[Izotop].[polokres],

[Izotop].[jedn],

[Zrodlo].[aktywnosc],

[Zrodlo].[typ_aktyw],

[Zrodlo].[data],

(

select data_aktywnosc from dane_rozne

) AS data_aktyw,

(

data_aktyw

-[Zrodlo].[data])*24 AS czas,

Exp(-Log(2)*((abs(

czas

)/2)/([izotop].[polokres]*(

select wsp_licz/wsp_mian

from jedn_t12 where jedn_t12.id_jedn=izotop.jedn

))) AS fact,

[zrodlo].[aktywnosc]*

fact

AS aktyw_nowa,

[osoba].[nazwisko],

[Aktywnosc].[typ_aktyw]

FROM ((Izotop INNER JOIN Zrodlo ON [Izotop].[id]=[Zrodlo].[id_nucl])

INNER JOIN Osoba ON [Zrodlo].[id_os]=[Osoba].[id])
INNER JOIN Aktywnosc ON

[zrodlo].[typ_aktyw]=[Aktywnosc].[id_aktyw];

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

owa kluczowe

–

UNION, INTERSECT, EXCEPT

Słowa kluczowe UNION, INTERSECT i EXCEPT słuŜą do łączenia lub pomijania
danych w wierszach. Wartości kolumn są porównywane i na podstawie tego
porównania tworzony jest nowy zestaw wyników. Słowa kluczowe uŜywamy
pomiędzy dwoma zapytaniami SQL. Zestawy wyników są porównywane i na
podstawie tego porównania wiersze są włączane lub pomijane do zestawu wyników.

UNION

Para zapytań połączonych słowem kluczowym UNION połączy wszystkie
wiersze zawierające wspólne identyfikatory w jeden wspólny zestaw wyników.

INTERSECTION

Para zapytań połączonych słowem INTERSECT spowoduje, Ŝe wiersze, których
nie moŜna znaleźć w obu zbiorach będą pominięte.

EXCEPT

Para zapytań połączonych słowem kluczowym EXCEPT spowoduje, Ŝe wiersze,
które znaleziono w obu zbiorach będą pominięte. W wyniku znajdą się tylko te
wiersze, które znajdują się po lewej stronie słowa kluczowego EXCEPT, a nie ma
ich po prawej stronie.

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Operator UNION

Operator umoŜliwia łączenie wyników kilku zapytań w
jednym zestawieniu. Wyniki pojawiają się jakby zostały
wybrane z jednej tabeli, podczas gdy pochodzą z wielu tabel.

Składnia:

SELECT lista_kolumn
FROM tabela [, tabela, ... ]
[ WHERE warunek ]

UNION [ ALL ]

SELECT list_kolumn
FROM tabela [, tabela, ... ]
[ WHERE warunek ]

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Operator UNION

osoba (id, nazwisko, imie)
adres (ida, id, miasto, ... )
kamienica (idk, miasto, ...)

SELECT miasto
FROM adres

UNION ALL

SELECT miasto
FROM kamienica ;

Sortowanie - automatycznie - od kolumny lewej

bez opcji ALL

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Operator UNION

przyk

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Operator UNION

przyk

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Operator UNION

przyk

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Operator INTERSECT

Operator umoŜliwia utworzenie zapytania zwracającego tylko
wiersze wspólne dla wybranych zapytań.

Składnia:

SELECT lista_kolumn
FROM tabela [, tabela, ... ]
[ WHERE warunek ]

INTERSECT

SELECT list_kolumn
FROM tabela [, tabela, ... ]
[ WHERE warunek ]

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

SELECT isbn FROM editions

INTERSECT

SELECT isbn FROM shipments

GROUP BY isbn HAVING count(id) > 2 ;

Isbn
------------
0001000210
1313332222
3333431414
4444444442

Operator INTERSECT

przyk

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Operator EXCEPT

Operator umoŜliwia utworzenie zapytania zwracającego
tylko wiersze, które występują w zbiorze po lewej stronie
operatora.

Składnia:

SELECT lista_kolumn
FROM tabela [, tabela, ... ]
[ WHERE warunek ]

EXCEPT

SELECT list_kolumn
FROM tabela [, tabela, ... ]
[ WHERE warunek ]

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

SELECT last_name, first_name FROM authors

EXCEPT

SELECT last_name, first_name

FROM authors AS a (author_id)
NATURAL INNER JOIN books
ORDER BY first_name ASC ;

Last_name |   first_name
--------------------------------
A...               |    A....
Z...                |    B....
C....               |    D....

Operator EXCEPT

przyk

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Podzapytania

(zapytania podrz

dne)

Co to jest podzapytanie ?

Najprościej, instrukcja SELECT zagnieŜdŜona w innej instrukcji SQL,
która dostarcza dla tej drugiej danych wejściowych.

Kwestię zapytań podrzędnych moŜna rozwaŜać w kilku
aspektach:

Typ powiązania pomiędzy zapytaniem nadrzędnym i podrzędnym,
Podzapytania mogą być wykorzystane w kilku typach instrukcji

języka SQL,

Wykorzystanie podzapytań zaleŜy typu danych zwracanych przez te

zapytania,

Część podzapytań moŜna zastąpić przez złączenie.

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

WyróŜniamy podzapytania:

niepowiązane - brak odwołań do zapytania nadrzędnego.

powiązane -

pozycje z listy SELECT zapytania
nadrzędnego wykorzystywane są
wewnątrz klauzuli WHERE podzapytania.

Podzapytania

(zapytania podrz

dne)

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Podzapytanie

niepowi

zane

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Podzapytanie

powi

zane

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Podzapytanie z klauzulą IN i odpowiednie zapytanie
zrealizowane złączeniem.

SELECT person_fname, person_lname

FROM People
WHERE person_id IN ( SELECT director_id FROM movies )
AND person_id IN (

SELECT DISTINCT cast_member_id FROM cast_movies ) ;

SELECT person_fname, person_lname

FROM Movies, Cast_Movies, People
WHERE

person_id = cast_member_id AND person_id = director_id ;

Podzapytania

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Podzapytanie z klauzulą NOT IN .

SELECT person_fname, person_lname

FROM People
WHERE person_id NOT IN ( SELECT director_id FROM movies )

SELECT DISTINCT person_fname, person_lname, movie_title

FROM People LEFT JOIN Movies

ON (People.person_id = Movies.director_id)

WHERE

Movies.movie_title IS NULL ;

Podzapytania

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Podzapytania

–

klauzule EXISTS, ANY, ALL

Klauzule stosowane dla podzapytań.

SELECT lista_kolumn FROM tabela
WHERE EXISTS ( podzapytanie)

EXISTS

jeŜeli podzapytanie zwraca dowolną wartość, to klauzula

EXISTS zwraca wartość logiczną prawda.

NOT EXISTS

–

wykorzystywana do znajdowania wierszy, dla których

powiązane podzapytanie nie zwraca Ŝadnych wartości.

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Podzapytania

–

klauzule EXISTS, ANY, ALL

Klauzule stosowane dla podzapytań.

SELECT lista_kolumn FROM tabela
WHERE warunek (ANY|ALL) ( podzapytanie )

ANY

wartość bieŜącego wiersza jest porównywana z kaŜdą

wartością podzapytania w oparciu o operator porównania.
JeŜeli jedno zwraca wartość prawdę, to całe porównanie jest
prawdą.

ALL

podobnie jak ANY, jednak jeŜeli jedno porównanie da fałsz, to

cała klauzula przyjmie wartość fałsz.

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Polecenie SELECT

przyk

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Podzapytania

–

realizacja INTERSECT

select miasto from adres ;

select miasto from kamienica ;

select miasto from adres
where miasto in

( select miasto from kamienica ) ;

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

SELECT S.studio_name, M.movie_title, (10000*M.gross) AS Receipts

FROM Movies M, Studios S
WHERE S.studio_id = M.studio_id

AND

( M.gross*10000) <
( SELECT SUM(payment) FROM Cast_Movies CM
WHERE M.movie_id = CM.movie_id ) ;

SELECT S.studio, M.movie_title, (10000*M.gross) AS Receipts,

SUM(CM.payment) AS PAYMENT

FROM Studios S, Movies M, Cast_Movies CM
WHERE S.studio_id = M.studio_id AND CM.movie_id = M.movie_id
GROUP BY M.studio_name, M.movie_title, M.gross
HAVING (M.gross*10000) < SUM(CM.payment) ;

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Widoki

Widok ( ang. viewed tables), wirtualne tabele tworzone w
standardzie SQL.

Widok tworzymy poleceniem CREATE VIEW
z istniejących tabel.

Korzyści płynące z widoków:

wygoda
ukrywanie efektów normalizacji
ograniczenie dostępu do danych
tworzenie warstwy abstrakcji

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Polecenie SELECT

przyk

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Wygoda

łą

czenia, funkcje agreguj

ce,

podzapytania

)

CREATE VIEW Movie_With_Studio

AS
SELECT movie_title, budget, gross, studio_name,

studio_city, studio_state

FROM Movies, Studios
WHERE Movies.studio_id = Studios.studio_id ;

SELECT movie_title, studio_name

FROM Movie_With_Studio ;

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

CREATE VIEW Cities_And_States (city,state)

AS
SELECT studio_city, studio_state

FROM Studios

UNION
SELECT person_city, person_state

FROM People

UNION
SELECT city, state

FROM Locations ;

SELECT * FROM Movie_With_Studio

Wygoda

łą

czenia, funkcje agreguj

ce,

podzapytania

)

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Ograniczenie dost

pu do danych

CREATE VIEW Movies_Minus_Finacials

AS
SELECT movie_id, studio_id, release_date,

director_id FROM Movies ;

SELECT * FROM Movies_Minus_Financials ;

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Tworzenie warstwy abstrakcji

Aliasy kolumn

CREATE VIEW Movies_Finacials ( title, budget, gross)

AS
SELECT movie_title, budget, gross FROM Movies ;

SELECT * FROM Movies_Financials ;

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Antoni Dydejczyk, Bazy danych, w08_04, 10.11.2004

Widok pojedynczej tabeli

CREATE VIEW Texas_Phone_List

AS
SELECT person_fname, person_lname, person_phone

FROM People

WHERE person_state = ‘TX’ ;

SELECT * FROM Texas_Phone_List ;

Tworzenie warstwy abstrakcji

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Wady:

Ograniczona moŜliwość modyfikacji

Ograniczenie struktury

Wydajność

Pojęcia:

Rozkład widoku

Materializacja widoku

Widoki

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Widoki

modyfikowanie

Uaktualnianie widoków jest moŜliwe, kiedy kaŜdy wiersz
widoku odpowiada dokładnie jednemu wierszowi w tabeli, lub
w tabelach na których został zbudowany.

Konstrukcje łamiące tą zasadę:

funkcje agregujące

operator DISTINCT

uŜycie klauzul GROUP BY i HAVING

wyraŜenia matematyczne

Antoni Dydejczyk, Bazy danych, wykład 7

WFiIS, Katedra Informatyki Stosowanej, 2007

Reguły standardu ANSI nie dopuszczają równieŜ modyfikacji
danych zawartych w widokach, które korzystają z
następujących konstrukcji:

odwołania do wielu tabel poprzez podzapytania, dowolny

typ złączenia ( łącznie z operatorami UNION )

odwołanie w widoku do innych niemodyfikowalnych

widoków

Widoki

modyfikowanie