MySQL SQL tutorial, rekenfuncties

SQL tutorials:

-----------
Tutorial SQL, rekenfuncties

Inhoud
Intro
Functies
Rekenfuncties

-ABS
-ACOS
-ASIN
-ATAN
-CEILING
-COS
-COT
-CRC32
-DEGREES
-EXP
-FLOOR
-LOG
-LOG2
-LOG10

-MOD
-PI
-POWER
-POW
-RADIANS
-RAND
-ROUND
-SIGN
-SIN
-SQRT
-TAN
-TRUNCATE

Intro

Alvorens te beginnen aan deze tutorial wordt geadviseerd om eerst de tutorial Tutorial ANSI SQL-92 te lezen.

Deze SQL tutorial gaat over rekenfuncties (numerieke functies), gespecificeerd op het gebruik van een database. De functies die hieronder worden beschreven kunnen ook werken op databases anders dan MySQL, maar niet persé. Sommige functies, zoals deze te gebruiken zijn in wijken af van de ANSI standaard. De voorkeur gaat uit naar de functies die volgens de ANSI norm te gebruiken zijn. Bij iedere functie staat of deze functie aan de ANSI SQL-92 norm voldoet middels :

De gebruikte SQL query's zijn getest op een database, versie 3.23.58, versie 4.1.11 en versie 5.0.18-nt. Eventuele opmerkingen over verschillen in deze versie staan bij de uitgevoerde SQL query's.

Als je wilt zoeken in deze tutorial dan kan dat met behulp van CTRL F.

Functies

De taal SQL kent verscheidene functies die gebruikt kunnen worden om de data in de database te manipuleren. Het aanroepen van functies in SQL lijkt op het aanroepen van functies in programmeertalen zoals C++, PHP en JavaScript. Eerst komt de functienaam, gevolgd door een haakje openen, parameters, haakje sluiten, oftewel:

functie(parameters)

Let op dat er geen spatie zit tussen de functie en het haakje openen. Dit is dus fout:

functie (par) : fout

Wat wel is toegestaan is dat functie parameters omgeven worden door spaties. Dit is dus allebei goed:

functie(par1,par2) : goed
functie( par1 , par2 ) : goed

De meeste functies kunnen gebruikt worden in zowel het SELECT gedeelte van de SQL query als in de WHERE van de query.

SELECT functie(kolomnaam)
FROM tabel ;

SELECT *
FROM tabel
WHERE functie(kolomnaam) = 'uitkomst' ;

Reken functies

MySQL kent vele reken functies (ook wel numerieke functies genoemd). Deze functies kunnen rekenen met velden van het type INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT, FLOAT, DOUBLE, DECIMAL en zelf opgegeven getallen. Deze kolomtypes worden hier verder niet uitgewerkt. Als er bij het berekenen een fout optreed dan zal NULL retourneren. Als er bijvoorbeeld waarden aan een parameter (ook wel argument genoemd) worden toegekend die ongeldig zijn dan geeft de functie ook NULL terug. De functies hieronder staan in alfabetische volgorde. De meeste van deze rekenkundige functies hebben betrekking op (geavanceerde) wiskunde en goniometrie. Een uitleg over de wiskunde zul je hier niet aantreffen slechts een oppervlakkige uitleg van de functie zoals deze te gebruiken is in MySQL.

ABS(x) : retourneert de absolute waarde van getal x op.

SELECT ABS(3.4) ;
SELECT ABS(-3.4) ;

Leveren allebei op: 3.4
ACOS(x) : retourneert de arccosinus van x mits x tussen -1 en 1 ligt, of gelijk is aan -1 of 1. Anders retourneert deze functie NULL op.

SELECT ACOS(-1) ;

Heeft als uitkomst : 3.1415926535898

SELECT ACOS(0) ;

Heeft als uitkomst : 1.5707963267949

SELECT ACOS(1) ;

Heeft als uitkomst : 0

SELECT ACOS(2) ;

