SQL
J
ZYK ZAPYTAC

dr inż. Piotr Muryjas
Instytut Informatyki
Politechnika Lubelska
BAZA DANYCH ECDL  PRZYKA
AD ROZWI
ZANIA
STUDENCI
Id_student VARCHAR2 (7)
Nazwisko VARCHAR2 (25)
Imie VARCHAR2 (15)
EGZAMINATORZY
EGZAMINY Data_ur DATE
Id_egzaminator VARCHAR2 (4)
Nr_egz NUMBER (7) Miejsce VARCHAR2 (15)
Nazwisko VARCHAR2 (25)
Id_student VARCHAR2 (7) PESEL VARCHAR2 (11)
Imie VARCHAR2 (15)
przeprowadza
zdaje
Id_przedmiot NUMBER (3) Kod_poczta VARCHAR2 (5)
Kod_poczta VARCHAR2 (5)
Id_egzaminator VARCHAR2 (4) Miasto VARCHAR2 (15)
Miasto VARCHAR2 (15)
Data_egz DATE Ulica VARCHAR2 (30)
Ulica VARCHAR2 (30)
Id_osrodek NUMBER (5) Numer VARCHAR2 (8)
Numer VARCHAR2 (8)
Zdal VARCHAR2(1) Tel VARCHAR2 (12)
Tel VARCHAR2 (12)
Fax VARCHAR2 (12)
Fax VARCHAR2 (12)
E_mail VARCHAR2 (30)
E_mail VARCHAR2 (30)
Nr_ECDL VARCHAR2 (9)
Data_ECDL DATE
jest miejscem
jest tematem
OSRODKI
Id_osrodek NUMBER (5)
PRZEDMIOTY
Nazwa_o VARCHAR2 (30)
Id_przedmiot NUMBER (3)
Kod_poczta VARCHAR2 (5)
Nazwa_p VARCHAR2 (40)
Miasto VARCHAR2 (15)
Opis VARCHAR2 (200)
Ulica VARCHAR2 (30)
Numer VARCHAR2 (8)
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 2
INSTRUKCJA SELECT
SELECT kol1 [alias1], kol2 [alias2],...,koln, wyrażenie [alias] FROM tabela
gdzie: kol1, kol2,...,koln - nazwy kolumn tabeli, oddzielone przecinkiem; lista kolumn może być
zastąpiona symbolem "*", jeśli wszystkie kolumny na liście
wyrażenie  dowolne wyrażenie oparte na kolumnach podanej tabeli
tabela - nazwa tabeli
Przykłady:
1/ SELECT Id_egzaminator Identyfikator, Nazwisko, Imie FROM egzaminatorzy;
2/ SELECT Id_osrodek, Nazwa_o nazwa FROM osrodki;
3/ SELECT * FROM przedmioty;
ELIMINOWANIE WIERSZY O POWTARZAJ
CEJ SI
WARTOÅšCI POLA
SELECT DISTINCT kol1, kol2,...,koln FROM tabela
Klauzula DISTINCT musi pojawić się jako pierwsze słowo kluczowe po słowie SELECT.
Przykłady:
1/ SELECT DISTINCT Id_przedmiot, Id_student FROM egzaminy;
2/ SELECT DISTINCT Id_osrodek FROM egzaminy;
SORTOWANIE WYÅšWIETLANYCH INFORMACJI
SELECT kol1, kol2,...,koln FROM tabela ORDER BY koli
gdzie: koli - nazwa kolumny, według której odbywa się sortowanie
Porządek sortowania - rosnący (ASCENDING) - domyślny, malejący (DESCENDING).
ORDER BY musi być ostatnią klauzulą w instrukcji SELECT.
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 3
SELECT kol1, kol2,...,koln FROM tabela
ORDER BY koli DESC
Przykłady:
1/ SELECT Id_osrodek, Nazwa_o FROM osrodki ORDER BY Miasto;
2/ SELECT Id_student, Nazwisko, Imie FROM studenci ORDER BY Nazwisko;
Sortowanie informacji według kilku kolumn, zgodnie z ogólną postacią polecenia:
SELECT kol1, kol2,...,koln FROM tabela
ORDER BY koli, kolj
lub
SELECT kol1, kol2,...,koln FROM tabela
ORDER BY koli DESC, kolj
Przykłady:
1/ SELECT Id_egzaminator, Nazwisko, Imie FROM egzaminatorzy ORDER BY Nazwisko, Imie;
2/ SELECT Id_student, Id_przedmiot, Data_egz FROM egzaminy ORDER BY Id_student, Data_egz
DESC;
DEFINIOWANIE WARUNKÓW WYSZUKIWANIA
SELECT kol1, kol2,...,koln FROM tabela WHERE warunek
gdzie: warunek - warunek lub warunki, które muszą spełniać wybrane i wyświetlane rekordy
tabeli
Operatory SQL:
" (NOT) BETWEEN ... AND ...
" (NOT) IN (lista wartości)
" (NOT) LIKE
" IS (NOT) NULL
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 4
SELECT kol1, kol2,...,koln FROM tabela
WHERE koli BETWEEN wyrażenie_1 AND wyrażenie_2
SELECT kol1, kol2,...,koln FROM tabela
WHERE koli IN (wartość_1, wartość_2,...,wartość_n | polecenie SELECT)
SELECT kol1, kol2,...,koln FROM tabela
WHERE koli NOT LIKE "A%" AND kolj LIKE "k_"
SELECT kol1, kol2,...,koln FROM tabela
WHERE koli IS NULL
Przykłady:
1/ SELECT Id_student, Id_przedmiot, Data_egz FROM egzaminy WHERE Data_egz BETWEEN
'20-04-05' AND '20-05-05' ORDER BY Id_student, Data_egz DESC;
2/ SELECT Id_przedmiot, Id_egzaminator, Data_egz FROM egzaminy WHERE Id_student IN
('0000050', '0000055');
3/ SELECT * FROM egzaminatorzy WHERE Id_egzaminator IN (SELECT DISTINCT
Id_egzaminator FROM egzaminy);
4/ SELECT * FROM przedmioty WHERE Id_przedmiot NOT IN (SELECT DISTINCT
Id_przedmiot FROM egzaminy);
5/ SELECT Nazwisko, Imie FROM studenci WHERE Nazwisko LIKE 'M%';
6/ SELECT * FROM studenci WHERE E_mail LIKE 'p_' ;
7/ SELECT Id_student, Nazwisko, Imie FROM studenci WHERE Nr_ECDL IS NULL;
8/ SELECT * FROM studenci WHERE Id_student = Nr_ECDL;
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 5
TWORZENIE KRYTERIÓW ZA
OŻONYCH
Wymaga stosowania operatorów AND i OR.
SELECT kol1, kol2,...,koln FROM tabela
WHERE warunek1 AND | OR warunek2 [AND | OR warunek2 ...]
Przykłady:
1/ SELECT Id_student, Id_przedmiot, Data_egz FROM egzaminy WHERE Id_osrodek = 5 AND
Data_egz BETWEEN '20-04-05' AND '20-05-05';
2/ SELECT Id_student FROM egzaminy WHERE Zdal = 'N' AND Id_przedmiot = 7;
3/ SELECT Id_student, Id_przedmiot FROM egzaminy WHERE Data_egz > '20-04-05' AND
(Id_egzaminator = '0036' OR Id_egzaminator = '0040');
4/ SELECT * FROM egzaminy WHERE Id_osrodek = 3 AND Data_egz LIKE '%04-05';
HIERARCHIA OPERATORÓW
OPERATOR OPERACJA
=, !=, <, >, <=, >=, IS NULL, LIKE, porównanie
BETWEEN, IN
NOT negacja
AND koniunkcja
OR alternatywa
" wszystkie operatory porównania i operatory SQL mają jednakowy, najwyższy
priorytet
" operator NOT
" AND
" OR
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 6
Przykłady:
1/ SELECT Id_student, Id_przedmiot FROM egzaminy WHERE Data_egz > '20-04-05' AND
Id_egzaminator = '0036' OR Id_egzaminator = '0040';
GRUPOWANIE DANYCH
Wykonuje się w celu uzyskania informacji zbiorczych dla zbioru rekordów.
SELECT wyrażenie_grup FROM tabela [WHERE warunek]
SELECT kol1, kol2,...,koln, wyrażenie_grup FROM tabela GROUP BY koli, kolj
gdzie: koli - nazwa kolumny według której przebiega grupowanie wierszy,
wyrażenie_grup - nazwa funkcji grupowej.
Rodzaje wyrażeń grupujących - funkcji grupowej:
" AVG([ DISTINCT ] kol | wyrażenie)
" COUNT( [ DISTINCT ] kol | wyrażenie)
" MAX( [ DISTINCT ] kol | wyrażenie)
" MIN( [ DISTINCT ] kol | wyrażenie)
" SUM([ DISTINCT ] kol | wyrażenie)
Przykłady:
1/ SELECT COUNT(*) FROM egzaminy WHERE Id_egzaminator = '0036';
2/ SELECT MAX(Data_egz) FROM egzaminy WHERE Id_osrodek = 5;
3/ SELECT Id_osrodek, COUNT(*) FROM egzaminy GROUP BY Id_osrodek;
4/ SELECT Id_przedmiot, COUNT(*) FROM egzaminy GROUP BY Id_przedmiot;
5/ SELECT MIN(Data_egz), Id_student FROM egzaminy GROUP BY Id_student;
6/ SELECT Id_egzaminator, Id_osrodek, COUNT(*) FROM egzaminy GROUP BY
Id_egzaminator, Id_osrodek ORDER BY Id_osrodek;
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 7
WYBÓR GRUP DANYCH WEDA
UG ZADANEGO KRYTERIUM
SELECT kol1, kol2,...,koln, wyrażenie_grup FROM tabela
GROUP BY koli, kolj
HAVING warunek_grup
gdzie: warunek_grup - warunek, jaki muszą spełniać poszczególne zdefiniowane grupy
Przykłady:
1/ SELECT COUNT(*) FROM egzaminy GROUP BY Id_osrodek HAVING Id_osrodek = 5;
2/ SELECT Id_egzaminator, COUNT(*) FROM egzaminy GROUP BY Id_egzaminator HAVING
COUNT(*) > 10;
3/ SELECT Id_przedmiot, COUNT(*) FROM egzaminy GROUP BY Id_przedmiot HAVING
COUNT(*) > 8;
4/ SELECT Id_student, COUNT(*) FROM egzaminy WHERE Id_osrodek = 1 GROUP BY
Id_student HAVING COUNT(*) > 2 ORDER BY Id_student;
WYSZUKIWANIE DANYCH W WIELU TABELACH
Typy operacji złączenia tabel:
" równozłączenie (equi-join) - oparte na operatorze równości
" non equi-join - oparte jest na operatorze relacji różnym od równości np. <, >, <=, >=
lub operatora SQL BETWEEN
SELECT tab1.kol1,...,tab1.koln, tab2.kol1,...,tab2.koln
FROM tabela1 tab1, tabela2 tab2
WHERE tab1.koli = tab2.kolj
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 8
Przykłady:
1/ SELECT DISTINCT egzaminy.Id_student, Id_przedmiot, Nazwisko, Imie
FROM egzaminy, studenci WHERE egzaminy.Id_student = studenci.Id_student;
2/ SELECT DISTINCT egzaminy.Id_student, Nazwisko, Imie, egzaminy.Id_osrodek, Nazwa_o
FROM egzaminy, studenci, osrodki WHERE egzaminy.Id_student = studenci.Id_student AND
egzaminy.Id_osrodek = osrodki.Id_osrodek ORDER BY egzaminy.Id_student;
Liczba związków łączących n tabel: n-1
Szczególny przypadek złączenia tabel (tzw. złączenie unarne):
SELECT tab1.kol1,...,tab1.koln, tab2.kol1,...,tab2.koln
FROM tabela1 tab1, tabela1 tab2
WHERE tab1.koli = tab2.kolj
ZÅ‚Ä…czenie rozszerzajÄ…ce:
SELECT tab1.kol1,...,tab1.koln, tab2.kol1,...,tab2.koln
FROM tabela1 tab1, tabela1 tab2
WHERE tab1.koli = tab2.koli(+)
Przykłady:
1/ SELECT o.Id_osrodek, Nazwa_o, COUNT(e.Id_osrodek) FROM osrodki o, egzaminy e
WHERE o.Id_osrodek = e.Id_osrodek(+) GROUP BY o.Id_osrodek, Nazwa_o;
2/ SELECT p.Id_przedmiot, Nazwa_p, COUNT(DISTINCT e.Id_student) FROM przedmioty p,
egzaminy e WHERE p.Id_przedmiot = e.Id_przedmiot(+) GROUP BY p.Id_przedmiot, Nazwa_p;
3/ SELECT o.Id_osrodek, Nazwa_o, MIN(e.Data_egz)
FROM osrodki o, egzaminy e
WHERE o.Id_osrodek = e.Id_osrodek(+) GROUP BY o.Id_osrodek, Nazwa_o;
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 9
INNE SPOSOBY A

