PostgreSQL 7

PostgreSQL 7.1.3 Tutorial

Tłumaczenie: Aleksander Machniak, 2001

Spis treści

PostgreSQL 7.1.3 Tutorial...................................................................................................................................................... 1
Rozdział 1. SQL..................................................................................................................................................................... 2
1.1. Relacyjny model danych .................................................................................................................................................. 3
1.2. Opis formalny modelu relacyjnego................................................................................................................................... 4

1.2.1. Domeny kontra typy danych ..................................................................................................................................... 4

1.3. Operacje na modelu relacyjnym ....................................................................................................................................... 5

1.3.1. Relacyjna algebra..................................................................................................................................................... 5
1.3.2. Relacyjny Calculus................................................................................................................................................... 7
1.3.3. Tuple Relational Calculus......................................................................................................................................... 7
1.3.4. Relacyjna Algebra - Relacyjny Calculus ................................................................................................................... 8

1.4. Język SQL....................................................................................................................................................................... 9

1.4.1. Select....................................................................................................................................................................... 9

1.4.1.1. Proste Select'y .................................................................................................................................................. 9
1.4.1.2. Złączenia ........................................................................................................................................................ 11
1.4.1.3. Funkcje (operatory) agregujące ....................................................................................................................... 13
1.4.1.4. Funkcje agregujące i grupowanie .................................................................................................................... 13
1.4.1.5. Having............................................................................................................................................................ 14
1.4.1.6. Podzapytania .................................................................................................................................................. 15
1.4.1.7. Podzapytania w FROM ................................................................................................................................... 16
1.4.1.8. Union, Intersect, Except.................................................................................................................................. 16

1.4.2. Definicje danych .................................................................................................................................................... 17

1.4.2.1. Create Table ................................................................................................................................................... 17
1.4.2.2. Typy danych w SQL ....................................................................................................................................... 18
1.4.2.3. Create Index ................................................................................................................................................... 18
1.4.2.4. Create View.................................................................................................................................................... 18
1.4.2.5. Drop Table, Drop Index, Drop View ............................................................................................................... 19

1.4.3. Manipulowanie danymi .......................................................................................................................................... 19

1.4.3.1. Insert Into ....................................................................................................................................................... 19
1.4.3.2. Update............................................................................................................................................................ 20
1.4.3.3. Delete............................................................................................................................................................. 20

1.4.4. Zbiory systemowe .................................................................................................................................................. 20
1.4.5. Wbudowany SQL................................................................................................................................................... 21

Rozdział 2. Architektura....................................................................................................................................................... 22
2.1. Podstawy architektury Postgres'a. .................................................................................................................................. 22
Rozdział 3. Rozpoczynamy .................................................................................................................................................. 23
3.1. Ustawienia środowiska .................................................................................................................................................. 23
3.2. Uruchamiamy Interactive Monitor (psql)........................................................................................................................ 24
3.3. Zarządzanie bazą danych ............................................................................................................................................... 24

3.3.1. Tworzenie bazy danych .......................................................................................................................................... 24
3.3.2. Dostęp do bazy danych........................................................................................................................................... 25
3.3.3. Usunięcie bazy danych ........................................................................................................................................... 26

Rozdział 4. Język zapytań..................................................................................................................................................... 27
4.1. Interactive Monitor ........................................................................................................................................................ 27
4.2. Zasady........................................................................................................................................................................... 28
4.3. Tworzenie tabel ............................................................................................................................................................. 28
4.4. Wstawianie danych do tabeli.......................................................................................................................................... 28
4.5. Przeglądanie tabeli......................................................................................................................................................... 29
4.6. Przekierowanie zapytań wybierających .......................................................................................................................... 29
4.7. Połączenia tabel............................................................................................................................................................. 30
4.8. Uaktualnienia ................................................................................................................................................................ 30
4.9. Kasowanie..................................................................................................................................................................... 31
4.10. Funkcje agregujące ...................................................................................................................................................... 31
Rozdział 5. Zaawansowane właściwości Postgres SQL ......................................................................................................... 33
5.1. Dziedziczenie ................................................................................................................................................................ 33
5.2. Wartości złożone ........................................................................................................................................................... 34

5.2.1. Tablice................................................................................................................................................................... 34

5.3. Więcej zaawansowanych właściwości ............................................................................................................................ 35

Rozdział 1. SQL

Spis treści

1.1.

Relacyjny model danych

1.2.

Opis formalny modelu relacyjnego

1.3.

Działania na modelu relacyjnym

1.3.1.

Relacyjna algebra

1.3.2.

Relacyjny Calculus

1.3.3.

Tuple Relational Calculus

1.3.4.

Relacyjna algebra - Relacyjny Calculus

1.4.

Język SQL

1.4.1.

Wyszukiwanie (Select)

1.4.2.

Definicje danych

1.4.3.

Manipulacja danymi

1.4.4.

Zbiory systemowe

1.4.5.

Wbudowany SQL

Rozdział niniejszy przedstawia zarys relacyjnych baz danych. Nie musisz go czytać, więc jeśli się
znudzisz lub będziesz chciał od razu zacząć od przykładów, przejdź dalej i powróć jak będziesz miał
więcej czasu i cierpliwości.

Ten materiał jest częścią "Master Thesis" Stefana Simkovics'a (

Simkovics, 1998

SQL stał się najpopularniejszym relacyjnym językiem zapytań. Nazwa jest skrótem od Structured Query
Language (Strukturalny Język Zapytań). W 1974 r. Donald Chamberlin i inni zdefiniowali SEQUEL
(Structured English Query Language). Po raz pierwszy został wprowadzony w prototypie IBM zwanym
SEQUEL-XRM w 1974-75 r. W latach 1976-77 powstała poprawiona wersja SEQUEL nazwana
SEQUEL/2 , a nazwę tą następnie zmieniono na SQL.

Nowy prototyp System R wprowadzony przez IBM w 1977 r. obejmował większą część SEQUEL/2
(teraz SQL) jak i wprowadzał wiele zmian do SQL. Sukces i akceptacja pierwszych użytkowników
spowodował, że IBM rozpoczęła wprowadzanie pierwszych komercyjnych produktów opartych na
technologii Systemu R, zawierających SQL.

Przez nastepne lata IBM i inni producenci stworzyli produkty SQL'owe, takie jak: SQL/DS (IBM), DB2
(IBM), ORACLE (Oracle Corp.), DG/SQL (Data General Corp.), and SYBASE (Sybase Inc.).

Obecnie SQL jest standardem oficjalnym. W 1982 r. Komitet Baz Danych X3H2 Amerykańskiego
Instytutu Standardów ANSI (American National Standards Institute) przedstawił propozycję standardu
języka relacyjnego, który zawierał dialekt IBM i został przyjęty w 1986 r. Rok później standard ten został
również zaakceptowany przez Międzynarodową Organizację Standaryzacji ISO (International
Organization for Standardization). Oryginalna wersja standardu jest nieformalnie nazywana SQL/86. W
1989 r. standard został rozszerzony i znów nieformalnie nazwany SQL/89.

Komitety ISO i ANSI przez wiele lat pracowały nad zdefiniowaniem bardziej rozbudowanej wersji
standardu nazwanej SQL2 lub SQL/92. Ta wersja w 1992 r. została przyjęta jako: "International Standard
ISO/IEC 9075:1992, Database Language SQL". Szczegółowy opis SQL/92 podano w

Date and Darwen,

1997

. Podczas pisania tego dokumentu trwały prace nad SQL3, który sprawi że SQL będzie językiem

kompletnym, zawierającym np. możliwość tworzenia zapytań krzyżowych. Jest to bardzo złożone
zadanie i dlatego nie jest znany czas zakończenia tych prac.

1.1. Relacyjny model danych

Jak wspomniano wcześniej, SQL jest językiem relacyjnym. Oznacza to, że jest oparty na relacyjnym
modelu danych, opublikowanym po raz pierwszy przez E.F.Codd'a w 1970 roku. Opis formalny modelu
relacyjnego przedstawimy później (w rozdziale

Opis formalny modelu relacyjnego

) ale najpierw musimy

spojrzeć na to z bardziej intuicyjnego punktu widzenia.

Relacyjna baza danych jest bazą postrzeganą przez użytkowników jako zbiór tabel (i nic więcej tylko
tabel). Tabela składa się z wierszy i kolumn, gdzie każdy wiersz reprezentuje rekord, a każda kolumna
atrybuty rekordów zawartych w tabeli.

Baza danych Suppliers (Dostawcy) i Parts (Produkty)

przedstawia

przykład bazy skłądającej się z trzech tabel.



SUPPLIER (Dostawca) jest tabelą przechowującą numer (SNO), nazwisko (SNAME) i miasto
(CITY) dostawcy.



PART (Produkt) jest tabelą przechowującą numer (PNO), nazwę (PNAME) i cenę (PRICE)
produktu.



SELLS (Sprzedaż) przechowuje informacje o tym, który produkt (PNO) jest sprzedany przez
którego dostawcę (SNO). Służy do połączenia pozostałych tabel wzajemnie.

Przykład 1

-1. Baza danych Dostawcy i Produkty

SUPPLIER: SELLS:
SNO | SNAME | CITY SNO | PNO
----+---------+-------- -----+-----
1 | Smith | London 1 | 1
2 | Jones | Paris 1 | 2
3 | Adams | Vienna 2 | 4
4 | Blake | Rome 3 | 1
3 | 3
4 | 2
PART: 4 | 3
PNO | PNAME | PRICE 4 | 4
----+---------+---------
1 | Screw | 10
2 | Nut | 8
3 | Bolt | 15
4 | Cam | 25

Tabele PART i SUPPLIER są encjami a SELLS jest relacją między określonym produktem a dostawcą.

Zanim zobaczymy relacje na tabelach przestudiujmy teorię modelu relacyjnego.

1.2. Opis formalny modelu relacyjnego

