sql.pdf

(200 KB) Pobierz

Joanna Jedrzejowicz

Strona 1

2009-11-15

Jezyk SQL (strukturalny jezyk zapytan)

Na bazie danych wykonywane sa nastepujace operacje:

1. dopisywanie, usuwanie i modyfikacja danych,

2. wyszukiwanie danych spelniajacych okreslone kryteria wyboru,

3. przetwarzanie danych, np. wykonywanie obliczen z wykorzystaniem operacji logicznych,

arytmetycznych i funkcji wbudowanych w system zarzadzania bazami danych,

4. zapisywanie sekwencji operacji w postaci odpowiednich konstrukcji algorytmicznych -

makra i makrodefinicje,

5. zapamietanie danych bedacych wynikiem wyszukiwania w celu dalszego wykorzystania,

np. wydrukowania lub utworzenia nowych relacji w bazie.

Uzytkownik okreslajac, jakie operacje na danych maja byc wykonane, posluguje sie pewnym

abstrakcyjnym jezykiem, tzw. jezykiem manipulacji danymi. Jezyki te we wspólczesnych

systemach zarzadzania bazami danych maja bardzo duze mozliwosci. Pozwalaja nie tylko

wybrac jedna z dostepnych w systemie operacji, ale umozliwiaja definiowanie operacji

zlozonych, okreslania zlozonych kryteriów wyboru, w których korzysta sie z wbudowanych w

system funkcji lub funkcji definiowanych przez uzytkownika (np. w systemie Access mozna

definiowac wlasne funkcje w jezyku Visual Basic). Pozwalaja takze na tworzenie,

zapamietanie, a nastepnie uruchamianie pewnych powtarzajacych sie sekwencji w postaci

makr. Najbardziej popularnym obecnie jezykiem tego typu jest jezyk SQL (Structured Query

Language). Pierwszy standard organizacji ISO tego jezyka powstal w 1987 roku, drugi w

1989, a trzeci w 1993 roku. W ksiazce uzywamy implementacji PostgreSQL 7.1 dostepnej w

sieci pod adresem:

http://www.postgresql.org.

Wersja ta niewiele rózni sie od

Standardu. Drobne róznice dotycza jedynie regul syntaktycznych poszczególnych konstrukcji.

Podstawowymi pojeciami w jezyku SQL sa:

tabela (odpowiednik relacji),

kolumna tabeli (atrybut relacji),

wiersz tabeli (pojedyncza krotka),

wartosc wpisana w komórke na przecieciu wiersza i kolumny.

Poslugujac sie jezykiem SQL mozemy wykonac wszystkie podstawowe operacje na danych:

wyszukiwanie danych w bazie (oznaczone slowem kluczowym SELECT),

wstawianie, modyfikowanie i usuwanie danych z bazy (INSERT INTO, UPDATE,

DELETE FROM),

dodawanie do bazy danych nowych relacji, indeksów i perspektyw (CREATE TABLE,

CREATE INDEX, CREATE VIEW),

ochrona danych w bazie (GRANT, REVOKE).

sql.doc

Joanna Jedrzejowicz

Strona 2

2009-11-15

1 SQL jako jezyk definicji danych

1.1 Typy danych, tworzenie tabel

W zakresie typów danych nie ma zgodnosci wsród firm dostarczajacych systemy baz danych.

W Standardzie jezyka SQL wystepuja typy napisowe, liczbowe, typy dla oznaczania dat i

czasu. W PostgreSQL 7.1 zaimplementowano nastepujace typy danych:

typ

opis

BOOL

typ logiczny,

CHAR(n)

napis stalej dlugosci n,

VARCHAR(n)

napis zmiennej dlugosci.

TIME

czas,

DATE

data,

TIMESTAMP

czas i data,

FLOAT

liczba zmiennoprzecinkowa,

INTEGER

liczba calkowita.

Omawianie instrukcji SQL rozpoczynamy od instrukcji sluzacej do utworzenia nowej tabeli

CREATE TABLE. Pokazemy jej zastosowanie na przykladzie tworzenia tabeli o nazwie

Pracownicy