CZENIA TABEL
Operator UNION
SELECT kol1, kol2,...,koln FROM tabela1
UNION
SELECT kol1, kol2,...,koln FROM tabela2
gdzie: kol1, kol2,...,koln - nazwy kolumn tabel, mogą być takie same, jeśli nie są - to wartości
odpowiadających sobie pól muszą być tego samego typu
SELECT kol1, kol2,...,koln FROM tabela1
UNION
SELECT kol1, kol2,...,koln FROM tabela2
GROUP BY 2
Przykład:
SELECT Id_student, Id_przedmiot FROM egzaminy WHERE Id_student = '0000050'
UNION
SELECT Id_student, Id_przedmiot FROM egzaminy WHERE Id_student = '0000060';
Operator INTERSECT
SELECT kol1, kol2,...,koln FROM tabela1
INTERSECT
SELECT kol1, kol2,...,koln FROM tabela2
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 10
Przykład:
SELECT Id_student, Id_przedmiot FROM egzaminy WHERE Data_egz BETWEEN '20-04-05'
AND '30-04-05'
INTERSECT
SELECT Id_student, Id_przedmiot FROM egzaminy WHERE Data_egz BETWEEN '10-04-05'
AND '25-04-05';
Operator MINUS
SELECT kol1, kol2,...,koln FROM tabela1
MINUS
SELECT kol1, kol2,...,koln FROM tabela2
Przykład:
1/ SELECT Id_student, Id_przedmiot FROM egzaminy WHERE Data_egz BETWEEN '20-04-05'
AND '30-04-05'
MINUS
SELECT Id_student, Id_przedmiot FROM egzaminy WHERE Data_egz BETWEEN '10-04-05'
AND '25-04-05';
2/ SELECT Id_osrodek, Nazwa_o FROM osrodki
MINUS
SELECT DISTINCT o.Id_osrodek, Nazwa_o FROM osrodki o, egzaminy e
WHERE o.Id_osrodek = e.Id_osrodek;
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 11
PODZAPYTANIA
SELECT kol1, kol2,...,koln FROM tabela1 WHERE koli =
(SELECT kolm FROM tabela2 [WHERE warunek])
Podzapytania:
" wewnętrzna instrukcja SELECT wykonywana jest w pierwszej kolejności i tylko jeden
raz (z wyjątkiem podzapytań skorelowanych)
" umożliwiają wykonanie skomplikowanych wyborów danych
" pozwalają definiować warunki w sposób dynamiczny
Przykłady:
1/ SELECT Id_student, Id_przedmiot FROM egzaminy WHERE Data_egz = (SELECT
MIN(Data_egz) FROM egzaminy);
2/ SELECT Id_student, Nazwisko, Imie FROM studenci WHERE Id_student IN (SELECT
DISTINCT Id_student FROM egzaminy);
3/ SELECT Id_przedmiot, Nazwa_p FROM przedmiotyWHERE Id_przedmiot NOT IN (SELECT
DISTINCT Id_przedmiot FROM egzaminy);
Rodzaje podzapytań:
" zwracające jedną wartość (przykład 1)
" zwracające wiele wartości (przykład 2 i 3)
Przykłady:
1/ SELECT * FROM studenci WHERE Miasto = (SELECT Miasto FROM studenci WHERE
PESEL = '49887400000');
2/ SELECT Id_student, Id_przedmiot FROM egzaminy WHERE Data_egz IN (SELECT Data_egz
FROM egzaminy WHERE Id_student = '0000049') AND Id_student <> '0000049';
3/ SELECT Id_egzaminator, Id_osrodek FROM egzaminy WHERE (Id_egzaminator, Data_egz) IN
(SELECT Id_egzaminator, MAX(Data_egz) FROM egzaminy GROUP BY Id_egzaminator);
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 12
PODZAPYTANIA A GRUPOWANIE DANYCH
SELECT koli, wyraż_grup FROM tabela1
GROUP BY koli HAVING koli =
(SELECT kolm FROM tabela2 WHERE warunek)
SELECT koli, wyraż_grup FROM tabela1
GROUP BY koli HAVING koli =
(SELECT kolm FROM tabela2 GROUP BY kolm)
Przykłady:
1/ SELECT Id_przedmiot FROM egzaminy GROUP BY Id_przedmiot HAVING
MAX(Data_egz) > (SELECT MAX(Data_egz) FROM egzaminy WHERE Id_przedmiot = 3);
2/ SELECT Id_egzaminator, COUNT(*) FROM egzaminy GROUP BY Id_egzaminator HAVING
COUNT(*) = (SELECT MAX(COUNT(*)) FROM egzaminy GROUP BY Id_egzaminator);
PODZAPYTANIA A SORTOWANIE DANYCH
SELECT koli, wyraż_grup FROM tabela1
GROUP BY koli HAVING koli =
(SELECT kolm FROM tabela2 WHERE warunek)
ORDER BY kolj
W podzapytaniu nie może wystąpić klauzula ORDER BY. Można ją umieszczać wyłącznie jako
ostanią w nadrzędnej instrukcji SELECT.
Przykład:
SELECT Id_przedmiot FROM egzaminy GROUP BY Id_przedmiot HAVING MAX(Data_egz) >
(SELECT MAX(Data_egz) FROM egzaminy WHERE Id_przedmiot = 3) ORDER BY Id_przedmiot;
ZAGNIEŻDŻANIE PODZAPYTAC

