Microsoft Word - BD_T_01

BAZY DANYCH

WPROWADZENIE

Deklaracje języka SQL:

•  DDL (Data Definition Language)
•  DML (Data Manipulation Language)
•  TPL (Transaction Process Language)
•  DCL (Data Control Language)

DDL - umoŜliwiających definiowanie obiektów wchodzących w skład bazy.

Przykładami poleceń języka definicji danych są:

POLECENIE

FUNKCJA

CREATE TABLE

Tworzy tabelę

CREATE INDEX

Tworzy indeks

ALTER TABLE

Modyfikuje lub wprowadza kolumny do tabeli

DROP TABLE

Usuwa tabelę z bazy

DROP INDEX

Usuwa indeks

GRANT

Przydziela uŜytkownikowi uprawnienia

DML słuŜy do odczytywania oraz modyfikowania danych zawartych w bazie.

POLECENIE

DDL

ELEMENTY

SŁOWNIKA DANYCH

POLECENIA DML

WYKONYWANIE

ZAPYTAŃ

WPROWADZANIE

ZMIAN W TABELACH

BAZY DANYCH

WPROWADZENIE

Do najwaŜniejszych poleceń DML-a naleŜą:

POLECENIE

FUNKCJA

SELECT

Odczytuje dane z tabeli lub perspektywy.
Jest to najczęściej wykorzystywane polecenie SQL.

INSERT

Wstawia wiersz do tabeli

DELETE

Usuwa wiersz z tabeli

UPDATE

Zmienia zawartość kolumn (pól) w tabeli

TPL  -  utrwalania  (zatwierdzania)  w  bazie  danych  zmian  wprowadzonych  za
pośrednictwem  poleceń  DML  oraz  do  odtwarzania  stanu  bazy  w  sytuacji  wystąpienia
awarii lub błędu.

JeŜeli  zakończenie  pewnej  transakcji  wymagać  będzie  wprowadzenia  zmian  w  kilku
oddzielnych  tabelach,  a  wprowadzenie  zmian  do  którejkolwiek  z  nich  okaŜe  się
niemoŜliwe, wówczas cała transakcja musi zostać odwołana.

POLECENIA TPL

COMMIT

ROLLBACK

BAZY DANYCH

WPROWADZENIE

TYPY DANYCH

Baza Oracle obsługuje szereg wbudowanych typów danych. PoniŜsza tabela prezentuje
typy danych obowiązujące w systemie Oracle od wersji 8.

Typ danych

Opis

BFILE

Wskaźnik do zewnętrznego pliku binarnego. Plik do 4 gigabajtów.

BLOB

Obiekt binarny nie większy niŜ 4 gigabajty.

CHAR(rozmiar)

Stałe pole znakowe nie większe niŜ 2000 bajtów.

CLOB

Obiekt binarny złoŜony z jednobajtowych znaków. Do 4 GB.

DATE

Data z przedziału 1/1/4712 p.n.e. – 31/12/4712 n.e.

LONG

Zmienne pole znakowe nie większe niŜ 2 gigabajty.

LONG RAW

Zmienny typ znakowy nie większy niŜ 2 gigabajty.

NCHAR(rozmiar)

Dane znakowe o stałym rozmiarze do 4000 bajtów.

NCLOB

Obiekt znakowy złoŜony z jednobajtowych znaków. Stały rozmiar
nieprzekraczający 4 GB.

NVARCHAR2(rozmiar) Pole znakowe o zmiennej długości nieprzekraczającej 4000 B.
NUMBER(p,s)

Liczba o precyzji p (od 1 do 38) i skali s (-84 do +127).

RAW(rozmiar)

Dane binarne nie dłuŜsze niŜ 2000 bajtów.

ROWID

Łańcuch heksadecymalny stanowiący unikatowy adres wiersza w
tabeli.

VARCHAR2(rozmiar)

Pole znakowe o zmiennej długości nieprzekraczającej 4000 B.

Oprócz tych typów moŜna równieŜ tworzyć własne, tzw. typy uŜytkownika.

