cz 2 Języki zapytań do baz danych

background image

1

BAZY DANYCH

Część II

Opracowanie : Dr hab. Bożena Śmiałkowska

background image

Cechy języków zapytań do baz danych

Deklaratywny charakter,

Zanurzenie w języku programowania,

Wysoki poziom abstrakcji

background image

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

]

background image

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/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

background image

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

background image

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

background image

Zalety SQL

background image

Wady i ograniczenia SQL

background image

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.

background image

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.

background image

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.

background image

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

background image

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.

background image

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,

background image

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

background image

SQL – postać poleceń (zapytań)

background image

Przykładowe pełne określenie
zapytania

background image

Przykładowe zapytanie
sparametryzowane

background image

Przykładowe zapytanie
sparametryzowane

background image

Zapytania dynamiczne - przykład

background image

Zapytania dynamiczne - przykład

background image

Komponenty SQL

background image

Podstawowe struktury danych w SQL

background image

Podstawowe struktury danych cd..

background image

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,

background image

Zasady konstrukcji wyrażeń

background image

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,

background image

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

.

background image

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.

background image

Obiektowy model SQL3

background image

Rozszerzenie typów o obiekty w SQL3

background image

32

Komponenty SQL

background image

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>)]);

background image

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.

background image

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

background image

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

));

background image

Zakładanie tabel bazy danych- przykłady

background image

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

background image

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

background image

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

).

background image

Wstawianie danych do bazy danych

Polecenie INSERT INTO jest wykorzystywane do dopisywania
(wstawiania) pojedynczych wierszy do tabeli bazy danych wcześniej
wykreowanej np.. Z użyciem CREATE TABLE

INSERT INTO

Tabela

[(kolumna1, kolumna2, …, kolumnak)]

VALUES

(listaWartosci)

;

INSERT INTO

Tabela

;

lub

background image

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

background image

43

Wstawianie danych do bazy danych

INSERT INTO

Ksiazki

VALUES (

’83-87102-55-5’, ‘Harrington’, ‘SQL

dla każdego’, ‘EDU-Mikron’, 1998,
‘Warszawa’

);

INSERT INTO

Ksiazki (ISBN, Autor, Tytul)

VALUES (

’83-87102-55-5’, ‘Harrington’, ‘SQL dla każdego’

);

background image

Usuwanie danych z bazy danych

Polecenia DELETE używamy do usuwania wierszy z tabeli. Wiersze
do usunięcia podajemy w klauzuli WHERE.

DELETE FROM

Wykładowcy

WHERE

NazwaWydziału = 'Studia informatyczne'

;

np.:

DELETE FROM

tabela

WHERE

warunek

;

background image

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

Tabela

SET

kolumna1 = NowaWartość,

kolumna2 = NowaWartość,

kolumnak = NowaWartość

WHERE

Warunek

;

background image

Aktualizowanie danych w bazie danych

UPDATE

Wykładowcy

SET

Pensja = Pensja*1.1

WHERE

Status = 'PL'

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

background image

Operacje w SQL na danych bazy danych

Selekcja

projekcja

Łączenie (join) dwóch tabel

background image

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

Znak

34

z

Kos

44

S

Bios

32

w

Dos

11

a

Bios

32

w

Dos

11

a

Znak

43

z

Bios

32

w

Bios

32

w

Dos

11

a

Znak

43

z

Bios

32

w

Bios

32

w

Dos

11

a

Znak

34

z

Kos

44

S

Dos

11

a

Bios

32

w

Suma tabel

Różnica tabel

Iloczyn tabel

background image

Struktura typowego zapytania
selekcyjnego

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.

Proste wyszukiwanie jest wykonywane dzięki kombinacji klauzul
SELECT, FROM i WHERE:

background image

Struktura typowego zapytania
selekcyjnego

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

;

background image

Warunki w zapytaniach selekcyjnych

W zapytaniach selekcyjnych używa się:

Operatory relacyjne

Operatory logiczne