©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 13
SELECT kol1, kol2, ..., koli FROM tabela1 WHERE koli =
(SELECT kolj FROM tabela2 WHERE kol =
SELECT kolm FROM tabela3 WHERE warunek)
Przykłady:
1/ SELECT Id_egzaminator, Nazwisko, Imie FROM egzaminatorzy WHERE Id_egzaminator IN
(SELECT Id_egzaminator FROM egzaminy GROUP BY Id_egzaminator HAVING COUNT(*)
= (SELECT MAX(COUNT(*)) FROM egzaminy GROUP BY Id_egzaminator));
2/ SELECT Id_student, Nazwisko, Imie FROM studenci WHERE Id_student IN (SELECT
Id_student FROM egzaminy GROUP BY Id_student, Zdal HAVING Zdal = 'N' AND
COUNT(*) = (SELECT MAX(COUNT(*)) FROM egzaminy GROUP BY Id_student, Zdal
HAVING Zdal = 'N'));
Zagnieżdżanie podzapytań może odbywać się bez żadnych ograniczeń !!!
UWAGI DOTYCZ
CE TWORZENIA PODZAPYTAC

" Wewnętrzne zapytania należy ujmować w nawiasy i muszą one wystąpić po prawej
stronie warunku w zapytaniu nadrzędnym.
" Konieczna jest zgodność kolejności, ilości i typu kolumn występujących w podzapytaniu
z ilością i typem kolumn, znajdujących się w zapytaniu nadrzędnym.
" Podzapytania nie mogą zawierać klauzuli sortowania ORDER BY. Może ona wystąpić
jedynie w zapytaniu głównym.
" Zapytania zagnieżdżone wykonywane są w kolejności od najbardziej wewnętrznego do
zapytania głównego.
" Tworzenie kolejnych podzapytań wymaga stosowania operatorów SQL.
PODZAPYTANIA SKORELOWANE
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 14
" Elementem klauzuli WHERE lub HAVING podzapytania jest kolumna występująca w
zapytaniu bezpośrednio nadrzędnym.
" Podzapytanie skorelowane jest wykonywane wielokrotnie tzn. dla każdego rekordu
zapytania nadrzędnego.
" W podzapytaniu występuje odwołanie do wartości pól rekordu z zapytania
nadrzędnego.
Przykłady:
1/ SELECT Id_egzaminator, Id_student, Id_przedmiot, Data_egz FROM egzaminy e1 WHERE
Data_egz = (SELECT MAX(Data_egz) FROM egzaminy WHERE Id_przedmiot =
e1.Id_przedmiot);
2/ SELECT Id_student, Id_osrodek, Id_przedmiot, Data_egz, Zdal FROM egzaminy e1
WHERE Data_egz = (SELECT MAX(Data_egz) FROM egzaminy WHERE Id_przedmiot =
e1.Id_przedmiot AND Id_osrodek = e1.Id_osrodek) AND Zdal = 'Y' ORDER BY Id_osrodek,
Id_przedmiot, Data_egz;
OPERATOR EXISTS W PODZAPYTANIACH SKORELOWANYCH
Operator EXISTS  przeprowadza kontrolę spełnienia określonego warunku użytego
w podzapytaniu skorelowanym. Możliwe jest także zaprzeczenie warunku w postaci NOT EXISTS.
Rezultat zastosowania operatora to wartość TRUE lub FALSE, w zależności od spełnienia bądz
nie
określonego warunku.
Przykłady:
1/ SELECT Id_przedmiot, Nazwa_p FROM przedmioty p WHERE NOT EXISTS
(SELECT Id_przedmiot FROM egzaminy e WHERE p.Id_przedmiot = e.Id_przedmiot);
2/ SELECT Id_student, Nazwisko, Imie FROM studenci s WHERE NOT EXISTS (SELECT
Id_student FROM egzamin e WHERE e.Id_student = s.Id_student);
3/ SELECT Id_egzaminator, Nazwisko, Imie FROM egzaminatorzy eg WHERE EXISTS
(SELECT Data_egz FROM egzaminy e WHERE e.Id_egzaminator = eg.Id_egzaminator);
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 15
EFEKTYWNOŚĆ WYKONYWANIA PODZAPYTAC

