Bazy danych 2

Wykład 4

Structured Query Language (SQL)

Cechy SQL

W standardzie SQL wyróżnia się dwie części:

DDL

(Data Definition Language) -

język definiowania danych

DML (Data Manipulation Language) – język manipulowania danymi

SQL jest językiem nieproceduralnym: użytkownik opisuje informacje,
których potrzebuje, a nie wskazuje w jaki sposób należy ja odnaleźć

Ma dość swobodny format – poszczególne fragmenty poleceń nie muszą
być umieszczone w określonych miejscach

SQL nie jest językiem zupełnym obliczeniowo

SQL może być osadzony w języku proceduralnym

Cechy SQL

Będziemy UśYWAĆ SKŁADNI Backusa-Naura

Wielkie litery będziemy używać w poleceniach

Małymi litrami będziemy zapisywać sowa definiowane przez
użytkownika

Pionowej kreski będziemy używać , by zaznaczyć możliwość wyboru
jednej z przedstawionych opcji

W nawiasach klamrowych umieszczamy elementy wymagane

W nawiasach kwadratowych umieszczamy elementy opcjonalne

Nawiasów okrągłych (…) używamy, aby zaznaczyć możliwość
powtórzenia elementu zero lub dowolna liczbę razy

Literały – to stałe wykorzystywane w poleceniach SQL, nieliczbowe
wartości danych zapisujemy w apostrofach, a wartości liczbowe bez

Klauzula SELECT

SELECT

[DISTINCT|ALL]{*|[wyrażenie kolumnowe[AS nowa_nazwa]] [,…]}

FROM

NazwaTabeli [alias][,…]

[WHERE

warunek_selekcji wierszy]

[GROUP BY

lista_kolumn][HAVING warunek_selekcji_grup]

[ORDER BY

lista_kolumn];

Klauzula SELECT

Kolejno

ść

przetwarzania jest nast

ę

puj

ą

ca

FROM

określa tabelę (lub tabele), z których będziemy korzystać

WHERE

pozwala wybrać wiersze spełniające zadany warunek selekcji wierszy

GROUP BY

tworzy grupy wierszy o tej samej wartości wskazanej kolumny

HAVING

pozwala wybrać grupy ze względu na podany warunek selekcji

SELECT

wskazuje, które kolumny powinny pojawić się w wyniku

ORDER BY

określa uporządkowanie wyniku



Porządku elementów zapytania SELECT nie można zmieniać

Klauzula SELECT

Przykład 1. Wyszukiwanie wszystkich kolumn i wierszy
Podaj wszystkie dane wszystkich pracowników

SELECT *
FROM Personel;

Przykład 2. Wyszukiwanie wybranych kolumn i wszystkich wierszy
Podaj list

ę

płac wszystkich pracowników, lista powinna zawiera

ć

jedynie numer

pracownika, jego imi

ę

i nazwisko oraz pensj

ę

.

SELECT pracownikNr, imi

ę

, nazwisko, pensja

FROM Personel;

Przykład 3. Wykorzystanie DISTINCT

Podaj numery wszystkich nieruchomo

ś

ci, które zostały odwiedzone przez klientów

SELECT DISTINCT nieruchomo

ść

Nr

FROM Wizyta;

Pola wyliczane

Przykład 4. Pola wyliczane

Podaj list

ę

miesi

ę

cznych płac wszystkich pracowników; lista powinna zawiera

ć

numer pracownika , jego imi

ę

i nazwisko oraz płac

ę

(w tabeli pensje s

ą

pensjami rocznymi)

SELECT PracownikNr, imi

ę

, nazwisko, pensja/12 AS pensjaMiesi

ę

czna

FROM Personel;

Polecenie SQL mo

ż

e zawiera

ć

dodawanie, odejmowanie, mno

ż

enie i

dzielenie oraz nawiasy pozwalaj

ą

ce budowa

ć

bardziej skomplikowane

wyra

