2. Instrukcja SELECT

Do wydobywania danych z bazy służy instrukcja SELECT języka SQL. Za jej pomocą można pobierać dane z jednej lub wielu tabel, dokonywać sortowania, agregacji pewnych wartości, a także grupowania wyników zapytań. Omawianie budowy instrukcji SELECT zaczniemy od jej najprostszej postaci umożliwiającej wydobywanie danych z jednej tabeli (wyrażenia umieszczone w nawiasach [] są opcjonalne):

SELECT [DISTINCT|TOP] kolumna1 [, kolumna2, ...]

FROM tabela

[WHERE warunek1 [AND|OR warunek2 [AND|OR ...]]]

[GROUP BY kryterium1 [DESC] [,kryterium2 [DESC] [,...]]

[HAVING warunek1 [AND|OR warunek2 [AND|OR ...]]]]

[ORDER BY kryterium1 [DESC] [,kryterium2 [DESC] [,...]]];

Listing 1: Składnia podstawowej instrukcji SELECT

O ile nie ustawimy tego celowo w ustawieniach aparatu bazy danych, interpreter języka SQL nie będzie rozróżniał pomiędzy dużymi a małymi literami w przypadku słów kluczowych oraz nazw kolumn. Jakkolwiek w przypadku SQL dobrym zwyczajem jest pisanie wszelkich słów kluczowych dużymi literami.

Podobna sytuacja występuje w przypadku formatowania poszczególnych wierszy instrukcji. Zapis całości w postaci jednej linii jest możliwy, jednakże nie do końca czytelny. Dlatego zaleca się rozpoczynanie kolejnych części składowych instrukcji SQL od nowej linii.

Fakt nie rozróżniania wielkości liter w przypadku pisania instrukcji SQL i nazw kolumn nie odnosi się do samych danych przechowywanych w bazie. Jeżeli więc będziemy chcieli odszukać w którejś z tabel pewne wyrażenie tekstowe, to musimy pamiętać, że dopóki nie zastosujemy specjalnych metod porównywania łańcuchów znakowych (omówionych w dalszej części tego rozdziału) wielkość liter będzie miała znaczenie.

W przypadku nazewnictwa tabel i kolumn dobrze jest unikać polskich znaków dialektycznych. Mimo iż np. MS Access powinien sobie z nimi radzić, to inne systemy bazodanowe mogą mieć problemy z obsługą polskich znaków.

W każdym wypadku należy trzymać się jednej konwencji nazewnictwa. Ułatwi nam to późniejszą budowę zapytań do bazy w języku SQL, gdyż nie będziemy musieli zbyt często otwierać projektu tabel by sprawdzić jak nazwaliśmy daną kolumnę.

Wbrew kolejności wynikającej z umieszczania słów kluczowych budowanie instrukcji powinno się zaczynać od wypełnienia treści klauzuli FROM po której określamy tabelę z której będziemy pobierać dane. Następnie określiwszy już tabelę można po słowie SELECT wypisać listę interesujących nas kolumn oddzielonych przecinkami. Każda instrukcja w języku SQL powinna kończyć się średnikiem ;.

Przykładową instrukcję pobierającą markę, model oraz wersję pojazdu z tabeli

Auta komisu samochodowego przedstawia Listing 2. Jest on zarazem

przykładem operacji projekcji, w której wybieramy wszystkie wiersze z tabeli, ale tylko niektóre kolumny.

SELECT Marka, Model, Wersja FROM Auta;

Listing 2: Prosta instrukcja SELECT

Czasami nie znamy struktury danej tabeli a chcemy wyświetlić jej całą zawartość. Wówczas możemy posłużyć się operatorem * który wyświetli wszystkie kolumny należące do danej tabeli w kolejności w jakiej były dodawane w trakcie jej tworzenia. Zapytanie takie ilustruje Listing 3:

SELECT * FROM Auta;

Listing 3: Użycie operatora *

2.1. Operacje na kolumnach

Na liście kolumn można wyświetlać nie tylko poszczególne kolumny, ale także

wyniki operacji arytmetycznych, tekstowych, czy logicznych przeprowadzanych

na kolumnach. Argumentami tych operacji nie muszą być tylko kolumny z tabel,

ale również pewne stałe wyrażenia np. stały tekst lub liczba. Mechanizm ten pozwala otrzymywać wyniki zapytań w formie maksymalnie zbliżonej do informacji, która nas interesuje tzn. bez konieczności późniejszej „ręcznej”

obróbki danych otrzymanych poprzez zapytanie.

2.1.1. Operacje arytmetyczne

W języku SQL mamy dostępny szereg operacji arytmetycznych oraz funkcji matematycznych, które możemy stosować do kolumn i stałych wyrażeń o typie liczbowym (operacji arytmetycznych nie można wykonywać na tekstowych typach danych). Typowymi działaniami jakie możemy wykonywać na takich kolumnach są: dodawanie, odejmowanie, mnożenie, dzielenie i potęgowanie (patrz Tabela 1).

Tabela 1: Operatory arytmetyczne w języku SQL

Operator Znaczenie

+ dodawanie

- odejmowanie

* mnożenie

/ dzielenie

() nawiasowanie – zmiana kolejności wykonywanych działań

W szczególności w danym wyrażeniu dane z kolumny nie muszą się pojawić w ogóle – wówczas ta sama, stała wartość zostanie wyświetlona dla każdego z wierszy z osobna. Przykład użycia operatorów arytmetycznych ilustruje Listing 4:

SELECT Moc, Moc*2.3, Poj_Silnika/Moc, Moc * (2 + Moc/2), 2*3 FROM Auta;

Listing 4: Użycie operatorów arytmetycznych w SQL

2.1.2. Funkcje matematyczne oraz daty i czasu

SQL oferuje także zbiór przydatnych funkcji matematycznych, które można wykorzystać do konstrukcji wyrażeń. Funkcji tych możemy używać wraz z operatorami arytmetycznymi by w ten sposób konstruować zaawansowane wyrażenia matematyczne.

Dla kolumn o typie daty / czasu możemy używać funkcji wybierających z pełnej daty tylko jej część w postaci liczby. Na przykład użycie funkcji YEAR dla wartości 2005-03-01 zwróciło by liczbę 2005.

Przykładowe funkcje matematyczne oraz daty/czasu dla MS Access i MySQL zgromadzone zostały w Tabela 2.

Tabela 2: Przykładowe funkcje matematyczne oraz daty i czasu

System bazodanowy MS Access MySQL Opis

Funkcje matematyczne

ABS(x) ABS(x) Wartość bezwzględna liczby X

FIX(x) FLOOR(x)

---- CEILING(X)

M MOD N MOD(M,N) Dzielenie modulo – reszta z dzielenia M przez N

X^Y POW(X,Y) X do potęgi Y

SGN(X) SIGN(X) Zwraca 1 dla X > 0, 0 dla X = 0, -1 dla X < 0

EXP(X) EXP(X) Exponenta z X

LOG(X) LOG(X) Logarytm naturalny z X ---- LOG(A,X) Logarytm o podstawie A z X SQR(X) SQRT(X) Pierwiastek kwadratowy z X

SIN(X) SIN(X) Sinus X

COS(X) COS(X) Cosinus X

TAN(X) TAN(X) Tangens X

---- ASIN(X) Arcus Sinus X

---- ACOS(X) Arcus Cosinus X

ATN(X) ATAN(X) Arcus Tangens X

RND(X) RAND(X) Liczba losowa, wylosowana na podstawie zarodka X ---- RADIANS(X) Zamienia X podany w stopniach na radiany ---- DEGREES(X) Zamienia X podany w radianach na stopnie

Funkcje daty / czasu

YEAR(D) YEAR(D) Zwraca rok z daty/czasu D MONTH(D) MONTH(D) Zwraca miesiąc z daty/czasu D

DAY(D) DAY(D) Zwraca dzień z daty/czasu D HOUR(D) HOUR(D) Zwraca godzinę z daty/czasu D MINUTE(D) MINUTE(D) Zwraca minutę z daty/czasu D SECOND(D) SECOND(D) Zwraca dzień z daty/czasu D

