SQL – podstawy języka relacyjnych baz danych

Wykłady z przedmiotu Podstawy baz danych – SQL firmy Oracle cz.1 – dr hab. prof. UŁ. Tadeusz Antczak
SQL firmy Oracle. Polecenia DDL
Historia SQL-a
1970: E.F.Codd, IBM – Relacyjne bazy danych (dr Edgar F.Codd w czerwcowym numerze pisma
„Communications of the ACM” przedstawił swój artykuł zatytułowany „Relacyjny model danych dla
dużych banków danych współużytkowanych”, gdzie nakreślił główne idee stworzonego przez siebie
modelu baz danych
1973: pierwszy system zarządzania relacyjną bazą danych (System R w firmie IBM)
1974: Chamberlain, IBM, San Jose – Structured English Query Language SEQUEL (prototyp SQL)
1976-7: SEQUEL/2
1977: ORACLE (Relational Software Inc.) – pierwsza implementacja praktyczna (komercyjna), tj.
pierwszy komercyjny serwer baz danych oparty na SQL,
1981: IBM – SQL/DS. (SZBD), poprzednik DB/2 (1983),
1982: ANSI: RDL (Relational Data Language),
1983: ISO – definicja SQL,
1986: ANSI – pierwszy standard SQL (SQL-86),
1987: ISO – pierwszy standard SQL: ISO 9075: 1987 (E),
1989: ISO – następny standard SQL: ISO 9076: 1989 (E) (SQL-89),
1992: ISO – kolejna, wzbogacona wersja: ISO 9075: 1992 (E) (SQL 2),
1999: wdrożenie następnej wersji standardu SQL 3 (uwzględniał cechy obiektowe)
Kolejne wersje standardu: SQL:2003, SQL:2006, SQL:2008
Obecnie obowiązuje wersja standardu SQL:2011
Cechy języka SQL
jest językiem wysokiego poziomu (językiem czwartej generacji – 4GL), opartym na słownictwie języka
angielskiego, jego wyrażenia mają określoną strukturę
jest językiem deklaratywnym (nieproceduralnym), zorientowanym na wynik (użytkownik definiuje, co
chce otrzymać, ale nie pisze jak), nie potrzebuje zatem podawania dokładnego sposobu przetwarzania
danych,
jest oparty na algebrze relacji,
zawiera logikę trójwartościową,
nie posiada instrukcji sterujących wykonywaniem programu,
nie dopuszcza rekurencji,
umożliwia definiowanie struktur danych, wyszukiwanie danych oraz operacje na danych,
działa na zbiorach danych.
1
Wykłady z przedmiotu Podstawy baz danych – SQL firmy Oracle cz.1 – dr hab. prof. UŁ. Tadeusz Antczak
Język SQL w aplikacjach bazodanowych
Zasadniczo język SQL jest jedynym językiem rozumianym przez oprogramowanie zarządzające bazami
danych. Ponieważ wszystkie instrukcje przekazywane do serwera są przekazywane w postaci wyrażeń
języka SQL, a zatem nie ma znaczenia, czy narzędzie używane na stacji klienckiej ma prosty interfejs w
postaci wiersza komend, czy jest to wysublimowany interfejs graficzny.
Do komunikowania się za pomocą języka SQL można podejść dwojako:
można włączać wyrażenia tego języka do modułów aplikacji
lub
budować te wyrażenia na podstawie informacji wprowadzanych przez użytkownika.
Interfejsy w postaci komend – takie, jakim jest na przykład SQL*Plus (który zawiera jednak pewne
rozszerzenia) – wymagają, aby użytkownik napisał odpowiednie instrukcje, które będą następnie przekazane
do serwera. Możliwe jest także uruchamianie skryptów przechowywanych gdzieś w systemie plików.
Struktura i wykorzystanie języka SQL
Język SQL:
•
jest przykładem języka transformacji, co oznacza, że został on tak zaprojektowany, że umożliwia
przekształcanie relacji wejściowych na relacje wejściową – a zatem zawsze w wyniku działań na
relacjach (tabele, kwerendy) otrzymujemy jako wynik również relację,
•
jest językiem nieproceduralnym, w którym użytkownik opisuje informację, której potrzebuje (tzn. którą
chce uzyskać z bazy danych), ale nie wskazuje on przy tym, w jaki sposób należy ja odnaleźć – a zatem
język SQL nie wymaga od użytkownika podania sposobu dostępu do danych,
•
zapytanie języka SQL składa się ze słów zarezerwowanych (kluczowych) oraz ze słów zdefiniowanych
przez samego użytkownika. Podobnie jak w innych językach tak i w SQL-u, słowa kluczowe są
niezmienną częścią języka i mają swoje specyficzne, ustalone znaczenie. Należy je również zapisywać w
sposób dokładny, bez jakichkolwiek zmian, tj. dokładnie tak, jak wymaga tego składnia języka SQL;
przykładowo nie wolno ich dzielić na składowe. Natomiast słowa zdefiniowane przez użytkownika są
tworzone zgodnie z pewnymi regułami syntaktycznymi przez samego użytkownika i są nazwami
pewnych obiektów takich, jak kolumny, tabele, perspektywy, indeksy, itp.
•
każde zapytanie (zdanie) w języku SQL firmy Oracle jest kończone średnikiem.
Komponenty języka SQL
 DDL (Data Definition Language) – język definiowania struktur danych (CREATE, DROP, ALTER
TABLE),
 DML (Data Manipulation Language) – język operacji na danych (SELECT, INSERT, UPDATE,
DELETE),
 Instrukcje sterowania danymi – kontrola uprawnień użytkowników (GRANT, REVOKE).
2
Wykłady z przedmiotu Podstawy baz danych – SQL firmy Oracle cz.1 – dr hab. prof. UŁ. Tadeusz Antczak
SQL – jako przykład języka czwartej generacji
Języki czwartej generacji (4GL), takie jak np. SQL, są na ogół proste (w porównaniu z językami 3GL) i
udostępniają mniejsza liczbę poleceń. Fakt, iż język SQL jest językiem czwartej generacji implikuje, iż
opisuje on tylko żądanie zadania, a nie sposób jego wykonania. Ponadto języki 4GL izolują również
użytkownika od wewnętrznych struktur danych i algorytmów.
Poniższy schemat prezentuje porównanie możliwości języka SQL jako języka 4GL.
wydajność
prostota
język SQL
manipulowanie danymi
przetwarzanie danych
opisuje żądane zadanie, a nie sposób ich wykonania
izolacja od wewnętrznych struktur danych i algorytmów
język czwartej generacji (4GL)
Ograniczenia języka SQL.
Strukturalny język zapytań SQL (Structured Qurey Language) :
• jest językiem, którego podstawą jest teoria zbiorów, a zatem jego zadaniem jest manipulowanie zbiorami
danych,
• składa się ze stosunkowo niewielkiej liczby podstawowych poleceń, takich jak SELECT, INSERT,
CREATE czy GRANT,
• jest wprawdzie elastycznym i wydajnym językiem, ale jest przeznaczony głównie do manipulowania
i przetwarzania danych w relacyjnych bazach danych.,
• nie jest językiem ogólnego przeznaczenia i nie umożliwia tworzenia różnego rodzaju algorytmów,
• nie zawiera choćby instrukcji sterowania programem lub wykonywania pętli – z tego powodu tego typu
elementy programistyczne muszą być implementowane za pomocą albo języka aplikacji, albo
interakcyjnie, tj. przez samego użytkownika,
• okazuje się także niewystarczający, np. w sytuacji, gdy zaistniej potrzeba ochrony i szczególnego
zabezpieczenia danych. W przypadku, gdy podstawą stosowanej metody zabezpieczania danych jest
język SQL, należy uwzględnić fakt, iż administrator bazy danych ma wprawdzie pewną kontrolę nad tym,
kto może manipulować danymi, ale nie ma kontroli nad formą tych modyfikacji.
Niewątpliwe wady języka SQL spowodowały konieczność rozszerzenia możliwości programistycznych
w bazie danych firmy Oracle, choćby ze względu na możliwość tworzenia konstrukcji proceduralnych
niedostępnych w językach czwartej generacji.
3
Wykłady z przedmiotu Podstawy baz danych – SQL firmy Oracle cz.1 – dr hab. prof. UŁ. Tadeusz Antczak
Rozszerzenia proceduralne języka SQL
Rozszerzenie języka jest zbiorem właściwości, które w pewien sposób zwiększają jego możliwości.
Jednym z takich rozszerzeń języka SQL, jest oferowany przez firmę ORACLE w swoim systemie
bazodanowym, język PL/SQL (Procedure Language Structured Query Language). Należy podkreślić, iż nie
jest prawdziwym stwierdzeniem, że PL/SQL jedynie wersją języka SQL, w pewien celowy sposób
rozszerzającą jego możliwości programistyczne. PL/SQL jest zupełnie odrębnym językiem programowania z
własną składnią, własnymi zasadami i własnym kompilatorem. Możliwe jest pisanie w języku PL/SQL,
zarówno programów wykorzystujących, jak i nie korzystających z poleceń SQL. Panuje przekonanie, iż
PL/SQL stanowi nadbudowę języka SQL, lecz nie jest stwierdzenie do końca prawdziwe, gdyż z poziomu
PL/SQL można w prosty sposób wykonywać tylko najpowszechniej stosowane polecenia języka SQL.
Tabele w języku SQL
Tabele są obiektami bazy danych całkowicie autonomicznymi, tzn. istnieją w bazie danych jako niezależne
środki przechowywania informacji w sposób zorganizowany – jako kolumny, które definiują strukturę
danych oraz jeden lub więcej wierszy danych zgodnych z tą strukturą. Ważną cechą tabel jest ich nazwa,
ponieważ na jej podstawie są one definiowane, zmieniane, usuwane i dostępne.
Do manipulowania tabelami używa się kilku poleceń:
 CREATE TABLE – definiuje tabelę i jej kolumny,
 ALTER TABLE – zmienia tabele i kolumny,
 DROP TABLE – usuwa tabelę – jej definicję i zawartość,
 RENAME – zmienia nazwę tabeli.
Polecenia CREATE TABLE i ALTER TABLE są ponadto używane w celu definiowania ograniczeń
kluczy oraz tzw. parametrów przechowywania, które są rozszerzeniami składni Oracle.
Typy tekstowe:
 VARCHAR2(L) – oznacza typ danych, za pomocą którego można przechowywać ciągi znaków o
zmiennej długości, gdzie L oznacza określoną maksymalną długość zmiennej tego typu.
Dopuszczalny rozmiar dla zmiennej VARCHAR2 wynosi 4000 bajtów.
 VARCHAR(L) – pozwala przechowywać napisy o zmiennej długości, których długość może wahać
się od 1 do 2000 znaków. Jest zdefiniowany przez standard ANSI, zaś typ VARCHAR2 przez firmę
Oracle.
 CHAR() – pozwala przechowywać ciągi znaków o stałej długości od 1 do 255 znaków.
Długość domyślna to 1 znak.
Typy numeryczne:
 NUMBER(zakres) – typ całkowity – pozwala przechowywać liczby całkowite ze znakiem mającą
tyle cyfr, ile wynosi parametr zakres ( o maksymalnej długości 38 cyfr).
 NUMBER(zakres, dokładność) – określa dziesiętną liczbę stałoprzecinkową zapisywaną
z dokładnością do maksymalnie 38 cyfr i wykładnikiem pomiędzy 84 a 127.
4
Wykłady z przedmiotu Podstawy baz danych – SQL firmy Oracle cz.1 – dr hab. prof. UŁ. Tadeusz Antczak
 NUMBER – określa kolumnę zmiennoprzecinkową z 38 cyframi pamiętanymi dokładnie
i wykładnikiem pomiędzy 125 a -127.
Typy czasu:
 DATE – obejmuje okres od pierwszego stycznia 4712 r. p.n.e. do 31 grudnia 4712 r. n.e.
Kolumna typu DATE może przechowywać czas z dokładnością do sekund.
Typy niestandardowe:
Oracle posiada cztery typy danych nie należące do standardu SQL:
 ROWID – unikalny identyfikator adresu, pod którym przechowywany jest rekord w bazie danych,
 LONG – rozszerzenie typu VARCHAR2, które pozwala przechowywać napisy o długości do 2GB,
 RAW – ciąg dowolnych znaków binarnych o długości do 255 znaków,
 LONG RAW – dane binarne o długości do 2 GB.
Uwaga: SQL firmy Oracle nie ma specjalnego typu do przechowywania danych logicznych. Oczywiście taki
typ miałby tylko dwie możliwe wartości: TRUE i FALSE. Do wartości logicznych można użyć
jednocyfrowej liczby całkowitej lub jednoelementowego znaku tekstowego.
Tworzenie nowej tabeli
Składnia polecenia CREATE TABLE, za pomocą którego w języku SQL można utworzyć najprostszą
tabelę, jest następująca:
CREATE TABLE nazwa_tabeli
(nazwa_kolumny1 typ_danych1);
Przykład:
CREATE TABLE towary (
id
NUMBER(4),
nazwa
VARCHAR2(15),
cena
NUMBER(7,2),
kategoria
VARCHAR2(30),
stan_magazyn
NUMBER,
wycofany
CHAR(3)
);
Uwaga 1: Każda nazwa tabeli powinna być unikalna ( w systemie Oracle wszystkie nazwy tabel są
przechowywane w przestrzeni tabel i z tego powodu powinny być unikalne.
Uwaga 2: Tabela musi posiadać przynajmniej jedną kolumnę, która z kolei musi posiadać określony typ
danych.
Wartość NOT NULL dla pola
by w danym polu tabeli nie mogła wystąpić wartość pusta należy po zdefiniowaniu typu danych pola podać
wyrażenie NOT NULL. Wyrażenie NOT NULL wskazuje właśnie, iż kolumna ta nie zaakceptuje wartości
pustej NULL.
5
Wykłady z przedmiotu Podstawy baz danych – SQL firmy Oracle cz.1 – dr hab. prof. UŁ. Tadeusz Antczak
Wartości domyślne
W celu ustawienia dla kolumny tabeli wartości domyślnej należy użyć słowa kluczowego DEFAULT, po
którym podajemy wartość domyślną. Wartość domyślna może być stałą bądź pseudofunkcją.
Przykład:
CREATE TABLE towary (
id
NUMBER(4) NOT NULL,
nazwa
VARCHAR2(15) NOT NULL,
cena
NUMBER(7,2) NOT NULL,
kategoria
VARCHAR2(30) NOT NULL,
stan_magazyn
NUMBER DEFAULT 0,
wycofany
CHAR(3) DEFAULT ‘Nie’
);
Sposoby usuwanie tabel i ich zawartości z bazy danych: DROP TABLE i TRUNCATE TABLE
Tabele i ich zawartość są usuwane za pomocą polecenia DROP TABLE nazwa_tabeli. Dla tabel, do których
nie odwołuje się żaden klucz obcy, tabela i jej zawartość zostanie całkowicie usunięta.
Przykład.
DROP TABLE czytelnicy;
Gdy usuwana tabela jest połączona więzami referencyjnymi (integralności) z innymi tabelami, należy użyć
polecenia DROP TABLE nazwa_tabeli CASCADE CONSTRAINTS.
Przykład.
DROP TABLE czytelnicy CASCADE CONSTRAINTS;
W celu natychmiastowego i bezpowrotnego usunięcia tabeli należy użyć polecenia TRUNCATE TABLE
nazwa_tabeli. Polecenie TRUNCATE TABLE czyści zawartość tabeli bezpowrotnie, ponieważ mechanizm
anulowania jest nieaktywny.
Przykład.
TRUNCATE TABLE towary;
Wskazówka: Usunięcie dużej tabeli bez uprzedniego jej wycięcia może zając dużo czasu, ponieważ każdy
usuwany blok jest kopiowany do przestrzeni anulowania. Nawet jeśli wykonanie polecenia DROP TABLE
zostanie po chwili wstrzymane, przywrócenie wszystkiego zajmie dużo czasu. Dlatego w przypadku
konieczności wymazania tabeli wskazane jest użycie polecenia TRUNCATE TABLE, a dopiero potem
polecenia DROP TABLE.
Zmiana nazwy tabeli.
RENAME stara_nazwa_tabeli TO nowa_nazwa_tabeli
Uwaga. Nazwy tabel mogą być zmieniane tylko wewnątrz tego samego schematu, a zatem niemożliwe jest
przenoszenie tabeli i jej zawartości pomiędzy schematami bez ponownego jej tworzenia.
6
Wykłady z przedmiotu Podstawy baz danych – SQL firmy Oracle cz.1 – dr hab. prof. UŁ. Tadeusz Antczak
Dodawanie kolumny do tabeli.
Dodawanie kolumn jest wykonywane za pomocą opcji ADD polecenia ALTER TABLE.
ALTER TABLE nazwa_tabeli ADD (nazwa_kolumny typ_kolumny);
Uwaga. Dodanie kolumny nie ma wpływu na dane znajdujące się w już istniejących kolumnach.
Zmiana definicji kolumny tabeli.
Z pewnymi ograniczeniami, przy użyciu opcji MODIFY polecenia ALTER TABLE można zmienić cztery
części definicji kolumny:
 kolumna może być zmieniona na dowolny prawidłowy typ danych, jeśli nie jest wypełniona w tym
znaczeniu, że każdy wiersz musi zawierać wartość NULL w tej kolumnie. W innym przypadku
kolumna typu VARCHAR2 może być zmieniona na CHAR tego samego rozmiaru i na odwrót.
Przykład. Przykład ilustruje przypadek, iż rozmiar kolumny może być ustawiony na większy rozmiar:
ALTER TABLE towary MODIFY (kategoria VARCHAR2(50));
 kolumna, która nie jest wypełniona danymi, może być zmieniona na dowolny prawidłowy wymiar.
Rozmiar i precyzja kolumny już wypełnionej danymi nie może ulec zmniejszeniu.
Przykład. Wypełnione kolumny typu numerycznego mogą być zwiększone.
ALTER TABLE towary MODIFY (cena NUMBER(9,2));
Uwaga: Ale powtórne wydanie polecenia z instrukcją ALTER TABLE…MODIFY zmniejszającą precyzję
już wypełnionej danymi kolumny typu numerycznego
ALTER TABLE towary MODIFY(cena NUMBER(5,2));
jest już niedozwolona, co zostanie zasygnalizowane błędem:
ERROR at line 1:
ORA-1440: kolumn to be modified must be empty to decrease precision or scale
 Ograniczenia NOT NULL mogą być dodane, jeśli ani jeden wiersz nie posiada ograniczenia NULL
w tej kolumnie. Ograniczenia NOT NULL mogą być również usuwane.
Przykład. Zniesienie ograniczenia NOT NULL dla pola kategoria.
ALTER TABLE towary MODIFY (kategoria VARCHAR(30) NULL);
 Można zmieniać wartości domyślne.
Usunięcie kolumny z tabeli.
ALTER TABLE nazwa_tabeli DROP COLUMN nazwa_kolumny;
Jednoetapowy proces tworzenia tabel, których kolumny i zawartość są wyprowadzone z innych tabel
Jeżeli tabela oryginalna jest statyczna lub nie posiada zbyt wielkiej liczby wierszy i ma być dość rzadko
kopiowana może okazać się koniecznym przechowywanie części jej danych lub danych zagregowanych
uzyskanych na jej podstawie w innej tabeli.
Wówczas należy użyć składni polecenia
CREATE TABLE nazwatabeli AS SELECT listapol FROM nazwatabelizrodłowej...
7
Wykłady z przedmiotu Podstawy baz danych – SQL firmy Oracle cz.1 – dr hab. prof. UŁ. Tadeusz Antczak
Przykład.
CREATE TABLE produktywycofane
AS SELECT id, nazwa, cena, stan
FROM towary
WHERE wycofany = ‘Nie’;
Tworzona jest tabela produktywycofane, która zawiera dane o produktach wycofanych, które są zawarte
aktualnie w tabeli towary.
Ograniczenia tabel/kolumn
Ograniczenia, jakie można ustawić dla tabel/kolumn służą następującym celom:
 ograniczają wartości, które mogłyby zostać wstawione do kolumny lub zestawu kolumn.
 przyśpieszają lub mogą przyśpieszać pobieranie pojedynczych wierszy lub zestawów wierszy.
Ograniczenia mogą być:
 statyczne, które limitują wartość lub zakres wartości, które mogą być wstawione (np. CHECK,
NOT NULL),
 w relacji ze wszystkimi wartościami kolumny, ograniczając w ten sposób nowe wartości tylko do
takich wartości, które nie występują w pewnych kolumnach lub zestawach kolumn (klucz unikalny,
klucz podstawowy).
 w relacji z inną tabelą, pozwalając w ten sposób na wstawienie jedynie takich wartości, które
występują także w innych kolumnach w innej (lub tej samej) tabeli (klucz obcy).
 indeksami – przyśpieszają one pobieranie danych. Ponadto indeksy tabeli są używane także w celu
zapewnienia unikalnej wartości w kolumnie unikalnego lub podstawowego klucza.
Ograniczenia dla kolumn dotyczące ograniczenia klucza podstawowego lub unikalnego (zarówno prostych,
jak i złożonych) oraz dynamicznych ograniczeń pomiędzy tabelami, kluczy obcych oraz rekurencyjnych
kluczy obcych bywają określane mianem ograniczeń dynamicznych.
Ograniczenia statyczne
Głównym celem ograniczeń statycznych jest uniemożliwienie wstawienia nieodpowiednich wartości do
tabeli, a dokładniej mówiąc do kolumn tabeli.
Ograniczenia NOT NULL.
Ograniczenia NOT NULL można dodawać i usuwać nie tylko w procesie tworzenia tabeli, ale również poza
tym procesem podczas modyfikacji kolumny tabeli. Ograniczenia NOT NULL są wyjątkowe w takim
znaczeniu, iż są ograniczeniami typu inline. Oznacza to, że są one częścią definicji kolumny, do której się
odnoszą. Dlatego dodanie lub usunięcie ograniczenia NOT NULL wymaga zmodyfikowania kolumny, a do
tego celu należy wykorzystać polecenie ALTER TABLE nazwa_tabeli MODIFY.
Ograniczenia CHECK.
Istnieje kilka możliwości wykorzystania ograniczenia CHECK:
8
Wykłady z przedmiotu Podstawy baz danych – SQL firmy Oracle cz.1 – dr hab. prof. UŁ. Tadeusz Antczak
 kolumny wymagane – zapobiega wprowadzaniu wartości niezidentyfikowanych do tych pól,
 prawidłowe zakresy – możliwość ograniczeni wprowadzanych wartości od określonych zakresów,
 zakresy kodów – możliwość ustawienia odpowiedniej struktury jednoznacznych kodów,
 reguły biznesowe – określenie pewnych reguł i wymuszenie pewnych zależności.
Ograniczenie CHECK jest:
•
definiowane przy użyciu podklauzuli CHECK(warunek)
albo
•
w poleceniu CREATE TABLE lub w poleceniu ALTER TABLE nazwa_tabeli ADD.
Parametr warunek może zawierać dowolną kolumnę tabeli, ale nie może zawierać zagregowanych wartości
lub podzapytań.
Przykład.
ALTER TABLE pracownicy ADD
(CHECK (data_zatr > data_ur);
Przykład nieprawidłowego użycia ograniczenia CHECK.
Przykład.
ALTER TABLE pracownicy ADD
(CHECK (data_ur > (SELECT data_ur FROM pracownicy WHERE id = 1)));.
Wyrażenie, które zostało przypisane temu ograniczeniu jest oparte na podzapytaniu, co nie jest
dopuszczalne!
Ograniczenia dynamiczne
Ograniczenia dynamiczne mogą być oparte na:
 wyrażeniu, w którym zostały użyte stałe i wartości w wierszu,
 danych przechowywanych w innych tabelach (klucze obce z innych tabel),
 wszystkich innych wartościach w kolumnie lub kolumnach tabeli, która ma zostać zmodyfikowana
(klucze unikalne, podstawowe i rekurencyjne).
Wymuszanie unikalnych dla wartości dla kluczy: UNIQUE
W języku SQL istnieje możliwość utworzenia kluczy unikalnych, które zapewniają, że wszystkie wartości w
kolumnie lub zestawie kolumn będą unikalne.
Przykład. Dodanie kolumny klucza unikatowego
ALTER TABLE pracownicy ADD
(pesel CHAR(11) UNIQUE);
Ograniczenia dynamiczne - klucze podstawowe
Uwaga. Klucze unikalne mogą być ustawiane dla kolumn z wartościami pustymi NULL. W takim
przypadku, aby wartość mogła być wprowadzona do kolumny musi być unikalna lub być pusta (NULL).
9
Wykłady z przedmiotu Podstawy baz danych – SQL firmy Oracle cz.1 – dr hab. prof. UŁ. Tadeusz Antczak
Innymi słowy, możliwe jest wprowadzenie do kolumny tabeli z kluczem unikalnym nieograniczonej liczby
wartości pustych, zaś wszystkie niepuste wartości mogą być wstawione każda tylko jeden raz.
Klucze podstawowe są specjalnymi przypadkami więzów, gdzie połączone są ograniczenia UNIQUE i
NOT NULL.
Dla kluczy podstawowych spełnione są warunki:
 może istnieć tylko jeden klucz podstawowy w jednej tabeli,
 klucze obce domyślnie tworzą relację z kluczem podstawowym tabeli,
 wiersz jest identyfikowany według klucza podstawowego, chociaż dostęp do niego można uzyskać
także w inny sposób.
Przykład:
CREATE TABLE towary (
id
NUMBER(4) PRIMARY KEY,
nazwa
VARCHAR2(15) NOT NULL,
cena
NUMBER(7,2) NOT NULL,
kategoria
VARCHAR2(30) NOT NULL,
stan_magazyn
NUMBER DEFAULT 0,
wycofany
CHAR(3) DEFAULT ‘Nie’
);
Dynamiczne ograniczenia (więzy) pomiędzy tabelami
Ograniczenia dynamiczne pomiędzy tabelami są jedną z najważniejszych funkcji, definiującą relacyjne
bazy danych i zapewniającą integralność danych.
Mechanizm służący do wymuszania więzów integralności to połączenie podstawowego lub unikalnego
klucza w tabeli po stronie jeden relacji oraz klucza obcego odwołującego się do tego klucza podstawowego
lub unikalnego w tabeli po stronie wiele. Tabele połączone mogą być identyczne i w takim przypadku
kolumna lub ich zestaw odwołuje się do innej kolumny lub ich zestawu.
Klucz obcy jest tworzony jako ograniczenie kolumny
FOREIGN KEY (nazwa_kolumny) REFERENCES nazwa_tabeli_rodzica(pole_łaczące_rodzica)
Nazywanie ograniczeń
Jeśli w tabeli wprowadzamy ograniczenia bez podawania ich nazw, wówczas system sam nadaje każdemu
ograniczeniu unikalną nazwę, która jest przechowywana w przestrzeni nazw (można je obejrzeć, gdyż są one
dostępne w Słowniku Danych – w tym celu należy wykorzystać jeden z widoków słownikowych:
USER_CONSTRAINTS lub USER_CONS_COLUMNS).
Przykład. Utwórzmy tabelę towary za pomocą następującego kodu
CREATE TABLE towary (
id
NUMBER(4) PRIMARY KEY,
nazwa
VARCHAR2(15) NOT NULL,
10
Wykłady z przedmiotu Podstawy baz danych – SQL firmy Oracle cz.1 – dr hab. prof. UŁ. Tadeusz Antczak
cena
NUMBER(7,2) NOT NULL,
kategoria
VARCHAR2(30) NOT NULL,
stan_magazyn
NUMBER DEFAULT 0,
wycofany
CHAR(3) DEFAULT ‘Nie’ CHECK(wycofany = 'Tak' OR wycofany ='Nie'));
Następnie za pomocą poniższego polecenia wyświetlamy nazwy ograniczeń i ich opis dla tej tabeli z widoku
słownikowego user_constraints:
SELECT constraint_name, search_condition
FROM user_constraints WHERE table_name='TOWARY';
Jeśli uruchomimy powyższy kod, to wydruk wyniku będzie następujący:
CONSTRAINT_NAME
SEARCH_CONDITION
-----------------------------------------------------------------------SYS_C005077
"NAZWA" IS NOT NULL
SYS_C005078
"CENA" IS NOT NULL
SYS_C005079
"KATEGORIA" IS NOT NULL
SYS_C005080
wycofany='Tak' OR wycofany='Nie'
SYS_C005081
Jedną z cech poprawnego programowania w języku SQL jest jednak nadawanie własnych nazw w stosunku
do ograniczeń, niż pozostawianie nazw nadawanych im przez system.
Przykład. Utwórzmy tabelę towary nazywając ograniczenia
CREATE TABLE towary (
id
NUMBER(4),
nazwa
VARCHAR2(15) CONSTRAINT nazwa_nn NOT NULL,
cena
NUMBER(7,2) CONSTRAINT cena_nn NOT NULL,
kategoria
VARCHAR2(30) CONSTRAINT kategoria_nn NOT NULL,
stan_magazyn
NUMBER DEFAULT 0,
wycofany
CHAR(3) DEFAULT ‘Nie’,
CONSTRAINT towary_pk PRIMARY KEY(id),
CONSTRAINT wycofany_CH CHECK(wycofany IN ('Tak', 'Nie')));
Jeśli teraz przejrzymy widok user_constraints dla tabeli towary, to otrzymamy wydruk
CONSTRAINT_NAME
SEARCH_CONDITION
------------------------------------------------------------------------------------------NAZWA_NN
"NAZWA" IS NOT NULL
CENA_NN
"CENA" IS NOT NULL
KATEGORIA_NN
"KATEGORIA" IS NOT NULL
WYCOFANY_CH
wycofany IN ('Tak', 'Nie')
TOWARY_PK
Sekwencje jako sposób generowania ciągu unikalnych liczb
W Oracle sekwencja jest obiektem, który zwraca nowy numer po każdym dostępie za pomocą wyrażenia
nazwa_sekwencji.nextval. Jeżeli po każdym wywołaniu wyrażenia nazwa_sekwencji.nextval wstawiany
11
Wykłady z przedmiotu Podstawy baz danych – SQL firmy Oracle cz.1 – dr hab. prof. UŁ. Tadeusz Antczak
jest nowy wiersz, wartości kluczy będą rozłożone jak prawdziwa sekwencja w równych odstępach. Jednak z
drugiej strony, jeśli po odczytaniu numeru z sekwencji wiersz nie będzie wstawiony lub zostanie usunięty,
pewnych numerów będzie brakować. Największą zaletą użycia sekwencji jest szybkość, ponieważ podczas
ich generowania nie trzeba korzystać z żadnej tabeli.
Przykład.
CREATE SEQUENCE sekw1
INCREMENT BY 3
START WITH 9
MINVALUE 1
MAXVALUE 9999
Powyższą sekwencję można usunąć poleceniem
DROP SEQUENCE sekw1;
Przykład.
INSERT INTO faktury
VALUES(sekw1.nextval,'Martex','Polska');
Najważniejsze opcje dla polecenia CREATE SEQUENCE są podane w poniższej tabeli:
Opcja
Cel
INCREMENT BY
Ustawia odstęp pomiędzy kolejnymi wartościami
START WITH
Ustawia wartość początkową pierwszego cyklu
MINVALUE
Ustawia minimalną wartość, którą sekwencja może wygenerować – jest ona
używana w pierwszym cyklu tylko jeśli nie określono wartości START WITH
i na początku wszystkich kolejnych cykli.
MAXVALUE
Maksymalna wartość jaką sekwencja może wygenerować, po której
rozpoczyna nowy cykl lub generowanie zostaje zakończone
NOCYKLE
Kiedy wszystkie wartości sekwencji zostaną użyte, sekwencja nie może być
zresetowana na wartość minimalną.
CYCLE
Kiedy osiągnięta zostanie wartość maksymalna sekwencja jest resetowana na
wartość minimalną i rozpoczyna się nowy cykl generowania.
CACHE
Liczba wartości sekwencji przechowywana w pamięci podręcznej.
ORDER
Kolejność sekwencji będzie zgodna z kolejnością żądań odczytu. Używana
tylko dla serwera równoległego.
NOORDER
W serwerze równoległym kolejność sekwencji nie będzie zgodna z
kolejnością żądań odczytu.
Kolumny indeksowane: polecenie CREATE INDEX
Kolumny są indeksowane za pomocą polecenia CREATE INDEX. Jest to proste polecenie z kilkoma
opcjami, z których połowa definiuje sam indeks, a druga połowa określa sposób jego przechowywania.
Podstawowa składnia instrukcji tworzącej indeks w systemie Oracle jest następująca:
12
Wykłady z przedmiotu Podstawy baz danych – SQL firmy Oracle cz.1 – dr hab. prof. UŁ. Tadeusz Antczak
CREATE [UNIQUE] INDEX nazwa_indeksu
ON nazwa_tabeli(kolumna [ASC|DESC],...);
Przykład.
CREATE INDEX towar
ON towary (nazwa ASC);
Za pomocą instrukcji powyżej jest tworzony indeks o nazwie towar w tabeli towary dla pola nazwa, przy
czy sortowanie jest rosnące.
Przykład.
CREATE INDEX towarcena
ON towary (nazwa DESC, cena DESC);
Za pomocą instrukcji powyżej jest tworzony dwupolowy indeks o nazwie towarcena w tabeli towary
składający się z pól nazwa i cena, przy czy sortowanie jest dla obu pól indeksu malejące.
Indeks można usunąć za pomocą polecenia
DROP INDEX nazwa_indeksu;
Uwaga. System zarządzania bazą danych sam zakłada indeksy dla kluczy głównych i unikalnych.
SQL firmy Oracle Modyfikacje danych w tabeli – operacje DML
Operacje DML modyfikujące dane
W języku SQL poleceniami służącymi do modyfikowania danych w tabelach są jedynie trzy operacje, które
zaliczamy do operacji DML:
 wstawianie nowego wiersza do tabeli,
 usuwanie wierszy z tabeli,
 aktualizowanie kolumn w tabeli.
Uwaga. W języku SQL wszelkie operacje wykonywane na danych są rozumiane jako operacje wykonywane
na zbiorach – lub bardziej dokładnie, pseudozbiorach, jak na przykład wszystkich wierszach tabeli.
Polecenie INSERT – wstawianie nowych wierszy danych do tabeli
Za pomocą polecenia INSERT można:
•
utworzyć nowy wiersz w tabeli bazy danych,
•
załadować wyszczególnione wartości do wszystkich lub wskazanych kolumn do wskazanej tabeli.
Składnia tego polecenia jest następująca:
INSERT INTO nazwa_tabeli (nazwa_pola1, nazwa_pola2, …, nazwa_polaN)
VALUES (wartość1, wartość2, …, wartośćN);
lub
INSERT INTO nazwa_tabeli
VALUES (wartość1, wartość2, …, wartośćN);
13
Wykłady z przedmiotu Podstawy baz danych – SQL firmy Oracle cz.1 – dr hab. prof. UŁ. Tadeusz Antczak
Przykład.
INSERT INTO towary (id, nazwa, cena, nazwa_kategorii, stan magazynu)
VALUES (104, 'chleb', 2.00, 'pieczywo',100);
Aby podstawowe polecenie INSERT działało w pożądany i prawidłowy sposób muszą być spełnione
następujące 3 warunki:
 liczba wstawianych wartości musi być dokładnie taka sama co liczba kolumn w tabeli, jeśli nie jest
podana liczba kolumn w poleceniu INSERT lub liczba kolumn wyszczególnionych na liście kolumn w
poleceniu INSERT,
 typy przekazywanych wartości muszą być zgodne z typami danych kolumn. Przede wszystkim należy
zwrócić uwagę, aby wartości typu alfanumerycznego były zawarte w pojedynczych cudzysłowach, a
daty, które nie są wprowadzane w standardowym formacie, muszą być skonwertowane przy użyciu
funkcji konwertującej TO_DATE,

wartości wstawiane do poszczególnych kolumn powinny zawierać się w dziedzinie możliwych wartości
dla każdej kolumny.
Wstawianie wierszy, które są wybrane z drugiej tabeli za pomocą podzapytania
W swojej standardowej postaci polecenie INSERT zawsze ładuje jeden wiersz danych do wskazanej w tym
poleceniu tabeli. Natomiast polecenie INSERT z podzapytaniem dodaje do wskazanej w tym poleceniu tyle
wierszy z tabeli, na której oparte jest podzapytanie, ile jest zwróconych jako wynik działania tego zapytania.
Przykład.
CREATE TABLE towarywycofane
(id NUMBER(2) CONSTRAINT towary_pk PRIMARY KEY,
nazwa VARCHAR2(30) NOT NULL,
cena NUMBER(7,2) NOT NULL,
stan NUMBER(7,2)
);
INSERT INTO towarywycofane
SELECT id, nazwa, cena, stan
FROM towary
WHERE wycofany = ‘Tak’;
Uwaga. Ostatni warunek może nie być spełniony dla działania samego polecenia INSERT, ale wstawione
wartości do kolumn nie będą należały do dziedziny kolumny, czyli nie będą sensownymi wartościami dla
danej kolumny.
Całą operację tworzenia tabeli towarywycofane i wypełniania ją wierszami dotyczącymi towarów
wycofanych, które znajdują się w tabeli towary można wykonać w znacznie prostszy sposób, używając
składni polecenia tworzenia tabeli CREATE TABLE wraz z operatorem AS i podzapytaniem
14
Wykłady z przedmiotu Podstawy baz danych – SQL firmy Oracle cz.1 – dr hab. prof. UŁ. Tadeusz Antczak
CREATE TABLE towarywycofane AS
SELECT id, nazwa, cena, stan
FROM towary
WHERE wycofany = ‘Tak’;
Usuwanie wierszy z tabeli – polecenia DELETE i TRUNCATE
Składnia polecenia DELETE:
DELETE FROM nazwa_tabeli
WHERE warunek_logiczny;
powoduje usunięcie wszystkich wierszy z tabeli wskazanej w tym poleceniu, które spełniają
warunek_logiczny w klauzuli WHERE.
Uwaga. Jeśli polecenie DELETE zostanie uruchomione bez klauzuli WHERE, wtedy jego działanie
spowoduje usunięcie wszystkich rekordów z tabeli podanej w tym poleceniu.
Przykład.
DELETE FROM towarywycofane
WHERE id IN (SELECT id
FROM towary
WHERE stan = 0);
Klauzula WHERE może zawierać podzapytanie, które może odwoływać się do wszystkich dostępnych
tabel.
Polecenie TRUNCATE TABLE nazwa_tabeli przyśpiesza proces usuwania dzięki temu, że nie zapisuje
informacji sprzed modyfikacji do przestrzeni wycofania. Ten fakt powoduje jednak, iż nie jest możliwe
przywrócenie usuniętych danych. To polecenie nie zawiera także klauzuli WHERE, a zatem zawsze usuwa
wszystkie wiersze wskazanej tabeli.
Aktualizowanie istniejących wierszy w tabeli – polecenie UPDATE
Polecenie UPDATE dotyczące aktualizacji danych różni się od pozostałych dwóch operacji DML
dotyczących modyfikacji danych w tabelach, ponieważ nie ma wpływu na liczbę wierszy w tabeli.
Składnia polecenia UPDATE jest następująca:
UPDATE nazwa_tabeli
SET lista
WHERE warunek;
gdzie:
•
nazwa_tabeli – wskazuje tabelę, w której przechowywane dane mają zostać zaktualizowane,
•
lista – wykaz kolumn, które mają zostać zaktualizowane oraz lista wartości, jakie będą przypisane
tym kolumnom,
•
warunek – warunek logiczny, którego spełnienie powoduje, iż dany wiersz dla którego jest spełniony
będzie aktualizowany.
15
Wykłady z przedmiotu Podstawy baz danych – SQL firmy Oracle cz.1 – dr hab. prof. UŁ. Tadeusz Antczak
Przykład.
UPDATE towary
SET nazwa = ‘czekolada mleczna’,
cena = 2.50
WHERE id = 1;
Polecenie UPDATE niesie ze sobą w pewnym sensie to samo niebezpieczeństwo, co polecenie DELETE –
jeśli nie zostanie załączona klauzula WHERE, zmodyfikowane zostaną wszystkie wiersze tabeli.
Instrukcja SELECT.
Pobieranie danych z tabeli – instrukcja SELECT
Podstawową, najczęściej używaną instrukcją języka SQL jest instrukcja SELECT, która służy do pobierania
danych z jednej tabeli lub większej liczby tabel (widoków). Niezależnie od liczby tabel i/lub widoków oraz
niezależnie od rodzaju operacji wykonywanych na zbiorach lub pseudozbiorach, zawsze jako wynik otrzymujemy wirtualną pojedynczą tabelę (tzw. dynamiczny zestaw wyników), którą dalej możemy przetwarzać.
Podstawowa postać instrukcji SELECT jest następująca:
SELECT lista_kolumn
FROM lista_tabel;
Uwaga. Każda instrukcja SELECT zawsze musi występować ze słowem kluczowym FROM, którego nigdy
nie można opuścić. Na końcu zawsze musi się znaleźć średnik, który kończy instrukcję.
Jeśli lista_kolumn składa się z jednej tabeli (widoku), to mamy do czynienia z zapytaniem jednotabelowym,
zaś w pozostałych przypadkach z zapytaniem wielotabelowym.
Pobieranie wszystkich wierszy i wszystkich kolumn
W celu pobrania wszystkich wierszy ze wszystkich kolumn wskazanej tabeli, zamiast wyszczególniania
wszystkich kolumn po słowie kluczowym SELECT, wystarczy wpisać symbol gwiazdki *.
Przykład.
SELECT *
FROM towary;
Pobieranie wszystkich wierszy i wybranych kolumn
W celu wybrania konkretnych kolumn i wszystkich wierszy, należy po słowie kluczowym SELECT podać
listę przecinkową zawierającą nazwy tych wybranych kolumn oddzielone przecinkami, których zawartość
chcemy odczytać.
Przykład.
SELECT nazwa, cena
FROM towary;
Uwaga. Elementy na liście przecinkowej są oddzielone przecinkami (stąd nazwa listy). Nie należy
umieszczać przecinka po ostatnim elemencie listy.
16
Wykłady z przedmiotu Podstawy baz danych – SQL firmy Oracle cz.1 – dr hab. prof. UŁ. Tadeusz Antczak
Listy przecinkowe w języku SQL. Aliasy
W języku SQL listy przecinkowe są wykorzystywane w różnych celach:
•
lista przecinkowa frazy SELECT określa, które kolumny mają być wybrane w zapytaniu,
•
lista przecinkowa frazy FROM podaje tabele, z których dane mają być wybrane,
•
lista przecinkowa frazy GROUP BY jest używana do agregowania danych w grupach wg podanych
kolumn,
•
lista przecinkowa frazy ORDER BY pozwala ustalić kryteria sortowania danych,
•
lista przecinkowa jest wykorzystywana w instrukcji IN.
Istotna odmiana listy przecinkowej powstaje w wyniku tzw. aliasingu, czyli nadawania aliasów
(„pseudonimów”) dla tych elementów, które bezpośrednio poprzedzają alias. Jeśli alias jest umieszczony za
określanym elementem, to do tego elementu można odwołać się zarówno poprzez jego nazwę lub alias (poza
pewnymi przypadkami, kiedy można odwołać się tylko za pomocą nazwy oraz pewnymi przypadkami, kiedy
można odwołać się tylko za pomocą aliasu).
Przykład. Nadanie nazwa zastępczych (aliasów) dwóm wyświetlanym kolumnom.
SELECT nazwa nazwa_towaru, cena cena_jednostkowa
FROM towary;
Klauzula DISTINCT
Domyślnie instrukcja SELECT wybiera wszystkie wiersze tabeli. To ustawienie domyślne można zmienić
umieszczając po słowie kluczowym SELECT klauzulę DISTINCT, co zapewni, że zostaną zwrócone tylko
różne wiersze. Wyrażenie SELECT DISTINCT wymusza na serwerze bazy danych przejrzenie całej tabeli
(lub całego indeksu, jeśli żądana kolumna jest zaindeksowana), co dla dużych tabel może być działaniem
czasochłonnym.
Przykład.
SELECT nr FROM pozycje_faktur;
SELECT DISTINCT nr FROM pozycje_faktur;
NR
----------1
1
1
2
2
2
3
3
4
4
4
5
NR
------------1
2
3
4
5
Sortowanie wyników przy użyciu klauzuli ORDER BY
Możliwe jest posortowanie danych zwróconych przez instrukcję SELECT według dowolnej z kolumn, która
została umieszczona na liście przecinkowej po słowie kluczowym SELECT, a nawet według kolumny
tabeli, która nie została umieszczona na tej liście.
Służy do tego klauzula ORDER BY instrukcji SELECT. Sortowanie danych można wykonać na dwa
17
Wykłady z przedmiotu Podstawy baz danych – SQL firmy Oracle cz.1 – dr hab. prof. UŁ. Tadeusz Antczak
sposoby: albo w porządku rosnącym (ustawienie domyślne) – opcja ASC lub w porządku malejącym
wartości kolumny użytej do sortowania – opcja DESC.
Przykład.
SELECT id, nazwa, cena, stan, towary
FROM towary
ORDER BY cena DESC;
Ten sam efekt sortowania można uzyskać podając w miejsce nazwy kolumny, wg której chcemy posortować
wynik, numer pozycji kolumny na liście przecinkowej po słowie SELECT. Stosujemy wtedy tzw. notację
pozycyjną, która, najogólniej rzecz ujmując, pozwala określić pozycję kolumn na podstawie ich kolejności.
Przykład.
SELECT id, nazwa, cena, stan, towary
FROM towary
ORDER BY 2;
Stałe alfanumeryczne
Język SQL nie rozróżnia stałych napisów i stałych pojedynczych znaków: oba rodzaje stałych muszą być
ujęte w pojedyncze cudzysłowy, aby możliwe było odróżnienie stałych od reszty kodu. Podobnie jak w
przypadku stałych numerycznych, za stałymi tekstowymi mogą występować aliasy.
Przykład.
SELECT id, nazwa, ‘jest zarejestrowanym w bazie klientem.’ komunikat
FROM klienci;
Zapytanie zwraca dwie pierwsze kolumny z tabeli klienci oraz kolumnę wirtualną o aliasie komunikat, która
zawiera stałą tekstową o wartości jest zarejestrowanym w bazie klientem.
ID
NAZWA
KOMUNIKAT
---------------------------------------------------------------------------------------------------1
Agrotex
jest zarejestrowanym w bazie klientem.
2
Mar-Trans
jest zarejestrowanym w bazie klientem.
3
ABC
jest zarejestrowanym w bazie klientem.
4
France Restauration
jest zarejestrowanym w bazie klientem.
5
Johns Markets
jest zarejestrowanym w bazie klientem.
Konkatenacja tekstów
Operator konkatenacji || jest jedynym operatorem dla tekstów w języku SQL, pozwala on na łączenie
(dodawanie) dwóch lub więcej kolumn lub stałych tekstowych, umożliwia przekształcenie wartości
tekstowych szeregu pól na poprawne zdanie w dowolnym języku, pozwala także realizować takie
zadania, jak wstawianie wartości kolumn w szablony listów czy na etykiety adresowe.
Przykład.
SELECT id, nazwisko, imie
SELECT id, nazwisko||’’||imie pracownik
FROM pracownicy
FROM pracownicy
ORDER BY pracownik;
ORDER BY pracownik;
18
Wykłady z przedmiotu Podstawy baz danych – SQL firmy Oracle cz.1 – dr hab. prof. UŁ. Tadeusz Antczak
Powyższe dwa zapytania zwracają listę numerów, nazwisk i imion pracowników z tabeli pracownicy.
Zauważmy, iż w drugim przypadku połączenie nazwiska i imienia zaowocowało ładniejszym wydrukiem
ID
NAZWISKO IMIE
--------------------------------------1
borsuk Jan
2
kotecki Adam
6
Kowalska Ewa
9
Kraska Katarzyna
4
Krawczyk Adam
5
LisiakAnna
1
2
6
9
4
5
ID
PRACOWNIK
------------------------------------------------borsuk Jan
kotecki Adam
Kowalska Ewa
Kraska Katarzyna
Krawczyk Adam
Lisiak Anna
Funkcje systemowe. Funkcje zaokrąglające i obcinające - ROUND i TRUNC
Funkcje systemowe zwracają następujące informacje:
 SYSDATE – aktualny czas i datę systemową,
 USER – nazwę użytkownika bazy danych,
 USERID – identyfikator systemowy użytkownika.
Funkcje zaokrąglające i obcinające – ROUND i TRUNC
Funkcje ROUND i TRUNC można wywołać albo z jednym, albo z dwoma argumentami. Przy wywołaniu z
tylko jednym argumentem funkcje zwrócą liczbę całkowitą:
TRUNC(liczba) obcina (odrzuca) cyfry na prawo od kropki dziesiętnej,
ROUND(liczba) zaokrągla liczbę do najbliższej liczby całkowitej, w górę lub w dół. Liczby dodatnie są
zaokrąglane w górę, jeśli ich część ułamkowa jest większa niż 0.5 i w dół w przeciwnym przypadku. Liczby
ujemne są zaokrąglane w dół, jeśli ich część ułamkowa jest większa niż 0.5, a w przeciwnym przypadku są
zaokrąglane w górę.
Jeśli opisane funkcje zostaną wywołane z dwoma argumentami, drugi argument, będący liczbą całkowitą,
określa, do ilu cyfr po przecinku należy wartość zaokrąglić lub obciąć.
Wycinanie podciągów tekstów – instrukcja SUBSTR
Funkcja wycinająca podciąg z tekstu SUBSTR pozwala wyciąć z napisu podciąg poczynając od wskazanej
pozycji o zadanej liczbie znaków.
Składnia funkcji SUBSTR jest następująca:
SUBSTR(wyrażenie_tekstowe, pozycja, dlugość);
gdzie:
•
wyrażenie_tekstowe – nazwa kolumny, stała lub jakiekolwiek inne prawidłowe wyrażenie tekstowe,
•
pozycja – pozycja w napisie (liczona w bajtach), od której ma się zaczynać podciąg, jeśli liczba jest
dodatnia, to wycinanie podciągu jest odbywa się od lewej strony, zaś wy przypadku, gdy argument
jest liczbą ujemną, to od końca napisu, czyli od prawej strony.
•
dlugość – długość podciągu, także w bajtach.
Przykład.
SELECT id, nazwa, kraj, SUBSTR(kraj, 1,1) litera1, SUBSTR(kraj, -1, 3) litera2
FROM klienci;
19
Wykłady z przedmiotu Podstawy baz danych – SQL firmy Oracle cz.1 – dr hab. prof. UŁ. Tadeusz Antczak
ID
NAZWA
KRAJ LITERA1 LITERA2
----------------------------------------------------------------------------1 Agrotex
Polska
P
ska
2 Mar-Trans
Polska
P
ska
3 ABC
Polska
P
ska
4 France Restauration
Francja
F
cja
5 Johns Markets
USA
U
USA
6 White Clover Markets
USA
U
USA
7 Alfreds Futterkiste
Niemcy
N
mcy
Zmiana wielkości liter
Funkcje UPPER i LOWER zwracają napis przekształcony odpowiednio na wielkie lub na małe litery. Ich
jedynym argumentem jest zawsze pewien ciąg znaków alfanumerycznych, którym może być nazwa kolumny
zawierającej dane typu tekstowego, stała tekstowa lub dowolne, poprawne wyrażenie tekstowe.
Funkcja INITCAP podmienia pierwsze litery wszystkich słów zawartych w przekazanym napisie na
wielkie.
Przykład.
SELECT id, nazwa, UPPER(nazwa) duzenazwa, LOWER(nazwa) malenazwa
FROM klienci;
ID
NAZWA
MALENAZWA
DUZENAZWA
---------------------------------------------------------------------------------------------------------------------1
Agrotex
agrotex
AGROTEX
2
Mar-Trans
mar-trans
MAR-TRANS
3
ABC
abc
ABC
4
France Restauration france-restauration FRANCE RESTAURATION
5
Johns Markets
john-markets
JOHNS MARKETS
Długość napisu – funkcja LENGTH
Funkcja LENGTH zwraca faktyczną długość napisu. Jest to użyteczne tylko w przypadku napisów o
zmiennej długości, takich jak wartości pobrane z kolumn o zmiennej długości (VARCHAR2).
W przypadku kolumn tekstowych o stałej długości, zadeklarowanych jako CHAR, funkcja LENGTH
zwróci zawsze te samą liczbę – wielkość zadeklarowaną w definicji kolumny, gdyż kolumny o stałej
długości są zawsze uzupełniane spacjami.
Przykład.
SELECT id, nazwa, LENGTH(nazwa) nazwa_dl
FROM towary;
ID
NAZWA
NAZWA_DL
------------------------------------------------------------------------1
czekolada mleczna
17
2
masło
5
3
truskawki
9
4
napój pomarańczowy
18
5
szprot w oleju
14
6
czekolada deserowa
18
20
Wykłady z przedmiotu Podstawy baz danych – SQL firmy Oracle cz.1 – dr hab. prof. UŁ. Tadeusz Antczak
Funkcje konwersji typów danych
Konwertowanie wartości na napis – funkcja TO_CHAR
Funkcja TO_CHAR pozwala przekształcić wartość na jej reprezentację znakową.
Składnia tej funkcji w najbardziej prostej postaci jest następująca:
TO_CHAR(wartość);
gdzie: wartość – parametr określający wartość przekształcaną.
W przypadku jednoparametrowym, funkcja TO_CHAR zwraca zmiennej długości napis o długości równej
tekstowej reprezentacji liczby.
Przykład. Przykład użycia jednoparametrowej funkcji TO_CHAR do konwersji liczb na tekst.
SELECT id, nazwa, cena, TO_CHAR(cena)||’ zł’ cena1
FROM towary;
ID
NAZWA
CENA CENA1
---------------------------------------------------------------------------1
czekolada mleczna
2,1
2,1 zł
2
masło
3,5
3,5 zł
3
truskawki
4,2
4,2 zł
4
napój pomarańczowy 2,4
2,4 zł
5
szprot w oleju
1,6
1,6 zł
W bardziej złożonym formatowaniu funkcja TO_CHAR posiada również drugi parametr.
TO_CHAR(wartość, maska);
maska – parametr określający maskę formatującą. Jest ona napisem, który określa sposób prezentacji wyniku
funkcji TO_CHAR. W tym przypadku funkcja ta zwraca napis stałej długości.
Przykład. Przykład użycia dwuparametrowej funkcji TO_CHAR do konwersji liczb na tekst.
SELECT id, nazwa, cena, TO_CHAR(cena)||’ zł’ cena_1, TO_CHAR(cena,’999.00’)||’ zł’ cena_2
FROM towary;
ID
NAZWA
CENA CENA1
CENA2
--------------------------------------------------------------------------------------------1
czekolada mleczna
2,1
2,1 zł
2,20 zł
2
masło
3,5
3,5 zł
3,50 zł
3
truskawki
4,2
4,2 zł
4,20 zł
4
napój pomarańczowy 2,4
2,4 zł
2,40 zł
5
szprot w oleju
1,6
1,6 zł
1,60 zł
Przekształcanie napisów w liczby – funkcja TO_NUMBER
Funkcja TO_NUMBER umożliwia wykonanie przekształcenia odwrotnego do funkcji TO_CHAR – a
mianowicie zamienia napisy w liczby.
Składnia funkcji TO_NUMBER w najczęściej używanej postaci:
TO_NUMBER(wyrazenie_tekstowe)
gdzie:
wyrazenie_tekstowe - napis, który będzie przetwarzany, stała lub wyrażenie alfanumeryczne.
21
Wykłady z przedmiotu Podstawy baz danych – SQL firmy Oracle cz.1 – dr hab. prof. UŁ. Tadeusz Antczak
Przykład.
SELECT TO_NUMBER(‘7777,77’) licz1, TO_NUMBER(‘,33333E4’) licz2
FROM dual;
Zamiana napisów na liczby przy użyciu jednoargumentowej funkcji TO_NUMBER
LICZ1
-----------7777,77
LICZ2
------------3333,3
Przekształcanie napisów w daty – funkcja TO_DATE
Funkcja TO_DATE umożliwia wykonanie przekształcenia napisów w daty.
Składnia funkcji TO_DATE w najczęściej używanej postaci:
TO_DATE(wyrazenie_tekstowe,maska_formatu_daty)
gdzie:
wyrazenie_tekstowe - napis, który będzie przetwarzany, stała lub wyrażenie alfanumeryczne,
maska_formatu daty – jest opcjonalna i formatem daty, na jaki zostanie przekształcony napis.
Parametr
Opis
YYYY
Rok czterocyfrowy
YY
Rok dwucyfrowy
Q
Kwartał
MM
Miesiąc dwucyfrowy
MONTH
Tekstowa nazwa miesiąca
D
Dzień tygodnia
DD
Dzień miesiąca
DAY
Tekstowa nazwa miesiąca
Funkcje sterowania wynikami na podstawie danych wejściowych
DECODE – funkcja kodowania wartości
Funkcję DECODE można uznać za funkcję zastępowania wartości innymi wartościami. Dla każdej wartości
w polu funkcja DECODE szuka pasujących elementów w parach wzorzec-wynik. DECODE podstawia
wartość określoną jako wynik w miejsce wzorca. Funkcja ta ma bardzo duże możliwości i można ją
stosować do wykonywania różnorodnych działań.
Składnia funkcji DECODE :
DECODE(wyrażenie, wzorzec1, wynik1, wzorzec2, wwynik2, …, wartość_domyślna)
wyrażenie – może to być kolumna, stała lub wynik konwersji.
Argumenty przekazywane funkcji DECODE to:
•
kolumna, stała lub wyrażenie, które jest przekształcane,
•
od jednej do 126 par wzorzec-wynik; jeśli nie jest używana wartość domyślna, par może być 127
•
wartość domyślna, która ma zostać zwrócona w przypadku, kiey wyrażenie, kolumna lub stała
nie zostanie znaleziona pomiędzy wzorcami.
22
Wykłady z przedmiotu Podstawy baz danych – SQL firmy Oracle cz.1 – dr hab. prof. UŁ. Tadeusz Antczak
Przykład.
SELECT DECODE(masa,’kg’, ‘kilogram’,’dag’,’dekagram’,’g’,’gram’,’??) jednostki_masy
FROM dual;
TRANSLATE – funkcja zastępowania znaków w ciągu
Funkcja TRANSLATE wykonuje operację zastępowania kolejnych znaków w ciągu.
Składnia funkcji TRANSLATE :
TRANSLATE(ciąg_znaków, jeśli, to)
Funkcja TRANSLATE analizuje wszystkie znaki argumentu ciąg_znaków, a następnie sprawdza ciąg jeśli,
poszukując określone znaku. Jeśli go znajdzie, notuje pozycję w ciągu jeśli, a następnie odczytuje tę samą
pozycję w ciągu to i wstawia odnaleziony znak do ciągu ciąg_znaków.
Przykład.
SELECT TRANSLATE(7671234, 234567890, ‘BCDEFGHIJ’) translate_znaki
FROM dual;
TRANSLATE_ZNAKI
-------------------------------------GFG1BCD
Uwaga. Jeśli znaku, który występuje w argumencie ciąg_znaków nie ma w argumencie jeśli, to znak nie
zostanie przekształcony, co ilustruje powyższy przykład w przypadku znaku 1.
Funkcja TRANSLATE z technicznego punktu widzenia jest funkcją znakową, ale może przeprowadzać
automatyczna konwersję danych i może działać z kombinacjami ciągów znaków i liczb.
Funkcje agregujące
Funkcja COUNT – zliczanie liczby wierszy
Funkcja COUNT oblicza liczbę wierszy uzyskanych w wyniku zapytania. Opcją domyślną jest zliczanie
wszystkich uzyskanych wierszy. Gdy nie zostaną nałożone żadne warunki ograniczające na dane, w
przypadku ogólnym funkcja COUNT zlicza liczbę wszystkich wierszy tabeli podstawowej lub widoku, na
którym została oparta. W ten sposób funkcja COUNT może zwracać albo liczbę wszystkich wierszy, albo
tylko liczbę różnych wartości.
Funkcja SUM – obliczanie sumy wartości
Funkcja SUM pozwala obliczyć sumę wartości wyrażenia przekazywanego jako argument. Możliwe jest
przy tym sumowanie wszystkich wartości w zwróconych wynikach lub tylko różnych wartości.
Funkcje MAX i MIN – wyznaczanie największej i najmniejszej wartości w zbiorze
Funkcje MAX i MIN zwracają odpowiednio największą i najmniejszą wartość występującą w kolumnie
użytej jako parametr tych funkcji. Jako że wybierana jest dokładnie wartość, nie ma znaczenia, czy wartość
jest wybierana spośród wszystkich wierszy, czy ze zbioru różnych wartości.
23
Wykłady z przedmiotu Podstawy baz danych – SQL firmy Oracle cz.1 – dr hab. prof. UŁ. Tadeusz Antczak
Funkcja AVG – wyliczanie wartości średniej w zbiorze
Funkcja AVG pierwsza z dostępnych funkcji statystycznych. Funkcja ta pozwala wyliczyć średnią ze zbioru
wartości. Możliwe jest także wyliczenie średniej ze zbioru wartości unikalnych, co jednak daje wyniki
niepoprawne ze statystycznego punktu widzenia. Tym niemniej, średnia dla zakresów ilości godzin i średnia
dla unikalnych zakresów godzin dają taki sam wynik, podobnie jest w przypadku kwot brutto. Przyczyną
tego stanu rzeczy jest to, że wartości tych kolumn są unikalne.
Zapytania agregujące
Funkcje agregujące zwracają informacje (obliczenia) zbiorcze (agregujące) dotyczące zgrupowanych
zestawów wierszy, a zapytania z tymi funkcjami określane są mianem zapytań agregujących. Rozróżnienie
pomiędzy zapytaniem prostym a zapytaniem agregującym jest istotne nie tylko ze względu na postać
zwracanych wyników, ale również ze względu na sama konstrukcję zapytań agregujących.
Podział zapytań agregujących.
 ze względu na liczbę przetwarzanych wierszy:
dotyczące obliczeń na wszystkich wierszach tabeli (widoku),
dotyczące obliczeń w grupach rekordów,
 ze względu na kolejność wykonywania obliczeń w stosunku do ograniczeń:
zapytania agregujące, w których najpierw wykonywane są obliczenia, a dopiero potem działają
ograniczenia,
zapytania agregujące, w których rekordy są najpierw wybierane do obliczeń przy pomocy
ograniczeń, a dopiero potem na wybranych rekordach wykonywane są obliczenia.
Zapytania agregujące dotyczące obliczeń na wszystkich wierszach
Przypadek, gdy agregowany zbiór, czyli uzyskane obliczenia, powstały w oparciu o wszystkie wiersze tabeli
podstawowej lub widoku jest ustawieniem standardowym. Przypadek taki ma miejsce, jeśli nie zostaną
narzucone żadne warunki na wiersze tabeli, wtedy funkcje agregujące wyliczą wartości na podstawie
wszystkich wierszy. Ponadto w składni instrukcji SELECT nie może pojawić się klauzula GROUP BY.
Przykład.
SELECT TO_CHAR(MAX(cena), ’999.00’)||’ zł’ max_cena, TO_CHAR(MIN(cena), ’999.00’)||’ zł’
min_cena, TO_CHAR(AVG(cena),’999.00’) ||’ zł’ cena_avg
FROM towary;
Ponieważ obliczenia dotyczą wszystkich wierszy w tabeli, zapytanie zwróci tylko jeden wiersz w zbiorze
wynikowym, co obrazuje poniższy wydruk:
MAX_CENA
-----------------4.20 zł
MIN_CENA
-----------------1.40 zł
CENA_AVG
--------------------2.40 zł
Uwaga. Dwuparametryczna funkcja TO_CHAR została dodatkowo wykorzystana w tym zapytaniu w celu
odpowiedniego sformatowania wydruku wyników.
24
Wykłady z przedmiotu Podstawy baz danych – SQL firmy Oracle cz.1 – dr hab. prof. UŁ. Tadeusz Antczak
Zapytania agregujące dotyczące obliczeń w grupach rekordów
Zapytania agregujące dotyczące obliczeń w grupach rekordów, czy inaczej mówiąc w odpowiednich
podzbiorach zbioru wszystkich wierszy tabeli podstawowej lub widoku, na którym oparte jest zapytanie
agregujące, wymagają umieszczenia w składni instrukcji SELECT klauzuli GROUP BY. Za pomocą
klauzuli GROUP BY w przypadku języka SQL jest tworzona grupa czy też podzbiór. Grupy określa się na
podstawie wspólnej cechy, jak na przykład posiadanie tej samej wartości w pewnej kolumnie.
Przykład.
KATEGORIA
LICZBA_TW
-------------------------- -----------------------art. spożywcze
4
nabiał
2
napoje
2
owoce i warzywa
1
ryby
1
SELECT kategoria, COUNT(id) liczba_tw
FROM towary
GROUP BY kategoria
ORDER BY liczba_tw DESC;
Uwaga. Jeśli w klauzuli GROUP BY zapytania występuje tylko jedna kolumna, GROUP BY zwróci
dokładnie tyle wierszy, ile istnieje w tej kolumnie różnych wartości.
Uwaga. Istnieje także możliwość grupowania według dowolnego kolumnowego wyrażenia SQL (takiego,
który tworzy kolumnę w dynamicznym zestawie wyników). W takim przypadku nie jest możliwe
umieszczenie w klauzuli GROUP BY aliasu tego wyrażenia, lecz należy wpisać dokładnie jego całą
składnię.
Zapytania agregujące dotyczące obliczeń w grupach rekordów
Istnieje możliwość grupowania wyników w poszczególnych grupach względem więcej niż jednej kolumny.
Można dokonać jednoczesnego grupowania względem więcej niż jednej kolumny, gdy w klauzuli GROUP
BY umieszczona zostanie więcej niż jedna nazwa kolumny i/lub wyrażenia kolumnowego. Kolejność
grupowania jest wyznaczona kolejnością pojawiania się nazw na liście przecinkowej klauzuli GROUP BY.
Przykład.
SELECT SUBSTR(INITCAP(nazwa),1,1) litera, kraj, COUNT(id) liczba_kl
FROM klienci
GROUP BY SUBSTR(INITCAP(nazwa),1,1), kraj
ORDER BY liczba_kl DESC;
Zapytania agregujące dotyczące obliczeń w grupach rekordów
LITERA KRAJ LICZBA_KL
----------------------------------------A
Polska
9
A
Niemcy
8
F
Niemcy
6
F
Francja
5
J
USA
5
M
Niemcy
4
M
Polska
3
W
USA
2
Z
Polska
1
Uwaga. W zapytaniu z grupowaniem maksymalna liczba zestawów zwracanych wartości wynosi iloczyn
liczby wartości we wszystkich kolumnach.
Jak z powyższych rozważań wynika, w zapytaniach agregujących z grupowaniem mamy do czynienia z
dwoma rodzajami pól:
•
kolumny pojedyncze – zawierają wartości (nazwy) grup,
•
kolumny zbiorcze – zawierają wartości zagregowane (obliczane) w ramach grup.
25
Wykłady z przedmiotu Podstawy baz danych – SQL firmy Oracle cz.1 – dr hab. prof. UŁ. Tadeusz Antczak
Jak wynika z powyżej przytoczonej wskazówki, wszystkie pola pojedyncze powinny zostać umieszczone na
liście przecinkowej klauzuli GROUP BY.
Wyrażenia warunkowe - klauzula WHERE – ograniczanie zakresu wybieranych wierszy
Klauzula WHERE:
•
jest kolejnym elementem składowym instrukcji SELECT,
•
służy do ograniczania zakresu wybieranych wierszy w dynamicznym zestawie wyników,
•
jest zdecydowanie najważniejszym sposobem użycia wyrażeń warunkowych,
•
umożliwia zdefiniowanie listy warunków określających, które z odczytanych wierszy mają znaleźć
się w dynamicznym zestawie wyników – zostają wybrane tylko te wiersze, które spełniają
zdefiniowane warunki klauzuli WHERE,
•
warunki określone w klauzuli WHERE działają przed wykonaniem obliczeń agregujących, o ile takie
mają miejsce w zapytaniu.
Przykład.
ID
NAZWA
CENA
STAN
---------------------------------------------------2
masło
3,50
120
10
śmietana
1,70
1060
SELECT id, nazwa, cena, stan
FROM towary
WHERE kategoria = ‘nabiał’;
Zaprezentowane zapytanie jest przykładem zapytania z ograniczeniami, tzw. „pasujące do wzorca”. Do
zestawu wyników są wybierane tylko te wiersze, których wartość w kolumnie kategoria jest równa ‘nabiał’,
czy inaczej mówiąc, tylko te rekordy, dla których wartości kolumny kategoria pasują do wzorca jakim jest
wartość ‘nabiał’. W niektórych przypadkach wynika to z faktu, iż nie ma sensu wyświetlać wartości
kolumny, które są znane, bo są stałe dla wszystkich wybranych rekordów.
Innym przykładem wyrażeń warunkowych, są wyrażenia „nie pasujące do wzorca”. Stanowią one w pewien
sposób negację wyrażeń warunkowych „pasujące do wzorca”. W celu stworzenia wyrażenia warunkowego
„nie pasujące do wzorca” należy wzorzec poprzedzić operatorem negacji NOT lub <>.
Przykład.
SELECT id, nazwa, cena, kategoria
FROM towary
SELECT id, nazwa, cena, kategoria
lub
FROM towary
WHERE kategoria <> ‘nabiał’;
WHERE NOT kategoria = ‘nabiał’;
ID
NAZWA
CENA KATEGORIA
--------------------------------------------------------------------1
czekolada mleczna 2,10 słodycze
3
truskawki
4,20 owoce
4
napój pomarańczowy 1,40 napoje
5
szprot w oleju
1,60 ryby
6
czekolada deserowa 2,20 słodycze
7
cukier
3,10 art. spożywcze
8
chleb
1,80 pieczywo
9
napój jabłkowy
2,00 napoje
26
Wykłady z przedmiotu Podstawy baz danych – SQL firmy Oracle cz.1 – dr hab. prof. UŁ. Tadeusz Antczak
Klauzula HAVING – odrzucanie wierszy po dokonaniu obliczeń
Aby dokonać ograniczenia rekordów w dynamicznym zestawie wyników po dokonaniu obliczeń
agregujących, należy umieścić wyrażenia warunkowe ograniczające zestaw wyświetlanych rekordów, ale
nie w klauzuli WHERE, tylko w klauzuli HAVING. Wynika to z faktu, iż warunki klauzuli WHERE są
wartościowe dla każdego pobieranego wiersza z tabeli, a zatem nie można w nich obrabiać wyników
zagregowanych
Klauzula HAVING:
 występuje w zapytaniach agregujących,
 można ją używać jedynie do wyrażeń zagregowanych, tzn. w kolumnach z obliczeniami
agregującymi oraz i kolumn umieszczonych na liście przecinkowej klauzuli GROUP BY,
 działa po wykonaniu obliczeń agregujących, dlatego możliwe jest używanie w wyrażeniach
warunkowych wartości obliczanych,
 umożliwia ograniczenie zestawu wyników po dokonaniu obliczeń agregujących,
 w składni instrukcji SELECT występuje po klauzulach WHERE i GROUP BY, o ile występują
one w zapytaniu.
Przykład.
SELECT kategoria, COUNT(id) liczba_tw
FROM towary
GROUP BY kategoria
HAVING COUNT(id) > 1
ORDER BY liczba_tw DESC;
Uwaga. Należy podkreślić, iż wyrażeniach warunkowych w klauzuli HAVING nie wolno używać aliasów.
Warto stosować również w tego typu zapytaniach sortowanie wierszy wg pola z obliczeniami.
Uwaga. Używanie klauzuli HAVING w przypadku, gdy wystarczyłoby użycie klauzuli WHERE, nie jest
poprawne, gdyż zazwyczaj tak skonstruowane zapytanie będzie wykonywać się dłużej, szczególnie w
przypadku, gdy kolumna, której dotyczy warunek, jest indeksowana. O ile WHERE działa na wszystkich
kolumnach, tzn. wyrażenia warunkowe występujące w klauzuli WHERE można definiować praktycznie dla
dowolnej kolumny, to HAVING można używać jedynie do wyrażeń zagregowanych i kolumn
umieszczonych na liście przecinkowej klauzuli GROUP BY.
Zapytania z ograniczeniami dotyczącego zakresu
Aby wybrać wartości w kolumnie, które należą do pewnego zakresu wartości, czy to liczbowych,
tekstowych, czy dat, można w tym celu równoważnie zastosować operatory większe lub równe (>=) i
mniejsze lub równe (<=) lub operator BETWEEN ... AND ...
Należy podkreślić, iż ten drugi operator do wyboru zakresów, którego składnia jest postaci
BETWEEN dolna_granica AND gorna_granica;
zawsze występuje z AND, zaś parametr dolna granica jest nie większy niż parametr gorna_granica
27
Wykłady z przedmiotu Podstawy baz danych – SQL firmy Oracle cz.1 – dr hab. prof. UŁ. Tadeusz Antczak
Przykład.
SELECT id, nazwa, stan
FROM towary
WHERE stan BETWEEN 1200 AND 2600
ORDER BY stan DESC;
Operator równy dowolnej wartości ze wskazanych – operator IN oraz operator różny od wszystkich
wskazanych wartości – operator NOT IN
Operator IN wykorzystujemy w przypadku, gdy wskazana wartość ma być równa którejś z wartości, tj. gdy
wskazana wartość ma być równa któremuś ze wzorców wyszczególnionych na liście operatora IN.
Przykład.
SELECT id, nazwa, cena, kategoria
FROM towary
WHERE kategoria IN (‘art. spożywcze’, ‘napoje’, ‘owoce i warzywa’);
Kolejność wymienia wartości na liście operatora IN jest w zasadzie dowolna, ale w przypadku większej ich
liczby należy podawać je w jakiejś kolejności, np. alfabetycznej, czy też rosnącej lub malejącej, itp. , tak,
aby nie zgubić żadnej z wartości, które powinny znaleźć się na tej liści.
Operator NOT IN działa na zasadzie zaprzeczenia operatora IN – w dynamicznym zestawie wyników są
uwzględnione te wiersze, dla których wartość wskazanej kolumny nie jest równa żadnej z wartości
występujących na liście operatora NOT IN.
Przykład.
SELECT id, nazwa, cena, stan, kategoria
FROM towary
WHERE kategoria NOT IN (‘art. spożywcze’, ‘napoje’, ‘owoce i warzywa’);
Symbole wieloznaczne i operator podobieństwa – operator LIKE
W języku SQL firmy Oracle wyróżniamy dwa symbole wieloznaczne:
 znak procenta (%) – zastępuje dowolny podciąg składający się z zera lub większej liczby znaków,
 znak podkreślenia ( _ ) – zastępuje dokładnie jeden, dowolny znak.
Symbole wieloznaczne są stosowane:
•
w wyrażeniach warunkowych w takich przypadkach, gdy znana jest tylko część wzorca. Wówczas
nieznaną część wzorca można zastąpić którymś z symboli wieloznacznych,
•
z operatorem LIKE, który pozwala porównać napis z innym, krótszym napisem zawierającym
właśnie takie uniwersalne znaki,
•
także z funkcją SUBSTR, która okazuje się przydatna w niektórych przypadkach użycia symboli
wieloznacznych (jednak na ogół to operator LIKE jest bardziej użyteczny).
28
Wykłady z przedmiotu Podstawy baz danych – SQL firmy Oracle cz.1 – dr hab. prof. UŁ. Tadeusz Antczak
Przykład.
SELECT id, nazwa, kraj
FROM klienci
WHERE nazwa LIKE ‘%Market%’;
ID
NAZWA
KRAJ
-----------------------------------------------------5
Johns Markets
USA
6
White Clover Markets
USA
Operatory logiczne
•
są także zwane operatorami boole’owskimi,
•
są zaimplementowane niemal w każdym języku programowania, a zatem także i w języku SQL,
•
są wykorzystywane w języku SQL zgodnie ze standardowymi regułami logicznymi,
•
to operatory AND, OR i NOT.
Poszczególne operatory logiczne mają następujące znaczenie:
 operator NOT – to operator logicznego przeczenia – jest on operatorem unarnym, logicznym
odpowiednikiem minusa. Operator NOT powinien być używany z umiarem – większość
przypadków jego użycia można zastąpić używając innych operatorów,
 operator AND – to dwuargumentowy operator koniunkcji – jest on używany do łączenia dwóch
wyrażeń logicznych i jest on prawdziwy, gdy oba łączone nim zdania logiczne są prawdziwe,
 operator OR – to binarny operator alternatywy – jest on używany do łączenia dwóch zdań
logicznych i jest fałszywy tylko wtedy, gdy oba zdania są fałszywe,
 język SQL nie zawiera operatora OR wyłączające, czyli operatora XOR, takiego jak bitowe OR
wyłączające w języku C (zdanie z tym operatorem jest prawdziwe, gdy oba składowe zdania
logiczne mają tę samą wartość logiczną). Tym niemniej można go zaprojektować jako złożony
warunek logiczny składający się z dwóch operatorów AND i jednego OR.
Zagnieżdżanie wyrażeń logicznych
AND
Prawda Fałsz Null
Prawda Prawda Fałsz Null
OR
Prawda
Fałsz
Null
Prawda Prawda Prawda Prawda
Fałsz
Fałsz Fałsz Fałsz
Fałsz
Prawda
Fałsz
Null
Null
Null Fałsz Null
Null
Prawda
Null
Null
Niekiedy występuje konieczność skonstruowania bardziej złożonych warunków logicznych. Przykładowo,
może to mieć miejsce w następujących przypadkach:
•
niekiedy pytanie, na które ma odpowiedzieć SQL, może być dość złożone,
•
jeśli używane jest skomplikowane złączenie, konieczne jest również użycie równie
skomplikowanych warunków w klauzuli WHERE,
•
oba warunki mogą być spełnione jednocześnie, co zdarza się zresztą dość często.
29
Wykłady z przedmiotu Podstawy baz danych – SQL firmy Oracle cz.1 – dr hab. prof. UŁ. Tadeusz Antczak
Przykład.
SELECT id, nazwa, cena, wycofany
FROM towary
WHERE (kategoria = ‘nabiał’ AND wycofany = ‘Tak’) OR (nazwa LIKE ‘napój%’ AND wycofany = ‘Nie’);
Złożone wyrażenia warunkowe można formułować nie tylko w klauzuli WHERE, ale także w klauzuli
HAVING.
Konwersja wartości NULL
Sytuacja, w której działania odbywają się na wyrażeniach, które mogą przyjmować wartość NULL, mogą
dać w efekcie „dziwne” wyniki. Aby otrzymać sensowny wynik, należy wyeliminować możliwość
pojawienia się takiej wartości w obliczeniach. Można do tego celu wykorzystać funkcję NVL, która
umożliwia przekonwertowanie wartości NULL na pewną wartość zastępczą.
Składnia tej funkcji jest następująca:
NVL(wyrażenie1, wyrażenie2)
Funkcja NVL sprawdza najpierw wyrażenie wyrażenie1. Jeśli nie ma ono wartości NULL, funkcja zwraca
jako wartość wyrażenie1. Gdy zaś wyrażenie1 ma wartość NULL, funkcja NVL zwraca jako wartość
wyrazenie2.
Warto zauważyć, że funkcja NVL nie dokonuje zmiany wartości wyrażenie1, gdy nie ma ono wartości
NULL, a jedynie zapewnia zastosowanie wartości zastępczej w czasie wykonywania programu w
przypadku, gdy przyjmuje NULL.
30