ż

enia

W kolumnie wyliczanej mo

ż

e wyst

ą

pi

ć

wi

ę

cej ni

ż

jedna kolumna

Klauzula WHERE

Klauzula WHERE zawiera warunek selekcji, 5 podstawowych warunków to:

Porównanie – polega na porównaniu warto

ś

ci jednego wyra

ż

enia z

warto

ś

ci

ą

drugiego wyra

ż

enia

Sprawdzenie zakresu – polega na sprawdzeniu, czy zadana warto

ść

nale

ż

y do wskazanego przedziału warto

ś

ci

Przynale

ż

no

ść

do zbioru – polega na sprawdzeniu, czy zadana warto

ść

jest równa jednemu spo

ś

ród elementów zbioru

Dopasowanie do wzorca – polega na sprawdzeniu czy słowo pasuje do
podanego wzorca

Warto

ś

c pusta – polega na sprawdzeniu, czy w kolumnie jest warto

ść

pusta

Klauzula WHERE

Przykład 5. Warunek selekcji: porównanie

Podaj wszystkich pracowników, których pensja jest wy

ż

sza ni

ż

10000 funtów

SELECT pracownikNr, imi

ę

, nazwisko, stanowisko, pensja

FROM Personel

WHERE pensja>10000;

Przykład 6. Zło

ż

ony warunek selekcji: porównanie

Podaj adresy wszystkich biur znajduj

ą

cych si

ę

w Londynie lub Glasgow

SELECT *

FROM Biuro

WHERE miasto=‘Londyn’ OR miasto=‘Glasgow’;

Klauzula WHERE

Przykład 6. Warunek selekcji: warto

ś

ci z zakresu (BETWEEN i NOT BETWEEN)

Podaj wszystkich pracowników maj

ą

cych pensj

ę

pomi

ę

dzy 20000 a 30000 funtóww

SELECT pracownikNr, imi

ę

, nazwisko, stanowisko, pensja

FROM Personel

WHERE pensja BETWEEN 20000 AND 30000;

Przykład 7. Warunek selekcji : przynale

ż

no

ść

do zbioru (IN lub NOT IN)

Podaj wszystkich kierowników i dyrektorów

SELECT pracownikNr, imi

ę

, nazwisko, stanowisko

FROM Personel

WHERE stanowisko IN (‘kierownik’, ‘dyrektor’);

Klauzula WHERE

W SQL wyst

ę

puj

ą

dwa szczególne symbole zast

ę

pcze:

% - znak procentu zast

ę

puje dowolny ci

ą

g znaków

_ - znak podkre

ś

lenia zast

ę

puje dowolny (jeden) znak

Przykład 8. Warunek selekcji: dopasowanie do wzorca (LIKE lub NOT LIKE)

Podaj wszystkich wła

ś

cicieli, w których adresie wyst

ę

puje słowo ‘Glasgow’’

SELECT klientNr, imi

ę

, nazwisko, adres, telNr

FROM Wła

ś

cicielPryatny

WHERE adres LIKE ‘%Glasgow%’;

Je

ś

li słowo powinno zawiera

ć

znak specjalny musimy u

ż

y

ć

klauzuli

ESCAPE, np. by znale

źć

ci

ą

g ’15%’ u

ż

yjemy warunku

LIKE ’15#%’ ESCAPE ‘#’

Klauzula WHERE

Przykład 9. Warunek selekcji: warto

ś

ci puste (IS NULL lub IS NOT NULL)

Podaj szczegółowe informacje o wszystkich wizytach w nieruchomo

ś

ci PG4, po

których nie zgłoszono uwag

SELECT klientNr, dataWizyty

FROM Wizyta

WHERE nieruchomo

ść

Nr=‘PG4’ AND uwagi IS NULL;

Klauzula ORDER BY

Do uporz

ą

dkowania wierszy b

ę

d

ą

cych wynikiem zapytania słu

ż

