Języki zapytań do baz danych

SYSTEMY BAZ
DANYCH Część II
Opracowanie : Dr Bożena Śmiałkowska
1
Cechy języków zapytań do baz danych
Deklaratywny charakter,
Zanurzenie w języku programowania,
Wysoki poziom abstrakcji
SQL
Języki zapytań do relacyjnych baz danych
Języki oparte na algebrze
relacji (ISBL w Ingresie)
Języki oparte na
rachunku krotek
(QUEL)
Języki oparte na
predykatach
Języki oparte na
rachunku dziedzin
(QBL)
SQL = [ język oparty na algebrze relacji ] + [ język oparty na rachunku krotek ]
Literatura do zagadnienia : SQL
 Celko J.: Zaawansowane techniki programowania. Mikrom, W-wa,








1999
Connan S.: SQL-The standard Handbook. McGraw-Hill Book
Company, London, 1993
Harrington J.L.: SQL dla każdego. EDU-Mikom, W-wa, 1998
SQL. Język relacyjnych baz danych. Wellesley Software. WNT, Wwa, 1999
Stephans R.: SQL w 3 tygodnie. LT&P, W-wa, 1999
http://galaxy.uci.agh.edu.pl/chwastek/lectures/db/dbtitle.html
http://baszta.iie.ae.wroc.pl/index.html
http://www.cs.put.poznan.pl/kjankiewicz/oracle/sql/index.html
http://www.cs.put.poznan.pl/rwrembel/courses/sbd.html
SQL to:
 Strukturalny język zapytań (Ang. Structured Query




Language),
niepełny język obsługi baz danych,
język obsługi baz danych zaimplementowany w systemach
zarządzania relacyjnymi bazami danych (RDBMS –
relacyjny DBMS), przeznaczony do definiowania struktur
danych, wyszukiwania danych oraz operacji na danych,
Posiada on akceptację ANSI oraz standard ISO.
W praktyce jest to standardowy język zapytań.
Cechy języka SQL
 Jest językiem wysokiego poziomu (4GL) opartym na języku





angielskim,
Jest językiem deklaratywnym (nieproceduralnym) zorientowanym
na wynik (użytkownik deklaruj co chce uzyskać, a nie jak to chce
zrealizować),
SQL nie ma instrukcji sterujących wykonanie programu,
Jest często zanurzony w innym języku programowania (np.: C,
Fortran, PL, itp.),
Nie zawiera w sobie rekurencji,
Umożliwia definiowanie struktur danych, wyszukiwanie danych
oraz operacje na danych (np.: kasowanie danych, modyfikowanie
danych itp., o ile użytkownik ma do tego prawo).
Zalety SQL
Wady i ograniczenia SQL
Historia SQL
 Koniec lat 70 – tych – firma ORACLE (Relational Software Inc.) –








pierwsza implementacja praktyczna (komercyjna),
1981 – IBM – SQL/DS. (RDL – Relational Data Language),
1983 – ISO definicja SQL,
1986 – ANSI – pierwszy standard SQL (SQL-86),
1987 – ISO – pierwszy standard SQL : ISO 9075:1987 (E),
1989 – ISO – następny standard SQL : ISO 9076: 1989 (E), (SQL98),
1992 – ISO – kolejna, wzbogacona wersja : ISO 9075 : 1992 (E),
(SQL 2),
1999 – SQL 3
Grupa ODMG (Object DataBase Management Group) w oparciu o
SQL opracowała język do obiektowych baz danych OQL.
Historia SQL…cd…

Koncepcja leżąca u podstaw języka SQL powstała w wyniku prac
prowadzonych w laboratorium badawczym IBM w San Jose w Kalifornii w
latach siedemdziesiątych. Tam też została zbudowana implementacja
prototypowa relacyjnych pojęć o nazwie System/R. Ten wczesny relacyjny
SZBD używał języka znanego wówczas jako SEQUEL. Dlatego właśnie
wiele osób wciąż wymawia nazwę SQL jak SEQUEL (to jest sikłel).

W latach 1973-1979 badacze z IBM opublikowali w akademickich
czasopismach dużo materiałów na temat budowy System/R. W tym czasie
zarówno w USA, jak i w Europie na konferencjach i seminariach
prowadzono ożywione dyskusje na temat poprawności relacyjnego SZBD.
IBM okazał się niewątpliwie nadzwyczaj powolny w dostrzeżeniu
komercyjnego znaczenia systemów relacyjnych. Pierwsze pomyślne,
komercyjne wykorzystanie idei związanych z relacyjnym modelem danych
przypadło korporacji ORACLE, założonej w 1977 r.
Historia SQL…cd…
 System ORACLE był i jest relacyjnym SZBD opartym na SQL.
Wielu innych producentów również wyprodukowało systemy
używające SQL. Z tego powodu w 1982 r. organizacja ANSI
(American National Standards Committee) przekazała swojemu
komitetowi baz danych (X3H2) sprawę utworzenia standardu
języka relacyjnych baz danych (RDL). Komitet ten opublikował
definicję składni standardu SQL w 1986 r., opartą głównie na
dwóch dialektach SQL IBM i ORACLE (ANSI, 1986). W 1987 r.
organizacja ISO (International Standards Organization)
opublikowała bardzo podobny standard (ISO, 1987). Ten standard
jest również znany pod nazwą SQLI. Oryginalny dokument ANSI
określa dwa poziomy dla SQLl: poziom pierwszy i poziom drugi.
Poziom drugi jest pełnym językiem SQL. Poziom pierwszy, którego
pierwotnym założeniem było pełnienie funkcji przecięcia dla
istniejących implementacji, jest podzbiorem poziomu drugiego.
Historia SQL…cd…
 W następstwie powyższych publikacji pojawiło się wiele
krytycznych uwag na temat standardu ANSI/ISO, zwłaszcza ze
strony specjalistów w dziedzinie baz danych, takich jak E. F. Codd
(1988a, 1988b) i C. Dale (1987). Wiele osób uważało, że wadą
standardu jest fakt, że jest on częścią wspólną istniejących
aplikacji. Inni uważali, że język ma poważniejsze wady, zwłaszcza
w zakresie relacyjnych konstrukcji.
 W 1989 r. w odpowiedzi na krytykę ANSI opublikowała dodatek do
