BAZY DANYCH

WPROWADZENIE

1

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

2

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

3

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

4

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

5

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

IN

Takie jak podane

LIKE

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

OR

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

6

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

7

• 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

8

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

9

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.

SS

Sekundy

MI

Minuty

HH24

Godziny (24h)

HH

Godziny

AM PM

Wskaźnik pory dnia

DAY

Nazwa dnia

DD

Dzień

MONTH

Nazwa miesiąca

MM

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

10

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

11

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

10

1

Ogniem i Mieczem

1980

POW

20

2

Wprowadzenie do baz danych

2001

PWN

30

3

Pan Tadeusz

1982

POW

40

1

Pan Wołodyjowski

1980

Nasza Księgarnia

50

4

Oracle9i. Podręcznik administratora baz danych

2003

HELION

AUTORZY

ID_AUT

NAZWISKO

IMIE

NARODOWOSC

1

Sienkiewicz

Henryk

polska

2

Date

C. J.

USA

3

Mickiewicz

Adam

polska

4

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(*)

AS

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

12

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

13

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

14

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