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.
POLECENIE
ELEMENTY
DDL
SŁOWNIKA DANYCH
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.
POLECENIA DML
WYKONYWANIE
WPROWADZANIE
ZAPYTAŃ
ZMIAN W TABELACH
1
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.
POLECENIA TPL
COMMIT
ROLLBACK
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.
2
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.
3
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.
4
WPROWADZENIE
OPERATORY
Rodzaj
Nazwa
Wartość
Operatory arytmetyczne
Działania na wartościach Mnożenie
*
liczbowych
Dzielenie
/
Odejmowanie
-
Dodawanie
+
Operatory znakowe
Działania na łańcuchach
Konkatenacja
||
Operatory porównawcze
Służą do znajdowania
Równe
=
podobieństw i różnic
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
NOT BETWEEN…AND …
od do
Inne niż podane
NOT IN
Inne niż podane
NOT LIKE
Operatory logiczne
Pozwalają na wiązanie
NOT
Logiczne „nie”
operacji porównawczych AND
Logiczne „i”
OR
Logiczne „lub”
Operacje mnogościowe
Służą do realizowania
UNION
Zwraca wiersze zwrócone
operacji na zbiorach i
przez dowolne zapytanie
łączenia ze sobą zapytań INTERSECT
Zwraca wiersze zwrócone
przez obydwa zapytania
MINUS
Zwraca wiersze zwrócone
przez pierwsze zapytanie z
wyjątkiem tych, które
zostały zwrócone również
przez drugie zapytanie
5
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
6
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, …, agr n)
arg1, arg2 itd. są argumentami funkcji.
Funkcje SQL-a dzielą się na dwa rodzaje:
7
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.
8
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.
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;
9
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 10
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
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;
11
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;
12
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 ) );
13
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 );
14