standardu, zawierający głównie ulepszenia cech integralności
(ANSI, 1989a). Duża część tego dodatku została włączona do
roboczej wersji proponowanej drugiej wersji standardu, również
wydanego przez ANSI w 1989 r. (ANSI, 1989b). ISO, blisko
współpracując z ANSI, wydała w tym samym roku dokument
zatytułowany "Database Language SQL with Integrity
Enhancement" (ISO, 1989).
Historia SQL…cd…
 W 1992 r. ANSI i ISO wydały pełną specyfikację rozszerzonej wersji SQL,
znanej jako SQL2. Dla tego standardu określono dwa podzbiory: poziom
minimalny i poziom pośredni. Poziom minimalny SQL2 jest w zasadzie taki
sam jak SQL1 z udoskonalonymi cechami integralności. Uzgodniono już
kolejne istotne rozszerzenia standardu SQL2 i oczekuje się pojawienia wersji
standardu o nazwie SQL3 pod koniec lat dziewięćdziesiątych.
 Nie ma zatem jednego standardu, a przynajmniej trzy. Oznacza to, że
jakakolwiek implementacja SQL może realizować wszystkie lub część z tych
trzech wersji standardu. Jest to jeden z powodów, dla których większość
implementacji komercyjnych uważa się w najlepszym razie za dialekty
standardu SQL. Innymi słowy, pod wieloma względami znajdują one wspólny
grunt wokół definicji podstaw lub poziomu jeden standardu SQLl. Pod
innymi względami różnią się one nie dostosowując się ani do SQLI, ani do
późniejszych standardów (typy danych są tu dobrym przykładem). Niektóre
implementacje oferują dodatkowe konstrukcje nie uwzględnione w
standardzie.
Ogólna charakterystyka SQL…
SQL (strukturalny język zapytań - Structured Query Language)
 Wysoki poziom bezpieczeństwa, integralności i kompletności
danych,
 Praca w konfiguracjach klient-serwer,
 Optymalizacja zapytań kierowanych do bazy przez
użytkowników lub ich systemy,
 Efektywne przetwarzanie transakcji,
 Zdolność manipulowania niestandardowymi strukturami
danych,
 SQL jest zwykle podzielony na trzy główne części: definicje
danych, operowanie danymi i kontrola danych,
Sposób wykorzystania SQL
 Interaktywny SQL – bezpośredni dostęp do danych za pomocą
interpretera SQL,
 Statyczny SQL – stały (predefiniowany) kod w SQL – może to być
zanurzony SQL (tzw. embedded SQL) – kod znajdujący się
wewnątrz innego języka programowania lub modułowy SQL, tzn.
samodzielne moduły w języku SQL mogą być łączone z modułami
innych języków,
 Dynamiczny SQL – kod SQL generowany dynamicznie przez
programy użytkowe – często generowany jest za pomocą
interfejsów graficznych lub z poziomu WWW,
 Definicyjny SQL – kod w SQL generowany przy pomocy narzędzi
CASE (Computer Aided Software Enginnering).
SQL – postać poleceń (zapytań)
Przykładowe pełne określenie
zapytania
Przykładowe zapytanie
sparametryzowane
Przykładowe zapytanie
sparametryzowane
Zapytania dynamiczne - przykład
Zapytania dynamiczne - przykład
Komponenty SQL
Podstawowe struktury danych w SQL
Podstawowe struktury danych cd..
Alfabet SQL
Obejmuje:
 Zestaw znaków SQL charakterystyczny dla implementacji
 litery duże i małe, cyfry, znaki specjalne , ; ( ) . % _ > < = „ +
* / - ? : ! spacja,
 Literały (stałe), zapisywane w cudzysłowiu np.: ‘Warszawa’
 Identyfikatory (nazwy), np.: nazwy tabel, kolumn
(atrybutów), widoków, schematów, itp.,
 Nazwy poleceń i funkcji - każda instrukcja w SQl zaczyna
się słowem kluczowym, może zawierać modyfikatory i
kończy się średnikiem,
 Znak * oznacza wszystkie kolumny (atrybuty) tabeli,
Zasady konstrukcji wyrażeń
Podstawowe typy danych w SQL
Wyróżnia się następujace typy danych:
Typy napisowe (String)
 Character(N) - Napis znakowy o stałej długości. Jeżeli na
wejściu znajdzie się napis o mniejszej długości niż N, to na
końcu napisu są dodawane spacje,
 Character Varying (N) - Napis znakowy o minimalnej
długości 1 i maksymalnej długości określonej przez
system. Jeżeli na wejściu pojawi się napis o mniejszej
długości niż N, to jest przechowywana tylko właściwa
długość napisu.
 Bit - Napisy bitowe głównie używane dla danych
graficznych i dźwięku. d. Bit Varying. Napisy bitowe
zmiennej długości,
Podstawowe typy danych w SQL
Typy liczbowe:
 Numeric - Synonim dla Decimal,
 Decimal(M, N) - Liczba dziesiętna o długości M z N miejscami po





przecinku dziesiętnym,
Integer - Liczba całkowita z zakresu wartości określonych przez
system,
Smallint - Liczba całkowita z mniejszego zakresu wartości
określonych przez system,
Float - Liczba przechowywana w reprezentacji zmiennopozycyjnej,
Real - Jest synonimem Float,
Double Precision.
Podstawowe typy danych w SQL
Typy daty i godziny (Datetime) :
 Date - Daty określone przez system,
 Time - Godziny określone przez system,
 Timestamp - Daty i godziny z uwzględnieniem ułamków sekund
 Interval - Przedziały między datami.
Konkretne implementacje różnią się
realizacją typów danych.
Obiektowy model SQL3
Rozszerzenie typów o obiekty w SQL3
Zakładanie tabel bazy danych
CREATE TABLE <nazwa tabeli>
(<nazwa kolumny><typ danych>(<długość>),
<nazwa kolumny><typ danych>(<długość>),
...
[PRIMARY KEY (nazwa atrubutu [ , nazwa atrubutu ]…)],
FOREIGN KEY (nazwa atrubutu [, nazwa atrybutu ] …)
REFERENCES <nazwa tabeli> (<nazwa atrybutu>)]);
Zakładanie tabel bazy danych - Opcje NOT
NULL i UNIQUE
 Każda kolumna w tabeli może być zdefiniowana jako