Operatory specjalne

OPERATORY RELACYJNE

=

,

<

,

>

,

<=

,

>=

,

!=

(lub symbol <>)

służą do porównania liczb, dat, napisów

Napisy muszą być zapisane w apostrofie. Data i godzina muszą być
zapisane zgodnie z formatem stosowanym w DBMS.

background image

Warunki w zapytaniach selekcyjnych

OPERATORY LOGICZNE

AND

,

OR

,

NOT

wraz nawiasami służą do konstrukcji złożonych warunków
logicznych (algebraicznie – odpowiadających iloczynowi, sumie
i dopełnieniu). Wyznaczenie wartości logiczne przebiega od
lewej do prawej (o ile nie ma nawiasów).

background image

Warunki w zapytaniach selekcyjnych

OPERATORY SPECJALNE

BETWEEN

LIKE

IN

IS NULL

Służą do definiowania warunków złożonych selekcji.

Operator LIKE pozwala na porównanie łańcuchów znaków z użyciem
symbolu specjalnego % oznaczającego dowolny ciąg znaków oraz _
do porównywania pojedynczego symbolu. Operatory specjalne mogą
być negowane z użyciem operatora NOT.

background image

Like w Access

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

\

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 wszystkie litery zostaną zmienione na wielkie.

>

Powoduje, że wszystkie litery zostaną zmienione na małe.

<

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

. , : ; - /

Dowolny znak lub spacja (pozycja wymagana).

C

Dowolny znak lub spacja (pozycja wymagana).

&

Litera lub cyfra (pozycja wymagana).

a

Litera lub cyfra (pozycja wymagana).

A

Litera (od A do Z, pozycja wymagana).

?

Litera (od A do Z, pozycja wymagana).

L

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

#

Cyfra lub spacja (pozycja nie jest wymagana, znaki plus i minus nie są dozwolone).

9

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

0

Opis

Znak

background image

Struktura typowego zapytania
selekcyjnego - przykłady

SELECT * FROM Moduły

WHERE NazwaModułu = 'Dedukcyjne bazy danych’

background image

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

AS

Miasto

FROM

klienci k

background image

Obliczenia w zapytaniach selekcyjnych

1

2

3

4

Ilosc)

background image

58

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

background image

59

Wstawianie danych do bazy danych z

użyciem SELECT

INSERT INTO TabelaA

[(kolumnaA1, kolumnaA2, …, kolumnaAk)]

SELECT

kolumnaB1, kolumnaB2, …, kolumnaBk

FROM

TabelaB

WHERE

WarunekWyboruWierszy

;

INSERT INTO

Ksiazki

SELECT

ISBN, Autor, Tytul, Wydawnictwo, Rok, Miejsce

FROM

ZamowioneKsiazki

WHERE

status=‘dostarczone’

;

background image

60

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'

background image

Rzut (projekcja)

background image

Rzut (projekcja)

background image

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

background image

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

background image

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 .

background image

66

Funkcje agregujące w SELECT

Funkcja agregujaca

Funkcja

Funkcja

agregujaca

agregujaca

Opis

Opis

Opis

AVG

AVG

Średnia wartości numerycznych

Średnia wartości numerycznych

COUNT

COUNT

Liczba wartości w wyrażeniu

Liczba wartości w wyrażeniu

COUNT (*)

COUNT (*)

Liczba wybranych wierszy

Liczba wybranych wierszy

MAX

MAX

Najwyższa wartość w wyrażeniu

Najwyższa wartość w wyrażeniu

MIN

MIN

Najniższa wartość w wyrażeniu

Najniższa wartość w wyrażeniu

SUM

SUM

Suma wartości w wyrażeniu

Suma wartości w wyrażeniu

STDEV

STDEV

Odchylenie statystyczne wartości

Odchylenie statystyczne wartości

STDEVP

STDEVP

Odchylenie statystyczne populacji

Odchylenie statystyczne populacji

VAR

VAR

Wariancja wartości

