plik

Bazy danych

Jan Kazimirski

Baza danych 'wypaut'

KLIENCI

NR_KLIENTA

IMIE
NAZWISKO
KARTA
FIRMA
ULICA
DOM
MIASTO
KOD
NIP
TEL

SAMOCHODY

NR_SAMOCODU

MARKA
MODEL
ROK_PRODUKCJI
KOLOR
POJ_SILNIKA
PRZEBIEG

PRACOWNICY

NR_PRAC

IMIE
NAZWISKO
DATA_ZATR
DZIAL
STANOWISKO
PENSJA
DODATEK

NR_MIEJSCA

TEL

MIEJSCA

NR_MIEJSCA

ULICA
DOM
MIASTO
KOD
TEL
UWAGI

WYPOZYCZENIA

NR_WYPOZYCZENIA

NR_KLIENTA
NR_SAMOCHODU
NR_PRAC_WYD
NR_PRAC_ODD
NR_MIEJSCA_WYD
NR_MIEJSCA_ODD

DATA_WYD
DATA_ODD
KAUCJA
CENA_JEDN

PRIMARY KEY (PK)

FOREIGN KEY (FK)

Bazy danych

Jan Kazimirski

Łączenie tabel

●

Ze względu na normalizację szukane dane mogą
być umieszczone w kilku tabelach

●

SQL pozwala na wybieranie danych z kilku tabel
jednocześnie

●

Złączenie tabel pozwala na ukrycie normalizacji i
prezentowanie danych z kilku tabel w spójnej

postaci

Bazy danych

Jan Kazimirski

Iloczyn kartezjański

●

Poniższa instrukcja wygeneruje iloczyn
kartezjański zbiorów krotek z relacji pracownicy i

miejsca.

select pracownicy.nazwisko, miejsca.miasto from

pracownicy,miejsca;

Bazy danych

Jan Kazimirski

Iloczyn kartezjański c.d.

NAZWISKO

...

MIASTO

...

NAZWISKO

...

MIASTO

...

Bazy danych

Jan Kazimirski

Iloczyn kartezjański c.d.

KOWALSKI

NOWAK

WARSZAWA

KRAKÓW

KOWALSKI

NOWAK

KOWALSKI

NOWAK

WARSZAWA

KRAKÓW

Bazy danych

Jan Kazimirski

Warunek złączenia

●

Iloczyn kartezjański składa się z wszystkich
możliwych kombinacji krotek. Zwykle nie o to nam

chodzi.

●

Aby ze zbioru wszystkich możliwych kombinacji

krotek wybrać tylko te, które nas interesują,
potrzebny jest

WARUNEK ZŁĄCZENIA

Bazy danych

Jan Kazimirski

Warunek złączenia c.d.

KOWALSKI

NOWAK

WARSZAWA

KRAKÓW

KOWALSKI

NOWAK

KOWALSKI

NOWAK

WARSZAWA

KRAKÓW

Bazy danych

Jan Kazimirski

Warunek złączenia z użyciem

WHERE

●

Połączenie dwóch tabel – pracownicy i miejsca:

select pracownicy.nazwisko,miejsca.miasto

from pracownicy,miejsca
where

pracownicy.nr_miejsca=miejsca.nr_miejsca;

●

Warunkiem złączenia jest równość atrybutów
miejsca.nr_miejsca (klucz główny) i
pracownicy.nr_miejsca (klucz obcy)

Bazy danych

Jan Kazimirski

Wybór kolumn

●

W złączeniach zdarza się, że kolumny kilku tabel
mają takie same nazwy. Aby się do nich

odwoływać należy używać nazw kwalifikowanych
w postaci nazwa_tabeli.nazwa_kolumny:

pracownicy.

nazwisko

miejsca.

miasto

samochody.

kolor ...

Bazy danych

Jan Kazimirski

Skracanie nazw tabel

●

Za nazwą tabeli można podać jej alias. Za jego
pomocą można odwoływać się do jej kolumn:

select

nazwisko,

miasto

from pracownicy

,miejsca

where

nr_miejsca=

nr_miejsca;

Bazy danych

Jan Kazimirski

Użycie JOIN

●

Zamiast używać WHERE do złączeń można
wykorzystać polecenie JOIN

●