NOT NULL. Oznacza to, że użytkownik nie może
wprowadzić wartości null do tej kolumny.
 Domyślną specyfikacją dla kolumny jest NULL. To
znaczy wartości null są dozwolone w kolumnie.
 Każda kolumna może być również zdefiniowana jako
UNIQUE (jednoznaczna). Ta klauzula zabrania
użytkownikowi wprowadzania powtarzających się
wartości do kolumny.
 Kombinację NOT NULL i UNIQUE możemy użyć do
zdefiniowania cech klucza głównego.
Zakładanie tabel bazy danych - przykłady
Do definicji kolumny możemy dodać klauzulę określającą wartość,
którą system automatycznie wpisuje do kolumny, jeżeli
użytkownik wprowadzi niepełną informację. Na przykład do
kolumny poziom w tabeli Moduły możemy dodać specyfikację
DEFAULT <wartość> wskazującą, że domyślnym poziomem
powinien być 1:
CREATE TABLE Moduły
(NazwaModułu Character( 30) NOT NULL UNIQUE,
Poziom Smallint DEFAULT 1,
KodKursu Character(3),
NrPrac Integer) ;
Zakładanie tabel bazy danych - przykłady
CREATE TABLE sale
(id_sali short not null,
kod_kursu text(10) not null,
nazwa_kursu text(30),
wymiar_godz byte,
czas_od text(12),
id_kierunku text(4),
Primary key (id_sali),
Foreign key (id_kierunku) references KIERUNKI
(nr_kierunek));
Zakładanie tabel bazy danych- przykłady
Instrukcja DROP TABLE – usuwanie
definicji tabeli
 Usuwa definicję tabel.
 Aby usunąć tabelę z bazy danych, używamy
następującego polecenia:
DROP TABLE <nazwa tabeli>
Na przykład
DROP TABLE Moduły
Modyfikacja struktury tabel bazy danych
Przy założeniu idealnej niezależności danych administrator danych powinien
móc modyfikować strukturę bazy danych bez wywierania wpływu na
użytkowników lub programy użytkowe, które mają dostęp do bazy danych. W
praktyce produkty oparte na SQL realizują tylko ograniczoną postać
niezależności danych. Administrator może dodać dodatkową kolumnę do tabeli,
zmodyfikować maksymalną długość istniejącej kolumny lub usunąć kolumnę z
tabeli. Każdą z tych operacji określamy używając polecenia ALTER TABLE.
Na przykład:
ALTER TABLE Wykładowcy
ADD COLUMN NrPokoju Smallint
ALTER TABLE Wykładowcy
ALTER COLUMN NazwiskoPrac Varchar(20)
ALTER TABLE Wykładowcy
DROP COLUMN NazwiskoPrac
dodanie
kolumny
zmiana
wymiaru
usuniecie
wymiaru
Operacje w SQL na danych bazy danych
 Wstawianie danych do bazy danych (INSERT INTO),
 Aktualizacja bazy danych (UPDATE),
 Kasowanie danych z bazy danych (DELETE),
 Operacje teoriomnogościowe na bazie danych: suma, różnica, iloczyn