Matematyczna koncepcja przedstawia model relacyjny jako czysto teoretyczną relację, która jest
podzbiorem iloczynu kartezjańskiego z listy dziedzin. Stąd bierze się nazwa tego modelu. W
rzeczywistości dziedzina, inaczej domena, jest po prostu zbiorem wartości. Na przykład, dziedziną jest
zbiór liczb całkowitych lub zbiór łańcuchów znakowych o długości 20 znaków, czy też zbiór liczb
rzeczywistych.

Iloczyn kartezjański dziedzin

, ...

, zapisany jako

× ... ×

jest zbiorem wszystkich krotek

, ...

, takich jak

∈

, ...

∈

Na przykład, gdy mamy

=2,

{0,1}

oraz

{a,b,c}

wtedy

jest

{(0,a),(0,b),(0,c),(1,a),(1,b),(1,c)}

Relacją jest dowolny podzbiór iloczynu kartezjańskiego jednej lub wielu dziedzin:

⊆

× ... ×

Na przykład

{(0,a),(0,b),(1,a)}

jest relacją; tzn. zbiorem

wspomnianych wyżej.

Elementy relacji są nazywane krotkami. Każda relacja jakiegoś iloczynu kartezjańskiego

× ... ×

jest zbiorem krotek

Relacja może być przeglądana jak tabela (przypomnij sobie

Bazę danych Dostawcy i Produkty

, gdzie

każda krotka jest przedstawiana jako wiersz, a każda kolumna jest częścią krotki). Nazwy kolumn
nazywane atrybutami odgrywają główną rolę w definicji schematu relacji.

Schematem relacji

jest dowolny zbiór atrybutów

, ...

. Jest dziedzina

, dla każdego atrybutu

1 <=

, skąd brane są wartości atrybutów. Często schemat relacji zapisuje się w ten sposób:

R(A

, ... A

)

1.2.1. Domeny kontra typy danych

W tym rozdziale często mówiliśmy o dziedzinach (domenach). Formalnie dziedzina jest zbiorem wartości
(np. zbiór liczb całkowitych albo rzeczywistych). W terminologii systemów bazodanowych często mówi
się o typach danych zamiast o domenach. Gdy zdefiniujemy tabelę możemy zdecydować jakie atrybuty
ma zawierać oraz jakiego rodzaju dane będą przechowywane jako wartości opisowe. Dla przykładu
wartości SNAME z tabeli SUPPLIER będą ciągami znaków, a SNO będzie przechowywać liczby
całkowite. Zdefiniujemy to przypisując typ danych do każdego atrybutu. Typem SNAME będzie
VARCHAR(20) (jest to SQL'owy typ dla stringów o długości <=20), typem SNO będzie INTEREGER.
Przypisując typ musimy także wybrać dziedzinę dla atrybutu. Domeną SNAME jest zbiór łańcuchów o dł.
<= 20, domeną SNO jest zbiór liczb całkowitych.

1.3. Operacje na modelu relacyjnym

W poprzednim rozdziale (

Opis formalny modelu relacyjnego

) poznaliśmy definicje matematyczne

modelu relacyjnego. Już wiemy jak przy użyciu modelu relacyjnego są przechowywane dane, ale jeszcze
nie wiemy co zrobić aby wyciągnąć informacje z bazy danych. Na przykład ktoś mógłby zapytać o
nazwiska dostawców, którzy sprzedają produkt "Screw". W tym celu stosuje się dwa różne sposoby
zapisu wyrażeń relacyjnych:



Relacyjna algebra która jest zapisem algebraicznym, w którym zapytania są formułowane przy
użyciu specjalnych operatorów.



Relacyjny Calculus jest zapisem logicznym, polegającym na formułowaniu logicznych warunków,
które muszą być spełnione przez zwracane krotki.

1.3.1. Relacyjna algebra

Relacyjna algebra została przedstawiona przez E. F. Codd'a w 1972 r. Zawiera zbiór operatorów i relacji:



SELECT (σ): wyciąga z relacji krotki spełniające warunek. Niech

będzie tabelą zawierającą

atrybut

. σ

A=a

(R) = {t ∈ R &mid; t(A) = a}, gdzie

oznacza krotkę z

R, a

t(A)

oznacza

wartość atrybutu

w krotce



PROJECT (π): wyciąga żądane atrybuty (kolumny) z relacji. Niech

będzie relacją zawierającą

atrybut

. π

(

) = {t(X) &mid; t ∈

}, gdzie

(

) oznacza wartość atrybutu

krotki



PRODUCT (×): tworzy iloczyn kartezjański z dwóch relacji. Niech

tabelą o budowie

i niech

będzie tabelą o budowie

jest zbiorem wszystkich

-krotek gdzie pierwsza

forma krotki z

R, a k

jest formą krotki z



UNION (∪): tworzy teoretyczną unię (złączenie) dwóch tabel. Są tabele

(o tej samej

budowie), złączenie

∪

jest zbiorem krotek z

lub

albo z obu tabel.



INTERSECT (∩): tworzy teoretyczne skrzyżowanie dwóch tabel. Są dane tabele

∩

jest

zbiorem krotek z

. Budowa tabel musi być taka sama.



DIFFERENCE (− or &setmn;): tworzy różnicę dwóch tabel. Niech

będą znów dwoma

tabelami o tej samej budowie.

jest zbiorem krotek z

ale nie z



JOIN (∏): łączy dwie tabele przez wspólne atrybuty. Niech

będzie tabelą z atrybutami

niech

będzie tabelą z atrybutami

. Jest więc jeden atrybut wspólny dla obu relacji -

. R ∏

= π

R.A,R.B,R.C,S.D,S.E

(σ

R.C=S.C

(R × S)). Co to oznacza? Na początku liczymy iloczyn kartezjański

. Potem wybieramy te krotki, których wartości dla wspólnego atrybutu C są równe.

Otrzymujemy tabelę zawierającą atrybut C podwójnie, poprawiamy to usuwając powtórzone
kolumny.

Przykład 1

-2. Inner Join

Spójrzmy na tabele stworzone dla pokazania kroków niezbędnych do złączenia. Mamy
następujące tabele:

R: S:
A | B | C C | D | E
---+---+--- ---+---+---
1 | 2 | 3 3 | a | b
4 | 5 | 6 6 | c | d
7 | 8 | 9

Najpierw liczymy iloczyn kartezjański

i otrzymujemy:

R x S:
A | B | R.C | S.C | D | E
---+---+-----+-----+---+---
1 | 2 | 3 | 3 | a | b
1 | 2 | 3 | 6 | c | d
4 | 5 | 6 | 3 | a | b
4 | 5 | 6 | 6 | c | d
7 | 8 | 9 | 3 | a | b
7 | 8 | 9 | 6 | c | d

Po selekcji σ

R.C=S.C

(R × S) mamy:

A | B | R.C | S.C | D | E
---+---+-----+-----+---+---
1 | 2 | 3 | 3 | a | b
4 | 5 | 6 | 6 | c | d

Aby usunąć duplikaty

przeprowadzamy następującą operację: π

R.A,R.B,R.C,S.D,S.E

(σ

R.C=S.C

(R × S))

i otrzymaliśmy:

A | B | C | D | E
---+---+---+---+---
1 | 2 | 3 | a | b
4 | 5 | 6 | c | d



DIVIDE (÷): Niech

będzie tabelą z atrybutami A, B, C, i D, a

niech będzie tabelą z atrybutami

C i D. Później definiujemy dzielenie jako:



R ÷ S = {t &mid; ∀ t

∈ S ∃ t

∈ R

lub tak t

(A,B)=t∧t

(C,D)=t

}, gdzie t

(x,y) oznacza krotkę tabeli

zawierającą tylko atrybuty

. Krotka

zawiera tylko atrybuty

relacji

Dla danych tabel

R: S:
A | B | C | D C | D
---+---+---+--- ---+---
a | b | c | d c | d
a | b | e | f e | f
b | c | e | f
e | d | c | d
e | d | e | f
a | b | d | e

R ÷ S przedstawia się tak

A | B
---+---
a | b
e | d

Po więcej szczegółowych informacji i definicji dotyczących relacyjnej algebry sięgnij do [

Ullman, 1988

]

lub [

Date, 1994

Przykład 1

-3. Zapytanie wykorzystujące algebrę relacyjną.

Operatory relacyjne formułujemy aby mieć możliwość wydobycia danych z bazy. Powróćmy teraz do
przykładu z poprzedniego rozdziału (

Operacje na modelu relacyjnym

). Jeśli ktoś chciałby znać nazwy

wszystkich dostawców, którzy sprzedają produkt

Screw

. Takie pytanie przy użyciu algebry relacyjnej

może być przedstawione jako następująca operacja:

SUPPLIER.SNAME

(σ

PART.PNAME='Screw'

(SUPPLIER ∏ SELLS ∏ PART))

Taką operację nazywamy zapytaniem (query). Jeśli zastosujemy powyższe zapytanie do naszych
przykładowych tabel (

Baza danych Dostawcy i Produkty

) otrzymamy następujący wynik:

SNAME
-------
Smith
Adams

1.3.2. Relacyjny Calculus

Relacyjny calculus jest oparty na logice pierwszego stopnia. Są dwa warianty relacyjnego calculusa:



Domain Relational Calculus (DRC), gdzie wartości zmienne odpowiadają atrybutom krotek.



Tuple Relational Calculus (TRC), gdzie wartości zmienne odpowiadają krotkom.

My skupimy się na omówieniu wariantu TRC, który jest podstawą większości relacyjnych języków.
Szersza dyskusja na ten temat znajduje się w

Date, 1994

oraz

Ullman, 1988

1.3.3. Tuple Relational Calculus

Zapytania używające TRC mają następującą formę:

x(A) &mid; F(x)

gdzie

jest zmienną krotką,

jest zbiorem atrybutów i

jest formułą. Wynikowa relacja zawiera

wszystkie krotki

t(A)

zawarte w

F(t)

Jeśli chcemy odpowiedzi na zapytanie z przykładu

Zapytanie z użyciem relacyjnej algebry

stosując TRC

formułujemy je nastepująco:

{x(SNAME) &mid; x ∈ SUPPLIER ∧

∃ y ∈ SELLS ∃ z ∈ PART (y(SNO)=x(SNO) ∧

z(PNO)=y(PNO) ∧
z(PNAME)='Screw')}

Stosując to zapytanie na przykładzie

Bazy danych Dostawcy i Produkty

znów otrzymamy taki sam wynik

jak w

Zapytaniu z użyciem relacyjnej algebry

1.3.4. Relacyjna Algebra - Relacyjny Calculus

Relacyjna algebra i calculus mają te same możliwości wyrazu, tzn. wszystkie zapytania sformułowane
przy pomocy relacyjnej algebry mogą być zapisane przy użyciu calculusa i na odwrót. Pierwszy
udowodnił to E. F. Codd w 1972 r. Dowód ten jest oparty na algorytmie ("Algorytm redukcji Codd'a"), w
którym rozstrzygający warunek relacyjnego calculusa można zredukować do równoznacznego warunku
relacyjnej algebry. Więcej na ten temat w

Date, 1994

oraz

Ullman, 1988

Często mówi się, że języki oparte na calculusie są "wyższego poziomu", ponieważ algebra (częściowo)
podaje kolejność operacji, podczas gdy calculus pozostawia compilatorowi lub interpreterowi stworzenie
najefektywniejszego porządku wykonania.

1.4. Język

SQL

Tak jak w przypadku większości nowoczesnych języków relacyjnych, w SQL'u można sformułować
wszystkie zapytania zapisane z użyciem relacyjnej algebry lub calculusa. Istnieją jednak właściwości
spoza zakresu relacyjnej algebry i calculusa.

Poniżej przedstawiamy cechy SQL, które nie należą do relacyjnej algebry, ani calculusa:



Operacje wstawiania, usuwania i modyfikacji danych.



Operacje arytmetyczne: W SQL'u można np. stosować operacje arytmetyczne w porównaniach.

A < B + 3.

Zauważ, że + i inne operatory występują częściej w relacyjnej algebrze, niż w calculusie.



Operacje przypisania i wyjścia. Można wyprowadzić na wyjście (ekran, plik) relację stworzoną w
zapytaniu albo przypisać nazwę takiej relacji.



Funkcje agregujące: Operacje takie jak średnia, suma, maksimum, itd. można stosować do kolumn
aby otrzymać jedną liczbę.

1.4.1. Select

Najczęściej używanym poleceniem SQL jest SELECT, używane do wybierania danych. Jego składnia jest
następująca:

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]
[ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | EXCEPT [ ALL ] } select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ FOR UPDATE [ OF class_name [, ...] ] ]
[ LIMIT { count | ALL } [ { OFFSET | , } start ]]