Użycie JOIN jest znacznie elastyczniejsze niż
WHERE

●

Użycie JOIN nie wyklucza użycia WHERE do
filtrowania generowanych rezultatów

Bazy danych

Jan Kazimirski

CROSS JOIN

●

CROSS JOIN jest równoznaczne z iloczynem
kartezjańskim zbiorów – tzn. generuje wszystkie

kombinacje złączeń krotek.

select p.nazwisko,m.miasto

from pracownicy p
cross join miejsca m;

Bazy danych

Jan Kazimirski

Składnia JOIN

●

Istnieje kilka alternatywnych składni złączenia z
użyciem JOIN – zależnie od budowy tabel

–

SELECT ... FROM T1 JOIN T2 ON CON

–

SELECT ... FROM T1 JOIN T2 USING(C)

–

SELECT ... FROM T1 NATURAL JOIN T2

gdzie T1,T2 – tabele, C – kolumna łącząca, CON

– warunek złączenia

Bazy danych

Jan Kazimirski

JOIN ... ON

●

Najbardziej ogólny sposób wywołania:

select p.nazwisko,m.miasto
from pracownicy p

join

miejsca m

p.nr_miejsca=m.nr_miejsca;

Bazy danych

Jan Kazimirski

JOIN ... USING ...

●

Zakłada, że kolumna (lub kolumny)
wykorzystywana jako warunek złączenia ma w

obu tabelach taką samą nazwę:

select p.nazwisko,m.miasto
from pracownicy p join miejsca m
using(nr_miejsca);

Bazy danych

Jan Kazimirski

NATURAL JOIN

●

Zakłada, że kolumna (lub kolumny) używana jako
warunek złączenia ma taką samą nazwę w obu

tabelach i że nie ma w nich żadnych innych
kolumn o takich samych nazwach:

select p.nazwisko,m.miasto
from pracownicy p natural join miejsca m;

UWAGA! Podany przykład nie da poprawnych wyników z
powodu kolumny tel obecnej w obu tabelach

Bazy danych

Jan Kazimirski

Typy złączeń

●

INNER JOIN – złączenie wewnętrzne

●

LEFT OUTER JOIN – złączenie zewnętrzne
lewostronne

●

RIGHT OUTER JOIN – złączenie zewnętrzne
prawostronne

●

FULL OUTER JOIN – złączenie zewnętrzne

Bazy danych

Jan Kazimirski

Złączenie wewnętrzne

KOWALSKI

NOWAK

KAMIŃSKI

WARSZAWA

WROCŁAW

KRAKÓW

KOWALSKI

WARSZAWA

NOWAK

KRAKÓW

select *

from t1
inner join t2

using(id);

Bazy danych

Jan Kazimirski

Lewostronne złączenie zewnętrzne

KOWALSKI

NOWAK

KAMIŃSKI

WARSZAWA

WROCŁAW

KRAKÓW

KOWALSKI

WARSZAWA

NOWAK

KRAKÓW

select *

from t1
left outer join t2

using(id);

KAMIŃSKI

null

Bazy danych

Jan Kazimirski

Prawostronne złączenie zewnętrzne

KOWALSKI

NOWAK

KAMIŃSKI

WARSZAWA

WROCŁAW

KRAKÓW

KOWALSKI

null

select *

from t1
right outer join

t2 using(id);

NOWAK

WARSZAWA

WROCŁAW

KRAKÓW

Bazy danych

Jan Kazimirski

Złączenie zewnętrzne

KOWALSKI

NOWAK

KAMIŃSKI

WARSZAWA

WROCŁAW

KRAKÓW

KOWALSKI

null

select *

from t1
full outer join t2

using(id);

NOWAK

WARSZAWA

WROCŁAW

KRAKÓW

KAMIŃSKI

null

Bazy danych

Jan Kazimirski

Złączenia wielu tabel

●

Łączyć można nie tylko dwie, ale również więcej
tabel

●

Przy złączeniu N tabel wymagane jest N-1
warunków złączenia.

select w.data_wyd,s.marka,k.nazwisko from
klienci k join wypozyczenia w using(nr_klienta)
join samochody s using(nr_samochodu);

Bazy danych

Jan Kazimirski

Inne warunki złączenia

●

