laboratorium (1)

Bazy danych

mgr inż. Piotr Kaczyński

Wydział Matematyczno-PrzyrodniczySzkoła Nauk Ścisłych

Uniwersytet Kardynała Stefana Wyszyńskiego

Podstawy pracy z RDMBS Oracle

Piotr Kaczyński Bazy danych

WprowadzenieSQL*Plus

Język SQL

Część I

Proste operacje na bazie danych



Język SQL

Plan prezentacji

1 WprowadzenieBazy danychBaza OraclePodstawy Administracji

2 SQL*PlusŁączenie z baząWykonywanie poleceń

3 Język SQLPolecenie wyboru SELECTWybór wierszy WHERESortowanie ORDER BY



Język SQL

Bazy danychBaza OraclePodstawy Administracji

Baza danych

System przechowujący i udostępniający daneKartoteka, notatnik itpPliki tekstowe na dyskuPliki na dysku o specyficznym formaciePliki ExcelSkomplikowane systemy zarządzania danymi

WymaganiaTrwałość danychSpójność danychWielodostęp



Język SQL


System zarządzania bazą danych

SZBD System Zarządzania Bazą Danych (DBMS)

SZRBD System Zarządzania Relacyjną Bazą Danych(RDBMS)

„SZBD = Baza Danych”Zalety

Zapewnia trwałość, spójność i wielodostępUmożliwia szybkie wyszukiwanie i dostęp do danychNiegraniczona ilość danychOptymalizacja

WadyRozwiązanie ciężkieWymaga instalacji i działania dodatkowego programuDane bez niego są bezużyteczne



Język SQL


Architektura 2-warstwowa

Aplikacja korzysta z bazy danych bezpośrednio

Większa kontrola nad zapytaniami

Mniejsza optymalizacja

Mniejsze bezpieczeństwo



Język SQL


Architektura 3-warstwowa

Aplikacja korzysta z serwera aplikacyjnego

Serwer aplikacyjny komunikuje się z bazą danych

Mniejsza kontrola komunikacji z bazą danych

Centralizacja dostępu

Wieksza optymalizacja

Większe bezpieczeństwo

Konieczny serwer aplikacyjny



Język SQL


Podział baz danych

StrukturalnyRelacyjneObiektoweRelacyjno-obiektowe

Funkcjonalny

OLTP On Line Transaction ProcessingOLAP On Line Analytical Processing

Liczba węzłówBazy rozproszoneBazy scentralizowane



Język SQL


Relacyjne bazy danych

Oparte o model relacji

Definition (Relacja)

Niech dane będą zbiory X1,X2, . . . ,Xn. Relacją n-członową ρnazywamy dowolny podzbiór ich iloczynu kartezjańskiego

ρ ⊂ X1 × X2 × . . .× Xn

Rekord danych to jeden element relacji

Zbiór rekordów = relacja = tabela

Kolumna tabeli (typ) - zbiór Xi



Język SQL


Tabela

Tabela to nazwany zbiór rekordów (wierszy)

Tabela posiada kolumny o zadanych typach danych

Kolumny mają nazwy

Example (Tabela dane osobowe)

Imię Nazwisko PłacaGrzegorz Schytyna 20000Donald Tesk 15000

Lew Rewin 2500Piotr Kaczyński 500



Język SQL


Operacje na tabelach

SumowanieOdejmowaniePrzecinanieSelekcja kolumnSelekcja wierszyŁączenie

Example

Id Imię Nazwisko1 Grzegorz Schytyna2 Donald Tesk3 Lew Rewin4 Piotr Kaczyński

Id Płaca1 200002 150003 25004 500



Język SQL


Obiektowe bazy danych

Model relacyjny jest ubogi i niezgodny z reczywistościąBrak hermetyzacjiObiekt identyfikowany przez daneBrak metodNiezgodność z obiektowym językami programowania

Obiektowe bazy danych pozbawione są tych wad

Język OQL

Brak komercyjnych obiektowych SZBD

Brak standardów

Rozwiązania łączone (moduły obiektowe w RDBMS)

Specjalne biblioteki (np. hibernate)



Język SQL


RDBMS Oracle

Korporacja Oracle ma 30 lat

RDBMS jest głównym produktemRozwiązanie stricte komercyjne

Oracle 10g Express Edition (XE) - do celów deweloperskich iszkoleńOracle 11g

Jeden z pionierów w dziedzinie relacyjnych baz danych

Szeroko stosowany w praktyce

Zalety: duże wsparcie, optymalizacja, szybka, wydajna

Wady: cena



Język SQL


Startowanie i stopowanie bazy

Po instalacji dostępne są skrótyStart DatabaseStop Database



Język SQL


Baza danych a usługi Windows

Pod systemem Windows RDBMS Oracle instalowany jest jakousługaMożna wyłączyć automatyczny startMicrosoft Windows Vista wymaga uprawnień administratorado startowania i zatrzymywania bazy danych



Język SQL


Wyłączanie automatycznego startu



Język SQL


Kopie zapasowe

Dostarczane jako skróty w menu startBackup DatabaseRestore Database

Składowani e w standardowym katalogu instalacyjnym

Odzyskiwanie z ostatnio zrobionej kopii zapasowej

Produkcyjnie bardzo ważna funkcja!



Język SQL

Łączenie z baząWykonywanie poleceń

Program SQL*Plus

Standardowy porogram do komunikacji z RDBMSStanardowa ścieżka działania

Podłączenie do bazy danych (logowanie)Wykonanie poleceńWyjście

Ważne klawiszeStrzałka ↑ - przywołanie ostatniego i poprzednich poleceńStrzałka ↓ - przywołanie następnych poleceń

W starych wersjach nie działał delete ani backspace!Uruchamiany z menu start

Run SQL Command Line

Wywołanie z linii poleceń: sqlplus



Język SQL


Podłączanie do bazy

Polecenie connectNależy podać użytkownika (user) i hasło (password)

Wyjście poleceniem exitStandardowy użytkownik i hasło w laboratorium

Użytkownik: studentHasło: bd

Podłączanie bez interaktywnego logowaniaconnect user/password

Example (Logowanie nieinteraktywne)connect student/bd



Język SQL


Polecenia SQL

Polecenia w języku zapytań SQL lub administracyjneKażde polecenie musi być zakończone ”;”

Brak średnika przenosi do kolejnej linii

Example

SQL> s e l e c t ∗ from a s d f g2 where k = 23 ;

Każde zapytanie zwraca informację o wierszachzwróconych/zmodyfikowanych

Ćwiczenie

Podłącz się do bazy danych i wykonaj zapytanies e l e c t t a b l e n a m e from a l l t a b l e s ;



Język SQL


Wykonywanie skryptów

sqlplus umożliwia wykonywanie poleceń wsadowo

Skrypt musi być zapisany w pliku

Skrypt wykonywany linia po linii, aż do napotkania błęduWyowołaniesqlplus user/password @plik

Ćwiczenie

Wykonaj skrypt sql createdb.sql



Język SQL

Polecenie wyboru SELECTWybór wierszy WHERESortowanie ORDER BY

Język SQL

Structured Query LanguageJęzyk deklaratywny a nie proceduralny

Mówimy co chcemy zobaczyć, a nie jak

Miał być bliski językowi naturalnemu

Czasami rozróżnia, czasami nie rozróżnia wielkich liter (zależyod RDBMS)

Polecenia dzielimy na trzy grupyDML Data Modification Language - polecenia

modyfikujące lub wyszukujące dane (SELECT,INSERT, UPDATE, DELETE)

DDL Data Definition Language - polecenia definiującedane (CREATE, DROP, ALTER)

DCL Data Control Language - polecenia określającedostęp do danych (GRANT, REVOKE)



Język SQL


Język SQL przykład

Example

SELECT p e n s j a FROM p r a c o w n i c y ;GRANT connect TO p k a c z y n s k i ;CREATE TABLE s t u d e n c i ( I m i e VARCHAR( 2 0 ) , Nazwisko VARCHAR( 3 0 ) ) ;



Język SQL


Polecenie SELECT

Podstawowe polecenie SQL, wybiera dane z tabeliMożliwe określenie kolumn, które zostaną wybraneWynikiem jest tabelaSkładnia

SELECT kolumna1, kolumna2, ... FROM tabela

Wybranie wszystkich kolumn: znak *Wybranie tylko różnych wierszy: klauzula DISTINCTZmiana nazw kolumn w tabeli wynikowej (aliasy)

SELECT kol1 alias1, kol2 alias2 , ... FROM tabela

Example

SELECT ∗ FROM p r a c o w n i c y ;SELECT nazwisko , p l a c a FROM p r a c o w n i c y ;SELECT n a z w i s k o surname , p l a c a s a l a r y FROM p r a c o w n i c y ;SELECT DISTINCT ∗ FROM s t u d e n c i ;



Język SQL


Polecenie SELECT - ćwiczenie

Ćwiczenie

Wybierz wszystkie dane z tabeli studenci

Wybierz tylko imiona z tabeli studenci

Wybierz wszystkie dane z tabeli studenci tak, aby kolumna zimieniem nazywała się ”name”

Ile różnych imion jest wśród studentów w tabeli studenci?



Język SQL


Klauzula WHERE

Klauzula dodawana na końcu polecenia SELECT

Zawęża ilość wierszy w tabeli wynikowej

Po klauzuli pojawia się warunek logiczny na dane w kolumnieWarunek logiczny może być złożony

Koniunkcja AND i alternatywa OR

Nawiasy działają w warunku logicznym

AND ma wyższy priorytet niż ORMożliwe operatory=, <>, <, >, <=, >=Warunek na daty betweenWyszukiwanie szablonu LIKE i znak zastępujący dowolny ciąg”%” lub dowolny znak ” ”



Język SQL


Klauzula WHERE przykład

Example

SELECT ∗ FROM p r a c o w n i c y WHERE i m i e = ’ P i o t r ’ ;SELECT i m i e name FROM p r a c o w n i c y WHERE name = ’ P i o t r ’ ;SELECT i m i e name FROM p r a c o w n i c y WHERE i m i e = ’ P i o t r ’ ;SELECT ∗ FROM p r a c o w n i c y WHERE p l a c a < 1 0 0 0 ;SELECT ∗ FROM p r a c o w n i c y WHERE na z w i s k o LIKE ’R%’ AND p l a c a > 1 0 0 0 ;

Ćwiczenie

Wybierz wszystkich studentów o imieniu Piotr

Wybierz wszystkich studentów, których imię zaczyna się odlitery P lub dalszej (w alfabecie)

Ile imion wśród studentów zaczyna się od litery ’P’ lub dalszej(w alfabecie)

Ilu jest studentów, których nazwisko zaczyna się na ”B” lub”D”?

Ilu jest studentów, którzy w nazwisku mają jako drugą literę’a’?



Język SQL


Klauzula ORDER BY

Sortuje wiersze według kolumn

Dodawana na koniec polecenia SELECT (już po WHERE, o ilejest)

Kolejne kolumny wymieniane w liście oddzielanej przecinkamiKolejność kolumn dyktuje priorytet sortowaniaNajpierw sortuj po x potem po y

Po każdej kolumnie można dodać ASCENDING (rosnąco) lubDESCENDING (malejąco)

Example

SELECT ∗ FROM p r a c o w n i c y ORDER BY nazwisko , i m i e ;SELECT i m i e FROM p r a c o w n i c y ORDER BY n a z w i s k o ;SELECT i m i e name FROM p r a c o w n i c y ORDER BY name ;SELECT ∗ FROM p r a c o w n i c y WHERE i m i e = ’ P i o t r ’ ORDER BY n a z w i s k o ;