Teraz zilustrujemy całą składnię polecenia SELECT na różnych przykładach. Tabele użyte w przykładach
zdefiniowano w

Bazie danych Dostawcy i Produkty

1.4.1.1. Proste Select'y

Podajemy najprostsze przykłady select'ów:

Przykład 1

-4. Proste zapytanie warunkowe

Aby otrzymać wszystkie krotki z tabeli PART, gdzie atrybut PRICE jest większy od 10, sformułujemy
takie zapytanie:

SELECT * FROM PART
WHERE PRICE > 10;

i otrzymujemy tabelę:

PNO | PNAME | PRICE
-----+---------+--------
3 | Bolt | 15
4 | Cam | 25

Znak "*" w poleceniu SELECT oznacza wszystkie kolumny w tabeli. Jeśli chcielibyśmy wydobyć tylko
kolumny PNAME i PRICE z tabeli PART użyjemy takiej formy:

SELECT PNAME, PRICE
FROM PART
WHERE PRICE > 10;

W tym wypadku wynikiem będzie:

PNAME | PRICE
--------+--------
Bolt | 15
Cam | 25

Zauważ, że SELECT odpowiada project'owi z relacyjnej algebry, a nie select'owi (zobacz

Relacyjna

Algebra

Warunki w klauzuli WHERE mogą być połączone przy użyciu operatorów logicznych OR, AND lub
NOT:

SELECT PNAME, PRICE
FROM PART
WHERE PNAME = 'Bolt' AND
(PRICE = 0 OR PRICE <= 15);

otrzymamy wynik:

PNAME | PRICE
--------+--------
Bolt | 15

Operatory arytmetyczne mogą być używane w liście atrybutów oraz w klauzuli WHERE. Na przykład,
jeśli chcemy znać koszt dwóch sztuk produktu, należy zapytać tak:

SELECT PNAME, PRICE * 2 AS DOUBLE
FROM PART
WHERE PRICE * 2 < 50;

i mamy:

PNAME | DOUBLE
--------+---------
Screw | 20
Nut | 16
Bolt | 30

Zauważ, że słowo DOUBLE po słowie kluczowym AS jest nowym tytułem drugiej kolumny. Ten sposób
może być użyty dla każdego elementu listy pól do przypisania nowej nazwy dla wynikowej kolumny.
Taka nowa nazwa jest często nazywana aliasem. Alias nie może być używany w dalszej części zapytania.

1.4.1.2. Złączenia

Następny przykład pokazuje sposób realizacji złączeń w SQL'u.

Aby połączyć trzy tabele SUPPLIER, PART i SELLS wg pól wspólnych formułujemy następujące
zapytanie:

SELECT S.SNAME, P.PNAME
FROM SUPPLIER S, PART P, SELLS SE
WHERE S.SNO = SE.SNO AND
P.PNO = SE.PNO;

otrzymujemy następującą tabelę:

W klauzuli FROM wprowadzamy alias dla każdej relacji, ponieważ istnieją atrybuty o takich samych
nazwach. Teraz możemy rozróżnić wspólne atrybuty poprzedzając je nazwą aliasu i oddzielając kropką.
Złączenie przeprowadzane jest w taki sam sposób, jak pokazano w

Złączeniu wewnętrznym

. Najpierw

powstaje iloczyn kartezjański SUPPLIER × PART × SELLS. Później wybierane są tylko te krotki, które
spełniają warunek (np. atrybuty o takich samych wartościach).

Innym sposobem na przeprowadzenie złączenia jest użycie składni JOIN:

select sname, pname from supplier

JOIN sells USING (sno)

JOIN part USING (pno);

daje to taki wynik:

Tabela, powstała przy użyciu składni JOIN, zawiera pola podane w klauzuli FROM, a przed WHERE,
GROUP BY, albo HAVING. Inne odwołania do tabel, tj. nazwy tabel lub inne klauzule JOIN, mogą być
zawarte w kaluzuli FROM; należy je rozdzielić przecinkami. Logicznie złączone tabele są takie same jak
inne tabele z klauzuli FROM.

Istnieją dwa główne typy złączeń SQL - Złączenie krzyżowe (bezwarunkowe) i złączenia warunkowe.

Typy złączeń

Złączenie krzyżowe

{

} CROSS JOIN {

}

Złączenie krzyżowe bierze dwie tabele T1 i T2 mające N i M osobnych wierszy i zwraca złączoną
tabelę zawierającą N*M wszystkich złączonych wierszy. Dla każdego wiersza R1 z T1, każdy
wiersz R2 z T2 jest łączony z R1 i daje w wyniku wiersz JR złączonej tabeli zawierający
wszystkie pola z R1 i R2. Złączenie krzyżowe CROSS JOIN jest równoważne poleceniu INNER
JOIN ON TRUE.

Złączenia warunkowe

{

} [ NATURAL ] [ INNER | { LEFT | RIGHT | FULL } [ OUTER ] ] JOIN {

} { ON

search condition

| USING (

join column list

) }

Złączenie warunkowe wprowadza warunki złączenia przez podanie klauzuli NATURAL, ON albo
USING. Klauzula ON zawiera warunki złączenia (

search condition

) i działa tak jak klauzula

WHERE. USING zawiera listę nazw kolumn oddzielonych przecinkami, które zostaną użyte do
złączenia. NATURAL jest skrótem odpowiadającym USING z nazwami wszystkich kolumn z obu
tabel. Działaniem ubocznym USING i NATURAL jest zwracanie tylko jednej kopii każdej
łączonej kolumny (porównaj to z definicją JOIN z relacyjnej algebry, podaną wcześniej).

[ INNER ] JOIN

Dla każdego wiersza R1 z T1, złączona tabela ma wiersz dla każdego wiersza z T2, który spełnia
warunki złączenia z R1.

Uwaga: Słowa INNER i OUTER są opcjonalne dla wszystkich złączeń JOIN. INNER jest
domyślne. LEFT, RIGHT oraz FULL dotyczą OUTER JOIN.

LEFT [ OUTER ] JOIN

Najpierw wykonuje się INNER JOIN. Później dla każdego wiersza z T1, który nie spełnia
warunków złączenia z żadnym wierszem z T2, zwracany jest dodatkowo wiersz zawierający
wartości null w kolumnach z T2.

Uwaga: Złączona tabela bezwarunkowo zawiera wiersz dla każdego wiersza z T1.

RIGHT [ OUTER ] JOIN

Najpierw wykonuje się INNER JOIN. Później dla każdego wiersza z T2, który nie spełnia
warunków złączenia z żadnym wierszem z T1, zwracany jest dodatkowo wiersz zawierający
wartości null w kolumnach z T1.

Uwaga: Złączona tabela bezwarunkowo zawiera wiersz dla każdego wiersza z T2.

FULL [ OUTER ] JOIN

Najpierw wykonuje się INNER JOIN. Później dla każdego wiersza z T1, który nie spełnia
warunków złączenia z żadnym wierszem z T2, zwracany jest dodatkowo wiersz z polami null w
kolumnach z T2. Dodatkowo dla każdego wiersza z T2, który nie spełnia warunków złączenia z
żadnym wierszem z T1, zwracany jest wiersz zawierający wartości null w kolumnach z T1.

Uwaga: Złączona tabela bezwarunkowo zawiera wiersz dla każdego wiersza z T2 oraz dla
każdego wiersza z T1.

Złączenia wszystkich typów mogą być łączone lub zagnieżdżone, w taki sposób, że jedno lub oba z

może być złączoną tabelą. W celu kontroli kolejności wykonania złączeń otacza się je nawiasami, w

przeciwnym wypadku są wykonywane od lewej do prawej strony.

1.4.1.3. Funkcje (operatory) agregujące

SQL wprowadza funkcje agregujące (tj. AVG, COUNT, SUM, MIN, MAX). Argumentem funkcji jest
wyrażenie wykonywane dla każdego wiersza, który spełnia warunki klauzuli WHERE, więc obliczenia są
wykonywane na przefiltrowanym zbiorze. Zazwyczaj funkcje zwracają pojedynczy rezultat dla całego
polecenia SELECT, ale gdy użyto w zapytaniu grupowania, wtedy obliczenia wykonywane są oddzielnie
dla każdej grupy (patrz następny punkt).

Przykład 1

-5. Funkcje agregujące

Jeśli chcemy znać średnią cenę wszystkich produktów z tabeli PART użyjemy następującego zapytania:

SELECT AVG(PRICE) AS AVG_PRICE
FROM PART;

Wynik:

AVG_PRICE
-----------
14.5

Jeśli chcemy liczbę części zapiszemy to tak:

SELECT COUNT(PNO)
FROM PART;

i otrzymamy:

COUNT
-------
4

1.4.1.4. Funkcje

agregujące i grupowanie

SQL pozwala podzielić krotki w tabeli na grupy. Wtedy funkcje agregujące można stosować osobno dla
każdej grupy.

Grupowania dokonuje się przy pomocy klauzuli GROUP BY z podaniem atrybutów definiujących grupy.
Jeśli mamy GROUP BY A

, &tdot;, A

dzielimy relację na grupy tak, że krotki są w tej samej grupie

wtedy i tylko wtedy, gdy pasują do wszystkich atrybutów A

, &tdot;, A

Przykład 1

-6. Funkcje agregujące

Jeśli chcemy znać liczbę produktów sprzedanych przez każdego dostawcę, zapisujemy zapytanie:

SELECT S.SNO, S.SNAME, COUNT(SE.PNO)
FROM SUPPLIER S, SELLS SE
WHERE S.SNO = SE.SNO
GROUP BY S.SNO, S.SNAME;

i otrzymujemy:

SNO | SNAME | COUNT
-----+-------+-------
1 | Smith | 2
2 | Jones | 1
3 | Adams | 2
4 | Blake | 3

Teraz zobaczmy jak to się dzieje. Najpierw następuje złączenie tabel SUPPLIER i SELLS:

S.SNO | S.SNAME | SE.PNO
-------+---------+--------
1 | Smith | 1
1 | Smith | 2
2 | Jones | 4
3 | Adams | 1
3 | Adams | 3
4 | Blake | 2
4 | Blake | 3
4 | Blake | 4

następnie grupowanie przez umieszczenie razem krotek z pasującymi atrybutami S.SNO i S.SNAME:

S.SNO | S.SNAME | SE.PNO
-------+---------+--------
1 | Smith | 1
| 2
--------------------------
2 | Jones | 4
--------------------------
3 | Adams | 1
| 3
--------------------------
4 | Blake | 2
| 3
| 4

W naszym przykładzie mamy cztery grupy i teraz możemy zastosować funkcję COUNT dla każdej grupy
otrzymując rezultat podany wcześniej.

Zauważ, że zapytanie z GROUP BY i funkcjami agregującymi ma sens, gdy odwołujemy się do
atrybutów grupowanych. Inne atrybuty mogą być użyte tylko wewnątrz argumentu funkcji. Inaczej nie
będzie unikalnej wartości do połączenia z innymi atrybutami.

Nie ma sensu użycie funkcji na przykład w takiej formie AVG(MAX(sno)), ponieważ SELECT robi tylko
jeden przebieg grupowania i agregacji. Można otrzymać wynik tego typu przy użyciu tabeli tymczasowej
lub podzapytania z pierwszym stopniem agregacji w klauzuli WHERE.

1.4.1.5. Having

Klauzula HAVING działa podobnie do WHERE i jest używana do wybrania tylko grup spełniających
warunek przy HAVING. Właściwie WHERE odrzuca niepotrzebne wiersze przed grupowaniem i
agregacją, podczas gdy HAVING robi to po grupowaniu. Dlatego WHERE nie może odnosić się do
rezultatu funkcji agregujących. Z drugiej strony nie ma sensu zapis warunku HAVING, który nie dotyczy
funkcji agregujących. W takim wypadku lepiej zastosować WHERE.

Przykład 1

-7. Having

Jeśli chcemy tylko tych dostawców, którzy sprzedali więcej niż jeden produkt, użyjemy zapytania:

SELECT S.SNO, S.SNAME, COUNT(SE.PNO)
FROM SUPPLIER S, SELLS SE
WHERE S.SNO = SE.SNO
GROUP BY S.SNO, S.SNAME
HAVING COUNT(SE.PNO) > 1;

i otrzymamy:

SNO | SNAME | COUNT
-----+-------+-------
1 | Smith | 2
3 | Adams | 2
4 | Blake | 3

1.4.1.6. Podzapytania

W klauzulach WHERE oraz HAVING możliwe jest stosowanie podzapytań w miejsce jakichś wartości.
W tym wypadku najpierw wykonywane jest podzapytanie. Użycie podzapytań rozszerza możliwości
SQL.

Przykład 1

-8. Podzapytanie

Jeśli chcemy znać wszystkie produkty kosztujące więcej od produktu o nazwie 'Screw', użyjmy zapytania:

SELECT *
FROM PART
WHERE PRICE > (SELECT PRICE FROM PART
WHERE PNAME='Screw');

Rezultat wygląda następująco:

PNO | PNAME | PRICE
-----+---------+--------
3 | Bolt | 15
4 | Cam | 25

Patrząc na powyższe zapytanie zwrócimy uwagę na dwukrotne wystąpienie słowa SELECT. Pierwszy raz
na początku zapytania - nazwijmy je zewnętrznym SELECT - i jeszcze raz w klauzuli WHERE, które
rozpoczyna zapytanie zagnieżdżone - nazwijmy je wewnętrznym SELECT. Dla każdej krotki
zewnętrznego SELECT'a wykonywany jest wewnętrzny SELECT. Po każdym wykonaniu znamy cenę
produktu o nazwie 'Screw' i możemy sprawdzić czy nie jest większa od aktualnego produktu. (W tym
wypadku wewnętrzne zapytanie jest wykonywane tylko raz, ponieważ nie jest zależne od stanu zapytania
zewnętrznego).

Jeśli chcemy poznać wszystkich dostawców, którzy nie sprzedają żadnego produktu (np. żeby usunąć ich
z bazy) użyjemy :

SELECT *
FROM SUPPLIER S
WHERE NOT EXISTS
(SELECT * FROM SELLS SE
WHERE SE.SNO = S.SNO);

Nasze zapytanie nie zwróci nic, bo w naszej bazie dostawcy sprzedają co najmniej jeden produkt. Zauważ
, że użyliśmy S.SNO z zewnętrznego SELECT w klauzuli WHERE wewnętrznego zapytania. W tym
wypadku, podzapytanie musi być wykonane (odświeżone) dla każdej krotki pytania zewnętrznego, bo
wartość S.SNO jest zawsze pobierana z bieżącej krotki zewnętrznego SELECT.

1.4.1.7. Podzapytania w FROM

Trochę innym sposobem użycia podzapytań jest umieszczenie ich w klauzuli FROM. Jest to przydatna
możliwość, ponieważ podzapytania tego typu mogą zwracać wiele kolumn i wierszy, podczas gdy
podzapytania użyte w wyrażeniach muszą zwracać pojedynczy rezultat. Pozwala to na więcej niż
jednokrotne grupowanie/agregację bez potrzeby korzystania z tabel tymczasowych.

Przykład 1

-9. Podzapytanie w FROM

Jeśli chcemy znać najwyższą średnią cenę produktu wśród wszystkich dostawców, możemy napisać
MAX(AVG(PRICE)), ale możemy to również zrobić tak:

SELECT MAX(subtable.avgprice)
FROM (SELECT AVG(P.PRICE) AS avgprice
FROM SUPPLIER S, PART P, SELLS SE
WHERE S.SNO = SE.SNO AND
P.PNO = SE.PNO
GROUP BY S.SNO) subtable;

Podzapytanie zwraca jeden wiersz dla każdego dostawcy (przez zastosowanie GROUP BY) i te wiersze
brane są do obliczeń zewnętrznego zapytania.

1.4.1.8. Union, Intersect, Except

Operatory te tworzą złączenie (union), skrzyżowanie (intersection) i teoretyczną różnicę krotek
pochodzących z dwóch tabel.

Przykład 1

-10. Union, Intersect, Except

To zapytanie jest przykładem dla złączenia UNION:

SELECT S.SNO, S.SNAME, S.CITY
FROM SUPPLIER S
WHERE S.SNAME = 'Jones'
UNION
SELECT S.SNO, S.SNAME, S.CITY
FROM SUPPLIER S
WHERE S.SNAME = 'Adams';

i daje taki wynik:

Następny przykład dla INTERSECT:

SELECT S.SNO, S.SNAME, S.CITY
FROM SUPPLIER S
WHERE S.SNO > 1
INTERSECT
SELECT S.SNO, S.SNAME, S.CITY
FROM SUPPLIER S
WHERE S.SNO < 3;

daje następujący rezultat:

SNO | SNAME | CITY
-----+-------+--------
2 | Jones | Paris

Jedyna krotka zwrócona z obu części zapytania zawiera SNO=2.

W końcu dla EXCEPT:

SELECT S.SNO, S.SNAME, S.CITY
FROM SUPPLIER S
WHERE S.SNO > 1
EXCEPT
SELECT S.SNO, S.SNAME, S.CITY
FROM SUPPLIER S
WHERE S.SNO > 3;

daje rezultat:

1.4.2. Definicje danych

Podano tutaj zbiór komend służących do definiowania danych zawartych w języku SQL.

1.4.2.1. Create Table

Podstawowa komenda definiowania danych służąca do tworzenia nowej relacji (tabeli). Składnia
CREATE TABLE jest następująca:

CREATE TABLE table_name
(name_of_attr_1 type_of_attr_1
[, name_of_attr_2 type_of_attr_2
[, ...]]);

Przykład 1

-11. Tworzenie tabeli

Aby utworzyć tabele zdefiniowane w

Bazie danych Dostawcy i Produkty

należy użyć poniższych poleceń

SQL:

CREATE TABLE SUPPLIER
(SNO INTEGER,
SNAME VARCHAR(20),
CITY VARCHAR(20));

CREATE TABLE PART
(PNO INTEGER,
PNAME VARCHAR(20),
PRICE DECIMAL(4 , 2));

CREATE TABLE SELLS
(SNO INTEGER,
PNO INTEGER);

1.4.2.2. Typy danych w SQL

Poniżej podano standardowe typy danych używane w SQL'u:



INTEGER: liczba całkowita ze znakiem (4 bajty).



SMALLINT: mała liczba całkowita ze znakiem (2 bajty).



DECIMAL (

]): liczba stałoprzecinkowa ze znakiem; precyzja

