Rozdział 1. 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 D₁, D₂, ... D_k, zapisany jako D₁ × D₂ × ... × D_k jest zbiorem wszystkich krotek v₁, v₂, ... v_k, takich jak v₁ D₁, v₂ D₂, ... v_k D_k.

Na przykład, gdy mamy k=2, D₁={0,1} oraz D₂={a,b,c} wtedy D₁ × D₂ 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 † D₁ × D₂ × ... × D_k.

Na przykład {(0,a),(0,b),(1,a)} jest relacją; tzn. zbiorem D₁ × D₂ wspomnianych wyżej.

Elementy relacji są nazywane krotkami. Każda relacja jakiegoś iloczynu kartezjańskiego D₁ × D₂ × ... × D_k 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 A₁, A₂, ... A_k. Jest dziedzina D_i, dla każdego atrybutu A_i, 1 <= i <= k, skąd brane są wartości atrybutów. Często schemat relacji zapisuje się w ten sposób: R(A₁, A₂, ... A_k).

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 R będzie tabelą zawierającą atrybut A. Ă_A=a(R) = {t R &mid; 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) &mid; 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 k₁ i niech S będzie tabelą o budowie k₂. R × S jest zbiorem wszystkich k₁ + k₂-krotek gdzie pierwsza k₁ forma krotki z R, a k₂ 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 &setmn;): 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

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óźniej definiujemy dzielenie jako:

• R ÷ S = {t &mid; t_s S t_r R

lub tak t_r(A,B)=t't_r(C,D)=t_s}, gdzie  t_r(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

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 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) &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ę:

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óź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. 

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óź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 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 A₁, &tdot;, A_k 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_k.

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:

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;

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

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.

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

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

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

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ń

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ż 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 \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 \? .

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:

+--------------+---------+---------+------+------------+

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

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

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.

23

---