Zwykle złączenia tabel dokonuje się w oparciu o
warunek równości ('=').

●

W przypadkach niektórych złączeń można
zastosować również inne warunki (np.

Nierówność '<>' itp.)

Bazy danych

Jan Kazimirski

Samozłączenie

●

Samozłączenie (self-join) polega na złączeniu
tabeli z samą sobą.

●

Przykład tabela pracownicy(id, nazwisko,szef):

KOWALSKI

NOWAK

KAMIŃSKI

null

select a.nazwisko,b.nazwisko
from pracownicy a join

pracownicy b
on a.szef=b.id;

Bazy danych

Jan Kazimirski

Tabele łączące

●

W wielu projektach baz danych pojawiają się tzw.

tabele łączące

●

Tabele łączące służą do złączenia dwóch lub
więcej tabel zawierających powiązane logicznie
informacje

●

Tabele łączące realizują tzw. relację wiele-do-
wielu

Bazy danych

Jan Kazimirski

Projekt z tabelą łączącą

KLIENCI

NR_KLIENTA

IMIE
NAZWISKO
KARTA
FIRMA
ULICA
DOM
MIASTO
KOD
NIP
TEL

SAMOCHODY

NR_SAMOCODU

MARKA
MODEL
ROK_PRODUKCJI
KOLOR
POJ_SILNIKA
PRZEBIEG

PRACOWNICY

NR_PRAC

IMIE
NAZWISKO
DATA_ZATR
DZIAL
STANOWISKO
PENSJA
DODATEK

NR_MIEJSCA

TEL

MIEJSCA

NR_MIEJSCA

ULICA
DOM
MIASTO
KOD
TEL
UWAGI

WYPOZYCZENIA

NR_WYPOZYCZENIA

NR_KLIENTA
NR_SAMOCHODU
NR_PRAC_WYD
NR_PRAC_ODD
NR_MIEJSCA_WYD
NR_MIEJSCA_ODD

DATA_WYD
DATA_ODD
KAUCJA
CENA_JEDN

PRIMARY KEY (PK)

FOREIGN KEY (FK)

Tabela łącząca

Bazy danych

Jan Kazimirski

Łączenie wyników zapytań

●

Rezultaty zapytań można łączyć za pomocą
operatorów algebry zbiorów:

–

Suma zbiorów (UNION)

–

Część wspólna zbiorów (INTERSECT)

–

Różnica zbiorów (EXCEPT)

Bazy danych

Jan Kazimirski

UNION

●

Wyrażenie

zap1 UNION zap2

zwraca sumę (w sensie teoriomnogościowym)

rezultatów zapytań zap1 i zap2.

●

Przykład:

select marka,kolor from samochody
where kolor ilike 'bialy'

union

select marka,kolor from samochody

where marka ilike 'fiat';

Bazy danych

Jan Kazimirski

INTERSECT

●

Wyrażenie

zap1 INTERSECT zap2

zwraca część wspólną rezultatów zapytań zap1 i

zap2.

●

Przykład:

select marka,kolor from samochody
where kolor ilike 'bialy'

intersect

select marka,kolor from samochody

where marka ilike 'fiat';

Bazy danych

Jan Kazimirski

EXCEPT

●

Wyrażenie

zap1 EXCEPT zap2

zwraca różnicę rezultatów zapytań zap1 i zap2.

●

Przykład:

select marka,kolor from samochody
where kolor ilike 'bialy'

except

select marka,kolor from samochody

where marka ilike 'fiat';

Bazy danych

Jan Kazimirski

Funkcje agregujące

●

Poznane dotychczas mechanizmy pozwalają na
dokonywanie obliczeń „poziomo” w tabeli – np.

wyliczania wartości dla każdego wiersza na
podstawie atrybutów.

●

Analiza danych w tabelach często wymaga
obliczeń „pionowych” - np. określenia sumy lub
średniej z kolumny w tabeli. Służą do tego tzw.

Funkcje agregujące

Bazy danych

Jan Kazimirski

Funkcje agregujące c.d.

●

Dostępne funkcje agregujące:

–

COUNT – zliczanie wierszy

–

SUM – suma wartości z kolumny

–

AVG – średnia z wartości z kolumny

–

MIN, MAX – najmniejsza i największa wartość z

