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