4. De uitdrukkingen in de taal SQL

4.1. Introduction

In de meeste commando's SQL is het mogelijk om een uitdrukking te gebruiken. Laten we bijvoorbeeld het commando SELECT nemen:

syntaxe

SELECT expr1, expr2, ... uit table WHERE expression

SELECT selecteert de rijen waarvoor expression waar is en geeft voor elk daarvan de waarden van expri weer.

Voorbeelden

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

In deze paragraaf willen we het begrip ‘uitdrukking’ toelichten. Een elementaire uitdrukking is van het type:

operand1 opérateur operand2

of

functie(parameters)

Voorbeeld

In de uitdrukking is GENRE gelijk aan 'ROMAN'

• GENRE is operand 1
• 'ROMAN' is operand 2
• = is de operator

In de uitdrukking upper(genre)

• is 'upper' een functie
• 'type' is een parameter van deze functie.

We behandelen eerst uitdrukkingen met operatoren, daarna zullen we de functies presenteren die beschikbaar zijn in Firebird.

4.2. Uitdrukkingen met een operator

We zullen uitdrukkingen met operatoren indelen op basis van het type van hun operanden:

• numeriek
• tekenreeks
• datum
• booleaanse of logische

4.2.1. Uitdrukkingen met numerieke operanden

4.2.1.1. Lijst met operatoren

Stel dat nombre1, nombre2 en nombre3 getallen zijn. De volgende operatoren kunnen worden gebruikt:

Relationele operatoren

nombre1 > nombre2	: getal1 is groter dan getal2
nombre1 >= nombre2	: getal1 is groter dan of gelijk aan getal2
nombre1 < nombre2	: getal1 is kleiner dan getal2
nombre1 <= nombre2	: getal1 is kleiner dan of gelijk aan getal2
nombre1 = nombre2	: getal1 is gelijk aan getal2
nombre1 != nombre2	: getal1 is niet gelijk aan getal2
nombre1 <> nombre2	: idem
nombre1 BETWEEN nombre2 AND nombre3	: getal1 ligt binnen het interval [nombre2,nombre3]
nombre1 IN (liste de nombres)	: getal1 behoort tot de lijst met getallen
nombre1 IS NULL	: getal1 heeft geen waarde
nombre1 IS NOT NULL	: getal1 heeft een waarde

Rekenkundige operatoren

nombre1 + nombre2	: optelling
nombre1 - nombre2	: aftrekken
nombre1 * nombre2	: vermenigvuldiging
nombre1 / nombre2	: deling

4.2.1.2. Relationele operatoren

Een relationele uitdrukking drukt een relatie uit die waar of onwaar is. Het resultaat van zo'n uitdrukking is dus een booleaanse waarde of logische waarde.

Voorbeelden:

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

We zijn bekend met rekenuitdrukkingen. Deze geven een berekening weer die moet worden uitgevoerd met numerieke gegevens. We zijn dergelijke uitdrukkingen al tegengekomen: we gaan ervan uit dat de prijs die is opgeslagen in de records van het bestand BIBLIO een prijs exclusief btw is. We willen elk effect weergeven met zijn prijs TTC bij een tarief van TVA van 18,6%:

    SELECT TITRE, PRIX*1.186 FROM BIBLIO

Als de prijzen met 3% moeten stijgen, luidt de opdracht

    UPDATE BIBLIO SET PRIX = PRIX*1.03

In een uitdrukking kunnen meerdere rekenoperatoren voorkomen, samen met functies en haakjes. Deze elementen worden volgens verschillende prioriteiten verwerkt:

1	fonctions	<---- hogere prioriteit
2	()
3	* et /
4	+ et -	<---- lagere prioriteit

Wanneer er twee operatoren met dezelfde prioriteit in de uitdrukking voorkomen, wordt degene die het meest links in de uitdrukking staat als eerste geëvalueerd.

Voorbeelden

De uitdrukking PRIX*TAUX+TAXES wordt als volgt berekend: (PRIX*TAUX)+TAXES. De vermenigvuldigingsoperator wordt namelijk als eerste toegepast. De uitdrukking PRIX*TAUX/100 wordt berekend als (PRIX*TAUX)/100.

4.2.2. Uitdrukkingen met operanden van het type teken

4.2.2.1. Lijst met operatoren

De volgende operatoren kunnen worden gebruikt:

Stel dat we keten1, keten2, keten3 en het patroon van de tekenreeksen hebben