DATE() CURDATE() Zwraca bieżącą datę w formacie YYYY-MM-DD

TIME() CURTIME() Zwraca bieżący czas w formacie hh:mm:ss

Przykład jednoczesnego użycia operatorów arytmetycznych i funkcji matematycznych w środowisku Access ilustruje Listing 5:

SELECT Moc, SIN(Moc), COS(Moc*1.5), RND(Poj_Silnika+Moc), FIX(SQR(Moc + 2.33) - LOG(Poj_Silnika))*(ATN(4.6)+1) FROM Auta;

Listing 5: Użycie funkcji matematycznych w SQL

Przykład użycia funkcji daty/czasu do wyłuskiwania jej poszczególnych części w postaci liczb został przedstawiony w Listing 6:

SELECT Data_Wstawienia, YEAR(Data_Wstawienia), MONTH(Data_Wstawienia), DAY(Data_Wstawienia) FROM Oferty;

Listing 6: Użycie funkcji daty w SQL

2.1.3. Wyrażenia tekstowe

Stały tekst w języku SQL umieszczany jest pomiędzy dwoma apostrofami ''

np. 'Oto tekst'. Najważniejszą cechą wyrażeń tekstowych jest możliwość ich

sklejania. W środowisku Access służy do tego operator & a w MySQL

wieloargumentowa funkcja CONCAT. Sklejać możemy również wyrażenia liczbowe

– wówczas liczby traktowane są jako tekst. Skoro kwalifikatorem wyrażeń

tekstowych jest znak ' to jak wewnątrz tekstu umieścić takowy znak? To proste –

wystarczy poprzedzić go jeszcze jednym znakiem '. Np. gdybyśmy chcieli wyświetlić tekst: Rok 99' był super to w języku SQL musielibyśmy go zapisać jako: 'Rok 99'' był super'. Wyświetlanie stałego tekstu, a także sklejanie różnych danych w jedno wyrażenie prezentuje Listing 7:

SELECT Marka&' '& Model, Marka & Model, 'Tekst stały', 'Moc*1.5 = '&(Moc*1.5), '2*1.5'

FROM Auta;

Listing 7: Sklejanie wyrażeń tekstowych i liczbowych

W systemie MySQL kwerenda z Listing 7 przyjęła by postać:

SELECT CONCAT(Marka,' ', Model), CONCAT(Marka, Model),

'Tekst stały', CONCAT('Moc*1.5 = ', Moc*1.5), '2*1.5' FROM Auta;

2.1.4. Nadawanie tytułów kolumnom i wyrażeniom

Gdy tworzymy jakieś wyrażenie na liście kolumn aparat bazy danych nadaje

mu swoją wewnętrzną nazwę np. Expr1001 (patrz poprzednie listingi). Nazwa

taka nie wnosi żadnej informacji dla osoby wywołującej potem nasze zapytanie,

lub, w przypadku gdy kolumn i wyrażeń jest dużo, nawet dla nas samych. Dzięki

słowu kluczowemu AS możemy nadawać alternatywne nazwy (tytuły) kolumnom

bądź wyrażeniom. MS Access używa niejawnie tej metody do przypisywania

tytułów do nazw kolumn (gdy w trakcie projektowania tabeli nadamy tytuł

kolumnie, to w widoku arkusza danych, bądź w wynikach zapytań zamiast nazwy

kolumny jest wyświetlany jej tytuł). Dlatego czasem w tym systemie pomimo

próby nadania przez nas tytułu jakiejś kolumnie, widzimy i tak ten określony na

poziomi projektu. W pozostałych przypadkach i systemach alternatywne

nazewnictwo kolumn działa bezproblemowo. Gdy w naszym tytule chcemy

umieścić więcej niż jeden wyraz, bądź też znajdują się w nim polskie litery, treść

takiego aliasu dobrze jest ograniczyć nawiasami prostokątnymi []. Używanie w

tytułach znaków kropki . lub przecinka , nawet wewnątrz nawiasów [] może

spowodować błąd i odmowę wykonania zapytania. Przykład użycia słowa AS do

nadawania alternatywnych nazw kolumnom i wyrażeniom ilustruje Listing 8:

SELECT Marka&' '& Model AS Auto, Moc*1.5 AS [Większa moc], Marka & Model AS [Bez odstępu], SIN(Moc)AS[Sin Mocy]

FROM Auta;

Listing 8: Użycie słowa AS do nadawania tytułów kolumnom i wyrażeniom

W niektórych systemach bazodanowych nazwy nadane w ten sposób kolumnom lub wyrażeniom mogą być potem użyte w dalszych częściach instrukcji SELECT np. w klauzuli WHERE gdzie nadaje się pewne warunki na wartości kolumn i wyrażeń. Jest to szczególnie istotne w przypadku długich nazw kolumn bądź skomplikowanych wyrażeń. MS Access niestety nie obsługuje tej funkcjonalności i przy ponownym użyciu, każde wyrażenie należy ponownie przepisać.

2.1.5. Nazwy kolumn z uwzględnieniem nazw tabel, aliasy nazw tabel

Jak zobaczymy w dalszej części, w przypadku gdy nasze zapytanie będzie pobierać dane z więcej niż jednej tabeli, wówczas jednoznaczna identyfikacja kolumn będzie miała kluczowe znaczenie. Co bowiem zrobić gdy w dwóch tabelach pojawi się kolumny o takich samych nazwach, którą z nich wybierze aparat bazy danych? Otóż żadną dopóki jasno nie określimy że chodzi nam o kolumnę z konkretnej tabeli. W tym celu można posłużyć się nazwą kolumny, poprzedzoną nazwą tabeli. W przypadku zapytań na tylko jednej tabeli postępowanie takie nie ma większego sensu, jakkolwiek jest jak najbardziej dozwolone poprzez język. Listing 9 prezentuje używanie opisanego sposobu zapisu, właśnie w zapytaniu na jednej tabeli:

SELECT Auta.Marka&' '& Auta.Model AS Auto, Auta.Moc*1.5 AS [Większa moc],

Auta.Marka & Auta.Model AS [Bez odstępu], SIN(Auta.Moc)AS[Sin Mocy]

FROM Auta;

Listing 9: Użycie pełnej nazwy kolumn

Jak widać wyniki nie różnią się od tych otrzymanych w Listing 8. Niekiedy gdy nazwa tabeli jest długa, pisanie jej przy nazwie każdej z kolumn może być uciążliwe. Dlatego również tabelom można nadać alternatywną nazwę przy pomocy słowa kluczowego AS. W odróżnieniu od użycia AS w przypadku kolumn i wyrażeń, alternatywna nazwa nadana tabeli nie jest widziana w wynikach zapytania (ze względu na fakt, że nazwy użytych tabel w ogóle nie są widziane w wynikach zapytania), natomiast można (a nawet trzeba) jej używać w każdym miejscu instrukcji zamiast oryginalnej nazwy tabeli. Ideę użycia aliasów w przypadku nazw tabel przedstawia Listing 10 (którego wyniki są identyczne jak w przypadku Listing 8 i Listing 9):

SELECT A.Marka&' '& A.Model AS Auto, A.Moc*1.5 AS [Większa moc],

A.Marka & A.Model AS [Bez odstępu], SIN(A.Moc)AS[Sin Mocy]

FROM Auta AS A;

Listing 10: Użycie słowa AS do nadawania alternatywnych nazw tabelom

2.2. Filtrowanie wyników zapytań przy pomocy klauzuli WHERE

Czasem nie interesują nas wszystkie rekordy będące wynikiem zapytania.

Wówczas chcielibyśmy ograniczyć ich ilość tylko do tych, które spełniają

określone kryteria. Dokładniej kolumny lub wyrażenia zdefiniowane na kolumnach

muszą spełniać pewne warunki dla danego wiersza, aby wiersz ten został

wyświetlony w końcowym zbiorze wyników zapytania. Do konstrukcji warunków

używany jest szereg operatorów. Najprostszymi z nich są operatory relacji