określa liczbę cyfr, z których

będzie składała się liczba, a skala

ilość cyfr po przecinku. Jeśli pominięto

domyślnie

przyjmowane jest 0.



FLOAT: liczba zmiennoprzecinkowa ze znakiem (8 bajtów).



CHAR(

): łańcuch znakowy o stałej długości



VARCHAR(

): łańcuch znakowy o zmiennej długości, maksymalnie

znaków.

1.4.2.3. Create Index

Indeksy przyspieszają dostęp do relacji. Jeśli relacja

posiada indeks na atrybucie

wtedy pobieramy

wszystkie krotki

mające

(

) =

w czasie zależnym od liczby tych krotek, niż od rozmiaru relacji

Do tworzenia indeksów w SQL'u stosuje się polecenie CREATE INDEX o składni:

CREATE INDEX index_name
ON table_name ( name_of_attribute );

Przykład 1

-12. Tworzenie indeksu

Aby utworzyć indeks o nazwie "I" dla atrybutu SNAME relacji SUPPLIER stosuje się następujące
polecenie:

CREATE INDEX I ON SUPPLIER (SNAME);

Index jest aktualizowany automatycznie, np. podczas wstawiania nowej krotki do relacji. Zastosowanie
indeksu jest zauważalne jako zwiększenie szybkości SELECT'ów oraz zwolnienie podczas UPDATE'ów.

1.4.2.4. Create View

Widok (view) to taka wirtualna tabela, która fizycznie nie istnieje w bazie danych, ale dla użytkownika
jest widoczna jak zwykła tabela. Gdy mówimy o tabeli bazowej, chodzi o kopię każdego wiersza tabeli
przechowywaną gdzieś w fizycznym zbiorze.

Widoki nie przechowują własnych, charakterystycznych, niepowtarzalnych danych. W rzeczywistości
system przechowuje definicje widoków (np. zasady dostępu do istniejących tabel bazowych w kolejności
tworzenia widoków) gdzieś w zbiorach systemowych (zobacz

Zbiory systemowe

). Więcej sposobów

implementacji widoków podano w SIM98.

W SQL'u polecenie CREATE VIEW definiuje widok, w następujący sposób:

CREATE VIEW view_name
AS select_stmt

gdzie

select_stmt

jest poleceniem wybierającym w sposób podany w podrozdziale

Select

. Zauważ, że

select_stmt

nie jest wykonywane podczas tworzenia widoku. Jest przechowywane w zbiorach

systemowych i wykonywane podczas odwołania do widoku w innym zapytaniu.

Zdefiniujmy następujący widok (znów użyjemy tabel z

Bazy danych Dostawcy i Produkty

CREATE VIEW London_Suppliers
AS SELECT S.SNAME, P.PNAME
FROM SUPPLIER S, PART P, SELLS SE
WHERE S.SNO = SE.SNO AND
P.PNO = SE.PNO AND
S.CITY = 'London';

Teraz możemy użyć tej wirtualnej relacji

London_Suppliers

jakby była zwykłą tabelą:

SELECT * FROM London_Suppliers
WHERE PNAME = 'Screw';

i zwraca następujący wynik:

SNAME | PNAME
-------+-------
Smith | Screw

Aby otrzymać taki wynik, najpierw system przeszukuje bazowe tabele SUPPLIER, SELLS oraz PART,
ponieważ definicja widoku zawiera odwołanie do nich. Później sprawdzane są dalsze warunki (podane w
zapytaniu odwołującym się do widoku) aby uzyskać końcowy wynik.

1.4.2.5. Drop Table, Drop Index, Drop View

Do usuwania tabel (włącznie z wszystkimi krotkami) stosuje się plecenie DROP TABLE:

DROP TABLE table_name;

Aby usunąć tabelę SUPPLIER użyj następującego polecenia:

DROP TABLE SUPPLIER;

Komendę DROP INDEX stosuje się do usuwania indeksów:

DROP INDEX index_name;

W końcu, do usuwania widoków służy polecenie DROP VIEW:

DROP VIEW view_name;

1.4.3. Manipulowanie danymi

1.4.3.1. Insert Into

Tworzoną tabelę (zobacz

Create Table

), można wypełnić krotkami przy użyciu INSERT INTO o

składni:

INSERT INTO table_name (name_of_attr_1
[, name_of_attr_2 [,...]])
VALUES (val_attr_1 [, val_attr_2 [, ...]]);

Aby wstawić pierwszą krotkę do relacji SUPPLIER (z

Bazy danych Dostawcy i Produkty

) używamy

następującego polecenia:

INSERT INTO SUPPLIER (SNO, SNAME, CITY)
VALUES (1, 'Smith', 'London');

Tak samo do relacji SELLS:

INSERT INTO SELLS (SNO, PNO)
VALUES (1, 1);

1.4.3.2. Update

Do zmiany jednej lub więcej wartości w relacji stosuje się polecenie UPDATE o składni:

UPDATE table_name
SET name_of_attr_1 = value_1
[, ... [, name_of_attr_k = value_k]]
WHERE condition;

Aby zmienić wartość atrybutu PRICE produktu 'Screw' w tabeli PART, używamy:

UPDATE PART
SET PRICE = 15
WHERE PNAME = 'Screw';

Nową wartością atrybutu PRICE, w krotce z nazwą 'Screw' jest 15.

1.4.3.3. Delete

Do usuwania rekordów (krotek) w tabeli stosuje się polecenie DELETE FROM z następującą składnią:

DELETE FROM table_name
WHERE condition;

Aby usunąć dostawcę o nazwisku 'Smith' w tabeli SUPPLIER wpisujemy :

DELETE FROM SUPPLIER
WHERE SNAME = 'Smith';

1.4.4. Zbiory systemowe

W każdym systemie baz SQL zbiory systemowe używane są do przechowywania informacji o tabelach,
widokach, indeksach zdefiniowanych w bazie. Zbiory te mogą być przepytywane jakby były zwykłymi
relacjami. Na przykład jest zbiór używany do definiowania widoków. Przechowuje polecenie z definicją
widoku. Podczas tworzenia widoku system pobiera definicję widoku z tego zbioru i tworzy widok przed
wykonaniem zapytania użytkownika (szczegółowy opis w

Simkovics, 1998

). Więcej informacji o

zbiorach systemowych znajduje się w

Date, 1994

1.4.5. Wbudowany SQL

W tej części nakreślimy sposób użycia SQL wewnątrz innego języka (tj.

). Są dwa powody

wbudowywania poleceń SQL'a w inny język:



Zapytania, których nie można sformułować w czystym SQL (np. zapytania okresowe). Aby
wykonać takie zapytania potrzeba języka bardziej rozbudowanego niż SQL.



Dostęp do bazy danych z aplikacji napisanej w jakimś języku (np. system rezerwacji biletów z
graficznym interfejsem napisanym jest w C, a informacje dotyczące ilości biletów są wyciągane z
bazy przy pomocy wbudowanego SQL'a).

Program używający wbudowanego SQL w swoim głównym języku zawiera polecenia tego języka oraz
wbudowanego SQL (embedded SQL - ESQL). Każde polecenie ESQL rozpoczyna się słowem
kluczowym EXEC SQL. Polecenia ESQL są konwertowane na polecenia języka głównego aplikacji
przez prekompilator (precompiler).

Gdy spojrzymy na przykłady z

Select

zauważymy, że wynikiem zapytań najczęściej jest zbiór krotek.

Większość języków nie jest zaprojektowana do operacji na zbiorach, dlatego potrzebujemy mechanizmu
dostępu do pojedynczej krotki w zbiorze zwracanym przez SELECT. Taki mechanizm można zapewnić
deklarując cursor. Możemy wtedy użyć polecenia FETCH do wyciągnięcia krotki i ustawienia kursora na
następnej.

Rozdział 2. Architektura

2.1. Podstawy architektury Postgres'a.

Zanim rozpoczniemy powinieneś poznać podstawową architekturę systemu Postgres. Jak współpracują
elementy Postgres'a wyjaśni następny rozdział. Postgres używa modelu klient/serwer. Sesja zawiera
poniższe współpracujące Unix'owe procesy (programy):



proces administratora (postmaster),



frontend aplikacja użytkownika końcowego (np. program psql),



backend jeden lub więcej serwerów (proces postgres).

Jeden administrator (postmaster) zarządza zbiorem (kolekcją) baz danych na jednym komputerze. Taki
zbiór baz jest nazywany klastrem. Aplikacja kliencka, chcąca otrzymać dostęp do bazy w klastrze tworzy
zapytania do biblioteki. Biblioteka przesyła żądania poprzez sieć do postmastera (Rys.2-1), który
rozpoczyna nowy proces serwera i łączy proces klienta z nowym serwerem. Od tej chwili komunikacja
odbywa się bez udziału postmastera. Tak więc postmaster jest uruchomiony zawsze, czekając na żądania,
podczas gdy procesy klienckie i procesy serwera mają określony "czas życia".

Biblioteka libpq zezwala pojedynczemu klientowi na nawiązanie wielu połączeń do procesu serwera. Ale
taka aplikacja w dalszym ciągu jest procesem jednowątkowym. Połączenia wielowątkowe
frontend/backend nie są jak na razie wspierane przez libpq. Zasada jest więc taka, że postmaster i serwer
zawsze są uruchomione na tej samej maszynie (serwerze bazy danych), podczas gdy klient może być
wszędzie. Należy to zapamiętać, ponieważ pliki dostępne na komputerze klienta mogą nie być dostępne
(albo dostępne pod inną nazwą) na serwerze bazy danych.

Trzeba brać pod uwagę również to, że proces postmaster i procesy serwerów są uruchamiane z
identyfikatorem postgresowego superużytkownika (administratora), który nie musi być specjalnym
użytkownikiem takim jak "postgres". Co więcej superużytkownik nie powinien być Unixowym
administratorem "root"! W każdym wypadku, wszyskie pliki związane z bazą danych należą do tego
superużytkownika.

Rozdział 3. Rozpoczynamy

Spis treści

3.1.

Ustawienia środowiska

3.2.

Uruchamiamy Interactive Monitor (psql)

3.3.

Zarządzanie bazą danych

3.3.1.

Tworzenie bazy danych

3.3.2.

Dostęp do bazy danych

3.3.3.

Usunięcie bazy danych

Niektóre czynności wymagane do uruchomienia Postgresa mogą być wykonane przez dowolnego
użytkownika, ale niektóre musi wykonać administrator bazy. Administratorem jest osoba, która
zainstalowała oprogramowanie, stworzyła katalogi bazy danych i uruchomiła proces postmastera. Nie
musi to być root ani administrator systemu komputerowego. Instalacja i używanie Postgresa nie wymaga
specjalnych kont, ani uprawnień.

Jeżeli instalujesz Postgresa sam zobacz do Administrator's Guide, gdzie są instrukcje instalacji, aby
powrócić po zakończeniu instalacji.

Wszystkie przykłady tego manuala rozpoczynające się od znaku "

" powinny być wprowadzane w linii

poleceń shell'a unixowego. Przykład po znaku "

" są poleceniami Postgres SQL.

3.1. Ustawienia środowiska

W tej części ustawimy nasze środowisko pracy zakładając, że Postgres został poprawnie zainstalowany i
uruchomiony. Zobacz do Administrator's Guide po instrukcje instalacji.

Postgres jest aplikacją klient/serwer. Jako użytkownik, potrzebujesz dostępu jedynie do części klienckiej
instalacji (dla przykładu Interactive Monitor psql jest programem klienckim). Dla ułatwienia zakładamy,
że Postgres został zainstalowany w katalogu

/usr/local/pgsql

. Dlatego, gdziekolwiek zobaczysz ten

katalog, powinieneś w to miejsce wstawić katalog z zainstalowanym Postgresem. Wszystkie polecenia
Postgresa są zainstalowane w katalogu

/usr/local/pgsql/bin

. Dlatego dodaj go do zmiennej PATH

shell'a. Jeśli korzystasz z shella Berkley C, np. csh lub tcsh powinieneś dodać

% set path = ( /usr/local/pgsql/bin path )

do pliku

.login

w twoim domowym katalogu. Jeśli używasz shella Bourne'a, np. sh, ksh, bash, musisz

dodać

% PATH=/usr/local/pgsql/bin:$PATH
% export PATH

do pliku .profile w twoim domowym katalogu. Od tego momentu zakładamy, że dodałeś katalog bin do
swojej ścieżki. Będziemy często odwoływać się w tym dokumencie do "ustawienia zmiennych
środowiskowych". Jeżeli niezupełnie rozumiesz ostatni punkt o ustawianiu ścieżki dostępu powinieneś
przejrzeć strony manuala dotyczące twojego shell'a, zanim przejdziesz dalej.

Jeżeli administrator systemu nie stosuje ustawień domyślnych, być może będziesz musiał ustawić inne
rzeczy, np. jeżeli serwer jest komputerem zdalnym trzeba ustawić zmienną PGHOST na nazwę tego
komputera. Zmienna PGPORT może być również zmianiana. Gdy podczas uruchamiania programu
otrzymasz komunikat o braku połączenia z postmasterem, powinieneś skonsultować z administratorem
ustawienie zmiennych środowiskowych.

3.2. Uruchamiamy Interactive Monitor (psql)

Zakładając, że twój administrator prawidłowo uruchomił postmastera i nadał ci uprawnienia użycia bazy
możesz uruchamiać programy. Jak wcześniej wspomniano musisz dodać

/usr/local/pgsql/bin

zmiennej PATH twojego shell'a. W większości przypadków to jest wszystko co powinieneś zrobić.

Możliwe są dwa sposoby uzyskania połaczenia. Administrator może wybrać zezwolenie na sieciowe
połączenie TCP/IP albo ograniczyć dostęp do połączeń lokalnych (na tym samym komputerze).
Możliwość wyboru ma znaczenie, dgy napotkamy problemy z połączeniem. Wtedy można wybrać
dostępne opcje połaczenia.

Gdy otrzymasz poniższy komunikat z linii poleceń Postgres'a (psql lub createdb):

% psql template1
psql: connectDBStart() -- connect() failed: No such file or directory
Is the postmaster running locally
and accepting connections on Unix socket '/tmp/.s.PGSQL.5432'?
or
% psql -h localhost template1
psql: PQconnectPoll() -- connect() failed: Connection refused
Is the postmaster running (with -i) at 'localhost'
and accepting connections on TCP/IP port 5432?

zazwyczaj oznacza to, że



nie jest uruchomiony postmaster, albo



próbujesz połączyć się z niewłaściwym hostem serwera.

Gdy otrzymasz taki komunikat:

FATAL 1:Feb 17 23:19:55:process userid (2360) != database owner (268)

oznacza to, że administrator uruchomił postmaster'a jako niewłaściwy użytkownik. Powiedz mu aby
wystartował Postgres'a jako postgresowy superużytkownik.

3.3. Zarządzanie bazą dan

ych

Teraz, gdy Postgres jest uruchomiony, możemy stworzyć bazy do ćwiczeń. Poniżej opiszemy
podstawowe komendy do zarządzania bazą.

Większość postgresowych programów zakłada, że jeżeli nie podano nazwy bazy, to jest nią nazwa
twojego konta.

Jeżeli administrator bazy stworzył twoje konto bez uprawnień do tworzenia bazy, to powinien ci
powiedzieć jaka jest nazwa twojej bazy. W takim przypadku możesz opuścić punkty dotyczące tworzenia
i usuwania bazy.

3.3.1. Tworzenie bazy danych

Powiedzmy, że chcesz stworzyć bazę o nazwie mydb. Możesz to zrobić w następujący sposób:

% createdb mydb

Jeżeli nie masz uprawnień do utworzenia bazy zobaczysz następujący komunikat:

% createdb mydb
NOTICE:user "your username" is not allowed to create/destroy databases
createdb: database creation failed on mydb.

Postgres zezwala ci na utworzenie dowolnej liczby baz i automatycznie stajesz się administratorem bazy,
którą utworzyłeś. Nazwy baz danych muszą zaczynać się od litery i są ograniczone do 32 znaków. Nie
każdy użytkownik może stać się administratorem bazy. Jeżeli Postgres odmawia utworzenia bazy wtedy
odpowiednie uprawnienie może nadać Ci administrator. Jeżeli tak się zdarzy skontaktuj sie z
administratorem.

3.3.2. Dostęp do bazy danych

Po utworzenie bazy możesz mieć do niej dostęp przez:



Uruchomienie programów terminalowych Postgres'a (np. psql), które zezwalają na interaktywną
edycję i uruchomienie wyrażeń SQL.



Użycie dostępnych natywnych aplikacji, takich jak pgaccess albo ApplixWare (przez ODBC) do
tworzenia i manipulacji bazą danych.



Użycie takich języków perl lub tcl, które posiadają wsparcie interfejsu dla Postgres'a. Niektóre z
nich posiadają potężne i wygodne narzędzia GUI pomocne przy tworzeniu aplikacji. Wspomniany
wyżej pgaccess, jest aplikacją napisaną w tk/tcl i może być użyty jako przykład.



Pisząc w C program przy użyciu bibloteki LIBPQ. Umożliwia to wywoływanie z programu
poleceń SQL i otrzymywanie z powrotem odpowiedzi i komunikatów. Ten sposób jest omówiony
w The PostgreSQL Programmer's Guide.

Aby sprawdzić przykłady podane w tym manualu możesz uaktywnić bazę mydb przez wpisanie
polecenia:

% psql mydb

Zostaniesz powitany poniższym komunikatem:

Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL

type \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: template1

mydb=>

Oznacza to, że terminal oczekuje na wpisanie zapytań SQL. Działanie programu psql oparte jest na
kodach znakowych podanych po znaku "

". Na przykład, aby otrzymać pomoc na temat składni poleceń

SQL trzeba wpisać:

mydb=> \h

Po wpisaniu zapytania można przesłać zapytanie do serwera przez wpisanie:

mydb=> \g

Mówi to serwerowi aby wykonał zapytanie. Jeśli zakończysz zapytanie znakiem ";" wpisywanie "\g" nie
jest konieczne. Psql automatycznie wykona zapytanie zakończone średnikiem. Aby wczytać zapytania z
pliku, powiedzmy myFile, zamiast podawać je ręcznie w wierszu poleceń, wpisz:

mydb=> \i fileName

Aby wyjść z psql i powrócić do Unix'a, wpisz:

mydb=> \q

Monitor zakończy działanie i powrócisz do linii poleceń shella. (Więcej informacji na temat kodów
otrzymasz po wpisaniu "\h" w linii poleceń Monitora). Białe znaki (tj. spacje, tabulatory, nowa linia)
mogą być używane w zapytaniach bez ograniczeń. Komentarze jednoliniowe poprzedza się "--".
Wszystko za tym znakiem, do końca linii, jest ignorowane. Komentarze długie oraz wewnętrzne zapisuje
się tak: "

/* ... */

3.3.3. Usunięcie bazy danych

Jeśli jesteś administratorem bazy mydb, możesz ją usunąć przez wpisanie poniższego polecenia:

% dropdb mydb

Wykonanie tego polecenia powoduje usunięcie wszystkich plików związanych z tą bazą i jest
nieodwracalne. Więc powinno być używane z wielką ostrożnością.

Rozdział 4. Język zapytań

Spis treści

4.1.

Interactive Monitor

4.2.

Zasady

4.3.

Tworzenie tabeli

4.4.

Wstawianie danych do tabeli

4.5.

Przeglądanie tabeli

4.6.

Przekierowanie zapytań wybierających

4.7.

Połączenia tabel

4.8.

Uaktualnienia

4.9.

Kasowanie

4.10.

Funkcje agregujące

Postgresowy język zapytań jest odmianą standardowego SQL. Posiada wiele rozszerzeń, takich jak:
system typów rozszerzonych, dziedziczenie, funkcje, role. Te dodatki pochodzą od oryginalnego
postgresowego języka PostQuel. W niniejszym rozdziale omówimy zasady użycia podstawowych
operacji Postgres SQL'a. Manual ten nie jest kompletnym podręcznikiem SQL, a jedynie wprowadzeniem
do tego zagadnienia. Na temat języka SQL napisano wiele książek między innymi

Melton and Simon,

1993

Date and Darwen, 1997

. Należy też zwrócić uwagę na niektóre cechy języka, ponieważ są one

rozszerzeniem standardu.

4.1. Interactive Monitor

W poniższym przykładzie zakładamy, że utworzyłeś bazę mydb opisaną w poprzednim rozdziale i
uruchomiłeś psql. Wszystkie przykłady można również znaleźć w katalogu

src/tutorial/

. Plik

README

w tym katalogu zawiera instrukcje jak ich używać. Aby rozpocząć tutorial wprowadź następujące
polecenia:

$ cd .../src/tutorial
$ psql -s mydb
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL

type \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: postgres

mydb=> \i basics.sql

Polecenie

wczytuje zapytania z podanego pliku. Opcja

-s

uruchamia tryb krokowy, który powoduje

zatrzymanie przed wysłaniem każdego zapytania do serwera. Zapytania z tego rozdziału znajdują się w
pliku

basics.sql

Psql posiada opcję

służącą do pokazania informacji o systemie. Do wyświetlenia szczegółowych

informacji zastosuj

4.2. Zasady

Podstawowym pojęciem w Postgresie jest tabela, nazywana kolekcją wierszy. Każdy wiersz ma ten sam
zbiór kolumn, a każda kolumna ma specyficzny typ. Ponadto każdy wiersz ma stały identyfikator OID
(object identifier), unikalny w danym klastrze bazy danych. Dawniej tabele w Postgresie były nazywane
klasami, wiersze instancjami obiektów, a kolumny atrybutami. Ma to sens jeśli rozważasz obiektowo-
relacyjne aspekty systemu bazodanowego, ale w tym manualu będziemy używać podstawowej
terminologii SQL. Jak już powiedziano tabele pogrupowane są w bazy, a zbiór baz zarządzanych przez
jeden proces postmastera stanowi klaster.

4.3. Tworzenie tabel

Możesz utworzyć nową tabelę podając jej nazwę oraz wszystkie kolumny wraz z ich typami:

CREATE TABLE weather (
city varchar(80),
temp_lo int, -- niska temperatura
temp_hi int, -- wysoka temperatura
prcp real, -- zakres
date date
);



Słowa kluczowe i identyfikatory są case-insensitive, co oznacza, że nie są rozróżniane małe/duże
litery. Mżna to ominąć przez otoczenie identyfikatora cudzysłowem, co jest zgodne z SQL92.
Postgresowy SQL zawiera podstawowe typy SQL:

int

float

real

smallint

char(N)

varchar(N)

date

time

timestamp

, także inne typy główne i duży zbiór typów

geometrycznych. Jak zobaczymy później w Postgresie można zdefiniować wiele typów własnych.
Konsekwentnie nazwy typów nie są słowami kluczowymi, z wyjątkiem sytuacji gdy wymagane
jest wsparcie standardu SQL92. Na razie wiesz, że polecenie CREATE jak to używane do
tworzenia tabel w tradycyjnym systemie relacyjnym, ale później okaże się, że tabele posiadają
własności, które są rozszerzeniem modelu relacyjnego.

4.4. Wstawianie danych do tabeli

Polecenie INSERT ma zastosowanie podczas wstawiania danych do tabeli:

INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');

Można też użyć COPY do wstawienia większej ilości danych z pliku ASCII. Zazwyczaj jest to metoda
szybsza, ponieważ dane są czytane (albo zapisywane) jako pojedyncza transakcja wprost do/z docelowej
tabeli. Na przykład tak:

COPY weather FROM '/home/user/weather.txt' USING DELIMITERS '|';

gdzie ścieżka do pliku źródłowego musi być dostępna dla serwera, nie dla klienta, ponieważ serwer
wprost czyta dane z pliku.

4.5. Przeglądanie tabeli

Tabela

weather

może być przepytywana przy pomocy normalnych zapytań wybierających i tworzących.

W tym celu używa się polecenia SELECT. Składa się ono z docelowej listy (zawierającej zwracane
kolumny) oraz warunków (ograniczeń). Dla przykładu, aby otrzymać wszystkie wiersze z tabeli

weather

wpisz:

SELECT * FROM weather;

na wyjściu otrzymasz:

+--------------+---------+---------+------+------------+
|city | temp_lo | temp_hi | prcp | date |
+--------------+---------+---------+------+------------+
|San Francisco | 46 | 50 | 0.25 | 1994-11-27 |
+--------------+---------+---------+------+------------+
|San Francisco | 43 | 57 | 0 | 1994-11-29 |
+--------------+---------+---------+------+------------+
|Hayward | 37 | 54 | | 1994-11-29 |
+--------------+---------+---------+------+------------+

W liście docelowej możesz podać wyrażenia obliczeniowe, na przykład:

SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;

Operatory logiczne (AND, OR i NOT) są dozwolone w części warunkowej każdego zapytania. Na
przykład, dla:

SELECT * FROM weather
WHERE city = 'San Francisco'
AND prcp > 0.0;

wynikiem jest:

W końcu wyniki zapytania mogą być posortowane i pozbawione duplikatów.

SELECT DISTINCT city
FROM weather
ORDER BY city;

4.6. Przekierowanie zapytań wybierających

Wynik zapytania SELECT może być skierowany do nowej tabeli

SELECT * INTO TABLE temp FROM weather;

Ta forma wywołuje polecenie CREATE, tworzące nową tabelę temp z nazwami i typami kolumn
podanymi w liście docelowej polecenia SELECT INTO. Wtedy możemy na nowej tabeli wykonać każdą
operację dostępną na innych tabelach.

4.7. Połączenia tabel

Dotychczas zapytania odnosiły się do jednej tabeli, ale mogą być zadawane do wielu tabeli jednocześnie
lub do tej samej tabeli, ale w ten sposób, że wiele wierszy jest przetwarzanych w jednym czasie. Takie
zapytania są nazywane złączeniami (joins). Na przykład powiedzmy, że chcemy znaleźć wszystkie
rekordy z najniższą i najwyższą temperaturą.. W efekcie musimy porównać kolumny temp_lo i temp_hi
każdego wiersza tabeli WEATHER z kolumnami temp_lo i temp_hi pozostałych wierszy tej tabeli.

Uwaga: To jest tylko koncepcja. Takie złączenie może być wykonane w bardziej efektywny sposób, ale
ten przykład jest bardziej poglądowy.

Można to zrobić następującym zapytaniem:

SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high,
W2.city, W2.temp_lo AS low, W2.temp_hi AS high
FROM weather W1, weather W2
WHERE W1.temp_lo < W2.temp_lo
AND W1.temp_hi > W2.temp_hi;

+--------------+-----+------+---------------+-----+------+
|city | low | high | city | low | high |
+--------------+-----+------+---------------+-----+------+
|San Francisco | 43 | 57 | San Francisco | 46 | 50 |
+--------------+-----+------+---------------+-----+------+
|San Francisco | 37 | 54 | San Francisco | 46 | 50 |
+--------------+-----+------+---------------+-----+------+

W1 i W2 są aliasami tabel zawierającymi wszystkie wiersze tabeli WEATHER. (W terminologii
systemów baz danych są często nazywane zmiennymi zakresu).

Uwaga: W tego typu zapytaniach tworzony jest iloczyn kartezjański z tabel podanych w zapytaniu. Dla
wierszy spełniających warunek Postgres zwraca wartości podane w liście docelowej. Nie zwraca uwagi
na powtarzające się wartości. Oznacza to, że Postgres czasami zwraca kilkakrotnie te same wiersze.
Najczęściej zdarza się tak przy użyciu "OR" w wyrażeniach logicznych. Aby pozbyć się duplikatów
wierszy należy użyć składni SELECT DISTINCT.

4.8. Uaktualnienia

Do zmiany istniejących rekordów służy polecenie UPDATE. Przypuśćmy, że w dniu 28 listopada
odczyty temperatur były o dwa stopnie za wysokie. Możesz poprawić dane w następujący sposób:

UPDATE weather
SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
WHERE date > '1994-11-28';

4.9. Kasowanie

Kasowanie (usuwanie) jest wykonywane przy użyciu DELETE:

DELETE FROM weather WHERE city = 'Hayward';

Zostaną usunięte dotyczące miasta Heyward. Należy ostrożnie korzystać z zapytań w takiej formie:

DELETE FROM tablename;

DELETE bez warunku po prosu usunie wszystkie wiersze z danej tabeli. System nie żąda potwierdzenia
tej operacji.

4.10. Funkcje agregujące

Jak wiele innych baz relacyjnych, PostgreSQL zawiera funkcje agregujące (sumujące). Funkcja
agregująca oblicza rezultat z wartości wielu wierszy. Na przykład są funkcje obliczające liczbę (count),
sumę (sum), średnią (avg), maksimum/minimum (max/min) ze zbioru wierszy.

Ważne jest aby dobrze zrozumieć zasadę współpracy między funkcjami a klauzulami SQL'a WHERE
oraz HAVING. Podstawową różnicą między WHERE i HAVING jest to, że WHERE filtruje wiersze
przed grupowaniem i obliczeniami (decyduje, które wiersze wejdą do obliczeń funkcji agregujących),
podczas gdy HAVING selekcjonuje wiersze już pogrupowane, po wykonaniu obliczeń. Dlatego klauzula
WHERE nie musi zawierać funkcji agregujących, ponieważ nie ma sensu użycie funkcji do
zdecydowania, które wiersze wejdą do funkcji. Z drugiej strony, klauzula HAVING zawsze zawiera
funkcje agregujące. (Mówiąc wprost, możesz zastosować klauzulę HAVING bez funkcji, ale jest wtedy
mniej efektywna niż WHERE z tym samym warunkiem).

Dla przykładu, możemy znaleźć najniższą temperaturę

SELECT max(temp_lo) FROM weather;

Jeśli chcemy znać miasto (miasta), w którym ją odczytano, możemy spróbować

SELECT city FROM weather WHERE temp_lo = max(temp_lo);

ale to nie będzie działać ponieważ funkcja

max

nie może być użyta z WHERE. Jednakże, najczęściej

zapytanie może być zbudowane w sposób dający właściwy wynik; tutaj przy użyciu podzapytania:

SELECT city FROM weather
WHERE temp_lo = (SELECT max(temp_lo) FROM weather);

To jest OK ponieważ podzapytanie jest niezależnym działaniem obliczającym własne funkcje.

Funkcje agregujące są również przydatne w kombinacji z klauzulą GROUP BY. Na przykład, możemy
pobrać najniższą temperaturę odczytaną dla każdego miasta osobno

SELECT city, max(temp_lo)
FROM weather
GROUP BY city;

co daje jeden wiersz na każde miasto. Możemy teraz przefiltrować pogrupowane wiersze używając
HAVING:

SELECT city, max(temp_lo)

FROM weather
GROUP BY city
HAVING min(temp_lo) < 0;

W rezultacie mamy wszystkie miasta z minimalną temperaturą poniżej zera. W końcu, jeśli zależy nam na
miastach na literę 'P' możemy zrobić tak:

SELECT city, max(temp_lo)
FROM weather
WHERE city like 'P%'
GROUP BY city
HAVING min(temp_lo) < 0;

Zauważ, że można dodać warunek w klauzuli WHERE, dopóki nie zastosujemy funkcji. Jest
efektywniejsze niż dodanie warunku do HAVING, ponieważ grupowanie i obliczenia dokonywane są dla
wierszy, które przeszły przez sprawdzenie warunku w WHERE.

Rozdział 5. Zaawansowane właściwości Postgres

SQL

Spis treści

5.1.

Dziedziczenie

5.2.

Wartości złożone

5.3.

Więcej zaawansowanych właściwości

Poznaliśmy podstawy dostępu do danych przy użyciu Postgres SQL. Teraz omówimy cechy Postgres'a,
które odróżniają go od innych menadżerów baz danych. Do właściwości tych należy dziedziczenie oraz
złożone typy danych (tablice i zbiory). Przykłady z tego rozdziału znajdują się w pliku

advance.sql

(Sposób użycia podano w

Rozdziale 4

5.1. Dziedziczenie

Utwórzmy dwie tabele. Tabela capitals (stolice) zawiera stolice stanów, które również są miastami (cities)
. Naturalnie tabela capitals powinna dziedziczyć od cities.

CREATE TABLE cities (
name text,
population real,
altitude int (wysokość n.p.m.)
);

CREATE TABLE capitals (
state char(2)
) INHERITS (cities);

W tym wypadku wiersz z tabeli capitals dziedziczy wszystkie kolumny (name, population, altitude) od jej
rodzica, tabeli cities. Kolumna name (nazwa) jest typu

text

, przeznaczonego dla zmiennej długości

łańcuchów ASCII. Kolumna population posiada typ

real

, przeznaczony dla liczba

zmiennoprzecinkowych pojedynczej precyzji. Stolice posiadają dodatkową kolumnę state, zawierającą
skrót stanu. W Postgresie tabele mogą dziedziczyć od wielu tabel, a zapytanie może odwoływać się do
wszystkich wierszy tabeli oraz tabel potomnych.

Na przykład, poniższe zapytanie wyszuka nazwy wszystkich miast, włącznie ze stolicami, położonych na
wysokości ponad 500 m:

SELECT name, altitude
FROM cities
WHERE altitude > 500;

i zwraca:

+----------+----------+
|name | altitude |
+----------+----------+
|Las Vegas | 2174 |
+----------+----------+
|Mariposa | 1953 |
+----------+----------+
|Madison | 845 |
+----------+----------+

Następne zapytanie wyszuka wszystkie miasta, które nie są stolicami stanów:

SELECT name, altitude
FROM ONLY cities
WHERE altitude > 500;

+----------+----------+
|name | altitude |
+----------+----------+
|Las Vegas | 2174 |
+----------+----------+
|Mariposa | 1953 |
+----------+----------+

Tutaj ONLY przed tabelą cities oznacza, że zapytanie dotyczy tylko tej jednej tabeli (nie dotyczy tabel
dziedziczących po niej). Użycie słowa ONLY jest możliwe także w wielu już omawianych komendach, tj.
SELECT, UPDATE i DELETE.

Uwaga: W poprzednich wersjach Postgresa dostęp do tabel potomków nie był opcją domyślną. Aby
uzyskać dostęp do nich należało zastosować "*" w następujący sposób

SELECT * from cities*;

Aby w wersjach od 7.1 korzystać ze starej składni należy ustawić opcję konfiguracyjną

SQL_Inheritance

na off, np. tak

SET SQL_Inheritance TO OFF;

lub dodać tę linię do pliku

postgresql.conf

5.2. Wartości złożone

W modelu relacyjnym kolumny w tabeli są niepodzielne. Postgres nie posiada takiego ograniczenia;
kolumny mogą zawierać pod-wartości, do których można uzyskać dostęp przy pomocy zapytań. Można
na przykład tworzyć kolumny będące tablicami typów podstawowych.

5.2.1. Tablice

W Postgresie pola danego wiersza mogą być zdefiniowane jako wielowymiarowe tablice o zmiennej
długości i różnych wartościach. Można tworzyć tablice dowolnego typu podstawowego lub
zdefiniowanego przez użytkownika. Aby zilustrować ich zastosowanie, najpierw stworzymy tabelę z
tablicami typów podstawowych.

CREATE TABLE SAL_EMP (
name text,
pay_by_quarter integer[],
schedule text[][]
);

Powyższe polecenie utworzy tabelę SAL_EMP z polami: 'name' typu text, 'pay_by_quarter' jako
jednowymiarową tablicą z elementami typu integer, które zawiera płacę za kwartał oraz 'schedule' jako
dwuwymiarową tablicą z elementami typu text, zawierające plan tygodniowy. Następnie wprowadzimy
dane; zauważ, że podczas wstawiania wartości do tablic należy stosować klamry i cudzysłów. Jeśli znasz
C, zauważysz podobieństwo do inicjalizacji struktur.

INSERT INTO SAL_EMP
VALUES ('Bill',
'{10000, 10000, 10000, 10000}',

'{{"meeting", "lunch"}, {}}');

INSERT INTO SAL_EMP
VALUES ('Carol',
'{20000, 25000, 25000, 25000}',
'{{"talk", "consult"}, {"meeting"}}');

Domyślnie Postgres stosuje dla tablic numerację od jedynki, tzn. pierwszym elementem jest array[1] a
ostatnim array[n]. Teraz możemy przeglądać tabelę SAL_EMP. Najpierw pokażemy sposób dostępu do
pojedynczego elementu tablicy. Poniższe zapytanie wyciąga pracowników, których płace zmieniły się w
drugim kwartale:

SELECT name
FROM SAL_EMP
WHERE SAL_EMP.pay_by_quarter[1] <>
SAL_EMP.pay_by_quarter[2];

+------+
|name |
+------+
|Carol |
+------+

Następne zapytanie podaje płace wszystkich pracowników w trzecim kwartale:

SELECT SAL_EMP.pay_by_quarter[3] FROM SAL_EMP;

+---------------+
|pay_by_quarter |
+---------------+
|10000 |
+---------------+
|25000 |
+---------------+

Jest możliwe wyciągnięcie fragmentu tablicy podając górną i dolną granicę każdego składnika. Poniższe
zapytanie wyciąga pierwszy element z planu Bill'a dla pierwszych dwu dni tygodnia.

SELECT SAL_EMP.schedule[1:2][1:1]
FROM SAL_EMP
WHERE SAL_EMP.name = 'Bill';

+-------------------+
|schedule |
+-------------------+
|{{"meeting"},{""}} |
+-------------------+

5.3. Więcej zaawansowanych właściwości

Postgres posiada wiele cech nie wspomnianych w tej części, przeznaczonej dla początkujących
użytkowników SQL’a. Są one omówione szczegółowo w User's Guide oraz Programmer's Guide.

Wyszukiwarka

Podobne podstrony:
Postgres PostGIS
Postgrad Med J 2001 Woolfson 68 74
PostgreSQL
PHP PostgreSQL
PostgreSQL, SQL, SQL, PSQL-wyklady borzyszkowski, PSQL-wyklady borzyszkowski, Ftp, Sql
konserwacja-bazy-postgresql
PostgreSQL Leksykon kieszonkowy psqllk
Postgres lab23
konserwacja bazy postgresql
Odzyskiwanie bazy danych PostgreSQL
BEZPIECZEŃSTWO DOSTĘPU DO DANYCH MS SQL SERVER POSTGRESQL, 9 semestr, SQL, RÓŻNE
Professional Linux Programming, R-04-t, PLP - Rozdział 4: Interfejsy PostgreSQL
Postgres lab2
Postgres PostGIS
Rozdzial 3 PostgreSQL
PostgreSQL 7 2 cwiczenia praktyczne cwpsql

więcej podobnych podstron