chaine1 > chaine2	: string1 is langer dan string2
chaine1 >= chaine2	: string1 is groter dan of gelijk aan string2
chaine1 < chaine2	: string1 is kleiner dan string2
chaine1 <= chaine2	: string1 is kleiner dan of gelijk aan string2
chaine1 = chaine2	: string1 is gelijk aan string2
chaine1 != chaine2	: string1 is niet gelijk aan string2
chaine1 <> chaine2	: idem
chaine1 BETWEEN chaine2 AND chaine3	: string1 ligt binnen het interval [chaine2,chaine3]
chaine1 IN liste de chaines	: reeks1 behoort tot de reeksenlijst
chaine1 IS NULL	: string1 heeft geen waarde
chaine1 IS NOT NULL	: string1 heeft een waarde
chaine1 LIKE modèle	: string1 komt overeen met sjabloon

Aaneenschakelingsoperator

string1 || string2 : chaine2 samengevoegd met chaine1

4.2.2.2. Relationele operatoren

Wat betekent het om tekenreeksen te vergelijken met operatoren zoals <, <=, enz.?

Elk teken wordt gecodeerd als een geheel getal. Bij het vergelijken van twee tekens worden hun geheelgetalcodes met elkaar vergeleken. De gebruikte codering volgt de natuurlijke volgorde van het woordenboek:

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

Cijfers komen vóór letters, en hoofdletters vóór kleine letters.

4.2.2.3. Vergelijking van twee tekenreeksen

Stel de vergelijking 'CHAT' < 'CHIEN'. Is deze waar of onwaar? Om deze vergelijking uit te voeren, vergelijkt het systeem de twee tekenreeksen teken voor teken op basis van hun gehele getalcodes. Zodra er twee verschillende tekens worden gevonden, wordt de tekenreeks met het kleinste van de twee als kleiner beschouwd dan de andere tekenreeks. In ons voorbeeld wordt 'CHAT' vergeleken met 'CHIEN'. We krijgen de volgende opeenvolgende resultaten:

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

Na deze laatste vergelijking wordt de tekenreeks 'CHAT' kleiner verklaard dan de tekenreeks 'CHIEN'. De relatie 'CHAT' < 'CHIEN' is dus waar.

Laten we nu ‘CHAT’ en ‘chat’ vergelijken.

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

Na deze vergelijking wordt de relatie 'CHAT' < 'chat' als waar aangemerkt.

Voorbeelden

SQL> select titre from biblio

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

4.2.2.4. De operator LIKE

De operator LIKE wordt als volgt gebruikt: chaîne LIKE modèle

De relatie is waar als chaîne overeenkomt met modèle. Dit is een tekenreeks die twee jokertekens kan bevatten:

%	die elke reeks tekens aanduidt
_	die staat voor 1 willekeurig teken

Voorbeelden

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

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

4.2.3. Uitdrukkingen met operanden van het type datum

Stel dat date1, date2 en date3 datums zijn. De volgende operatoren kunnen worden gebruikt:

Relationele operatoren

date1 < date2	is waar als datum1 eerder is dan datum2
date1 <= date2	is waar als datum1 eerder is dan of gelijk is aan datum2
date1 > date2	is waar als datum1 later is dan datum2
date1 >= date2	is waar als datum1 later is dan of gelijk is aan datum2
date1 = date2	is waar als datum1 en datum2 gelijk zijn
date1 <> date2	is waar als datum1 en datum2 verschillend zijn.
date1 != date2	idem
date1 BETWEEN date2 AND date3	is waar als datum1 tussen datum2 en datum3 ligt
date1 IN (liste de dates)	is waar als datum1 in de lijst met datums voorkomt
date1 IS NULL	is waar als datum1 geen waarde heeft
date1 IS NOT NULL	is waar als datum1 een waarde heeft
date1 LIKE modèle	is waar als date1 overeenkomt met het sjabloon
ALL,ANY,EXISTS

Rekenkundige operatoren

date1 - date2	: aantal dagen tussen datum1 en datum2
date1 - nombre	: datum2 zodanig dat datum1 - datum2 = getal
date1 + nombre	: datum2 zodanig dat datum2 - datum1 = getal

Voorbeelden

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'

Hoe oud zijn de boeken in de bibliotheek?

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

4.2.4. Uitdrukkingen met booleaanse operanden

Ter herinnering: een booleaanse of logische waarde kan twee waarden aannemen: waar of onwaar. De logische operand is vaak het resultaat van een relationele uitdrukking.

Stel dat booléen1 en booléen2 twee booleaanse waarden zijn. Er zijn drie mogelijke operatoren, in volgorde van prioriteit:

booléen1 AND booléen2	is waar als zowel booléen1 als booléen2 waar zijn.
booléen1 OR booléen2	is waar als booléen1 of booléen2 waar is.
NOT booléen1	heeft als waarde het omgekeerde van de waarde van booléen1.

Voorbeelden

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

We zoeken naar boeken tussen twee prijzen:

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

Omgekeerd zoeken:

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

Let op de volgorde van de operatoren!

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

We gebruiken haakjes om de prioriteit van de operatoren te bepalen:

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

4.3. De vooraf gedefinieerde functies van Firebird

Firebird beschikt over vooraf gedefinieerde functies. Deze zijn niet direct bruikbaar in de opdrachten SQL. Eerst moet het script SQL <firebird>\UDF\ib_udf.sql worden uitgevoerd, waarbij <firebird> verwijst naar de installatiemap van Firebird:

Met IBExpert gaan we als volgt te werk:

• we gebruiken het hulpprogramma [Script Excecutive] dat is verkregen via de optie [Tools/ Script Executive]:

• zodra de tool beschikbaar is, laden we het script <firebird>\UDF\ib_udf.sql:

• Vervolgens voeren we het script uit:

Zodra dit is gebeurd, zijn de vooraf gedefinieerde functies van Firebird beschikbaar voor de database waarmee men was verbonden toen het script werd uitgevoerd. Om dit te zien, hoeft men alleen maar naar de database-verkenner te gaan en op het knooppunt [UDF] te klikken van de database waarin de functies zijn geïmporteerd:

Hierboven ziet u de functies die beschikbaar zijn in de database. Om ze te testen, is het handig om een tabel met één rij te hebben. Laten we deze TEST noemen:

en definiëren we deze als volgt (rechtsklikken op Tables / New Table):

Laten we één rij in deze tabel plaatsen:

Laten we nu naar de editor SQL (F12) gaan en de volgende opdracht SQL uitvoeren:

SQL> select cos(0) from test

die gebruikmaakt van de vooraf gedefinieerde functie cos (cosinus). Het bovenstaande commando berekent cos(0) voor elke rij van de tabel TEST, dus in feite voor slechts één rij. Er wordt dus gewoon de waarde van cos(0) weergegeven:

De functies UDF (User Defined Function) zijn functies die de gebruiker zelf kan aanmaken; op het internet zijn dan ook bibliotheken met UDF-functies te vinden. We beschrijven hier slechts enkele van de functies die beschikbaar zijn in de downloadbare versie van Firebird (2005). We classificeren ze op basis van het overheersende type van hun parameters of op basis van hun rol:

• functies met numerieke parameters
• functies met parameters van het type tekenreeks

4.3.1. Functies met numerieke parameters

abs(nombre)	absolute waarde van een getal abs(-15)=15
ceil(nombre)	kleinste gehele getal groter dan of gelijk aan nombre ceil(15,7) = 16
floor(nombre)	grootste geheel getal kleiner dan of gelijk aan nombre floor(14,3)=14
div(nombre1,nombre2)	quotiënt van de gehele deling (het quotiënt is een geheel getal) van nombre1 door nombre2 div(7,3)=2
mod(nombre1,nombre2)	rest van de gehele deling (het quotiënt is een geheel getal) van nombre1 door nombre2 mod(7,3)=1
sign(nombre)	-1 als nombre < 0 0 als nombre = 0 +1 als nombre > 0 sign(-6) = -1
sqrt(nombre)	vierkantswortel van nombre als nombre ≥ 0 -1 als nombre<0 sqrt(16)=4

4.3.2. Functies met parameters van het type tekenreeks

ascii_char(getal)	teken met code ASCII nombre ascii_char(65) = 'A'
lower(string)	zet chaine in kleine letters lower('INFO')='info'
ltrim(string)	Left Trim - De spaties vóór de tekst van chaine worden verwijderd: ltrim(' kitten')='kitten'
replace(string1,string2,string3)	vervangt chaine2 door chaine3 in chaine1. replace('kat en hond','kat','**')='**at en **ien'
rtrim(string1, string2)	Right Trim - hetzelfde als ltrim, maar aan de rechterkant rtrim('kat ')='kat'
substr(string, p, q)	deelreeks van chaine die begint op positie p en eindigt op positie q. substr('chaton',3,5)='ato'
ascii_val(teken)	code ASCII van caractère ascii_val('A') = 65
strlen(string)	aantal tekens van chaine strlen('kitten') = 6