Dane liczbowe

•  liczby dodatnie,
•  ujemne,
•  zmiennoprzecinkowe (maksymalnie 38 cyfr).

Definicja typu liczbowego to:

Number(p,s)

Gdzie p jest precyzją (do 38 cyfr), a s – skalą, czyli ilością cyfr po przecinku.
JeŜeli pominiemy parametr s, skala będzie równa 0. W ten sposób otrzymamy typ
całkowitoliczbowy.

BAZY DANYCH

WPROWADZENIE

Dane znakowe

• W kolumnach posiadających stały rozmiar wykorzystuje się typ char. Maksymalna

długość - 255 znaków.

• Do deklarowania pól o zmiennej długości słuŜy typ varchar2, maksymalna długość

-  2000  znaków  (np.  pole  memo  –  notatnik).  Długość  danych  tego  typu  moŜe  się
zmieniać,  jednak  zawsze  naleŜy  określić  maksymalną  dopuszczalna  długość  dla
danego pola.

• Typ long moŜe przechowywać do 2 gigabajtów danych, lecz posiada dodatkowe

ograniczenia. Tylko jedna kolumna w tabeli moŜe być typu long. Kolumny tego
typu nie mogą być indeksowane, nie mogą być takŜe przekazywane jako argument
funkcji czy procedury. Z kolumny typu long nie moŜna równieŜ korzystać w
podwyraŜeniach WHERE, ORDER BY oraz GROUP BY polecenia SELECT.

Daty

W systemie Oracle przechowywane w formacie składającym się z wieku, roku, miesiąca,
dnia, minuty i sekundy. Wszystkie te elementy są częściami składowymi typu date.
Bez podania godziny, system przyjmie domyślną godzinę (12 w południe). Wprowadzenie
samej godziny - system uzupełni ją o domyślną datę (pierwszy dzień bieŜącego miesiąca).

UŜytkownik ma do dyspozycji stałą sysdate, która zwiera aktualną datę systemową.

Dane binarne

W  formatach  raw  i  long  raw.  Format  raw  pozwala  na  przechowywanie  obiektów  nie
większych  niŜ  2000  bajtów  a  long  raw  -  aŜ  do  2  gigabajtów.  Ten  typ  danych  jest
stosowany do zapisywania w bazie plików dźwiękowych oraz obrazów.

BAZY DANYCH

WPROWADZENIE

OPERATORY

Rodzaj

Nazwa

Wartość

Operatory arytmetyczne

MnoŜenie

Dzielenie

Odejmowanie

Działania na wartościach
liczbowych

Dodawanie

Operatory znakowe
Działania na łańcuchach

Konkatenacja

Operatory porównawcze

Równe

RóŜne od

Większe od

Mniejsze od

Większe lub równe

Mniejsze lub równe

Równe NULL

IS NULL

W przedziale od do

BETWEEN … AND …

Takie jak podane

RóŜne od NULL

IS NOT NULL

Poza przedziałem
od do

NOT BETWEEN…AND …

Inne niŜ podane

NOT IN

SłuŜą do znajdowania
podobieństw i róŜnic

Inne niŜ podane

NOT LIKE

Operatory logiczne

NOT

Logiczne „nie”

AND

Logiczne „i”

Pozwalają na wiązanie
operacji porównawczych

Logiczne „lub”

Operacje mnogościowe

UNION

Zwraca wiersze zwrócone
przez dowolne zapytanie

INTERSECT

Zwraca wiersze zwrócone
przez obydwa zapytania

SłuŜą do realizowania
operacji na zbiorach i
łączenia ze sobą zapytań

MINUS

Zwraca wiersze zwrócone
przez pierwsze zapytanie z
wyjątkiem tych, które
zostały zwrócone równieŜ
przez drugie zapytanie

BAZY DANYCH

WPROWADZENIE

Hierarchia operatorów:

a) Operatory unarne (działające na pojedynczych operandach)
b) Dzielenie i mnoŜenie
c) Dodawanie i odejmowanie
d) Operatory porównawcze
e) Operator logiczny NOT
f) Operator logiczny AND
g) Operator logiczny OR