z nastepujacymi kolumnami:

identyfikator pracownika,

nazwisko pracownika,

imie pracownika,

kod dzialu, w którym pracuje,

informacja czy pracownik jest ubezpieczony.

Instrukcja sluzaca do utworzenia takiej tabeli jest nastepujaca:

CREATE TABLE

Pracownicy

( ID_prac INTEGER,

nazwisko VARCHAR(25),

imie VARCHAR(10),

kod_dz VARCHAR(10),

ubezpiecz BOOL).

Mozna okreslac domyslne wartosci, na przyklad jezeli zastapimy czesc dotyczaca kolumny

ubezpiecz

przez

ubezpiecz BOOL DEFAULT ‘t’,

to w ostatniej kolumnie bedzie wpisywana

domyslna wartosc badz podana explicite w instrukcji INSERT.

W PostgreSQL mozna takze definiowac tabele, które dziedzicza atrybuty po tabelach

zdefiniowanych wczesniej. Na przyklad tabela

Dyrektorzy

zdefiniowana jako:

CREATE TABLE

Dyrektorzy

(wynagrodzenie FLOAT,

samochod VARCHAR(10))

INHERITS (Pracownicy);

zawiera 7 atrybutów, z czego 5 jest identycznych jak w tabeli

Pracownicy

oraz dodatkowo

zawiera atrybuty

wynagrodzenie, samochod.

Tworzenie tabeli w PostgreSQL moze byc polaczone z ustalaniem wiezów spójnosci. Wiezy

spójnosci sa to warunki zaimplementowane w bazie danych, które okreslaja kiedy dane sa

zgodne z rzeczywistoscia. Wiezy ograniczaja mozliwe wartosci jakie moga pojawiac sie w

wierszach tabeli. W PostgreSQL tworzeniu tabeli moze towarzyszyc ustalenie nastepujacych

wiezów (CONSTRAINT):

sql.doc

Joanna Jedrzejowicz

Strona 3

2009-11-15

wiezy NOT NULL - w kolumnie nie jest dozwolona wartosc NULL,

wiezy CHECK - warunek, który ma byc prawdziwy dla wszystkich wierszy tabeli,

wiezy UNIQUE – wartosc atrybutu nie moze sie powtarzac w tabeli,

wiezy PRIMARY KEY – okreslaja klucz podstawowy, czyli sa polaczeniem

wiezów UNIQUE i NOT NULL,

wiezy FOREIGN KEY – okreslaja klucz obcy, czyli atrybut zwiazany z kluczem z

innej tabeli, tworzy sie przez zastosowanie REFERENCES w jednej tabeli oraz

PRIMARY KEY w drugiej.

Utworzymy nowa tabele

Praca:

CREATE TABLE

Praca

( ID_trans INTEGER PRIMARY KEY,

ID_prac INTEGER REFERENCES Pracownicy,

data DATE NOT NULL,

czas FLOAT CHECK (czas > 0),

stawka FLOAT CHECK (stawka> 0 AND stawka< 50.00)

)

oraz zmodyfikujemy instrukcje tworzenia tabeli

Pracownicy.

CREATE TABLE

Pracownicy

( ID_prac INTEGER PRIMARY KEY,

nazwisko VARCHAR(25) NOT NULL,

imie VARCHAR(10),

kod_dz VARCHAR(10),

ubezpiecz BOOL).

Zauwazmy, ze po ustaleniu powyzszych wiezów kazdej operacji wpisywania lub modyfikacji

danych bedzie towarzyszyc sprawdzanie pewnych warunków. Dla tabeli

Praca,

w której

ID_trans jest kluczem glównym, sprawdza sie, czy wartosci atrybutu ID_trans w

poszczególnych wierszach sa okreslone i unikatowe. Z kolei kazda wartosc atrybutu ID_prac

z tabeli

Praca

powinna wystepowac w tabeli

Pracownicy,

wartosc atrybutu

data

musi byc

okreslona, zas wartosci atrybutów

czas

stawka

musza spelniac warunki okreslone we

wiezach, tj.

czas

musi byc dodatni,

stawka