y klauzula ORDER BY

zawieraj

ą

ca list

ę

oddzielonych przecinkami identyfikatorów kolumn, wg których nale

ż

y

posortowa

ć

wynik

Przykład 10. Porz

ą

dkowanie według jednej kolumny

Wygeneruj list

ę

pensji wszystkich pracowników uporz

ą

dkowan

ą

malej

ą

co według pensji

SELECT pracownikNr, imi

ę

, nazwisko, pensja

FROM Personel

ORDER BY pensja DESC

Przykład 11. Porz

ą

dkowanie według wielu kolumn.

Wygeneruj list

ę

wybranych informacji dotycz

ą

cych nieruchomo

ś

ci uporz

ą

dkowana według

rodzajów nieruchomo

ś

ci

SELECT nieruchomo

ść

Nr, typ, pokoje, czynszimi

ę

, nazwisko, pensja

FROM Nieruchomo

ść

ORDER BY typ, czynsz DESC;

Funkcje agregujące

W standardzie SQL pi

ęć

funkcji agreguj

ą

cych

COUNT – zwraca liczb

ę

warto

ś

ci wyst

ę

puj

ą

cych w okre

ś

lonej kolumnie

SUM – zwraca sum

ę

warto

ś

ci wyst

ę

puj

ą

cych w okre

ś

lonej kolumnie

AVG – zwraca

ś

redni

ą

warto

ś

ci wyst

ę

puj

ą

cych w okre

ś

lonej kolumnie

MIN – zwraca najmniejsza warto

ść

wyst

ę

puj

ą

c

ą

w okre

ś

lonej kolumnie

MAX – zwraca najwi

ę

ksz

ą

warto

ść

wyst

ę

puj

ą

c

ą

w okre

ś

lonej kolumnie

Funkcje COUNT, MIN i MAX mo

ż

na stosowa

ć

zarówno do warto

ś

ci

liczbowych jak i nieliczbowych

Wszystkie funkcje oprócz COUNT(*) pomijaj

ą

warto

ś

ci puste

Je

ż

eli chcemy wyeliminowa

ć

powtórzenia, u

ż

ywamy słowa kluczowego

DISTINCT przed nazw

ą

kolumny w argumencie funkcji, mo

ż

e by

ć

u

ż

yte

tylko jeden raz w zapytaniu

Funkcje agregujące

Funkcje agreguj

ą

ce mog

ą

by

ć

stosowne jedynie na li

ś

cie SELECT lub klauzuli

HAVING

Je

ż

eli lista SELECT zawiera funkcj

ę

agreguj

ą

c

ą

i w zapytaniu nie jest

zastosowana klauzula GROUP BY słu

żą

ca do grupowania danych, to

wówczas

ż

aden z elementów listy SELECT nie mo

ż

e odwoływa

ć

si

ę

do

kolumny, o ile ta kolumna nie jest argumentem funkcji agreguj

ą

cej, np.

bł

ę

dne jest poni

ż

sze zapytanie

SELECT pracownikNr, COUNT(pensja)

FROM Personel

Funkcje agregujące

Przykład 12. Zastosowanie COUNT(*)

W ilu nieruchomo

ś

ciach miesi

ę

czny czynsz jest wy

ż

szy ni

ż

350 funtów.

SELECT COUNT(*) AS liczba

FROM Nieruchomo

ść

WHERE czynsz>350;

Przykład 13. Zastosowanie COUNT DISTINCT

Ile nieruchomo

ś

ci odwiedzono w maju 2001 roku?

SELECT COUNT(DISTINCT nieruchomo

ś

ciNr) AS liczba

FROM Wizyta

WHERE data Wizyty BETWEN ‘1.05.2001” AND ’31.05.2001’;

Funkcje agregujące

Przykład 14. Zastosowanie COUNT i SUM

Oblicz, ilu jest dyrektorów i jaka jest ich sumaryczna pensja.