większości, mniejszości, równości itd. Pełny spis operatorów relacji zawiera

Tabela 3: Operatory relacji

Operator Opis

< Mniejszy

> Większy

<= Mniejszy bądź równy

>= Większy bądź równy

= Równy

<> Różny (nierówny)

Kryteria wyboru wierszy określamy po klauzuli WHERE instrukcji SELECT. Dany wiersz jest wyświetlany gdy cały warunek zapisany w klauzuli WHERE przyjmuje wartość prawdy (ang. TRUE). Jeżeli warunek przyjmuje wartość fałszu (ang.

FALSE) to wiersz jest odrzucany.

Proste użycie operatora relacji w klauzuli WHERE prezentuje Listing 11, w którym z tabeli Auta wybierane są tylko te samochody, których moc jest większa niż 100 KM:

SELECT Marka, Model, Wersja, Moc, Rocznik,

Poj_Silnika AS [Pojemność], Kolor, Metalic FROM Auta

WHERE Moc > 100;

Listing 11: Proste użycie klauzuli WHERE

2.2.1. Łączenie warunków, operatory AND, OR, NOT

Warunki logiczne możemy łączyć przy pomocy operatorów logicznych AND, OR oraz NOT. Tabela 4 zawiera spis operatorów logicznych wspieranych przez SQL.

Operatory te możemy łączyć stosując ich dowolne kombinacje w celu uzyskania odpowiedniego wyrażenia zwracającego prawdę lub fałsz. Należy przy tym pamiętać o poprawnym nawiasowaniu wyrażeń.

Tabela 4: Operatory logiczne

Operator Opis

p AND q Zwraca prawdę gdy oba warunki p i q są spełnione P OR q Zwraca prawdę gdy któryś z warunków p lub q jest spełniony

NOT p Zwraca prawdę gdy warunek p jest niespełniony (zaprzecza p)

W MS Access pola typy danych tak/nie widziane w widoku arkusza danych jako pola wyboru, mogą przyjmować tylko dwie wartości TRUE (gdy pole jest zaznaczone) albo FALSE (gdy pole jest odznaczone).

Do konstrukcji warunku logicznego można też użyć wyrażeń operujących na kolumnach (podobnych lub niekiedy identycznych nawet do tych, które są używane są na liście kolumn po instrukcji SELECT). Tak więc zamiast Moc >

100 można z powodzeniem napisać Moc*1.5 > 150, co stanowi ilustrację użycia wyrażenia w warunku logicznym, aczkolwiek nie powinno to zmienić sensu poprzedniego warunku.

Listing 12 używa operatora AND do konstrukcji warunku wyświetlającego jedynie te auta, których moc jest większa niż 100 KM (czyli Moc*1.5 > 150) a lakier jest typu metalicznego.

SELECT Marka, Model, Wersja, Moc, Rocznik,

Poj_Silnika AS [Pojemność], Kolor, Metalic FROM Auta

WHERE (Moc*1.5 > 150) AND (Metalic = TRUE);

Listing 12: Użycie operatora AND do konstrukcji złożonego warunku WHERE

Użycie operatora OR do połączenia obu warunków zmieniło by sens zapytania, którego wynikiem były by teraz auta o mocy większej niż 100 KM lub posiadających metaliczny lakier – ilustruje to Listing 13:

SELECT Marka, Model, Wersja, Moc, Rocznik,

Poj_Silnika AS [Pojemność], Kolor, Metalic FROM Auta

WHERE (Moc*1.5 > 150) OR (Metalic = TRUE);

Listing 13: Użycie operatora OR do konstrukcji złożonego warunku WHERE

2.2.2. Operator IN

Niekiedy, gdy pewna kolumna, bądź wyrażenie mogą przyjmować jedną z wielu wartości, pisanie skomplikowanego ciągu wyrażeń połączonych operatorem OR mogłoby być uciążliwe np.:

(Kolor = 'Srebrny') OR (Kolor = 'Złoty')

OR (Kolor = 'Czerwony') OR (Kolor = 'Niebieski')

Zamiast tego można użyć operatora IN, który w przypadku omawianego przykładu przyjąłby postać:

Kolor IN('Srebrny', 'Złoty', 'Czerwony', 'Niebieski').

Zaprzeczenie operatora IN przy pomocy operatora NOT:

Kolor NOT IN('Srebrny', 'Złoty', 'Czerwony', 'Niebieski') powoduje negację całego warunku, czyli zgodnie z prawami de Morgana ciąg nierówności połączonych operatorami AND:

(Kolor <> 'Srebrny') AND (Kolor <> 'Złoty') AND (Kolor <> 'Czerwony') AND (Kolor <> 'Niebieski')

Wartościami znajdującymi się na liście wewnątrz nawiasów operatora IN mogą być dowolne ciągi znakowe, liczby, a nawet wyniki tzw. podzapytań, o których będzie mowa w dalszej części.

Listing 14 przedstawia użycie operatora IN:

SELECT Marka, Model, Wersja, Moc, Rocznik,

Poj_Silnika AS [Pojemność], Kolor, Metalic FROM Auta

WHERE (Moc*1.5 > 150) AND (Metalic = TRUE)

AND (Kolor IN('Srebrny', 'Złoty', 'Czerwony', 'Niebieski'));

Listing 14: Użycie operatora IN

2.2.3. Operator BETWEEN

W przypadku operatora IN mieliśmy do czynienia z dyskretnym, skończonym zbiorem wartości. Co jednak zrobić gdy interesuje nas czy dane wyrażenie znajduje się pomiędzy dwiema wartościami np.: interesują nas tylko auta o pojemnościach z przedziału od 2500 cc do 3500 cc. Warunek taki można rozbić na dwa podwarunki:

(Poj_Silnika >= 2500) AND (Poj_Silnika <= 3500) Wyrażenie to można zastąpić poprzez operator BETWEEN:

Poj_Silnika BETWEEN 2500 AND 3500 Oczywiście gdybyśmy napisali:

Poj_Silnika NOT BETWEEN 2500 AND 3500

to otrzymalibyśmy wszystkie auta, których pojemność leżałaby poza tym przedziałem.

Listing 15 prezentuje użycie operatora BETWEEN:

SELECT Marka, Model, Wersja, Moc, Rocznik,

Poj_Silnika AS [Pojemność], Kolor, Metalic FROM Auta

WHERE (Moc*1.5 > 150) AND (Metalic = TRUE)

AND (Kolor IN('Srebrny', 'Złoty', 'Czerwony', 'Niebieski')) AND (Poj_Silnika BETWEEN 2500 AND 3500);

Listing 15: Użycie operatora BETWEEN

2.2.4. Operator LIKE

Czasami interesuje nas czy dane wyrażenie tekstowe zawiera w sobie pewien tekst, lub jest określonego formatu. W takich sytuacjach posługujemy się operatorem LIKE. Operator ten porównuje zadane wyrażenie ze zdefiniowanym wcześniej wzorcem. Konstrukcja wzorca zależy od naszej wiedzy na temat poszukiwanego tekstu, im wzorzec jest dokładniejszy, tym bardziej zawężony zbiór wyników otrzymamy. Składnia operatora jest następująca:

Wyrażenie LIKE 'Wzorzec'

Listing 16 dokłada warunek aby nazwa modelu auta kończyła się na sylabę

„ra” (charakterystyczną dla niektórych modeli Opla). W tym celu użyto operatora LIKE jako wzorzec przekazując '*ra', gdzie * oznacza dowolny ciąg znaków i cyfr (w szczególności ciąg ten może być nawet pusty):

SELECT Marka, Model, Wersja, Moc, Rocznik,

Poj_Silnika AS [Pojemność], Kolor, Metalic FROM Auta

WHERE (Moc*1.5 > 150) AND (Metalic = TRUE)

AND (Kolor IN('Srebrny', 'Złoty', 'Czerwony', 'Niebieski')) AND (Poj_Silnika BETWEEN 2500 AND 3500)

AND (Model LIKE '*ra');

Listing 16: Użycie operatora LIKE