jest dodatnia i mniejsza niz 50. Wiersze, które nie

spelniaja warunków nie beda wpisywane do tabeli.

1.2 Zmiany w definicji tabeli

Instrukcja ALTER TABLE umozliwia wykonanie nastepujacych operacji:

- zmiana nazwy tabeli,

- zmiana nazwy kolumny w tabeli,

- dolaczenie nowej kolumny,

- dolaczenie wartosci domyslnej w kolumnie,

- usuniecie definicji wartosci domyslnej.

Instrukcje:

- ALTER TABLE Praca RENAME TO Godz_dod,

- ALTER TABLE Pracownicy RENAME COLUMN ID_prac to ID,

- ALTER TABLE Pracownicy ADD COLUMN Pesel CHAR(11),

- ALTER TABLE Pracownicy ALTER COLUMN ubezpiecz SET DEFAULT ‘t’,

- ALTER TABLE Pracownicy ALTER COLUMN ubezpiecz DROP DEFAULT,

sql.doc

Joanna Jedrzejowicz

Strona 4

2009-11-15

sluza odpowiednio do zmiany nazwy tabeli

Praca,

zmiany nazwy kolumny ID_prac w tabeli

Pracownicy,

dolaczenie nowej kolumny w tabeli

Pracownicy,

ustalenie wartosci domyslnej w

kolumnie

ubezpiecz

oraz usuniecie definicji wartosci domyslnej.

1.3 Definiowanie indeksów, klastrów i perspektyw

Obok instrukcji tworzenia tabeli w SQL dysponujemy instrukcja sluzaca do tworzenia

indeksów. Indeksy stanowia podstawowa strukture w bazach danych umozliwiajaca szybkie

wyszukiwanie informacji. Zalozenie nowego indeksu wykonuje sie przy pomocy instrukcji:

CREATE [UNIQUE] INDEX nazwa_indeksu ON nazwa_tabeli (nazwa_atrybutu, ..)

Slowo kluczowe UNIQUE gwarantuje stworzenie indeksu jednoznacznego. Kazda próba

wpisania duplikatów atrybutów okreslajacych indeks spowoduje blad. Na przyklad w tabeli

Pracownicy

mozemy zwiazac indeks z dzialem pracownika:

CREATE INDEX ind_dz ON

Pracownicy

(kod_dz);

Zauwazmy, ze potrzebne jest przypisanie nazwy kazdemu indeksowi.

Z indeksem laczy sie pojecie klastra pamieci. Wykonanie instrukcji:

CLUSTER nazwa_indeksu ON nazwa_tabeli

powoduje przemieszczenie elementów tabeli zgodnie z uporzadkowaniem przez wskazany

indeks, co ulatwia wyszukiwanie duplikatów. Wykonanie

CLUSTER ind_dz ON Pracownicy

spowoduje, ze wiersze dotyczace pracowników z tych samych dzialów beda pogrupowane i w

zwiazku z tym instrukcja typu

SELECT * FROM Pracownicy WHERE kod_dz = 10

wykona sie szybciej.

Perspektywa jest tabela wirtualna. Mozna jej uzywac tak jakby byla tabela. Wiersze

perspektywy nie sa przechowywane w bazie danych, sa jedynie wyliczane na zadanie.

Perspektywy sluza do dostosowania bazy danych do potrzeb róznych grup uzytkowników.

Stanowia 'perspektywe' z jakiej dana grupa widzi baze danych. Perspektywy umozliwiaja

wykonywanie operacji podobnych do operacji wykonywanych na tabelach, to jest operacji

wyszukiwania i modyfikowania danych. Ponizsza instrukcja definiuje perspektywe

Pracownicy-sprzedaz

zlozona z tych wierszy z tabeli

Pracownicy,

które dotycza osób

zatrudnionych w dziale sprzedazy.

CREATE VIEW Pracownicy-sprzedaz AS

SELECT nazwisko, imie FROM Pracownicy WHERE kod_dz='sprzedaz';

1.4 Liczniki i wyzwalacze

W PostgreSQL oprócz tabel, indeksów i perspektyw mozna takze definiowac liczniki,