Aby wymusić inną kolejność naleŜy stosować nawiasy.

Polecenie SELECT

SłuŜy do odczytywania danych z tabeli lub perspektywy. Jest to najczęściej
wykorzystywane polecenie języka SQL.

Składnia:

SELECT [DISTINCT|ALL] {*
| { [schemat.]{tabela | perspektywa}.*
| wyraŜenie [alias_k] }
[,| { [schemat.]{tabela | perspektywa}.*
| wyraŜenie [alias_k] } ] … }
FROM [schemat.]{tabela | perspektywa} [alias_t]
[, [schemat.]{tabela | perspektywa} [alias_t] …
[WHERE warunek]
[GROUP BY wyraŜenie [, wyraŜenie] … [HAVING warunek] ]
[{UNION | UNION ALL | MINUS} SELECT polecenie ]
[ORDER BY {wyraŜenie | pozycja} [ASC | DESC]
[, { wyraŜenie | pozycja} [ASC | DESC] … ]

Argumenty:

•  DISTINCT – zwraca tylko jedną kopię kaŜdego zbioru (eliminuje duplikaty)
•  ALL – zwraca wszystkie wybrane wiersze (ustawienie domyślne)
•  zwraca wszystkie kolumny wyszczególnione w wyraŜeniu FROM

BAZY DANYCH

WPROWADZENIE

•  tabele.* - zwraca wszystkie kolumny wskazanej tabeli
•  perspektywa.* - zwraca wszystkie kolumny wskazanej perspektywy
•  wyraŜenie  -  zazwyczaj  jest  to  nazwa  kolumny  lub  wyraŜenia  utworzonego  z

wykorzystaniem nazwy tabeli lub perspektywy

•  alias_k – tworzy alias kolumny
•  schemat – określa schemat zawierający podaną tabelę
•  alias_t – tworzy alias, perspektywę lub migawkę tabeli
•  WHERE – filtruje wiersze zwracane przez zapytanie
•  GROUP  BY  –  grupuje  odczytane  wiersze  na  podstawie  wartości  wyraŜenia

wyraŜenie

w kaŜdym wierszu i zwraca informacje o poszczególnych grupach.

• HAVING – filtruje odczytane wiersze, zwracając tylko te, które spełniają podane

wyraŜenie logiczne.

•  UNION – zwraca wszystkie unikatowe wiersze ze wszystkich zapytań.
•  UNION ALL – zwraca wszystkie wiersze z zapytań, łącznie z duplikatami.
•  MINUS  –  zwraca  wszystkie  wiersze  zwrócone  przez  pierwsze  zapytanie  oprócz

tych zwróconych przez drugie zapytanie.

• ORDER BY – sortuje według klucza wiersze zwrócone przez SELECT. Domyślną

kolejnością jest ASC (porządek rosnący). DESC sortuje w porządku malejącym.

W klauzuli WHERE moŜna stosować operator LIKE - dopasowywanie się do wzorców
(znak % dopasowuje się do dowolnego tekstu, znak _ - do dowolnego pojedynczego
znaku).

FUNKCJE

Funkcje języka SQL to zbiór poleceń, których celem jest zwrócenie pewnego rezultatu.

Składnia:

nazwa_funkcji(arg1, arg2, …, agrn)

arg1, arg2 itd. są argumentami funkcji.

Funkcje SQL-a dzielą się na dwa rodzaje:

BAZY DANYCH

WPROWADZENIE

Funkcje jednowierszowe – zwracają osobny rezultat dla kaŜdego wiersza tabeli lub
perspektywy.

a) Funkcje znakowe:

• LOWER(wartość) – funkcja słuŜy do konwertowania łańcuchów tekstowych do

małych liter.

• UPPER(wartość) – funkcja słuŜy do konwertowania łańcuchów tekstowych do

wielkich liter.

• INITCAP(wartość) – zmienia pierwsze litery w słowie na wielkie.
• LPAD – (wartość,n[,’ciąg’]) – uzupełnia kolumny z lewej strony podanym

podciągiem aŜ do długości n znaków.

• RPAD – (wartość,n[,’ciąg’]) – uzupełnia kolumny z prawej strony podanym

podciągiem aŜ do długości n znaków.

• SUBSTR(wartość,n [,m]) – z podanego łańcucha znaków wycina m znaków

począwszy od pozycji n-tej.

• INSTR(wartość,’ciąg’ [,m,n]) – wskazuje miejsce pierwszego (n-tego) wystąpienia

ciągu w łańcuchu znaków począwszy od pozycji m-tej.

• LTRIM(wartość [,’znaki’]), RTRIM(wartość [,’znaki’]) – usuwa z lewej (prawej)

strony podane znaki (spacje).

• LENGTH(wartość) – zwraca długość łańcucha znaków.
• TRANSLATE(źródło,z,na) – kaŜde wystąpienie w źródle znaku z ciągu z zostanie

zastąpione odpowiadającym mu znakiem z ciągu na.

• REPLACE(źródło,wzór,nowy) – kaŜde wystąpienie w źródle ciągu wzorzec

zostanie zastąpione przez ciąg nowy.

b) Funkcje liczbowe