Wariancja wartości

VARP

VARP

Wariancja populacji

Wariancja populacji

background image

67

Funkcje agregujące

SELECT AVG(unitprice)

FROM products

SELECT AVG(unitprice)

FROM products

SELECT SUM(quantity)

FROM orderdetails

SELECT SUM(quantity)

FROM orderdetails

Przykład 1

Przykład 1

Przykład 2

Przykład 2

SELECT AVG(DISTINCT unitprice)

FROM products

SELECT AVG(DISTINCT unitprice)

FROM products

Przykład 1

Przykład 1

background image

68

Funkcje agregujące COUNT( )

SELECT COUNT (*)

FROM employees

SELECT COUNT (*)

FROM employees

SELECT COUNT(reportsto)

FROM employees

SELECT COUNT(reportsto)

FROM employees

Przykład 1

Przykład 1

Przykład 2

Przykład 2

background image

69

Klauzula GROUP BY

SELECT product, order

,quantity

FROM orderhist

SELECT product, order

,quantity

FROM orderhist

SELECT product

,SUM(quantity) AS total_quantity

FROM orderhist
GROUP BY product

SELECT product

,SUM(quantity) AS total_quantity

FROM orderhist
GROUP BY product

product

product

product

total_quantity

total_quantity

total_quantity

1

1

15

15

2

2

35

35

3

3

45

45

product

product

product

order

order

order

quantity

quantity

quantity

1

1

1

1

5

5

1

1

1

1

10

10

2

2

1

1

10

10

2

2

2

2

25

25

3

3

1

1

15

15

3

3

2

2

30

30

product

product

product

total_quantity

total_quantity

total_quantity

2

2

35

35

Tylko wiersze
spełniające warunek
w WHERE wirtualnie
są grupowane

SELECT product

,SUM(quantity) AS total_quantity

FROM orderhist
WHERE product = 2
GROUP BY product

SELECT product

,SUM(quantity) AS total_quantity

FROM orderhist
WHERE product = 2
GROUP BY product

background image

70

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

23500.00

Studia informatyczne

SL

Jones S

237

23500.00

Studia biznesu

SL

Thomas P

145

16500.00

Studia biznesu

L

Smith J

123

26500.00

Studia informatyczne

PL

Evans R

345

16000.00

Studia informatyczne

L

DaviasT

234

Pensja

NazwaWydziału

Status

NazwiskoPrac

NrPrac

Tabela

Wykładowcy

wynik

background image

71

Klauzula GROUP BY z klauzulą
HAVING

SELECT product, order

,quantity

FROM orderhist

SELECT product, order

,quantity

FROM orderhist

SELECT product, SUM(quantity)

AS total_quantity

FROM orderhist
GROUP BY product
HAVING SUM(quantity)>=30

SELECT product, SUM(quantity)

AS total_quantity

FROM orderhist
GROUP BY product
HAVING SUM(quantity)>=30

product

product

product

total_quantity

total_quantity

total_quantity

2

2

35

35

3

3

45

45

product

product

product

order

order

order

quantity

quantity

quantity

1

1

1

1

5

5

1

1

1

1

10

10

2

2

1

1

10

10

2

2

2

2

25

25

3

3

1

1

15

15

3

3

2

2

30

30

background image

72

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:

Studia informatyczne

23500.00

Studia
informatyczne

SL

Jones S

237

23500.00

Studia biznesu

SL

Thomas P

145

16500.00

Studia biznesu

L

Smith J

123

26500.00

Studia
informatyczne

PL

Evans R

345

16000.oo

Studia
informatyczne

L

DaviasT

234

Pensja

NazwaWydziału

Status

NazwiskoPrac

NrPrac

Tabela

Wykładowcy

wynik

SELECT NazwaWydziału
FROM Wykładowcy

GROUP BY

NazwaWydziału

HAVING count(*) > 2

background image

Funkcje agregujące - przykłady

Wartości towarów w poszczególnych grupach towarowych:

SELECT