mnogościowy i kartezjański (UNION, INTERSECT, EXCEPT ),
Selekcja danych (SELECT),
Projekcja (rzutowanie) danych (realizowane przez SELECT),
Łączenie tabel bazy danych: naturalne, warunkowe, zewnętrzne (JOIN,
NATURAL JOIN, LEFT OTHER JOIN, RIGHT OTHER JOIN FULL OTHER JOIN
oraz realizowane przez SELECT),
Dzielenie tabel (DIVISION).
Wstawianie danych do bazy danych
Polecenie INSERT dodaje dodatkowy wiersz do podanej tabeli. Na
przykład instrukcja:
INSERT INTO Moduly
VALUES ('Wstęp do zarządzania',I,'BSD',123)
dodaje dodatkowy wiersz do tabeli Moduly. Porządek, w jakim
powinny być podane wartości w poleceniu INSERT, musi się
zgadzać z porządkiem, w jakim pierwotnie określono kolumny dla
tabeli w poleceniu CREATE TABLE. Jeżeli chcemy wypisać
wartości w jakimś innym porządku niż pierwotnie określony lub
jeśli chcemy ominąć jakieś kolumny przed wstawianiem, to musimy
dodać listę nazw kolumn do polecenia INSERT. Na przykład:
INSERT INTO Moduty (Poziom, KodKursu, NrPrac,
NazwaModulu) VALUES (2,'CSD',237,'Tworzenie
systemów informacyjnych')
Wstawianie danych do bazy danych
Wstawianie danych do bazy danych
Specjalna wersja polecenia INSERT umożliwia dodanie wielu wierszy
do tabeli. Jest zwykle używana, aby umieścić wyniki jakiegoś
zapytania w podanej tabeli. Przypuśćmy na przykład, że chcemy
utworzyć tabelę wykładowców pracujących na wydziale studi6w
informatycznych. Możemy to zrobić, jak następuje:
CREATE TABLE WykladowcyInformatyki
(NrPrac Number(5),
NazwiskoPrac Varchar(15),
Status Varchar(10),
Pensja Decimal(7, 2))
INSERT INTO WykladowcyInformatyki(NrPrac, NazwiskoPrac, Status,
Pensja)
SELECT NrPrac, NazwiskoPrac, Status, Pensja
FROM Wykladowcy
WHERE NazwaWydzialu = 'Studia informatyczne'
Usuwanie danych z bazy danych
Polecenia DELETE używamy do usuwania wierszy z tabeli. Wiersze
do usunięcia podajemy w klauzuli WHERE, podobnie jak w poleceniu
SELECT.
np.:
DELETE FROM Wykładowcy
WHERE NazwaWydziału = 'Studia informatyczne'
Aktualizowanie danych w bazie danych
Polecenia UPDATE używamy do zmodyfikowania zawartości jednego
lub więcej wierszy tabeli. Wiersze do modyfikacji są określane w
opcjonalnej klauzuli WHERE, a zmianę lub zmiany do wykonania
podajemy w klauzuli SET.
Na przykład następujące polecenie zwiększy o 10% pensję wszystkich
wykładowców o statusie PL:
UPDATE Wykładowcy SET Pensja = Pensja*1.1
WHERE Status = 'PL'
Operacje w SQL na danych bazy danych
Selekcja
projekcja
Łączenie (join) dwóch tabel
Operacje „mnogościowe” w SQL na
danych bazy danych
a
11
Dos
w
32
Bios
S
44
Kos
z
34
Znak
a
11
Dos
w
32
Bios
w
32
Bios
z
43
Znak
Różnica tabel
Suma tabel
A
11
Dos
w
32
Bios
S
44
Kos
z
34
Znak
a
11
a
11
Dos
w
32
Bios
w
32
Bios
z
43
Znak
Iloczyn tabel
Dos
w
32
Bios
Selekcja
Struktura typowego zapytania
selekcyjnego
 Proste wyszukiwanie jest wykonywane dzięki kombinacji klauzul
SELECT, FROM i WHERE:
SELECT <nazwa atrybutul.>, <nazwa atrybutu2>,...
FROM <nazwa tabeli>
[WHERE <warunek>]
 Klauzula SELECT wskazuje na kolumny, z których wartości mają być
wydobyte. Klauzula FROM określa tabele, z których mają pochodzić
dane. Klauzula WHERE określa warunek lub warunki, które mają być
spełnione przez sprowadzane dane. W następującym poleceniu
gwiazdka "*" pełni funkcję symbolu uniwersalnego. Oznacza to, że
zostaną wypisane wszystkie atrybuty z tabeli, której nazwa znajduje
się po słowie FROM.
 Klauzula WHERE jest opcjonalna
 Opcja DISTINCT w wyniku daje różne (niepowtarzalne) wyniki
SELECT DISTINCT stanowisko
FROM pracownicy;
Warunki w zapytaniach selekcyjnych
Like w Access
Znak
Opis
0
Cyfra (Od 0 do 9, pozycja wymagana, znaki plus [+] i minus [–] nie są dozwolone).
9
Cyfra lub spacja (pozycja nie jest wymagana, znaki plus i minus nie są dozwolone).
#
Cyfra lub spacja (pozycja nie jest wymagana, w trybie edycji spacje wyświetlane są jako puste miejsca, lecz podczas
zapisywania danych spacje są usuwane; znaki plus i minus są dozwolone).
L
Litera (od A do Z, pozycja wymagana).
?
Litera (od A do Z, pozycja wymagana).
A
Litera lub cyfra (pozycja wymagana).
a
Litera lub cyfra (pozycja wymagana).
&
Dowolny znak lub spacja (pozycja wymagana).
C
Dowolny znak lub spacja (pozycja wymagana).
. , : ; - /
Dziesiętny symbol zastępczy oraz separator tysięcy, dat i godzin. (Znak, który zostanie użyty w charakterze separatora zależy
od ustawień w oknie dialogowym Właściwości: Ustawienia regionalne w Panelu sterowania systemu Windows).
<
Powoduje, że wszystkie litery zostaną zmienione na małe.
>
Powoduje, że wszystkie litery zostaną zmienione na wielkie.
!
Powoduje, że wszystkie dane są wyświetlane od strony prawej do lewej zamiast od lewej do prawej. Znaki wpisane do maski
wprowadzania zawsze będą ją wypełniać od strony lewej do strony prawej. Wykrzyknik może pojawić się w dowolnym
miejscu maski wprowadzania
\
Powoduje, że znak, który po nim występuje, zostanie wyświetlony jako znak literałowy (na przykład, \A będzie wyświetlone po
prostu jako A).
Struktura typowego zapytania
selekcyjnego - przykłady
SELECT * FROM Moduły
WHERE NazwaModułu = 'Dedukcyjne bazy danych’
Selekcja - przykłady
SELECT
 W celu połączenia
w jeden dwóch
łańcuchów znaków
należy wykorzystać
znak konkatenacji
‘||’
 Kolumny
wyliczone mogą
być nazwane przez
zastosowanie
klauzuli AS
FROM
WHERE
AND
AND
SELECT
FROM
w.nr_w,
p.nazwisko,
p.stanowisko,
p.dzial,
m .miasto,
m.ulica
pracownicy p, miejsca m,wypozyczenia w
p.nr_m=m.nr_m
p.nr_p=
w.prac_wyp
m.miasto = ‘WARSZAWA’
k.imie || ‘ ‘ || k.nazwisko AS Klient,
‘ul. ‘ || k.ulica || ‘ ‘ || k.numer AS
Ulica,
k.kod || ‘ ‘ || k.miasto AS Miasto
klienci k
Obliczenia w zapytaniach selekcyjnych
Ilosc)
1
2
3
4
Selekcja – przykłady cd..
Wyrażenie CASE pozwala na wybranie pewnej wartości w
zależności od wartości w innej kolumnie. W przykładzie
sprawdzamy czy klient pochodzi z Warszawy, jeśli tak to
wpisywana jest wartość „Klient oddziału macierzystego”, w
przeciwnym razie jest to Klient z przedstawicielstwa”.
SELECT k.imie, k.nazwisko, k.miasto ,
CASE k.miasto
WHEN ‘Warszawa’ THEN ‘Klient oddziału macierzystego’
ELSE ‘Klient z przedstawicielstwa’
END
FROM
klienci k
54
Rzut (projekcja)
Rzut (projekcja)
Rzut (projekcja)
Selekcja jako rzut (projekcja)
Jeśli w zapytaniu selekcyjnym pominięto warunki selekcji (warunek po
słowie kluczowym WHERE), to mamy do czynienia z rzutem (projekcją).
Jeżeli określimy nazwy kolumn w zapytaniu selekcyjnym, to instrukcja
SELECT języka SQL staje się kombinacją operatorów selekcji (RESTRICT) i
rzutu (PROJECT) algebry relacyjnej.
SELECT Poziom
FROM Moduły
Poziom
1
1
3
3
2
1
1
Wynik
selekcji
Struktura typowego zapytania
selekcyjnego - przykłady
Powtarzające się wartości są dozwolone w SQL ale są
niedozwolone w relacyjnym modelu danych. Aby uzyskać
prawdziwie relacyjny wynik na powyższe zapytanie, musimy do
klauzuli SELECT dodać słowo kluczowe DISTINCT (różne). Usuwa
to powtarzające się wartości w tabeli.
SELECT DISTINCT Poziom
FROM Moduły
Poziom
1
3
2
Wynik selekcji z
poprzedniego
zapytania
Wybór krotek z uporządkowaniem wyniku
wyszukiwania
SELECT Nazwisko, Imię, Zarobki
FROM pracownicy
WHERE Zarobki>1000)
ORDER BY Nazwisko;
Wybierz pola Nazwisko, Imię, Zarobki z rekordów tabeli o nazwie
pracownicy dla których pole Zarobki ma wartość większą niż 1000
i posortuj je wg pola Nazwisko
Aby uzyskać listę w porządku malejącym, do klauzuli ORDER BY
musimy dodać słowo kluczowe DESC po nazwie atrybutu .
Grupowanie krotek do obliczeń
klauzula GROUP BY
 Klauzula (instrukcja) GROUP BY dzieli dane wyselekcjonowane z