• ROUND(wartość,n) – zaokrągla wartość do n-tego dziesiętnego miejsca po

przecinku.

• TRUNC(wartość,n) – obcina wartość do n-tego dziesiętnego miejsca po przecinku.
• CEIL(wartość), FLOOR(wartość) – najmniejsza (największa) liczba całkowita

większa lub równa (mniejsza lub równa) podanej wartości.

•  POWER(wartość,n) –podnosi wartość do podanej potęgi.
•  SQRT(wartość) – oblicza pierwiastek kwadratowy z podanej wartości.
•  ABS(wartość) – oblicza wartość bezwzględną wyraŜenia.
•  MOD(wartość1, wartość2) – zwraca resztę z dzielenia.

BAZY DANYCH

WPROWADZENIE

c) Funkcje operujące na datach

• MONTHS_BETWEEN(data1,data2) – Zwraca liczbę miesięcy, jakie upłynęły

między datami.

• ADD_MONTHS(data,n) – Zwraca datę plus n miesięcy kalendarzowych.
• NEXT_DAY(data,dzień) – Zwraca następną datę po podanej, przypadającą na

podany dzień.

• LAST_DAY(data) – Zwraca datę ostatniego dnia w miesiącu podanej daty.

d) Funkcje konwersji

• TO_CHAR(liczba|data [,’format’]) – konwertuje liczbę lub datę na tekst o

odpowiednim formacie.

• TO_NUMBER(‘tekst’) – zamienia tekst na liczbę
• TO_DATE(‘tekst’,’format’) – zamienia tekst na datę w odpowiednim formacie.

Sekundy

Minuty

HH24

Godziny (24h)

Godziny

AM PM

Wskaźnik pory dnia

DAY

Nazwa dnia

Dzień

MONTH

Nazwa miesiąca

Miesiąc

BC AD

Wskaźnik ery

YYYY

Rok

SCC

Stulecie

Np. Porównanie, czy data zatrudnienia jest większa od zadanej:

DATA_ZAT > TO_DATE(‘05/08/1999’,’DD/MM/YYYY’)

Np. Zapytanie wyświetlające tekst z datą aktualną zawierającą wiek:

SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY/SCC') FROM DUAL;

BAZY DANYCH

WPROWADZENIE

DUAL jest tabelą słownika danych. Znajduje się w schemacie uŜytkownika SYS, ale
dostęp do niej mają wszyscy uŜytkownicy bazy. Tabela DUAL jest uŜyteczna przy
obliczaniu róŜnych wyraŜeń w poleceniu SELECT.

Funkcje grupujące – zwracają pojedynczy rezultat dla całych grup wierszy.