W przypadku MS Access wzorzec można zbudować bardzo dokładnie, uwzględniając nawet zakres znaków lub cyfr z jakich ma pochodzić poszczególny znak w wyrażeniu. W Tabela 5 zgromadzono przykładowe wzorce dla środowiska MS Access:

Tabela 5: Przykładowe wzorce dla operatora LIKE w środowisku MS Access

Typ tekstu zastępowanego

Wzorzec Wyrażenia pasujące (LIKE zwraca TRUE)

Wyrażenia niepasujące (LIKE zwraca FALSE)

Wiele znaków a*a aa, aBa, aBBBa aBC

*ab* abc, AABB, Xab aZb, bac

Znak specjalny (używany do budowy wzorca)

a[*]a a*a aaa

Wiele znaków ab* abcdefg, abc cab, aab

Pojedynczy znak a?a aaa, a3a, aBa aBBBa Pojedyncza cyfra a#a a0a, a1a, a2a aaa, a10a

Zakres znaków [a–z] f, p, j 2, &

Wykluczenie zakresu znaków

[!a–z] 9, &, % b, a Wykluczenie cyfr [!0–9] A, a, &, ~ 0, 1, 9 Kombinacja a[!b–m]# An9, az0, a99 abc, aj0

W środowisku MySQL odpowiednikiem znaku * jest %, a znaku ? znak _ .

Chcąc użyć znaków specjalnych używanych do budowy wzorca jako znaków

występujących w szukanym tekście, należy poprzedzić je znakiem \. W

szczególności poszukując ciągu znaków postaci: \*bac we wzorcu musimy

napisać: '\\\*bac'.

2.2.5. Operator IS NULL

Niekiedy chcemy wybrać takie rekordy, w których wybrane pola nie zostały wypełnione (posiadają wpisane wartości NULL). Operator IS NULL zwraca prawdę gdy interesujące nas pole jest puste. Stosując ten operator w naszym przykładowym zapytaniu jesteśmy w stanie odsiać auta nie posiadające opisu wersji – ilustruje to Listing 17:

SELECT Marka, Model, Wersja, Moc, Rocznik,

Poj_Silnika AS [Pojemność], Kolor, Metalic FROM Auta

WHERE (Moc*1.5 > 150) AND (Metalic = TRUE)

AND (Kolor IN('Srebrny', 'Złoty', 'Czerwony', 'Niebieski')) AND (Poj_Silnika BETWEEN 2500 AND 3500)

AND (Model LIKE '*ra') AND (Wersja IS NULL);

Listing 17: Użycie operatora IS NULL

Chcąc wyświetlić auta posiadające opis wersji (czyli te dla których pole Wersja nie ma wpisanej wartości NULL) możemy posłużyć się kombinacją IS NOT NULL - co pokazuje Listing 18:

SELECT Marka, Model, Wersja, Moc, Rocznik,

Poj_Silnika AS [Pojemność], Kolor, Metalic FROM Auta

WHERE (Moc*1.5 > 150) AND (Metalic = TRUE)

AND (Kolor IN('Srebrny', 'Złoty', 'Czerwony', 'Niebieski')) AND (Poj_Silnika BETWEEN 2500 AND 3500)

AND (Model LIKE '*ra') AND (Wersja IS NOT NULL);

Listing 18: Użycie operatora IS NOT NULL

2.3. Sortowanie wyników zapytań przy pomocy klauzuli ORDER BY

W większości przypadków przydatne jest aby dane otrzymane w wyniku

zapytania były posortowane. Sortować możemy względem jednej lub większej

ilości kolumn, zarówno malejąco jak i rosnąco. W języku SQL służy do tego

klauzula ORDER BY. Zgodnie z Listing 1 umieszczamy ją na końcu instrukcji

SELECT. Jej składnia jest następująca:

ORDER BY kolumna1, kolumna2, kolumna3

W takim przypadku dane zostaną posortowane rosnąco względem kolumna1.

Następnie grupy rekordów zawierające identyczne wartości w kolumna1 zostaną posortowane według kolumna2, a dalej wewnątrz tych grup rekordy zawierające identyczne rekordy w kolumna2 zostaną posortowane według kolumna3.

Dołożenie kolejnych kolumn do listy po klauzuli ORDER BY spowoduje dalsze sortowania w coraz to mniejszych podgrupach (dla których wartości z poprzednich kolumn są takie same). Oznacza to że zazwyczaj najbardziej zauważalne są efekty sortowania po pierwszej kolumnie. W przypadku gdy zgromadzone w niej dane są unikatowe, sortowanie po dalszych kolumnach nie ma już sensu.

Domyślnym sposobem sortowania jest sortowanie rosnące. Gdyby chcieć jawnie określić sposób w jaki będziemy sortować daną kolumnę za jej nazwą należałoby umieścić słowo kluczowe ASC (dla sortowania rosnącego) lub DESC (dla sortowania malejącego). W praktyce ze względu na domyślność sortowania rosnącego słowa kluczowego ASC nie umieszcza się w ogóle. Oto przykład w którym po kolumna1 i kolumna3 sortuje się malejąco, a po kolumna3 rosnąco:

ORDER BY kolumna1 DESC, kolumna2 ASC, kolumna3 DESC Sortować możemy nie tylko po kolumnach ale również względem wyrażeń na kolumnach, podobnych do tych jakie są używane na liście tuż po instrukcji SELECT, lub w klauzuli WHERE. Ponadto nie ma wymogu aby wyrażenie bądź kolumna, po której następuje sumowanie była elementem wyświetlanym (tj. z listy po instrukcji SELECT). Oznacza to że sortować możemy np. po wyrażeniu (Poj_Silnika + Moc*9.5) nawet wówczas gdy w wyświetlanych wynikach zapytania ono nie występuje.

Listing 19 modyfikuje Listing 13 sortując wyniki względem marki malejąco (od Z do A), a auta o tej samej marce sortuje według specjalnego kryterium obliczanego na podstawie mocy i pojemności silnika.

SELECT Marka, Model, Wersja, Moc, Rocznik,

Poj_Silnika AS [Pojemność], Kolor, Metalic FROM Auta

WHERE (Moc*1.5 > 150) OR (Metalic = TRUE) ORDER BY Marka DESC, (Moc*8.0 + Poj_Silnika);

Listing 19: Sortowanie z użyciem klauzuli ORDER BY

2.4. Przetwarzanie wyników zapytań przy użyciu opcji DISTINCT i TOP

2.4.1. Wybieranie unikalnych kombinacji wartości

Opcji DISTINCT używamy gdy zależy nam na otrzymaniu unikalnej kombinacji wartości w każdym wierszu – tzn. aby każdy wiersz będący wynikiem zapytania był różny od pozostałych.

Przypuśćmy, że chcemy otrzymać spis marek wszystkich aut znajdujących się w komisie. Proste wybranie pola Marka dla każdego rekordu z tabeli Auta (Listing 20a) wyświetli co prawda listę marek, ale ze względu na fakt, że w komisie może się znajdować jednocześnie wiele aut tej samej marki, lista ta będzie zawierała niekiedy kilkukrotnie powieloną tę samą nazwę marki.

Zastosowanie opcji DISTINCT likwiduje ten problem (Listing 20b).

Podobna sytuacja będzie miała miejsce w przypadku gdybyśmy chcieli uzyskać spis wszystkich modeli aut wraz z ich markami. W takowym wypadku użycie opcji DISTINCT nie zapewni już unikalności wartości w pojedynczej kolumnie ale w całym wierszu, a zatem wyświetli unikalne kombinacje marek i modeli (patrz Listing 21).

a b

SELECT Marka FROM Auta

ORDER BY Marka;

SELECT DISTINCT Marka FROM Auta

ORDER BY Marka;

Listing 20: Użycie opcji DISTINCT w zapytaniu z pojedynczą kolumną

a b

SELECT Marka, Model FROM Auta

ORDER BY Marka, Model;

SELECT DISTINCT Marka, Model FROM Auta

ORDER BY Marka, Model;

Listing 21: Użycie opcji DISTINCT w zapytaniu z wieloma kolumnami

2.4.2. Wybieranie określonej ilości początkowych wierszy