bazy danych na grupy, biorąc za podstawę wartości w określonej
kolumnie lub zbiorze kolumn, i umożliwia wykonanie obliczeń
podsumowujących (agregujących) na wartościach w każdej
grupie,
 Ta instrukcja dzieli dane na grupy, biorąc za podstawę wartości w
określonej kolumnie lub zbiorze kolumn, i umożliwia wykonanie
obliczenia podsumowującego wartości w każdej grupie. W
powyższym wypadku dla każdej grupy wykonujemy zliczenie
liczby wierszy w grupie i obliczenie średniej pensji w grupie.
 do grupy funkcji agregujących należą następujące funkcje:
Count( ) – oblicza ilość wystąpień,
Max( ) - wyznacza wartość największą w grupie,
Min( ) - wyznacza wartość najmniejszą w grupie wartości,
Avg() - wyznacza wartość średnią w grupie
Sum() - suma wartości w grupie
Funkcje agregujące - przykłady
Funkcje agregujące - przykłady
Grupowanie krotek do obliczeń
klauzula GROUP BY - przykład
SELECT NazwaWydziału, avg(Pensja), count(*) FROM
Wykładowcy
GROUP BY NazwaWydziału
NrPrac
NazwiskoPrac
Status
NazwaWydziału
Pensja
234
DaviasT
L
Studia informatyczne
16000.00
345
Evans R
PL
Studia informatyczne
26500.00
123
Smith J
L
Studia biznesu
16500.00
237
Jones S
SL
Studia informatyczne
23500.00
145
Thomas P
SL
Studia biznesu
23500.00
Tabela
Wykładowcy
wynik
Studia informatyczne 22000.00 3
Studia biznesu
20000.00 2
Grupowanie krotek do obliczeń
klauzula HAVING - przykład
Klauzula GROUP BY może również mieć swoją własną klauzulę
ograniczającą "WHERE ' - HAVING. Następująca instrukcja wyszukuje z
naszej bazy danych tylko te wydziały, które mają więcej niż dwóch
wykładowców:
NrPrac
NazwiskoPrac
Status
NazwaWydziału
Pensja
234
DaviasT
L
Studia informatyczne
16000.oo
345
Evans R
PL
Studia informatyczne
26500.00
123
Smith J
L
Studia biznesu
16500.00
237
Jones S
SL
Studia informatyczne
23500.00
145
Thomas P
SL
Studia biznesu
23500.00
Tabela
Wykładowcy
SELECT NazwaWydziału
FROM Wykładowcy
GROUP BY NazwaWydziału
HAVING count(*) > 2
wynik
Studia informatyczne
Funkcje agregujące - przykłady
Kolejność klauzul w zapytaniu
selekcyjnym
Select … From … Where …
Group by
Having
Order by
Przykłady zapytań selekcyjnych
1
2
3
Przykłady zapytań selekcyjnych
1
2
3
Przykłady zapytań selekcyjnych
Zapytania zagnieżdżone - przykład
Zapytania zagnieżdżone - przykład
Wykonywanie podzapytania może być powtarzane. W takim wypadku
otrzymujemy ciąg wartości do porównywania z wynikami najbardziej
zewnętrznego zapytania. Rozważmy na przykład następujące zadanie:
Wypisz listę nazwisk pracowników, nazw wydziałów i pensji wszystkich
wykładowców, którzy zarabiają więcej niż wynosi średnia pensja
pracownika ich wydziału.
Tabela
Wykładowcy
NrPrac
NazwiskoPrac
Status
NazwaWydziału
Pensja
234
DaviasT
L
Studia informatyczne
16000.oo
237
Jones S
SL
Studia informatyczne
23500.00
345
Evans R
PL
Studia informatyczne
26500.00
123
Smith J
L
Studia biznesu
16500.00
145
Thomas P
SL
Studia biznesu
23500.00
SELECT NazwiskoPrac,
NazwaWydziału, Pensja
FROM Wykładowcy L
WHERE Pensja>
(SELECT AVG(Pensja)
FROM Wykładowcy
WHERE L.NazwaWydziału
= NazwaWydziału)
Zapytania zagnieżdżone - przykład
Słowo "strukturalny" w strukturalnym języku zapytań (SQL) pierwotnie
odnosiło się do możliwości zagnieżdżania zapytań w instrukcjach SELECT.
Na przykład, aby znaleźć osobę, która zarabia więcej niż Jones
NrPrac
NazwiskoPrac
Status
NazwaWydziału
Pensja
234
DaviasT
L
Studia informatyczne
16000.oo
237
Jones S
SL
Studia informatyczne
23500.00
345
Evans R
PL
Studia informatyczne
26500.00
123
Smith J
L
Studia biznesu
16500.00
145
Thomas P
SL
Studia biznesu
23500.00
SQL
wykonuje
na
początku
najbardziej
wewnętrzne zapytanie, którego wynik jest
porównywany z wynikiem zwracanym przez
najbardziej zewnętrzne zapytanie.
Tabela
Wykładowcy
SELECT Nrprac, NazwiskoPrac
FROM Wykładowcy
WHERE Pensja >
(SELECT Pensja
FROM Wykładowcy
WHERE NazwiskoPrac =
'Jones S’')
Złączenia tabel - przykład
 SQL wykonuje złączenia relacyjne przez wskazanie wspólnych
