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
1
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, przejdz
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óz
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.
2
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óz
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.
3
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 D1, D2, ... Dk, zapisany jako D1 × D2 × ... × Dk jest zbiorem wszystkich krotek
v1, v2, ... vk, takich jak v1 " D1, v2 " D2, ... vk " Dk.
Na przykÅ‚ad, gdy mamy k=2, D1={0,1} oraz D2={a,b,c} wtedy D1 × D2 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: R Ä…" D1 × D2 × ... × Dk.
Na przykÅ‚ad {(0,a),(0,b),(1,a)} jest relacjÄ…; tzn. zbiorem D1 × D2 wspomnianych wyżej.
Elementy relacji sÄ… nazywane krotkami. Każda relacja jakiegoÅ› iloczynu kartezjaÅ„skiego D1 × D2 × ... × Dk
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 R jest dowolny zbiór atrybutów A1, A2, ... Ak. Jest dziedzina Di, dla każdego atrybutu Ai,
1 <= i <= k, skąd brane są wartości atrybutów. Często schemat relacji zapisuje się w ten sposób: R(A1,
A2, ... Ak).
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.
4
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 R bÄ™dzie tabelÄ… zawierajÄ…cÄ…
atrybut A. ÃA=a(R) = {t " R ∣ t(A) = a}, gdzie t oznacza krotkÄ™ z R, a t(A) oznacza
wartość atrybutu A w krotce t.
·ð PROJECT (Ä„): wyciÄ…ga żądane atrybuty (kolumny) z relacji. Niech R bÄ™dzie relacjÄ… zawierajÄ…cÄ…
atrybut X. Ä„X(R) = {t(X) ∣ t " R}, gdzie t(X) oznacza wartość atrybutu X krotki t.
·ð PRODUCT (×): tworzy iloczyn kartezjaÅ„ski z dwóch relacji. Niech R tabelÄ… o budowie k1 i niech
S bÄ™dzie tabelÄ… o budowie k2. R × S jest zbiorem wszystkich k1 + k2-krotek gdzie pierwsza k1
forma krotki z R, a k2 jest formÄ… krotki z S.
·ð UNION (*"): tworzy teoretycznÄ… uniÄ™ (zÅ‚Ä…czenie) dwóch tabel. SÄ… tabele R i S (o tej samej
budowie), złączenie R *" S jest zbiorem krotek z R lub S albo z obu tabel.
·ð INTERSECT ()"): tworzy teoretyczne skrzyżowanie dwóch tabel. SÄ… dane tabele R i S, R )" S jest
zbiorem krotek z R i S. Budowa tabel musi być taka sama.
·ð DIFFERENCE (- or ∖): tworzy różnicÄ™ dwóch tabel. Niech R i S bÄ™dÄ… znów dwoma
tabelami o tej samej budowie. R - S jest zbiorem krotek z R ale nie z S.
·ð JOIN ("): Å‚Ä…czy dwie tabele przez wspólne atrybuty. Niech R bÄ™dzie tabelÄ… z atrybutami A,B,C i
niech S będzie tabelą z atrybutami C,D,E. Jest więc jeden atrybut wspólny dla obu relacji - C. R "
S = Ä„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 R
× S. 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
5
Najpierw liczymy iloczyn kartezjaÅ„ski R × S 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 S.C 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 R bÄ™dzie tabelÄ… z atrybutami A, B, C, i D, a S niech bÄ™dzie tabelÄ… z atrybutami
C i D. Póz
niej definiujemy dzielenie jako:
·ð R ÷ S = {t ∣ " ts " S " tr " R
lub tak tr(A,B)=t'"tr(C,D)=ts}, gdzie tr(x,y) oznacza krotkÄ™ tabeli R zawierajÄ…cÄ… tylko atrybuty x i
y. Krotka t zawiera tylko atrybuty A i B relacji R.
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
6
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) ∣ F(x)
gdzie x jest zmienną krotką, A jest zbiorem atrybutów i F 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) ∣ 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
7
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.
8
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;
9
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.
10
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ę:
SNAME | PNAME
-------+-------
Smith | Screw
Smith | Nut
Jones | Cam
Adams | Screw
Adams | Bolt
Blake | Nut
Blake | Bolt
Blake | Cam
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óz
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:
sname | pname
-------+-------
Smith | Screw
Adams | Screw
Smith | Nut
Blake | Nut
Adams | Bolt
Blake | Bolt
Jones | Cam
Blake | Cam
(8 rows)
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.
11
Typy złączeń
Złączenie krzyżowe
{ T1 } CROSS JOIN { T2 }
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
{ T1 } [ NATURAL ] [ INNER | { LEFT | RIGHT | FULL } [ OUTER ] ] JOIN { T2 } { 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óz
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óz
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óz
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.
12
 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 T1 i
T2 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 A1, ⃛, Ak dzielimy relacjÄ™ na grupy tak, że krotki sÄ… w tej samej grupie
wtedy i tylko wtedy, gdy pasujÄ… do wszystkich atrybutów A1, ⃛, Ak.
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;
13
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.
14
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);
15
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:
SNO | SNAME | CITY
-----+-------+--------
2 | Jones | Paris
3 | Adams | Vienna
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;
16
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:
SNO | SNAME | CITY
-----+-------+--------
2 | Jones | Paris
3 | Adams | Vienna
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);
17
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 (p[,q]): liczba staÅ‚oprzecinkowa ze znakiem; precyzja p okreÅ›la liczbÄ™ cyfr, z których
będzie składała się liczba, a skala q ilość cyfr po przecinku. Jeśli pominięto q domyślnie
przyjmowane jest 0.
·ð FLOAT: liczba zmiennoprzecinkowa ze znakiem (8 bajtów).
·ð CHAR(n): Å‚aÅ„cuch znakowy o staÅ‚ej dÅ‚ugoÅ›ci n.
·ð VARCHAR(n): Å‚aÅ„cuch znakowy o zmiennej dÅ‚ugoÅ›ci, maksymalnie n znaków.
1.4.2.3. Create Index
Indeksy przyspieszają dostęp do relacji. Jeśli relacja R posiada indeks na atrybucie A, wtedy pobieramy
wszystkie krotki t mające t(A) = a w czasie zależnym od liczby tych krotek, niż od rozmiaru relacji R.
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.
18
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óz
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 [, ...]]);
19
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.
20
1.4.5. Wbudowany SQL
W tej części nakreślimy sposób użycia SQL wewnątrz innego języka (tj. C). 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.
21
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.
22
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.
23
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 do
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Ä… danych
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
24
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:
25
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ą.
26
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 i 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ż znalez
ć w katalogu src/tutorial/. Plik README
w tym katalogu zawiera instrukcje jak ich używać. Aby rozpocząć tutorial wprowadz
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 \i 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ę \d służącą do pokazania informacji o systemie. Do wyświetlenia szczegółowych
informacji zastosuj \? .
27
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óz
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óz
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 z
ródłowego musi być dostępna dla serwera, nie dla klienta, ponieważ serwer
wprost czyta dane z pliku.
28
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:
+--------------+---------+---------+------+------------+
|city | temp_lo | temp_hi | prcp | date |
+--------------+---------+---------+------+------------+
|San Francisco | 46 | 50 | 0.25 | 1994-11-27 |
+--------------+---------+---------+------+------------+
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.
29
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 znalez
ć 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';
30
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 znalez
ć 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)
31
 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.
32
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 |
+----------+----------+
33
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}',
34
 '{{"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.
35