•  AVG([DISTINCT|ALL] n) – zwraca średnią arytmetyczną
•  COUNT({* | [DISTINCT|ALL] wyraŜenie) – zwraca ilość wierszy w zapytaniu
•  MAX([DISTINCT|ALL]  wyraŜenie)  –  zwraca  maksymalną  wartość  podanego

wyraŜenia

• MIN([DISTINCT|ALL] wyraŜenie) - zwraca minimalną wartość podanego

wyraŜenia

• SUM([DISTINCT|ALL] wyraŜenie) – zwraca sumę wartości danego atrybutu

BAZY DANYCH

WPROWADZENIE

PRZYKŁADY

RozwaŜmy prosty przykład bazy danych na temat ksiąŜek. Baza składa się z dwóch tabel.
Dla uproszczenia załoŜono, Ŝe jedna ksiąŜka posiada tylko jednego autora.

KSIĄśKI

ID_POZ

ID_AUT TYTUL

ROK_WYD WYDAWNICTWO

Ogniem i Mieczem

1980

POW

Wprowadzenie do baz danych

2001

PWN

Pan Tadeusz

1982

POW

Pan Wołodyjowski

1980

Nasza Księgarnia

Oracle9i. Podręcznik administratora baz danych

2003

HELION

AUTORZY

ID_AUT

NAZWISKO

IMIE

NARODOWOSC

Sienkiewicz

Henryk

polska

Date

C. J.

USA

Mickiewicz

Adam

polska

Looney

Kevin

USA

a) Wyświetlić wszystkie ksiąŜki, których autor posiada AUT_ID = 1.

SELECT * FROM KSIAZKI WHERE ID_AUT = 1;

b) Wyświetlić ksiąŜki wydane od roku 2000 posortuj dane według identyfikatora autora w
kolejności odwrotnej.

SELECT * FROM KSIAZKI WHERE ROK_WYD >= 2000 ORDER BY ID_AUT
DESC;

c) Policzyć wszystkie ksiąŜki wydane przez wydawnictwo POW. Kolumnę nazwać nazwę
liczba_ksiąŜek (do nadawania nazw kolumną wykorzystuje się operator AS).

SELECT

COUNT(*)

LICZBA_KSIĄśEK

FROM

KSIAZKI

WHERE

WYDAWNICTWO = ‘POW’;

d) Wyświetlić identyfikatory wszystkich ksiąŜek, które w tytule mają wyraz „BAZ”. Nie
uwzględniaj wielkości liter.

SELECT ID_POZ FROM KSIAZKI WHERE UPPER(TYTUL) LIKE ‘%BAZ%’;

e) Wyświetlić tytuły ksiąŜek wydanych w ciągu ostatnich 5 lat.

SELECT TYTUL FROM KSIAZKI WHERE ROK_WYD > TO_CHAR(SYSDATE,
'YYYY') - 5;

BAZY DANYCH

WPROWADZENIE

POŁĄCZENIA

Operacja  połączenia  polega  na  łączeniu  dwóch  lub  więcej  relacji  z  wykorzystaniem
określonych warunków połączenia.

Warunek  połączenia

to warunek porównujący ze sobą wartości atrybutów z dwóch

róŜnych relacji.

W przypadku, gdy w łączonych relacjach występują atrybuty o takich samych nazwach, to
w zapytaniu muszą być poprzedzone nazwą relacji celem uniknięcia dwuznaczności.

W  klauzuli  FROM  moŜliwe  jest  uŜywanie  aliasów,  czyli  alternatywnych  nazw  relacji
zamiast nazw tabel.

W przypadku łączenia N relacji konieczne jest wykorzystanie N-1 warunków złączenia.

a)  Wyświetl  tytuł,  rok  wydania  i  wydawnictwo  oraz  imię  i  nazwisko  autora  wszystkich
ksiąŜek.

SELECT  K.TYTUL,  K.ROK_WYD,  K.WYDAWNICTWO,  A.NAZWISKO,  A.IMIE
FROM KSIAZKI K, AUTORZY A WHERE K.ID_AUT = A.ID_AUT;

b) Wyświetl tytuły wszystkich ksiąŜek napisanych przez autorów narodowości polskiej.