atrybutów w klauzuli WHERE instrukcji SELECT. Na przykład poniższa
instrukcja SELECT wydobywa dane z tabel Wykładowcy i Moduły, które
są istotne dla osób pracujących na wydziale ‘studia informatyczne’.
 Konkretny warunek (lub warunki) użyty do określenia złączenia jest
nazywany warunkiem złączenia. W powyższym przykładzie warunkiem
złączenia jest L.NrPrac = M.NrPrac.
NrPrac
NazwiskoPrac
Status
NazwaWydziału
Pensja
234
DaviasT
L
Studia
informatyczne
16000.oo
237
Jones S
SL
Studia
informatyczne
23500.00
345
Evans R
PL
Studia
informatyczne
26500.00
123
Smith J
L
Studia biznesu
16500.00
145
Thomas P
SL
Studia biznesu
23500.00
Tabela
Wykładowcy
SELECT NazwiskoPrac, Pensja,
NazwaModulu
FROM Wykladowcy L, Modufiy M
WHERE L.NrPrac = M.NrPrac
Inne złączenia tabel
Złączenie (złączenie naturalne) tabel
Złączenie (złączenie naturalne) tabel
Złączenie (złączenie naturalne) tabel
Złączenie (złączenie naturalne) tabel
Złączenie (złączenie naturalne) tabel
NATURAL JOIN lub JOIN wykonuje złączenie dwóch tabel,
korzystając ze związku klucz główny - klucz obcy (wtórny), o których
informacja jest przechowywana w definicji tabel, przy założeniu, ze
kolumny złączenia mają tę samą nazwę w obu tabelach.
Operator złączenia naturalnego może być użyty w klauzuli FROM.
Jeżeli nazwa klucza głównego-klucza obcego są rożne, to zapytanie
może mieć następującą postać:
SELECT NazwaModułu, NazwiskoPrac
FROM Wykładowcy NATURAL JOIN Moduły
Jeżeli nazwa jest różna, musielibyśmy przepisać zapytanie w
następujący sposób:
SELECT NazwaModulu, NazwiskoPrac
FROM Wykładowcy L JOIN Moduły M
ON L.NrPrac = M.KodPrac
Złączenie (złączenie naturalne) tabel
Własności złączenia naturalnego
Własności złączenia naturalnego
Złączenie Θ-join
Złączenie Θ-join
Złączenie warunkowe - przykład
Złączenie zewnętrzne tabel
Złączenie zewnętrzne lewostronne
Złączenie zewnętrzne lewostronne
Złączenie zewnętrzne prawostronne
Złączenie zewnętrzne prawostronne
Złączenie zewnętrzne pełne
FULL OUTER JOIN
Przykłady złączeń
Tabele: Kobiety
Faceci
imie
Wiek
Imie
Wiek
Anna
23
Jan
33
Maria
34
Henryk
55
Sabina
43
Józef
21
Teresa
55
Marian
18
Wanda
33
Tomasz
44
Edyta
56
Zbigniew
76
Zofia
23
Mirosława
33
SELECT Faceci.Imie AS Pan, Faceci.Wiek AS WiekPana,
Kobiety.Imie AS Pani, Kobiety.Wiek AS WiekPani
FROM Faceci NATURAL JOIN Kobiety;
WYNIK=Zbiór pusty, bo złączenie naturalne wymaga
równości wszystkich kolumn
93
Przykłady złączeń cd..
SELECT Faceci.Imie AS Pan, Faceci.Wiek AS WiekPana,
Kobiety.Imie AS Pani, Kobiety.Wiek AS WiekPani
FROM Faceci JOIN Kobiety ON Faceci.Wiek = Kobiety.Wiek
ORDER BY WiekPana, Pan, WiekPani;
Pan
WiekPana
Pani
WiekPani
Jan
33
Wanda
33
Jan
33
Mirosława
33
Henryk
55
Teresa
55
SELECT Faceci.Imie AS Pan, Faceci.Wiek AS WiekPana,
Kobiety.Imie AS Pani, Kobiety.Wiek AS WiekPani
FROM Faceci NATURAL JOIN Kobiety;
Wynik=zbiór pusty
94
Przykłady złączeń cd..
SELECT Faceci.Imie AS Pan, Faceci.Wiek AS WiekPana,
Kobiety.Imie AS Pani, Kobiety.Wiek AS WiekPani
FROM Faceci JOIN Kobiety ON Faceci.Wiek <= Kobiety.Wiek
ORDER BY WiekPana, Pan, WiekPani;
Pan
WiekPana
Pani
WiekPani
Marian
18
Anna
23
Marian
18
Zofia
23
Marian
18
Mirosława
33
Marian
18
Maria
34
…………..
…………..
…………..
…………..
Henryk
55
Teresa
55
Henryk
55
Edyta
56
95
Złączenie RIGHT JOIN
SELECT … FROM
T1 RIGHT JOIN T2 ON <warunek złączenia>
WHERE <warunek wyboru>;
WYNIK=Prawie jak „zwykłe” złączenie, z tym, że wiersze z
prawej tabeli nie mające odpowiedników w lewej tabeli są
uzupełniane wartościami NULL
Kolejność tabel jest istotna!
96
Przykłady złączeń cd..
SELECT * FROM Faceci RIGHT JOIN Kobiety
ON Faceci.Wiek= Kobiety.Wiek;
Pan
WiekPana
Pani
WiekPani
Null
Null
Anna
23
Null
Null
Maria
34
Null
Null
Sabina
43
Henryk
55
Teresa
55
Jan
33
Wanda
33
Null
Null
Edyta
56
Null
Null
Zofia
23
Jan
33
Mirosława
33
97
Przykłady złączeń cd..
SELECT * FROM Kobiety LEFT JOIN Faceci
ON Faceci.Wiek= Kobiety.Wiek;
Pani
WiekPani
Pan
WiekPana
Anna
23
Null
Null
Maria
34
Null
Null
Sabina
43
Null
Null
Teresa
55
Henryk
55
Wanda
33
Jan
33
Edyta
56
Null
Null
Zofia
23
Null
Null
Mirosława
33
Jan
33
98
Przykłady złączeń cd..
SELECT * FROM Kobiety RIGHT JOIN Faceci
ON Faceci.Wiek = Kobiety.Wiek;
Pani
WiekPani
Pan
WiekPana
Wanda
33
Jan
33
Teresa
55
Henryk
55
Null
Null
Józef
21
Null
Null
Marian
18
Null
Null
Tomasz
44
Null
Null
Zbigniew
76
Mirosława
33
Jan
33
99
Przykłady złączeń cd..
Konstrukcje JOIN i LEFT JOIN są często traktowane jako
synonimy, ale to nieprawda:
Jeżeli w tabeli znajdującej się po prawej stronie ON w
konstrukcji LEFT JOIN nie ma żadnych wierszy, dla prawej
tabeli użyty zostanie wiersz z samymi wartościami NULL;
SELECT Kobiety.* FROM Kobiety JOIN Faceci ON
Kobiety.Wiek = Faceci.Wiek
WHERE Faceci.Wiek IS NULL;
Wynik jest zbiorem pustym
100
Przykłady złączeń cd..
SELECT Kobiety.* FROM Kobiety LEFT JOIN Faceci
ON Kobiety.Wiek = Faceci.Wiek
WHERE Faceci.Wiek IS NULL;
imie
Wiek
Anna
23
Maria
34
Sabina
43
Edyta
56
Zofia
23
Znajdź wiersze
tabeli Kobiety nie
mające
odpowiedników w
tabeli Faceci
101
Złączenie zewnętrzne pełne
FULL OUTER JOIN
Złączenie zewnętrzne tabel
W SQL2 występuje również standardowa składnia złączeń
zewnętrznych. Na przykład lewostronne, prawostronne i obustronne
złączenia zewnętrzne zostałyby określone w SQL2 w następujący
sposób:
SELECT *
FROM Wykladowcy L LEFT OUTER JOIN Moduly M
ON L.NrPrac = M.KodPrac
SELECT *
FROM Wykladowcy L RIGHT OUTER JOIN Moduly M
ON L.NrPrac = M.KodPrac
SELECT NazwaModulu, NazwiskoPrac
FROM Wykladowcy L FULL OUTER JOIN Moduly M
ON L.NrPrac = M.KodPrac
Iloczyn kartezjański krotek
Iloczyn kartezjański krotek
Suma tabel - przykład
 Operator sumy języka SQL odpowiada operatorowi sumy algebry
