Scriptorama.nl

Dat SQL handige features heeft, konden we hier al terug lezen. Af en toe heb je queries nodig waarbij enige creativiteit nodig is of waarvan je verdere bewerkingen niet wilt doen je in scripttaal zoals Perl of PHP. Gelukkig kan SQL meeste functionaliteit voor je verschaffen en scheelt het weer script werk en performance. We zullen hier vijf handige SQL queries laten zien.

SQL dump

Voor het uitvoeren van de queries hebben we natuurlijk data nodig. Hieronder vind je een voorbeeld tabel met gegevens erin en de SQL structuur. We zullen voornamelijk gebruik maken van MySQL. Het moet echter geen probleem zijn om de queries te herschrijven zodat deze ook werken op PostgreSQL of Oracle.

Zoals je misschien al opvalt, zitten er dubbele rijen tussen. Dat is met opzet gedaan, waarom lees je later. Laten we beginnen met onze eerste query.

Een datum formaat geven

Meestal wordt een datum opgeslagen in een DATE of DATETIME type, niet in een varchar type!

Als je data (meervoud van datum) in varchar type hebt opgeslagen, probeer dat zo snel mogelijk om te zetten in DATE of DATETIME. Doordat je niet een datum datatype gebruikt beperk je jezelf enorm in de functionaliteit die SQL je kan bieden!

De datum wordt altijd opgeslagen in deze vorm: YYYY-MM-DD (DATE) en YYYY-MM-DD HH:MM:SS (DATETIME). Wanneer je deze datums wilt weergeven, dan wil je dat natuurlijk in een leesbaar formaat doen. Dus in het Nederlandse datum formaat 22-09-2006 in plaats van 2006-09-22. Veel beginnende web developers kennen SQL nog niet genoeg en weten niet hoe zij dit probleem moeten aanpakken.

Een oplossing is de gegevens in je scripttaal verwerken. PHP biedt bijvoorbeeld de nodige functies om datums netjes weer te geven. Dit is zonde, SQL kan dit namelijk voor je doen en nog veel meer.

De 'magische' functie voor MySQL is DATE_FORMAT().

DATE_FORMAT(date,format)

Formats the date value according to the format string.

Als eerste parameter verwacht het een datum in DATE of DATETIME formaat. Daarna kun je aangeven hoe je het gepresenteerd wilt hebben.

Wil je 2006-09-22 weergeven als 22-09-2006? Dan doe je dat zo:

Kolom gebdatum2 kun je nu gebruiken voor het weergeven van je datum. Je kunt nog veel meer formaten doorvoeren, in de handleiding vind je een overzicht hiervan.

Belangrijk: Gebruik altijd een alias voor je nieuwe datum kolom zodat je deze makkelijk kunt benaderen. Doe je dat niet, dan zal de kolomnaam de functie aanroep zijn en dat is natuurlijk niet handig. Verder is het afgeraden je nieuwe kolom te gebruiken in een WHERE clausule. Doordat het resultaat van DATE_FORMAT() een string is en niet meer een datum-type zal het sorteren hierop ongewenste resultaten leveren.

Het kan wel eens voorkomen dat je Nederlandse woorden wilt gebruiken voor data. Zoals 22 augustus 2006. Dit kun je ook met SQL doen, alleen wordt het wel een lange query. Vincent Kleijendorst heeft een mooie oplossing bedacht. Hij combineert PHP en MySQL om zo dynamisch tot het goede resultaat te komen. Er wordt uitveorig gebruik gemaakt van MySQL's ELT() functie, die we vanaf versie 3.20.17 kennen, en speelt bij deze snippet een belangrijke rol.

Voorbeeld van ELT() en wat deze doet:

De functie dateFormat() gebruikt dezelfde argumenten als de PHP functie date() en geeft de MySQL DATE_FORMAT() functie terug.

Hieronder staat een voorbeeld hoe je het moet gebruiken.

Handige helper, bedankt Vincent.

Dubbele rijen verwijderen

Naarmate je tabel groter wordt kan het zijn dat je dubbele gegevens krijgt. Nu wil je nog een query om alle dubbele gegevens te verwijderen. Dit is gelukkig niet zo moeilijk. De eerste stap is de dubbele data opzoeken en dat kan met een simpele SELECT samen met een subquery.