kolumny

Bazy danych

Jan Kazimirski

Funkcje agregujące - przykłady

●

Ilu jest pracowników?

select count(*) from pracownicy;

●

Jaka jest średnia pensja?

select avg(pensja) from pracownicy;

●

Jaka jest najwyższa pensja

select max(pensja) from pracownicy;

Bazy danych

Jan Kazimirski

Zestawienia z podziałem na

kategorie

●

Funkcje agregujące często wykorzystuje się
razem z klauzulą GROUP BY.

●

GROUP BY pozwala podzielić wyniki zapytania
na określone kategorie

●

Przykład: Jakie są średnie zarobki w
poszczególnych działach?

select dzial,avg(pensja) from pracownicy
group by dzial;

Bazy danych

Jan Kazimirski

GROUP BY i WHERE

●

Klauzula WHERE może wystąpić razem z
GROUP BY. Działanie WHERE

poprzedzi

działanie GROUP BY.

●

Jaki jest średni przebieg samochodów bez
uwzględnienia białych?

select marka,avg(przebieg) from samochody
where kolor not ilike 'bialy' group by marka;

Bazy danych

Jan Kazimirski

HAVING

●

Klauzula HAVING użyta razem z GROUP BY
pozwala zawęzić wyniki do wierszy spełniających
warunek. HAVING działa

grupowaniu.

●

Jaki jest średni przebieg fordów o kolorze innym
niż biały?

select marka,avg(przebieg) from samochody
where kolor not ilike 'bialy' group by marka

having marka ilike 'ford';

Bazy danych

Jan Kazimirski

Podzapytania

●

W zapytaniu często pojawia się potrzeba użycia
wyniku (lub wyników) innego zapytania.

●

Można to uzyskać za pomocą podzapytania. W
ramach zapytania uruchamiane jest inne

zapytanie (zapytanie wewnętrzne).

●

Podzapytania często można zastąpić

złączeniami.

Bazy danych

Jan Kazimirski

Typy podzapytań

●

Podzapytania niepowiązane

– podzapytanie nie

zawiera odwołań do zapytania zewnętrznego.
Rezultat wykonania podzapytania wykorzystany

zostaje w zapytaniu zewnętrznym

●

Podzapytania powiązane

– podzapytanie

odwołuje się do zapytania zewnętrznego.
Podzapytanie wykonuje się dla każdego wiersza
zapytania zewnętrznego

Bazy danych

Jan Kazimirski

Typy podzapytań c.d.

●

Podzapytania zwracające jedną wartość

–

rezultatem podzapytania jest jedna wartość.
Można ją np. użyć w warunku WHERE.

●

Podzapytania zwracające listę wartości

–

rezultatem podzapytania jest lista wartości.

Stosowane słowa kluczowe IN, EXISTS, ANY,
ALL

Bazy danych

Jan Kazimirski

Podzapytania - przykłady

●

Lista pracowników o pensji wyższej niż średnia
pensja w firmie:

select nazwisko,pensja from pracownicy where

pensja>(

select avg(pensja) from pracownicy

);

Bazy danych

Jan Kazimirski

Podzapytania - przykłady

●

Lista pracowników o pensji wyższej niż średnie
we wszystkich działach:

select nazwisko,pensja from pracownicy where
pensja> all (

select avg(pensja) from

pracownicy group by dzial

);

Bazy danych

Jan Kazimirski

Podzapytania - przykłady

●

Lista pracowników o pensji wyższej niż średnia w
którymkolwiek z działów:

select nazwisko,pensja from pracownicy where
pensja> any (

select avg(pensja) from

pracownicy group by dzial

);

Bazy danych

Jan Kazimirski

Podzapytania - przykłady

●

Lista samochodów które były kiedykolwiek
wypożyczane:

select marka,kolor from samochody

where nr_samochodu
in (

select distinct nr_samochodu from

wypozyczenia

);

Bazy danych

Jan Kazimirski

Podzapytania - przykłady

●

Lista samochodów które były kiedykolwiek
wypożyczane – wersja z EXISTS

select nr_samochodu,marka,kolor from

samochody s where exists
(

select nr_samochodu from wypozyczenia w

where w.nr_samochodu = s.nr_samochodu

);

Document Outline