relacyjnej.
 Daje on możliwość połączenia wyników dwóch zgodnych zapytań.
 Na przykład poniższe zapytanie produkuje wynik łączący informacje na
temat modułów ‘studia informatyczne’ z modułami na ‘wydziale
elektrycznym’.
NrPrac
NazwiskoPrac
Status
NazwaWydziału
Pensja
234
DaviasT
L
Studia informatyczne
16000.oo
237
Jones S
SL
Studia informatyczne
23500.00
345
Evans R
PL
Studia informatyczne
26500.00
123
Smith J
L
Studia biznesu
16500.00
145
Thomas P
SL
Studia biznesu
23500.00
Tabela
Wykładowcy
SELECT NazwaModulu, Poziom
FROM Moduly
WHERE KodKursu = 'CSD'
UNION
SELECT NazwaModulu, Poziom
FROM Moduly WHERE
KodKursu = 'EED'
Suma tabel - przykład
Suma tabel - przykład
Suma tabel - przykład
np.:
Różnica tabel - przykład
Np.:
Różnica tabel - przykład
Różnica tabel z wykorzystaniem
EXCEPT - przykład
lub
Np.:
Różnica tabel z wykorzystaniem złączeń
- przykład
Np.:
Iloczyn tabel - przykład
 Operator iloczynu języka SQL odpowiada operatorowi iloczynu algebry
relacyjnej.
 Daje on możliwość porównywania wyników dwóch zgodnych zapytań.
 Na przykład poniższe zapytanie produkuje wynik wspólnych
informacje na temat modułów ‘studia informatyczne’ z modułami na
‘wydziale elektrycznym’.
NrPrac
NazwiskoPrac
Status
NazwaWydziału
Pensja
234
DaviasT
L
Studia informatyczne
16000.oo
237
Jones S
SL
Studia informatyczne
23500.00
345
Evans R
PL
Studia informatyczne
26500.00
123
Smith J
L
Studia biznesu
16500.00
145
Thomas P
SL
Studia biznesu
23500.00
Tabela
Wykładowcy
SELECT NazwaModulu, Poziom
FROM Moduly
WHERE KodKursu = 'CSD'
INTERSECT
SELECT NazwaModulu, Poziom
FROM Moduly WHERE
KodKursu = 'EED'
Iloczyn tabel - przykład
Podzielenie tabel (division)
Podzielenie tabel (division)
Operacje z wykorzystaniem kursora
Operacje z wykorzystaniem kursora
Operacje pozycyjne UPDATE,
DELETE z wykorzystaniem kursora
Perspektywy – widoki (views)
Perspektywy – widoki (views)
Modyfikacja perspektyw
Modyfikacja perspektyw cd..
Modyfikacja perspektyw cd..
Modyfikacja perspektyw cd..
Modyfikacja perspektyw cd..
Modyfikacja perspektyw cd..
Zabezpieczenia baz danych
129
Zabezpieczenia baz danych cd..
130
Spójność bazy danych
131
Rodzaje spójności bazy danych
132
Integralność referencyjna - przykład
 Integralność referencyjną definiujemy już w SQL89 przez
specyfikację klucza obcego. Poniższe definicje określają,
że NrPrac w tabeli Moduły ma zostać ustawione na null,
jeżeli powiązany rekord wykładowcy jest usuwany.
 Więzy integralności referencyjnej określają również, że
jeśli dokonamy jakiejkolwiek zmiany w numerze
pracownika w rekordzie wykładowcy, to zmiana ta
powinna zostać odzwierciedlona w powiązanych
rekordach modułów.
133
Integralność referencyjna cd..przykładu
CREATE TABLE Wykładowcy
(Nrprac Number(5),
Nazwiskoprac Varchar(15),
Status Varchar(10),
NazwaWydziału(Varchar(20),
Pensja Decimal(7, 2),
PRIMARY KEY (Nrprac))
134
Integralność referencyjna cd..przykładu
CREATE TABLE Moduły
(NazwaModulu Char(15),
Poziom Smallint,
KodKursu Char(3),
NrPrac Number(5),
PRIMARY KEY (NazwaModutu)
FOREIGN KEY (Nrprac) references Wykladowcy)
ON DELETE SET NULL
ON UPDATE CASCADE)
135
Integralność referencyjna cd..
W SQL2 opcjami propagacji są NO ACTION,
CASCADE, SET DEFAULT i SET NULL.
 Opcje CASCADE i SET NULL odpowiadają