SELECT COUNT(pracownikNr) AS liczba, SUM(pensja) as suma

FROM Personel

WHERE stanowisko=‘dyrektor’;

Przykład 15. Zastosowanie MIN, MAX i AVG

Oblicz najmniejsz

ą

, najwi

ę

ksza i

ś

redni

ą

pensj

ę

pracownika

SELECT MIN(pensja) S minimum, MAX(pensja) AS maksimum, AVG(pensja)
AS

ś

rednia

FROM Personel;

Klauzula GROUP BY

Zapytanie z klauzul

ą

GROUP BY nazywamy zapytaniem grupuj

ą

cym, poniewa

ż

w trakcie jego obliczania dane z tabeli SELECT s

ą

dzielone na grupy i dla

ka

ż

dej grupy jest generowany jeden wiersz podsumowania.

Kolumny wymienione w klauzuli GROUP BY nazywamy kolumnami
grupuj

ą

cymi.

Gdy w zapytaniu wyst

ę

puje GROUP BY, dla ka

ż

dego elementu z listy SELECT

musi istnie

ć

mo

ż

liwo

ść

wyznaczenia jednoznacznie warto

ś

ci w ramach grupy

Klauzula SELECT mo

ż

e zawiera

ć

jedynie

Nazwy kolumn grupowania

Funkcje agreguj

ą

ce

Stałe

Wyra

ż

enia zawieraj

ą

ce kombinacje powy

ż

szych elementów

Wszystkie nazwy kolumn na li

ś

cie SELECT musz

ą

wyst

ę

powa

ć

w

klauzuli GROUP BY, chyba

ż

e nazwa kolumny jest u

ż

ywana jako

argument funkcji agreguj

ą

cej

Klauzula GROUP BY

Przykład 16. Zastosowanie GROUP BY

Oblicz, dla ka

ż

degobiura liczb

ę

zatrudnionych w nim pracowników oraz ich

sumaryczna pensj

ę

.ilu jest dyrektorów i jaka jest ich sumaryczna pensja.

SELECT biuroNr, COUNT(pracownikNr) AS liczba, SUM(pensja) AS suma

FROM Personel

GROUP BY biuroNr

ORDER BY biuroNr;

Klauzula HAVING

Klauzul

ę

HAVING stosuje si

ę

razem w poł

ą

czeniu z klauzul

ą

GROUP BY

Klauzula HAVING słu

ż

y do wyboru grup, które ostatecznie trafia do tabeli

wynikowej

Nazwy kolumn wyst

ę

puj

ą

ce w klauzuli HAVING pojawiały si

ę

tak

ż

e na li

ś

cie

GROUP BY lub były argumentami funkcji agreguj

ą

cej

Przykład 17. Zastosowanie HAVING

Dla ka

ż

dego biura zatrudniaj

ą

cego wi

ę

cej ni

ż

jednego pracownika, podajliczb

ę

pracowników biura oraz sum

ę

ich zarobków

SELECT biuroNr, COUNT(pracownikNr) AS liczba, SUM(pensja) AS suma

FROM Personel

GROUP BY biuroNr

HAVING COUNT(pracownikNr)>1

ORDER BY biuroNr;

Podzapytania

Zapytania zagnie

ż

d

ż

one (wewn

ę

trzne) mog

ą

wyst

ę

powa

ć

w klauzulach

WHERE i HAVING zapytania zewn

ę

trznego, a tak

ż

e w klauzulach INSERT,

UPDATE i DELETE

Istniej

ą

trzy rodzaje podzapyta

ń

:

Podzapytania skalarne – zwracaj

ą

jedn

ą

kolumn

ę

i jeden wiersz

Podzapytania krotkowe – zwracaj

ą

kilka kolumn i tak jak poprzednio tylko

jeden wiersz

Podzapytanie tabelowe – zwracaj

ą

jedn

ą

lub wi

ę

cej kolumn i wiele wierszy

Podzapytania

