4. Wyrażenia w języku SQL

4.1. Introduction

W większości poleceń SQL można użyć wyrażenia. Weźmy na przykład polecenie SELECT:

syntaxe

SELECT expr1, expr2, ... z table

WHERE expression

SELECT wybiera wiersze, dla których warunek expression jest spełniony, i wyświetla dla każdego z nich wartości z expri.

Przykłady

SQL> select prix*1.186 from biblio
SQL> select titre from biblio where prix between 100 and 150

W niniejszym akapicie zamierzamy wyjaśnić pojęcie wyrażenia. Wyrażenie elementarne ma postać:

operand1 opérateur operand2

lub

funkcja(parametry)

Przykład

W wyrażeniu GENRE = 'ROMAN'

GENRE jest operandem 1
„ROMAN” jest operandem 2
= jest operatorem

W wyrażeniu upper(genre)

„upper” jest funkcją
typ jest parametrem tej funkcji.

Najpierw omówimy wyrażenia z operatorami, a następnie przedstawimy funkcje dostępne w Firebirdzie.

4.2. Wyrażenia z operatorami

Wyrażenia z operatorami podzielimy według typu ich operandów:

liczbowe
ciąg znaków
data
boolowskie lub logiczne

4.2.1. Wyrażenia z operandami typu liczbowego

4.2.1.1. Lista operatorów

Niech nombre1, nombre2, nombre3 będą liczbami. Dostępne są następujące operatory:

Operatory relacyjne

`nombre1 > nombre2`	: liczba1 jest większa od liczby2
`nombre1 >= nombre2`	: liczba1 jest większa lub równa liczbie2
`nombre1 < nombre2`	: liczba1 jest mniejsza od liczby2
`nombre1 <= nombre2`	: liczba1 jest mniejsza lub równa liczbie2
`nombre1 = nombre2`	: liczba1 jest równa liczbie2
`nombre1 != nombre2`	: liczba1 różna od liczby2
`nombre1 <> nombre2`	: to samo
`nombre1 BETWEEN nombre2 AND nombre3`	: liczba1 mieści się w przedziale [nombre2,nombre3]
`nombre1 IN (liste de nombres)`	: liczba1 należy do listy liczb
`nombre1 IS NULL`	: liczba1 nie ma wartości
`nombre1 IS NOT NULL`	: liczba1 ma wartość

Operatory arytmetyczne

`nombre1 + nombre2`	: dodawanie
`nombre1 - nombre2`	: odejmowanie
`nombre1 * nombre2`	: mnożenie
`nombre1 / nombre2`	: dzielenie

4.2.1.2. Operatory relacyjne

Wyrażenie relacyjne określa relację, która jest prawdziwa lub fałszywa. Wynikiem takiego wyrażenia jest zatem wartość boolowska lub logiczna.

Przykłady:

SQL> select titre,prix from biblio where prix between 100 and 150

SQL> select titre,prix from biblio where prix not between 100 and 150

SQL> select titre,prix from biblio where prix in (200,210)

4.2.1.3. Operatory arytmetyczne

Wyrażenia arytmetyczne są nam dobrze znane. Wyrażają one obliczenia, które należy wykonać na danych liczbowych. Spotkaliśmy się już z takimi wyrażeniami: zakładamy, że cena zapisana w rekordach pliku BIBLIO jest ceną bez podatku. Chcemy wyświetlić każdy tytuł wraz z jego ceną TTC przy stawce TVA wynoszącej 18,6%:

    SELECT TITRE, PRIX*1.186 FROM BIBLIO

Jeśli ceny mają wzrosnąć o 3%, polecenie będzie brzmiało

    UPDATE BIBLIO SET PRIX = PRIX*1.03

W wyrażeniu może występować kilka operatorów arytmetycznych, a także funkcje i nawiasy. Elementy te są przetwarzane zgodnie z różnymi priorytetami:

1	`fonctions`	<---- wyższy priorytet
2	`()`
3	`* et /`
4	`+ et -`	<---- niższy priorytet