T.GrupaTow, SUM(T.CenaZak*TM.Stan) as Wartosc

FROM

Towat T, TowMag TM

WHERE

T.NrTow = TM.NrTow

GROUP BY

T.GrupaTow

HAVING

GrupaTow <> ‘AGD”;

45 000.00

RTV

24 000.00

KOM

Wartosc

GrupaTow

background image

74

Klauzula GROUP BY

SELECT product,SUM(quantity) AS
total_quantity

FROM orderdetails

GROUP BY product

SELECT product,SUM(quantity) AS
total_quantity

FROM orderdetails

GROUP BY product

SELECT product, SUM (quantity) AS
total_quantity

FROM orderdetails

GROUP BY product
HAVING SUM(quantity) > 1200

SELECT product, SUM (quantity) AS
total_quantity

FROM orderdetails

GROUP BY product
HAVING SUM(quantity) > 1200

Przykład 2

Przykład 2

Przykład 1

Przykład 1

background image

Kolejność klauzul w zapytaniu
selekcyjnym

Select … From … Where …

Group by

Having

Order by

background image

Przykłady zapytań selekcyjnych

1

2

3

background image

Przykłady zapytań selekcyjnych

4

5

6

background image

Przykłady zapytań selekcyjnych

background image

79

Klauzule COMPUTE oraz
COMPUTE BY

COMPUTE BY

COMPUTE

SELECT product, order, quantity

FROM orderhist
ORDER BY product, order
COMPUTE SUM(quantity) BY product
COMPUTE SUM(quantity)

SELECT product, order, quantity

FROM orderhist
ORDER BY product, order
COMPUTE SUM(quantity) BY product
COMPUTE SUM(quantity)

SELECT product, order

,quantity

FROM orderhist
ORDER BY product, order
COMPUTE SUM(quantity)

SELECT product, order

,quantity

FROM orderhist
ORDER BY product, order
COMPUTE SUM(quantity)

product

product

product

order

order

order

quantity

quantity

quantity

1

1

1

1

5

5

1

1

2

2

10

10

2

2

1

1

10

10

2

2

2

2

25

25

3

3

1

1

15

15

3

3

2

2

30

30

sum

sum

95

95

product

product

product

order

order

order

quantity

quantity

quantity

1

1

1

1

5

5

1

1

2

2

10

10

sum

sum

15

15

2

2

1

1

10

10

2

2

2

2

25

25

sum

sum

35

35

3

3

1

1

15

15

3

3

2

2

30

30

sum

sum

45

45

sum

sum

95

95

background image

Zapytania zagnieżdżone - przykład

background image

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.

23500.00

Studia biznesu

SL

Thomas P

145

16500.00

Studia biznesu

L

Smith J

123

26500.00

Studia informatyczne

PL

Evans R

345

23500.00

Studia informatyczne

SL

Jones S

237

16000.oo

Studia informatyczne

L

DaviasT

234

Pensja

NazwaWydziału

Status

NazwiskoPrac

NrPrac

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)

background image

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

23500.00

Studia biznesu

SL

Thomas P

145

16500.00

Studia biznesu

L

Smith J

123

26500.00

Studia informatyczne

PL

Evans R

345

23500.00

Studia informatyczne

SL

Jones S

237

16000.oo

Studia informatyczne

L

DaviasT

234

Pensja

NazwaWydziału

Status

NazwiskoPrac

NrPrac

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

z wynikiem

zwracanym

przez

najbardziej zewnętrzne zapytanie.

background image

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.

23500.00

Studia biznesu

SL

Thomas P

145

16500.00

Studia biznesu

L

Smith J

123

26500.00

Studia
informatyczne

PL

Evans R

345

23500.00

Studia
informatyczne

SL

Jones S

237

16000.oo

Studia
informatyczne

L

DaviasT

234

Pensja

NazwaWydziału

Status

NazwiskoPrac

NrPrac

Tabela

Wykładowcy

