Języki zapytań do baz danych

SQL

Języki zapytań do relacyjnych baz
danych

Języki oparte na

algebrze relacji (ISBL w

Ingresie)

Języki oparte na

predykatach

Języki oparte

na rachunku

dziedzin (QBL)

Języki oparte na

rachunku

krotek (QUEL)

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, W-wa, 1999



Stephans R.: SQL w 3 tygodnie. LT&P, W-wa, 1999



http://galaxy.uci.agh.edu.pl/chwastek/lectures/db/dbtitl

e.html



http://baszta.iie.ae.wroc.pl/index.html



http://www.cs.put.poznan.pl/kjankiewicz/oracle/sql/inde

x.html



http://www.cs.put.poznan.pl/rwrembel/courses/sbd.htm

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).

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), (SQL-98),



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

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

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ą

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).

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,

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.

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

));

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

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

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'

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

UPDATE Wykładowcy SET Pensja =
Pensja*1.1
WHERE Status = 'PL'

Na przykład następujące polecenie zwiększy o 10% pensję
wszystkich wykładowców o statusie PL:

Operacje „mnogościowe” w
SQL na danych bazy danych

Kos

Znak

Dos

Bios

Dos

Bios

Znak

Dos

Bios

Bios

Znak

Dos

Bios

Kos

Znak

Dos

Bios

Suma tabel

Różnica tabel

Iloczyn tabel

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

;

Like w Access

Znak

Opis

Cyfra (Od 0 do 9, pozycja wymagana, znaki plus [+] i minus [–] nie są dozwolone).

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).

Litera (od A do Z, pozycja wymagana).

Litera lub cyfra (pozycja wymagana).

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).

Selekcja - przykłady

SELECT

w.nr_w,
p.nazwisko,

p.stanowisko,

p.dzial,
m .miasto,

m.ulica

FROM

pracownicy p, miejsca

m,wypozyczenia w
WHERE

p.nr_m=m.nr_m

AND

p.nr_p=
w.prac_wyp

AND

m.miasto = ‘WARSZAWA’



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

SELECT

k.imie || ‘ ‘ || k.nazwisko AS

Klient,

‘ul. ‘ || k.ulica || ‘ ‘ || k.numer

AS
Ulica,

k.kod || ‘ ‘ || k.miasto

Miasto
FROM

klienci k

Selekcja – przykłady cd..

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

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”.

Selekcja jako rzut (projekcja)

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

Jeśli w zapytaniu selekcyjnym pominięto warunki selekcji
(warunek po słowie kluczowym WHERE), to mamy do czynienia z
rzutem (projekcją).

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

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

dzieli

dane

wyselekcjonowane z bazy danych na grupy, biorąc za

podstawę wartości w określonej kolumnie lub zbiorze

kolumn,

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

Grupowanie krotek do
obliczeń klauzula GROUP
BY - przykład

SELECT NazwaWydziału, avg(Pensja), count(*)
FROM Wykładowcy
GROUP BY NazwaWydziału

Studia informatyczne
22000.00 3

Studia biznesu
20000.00 2

NrPrac

NazwiskoPrac

Status

NazwaWydziału

Pensja

234

DaviasT

Studia

informatyczne

16000.00

345

Evans R

Studia
informatyczne

26500.00

123

Smith J

Studia biznesu

16500.00

237

Jones S

Studia
informatyczne

23500.00

145

Thomas P

Studia biznesu

23500.00

Tabela

Wykładowcy

wynik

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:

tudia informatyczne

NrPrac

NazwiskoPrac

Status

NazwaWydziału

Pensja

234

DaviasT

Studia

informatyczne

16000.oo

345

Evans R

Studia
informatyczne

26500.00

123

Smith J

Studia biznesu

16500.00

237

Jones S

Studia
informatyczne

23500.00

145

Thomas P

Studia biznesu

23500.00

Tabela

Wykładowcy

wynik

SELECT NazwaWydziału
FROM Wykładowcy

GROUP BY

NazwaWydziału
HAVING count(*) > 2

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

pensji

wszystkich

wykładowców, którzy zarabiają więcej niż wynosi średnia
pensja pracownika ich wydziału.

NrPrac

NazwiskoPrac

Status

NazwaWydziału

Pensja

234

DaviasT

Studia
informatyczne

16000.oo

237

Jones S

Studia

informatyczne

23500.00

345

Evans R

Studia

informatyczne

26500.00

123

Smith J

Studia biznesu

16500.00

145

Thomas P

Studia biznesu

23500.00

Tabela

Wykładowcy

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

Studia

informatyczne

16000.oo

237

Jones S

Studia
informatyczne

23500.00

345

Evans R

Studia

informatyczne

26500.00

123

Smith J