Przykład 18. Zastosowanie podzapytania z równo

ś

ci

ą

Podaj wszystkich pracowników zatrudnionych w biurze przy ‘163 MainStr.’

SELECT pracownikNr, imi

ę

, nazwisko, stanowisko

FROM Personel

WHERE biuroNr=(SELECT biuroN

FROM Biuro

WHERE ulica=‘163 Main Str.’);

Przykład 19. Stosowanie podzapyta

ń

z funkcj

ą

agreguj

ą

c

ą

Podaj wszystkich pracowników, których pensja jest wy

ż

sza od

ś

redniej; poka

ż

ró

ż

nic

ę

mi

ę

dzy poszczególnymi pensjami z

ś

redni

ą

.

SELECT pracownikNr, imi

ę

, nazwisko, stanowisko, pensja – ró

ż

nica

FROM Personel

WHERE pensja>(SELECT AVG(pensja) FROM Personel AS ró

ż

nica) ;

Podzapytania

Do podzapyta

ń

stosuje si

ę

poni

ż

sze zasady:

W podzapytaniach nie wolno u

ż

ywa

ć

klauzuli ORDER BY

Lista SELECT podzapytania musi składa

ć

si

ę

z pojedynczej nazwy kolumny lub

wyra

ż

enia, z wyj

ą

tkiem podzapyta

ń

wykorzystywanych z operatorem EXISTS

Domy

ś

lnie nazwy kolumn w podzapytaniu odnosz

ą

si

ę

do nazwy tabeli z

klauzuli FROM podzapytania. Do kolumn tabeli z klauzul

ą

FROM zapytania

zewn

ę

trznego mo

ż

na odwoła

ć

si

ę

poprzedzaj

ą

c nazw

ę

kolumny nazw

ą

tabeli.

Je

ż

eli podzapytanie jest jednym z dwóch argumentów, których dotyczy

porównanie, to musi wyst

ę

powa

ć

po prawej stronie porównania.

Podzapytania

Przykład 20. Podzapytania zagnie

ż

d

ż

one:

Podaj wszystkie nieruchomo

ś

ci nadzorowane przez pracowników zatrudnionych w

biurze ‘163 Main Str’

SELECT nieruchomo

ść

Nr, ulica, miasto, kodPocztowy, typ, pokoje, czynsz

FROM Nieruchomo

ść

WHERE pracownikNr IN (SELECT pracownikNr

FROM Personel

WHERE biuroNr=(SELECT biuroN

FROM Biuro

WHERE ulica=‘163 Main Str.’));

Klauzule ANY, SOME i ALL

Słowa ANY, SOME i ALL w przypadku gdy wynik daje wiele wierszy i
stosujemy operatory porównania

Przykład 20. Zastosowanie ANY/SOME:

Znajd

ź

wszystkich pracowników, którzy maj

ą

pensj

ę

wy

ż

sza ni

ż

przynajmniej jeden pracownik biura o numerze B003

SELECT pracownikNr, imi

ę

, nazwisko, pensja

FROM Personel

WHERE pensja>SOME (SELECT pensja

FROM Personel

WHERE biuroNr=‘B003’);

Zapytania dotyczące wielu tabel

Aby dokonać złączenia w klauzuli FROM należy wymienić tabele
oddzielając je przecinkami, a w klauzuli WHERE określić kolumny wg
których jest dokonywane złączenie

Dla każdej tabeli można zdefiniować alias – można go używać
wszędzie zamiast nazwy

Przyk

łłłł

ad 22. Proste z

łą

czenie:

Podaj nazwy wszystkich klientów, którzy odwiedzili wszystkie nierucho-

mo

ś

ci . Wraz z danymi klienta podaj zg

ł

oszone przez niego uwagi.

SELECT k.klientNr, imi

ę

, nazwisko, nieruchomo

ść

Nr, uwagi

FROM Klient k, Wizyta w

WHERE k.klientNr=w.klientNr

