Wykład 1

SQL - język relacyjnych i obiektowo-relacyjnych baz danych – podstawowe konstrukcje

 

Streszczenie

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.

 


1.1 Bazy danych

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.
 


1.2 Relacyjny model danych

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     10
Wś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  BOSTON
W 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  9999
 
W 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
  • Jej wartości nie opisują cechy pracownika.
  • Reprezentują związek danego pracownika z departamentem, o którym informacja znajduje się w innej tabeli i tylko korzystając z identyfikatora możemy rozpoznać w innej tabeli wiersz właściwego departamentu i odczytać o nim informacje.
  • Istotne jest więc, aby identyfikator ten jednoznacznie określał dany departament. W modelu relacyjnym nie ma innej możliwości identyfikacji wiersza tylko poprzez wartości kolumn, które jednoznacznie identyfikują wiersz.

Klucz główny i jednoznaczny

  • W tabeli Emp kluczem głównym jest jednoznaczny numer pracownika Empno. Ename nie musi być kluczem!
  • W tabeli Dept kluczem głównym jest jednoznaczny numer departamentu Deptno.
  • W tabeli Salgrade kluczem głównym jest jednoznaczny numer zaszeregowania Grade.

Klucz obcy

  • W tabeli Emp kluczem obcym jest numer Deptno, którego wartości pochodzą z kolumny Deptno w tabeli Dept.

    Na przykład, wartość 10 występująca w wierszu pracownika o nazwisku "King" tabeli Emp stanowi odwołanie do wiersza w tabeli Dept, w którym są zapisane informacje o departamencie o numerze 10 i nazwie "Accounting":

       "Pracownik King pracuje w departamencie Accounting"

  • W tabeli Emp kluczem obcym jest też numer Mgr, którego wartości pochodzą z kolumny Empno w tej samej tabeli Emp.

    Na przykład, wartość 7839 występująca w wierszu pracownika o nazwisku "Blake" tabeli Emp stanowi odwołanie do wiersza w tej samej tabeli Emp, w którym są zapisane informacje o pracowniku o numerze 7839 i nazwisku "King":

       "Przełożonym pracownika Blake jest pracownik King"

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.
 


1.3 Historia języka SQL

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.

  


1.4 Definiowanie danych

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.
 

Standardowe typy danych

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]

 

Tworzenie tabeli

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ń.

  1. Zapis: nazwa,...  daje możliwość użycia jednej lub więcej nazw rozdzielonych przecinkami.
  2. Meta-nawiasy [...] oznaczają opcjonalne fragmenty.

Tabele przykładowej bazy danych można utworzyć za pomocą następujących instrukcji:
 
CREATE TABLE Dept(
  Deptno NUMBER(2) PRIMARY KEY,
  Dname VARCHAR2(14),
  Loc VARCHAR2(13)
);

CREATE TABLE Emp(
  Empno NUMBER(4) PRIMARY KEY,
  Ename VARCHAR2(10),
  Job VARCHAR2(9),
  Mgr NUMBER(4) REFERENCES Emp,
  Hiredate DATE,
  Sal NUMBER(7,2),
  Comm NUMBER(7,2),
  Deptno NUMBER(2) NOT NULL REFERENCES Dept
);

CREATE TABLE Salgrade(
  Grade NUMBER(2) PRIMARY KEY,
  Losal NUMBER(7,2) NOT NULL,
  Hisal NUMBER(7,2) NOT NULL,
);

 

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

można przekonać się, jaki jest schemat utworzonej tabeli. Np. wykonanie instrukcji 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

Zwróćmy uwagę na przedrostek SQL>, który sygnalizuje linię poleceń w programie SQL*Plus. Będziemy go dalej pomijać.
 

Usuwanie tabeli

Usunięcie tabeli jest prostsze niż jej założenie:
 
DROP TABLE nazwa_tabeli;
np.

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;

 

Zmiana schematu tabeli

Schemat istniejącej tabeli może być zmieniony za pomocą instrukcji ALTER TABLE.