Język SQL


Klauzula ORDER BY ćwiczenie

Ćwiczenie

Wypisz listę studentów posortowaną po nazwiskach i imionach

Wypisz listę studentów któych nazwiska zaczynają się na ’A’posortowaną po imionach



Język SQL


Proste operacje na bazie danych - przypomnienie

Baza danych - co to jest

Relacyjne bazy danych

Narzędzie SQL*Plus

Podłączanie do bazy danych (connect student/bd)Najprostsze zapytania do bazy danych

Klauzula SELECTFiltrowanie wierszy WHEREWarunek LIKESortowanie ORDER BY

ExampleSELECT ∗ FROM STUDENCI WHERE Imie LIKE ’A\%’ORDER BY Nazwisko;


FunkcjeKonwersja typów

Agregacje

Część II

Funkcje i agregacje



Agregacje

Plan prezentacji

4 FunkcjeFunkcje znakoweFunkcje liczboweFunkcje czasu

5 Konwersja typów

6 AgregacjeFunkcje agregującePodgrupyFiltrowanie podgrup



Agregacje

Funkcje znakoweFunkcje liczboweFunkcje czasu

Funkcje

Funkcje przyporządkowują argumentom pewne wyniki

Działają na wynikach zapytania

Modyfikują wyniki zapytania

Działają na każdym wierszu zapytania wynikowegoKolumny, które są wynikiem funkcji nie mają nazwy

Nazwa taka, jak wyrażenie definiująceNajlepiej nadawać aliasy



Agregacje


Funkcje znakowe

Konkatenacja ciągów - operator ||

Example

SELECT I m i e | | Nazwisko t x t FROM s t u d e n c i ;

lower - przekształca litery na małe

Example

SELECT lower ( Nazwisko ) n a z w i s k o FROM s t u d e n c i ;

upper - przekształca litery na duże

Example

SELECT upper ( I m i e ) i m i e FROM s t u d e n c i ;



Agregacje


Funkcje znakowe c.d.

initcap - zamienia pierwsze litery wyrazów na wielkie,pozostałe małe

Example

SELECT i n i t c a p ( I m i e | | ’ ’ | | Nazwisko ) i m i e FROM s t u d e n c i ;