Studia biznesu

16500.00

145

Thomas P

Studia biznesu

23500.00

Tabela

Wykładowcy

SELECT Nrprac,
NazwiskoPrac
FROM Wykładowcy
WHERE Pensja >
(

SELECT

Pensja

FROM

Wykładowcy

WHERE

NazwiskoPrac

=
'Jones S’

)

SQL wykonuje

na początku najbardziej

wewnętrzne zapytanie

, którego wynik

jest

porównywany

wynikiem

zwracanym przez najbardziej zewnętrzne
zapytanie.

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

Studia
informatyczne

16000.oo

237

Jones S

Studia

informatyczne

23500.00

345

Evans R

Studia
informatyczne

26500.00

123

Smith J

Studia biznesu

16500.00

145

Thomas P

Studia biznesu

23500.00

Tabela

Wykładowcy

SELECT NazwiskoPrac,
Pensja, NazwaModulu
FROM

Wykladowcy L,

Modufiy M

WHERE L.NrPrac =
M.NrPrac

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

Przykłady złączeń

Tabele:

Kobiety Faceci

imie

Wiek

Anna

Maria

Sabina

Teresa

Wanda

Edyta

Zofia

Mirosława

Imie

Wiek

Jan

Henryk

Józef

Marian

Tomasz

Zbigniew

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

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

Wanda

Jan

Mirosława

Henryk

Teresa

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

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

Anna

Marian

Zofia

Marian

Mirosława

Marian

Maria

…………..

Henryk

Teresa

Henryk

Edyta

Złączenie RIGHT JOIN

SELECT … FROM
T1

RIGHT JOIN

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!

Przykłady złączeń cd..

SELECT * FROM Faceci

RIGHT JOIN

Kobiety
ON Faceci.Wiek= Kobiety.Wiek;

Pan

WiekPana

Pani

WiekPani

Null

Anna

Null

Maria

Null

Sabina

Henryk

Teresa

Jan

Wanda

Null

Edyta

Null

Zofia

Jan

Mirosława

Przykłady złączeń cd..

SELECT * FROM Kobiety

LEFT JOIN

Faceci

ON Faceci.Wiek= Kobiety.Wiek;

Pani

WiekPani

Pan

WiekPana

Anna

Null

Maria

Null

Sabina

Null

Teresa

Henryk

Wanda

Jan

Edyta

Null

Zofia

Null

Mirosława

Jan

Przykłady złączeń cd..

SELECT * FROM Kobiety

RIGHT JOIN

Faceci
ON Faceci.Wiek = Kobiety.Wiek

;

Pani

WiekPani

Pan

WiekPana

Wanda

Jan

Teresa

Henryk

Null

Józef

Null

Marian

Null

Tomasz

Null

Zbigniew

Mirosława

Jan

100

Przykłady złączeń cd..

Konstrukcje

JOIN

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

101

Przykłady złączeń cd..

SELECT Kobiety.* FROM Kobiety

LEFT JOIN

Faceci

ON Kobiety.Wiek = Faceci.Wiek
WHERE Faceci.Wiek IS NULL;

Znajdź wiersze
tabeli Kobiety
nie mające
odpowiedników
w tabeli Faceci

imie

Wiek

Anna

Maria

Sabina

Edyta

Zofia

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

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

Studia
informatyczne

16000.oo

237

Jones S

Studia

informatyczne

23500.00

345

Evans R

Studia

informatyczne

26500.00

123

Smith J

Studia biznesu

16500.00

145

Thomas
P

Studia biznesu

23500.00

Tabela

Wykładowcy

SELECT

NazwaModulu,

Poziom

FROM

Moduly

WHERE KodKursu = 'CSD'

UNION
SELECT

NazwaModulu,

Poziom

FROM

Moduly

WHERE

KodKursu = 'EED'

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

Studia
informatyczne

16000.oo

237

Jones S

Studia

informatyczne

23500.00

345

Evans R

Studia

informatyczne

26500.00

123

Smith J

Studia biznesu

16500.00

145

Thomas
P

Studia biznesu

23500.00

Tabela

Wykładowcy

SELECT

NazwaModulu,

Poziom

FROM

Moduly

WHERE KodKursu = 'CSD'

INTERSECT
SELECT

NazwaModulu,

Poziom

FROM

Moduly

WHERE

KodKursu = 'EED'

133

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

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

135

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

)

136

Integralność referencyjna
cd..

W SQL2 opcjami propagacji są

NO ACTION

CASCADE, SET DEFAULT i SET NULL



Opcje

CASCADE

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

137

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:

138

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

))

139

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.

140

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.

141

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.

142

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

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)

143

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
   wykonywać sprawdzanie opóźnione czy natychmiastowe.

144

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

Document Outline