ALTER TABLE nazwa_tabeli  klauzula_zmiany_schematu;

Może to być:

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;

Zakładanie indeksów (Oracle)

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] ,...);

Dwie opcje występujące w składni to:

· 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;

System zarządzania bazą danych zwykle sam zakłada indeksy dla kluczy głównych i jednoznacznych.

 


1.5 Wprowadzanie zmian w tabelach bazy danych
 

Wstawianie danych

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ść,...);

Przykład
 
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ść,...);

Przykład
INSERT INTO Emp(Empno, Ename, Deptno)
VALUES (9901, 'Kowalski', 10);


Modyfikowanie danych

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];

Przykład
 
Podnieś wszystkim sprzedawcom (SALESMAN) zarobki Sal o 10%.

Wykonujemy instrukcję:

UPDATE Emp SET Sal = Sal*1.1
WHERE Job = 'SALESMAN';


Usuwanie danych

Dane (wiersze) można usuwać z tabel w bazie danych kierując do systemu następującą instrukcję:

DELETE FROM nazwa_tabeli
[WHERE warunek];

Przykład
 
Usuń wszystkich pracowników, którzy nie mają określonego stanowiska (Job IS NULL).

Wykonujemy instrukcję:
 

DELETE FROM Emp
WHERE Job IS NULL;

Zatwierdzanie zmian w bazie danych

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];

Można też zrezygnować z wprowadzenia zmian do bazy danych, wycofując je za pomocą instrukcji:
 
ROLLBACK [WORK];

Więcej na temat tych instrukcji znajdzie się na trzecim wykładzie w ramach omawiania tematu transakcji na bazie danych.
 


1.6 Proste zapytania

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       1400
Warunek 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           1300
Należ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

Kolejność tę można zmienić za pomocą nawiasów.

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
      7902
Gdybyś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],...];

np. używając w wyrażeniu standardowych funkcji Oracle, w celu obliczenia okresu zatrudnienia pracownika od daty jego zatrudnienia do dnia dzisiejszego – w pełnych latach:

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                17
Przy 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.

2. Jeśli występuje klauzula WHERE, do każdego wiersza danej tabeli zastosuj warunek WHERE. Pozostaw wiersze dające wartość True usuwając wiersze dające False lub Null.

3. Dla każdego pozostającego wiersza oblicz wartości wyrażeń na liście SELECT.

4. Jeśli po SELECT występuje DISTINCT, usuń duplikaty wśród wynikowych wierszy.

5. Jeśli występuje klauzula ORDER BY, wykonaj sortowanie wynikowych wierszy zgodnie ze specyfikacją.

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

 


1.7 Wyrażenia

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ń =, <>, <, <=, >, >=
W Oracle argumenty operatorów porównań muszą być wyrażeniami, w Standardzie mogą być listami wyrażeń tej samej długości (wówczas porównania odbywają się po składowych).
 

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.

CASE, DECODE i NVL

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

który ma następującą interpretację:
jeśli wyrażenie1 = wyrażenie2, to wartością wyrażenia jest wyrażenie3, w przeciwnym razie, jeśli wyrażenie1 = wyrażenie4, to wartością wyrażenia jest wyrażenie5 itd…

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)

której wartością jest V2, jeśli V1 jest Null, w przeciwnym razie V1

np. funkcje NVL(Sal,0) i NVL(Stopień, 'Brak') umożliwiają zinterpretowanie wartości Null jako odpowiednio 0 bądź 'Brak'.
 

Konwersja typu

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)

oznaczająca zmianę typu danych wyrażenie na typ_danych. A więc na przykład, aby dokonać konwersji napisu na liczbę, piszemy

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.
 

Konkretne wartości bieżące

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.

 


1.8 Podsumowanie

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.

 


1.9 Słownik pojęć

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.

 


1.10 Zadania

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.



Strona przygotowana przez Lecha Banachowskiego. Ostatnia aktualizacja - 12/31/06 .