W tym wykładzie rozpoczynamy naukę podstawowego języka baz danych - SQL. Warto przypomnieć, że wprowadzenie do języka SQL było już przedstawione na wykładach z "Relacyjnych Baz Danych". Część materiału będzie więc teraz przypomnieniem.
Rozpoczniemy od krótkiego wprowadzenia w dziedzinę baz danych w tym od przypomnienia definicji relacyjnego modelu danych (z pierwszego wykładu z przedmiotu "Relacyjne bazy danych"). Następnie przejdziemy do nauki języka SQL przedstawiając podstawowe informacje na temat typów danych, instrukcji tworzących, modyfikujących i usuwających tabele bazy danych oraz instrukcji, które wstawiają, usuwają i modyfikują dane w tabelach w bazie danych. Na temat wymienionych instrukcji będzie też mowa w kolejnych wykładach. Na tym wykładzie rozpoczniemy też dyskusję centralnego tematu w dziedzinie baz danych, a mianowicie jak wydobywać dane z tabel w bazie danych. Wspomnimy także o programie SQL*Plus, który jest standardowym, tekstowym interfejsem do baz danych Oracle.
Informacje (dane) są takim samym zasobem firmy, jak każdy inny (np. pracownicy, materiały, urządzenia) i wymagającym, tak samo jak one, zarządzania. Informacja może mieć wartość dla firmy tylko wtedy, gdy jest dokładna i dostępna wtedy, kiedy trzeba. Baza danych stała się standardową metodą strukturalizacji zarządzania informacją w większości organizacji.
Baza danych dotyczy zawsze pewnego fragmentu rzeczywistości związanego z firmą, organizacją lub innym działającym lub planowanym systemem. Stanowi kolekcję danych, których zadaniem jest reprezentowanie tego fragmentu rzeczywistości. Baza danych jest na ogół częścią systemu informacyjnego, obsługującego zapotrzebowania informacyjne związane z tym fragmentem rzeczywistości.
W kontekście systemów informacyjnych warto podkreślić wszechobecność informacji elektronicznej w codziennym życiu, dostępnej zaczynając od serwisów działających na przeglądarkach internetowych, a kończąc na skomplikowanych aplikacjach naukowych, jak projekty badania genomu człowieka i projekt obserwacji Ziemi prowadzony przez NASA. Warto tu też wspomnieć o zastosowaniach, które obejmują duże zbiory danych, takie jak biblioteki elektroniczne, multimedialne bazy danych i interaktywne wideo.
Każda baza danych jest oparta na ustalonym modelu danych określającym podstawowe zasady strukturalizacji i posługiwania się zbiorami danych. Obecnie najpopularniejszym modelem jest relacyjny model danych, który za chwilę przypomnimy. W wykładzie 7 będzie mowa o rozszerzeniu modelu relacyjnego do obiektowo-relacyjnego modelu danych. W dodatkowym wykładzie przedstawimy najnowszy model danych – oparty na języku XML.
Każda baza danych jest zarządzana przez wyspecjalizowany system, który zarządza zapisem i przechowywaniem danych w pamięci komputera (przede wszystkim na dyskach), zabezpiecza dane przed utratą i niepowołanym lub nieodpowiednim użyciem oraz udostępnia dane zapisane w bazie danych przy użyciu języka wysokiego poziomu opartego na przyjętym modelu danych.
Począwszy od tego wykładu aż do wykładu 7 będziemy prezentować język SQL służący do komunikacji z bazami danych (oparty na relacyjnym i obiektowo-relacyjnym modelu danych).
W wykładach od 8 do 13 będziemy studiować architekturę i zasady działania systemu zarządzania bazą danych.
Natomiast wykłady od 14 do 15 i dodatkowe 2 i 3 zawierają materiał dotyczący trzech specyficznych rodzajów systemów baz danych,
odpowiednio systemów rozproszonych, hurtowni danych oraz baz danych opartych na języku XML.
Relacyjny model danych pojawił się po raz pierwszy w artykule naukowym Edgara Codda w 1970 roku. W terminologii matematycznej baza danych jest zbiorem relacji. Stąd historycznie pochodzi nazwa relacyjny model danych i relacyjna baza danych. W matematyce definiuje się relację jako podzbiór iloczynu kartezjańskiego zbiorów wartości. Reprezentacją relacji jest dwuwymiarowa tabela złożona z kolumn i wierszy.
Przyjmuje się następujące założenia o tabeli jako podstawowym elemencie relacyjnego modelu danych:
Przedstawimy teraz trzy tabele, które będą nam towarzyszyć w przykładach w większości wykładów.
W tabeli EMP zapisujemy informacje o pracownikach hipotetycznej firmy. Jeden wiersz reprezentuje dane jednego pracownika. W szczególności wynika z niego, że pracownik o nazwisku "King" pracuje w departamencie o numerze 10, piastuje stanowisko "PRESIDENT" oraz jego zarobki wynoszą 5000.
EMP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ------- --------- ---- --------- ----- ---- ------ 7839 KING PRESIDENT NULL 17-NOV-81 5000 NULL 10 7698 BLAKE MANAGER 7839 01-MAY-81 2850 NULL 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 NULL 10 7566 JONES MANAGER 7839 02-APR-81 2975 NULL 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7900 JAMES CLERK 7698 03-DEC-81 950 NULL 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7902 FORD ANALYST 7566 03-DEC-81 3000 NULL 20 7369 SMITH CLERK 7902 17-DEC-80 800 NULL 20 7788 SCOTT ANALYST 7566 09-DEC-82 3000 NULL 20 7876 ADAMS CLERK 7788 12-JAN-83 1100 NULL 20 7934 MILLER CLERK 7782 23-JAN-82 1300 NULL 10Wśród wartości występujących w tabeli EMP znajduje się charakterystyczna dla baz danych tzw. pseudo-wartość NULL reprezentująca "brak wartości" w danym polu. Jest to coś innego niż po prostu puste miejsce lub zero. W szczególności możemy więc z tabeli EMP odczytać, że pracownik o nazwisku "King" nie ma przełożonego oraz nie dotyczy go prowizja (COMM).
W tabeli DEPT zapisujemy informacje o departamentach (działach) hipotetycznej firmy.
DEPT
DEPTNO DNAME LOC ------ ----------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTONW tabeli SALGRADE zapisujemy informacje o klasach zaszeregowania zarobków pracowników hipotetycznej firmy.
SALGRADE
GRADE LOSAL HISAL ----- ----- ----- 1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999W szczególności możemy z tabeli SALGRADE odczytać, że grupa zaszeregowania 1 obejmuje zarobki od 700 do 1200.
Tabele EMP, DEPT i SALGRADE stanowią przykładową bazę danych używaną do nauki
SQL w materiałach firmy Oracle.
Znaczenie kolumny Deptno w tabeli Emp
|
|
|
Każdy schemat bazy danych można przedstawić graficznie. Tabele są reprezentowane jako ramki, powiązania między tabelami typu klucz obcy -> klucz główny są reprezentowane za pomocą strzałek. Na rysunku 1.1 są przedstawione tabele naszej przykładowej bazy danych oraz związki między nimi. Związek tabeli SALGRADE z tabelą EMP nie jest typu klucz obcy -> klucz główny – został opisany tekstowo jako warunek między wartościami w kolumnach w odpowiadających sobie wierszach obu tabel.
Rys. 1.1 Diagram tabel EMP, DEPT i SALGRADE. Oznaczenie PK
wskazuje na klucz główny. Oznaczenie FK z numerem oznacza klucz obcy. Diagram
został utworzony przy użyciu
narzędzia do tworzenia diagramów informatycznych o nazwie Microsoft Visio.
Modele danych poprzedzające model relacyjny, to jest model sieciowy i hierarchiczny, opierały się na pojęciu pliku jako zbioru rekordów, przy czym rekordy były rozszerzane o pola wskaźnikowe kierujące do rekordów w tym samym lub innych plikach. Nie było potrzeby wprowadzania nowego języka programowania – wystarczały tradycyjne języki programowania jak Cobol i PL/I.
Dla baz danych opartych na relacyjnym modelu danych opracowano specjalny język o nazwie SQL (Structured Query Language - Strukturalny Język Zapytań), umożliwiający dostęp i przetwarzanie danych w bazie danych na poziomie obiektów modelu relacyjnego tj. tabel.
Ponadto zaistniała potrzeba określenia sposobu użycia instrukcji tego języka w programach konwencjonalnych języków programowania jak C, C++, Java, Visual Basic. Kwestia ta została również objęta Standardem SQL.
Znamienne też jest powszechne stosowanie narzędzi do generowania aplikacji klienckich jak Oracle Forms, Visual C++, Visual Basic, MS Access bez potrzeby sięgania do tradycyjnego sposobu programowania.
Język SQL został przyjęty jako standard przez społeczność informatyczną. Jego historia sięga początku lat siedemdziesiątych, kiedy to w laboratorium badawczym IBM w San Jose powstał język o nazwie Sequel.
Pierwszy standard międzynarodowej organizacji ISO języka SQL powstał w 1987 roku, drugi w 1989, a trzeci w 1992 roku (SQL2), czwarty w 1999 roku (SQL’1999); nad następnym standardem uwzględniającym między innymi cechy obiektowe trwają prace. Oprócz samego Standardu SQL załączamy także konstrukcje z najbardziej znanego obecnie systemu relacyjnych baz danych - Oracle9i.
Byłoby dobrze, chociaż nie jest to konieczne, aby Czytelnik miał dostęp do komputera z zainstalowanym systemem Oracle. Oprogramowanie serwera Oracle w postaci wersji bezpłatnej, ważnej przez pewien określony czas, można uzyskać pod internetowym adresem: http://technet.oracle.com/.
PJWSTK zapewnia dwa interfejsy do łączenia się z bazą danych Oracle z zewnątrz. Jednym z nich jest iSQL*Plus (interfejs WWW), a drugim SQL*Plus (standardowy, interakcyjny interfejs do bazy danych Oracle). Oczywiście zamiast SQL*Plus możemy użyć innego oprogramowania klienckiego np. TOAD, TOra. Obydwa sposoby dostępu zapewniają podobne możliwości, przy czym do korzystania z iSQL potrzebna jest tylko przeglądarka natomiast do korzystania z SQL*Plus konieczne jest jego zainstalowanie na swoim komputerze.
W naszym kursie używamy przykładowej bazy danych Oracle. Można ją założyć wykonując skrypt z poleceniami SQL z pliku o nazwie demobld.sql.
W tym punkcie przedstawimy podstawowe informacje na temat typów danych, instrukcji tworzących, modyfikujących i usuwających tabele w bazie danych. Wspomnimy także o programie SQL*Plus będącym standardowym tekstowym interfejsem do bazy danych Oracle.
W zakresie typów danych nie ma, niestety, zgodności wśród firm dostarczających systemy baz danych. Poniżej wyliczamy typy danych, które występują w Standardzie, a oprócz tego zamieszczamy informacje o typach danych występujących w Oracle.
Z punktu widzenia tego wykładu wchodzenie w szczegóły implementacyjne typów danych nie jest istotne – do przedstawienia instrukcji języka SQL używać będziemy trzech typów: napisowego VARCHAR2, liczbowego NUMBER i datowego DATE według składni systemu Oracle. Dla pełności obrazu wyliczamy pełny zestaw standardowych typów danych.
1. Typy napisowe (stałe są zapisywane w pojedynczych apostrofach, np.: 'Jan III', 'Kowalski '): · CHARACTER(N), CHAR(N) - napis znakowy stałej długości N (w Oracle CHAR(N)). · NATIONAL CHARACTER(N), NATIONAL CHAR(N) - napis znakowy stałej długości N zapisany w alfabecie narodowym (w Oracle NCHAR(N)). · CHARACTER VARYING(N), VARCHAR(N) - napis znakowy zmiennej długości (w Oracle VARCHAR(N) i zalecane VARCHAR2(N)). · NATIONAL CHARACTER VARYING(N), NATIONAL CHAR VARYING(N), NCHAR VARYING(N) - napis znakowy zmiennej długości zapisany w alfabecie narodowym (w Oracle NVARCHAR2(N)). · BIT(N) - ciąg bitów długości N. Ciągi bitów są głównie używane do reprezentowania danych graficznych i dźwiękowych. · BIT VARYING(N) - ciąg bitów zmiennej długości (w Oracle RAW(n) i LONG RAW). 2. Typy numeryczne: · NUMERIC, DECIMAL, NUMERIC(p,s) - typ dziesiętny (W Oracle NUMBER(p,s)). · FLOAT, REAL - typ zmiennopozycyjny. · INTEGER - typ całkowity. 3. Typy dat: · DATE (data), TIME (godzina), TIMESTAMP (data i godzina) - typ rozszerzający dane zawarte w typie danych DATE (W Oracle nie ma typu TIME). 4. Typy przedziału czasu - zaczynające się od słowa INTERVAL. 5. W Oracle są jeszcze typy: · LONG - dokumenty tekstowe (w jednej tabeli może być tylko jedna kolumna tego typu). · BLOB – duże obiekty binarne. · BFILE - duże obiekty binarne zapisywane w plikach. · CLOB – duże obiekty tekstowe. · NCLOB – duże obiekty tekstowe zapisane w alfabecie narodowym. · ROWID - identyfikatory (adresy) wierszy tabel. 6. Nowa wersja standardu SQL’1999 wprowadza cztery nowe typy danych: · LOB – duże obiekty albo binarne, albo tekstowe. · BLOB - duże obiekty binarne – podtyp typu LOB. · CLOB – duże obiekty tekstowe – podtyp typu LOB. · BOOLEAN – wartości logiczne true, false i unknown. |
Tak jak to podaliśmy powyżej, do ilustracji omawianych konstrukcji języka SQL będziemy używać przykładowej bazy danych dostarczanej przez firmę Oracle. Tabela Emp zawiera informacje o pracownikach, tabela Dept informacje o działach, w których pracują pracownicy a tabela Salgrade informacje o klasach zaszeregowania zarobków pracowników. Wyniki przykładowych zapytań będą obliczane względem ich zawartości podanych powyżej.
Zauważmy, że związek atrybutu Sal w tabeli Emp z wartościami w tabeli Salgrade nie jest standardowy, tzn. jest innego typu niż klucz-obcy->klucz-główny. Mianowicie, zarobki pracownika Emp.Sal są zaliczane do grupy Salgrade.Grade takiej, że:
Emp.Sal należy do przedziału: [Salgrade.Losal, Salgrade.Hisal] |
Tabela jest tworzona za pomocą następującej instrukcji:
CREATE TABLE nazwa_tabeli( nazwa_kolumny typ_danych więzy_spójności,...); |
Rodzaje więzów spójności omówimy po przedstawieniu przykładu. Przypomnijmy najpierw podstawowe zasady notacji definiowania składni instrukcji i wyrażeń.
nazwa,...
daje możliwość użycia jednej lub więcej nazw
rozdzielonych przecinkami.
Tabele przykładowej bazy danych można utworzyć za pomocą następujących instrukcji:
CREATE TABLE Dept(
CREATE TABLE Salgrade( |
Oto znaczenie użytych powyżej, podstawowych więzów spójności:
NOT NULL
– pseudo-wartość Null nie jest dozwolona w danej kolumnie,
PRIMARY KEY
– dana kolumna stanowi klucz główny,
REFERENCES nazwa_tabeli
– dana kolumna stanowi klucz obcy odwołujący się do klucza głównego podanej tabeli.
Używając instrukcji programu SQL*Plus:
describe tabela
|
describe
Dept
spowoduje wyświetlenie na ekranie:SQL> describe Dept Name Null? Type ------------------ ----------- ----------- DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13) |
Natomiast wyświetlenie zawartości tabeli Dept odbywa się za pomocą instrukcji:
SELECT * FROM Dept;
co po wprowadzeniu przykładowych danych dałoby (w programie SQL*Plus) następujący rezultat:SQL> SELECT * FROM Dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON |
SQL>
, który sygnalizuje linię poleceń w programie SQL*Plus.
Będziemy go dalej pomijać.
Usunięcie tabeli jest prostsze niż jej założenie:
DROP TABLE nazwa_tabeli; |
DROP TABLE Emp;
Jednak gdy inne tabele mają klucze obce odwołujące się do danej tabeli, jej usunięcie nie powiedzie się. Np. w naszej przykładowej bazie danych nie powiodłaby się instrukcja:
DROP TABLE Dept;
gdyż w tabeli Emp jest klucz obcy Deptno odwołujący się do klucza głównego tabeli Dept. Dopiero użycie dodatkowej klauzuli
CASCADE
w Standardzie i CASCADE CONSTRAINTS
w Oracle spowoduje usunięcie tabeli
Dept oczywiście razem z więzami spójności referencyjnej dla kolumny
Deptno.
DROP TABLE Dept CASCADE CONSTRAINTS;
Schemat istniejącej tabeli może być zmieniony za pomocą instrukcji ALTER TABLE.
ALTER TABLE nazwa_tabeli klauzula_zmiany_schematu; |
Oto przykład dodania nowej kolumny w Standardzie
ALTER TABLE Emp ADD COLUMN Pesel CHAR(11);
i w Oracle
ALTER TABLE Emp ADD Pesel CHAR(11);
Nowa kolumna zostaje wypełniona pseudo-wartościami NULL.
Oto przykład zmiany typu danych kolumny w Oracle
ALTER TABLE Emp MODIFY Pesel VARCHAR2(20);
Jest też możliwe usunięcia kolumny:
ALTER TABLE Emp DROP Pesel;
Używając polecenia ALTER TABLE można definiować tabele z cyklicznymi powiązaniami REFERENCES między sobą, co nie jest możliwe za pomocą samego CREATE TABLE. Na przykład, jeśli klucz obcy w tabeli
A odwołuje się do klucza głównego w tabeli B, a klucz obcy w tabeli
B odwołuje się do klucza głównego w tabeli A, to mamy do czynienia z cyklicznymi powiązaniami REFERENCES. W takim przypadku najpierw wykonujemy instrukcje CREATE TABLE nie definiując kluczy obcych. Następnie, za pomocą instrukcji ALTER TABLE wprowadzamy więzy REFERENCES odwołujące się do istniejących już w tym momencie tabel.
Na przykład, zakładając, że utworzyliśmy tabelę Emp bez definiowania klucza obcego do tabeli Dept, moglibyśmy ten klucz obcy wprowadzić wykonując instrukcję:
ALTER TABLE Emp
ADD CONSTRAINT fk_Emp_dept
FOREIGN KEY (Deptno) REFERENCES Dept;
Indeksy stanowią podstawową strukturę danych w bazach danych umożliwiającą:
· sprawdzanie warunku jednoznaczności klucza (głównego, jednoznacznego);
· szybkie wyszukiwanie informacji w tabelach.
Jednakże Standard języka SQL, nie określa jak zakładać indeks. Pozostawia opracowanie składni producentom systemów baz danych. Definiując indeks podajemy jego nazwę, nazwę tabeli, do której odnosi się oraz wyróżnione kolumny tej tabeli nazywane pozycjami indeksu. W zamierzeniu indeks jest strukturą danych, która ma umożliwiać szybkie wyszukiwanie wierszy tabeli w oparciu o wartości pozycji indeksu.
Poniżej przedstawiamy składnię stosowaną w systemie Oracle.
CREATE [UNIQUE] INDEX nazwa_indeksu ON nazwa_tabeli(kolumna [ASC|DESC] ,...); |
· UNIQUE
– wartości pozycji indeksu muszą jednoznacznie określać wiersz tabeli; indeks taki nazywa się
indeksem jednoznacznym; opcja ta umożliwia zatem realizację więzów spójności UNIQUE;
· ASC
(domyślne) i DESC
– w jakiej kolejności utrzymywać pozycje indeksu: rosnącej czy malejącej. Odpowiednie uporządkowanie pozycji indeksu może przyśpieszyć wypisywanie wierszy tabeli w wymaganym porządku.
Przykład
Instrukcja
CREATE UNIQUE INDEX Ind_Emp_num ON Emp(Empno);
tworzy indeks jednoznaczny na kolumnie Empno tabeli Emp. To znaczy, system będzie
sprawować kontrolę nad tym, aby wartości w kolumnie
Empno nie powtarzały się.
Natomiast, instrukcja
CREATE INDEX Ind_Emp_naz ON Emp(Ename DESC);
tworzy zwykły indeks na kolumnie Ename tabeli Emp, tzn. wartości w kolumnie
Ename mogą się powtarzać. Pozycje tego indeksu są trzymane w odwrotnym porządku leksykograficznym.
Indeks można usunąć z bazy danych za pomocą następującej instrukcji:
DROP INDEX nazwa_indeksu; |
Do tabeli w bazie danych poszczególne wiersze są wstawiane za pomocą instrukcji INSERT. Jej podstawowa postać jest następująca:
INSERT INTO nazwa_tabeli VALUES (wartość,...); |
Wstaw do bazy nowego pracownika o nazwisku 'Kowalski'. |
Wykonujemy instrukcję:
INSERT INTO Emp VALUES
(9901, 'Kowalski', 'SALESMAN', 9345, '23-JAN-90',300,100,12);
Można też dokonywać wstawienia ograniczając się do kolumn o podanych nazwach
(do nie wymienionych system sam wstawia pseudo-wartość
Null):
INSERT INTO nazwa_tabeli(nazwa_kolumny,...) VALUES (wartość,...); |
INSERT INTO Emp(Empno, Ename, Deptno)
VALUES (9901, 'Kowalski', 10);
System zarządzania bazą danych dokonuje modyfikacji wartości zapisanych w bazie danych, jeśli otrzyma do wykonania instrukcję UPDATE:
UPDATE nazwa_tabeli SET nazwa_kolumny = wyrażenie,... [WHERE warunek]; |
Podnieś wszystkim sprzedawcom (SALESMAN) zarobki Sal o 10%. |
Wykonujemy instrukcję:
UPDATE Emp SET Sal = Sal*1.1
WHERE Job = 'SALESMAN';
Dane (wiersze) można usuwać z tabel w bazie danych kierując do systemu następującą instrukcję:
DELETE FROM nazwa_tabeli [WHERE warunek]; |
Usuń wszystkich pracowników, którzy nie mają określonego stanowiska
(Job IS NULL ). |
Wykonujemy instrukcję:
DELETE FROM Emp
WHERE Job IS NULL;
Instrukcje INSERT, DELETE i UPDATE nie dokonują same trwałych zmian w bazie danych. Aby zmiany wprowadzone przez nie utrwalić, należy wykonać instrukcję:
COMMIT [WORK]; |
ROLLBACK [WORK]; |
Więcej na temat tych instrukcji
znajdzie się na trzecim wykładzie w ramach omawiania tematu transakcji na
bazie danych.
Centralną instrukcją języka SQL jest instrukcja służąca do wydobywania danych z bazy danych. Jest nią instrukcja SELECT, określająca, z jakich tabel w bazie danych mają być sprowadzone dane, jakie warunki mają spełniać i w jakiej dokładnie postaci mają się pojawić przed użytkownikiem (aplikacją użytkownika). Instrukcja SELECT składa się z kilku części nazywanych klauzulami. Omówimy je po kolei.
Najprostsza postać instrukcji SELECT jest następująca:
SELECT [DISTINCT] nazwa_kolumny,... FROM nazwa_tabeli [WHERE warunek]; |
Obie klauzule SELECT i FROM są wymagane w każdym zapytaniu – natomiast klauzule DISTINCT i WHERE są opcjonalne.
Przykład
Wypisz dane o nazwiskach, zarobkach i stanowiskach pracowników firmy. |
Piszemy:
SELECT Ename, Sal, Job
FROM Emp;
Oto wynik tego zapytania na ekranie:
ENAME SAL JOB ---------- ---------- --------- SMITH 800 CLERK ALLEN 1600 SALESMAN WARD 1250 SALESMAN JONES 2975 MANAGER MARTIN 1250 SALESMAN BLAKE 2850 MANAGER CLARK 2450 MANAGER SCOTT 3000 ANALYST KING 5000 PRESIDENT TURNER 1500 SALESMAN ADAMS 1100 CLERK JAMES 950 CLERK FORD 3000 ANALYST MILLER 1300 CLERK
Istnieje także skrócona postać listy SELECT ze znakiem *, co oznacza wypisanie danych z wszystkich kolumn tabeli, a więc instrukcja:
SELECT *
FROM Emp;
wypisze całą zawartość tabeli, taką jak podaliśmy na początku wykładu.
W Standardzie jest jeszcze krótsza postać wypisania zawartości tabeli, nie występująca w Oracle, a równoważna powyższej instrukcji SELECT:
TABLE Emp; |
Uwaga: Chociaż nie jest to formalnie wymagane, jednak ze względu na czytelność zapisu zapytania, wskazane jest poprzedzanie każdej nazwy kolumny nazwą tabeli, z której dana kolumna pochodzi, lub ewentualnie nazwą jej aliasu nadanego w klazuli FROM. |
Tak więc nasze pierwsze zapytanie moglibyśmy zapisać na dwa sposoby:
SELECT Emp.Ename, Emp.Sal, Emp.Job
FROM Emp;
albo
SELECT e.Ename, e.Sal, e.Job
FROM Emp e;
Będziemy to traktować jako standard zapisu zapytania i będziemy się do niego stosować na wykładach. Jako przykłady innych użytecznych standardów mogą służyć: pisanie słów kluczowych dużymi literami a nazw kolumn i tabel małymi ale rozpoczynając od dużej.
Przykład
Wypisz dane o nazwiskach, zarobkach i stanowiskach pracowników firmy, którzy pracują w dziale o numerze 10. |
W tym przykładzie chodzi o ograniczenie wypisywanych wyników lub, wyrażając się inaczej, o ich selekcję za pomocą pewnego warunku. Warunek ten zapisujemy w klauzuli WHERE:
SELECT Emp.Ename, Emp.Sal, Emp.Job
FROM Emp
WHERE Emp.Deptno = 10;
Oto wynik tego zapytania:
ENAME SAL JOB ---------- ---------- --------- CLARK 2450 MANAGER KING 5000 PRESIDENT MILLER 1300 CLERK
Ze względu na możliwość wystąpienia pseudo-wartości Null, warunki logiczne oprócz
wartości True i False mogą także dawać Null
(pseudo-wartość Null
omówiono na pierwszym wykładzie z RBD). Przyjmuje się, że jeśli w predykacie
porównania występuje pseudo-wartość Null, jego wartość też jest Null.
A więc wartością warunków zarówno Sal = Null
jak i Null = Null
jest Null. Przy SELECT wypisywane są tylko te wiersze, dla których warunek WHERE przyjmuje wartość
True.
Warunek w klauzuli WHERE może mieć bardziej skomplikowaną postać.
W szczególności może zawierać porównanie wartości z dwóch różnych kolumn np. zarobków pracownika z jego prowizją:
SELECT Emp.Ename, Emp.Sal, Emp.Comm
FROM Emp
WHERE Emp.Sal <= Emp.Comm;
Oto wynik tego zapytania:
ENAME SAL COMM ---------- ---------- ---------- MARTIN 1250 1400Warunek WHERE może też być koniunkcją (AND), alternatywą (OR) bądź negacją (NOT) innych warunków logicznych.
Oto przykład koniunkcji dwóch warunków - w wypisywanych wierszach zarówno zarobki mają być większe niż 1100, jak i stanowisko ma być równe 'CLERK':
SELECT Emp.Empno, Emp.Ename, Emp.Job, Emp.Sal
FROM Emp
WHERE Emp.Sal >= 1100 AND Emp.Job='CLERK';
EMPNO ENAME JOB SAL ---------- ---------- --------- ---------- 7876 ADAMS CLERK 1100 7934 MILLER CLERK 1300Należy pamiętać, że przy obliczaniu wartości wyrażeń obowiązuje ustalone pierwszeństwo stosowania operatorów. W przypadku operatorów logicznych obowiązuje następująca kolejność:
1. NOT 2. AND 3. OR |
Powtarzające się wiersze nie są automatycznie eliminowane z wyników zapytania. Słowo DISTINCT oznacza eliminację powtarzających się wierszy.
Przykład
Wypisz identyfikatory osób, które mają podwładnych, tzn. które są kierownikami pewnych pracowników. |
SELECT DISTINCT Emp.Mgr
FROM Emp;
MGR ---------- 7566 7698 7782 7788 7839 7902Gdybyśmy nie zamieścili słowa DISTINCT, dla każdego pracownika zostałby wypisany identyfikator jego kierownika, a więc wiele identyfikatorów powtarzałoby się.
W miejscu nazwy kolumny może wystąpić wyrażenie. Wyrażeniom na liście SELECT mogą zostać nadane nazwy czyli aliasy. Alias może mieć postać prostego identyfikatora, czyli napisu złożonego z liter, cyfr i znaków podkreślenia albo tzw. ograniczonego identyfikatora, czyli dowolnego napisu ograniczonego podwójnymi cudzysłowami, np. "Zarobki pracownika:".
W szczególności w ograniczonym identyfikatorze mogą występować spacje, które są niedozwolone w prostym identyfikatorze.
Wyniki zapytania mogą zostać posortowane w porządku rosnącym ASC (domyślnie) lub malejącym – wtedy używamy słowa kluczowego DESC.
SELECT [DISTINCT] wyrażenie [[AS] alias],... FROM nazwa_tabeli [WHERE warunek] [ORDER BY wyrażenie [ASC|DESC],...]; |
SELECT Emp.Empno, Emp.Ename,
Trunc(Months_Between(Sysdate,Emp.Hiredate)/12) Zatrudnienie
FROM Emp
ORDER BY Zatrudnienie DESC;
EMPNO ENAME ZATRUDNIENIE ---------- ---------- ------------ 7369 SMITH 19 7499 ALLEN 19 7521 WARD 19 7698 BLAKE 19 7782 CLARK 19 7566 JONES 19 7654 MARTIN 18 7934 MILLER 18 7902 FORD 18 7839 KING 18 7844 TURNER 18 7900 JAMES 18 7788 SCOTT 17 7876 ADAMS 17Przy wypisywaniu wyniku, jako etykiety kolumn są używane aliasy kolumn. Mogą one też wystąpić w klauzuli ORDER BY, jak w powyższym przykładzie, gdzie sortujemy dane o pracownikach według stażu pracy. W klauzuli WHERE, jak również w klauzulach GROUP BY i HAVING, które zostaną opisane później, nie można ich używać.
Aby zrozumieć, jak działa instrukcja SELECT, warto pamiętać o jej semantyce określonej przez zasady jej wykonywania. Zasady te są przydatne do zrozumienia działania instrukcji SELECT, natomiast system bazy danych wykonuje na ogół zapytanie w inny, szybszy sposób, korzystając przede wszystkim z założonych indeksów. Temat ten zostanie przedstawiony na dalszych wykładach.
Zasady wykonywania prostego zapytania (semantyka)
1. Weź tabelę podaną w klauzuli FROM.
|
Zauważmy, że każda instrukcja SELECT określa funkcję, która wejściowej tabeli (jednej lub więcej jak okaże się na następnym wykładzie), znajdującej się na liście FROM przyporządkowuje nową tabelę (bez nazwy) o kolumnach zdefiniowanych przez elementy listy SELECT - zobacz Rys. 1.2. Oznacza to jednorodny charakter danych i wyników. Wyniki jednej instrukcji SELECT mogą posłużyć jako dane do innej instrukcji SELECT. Przy opisie semantyki języka SQL nie musimy sięgać do pojęć znajdujących się poza relacyjnym modelem danych – w szczególności nie musimy odwoływać się ani do pojęć języka programowania ani do pojęć systemu komputerowego. Właśnie ta własność łącznie z prostotą i naturalnością struktury danych jaką jest tabela, stała się podstawą niezmiernej popularności relacyjnego modelu danych.
Rys. 1.2 Zapytanie przekształca wejściowe tabele A i B na tabelę wyjściową C
W wyrażeniach mogą się pojawiać operatory wykonujące operacje na danych. Są to:
1. Operatory arytmetyczne: +, -, *, /
2. Operator łączenia napisów ||
Np. zestawienie nazwiska pracownika z zajmowanym przez niego stanowiskiem
'Osoba '|| Ename || ' pracuje na stanowisku ' || Job
3. Operatory porównań =, <>, <, <=, >, >=
4. Operator przynależności do listy wartości x [NOT] IN (x1,....)
Np. Kolor IN ('Czarny', 'Biały', 'Czerwony')
5. Operator należenia do przedziału x [NOT] BETWEEN z AND y
Np.
Sal BETWEEN 1000 AND 2000
W Oracle x,y,z muszą być wyrażeniami, w Standardzie mogą być listami wyrażeń tej samej długości
(porównania odbywają się po składowych).
6. Operator wzorca w tekście x [NOT] LIKE y
W argumencie y mogą występować symbole uniwersalne sygnalizujące możliwość wystąpienia jednego znaku lub ciągu znaków; są dwa rodzaje znaków uniwersalnych: znak podkreślenia
_
oznaczający dowolny jeden znak oraz znak %
oznaczający dowolny ciąg
znaków. Gdy Ename = 'Kowalski', to oba predykaty
Ename LIKE 'Kowal%'
i Ename LIKE 'Kowalsk_'
są spełnione.
7. Operator testujący Null x IS [NOT] NULL
8. Operatory logiczne NOT, AND, OR
Każdy konkretny system dostarcza dużej liczby pomocniczych funkcji na danych. Kilka z nich, występujących w Standardzie i w Oracle, przedstawimy poniżej.
W Standardzie występuje następujący schemat funkcji warunkowych:
CASE wyrażenie1 WHEN wyrażenie2 THEN wyrażenie3 [WHEN wyrażenie4 THEN wyrażenie5 …] [ELSE wyrażenie] END |
W Oracle występuje taka sama funkcja, tylko ma nieco odmienną składnię:
DECODE(wyrażenie1, wyrażenie2, wyrażenie3, wyrażenie4, wyrażenie5... [,wyrażenie] ) |
W Standardzie funkcja CASE zamiast z wyrażeniem może występować z predykatem:
CASE WHEN predykat THEN wyrażenie ... [ELSE wyrażenie] END |
Przy prezentacji danych użyteczną operacją jest reprezentowanie pseudo-wartości Null jako konkretnej wartości. W Standardzie można to wykonać stosując uprzednio zdefiniowane funkcje:
CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2
Natomiast w Oracle używamy specjalnej funkcji:NVL(V1,V2) |
np. funkcje NVL(Sal,0)
i NVL(Stopień, 'Brak')
umożliwiają zinterpretowanie wartości
Null jako odpowiednio 0 bądź 'Brak'.
Kolejną funkcją (grupą funkcji) jest funkcja służąca do zmiany typu wyrażenia.
W Standardzie występuje konstrukcja:
CAST(wyrażenie AS typ_danych) |
CAST('123' AS INTEGER)
lub liczbę na napis
CAST(123 AS VARCHAR(4))
W Oracle natomiast jest kilka osobnych funkcji określających w sposób jawny, na jaki typ danych ma być dokonana
konwersja TO_CHAR, TO_DATE,
TO_NUMBER,... Na przykład,TO_DATE('11-12-97', 'mm-dd-yy')
zmienia typ napisowy na datowy, przy czym informacja o dacie ma być odczytywana zgodnie z formatem podanym jako drugi argument, czyli z numerem miesiąca zamieszczonym na początku, potem następującym dniem i rokiem reprezentowanym przez dwie cyfry.Jest też szereg funkcji zwracających konkretne wartości, np. bieżącą datę:
w Standardzie
Current_date
w Oracle
Sysdate
lub aktualnego użytkownika (programu, w którym jest stosowana ta funkcja, może używać wielu użytkowników):
User
zarówno w Standardzie, jak i w Oracle.
Na tym wykładzie przedstawiliśmy podstawowe informacje na temat typów danych, instrukcji tworzących, modyfikujących i usuwających tabele bazy danych oraz instrukcji, które wstawiają, usuwają i modyfikują dane w tabelach bazy danych.
Rozpoczęliśmy dyskusję najważniejszego tematu w dziedzinie baz danych, a mianowicie jak wydobywać dane z bazy danych.
CREATE TABLE – instrukcja SQL powodująca utworzenie tabeli o podanej nazwie i schemacie.
DROP TABLE - instrukcja SQL powodująca usunięcie tabeli o podanej nazwie.
ALTER TABLE - instrukcja SQL powodująca zmianę schematu tabeli o podanej nazwie.
CREATE INDEX - instrukcja SQL powodująca utworzenie indeksu dla podanej tabeli.
DROP INDEX - instrukcja SQL powodująca usunięcie indeksu o podanej nazwie.
INSERT - instrukcja SQL powodująca wstawieniego jednego lub więcej wierszy do tabeli w bazie danych.
UPDATE - instrukcja SQL powodująca modyfikację wartości w jednym lub więcej wierszach tabeli bazy danych.
DELETE - instrukcja SQL powodująca usunięcie jednego lub więcej wierszy z tabeli bazy danych.
COMMIT - instrukcja SQL powodująca zatwierdzenie zmian dokonanych w bazie danych.
ROLLBACK - instrukcja SQL powodująca wycofanie zmian dokonanych w bazie danych.
SELECT - instrukcja SQL powodująca sprowadzenie danych z bazy danych.
Operatory SQL - operatory ułatwiające układanie zapytań. Takie jak BETWEEN, IN, LIKE.
I. Ułóż i przetestuj następujące zapytania:
1. Wyznacz pracowników, którzy zarabiają powyżej 2000 i których nazwiska nie zaczynają się na literę A. Uporządkuj wyniki według daty zatrudnienia.
2. Wyznacz pracowników, którzy zarabiają poniżej 1500 zł i których nazwiska nie kończą się na literę S. Uporządkuj wyniki według daty zatrudnienia.
3. Wyznacz wszystkie stanowiska (bez powtórzeń).
4. Wyznacz pracowników, którzy nie dostali prowizji.
5. Wypisz identyfikatory osób, które nie mają kierownika. Dołącz ich zarobki i stanowiska. Uporządkuj wyniki według zarobków.
II. Ułóż i przetestuj następujące zapytania:
1. Wyznacz pracowników, którzy zarabiają poniżej 2000zł i których nazwiska zaczynają się na literę A. Uporządkuj wyniki według zarobków.
2. Wyznacz pracowników, którzy zarabiają powyżej 1500 zł i których nazwiska kończą się na 'SKI'. Uporządkuj wyniki według daty zatrudnienia.
3. Wyznacz wszystkie zarobki (bez powtórzeń).
4. Wyznacz pracowników, którzy dostali prowizję.
5. Wypisz identyfikatory osób, które mają kierownika i prowizję. Dołącz ich zarobki i stanowiska. Uporządkuj wyniki według zarobków.