SELECT NazwiskoPrac, Pensja,
NazwaModulu
FROM

Wykladowcy L, Modufiy M

WHERE L.NrPrac = M.NrPrac

background image

Inne złączenia tabel

background image

Złączenie (złączenie naturalne) tabel

background image

Złączenie (złączenie naturalne) tabel

background image

Złączenie (złączenie naturalne) tabel

background image

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

background image

Złączenie (złączenie naturalne) tabel

background image

Złączenie warunkowe - przykład

background image

Złączenie zewnętrzne tabel

background image

Złączenie zewnętrzne lewostronne

Wynikiem złączenia lewostronnego tabeli R z tabelą S są
wszystkie krotki operatora R złączone bądź z
dopasowanymi krotkami tabeli S, bądź z wartościami
NULL, gdy brak dla krotek z R dopasowanych do nich
krotek S.

R złączone lewostronnie z S

background image

Złączenie zewnętrzne lewostronne

background image

Złączenie zewnętrzne prawostronne

R złączone prawostronnie z S

Wynikiem złączenia prawostronnego tabeli R z tabelą S
są wszystkie krotki prawego operatora S złączone bądź z
dopasowanymi krotkami relacji R, bądź z wartościami
NULL, gdy brak dla krotek w R dopasowanych do nich
krotek.

background image

Złączenie zewnętrzne prawostronne

background image

Złączenie zewnętrzne pełne
FULL OUTER JOIN

Wynikiem złączenia zewnętrznego
pełnego jest suma mnogościowa
złączenia zewnętrznego lewostronnego i
złączenia zewnętrznego prawostronnego.

background image

97

Złączenie zewnętrzne pełne
FULL OUTER JOIN

background image

98

Przykłady złączeń

Tabele:

Kobiety Faceci

33

Mirosława

23

Zofia

56

Edyta

33

Wanda

55

Teresa

43

Sabina

34

Maria

23

Anna

Wiek

imie

Wiek

Imie

76

Zbigniew

44

Tomasz

18

Marian

21

Józef

55

Henryk

33

Jan

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

background image

99

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;

55

Teresa

55

Henryk

33

Wanda

33

Jan

33

Mirosława

33

Jan

WiekPani

Pani

WiekPana

Pan

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

background image

100

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;

56

Edyta

55

Henryk

55

Teresa

55

Henryk

…………..

…………..

…………..

…………..

34

Maria

18

Marian

33

Mirosława

18

Marian

23

Zofia

18

Marian

23

Anna

18

Marian

WiekPani

Pani

WiekPana

Pan

background image

101

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!

background image

102

Przykłady złączeń cd..

SELECT * FROM Faceci

RIGHT JOIN

Kobiety

ON Faceci.Wiek= Kobiety.Wiek;

34

Maria

Null

Null

33

Mirosława

33

Jan

23

Zofia

Null

Null

56

Edyta

Null

Null

33

Wanda

33

Jan

55

Teresa

55

Henryk

43

Sabina

Null

Null

23

Anna

Null

Null

WiekPani

Pani

WiekPana

Pan

background image

103

Przykłady złączeń cd..

SELECT * FROM Kobiety

LEFT JOIN

Faceci

ON Faceci.Wiek= Kobiety.Wiek;

Null

Null

23

Anna

Null

Null

34

Maria

Null

Null

43

Sabina

55

Henryk

55

Teresa

33

Jan

33

Wanda

Null

Null

56

Edyta

Null

Null

23

Zofia

33

Jan

33

Mirosława

WiekPana

Pan

WiekPani

Pani

background image

104

Przykłady złączeń cd..

SELECT * FROM Kobiety

RIGHT JOIN

Faceci

ON Faceci.Wiek = Kobiety.Wiek

;

33

Jan

33

Mirosława

76

Zbigniew

Null

Null

44

Tomasz

Null

Null

18

Marian

Null

Null

21

Józef

Null

Null

55

Henryk

55

Teresa

33

Jan

33

Wanda

WiekPana

Pan