Czasami nie interesują nas wszystkie wiersze będące wynikiem zapytania, a wystarczająca ilość poszukiwanej informacji zawarta jest już w kilku – kilkunastu początkowych wierszach. W innym przypadku – gdy pracujemy w systemie klient – serwer mogłoby się okazać, że transfer wszystkich rekordów z serwera na stację klienta zabiera bardzo dużo czasu (rekordów jest bardzo dużo), a my chcemy jedynie sprawdzić czy nasze zapytanie działa poprawnie i zobaczyć jedynie kilka wierszy. W takich sytuacjach pomocna okazuje się opcja TOP ograniczająca ilość wyświetlanych rekordów. Występuje ona w dwóch wariantach – w pierwszym z nich możemy jawnie określić maksymalną liczbę rekordów do wyświetlenia, w drugim – procent z całkowitej ilości rekordów:

SELECT TOP 5 kolumna1, kolumna2, kolumna3 ...;

SELECT TOP 25 PERCENT kolumna1, kolumna2, kolumna3 ...;

Oba sposoby przedstawione zostały w Listing 22a i Listing 22b, w których wyświetla się odpowiednio 5 pierwszych i pierwsze 45% aut o najmniejszej mocy, znajdujących się w komisie.

a b

SELECT TOP 5 Marka, Model, Wersja, Moc FROM Auta

ORDER BY Moc;

SELECT TOP 45 PERCENT Marka, Model, Wersja, Moc FROM Auta

ORDER BY Moc;

Listing 22: Użycie opcji TOP

2.5. Łączenie wyników kilku zapytań za pomocą operatora UNION

Wyniki kilku osobnych instrukcji SELECT można połączyć w jedną całość (unię) za pomocą operatora UNION. Jedynym wymaganiem jest zgodność ilości elementów występujących na liście wyrażeń (liście kolumn) każdej z instrukcji SELECT. Typy odpowiadających sobie wyrażeń z poszczególnych instrukcji łączonych operatorem UNION też powinny być zgodne, a przynajmniej powinna istnieć możliwość konwersji na jeden wspólny typ.

SELECT kolumna1 [,kolumna2,...]

FROM tablica1 [, tablica2]

...

UNION [ALL]

SELECT kolumna1 [,kolumna2,...]

FROM tablica1 [, tablica2]

...

UNION [ALL]

SELECT kolumna1 [,kolumna2,...]

FROM tablica1 [, tablica2]

...

[ORDER BY ...];

Nazwy kolumn (bądź wyrażeń) wyświetlane w wynikach będą zgodne z tymi z pierwszej kwerendy wchodzącej w skład unii.

W większości systemów bazodanowych wszystkie powtarzające się wiersze są wyświetlane tylko raz, a wyniki są sortowane rosnąco według kolumn wymienionych na liście wyrażeń po instrukcji SELECT. Aby wyświetlić wszystkie wiersze należy użyć opcji ALL (opcja ta nie działa w obecnych wersjach MS Access). Domyślny sposób sortowania można zmienić podając go jawnie po ostatniej instrukcji SELECT. Nazwy kolumn użyte w klauzuli ORDER BY muszą być zgodne z tymi z instrukcji pierwszej.

Przydatność unii jest bardzo szeroka. W większości przypadków jesteśmy w

stanie zbudować jeden zbiór wyników pochodzący niekiedy z wielu znacznie

różniących się od siebie pojedynczych zapytań, których jedyną częścią wspólną

jest ilość wyrażeń występujących po instrukcji SELECT. Poszczególne zapytania

mogą bazować na różnych tabelach i wyświetlać różne wyrażenia. W niektórych

przypadkach sens takich unii będzie oczywisty np. jednoczesne wyświetlenie nazwisk i imion zarówno klientów jak i pracowników – aby otrzymać listę wszystkich ludzi mających styczność z naszym komisem. W innych sytuacjach połączenie paru osobnych zapytań może być zupełnie bez sensu, lecz dopóki ilość wyrażeń w każdym z zapytań będzie zgodna – aparat bazy danych nie zwróci komunikatu o błędzie. Przykład takiej bezsensownej unii przedstawia Listing 23, w którym jednocześnie wyświetlane są marki i modele aut z tabeli Auta oraz nazwiska i imiona klientów z tabeli Klienci. Całość sortowana jest w odwrotnym porządku alfabetycznym względem pierwszej kolumny.

SELECT Marka, Model FROM Auta

UNION

SELECT Nazwisko, Imie FROM Klienci

ORDER BY Marka DESC;

...

Listing 23: Użycie operatora UNION

Operator UNION zwracał sumę wierszy obu łączonych zapytań. Odpowiadała ona algebraicznej sumie dwóch zbiorów, w których elementami są wiersze będące wynikami zapytań. Istnieją jeszcze dwa inne operatory odpowiadające algebraicznej różnicy (operator MINUS) oraz części wspólnej dwóch zbiorów (operator INTERSECT). Operator MINUS zwraca wszystkie wiersze będące wynikiem zapytania pierwszego i nie będące wynikiem zapytania drugiego.

Natomiast operator INTERSECT wyświetla jedynie te wiersze które występują w

wynikach zarówno pierwszego jak i drugiego zapytania. Składnia obu operatorów

jest identyczna jak w przypadku operatora UNION. Niestety operator MINUS jak i

INTERSECT nie są obsługiwane przez aktualne wersje MS Access.

2.6. Funkcje agregujące w języku SQL

Do tej pory zajmowaliśmy się zapytaniami, które zwracały zbiór wierszy pochodzący z określonej tabeli. Każdy wiersz odpowiadał pojedynczemu rekordowi z tabeli. Poszczególne pola w wierszu mogły przybierać wartości albo bezpośrednio pobrane z kolumn, albo będące wynikiem pewnych obliczeń na kolumnach danego rekordu w tabeli źródłowej. Tego typu zapytania nie pozwalały jednak na obliczanie różnego rodzaju statystyk i podsumowań dotyczących danych zgromadzonych w tabeli, gdyż przetwarzały każdy rekord z osobna, gubiąc nijako informację o zbiorze rekordów jako populacji charakteryzowanej np.

przez pewne wartości średnie lub sumy.

Ten sposób wykonywania podstawowej wersji instrukcji SELECT został przed- stawiony na Rysunek 1. Na początku z tabeli źródłowej wybierane są tylko reko- rdy spełniające warunki określone po klauzuli WHERE (na rysunku pomijane pola są zakreskowywane). Wyniki tej operacji przechowywane są w tymczasowej tabeli w pamięci komputera. Następnie w dalszym ciągu pracując z tabelą tym- czasową wybierane są tylko te kolumny, które pojawiają się w wyrażeniach na liś- cie po instrukcji SELECT. Dalej wyrażenia te są obliczane (ewaluowane) dla każdego rekordu z osobna i zapisywane w tabeli docelowej. Na koniec tabela ta może zostać posortowana według kryteriów określonych po klauzuli ORDER BY, a także o ile użyto takowych opcji, powtarzające się wiersze mogą zostać usunięte (opcja DISTINCT), a ilość wyświetlanych rekordów ograniczona (opcja TOP).

Rysunek 1: Schemat przetwarzania podstawowej instrukcji SELECT A) Tabela źródłowa B) Tabela tymczasowa (w pamięci komputera)

Powstała po odfiltrowaniu rekordów przy pomocy klauzuli WHERE

C) Tabela tymczasowa (w pamięci komputera)

Powstała po pominięciu kolumn nie wymienionych na liście wyrażeń po instrukcji SELECT

D) Tabela docelowa

(będąca wynikiem zapytania)

Powstała po obliczeniu wartości wyrażeń na liście po instrukcji SELECT, oraz opcjonalnie po:

sortowaniu przy pomocy klauzuli ORDER BY

wykluczeniu powtarzających się wierszy (DISTINCT)

ograniczeniu ilości rekordów przeznaczonych do wyświetlenia (TOP)

Jeżeli na liście po instrukcji SELECT każde z wyrażeń zastąpimy którąś z funkcji agregujących z argumentem będącym tym właśnie wyrażeniem to pokazany na Rysunek 1 schemat ulegnie zmianie i przybierze postać jak na Rysunek 2. W tym przypadku tabela otrzymana w punkcie D) czyli po obliczeniu wartości wyrażeń (tym razem występujących nie samodzielnie, ale jako argumenty funkcji agregujących) dla każdego z wierszy z osobna, jest wciąż tabelą tymczasową. Z reguły nie jest ona ani sortowana ani też żadne powtarzające się rekordy nie są pomijane, czy ich ilość ograniczana. W następnym kroku dla każdej z kolumn tej tabeli wołana jest odpowiadająca jej funkcja agregująca obliczająca pewną wartość dla wszystkich pól w tej kolumnie (średnią, sumę, odchylenie standartowe, minimum, maksimum, itp.). Docelowa tabela posiada zatem jedynie jeden wiersz w którym w każdej z kolumn przechowywana jest wartość obliczona na podstawie działania odpowiedniej funkcji agregującej (na rysunku wartości zagregowane zaznaczane są na szaro).

Jest to mechanizm identyczny jak w przypadku pracy z arkuszem kalkulacyjnym typu MS Excel, gdzie dla wybranej grupy komórek (zgromadzonych najczęściej w kolumnie) stosujemy pewną funkcję agregującą, a wynik jej działania zapisujemy w innej komórce (najczęściej pod spodem).

Rysunek 2: Schemat przetwarzania instrukcji SELECT z agregacją

Oczywiście nie każdą funkcję agregującą można zastosować do każdego typu wartości. Jest oczywiste, że obliczanie średniej lub sumy na polach tekstowych

A) Tabela źródłowa B) Tabela tymczasowa (w pamięci komputera)

Powstała po odfiltrowaniu rekordów przy pomocy klauzuli WHERE

C) Tabela tymczasowa (w pamięci komputera)

Powstała po pominięciu kolumn nie wymienionych na liście wyrażeń po instrukcji SELECT

D) Tabela tymczasowa (w pamięci komputera)

Powstała po obliczeniu wartości wyrażeń stanowiących argumenty funkcji agregujących znajdujących się na liście po instrukcji SELECT

E) Tabela docelowa

(jednowierszowa, będąca wynikiem zapytania)

Powstała po wykonaniu odpowiednich agregacji (np.

sumowaniu lub uśrednianiu w ramach danej kolumny) wartości we wszystkich wierszach otrzymanych w punkcie D)

jest raczej operacją niezdefiniowaną. Typ obsługiwanych wyrażeń zależy ściśle od rodzaju funkcji agregujących, które kolejno zostały omówione poniżej.

2.6.1. Funkcje SUM, AVG, STDEV, STDEVP, VAR i VARP

Funkcja SUM(Wyrażenie) dodaje wszystkie wartości (występujące w osobnych wierszach) wyrażenia przekazanego jako argument. Funkcja AVG(Wyrażenie) dla takiego samego argumentu oblicza wartość średnią, funkcje STDEV(Wyrażenie) i VAR(Wyrażenie) obliczają odpowiednio odchylenie standardowe i wariancję próbki, a STDEVP(Wyrażenie) oraz VARP(Wyrażenie) odchylenie standardowe i wariancję populacji.

Wszystkie funkcje SUM, AVG, STDEV, STDEVP, VAR i VARP obsługują jedynie numeryczne typy wartości. W obliczeniach nie uwzględniają one również wartości NULL jeżeli takowe pojawiają się w przetwarzanych wierszach. A jeżeli we wszystkich wierszach występują tylko wartości NULL, wówczas funkcja również zwróci wartość NULL.

Aby pominąć w obliczeniach wartości powtarzające się, ponownie należy użyć opcji DISTINCT tym razem jako pierwszej części argumentu funkcji agregującej np. SUM(DISTINCT Moc). W MS Access opcja ta dla funkcji agregujących nie jest jednak obsługiwana i dla osiągnięcia podobnego rezultatu należy posłużyć się odpowiednim podzapytaniem (omówionym w jednym z dalszych rozdziałów).

Funkcje agregujące można łączyć w bardziej skomplikowane wyrażenia.

Mogą one posłużyć jako argumenty działań podstawowych takich jak dodawanie, odejmowanie, mnożenie i dzielenie, a także jako argumenty innych nie wykonujących agregacji funkcji SQL (zgromadzonych w Tabela 2). Argumentami funkcji agregujących mogą być dowolne wyrażenia i funkcje pod warunkiem, że typ zwracanego przez nie wyniku będzie obsługiwany przez daną funkcję agregującą. Argumentem funkcji agregującej nie może być inna funkcja agregująca. Oznacza to że próba wykonania wyrażenia postaci SUM(AVG(Moc)) zakończy się niepowodzeniem i komunikatem o błędzie.

Przykład użycia funkcji SUM, AVG, STDEVP i VARP oraz budowy złożonych wyrażeń przy ich użyciu przedstawia Listing 24.

SELECT SUM(Moc) AS [Moc całkowita], AVG(Moc) AS [Moc średnia],

STDEVP(Moc) AS [Odch Std populacji], VARP(Moc) AS [Wariancja populacji],

SUM(1000*Sin(Moc) + Poj_Silnika) / LOG(AVG(Rocznik)) AS [Wyrażenie Test]

FROM Auta;

Listing 24: Użycie funkcji SUM, AVG, STDDEVP i VARP

2.6.2. Funkcje MIN i MAX

Funkcje MIN(Wyrażenie) i MAX(Wyrażenie) zwracają odpowiednio element minimalny i maksymalny ze zbioru wartości przekazanych jako argument. W przeciwieństwie do funkcji takich jak SUM, AVG itp., funkcje MIN i MAX operują na większości typów danych tzn. mogą poszukiwać wartości ekstremalnych nie tylko wśród liczb, ale także np. wśród dat lub znaków.

Podobnie jak było dla poprzedniej grupy funkcji, również MIN i MAX zwrócą NULL jeżeli wartości we wszystkich przetwarzanych wierszach również były typu NULL.

Listing 25 pokazuje przykład użycia funkcji MIN i MAX:

SELECT MIN(Moc) AS [Moc Min], MAX(Moc) AS [Moc Max],

MIN(Poj_Silnika) AS [Pojemność Min], MAX(Poj_Silnika) AS [Pojemność Max],

MAX(Poj_Silnika*SIN(Moc)) AS [Poj*Sin(Moc) Max]

FROM Auta;

Listing 25: Użycie funkcji MIN i MAX

2.6.3. Funkcja COUNT

Ciekawym przykładem funkcji agregującej jest funkcja COUNT(Wyrażenie) zwracająca ilość wierszy będących wynikiem zapytania na podstawie zliczania nie NULL’owych wartości wybranej kolumny bądź wyrażenia. Jeżeli chcemy policzyć wszystkie wiersze danego zapytania wówczas najlepiej posłużyć się konstrukcją COUNT(*), która automatycznie wybiera kolumnę na podstawie której najlepiej wykonać obliczenia ilości wierszy, w dodatku jeżeli w niektórych polach danego wiersza pojawią się jakieś wartości NULL to wiersz ten i tak zostanie zliczony. W sytuacji gdy chcemy zliczyć ilość unikatowych wierszy (wykluczając te które się powtarzają) należy użyć: COUNT(DISTINCT Wyrażenie) jednakże jak było to również w przypadku funkcji z podpunktu 2.6.1, MS Access nie obsługuje tej składni i trzeba zastosować specjalne podzapytanie.

Listing 26 używa funkcji COUNT wraz z funkcjami AVG i SUM. W pierwszym

wyrażeniu obliczana jest całkowita ilość aut w komisie. Druga kolumna to ilość

aut z opisem wersji (jedno auto nie posiada opisu wersji – czyli ma w tym polu

wpisaną wartość NULL i zostało pominięte w obliczeniach). Trzecie wyrażenie

oblicza średnią moc przy pomocy funkcji AVG, a czwarte przy pomocy ilorazu

całkowitej mocy przez ilość aut. Wartość średniej mocy obliczona w piątej

kolumnie jest błędna, gdyż dzielenie odbywa się przez ilość aut posiadających

opis wersji, a liczba takich aut jest mniejsza od ilości aut posiadających opis

mocy.

SELECT COUNT(*) AS [Ilość aut],

COUNT(Wersja) AS [Ilość z wersją], AVG(Moc) AS [AVG(Moc)],

SUM(Moc) / COUNT(*) AS [SUM(Moc)/COUNT(*)],

SUM(Moc) / COUNT(Wersja) AS [SUM(Moc)/COUNT(Wersja)]

FROM Auta;

Listing 26: Użycie funkcji COUNT

2.6.4. Funkcje agregujące a klauzula WHERE

Do tej pory wykonywaliśmy agregacje dla wszystkich rekordów z tabeli.

Jednakże – zgodnie ze schematem przedstawionym na Rysunek 2 ilość rekordów na których wykonywane są obliczenia może być ograniczona za pomocą klauzuli WHERE.

Listing 27 modyfikuje treść Listing 26 dodając do niego klauzule WHERE, dzięki której pomijane są auta nie posiadające opisu wersji. Teraz zarówno ilości aut jak i wartości średnich mocy są zgodne.

SELECT COUNT(*) AS [Ilość aut],

COUNT(Wersja) AS [Ilość z wersją], AVG(Moc) AS [AVG(Moc)],

SUM(Moc) / COUNT(*) AS [SUM(Moc)/COUNT(*)],

SUM(Moc) / COUNT(Wersja) AS [SUM(Moc)/COUNT(Wersja)]

FROM Auta

WHERE Wersja IS NOT NULL;

Listing 27: Użycie klauzuli WHERE w zapytaniach z agregacją

2.7. Grupowanie agregacji przy pomocy klauzuli GROUP BY

Niekiedy zależy nam na wykonaniu pewnych agregacji dla określonego

podzbioru rekordów w tabeli. Z pomocą przychodzi wówczas klauzula WHERE,

która zgodnie z tym co zostało wspomniane w poprzednim podpunkcie wybierze

odpowiedni podzbiór danych – np. auta określonej marki. Co jednak zrobić w

sytuacji gdybyśmy chcieli otrzymać ten sam raport dla każdej marki z tabeli

Auta? Używając jedynie klauzuli WHERE należałoby to samo zapytanie

agregujące wykonać dla każdej z marek z osobna. Nie byłoby to zbyt wygodne

rozwiązanie, zważając dodatkowo na fakt, że listę dostępnych marek też

należałoby wcześniej pobrać w osobnym zapytaniu.

Dzięki klauzuli GROUP BY opisany problem ulega natychmiastowemu rozwiązaniu. Pozwala ona bowiem na wykonanie agregacji dla każdego podzbioru rekordów z osobna i wyświetlenie wyników w formie jednego raportu.

W tym przypadku nie będziemy mieli już do czynienia z pojedynczym wierszem wynikowym a z wieloma wierszami, z których każdy będzie odpowiadał konkretnemu podzbiorowi danych. Składnia tej klauzuli jest następująca:

GROUP BY kolumna1, kolumna2, kolumna3

Kolumna (bądź też wyrażenie) stanowi tutaj kryterium grupowania (podobnie jak w klauzuli ORDER BY kryterium sortowania). W tym przypadku oznacza to że wszystkie rekordy o tych samych wartościach w kolumna1, kolumna2 i kolumna3 zostaną zaliczone do tego samego podzbioru.

Listing 28 używa klauzuli GROUP BY do obliczenia ilości, mocy średniej a także mocy całkowitej dla aut danej marki:

SELECT COUNT(*) AS [Ilość aut], AVG(Moc) AS [Moc Średnia], SUM(Moc) AS [Moc Całkowita]

FROM Auta

GROUP BY Marka;

Listing 28: Proste użycie klauzuli GROUP BY do podziału na kategorie

Jak widać w otrzymanym raporcie brakuje jeszcze informacji, której marki dotyczy dany wiersz. Aby usunąć ten problem należy kolumny (bądź wyrażenia) po których grupujemy umieścić również na liście wyrażeń po instrukcji SELECT – tak jak pokazano na Listing 29.

SELECT Marka, COUNT(*) AS [Ilość aut],

AVG(Moc) AS [Moc Średnia],SUM(Moc) AS [Moc Całkowita]

FROM Auta

GROUP BY Marka;

Listing 29: Użycie klauzuli GROUP BY do podziału na kategorie

Tym samym dochodzimy do bardzo ważnej kwestii. Do tej pory rozważając zapytania z agregacją bez podziału na grupy (kategorie) konstruowane były jedynie takie zapytania, w których w liście po instrukcji SELECT każde wyrażenie zawierało w sobie funkcję agregującą. W przypadku gdybyśmy chcieli umieścić tam jeszcze inne pole np. Marka aparat bazy danych zwróciłby błąd. W pojedynczym wierszu będącym wynikiem zapytania znajdują się bowiem wartości zagregowane dla wszystkich marek, więc nie można umieścić w nim którejś konkretnej z nich (wybranej np. losowo) gdyż doprowadziłoby to do niespójności logicznej takiego wyniku. Sytuacja zmienia się diametralnie gdy agregacje przeprowadzamy w podgrupach dotyczących danej marki. Wówczas każdy z wielu wierszy wyniku przynależy do jednej, konkretnej marki. Nie ma więc problemu by wyświetlić jej nazwę w którymś z pól odpowiedniego wiersza należącego do wyniku zapytania. Co więcej niektóre aparaty baz danych zapytania z Listing 28 nie obsługują w ogóle gdyż pole Marka nie pojawia się w nim na liście po instrukcji SELECT. Dlatego bardzo istotne jest aby każde wyrażenie występujące liście kolumn po instrukcji SELECT , a nie zawierające funkcji agregującej występowało również na liście kryteriów grupowania po klauzuli GROUP BY i odwrotnie – każde wyrażenie będące kryterium grupowania występowało na liście wyrażeń po instrukcji SELECT .

2.7.1. Jednoczesne użycie GROUP BY i ORDER BY

Rezultaty zapytań z agregacją i grupowaniem można również posortować przy użyciu klauzuli ORDER BY. W przeciwieństwie do zapytań nie wykonujących agregacji sortować możemy jedynie względem wyrażeń będących kryteriami grupowania lub względem wyrażeń zawierających funkcje agregujące (niekoniecznie użytych na liście po instrukcji SELECT). Niektóre aparaty baz danych są jeszcze bardziej restrykcyjne i pozwalają na sortowanie jedynie według kryteriów grupowania.

SELECT Typ_Silnika, Marka, COUNT(*) AS [Ilość aut], AVG(Moc) AS [Moc Średnia]

FROM Auta

GROUP BY Typ_Silnika, Marka

ORDER BY Typ_Silnika, AVG(Moc) DESC;

Listing 30: Jednoczesne użycie GROUP BY i ORDER BY

Listing 30 oblicza ilość aut oraz ich moc średnią dla danego typu silnika i danej marki. Wyniki sortuje następnie według typu silnika oraz średniej mocy (malejąco).

2.7.2. Filtrowanie grup przy użyciu klauzuli HAVING

W sytuacji gdyby w raporcie końcowym chcieć pominąć grupy nie spełniające określonych kryteriów (np. o średniej mocy większej niż 200 KM) nie można by było posłużyć się klauzulą WHERE. Zgodnie ze schematami przedstawionymi na Rysunek 1 i Rysunek 2 kryteria w niej zawarte pozwalają pominąć wybrane, pojedyncze wiersze jeszcze przed wykonaniem agregacji. Oznacza to na przykład, że za jej pomocą można nie uwzględnić w obliczeniach aut, których moc jest większa niż 200 KM, ale ten warunek nie zapewni poprawnego wykluczenia grup aut o mocy średniej większej niż 200 KM. Przypuśćmy bowiem, że mamy następującą grupę aut o mocach 150 KM, 250 KM i 350 KM.