wyzwalacze i funkcje. Licznik jest obiektem, który automatycznie zwieksza swoja wartosc po

jego kazdorazowym odczycie. Liczniki sa uzywane do generowania unikalnych

identyfikatorów wierszy. Licznik definiuje sie przy pomocy polecenia:

CREATE SEQUENCE nazwa_licznika

Trzy funkcje standardowe operuja na licznikach:

- nextval(nazwa) – zwraca kolejna wartosc licznika i jednoczesnie modyfikuje licznik,

- currval(nazwa) – zwraca wartosc z poprzedniego odwolania nextval, bez zmiany wartosci

licznika,

- setval(nazwa, N) – ustala wartosc licznika na N.

sql.doc

Joanna Jedrzejowicz

Strona 5

2009-11-15

Jezeli utworzymy licznik

CREATE SEQUENCE numer_prac;

to mozemy uzyc go przy tworzeniu tabeli:

CREATE TABLE

Pracownicy

( ID_prac INTEGER DEFAULT nextval(‘numer_prac’),

nazwisko VARCHAR(25) NOT NULL,

imie VARCHAR(10),

kod_dz VARCHAR(10),

ubezpiecz BOOL).

Uwaga: jezeli licznik nie zostal zaincjalizowany przy pomocy

setval,

to domyslnie przyjmuje

sie wartosc 1 jako poczatkowa.

Wyzwalacze sa procedurami uruchamianymi automatycznie na skutek zdarzen zachodzacych

w bazie danych. Sluza do oprogramowania wiezów spójnosci i do oprogramowania stalych

czynnosci, które powinny byc wykonywane w kazdej aplikacji korzystajacej z bazy danych.

Wyzwalacze obu typów moga byc uruchamiane w wyniku nastepujacych polecen: INSERT

(wstawianie), DELETE (usuwanie), UPDATE (modyfikacja). Wyzwalacz definiuje sie za

pomoca polecenia:

CREATE TRIGGER nazwa_wyzwalacza {BEFORE|AFTER}

{zdarzenie_1 [OR zdarzenie_2 [OR zdarzenie_3]]}

ON nazwa_tabeli FOR EACH ROW

EXECUTE PROCEDURE nazwa_procedury (argumenty,...);

gdzie zdarzenie_1, zdarzenie_2, zdarzenie_3 jest jednym z polecen INSERT, DELETE,

UPDATE, zas nazwa_procedury jest nazwa procedury zdefiniowanej w jezyku C.

Na przyklad instrukcja:

CREATE TRIGGER usuwanie_prac AFTER DELETE

Pracownicy

FOR EACH ROW EXECUTE PROCEDURE komunikat();

powoduje utworzenie wyzwalacza, który bedzie wywolywac procedure (która musi byc

wczesniej zdefiniowana)

komunikat

po kazdej operacji usuwania danych z tabeli

Pracownicy.

Uzytkownik PostgreSQL ma mozliwosc definiowania w SQL swoich wlasnych funkcji,

których moze nastepnie uzywac w wyrazeniach i zapytaniach . Definicja funkcji jest postaci:

CREATE FUNCTION nazwa_funkcji ([typ_arg {,typ_arg}])

RETURNS typ_wyniku

AS ciag_zapytan_sql

LANGUAGE 'sql';

Zauwazmy, ze kazda funkcja zwraca pewien wynik - jest to wartosc zwracana przez ostatnie

zapytanie z ciagu zapytan SQL (ciala funkcji). Odwolanie do argumentu funkcji w ciele

funkcji jest postaci $n, gdzie n jest numerem argumentu.

Przyklad. Instrukcja

CREATE FUNCTION premia(FLOAT) RETURNS FLOAT AS

'SELECT 0.25 * $1;'

LANGUAGE 'sql'

definiuje funkcje jedno-argumentowa premia; cialo tej funkcji sklada sie tylko z jednego

zapytania. Mozna jej uzyc w innych zapytaniach, np.

SELECT premia(Praca.stawka) AS odpowiedz.

sql.doc

Plik z chomika:

masterdigits1

sql.pdf

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: