Podręczna pomoc dla programistów aplikacji bazodanowych
Język SQL jest podstawowym narzędziem programistów i operatorów baz danych. Posiada stosunkowo niewiele instrukcji, a jednak za jego pomocą można wykonywać wszystkie operacje na danych, tabelach i bazach. Często jednak podczas pracy trzeba przypomnieć sobie składnię instrukcji, znaczenie jej parametrów lub sposób korzystania z niej. Sprawę dodatkowo komplikuje to, że implementacje języka SQL w różnych systemach zarządzania bazami danych różnią się nieznacznie od siebie. Przetrząsanie kilkusetstronicowej dokumentacji zwykle zajmuje zbyt wiele czasu. Programistom, pracującym najczęściej pod presją czasu, potrzebne jest podręczne źródło podstawowych informacji.
Taką właśnie rolę pełni książka 'SQL. Leksykon kieszonkowy'. Zgromadzono w niej opisy poleceń języka w implementacjach dla najpopularniejszych systemów baz danych -- Oracle, DB2, MS SQL Server oraz MySQL. Opis każdego z poleceń jest zilustrowany przykładami, co dodatkowo ułatwia zrozumienie jego zastosowania. W książce opisano:
Funkcje grupowania i sumowania
Funkcje przetwarzające dane
Polecenie SELECT wraz z podzapytaniami i funkcjami agregującymi
Sposoby uaktualniania i usuwania danych
Metody wprowadzania danych
Zarządzanie transakcjami
Złączenia tabel
Darmowy fragment publikacji:
IDZ DO
IDZ DO
PRZYK£ADOWY ROZDZIA£
PRZYK£ADOWY ROZDZIA£
SPIS TREĎCI
SPIS TREĎCI
KATALOG KSI¥¯EK
KATALOG KSI¥¯EK
KATALOG ONLINE
KATALOG ONLINE
ZAMÓW DRUKOWANY KATALOG
ZAMÓW DRUKOWANY KATALOG
TWÓJ KOSZYK
TWÓJ KOSZYK
DODAJ DO KOSZYKA
DODAJ DO KOSZYKA
CENNIK I INFORMACJE
CENNIK I INFORMACJE
ZAMÓW INFORMACJE
ZAMÓW INFORMACJE
O NOWOĎCIACH
O NOWOĎCIACH
ZAMÓW CENNIK
ZAMÓW CENNIK
CZYTELNIA
CZYTELNIA
FRAGMENTY KSI¥¯EK ONLINE
FRAGMENTY KSI¥¯EK ONLINE
Wydawnictwo Helion
ul. Chopina 6
44-100 Gliwice
tel. (32)230-98-63
e-mail: helion@helion.pl
SQL. Leksykon
kieszonkowy
Autor: Jonathan Gennick
T³umaczenie: Tomasz Pêdziwiatr
ISBN: 83-7361-596-2
Tytu³ orygina³u: SQL Pocket Guide
Format: B5, stron: 192
Podrêczna pomoc dla programistów aplikacji bazodanowych
Jêzyk SQL jest podstawowym narzêdziem programistów i operatorów baz danych.
Posiada stosunkowo niewiele instrukcji, a jednak za jego pomoc¹ mo¿na wykonywaæ
wszystkie operacje na danych, tabelach i bazach. Czêsto jednak podczas pracy trzeba
przypomnieæ sobie sk³adniê instrukcji, znaczenie jej parametrów lub sposób korzystania
z niej. Sprawê dodatkowo komplikuje to, ¿e implementacje jêzyka SQL w ró¿nych
systemach zarz¹dzania bazami danych ró¿ni¹ siê nieznacznie od siebie. Przetrz¹sanie
kilkusetstronicowej dokumentacji zwykle zajmuje zbyt wiele czasu. Programistom,
pracuj¹cym najczêġciej pod presj¹ czasu, potrzebne jest podrêczne ĥród³o
podstawowych informacji.
Tak¹ w³aġnie rolê pe³ni ksi¹¿ka „SQL. Leksykon kieszonkowy”. Zgromadzono w niej
opisy poleceñ jêzyka w implementacjach dla najpopularniejszych systemów baz danych
— Oracle, DB2, MS SQL Server oraz MySQL. Opis ka¿dego z poleceñ jest zilustrowany
przyk³adami, co dodatkowo u³atwia zrozumienie jego zastosowania. W ksi¹¿ce opisano:
• Funkcje grupowania i sumowania
• Funkcje przetwarzaj¹ce dane
• Polecenie SELECT wraz z podzapytaniami i funkcjami agreguj¹cymi
• Sposoby uaktualniania i usuwania danych
• Metody wprowadzania danych
• Zarz¹dzanie transakcjami
• Z³¹czenia tabel
Spis treści
Wprowadzenie ...................................................n..................... 7
Struktura książki ...................................................ą....................................8
Informacje zwrotne...................................................ą................................9
Konwencje typograficzne...................................................ą......................9
Podziękowania ...................................................ą.....................................10
Przykłady kodu...................................................ą....................................11
Funkcje ...................................................n................................ 13
Funkcje przetwarzania daty ...................................................ą...............15
Funkcje liczbowe i matematyczne ...................................................ą.....22
Funkcje trygonometryczne ...................................................ą.................25
Funkcje tekstowe...................................................ą..................................26
Pozostałe funkcje...................................................ą..................................33
Funkcje grupowania i sumowania .................................... 34
Funkcje agregujące...................................................ą...............................34
Klauzula GROUP BY...................................................ą...........................35
Użyteczne techniki stosowania klauzuli GROUP BY ........................38
Klauzula HAVING ...................................................ą..............................39
Rozszerzenia klauzuli GROUP BY (Oracle) ........................................41
Rozszerzenia klauzuli GROUP BY (SQL Server)................................44
Konwersja typów danych ...................................................n 46
Funkcja ANSI/ISO CAST ...................................................ą...................47
Funkcja ANSI/ISO EXTRACT ...................................................ą...........48
Konwersja daty i czasu (Oracle) ...................................................ą........49
Konwersja wartości liczbowych (Oracle)...............................................54
Pozostałe funkcje konwersji (Oracle) ...................................................ą57
Konwersja daty i czasu (DB2) ...................................................ą............58
Konwersja wartości liczbowych (DB2).................................................62
Inne funkcje konwersji (DB2) ...................................................ą.............64
Konwersja daty i czasu (SQL Server) ...................................................ą64
Konwersja wartości liczbowych (SQL Server) ....................................69
Spis treści
3
Inne funkcje konwersji (SQL Server)...................................................ą.71
Konwersja daty i czasu (MySQL) ...................................................ą......72
Konwersja wartości liczbowych (MySQL)...........................................77
Literały...................................................n................................ 79
Literały tekstowe...................................................ą..................................79
Literały liczbowe...................................................ą..................................81
Literały daty i czasu ...................................................ą............................81
Pobieranie danych...................................................n............. 83
Klauzula SELECT...................................................ą.................................83
Słowa kluczowe ALL i DISTINCT...................................................ą.....93
Klauzula FROM ...................................................ą...................................95
Klauzula WHERE ...................................................ą................................99
Klauzula GROUP BY...................................................ą...........................99
Klauzula HAVING ...................................................ą............................100
Klauzula ORDER BY ...................................................ą.........................100
Podzapytania...................................................n................... 101
Klauzula WITH ...................................................ą..................................102
Klauzula WITH i podzapytania skorelowane...................................104
Predykaty...................................................n.......................... 106
Predykaty porównań grupowych...................................................ą....107
Większa liczba wartości po lewej stronie porównania (Oracle) .....109
Predykaty EXISTS ...................................................ą..............................110
Predykaty IN ...................................................ą......................................110
Predykaty BETWEEN...................................................ą........................111
Predykaty LIKE...................................................ą..................................112
Scalanie danych ...................................................n............... 113
Uaktualnianie danych ...................................................n.... 115
Proste uaktualnianie ...................................................ą..........................115
Nowe wartości pozyskiwane z podzapytań .....................................116
Uaktualnianie danych za pośrednictwem kursora...........................117
Uaktualnianie danych za pomocą widoków i podzapytań.............117
Uaktualnianie partycji (Oracle) ...................................................ą........118
Zwracanie uaktualnionych danych (Oracle) .....................................118
Klauzula FROM instrukcji UPDATE (SQL Server) ..........................119
4
SQL. Leksykon kieszonkowy
Unie...................................................n.................................... 120
Operacja UNION i UNION ALL ...................................................ą.....120
Kolejność przetwarzania instrukcji...................................................ą..122
Operacja EXCEPT (lub MINUS)...................................................ą.......123
Operacja INTERSECT...................................................ą........................125
Usuwanie danych ...................................................n............ 127
Usuwanie wszystkich wierszy ...................................................ą.........129
Usuwanie danych z widoków i podzapytań ....................................130
Usuwanie danych z partycji (Oracle) .................................................130
Zwracanie usuwanych danych (Oracle) ............................................131
Podwójna klauzula FROM (SQL Server) ...........................................132
Wartości NULL ...................................................n............... 133
Predykaty dla wartości NULL ...................................................ą.........133
Wartości NULL w wyrażeniach CASE ..............................................135
Funkcje operujące wartościami NULL (Oracle)................................135
Funkcje operujące wartościami NULL (DB2)....................................136
Funkcje operujące wartościami NULL (SQL Server) .......................137
Funkcje operujące wartościami NULL (MySQL)..............................137
Wprowadzanie danych...................................................n... 138
Wprowadzanie pojedynczych wierszy ..............................................138
Obiekty docelowe podczas wprowadzania danych.........................140
Wprowadzanie danych do podzapytań ............................................140
Wprowadzanie danych
za pomocą ścieżek bezpośrednich (Oracle)....................................141
Zwracanie wprowadzonych wartości (Oracle) .................................142
Wprowadzenie danych do wielu tabel (Oracle) ...............................143
Wyrażenia CASE ...................................................n............. 145
Proste wyrażenia CASE ...................................................ą....................145
Przeszukiwane wyrażenia CASE...................................................ą.....146
Wyrażenia regularne...................................................n....... 148
Wyrażenia regularne (Oracle) ...................................................ą..........148
Wyrażenia regularne (SQL Server)...................................................ą..151
Wyrażenia regularne (MySQL) ...................................................ą........151
Spis treści
5
Zapytania hierarchiczne ...................................................n 153
Klauzula WITH rekurencyjnych zapytań ANSI/ISO (DB2) ...........153
Składnia klauzuli CONNECT BY (Oracle).............................................155
Zapytania rekurencyjne...................................................n.. 161
Zapytania retrospektywne (Oracle)................................ 161
Zarządzanie transakcjami................................................ 162
Tryb automatycznego zatwierdzania.................................................163
Rozpoczynanie transakcji ...................................................ą.................164
Kończenie transakcji ...................................................ą..........................167
Przerwanie transakcji ...................................................ą........................169
Przerwanie transakcji i powrót do wyznaczonego punktu.............170
Złączanie tabel ...................................................n................ 171
Koncepcja złączenia...................................................ą...........................171
Złączenia bezwarunkowe ...................................................ą.................173
Złączenia wewnętrzne...................................................ą.......................174
Złączenia wyznaczane za pomocą nierówności ...............................178
Złączenia zewnętrzne...................................................ą........................179
Skorowidz...................................................n......................... 185
6
SQL. Leksykon kieszonkowy
Zmiana wielkości liter ciągu tekstowego
Aby zamienić litery ciągu tekstowego na wielkie lub małe, należy
zastosować odpowiednio funkcje 722 4 lub .19 4.
722 4
EKæIAVGMUVQY[
.19 4
EKæIAVGMUVQY[
Serwer Oracle udostępnia również funkcję +0+6 #2
EKæIAVGMUVQY[ ,
której zadaniem jest ustanowienie wielkiej litery na początku
każdego słowa ciągu tekstowego i zapewnienie, że pozostałe litery
każdego słowa będą małymi literami.
Baza danych DB2 obsługuje również inne nazwy dla funkcji 722 4
i .19 4 — 7 #5 i . #5 .
Pozostałe funkcje
Wśród funkcji bazy danych Oracle są dostępne dwie, które nie
spełniają kryteriów żadnej z wymienionych kategorii. Nie oznacza
to jednak, że są mniej użyteczne.
)4 #6 56
YCTVQħè=YCTVQħè?
Funkcja ta zwraca największą wartość z listy wprowadzo-
nych wartości. Danymi wejściowymi mogą tu być zarówno
liczby, jak i daty bądź ciągi tekstowe.
. #56
YCTVQħè=YCTVQħè?
Funkcja ta zwraca najmniejszą wartość z listy wprowadzo-
nych wartości. Danymi wejściowymi mogą tu być zarówno
liczby, jak i daty bądź ciągi tekstowe.
Funkcje grupowania i sumowania
34
Funkcje grupowania i sumowania
Język SQL umożliwia grupowanie wierszy w zbiory, a następnie
zestawianie uzyskanych wyników na wiele sposobów. Ostatecznie
zwracany jest pojedynczy wiersz utworzony na bazie takiego
zbioru. Uzyskanie wspomnianego rezultatu wymaga zastosowa-
nia klauzul )4172$; lub *#8+0) oraz funkcji agregujących.
Funkcje agregujące
Funkcje agregujące pobierają jako dane wejściowe zbiór wartości,
po jednej z każdego wiersza, i zwracają jedną wartość wyniku.
Jedną z najczęściej wykorzystywanych funkcji agregujących jest
1706. Jej zadanie polega na zliczaniu wszystkich niepustych war-
tości kolumny. Przedstawiona poniżej instrukcja zlicza wszystkie
odsyłacze zapisane w tabeli CVTCMELG.
5 . 6 1706
WTNACVTCMELK
(41/CVTCMELG
Dołączenie do polecenia słów kluczowych #.. i +56+0 6 pozwala
na określenie, czy wszystkie niepuste (różne od 07..) wartości
zostaną wykorzystane jako dane wejściowe oraz czy odrzucone
zostaną wartości powtarzające się.
5 . 6 1706
+56+0 6KFAOKCUVC
1706
#..KFAOKCUVC
(41/CVTCMELG
Słowo kluczowe #.. jest dołączane w sposób domyślny — instruk-
cja 1706
Y[TCľGPKG jest tożsama z instrukcją 1706
#..Y[TCľGPKG .
Funkcja 1706 ma szczególny charakter, gdyż umożliwia prze-
kazanie jako argumentu symbolu gwiazdki ( ).
5 . 6 1706
(41/CVTCMELG
Funkcje grupowania i sumowania
35
W przypadku zastosowania instrukcji 1706
zliczaniu podle-
gają wiersze, a nie wartości kolumn. Fakt występowania wartości
07.. w kolumnie nie ma tu żadnego znaczenia, ponieważ od-
rzucanie wartości pustych występuje jedynie podczas zliczania
wartości kolumn, a nie wierszy jako całości.
W tabeli 3. zostały zestawione najczęściej wykorzystywane funkcje
agregujące. Większość producentów baz danych implementuje
wszystkie z wymienionych funkcji. Na szczególną uwagę za-
sługuje w tym przypadku firma Oracle, która wyposaża swoje
produkty w bardzo wiele funkcji agregujących, choć składnia
ich wywołania często jest dość skomplikowana. Więcej informacji
na ten temat znajduje się w dokumentacji serwera.
Klauzula GROUP BY
Użyteczność funkcji agregujących jest zauważalna dopiero wów-
czas, gdy zastosuje się je do grupy wierszy, a nie do wszystkich
wierszy tabeli. W tym celu trzeba zastosować klauzulę )4172$;.
Kolejna z prezentowanych instrukcji umożliwia wyznaczenie
liczby atrakcji turystycznych w każdym mieście.
5 . 6OPCYCAOKCUVC 1706
(41/OKCUVCO+00 4,1+0CVTCMELGC
10OKFAOKCUVC CKFAOKCUVC
)4172$;OPCYCAOKCUVC
Tabela 3. Najczęściej wykorzystywane funkcje agregujące
Funkcja
#8)
Z
1706
Z
/#:
Z
/ +#0
Z
Opis
Zwraca wartość średnią ze zbioru liczb
Zlicza wartości różne od 07.. danego zbioru
Zwraca największą wartość ze zbioru
Zwraca wartość mediany (wartość środkową) danego zbioru liczb.
Zwracana wartość może być wynikiem interpolacji. Funkcja jest
dostępna jedynie w bazie danych Oracle
36
SQL. Leksykon kieszonkowy
/+0
Z
56 8
Z
57/
Z
8#4+#0
Z
Zwraca najmniejszą wartość ze zbioru
Zwraca wartość odchylenia standardowego dla zbioru wartości.
W serwerze SQL Server w nazwie funkcji 56 8 występuje tylko
jedna litera
Zwraca wartość sumy liczb zbioru
Zwraca wartość wariancji dla zbioru wartości. W serwerze SQL
Server funkcja ta ma nazwę 8#4. Nie występuje natomiast
w bazach danych DB2 i MySQL
Podczas wykonywania tego typu zapytania baza danych w pierw-
szej kolejności sortuje wiersze, a następnie grupuje je zgodnie
z wyrażeniem określonym w klauzuli )4172$;.
/WPKUKPI2KEVWTGF4QEMU
/WPKUKPI8CNNG[5RWT
/WPKUKPI5JKRYTGEM6QWTU
)NCFUVQPG*QGIJ2GV CUMGV QORCP[
*CPEQEM3WKPE[5VGCO*QKUV
*CPEQEM6GORNG,CEQD
*CPEQEM(KPNCPFKC7PKXGTUKV[
)GTOHCUM5GPG[0CVKQPCN9KNFNKHG4GHWIG
Grupy mogą się niekiedy składać tylko z jednego wiersza. Gru-
powanie wymaga zazwyczaj przeprowadzenia pewnej formy
sortowania danych. Niemniej, jak można zauważyć na powyższym
przykładzie, sortowanie musi być wykonywane jedynie do pew-
nego etapu — do chwili pogrupowania stosownych wierszy.
Po utworzeniu grup każda funkcja agregująca jest wykonywana
tylko raz na danej grupie. Wartość zastosowanej w tym przypadku
funkcji 1706
jest wyznaczana oddzielnie dla każdej grupy.
/WPKUKPI
Funkcje grupowania i sumowania
37
/WPKUKPI
/WPKUKPI
)NCFUVQPG
*CPEQEM
*CPEQEM
*CPEQEM
)GTOHCUM
Wszystkie kolumny, którym nie odpowiada żadna wartość funkcji
agregującej, zostały zastąpione jednym wpisem.
/WPKUKPI
)NCFUVQPG
*CPEQEM
)GTOHCUM
W praktyce sprowadzenie wielu wierszy do jednego wiersza
wartości zagregowanych oznacza, że funkcja agregująca musi zo-
stać zastosowana w odniesieniu do dowolnej kolumny nie objętej
klauzulą )4172$;.
Użyteczne techniki
stosowania klauzuli GROUP BY
W dalszej części tego podrozdziału zostały opisane niektóre, czę-
ściej stosowane techniki budowania zapytań z klauzulą )4172$;.
38
SQL. Leksykon kieszonkowy
Zmniejszenie liczby kolumn
w klauzuli GROUP BY
Niekiedy zachodzi konieczność umieszczenia danej kolumny
w sekcji 5 . 6 zapytania z klauzulą )4172$;, ale bez umieszcza-
nia tej kolumny w klauzuli )4172$;. Jako przykład można roz-
ważyć przedstawione poniżej zapytanie, w którym nazwa miasta
wynika z identyfikatora miasta.
5 . 6OKFAOKCUVCPPCYCAOKCUVC 1706
(41/OKCUVCO+00 4,1+0CVTCMELGC
10OKFAOKCUVC CKFAOKCUVC
)4172$;OKFAOKCUVCOPCYCAOKCUVC
W takim przypadku, zamiast grupowania względem dwóch ko-
lumn, znacznie korzystniejsze będzie przeprowadzenie grupowa-
nia jedynie względem kolumny KFAOKCUVC. Skróceniu ulega w ten
sposób klucz sortowania. Sortowanie grupujące będzie prawdo-
podobnie wykonane znacznie szybciej i zużyje mniej przestrzenni
dyskowej. Jednym ze sposobów osiągnięcia zamierzonego efektu
jest zastosowanie następującego zapytania.
5 . 6OKFAOKCUVC/#:
OPCYCAOKCUVC 1706
(41/OKCUVCO+00 4,1+0CVTCMELGC
10OKFAOKCUVC CKFAOKCUVC
)4172$;OKFAOKCUVC
Kolumna PCYCAOKCUVC została usunięta z klauzuli )4172$;. Jedno-
cześnie do sekcji 5 . 6 zostało dodane wywołanie funkcji /#:.
Dzięki temu spełnione jest założenie, że wszystkie wyrażenia
nie objęte klauzulą )4172$; muszą być uwzględnione w funkcji
agregującej. W rezultacie otrzymujemy tak samo użyteczną war-
tość nazwy miasta. Wynika to z faktu, że wszystkie nazwy miast
należące do grupy miast o jednakowej wartości KFAOKCUVC są takie
same. Funkcja /#: może więc zwrócić tylko tę jedną nazwę.
Funkcje grupowania i sumowania
39
Grupowanie przed złączeniem
W prezentowanych wcześniej przykładach zastosowania klauzuli
)4172$; definiowane były złączenia wykonywane przed operacją
grupowania. Wykorzystanie podzapytań umożliwia wykonanie
zapytania w inny sposób, tak by złączenie zostało wykonane po
zakończeniu działania funkcji agregującej.
5 . 6OPCYCAOKCUVCCITNKEDCACVT
(41/OKCUVCO+00 4,1+0
5 . 6KFAOKCUVC 1706
NKEDCACVT
(41/CVTCMELG
)4172$;KFAOKCUVC CIT
10OKFAOKCUVC CITKFAOKCUVC
Zaletą zastosowania tej wersji zapytania jest to, że złączenie jest
wykonywane na znacznie mniejszej liczbie wierszy, niż gdyby było
przeprowadzone przed agregacją danych. Dodatkową korzyścią
jest zmniejszenie ilości potrzebnej przestrzeni dyskowej oraz pa-
mięci operacyjnej. Wiersze poddawane grupowaniu nie zawierają
bowiem żadnych informacji pochodzących z tabeli OKCUVC.
Klauzula HAVING
Klauzula *#8+0) nakłada ograniczenia na wiersze zwracane przez
zapytanie zawierające klauzulę )4172 $;. Na przykład w celu
pobrania nazw tylko tych miast, w których występuje więcej niż
jedna atrakcja turystyczna, można zastosować instrukcję:
5 . 6OPCYCAOKCUVC 1706
(41/OKCUVCO+00 4,1+0CVTCMELGC
10OKFAOKCUVC CKFAOKCUVC
)4172$;OPCYCAOKCUVC
*#8+0) 1706
Nigdy nie należy umieszczać w klauzuli *#8+0) warunku, który nie
obejmuje funkcji agregacji. Rozważmy kolejne zapytanie, które
40
SQL. Leksykon kieszonkowy
udostępnia informacje na temat liczby atrakcji turystycznych
Keweenaw Peninsula.
5 . 6OPCYCAOKCUVC 1706
(41/OOKCUVC+00 4,1+0CVTCMELGC
10OKFAOKCUVC CKFAOKCUVC
)4172$;OPCYCAOKCUVC
*#8+0)OPCYCAOKCUVC+0
QRRGT*CTDQT *CPEQEM
Znacznie efektywniejsze jest zastosowane zapytania, w którym
ograniczenia nakładane na nazwy miast są wyrażone za pomocą
klauzuli 9* 4 .
5 . 6OPCYCAOKCUVC 1706
(41/OOKCUVC+00 4,1+0CVTCMELGC
10OKFAOKCUVC CKFAOKCUVC
9* 4 OPCYCAOKCUVC+0
QRRGT*CTDQT *CPEQEM
)4172$;OPCYCAOKCUVC
Klauzula 9* 4 zmniejsza liczbę wierszy podawanych działaniu
klauzuli )4172$;. Konieczne jest więc zapisanie mniejszej liczby
wierszy i wykonanie mniejszej liczby operacji funkcji agregującej.
Klauzula *#8+0) znajduje zastosowanie w przypadku filtrowania
wierszy będących wynikiem operacji )4172 $;, jeśli filtrowaniu
podlegają agregowane wartości.
Rozszerzenia klauzuli GROUP BY
(Oracle)
W bazach danych Oracle zostało zaimplementowanych kilka
użytecznych rozszerzeń klauzuli )4172$;. Należą do nich: 41..72,
7$ oraz )4172+0) 5 65. Funkcje poszczególnych opcji zostały
omówione w kolejnych podrozdziałach. Dodatkowo opisane zo-
stały również pewne funkcje skalarne, które ułatwiają przetwa-
Funkcje grupowania i sumowania
41
rzanie danych pozyskanych w wyniku zastosowania wspomnia-
nych rozszerzeń.
Rozszerzenie ROLLUP (Oracle)
Rozszerzenie klauzuli )4172$; o słowo kluczowe 41..72 powoduje
dodanie wiersza podsumowania do każdej grupy danych.
5 . 6JPCYCAJTCDUVYCOPCYCAOKCUVC
1706
CKFACVTCMELK NKEDC
(41/
JTCDUVYCJ+00 4,1+0OKCUVCO
10JKFAJTCDUVYC OKFAJTCDUVYC
+00 4,1+0CVTCMELGC
10OKFAOKCUVC CKFAOKCUVC
)4172$;41..72
JPCYCAJTCDUVYCOPCYCAOKCUVC
*#8+0) 1706
CKFACVTCMELK
Poniżej został zamieszczony wynik wykonania instrukcji. Wiersze
pogrubione zostały wygenerowane w wyniku użycia opcji 41..72.
0# 9#A*4#$569#0# 9#A/+#56#.+ $#
#NIGT/WPKUKPI
#NIGT
*QWIJVQP*CPEQEM
*QWIJVQP
/CTSWGVVG+UJRGOKPI
/CTSWGVVG/CTSWGVVG
/CTSWGVVG
Dołączenie klauzuli )4172$; spowodowało wygenerowanie stan-
dardowego podsumowania względem miast. Słowo kluczowe
41..72 odpowiada za dodanie podsumowań na wszystkich po-
zostałych poziomach — wygenerowanych dla poszczególnych
hrabstw i dla całego zestawienia. Hrabstwo Marquette ma pięć
atrakcji turystycznych, natomiast całe zestawienie informuje
o dwudziestu atrakcjach.
42
SQL. Leksykon kieszonkowy
Opcja 41..72 nie musi być stosowana w odniesieniu do wszystkich
kolumn klauzuli )4172$;. Przykładowy, przedstawiony poniżej,
fragment zapytania umożliwia zliczenie atrakcji wszystkich miast
i wszystkich hrabstw, ale nie powoduje wygenerowania ogólnej
wartości sumy, widocznej w poprzednim przykładzie.
)4172$;JPCYCAJTCDUVYC41..72
OPCYCAOKCUVC
Rozszerzenie CUBE (Oracle)
Dodanie do instrukcji słowa kluczowego 7$ powoduje wygene-
rowanie podsumowań dla wszystkich możliwych kombinacji ko-
lumn wymienionych w zapytaniu oraz dołączenie ogólnej wartości
sumy.
5 . 6JPCYCAJTCDUVYCOPCYCAOKCUVC
1706
CKFACVTCMELK NKEDC
(41/
JTCDUVYCJ+00 4,1+0OKCUVCO
10JKFAJTCDUVYC OKFAJTCDUVYC
+00 4,1+0CVTCMELGC
10OKFAOKCUVC CKFAOKCUVC
9* 4 JPCYCAJTCDUVYC *QWIJVQP
)4172$; 7$
JPCYCAJTCDUVYCOPCYCAOKCUVC
0# 9#A*4#$569#0# 9#A/+#56#.+ $#
*CPEQEM
*QWIJVQP
*QWIJVQP*CPEQEM
Rozszerzenie GROUPING SETS (Oracle)
Wprowadzona w bazie danych Oracle 9i opcja )4172+0)5 65 umo-
żliwia wyznaczanie dowolnych obszarów grupowania.
5 . 6JPCYCAJTCDUVYCOPCYCAOKCUVC
1706
CKFACVTCMELK NKEDC
Funkcje grupowania i sumowania
43
(41/
JTCDUVYCJ+00 4,1+0OKCUVCO
10JKFAJTCDUVYC OKFAJTCDUVYC
+00 4,1+0CVTCMELGC
10OKFAOKCUVC CKFAOKCUVC
)4172$;
)4172+0)5 65
JPCYCAJTCDUVYCOPCYCAOKCUVC
*#8+0) 1706
CKFACVTCMELK
0# 9#A*4#$569#0# 9#A/+#56#.+ $#
#NIGT
*QWIJVQP
/CTSWGVVG
*CPEQEM
+UJRGOKPI
/CTSWGVVG
/WPKUKPI
Zastosowane w tym przykładzie rozszerzenie )4172+0)5 65 powo-
duje wykonanie funkcji agregującej dla hrabstw i miast. Uzyska-
nie takiego samego wyniku bez wykorzystania opcji )4172+0)5 65
wymagałoby zrealizowania dwóch zapytań.
Funkcje związane z klauzulą GROUP BY
(Oracle)
Jako kolejne zostaną przedstawione funkcje, których stosowanie
wraz z dostępnymi w bazie Oracle rozszerzeniami klauzuli )4172
$; bywa bardzo użyteczne.
)4172+0)
MQNWOPC
Funkcja ta zwraca wartość 1, jeśli wynikiem operacji 7$ ,
41..72 lub )4172+0)5 65 była wartość 07... W przeciwnym
przypadku wynikiem jest wartość 0.
)4172+0)A+
MQNWOPCMQNWOPC
Działanie tej funkcji jest podobne do funkcji )4172+0). Róż-
nica polega na tym, że w jej wyniku zwracany jest wektor
44
SQL. Leksykon kieszonkowy
bitów o wartościach 1 lub 0, zależnych od tego, czy odpo-
wiadające im kolumny zawierają wartości 07.., wygenero-
wane przez rozszerzenia klauzuli )4172 $;. Funkcja ta jest
dostępna w bazie danych Oracle w wersji 9i i kolejnych.
)4172A+
Funkcja ta umożliwia wyznaczenie duplikowanych wierszy,
generowanych przez opcje 7$ , 41..72 i )4172+0)5 65. Wyni-
kiem jej działania jest wartość z przedziału od 0 do n–1, od-
powiadająca każdemu wierszowi zbioru o n duplikatach.
Poprzez wykorzystanie zwracanej przez funkcję wartości
można zdecydować o tym, ile zduplikowanych wierszy po-
winno pozostać w wyniku. Aby wyeliminować wszystkie du-
plikaty, wystarczy zastosować klauzulę *#8+0))4172A+
.
Rozszerzenia klauzuli GROUP BY
(SQL Server)
W serwerze SQL, podobnie jak w bazie danych Oracle, również
zaimplementowane zostały rozszerzenia 7$ i 41..72. Niestety
sposób ich wykorzystania jest nieco odmienny i mniej elastyczny.
Rozszerzenie ROLLUP (SQL Server)
Opcja 41..72 dodaje wiersz podsumowania dla każdej grupy
danych.
5 . 6JPCYCAJTCDUVYCOPCYCAOKCUVC
1706
CKFACVTCMELK NKEDC
(41/
JTCDUVYCJ+00 4,1+0OKCUVCO
10JKFAJTCDUVYC OKFAJTCDUVYC
+00 4,1+0CVTCMELGC
10OKFAOKCUVC CKFAOKCUVC
)4172$;JPCYCAJTCDUVYCOPCYCAOKCUVC9+6*41..72
*#8+0) 1706
CKFACVTCMELK
Funkcje grupowania i sumowania
45
Uzyskany rezultat jest identyczny z prezentowanym podczas
omawiania rozszerzenia 41..72 bazy danych Oracle. Serwer SQL
Server nie pozwala jednak na stosowanie opcji 41..72 jedynie
w odniesieniu do wybranych kolumn klauzuli )4172$;. Rozsze-
rzenie obejmuje wszystkie kolumny lub nie obejmuje żadnej z nich.
Rozszerzenie CUBE (SQL Server)
Opcja 7$ generuje podsumowania dla wszystkich kombinacji
kolumn ujętych w klauzuli )4172 $;. Dodaje także ogólne pod-
sumowanie.
5 . 6JPCYCAJTCDUVYCOPCYCAOKCUVC
1706
CKFACVTCMELK NKEDC
(41/
JTCDUVYCJ+00 4,1+0OKCUVCO
10JKFAJTCDUVYC OKFAJTCDUVYC
+00 4,1+0CVTCMELGC
10OKFAOKCUVC CKFAOKCUVC
9* 4 JPCYCAJTCDUVYC *QWIJVQP
)4172$;JPCYCAJTCDUVYCOPCYCAOKCUVC9+6* 7$
Uzyskany wynik nie odbiega w żaden sposób od rezultatu wyko-
nania zapytania z opcją 7$ w bazie danych Oracle (opisanego
wcześniej). Podobnie jak w przypadku rozszerzenia 41..72, nie
można stosować opcji 7$ jedynie w odniesieniu do wybranych
kolumn klauzuli )4172$;.
46
SQL. Leksykon kieszonkowy
Pobierz darmowy fragment (pdf)