WiekPani

Pani

background image

105

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

background image

106

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

23

Zofia

56

Edyta

43

Sabina

34

Maria

23

Anna

Wiek

imie

background image

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

background image

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

23500.00

Studia biznesu

SL

Thomas P

145

16500.00

Studia biznesu

L

Smith J

123

26500.00

Studia informatyczne

PL

Evans R

345

23500.00

Studia informatyczne

SL

Jones S

237

16000.oo

Studia informatyczne

L

DaviasT

234

Pensja

NazwaWydziału

Status

NazwiskoPrac

NrPrac

Tabela

Wykładowcy

SELECT

NazwaModulu, Poziom

FROM

Moduly

WHERE KodKursu = 'CSD'
UNION
SELECT

NazwaModulu, Poziom

FROM

Moduly

WHERE

KodKursu = 'EED' ;

background image

Suma tabel - przykład

background image

Suma tabel - przykład

background image

Suma tabel - przykład

np.:

background image

Różnica tabel - przykład

Np.:

background image

Różnica tabel - przykład

background image

Różnica tabel z wykorzystaniem
EXCEPT - przykład

lub

Np.:

background image

Różnica tabel z wykorzystaniem złączeń
- przykład

Np.:

background image

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

23500.00

Studia biznesu

SL

Thomas P

145

16500.00

Studia biznesu

L

Smith J

123

26500.00

Studia informatyczne

PL

Evans R

345

23500.00

Studia informatyczne

SL

Jones S

237

16000.oo

Studia informatyczne

L

DaviasT

234

Pensja

NazwaWydziału

Status

NazwiskoPrac

NrPrac

Tabela

Wykładowcy

SELECT

NazwaModulu, Poziom

FROM

Moduly

WHERE KodKursu = 'CSD'
INTERSECT
SELECT

NazwaModulu, Poziom

FROM

Moduly

WHERE

KodKursu = 'EED'

background image

Iloczyn mnogościowy tabel - przykład

background image

118

Iloczyn kartezjański krotek

background image

119

Iloczyn kartezjański krotek

background image

Podzielenie tabel (division)

background image

Podzielenie tabel (division)

background image

122

Perspektywy – widoki (views)

CREATE VIEW RTV (NrTow, Nazwa, CenaSprz)

As SELECT T.NrTow, T.Nazwa, T.CenaSprz

FROM Towar T WHERE T.GrupaTow=‘RTV’;

Definicja widoku wykorzystywana jest w momencie odwołania się do
widoku. Odwołanie to realizuje się na podobnych zasadach jak dla tabel
(relacji) bazy danych, np.

SELECT RTV.NrTow, RTV.Nazwa, RTV.CenaSprz

FROM RTV

WHERE RTV.CenaSprz < 2000;

Widoki są wirtualnymi tablicami bazy danych.

Nie mają one bezpośredniej reprezentacji w bazie danych. W bazie danych

zapamiętywana jest definicja perspektywy.

Definicja widoku jest wykorzystywana w momencie odwołania się do widoku

tak jak do innej tabeli bazy danych.

Definicja
perspektywy

Odwołanie do
perspektywy

background image

123

Przykład perspektywy

Perspektywa DobryDostawca ustala DNR, nazwisko, status i sumę
dostarczanych części dla tych dostawców, którzy dostarczają ich ponad 600:

CREATE VIEW DobryDostawca( DNR, nazwisko, status, suma )
AS

SELECT V.DNR, D.NAZW, D.STATUS, V.SUMA
FROM DOSTAWCA AS D, OcenaDostawcy AS V
WHERE V.DNR = D.DNR AND V.SUMA > 600;

Rezultat:
Wirtualna tabela o postaci:

DNR

D1
D2
D4

nazwisko

Abacki
Bober
Dąbek

status

20
10
20

suma

1300

700
900

background image

124

Przykład perspektywy

CREATE VIEW OcenaDostawcy(DNR, suma )
AS