Szybkość wykonywania podzapytań zależy w głównej mierze od:
" wielkości tabel,
" ilości zwracanych wierszy,
" istnienia indeksów dla pól użytych w warunkach.
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 16
POLECENIA J
ZYKA DDL
" CREATE
" ALTER
" DROP
Język DDL przeznaczony jest do:
" definiowania nowych tabel, perspektyw i indeksów
" modyfikowania struktury istniejÄ…cych tabel
" usuwania tabel, perspektyw i indeksów
" definiowania użytkowników BD
" definiowania praw do wykonywania operacji lub zbiorów praw (tzw. ról)
" usuwania użytkowników BD, praw lub ról
ZASADY DEFINIOWANIA TABEL
Nazwa tabeli:
" musi rozpoczynać się od litery
" może zawierać cyfry, znak podkreślenia, znaki specjalne $ i #; nie może w niej
wystąpić znak spacji
" nie może być dłuższa niż 30 znaków
" musi być jednoznaczna
" duże i małe litery nie są rozróżniane
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 17
TWORZENIE TABEL - CREATE TABLE
Polecenie tworzy nowÄ… pustÄ… tabelÄ™.
CREATE TABLE tabela
(kolumna_1 typ_kol1 [option1],
kolumna_2 typ_kol2 [option2],...,
kolumna_n typ_koln [optionn],
definicje_war_integralności)
Opis każdej kolumny może ponadto zawierać pewne opcje, dotyczące:
a) domyślnej wartości początkowej - DEFAULT war_pocz
b) możliwości przyjmowania bądz
nie wartości NULL (domyślnie przyjmuje się NULL)
c) definiowania klucza głównego tabeli - PRIMARY KEY
d) określania unikalnych wartości w kolumnie - UNIQUE
e) kontroli wartości wprowadzanych do kolumny - CHECK (warunek)
f) informacje o utworzonej tabeli można uzyskać wydając polecenie
DESC nazwa_tabeli
Przykłady:
1/ CREATE TABLE przedmioty (Id_przedmiot NUMBER(1) PRIMARY KEY,
Nazwa_p VARCHAR2(40) NOT NULL,
Opis VARCHAR2(200) NOT NULL);
2/ CREATE TABLE przedmioty (Id_przedmiot NUMBER(1),
Nazwa_p VARCHAR2(40) NOT NULL,
Opis VARCHAR2(200) NOT NULL,
PRIMARY KEY(Id_przedmiot), UNIQUE(Nazwa_p, Opis));
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 18
DEFINIOWANIE TABEL W OPARCIU O WZORZEC INNEJ TABELI
CREATE TABLE tabela [(kol1, kol2, ..., koln)]
AS SELECT zapytanie
Przykłady:
1/ CREATE TABLE kopia_przedmioty AS
SELECT Id_przedmiot, Nazwa_p FROM przedmioty;
2/ CREATE TABLE kopia_osrodki (Nr_osr, Nazwa_osr) AS
SELECT Id_osrodek, Nazwa_o FROM osrodki WHERE Miasto = 'Lublin';
Uwagi dotyczÄ…ce tworzenia tabel wg wzorca:
" Utworzona tabela zawiera kolumny wskazane na liście polecenia SELECT.
" Jeśli nie określono nazw kolumn w tworzonej tabeli (przykład 1), domyślnie
otrzymajÄ… one nazwy kolumn pochodzÄ…ce ze wzorca.
" Jeśli podano listę nazw kolumn w tworzonej tabeli, ilość kolumn na tej liście musi być
równa ilości kolumn na liście polecenia SELECT.
" Na liście polecenia SELECT nie mogą wystąpić wyrażenia, jeśli nie podano nazwy
kolumny w tworzonej tabeli (konieczne jest opisanie wyrażenia za pomocą aliasu).
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 19
DEFINIOWANIE WI
ZÓW INTEGRALNOŚ CI - CONSTRAINT
" występowanie wartości różnych od NULL
" występowanie unikalnych wartości w kolumnie (kolumnach) - UNIQUE
" wskazanie kolumny będącej kluczem podstawowym:
CONSTRAINT nazwa_więzu PRIMARY KEY (koli, kolj) lub
CONSTRAINT nazwa_więzu PRIMARY KEY
" wskazanie klucza obcego jako zwiÄ…zku z inna tabelÄ…:
CONSTRAINT nazwa_więzu FOREIGN KEY (koli) REFERENCES
tabela(kolumna)
" usuwanie wartości pól z tabel pozostających w związku FOREIGN KEY:
CONSTRAINT nazwa_więzu FOREIGN KEY (koli) REFERENCES
tabela(kolumna) ON DELETE CASCADE
" spełnianie przez wartości kolumny określonego warunku:
CONSTRAINT nazwa_więzu CHECK (warunek)
Więzy integralności mogą być definiowane na poziomie:
" kolumny (NOT NULL, PRIMARY KEY, UNIQUE, CHECK)
" tabeli (CHECK, FOREIGN KEY, PRIMARY KEY, UNIQUE)
Przykłady:
1/ CREATE TABLE osrodki
(Id_osrodek NUMBER(5) CONSTRAINT osr_Id_osrodek_nn NOT NULL,
Nazwa_o VARCHAR2(30) CONSTRAINT osr_nazwa_nn NOT NULL,
Kod VARCHAR2(5),
Miasto VARCHAR2(15),
Ulica VARCHAR2(30),
Numer VARCHAR2(8),
CONSTRAINT osr_Id_osrodek_pk PRIMARY KEY(Id_osrodek));
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 20
2/ CREATE TABLE egzaminy
(Nr_egz NUMBER(7)
CONSTRAINT egin_nr_egz_nn NOT NULL,
Id_student VARCHAR2(7)
CONSTRAINT egin_Id_student_nn NOT NULL,
Id_przedmiot NUMBER(1)
CONSTRAINT egin_Id_przedmiot_nn NOT NULL,
Id_egzaminator VARCHAR2(4)
CONSTRAINT egin_Id_egzaminator_nn NOT NULL,
Data_egz DATE,
Id_osrodki NUMBER(5)
CONSTRAINT egin_Id_osrodek_nn NOT NULL,
Zdal VARCHAR2(1)
CONSTRAINT egin_zdal_check CHECK(Zdal IN ('Y', 'N')),
CONSTRAINT egin_nr_egz_pk PRIMARY KEY (Nr_egz),
CONSTRAINT egin_nr_egz_check CHECK(Nr_egz > 0),
CONSTRAINT egin_Id_student_fk FOREIGN KEY (Id_student) REFERENCES
studenci (Id_student),
CONSTRAINT egin_Id_przedmiot_fk FOREIGN KEY (Id_przedmiot) REFERENCES
przedmioty (Id_przedmiot),
CONSTRAINT egin_Id_egzaminator_fk FOREIGN KEY (Id_egzaminator)
REFERENCES egzaminatorzy (Id_egzaminator),
CONSTRAINT egin_Id_osrodek_fk FOREIGN KEY (Id_osrodek) REFERENCES
osrodki (Id_osrodek) ON DELETE CASCADE);
MODYFIKACJA DEFINICJI TABEL - ALTER TABLE
Modyfikacja definicji tabeli obejmuje:
" dodanie nowych kolumn
" zmiany typu, rozmiaru, wartości domyślnej kolumn lub warunku NOT NULL
" dodanie/usunięcie więzów integralności (kolumn lub tabeli)
" aktywację/dezaktywację więzów integralności
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 21
ALTER TABLE tabela
ADD (kol1 typ_kol1 [opt1], kol2 typ_kol2 [opt2], ...)
MODIFY (kolumna kol_typ [opt])
ALTER TABLE tabela
ADD CONSTRAINT nazwa_więzu definicja_więzu
DROP CONSTRAINT nazwa_więzu [CASCADE]
DROP PRIMARY KEY [CASCADE]
DROP UNIQUE(kol1, kol2) [CASCADE]
ENABLE CONSTRAINT nazwa_więzu
DISABLE CONSTRAINT nazwa_więzu
Przykłady:
1/ ALTER TABLE egzaminatorzy
ADD (Pensja NUMBER(7) DEFAULT 100,
Data_ur DATE NOT NULL);
2/ ALTER TABLE egzaminatorzy
ADD CONSTRAINT egz_pensja CHECK(Pensja > 0);
3/ ALTER TABLE egzaminatorzy
ADD (Miejsce_pracy VARCHAR2(40))
MODIFY (Pensja NUMBER(8,2));
4/ ALTER TABLE egzaminatorzy
MODIFY (Pensja DEFAULT 500);
5/ ALTER TABLE osrodki
DROP CONSTRAINT osr_nazwa_uk;
6/ ALTER TABLE osrodki
DROP PRIMARY KEY CASCADE;
7/ ALTER TABLE osrodki
DISABLE CONSTRAINT osr_Id_osrodek_pk;
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 22
OGRANICZENIA MODYFIKACJI TABEL
" Konieczne jest posiadanie praw ALTER TABLE do tabeli lub ALTER ANY TABLE.
" Dodanie nowej kolumny, dla której musi być spełniony warunek NOT NULL
możliwe jest, gdy tabela nie zawiera żadnych rekordów; jeśli tabela zawiera rekordy
należy wprowadzić dane do wstawionej kolumny a następnie zmienić jej własność na
NOT NULL.
" Modyfikacja kolumn poprzez zmianę typów danych i zmniejszenie rozmiaru możliwa
jest, jeśli dla wszystkich rekordów wartości w tych kolumnach są równe NULL.
" Zawsze możliwe jest zwiększenie rozmiaru typu danych znakowych lub
numerycznych.
" Zmiana wartości domyślnej w kolumnie przy pomocy parametru DEFAULT nie
wpływa na wartości już przechowywane w BD.
" Modyfikacja istniejących więzów może polegać jedynie na wprowadzeniu warunku
NOT NULL, przy założeniu, że dane zgromadzone w BD we wskazanej kolumnie nie
są równe NULL.
" Nie można usunąć więzów definiujących związki między tabelami opartych na
indeksach typu PRIMARY KEY lub UNIQUE bez usunięcia odpowiedniego klucza
obcego; konieczne jest użycie opcji CASCADE.
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 23
WA

CZANIE/WYA