trim({leading|trailing|both} znak from Przycinawybrany znak z początku lub końca innego ciągu znaków

Example

SELECT tr im ( l e a d i n g ’A ’ FROM I m i e ) FROM s t u d e n c iWHERE i m i e l i k e ’A%’ ;SELECT tr im ( both ’A ’ FROM upper ( I m i e ) ) FROM s t u d e n c iWHERE i m i e l i k e ’A%’ ;



Agregacje



substr(ciąg, m[, n]) Wybranie podciągu znaków odługości n od znaku m

Example

SELECT sub s t r ( Nazwisko , 2 , 5) FROM S t u d e n c i ;SELECT sub s t r ( Nazwisko , 2) FROM S t u d e n c i ;

replace(ciąg, x, y) zamienia wystąpienia x na y

Example

SELECT r e p l a c e ( upper ( Nazwisko ) , ’A ’ , ’ x ’ ) FROM S t u d e n c i ;



Agregacje



translate(ciąg, x, y) zamienia znaki x na y (liczy siępozycja w ciągu)

Example

SELECT t r a n s l a t e ( upper ( Nazwisko ) , ’AB ’ , ’BA ’ ) FROM S t u d e n c i ;

length zwraca długość ciągu znaków

Example

SELECT Nazwisko FROM S t u d e n c i WHERE l e n g t h ( Nazwisko ) > 1 0 ;



Agregacje


Funkcje znakowe - ćwiczenia

Ćwiczenia1 Wypisz imiona i nazwiska studentów tak, aby pierwsza litera

imienia była zastąpiona pierwszą literą nazwiska2 Dla każdego studenta wygeneruj hasło składające się z

pierwszych trzech liter imienia (pisanych dużymi literami),dwóch ostatnich liter nazwiska i liczby znaków w imieniu(Przykład: Piotr Kaczyński -¿ PIOki5)



Agregacje


Funkcje liczbowe

Można stosować operatory arytmetyczneabs - wartość bezwzględnaceil - sufitfloor - podłogamod(m,n) - reszta z dzielenia m przez npower(m,n) - mn

round(m,n) - zaokrągla m do n miejsc po przecinkutrunc(m,n) - obcina m do n miejsc po przecinkusign - zwraca znak liczy (−1,0 lub 1)sqrt(n) -

√n

Example

SELECT round (2∗ s q r t ( l e n g t h ( Nazwisko ) ) +3∗ s i g n ( l e n g t h ( I m i e ) ) , 2 ) l i c z b a FROM S t u d e n c i ;



Agregacje


Funkcje liczbowe - ćwiczenia

Ćwiczenia1 Wypisz wszystkich studentów, którzy mają ocenę 4 lub 4.5

Wykorzystaj warunek logicznyWykorzystaj funkcję zaokrąglającą

2 Wyświetl oceny studentów podwyższone o 10%



Agregacje


Czas

Czas zapisywany jest w 2 możliwych „prezycjach”DATE - z dokładnością do dniTIMESTAMP - z dokładnością do ułamków sekundy

Przedziały czasowe INTERVALLata do miesięcyDni do sekund

Uwaga na zmiany czasu!

Example

09/10/29 21:57:01,552000 +01:00



Agregacje


Odczyt czasu

Funkcje służące do pobrania aktualnego czasucurrent date zwraca datę w formacie DATEcurrent timestamp zwraca datę w formacie TIMESTAMP

Zwracany czas jest czasem ustawionym na serwerze

Example

SELECT cur r en t da te , cur rent t imestamp FROM S t u d e n c i ;



Agregacje


Znaczniki czasu

Literały czasowePoprzedzone DATE i podawane jako ciąg znakówPoprzedzone TIMESTAMP i podawane jako ciąg znaków

Example

SELECT Imie , Nazwisko FROM s t u d e n c i WHEREd a t a u r o d z e n i a >= DATE ’ 1989−01−01 ’ ;SELECT Imie , Nazwisko FROM s t u d e n c i WHEREo s t a t n i e l o g o w a n i e >= TIMESTAMP ’ 2009−10−29 23 : 00 : 00 ’ ;



Agregacje


Przedziały czasu

Definiowane słowem kluczowym INTERVAL

Służą do określenia okresu czasu a nie jednej chwili

Umożliwiają określanie relacji między znacznikami czasowymi

Określane przez INTERVAL okres początek TO koniecPoczątek i koniec mogą określać

YEARMONTHDAYHOURMINUTESECOND

Każdy z typów może określać w () precyzję



Agregacje


Przedziały czasu c.d.

Example

INTERVAL ’ 25 ’ YEARINTERVAL ’ 19−3 ’ YEAR TO MONTHINTERVAL ’ 1 2 :16 ’ DAY TO MINUTEINTERVAL ’ 2 3 : 1 2 : 1 2 . 1 5 ’ HOUR TO SECOND



Agregacje


Działania na czasie

Chwile czasowe i przedziały czasu można dodawać iodejmowaćWynik jest logiczną konsekwencją wybranych argumentów

Znacznik czasowy - znacznik czasowy = przedział czasuZnacznik czasowy + przedział czasu = znacznik czasowy

Przedziały czasowe można mnożyć i dzielić przez liczbęPrzedizał czasowy odpowiednio dłuższy i krótszy

Uwaga na zmianę czasu!

Example

SELECT ( cur rent t imestamp − INTERVAL ’ 168 ’ HOUR)FROM S t u d e n c i ;



Agregacje


Ekstrakcja elementów daty

Można wybrać dowolny element znacznika czasowego

Funkcja extract(typ FROM data)

Typem może być YEAR, MONTH, DAY, HOUR, MINUTE,SECOND

Example

SELECT ex t r a c t (DAY FROM cur rent t imestamp ) FROM S t u d e n c i ;SELECT ( e x t r a c t (YEAR FROM current t imestamp ) +

e x t r a c t (MONTH FROM cur rent t imestamp ) +e x t r a c t (DAY FROM cur rent t imestamp ) ) AS nr

FROM s t u d e n c i ;



Agregacje


Funkcje czasu - ćwiczenia

Ćwiczenia1 Wypisz wszystkich studentów, którzy urodzili się przed

pierwszym czerwca 19892 Wypisz studentów, którzy urodzili się w czerwcu3 Wypisz studentów, których dzień urodzenia jest liczbą

podzielną przez 34 Wypisz tych studentów, którzy urodzili się w ostatnim dniu

miesiąca5 Każdemu studentowi oblicz numer potrzebny do wróżb

numerologicznych



Agregacje

Konwersja typów

Funkcja cast(wyr AS typ)

Wyrażeniem może być dowolny typ (ciąg znaków, liczba etc)Typ na który rzutujemy może być np.TIMESTAMPDATENUMBER

Example

SELECT cas t ( ’ 2009−01−01 ’ AS DATE) data FROM S t u d e n c i ;SELECT cas t ( e x t r a c t (YEAR FROM cu r r en t da t e ) | |

e x t r a c t (MONTH FROM cu r r en t da t e ) | |e x t r a c t (DAY FROM cu r r en t da t e ) AS NUMBER) l i c z b a

FROM S t u d e n c i ;



Agregacje

Wyrażenie CASE

Zastępuje wyrażenie jednym z wyników

Odpowiednik klauzuli switch w języku C++

Składnia

CASE w y r a z e n i eWHEN x1 THEN y1WHEN x2 THEN y2[ELSE y3 ]

END



Agregacje

Wyrażenie CASE

Example

SELECT CASE sub s t r ( Imie , 1 , 1 )WHEN ’A ’ THEN ’OK! ’WHEN ’B ’ THEN ’NOT OK’ELSE ’MAYBE OK’

END FROM S t u d e n c i ;

SELECT CASE sub s t r ( Imie , 1 , 1 )WHEN ’A ’ THEN cu r r en t da t eWHEN ’B ’ THEN ’AHA ’

END FROM S t u d e n c i ;



Agregacje

Operatory zbiorowe

Tabele są relacjami a relacje zbioramiMożna wykonać operacje teoriomnogościoweUNION - Suma zbiorów z eliminacją powtórzeńUNION ALL - Suma wszystkiegoMINUS - Różnica zbiorówINTERSECT - Przecięcie zbiorów

Operacje muszą być wykonywane na tabelach o tej samejliczbie kolumn i takich samych typów

Example

SELECT ∗ FROM s t u d e n c i WHERE I m i e LIKE ’A%’UNIONSELECT ∗ FROM s t u d e n c i WHERE I m i e LIKE ’B%’ ;

SELECT ∗ FROM s t u d e n c i WHERE I m i e LIKE ’A%’INTERSECTSELECT ∗ FROM s t u d e n c i WHERE Nazwisko LIKE ’A%’ ;



Agregacje

Operatory zbiorowe - ćwiczenia

Ćwiczenia1 Wypisz studentów, których imie zaczyna się na ’A’, ale druga

litera imienia to nie ’n’



Agregacje

Funkcje agregującePodgrupyFiltrowanie podgrup

Agregacje

Służą do wyliczania „statystyk”Grupują wiersze według zadanej kolumny (kategorii)Dostępne funkcje agregująceCOUNT(*) - liczba wierszyCOUNT(distinct wyr) liczba niepustych wystąpień wyrMAX, MIN, AVG - wartość maksymalna minimalna, średniaSUM - suma elementów

Example

SELECT COUNT(∗ ) FROM S t u d e n c i ;

SELECT MIN( Ocena ) , MAX( Ocena ) , AVG( Ocena )FROM S t u d e n c i ;

SELECT MIN( Ocena ) , MAX( Ocena ) , AVG( Ocena )FROM S t u d e n c iWHERE I m i e l i k e ’M%’ ;



Agregacje


Grupy i podgrupy

Można agregować dla więcej niż jednej grupyTworzona jest „hierarchia” ze względu na kolumnyKlauzula GROUP BYKażda kolumna, która znajdzie się w wyrażeniu SELECT musialbo być wewnątrz funkcji agregującej albo w klauzuli GROUPBY

Example

SELECT Imie , AVG( Ocena ) FROM S t u d e n c iGROUP BY( I m i e ) ;

SELECT sub s t r ( Imie , 1 , 1 ) im , AVG( Ocena ) FROM S t u d e n c iGROUP BY( s ub s t r ( Imie , 1 , 1 ) )ORDER BY im ;

SELECT sub s t r ( Imie , 1 , 1 ) , s ub s t r ( Nazwisko , 1 , 1 ) , AVG( Ocena )FROM S t u d e n c iGROUP BY( s ub s t r ( Imie , 1 , 1 ) , s ub s t r ( Nazwisko , 1 , 1 ) ) ;



Agregacje


Filtrowanie grup

Klauzula WHERE odfiltrowuje na podstawie wartości kolumn

Filtrowanie na podstawie wyników agregacji klauzulą HAVINGUżycie klauzuli HAVING nie wyklucza użycie WHEREWHERE jest stosowane „wcześniej”

Example

SELECT Imie , COUNT(∗ ) FROM S t u d e n c iWHERE COUNT(∗ ) >= 3 GROUP BY I m i e ; −− Błąd ! !

SELECT Imie , COUNT(∗ ) FROM S t u d e n c iGROUP BY I m i e HAVING COUNT(∗ ) >= 3 ;



Agregacje


Ćwiczenia

Ćwiczenia1 Wyświetl maksymalną ocenę uzyskaną przez studentów,

których nazwisko zaczyna się na ’K’2 Wyświetl statystyki ocen: ile jakich ocen było3 Wyświetl ile osób urodziło się w danym miesiącu4 Wyświetl średnią ocen dla kobiet i dla mężczyzn z podziałem

na miesiąc, w którym są urodzeni5 Wyświetl średnią ocen według miesiąca urodzenia studenta

biorąc pod uwagę oceny zaliczające (3 i więcej) oraz temiesiące, dla których osób w nich urodzonych jest więcej niż 2.


Złączenia

Część III

Złączenia


Złączenia

Plan prezentacji

7 ZłączeniaZłączenia wewnętrznePołączenia zewnętrzneWiele tabel


ZłączeniaZłączenia wewnętrznePołączenia zewnętrzneWiele tabel

Złączenia tabel

Dane mogą znajdować się w wielu tabelachRóżne rodzaje relacji między tabelami

Jeden do jednegoJeden do wieluWiele do jednegoWiele do wielu

Złączenia umożliwiają wybór z wielu tabel jednocześnieRóżne możliwości złączeń

WewnętrzneZewnętrzne

Wyniki złączeń można filtrować (WHERE), sortować ( SORT BYi grupować (GROUP BY) tak, jak zapytania z jednej tabeli.



Iloczyn kartezjański

Tabele są relacjamiRelacje są zbiorami

Można wykonać iloczyn kartezjański zbiorów

Każdy wiersz jednej tabeli łączony jest z każdym wierszemdrugiej

Można wykonać iloczyn więcej niż dwóch tabel

Wynik może mieć bardzo dużo wierszy

Łączone tabele wymieniane po przecinku

Example

SELECT ∗ FROM S t u d e n c i , Przedmioty ;



Aliasy tabel

Mogą zdażyć się konflikty nazw kolumn tabel

Można poprzedzać nazwy kolumn nazwami tabel

Example

−− Błąd , kolumna I d j e s t w obu t a b e l a c hSELECT ∗ FROM Przedmioty , K s i a z k i WHERE I d = 4 ;−− PoprawnieSELECT ∗ FROM Przedmioty , K s i a z k i WHERE K s i a z k i . I d = 4 ;

Tabelom podobnie jak kolumnom można nadawać aliasy

Example

SELECT ∗ FROM Przedmioty p , K s i a z k i k WHERE p . I d = 3 ;



Połączenia równościowe

Tabele najczęściej łączy się „po kolumnie”Jedna z tabel zawiera informacje uzupełniające do infromacjiw drugiejWartości w danej kolumnie muszą być równeKlauzula JOIN tabela ON warunek złączeniaW obu tabelach muszą istnieć odpowiadające wiersze

Jeśli możliwych połączeń jest wiele, zwielokrotnione zostanąwiersze w tabeli wynikowej.

Warunek złączenia podobny ja w klauzuli WHERE)

Example (Złączenie równościowe)

SELECT k . Tytul , s . Imie , s . NazwiskoFROM K s i a z k i kJOIN S t u d e n c i s ON k . Wypozyczajacy = s . I n d e k s ;



Połączenia naturalne

Kolumny, po których wykonywane jest łączenie częstonazywają się tak samo

Złączenie naturalne jest skróconym zapisem złączeniawewnętrznego

Klauzula NATURAL JOIN tabela bez podania

Example (Złączenie naturalne)

SELECT Nazwa , I n d e k s , Ocena FROM Przedmioty pJOIN Oceny o ONp . I d p r z e d m i o t u = o . I d p r z e d m i o t u ;SELECT Nazwa , I n d e k s , OcenaFROM PrzedmiotyNATURAL JOIN Oceny ;



Ćwiczenia

1 Wypisz wszystkich studentów i ich oceny2 Wypisz pary studentów, którzy mają tak samo na imię3 Wypisz pary studentów, których nazwiska zaczynają się na tą

samą literę4 Wypisz wszystkich studentów oraz średnią ich ocen ze

wszystkich przedmiotów5 Wypisz wszystkich studentów i średnią ich ocen z algebry i

baz danych (id przedmiotu 4 oraz 1)6 Wypisz wszystkich studentów i ich średnią z algebry i baz

danych któych średnia z tych przedmiotó jest większa niż 4.07 Wypisz dane jak do punktu poprzedniego sortując po średniej.



Połączenia zewnętrzne

Połączenia wewnętrznePasujące wiersze muszą istenieć w obu tabelach

Połączenia zewnętrzneW jednej z tabel nie musi istnieć wiersz pasujący

Odpowiadają zapytaniom typuWypisz dane o pracownikach i ich żonach (o ile mają żonę)

KlauzuleLEFT JOINRIGHT JOINFULL JOIN



LEFT JOIN

W składni podobne do JOIN

Uwzględniają wszystkie wiersze z tabeli pierwszej i tylko tewiersze z tabeli dołączanej, które pasują

Example (LEFT JOIN)

Wypisz wszystkich studentów i tytuły książek, które wypożyczyli, oile mają wypożyczoną książkę.SELECT Imie , Nazwisko , T y t u l FROM S t u d e n c i sLEFT JOIN K s i a z k i k ON ( s . I n d e k s=k . Wypozyczajacy ) ;



RIGHT JOIN


Uwzględniają wszystkie wiersze z tabeli dołączanej i tylko tewiersze z tabeli pierwszej, które pasują

Example (RIGHT JOIN)

Wypisz wszystkie książki i nazwiska studentów, którzy jewypożyczyli (o ile ktoś wypożyczył daną książkę).SELECT Imie , Nazwisko , T y t u l FROM S t u d e n c i sRIGHT JOIN K s i a z k i k ON ( s . I n d e k s=k . Wypozyczajacy ) ;



FULL JOIN


Uwzględniają wszystkie wiersze z tabeli pierwzej i wszystkiewiersze z tabeli drugiej

Jeśli jakieś wiersze pasują, to są one wypisywane jako jeden

Example (FULL JOIN)

Wypisz wszystkie książki i wszystkich studentów, którzy jewypożyczyli (lub nie wypożyczyli nic).SELECT Imie , Nazwisko , T y t u l FROM S t u d e n c i sFULL JOIN K s i a z k i k ON ( s . I n d e k s=k . Wypozyczajacy ) ;



Łączenie wielu tabel

Możliwe jest kaskadowe łączenie wielu tabel

Wynik złączenia jest argumentem kolejnego złączenia (jakotabela „lewa”)

Wykorzystywane np. w złączeniu wiele do wielu

Możliwe jest łączenie tabel samych ze sobą

Example (Many to many)

Wypisz studentów, przedmioty oraz oceny jakie z nich dostali.SELECT Imie , Nazwisko , Nazwa , OcenaFROM S t u d e n c i sJOIN Oceny o ON s . I n d e k s = o . I n d e k sJOIN Przedmioty p ON o . I d p r z e d m i o t u = p . I d p r z e d m i o t uWHERE I m i e l i k e ’A%’ ;



Ćwiczenia

1 Wypisz wszystkich studentów, którzy mają wypożyczoną choćjedną książkęd

2 Wypisz wszystkich studentów i ich ocenę z przedmiotów,których nazwa zaczyna się na ’A’, o ile takową dostali

3 Dla każdego studenta wypisz, ile ma pożyczonych książek;4 Wypisz nazwę przedmiotu, średnią ocen z niego w zależności

od tego, na jaką literę zaczyna się nazwisko osoby


Podzapytania

Część IV

Podzapytania


Podzapytania

Plan prezentacji

8 PodzapytaniaPodzapytania jako źródło zapytańPodzapytania filtrującePodzapytania skorelowane


PodzapytaniaPodzapytania jako źródło zapytańPodzapytania filtrującePodzapytania skorelowane

Podzapytania

Zapytania umieszczone wewnątrz innych zapytań

Zawsze umieszczone w nawiasachMożliwe do umieszczenia w

Klauzuli FROMKlauzuli WHERE, HAVINGKlauzuli SELECT

Używane w zależności od ilości zwracanych wierszyWierszoweTablicowe

Możliwe użycie wyników zapytania nadrzędnego



Zapytania jako źródło zapytań

Podzapytania można umieszczać jako tabelę źródłowązapytania

Po klauzuli FROMJako prawy element złączenia

Aliasy kolumn podzapytania stają się nazwami kolumn tabeliźródłowejObowiązują te same zasady co w zwykłych zapytaniach

Example (Podzapytanie FROM)

SELECT ∗ FROM (SELECT Imie , Nazwisko , AVG( Ocena ) s r e d n i aFROM S t u d e n c iNATURAL JOIN OcenyGROUP BY Imie , Nazwisko ) ;

−− Błąd p o n i ż e j , z ł a nazwa kolumnySELECT Nazwisko , AVG( Ocena )

FROM (SELECT Imie , Nazwisko , AVG( Ocena ) s r e d n i aFROM S t u d e n c iNATURAL JOIN OcenyGROUP BY Imie , Nazwisko ) ;Piotr Kaczyński Bazy danych


Podzapytania wierszowe

Zwracają pojedynczy wiersz (może być klika kolumn)Możliwe do użycia w klauzuli WHERE

Jako wrunek z operatorem logicznym (=, <= itd)Warunek = i <> może być używany do wielu kolumnLiczy się kolejność kolumn w podzapytaniu

Można również użyć bezpośrednio w klauzuli SELECTDefiniuje wartość kolumny

Example

SELECT Imie , Nazwisko FROM S t u d e n c iNATURAL JOIN OcenyWHERE Ocena = (SELECT MIN( Ocena )

FROM OcenyNATURAL JOIN S t u d e n c iWHERE I d P r z e d m i o t u = 1)

AND I d P r z e d m i o t u = 1 ;



Podzapytania wierszowe - Przykład

Example

Wypisz wszystkich studentów, którzy mają na imię Piotr i ichnumer indeksu jest równy średniej wartości wszystkich indeksów.SELECT Imie , Nazwisko FROM S t u d e n c iWHERE ( Imie , I n d e k s ) =

(SELECT ’ P i o t r ’ , AVG( I n d e k s ) FROM S t u d e n c i ) ;

Example

Wybierz wszystkich studentów, których średnia ocen jest mniejszaniż średnia ocen wszystkich studentów ze wszystkich przedmiotów.SELECT Imie , Nazwisko , AVG( Ocena )FROM S t u d e n c iNATURAL JOIN OcenyGROUP BY Imie , NazwiskoHAVING AVG( Ocena ) < (SELECT AVG( Ocena ) FROM S t u d e n c i

NATURAL JOIN Oceny ) ;



Podzapytania tablicowe

Podzapytania zwracają więcej niż jeden wierszMożliwe wykorzystanie

W klauzuli WHEREW klauzuli HAVING

Porównywanie elementu wiersza zapytania głównego zwieloma wierszami podzapytaniaOperatory logiczneALLANY



Operatory ALL i ANY

Operator ANYWarunek jest spełniony jeśli zachodzi dla dowolnego wierszapodzapytania

Operator ALLWarunek jest spełniony jeśli zachodzi dla każdego wierszapodzapytania

Jeśli operatorem porównującym jest = to porównania możnarobić dla wielu kolumn.



Operatory ALL i ANY przykład

Example (Operator ANY)

Wypisz wszystkich studentów którzy mają średnią ocenę większąniż dowolna z ocen z przedmiotu o id 1SELECT Imie , Nazwisko FROM S t u d e n c i NATURAL JOIN OCENYGROUP BY Imie , NazwiskoHAVING AVG( Ocena ) > ANY (SELECT Ocena FROM Oceny

WHERE I d P r z e d m i o t u = 1 ) ;

Example (Operator ALL)

Wypisz wszystkich studentów którzy mają średnią ocenę większąniż średnia z ocen z poszczególnych przedmiotów.SELECT Imie , Nazwisko FROM S t u d e n c i NATURAL JOIN OCENYGROUP BY Imie , NazwiskoHAVING AVG( Ocena ) > ALL (SELECT AVG( Ocena ) FROM Oceny

GROUP BY I d P r z e d m i o t u ) ;



Zagnieżdżanie podzapytań

Każde podzapytanie może mieć podzapytania

Zagnieżdżeń może być nieskończenie wiele

Uwaga na wydajność takich zapytań!

Example

Wypisz tych studentów, którzy mają pożyczoną książkę o tytule,który zaczyna się na tą samą literę, co książka wypożyczona przezstudenta o największym numerze indeksu wśród studentówwypożyczających książki.SELECT Imie , Nazwisko FROM S t u d e n c iJOIN K s i a z k i ON S t u d e n c i . I n d e k s = K s i a z k i . WypozyczajacyWHERE sub s t r ( Tytul , 1 , 1 ) =

(SELECT sub s t r ( Tytul , 1 , 1 ) FROM K s i a z k iWHERE Wypozyczajacy =

(SELECT MAX( Wypozyczajacy ) FROM K s i a z k i ) ) ;



Ćwiczenia

z1 Wypisz studenta o najdłuższym nazwisku

z2 Wypisz imiona, nazwiska oraz średnią ocen tych studentów,którzy mają średnią o 10% lepszą niż średnia wszystkichstudentów

z3 Wypisz kto dostał najlepszą ocenę z Algebry (id przedmiotu =1)

z4 Wypisz imię i nazwisko studenta, który wypożyczył najwięcejksiążek oraz ile tych książek pożyczył



Podzapytania skorelowane

Podzapytania mogą mieć jako parametr wartość zapytanianadrzędnego

Uwaga na wydajność i aliasy

Example

Wypisz studentów (imię i nazwisko), którzy mają wypożyczonąksiążkę o tytule, który zaczyna się na literę większą niż pierwszalitera tytułu dowolnej książki autora, którego nazwisko zaczyna sięna tą samą literę co nazwisko studenta.SELECT Imie , Nazwisko FROM S t u d e n c i sJOIN K s i a z k i ON s . I n d e k s = K s i a z k i . WypozyczajacyWHERE sub s t r ( Tytul , 1 , 1 ) > ALL (SELECT sub s t r ( Tytul , 1 , 1 )

FROM K s i a z k iWHERE sub s t r ( Autor , 1 , 1 ) =

s ub s t r ( s . Imie , 1 , 1 ) ) ;



Operator EXISTS

Operator sprawdzający czy isnieje choć jeden odpowiadającywiersz

Podzapytanie obliczane dla każdego wiersza

Example (Exists)

Wypisz wszystkich studentów, dla których da się znaleźć innegostudenta o nazwisku zaczynającym się na te same trzy pierwszelitery.SELECT Imie , Nazwisko FROM S t u d e n c i sWHERE EXISTS (SELECT ∗ FROM S t u d e n c i

WHERE sub s t r ( Nazwisko , 1 , 3 ) =s ub s t r ( s . Nazwisko , 1 , 3 )AND s . I n d e k s <> I n d e k s ) ;



Ćwiczenia

x1 Wypisz nazwy przedmiotów oraz imiona i nazwiska tychstudentów, którzy mają największą średnią w danymprzedmiocie.

x2 Wypisz imiona i nazwiska oraz średnie tych studentów, którychśrednia jest najwyższa wśród studentów o tym samym imieniu.

x3 Wypisz imiona i nazwiska tych studentów, którzy mają imiętakie, którego nie ma żaden inny student

x4 Wypisz tych studentów, którzy mają wszystkie oceny niższeniż średnia tych studentów, którzy mają tak samo na imię.


Definiowanie tabelModyfikacja danych

Część V

Manipulacja danymi



Plan prezentacji

9 Definiowanie tabelTypy danychPolecenia DDL

10 Modyfikacja danychWstawianieZmianaUsuwanie



Typy danychPolecenia DDL

Typy liczbowe

Typ Opis MaxNUMBER(n,p) Liczba o precyzji n, p p=38, s=-84− >127INTEGER Synonim NUMBER(38) 38 cyfrFLOAT Zmiennoprzecinkowy

Bez podania precyzji NUMBER oznacza liczbęzmiennoprzecinkową o maksymalnie 9 cyfrach po przecinku.

n oznacza liczbę wszystkich cyfr w liczbie

Podając tylko n oznacza liczbę całkowitą o n cyfrach

p oznacza ilość cyfr „po przecinku” (wśród n wszystkich)

Podając ujemne p liczba jest zaokrąglana do dziesiątek (-1),setek (-2), tysięcy (-3) itd.




Typy liczbowe - przykłady

Example

Liczba 12345678.89

NUMBER → 12345678.9

NUMBER(5) → nie wyświetli się (za mała precyzja)

NUMBER(9,1) → 12345678.9

NUMBER(8,1) → nie wyświetli się (za mała precyzja)

NUMBER(7,-1) → 123456780

NUMBER(7,-2) → 123456700




Typy znakowe

Typ Opis MaxVARCHAR2 Ciąg znaków zmiennej długości 4000NVARCHAR2 Ciąg znaków narodowych zmiennej długości 4000CHAR Ciąg znaków stałej długości 2000NCHAR Ciąg znaków narodowych stałej długości 2000CLOB Bardzo długi ciąg znaków 4GB

Pola o zmiennej szerokości będą zajmować tylko tyle znaków,ile przechowywany tekstPola o stałej szerokości wypełniane są znakami pustymi, jeślitekst nie zajmuje całości

Nadają się do kodów, symboli etc.




Daty i czas

Typ Opis MaxDATE Data (do dni) 31.12.9999TIMESTAMP Dokładny czasINTERVAL YEAR TO MONTH Przedział czasowyINTERVAL DAY TO SECOND Przedział czasowy

Typ TIMESTAMP można opatrzeć WITH TIME ZONE -dodatkowo przesunięcie czasoweKażdy z typów można opatrzeć precyzjąTIMESTAMP(n) ilość cyfr na ułamki sekundyINTERVAL YEAR(n) TO MONTH ilość cyfr dla rokuINTERVAL DAY(n) TO SECOND(p) n cyfr dla dni i p dlaułamka sekund




Binarne

Typ Opis MaxRAW Ciąg bajtów 2000BLOB Bardzo długi ciąg bajtów 4GBXMLType Dane w XML 4GB

Typy binarne są mało przenośne

Mało wydajne przy wyszukiwaniu

Należy używać z wielką ostrożnością




Wartości puste

Wartość pustą reprezentuje literał NULL

Dana kolumna może zawierać wartość pustą o ile definicja nato pozwala

Identyfikuje wartość „nie znana”W zapytaniach można sprawdzać czy dana kolumna jest NULL

Oparator logiczny IS NULLL oraz IS NOT NULLNie można stosować = NULL

W Oracle pusty ciąg znaków jest identyfikowany jako NULL, anie ’’

Example

SELECT ∗ FROM S t u d e n c i WHERE P e s e l I S NOT NULL ;




Tworzenie tabeli

Polecenie CREATE TABLE nazwa

Po nim następuje w nawiasach lista nazw kolumn i ich typów

Lista oddzielana przecinkami

Można podać wartość dimyślną kolumny DEFAULT

Example

CREATE TABLE S t u d e n c i ( I m i e VARCHAR2( 3 0 ) ,Nazwisko VARCHAR2( 3 0 ) ,Ocena NUMBER( 2 , 1 ) ) ;

CREATE TABLE S t u d e n c i ( I m i e VARCHAR2( 3 0 ) ,Nazwisko VARCHAR2( 3 0 ) ,Ocena NUMBER( 2 , 1 ) DEFAULT 2 ) ;




Ograniczenia

Domyślnie wszystkie kolumny mogą przyjąć wartość NULLPrzy definicji można dodać NOT NULL po typie kolumny

Można dodać ograniczenie na wartość liczbowąPrzy definicji kolumny podajemy warunek po słowie CHECKNa końcu definicji kolumn jeśli obejmuje więcej niż jednąkolumnę

Example

CREATE TABLE S t u d e n c i ( I m i e VARCHAR2( 3 0 ) NOT NULL ,Nazwisko VARCHAR2( 3 0 ) NOT NULL ,Ocena NUMBER( 1 , 1 )CHECK ( Ocena >= 2 AND Ocena <= 5 ) ;

CREATE TABLE Praca (Od DATE NOT NULL ,Do DATE NOT NULL ,CHECK (Od <= Do ) ;




Modyfikacja tabeli

Właściwości tabeli i kolumn można zmieniaćPolecenie ALTER TABLE nazwaDROP COLUMN nazwa - usuwa kolumnę (niezalecane!)ADD nazwa typ - dodaje kolumnę o zadanym typieMODIFY nazwa typ - zmienia typ kolumnyRENAME TO nazwa - zmienia nazwę tabeliRENAME COLUMN nazwa TO nazwa2 - zmienia nazwę kolumny

Example

ALTER TABLE DROP COLUMN Ocena ;ALTER TABLE S t u d e n c i ADD P e s e l NUMBER( 1 1 ) ;ALTER TABLE S t u d e n c i MODIFY Ocena NUMBER( 4 , 2 ) ;ALTER TABLE S t u d e n c i RENAME TO Wyniki ;ALTER TABLE S t u d e n c i RENAME COLUMN Ocena RENAME TO Wynik ;




Usuwanie tabeli

Wykonywane polecenien DROP TABLE nazwaOperacja niepolecana

Szczególnie, gdy są ważne dane

Operacja nieodwracalna

Usuwa, jeśli tabela nie jest przez kogoś używana

Example

DROP TABLE S t u d e n c i ;



WstawianieZmianaUsuwanie

Transakcyjność

Każda operacja wykonywana jest w ramach transakcjiTransakcje powinny być ACID

Atomicity - niepodzielnośćConsistency - spójnośćIsolation - izolacjaDurability - trwałość

Zatwierdzanie transakcji COMMIT

Cofanie transakcji ROLLBACK

Operacje DDL wykonują COMMIT automatycznie

Wszystkie inne należy zatwierdzać ręcznie




Wstawianie proste

Wiersz wstawiany jest przezINSERT INTO nazwa tabeli VALUES(wartości)

Wartości podawane są w kolejności takiej, jak definicjakolumnyMożna podać do których kolumn i w jakiej kolejnościINSERT INTO nazwa tabeli(kolumny) VALUES(wartości)

Nie trzeba podawać wszystkich kolumn (wtedy wstawionezostaną NULLe)

Example

CREATE TABLE S t u d e n c i ( I m i e VARCHAR2( 3 0 ) ,Nazwisko VARCHAR2( 3 0 ) ,Ocena NUMBER( 2 , 1 ) ) ;

INSERT INTO S t u d e n c i VALUES( ’ P i o t r ’ , ’ Kaczynsk i ’ , 5 ) ;INSERT INTO S t u d e n c i ( Nazwisko , I m i e )

VALUES( ’ P i o t r ’ , ’ Kaczynsk i ’ ) ;




Wstawianie z zapytania

Wartości wstawiane do tabeli mogą pochodzić z zapytaniaObie wersje INSERT dozwolone (z podaniem kolumn i bez)

Można tworzyć tabelę od razu uzupełniając ją danymiPolecenie CREATE TABLE nazwa AS SELECT FROM ...Można podać nazwy kolumn i ich typy (ale muszą być zgodne)

Example

INSERT INTO Oceny SELECT ∗ FROM Oceny ;INSERT INTO Oceny ( Ocena ) SELECT Ocena∗2 FROM Oceny ;CREATE TABLE Wyniki AS SELECT ∗ From Oceny ;CREATE TABLE Wyniki ( Nazwisko VARCHAR2( 3 0 ) ,

Wynik NUMBER( 3 , 1 ) )AS SELECT Nazwisko , Ocena∗3 FROM Oceny ;




Zmiana wierszy

Polecenie UPDATE nazwa tabeli SET

Po tym następuje lista kolumna = wartość

Na końcu klauzula WHERE aby wyselekcjonować, któreweirsze mają być zmienione

Wartość może być przypisana z podzapytania wierszowego

Example

UPDATE Oceny SET Ocena = 2 ;UPDATE Oceny SET Ocena = 2∗Ocena ;UPDATE Oceny SET Ocena = 2 ,

Uwagi = ’ Im i e na A ’WHERE I m i e LIKE ’A%’ ;

UPDATE Oceny o SET Ocena = (SELECT AVG( Ocena ) FROM Oceny )WHERE EXISTS (SELECT Ocena FROM Oceny

WHERE Ocena > o . Ocena ) ;




Usuwanie proste

Usuwanie poleceniem DELETE FROM nazwa tabeli

Można (w zasadzie powinno się) podać klauzulę WHERE

Klauzula WHERE ogranicza wiersze, które mają być skasowane

Można stosować podzapytania

Example

DELETE FROM S t u d e n c i ;DELETE FROM S t u d e n c i WHERE I m i e LIKE ’A%’ ;DELETE FROM S t u d e n c i s

WHERE s . ocena <SELECT AVG( Ocena ) FROM S t u d e n c i ;




Ćwiczenia

Ćwiczenia

Napisz skrypt, który1 Utworzy tabelę Pracownicy z kolumnami Imie, Nazwisko,

PESEL, Pensja2 Wstawi 5 pracowników do tej tabeli (wymyśl dane)3 Doda kolumnę Kod o takim samym rozmiarze jak PESEL4 Zakuduje numery PESEL dodając do każdej cyfry 2 (modulo

10) i wstawi wartości do kolumny Kod5 Usunie wszystkich pracowników, których Kod PESEL kończy

się na 06 Podwyższy pozostałym pracownikom pensję o 20%7 Podwyższy o 10% pensję tym pracownikom, którzy mają

pensję większą niż średnia pensja wszystkich pracowników


Model pojęciowyModel logiczny

Normalizacja

Część VI

Projektowanie bazy danych



Normalizacja

Plan prezentacji

11 Model pojęciowyDiagramy związków encjiZwiązkiIdentyfikacja encji

12 Model logicznyPrzekształcanie modelu pojęciowegoOgraniczenia integralnościIndeksy

13 NormalizacjaZależnościPostacie normalne



Normalizacja

Diagramy związków encjiZwiązkiIdentyfikacja encji

Cele projektowania pojęciowego

Model konceptualny

Precyzyjne określenie zakresu projektu

Określenie wymagań użytkownikaModelowanie informacji

Niezbędne dla działania systemuNiezależne od implementacjiWedług określonego modelu danych

Kierujemy się potrzebami użytkownika

Odpowiada na pytanie co? a nie jak?



Normalizacja


Diagramy związków encji

Przykład modelu pojęciowego

Entity Relationship Diagram (ERD)Elementy diagramu

EncjeAtrybuty encjiZwiązkiUnikalne identyfikatory

Kilka konwencji diagramówWyamagana konsekwencja!



Normalizacja


Diagram związków encji - przykład



Normalizacja


Encje

Model rzeczy, osób itp.O których chcemy przechowywać informacjeKtóre mają tożsamość

Nazwa: rzeczownik w l.poj., wielkie litery

Example (Przykłady encji)

produkt (towar), przedmiot

osoba, pracownik, klient

dokument, pozycja dokumentu

...



Normalizacja


Atrybuty

Określają cechy rzeczy, osób itp.IdentyfikująOpisująPodają ilościKlasyfikują

Są typu prostego (atomowego, nie są tablicami)Muszą mieć precyzyjne nazwyRodzaje atrybutów

Należące do unikalnego identyfikatora (znak #)Obowiązkowe (znak *)Opcjonalne (znak o)

Example (Przykłady atrybutów)

Kod towaru, nazwa

Nr identyfikacyjny, imię, nazwisko, adres

Cena, wartość, rodzaj płatnościPiotr Kaczyński Bazy danych


Normalizacja


Związki

Pokazują zależności między encjami

Powinny być obustronnie nazwane

Nie zapisuje się sposobu realizacjiCechy związków

Opcjonalność (po każdej stronie)Opcjonalny (linia przerywana)Obowiązkowy (linia ciągła)

Stopień (po każdej stronie)Jeden (1, pojedyncza linia)Wiele (n, kurza łapka)

Pierwotna unikalność identyfikatora (pozioma kreska)Transferowalność (związek nietransferowalny zaznacza �)Rekurencyjność



Normalizacja


Błędny projekt pojęciowy

Uwzględnianie identyfikatorów uwzględnia implementację



Normalizacja


Transferowalność i rekurencyjność

Związek tranferowalnyTą samą instancję związku można przenieść na inne encje

Example

Relacja Producent → Towar nietransferowalna

Relacja Towar → Właściciel transferowalna

Związek rekurencyjnyJedna instancja encji jest w relacji z inną instancją tej samejencjiDrzewa, grafy

Example

Każdy pracownik ma szefa, który również jest pracownikiem

Relacja Pracownik → Pracownik



Normalizacja


Poprawność związków

Związki muszą sensownie odzwierciedlać rzeczywistośćZwiązki 1-1 są podejrzaneZwiązki obustronnie obowiązkowe są podejrzane (zbytnieobostrzenie)Związki rekurencyjne muszą być obustronnie opcjonalneRozbicie związków n-m (wiele do wielu)



Normalizacja


Związki wykluczające się

Stosujemy, gdy dana encja może być w związku z jedną zdwóch różnych encjiZapisywana w konwencji „łuku”Łuk może być

Obowiązkowy (zachodzi dokładnie jeden związek)Opcjonalny (zachodzi jeden lub żaden)



Normalizacja


Związki rekurencyjne

DrzewaZwiązek obustronnie opcjonalnyModelują hierarchię

GrafyRekurencyjny związek n-mOstatecznie - dwa związki 1-n



Normalizacja


Identyfikacja encji

Każda encja powinna mieć unikalny identyfikatorMożliwe składniki identyfikatora

AtrybutyZwiązkiAtrybuty i związki

Example

Pozycję faktury identyfikuje numer pozycji i związek z odpowiedniąfakturą.

Pierwotny unikalny identyfikatorIdentyfikuje wszystkie wystąpienia encjiWszystkie składniki obowiązkowePowinien być niezmienny, łatwy do wyznaczenia, czytelny,słabo złożony (mała ilość znaków)

Wtórny unikalny identyfikatorDo celów kontrolnychPola mogą być opcjonalne



Normalizacja

Przekształcanie modelu pojęciowegoOgraniczenia integralnościIndeksy

Cele projektu logicznego

ZaprojektowanieStruktur dcanychOgraniczeń integralnościObudowy proceduralnej (wyzwalacze)PerspektywRozwiązań dla przetwarzania rozproszonego

Brany pod uwagę konkretny SZBD

Projekt logiczny jest podstawą do stworzenia fizycznej bazydanych



Normalizacja


Przekształcenie modelu pojęciowego w logiczny

Encje → tabeleNazwy tabel w liczbie mnogiejBez znaków narodowych etc.

Atrybuty → kolumnyTypy dostępne w SZRBDObowiązkowość → NOT NULL

Pierwotne identyfikatory unikalne → klucze główne

Wtórne identyfikatory unikalne → ograniczenia unikalnościZwiązki → klucze obce

Obowiązkowość → NOT NULLNietransferowalność → niezmienność klucza



Normalizacja


Ograniczenia integralności

DeklaratywneWykonywane przezn serwer bdDotyczą wszystkich operacjiDotyczą wszystkich wierszyWykonywane bezbłędnie i zoptymalizowane

ProceduralneWykonywane przez serwer (dla wszystkich operacji) lubaplikacjęDotyczą tylko zmienianych danychKod może zawierać błędy (pisane przez developerów)Realizowane przez wyzwalacze (tzw. triggers)

Należy dążyć do ograniczeń deklaratywnych



Normalizacja


Wyzwalacze

Procedury rezydujące w serwerze b.d.Automatycznie wyzwalane przez:

Wstawienie wierszaUsunięcie wierszaModyfikację określonych kolumn

Służą doWymuszania nietypowych reguł integralnościNietypowych zabezpieczeń dostępuŚledzenia zmian w b.d.Wymuszania integralności referencyjnej w rozproszonych b.d.Nietypowej replikacji danych

Klauzla CREATE TRIGGER



Normalizacja


Ograniczenia deklaratywne

Kolumny: NOT NULL, CHECK

Wiersza: CHECK

Tabeli: Klucz główny (PRIMARY KEY), unikalność (UNIQUE)

Referencyjne: Klucz obcy (FOREIGN KEY

Example (Ograniczenie UNIQUE)

CREATE TABLE STUDENCI( I m i e VARCHAR( 3 0 ) ,Nazwisko VARCHAR( 3 0 ) ,P e s e l NUMBER( 1 1 ) ,UNIQUE( I m i e ) ) ;



Normalizacja


Klucz główny

Funkcja: jednoznaczna identyfikacja wierszaTworzenie ograniczeniem deklaratywnym PRIMARY KEYDla idetyfikatora z samych atrybutów: odpowiednie kolumnyDla identyfikatora z atrybutów i związków: kolumny atrybutówi kolumny kluczy obcychSztuczne klucze główne

Klucz numerycznyGenerowane z sekwencji

Klucz główny tworzy indeks

Example

CREATE TABLE STUDENCI( I m i e VARCHAR( 3 0 ) ,Nazwisko VARCHAR( 3 0 ) ,Ocena NUMBER( 1 , 1 ) ,PRIMARY KEY( Imie , Nazwisko ) ) ;



Normalizacja


Klucz obcy

Realizują związkiImplementacja:

Dodanie kolumn klucza obcego odpowiadających kolumnomwskazywanego klucza głównegoOgraniczenie deklaratywne REFERENCES

Sposób obsługi modyfikacji/usuwania wskazywanego kluczagłównego

Kaskada - ON DELETE CASCADE (kasuje/modyfikuje się razemz kluczem głównym)Restrykcja - bez klauzuli (usuwanie/modyfikacja niemożliwa,gdy jest choć jedna instancja encji podrzędnej)

Reprezentacja łukuDwa osobne klucze obce opcjonalneWarunek wykluczania (CHECK)

Na każdy klucz obcy powinien być nałożony indeks



Normalizacja


Klucz obcy - przykłady

Example

CREATE TABLE F a k t u r y ( i d f a k t u r y NUMBER PRIMARY KEY ) ;CREATE TABLE P o z y c j e ( i d p o z y c j i NUMBER,

i d f a k t u r y REFERENCES F a k t u r y ( i d f a k t u r y )ON DELETE CASCADE ) ;



Normalizacja


Indeksy

Specjalne struktury w bazie danych

Przyspieszają wyszukiwanie danych

Przyspieszają złączenia tabel

Ich użycie musi być uzasadnione

Przy częstych zmianach tabeli są mało wydajne

Zajmują miejsce na dysku

Tworzenie poleceniem CREATE INDEX

Example

CREATE INDEX n a z w a i n d e k s u ON t a b e l a ( kolumny ) ;CREATE INDEX f k i n d e x p o z y c j e ON P o z y c j e ( i d f a k t u r y ) ;



Normalizacja

ZależnościPostacie normalne

Zależności funkcyjne

Definition

Mówimy, że zbiór atrybutów Y jest zależny funkcyjnie od zbioruatrybutów X tej samej relacji, gdy z każdą konfiguracją wartościatrybutów z X jest związana co najwyżej jedna konfiguracjawartości w Y . Zależność tą oznaczamy X → Y .

Example

Imię zależy funkcyjnie od id pracownika

Id pracownika nie zależy funkcyjnie od imienia

Imię nie zależy funkcyjnie od nazwiska



Normalizacja


Zależność tranzytywna

Definition

Mówimy, że istnieje zależność tranzytywna A do X jeśli istniejezbiór Y zależny funkcyjne od X oraz zbiór A jest zależnyfunkcyjnie od Y . Przy czym X nie jest zależne funkcyjnie od Xoraz A nie jest podzbiorem sumy X i Y .



Normalizacja


Zależności wielowartościowe

Definition

Mówimy, że zbiór atrybutów Y jest zależny wielowartościowo odzbioru X gdy z każdą konfiguracją wartości atrybutów z X jestzwiązany zbiór konfiguracji wartości z Y niezależnie od wartościpozostałych atrybutów. Zależność tą oznaczamy X →→ Y .

Example

Zajęcia Wykładowca PodręcznikBD Kaczyński XYZBD Kwasowiec XYZBD Kaczyński UVW

Podręczniki nie zależą od wykładowców

Nie ma zależności funkcyjnych

Zależności wielowartościowe Z →→W oraz Z →→ PPiotr Kaczyński Bazy danych


Normalizacja


Pierwsza postać normalna (1NF)

Definition

Relacja jest w 1NF gdy wszystkie atrybuty są atomowe – prostychtypów

1NF jest wymogiem dla rachunku relacyjnego, a więc ijęzyków zapytańKontrprzykłady:

Atrybut tablicowyZbiór



Normalizacja


Druga postać normalna (2NF)

Definition

Relacja jest w 2NF gdy każdy atrybut niekluczowy (nie należący doklucza właściwego) jest zależny funkcyjnie od całego kluczawłaściwego

Przyczyną braku 2NF jest zwykle błędne połączenie danychNaruszenie 2NF polega na istnieniu zależności funkcyjnej odfragmentu klucza właściwegoKontrprzykład: spis przepustekTabela z kolumnami #Id prac,#Budynek,NazwiskoNazwisko zależy funkcyjnie od id prac, czyli od fragmentukluczaNależy rozłożyć na dwie tabele:#Id prac,#Budynek#Id prac,Nazwisko



Normalizacja


Trzecia postać normalna

Definition

Relacja jest w 3NF gdy każdy atrybut niekluczowy jestbezpośrednio zależny funkcyjnie od całego klucza właściwego

Możliwe przypadki naruszenia 3NFNaruszenie 2NFIstnienie zależności tranzytywnej (a więc niebezpośredniej) odklucza właściwego

3NF jest zazwyczaj wystarczająca dla usunięcia praktycznieważnych anomaliiKażdy schemat można doprowadzić do 3NF zachowując

ZależnościOdwracalność rozkładu



Normalizacja


Trzecia postać normalna - przykład

Example

Dana jest następująca relacja#Id pracownika Nazwisko Stanowisko Pensja

Relacja jest w 2NF (klucz jednoatrybutowy)

Isnieje zależność tranzytywna I → S → PRozkład na INS oraz SP sprowadza do 3NF



Normalizacja


Postać normalna Boyce’a-Codda (BCNF)

Definition

Relacja jest w BCNF gdy każda nietrywialna zależność funkcyjnajest zależnością od klucza (niekoniecznie właściwego)

W BCNF zależności tranzytywne nie istnieją w ogóle

Nie każdy schemat można doprowadzić do BCNF zzachowaniem zależności

Najczęściej skutki wynikające z braku BCNF nie są istotne zpunktu widzenia projektu



Normalizacja


Postać normalna BC - przykład

Example

Dana jest następująca relacjaMiasto Ulica Kod pocztowy

Istnieją tu klucze MU, UK (nazwy ulic mogą się powtarzać wróżnych miastach)

Występują zależności MU → K , K → MAnomalie: usuwając ulicę możemy utracić informację o mieście

Relacja jest w 3NF: brak atrybutow niekluczowych

Relacja nie jest w BCNF: K nie jest kluczem

Relacja jest nierozkładalna do BCNF bez utraty zależności



Normalizacja


Czwarta postać normalna (4NF)

Definition

Relacja jest w 4NF gdy jeśli każda nietrywialna zależnośćwielowartościowa jest zależnością od klucza (niekonieczniewłaściwego)

Example

Dana jest następująca relacjaZajęcia Wykładowca Podręcznik

Istnieją zależności Z →→W oraz Z →→ PNie ma zależności funkcyjnych, a więc jest BCNF

Występuje nadmiar informacji: powtórzone dane dlapodręczników i wykładowców

Rozkład ZW , ZP doprowadza do 4NF



Normalizacja


Wymagania

Struktury relacyjne w systemach transakcyjnych powinny byćco najmniej w 3NFOdstępstwa

Poważne problemy wydajnościoweProjekt musi być znormalizowany, dopiero później możnadenormalizować

Konsekwencje odstępstwRedundancja i anomalieRyzyko utraty spójności danychKomplikacja kodu


PL/SQL - WstępKursory

Programy składowane

Część VII

PL/SQL




Plan prezentacji

14 PL/SQL - WstępPodstawyZmienne i typy danychInstrukcje sterujące

15 KursoryTworzenie i podstawowe użycie kursoraKursor a pętla FOR

16 Programy składowaneProcedury i funkcjeWyzwalacze




PodstawyZmienne i typy danychInstrukcje sterujące

Język PL/SQL

Język Proceduralny a nie deklaratywny,Mówimy jak a nie coW tym sensie podobny do C czy C++

Specyficzny dla SZRBD OracleWykorzystywany do zadań specjalnych

Nietypowe zapytaniaWykonywanie skomplikowanych zadań administracyjnychMigracja danych...

Preferowany język deklaratywny





Blok języka PL/SQL

Program składa się z bloków (odpowiedniki {...} w C)Bloki mogą być zagnieżdżane/ uruchamia ostatnio wprowadzony blok w SQL*Plus

Blok PL/SQL - podstawowy

BEGINp o l e c e n i a

END ;

Blok PL/SQL - rozszerzony

DECLAREd e k l a r a c j e

BEGINp o l e c e n i a

EXCEPTIONo b s ł u g a błędów

END ;Piotr Kaczyński Bazy danych




Typy danych

Te same co w SQLDodatkowo

Typ logiczny BOOLEAN (TRUE lub FALSE)Typ RECORD

Możliwa deklaracja tablicDeklaracja zmiennych w sekcji DECLAREWidoczność zmiennych podobnie jak w C (zagnieżdżaniebloków)Stałe deklarowane z CONSTANT

Example

DECLAREi m i e VARCHAR2( 2 0 ) ;p o c z a t e k DATE ;poprawnie BOOLEAN;





Inicjalizacja zmiennych

Inicjalizacja przez przypisanie :=Zmienne mogą przyjmować wartość NULLZmienne domyślnie przyjmują NULLMożna dodać ograniczenie NOT NULLStałe trzeba zainicjalizować

Example

DECLAREi m i e VARCHAR2( 2 0 ) := ’ P i o t r ’ ;p o c z a t e k DATE NOT NULL := ’ 2010−01−01 ’ ;poprawnie BOOLEAN := TRUE ;s t a l a CONSTANT VARCHAR2( 1 0 ) := ’Moja S t a l a ’ ;

BEGINNULL ;

END ;/





Rekordy

Odpowiednik struktury w C++Definicja typuTYPE nazwa typu IS RECORD(lista pól)

Deklaracja zmiennejnazwa typu nazwa zmiennej

Odwołanie do pól przez kropkę

Example

DECLARETYPE ADRES I S RECORD( U l i c a VARCHAR2( 2 0 ) , Numer NUMBER( 3 ) ) ;m o j a d r e s ADRES ;

BEGINm o j a d r e s . U l i c a := ’ Dewa j t i s ’ ;m o j a d r e s . Numer := ’ 5 ’ ;

END ;/





Sufiksy typów

Sufiks %TYPE wyciąga typ atrybutu do którego jest doklejony

Sufiks %ROWTYPE wyciąga typr rekordu jednego wiersza tabeli

Example

DECLAREi m i e s t u d e n t a s t u d e n c i . i m i e%TYPE ;d a n e s t u d e n t a s t u d e n c i%ROWTYPE;

BEGINi m i e s t u d e n t a := ’ P i o t r ’ ;d a n e s t u d e n t a . i m i e := ’ P i o t r ’ ;d a n e s t u d e n t a . n a z w i s k o := ’ Kaczynsk i ’ ;

END ;/





printf i scanf w PL/SQL

Pobranie od użytkownikaZmienna z &Zostanie zastąpiona przed wywołaniem procedury

Wypisanie na ekranFunkcja dmbs output.putlineNależy odpowiednio ustawić SQL*PlusSET SERVEROUTPUT ON

Example

SET SERVEROUTPUT ON;DECLARE

x NUMBER( 2 ) := &x ;BEGIN

DBMS OUTPUT. PUT LINE ( ’ Kwadrat t e j l i c z b y to ’ | | x∗x ) ;END ;/





Operatory w PL/SQL

Arytmetyczne+, -, *, /, **** oznacza potęgowanie

LogiczneAND, OR

Porównania=, <, >, <=, >=, <>, !=IS NULL, IS NOT NULL, LIKE





Instrukcja IF

Wykonuje instrukcje jeśli warunek jest prawdziwy (do END IF)Możliwa klauzula ELSEDodatkowe warunki ELSIF

Example

DECLAREx NUMBER( 2 ) := &x ;

BEGINIF x < 0 THEN

x := −1;ELSIF x = 0 THEN

x := 0 ;ELSE

x := 1 ;END IF ;

END ;/





Instrukcja CASE

Sprawdzanie wielu warunków (porównanie do wielu wartości)

Example

DECLAREx NUMBER( 2 ) := 2 ;

BEGINCASE

WHEN x > 2 THENx := −x ;

WHEN x < 2 THENx := 2∗x ;

ELSEx := x∗∗x ;

END CASE ;CASE x

WHEN 3 THENx := 4 ;

WHEN 4 THENx := 5 ;

END CASE ;END ;/





Pętla LOOP

Podobna do pętli do-while w CWyjście z pętli

Po napotkaniu EXITEXIT może być z warunkiem (EXIT WHEN)

Example

DECLAREx NUMBER( 2 ) ;

BEGINx := 0 ;LOOP

x := x +1;dbms output . p u t l i n e ( ’X = ’ | | x ) ;IF x = 5 THEN

EXIT ;END IF ;EXIT WHEN x = 5 ;

END LOOP;END ;/





Pętla WHILE

Podobna do pętli while w C

Wyjście z pętli EXIT i EXIT WHEN

Warunek pętli musi być spełniony

Example


BEGINx := 0 ;WHILE x <= 5 LOOP

x := x +1;dbms output . p u t l i n e ( ’X = ’ | | x ) ;

END LOOP;END ;/





Pętla FOR

Podobna do pętli for w CWyjście z pętli EXIT i EXIT WHENIterator zmienia się w zadanym zakresieIteratora nie można zmieniaćMożna iterować od końca (REVERSE)

Example


BEGINFOR x IN 1 . . 5 LOOP

dbms output . p u t l i n e ( ’X = ’ | | x ) ;END LOOP;FOR x IN REVERSE 1 . . 5 LOOP

dbms output . p u t l i n e ( ’X = ’ | | x ) ;END LOOP;

END ;/





Użycie SELECT

Można wartość zwróconą z zapytania podstawić do zmiennej

Zapytanie musi zwrócić jeden wiersz

Zmienna musi być typu rekordowego o tych samychatrybutach

Instrukcja SELECT INTO

Obowiązują te same zasady co w normalnym SELECT

Example

DECLAREd a n e a g e n t a a g e n c i%ROWTYPE;

BEGINSELECT ∗ INTO d a n e a g e n t a FROM Agenc i WHERE ID = 1 ;

END ;/





Użycie DML

DELETE identyczne jak deklaratywne;

Można używać zmiennych PL/SQL

Przy UPDATE można użyć pseudokolumny row jeśli zmieniamycały wiersz

Example

DECLAREd a n e a g e n t a Agenc i%ROWTYPE;

BEGINSELECT ∗ INTO d a n e a g e n t a FROM Agenc i WHERE ID = 1 ;d a n e a g e n t a . I m i e := ’ A r tu r ’ ;UPDATE Agenc i SET row = d a n e a g e n t a ;d a n e a g e n t a . i d := 1 0 0 ;INSERT INTO Agenc i VALUES d a n e a g e n t a ;

END ;/




Tworzenie i podstawowe użycie kursoraKursor a pętla FOR

Kursor

Specjalny twór w pamięci SZRBDPrzechowuje aktualny wiersz z listy

Lista tworzona dowolnie np. zapytaniem

Zachowuje się podobnie jak iterator w C++Rodzaje kursorów

Jawne, deklarowane przez programistęNiejawne przez wywołanie poleceń UPDATE, INSERT, DELETE iSELECT





Deklarowanie kursora

W sekcji DECLAREAby go użyć należy

1 Otworzyć kursor (wykona się zapytanie z nim związane)2 Pobrać kolejny rekord3 Zamknięcie

Kursory mogą mieć argumenty (tak jak funkcje)

Example

DECLARECURSOR c u r a g e n c i I S SELECT ∗ FROM Agenc i ;CURSOR c u r a g e n c i 2 ( i d a NUMBER( 2 ) ) I S

SELECT ∗ FROM Agenc i WHERE i d = i d a ;





Używanie kursora

Otwieranie poleceniem OPENMożna podać ew. argumenty kursora

Zamykanie poleceniem CLOSEPobieranie kolejnego rekordu FETCH

Example

DECLARECURSOR c u r a g e n c i ( i d a g e n t a a g e n c i . i d%TYPE)

I S SELECT ∗ FROM Agenc i WHERE i d < i d a g e n t a ;agent a g e n c i%ROWTYPE;

BEGINOPEN c u r a g e n c i ( 5 ) ;FETCH c u r a g e n c i INTO agent ;dbms output . p u t l i n e ( agent . n a z w i s k o ) ;CLOSE c u r a g e n c i ;

END ;/





Atrybuty kursora

%ISOPEN zwraca, czy kursor jest otwarty

%FOUND zwraca prawdę, jeśli ostatnie pobranie zwróciłocokolwek

%ROWCOUNT zwraca ile wierszy zostało pobranych

Example


I S SELECT ∗ FROM Agenc i WHERE i d < i d a g e n t a ;agent a g e n c i%ROWTYPE;

BEGINOPEN c u r a g e n c i ( 5 ) ;dbms output . p u t l i n e ( ’ Pobrano ’ | | c u r a g e n c i%ROWCOUNT) ;FETCH c u r a g e n c i INTO agent ;dbms output . p u t l i n e ( ’ Pobrano ’ | | c u r a g e n c i%ROWCOUNT) ;

END ;





Użycie kursora w pętli FOR

Pętla przebiega wiersze

Automatyczne pobieranie kolejnego wiersza

Nie trzeba zamykać czy otwierać kursora

Iterator jest „wskaźnikiem” na wiersz

Zamiast kursora można użyć podzapytania

Example


I S SELECT ∗ FROM Agenc i WHERE i d < i d a g e n t a ;BEGIN

FOR agent IN c u r a g e n c i ( 1 5 ) LOOPdbms output . p u t l i n e ( agent . na z w i s k o ) ;

END LOOP;END ;





Użycie kursora do zmiany danych

Użycie z klauzulą WHERE CURRENT OF

Kursor musi być zadeklarowany z opcją FOR UPDATE

Otwartyu kursor blokuje możliwość zmiany innym

Example


I S SELECT ∗ FROM Agenc i WHERE i d < i d a g e n t aFOR UPDATE;

BEGINFOR agent IN c u r a g e n c i ( 1 5 ) LOOP

UPDATE a g e n c i SET Nazwisko = ’AA ’WHERE CURRENT OF c u r a g e n c i ;

DELETE FROM Agenc i WHERE CURRENT OF c u r a g e n c i ;END LOOP;

END ;




Procedury i funkcjeWyzwalacze


Przechowywane na serwerze bazy danych

Skompilowane

Nazwane

Możliwe do użycia w dowolnym (sensownym) miejscu kodu

Napisane w PL/SQLTypy programów

ProceduryFunkcjePakietyWyzwalacze

Po błędnej kompilacji show errors





Procedury

Wykonanie bez zwracania

Mają parametry

Nie mogą być używane w zapytaniach

Blok PL/SQL poprzedzony CREATE PROCEDUREWywołanie

Z innego programu/zapytania jak funkcjęJako argument execute

Są to polecenia DDL - Uwaga na auto commit





Tworzenie procedury

Example

DROP PROCEDURE z m i e n a g e n t a ;CREATE PROCEDURE z m i e n a g e n t a ( i d a g e n t a IN NUMBER) I S

CURSOR c u r a g e n c i ( i d a g e n t a a a g e n c i . i d%TYPE)I S SELECT ∗ FROM Agenc i WHERE i d < i d a g e n t a aFOR UPDATE ;

BEGINFOR agent IN c u r a g e n c i ( i d a g e n t a ) LOOPUPDATE a g e n c i SET Nazwisko = ’AA ’ | | Nazwisko

WHERE CURRENT OF c u r a g e n c i ;END LOOP;

END ;/execute z m i e n a g e n t a ( 1 0 ) ;





Funkcje

Zwracają wartośćMogą być używane w zapytaniachBlok PL/SQL poprzedzony CREATE FUNCTIONDeklaracja zwracanego typu po RETURNWywołanie tylko z zapytań lub innych procedur

Example

CREATE FUNCTION p o l i c z l i t e r y a g e n t o w ( i d a g e n t a IN NUMBER)RETURN NUMBER I S

x NUMBER := 0 ;BEGIN

FOR agent IN (SELECT ∗ FROM Agenc i WHERE i d < i d a g e n t a ) LOOPx := x + l e n g t h ( agent . n a z w i sk o ) ;

END LOOP;RETURN x ;

END ;/s e l e c t p o l i c z l i t e r y a g e n t o w ( 1 0 ) FROM DUAL;





Wyzwalacze

Procedura wywoływana niejawniePrzez zajście zdarzenia w bazie danych

Zmiana danychUsunięcie danychTworzenie lub modyfikacja tabelZalogowanie/wylogowanie użytkownika itp

W wybranym momencie zdarzeniaPrzedPo

Dla różnych zestawów danychJednokrotnie dla poleceniaJednokrotnie dla każdego rekordu w poleceniu





Tworzenie wyzwalacza

CREATE TRIGGER

Wyzwalacz musi mieć nazwę

Kiedy: BEFORE lub AFTER

Wybór zdarzenia UPDATE, INSERT, DELETE

Example

DROP TRIGGER p o u s u n i e c i u ;CREATE TRIGGER p o u s u n i e c i u

AFTER DELETE ON Agenc iBEGIN

dbms output . p u t l i n e ( ’USUNALES AGENTA! ! Powiadamiam CBA! ’ ) ;END ;





Wyzwalacz wierszowy

Wykonywany dla każdego wierszawstawianego/modyfikowanegoMożna odwoływać się do starej i nowej wartości:OLD i :NEW

Dodatkowa klauzula FOR EACH ROW

Example

DROP TRIGGER p o u s u n i e c i u 2 ;CREATE TRIGGER p o u s u n i e c i u 2

AFTER DELETE ON Agenc iFOR EACH ROW

BEGINdbms output . p u t l i n e ( ’ Usuwasz agenta ’ | | :OLD. n a z w i s k o ) ;

END ;





Ćwiczenia

Ćwiczenia1 Wypisz nazwiska tych agentów, którzy mają nazwisko dłuższe

niż nazwisko agenta przed nim na liście agentów posortowanejalfabetycznie (po nazwiskach).


Użytkownicy i schematyZarządzanie bazą danych

Zagadnienia dodatkowe

Część VIII

Administracja bazą danych




Plan prezentacji

17 Użytkownicy i schematySchematyUżytkownicyPrzywileje

18 Zarządzanie bazą danychUruchamianie i zatrzymywanieKopie zapasoweMetadane

19 Zagadnienia dodatkoweWidokiPrzestrzenie tabel




SchematyUżytkownicyPrzywileje

Schematy

Schemat to wydzielony zbiór obiektów bazy danychTabelIndeksówFunkcjiWyzwalaczy...

Posiada nazwę

Jest własnością jednego użytkownika

W Oracle nazwa schematu jest tożsama z nazwą użytkownika

Pełna nazwa obiektu poprzedzana nazwą schematu





Dostęp do obiektów

Pełne odwołanie do obiektuSCHEMAT.Obiekt

Nie trzeba poprzedzać nazwą schematu jeśli to nasz schemat

W różnych schematach mogą istnieć obiekty o tych samychnazwachDostęp do różnych schematów może być różny

Użytkonik może określać zakres dostępu do swojego schematu

Example

SELECT ∗ FROM Agenc i ;SELECT ∗ FROM PIOTREK . Agenc i ;SELECT ∗ FROM PIOTREK . Agenc i NATURAL JOIN Agenc i ;





Użytkownicy

Osoba lub aplikacja uprawniona do dostępu do danych

Użytkownik ma nazwęUwierzytelnianie (weryfikacja tożsamości)

Przez SZBDPrzez system operacyjnyPrzez usługę sieciową

Każdemu użytkonikowi przysługują określone prawaSystem przywilejów





Tworzenie i usuwanie użytkowników

Tworzenie użytkownikaCREATE USER nazwa

Metody identyfikacji (IDENTIFIED)Lokalna (BY hasło)Zewnętrzna (IDENTIFIED EXTERNALLY)Globalna (IDENTIFIED GLOBALLY as nazwa)

Blokowanie użytkonikaACCOUNT LOCK/UNLOCK

Zmuszenie użytkownika do zmiany hasłaPASSWORD EXPIRE

Usuwanie użytkownikaDROP USER nazwa





Użytkownicy - przykład

Example

CREATE USER k r e c i k IDENTIFIED BY c z e c h y ;DROP USER k r e c i k ;CREATE USER k r e c i k IDENTIFIED e x t e r n a l l y ;DROP USER k r e c i k ;CREATE USER k r e c i k IDENTIFIED g l o b a l l y as ’ Ala Janosz ’ ;DROP USER k r e c i k ;CREATE USER k r e c i k IDENTIFIED BY c z e c h y PASSWORD EXPIRE ;ALTER USER k r e c i k IDENTIFIED BY s l o w a c j a ;ALTER USER k r e c i k PASSWORD EXPIRE ;ALTER USER k r e c i k ACCOUNT LOCK;ALTER USER k r e c i k ACCOUNT UNLOCK;DROP USER k r e c i k ;





Przywileje

Przywileje pozwalają na wykonywanie poszczególnychczynności na bazie danychRodzaje przywilejów

Systemowe (do działań na bazie danych)Obiektowe (do działań na obiektach bazy danych)

Przywileje mogą być pogrupowaneGrupa przywilejów to rolaJeden użytkonik może mieć wiele ról





Przyznawanie i usuwanie przywilejów

Przyznanie przywilejówGRANT przywileje TO użytkownik

Odebranie przywilejuREVOKE przywileje FROM użytkownik

Nadanie uprawnień do przekazywania uprawnieńPrzywileje można wymieniać jako listę oddzieloną przecinkami

Dla uprawnień systemowych WITH ADMIN OPTIONDla uprawnień obiektowych WITH GRANT OPTION

Example

GRANT CONNECT, SELECT ANY TABLE TO k r e c i k ;REVOKE SELECT ANY TABLE FROM k r e c i k ;





Przywileje systemowe

CREATE SESSION Pozwala podłączyć się do bazy danych

CREATE Pozwala na tworzenie obiektów we własnymschemacie

TABLE, PROCEDURE, TRIGGER, USER

CREATE ANY Pozwala na tworzenie obiektów w dowolnymschemacie

ALTER Pozwala na zmianę obiektów

codeUSER, SESSION

ALTER ANY Pozwala na zmianę obiektów w dowolnymschemacie

INDEX, ROLE, TABLE, TRIGGER

SELECT ANY TABLE Pozwala na zapytania do tabel w dowolnymschemacie





Przywileje systemowe c.d.

DROP Pozwala na usuwanie obiektówUSER

DROP ANY Pozwala na usuwanie obiektów w dowolnymschemacieTABLE, ROLE, INDEX, ROLE

GRANT ANY ROLE Pozwala nadawać role innymEXECUTE ANY Pozwala wywoływać procedury

PROCEDURESYSDBA Uprawnienia administratora bazy danych

Example

GRANT CREATE SESSION TO k r e c i k ;GRANT CREATE ANY TABLE TO k r e c i k WITH ADMIN OPTION ;REVOKE CREATE ANY TABLE FROM k r e c i k ;





Przywileje obiektowe

Przyznawane dla konkretnych obiektów bazy danychTabel

SELECT, ALTER, INSERT, UPDATE, DELETE

Procedur i funkcjiEXECUTE

GRANT uprawnienie TO użytkownik ON obiekt

Można przyznać wszystkie uprawnienia na raz (ALL)

Do obiektów utworzonych przyznawane jest ALL dla twórcy





Role

Rola to zbiór uprawnieńTworzenie/usuwanie rólCREATE/DROP ROLE nazwa

Dodawanie i odejmowanie uprawnień rolom identyczne jakużytkownikomDodawanie i odbieranie ról użytkownikom identyczne jakuprawnieńZmiana roli wpływa na uprawnienia wszystkich użytkownikówją posiadających

Example

CREATE ROLE p r z e g l a d a c z ;GRANT SELECT ANY TABLE TO p r z e g l a d a c z ;GRANT p r z e g l a d a c z TO k r e c i k WITH ADMIN OPTION ;GRANT SELECT , DELETE ON s t u d e n c i TO k r e c i k ;DROP ROLE p r z e g l a d a c z ;




Uruchamianie i zatrzymywanieKopie zapasoweMetadane

Uruchamianie i zatrzymywanie

Tryby pracy bazy danych ORACLE

CLOSED Baza danych zamknięta poprawnie (niepracuje)

CRASH CLOSED Baza danych zamnięta po awarii (niepracuje)

NOMOUNT SZRBD wystartowany, żadna baza danychnie zamontowana

MOUNTED Zamontowana baza danych, ale plikikontrolne nie są otwarte

OPENED W pełni otwarta baza danych.Zamykanie bazy danych SHUTDOWN/ALTER DATABASE CLOSE

Bez czekania na użytkowników z opcją IMMEDIATE

Podwyższanie poziomu działania STARTUP/ALTER DATABASE





Uruchamianie i zatrzymywanie

Example

SHUTDOWN;STARTUP NOMOUNT;ALTER DATABASE MOUNT;ALTER DATABASE OPEN;ALTER DATABASE CLOSE ;SHUTDOWN IMMEDIATE ;





Kopie zapasowe

Kopie zapasowe umożliwiają odtworzenie stanu bazy danychpo awariiMożliwe sposoby tworzenia

Export - Logiczna ekstrakcja danych do pliku (stworzenieskryptu SQL)Zimny backup - backup po zamknięciu bazy danychCiepły backup - backup w trakcie pracy bazy danych

Możliwe cofnięcie do dowolnego stanu w przeszłości

Baza danych musi pracować w trybie ARCHIVELOG

Example

RECOVER DATABASE UNTIL TIME ’ 2010−10−01:00:00:00 ’ ;





Tryb ARCHIVELOG

Praca w tym trybie wymaga zapisu informacji o każdejtransakcji do dziennikaPozwala na wykonywanie ciepłych backupówSpowalnia pracę bazy danychUmożliwia cofanie i przywracanie zmian (roll back i rollforward)Przełączanie trybu tylko przy zamkniętej bazie danych(MOUNTED)Po nieudanej próbie odtworzenia otwarcie z opcją RESETLOGS

Example

CONNECT s y s AS SYSDBA ;STARTUP MOUNT;ALTER DATABASE ARCHIVELOG ;ARCHIVE LOG START ;SHUTDOWN;STARTUP MOUNT;ALTER DATABASE OPEN RESETLOGS ;Piotr Kaczyński Bazy danych




Zimny backup

Nie wymaga trybu archivelog

Wymaga całkowitego zamknięcia bazy danych

Wymagane skopiowanie odpowiednich plikówKonieczne jest zbackupowanie pliku kontrolnego (bardzoważny!)ALTER DATABASE BACKUP CONTROLFILE TO ’nazwapliku’

Dane przechowywane są w plikach dbfinstalacja oracle/oradata/XE





Ciepły backup

Wymaga trybu ARCHIVELOG

Nie wymaga zamknięcia bazy danych

Można kolejno backupować przestrzenie tabel lub całą bazędanychNależy również backupować

Plik kontrolnyPliki dziennika logu

Example

ALTER TABLESPACE xyz BEGIN BACKUP;−− Kopiowanie p l i k uALTER TABLESPACE xyz END BACKUP;ALTER DATABASE BEGIK BACKUP;−− Kopiowanie w s z y s t k i c h p l i k ó wALTER DATABASE xyz END BACKUP;





Odzyskiwanie bazy danych

Odzyskiwanie z zimnego backupuZatrzymanie bazy danychPrzegranie plików danychEwentualnie przegranie pliku kontrolnego

Odzyskiwanie z ciepłego backupuZatrzymanie bazy danychPrzegranie plików danychPrzegranie plików logówEwentualnie przegranie pliku kontrolnego





Metadane

Oracle zapisuje dane o przechowywanych danych w tabelachNajbardziej interesujące to:ALL OBIEKT - przechowuje informacje o obiektach bazy danych(TABLES, USERS, INDEXES itd)DBA ROLES - role w systemieDBA ROLE PRIVS - uprawnienia rólALL TAB COLUMNS - informacje o kolumnach w tabelach

Opis istniejącej tabeli - polecenie desc

Example

SELECT column name from ALL TAB COLUMNSWHERE t a b l e n a m e = ’ S tudenc i ’ ;

desc ALL TAB COLUMNS ;




WidokiPrzestrzenie tabel

Widoki

Widok to zapisane zapytanie

Tworzone dla uproszczenia pracy z bazą danychDane można modyfikować w widokach

Nie mogą zawierać DISTINCT, GROOUP BY, ORDER BY iagregatów

Tworzenie widokuCREATE VIEW AS definicja

Example

CREATE VIEW n a j l e p s i AS SELECT ∗ FROM S t u d e n c iWHERE Ocena = 5 ;




WidokiPrzestrzenie tabel

Przestrzenie tabel

Przestrzeń tabel to logiczny element bazy danych grupującyKażdy schemat należy dokładnie do jednej przestrzeni tabelKażda przestrzeń tabel skojarzona jest z jednym plikiem dbfRóżne typy przestrzeni (zwykła, tymczasowa i undo)Przy tworzeniu tabeli opcja TABLESPACE;Tworzenie nowej przestrzeni CREATE TABLESPACE

Example

c r ea te t a b l e s p a c e j a k a sl o g g i n gd a t a f i l e ’ d : / t s s t h . dbf ’s i z e 32ma u t o e x t e n d onnext 32m m a x s i z e 2048me x t e n t management l o c a l ;

CREATE USER k r e c i k i d e n t i f i e d by c z e c h y DEFAULT TABLESPACE j a k a s ;CREATE TABLE STUDENCI( I m i e VARCHAR( 3 0 ) , Nazwisko VARCHAR( 3 0 ) ,

Ocena NUMBER( 1 , 1 ) ) TABLESPACE j a k a s ;Piotr Kaczyński Bazy danych

laboratorium (1)

Documents

Transcript of laboratorium (1)