SELECT DNR, SUM( ILOŚĆ ) FROM DC
GROUP BY DNR;

Perspektywa OcenaDostawcy podaje numer dostawcy i
sumę dostarczanych przez niego części.

background image

125

Usunięcie perspektywy

CREATE VIEW DobryDostawca( DNR, nazwisko, status, suma )
AS

SELECT V.DNR, D.NAZW, D.STATUS, V.SUMA
FROM DOSTAWCA AS D, OcenaDostawcy AS V
WHERE V.DNR = D.DNR AND V.SUMA > 600;

DROP VIEW DobryDostawca;

background image

126

Modyfikacja perspektyw cd..

CREATE VIEW RTV (NrTow, Nazwa, CenaSprz)

As SELECT T.NrTow, T.Nazwa, T.CenaSprz

FROM Towar T WHERE T.GrupaTow=‘RTV’;

Definicja perspektywy zawiera klucz główny tabeli Towar –
można perspektywę aktualizować

Update RTV

Set CenaSprz = CenaSprz +10

Definicja przykładowej perspektywy

background image

127

Czy możliwa jest aktualizacja widoku?

Dział
IdDz
Nazwa

Pracownik
Nazwisko
Zarobek
Dział

/MojeDziały
Nazwa
ŚredniZarobek

Dane rzeczywiste

Dane wirtualne

Create view MojeDziały (nazwa, ŚredniZarobek)
as
Select Nazwa, AVG(Zarobek)
From Pracownik
Group By Dział;

zatrudnia

*

background image

128

Czy możliwa jest aktualizacja widoku?

Dział
Nazwa

Pracownik
Nazwisko
Zarobek

/MojeDziały
Nazwa
ŚredniZarobek

Dane rzeczywiste

Dane wirtualne

Podwyższ średni zarobek w dziale „Krasnale ogrodowe” o 500 zł:

update MojeDziały set ŚredniZarobek = ŚredniZarobek + 500
where Nazwa = ‘Krasnale ogrodowe’;

?

Zlecenie jest błędne, gdyż:
Nie ma danej o nazwie ŚredniZarobek.
Nawet gdybyśmy chcieli je poprawnie wykonać na danych rzeczywistych, mamy
do wyboru nieskończenie wiele sposobów. Prawdopodobnie tylko jeden z nich
satysfakcjonowałby naszego szefa, który wydał takie polecenie.

zatrudnia

*

background image

129

Modyfikacja perspektyw cd..

Widok

MIASTA

definiuje pary (DAdres, MAdres), gdzie:

DAdres – adres dostawcy,

MAdres – adres magazynu, do którego dostawca dostarcza towary

CREATE VIEW MIASTA(DAdres, MAdres)

As SELECT DISTINCT D.Adres, M.Adres

FROM DOSTAWCA D, MAGAZYN M, DOSTWA W

WHERE D.NrDCY AND M.NrMag = W.NrMag;

background image

130

Modyfikacja perspektyw cd..

Widok

MIASTA

definiuje pary (DAdres, MAdres), gdzie:

DAdres – adres dostawcy,

MAdres – adres magazynu, do którego dostawca dostarcza towary

CREATE VIEW MIASTA(DAdres, MAdres)

As SELECT DISTINCT D.Adres, M.Adres

FROM DOSTAWCA D, MAGAZYN M, DOSTWA W

WHERE D.NrDCY AND M.NrMag = W.NrMag;

Widok jest niemodyfikowalny, gdyż w
jego definicji zawarto słowo DISTINCT a
w tabeli bazy danych może być wiele
rekordów o tej samej wartości D.Adres

!

background image

131

Modyfikacja perspektyw cd..

CREATE VIEW

Miasta1

(DAdres, MAdres)

AS

SELECT D.Adres, M.Adres
FROM Dostawca D, Magazyn M, Dostawa W
WHERE D.NrDcy=W.NrDcy AND M.NrMag = W.NrMag

NrDcy Nazwa Adres

1

Nowak Szczecin