Gdy w wyrażeniu występują dwa operatory o tej samej priorytecie, w pierwszej kolejności obliczany jest ten, który znajduje się bardziej po lewej stronie wyrażenia.

Przykłady

Wyrażenie PRIX*TAUX+TAXES zostanie obliczone jako (PRIX*TAUX)+TAXES. W rzeczywistości to operator mnożenia zostanie zastosowany jako pierwszy. Wyrażenie PRIX*TAUX/100 zostanie obliczone jako (PRIX*TAUX)/100.

4.2.2. Wyrażenia z operandami typu znakowego

4.2.2.1. Lista operatorów

Dostępne są następujące operatory:

Niech chaine1, chaine2, chaine3 będą przykładami ciągów znaków

`chaine1 > chaine2`	: ciąg1 jest dłuższy niż ciąg2
`chaine1 >= chaine2`	: ciąg1 jest większy lub równy ciągowi2
`chaine1 < chaine2`	: ciąg1 jest mniejszy niż ciąg2
`chaine1 <= chaine2`	: ciąg1 jest mniejszy lub równy ciągowi2
`chaine1 = chaine2`	: ciąg1 jest równy ciągowi2
`chaine1 != chaine2`	: ciąg1 różni się od ciągu2
`chaine1 <> chaine2`	: to samo
`chaine1 BETWEEN chaine2 AND chaine3`	: ciąg1 mieści się w przedziale [chaine2,chaine3]
`chaine1 IN liste de chaines`	: ciąg1 należy do listy ciągów
`chaine1 IS NULL`	: ciąg1 nie ma wartości
`chaine1 IS NOT NULL`	: ciąg1 ma wartość
`chaine1 LIKE modèle`	: ciąg1 pasuje do wzorca

Operator konkatenacji

ciąg1 || ciąg2 : chaine2 połączony z chaine1

4.2.2.2. Operatory relacyjne

Co oznacza porównywanie ciągów znaków za pomocą operatorów takich jak <, <= itp.?

Każdy znak jest zakodowany za pomocą liczby całkowitej. Podczas porównywania dwóch znaków porównywane są ich kody całkowite. Zastosowane kodowanie zachowuje naturalną kolejność słownika:

blanc<..< 0 < 1 < ...< 9 < ...< A < B <... < Z < ... < a < b < ... < z

Cyfry występują przed literami, a wielkie litery przed małymi.

4.2.2.3. Porównanie dwóch ciągów znaków

Rozważmy relację „CHAT” < „CHIEN”. Czy jest ona prawdziwa, czy fałszywa? Aby przeprowadzić to porównanie, algorytm SGBD porównuje oba ciągi znak po znaku na podstawie ich kodów całkowitoliczbowych. Gdy tylko wykryje się różnicę między dwoma znakami, ciąg, w którym występuje mniejszy z tych znaków, uznaje się za mniejszy od drugiego ciągu. W naszym przykładzie ciąg „CHAT” jest porównywany z ciągiem „CHIEN”. Otrzymujemy następujące wyniki:

     'CHAT    ' 'CHIEN'
----------------------------
    'C'    =    'C'
    'H'    =    'H'
     'A    '     < 'I'

Po tym ostatnim porównaniu ciąg znaków „CHAT” zostaje uznany za krótszy od ciągu znaków „CHIEN”. Zatem relacja „CHAT” < „CHIEN” jest prawdziwa.

Porównajmy teraz ciągi „CHAT” i „chat”.

     'CHAT    ' 'chat'
--------------------------
     'C    '     < 'c'

Po tym porównaniu relacja „CHAT” < „chat” zostaje uznana za prawdziwą.

Przykłady

SQL> select titre from biblio

SQL> select titre from biblio where upper(titre) between 'L' and 'M'

4.2.2.4. Operator LIKE

Operator LIKE stosuje się w następujący sposób: chaîne LIKE modèle

Równoważność jest spełniona, jeśli chaîne odpowiada modèle. Jest to ciąg znaków, który może zawierać dwa znaki wieloznaczne:

`%`	oznaczający dowolny ciąg znaków
`_`	która oznacza 1 dowolny znak

Przykłady

SQL> select titre from biblio

SQL> select titre from biblio where titre like 'M%';

SQL> select titre from biblio where titre like 'L_ %';

4.2.2.5. Operator konkatenacji

SQL > select '[' || titre || ']' from biblio where upper(titre) LIKE 'L_ %'

4.2.3. Wyrażenia z operandami typu data

Niech date1, date2, date3 będą datami. Dostępne są następujące operatory:

Operatory relacyjne

`date1 < date2`	jest prawdziwe, jeśli date1 jest wcześniejsza niż date2
`date1 <= date2`	jest prawdziwe, jeśli data1 jest wcześniejsza lub równa dacie2
`date1 > date2`	jest prawdziwe, jeśli data1 jest późniejsza niż data2
`date1 >= date2`	jest prawdziwe, jeśli data1 jest późniejsza lub równa dacie2
`date1 = date2`	jest prawdziwe, jeśli data1 i data2 są identyczne
`date1 <> date2`	jest prawdziwe, jeśli data1 i data2 są różne.
`date1 != date2`	to samo
`date1 BETWEEN date2 AND date3`	jest prawdziwe, jeśli data1 mieści się między datą2 a datą3
`date1 IN (liste de dates)`	jest prawdziwe, jeśli data1 znajduje się na liście dat
`date1 IS NULL`	jest prawdziwe, jeśli data1 nie ma wartości
`date1 IS NOT NULL`	jest prawdziwe, jeśli data1 ma wartość
`date1 LIKE modèle`	jest prawdziwe, jeśli date1 odpowiada wzorcowi
`ALL,ANY,EXISTS`

Operatory arytmetyczne

`date1 - date2`	: liczba dni dzielących datę1 od daty2
`date1 - nombre`	: data2 taka, że data1 – data2 = liczba
`date1 + nombre`	: data2 taka, że data2 – data1 = liczba

Przykłady

SQL> select achat from biblio

SQL>select achat from biblio
   where achat between '01.01.1988' and '31.12.1988';

SQL> select titre, achat from biblio
where cast(achat as char(10)) like '1988'

Jak stare są książki w bibliotece?

SQL> select titre, cast('now' as date)-achat "age(jours)" from biblio

4.2.4. Wyrażenia z operandami boolowskimi

Przypomnijmy, że wartość logiczna może przyjmować dwie wartości: prawdziwe lub fałszywe. Operand logiczny jest często wynikiem wyrażenia relacyjnego.

Niech booléen1 i booléen2 będą dwiema wartościami boolowskimi. Istnieją trzy możliwe operatory, których kolejność pierwszeństwa jest następująca:

`booléen1 AND booléen2`	jest prawdziwe, jeśli zarówno booléen1, jak i booléen2 są prawdziwe.
`booléen1 OR booléen2`	jest prawdziwe, jeśli prawdziwe jest albo booléen1, albo booléen2.
`NOT booléen1`	ma wartość odwrotną do wartości booléen1.

Przykłady

SQL> select titre,genre,prix from biblio order by prix desc

Szukamy książek w przedziale cenowym:

SQL> select titre,genre,prix from biblio
     where prix>=130 and prix <=170

Wyszukiwanie odwrotne:

SQL> select titre,genre,prix from biblio
     where prix<130 or prix >170
     order by prix asc

Należy zwrócić uwagę na kolejność operatorów!

SQL> select titre,genre,prix from biblio
  where genre='ROMAN' and prix>200 or prix<100
  order by prix asc

Aby kontrolować priorytet operatorów, stosuje się nawiasy:

SQL> select titre,genre,prix from biblio
  where genre='ROMAN' and (prix>200 or prix<100)
  order by prix asc

4.3. Wbudowane funkcje Firebirda

Firebird posiada wbudowane funkcje. Nie można z nich od razu korzystać w poleceniach SQL. Najpierw należy uruchomić skrypt SQL <firebird>\UDF\ib_udf.sql, gdzie <firebird> oznacza katalog instalacyjny Firebirda:

W przypadku pliku IBExpert postępujemy w następujący sposób:

używamy narzędzia [Script Excecutive] uzyskanego za pomocą opcji [Tools/ Script Executive]:

po uruchomieniu narzędzia ładujemy skrypt <firebird>\UDF\ib_udf.sql:

następnie uruchamiamy skrypt:

Po wykonaniu tej czynności predefiniowane funkcje Firebirda są dostępne dla bazy danych, z którą użytkownik był połączony w momencie uruchomienia skryptu. Aby to sprawdzić, wystarczy przejść do eksploratora baz danych i kliknąć węzeł [UDF] w bazie, do której zaimportowano funkcje:

Powyżej widoczne są funkcje dostępne w bazie. Aby je przetestować, warto utworzyć tabelę zawierającą jeden wiersz. Nazwijmy ją TEST:

i zdefiniujmy ją w następujący sposób (kliknij prawym przyciskiem myszy na Tables / New Table):

Umieśćmy w tej tabeli jeden wiersz:

Przejdźmy teraz do edytora SQL (F12) i wygenerujmy następujące polecenie SQL:

SQL> select cos(0) from test

które wykorzystuje predefiniowaną funkcję cos (cosinus). Powyższe polecenie oblicza wartość cos(0) dla każdego wiersza tabeli TEST, a więc w rzeczywistości dla jednego wiersza. Wyświetlana jest zatem po prostu wartość cos(0):

Funkcje UDF (funkcje zdefiniowane przez użytkownika) to funkcje, które użytkownik może tworzyć, dzięki czemu w Internecie można znaleźć biblioteki funkcji UDF. Opisujemy tutaj tylko niektóre z tych dostępnych w wersji Firebirda do pobrania (2005). Klasyfikujemy je według dominującego typu parametrów lub według pełnionej funkcji:

funkcje z parametrami typu liczbowego
funkcje z parametrami typu ciąg znaków

4.3.1. Funkcje z parametrami typu liczbowego

`abs(nombre)`	wartość bezwzględna liczby abs(-15) = 15
`ceil(nombre)`	najmniejsza liczba całkowita większa lub równa nombre ceil(15,7) = 16
`floor(nombre)`	największa liczba całkowita mniejsza lub równa nombre floor(14,3) = 14
`div(nombre1,nombre2)`	iloraz z dzielenia całkowitego (iloraz jest liczbą całkowitą) nombre1 przez nombre2 div(7,3)=2
`mod(nombre1,nombre2)`	reszta z dzielenia całkowitego (iloraz jest liczbą całkowitą) liczby nombre1 przez nombre2 mod(7,3)=1
`sign(nombre)`	-1, jeśli nombre < 0 0, jeśli nombre = 0 +1, jeśli nombre > 0 sign(-6) = -1
`sqrt(nombre)`	pierwiastek kwadratowy z nombre, jeśli nombre ≥ 0 -1, jeśli nombre < 0 sqrt(16)=4

4.3.2. Funkcje z parametrami typu ciąg znaków

ascii_char(liczba)	znak o kodzie ASCII nombre ascii_char(65) = 'A'
lower(ciąg)	zamienia chaine na małe litery lower('INFO') = 'info'
ltrim(ciąg)	Left Trim – spacje poprzedzające tekst chaine są usuwane: ltrim(' kotek')='kotek'
replace(ciąg1,ciąg2,ciąg3)	zastępuje chaine2 przez chaine3 w chaine1. replace('kot i pies','kot','')='ot i **ies'
rtrim(ciąg1,ciąg2)	Right Trim – to samo co ltrim, ale po prawej stronie rtrim('kot ')='kot'
substr(ciąg, p, q)	podciąg ciągu chaine, zaczynający się w pozycji p i kończący się w pozycji q. substr('chaton', 3, 5) = 'ato'
ascii_val(znak)	kod ASCII z caractère ascii_val('A') = 65
strlen(ciąg)	liczba znaków w chaine strlen('chaton') = 6