Bazy danych

Jan Kazimirski

Bazy Danych

wykład 4

Bazy danych

Jan Kazimirski

2

Podstawy języka SQL

c.d.

Bazy danych

Jan Kazimirski

3

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

4

Łą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

5

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

6

Iloczyn kartezjański c.d.

NAZWISKO

...

MIASTO

...

NAZWISKO

...

MIASTO

...

Bazy danych

Jan Kazimirski

7

Iloczyn kartezjański c.d.

KOWALSKI

NOWAK

WARSZAWA

KRAKÓW

KOWALSKI

NOWAK

KOWALSKI

NOWAK

WARSZAWA

WARSZAWA

KRAKÓW

KRAKÓW

Bazy danych

Jan Kazimirski

8

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

9

Warunek złączenia c.d.

KOWALSKI

NOWAK

WARSZAWA

KRAKÓW

KOWALSKI

NOWAK

KOWALSKI

NOWAK

WARSZAWA

WARSZAWA

KRAKÓW

KRAKÓW

2

1

1

2

2

2

1

1

2

1

2

1

Bazy danych

Jan Kazimirski

10

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

11

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

12

Skracanie nazw tabel

●

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

select

p.

nazwisko,

m.

miasto

from pracownicy

p

,miejsca

m

where

p.

nr_miejsca=

m.

nr_miejsca;

Bazy danych

Jan Kazimirski

13

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

14

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

15

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

16

JOIN ... ON

●

Najbardziej ogólny sposób wywołania:

select p.nazwisko,m.miasto
from pracownicy p

join

miejsca m

on

p.nr_miejsca=m.nr_miejsca;

Bazy danych

Jan Kazimirski

17

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

18

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

19

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

20

Złączenie wewnętrzne

KOWALSKI

NOWAK

KAMIŃSKI

1

2

3

WARSZAWA

WROCŁAW

KRAKÓW

1

3

5

KOWALSKI

1

WARSZAWA

1

NOWAK

3

KRAKÓW

3

select *

from t1
inner join t2

using(id);

Bazy danych

Jan Kazimirski

21

Lewostronne złączenie zewnętrzne

KOWALSKI

NOWAK

KAMIŃSKI

1

2

3

WARSZAWA

WROCŁAW

KRAKÓW

1

3

5

KOWALSKI

1

WARSZAWA

1

NOWAK

3

KRAKÓW

3

select *

from t1
left outer join t2

using(id);

KAMIŃSKI

2

null

null

Bazy danych

Jan Kazimirski

22

Prawostronne złączenie zewnętrzne

KOWALSKI

NOWAK

KAMIŃSKI

1

2

3

WARSZAWA

WROCŁAW

KRAKÓW

1

3

5

KOWALSKI

1

null

null

select *

from t1
right outer join

t2 using(id);

NOWAK

3

WARSZAWA

WROCŁAW

KRAKÓW

1

3

5

Bazy danych

Jan Kazimirski

23

Złączenie zewnętrzne

KOWALSKI

NOWAK

KAMIŃSKI

1

2

3

WARSZAWA

WROCŁAW

KRAKÓW

1

3

5

KOWALSKI

1

null

null

select *

from t1
full outer join t2

using(id);

NOWAK

3

WARSZAWA

WROCŁAW

KRAKÓW

1

3

5

KAMIŃSKI

2

null

null

Bazy danych

Jan Kazimirski

24

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

25

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

26

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

1

2

3

null

1

1

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

pracownicy b
on a.szef=b.id;

Bazy danych

Jan Kazimirski

27

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

28

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

29

Łą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

30

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

31

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

32

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

33

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

34

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

35

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

36

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

37

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

38

HAVING

●

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

po

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

39

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

40

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

41

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

42

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

43

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

44

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

45

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

46

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

);