Nu hebben wel alleen gegroepeerd op naam, met andere woorden alle dubbele gegevens gebaseerd op kolom 'naam'. Wil je echter op alle kolommen groeperen, dan voeg je die simpelweg toe aan je GROUP BY clausule. We gaan er in dit geval ervan uit dat we de rij met de kleinste id (vaak de eerst ingevoegde rij) willen behouden, vandaar de MIN() in de subquery.

Nu is het verwijderen van de rijen een fluitje van een cent, vervang de SELECT met een DELETE en klaar is klara!

Condities in queries

Een veel voorkomende situatie is dat je een bepaalde tekst wilt weergeven aan de hand van een waarde. De dateFormat() functie van Vincent van zojuist was daar al een voorbeeld voor.

In onze tabel geeks hebben we de kolom geek. Is de waarde 0, dan is de persoon geen geek. Is de waarde 1, dan is diegene wel een geek. Je wilt dus in je query op een of andere manier een conditie gebruiken. Nu kan je dit oplossen in je favoriete scripttaal, maar SQL kan dit ook. Bijna elke taal kent wel een if-else constructie en in SQL kan dit ook. Je kunt je afvragen of zo'n constructie wel in SQL thuis hoort. Het kan soms handiger zijn om dit te verwerken in je scripttaal.

In SQL bestaat een CASE statement. Deze gebruik je als volgt:

Nu kan je zoveel WHEN statements gebruiken in een CASE. In dit geval is het simpel: 0 = 'Nee' en 1 = 'Ja'. Deze query kunnen we versimpelen aangezien we weten dat alleen 0 en 1 kunnen. Daarom kunnen we een ELSE statement gebruiken. De nieuwe query wordt:

Vergeet niet de END aan het einde van je CASE statement!

Officieel kent MySQL ook een IF statement. Aangezien dit een MySQL hersenspinsel is en over het algemeen dus niet in andere database servers bestaat is het aan te raden om deze functie niet te gebruiken

Berekenen van aantal dagen verschil tussen data

Je hebt de registratiedatum van een van je forum gebruikers. Stel nu dat je wilt weten hoeveel dagen deze gebruiker al lid is. Simpel! In MySQL kennen we DATEDIFF().

DATEDIFF(expr1,expr2)

DATEDIFF() returns expr1 – expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.

Let in dit geval goed op want de parameter volgorde is van belang. Als je niet oplet krijg je straks nog een negatief aantal dagen. Je oudste datum moet als eerste parameter!

LET OP:DATEDIFF() is geen standaard SQL functie, deze werkt alleen op MySQL en SQL server.

Als eerste halen we de huidige datum, dat doen we door de functie CURRENT_DATE() te gebruiken. Daarna zie je query in actie met diff waarin het aantal dagen verschil zit met CURRENT_DATE (op het huidige moment van schrijven). CURRENT_DATE() gebruiken hier slechts als voorbeeld, je kunt natuurlijk ook kolomnamen gebruiken.

Een andere manier is met TO_DAYS(), hiermee kun je een datum omzetten in het aantal dagen vanaf 0000-00-00 (makkelijker is met DATEDIFF()). Hiermee kun je dus ook mee rekenen. Deze query geeft hetzelfde resultaat als hierboven.

LET OP: TO_DAYS() is een MySQL only functie.

Running total berekenen

Voor de volgende query willen we een andere berekening uitvoeren. Dit keer je wil je weten wat het totaal aantal posts is voorafgaande rijen plus het huidige rij. Voorbeeld: de eerste drie rijen zijn identiek en hebben allemaal een posts_count van 1337. Nu wil je weten hoeveel posts er zijn bij gebruiker 'Tri Pham' plus alle voorafgaande posts_count bij elkaar opgeteld? In dit geval is dat 1337 + 1337 + 1337 + 7 = 4018. Dit wordt een 'running total' genoemd. Dit is de query met resultaat.

De sleutel tot deze query is de join tussen b.id en a.id. Voor elke rij die een kleinere of gelijke id heeft wordt de posts_count opgenomen in de SUM(). Nu werken we met een id veld, maar dit kun je natuurlijk ook toepassen voor andere numerieke data.

Wil je het alleen weten tot en met gebruikter 'Tri Pham'? Dan voeg je simpelweg een WHERE clausule toe.

In dit geval is de sorteer volgorde niet meer belangrijk, dus die is weggehaald uit de query.

We hebben 5 handige queries laten zien die laten zien waar SQL wel allemaal tot toe in staat is. Heb je zelf nog een handige query die je wilt delen? Deel het met ons!