CZANIE WI
ZÓW INTEGRALNOŚ CI
" definicja więzów integralności przechowywana jest w BD
" domyślnie więzy integralności są aktywne i umożliwiają kontrolę poprawności
wprowadzania danych
" więzy integralności mogą nie być sprawdzane, jeśli będą nieaktywne
" aktywacja/dezaktywacja więzów może nastąpić na etapie definiowania (CREATE
TABLE) lub modyfikowania tabeli (ALTER TABLE)
Włączanie więzów:
ENABLE PRIMARY KEY | UNIQUE(kol1,...) | CONSTRAINT nazwa_więzu
Przykłady:
1/ CREATE TABLE przedmioty (Id_przedmiot NUMBER(1) PRIMARY KEY,
Nazwa_p VARCHAR2(40) NOT NULL,
Opis VARCHAR2(200) NOT NULL)
ENABLE PRIMARY KEY;
2/ CREATE TABLE przedmioty (Id_przedmiot NUMBER(1) PRIMARY KEY,
Nazwa_p VARCHAR2(40) NOT NULL,
Opis VARCHAR2(200) NOT NULL);
ALTER TABLE przedmioty ENABLE PRIMARY KEY;
3/ CREATE TABLE przedmioty (Id_przedmiot NUMBER(1)
CONSTRAINT prz_Id_przedmiot_pk PRIMARY KEY DISABLE,
Nazwa_p VARCHAR2(40) NOT NULL,
Opis VARCHAR2(200) NOT NULL);
ALTER TABLE przedmioty ENABLE CONSTRAINT prz_Id_przedmiot_pk;
4/ ALTER TABLE egzaminy ENABLE CONSTRAINT egin_Id_student_fk;
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 24
Uwagi dotyczące aktywacji więzów:
" Aktywacja więzów dla tabeli zawierających rekordy może nastąpić tylko wówczas,
gdy wszystkie rekordy spełniają podane warunki integralności.
" Aktywacja więzów typu PRIMARY KEY lub UNIQUE powoduje utworzenie
odpowiednich indeksów (przykład 3) - wymagane jest posiadanie prawa CREATE
INDEX lub CREATE ANY INDEX.
" Aktywacja więzów definiujących związek tabeli z innymi tabelami wymaga istnienia
aktywnych więzów w tabelach powiązanych (przykład 4).
Wyłączanie więzów:
DISABLE PRIMARY KEY | UNIQUE(kol1,...) | CONSTRAINT nazwa_więzu
Przykłady:
1/ CREATE TABLE przedmioty (Id_przedmiot NUMBER(1) PRIMARY KEY,
Nazwa_p VARCHAR2(40) NOT NULL,
Opis VARCHAR2(200) NOT NULL)
DISABLE PRIMARY KEY;
2/ CREATE TABLE przedmioty (Id_przedmiot NUMBER(1) PRIMARY KEY,
Nazwa_p VARCHAR2(40) NOT NULL,
Opis VARCHAR2(200) NOT NULL);
ALTER TABLE przedmioty DISABLE PRIMARY KEY;
3/ CREATE TABLE przedmioty (Id_przedmiot NUMBER(1)
CONSTRAINT prz_Id_przedmiot_pk PRIMARY KEY,
Nazwa_p VARCHAR2(40) NOT NULL,
Opis VARCHAR2(200) NOT NULL);
ALTER TABLE przedmioty DISABLE CONSTRAINT prz_Id_przedmiot_pk CASCADE;
4/ ALTER TABLE studenci DISABLE CONSTRAINT st_Id_stud_pk CASCADE;
5/ ALTER TABLE egzaminatorzy ADD (CONSTRAINT egz_pensja CHECK(Pensja > 500))
DISABLE CONSTRAINT egz_pensja;
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 25
Uwagi dotyczące dezaktywacji więzów:
" Dezaktywacja więzów nie powoduje usunięcia ich definicji z BD.
" Definicja więzów i ich dezaktywacja w tej samej instrukcji dla tabeli zawierających
rekordy nie wymusza kontroli danych istniejÄ…cych w tabeli.
" Dezaktywacja więzów typu PRIMARY KEY lub UNIQUE powoduje usunięcie z BD
odpowiednich indeksów (przykład 4) - wymagane jest posiadanie prawa DROP
INDEX lub DROP ANY INDEX.
" Dezaktywacja więzów definiujących związek tabeli z innymi tabelami wymaga
usunięcia aktywnych więzów w tabelach powiązanych przy użyciu opcji CASCADE
(przykład 4).
USUNI
CIE TABEL - DROP TABLE
DROP TABLE tabela [CASCADE CONSTRAINTS]
Uwagi dotyczÄ…ce usuwania tabel:
" Konieczne jest posiadanie prawa DROP TABLE do danej tabeli lub prawa DROP
ANY TABLE.
" Usuwane sÄ… wszystkie indeksy skojarzone z tabelÄ….
" Wszystkie perspektywy oparte na tabeli tracÄ… sens, jednak ich definicja nie jest
usuwana z BD.
" Jeśli dla usuwanej tabeli zdefiniowane są związki oparte na indeksach typu
PRIMARY KEY lub UNIQUE, należy je również usunąć używając opcji CASCADE
CONSTRAINTS; w przeciwnym razie tabela nie zostanie usunięta, a SZRBD
ORACLE sygnalizuje błąd wykonania instrukcji.
Przykłady:
1/ DROP TABLE egzaminatorzy;
2/ DROP TABLE osrodki CASCADE CONSTRAINTS;
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 26
POLECENIA J
ZYKA DML
" INSERT
" UPDATE
" DELETE
Język DML przeznaczony jest do:
" wstawiania nowych rekordów do tabel
" modyfikowania istniejących rekordów
" usuwania rekordów z tabel
WSTAWIANIE REKORDÓW - INSERT
Wstawienie jednego rekordu:
INSERT INTO tabela [(kol1, kol2,..., koln)]
VALUES (wart1, wart2,..., wartn)
gdzie: tabela - nazwa tabeli, w której wstawiany będzie nowy rekord
kol1,.., koln - nazwy kolumn tabeli
wart1,..., wartn - wartości wstawiane odpowiednio do kolumn kol1,..., koln
Wstawienie wielu rekordów:
INSERT INTO tabela1 [(kol1, kol2,..., koln)]
SELECT koli, kolj,...,kolo
FROM tabela2
[WHERE warunek]
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 27
Przykłady:
1/ INSERT INTO studenci
VALUES ('0000049', 'Szafrańska', 'Agnieszka', '17-04-77', 'Lublin', '17047701583', '20145',
'Lublin', 'Daszyńskiego', '19/119', NULL, NULL, NULL, NULL, NULL);
2/ INSERT INTO osrodki (Id_osrodek, Nazwa_o, Miasto, Ulica)
VALUES (1, 'CKMP', 'Lublin', 'Bursaki');
3/ INSERT INTO przedmioty SELECT * FROM kopia_przedmioty;
4/ INSERT INTO przedmioty (Id_przedmiot, Nazwa_p) SELECT Id_przedmiot, Nazwa_p FROM
kopia_przedmioty;
5/ INSERT INTO egzaminy (Nr_egz, Id_student, Id_przedmiot, Id_egzaminator, Data_egz,
Id_osrodek) VALUES (1, '0000049', 1, '0036', '20-08-98', 1);
Uwagi dotyczące wstawianie rekordów:
" Wymagane posiadanie prawa INSERT do danej tabeli lub INSERT ANY TABLE.
" Wstawianie wartości typu znakowego lub daty wymaga umieszczenia ich pomiędzy
apostrofami (przykład 1).
" Jeśli polecenie INSERT zawiera listę kolumn, to ich liczba musi być równa liczbie
wstawianych wartości w klauzuli VALUES lub liczbie kolumn na liście polecenia
SELECT (przykład 2).
" Pominięcie listy kolumn w poleceniu INSERT wymaga podania wartości dla każdej
kolumn (przykład 1).
" Wstawianie rekordów przy pomocy polecenia SELECT musi odbywać się przy
zachowaniu zgodności typu wstawianych danych i typu odpowiednich kolumn.
" Brak listy kolumn w poleceniu INSERT i skojarzonym z nim poleceniu SELECT
wymaga istnienia wewnętrznej zgodności kolejności kolumn, określonej na etapie
definiowania tabel (przykład 3).
" Pominięcie kolumny na liście polecenia INSERT powoduje przypisanie wartości NULL
lub wartości określonej parametrem DEFAULT na etapie definiowania tabeli
(przykład 5).
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 28
MODYFIKACJA REKORDÓW - UPDATE
UPDATE tabela SET kol1 = wart1, kol2 = wart2,..., koln = wartn
[ WHERE warunek ]
UPDATE tabela1 SET kol1 = (SELECT koli FROM tabela2)
[ WHERE warunek ]
UPDATE tabela1 SET (kol1, kol2) = (SELECT koli, kolj FROM tabela2)
[ WHERE warunek ]
gdzie: tabela - nazwa tabeli, w której modyfikowane będą rekordy
kol1, kol2,..., koln - nazwy kolumn tabeli modyfikowanej
wart1,..., wartn - wartości podstawiane odpowiednio do kolumn kol1,..., koln
warunek - warunek, jaki muszą spełniać modyfikowane rekordy
Przykłady:
1/ UPDATE studenci SET Nazwisko = 'Nowak' WHERE Id_student = '0000049';
2/ UPDATE studenci st SET Nr_ECDL = Id_student WHERE 7 = ANY (SELECT COUNT(*)
FROM egzaminy e GROUP BY Id_student, Zdal HAVING e.Id_student = st.Id_student AND
Zdal = 'Y');
3/ UPDATE egzaminy SET Id_osrodek = (SELECT Id_osrodek FROM osrodki WHERE Nazwa_o
= 'WSPA' ) WHERE Id_osrodek IN (SELECT Id_osrodek FROM osrodki WHERE Nazwa_o =
'CKMP') AND Data_egz > '16-08-98';
Uwagi dotyczące modyfikacji rekordów:
" Wymagane posiadania prawa UPDATE do tabeli lub UPDATE ANY TABLE.
" Klauzula WHERE ogranicza ilość modyfikowanych rekordów.
" Polecenie UPDATE może zawierać zapytania zagniedżone lub skorelowane do
określania wartości wstawianych do kolumn.
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 29
USUWANIE REKORDÓW - DELETE
DELETE FROM tabela [ WHERE warunek ]
gdzie: tabela - nazwa tabeli, z której usuwane będą rekordy
warunek - warunek, jaki muszą spełniać usuwane rekordy
Uwagi dotyczące usuwania rekordów:
" Wymagane posiadanie prawa DELETE do danej tabeli lub DELETE ANY TABLE.
" Klauzula WHERE ogranicza ilość usuwanych rekordów.
Przykład:
DELETE FROM studenci WHERE Nr_ECDL IS NOT NULL;
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 30
PERSPEKTYWY
Perspektywa (ang. view):
" to obiekt podobny do okna dynamicznego, przez które użytkownicy mogą widzieć
danÄ… tabelÄ™
" wirtualna tabela tzn. nie istniejÄ…ca fizycznie w bazie danych (przechowywana jest
wyłącznie definicja perspektywy), przez użytkownika widziana jest jak "normalna"
tabela
" definiowana jest w oparciu o bazowÄ… tabelÄ™ lub innÄ… perspektywÄ™
" nie zawiera danych fizycznych, lecz operuje na danych z tabeli bazowej
Perspektywy definiuje siÄ™ dla:
" ograniczenia dostępu do wszystkich danych tabeli; każdy użytkownik widzi tylko
część danych, które go interesują
" ułatwienia dostępu do danych w tabeli, poprzez uproszczenie zapytań
" uniknięcia konieczności znajomości całej struktury tabel w bazie danych
" umożliwienia widzenia informacji w różny sposób przez wielu użytkowników
" zapewnienia kontroli poprawności wprowadzanych i modyfikowanych danych
Rodzaje perspektyw:
1/ perspektywy proste - oparte wyłącznie na jednej tabeli bazowej i nie zawierające
w swojej definicji funkcji i klauzuli GROUP BY
2/ perspektywy złożone - oparte na kilku tabelach i/lub zawierające
w swojej definicji funkcje i klauzulÄ™ GROUP BY
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 31
TWORZENIE PERSPEKTYW - CREATE VIEW
CREATE [FORCE | NO FORCE] VIEW nazwa_perspektywy [(kol1, kol2, ...)]
AS SELECT zapytanie
CREATE VIEW nazwa_perspektywy [kol1, kol2, ..., alias_wyrażenia]
AS SELECT zapytanie WITH CHECK OPTION | WITH READ ONLY
CREATE OR REPLACE VIEW nazwa_perspektywy
AS SELECT zapytanie WITH CHECK OPTION CONSTRAINT nazwa_więzu
Uwagi dotyczÄ…ce tworzenia perspektyw:
" Wymagane jest posiadanie praw CREATE VIEW lub CREATE ANY VIEW.
" Perspektywę można utworzyć niezależnie od faktu istnienia tabeli bazowej lub
posiadania praw do schematu danych użytkownika BD (klauzula FORCE).
" Definicja perspektywy może zawierać listę kolumn, których ilość musi być zgodna
z ilością kolumn na liście polecenia SELECT.
" Definicja perspektywy musi zawierać nazwy kolumn, jeśli lista polecenia SELECT
zawiera funkcje lub wyrażenia, dla których nie występują aliasy na tych pozycjach.
" Polecenie SELECT w definicji perspektywy nie może zawierać klauzuli ORDER BY.
" Definicja perspektywy może ograniczać rodzaje operacji wykonywanych na tabeli
bazowej lub innej perspektywie (WITH READ ONLY - tylko odczyt rekordów).
" Na etapie definicji perspektywy możliwe jest określenie warunku, jaki będą musiały
spełniać dane wprowadzane przez perspektywę do tabeli bazowej (klauzula WITH
CHECK OPTION); użycie CONSTRAINT pozwala nazwać powyższy warunek.
" Modyfikacja definicji perspektywy wymaga użycia klauzuli OR REPLACE.
" Zaleca siÄ™ stosowanie listy kolumn w poleceniu SELECT definiujÄ…cym perspektywÄ™.
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 32
Przykłady:
1/ CREATE VIEW student_form_we AS SELECT Id_student, Nazwisko, Imie, Data_ur, Miejsce,
PESEL, Kod, Miasto, Ulica, Numer FROM studenci;
2/ CREATE VIEW osr_lublin AS SELECT Id_osrodek, Nazwa_o, Miasto, Ulica, Numer FROM
osrodki WHERE Miasto = 'Lublin' WITH CHECK OPTION CONSTRAINT o_lublin;
3/ CREATE VIEW egzaminy_ecdl AS SELECT Nr_egz, Nazwisko, Imie, Nazwa_p FROM
egzaminy e, studenci st, przedmioty p WHERE e.Id_student = st.Id_student AND e.Id_przedmiot
= p.Id_przedmiot;
4/ CREATE VIEW l_egzamin (Id_egzaminator, l_egz) AS SELECT Id_egzaminator, COUNT(*)
FROM egzaminy GROUP BY Id_egzaminator;
5/ CREATE VIEW l_prob AS SELECT Id_student, COUNT(*) Proby FROM egzaminy GROUP
BY Id_student;
6/ CREATE VIEW egzaminy_lublin AS SELECT Nr_egz, Id_student, Id_przedmiot,
Id_egzaminator, Data_egz, Id_osrodek, Zdal FROM egzaminy WHERE Id_osrodek = ANY
(SELECT Id_osrodek FROM osrodki WHERE Miasto = 'Lublin') WITH CHECK OPTION;
USUWANIE PERSPEKTYW - DROP VIEW
DROP VIEW nazwa_perspektywy
Uwagi dotyczÄ…ce usuwania perspektyw:
" Wymagane jest posiadanie praw do schematu danych użytkownika lub prawa
DROP ANY VIEW.
" Usunięcie perspektywy, na której oparto inne perspektywy nie powoduje
automatycznego ich usunięcią z BD; tracą one jednak sens.
Przykład:
DROP VIEW student_form_we;
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 33
MODYFIKACJA TABEL POPRZEZ PERSPEKTYWY
1. Odbywa się poprzez zastosowanie poleceń INSERT, UPDATE i DELETE
2. Usuwanie rekordów przez perspektywę jest możliwe tylko wówczas, gdy:
" definicja perspektywy nie zawiera w instrukcji SELECT klauzul DISTINCT,
GROUP BY, HAVING
" modyfikowalna jest perspektywa, na której oparto definicję danej perspektywy
" perspektywa oparta jest tylko na jednej tabeli
" klauzula WHERE nie zawiera podzapytania skorelowanego
3. Modyfikacja rekordów możliwa jest, jeśli:
" spełnione są w/w warunki
" dane wprowadzane to tabeli poprzez perspektywę spełniają warunek podany
w definicji perspektywy, o ile użyto klauzuli WITH CHECK OPTION
" na liście polecenia SELECT nie występują wyrażenia, stałe lub funkcje grupowe
4. Wstawienie rekordów możliwe jest, jeśli:
" spełnione są w/w warunki
" definicja perspektywy zawiera kolumny, które muszą przyjmować wartości
różne od NULL
Przykłady:
1/ INSERT INTO student_form_we
VALUES ('0000049', 'Szafrańska', 'Agnieszka', '17-04-77', 'Lublin', '17047701583', '20145',
'Lublin', 'Daszyńskiego', '19/119');
2/ INSERT INTO osr_lublin VALUES (3, 'LBS', '20950', 'Lublin', 'Plac Wolności', '1');
3/ INSERT INTO egzaminy_lublin VALUES (10, '0000049', 1, '0036', '20-09-98', 3);
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 34
INDEKSY
Rodzaje indeksów:
" PRIMARY KEY - indeks podstawowy (jednoznaczna identyfikacja rekordów)
" UNIQUE - indeks unikalny (niepowtarzalność danych w kolumnie lub kolumnach)
" NON UNIQUE - indeks zwiększający szybkość dostępu do rekordów
Przypadki, w których powinno tworzyć się indeksy:
" przyspieszenie dostępu do danych w przypadku dużej liczby wierszy
" tworzenie indeksów dla kolumn, dla których powinno zapewnić się wartości
niepowtarzalne
" tworzenie indeksów dla kolumn często używanych w klauzulach WHERE,
szczególnie tych, które używane są jako kolumny łączące
Ograniczenia wynikające z istnienia indeksów:
" indeksy spowalniajÄ… wykonywanie operacji typu INSERT, UPDATE i DELETE
" jeśli to możliwe, tworzenie indeksu dla tabel powinno być wykonywane po
wstawieniu rekordów
" max liczba kolumn w definicji indeksu wynosi 16
" dana kombinacja kolumn może pojawić się w definicji indeksu tylko jeden raz
" każdy nowy indeks tabeli wydłuża czas przetwarzania danych
" wartości NULL nie są indeksowane
Sposoby tworzenia indeksów:
" na etapie definiowania tabeli (indeks typu PRIMARY KEY, UNIQUE)
" przy pomocy osobnych instrukcji (indeks typu UNIQUE, NON UNIQUE)
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 35
TWORZENIE INDEKSÓW - CREATE INDEX
CREATE [UNIQUE] INDEX nazwa_indeksu ON tabela (kol1,..., koln)
Uwagi dotyczące tworzenia indeksów:
" Utworzenie indeksu wymaga posiadania prawa własności danej tabeli lub prawa
typu INDEX do danej tabeli lub prawa CREATE ANY INDEX.
" Dodatkowo konieczna jest odpowiednia ilość przestrzeni BD lub posiadanie prawa
UNLIMITED TABLESPACE.
" Polecenie nie tworzy indeksu typu PRIMARY KEY.
Przykłady:
1/ CREATE INDEX i_osrodki_nazwa ON osrodek (nazwa_o);
2/ CREATE UNIQUE INDEX i_przedmiot_nazwa ON przedmiot (nazwa_p);
USUWANIE INDEKSÓW - DROP INDEX
DROP INDEX nazwa_indeksu
gdzie: nazwa_indeksu - nazwa indeksu, który zostanie usunięty
Przykład:
DROP INDEX i_osrodki_nazwa;
Uwaga:
Polecenie DROP INDEX pozwala usunąć tylko te indeksy, które zostały utworzone przy
pomocy instrukcji CREATE INDEX !!!
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 36
SEKWENCJE
Sekwencja - obiekt bazy danych, umożliwiający generowanie unikalnych
kolejnych wartości liczbowych
Sekwencje tworzone są w celu automatycznego generowania unikalnych wartości dla
kluczy typu PRIMARY KEY lub UNIQUE
Sekwencja składa się z dwóch tzw. pseudokolumn:
CURRVAL - informacja o bieżącym numerze przechowywanym w sekwencji
NEXTVAL - inkrementacja sekwencji i generowanie nowej wartości
Pseudokolumny wykorzystywane sÄ…:
" na liście kolumn instrukcji SELECT wyświetlającej informacje z tabel
" na liście kolumn instrukcji SELECT użytej w poleceniu INSERT
" jako element klauzuli VALUES polecenia INSERT
" jako element klauzuli SET w poleceniu UPDATE
Pseudokolumn nie wolno używać w:
" podzapytaniach występujących w instrukcjach SELECT, UPDATE, DELETE
" definicjach perspektyw
" poleceniu SELECT zawierajÄ…cym operator DISTINCT, klauzule GROUP BY lub
ORDER BY
" operacji złączenia tabel za pomocą operatorów UNION, INTERSECT lub MINUS
" warunkach definiowanych po klauzuli WHERE
" definicji więzów integralności typu CHECK
" nadawaniu wartości domyślnych pól w poleceniach CREATE TABLE lub ALTER
TABLE
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 37
TWORZENIE SEKWENCJI - CREATE SEQUENCE
CREATE SEQUENCE nazwa_sekwencji
INCREMENT BY liczba_całkowita
START WITH liczba_całkowita
MAXVALUE liczba_całkowita | NOMAXVALUE
MINVALUE liczba_całkowita | NOMINVALUE
CYCLE | NOCYCLE
CACHE liczba_całkowita | NOCACHE
Uwagi dotyczÄ…ce tworzenia sekwencji:
" Definicja sekwencji wymaga posiadania prawa CREATE SEQUENCE lub CREATE
ANY SEQUENCE.
" W definicji sekwencji można określić pierwszą generowaną liczbę (START WITH),
krok inkrementacji (INCREMENT BY), minimalnÄ… (MINVALUE) i maksymalnÄ…
generowaną wartość całkowitą (MAXVALUE), sposób zachowania się sekwencji w
momencie osiągnięcia wartości granicznej (CYCLE) oraz ilość kolejnych wartości
sekwencji przechowywanych w pamięci (CACHE).
Przykłady:
1/ CREATE SEQUENCE s_przedmioty
MINVALUE 1
MAXVALUE 7
INCREMENT BY 1
START WITH 1
NOCYCLE;
2/ CREATE SEQUENCE s_egzaminy
MINVALUE 1
NOMAXVALUE
INCREMENT BY 1
START WITH 1
NOCYCLE;
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 38
WYKORZYSTANIE SEKWENCJI
Przykłady:
1/ INSERT INTO przedmioty VALUES (s_przedmioty.NEXTVAL, 'Edytory tekstu', NULL);
2/ SELECT s_przedmioty.CURRVAL, s_przedmioty.NEXTVAL, s_przedmioty.NEXTVAL FROM
sys.dual;
3/ UPDATE egzaminy SET Nr_egz = s_egzaminy.CURRVAL;
Uwagi dotyczÄ…ce wykorzystania sekwencji:
" Dane sekwencja może być wykorzystywana jednocześnie przez wiele tabel i przez
wielu użytkowników.
" Każdemu użytkownikowi przydzielana jest niepowtarzalna wartość generowana
przez sekwencjÄ™.
" Przed pierwszym odwołaniem do CURRVAL należy wykonać odwołanie do
NEXTVAL.
" Odwołanie do NEXTVAL powoduje umieszczenie nowej wartości w CURRVAL.
" Wielokrotne odwołania do pseudokolumny NEXTVAL w tym samym poleceniu SQL
zwraca zawsze tą samą wartość.
" Wartości generowane przez sekwencje nie podlegają anulowaniu w sytuacji
wycofania transakcji.
" Jednoczesne występowanie odwołań do CURRVAL i NEXTVAL zawsze prowadzi do
wyświetlenia tych samych wartości, niezależnie od porządku odwołań.
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 39
MODYFIKACJA SEKWENCJI - ALTER SEQUENCE
ALTER SEQUENCE nazwa_sekwencji
INCREMENT BY liczba_całkowita
MAXVALUE liczba_całkowita | NOMAXVALUE
MINVALUE liczba_całkowita | NOMINVALUE
CYCLE | NOCYCLE
CACHE liczba_całkowita | NOCACHE
Uwagi dotyczÄ…ce modyfikacji sekwencji:
" Możliwa przy posiadaniu prawa ALTER w odniesieniu do danej sekwencji lub
ALTER ANY SEQUENCE.
" Dotyczy zmiany kroku inkrementacji (INCREMENT BY), maksymalnej
(MAXVALUE) i minimalnej (MINVALUE) wartości generowanej, liczby wartości
przechowywanych w pamięci (CACHE) oraz sposobu zachowania się w momencie
osiągnięcia wartości granicznej (CYCLE).
" Nie może prowadzić do wewnętrzych sprzeczności np. nowa wartość MAXVALUE
nie może być mniejsza od ostatnio wygenerowanej wartości.
" Nie wymusza ponownego generowania wartości od przyjętej wartości początkowej
(sekwencję należy usunąć i zdefiniować od nowa).
" Prowadzi do generowania nowych wartości tylko dla operacji zachodzących
w przyszłości.
Przykłady:
1/ ALTER SEQUENCE s_egzaminy INCREMENT BY 10;
2/ ALTER SEQUENCE s_przedmioty MAXVALUE 9;
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 40
USUWANIE SEKWENCJI - DROP SEQUENCE
DROP SEQUENCE nazwa_sekwencji
Uwagi dotyczÄ…ce usuwania sekwencji:
" Możliwe przy posiadaniu prawa ALTER ANY SEQUENCE lub prawa własności
sekwencji.
Przykład:
DROP SEQUENCE s_przedmioty;
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 41
FUNKCJE J
ZYKA SQL
Rodzaje funkcji:
1. Funkcje skalarne lub operujÄ…ce na jednym rekordzie danych:
" numeryczne
" znakowe
" daty
" konwersji
2. Funkcje agregujące lub operujące na zbiorach rekordów danych:
" grupowe
" inne
FUNKCJE NUMERYCZNE
Funkcje, których argumentami i rezultatem są wartości numeryczne.
Nazwa funkcji Opis
ABS(n) wartość bezwzględna z n
CEIL(n) najmniejsza liczba całkowita większa lub równa n
COS(n) cosinus z n (w radianach)
COSH(n) cosinus hiperboliczny z n
EXP(n) e do potęgi n (e = 2.71828183)
FLOOR(n) największa liczba całkowita mniejsza lub równa n
LN(n) logarytm naturalny z n
LOG(m, n) logarytm przy podstawie m z n
MOD(m,n) reszta z dzielenia m przez n
POWER(m,n) m do potęgi n
ROUND(n,[m]) wartość n zaokrąglona do m miejsc dziesiętnych
SIN(n) sinus z n (w radianach)
SINH(n) sinus hiperboliczny z n
SQRT(n) pierwiastek kwadratowy
TAN(n) tangens z n (w radianach)
TANH(n) tangens hiperboliczny
TRUNC(n,[m]) wartość n zawierająca m miejsc dziesiętnych (bez zaokrąglania)
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 42
FUNKCJE ZNAKOWE
Funkcje, których argumentami są ciągi znaków, a rezultatem są wartości numeryczne lub ciągi
znaków.
Nazwa funkcji Opis
Funkcje o rezultatach znakowych
CHR(n) Wyświetla znak o podanym kodzie n
CONCAT(cz1, cz2) Konkatenacja ciągów znaków cz1 i cz2
INITCAP(cz) Zamiana pierwszych litery wyrazów ciągu na
duże litery
LOWER(cz) Zamiana znaków w ciągu na małe litery
LPAD(cz1, n [, cz2]) Uzupełnienie z lewej strony ciągu znaków cz1
o ciąg cz2 do całkowitej długości n znaków
LTRIM(cz1 [, cz2]) Usunięcie z lewej strony w ciągu znaków cz1
znaków określonych przez cz2
REPLACE(cz1, cz2, [, cz3]) Zastąpienie w ciągu znaków cz1, ciągu cz2
poprzez ciąg znaków cz3
RPAD(cz1, n [,cz2]) Uzupełnienie z prawej strony ciągu znaków cz1
o ciąg cz2 do całkowitej długości n znaków
RTRIM(cz1 [,cz2]) Usunięcie z prawej strony w ciągu znaków cz1
znaków określonych przez cz2
SUBSTR(cz, m [,n]) Wycięcie z ciągu znaków cz podciągu znaków
zaczynającego się od pozycji m; n - liczba znaków
wycinanych
UPPER(cz) Zamiana znaków w ciągu na duże litery
Funkcje o rezultatach numerycznych
ASCII(znak) Podaje kod ASCII wskazanego znaku
INSTR(cz1, cz2 [,n [,m]]) Przeszukanie ciągu znaków cz1, począwszy od
pozycji n w celu znalezienia m-tego wystÄ…pienia
ciÄ…gu cz2
LENGTH(cz) Długość ciągu znaków
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 43
FUNKCJE DATY
Funkcje, których argumenty i rezultaty są typu daty.
Nazwa funkcji Opis
Funkcje o rezultatach typu daty
ADD_MONTHS(d, n) Dodanie do miesiąca daty d liczby miesięcy określonej
przez n
LAST_DAY(d) Wyznaczenie ostatniego dnia miesiąca określonego
w dacie d
NEW_TIME(d, s1, s2) Wyznaczenie czasu i daty w strefie czasowej s2
odpowiadajÄ…cego czasowi i dacie d w strefie s1
NEXT_DAY(d, cz) Wyznaczenie najbliższej daty dnia, którego nazwę
podano w ciągu znaków cz w stosunku do daty d
ROUND(d [,fmt]) Wyznaczenie zaokrÄ…glenia daty d w oparciu o format
określony przez fmt np. do początku roku
SYSDATE Wyznaczenie daty systemowej
TRUNC(d [, fmt]) Wycięcie z daty d informacji według formatu fmt.
Funkcje o rezultatach numerycznych
MONTHS_BETWEEN(d1, d2) Różnica między datami d1 i d2 wyrażona w miesiącach
FUNKCJE KONWERSJI
Funkcje dokonujące konwersji danych między różnymi typami danych.
Nazwa funkcji Opis
CHARTOROWID(ciÄ…g) Konwersja typu VARCHAR2 lub CHAR na typ ROWID
TO_CHAR(d [,fmt]) Konwersja daty d do postaci ciągu znaków wg formatu fmt
TO_CHAR(n [,fmt]) Konwersja danych numerycznych n do postaci ciągu znaków
wg podanego formatu fmt
TO_DATE(ciąg [,fmt]) Konwersja ciągu znaków ciąg do postaci daty wg formatu fmt
TO_NUMBER(ciąg [,fmt]) Konwersja ciągu znaków ciąg do postaci danej numerycznej
wg formatu fmt
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 44
FUNKCJE GRUPOWE
Funkcje umożliwiające wykonanie operacji matematycznych na grupach rekordów.
Nazwa funkcji Opis
AVG(DISTINCT|ALL n) Wyznaczenie wartości średniej z n;
n - wyrażenie lub nazwa kolumny
COUNT(*| [DISTINCT|ALL] wyr) Zliczenie ilości rekordów; wyr - nazwa kolumny lub
wyrażenie
MAX([DISTINCT|ALL] wyr) Wartość maksymalna z wyrażenia wyr
MIN([DISTINCT|ALL] wyr) Wartość minimalna z wyrażenia wyr
STDDEV([DISTINCT|ALL] x) Standardowe odchylenie od x
SUM([DISTINCT|ALL] n) Suma wartości n
VARIANCE([DISTINCT|ALL] x) Wariancja z x
INNE FUNKCJE
Funkcje umożliwiające wykonanie operacji matematycznych na grupach rekordów.
Nazwa funkcji Opis
NVL(wyr1, wyr2) Funkcja zwraca wartość wyrażenia wyr1, jeśli jest ono
różne od NULL; jeśli wyr1 = NULL to funkcja zwraca
wartość wyr2
©Piotr Muryjas, Instytut Informatyki, Politechnika Lubelska 45