Języki zapytań do baz danych

Download Report

Transcript Języki zapytań do baz danych

SYSTEMY BAZ DANYCH

Część II

Opracowanie : Dr Bożena Śmiałkowska

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 predykatach Języki oparte na rachunku krotek (QUEL) 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, 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

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), (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 prowadzonych w laboratorium badawczym IBM w San Jose w Kalifornii w latach leżąca u podstaw języka SQL powstała w wyniku prac siedemdziesiątych. Tam też została zbudowana implementacja prototypowa relacyjnych pojęć o nazwie System/R. Ten wczesny relacyjny SZBD wiele używał języka znanego wówczas jako SEQUEL. Dlatego właśnie osób wciąż wymawia nazwę SQL jak SEQUEL (to jest sikłel).



W latach 1973-1979 badacze z czasopismach IBM opublikowali w akademickich prowadzono dużo materiałów na temat budowy System/R. W tym czasie zarówno w USA, jak i w Europie na konferencjach i seminariach 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, jakakolwiek implementacja SQL że 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 słowy, pod wieloma względami znajdują one wspólny standardu SQL. Innymi grunt innymi wokół definicji podstaw lub poziomu jeden standardu SQLl. Pod 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) innych języków, – 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 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

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) długość napisu. - 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 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 Decimal(M, N) Liczba dziesiętna o długości M z N miejscami po przecinku dziesiętnym, Integer system, - Synonim dla Decimal, Liczba całkowita z zakresu wartości określonych przez 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 ((), (), [ ...

PRIMARY KEY FOREIGN KEY REFERENCES (nazwa atrubutu [ , nazwa atrubutu ]…)], (nazwa atrubutu [, 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 powinien wskazującą, że domyślnym poziomem 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

Na przykład

DROP TABLE Moduły

Modyfikacja struktury tabel bazy danych

Przy tabeli.

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 Każdą z tych operacji określamy używając polecenia

ALTER TABLE.

Na przykład:

ALTER TABLE Wykładowcy ADD COLUMN NrPokoju Smallint

dodanie kolumny

ALTER TABLE Wykładowcy ALTER COLUMN NazwiskoPrac Varchar(20) ALTER TABLE Wykładowcy DROP COLUMN NazwiskoPrac

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 do tabeli. Jest zwykle umożliwia dodanie wielu wierszy 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 do używamy do usuwania wierszy z tabeli. Wiersze 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 lub UPDATE używamy do zmodyfikowania zawartości jednego 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

projekcja Selekcja Łączenie (join) dwóch tabel

S z

a w Operacje „mnogościowe” w SQL na danych bazy danych 11 32 Dos Bios

a w 11 32 Dos Bios a w 11 32 Dos Bios 44 34 Kos Znak w z 32 43 Bios Znak w z 32 43 Bios Znak

Suma tabel

A w S z 11 32 44 34 Dos Bios Kos Znak

Różnica tabel

a 11 Dos

Iloczyn tabel

32 Bios

Selekcja

Struktura typowego zapytania selekcyjnego

    Proste wyszukiwanie jest wykonywane dzięki kombinacji klauzul SELECT, FROM i WHERE: SELECT , ,... FROM [WHERE ] Klauzula

SELECT

wydobyte. Klauzula dane. Klauzula spełnione przez sprowadzane dane. W następującym poleceniu gwiazdka " * wskazuje na kolumny, z których wartości mają być

WHERE

" pełni funkcję symbolu uniwersalnego. Oznacza to, że zostaną wypisane wszystkie atrybuty z tabeli, której nazwa znajduje się po słowie FROM.

FROM

określa tabele, z których mają pochodzić określa warunek lub warunki, które mają być Klauzula WHERE jest opcjonalna Opcja

DISTINCT

w wyniku daje różne (niepowtarzalne) wyniki

SELECT DISTINCT

stanowisko

FROM

pracownicy ;

Warunki w zapytaniach selekcyjnych

\ !

< > Znak 0

# L ?

A a & C . , : ; - /

Like w Access

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

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

‘ul. ‘

klienci k ‘ ‘

k.nazwisko k.ulica

Ulica, k.kod || ‘ ‘ || k.miasto

AS AS

‘ ‘ || k.numer Klient,

Miasto

1 2 3 4

Obliczenia w zapytaniach selekcyjnych Ilosc)

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 WHEN ELSE

k.miasto

‘Warszawa’

THEN

‘Klient oddziału macierzystego’ ‘Klient z przedstawicielstwa’

END FROM

klienci k 54

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

3 2 1 1 Poziom 1 1 3

Wynik selekcji

Struktura typowego zapytania selekcyjnego - przykłady Powtarzające się wartości są dozwolone w SQL ale są niedozwolone prawdziwie relacyjny wynik na klauzuli SELECT to w relacyjnym modelu danych.

Aby uzyskać powyższe zapytanie, musimy do dodać słowo kluczowe powtarzające się wartości w tabeli.

DISTINCT (różne). Usuwa 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 bazy danych na grupy, dzieli dane wyselekcjonowane z biorąc za podstawę wartości w określonej kolumnie lub zbiorze kolumn, i grupie, umożliwia wykonanie obliczeń podsumowujących (agregujących) na wartościach w każdej Ta instrukcja dzieli dane na grupy, 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 biorąc za podstawę wartości w średniej pensji w grupie.

do grupy funkcji agregujących należą następujące funkcje: Count( ) Max( ) Min( ) – oblicza ilość wystąpień, - wyznacza - wyznacza wartość największą w grupie, wartość najmniejszą w grupie wartości, Avg() Sum() - wyznacza - suma wartość średnią w grupie wartości w grupie

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 Nazwa Wydziału

NrPrac NazwiskoPrac

234 DaviasT 345 123 237 145 Evans R Smith J Jones S Thomas P

Status NazwaWydziału

Pensja

Studia informatyczne 16000.00

PL L SL SL Studia informatyczne 26500.00

Studia biznesu 16500.00

Studia informatyczne 23500.00

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 ' wykładowców: HAVING.

Następująca instrukcja wyszukuje z naszej bazy danych tylko te wydziały, które mają więcej niż dwóch

NrPrac NazwiskoPrac Status NazwaWydziału Pensja

234 DaviasT L Studia informatyczne 16000.oo

Tabela Wykładowcy

345 Evans R PL Studia informatyczne 26500.00

123 237 Smith J Jones S L SL Studia biznesu Studia informatyczne 16500.00

23500.00

SELECT NazwaWydziału FROM Wykładowcy GROUP BY Nazwa Wydziału HAVING count(*) > 2

145 Thomas P SL Studia biznesu 23500.00

wynik

tudia 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

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 237 345 123 145 DaviasT Jones S Evans R Smith J Thomas P L SL PL L SL Studia informatyczne Studia informatyczne Studia informatyczne Studia biznesu Studia biznesu 16000.oo

23500.00

26500.00

16500.00

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

Tabela Wykładowcy

237 Jones S SL Studia informatyczne 23500.00

345 Evans R PL Studia informatyczne 26500.00

123 145 Smith J Thomas P L SL Studia biznesu Studia biznesu 16500.00

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.

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

Tabela Wykładowcy

234 237 345 123 DaviasT Jones S Evans R Smith J L SL PL L Studia informatyczne Studia informatyczne Studia informatyczne Studia biznesu 16000.oo

23500.00

26500.00

16500.00

SELECT NazwiskoPrac, Pensja, NazwaModulu FROM Wykladowcy L, Modufiy M WHERE L.NrPrac = M.NrPrac

145 Thomas P SL Studia biznesu 23500.00

Inne złączenia 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 kolumny złączenia mają tę samą nazwę w obu tabelach.

założeniu, ze 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 m oż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

Złączenie Θ-join

Złączenie warunkowe - przykład

Złączenie zewnętrzne tabel

Złączenie zewnętrzne lewostronne

Złączenie zewnętrzne prawostronne

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

Przykłady złączeń

Tabele: Kobiety Faceci

imie

Anna Maria Sabina Teresa Wanda Edyta Zofia Mirosława 33 56 23 33

Wiek

23 34 43 55

Imie

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

Wiek

33 55 21 18 44 76 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

Jan

WiekPana

Pani

Wanda

WiekPani

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

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 Marian Marian Marian …………..

Henryk Henryk 18 18 18 18 …………..

55 55 Anna Zofia Mirosława Maria …………..

Teresa Edyta 23 23 33 34 …………..

55 56 95

Złączenie RIGHT JOIN

SELECT … FROM T1

RIGHT

JOIN T2 ON WHERE ;

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

Pan

Null SELECT * FROM Faceci RIGHT JOIN Kobiety ON Faceci.Wiek= Kobiety.Wiek;

WiekPana

Null

Pani

Anna

WiekPani

23 Null Null Henryk Jan Null Null Jan Null Null 55 33 Null Null 33 Maria Sabina Teresa Wanda Edyta Zofia Mirosława 34 43 55 33 56 23 33

Przykłady złączeń cd..

SELECT * FROM Kobiety LEFT JOIN Faceci ON Faceci.Wiek= Kobiety.Wiek;

Pani

Anna

WiekPani

Pan

Null

WiekPana

Null Maria Sabina Teresa Wanda Edyta Zofia Mirosława 34 43 55 33 56 23 33 Null Null Henryk Jan Null Null Jan Null Null 55 33 Null Null 33

Przykłady złączeń cd..

SELECT * FROM Kobiety RIGHT JOIN Faceci ON Faceci.Wiek = Kobiety.Wiek

;

Pani

Wanda

WiekPani

Pan

Jan

WiekPana

33 Teresa 55 Henryk 55 Null Null Józef 21 Null Null Marian 18 Null Null Mirosława Null Null 33 Tomasz Zbigniew Jan 44 76 33

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

Anna Maria Sabina Edyta Zofia

Wiek

23 34 43 56 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 sposób: 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 SELECT * FROM Wykladowcy L LEFT OUTER JOIN ON L.NrPrac = M.KodPrac Moduly M SELECT * FROM Wykladowcy L RIGHT OUTER JOIN ON L.NrPrac = M.KodPrac Moduly M SELECT NazwaModulu, NazwiskoPrac FROM Wykladowcy L FULL OUTER JOIN ON L.NrPrac = M.KodPrac Moduly M

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

234 237 345 123 145 DaviasT Jones S Evans R Smith J Thomas P L SL PL L SL Studia informatyczne Studia informatyczne Studia informatyczne Studia biznesu Studia biznesu

Pensja

16000.oo

23500.00

26500.00

16500.00

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

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

Tabela Wykładowcy

NrPrac NazwiskoPrac Status NazwaWydziału Pensja

234 237 345 123 145 DaviasT Jones S Evans R Smith J Thomas P L SL PL L SL Studia informatyczne Studia informatyczne Studia informatyczne Studia biznesu Studia biznesu 16000.oo

23500.00

26500.00

16500.00

23500.00

SELECT NazwaModulu, Poziom FROM Moduly WHERE KodKursu = 'CSD' INTERSECT SELECT NazwaModulu, Poziom FROM Moduly WHERE KodKursu = 'EED'

Iloczyn tabel - przykład

Podzielenie tabel (division)

Operacje z wykorzystaniem kursora

Operacje pozycyjne UPDATE, DELETE z wykorzystaniem kursora

Perspektywy – widoki (views)

Modyfikacja perspektyw

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 rekordach zostać

modułów

odzwierciedlona w powiązanych 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

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 poprawne modyfikacje.

CHECK , aby wymusić  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 modelowanym przez tą bazę. właściwość integralności, kiedy istnieje odpowiedniość między faktami przechowywanymi w bazie danych a światem rzeczywistym 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 ona, że wartość klucza obcego może się znajdować tylko w jednym z dwóch stanów. dotyczy kluczy obcych . Mówi 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 wiersze w tabeli docelowej.

. Mówią one co powinno się stać z powiązaną tabelą, gdy modyfikujemy wiersz lub

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 wykonywania sprawdzał naruszanie więzów pod koniec 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.

143

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