2

Adamski Gorzów

3

Kowal Koło

4 Lipski Płock

Magazyn

Dostawca

NrMag

Adres

1

Szczecin

2

Koło

3 Szczecin

Dostawa

NrDost

NrDcy NrTow

NrMag

….

1

1 2 2 ….

2

4 3 2 …

3

2 2 1 …

4

2 3 1 …

5

1 1 3 …

Miasta1

DAdres

MAdres

Szczecin Koło

Płock Szczecin

Gorzów Szczecin

Gorzów Szczecin

Szczecin Szczecin

background image

132

Modyfikacja perspektyw cd..

UPDATE Miasta1 SET DAdres=‘Kraków’ WHERE DAdres=‘Szczecin’;

NrDcy Nazwa Adres

1

Nowak

Szczecin

2

Adamski Gorzów

3

Kowal Koło

4 Lipski Płock

Magazyn

Dostawca

NrMag

Adres

1

Szczecin

2

Koło

3 Szczecin

Dostawa

NrDost

NrDcy NrTow

NrMag

….

1

1 2 2 ….

2

4 3 2 …

3

2 2 1 …

4

2 3 1 …

5

1 1 3 …

Miasta1

DAdres

MAdres

Szczecin Koło

Płock Szczecin

Gorzów Szczecin

Gorzów Szczecin

Szczecin Szczecin

Widok Miasta1 jest modyfikowalny, gdyż zawiera klucz w jego definicji

Modyfikacja zostanie wykonana na tabeli

Dostawca

NrDcy Nazwa Adres

1

Nowak

Kraków

2

Adamski Gorzów

3

Kowal Koło

4 Lipski Płock

Dostawca

background image

133

Aktualizacja perspektyw

Najpoważniejszy i nierozwiązany problemem to aktualizacja
perspektyw.

Baza danych

Dane zapamiętane

Perspektywa

Dane wirtualne

Aktualizacja

Na ogół odwzorowanie danych wirtualnych w dane zapamiętane nie jest
jednoznaczne
. Odwzorowanie aktualizacji danych wirtualnych w aktualizacje
danych zapamiętanych można zrobić na wiele sposobów. Czasami takie
odwzorowanie odwrotne w ogóle nie istnieje.

background image

134

Modyfikacja perspektyw
podsumowanie

Jeśli widok (perspektywa) jest tak zdefiniowany,

że można przetransformować operacje jego
modyfikowania na modyfikację tabel
bazodanowych, to można go modyfikować

Sytuacja taka występuje np. wówczas, gdy w

definicji pespektywy zawarto klucz główny tabel
bazy danych, nad którymi jest definiowana
perspektywa i definicja perspektywy nie zawiera
funkcji agregujących ani klauzuli DISTINCT


Wyszukiwarka

Podobne podstrony:
bd cz 2 jezyki zapytan do baz danych
cz 3 Języki zapytań do baz danych
bd cz 2 jezyki zapytan do baz danych
problematyka masoowego dostepu do baz danych mity i fakty mqsixoztwl26gv7afh6a6hsnoalkzz6a5q7na7a M
BD Wykad 1 Wprowadzenie do baz danych id 8 (2)
dostep uzytkownikow mobilnych do baz danych wap, pqa, mbts zkpjy2ly76foe7e2jm5bgz7ytxqqyaxaghe36ha
lab1Wstęp teoretyczny do baz danych, bazy danych
Wprowadzenie do baz danych, Bazy Danych
problematyka masoowego dostepu do baz danych mity i fakty mqsixoztwl26gv7afh6a6hsnoalkzz6a5q7na7a M
Wprowadzenie do baz danych
Dostep uzytkownikow mobilnych do baz danych WAP, PQA, MBTS
Wprowadzenie do baz danych wpbada
Wprowadzenie do baz danych wpbada
BD Wykˆad 1 Wprowadzenie do baz danych
Do Baz Danych
Wprowadzenie do baz danych 2

więcej podobnych podstron