Heeft als uitkomst : NULL
ASIN(x) : retourneert de arcsinus van x mits x tussen -1 en 1 ligt, of gelijk is aan -1 of 1. Anders retourneert deze functie NULL op.

SELECT ASIN(-1) ;

Heeft als uitkomst : -1.5707963267949

SELECT ASIN(0) ;

Heeft als uitkomst : 0

SELECT ASIN(1) ;

Heeft als uitkomst : 1.5707963267949

SELECT ASIN(2) ;

Heeft als uitkomst : NULL
ATAN(x) : retourneert de arctangens van x.

SELECT ATAN(-1) ;

Heeft als uitkomst : -0.78539816339745

SELECT ATAN(0) ;

Heeft als uitkomst : 0

SELECT ATAN(1) ;

Heeft als uitkomst : 0.78539816339745

SELECT ATAN(2) ;

Heeft als uitkomst : 1.1071487177941
CEILING(x) : retourneert het kleinste hele getal op afgerond naar boven.

SELECT CEILING(2.6) ;

Heeft als uitkomst : 3

SELECT CEILING(-2.6) ;

Heeft als uitkomst : -2
In MySQL 4.0.6 werd de alias CEIL() geïntroduceert. Deze werkt exact hetzelfde als CEILING().
COS(x) : retourneert de cosinus van x op.

SELECT COS(-1) ;

Heeft als uitkomst : 0.54030230586814

SELECT COS(0) ;

Heeft als uitkomst : 1

SELECT COS(1) ;

Heeft als uitkomst : 0.54030230586814

SELECT COS(2) ;

Heeft als uitkomst : -0.41614683654714
Zie ook verderop bij het gebruik van PI().
COT(x) : retourneert de cotangens van x op.

SELECT COT(-1) ;

Heeft als uitkomst : -0.64209261593433
Zie ook verderop bij het gebruik van PI().
CRC32(string) : retourneert een CRC32 HASH van een string.

SELECT CRC32('MySQL') ;

Geeft als resultaat : 3259397556
Dit naar het voorbeeld op www.mysql.com . CRC berekeningen worden in principe alleen gebruikt om data te verifieren met andere data. De CRC32 berekening van MySQL wijkt af van traditionele CRC32 berekeningen. MySQL retourneert de cyclische redundancy checksum polynoom van 32-bit lengtes van de opgegeven string. Meer informatie over CRC berekeningen kun je hier vinden. CRC32() werd geïntroduceert in MySQL 4.1.0.
DEGREES(x) : retourneert de waarde van x omgezet van radialen in graden.

SELECT DEGREES(PI()) ;

Levert het aantal graden op van : 180

SELECT DEGREES(PI() * 2) ;

Levert het aantal graden op van : 360

SELECT DEGREES(- PI()) ;

Levert het aantal graden op van : -180
EXP(x) : retourneert e tot de macht x op, waarbij e de basis van het natuurlijke logaritme is.

SELECT EXP(1) ;

Levert het resultaat : 2.718281828459
FLOOR(x) : retourneert het grootste getal op dat niet groter is dan x.

SELECT FLOOR(4.8) ;

Geeft als resultaat : 4

SELECT FLOOR(-4.8) ;

Geeft als resultaat : -5
Let op! Deze functie is niet te gebruiken bij velden van het type BIGINT omdat de geretourneerde waarde van het type BIGINT is.
LOG(x) : retourneert het natuurlijke logaritme van x op.

SELECT LOG(0) ;

Geeft als resultaat : NULL

SELECT LOG(2) ;

Geeft als resultaat : 0.69314718055995

SELECT LOG(EXP(1)) ;

Geeft als resultaat : 1
Vanaf MySQL versie 4.0.3 kan LN() als alias worden gebruikt voor LOG(). Om het logaritme van x te berekenen voor een willekeurige basis z kun je de volgende formule gebruiken :

LOG(x) /LOG(z)

Zo geeft de SQL query :

SELECT LOG(100)/LOG(10) ;

Het resultaat : 2
MySQL heeft hiervoor 2 extra functies ontwikkeld, LOG2(x) en LOG10(x). Deze retourneren het logaritme x tot de basis 2 respectievelijk de basis 10.