SELECT  K.TYTUL  FROM  KSIAZKI  K,  AUTORZY  A  WHERE  K.  ID_AUT  =  A.
ID_AUT AND A.NARODOWOSC = ‘polska’;

c)  Wyświetl  nazwiska  i  imiona  autorów  oraz  liczbę  ksiąŜek  napisanych  przez  tych
autorów.  Posortuj  wynik  w  kolejności  od  największej  do  najmniejszej  liczby  wydanych
ksiąŜek.

SELECT  A.NAZWISKO,  A.IMIE,  COUNT(A.NAZWISKO)  AS  LICZBA_KSIAZEK
FROM  AUTORZY  A,  KSIAZKI  K  WHERE  A.ID_AUT  =  K.ID_AUT  GROUP  BY
A.NAZWISKO, A.IMIE ORDER BY LICZBA_KSIAZEK DESC;

BAZY DANYCH

WPROWADZENIE

PODZAPYTANIA

Zapytania SQL moŜna zagnieŜdŜać.
Wynik jednego zapytania moŜe zostać uŜyty np. jako warunek selekcji w innym
zapytaniu.

Podzapytania moŜna podzielić na dwa rodzaje:

• Podzapytanie proste, które jest wykonywane przed wykonaniem zapytania

głównego.

• Podzapytanie skorelowane wykonywane dla kaŜdej krotki zapytanie głównego.

JeŜeli wiemy, Ŝe wynikiem podzapytania będzie pojedyncza wartość, to wartość tą moŜna
uŜyć bezpośrednio w warunku klauzuli WHERE.

a) Wyświetlić tytuł ksiąŜki , która posiada największy numer ID_POZ.

SELECT  TYTUL  FROM  KSIAZKI  WHERE  ID_POZ  =  (  SELECT  MAX(ID_POZ)
FROM KSIAZKI );

Podzapytania mogą być zagnieŜdŜane dowolną ilość razy.

b)  Wyświetl  wszystkie  informacje  na  temat  ksiąŜek,  które  zostały  wydane  przez  to
wydawnictwo,  przez  które  została  wydana  ksiąŜka  posiadająca  najmniejszy  numer
POZ_ID.

SELECT  *  FROM  KSIAZKI  WHERE  WYDAWNICTWO    =  (  SELECT
WYDAWNICTWO  FROM  KSIAZKI  WHERE  ID_POZ  =  (  SELECT  MIN(ID_POZ)
FROM KSIAZKI  )  );

Jednak  w  sytuacji,  gdy  w  wyniku  zapytania  wewnętrznego  moŜemy  otrzymać  kilka
wartości, naleŜy wykorzystać operator IN.

c) Wyświetlić wszystkie ksiąŜki wydane przez wydawnictwa, które wydały swoje ksiąŜki
w tym samym roku, w którym wydano najstarszą ksiąŜkę.

Następujące zapytanie zwróci błąd:

SELECT  *  FROM  KSIAZKI  WHERE  WYDAWNICTWO    =  (  SELECT
WYDAWNICTWO

FROM

KSIAZKI

WHERE

ROK_WYD

(

SELECT

MIN(ROK_WYD) FROM KSIĄśKI ) );

BAZY DANYCH

WPROWADZENIE

ORA-01427: jednowierszowe podzapytanie zwraca więcej niŜ jeden wiersz;

Poprawne zapytanie:

SELECT * FROM KSIAZKI WHERE WYDAWNICTWO IN ( SELECT
WYDAWNICTWO

FROM

KSIAZKI

WHERE

ROK_WYD

(

SELECT

MIN(ROK_WYD) FROM KSIAZKI  )  );

d)  Przykład  zapytania  skorelowanego.  Z  tabeli  pracownicy  wyświetlić  nazwiska
pracowników otrzymujących premię niŜszą od średniej dla filii, w której pracują.

SELECT NAZWISKA ID_FIL FROM PRACOWNICY WHERE PREMIA < ( SELECT
AVG(PREMIA) FROM PRACOWNICY WHERE ID_FIL = PRACOWNICY.ID_FIL  );