Zapytania dotyczące wielu tabel

W standardzie SQL opisano alternatywne sposoby zapisu powy

ż

szego

z

ł

aczenia

FROM Klient k JOIN Wizyta w ON k.klientNr=w.klientNr

FROM Klient JOIN Wizyta USING klientNr

FROM Klient NATURAL JOIN Wizyta

Złączenia

Procedura generowania wyniku zapytania SELECT ze z

łą

czeniem jest

nast

ę

puj

ą

ca:

(1) Utwórz iloczyn kartezja

ń

ski tabel wymienionych po klauzuli FROM

(2) Je

ż

eli istnieje klauzula WHERE, to zastosuj warunek selekcji do

ka

ż

dego z wierszy iloczynu, pozostawiaj

ą

c tylko te wiersze, które

spe

ł

niaj

ą

warunek

(3) Dla ka

ż

dego z pozosta

ł

ych wierszy ustal warto

ść

ka

ż

dego elementu

z listy SELECT i wygeneruj jeden wiersz

(4) Je

ż

eli w zapytaniu u

ż

yto SELECT DISTINCT, usu

ń

powtarzaj

ą

ce sie

wiersze z tabeli wynikowej

(5) Je

ż

eli wyst

ę

puje klauzula ORDER BY, to uporz

ą

dkuj tabel

ę

wynikow

ą

wed

ł

ug ustalonego kryterium

Złączenia zewnętrzne

Złączenie zewnętrzne zachowuje wiersze niespełniające warunku
złączenia

Istnieją trzy rodzaje złączenia zewnętrznego:

Lewostronne – LEFT JOIN

Prawostronne – RIGHT JOIN

Pełne – FULL JOIN

Złączenia zewnętrzne

Rozwa

ż

my przyk

ł

ad:

Z

łą

czenie lewostronne:

SELECT b.*, d.*
FROM biuro1 b LEFT JOIN Nieruchomo

ść

1 d

ON b.bmiasto=d.nmiasto;

Z

łą

czenie prawostronne:

SELECT b.*, d.*
FROM biuro1 b RIGHT JOIN Nieruchomo

ść

1 d

ON b.bmiasto=d.nmiasto;

Z

łą

czenie pe

ł

ne:

SELECT b.*, d.*
FROM biuro1 b FULL JOIN Nieruchomo

ść

1 d

ON b.bmiasto=d.nmiasto;

UNION, INTERSECT, EXCEPT

W j

ę

zyku SQL mamy mo

ż

liwo

ść

wykonywania operacji na zborach:

sumy, iloczynu, ró

ż

nicy

Operacje te nazywane s

ą

w standardzie SQL odpowienio: UNION,

INTERSECT i EXCEPT

Sposób zapisu jest nast

ę

puj

ą

cy

operacja [ALL] [CORRESPONDING [BY [kolumna1[,...]]]]

gdzie

opcja

CORRESPONDING [BY [kolumna1[,...]]]

informuje,

ż

e

operacja jest wykonywana na wskazanych kolumnach

opcja

CORRESPONDING

informuje,

ż

e operacja jest wykonywana

w oparciu o wspólne kolumny

opcja

ALL

- wynik mo

ż

e zawiera

ć

powtarzaj

ą

ce si

ę

wiersze

UNION, INTERSECT, EXCEPT

Przykład 25. Zastosowanie UNION:

Podaj list

ę

wszystkich miast w których znajduje si

ę

biuro lub nieruchomo

ść

(SELECT miasto

FROM biuro

WHERE miasto IS NOT NULL)

UNION

(SELECT miasto

FROM Nieruchomo

ść

WHERE miasto IS NOT NULL)

(SELECT *

FROM biuro

WHERE miasto IS NOT NULL)

UNION CORRESPONDING BY miasto

(SELECT *

FROM Nieruchomo

ść

WHERE miasto IS NOT NULL)

lub

Dziękuję za uwagę