SELECT LOG2(65536) ;

Resulteert in : 16

SELECT LOG10(100) ;

Geeft als resultaat : 2
LOG2() is geïntroduceerd in MySQL 4.0.3.
MOD(a, b) : retourneert het restant van het quotiënt a / b.

SELECT MOD(53, 5) ;

Geeft als resultaat : 3
De functie MOD(a, b) werkt hetzelfde als a % b (zie reken operatoren). Bij de deling 53 / 5 (=10) blijft er een restant van 3 over. Dit is het resultaat van de functie MOD(), het restant van de quotiënt a / b.
PI() : retourneert een benadering van π (pi).

SELECT PI() ;

Geeft als resultaat : 3.141593

Als je meer decimalen wilt dan kun je dat op de volgende manier doen:

SELECT PI()+0.0000000000000000000000000 ;

Deze query, waarbij er 25 decimalen worden gevraagd, geeft als resultaat : 3.1415926535897931159979635
In theorie zou je de functie ROUND() kunnen gebruiken, maar in de praktijk gaat dit niet op. De query:

SELECT ROUND(PI(),20) ;

Geeft als resultaat : 3.14159265358979356009
Dit is een afwijkend resultaat vanaf de 15e decimaal. Hierdoor is het gebruik van ROUND() dus een slecht gebruik van middelen voor precieze berekeningen met PI(). De functie PI() is daarintegen wel uitstekend te combineren met de functies COS() , COT() en DEGREES() voor het uitrekenen van bijvoorbeeld graden bij cirkel berekeningen, parabolen etc. Zo geeft de SQL query :

SELECT COS(PI()) ;

Als resultaat : -1
Verderop zullen een aantal praktijkvoorbeelden volgen waarbij deze functies specifieker aan bod komen.
POWER(x,y) : retourneert het resultaat van x tot de macht y op.

SELECT POWER(2,4) ;

Geeft als resultaat : 16 In wiskunde kan dit eenvoudig geschreven worden als 2⁴ met als uitkomst 16.
Een alias van deze functie is het verkorte POW(x,y).

SELECT POW(2,-1.5) ;

Geeft als resultaat : 0.35355339059327
Je ziet dat je met alle gemak decimalen en negatieve getallen kunt gebruiken in deze functie (zoals verwacht kan worden). In de wiskunde zou dit :

(2)^-1.5 = 0.35355339059327 zijn.
RADIANS(x) : retourneert de waarde van x, omgezet van graden naar radialen.
Deze functie is dus het tegenovergestelde van de functie DEGREES() . Zo geeft de SQL query :

SELECT RADIANS(360) ;

Als resultaat : 6.2831853071796
Dit resultaat staat weer gelijk aan 2 x pi. Wat dus klopt.
RAND() : levert een willekeurige waarde op tussen 0.0 en 1.0. Deze waarde is een zogenaamde floating-point waarde. Ook kun je een numerieke parameter meegeven. RAND(n) gebruikt de parameter n als het willekeurige getal wat waarden gelijk aan n oplevert. Hierdoor is deze functie, met vaste parameter, te gebruiken bij een reeks van herhalende getallen. In onderstaande voorbeelden zal duidelijk worden wat hiermee bedoelt wordt.

Bij 3 x de SQL query :

SELECT RAND() ;

Retourneert MySQL in ons geval: 0.89836249304783 , 0.4410376692573 en 0.63629063557488
Bij 3x de query

SELECT RAND(5) ;

Retourneert MySQL 3 x hetzelfde resultaat : 0.40613597483014
Dat wordt dus bedoeld met een reeks van herhalende getallen.
ROUND(x,d) : retourneert x met d decimalen. Parameter d kan weggelaten worden. In dat geval retoureert de functie x afgerond tot een geheel getal.

SELECT ROUND(-45.9836782384, 3) ;

Geeft als resultaat : -45.984
SIGN(x) : retourneert 1, 0 of -1 op, afhankelijk van de positieve, 0 of respectievelijk negatieve waarde van x. Zo geeft de query:

SELECT SIGN(12) ;

Als resultaat : 1
Het getal 12 is immers positief. De SQL query :

SELECT SIGN(0) ;

Geeft als resultaat : 0
En de SQL query :

SELECT SIGN(-4) ;

Geeft als resultaat : -1

SELECT SIGN(NULL) ;

Geeft als resultaat NULL omdat NULL geen geldig argument voor deze functie is.
SIN(x) : retourneert de sinus van x in radialen.

SELECT SIN(0) ;

Resulteert in : 0

SELECT SIN(PI()/2) ;

Resulteert in : 1
SQRT(x) : retourneert de vierkantswortel (de gewone wortel) van x op.

SELECT SQRT(16) ;

Geeft als resultaat : 4

De wiskundigen onder ons weten dat √16 hetzelfde is als ²√16 en hetzelfde is als 16^½. Dat betekent dat bovenstaande SQL query gelijk is aan de SQL query :

SELECT POW(16,(1/2)) ;

Deze SQL query geeft dan ook hetzelfde resultaat. Om nu bijvoorbeeld vierdemachtswortels uit te voeren met SQL kun je de volgende wiskundige notatie ⁴√625 dus herschrijven als 625^¼. In SQL wordt dan :

SELECT POW(625,(1/4)) ;

Met als resultaat : 5
Want 5×5×5×5 = 5⁴ = 625
TAN(x) : retourneert de tangens van x, waarbij x gemeten is in radialen.

SELECT TAN(PI()/4) ;

Geeft als resultaat : 1
TRUNCATE(x,b) : retourneert x, afgekapt tot b decimalen.
Het grote verschil met ROUND() is dat TRUNCATE() afkapt en niet afrond. Zo geeft de SQL query :

SELECT TRUNCATE(2.88888,2) ;

het resultaat : 2.88
Als je het getal 2.88888 zou afronden op 2 decimalen zou je als uitkomst 2.89 krijgen. Zo geeft de SQL query :

SELECT TRUNCATE(1.999,0) ;

het resultaat : 1
b kan ook negatief zijn in de functie. Bij een negatieve b worden het aantal getallen van de absolute b omgezet naar 0. Waardoor je bij onderstaande SQL query :

SELECT TRUNCATE(233,-2) ;

het resultaat krijgt : 200
De -2 als 2e parameter maakt van de eerste 2 getallen (gezien vanaf rechts) 0. Daardoor blijft de 2 uit de 233 over samen met 2 nullen en krijg je als resultaat 200.

-----------

Ik hoop dat jullie er wat van geleerd hebben. Mocht je fouten tegenkomen (nobody's perfect) laat me dit dan s.v.p. weten op donny at semeleer dot nl . Uiteraard zijn op- en aanmerkingen ook van harte welkom!
De string-, aggegrate, datum- en tijdfuncties van worden in een aparte tutorial besproken.

-----------

-----------

Overige SQL tutorials:

-----------
Interessante links:

MySQL info
MySQL (nl)
Database nieuws:
http://www.databasejournal.com
Ansi sql tester:
http://developer.mimer.se/validator/parser92/index.tml
Ansi sql-92 documentatie:
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
Startpagina dochters:
sql | database | php | cursus

-----------

Naslagwerken gebruikt voor deze tutorial:

SQL voor MySQL ontwikkelaars door Rick f. van der Lans, ISBN 978 90 12 12150 7

Websites gebruikt voor deze tutorial:

http://www.mysql.com
http://developer.mimer.se/validator/parser92/index.tml
http://www.w3schools.com/sql/sql_intro.asp
http://www.phpfreakz.nl

-----------

Deze tutorial is gemaakt door Donny Semeleer (Donny_nl). Niets van deze tutorial mag gebruikt worden zonder schriftelijke toestemming van de maker. De gebruikte gegevens zijn fictieve gegevens. Copyright © 2005-2010 D. Semeleer. Laatste geupdate op 17 september 2006.