Średnia moc takiej grupy wynosi 250 KM, a zatem nie spełnia żądanego kryterium akceptacji grupy w końcowym raporcie. Jeżeli jednak za pomocą klauzuli WHERE wykluczymy auta których moc jest większa od 200 KM, wówczas w momencie wykonywania obliczeń grupa ta będzie zawierała jedynie jedno auto o mocy 150 KM, a zatem również moc średnia w tej grupie będzie wynosiła 150 KM. Jest to jednak wynik nieprawidłowy bo otrzymany dla zupełnie innej niż zamierzonej na początku grupy aut.

Aby ominąć ten problem i nałożyć warunki nie na pojedyncze rekordy ale na grupy rekordów podlegające agregacji, należy posłużyć się klauzulą HAVING.

Umieszczana jest ona zaraz pod klauzulą GROUP BY i pozwala na określenie kryteriów jakie spełniać mają wartości zagregowane dla danej grupy. Wyrażenia na wartości zagregowane nie muszą być identyczne z tymi, które są użyte na liście wyrażeń po instrukcji SELECT. Jedynym wymogiem jest to aby zawierały one funkcje agregujące. W klauzuli HAVING można zatem nałożyć warunek:

SUM(Moc) > 200, mimo iż w raporcie obliczamy jedynie moc średnią posługując się funkcją AVG.

Listing 31 modyfikuje Listing 30 wykluczając w obliczeniach auta o mocy mniejszej niż 100 KM (klauzula WHERE) a w wynikach końcowych pomijając grupy aut o mocy średniej większej od 350 KM (klauzula HAVING).

SELECT Typ_Silnika, Marka, COUNT(*) AS [Ilość aut], AVG(Moc) AS [Moc Średnia]

FROM Auta

WHERE Moc > 100

GROUP BY Typ_Silnika, Marka HAVING AVG(Moc) < 350

ORDER BY Typ_Silnika, AVG(Moc) DESC;

Listing 31: Jednoczesne użycie WHERE i HAVING

Pełny schemat przetwarzania instrukcji SELECT z agregacją, grupowaniem, filtrowaniem rekordów przy użyciu klauzuli WHERE i grup przy użyciu klauzuli HAVING, a także sortowaniem przedstawia Rysunek 3.

Rysunek 3: Schemat przetwarzania instrukcji SELECT z agregacją i grupowaniem

2.8. Wyrażenie warunkowe IIF

Aparat bazy danych MS Access dostarcza użytecznej funkcji IIF (w systemie MySQL IF), która pozwala na wybór odpowiedniego elementu (wyrażenia) w zależności od wartości warunku logicznego przekazanego jako jeden z argumentów. Składnia funkcji jest następująca:

IIF(warunek, wyrażenie1, wyrażenie2)

A) Tabela źródłowa B) Tabela tymczasowa (w pamięci komputera)

Powstała po odfiltrowaniu rekordów przy pomocy klauzuli WHERE

C) Tabela tymczasowa (w pamięci komputera)

Powstała po pominięciu kolumn nie wymienionych na liście wyrażeń po instrukcji SELECT

D) Tabela tymczasowa (w pamięci komputera)

Powstała po obliczeniu wartości wyrażeń stanowiących argumenty funkcji agregujących znajdujących się na liście po instrukcji SELECT

E) Tabela tymczasowa (w pamięci komputera)

Powstała po wykonaniu odpowiednich agregacji wartości we wszystkich wierszach otrzymanych w punkcie D), z uwzględnieniem podziału na grupy podanego w klauzuli GROUP BY, a następnie po wykluczeniu grup nie spełniających kryteriów podanych po klauzuli HAVING (wartości zagregowane przedstawione są na szaro).

F) Tabela docelowa

Powstała po

wykonaniu opcjonalnego:

sortowania przy pomocy klauzuli ORDER BY ograniczenia ilości rekordów przeznaczonych do wyświetlenia (TOP)

Jeżeli warunek (wyrażenie warunkowe) jest spełniony wówczas funkcja przyjmuje wartość: wyrażenie1, w przeciwnym wypadku wyrażenie2.

Funkcja IIF okazuje się bardzo pomocna w eleganckim przygotowywaniu zapytań - raportów. Listing 32 wykorzystuje ją do zamiany zwykłego pola typu prawda / fałsz na wyrażenie wypisujące fakt posiadania turbosprężarki przez auto.

SELECT Marka, Model, Wersja,

IIF(Turbo = TRUE, 'Posiada', 'Nie Posiada') AS [Turbosprężarka]

FROM Auta;

Listing 32: Użycie funkcji IIF w prostym zapytaniu - raporcie

Innym zastosowaniem IIF może być w pewnych sytuacjach zamiana danych wyświetlanych zazwyczaj w wierszach na dane wyświetlane w kolumnach. Listing 33 oblicza moc całkowitą aut z danym typem silnika za pomocą klasycznego wyrażenia z agregacją i grupowaniem. Wynik dla każdego z typów silnika zawarty jest w osobnym wierszu.

SELECT Typ_Silnika, SUM(Moc) AS [Moc Całkowita]

FROM Auta

GROUP BY Typ_Silnika;

Listing 33: Obliczenie mocy całkowitej dla danego typu silnika w wierszach

Przy pomocy funkcji IIF można zmodyfikować to zapytanie tak aby jego

wynikiem był pojedynczy wiersz z wartościami obliczonymi dla poszczególnych

silników w kolumnach. W tym celu zamiast sumować moc, dla danego silnika

będzie się sumować wyrażenie IIF, w którym jeżeli silnik jest właściwego typu to

zwraca ono moc tego silnika, a jeżeli jest innego typu to zwraca NULL. Funkcja

SUM ignoruje wartości NULL a zatem w kolumnie z wyrażeniem dotyczącym

konkretnego typu silnika otrzyma się całkowitą moc przypadającą właśnie na ten typ. Rozwiązanie to zostało przedstawione na Listing 34.

SELECT SUM(IIF(Typ_Silnika='Benzynowy', Moc, NULL)) AS [Moc Benzynowych],

SUM(IIF(Typ_Silnika='Diesel', Moc, NULL)) AS [Moc Dieslowskich]

FROM Auta;

Listing 34: Obliczenie mocy całkowitej dla danego typu silnika w kolumnach

Kolejnym istotnym zastosowaniem IIF jest dowolne formatowanie daty i czasu. W MS Access pola typu data/czas wyświetlane są zgodnie z formatem wybranym na etapie projektowania tabeli, zależnym niekiedy od ustawień regionalnych pakietu Office. Podobna sytuacja może zachodzić i innych systemach bazodanowych. Aby mieć pewność, że nasze wyrażenie na datę będzie miało ściśle określony format, lub interesuje nas tylko część daty (np. tylko rok i miesiąc) to do zbudowania własnego wyrażenia możemy posłużyć się poznanymi w punkcie 2.1.2 funkcjami daty i czasu. Jednakże funkcje te zwracają części daty lub czasu w postaci liczbowej, a zatem np. dla daty 2005-03-05, funkcja MONTH zwraca 3 a nie 03 tak jak byśmy tego w większości przypadków potrzebowali do konstrukcji wyrażeń na datę w innym formacie. Do budowy takiego wyrażenia można się jednak posłużyć instrukcją IIF i dla miesięcy, dni, godzin, minut lub sekund mniejszych od 10 doklejać znak '0' przed wynikiem funkcji wycinającej odpowiedni fragment z daty lub czasu, a dla pozostałych wartości wstawiać znak pusty ''. Listing 35 modyfikuje Listing 6 łącząc funkcje YEAR, MONTH i DAY w jedno wyrażenie daty w formacie YYYY-MM-DD.

SELECT Data_Wstawienia, YEAR(Data_Wstawienia) & '-' &

IIF(MONTH(Data_Wstawienia) < 10, '0', '') &

MONTH(Data_Wstawienia) & '-' &

IIF(DAY(Data_Wstawienia) < 10, '0', '') &

DAY(Data_Wstawienia) AS [Data formatowana]

FROM Oferty;

Listing 35: Użycie funkcji IIF do formatowania daty