odpowiednio operacji kaskadowej propagacji
(CASCADES) i ustawienia NULL (NULLIFIES).
 Opcja NO ACTION częściowo odpowiada, ale nie
do końca, operacji RESTRICTED.
 Opcja SET DEFAULT wymusza na systemie
używanie zadeklarowanej wartości domyślnej
136
Integralność dziedziny
 Integralność
dziedziny możemy częściowo
określać podając odpowiedni typ danych dla
kolumny.
 Możemy tez użyć klauzuli CHECK, aby wymusić
poprawne modyfikacje.
 Możemy na przykład wymusić, aby wartość
wstawiana do kolumny poziom była w
określonym
zbiorze
lub
aby
numery
pracowników mieściły się w podanym zakresie:
137
Integralność dziedziny - przykłady
CREATE TABLE Moduły
(NazwaModułu Char(IS),
Poziom Smallint,
KodKursu Char(3),
Nrprac Number(5),
PRIMARY KEY (NazwaModułu)
FOREIGN KEY (Nrprac IDENTIFIES Wykładowcy)
ON DELETE RESTRICT
ON UPDATE CASCADE
CHECK (Poziom IN 1, 2, 3))
CREATE TABLE Wykładowcy
NrPrac Number(5),
NazwiskoPrac Varchar(15),
Status Varchar( 10),
NazwaWydziatu(Varchar(20),
Pensja Decimal(7, 2),
PRIMARY KEY (NrPrac)
CHECK (NrPrac BETWEEN 100 AND 10999))
138
Integralność danych cd..
 Mówimy, że baza danych ma właściwość integralności,
kiedy istnieje odpowiedniość między faktami
przechowywanymi w bazie danych a światem rzeczywistym
modelowanym przez tą bazę.
 Tą właśnie integralność zapewniają reguły integralności,
które można podzielić na dwa rodzaje: integralność encji
oraz integralność referencyjną.
 Integralność encji dotyczy kluczy głównych. Mówi ona, że
każda tabela musi mieć klucz główny i że kolumna lub
kolumny wybrane jako klucz główny powinny być
jednoznaczne i nie zawierać wartości null. Wynika stąd, że
w tabeli są zabronione powtórzenia wierszy.
139
Integralność danych cd..
 Integralność referencyjna dotyczy kluczy obcych. Mówi
ona, że wartość klucza obcego może się znajdować tylko w
jednym z dwóch stanów.
 Wartość klucza obcego odwołuje się do wartości klucza
głównego w tabeli w bazie danych. Czasami wartość klucza
obcego może być null, co oznacza że nie ma związku
między reprezentowanymi obiektami w bazie danych albo
że ten związek jest nieznany.
 Utrzymywanie integralności referencyjnej oprócz określenia
czy klucz obcy jest null, czy nie obejmuje również
określenie więzów propagacji. Mówią one co powinno się
stać z powiązaną tabelą, gdy modyfikujemy wiersz lub
wiersze w tabeli docelowej.
140
Integralność danych cd..
 Są trzy możliwości, które określają co się będzie
działo z docelowymi i powiązanymi krotkami dla
każdego związku między tabelami w naszej bazie:
 Ograniczone usuwanie (Restricted). Podejście
ostrożne – nie dopuszcza do usuwania rekordu
nadrzędnego, jeśli istnieją rekordy podrzędne.
 Kaskadowe usuwanie (Cascades). Podejście
ufne – przy usuwaniu rekordu nadrzędnego
usuwa także rekordy podrzędne.
 Izolowane usuwanie (Isolated). Podejście
wyważone – usuwa jedynie rekord nadrzędny.
141
Asercje
 Więzy mogą być nazywane i określane niezależnie od
jakiejkolwiek tabeli lub dziedziny. W takim wypadku więzy są
nazywane asercjami.
 Możemy na przykład zadeklarować asercję, określającą
następujące sprawdzanie niezależnie od tabeli Wykładowcy:
CREATE ASSERTION NrPracCheck
CHECK (NrPrac BETWEEN 100 AND 10999)
142
Asercje
Za każdym razem, gdy instrukcja SQL dokonuje wstawienia, modyfikacji
bądź usunięcia wiersza tabeli, istnieje możliwość, że więzy mogą zostać
naruszone.
SQL89 wymaga, aby system sprawdzał naruszanie więzów pod koniec
wykonywania każdej instrukcji.
 SQL2 umożliwia sprawdzanie więzów pod koniec transakcji. Jeżeli
więzy są sprawdzane po każdej instrukcji, to mówimy , ze sprawdzanie
odbywa się w trybie natychmiastowym. Jeżeli sprawdzenie następuje
pod koniec transakcji, to mówimy, że sprawdzanie odbywa się w trybie
opóźnionym.
Dlatego dla każdej definicji więzów możemy dołączyć specyfikację tego,
czy więzy są sprawdzane z opóźnieniem (DEFERRABLE), czy
sprawdzane natychmiast (NOT DEFERRABLE).
Początkowy tryb więzów może być określony jako INITIALLY
DEFERRED lub INITIALLY IMMEDIATE. Tryb sprawdzania więzów może
być następnie w czasie sesji zmieniony za pomocą instrukcji SET
CONSTRAINTS, która określa, czy dla listy nazwanych więzów
143
wykonywać sprawdzanie opóźnione czy natychmiastowe.
Zatwierdzanie zmian w bazie danych
 Instrukcje INSERT, DELETE, UPDATE nie
dokonują same trwałych zmian w bazie danych.
Aby zmiany wprowadzone przez nie utrwalić,
należy wykonać instrukcję COMMIT.
 Można również zrezygnować z wprowadzania
zmian do bazy danych, wycofując je za pomocą
instrukcji ROLLBACK.
 W PostgreSQL domyślnie jest włączona opcja
auto-zatwierdzania więc nie trzeba przy
pojedynczych instrukcjach wykonywać COMMIT.
144